1 背景
作為一個常年在一線帶組的Owner以及老面試官，我們面試的目標基本都是一線的開發人員。從服務端這個技術棧出發，問題的範圍主要還是圍繞開發語言（Java、Go）等核心知識點、資料庫技術、快取技術、訊息中介軟體、微服務框架的使用等幾個方面來提問。
MySQL作為大廠的主流資料儲存配置，當然是被問的最多的，而其中重點區域就是索引的使用和優化。
2 索引的優化步驟
2.1 高效索引的原則
- 正確理解和計算索引欄位的區分度，下面是計算規則，區分度高的索引，可以快速得定位資料，區分度太低，無法有效的利用索引，可能需要掃描大量資料頁，和不使用索引沒什麼差別。我們建立索引的時候，儘量選擇區分度高的列作為索引。
selecttivity = count(distinct c_name)/count(*)
- 正確理解和計算字首索引的欄位長度，下面是判斷規則，合適的長度要保證高的區分度和最恰當的索引儲存容量，只有達到最佳狀態，才是保證高效率的索引。下買呢長度為6的時候是最佳狀態。
select count(distinct left(c_name , calcul_len)) / count(*) from t_name;
mysql> SELECT
count(DISTINCT LEFT(empname, 3)) / count(*) AS sel3,
count(DISTINCT LEFT(empname, 4)) / count(*) AS sel4,
count(DISTINCT LEFT(empname, 5)) / count(*) AS sel5,
count(DISTINCT LEFT(empname, 6)) / count(*) AS sel6,
count(DISTINCT LEFT(empname, 7)) / count(*) AS sel7
FROM
emp;
+--------+--------+--------+--------+--------+
| sel3 | sel4 | sel5 | sel6 | sel7 |
+--------+--------+--------+--------+--------+
| 0.0012 | 0.0076 | 0.0400 | 0.1713 | 0.1713 |
+--------+--------+--------+--------+--------+
1 row in set
- 聯合索引注意最左匹配原則：按照從左到右的順序匹配，MySQL會一直向右匹配索引直到遇到範圍查詢(>、<、between、like)然後停止匹配。如 depno=1 and empname>'' and job=1 ，如果建立(depno,empname,job)順序的索引，empname 和 job是用不到索引的。
- 應需而取策略，查詢記錄的時候，不要一上來就使用*，只取需要的資料，可能的話儘量只利用索引覆蓋，可以減少回表操作，提升效率。
- 正確判斷是否使用聯合索引（ 策略篇 聯合索引的使用那一小節有說明判斷規則），也可以進一步分析到索引下推（IPC），減少回表操作，提升效率。
- 避免索引失效的原則：禁止對索引欄位使用函式、運算子操作，會使索引失效。這是實際上就是需要保證索引所對應欄位的”乾淨度“。
- 避免非必要的型別轉換，字串欄位使用數值進行比較的時候會導致索引無效。
- 模糊查詢'%value%'會使索引無效，變為全表掃描，因為無法判斷掃描的區間，但是'value%'是可以有效利用索引。
- 索引覆蓋排序欄位，這樣可以減少排序步驟，提升查詢效率
- 儘量的擴充套件索引，非必要不新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可。
- 無需強制索引順序，比如 建立(depno,empno,jobno)順序的索引，你可以是 empno = 1 and jobno = 2 and depno = 8。因為MySQL的查詢優化器會根據實際索引情況進行順序優化，所以這邊不強制順序一致性。但是同等條件下還是按照順序進行排列，比較清晰，並且節省查詢優化器的處理。
2.2 查詢優化分析器 - explain
explain命令大家應該很熟悉，具體用法和欄位含義可以參考官網explain-output，這裡需要強調rows是核心指標，絕大部分rows小的語句執行一定很快，因為掃描的內容基數小。
所以優化語句基本上都是在優化降低rows值。
2.2.1 Explain輸出的欄位
| Column | JSON Name | Meaning |
|---|---|---|
| id select_id | The SELECT | identifier |
| select_type | None | The SELECT type |
| table table_name | The table for the output row | |
| partitions | partitions | The matching partitions |
| type | access_type | The join type |
| possible_keys | possible_keys | The possible indexes to choose |
| key | key | The index actually chosen |
| key_len | key_length | The length of the chosen key |
| ref | ref | The columns compared to the index |
| rows | rows | Estimate of rows to be examined |
| filtered | filtered | Percentage of rows filtered by table condition |
| Extra | None | Additional information |
2.2.2 select_type 列舉
注意幾個核心關鍵引數：possible_keys、key、rows、select_type,對於優化指導很有意義。
- select_type：表示查詢中每個select子句的型別（Simple、Primary、Depend SubQuery）
- possible_keys :指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢使用
- key：key列顯示MySQL實際決定使用的鍵（索引），未走索引是null
- rows：表示MySQL根據表統計資訊及索引選用情況，估算所需要掃描的行數
慢查詢優化基本步驟
- 先執行檢視實際耗時，判斷是否真的很慢（注意設定SQL_NO_CACHE）。
- 高區分度優先策略：where條件單表查，鎖定最小返回記錄表的條件。
就是查詢語句的where都應用到表中返回的記錄數最小的表開始查起，單表每個欄位分別查詢，看哪個欄位的區分度最高。區分度高的欄位往前排。 - explain檢視執行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢）
- order by limit 形式的sql語句讓排序的表優先查
- 瞭解業務方的使用場景，根據使用場景適時調整。
- 加索引時參照建上面索引的十大原則
- 觀察結果，不符合預期繼續從第一步開始分析
2.3 查詢案例分析
下面幾個例子詳細解釋瞭如何分析和優化慢查詢。
複雜查詢條件的分析
一般來說我們編寫SQL的方式是為了 是實現功能，在實現功能的基礎上保證MySQL的執行效率也是非常重要的，這要求我們對MySQL的執行計劃和索引規則有非常清晰的理解，分析下面的案例：
1 mysql> select a.*,b.depname,b.memo from emp a left join
2 dep b on a.depno = b.depno where sal>100 and a.empname like 'ab%' and a.depno=106 order by a.hiredate desc ;
3 +---------+---------+---------+---------+-----+---------------------+------+------+-------+------------+----------+
4 | id | empno | empname | job | mgr | hiredate | sal | comn | depno | depname | memo |
5 +---------+---------+---------+---------+-----+---------------------+------+------+-------+------------+----------+
6 | 4976754 | 4976754 | ABijwE | SALEMAN | 1 | 2021-01-23 16:46:24 | 2000 | 400 | 106 | kDpNWugzcQ | TYlrVEkm |
7 ......
8 +---------+---------+---------+---------+-----+---------------------+------+------+-------+------------+----------+
9 744 rows in set (4.958 sec)
總共就查詢了744條資料，卻耗費了4.958的時間，我們看一下目前表中現存的索引以及索引使用的情況分析
1 mysql> show index from emp;
2 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
4 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 | emp | 0 | PRIMARY | 1 | id | A | 4952492 | NULL | NULL | | BTREE | | |
6 | emp | 1 | idx_emo_depno | 1 | depno | A | 18 | NULL | NULL | | BTREE | | |
7 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 2 rows in set
9
10 mysql> explain select a.*,b.depname,b.memo from emp a left join
11 dep b on a.depno = b.depno where sal>100 and a.empname like 'ab%' and a.depno=106 order by a.hiredate desc ;
12 +----+-------------+-------+------+---------------+---------------+---------+-------+--------+-----------------------------+
13 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
14 +----+-------------+-------+------+---------------+---------------+---------+-------+--------+-----------------------------+
15 | 1 | SIMPLE | a | ref | idx_emo_depno | idx_emo_depno | 3 | const | 974898 | Using where; Using filesort |
16 | 1 | SIMPLE | b | ref | idx_dep_depno | idx_dep_depno | 3 | const | 1 | NULL |
17 +----+-------------+-------+------+---------------+---------------+---------+-------+--------+-----------------------------+
18 2 rows in set
可以看出，目前在emp表上除了主鍵只存在一個索引 idx_emo_depno ，作用在部門編號欄位上，該索引的目標是過濾出具體部門編號下的資料。
通過explain 分析器可以看到 where條件後面是走了 idx_emo_depno 索引，但是也比較了 97W的資料，說明該欄位的區分度並不高，根據高區分度優先原則，我們對這個表的三個查詢欄位分別進行區分度計算。
1 mysql> select count(distinct empname)/count(*),count(distinct depno)/count(*),count(distinct sal)/count(*) from emp;
2 +----------------------------------+--------------------------------+------------------------------+
3 | count(distinct empname)/count(*) | count(distinct depno)/count(*) | count(distinct sal)/count(*) |
4 +----------------------------------+--------------------------------+------------------------------+
5 | 0.1713 | 0.0000 | 0.0000 |
6 +----------------------------------+--------------------------------+------------------------------+
7 1 row in set
這是計算結果，empname的區分度最高，所以合理上是可以建立一個包含這三個欄位的聯合索引，順序如下：empname、depno、sal；
並且查詢條件重新調整了順序，符合最左匹配原則；另一方面根據應需而取的策略，把b.memo欄位去掉了。
1 mysql> select a.*,b.depname from emp a left join
2 dep b on a.depno = b.depno where a.empname like 'ab%' and a.depno=106 and a.sal>100 order by a.hiredate desc ;
3 +---------+---------+---------+---------+-----+---------------------+------+------+-------+------------+
4 | id | empno | empname | job | mgr | hiredate | sal | comn | depno | depname |
5 +---------+---------+---------+---------+-----+---------------------+------+------+-------+------------+
6 | 4976754 | 4976754 | ABijwE | SALEMAN | 1 | 2021-01-23 16:46:24 | 2000 | 400 | 106 | kDpNWugzcQ |
7 ......
8 +---------+---------+---------+---------+-----+---------------------+------+------+-------+------------+
9 744 rows in set (0.006 sec)
這邊還有一個問題，那就是聯合索引根據最左匹配原則：必須按照從左到右的順序匹配，MySQL會一直向右匹配索引直到遇到範圍查詢(>、<、between、like)然後停止匹配。
所以語句中 執行到a.empname 欄位，因為使用了like，後面就不再走索引了。在這個場景中， 獨立的empname欄位上的索引和這個聯合索引效率是差不多的。
另外排序欄位hiredate也可以考慮到覆蓋到索引中，會相應的提高效率。
無效索引的分析
有一個需求，使用到了使用者表 userinfo 和消費明細表 salinvest ，目的想把2020年每個使用者在四個品類等級（A1、A2、A3、A4）上的消費額度進行統計，所以便下了如下的指令碼：
1 select (@rowNO := @rowNo+1) AS id,bdata.* from
2 (
3 select distinct a.usercode,a.username,
4 @A1:=IFNULL((select sum(c.ltimenum) from `salinvest` c where c.usercode=a.usercode AND c.gravalue='A1'
5 and c.logdate between '2020-01-01' and '2020-12-31'),0) as A1,
6 @A2:=IFNULL((select sum(c.ltimenum) from `salinvest` c where c.usercode=a.usercode AND c.gravalue='A2'
7 and c.logdate between '2020-01-01' and '2020-12-31'),0) as A2,
8 @A3:=IFNULL((select sum(c.ltimenum) from `salinvest` c where c.usercode=a.usercode AND c.gravalue='A3'
9 and c.logdate between '2020-01-01' and '2020-12-31'),0) as A3,
10 @A4:=IFNULL((select sum(c.ltimenum) from `salinvest` c where c.usercode=a.usercode AND c.gravalue='A4'
11 and c.logdate between '2020-01-01' and '2020-12-31'),0) as A4,
12 ,(@A1+@A2+@A3+@A4) as allnum
13 from userinfo a
14 inner JOIN `salinvest` b on a.usercode = b.usercode
15 where b.logdate between '2020-01-01' and '2020-12-31'
16 order by allnum desc
17 ) as bdata,(SELECT @rowNO:=0) b;
這個查詢看起來貌似沒什麼問題 ，雖然用到了複合查詢、子查詢，但是如果索引做的正確，也不會有什麼問題。那我們來看看索引，有一個聯合索引，符合我們最左匹配原則和高區分度優先原則：
1 mysql> show index from salinvest;
2 +------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
4 +------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 | lnuminvest | 0 | PRIMARY | 1 | autocode | A | 5 | NULL | NULL | | BTREE | | |
6 | lnuminvest | 1 | idx_salinvest_complex | 1 | usercode | A | 2 | NULL | NULL | YES | BTREE | | |
7 | lnuminvest | 1 | idx_salinvest_complex | 2 | gravalue | A | 2 | NULL | NULL | YES | BTREE | | |
8 | lnuminvest | 1 | idx_salinvest_complex | 3 | logdate | A | 2 | NULL | NULL | YES | BTREE | | |
9 +------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 4 rows in set
那我們來看看它的執行效率：
mysql> select (@rowNO := @rowNo+1) AS id,bdata.* from
(
select (@rowNO := @rowNo+1) AS id,bdata.* from
(
select distinct a.usercode,a.username,
@A1:=IFNULL((select sum(c.ltimenum) from `salinvest` c where c.usercode=a.usercode AND c.gravalue='A1'
and c.logdate between '2020-01-01' and '2020-12-31'),0) as A1,
@A2:=IFNULL((select sum(c.ltimenum) from `salinvest` c where c.usercode=a.usercode AND c.gravalue='A2'
and c.logdate between '2020-01-01' and '2020-12-31'),0) as A2,
@A3:=IFNULL((select sum(c.ltimenum) from `salinvest` c where c.usercode=a.usercode AND c.gravalue='A3'
and c.logdate between '2020-01-01' and '2020-12-31'),0) as A3,
@A4:=IFNULL((select sum(c.ltimenum) from `salinvest` c where c.usercode=a.usercode AND c.gravalue='A4'
and c.logdate between '2020-01-01' and '2020-12-31'),0) as A4,
,(@A1+@A2+@A3+@A4) as allnum
from userinfo a
inner JOIN `salinvest` b on a.usercode = b.usercode
where b.logdate between '2020-01-01' and '2020-12-31'
order by allnum desc
) as bdata,(SELECT @rowNO:=0) b;
+----+------------+---------+------+------+------+------+------+--------+
| id | usercode | username | A1 | A2 | A3 | A4 |allnum
+----+------------+---------+------+------+------+------+------+--------+
| 1 | 063105015 | brand | 789.00 | 1074.50 | 998.00 | 850.00 |
......
+----+------------+---------+------+------+------+------+------+--------+
6217 rows in set (12.745 sec)
我這邊省略了查詢結果，實際上結果輸出6000多條資料，在約50W的資料中進行統計與合併，輸出6000多條資料，花費了將近13秒，這明顯是不合理的。
我們來分析下是什麼原因：
1 mysql> explain select (@rowNO := @rowNo+1) AS id,bdata.* from
2 (
3 select distinct a.usercode,a.username,
4 @A1:=IFNULL((select sum(c.ltimenum) from `salinvest` c where c.usercode=a.usercode AND c.gravalue='A1'
5 and c.logdate between '2020-01-01' and '2020-12-31'),0) as A1,
6 @A2:=IFNULL((select sum(c.ltimenum) from `salinvest` c where c.usercode=a.usercode AND c.gravalue='A2'
7 and c.logdate between '2020-01-01' and '2020-12-31'),0) as A2,
8 @A3:=IFNULL((select sum(c.ltimenum) from `salinvest` c where c.usercode=a.usercode AND c.gravalue='A3'
9 and c.logdate between '2020-01-01' and '2020-12-31'),0) as A3,
10 @A4:=IFNULL((select sum(c.ltimenum) from `salinvest` c where c.usercode=a.usercode AND c.gravalue='A4'
11 and c.logdate between '2020-01-01' and '2020-12-31'),0) as A4,
12 ,(@A1+@A2+@A3+@A4) as allnum
13 from userinfo a
14 inner JOIN `salinvest` b on a.usercode = b.usercode
15 where b.logdate between '2020-01-01' and '2020-12-31'
16 order by allnum desc
17 ) as bdata,(SELECT @rowNO:=0) b;
18 +----+--------------------+------------+------------+--------+------------------------+------------------------+---------+-----------------------+------+----------+-----------------------------------------------------------+
19 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
20 +----+--------------------+------------+------------+--------+------------------------+------------------------+---------+-----------------------+------+----------+-----------------------------------------------------------+
21 | 1 | PRIMARY | <derived8> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100 | NULL |
22 | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100 | NULL |
23 | 8 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
24 | 2 | DERIVED | b | NULL | index | idx_salinvest_complex | idx_salinvest_complex | 170 | NULL | 5 | 20 | Using where; Using index; Using temporary; Using filesort |
25 | 7 | DEPENDENT SUBQUERY | c | NULL | ALL | idx_salinvest_complex | NULL | NULL | NULL | 5 | 20 | Using where |
26 | 6 | DEPENDENT SUBQUERY | c | NULL | ALL | idx_salinvest_complex | NULL | NULL | NULL | 5 | 20 | Using where |
27 | 5 | DEPENDENT SUBQUERY | c | NULL | ALL | idx_salinvest_complex | NULL | NULL | NULL | 5 | 20 | Using where |
28 | 4 | DEPENDENT SUBQUERY | c | NULL | ALL | idx_salinvest_complex | NULL | NULL | NULL | 5 | 20 | Using where |
29 +----+--------------------+------------+------------+--------+------------------------+------------------------+---------+-----------------------+------+----------+-----------------------------------------------------------+
30 9 rows in set
看最後四條資料，看他的possible_key和 實際的key，預估是走 idx_salinvest_complex 索引，實際是走了空索引，這個是為什麼呢？ 看前面的select_type 欄位，值是 DEPENDENT SUBQUERY，瞭然了。
官方對 DEPENDENT SUBQUERY 的說明：子查詢中的第一個SELECT， 取決於外面的查詢 。
什麼意思呢？它意味著兩步：
第一步，MySQL 根據 select distinct a.usercode,a.username 得到一個大結果集 t1，這就是我們上圖提示的6000使用者。
第二步，上面的大結果集 t1 中的每一條記錄，等同於與子查詢 SQL 組成新的查詢語句： select sum(c.ltimenum) from salinvest c where c.usercode in (select distinct a.usercode from userinfo a) 。
也就是說， 每個子查詢要比較6000次，幾十萬的資料啊……即使這兩步驟查詢都用到了索引，但還是會很慢。
這種情況下， 子查詢的執行效率受制於外層查詢的記錄數，還不如拆成兩個獨立查詢順序執行呢。
這種慢查詢的解決辦法，網上有很多方案，最常用的辦法是用聯合查詢代替子查詢，可以自己去查一下。
3 適當的分庫分表
物理服務機的CPU、記憶體、儲存裝置、連線數等資源有限，某個時段大量連線同時執行操作，會導致資料庫在處理上遇到效能瓶頸。為了解決這個問題，行業先驅門充分發揚了分而治之的思想，對大庫表進行分割，
然後實施更好的控制和管理，同時使用多臺機器的CPU、記憶體、儲存，提供更好的效能。而分治有兩種實現方式：垂直拆分和水平拆分。
3.1 垂直分庫
垂直分庫其實是一種簡單邏輯分割，比如資料庫中建立獨立的商品庫 Products、訂單庫Orders，積分庫Scores 等。
3.2 垂直分表
比較適用於那種欄位比較多的表，假設我們一張表有100個欄位，分析了一下當前業務執行的SQL語句，有20個欄位是經常使用的，而另外80個欄位使用比較少。把20個欄位放在主表裡面，我們再建立一個輔助表，存放另外80個欄位。
3.3 庫內分表
按照一定的策略對單個大容量表進行拆分。
3.4 分庫分表
分庫分表在庫內分表的基礎上，將分的表挪動到不同的主機和資料庫上。可以充分的使用其他主機的CPU、記憶體和IO資源。
4 完整的索引知識體系
參考筆者之前寫的索引四篇+分庫分表兩篇
MySQL全面瓦解22：索引的介紹和原理分析
MySQL全面瓦解23：MySQL索引實現和使用
MySQL全面瓦解24：構建高效能索引（策略篇）
MySQL全面瓦解25：構建高效能索引（案例分析篇）
MySQL全面瓦解28：分庫分表
MySQL全面瓦解29：分庫分表之Partition功能詳解