Stories

Detail Return Return

MySQL索引優化(萬字詳解) - Stories Detail

前言

索引優化這四個字説實話我認為其實挺難理解的。看到這四個字我腦門上是：？？？？

索引還要優化嗎？調優SQL一般來説不就是看它有沒有走索引，沒走索引給它加上索引就好了嗎？

嗯，所以你是怎麼給它加索引的？

看SQL應該怎麼走索引撒！

那SQL是怎麼走索引的呢？又是怎麼判斷這條SQL會不會走索引呢？

我：…, 咱今天就來分析分析！

要是你還不瞭解MySQL底層的數據結構，建議你先看看MySQL數據結構

最左前綴法則

我們一般要優化的都是複雜SQL,而複雜SQL一般走的都是聯合索引，説到聯合索引的匹配規則，就逃不開這個：最左前綴法則

什麼是最左前綴法則？

最左前綴法則即為：索引的匹配從最左邊的字段開始，匹配成功才能往右繼續匹配下一個字段。

不理解？沒關係，我們先來看看這個聯合索引：name_age_position

image-20230120150557493

聯合索引是以三個字段name,age,position組成，並且創建該索引時字段順序為name、age、positon。

那麼該索引就會以這樣的方式排序（索引就是排好序的高效的數據結構）

  • name字段從小到大排序
  • name字段的值相同時，age字段從小到大排序
  • age字段的值相同時，postion字段從小到大排序

如上圖所示，從zhangsan18到zhangsan100是順序的，而name都為zhangsan18的三個結點中，age又是從小到大排序，age相同時position也是從小到大排序。

請你一定要把這個數據結構牢記於心，忘了就看看

現在通過這個聯合索引再來解析一下最左前綴法則：在索引匹配時，必須先能夠匹配name字段(最左邊的)，才能繼續匹配age字段(下一個), age字段匹配成功了才能匹配position字段。

為什麼？

因為聯合索引中的最左邊字段是有序的，而第二個字段是在第一個字段相同的情況下有序，第三個字段是在第二個字段相同的情況下有序。

如果你想要用age字段直接在聯合索引中查找數據，對不起，找不到，因為age字段中聯合索引中是無序的。

你把第一行name字段遮掉看看age字段的情況：18,18,20,15,25,16,33。無序的對吧。

還是有點迷惑？沒關係，我們再來通過案例分析分析。

什麼是走索引？就是看索引會不會起到作用，能夠起到作用就叫走了索引，沒有起到作用就叫沒走索引。

案例分析

表結構：

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時\r\n間',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';

sql1

explain select * from employees where name > 'zhangsan18'

name字段是聯合索引最左邊的字段，所以會走索引

image-20230120145946516

sql2

explain select * from employees where age = 18

age字段並非聯合索引最左邊的字段，在索引中無序，故不走索引，全表掃描

image-20230120150113853

sql3

explain select * from employees where name = 'zhangsan18' and age = 20;

name字段和age字段都會走索引，因為在name字段相同時，age字段是有序的, 所以此時age也可以走索引。

image-20230120150855590

以上圖為例，當定位到zhangsan18時，可以直接定位到age=20這條數據,不需要從age=18的地方遍歷尋找，所以索引對age字段也起到作用了。

image-20230120151229081

你現在明白什麼是最左前綴法則了吧，還不明白就私信我吧[嘆氣.jpg]。

SQL案例

現在，我們再來通過一些sql繼續深挖這最左前綴法則。

sql4

explain select * from employees where age = 20 and name = 'zhangsan18';

和sql3相同，name和age都會走索引，最左前綴和你sql語句的位置無關，mysql在執行時會自動調整位置，也就是改成name = 'zhangsan18' and age = 20

sql5

explain select * from employees where name > 'zhangsan18' and age = 20;

只有name字段會走索引，age不會走索引，因為此時mysql的查詢邏輯是定位到name=zhangsan18最右邊的一條數據，然後通過葉子結點的指針向右掃描遍歷，索引對age字段未起到作用。如圖

image-20230120171406509

explain結果：

image-20230120171842732

sql6

explain select * from employees where name >= 'zhangsan18' and age = 20;

和sql5差不多，唯一的區別就是name是大於等於。此時name和age都會走索引。

image-20230120172053144

現在，我估計你一定暈了，網上不是説範圍查找會導致索引失效嗎？怎麼還走了age字段。

這樣，我把sql這樣寫：

explain select * from employees where (name = 'zhangsan18' and age = 20) or (name > 'zhangsan18' and age = 20);

name = 'zhangsan18' and age = 20部分：name和age都會走索引，這個沒問題吧？

name > 'zhangsan18' and age = 20部分：name走索引，age不走索引，這個也沒問題吧？

合起來就是name和age都會走索引，因為name = 'zhangsan18' and age = 20時age要走索引。

