在MySQL中，索引是用來提高數(shù)據(jù)庫查詢效率的重要工具。了解和掌握索引的使用規(guī)則對于優(yōu)化數(shù)據(jù)庫性能至關重要。下面是一些關于MySQL索引使用的基本規(guī)則和最佳實踐：

前言:

索引能提升查詢效率，但不是建了索引就一定會用上。MySQL 最終是否走索引，還要看查詢條件、返回字段、排序分組方式，以及優(yōu)化器對成本的判斷。

這篇主要整理 MySQL 索引使用規(guī)則：聯(lián)合索引怎么匹配、哪些寫法會導致索引失效、覆蓋索引為什么能減少回表，以及實際建索引時應該遵守哪些原則。

一、聯(lián)合索引的核心：最左前綴法則

聯(lián)合索引也叫復合索引，一個索引里包含多個列。例如：

create index idx_user_pro_age on tb_user(profession, age);

這個索引的順序是 profession、age。使用時要遵守最左前綴法則，也就是查詢條件要從索引最左邊的列開始，并且不能跳過中間列。

explain select * from tb_user
where profession = '軟件工程';
explain select * from tb_user
where profession = '軟件工程' and age = 25;
explain select * from tb_user
where age = 25;

前兩個查詢都能從 profession 開始匹配聯(lián)合索引。第三個查詢只使用 age，跳過了最左邊的 profession，通常不能按這個聯(lián)合索引進行高效查找。

MySQL 官方文檔中也有類似說明：如果有一個三列索引 col1、col2、col3，那么它可以用于 col1、col1 + col2、col1 + col2 + col3 這樣的左側連續(xù)組合；如果只查 col2 或 col3，就不符合左側前綴。

最左前綴不是“用了聯(lián)合索引”這么簡單，而是“從聯(lián)合索引最左列開始連續(xù)匹配”。

二、范圍查詢會影響右側列繼續(xù)匹配

聯(lián)合索引中，如果某一列使用了范圍查詢，右側列可能無法繼續(xù)作為高效定位條件。

例如有一個聯(lián)合索引：

create index idx_user_pro_age_status
on tb_user(profession, age, status);

查詢語句如下：

explain select * from tb_user
where profession = '軟件工程'
  and age > 25
  and status = '啟用';

profession 是等值查詢，可以先使用。age 是范圍查詢，MySQL 會在 age 范圍內(nèi)查找數(shù)據(jù)。status 雖然也寫在查詢條件中，但它在范圍條件右側，可能不能繼續(xù)參與完整的索引定位。

有些資料會提到把大于、小于改成大于等于、小于等于，可能在部分場景中讓執(zhí)行計劃表現(xiàn)不同。但這個不能當成固定優(yōu)化公式。大于等于、小于等于本質(zhì)上也屬于范圍條件，最終還是要看 EXPLAIN 的結果。

范圍條件后面的列，不一定還能繼續(xù)作為高效定位條件。

三、常見索引失效情況

索引失效并不代表索引被刪除了，而是這條 SQL 沒有辦法按預期利用索引。常見情況有下面幾類。

1. 在索引列上進行運算或函數(shù)處理

explain select * from tb_user
where substring(phone, 10, 2) = '15';

phone 字段如果有索引，這里也很難直接利用。因為 MySQL 不是拿原始 phone 值去匹配，而是先對 phone 做 substring 處理，再比較結果。

寫 SQL 時要盡量讓索引列保持原樣，不要在索引列外面套函數(shù)、運算表達式。

2. 字符串類型字段不加引號

explain select * from tb_user
where phone = 17799990015;

如果 phone 是字符串類型，這里沒有加引號，可能觸發(fā)隱式類型轉(zhuǎn)換。類型轉(zhuǎn)換一旦發(fā)生，就可能讓索引無法按原本的字符串規(guī)則使用。

正確寫法應該是：

explain select * from tb_user
where phone = '17799990015';

3. 頭部模糊查詢

explain select * from tb_user
where profession like '%工程';
explain select * from tb_user
where profession like '%工程%';

這兩種寫法都在前面加了百分號，MySQL 無法從索引的起點開始匹配，索引效果會明顯變差。

如果只是尾部模糊匹配，通常更容易利用索引：

explain select * from tb_user
where profession like '軟件%';

4. or 條件中有一側沒有索引

explain select * from tb_user
where phone = '17799990015'
   or address = '北京';

如果 phone 有索引，但是 address 沒有索引，優(yōu)化器可能認為走索引意義不大，最后選擇全表掃描。

or 查詢不是一定不能用索引，關鍵要看 or 兩側字段是否都有合適索引，以及優(yōu)化器評估后的成本。

