MySQL相關(guān)

1佃迄、幾種主要的數(shù)據(jù)庫(kù)引擎的區(qū)別

MyISAM讀取操作速度很快派桩,而且不占用大量的內(nèi)存和存儲(chǔ)資源构诚,但是不支持事務(wù)和外鍵,也不支持行級(jí)鎖铆惑,寫操作會(huì)鎖定整個(gè)表范嘱,效率便低了一些,最大的缺陷是崩潰后無法安全恢復(fù)员魏,適合對(duì)事務(wù)完整性沒有要求并以訪問為主的應(yīng)用

InnoDB是一個(gè)事務(wù)性引擎丑蛤,支持行級(jí)鎖和外鍵,提供了具有提交撕阎、回滾和崩潰恢復(fù)能力的事務(wù)安全存儲(chǔ)引擎受裹,而且在READ COMMITTED和REPEATABLE READ隔離級(jí)別下支持MVCC,即多版本并發(fā)控制虏束,大大提高了事務(wù)的并發(fā)能力名斟。

MVCC:

MVCC,即多版本并發(fā)控制魄眉,在概念上砰盐,通過在行記錄上隱世的增加兩個(gè)版本號(hào)字段,一個(gè)創(chuàng)建版本號(hào)和一個(gè)刪除版本號(hào)坑律。使用MVCC可以提高減少數(shù)據(jù)的加鎖操作岩梳,大大提高了事務(wù)的并發(fā)能力囊骤。

2、事務(wù)的特征

原子性:事務(wù)是一個(gè)完整的操作冀值,要么全部執(zhí)行也物,要么都不執(zhí)行

一致性:當(dāng)事務(wù)完成時(shí),數(shù)據(jù)必須處于一致狀態(tài)

隔離性:對(duì)數(shù)據(jù)修改的所有并發(fā)事務(wù)都是彼此隔離的

持久性:事務(wù)一旦提交列疗,對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)的修改便是永久的

3滑蚯、MySQL的隔離級(jí)別

串行化(Serializable):可避免臟讀、幻讀和不可重復(fù)讀

可重復(fù)讀(Repeatable read):可避免臟讀和不可重復(fù)讀

讀已提交(Read committed):可避免臟讀

讀未提交(read uncommitted):最低級(jí)別抵栈,任何情況都無法保證

臟讀

讀取了另一個(gè)未提交事務(wù)中的數(shù)據(jù)

不可重復(fù)讀和幻讀

都是讀取了另一個(gè)已提交事務(wù)中的數(shù)據(jù)告材,區(qū)別在于不可重復(fù)讀重點(diǎn)在于更新和刪除,幻讀重點(diǎn)在于插入

在可重復(fù)讀中古劲,第一次讀取數(shù)據(jù)時(shí)斥赋,就將這些數(shù)據(jù)加索,其他事務(wù)就無法修改這些數(shù)據(jù)产艾,就實(shí)現(xiàn)可重復(fù)讀了疤剑。但是無法鎖住插入的數(shù)據(jù),這個(gè)事務(wù)就發(fā)現(xiàn)莫名奇妙多了一個(gè)數(shù)據(jù)闷堡,這就是幻讀隘膘。

4、鎖的級(jí)別

共享鎖:可以并發(fā)讀取數(shù)據(jù)杠览,但任何事務(wù)都不能獲取排它鎖弯菊,直到共享鎖釋放

排它鎖:對(duì)數(shù)據(jù)A加上排它鎖后,其他事務(wù)不能再對(duì)A加上任何類型的鎖倦零,獲取排它鎖的事務(wù)可以對(duì)數(shù)據(jù)進(jìn)行讀取和修改操作

5、鎖的粒度

行級(jí)鎖:粒度最小吨悍,并發(fā)度最高

頁級(jí)鎖:一次鎖定一頁扫茅,

表級(jí)鎖:粒度大,并發(fā)度低

6育瓜、悲觀鎖和樂觀鎖

悲觀鎖:每次獲取數(shù)據(jù)的時(shí)候都會(huì)認(rèn)為別人會(huì)修改數(shù)據(jù)葫隙,所以每次拿數(shù)據(jù)時(shí)都加上鎖