還是迷惑？那梳理下流程。

mysql執行時先定位到name=zhangsan18, 然後由於後面還有個age=20條件，所以會直接定位到這裏

image-20230120172715433

然後再往右掃描name>zhangsan18的記錄, 你告訴我這個過程有沒有用上age字段的索引？用上了吧，所以age字段也會走索引，也僅僅是這個時候會走索引，後面name>zhangsan18的還是不走索引。

sql7

explain select * from employees where name like 'zhangsan18%' and age = 10

name和age都會走索引，和sql6一樣理解就好。

image-20230120172053144

sql8

explain select * from employees where name between 'zhangsan18' and 'zhangsan50' and age = 10

name和age都會走索引

image-20230120172053144

到這裏，你對最左前綴法則應該會有個深刻的認識了，更多的想法，就由你自己去探索啦

索引下推

MySQL在5.6之後加了一個優化：索引下推，可以在索引遍歷過程中，對索引中包含的所有字段先做判斷，過濾掉不符合條件的記錄之後再回表，可以有效的減少回表次數

拿這條sql舉例：

explain select * from employees where name > 'zhangsan18' and age = 20;

這條sqlname字段走索引，age不走索引，在沒有索引下推時，查詢邏輯是這樣的：

1、存儲引擎通過聯合索引找到name > 'zhangsan18'的記錄

2、然後使用聯合索引存儲的主鍵進行回表操作，查詢出所有數據

3、將數據返回給Server層

4、Server層判斷這條記錄的age是否為20, 是則返回給客户端，否則丟棄

這裏就有個優化點，在第一步用聯合索引找到name > 'zhangsan18'的記錄時，能不能直接判斷age是否為20？如果是再進行後面的步驟。

哎，你覺得能不能？

能！age字段本來就在聯合索引裏面，直接判斷就完事了~

所以，這就是索引下推。

OrderBy

order by是怎麼使用索引的？

order by同樣遵循最左前綴法則，只有當order by的字段是最左字段或者跟隨where條件的字段時，才能使用索引排序

排序排序，關鍵就在於：有序

如以下聯合索引：name_age_position

image-20230120150557493

name字段是天然有序的，name值相同時，age是有序的，age相同時，position是有序的。

那應該怎麼判斷sql使用了索引排序呢？

如以下sql

explain select id from employees order by name;

image-20230120221351505

Extra列：

Using index：使用覆蓋索引

Using filesort：將用文件排序而不是索引排序，數據較小時從內存排序，否則需要在磁盤完成排序。

只要沒有Extra列出現use filesort，那麼就是用的索引排序

再看看使用文件排序的sql

explain select id from employees order by age;

image-20230120221742350

注意，使用了索引是使用了索引，文件排序是文件排序，這是兩碼事。

比如你使用了索引進行查找數據，但是查找出的數據是用的文件排序。

接下來看看一些案例

sql1

explain select * from employees where name = 'zhangsan18' order by age,position;

索引排序，age跟在name字段後,position跟在age字段後

image-20230121214814792

sql2

explain select * from employees where name = 'zhangsan18' order by position,age;

文件排序，因為該sql是先使用position字段排序，再使用age字段排序，而position字段在name相同時依舊是無序的。

image-20230121215028356

sql3

explain select * from employees where name = 'zhangsan18' and age = 18 order by position,age;

索引排序，position跟在age後，是有序的，而orderby後的age其實會被優化成常量，因為是通過age=10查詢出的數據

image-20230121215301638

sql4

explain select * from employees where name = 'zhangsan18' order by age asc,position desc;

文件排序，雖然age字段可以用索引排序，但是position字段逆序排序。

image-20230121220740347

可能會不太好理解，這裏結合圖説明一下

image-20230121221138310

索引是先通過age字段排序，然後對age字段相同的記錄，進行position逆序排序，最終查詢出的結果是這樣的

image-20230121221406010

所以position字段需使用文件排序。

sql5

select * from employees where name = 'zhangsan18' order by age desc,position desc;

索引排序，因為age，position字段都是逆序的，相當於是索引上從右往左遍歷

image-20230121221643560

image-20230121221709624

sql6

explain select * from employees where name > 'zhangsan18' order by age,position;

文件排序，因為name走範圍查詢，age字段走不了索引了。同上篇索引優化一中sql5的分析

image-20230121222001356

sql7

explain select * from employees where name >= 'zhangsan18' order by age,position;

依舊是文件排序，如果你看了上文，你可能又會有疑惑了：age字段不是會走索引嗎？咋是文件排序勒？

這裏再強調一遍：走索引是走索引，排序是排序。

沒錯，在name=zhangsan18時，age,position是有序的，可以使用索引排序。

但是在name>zhangsan18時，age,position是無序的，需要使用文件排序。

image-20230121222646265

15,25,16,33：無序的對吧

