點贊再看，養成習慣，微信搜一搜【一角錢小助手】關注更多原創技術文章。本文 GitHub org_hejianhui/JavaStudy 已收錄，有我的系列文章。
前言
- MySQL索引底層資料結構與算法
- MySQL性能優化原理-前篇
- MySQL性能優化-實踐篇1
上一篇 《MySQL性能優化-實踐篇1》我們講了資料庫表設計的一些原則，Explain工具的介紹、SQL語句優化索引的最佳實踐，本篇繼續來聊聊 MySQL 如何選擇合适的索引。
MySQL Trace 工具
MySQL 最終是否選擇走索引或者一張表涉及多個索引，最終是如何選擇索引，可以使用 trace 工具來一查究竟，開啟 trace工具會影響 MySQL 性能，是以隻能臨時分析 SQL 使用，用完之後立即關閉。
案例分析
講 trace 工具之前我們先來看一個案例：
MySQL 如何選擇合适的索引
如果用name索引需要周遊name字段聯合索引樹，然後還需要根據周遊出來的主鍵值去主鍵索引樹裡再去查出最終資料，成本比全表掃描還高，可以用覆寫索引優化，這樣隻需要周遊name字段的聯合索引樹就能拿到所有結果，如下:
對于上面這兩種
name>'a'
和
name>'zzz'
的執行結果，mysql最終是否選擇走索引或者一張表涉及多個索引，mysql最終如何選擇索引，我們可以用trace工具來一查究竟，開啟trace工具會影響mysql性能，是以隻能臨時分析sql使用，用完之後立即關閉。
trace工具用法
開啟/關閉Trace
案例1
執行這兩句sql
提出來trace值，詳見注釋
結論：全表掃描的成本低于索引掃描，是以MySQL最終選擇全表掃描。
案例2
結論：檢視trace字段可知索引掃描的成本低于全表掃描，是以MySQL最終選擇索引掃描。
常見SQL深入優化
Order by
Group by
Order by
Group by
案例1
EXPLAIN select * from employees where name = 'ZhangSan' and position = 'dev' order by age;
分析：
利用最左字首法則：中間字段不能斷，是以查詢用到了
name索引
，從 key_len = 74 也能看出，age 索引列用在排序過程過程中，因為 Extra 字段裡沒有
using filesort
。
案例2
分析：
從 explain 的執行結果來看：key_len = 74，查詢使用了 name 索引，由于用了 position 進行排序，跳過了 age，出現了
Using filesort
。
案例3
分析：
查詢隻用到
索引name
，age 和 position 用于排序，無
Using filesort
。
案例4
分析：
和案例3中explain的執行結果一樣，但是出現了
Using filesort
，因為索引的建立順序為
name,age,position
, 但是排序的時候 age 和 position 颠倒位置了。
案例5
分析：
與案例4對比，在Extra中并未出現**
Using filesort
**，因為 age 為常量，在排序中被優化，是以索引未颠倒，不會出現
Using filesort
。
案例6
分析：
雖然排序的字段列與索引順序一樣，且
order by
預設升序，這裡
position desc
變成列降序，導緻與索引的排序方式不同，進而産生
Using filesort
。MySQL8 以上版本有降序索引可以支援該種查詢方式。
案例7
分析：
對于排序來說，多個相等條件也是範圍查詢。
案例8
可以用覆寫索引優化
優化總結
- MySQL支援兩種方式的排序
filesort
index
- order by 滿足兩種情況會使用 Using index.
- order by 語句使用索引最左前例。
- 使用 where 子句與 order by 子句條件列組合滿足索引最左前例。
盡量在 索引列上完成排序，遵循 索引建立( 索引建立的順序)時的最左字首法則。如果 order by 的條件不在索引列上，就會産生 Using filesort。能用覆寫索引盡量用覆寫索引。group by 和 order by 很類似，其實質是 先排序後分組，遵循 索引建立順序的最左字首法則。對于 group by 的優化如果不需要排序的可以加上
order by null
禁止排序。 注意：where 高于 having，能寫在 where 中的限定條件就不要去 having 限定了。
Using filesort檔案排序原理
filesort檔案排序方式
- 單路排序：是一次性取出滿足條件行的所有字段，然後在
sort buffer
- 雙路排序(又叫回表排序模式)：是首先根據相應的條件取出相應的排序字段和可以直接定位運作資料的行ID，然後在 sort buffer 中進行排序，排序完後需要再次取回其它需要的字段；用 trace 工具可以看到 sort_mode 資訊裡顯示 < sort_key, rowid >
MySQL 通過比較系統變量
max_length_for_sort_data
(預設1024位元組) 的大小和需要查詢的字段總大小來判斷使用那種排序模式。
- 如果
max_length_for_sort_data
- 如果
max_length_for_sort_data
驗證各種排序方式
檢視下這條sql對應trace結果如下(隻展示排序部分):
修改系統變量
max_length_for_sort_data
(預設1024位元組) ，employees 表所有字段長度總和肯定大于10位元組
trace排序部分結果:
單路排序的詳細過程：
- 從索引 name 找到第一個滿足 name='ZhangSan' 條件的主鍵 id；
- 根據主鍵id取出整行，取出所有字段的值，存入sort_buffer中；
- 從索引name找到下一個滿足 name='ZhangSan' 條件的主鍵 id;
- 重複步驟2、3直到不滿足 name='ZhangSan'；
- 對 sort_buffer 中的資料按照字段 position 進行排序；
- 傳回結果給用戶端
雙路排序的詳細過程：
- 從索引 name 找到第一個滿足 name='ZhangSan' 的主鍵id；
- 根據主鍵id取出整行，把排序字段 position 和 主鍵id 這兩個字段放到 sort_buffer 中；
- 從索引 name 取下一個滿足 name='ZhangSan' 記錄的主鍵id；
- 重複步驟3、4直到不滿足 name='ZhangSan'；
- 對 sort_buffer 中的字段 position 和 主鍵id按照 position 進行排序；
- 周遊排序好的 id 和 字段 position，按照 id 的值回到原表中取出所有的字段的值傳回給用戶端。
對比兩個排序模式，單路排序會把所有需要查詢的字段都放到 sort_buffer 中，而雙路排序隻會把主鍵和需要排序的字段放到 sort_buffer 中進行排序，然後再通過主鍵回到原表查詢需要的字段。
如果MySQL排序記憶體配置的比較小并且沒有條件繼續增加了，可以适當把
max_length_for_sort_data
配置小點，讓優化器選擇使用雙路排序算法，可以在 sort_buffer 中一次排序更多的行，隻是需要再根據主鍵回到原表取資料。
如果MySQL排序記憶體有條件可以配置比較大，可以适當增大
max_length_for_sort_data
的值，讓優化器優先選擇全字段排序(單路排序)，把需要的字段放到 sort_buffer 中，這樣排序後就會直接從記憶體裡傳回查詢結果了。
是以，MySQL 通過
max_length_for_sort_data
這個參數來控制排序，在不同場景使用不同的排序模式，進而提升排序效率。
注意：如果全部使用sort_buffer 記憶體排序一般情況下效率會高于磁盤檔案排序，但不能因為這個就随便增大 sort_buffer(預設1M)，MySQL很多參數設定都做過優化的，不要輕易調整。
分頁查詢優化
在這我們先往
employess
插入一些測試資料
很多時候我們業務系統實作分頁功能可能會用如下SQL實作
表示從表 employees 中取出從 10001 行開始的 10 行記錄。看似隻查詢了 10 條記錄，實際這條 SQL 是先讀取 10010 條記錄，然後抛棄前 10000 條記錄，然後讀到後面 10 條想要的資料。是以要查詢一張大表比較靠後的資料，執行效率是非常低的。
常見的分頁場景優化技巧
- 根據自增且連續的主鍵排序的分頁查詢
- 根據非主鍵字段排序的分頁查詢
案例1: 根據自增且連續的主鍵排序的分頁查詢
首先來看一個根據自增且連續主鍵排序的分頁查詢的例子：
該 SQL 表示查詢從第 9001開始的五行資料，沒添加單獨 order by，表示通過主鍵排序。我們再看表 employees ，因為主鍵是自增并且連續的，是以可以改寫成按照主鍵去查詢從第 9001開始的五行資料，如下:
查詢結果是一緻的，我們再對比一下執行計劃：
顯然改寫後的 SQL 走了索引，而且掃描的行數大大減少，執行效率更高。但是，這條改寫的 SQL 在很多場景并不實用，因為表中可能某些記錄被删後，主鍵空缺，導緻結果不一緻，如下圖試驗所示(先删除一條前面的記錄，然後再測試原 SQL 和優化後的 SQL):
兩條 SQL 的結果并不一樣，是以，如果主鍵不連續，不能使用上面描述的優化方法。
另外如果原SQL是order by 非主鍵的字段，按照上面說的方法改寫會導緻兩條SQL的結果不一緻。是以這種改寫得滿足以下兩個條件：
- 主鍵自增且連續
- 結果是按照主鍵排序的
案例2: 根據非主鍵字段排序的分頁查詢
再看一個根據非主鍵字段排序的分頁查詢，SQL 如下:
發現并沒有使用 name 字段的索引(key 字段對應的值為 null)，具體原因上前面講過 : 掃描整個索引并查找到沒索引的行(可能要周遊多個索引樹)的成本比掃描全表的成本更高，是以優化器放棄使用索引。知道不走索引的原因，那麼怎麼優化呢? 其實關鍵是讓排序時傳回的字段盡可能少，是以可以讓排序和分頁操作先查出主鍵，然後根據主鍵查到對應的記錄，SQL 改寫如下：
需要的結果與原 SQL 一緻，執行時間減少了一半以上，我們再對比優化前後sql的執行計劃:
原 SQL 使用的是 filesort 排序，而優化後的 SQL 使用的是索引排序。
Join關聯查詢優化
往t1表插入1萬行記錄，往t2表插入100行記錄
MySQL 的表關聯常見有兩種算法
- Nested-Loop Join 算法
- Block Nested-Loop Join 算法
案例1：嵌套循環連接配接 Nested-Loop Join(NLJ)算法
一次一行循環地從第一張表(稱為驅動表)中讀取行，在這行資料中取到關聯字段，根據關聯字段在另一張表(被驅動表)裡取出滿足條件的行，然後取出兩張表的結果合集。
從執行計劃中可以看到這些資訊:
- 驅動表是 t2，被驅動表是 t1。先執行的就是驅動表(執行計劃結果的id如果一樣則按從上到下順序執行sql);優化器一般會優先選擇小表做驅動表。是以使用 inner join 時，排在前面的表并不一定就是驅動表。
- 使用了 NLJ 算法。一般 join 語句中，如果執行計劃 Extra 中未出現 Using join buffer 則表示使用的 join 算法是 NLJ。
上面SQL的大緻流程如下：
- 從表 t2 中讀取一行資料；
- 從第1步的資料中，取出關鍵字字段 a，到表 t1 中查找；
- 取出表 t1 中滿足條件的行，跟 t2 中擷取到的結果合并，作為結果傳回給用戶端；
- 重複上面 3 步。
整個過程會讀取 t2 表的所有資料(掃描100行)，然後周遊這每行資料中字段 a 的值，根據 t2 表中的 a 的值索引掃描 t1 表中對應的行(掃描 100次 t1 表的索引，1次掃描可以認為最終隻掃描 t1 表一行完整資料，也就是總共 t1 表也掃描了100行)。是以整個過程掃描了 200 行。
如果被驅動表的關聯字段沒有索引，使用NLJ算法性能會比較低(下面有詳細解釋)，MySQL 會選擇 Block Nested-Loop Join 算法。
案例2：基于塊的嵌套循環連接配接 Block Nested-Loop Join(BNL)算法
把驅動表的資料讀入到 join_buffer 中，然後掃描被驅動表，把被驅動表每一行取出來跟 join_buffer 中的資料做對比。
Extra 中 的Using join buffer (Block Nested Loop)說明該關聯查詢使用的是 BNL 算法。
上面sql的大緻流程如下:
- 把 t2 的所有資料放入到 join_buffer 中
- 把表 t1 中每一行取出來，跟 join_buffer 中的資料做對比
- 傳回滿足 join 條件的資料
整個過程對表 t1 和 t2 都做了一次全表掃描，是以掃描的總行數為10000(表 t1 的資料總量) + 100(表 t2 的資料總量) = 10100。并且 join_buffer 裡的資料是無序的，是以對表 t1 中的每一行，都要做 100 次判斷，是以記憶體中的判斷次數是 100 * 10000= 100 萬次。
被驅動表的關聯字段沒索引為什麼要選擇使用 BNL 算法而不使用 Nested-Loop Join 呢?
如果上面第二條sql使用 Nested-Loop Join，那麼掃描行數為 100 * 10000 = 100萬次，這個是磁盤掃描。
很顯然，用BNL磁盤掃描次數少很多，相比于磁盤掃描，BNJ 的記憶體計算會快得多。
是以MySQL對于被驅動表的關聯字段沒索引的關聯查詢，一般都會使用 BNL 算法。如果有索引一般選擇 NLJ 算法，有索引的情況下 NLJ 算法比 BNL算法性能更高。
對于關聯SQL的優化
- 關聯字段加索引，讓mysql做join操作時盡量選擇NLJ算法
- 小标驅動大表，寫多表連接配接sql時如果明确知道哪張表是小表可以用
straight_join
straight_join解釋
straight_join功能同join類似，但能讓左邊的表來驅動右邊的表，能改表優化器對于聯表查詢的執行順序。
比如 :
select * from t2 straight_join t1 on t2.a = t1.a;
代表制定mysql選 t2 表作為驅動表。
- straight_join 隻适用于inner join，并不适用于left join，right join。(因為left join，right join已經代表指 定了表的執行順序)
- 盡可能讓優化器去判斷，因為大部分情況下mysql優化器是比人要聰明的。使用straight_join一定要慎重，因 為部分情況下人為指定的執行順序并不一定會比優化引擎要靠譜。
in 和 exsits 優化
原則：小表驅動大表，即小的資料集驅動大的資料集。
in：當B表的資料集小于A表的資料集時，in優于exists
exists：當A表的資料集小于B表的資料集時，exists優于in
将主查詢A的資料，放到子查詢B中做條件驗證，根據驗證結果(true或false)來決定主查詢的資料是否保留
- EXISTS (subquery)隻傳回TRUE或FALSE,是以子查詢中的SELECT * 也可以用SELECT 1替換,官方說法是實際執行時會 忽略SELECT清單,是以沒有差別；
- EXISTS子查詢的實際執行過程可能經過了優化而不是我們了解上的逐條對比；
- EXISTS子查詢往往也可以用JOIN來代替，何種最優需要具體問題具體分析；
Count(*)
Count(*)
臨時關閉mysql查詢緩存，為了檢視sql多次執行的真實時間。
四個sql的執行計劃一樣，說明這四個sql執行效率應該差不多，差別在于根據某個字段count不會統計字段為null值的資料行。
為什麼mysql最終選擇輔助索引而不是主鍵聚集索引?
因為二級索引相對主鍵索引存儲資料更少，檢索性能應該更高
常見的優化方法如下：
- 查詢MySQL自己維護的總行數
- show table status
- 将總數維護到Redis裡
- 增加計數表
查詢MySQL自己維護的總行數
對于myisam存儲引擎的表做不帶where條件的count查詢性能是很高的，因為myisam存儲引擎的表的總行數會被 mysql存儲在磁盤上，查詢不需要計算。
對于innodb存儲引擎的表mysql不會存儲表的總記錄行數，查詢count需要實時計算。
show table status
如果隻需要知道表總行數的估計值可以用如下sql查詢，性能很高
将總數維護到Redis裡
插入或删除表資料行的時候同時維護redis裡的表總行數key的計數值(用incr或decr指令)，但是這種方式可能不準，很難保證表操作和redis操作的事務一緻性。
增加計數表
插入或删除表資料行的時候同時維護計數表，讓他們在同一個事務裡操作。
部分圖檔來源于網絡，版權歸原作者，侵删。 ?點選閱讀原文，檢視往期内容！ 快留言?和我互動吧～