樂觀鎖:每次獲取數(shù)據(jù)時(shí)都會(huì)認(rèn)為別人不會(huì)修改數(shù)據(jù),只是在提交數(shù)據(jù)更新的時(shí)候才會(huì)去檢查在此期間有沒有其他人去更新這個(gè)數(shù)據(jù)躏仇,如果發(fā)生沖突恋脚,則返回用戶錯(cuò)誤數(shù)據(jù),讓用戶決定如何處理焰手,一般實(shí)現(xiàn)樂觀鎖的方式就是記錄數(shù)據(jù)版本號(hào)

7糟描、explain詳解

id:標(biāo)識(shí)select查詢的序列號(hào),標(biāo)識(shí)查詢select子句的執(zhí)行順序

select_type:查詢類型:simple(簡(jiǎn)單子查詢)书妻、primary(包含union或子查詢船响,最外層的部分標(biāo)記為primary)、subquery(查詢中的子查詢)、derived(派生表见间,從子查詢派生出來的聊闯,位于from中的紫釵性能)

table:表示正在訪問的表名,也可能是臨時(shí)表或者union合并結(jié)果集

derivedN米诉,表示使用了id為N的查詢產(chǎn)生的衍生表

union n1,n2菱蔬,n1,n2表示參與union的id

type:類型

all:全表掃描

index:全索引掃描

range:index的范圍限制

index_subquery:在某些in查詢使用此種類型,查詢的是非唯一索引

unique_subquery:在某些in查詢使用此種類型

index_merge:索引合并優(yōu)化被使用了

ref_or_null:MySQL必須在初次查找的結(jié)果找到null條目史侣,然后進(jìn)行二次查找

ref:使用了非唯一性索引進(jìn)行查找

eq_ref:唯一性索引

const:主鍵出現(xiàn)再where后面作為條件查詢拴泌,MySQL優(yōu)化器會(huì)把此次查詢優(yōu)化轉(zhuǎn)化為一個(gè)常量

system:表中只有一行

possible_keys:顯示可能用到的索引

keys:MySQL使用了哪個(gè)索引

rows:大致估算出找出所需的記錄需要讀取的行數(shù)

extra:包含了一些十分重要的額外信息

using filesort:說明MySQL無法利用索引進(jìn)行排序,只能用排序算法進(jìn)行排序抵窒,這會(huì)額外消耗資源弛针,說明這個(gè)SQL需要優(yōu)化,需要重新建立索引等

using temporay:建立了臨時(shí)表保存中間結(jié)果李皇,查詢完之后又把臨時(shí)表刪除削茁,出現(xiàn)這個(gè)說明sql需要優(yōu)化

using index:表明當(dāng)前查詢時(shí)覆蓋索引的,直接從索引中讀取數(shù)據(jù)掉房,而不用訪問數(shù)據(jù)表

using where:使用where進(jìn)行條件過濾

using join buffer:使用了連接緩存

impossible where:where語句總是false

distinct:優(yōu)化distinct操作茧跋,在找到第一匹配的元組后即停止找同樣值的動(dòng)作

重點(diǎn):

type、possible_keys卓囚、key瘾杭、rows、extra

8哪亿、索引原理

什么是索引

索引是一種數(shù)據(jù)結(jié)構(gòu)粥烁,幫助我們快速檢索數(shù)據(jù)庫(kù)中的數(shù)據(jù)。

索引的優(yōu)勢(shì)

加快檢索速度蝇棉,減少I/O次數(shù)

索引的劣勢(shì)

索引會(huì)占用存儲(chǔ)空間讨阻,構(gòu)建索引會(huì)降低數(shù)據(jù)表修改的效率

索引類型

從存儲(chǔ)結(jié)構(gòu)上來劃分:BTree索引(B-Tree或B+Tree索引),Hash索引篡殷,full-index全文索引钝吮,R-Tree索引。這里所描述的是索引存儲(chǔ)時(shí)保存的形式

從應(yīng)用層次來分:普通索引板辽,唯一索引奇瘦,復(fù)合索引

根據(jù)中數(shù)據(jù)的物理順序與鍵值的邏輯(索引)順序關(guān)系:聚集索引,非聚集索引

聚簇索引:葉子節(jié)點(diǎn)保存的就是數(shù)據(jù)節(jié)點(diǎn)

非聚簇索引:葉子節(jié)點(diǎn)保存的是指向?qū)?yīng)數(shù)據(jù)塊的指針

MyISAM和InnoDB存儲(chǔ)引擎下索引有什么區(qū)別

MyISAM引擎采用B+tree作為索引結(jié)構(gòu)劲弦,索引文件和數(shù)據(jù)文件是分離的耳标,葉節(jié)點(diǎn)的data域保存的是數(shù)據(jù)記錄的地址。

