資料庫系列:MySQL索引優化總結(綜合版)

語言: CN / TW / HK

1 背景

作為一個常年在一線帶組的Owner以及老面試官,我們面試的目標基本都是一線的開發人員。從服務端這個技術棧出發,問題的範圍主要還是圍繞開發語言(Java、Go)等核心知識點、資料庫技術、快取技術、訊息中介軟體、微服務框架的使用等幾個方面來提問。

MySQL作為大廠的主流資料儲存配置,當然是被問的最多的,而其中重點區域就是索引的使用和優化。

2 索引的優化步驟

2.1 高效索引的原則

  1. 正確理解和計算索引欄位的區分度,下面是計算規則,區分度高的索引,可以快速得定位資料,區分度太低,無法有效的利用索引,可能需要掃描大量資料頁,和不使用索引沒什麼差別。我們建立索引的時候,儘量選擇區分度高的列作為索引。
selecttivity = count(distinct c_name)/count(*)
  1. 正確理解和計算字首索引的欄位長度,下面是判斷規則,合適的長度要保證高的區分度和最恰當的索引儲存容量,只有達到最佳狀態,才是保證高效率的索引。下買呢長度為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
  1. 聯合索引注意最左匹配原則:按照從左到右的順序匹配,MySQL會一直向右匹配索引直到遇到範圍查詢(>、<、between、like)然後停止匹配。如 depno=1 and empname>'' and job=1 ,如果建立(depno,empname,job)順序的索引,empname 和 job是用不到索引的。
  2. 應需而取策略,查詢記錄的時候,不要一上來就使用*,只取需要的資料,可能的話儘量只利用索引覆蓋,可以減少回表操作,提升效率。
  3. 正確判斷是否使用聯合索引(策略篇 聯合索引的使用那一小節有說明判斷規則),也可以進一步分析到索引下推(IPC),減少回表操作,提升效率。
  4. 避免索引失效的原則:禁止對索引欄位使用函式、運算子操作,會使索引失效。這是實際上就是需要保證索引所對應欄位的”乾淨度“。
  5. 避免非必要的型別轉換,字串欄位使用數值進行比較的時候會導致索引無效。
  6. 模糊查詢'%value%'會使索引無效,變為全表掃描,因為無法判斷掃描的區間,但是'value%'是可以有效利用索引。
  7. 索引覆蓋排序欄位,這樣可以減少排序步驟,提升查詢效率
  8. 儘量的擴充套件索引,非必要不新建索引。比如表中已經有a的索引,現在要加(a,b)的索引,那麼只需要修改原來的索引即可。
  9. 無需強制索引順序,比如 建立(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根據表統計資訊及索引選用情況,估算所需要掃描的行數

慢查詢優化基本步驟

  1. 先執行檢視實際耗時,判斷是否真的很慢(注意設定SQL_NO_CACHE)。
  2. 高區分度優先策略:where條件單表查,鎖定最小返回記錄表的條件。
    就是查詢語句的where都應用到表中返回的記錄數最小的表開始查起,單表每個欄位分別查詢,看哪個欄位的區分度最高。區分度高的欄位往前排。
  3. explain檢視執行計劃,是否與1預期一致(從鎖定記錄較少的表開始查詢)
  4. order by limit 形式的sql語句讓排序的表優先查
  5. 瞭解業務方的使用場景,根據使用場景適時調整。
  6. 加索引時參照建上面索引的十大原則
  7. 觀察結果,不符合預期繼續從第一步開始分析

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 完整的索引知識體系