關(guān)于SQL優(yōu)化，這個(gè)問題，相信大家過多過少都有過一些了解。最近我也在研究SQL優(yōu)化方面的東西，分享一些經(jīng)驗(yàn)。
首先簡(jiǎn)單介紹下索引，"索引" 是SQL優(yōu)化中很重要的一部分（但是索引并不是優(yōu)化的唯一選項(xiàng)）
索引原理簡(jiǎn)述
如何理解索引？索引其實(shí)就是一種數(shù)據(jù)結(jié)構(gòu)，用于快速定位和訪問數(shù)據(jù)庫(kù)中的數(shù)據(jù)。
通常來說索引使用的數(shù)據(jù)結(jié)構(gòu)是 B-Tree / B+Tree。以B-Tree為例，假設(shè)每個(gè)節(jié)點(diǎn)存儲(chǔ)100個(gè)Key，三層的B-Tree 可存儲(chǔ)一百萬(wàn)數(shù)據(jù)，如果將根節(jié)點(diǎn)存入內(nèi)存中的話，只需要讀取兩次磁盤就可以從100萬(wàn)數(shù)據(jù)中找到指定數(shù)據(jù)
關(guān)于B-Tree 推薦閱讀這篇 https://www.geeksforgeeks.org/introduction-of-b-tree-2/ 包含B-Tree的查詢，新增，刪除操作如何實(shí)現(xiàn)
MySQL 執(zhí)行計(jì)劃
SQL優(yōu)化中查看執(zhí)行計(jì)劃是必不可少的一項(xiàng)，通過 explain 關(guān)鍵字可以查看MySQL中的執(zhí)行計(jì)劃
注：\G 含義是縱向顯示結(jié)果
如果之前沒有了解的 EXPLAIN 的同學(xué)，看到這個(gè)列表肯定是一臉懵逼。沒關(guān)系我們先來挑幾個(gè)重要的屬性認(rèn)識(shí)一下。
- type：ALL 代表全表掃描
- key：代表使用的索引，NULL 代表沒有使用索引
- rows：掃描行數(shù)
關(guān)于explain 再擴(kuò)展一下，先執(zhí)行 explain extended ...; ，再執(zhí)行 SHOW WARNINGS 可以看到MySQL優(yōu)化器對(duì)我們的SQL做了什么優(yōu)化。如下圖所示
利用索引來優(yōu)化SQL
使用索引的優(yōu)點(diǎn)：減少服務(wù)器掃描的數(shù)據(jù)量、避免排序和臨時(shí)表、將隨機(jī)I/O變?yōu)轫樞騃/O
通過下圖，我們可以看到，添加了索引之后掃描行數(shù)從三十萬(wàn)行降到了1，性能提升可想而知
生產(chǎn)環(huán)境要注意，創(chuàng)建索引是一個(gè)非常耗時(shí)的操作，并且會(huì)阻塞其他操作。
生產(chǎn)環(huán)境添加索引有沒有什么完美方案？
有的，如果你的MySQL使用主從策略的時(shí)候，可以像Nginx不停機(jī)升級(jí)web服務(wù)那樣，先移除一個(gè)節(jié)點(diǎn)為該節(jié)點(diǎn)執(zhí)行ALTER TABLE操作，然后巴拉巴拉，因?yàn)榫唧w我也沒操作過就不細(xì)說了，感興趣大家可以Google一下，動(dòng)手嘗試一下。如果是單機(jī)部署的話，只能用戶少的時(shí)候在執(zhí)行這種操作了
使用索引連接表
索引也可以提高表連接的性能，下面是個(gè)例子，用戶表左連訂單表，對(duì)user_id 添加索引的前后對(duì)比
like優(yōu)化
通過上述例子，我們可以看出，如果模糊查詢時(shí)以%開頭的話，MySQL無法使用索引，但是通常來說模糊查詢時(shí)我們的匹配方式都會(huì)是 %xxx%，那么如何優(yōu)化呢？
這里可以通過存"反值"的方式巧妙的解決這個(gè)問題，例如我現(xiàn)在在數(shù)據(jù)庫(kù)加一列 reverse_order_no 存儲(chǔ)訂單號(hào)的反值（并添加索引），匹配的時(shí)候再通過 REVERSE('%910') 函數(shù)將參數(shù)取反。
這里也可以使用 or，如下圖，查看執(zhí)行計(jì)劃會(huì)發(fā)現(xiàn)Extra 屬性返回 "Using sort_union(order_no,reverse_order_no); Using where" 這里代表MySQL發(fā)生了索引合并，后文我們會(huì)講到
排序以及多列索引
排序需要加索引！相信大家可能知道這個(gè)道理，但是如下圖所示，user_id 和 addtime 兩列都建立了索引，那么下面這條查詢排序使用索引了嗎？
答案是：并沒有！為什么？注意 Extra 中的 using filesort，代表MySQL 使用了內(nèi)部文件排序算法對(duì)結(jié)果集進(jìn)行了排序。MySQL 通常在一個(gè)表上只選擇一個(gè)索引（有例外的情況），這種情況如果我們希望排序使用索引的話，可以建立一個(gè)多列索引，如下圖所示
而且多列索引最左邊的列，可以當(dāng)作單列索引來使用
MySQL 優(yōu)化器特性
我們剛剛說過 MySQL 通常在一個(gè)表上只選擇一個(gè)索引，如何理解？例如索引A和索引B 一個(gè)需要掃描十萬(wàn)行，一個(gè)需要掃描五萬(wàn)行，那么MySQL一定選擇開銷最小的索引方式。
在一些特殊情況下，MySQL 會(huì)選擇 Index Merge（索引合并），即在一個(gè)表上使用多個(gè)索引
- Union：兩個(gè)基數(shù)很高的索引執(zhí)行OR操作時(shí)
- Sort-Union：與上述類似，一旦or的左右兩邊出現(xiàn)范圍查詢，會(huì)使用該算法，區(qū)別是Sort-Union會(huì)進(jìn)行排序
- intersect：針對(duì)唯一值不多的索引列，例如在 is_pay（0-未支付，1-支付），is_send（0-未發(fā)貨，1-發(fā)貨） 兩列建立索引，查詢已支付并且未發(fā)貨的訂單，如下圖所示
根據(jù)MySQL 5.7開發(fā)文檔所示，還有一種會(huì)使用intersect，InnoDB 主鍵上的任何范圍搜索
關(guān)于Index Merge的更多信息，參考MySQL開發(fā)文檔
https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html
索引的影響
添加索引雖然可以提升我們的SQL性能，但是隨之而來也會(huì)帶來一定的開銷
- 數(shù)據(jù)插入和更新的性能，因?yàn)樾枰獦?gòu)建索引的原因，在數(shù)據(jù)量大的時(shí)候會(huì)比較明顯，下圖是 《Effective MySQL之SQL語(yǔ)句最優(yōu)化》中對(duì)添加索引前后的插入性能對(duì)比
- 磁盤空間的影響，同樣也是來自于書中的測(cè)試
可以看到在添加了索引之后，空間占用是原來的7倍，在數(shù)據(jù)量龐大時(shí)，這是一個(gè)需要關(guān)注的點(diǎn)。
還有需要注意的一點(diǎn)是，在MySQL Innodb 中有聚簇索引和二級(jí)索引，一般來說主鍵就是聚簇索引，而其他的索引都是二級(jí)索引。
二級(jí)索引所存儲(chǔ)的值是聚簇索引。所以當(dāng)使用二級(jí)索引來進(jìn)行檢索時(shí)，MySQL 會(huì)先通過該索引找到對(duì)應(yīng)的聚簇索引，再通過該聚簇索引找到對(duì)應(yīng)的數(shù)據(jù)。這時(shí)使用占用字節(jié)更小的類型來做主鍵會(huì)更好，會(huì)節(jié)省索引占用空間
參考
Effective MySQL之SQL語(yǔ)句最優(yōu)化