好了，關於排序的案例就到這裏，更多的案例就還是由你自己去探索吧

什麼是文件排序？

文件排序分為單路排序和雙路排序

單路排序

一次性取出滿足條件行的所有字段，然後在sort buffer中進行排序

雙路排序

首先根據相應的條件取出相應的排序字段和可以直接定位行數據的行id(主鍵)，然後在sort buffer中進行排序，排序完後需要再次取回其它需要的字段。

MySQL是選擇用哪種排序的？

MySQL通過比較系統變量max_length_for_sort_data(默認1024字節)的大小和需要查詢的字段總大小來判斷使用哪種排序模式。

  • 如果字段的總長度小於max_length_for_sort_data ，那麼使用單路排序模式
  • 如果字段的總長度大於max_length_for_sort_data ，那麼使用雙路排序模式。

小結

1、如果可以使用索引排序，儘量使用索引排序，但是實在沒有辦法進行索引排序也不要勉強，優先對where篩選語句做索引優化，因為篩選出的數據往往是很少的，排序成本很低。

2、如果沒有辦法使用文件排序，服務器內存又充足的情況下，那麼可以適當調整下max_length_for_sort_data,讓MySQL使用單路排序，這樣可以減少回表，效率會好一些。

分頁查詢

在平常，我們寫的分頁查詢sql一般是這樣

explain select * from employees order by name limit 10000,10;

這樣的sql你會發現越翻到後面查詢會越慢，這是因為這裏看似是從表中查詢10條記錄，實際上是在表中查詢了10010條記錄，然後將10000條記錄丟棄所得到的結果。

優化sql如下：

explain select * from employees t1 join (select id from employees order by `name` limit 10000, 10) t2 on t1.id = t2.id;

執行計劃：

image-20230123092745913

優化思路：先使用覆蓋索引方式查出10條數據，再使用這10條數據連接查詢。

覆蓋索引：查詢的字段被索引完全覆蓋，比如id在聯合索引中

原理：結合MySQL數據結構, 主鍵索引(innodb引擎)會存儲完整的記錄，而二級索引只存儲主鍵。MySQL一個結點默認為16KB。

故：二級索引一個葉子結點能夠存放的記錄會多的多，掃描二級索引比掃描主鍵索引的IO次數會少很多。

圖示：

compare-index

優化前sql查詢時間

image-20230123092607529

set global query_cache_size=0;
set global query_cache_type=0;

優化後：

image-20230123093007842

Join查詢

jion查詢分為內連接，左連接，右連接；

關聯時又有兩種情況：使用索引字段關聯，不使用索引字段關聯。

我以案例舉例説明，如以下兩張表t1,t2, a字段有索引，b字段無索引

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
t2表結構與t1完全相同

其中t1表具有1w條數據，t2表具有100條數據。

使用索引字段關聯查詢

explain select * from t1 inner join t2 on t1.a = t2.a;

執行計劃：

image-20230122111216120

分析執行計劃：

1、先全表掃描t2表(100條數據)

2、使用t2表的a字段關聯查詢t1表，使用索引idx_a

3、取出t1表中滿足條件的行，和t2表的數據合併，返回結果給客户端

成本計算：

1、掃描t2表：100次

2、掃描t1表：100次，因為使用索引可以定位出的數據，這個過程的時間複雜度大概是O(1)

此處説的100次只是為了更好的計算和理解,實際可能就幾次

翻譯成代碼可能是這樣：

for x in range(100): # 循環100次
  print(x in t1) # 一次定位

所以總計掃描次數：100+100=200次

這裏引出兩個概念

小表驅動大表, 小表為驅動表，大表為被驅動表

  • inner join時,優化器一般會優先選擇小表做驅動表, 排在前面的表並不一定就是驅動表。
  • left join時，左表是驅動表，右表是被驅動表
  • right join時，右表時驅動表，左表是被驅動表

嵌套循環連接 Nested-Loop Join(NLJ) 算法

一次一行循環地從第一張表（驅動表）中讀取行，在這行數據中取到關聯字段，根據關聯字段在另一張表（被驅動表）裏取出滿足條件的行，然後取出兩張表的結果合集。

使用索引字段關聯查詢的一般為NLJ算法

使用非索引字段查詢

explain select * from t1 inner join t2 on t1.b = t2.b;

執行計劃：

image-20230123094246851

Extra列：Using join buffer：使用join buffer（BNL算法）

分析執行計劃：

1、先全表掃描t2表(100條數據)，將數據加載到join buffer(內存)中

2、全表掃描t1表，逐一和join buffer中的數據比對

3、返回滿足條件的行

成本計算：

1、掃描t2表：100次

2、掃描t1表：1w次

3、在內存中比對次數：100*10000=100w次

翻譯成代碼可能是這樣：

for i in range(100): # 循環100次
  for j in range(10000) # 循環10000次