主鍵索引和輔助索引在結(jié)構(gòu)上沒有什么區(qū)別邑跪,只是主鍵索引要求key必須是唯一的麻捻,而輔助索引則可以重復(fù)

InnoDB引擎也采用B+tree作為索引結(jié)構(gòu)纲仍,主鍵索引也就是聚簇索引中,葉節(jié)點(diǎn)的data域保存了完整的數(shù)據(jù)記錄贸毕;而輔助索引的葉節(jié)點(diǎn)的data域保存了主鍵

9郑叠、如何優(yōu)化MySQL

MySQL優(yōu)化大致可以分為三部分:索引的優(yōu)化、SQL語句優(yōu)化和表的優(yōu)化

索引優(yōu)化可以遵循以下幾個(gè)原則:

聯(lián)合索引最左前綴匹配原則

盡量把字段長(zhǎng)度小的列放在聯(lián)合索引的最左側(cè)(因?yàn)樽侄卧叫∶鞴鳎豁摯鎯?chǔ)的數(shù)據(jù)量越大乡革,IO性能也就越好)

order by 有多個(gè)列排序的,應(yīng)該建立聯(lián)合索引

對(duì)于頻繁的查詢優(yōu)先考慮使用覆蓋索引

前導(dǎo)模糊查詢不會(huì)使用索引摊腋,比如說Like '%aaa%'這種

負(fù)向條件不會(huì)使用索引沸版,如!=,<>兴蒸,not like视粮,not in,not exists

索引應(yīng)該建立在區(qū)分度比較高的字段上? 一般區(qū)分度在80%以上的時(shí)候就可以建立索引橙凳,區(qū)分度可以使用 count(distinct(列名))/count(*)

對(duì)于where子句中經(jīng)常使用的列蕾殴,最好設(shè)置索引

SQL語句優(yōu)化,可以通過explain查看SQL的執(zhí)行計(jì)劃岛啸,優(yōu)化語句原則可以有:

在where和order by涉及的列上建立合適的索引钓觉,避免全表掃描

任何查詢都不要使用select * ,而是用具體的字段列表代替

多表連接時(shí)坚踩,盡量小表驅(qū)動(dòng)大表荡灾,即小表join大表

用exists代替in

盡量避免在where字句中對(duì)字段進(jìn)行函數(shù)操作

數(shù)據(jù)庫(kù)表優(yōu)化

表字段盡可能用not null

字段長(zhǎng)度固定表查詢會(huì)更快

將數(shù)據(jù)庫(kù)大表按照時(shí)間或者一些標(biāo)志拆分成小表

水平拆分:將記錄散列到不同的表中,每次從分表查詢

垂直拆分:將表中的大字段單獨(dú)拆分到另一張表瞬铸,形成一對(duì)一的關(guān)系

10批幌、為什么索引結(jié)構(gòu)默認(rèn)使用B+Tree,而不是Hash嗓节,二叉樹荧缘,紅黑樹?

B+tree是一種多路平衡查詢樹赦政,節(jié)點(diǎn)是天然有序的胜宇,非葉子節(jié)點(diǎn)包含多個(gè)元素窝稿,不保存數(shù)據(jù)美莫,只用來索引桐汤,葉子節(jié)點(diǎn)包含完整數(shù)據(jù)和帶有指向下一個(gè)節(jié)點(diǎn)的指針,形成一個(gè)有序鏈表掰派,有助于范圍和順序查找。因?yàn)榉侨~子節(jié)點(diǎn)不保存數(shù)據(jù)左痢,所以同樣大小的磁盤頁可以容納更多的元素靡羡,同樣能數(shù)據(jù)量的情況下系洛,B+tree相比B-tree高度更低,因此查詢時(shí)IO會(huì)更少

B-tree不管葉子節(jié)點(diǎn)還是非葉子節(jié)點(diǎn)略步,都會(huì)保存數(shù)據(jù)描扯,這樣導(dǎo)致在非葉子節(jié)點(diǎn)中能保存的指針數(shù)量變少(有些資料也稱為扇出),指針少的情況下要保存大量數(shù)據(jù)趟薄,只能增加樹的高度绽诚,導(dǎo)致IO操作變多,查詢性能變低杭煎;