5. 優(yōu)化器認為全表掃描更快

有時候 SQL 寫法沒有明顯問題，但 MySQL 仍然不走索引。原因可能是數(shù)據(jù)量太小、條件區(qū)分度太低，或者優(yōu)化器認為全表掃描比走索引再回表更快。

所以判斷索引是否生效，不能只看“建沒建索引”，還要看執(zhí)行計劃里的 key、rows、Extra 等信息。

四、SQL 提示：use、ignore、force index

當一個字段既有單列索引，又在聯(lián)合索引里出現(xiàn)時，MySQL 會根據(jù)優(yōu)化器成本選擇索引。如果想影響優(yōu)化器選擇，可以使用索引提示。

explain select * from tb_user use index(idx_user_pro)
where profession = '軟件工程';
explain select * from tb_user ignore index(idx_user_pro)
where profession = '軟件工程';
explain select * from tb_user force index(idx_user_pro)
where profession = '軟件工程';

use index 是建議使用某個索引，但優(yōu)化器仍然可能選擇別的方案。

ignore index 是告訴優(yōu)化器不要考慮某個索引。

force index 是更強的提示，表示強制優(yōu)先使用指定索引。

不過，強制使用索引不等于一定更快。如果數(shù)據(jù)區(qū)分度很低，或者回表成本很高，強行走索引反而可能變慢。實際使用時要結合執(zhí)行計劃和查詢耗時一起判斷。

五、覆蓋索引：為什么盡量少寫 select *

覆蓋索引指的是：查詢使用了索引，并且需要返回的字段都能從索引中拿到，不需要再回到表里查詢完整行數(shù)據(jù)。

例如表中有 id、username、password、status 四個字段，現(xiàn)在要優(yōu)化這條 SQL：

select id, username, password
from tb_user
where username = 'itcast';

可以考慮建立聯(lián)合索引：

create index idx_user_name_pwd
on tb_user(username, password);

如果 id 是主鍵，在 InnoDB 的二級索引中會保存主鍵值。這樣查詢 username、password、id 時，就可能直接從索引中拿到結果，不需要回表查詢 status 等其他字段。

這也是為什么不建議隨手寫 select *。因為返回字段越多，越容易超出索引本身能提供的范圍，最后就需要回表。

執(zhí)行計劃 Extra 字段里常見兩個信息：

Using index condition 表示使用了索引條件下推，但仍可能需要讀取完整行。

Using where; Using index 通常表示查詢需要的字段可以從索引中拿到，不需要回表。

覆蓋索引的價值，就是少一次回表。

六、前綴索引：長字符串字段怎么建索引

如果字段是 varchar、text 這類字符串類型，而且內(nèi)容比較長，直接給整列建索引會占用更多空間，也會增加磁盤 IO。

這時可以使用前綴索引，只取字段前 N 個字符建立索引。

create index idx_email on tb_user(email(5));

前綴長度不是隨便寫的，要看區(qū)分度?？梢韵扔嬎阃暾侄蔚倪x擇性：

select count(distinct email) / count(*) from tb_user;

再計算不同前綴長度的選擇性：

select count(distinct substring(email, 1, 5)) / count(*) from tb_user;
select count(distinct substring(email, 1, 8)) / count(*) from tb_user;
select count(distinct substring(email, 1, 10)) / count(*) from tb_user;

選擇性越接近完整字段，說明這個前綴長度越能區(qū)分數(shù)據(jù)。前綴太短，重復值多，過濾效果差；前綴太長，索引空間節(jié)省不明顯。

創(chuàng)建后可以通過 show index 查看 sub_part，確認前綴索引截取的長度。

七、不完全滿足最左前綴時，為什么有時看起來還走了索引

最左前綴法則是聯(lián)合索引用于高效查找的基本規(guī)則，但實際執(zhí)行計劃里，有時即使 SQL 沒有完全滿足最左前綴，也可能看到 MySQL 使用了某個索引。

這不代表最左前綴法則失效了，而是優(yōu)化器可能在其他角度利用索引。

第一種情況是覆蓋索引。

如果查詢字段都在聯(lián)合索引中，即使 where 條件沒有從最左列開始，MySQL 也可能掃描整個索引來返回數(shù)據(jù)。因為掃描索引比掃描整張表更輕。

create index idx_abc on tb_demo(a, b, c);
select b, c
from tb_demo
where b = 10;

這里 where 條件沒有使用 a，不滿足最左前綴。但如果只返回 b、c，優(yōu)化器可能選擇掃描 idx_abc，因為索引本身已經(jīng)包含需要的字段。