所以總計掃描次數為：100+10000=10100次，內存中數據比對次數為：100*1w=100w次

這個過程稱為：基於塊的嵌套循環連接Block Nested-Loop Join(BNL)算法

把驅動表的數據讀入到join buffer中，然後掃描被驅動表，把被驅動表每一行取出來跟join buffer中的數據做對比。

使用BNL算法join buffer不夠時怎麼辦？

案例中t2表只有一百行數據，如果數據量很大時，比如t2表一共有1000行數據，join buffer一次只能放800行時怎麼辦？

此時會使用分段放的策略：先放入800行到join buffer，然後掃描t1表，比對完畢之後，將join buffer清空，放入剩餘的200行，再次掃描t1表，再比對一次。

也就是説：此時會多掃描一次t1表，如果2次都放不下，就再多掃描一次，以此類推。

小結

join查詢中一般有兩種算法：

  • 嵌套循環連接(NLJ)算法：使用索引字段關聯查詢
  • 基於塊的嵌套循環連接(BNL)算法：使用非索引字段關聯查詢
NLJ算法比BNL算法性能更高

關聯查詢的優化方式：

  • 對關聯字段加索引：讓MySQL儘量選擇NLJ算法
  • 小表驅動大表：一般來説MySQL優化器會自己判斷哪個是小表，如果使用left join和right join是要注意。
  • 如果不得已要使用BNL算法，那麼在內存充足的情況下，可以調大一些join buffer，避免多次掃描被驅動表。

為什麼非索引字段不使用NLJ算法？

NLJ算法性能這麼好，為什麼非索引字段關聯時不使用這種算法呢？

這是因為NLJ算法採用的是磁盤掃描方式：先掃驅動表，取出一行數據，通過該數據的關聯字段到被驅動表中查找，這個過程是使用索引查找的，非常快。

如果非索引字段採用這種方式，那麼通過驅動表的數據的關聯字段，到被驅動表中查找時，由於無法使用索引，此時走的是全表掃描。

比如驅動表有100條數據，那麼就要全表掃描被驅動表100次，被驅動表有1w條數據，那麼就是磁盤IO:100*1w=100w次，這個過程是非常慢的。

In&Exist

in和exist的優化只有一個原則：小表驅動大表

in：當B表的數據集小於A表的數據集時，in優於exists

select * from A where id in (select id from B)

即in中的表為小表

exist: 當A表的數據集小於B表的數據集時，exists優於in

select * from A where exists (select 1 from B where B.id = A.id)

即外層的表為小表

count查詢

關於count這裏就不詳細説明了，因為各種用法效率都差不多。

字段有索引：count(*)≈count(1)>count(字段)≈count(主鍵 id)

字段無索引：count(*)≈count(1)>count(主鍵 id)>count(字段)

索引設計原則

關於索引部分到這裏就差不多了，總結一下索引設計原則

  1. 先寫代碼，再根據情況建索引

    一般來説，都是都沒代碼寫完之後，才能明確哪些字段會用到索引，但我也發現大部人寫完代碼就不管了。所以如果在設計時可以初步知道哪些字段可以建立索引，那麼可以在設計表時就建好索引，寫完代碼再做調整

  2. 儘量讓聯合索引覆蓋大部分業務

    一個表不要建立太多的索引，因為MySQL維護索引也是需要耗費性能的，所以儘量讓一到三個聯合索引就覆蓋業務裏面的sql查詢條件

  3. 不要在小基數的字段上建索引

    如果在小基數的字段上建立索引是沒有意義的，如性別，一張1千萬數據的表，對半分的話500w男，500w女，篩選不出什麼。

  4. 字符串長度過長的索引可以取部分前綴建立索引

    字段過長的話也會導致索引佔用的磁盤空間比較大，如varcahr(255), 這個時候可以取部分前綴建立索引，如前20個字符。但要注意的是，這樣會導致排序失效，因為只取了前20個字符串，索引只能保證大範圍的有序。

    也可以在後期根據一定的計算規則計算最佳索引長度：distinct(left(字段，長度))/count約等於1

  5. 後期可以根據慢sql日誌繼續優化索引

    隨意業務的迭代，查詢條件也會發生改變，此時可以根據慢sql持續優化索引

  6. 可以建唯一索引，儘量建唯一索引

  7. where條件和order by衝突時時，優先取where的條件建索引

    因為篩選出數據後，一般數據量比較少，排序的成本不大，所以優先讓數據更快的篩選出來。

如果我的文章對你有所幫助，還請幫忙點贊、收藏、轉發一下，你的支持就是我更新的動力，非常感謝！

追更，想要了解更多精彩內容，歡迎關注公眾號：程序員阿紫

個人博客網站：https://zijiancode.cn

MySQL , mysql索引
Favorites

Add a new Comments

Some HTML is okay.