Hash索引底層是基于哈希表恩够,就是以key-value存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu),多個(gè)數(shù)據(jù)在存儲(chǔ)關(guān)系上是沒有任何順序關(guān)系的羡铲。只適合等值查詢蜂桶,不適合范圍查詢,而且也無法利用索引完成排序也切,不支持聯(lián)合索引的最左匹配原則扑媚,如果有大量重復(fù)鍵值的情況下,哈希索引效率會(huì)很低贾费,因?yàn)榇嬖诠E鲎病?/p>

二叉樹:樹的高度不均勻钦购,不能自平衡,查找效率跟數(shù)據(jù)有關(guān)(樹的高度)褂萧,并且IO代價(jià)高押桃。

紅黑樹:樹的高度隨著數(shù)據(jù)量增加而增加,IO代價(jià)高导犹。

11唱凯、主鍵索引查詢只會(huì)查一次,而非主鍵索引需要回表查詢多次谎痢。是所有情況都是這樣的嗎磕昼?非主鍵索引一定會(huì)查詢多次嗎?

如果我們使用了覆蓋索引节猿,也就是當(dāng)一條查詢語句符合覆蓋索引條件時(shí)票从,MySQL只需要通過查詢索引就可以返回需要的數(shù)據(jù),避免了查到索引后再返回表操作滨嘱,減少I/O提高效率

12峰鄙、聯(lián)合索引最左前綴匹配原則

在創(chuàng)建多列的聯(lián)合索引下,MySQL索引查詢會(huì)遵循最左前綴匹配原則太雨,即檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開始匹配吟榴。例如,當(dāng)創(chuàng)建了一個(gè)聯(lián)合索引(key1,key2,key3)相當(dāng)于創(chuàng)建了(key1)囊扳,(key1,key2)吩翻,(key1,key2,key3)三個(gè)索引

13兜看、MySQL5.6中對(duì)索引做了哪些優(yōu)化

索引下推技術(shù)

例如有索引(key1,key2),SQL語句中where key1 = 'XXX' and key2 like '%XXX%'

如果沒有使用索引下推技術(shù)狭瞎,MySQL會(huì)通過key1 = 'XXX'從存儲(chǔ)引擎返回對(duì)應(yīng)的數(shù)據(jù)至MySQL服務(wù)端细移,服務(wù)端再基于key2 like 判斷是否符合條件。

如果使用了索引下推技術(shù)熊锭,MySQL首先返回key1='XXX'的索引葫哗,再根據(jù)key2 like 判斷索引是否符合條件,如果符合則通過索引定位數(shù)據(jù)球涛,如果不符合則直接reject掉劣针。有了索引下推優(yōu)化,可以在有l(wèi)ike條件查詢的情況下亿扁,減少回表次數(shù)捺典。

14、那什么情況下會(huì)發(fā)生明明創(chuàng)建了索引从祝,但是執(zhí)行的時(shí)候并沒有通過索引呢

在一條單表查詢語句真正執(zhí)行之前襟己,MySQL的查詢優(yōu)化器會(huì)找出執(zhí)行該語句所有可能使用的方案,對(duì)比之后找出成本最低的方案牍陌。

這個(gè)成本最低的方案就是所謂的執(zhí)行計(jì)劃擎浴。優(yōu)化過程大致如下:

1、根據(jù)搜索條件毒涧,找出所有可能使用的索引?

2贮预、計(jì)算全表掃描的代價(jià)?

3、計(jì)算使用不同索引執(zhí)行查詢的代價(jià)?

4契讲、對(duì)比各種執(zhí)行方案的代價(jià)仿吞,找出成本最低的那一個(gè)

15、紅黑樹的五個(gè)性質(zhì)

節(jié)點(diǎn)是黑色或者紅色

根節(jié)點(diǎn)是黑色

所有的葉子節(jié)點(diǎn)都是黑色捡偏,葉子是nil節(jié)點(diǎn)

每個(gè)紅色節(jié)點(diǎn)唤冈,必須有兩個(gè)黑色的子節(jié)點(diǎn)

從任一節(jié)點(diǎn)到其每個(gè)葉子的所有簡(jiǎn)單路徑都包含相同數(shù)目的黑色節(jié)點(diǎn)

16、MySQL一條查詢語句是如何執(zhí)行的

MySQL的基本架構(gòu)分為兩個(gè)層次:server層和存儲(chǔ)引擎層

其中server層包括:連接器银伟、查詢緩存你虹、分析器、優(yōu)化器彤避、執(zhí)行器