第二種情況是索引下推。

MySQL 5.6 之后支持 ICP。它可以把一部分索引列上的過濾條件下推到存儲引擎層，先在索引層過濾一批數(shù)據(jù)，減少回表次數(shù)。

select *
from tb_demo
where a = 1 and c = 3;

如果索引是 a、b、c，a 可以按最左前綴使用，c 雖然跳過了 b，但仍可能通過索引下推參與過濾。開啟狀態(tài)可以關注 optimizer_switch=index_condition_pushdown=on。

第三種情況是排序或分組。

如果 order by、group by 的字段順序和聯(lián)合索引順序匹配，優(yōu)化器可能利用索引順序減少額外排序。不過這類優(yōu)化對字段順序、排序方向、where 條件都有要求，不能只看“字段在索引里”就認為一定能避免排序。

所以看到執(zhí)行計劃里使用了索引時，還要繼續(xù)看 type、key_len、rows 和 Extra。它可能是高效定位，也可能只是全索引掃描。

八、單列索引和聯(lián)合索引怎么選

單列索引是一個索引只包含一個字段。聯(lián)合索引是一個索引包含多個字段。

如果業(yè)務里經(jīng)常按多個條件組合查詢，通常優(yōu)先考慮聯(lián)合索引，而不是給每個字段都單獨建一個索引。

例如經(jīng)常按職業(yè)、年齡、狀態(tài)查詢：

select *
from tb_user
where profession = '軟件工程'
  and age = 25
  and status = '啟用';

比起分別給 profession、age、status 建三個單列索引，更常見的做法是根據(jù)查詢頻率和區(qū)分度建立一個聯(lián)合索引：

create index idx_user_pro_age_status
on tb_user(profession, age, status);

聯(lián)合索引的好處是可以同時服務多條件過濾，并且在返回字段合適時形成覆蓋索引，減少回表。

但聯(lián)合索引也不是越長越好。索引列越多，維護成本越高，插入、更新、刪除數(shù)據(jù)時都要維護對應索引結構。

九、索引設計原則

索引設計可以按下面幾條來判斷：

1. 數(shù)據(jù)量較大，并且查詢比較頻繁的表，才更有必要建立索引。
2. 經(jīng)常出現(xiàn)在 where、order by、group by 后面的字段，優(yōu)先考慮索引。
3. 盡量選擇區(qū)分度高的列，例如手機號、用戶名這類重復率低的字段。
4. 字符串字段較長時，可以考慮前綴索引。
5. 多條件查詢優(yōu)先考慮聯(lián)合索引，減少多個單列索引堆疊。
6. 控制索引數(shù)量，索引會提升查詢，但也會降低增刪改效率。
7. 如果索引列業(yè)務上不允許為空，建表時可以聲明 NOT NULL。

索引設計不是“給字段都建上”，而是圍繞查詢場景選擇最少、最有效的索引。

十、實際排查時怎么判斷索引用得好不好

平時排查 SQL 時，可以先看四個點。

第一，看 possible_keys 和 key。

possible_keys 表示可能用到的索引，key 表示最終實際選擇的索引。

第二，看 key_len。

它可以幫助判斷聯(lián)合索引大概使用到了哪些列。尤其是聯(lián)合索引中出現(xiàn)范圍查詢、跳過字段時，key_len 很有參考價值。

第三，看 rows。

rows 越大，說明 MySQL 預計要掃描的數(shù)據(jù)越多。即使走了索引，如果 rows 很大，查詢也不一定快。

第四，看 Extra。

Extra 里如果出現(xiàn)覆蓋索引、索引條件下推、臨時表、文件排序等信息，都能幫助判斷這條 SQL 還有沒有優(yōu)化空間。

總結

MySQL 索引使用規(guī)則可以壓縮成一句話：

先看查詢條件是否滿足最左前綴，再看有沒有函數(shù)、隱式轉(zhuǎn)換、頭部模糊、or 條件這些失效寫法，最后結合返回字段判斷能不能形成覆蓋索引。

建索引時不要只盯著某一個字段，而要把 where 條件、返回字段、排序分組和字段區(qū)分度放在一起看。真正好用的索引，通常不是數(shù)量最多的索引，而是剛好匹配高頻查詢場景、維護成本又可控的索引。

參考資料：

• MySQL 8.0 Reference Manual: Multiple-Column Indexes
• MySQL 8.0 Reference Manual: Index Hints
• MySQL 8.0 Reference Manual: Index Condition Pushdown Optimization

到此這篇關于MySQL 索引使用規(guī)則和最佳實踐的文章就介紹到這了,更多相關mysql索引使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論