一悟民、Explain
- EXPLAIN不會告訴你關(guān)于觸發(fā)器、存儲過程的信息或函數(shù)對查詢的影響情況
- EXPLAIN不考慮各種Cache
- EXPLAIN不能顯示MySQL在執(zhí)行查詢時所作的優(yōu)化工作
- 部分統(tǒng)計信息是估算的篷就，并非精確值
- EXPALIN只能解釋SELECT操作射亏，其他操作要重寫為SELECT后查看執(zhí)行計劃
select_type
查詢中每個select子句的類型
-
SIMPLE(簡單SELECT,不使用UNION或子查詢等) -
PRIMARY(查詢中若包含任何復(fù)雜的子部分,最外層的select被標記為PRIMARY) -
UNION(UNION中的第二個或后面的SELECT語句) -
DEPENDENT UNION(UNION中的第二個或后面的SELECT語句，取決于外面的查詢) -
UNION RESULT(UNION的結(jié)果) -
SUBQUERY(子查詢中的第一個SELECT) -
DEPENDENT SUBQUERY(子查詢中的第一個SELECT竭业，取決于外面的查詢) -
DERIVED(派生表的SELECT, FROM子句的子查詢) -
UNCACHEABLE SUBQUERY(一個子查詢的結(jié)果不能被緩存智润，必須重新評估外鏈接的第
一行)
Type
表示MySQL在表中找到所需行的方式，又稱“訪問類型”永品。
常用的類型有：ALL, index, range, ref, eq_ref, const, system, NULL（從左到右做鹰，性能從差到好）
-
all:Full Table Scan， MySQL將遍歷全表以找到匹配的行 -
index: Full Index Scan鼎姐，index與ALL區(qū)別為index類型只遍歷索引樹 -
range:只檢索給定范圍的行钾麸，使用一個索引來選擇行 -
ref:表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值 -
eq_ref:類似ref炕桨，區(qū)別就在使用的索引是唯一索引饭尝，對于每個索引鍵值，表中只有一條記錄匹配献宫，簡單來說钥平，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件 -
const、system:當(dāng)MySQL對查詢某部分進行優(yōu)化姊途，并轉(zhuǎn)換為一個常量時涉瘾，使用這些類型訪問。如將主鍵置于where列表中捷兰，MySQL就能將該查詢轉(zhuǎn)換為一個常量,system是const類型的特例立叛，當(dāng)查詢的表只有一行的情況下陨倡，使用system -
null: MySQL在優(yōu)化過程中分解語句铃慷，執(zhí)行時甚至不用訪問表或索引缤灵，例如從一個索引列里選取最小值可以通過單獨索引查找完成。
possible_keys
指出MySQL能使用哪個索引在表中找到記錄咱揍，查詢涉及到的字段上若存在索引展箱，則該索引將被列出沼溜，但不一定被查詢使用
該列完全獨立于EXPLAIN輸出所示的表的次序漏麦。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。
如果該列是NULL艘策，則沒有相關(guān)的索引蹈胡。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能柬焕。如果是這樣审残，創(chuàng)造一個適當(dāng)?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢
key
key列顯示MySQL實際決定使用的鍵（索引）
如果沒有選擇索引，鍵是NULL斑举。要想強制MySQL使用或忽視possible_keys列中的索引搅轿，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX富玷。
key_len
表示索引中使用的字節(jié)數(shù)璧坟，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度赎懦，即key_len是根據(jù)表定義計算而得雀鹃，不是通過表內(nèi)檢索出的）
不損失精確性的情況下，長度越短越好
rows
表示MySQL根據(jù)表統(tǒng)計信息及索引選用情況励两，估算的找到所需的記錄所需要讀取的行數(shù)
該數(shù)字越小越好
extra
該列包含MySQL解決查詢的詳細信息,有以下幾種情況：
-
Using where:列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的黎茎，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候，表示mysql服務(wù)器將在存儲引擎檢索行后再進行過濾 -
Using temporary：表示MySQL需要使用臨時表來存儲結(jié)果集当悔，常見于排序和分組查詢 -
Using filesort：MySQL中無法利用索引完成的排序操作稱為“文件排序” -
Using join buffer：改值強調(diào)了在獲取連接條件時沒有使用索引傅瞻，并且需要連接緩沖區(qū)來存儲中間結(jié)果。如果出現(xiàn)了這個值盲憎，那應(yīng)該注意嗅骄，根據(jù)查詢的具體情況可能需要添加索引來改進能。 -
Impossible where：這個值強調(diào)了where語句會導(dǎo)致沒有符合條件的行饼疙。 -
Select tables optimized away：這個值意味著僅通過使用索引溺森，優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行
二、sql 語句
select *
查詢語句最好指明要查詢的字段窑眯，可以節(jié)省不必要的運算
in
in包含的值不應(yīng)過多
連續(xù)的值用between
不連續(xù)的用union all
子查詢用exists屏积，當(dāng)查詢的兩個表大小相當(dāng)，那么in和exists差別不大磅甩，如果一個表大一個表小炊林，則子查詢的表大的用exists，子查詢的表小的用in
排序字段盡量加索引
or
or兩邊的字段如果有一個沒有索引更胖，則最好用union all代替。否則會導(dǎo)致查詢不走索引，全表掃描
union 和 union all
union和union all的差異主要是前者需要將結(jié)果集合并后再進行唯一性過濾操作却妨，這就會涉及到排序饵逐，增加大量的CPU運算，加大資源消耗及延遲彪标。當(dāng)然倍权，union all的前提條件是兩個結(jié)果集沒有重復(fù)數(shù)據(jù)。
null 判斷
在sql語句中對字段執(zhí)行 is null 或 is not null會導(dǎo)致不走索引捞烟，全表掃描
避免在where中對字段運算
where中避免使用類似 where age*2=8薄声，建議改成where age=？ 题画，問號中的參數(shù)在程序中進行8/2`后再設(shè)置到sql語句中
注意范圍查詢
對于聯(lián)合索引來說默辨，如果存在范圍查詢，比如between,>,<等條件時苍息，會造成后面的索引字段失效缩幸。所以查詢是將有索引的字段放在范圍查詢之前比較
Join on
-
inner join: 自動找出數(shù)據(jù)少的表作為驅(qū)動表 -
left join:左邊的表作為驅(qū)動表 -
right join:右邊的表作為驅(qū)動表
ON 條件（“A LEFT JOIN B ON 條件表達式”中的ON）用來決定如何從 B 表中檢索數(shù)據(jù)行。如果 B 表中沒有任何一行數(shù)據(jù)匹配 ON 的條件,將會額外生成一行所有列為 NULL 的數(shù)據(jù),在匹配階段 WHERE 子句的條件都不會被使用竞思。僅在匹配階段完成以后表谊，WHERE 子句條件才會被使用。ON將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過濾盖喷。
在使用Left (right) join的時候爆办，一定要在先給出盡可能多的匹配滿足條件，減少Where的執(zhí)行课梳。盡可能滿足ON的條件距辆，而少用Where的條件，從執(zhí)行性能來看也更加高效惦界。
強制使用索引
select * from a force index(idx_name)
三挑格、索引
索引目的
索引的目的在于提高查詢效率，可以類比字典沾歪，如果要查“mysql”這個單詞漂彤，我們肯定需要定位到m字母，然后從下往下找到y字母灾搏，再找到剩下的sql挫望。如果沒有索引，那么你可能需要把所有單詞看一遍才能找到你想要的狂窑，如果我想找到m開頭的單詞呢媳板？或者ze開頭的單詞呢？是不是覺得如果沒有索引泉哈，這個事情根本無法完成蛉幸？
索引原理
除了詞典破讨，生活中隨處可見索引的例子，如火車站的車次表奕纫、圖書的目錄等提陶。它們的原理都是一樣的，通過不斷的縮小想要獲得數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果匹层，同時把隨機的事件變成順序的事件隙笆，也就是我們總是通過同一種查找方式來鎖定數(shù)據(jù)。
數(shù)據(jù)庫也是一樣升筏，但顯然要復(fù)雜許多撑柔，因為不僅面臨著等值查詢，還有范圍查詢(>您访、<铅忿、between、in)洋只、模糊查詢(like)辆沦、并集查詢(or)等等。
數(shù)據(jù)庫應(yīng)該選擇怎么樣的方式來應(yīng)對所有的問題呢识虚？我們回想字典的例子肢扯，能不能把數(shù)據(jù)分成段，然后分段查詢呢担锤？最簡單的如果1000條數(shù)據(jù)蔚晨，1到100分成第一段，101到200分成第二段肛循，201到300分成第三段……這樣查第250條數(shù)據(jù)铭腕，只要找第三段就可以了，一下子去除了90%的無效數(shù)據(jù)多糠。
但如果是1千萬的記錄呢累舷，分成幾段比較好？稍有算法基礎(chǔ)的同學(xué)會想到搜索樹夹孔，其平均復(fù)雜度是lgN被盈，具有不錯的查詢性能。但這里我們忽略了一個關(guān)鍵的問題搭伤，復(fù)雜度模型是基于每次相同的操作成本來考慮的只怎，數(shù)據(jù)庫實現(xiàn)比較復(fù)雜，數(shù)據(jù)保存在磁盤上怜俐，而為了提高性能身堡，每次又可以把部分數(shù)據(jù)讀入內(nèi)存來計算，因為我們知道訪問磁盤的成本大概是訪問內(nèi)存的十萬倍左右拍鲤，所以簡單的搜索樹難以滿足復(fù)雜的應(yīng)用場景贴谎。
磁盤IO與預(yù)讀
前面提到了訪問磁盤汞扎，那么這里先簡單介紹一下磁盤IO和預(yù)讀，磁盤讀取數(shù)據(jù)靠的是機械運動擅这，每次讀取數(shù)據(jù)花費的時間可以分為尋道時間佩捞、旋轉(zhuǎn)延遲、傳輸時間三個部分蕾哟。
尋道時間指的是磁臂移動到指定磁道所需要的時間，主流磁盤一般在5ms以下莲蜘；
旋轉(zhuǎn)延遲就是我們經(jīng)常聽說的磁盤轉(zhuǎn)速谭确，比如一個磁盤7200轉(zhuǎn)，表示每分鐘能轉(zhuǎn)7200次票渠，也就是說1秒鐘能轉(zhuǎn)120次逐哈，旋轉(zhuǎn)延遲就是1/120/2 = 4.17ms；
傳輸時間指的是從磁盤讀出或?qū)?shù)據(jù)寫入磁盤的時間问顷，一般在零點幾毫秒昂秃，相對于前兩個時間可以忽略不計。
那么訪問一次磁盤的時間杜窄，即一次磁盤IO的時間約等于5+4.17 = 9ms左右肠骆，聽起來還挺不錯的，但要知道一臺500 -MIPS的機器每秒可以執(zhí)行5億條指令塞耕，因為指令依靠的是電的性質(zhì)蚀腿，換句話說執(zhí)行一次IO的時間可以執(zhí)行40萬條指令，數(shù)據(jù)庫動輒十萬百萬乃至千萬級數(shù)據(jù)扫外，每次9毫秒的時間莉钙，顯然是個災(zāi)難。下圖是計算機硬件延遲的對比圖筛谚，供大家參考：
考慮到磁盤IO是非常高昂的操作磁玉，計算機操作系統(tǒng)做了一些優(yōu)化，當(dāng)一次IO時驾讲，不光把當(dāng)前磁盤地址的數(shù)據(jù)蚊伞，而是把相鄰的數(shù)據(jù)也都讀取到內(nèi)存緩沖區(qū)內(nèi)，因為局部預(yù)讀性原理告訴我們蝎毡，當(dāng)計算機訪問一個地址的數(shù)據(jù)的時候厚柳，與其相鄰的數(shù)據(jù)也會很快被訪問到。
每一次IO讀取的數(shù)據(jù)我們稱之為一頁(page)沐兵。具體一頁有多大數(shù)據(jù)跟操作系統(tǒng)有關(guān)别垮，一般為4k或8k，也就是我們讀取一頁內(nèi)的數(shù)據(jù)時候扎谎，實際上才發(fā)生了一次IO碳想，這個理論對于索引的數(shù)據(jù)結(jié)構(gòu)設(shè)計非常有幫助烧董。
索引的數(shù)據(jù)結(jié)構(gòu)
前面講了生活中索引的例子，索引的基本原理胧奔，數(shù)據(jù)庫的復(fù)雜性逊移，又講了操作系統(tǒng)的相關(guān)知識，目的就是讓大家了解龙填，任何一種數(shù)據(jù)結(jié)構(gòu)都不是憑空產(chǎn)生的胳泉，一定會有它的背景和使用場景，我們現(xiàn)在總結(jié)一下岩遗，我們需要這種數(shù)據(jù)結(jié)構(gòu)能夠做些什么
其實很簡單扇商，那就是：每次查找數(shù)據(jù)時把磁盤IO次數(shù)控制在一個很小的數(shù)量級，最好是常數(shù)數(shù)量級宿礁。那么我們就想到如果一個高度可控的多路搜索樹是否能滿足需求呢案铺？就這樣，b+樹應(yīng)運而生梆靖。
詳解b+樹
![]()
b+tree
如上圖控汉，是一顆b+樹，這里只說一些重點返吻，淺藍色的塊我們稱之為一個磁盤塊姑子，可以看到每個磁盤塊包含幾個數(shù)據(jù)項（深藍色所示）和指針（黃色所示），如磁盤塊1包含數(shù)據(jù)項17和35测僵，包含指針P1壁酬、P2、P3恨课，P1表示小于17的磁盤塊舆乔，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊剂公。真實的數(shù)據(jù)存在于葉子節(jié)點即3希俩、5、9纲辽、10颜武、13、15拖吼、28鳞上、29、36吊档、60篙议、75、79、90鬼贱、99移怯。
非葉子節(jié)點只不存儲真實的數(shù)據(jù)，只存儲指引搜索方向的數(shù)據(jù)項这难，如17舟误、35并不真實存在于數(shù)據(jù)表中。
b+樹的查找過程
如圖所示姻乓，如果要查找數(shù)據(jù)項29嵌溢，
那么首先會把磁盤塊1由磁盤加載到內(nèi)存，此時發(fā)生一次IO蹋岩，在內(nèi)存中用二分查找確定29在17和35之間堵腹，鎖定磁盤塊1的P2指針，內(nèi)存時間因為非常短（相比磁盤的IO）可以忽略不計
通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存星澳，發(fā)生第二次IO，29在26和30之間旱易，鎖定磁盤塊3的P2指針
通過指針加載磁盤塊8到內(nèi)存禁偎，發(fā)生第三次IO，同時內(nèi)存中做二分查找找到29阀坏，結(jié)束查詢如暖，總計三次IO。
真實的情況是忌堂，3層的b+樹可以表示上百萬的數(shù)據(jù)盒至，如果上百萬的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的士修，如果沒有索引枷遂，每個數(shù)據(jù)項都要發(fā)生一次IO，那么總共需要百萬次的IO棋嘲，顯然成本非常非常高酒唉。
b+樹性質(zhì)
通過上面的分析，我們知道IO次數(shù)取決于b+樹的高度h
假設(shè)當(dāng)前數(shù)據(jù)表的數(shù)據(jù)為N沸移，每個磁盤塊的數(shù)據(jù)項的數(shù)量是m痪伦，則有h=㏒(m+1)N，當(dāng)數(shù)據(jù)量N一定的情況下雹锣，m越大网沾，h越小蕊爵；
而m = 磁盤塊的大小 / 數(shù)據(jù)項的大小辉哥，磁盤塊的大小也就是一個數(shù)據(jù)頁的大小，是固定的攒射，如果數(shù)據(jù)項占的空間越小证薇，數(shù)據(jù)項的數(shù)量越多度苔，樹的高度越低。
這就是為什么每個數(shù)據(jù)項浑度，即索引字段要盡量的小寇窑，比如int占4字節(jié)，要比bigint 8字節(jié)少一半箩张。
這也是為什么b+樹要求把真實的數(shù)據(jù)放到葉子節(jié)點而不是內(nèi)層節(jié)點甩骏，一旦放到內(nèi)層節(jié)點，磁盤塊的數(shù)據(jù)項會大幅度下降先慷，導(dǎo)致樹增高饮笛。當(dāng)數(shù)據(jù)項等于1時將會退化成線性表买喧。
當(dāng)b+樹的數(shù)據(jù)項是復(fù)合的數(shù)據(jù)結(jié)構(gòu)棺牧，比如(name,age,sex)的時候，b+樹是按照從左到右的順序來建立搜索樹的绕辖。
比如當(dāng)(張三,20,F)這樣的數(shù)據(jù)來檢索的時候脓诡，b+樹會優(yōu)先比較name來確定下一步的所搜方向无午，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)祝谚；
但當(dāng)(20,F)這樣的沒有name的數(shù)據(jù)來的時候宪迟，b+樹就不知道下一步該查哪個節(jié)點，因為建立搜索樹的時候name就是第一個比較因子交惯，必須要先根據(jù)name來搜索才能知道下一步去哪里查詢次泽。
比如當(dāng)(張三,F)這樣的數(shù)據(jù)來檢索時，b+樹可以用name來指定搜索方向席爽，但下一個字段age的缺失意荤，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了只锻， 這個是非常重要的性質(zhì)袭异，即索引的最左匹配特性。
建索引的幾大原則
-
最左前綴匹配原則炬藤，非常重要的原則御铃，mysql會一直向右匹配直到遇到范圍查詢(>、<沈矿、between上真、like)就停止匹配，比如
a = 1 and b = 2 and c > 3 and d = 4
如果建立(a,b,c,d)順序的索引羹膳，d是用不到索引的睡互，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。
-
=和in可以亂序就珠，比如
a = 1 and b = 2 and c = 3
建立(a,b,c)索引可以任意順序寇壳，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式。
- 盡量選擇
區(qū)分度高的列作為索引妻怎，區(qū)分度的公式是
count(distinct col)/count(*)
表示字段不重復(fù)的比例壳炎，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1逼侦，而一些狀態(tài)匿辩、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0
那可能有人會問，這個比例有什么經(jīng)驗值嗎榛丢？使用場景不同铲球，這個值也很難確定，一般需要join的字段我們都要求是0.1以上晰赞，即平均1條掃描10條記錄稼病。
- 索引列不能參與計算，保持列
干凈掖鱼，比如
from_unixtime(create_time) = ’2014-05-29’
就不能使用到索引然走，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值锨用，但進行檢索時，需要把所有元素都應(yīng)用函數(shù)才能比較隘谣，顯然成本太大增拥。
所以語句應(yīng)該寫成
create_time = unix_timestamp(’2014-05-29’)
盡量的擴展索引，不要新建索引寻歧。
比如表中已經(jīng)有a的索引掌栅，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可码泛。
慢查詢優(yōu)化基本步驟
- 先運行看看是否真的很慢猾封，注意設(shè)置
SQL_NO_CACHE -
where條件單表查，鎖定最小返回記錄表噪珊。這句話的意思是把查詢語句的where都應(yīng)用到表中返回的記錄數(shù)最小的表開始查起晌缘，單表每個字段分別查詢，看哪個字段的區(qū)分度最高 -
explain查看執(zhí)行計劃痢站，是否與2預(yù)期一致（從鎖定記錄較少的表開始查詢） -
order by limit形式的sql語句讓排序的表優(yōu)先查 - 了解業(yè)務(wù)方使用場景磷箕，根據(jù)業(yè)務(wù)針對性優(yōu)化
- 加索引時參照建索引的幾大原則
四、表結(jié)構(gòu)設(shè)計
- 建表時顯式指定字符集為
utf8或utf8mb4 - 如無特殊需求阵难，存儲引擎一律為
InnoDB - 每個表必須設(shè)置
主鍵ID岳枷，主鍵ID最好是int或bigint且為auto_increment - 所有字段應(yīng)該都是
not null并設(shè)置合適的default - 避免使用
text，blob等類型，如不可避免則最好單獨放一張表空繁，需要時根據(jù)主鍵ID查詢 - 需要經(jīng)常
join的字段如user_id殿衰，必須建索引 - 根據(jù)業(yè)務(wù)選取合適的字段類型。數(shù)字類型如無負數(shù)盛泡，則設(shè)置無符號
unsigned闷祥；字符類型盡量使用varchar，根據(jù)業(yè)務(wù)需要設(shè)置合適的長度饭于。
五蜀踏、案例分享
- 批量插入
start transaction;
set session foreign_key_checks=0; // 禁用外鍵檢查
// 批量插入
insert into table_name(name,age) values(?,?),(?,?)...;
// 批量插入或修改
insert into table_name(name,age) values(?,?),(?,?)... on duplicate key update name=values(name),age=values(age);
commit;
set session foreign_key_checks=1; // 啟用外鍵檢查