存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和讀取

連接器負(fù)責(zé)與客戶端建立鏈接傅物、權(quán)限驗(yàn)證和管理鏈接

當(dāng)向server發(fā)送sql請(qǐng)求時(shí),如果開啟了查詢緩存忠藤,則去查詢緩存中看是否有挟伙,如果有則直接返回楼雹,否則再走分析器進(jìn)行詞法分析和語法分析

分析器工作完成后模孩,將語句傳遞到優(yōu)化器進(jìn)行優(yōu)化尖阔,確定優(yōu)化執(zhí)行方案,優(yōu)化器會(huì)根據(jù)自己的優(yōu)化算法選擇效率最好的一個(gè)方案

之后就會(huì)交給執(zhí)行器進(jìn)行執(zhí)行榨咐,執(zhí)行之前還會(huì)進(jìn)行權(quán)限驗(yàn)證介却,然后調(diào)用數(shù)據(jù)庫(kù)存儲(chǔ)引擎接口,返回引擎執(zhí)行結(jié)果

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末块茁,一起剝皮案震驚了整個(gè)濱河市齿坷,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌数焊,老刑警劉巖永淌,帶你破解...
    沈念sama閱讀 217,406評(píng)論 6 503
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場(chǎng)離奇詭異佩耳,居然都是意外死亡遂蛀,警方通過查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,732評(píng)論 3 393
  • 文/潘曉璐 我一進(jìn)店門干厚,熙熙樓的掌柜王于貴愁眉苦臉地迎上來李滴,“玉大人,你說我怎么就攤上這事蛮瞄∷鳎” “怎么了?”我有些...
    開封第一講書人閱讀 163,711評(píng)論 0 353
  • 文/不壞的土叔 我叫張陵挂捅,是天一觀的道長(zhǎng)芹助。 經(jīng)常有香客問我,道長(zhǎng)闲先,這世上最難降的妖魔是什么周瞎? 我笑而不...
    開封第一講書人閱讀 58,380評(píng)論 1 293
  • 正文 為了忘掉前任,我火速辦了婚禮饵蒂,結(jié)果婚禮上声诸,老公的妹妹穿的比我還像新娘。我一直安慰自己退盯,他們只是感情好彼乌,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,432評(píng)論 6 392
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著渊迁,像睡著了一般慰照。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上琉朽,一...
    開封第一講書人閱讀 51,301評(píng)論 1 301
  • 那天毒租,我揣著相機(jī)與錄音,去河邊找鬼箱叁。 笑死墅垮,一個(gè)胖子當(dāng)著我的面吹牛惕医,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播算色,決...
    沈念sama閱讀 40,145評(píng)論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼抬伺,長(zhǎng)吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼!你這毒婦竟也來了灾梦?” 一聲冷哼從身側(cè)響起峡钓,我...
    開封第一講書人閱讀 39,008評(píng)論 0 276
  • 序言:老撾萬榮一對(duì)情侶失蹤,失蹤者是張志新(化名)和其女友劉穎若河,沒想到半個(gè)月后能岩,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 45,443評(píng)論 1 314
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡萧福,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,649評(píng)論 3 334
  • 正文 我和宋清朗相戀三年捧灰,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片统锤。...
    茶點(diǎn)故事閱讀 39,795評(píng)論 1 347
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡毛俏,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出饲窿,到底是詐尸還是另有隱情煌寇,我是刑警寧澤,帶...
    沈念sama閱讀 35,501評(píng)論 5 345
  • 正文 年R本政府宣布逾雄,位于F島的核電站阀溶,受9級(jí)特大地震影響,放射性物質(zhì)發(fā)生泄漏鸦泳。R本人自食惡果不足惜银锻,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,119評(píng)論 3 328
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望做鹰。 院中可真熱鬧击纬,春花似錦、人聲如沸钾麸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,731評(píng)論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽饭尝。三九已至肯腕,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間钥平,已是汗流浹背实撒。 一陣腳步聲響...
    開封第一講書人閱讀 32,865評(píng)論 1 269
  • 我被黑心中介騙來泰國(guó)打工, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人知态。 一個(gè)月前我還...
    沈念sama閱讀 47,899評(píng)論 2 370
  • 正文 我出身青樓捷兰,卻偏偏與公主長(zhǎng)得像,于是被迫代替她去往敵國(guó)和親肴甸。 傳聞我的和親對(duì)象是個(gè)殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,724評(píng)論 2 354