SQL 寫不好 加班少不了 日常工作中SQL 是必不可少的一項技術 但是很多人不會過多的去關註SQL問題 一是數據量小 二是沒有意識到索引的重要性 本文主要是整理 SQL失效場景 如果裏面的細節你都知道 那你一定是學習能力比較好的人 膜拜

寫完這篇文章 我感覺自己之前知道的真的是 「目錄」 沒有明白其中的內容 如果你能跟著節奏看完文章 一定會有收獲 至少我寫完感覺思維通透很多 以後百分之九十的 SQl索引問題 和 面試這方面問題都能拿

索引失效 整理

基礎數據準備

準備一個數據表作為 數據演示 這裏面一共 建立了三個索引

SETNAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROPTABLEIFEXISTS`student`;
CREATETABLE`student` (
`id`int(11) NOTNULL AUTO_INCREMENT,
`sname`varchar(20) CHARACTERSET utf8 COLLATE utf8_general_ci NOTNULL,
`s_code`int(100) NULLDEFAULTNULL,
`address`varchar(100) CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL,
`height`doubleNULLDEFAULTNULL,
` classid`int(11) NULLDEFAULTNULL,
`create_time` datetime(0) NOTNULLONUPDATECURRENT_TIMESTAMP(0),
PRIMARY KEY (`id`) USING BTREE,
INDEX`普通索引`(`height`) USING BTREE,
INDEX`聯合索引`(`sname`, `s_code`, `address`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5CHARACTERSET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERTINTO`student`VALUES (1, '學生1', 1, '上海', 170, 1, '2022-11-02 20:44:14');
INSERTINTO`student`VALUES (2, '學生2', 2, '北京', 180, 2, '2022-11-02 20:44:16');
INSERTINTO`student`VALUES (3, '變成派大星', 3, '京東', 185, 3, '2022-11-02 20:44:19');
INSERTINTO`student`VALUES (4, '學生4', 4, '聯通', 190, 4, '2022-11-02 20:44:25');


問題思考

上面的SQL 我們已經建立好基本的數據 在驗證之前 先帶著幾個問題

我們先從上往下進行驗證

最左匹配原則

寫在前面：我很早之前就聽說過資料庫的最左匹配原則，當時是透過各大部落格論壇了解的，但是這些部落格的局限性在於它們對最左匹配原則的描述就像一些數學定義一樣，往往都是列出123點，滿足這123點就能匹配上索引，否則就不能。

最左匹配原則就是指在聯合索引中，如果你的 SQL 語句中用到了聯合索引中的最左邊的索引，那麽這條 SQL 語句就可以利用這個聯合索引去進行匹配，我們上面建立了聯合索引 可以用來測試最左匹配原則 sname , s_code , address

請看下面SQL語句 進行思考 是否會走索引

-- 聯合索引 sname,s_code,address
1、select create_time from student where sname = "變成派大星"-- 會走索引嗎？
2、select create_time from student where s_code = 1-- 會走索引嗎？
3、select create_time from student where address = "上海"-- 會走索引嗎？
4、select create_time from student where address = "上海"and s_code = 1-- 會走索引嗎？
5、select create_time from student where address = "上海"and sname = "變成派大星"-- 會走索引嗎？
6、select create_time from student where sname = "變成派大星"and address = "上海"-- 會走索引嗎？
7、select create_time from student where sname = "變成派大星"and s_code = 1and address = "上海"-- 會走索引嗎？







憑你的經驗 哪些會使用到索引呢 ？可以先思考一下 在心中記下數位

走索引例子

EXPLAINselect create_time from student where sname = "變成派大星"-- 會走索引嗎？


未走索引例子

EXPLAINselect create_time from student where address = "上海"and s_code = 1-- 會走索引嗎？


走的全表掃描 rows = 4

如果不知道 EXPLAIN 是什麽的 或者看不懂分析出來的數據的話 建議去看看另一篇文章 分析命令EXPLAIN超詳解 ^[1]

如果你內心的答案沒有全部說對就接著往下看

最左匹配原則顧名思義：最左優先，以最左邊的為起點任何連續的索引都能匹配上。 同時遇到範圍查詢(>、<、between、like)就會停止匹配 。

例如：s_code = 2 如果建立( sname , s_code )順序的索引，是匹配不到( sname , s_code )索引的;

但是如果查詢條件是sname = "變成派大星" and s_code = 2或者a=1(又或者是s_code = 2 and sname = "變成派大星" )就可以， 因為最佳化器會自動調整 sname , s_code 的順序 。再比如sname = "變成派大星" and s_code > 1 and address = "上海" address是用不到索引的 ，因為s_code欄位是一個範圍查詢，它之後的欄位會停止匹配。

不帶範圍查詢 索引使用型別

帶範圍使用型別

根據上一篇文章的講解 可以明白 ref 和range的含義 級別還是相差很多的

思考

為什麽左連結一定要遵循最左綴原則呢？

驗證

看過一個比較好玩的回答

你可以認為聯合索引是闖關遊戲的設計
例如你這個聯合索引是state/city/zipCode
那麽state就是第一關 city是第二關， zipCode就是第三關
你必須匹配了第一關，才能匹配第二關，匹配了第一關和第二關，才能匹配第三關

這樣描述不算完全準確 但是確實是這種思想

要想理解聯合索引的最左匹配原則，先來理解下索引的底層原理。索引的底層是一顆B+樹，那麽聯合索引的底層也就是一顆B+樹，只不過聯合索引的B+樹節點中儲存的是鍵值。由於構建一棵B+樹只能根據一個值來確定索引關系，所以資料庫依賴聯合索引最左的欄位來構建 文字比較抽象 我們看一下

加入我們建立 A,B 聯合索引 他們在底層儲存是什麽樣子呢？

圖解：

我們可以看出幾個特點

這裏應該就能看出 如果沒有A的支持 B的索引是雜湊的 不是連續的

再細致一點 我們重新建立一個表

DROPTABLEIFEXISTS`leftaffix`;
CREATETABLE`leftaffix` (
`a`int(11) NOTNULL AUTO_INCREMENT,
`b`int(11) NULLDEFAULTNULL,
`c`int(11) NULLDEFAULTNULL,
`d`int(11) NULLDEFAULTNULL,
`e`varchar(11) CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL,
PRIMARY KEY (`a`) USING BTREE,
INDEX`聯合索引`(`b`, `c`, `d`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8CHARACTERSET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of leftaffix
-- ----------------------------
INSERTINTO`leftaffix`VALUES (1, 1, 1, 1, '1');
INSERTINTO`leftaffix`VALUES (2, 2, 2, 2, '2');
INSERTINTO`leftaffix`VALUES (3, 3, 2, 2, '3');
INSERTINTO`leftaffix`VALUES (4, 3, 1, 1, '4');
INSERTINTO`leftaffix`VALUES (5, 2, 3, 5, '5');
INSERTINTO`leftaffix`VALUES (6, 6, 4, 4, '6');
INSERTINTO`leftaffix`VALUES (7, 8, 8, 8, '7');
SET FOREIGN_KEY_CHECKS = 1;
















在建立索引樹的時候會對數據進行排序 根據最左綴原則 會先透過 B 進行排序 也就是 如果出現值相同就 根據 C 排序 如果 C相同就根據D 排序 排好順序之後就是如下圖：

索引的生成就會根據圖二的順序進行生成 我們看一下 生成後的樹狀數據是什麽樣子

解釋一些這個樹狀圖 首先根據圖二的排序 我們知道順序 是 1111a 2222b 所以 在第三層 我們可以看到 1111a 在第一層 2222b在第二層 因為 111 < 222 所以 111 進入第二層 然後得出第一層

簡化一下就是這個樣子

但是這種順序是相對的。這是因為MySQL建立聯合索引的規則是首先會對聯合索引的最左邊 第一個欄位排序 ，在第一個欄位的排序基礎上，然後在對第二個欄位進行排序。所以B=2這種查詢條件沒有辦法利用索引。

看到這裏還可以明白一個道理 為什麽我們建立索引的時候不推薦建立在經常改變的欄位 因為這樣的話我們的索引結構就要跟著你的改變而改動 所以很消耗效能

補充

評論區老哥的提示 最左綴原則可以透過跳躍掃描的方式打破 簡單整理一下這方面的知識

這個是在 8.0 進行的最佳化

MySQL8.0版本 開始增加了索引跳躍掃描的功能，當第一列索引的唯一值較少時，即使where條件沒有第一列索引，查詢的時候也可以用到聯合索引。

比如我們使用的聯合索引是 bcd 但是b中欄位比較少 我們在使用聯合索引的時候沒有 使用 b 但是依然可以使用聯合索引 MySQL聯合索引有時候遵循最左字首匹配原則，有時候不遵循。

小總結

前提 如果建立 b,c,d 聯合索引面

我們接著看下一個失效場景

select *

思考

這裏是我之前的一個思維誤區 select * 不會導致索引失效 之前測試發現失效是因為where 後面的查詢範圍過大 導致索引失效 並不是 Select * 引起的 但是為什麽不推薦使用 select *

解釋

在阿裏的開發手冊中，大面的概括了上面幾點。

在使用 Select * 索引使用正常

雖然走了索引但是 也不推薦這種寫法 為什麽呢？

首先我們在上一個驗證中建立了聯合索引 我們使用B=1 會走索引 但是 與直接查詢索引欄位不同 使用 SELECT* ,獲取了不需要的數據，則首先透過輔助索引過濾數據，然後再透過聚集索引獲取所有的列，這就多了一次b+樹查詢，速度必然會慢很多，減少使用 select * 就是降低回表帶來的損耗。

也就是 Select * 在一些情況下是會走索引的 如果不走索引就是 where 查詢範圍過大 導致MySQL 最優選擇全表掃描了 並不是 Select * 的問題

上圖就是索引失效的情況

範圍尋找也不是一定會索引失效 下面情況就會索引生效就是 級別低 生效的原因是因為縮小了範圍

小總結

使用函式

使用在Select 後面使用函式可以使用索引 但是下面這種做法就不能

因為索引保存的是索引欄位的原始值，而不是經過函式計算後的值，自然就沒辦法走索引了。

不過，從 MySQL 8.0 開始，索引特性增加了函式索引，即可以針對函式計算後的值建立一個索引，也就是說該索引的值是函式計算後的值，所以就可以透過掃描索引來查詢數據。

這種寫法我沒使用過 感覺情況比較少 也比較容易註意到這種寫法

計算操作

這個情況和上面一樣 之所以會導致索引失效是因為改變了索引原來的值 在樹中找不到對應的數據只能全表掃描

因為索引保存的是索引欄位的原始值，而不是 b - 1 運算式計算後的值，所以無法走索引，只能透過把索引欄位的取值都取出來，然後依次進行運算式的計算來進行條件判斷，因此采用的就是全表掃描的方式。

下面這種計算方式就會使用索引

Java比較熟悉的可能會有點疑問，這種對索引進行簡單的運算式計算，在程式碼特殊處理下，應該是可以做到索引掃描的，比方將 b - 1 = 6 變成 b = 6 - 1 。是的，是能夠實作，但是 MySQL 還是偷了這個懶，沒有實作。

小總結

總而言之 言而總之 只要是影響到索引列的值 索引就是失效

Like %

這個真的是難受哦 因為經常使用這個 所以還是要小心點 在看為什麽失效之前 我們先看一下 Like % 的解釋

1. %百分號通配符: 表示任何字元出現任意次數(可以是0次).

2. _底線通配符: 表示只能匹配單個字元,不能多也不能少,就是一個字元.

3. like操作符: LIKE作用是指示mysql後面的搜尋模式是利用通配符而不是直接相等匹配進行比較.

註意: 如果在使用like操作符時,後面的沒有使用通用匹配符效果是和=一致的,

SELECT * FROM products WHERE products.prod_name like'1000'; 


2.匹配包含"Li"的記錄(包括記錄"Li") :

SELECT* FROM products WHERE products.prod_name like'%Li%';


3.匹配以"Li"結尾的記錄(包括記錄"Li",不包括記錄"Li ",也就是Li後面有空格的記錄,這裏需要註意)

SELECT * FROM products WHERE products.prod_name like'%Li';


在左不走 在右走

右： 雖然走 但是索引級別比較低主要是模糊查詢 範圍比較大 所以索引級別就比較低

左： 這個範圍非常大 所以沒有使用索引的必要了 這個可能不是很好最佳化 還好不是一直拼接上面的。公眾 號Java精選，回復java面試，獲取面試資料，支持線上刷題。

小總結定期回顧-審視自己、查缺補漏

索引的時候和查詢範圍關系也很大 範圍過大造成索引沒有意義從而失效的情況也不少

使用Or導致索引失效

這個原因就更簡單了

在 WHERE 子句中，如果在 OR 前的條件列是索引列，而在 OR 後的條件列不是索引列，那麽索引會失效 舉個例子，比如下面的查詢語句，b 是主鍵，e 是普通列，從執行計劃的結果看，是走了全表掃描。

最佳化

這個的最佳化方式就是 在Or的時候兩邊都加上索引

就會使用索引 避免全表掃描

in使用不當

首先使用In 不是一定會造成全表掃描的 IN肯定會走索引，但是當IN的取值範圍較大時會導致索引失效，走全表掃描

in 在結果集 大於30%的時候索引失效

not in 和 In的失效場景相同

order By

這一個主要是Mysql 自身最佳化的問題 我們都知道OrderBy 是排序 那就代表我需要對數據進行排序 如果我走索引 索引是排好序的 但是我需要回表 消耗時間 另一種 我直接全表掃描排序 不用回表 也就是

Mysql 認為直接全表掃面的速度比 回表的速度快所以就直接走索引了 在 Order By 的情況下 走全表掃描反而是更好的選擇

子查詢會走索引嗎

答案是會 但是使用不好就不會

大總結

減少回表最佳化思路

這個對於SQL有研究的人可能是比較了解的但是對於工作時長不久的會比較陌生的詞語 但是這個是非常有意思 且重要的

在這個索引問題上面還有一個細節的東西 其中印象比較深刻的是回表會造成效率下降 但是在我們日常工作中是比較常用單列索引 聯合索引對於新手來說不是很常用 但是單列索引在一些情況下肯定不是最優解 例如 like % 問題 會造成索引問題 近期了解到一個 ICP 知識 我之前都沒有關註過 不知道大家對這個了解多少 我這裏就進行一些整理

首先我們ICP 全稱是 Index Condition Pushdown 中文可以說成是索引下推 主要的作用解決數據查詢回表的問題 但是前提是和聯合索引進行使用 才能發揮出來功效 接下來不了解的小夥伴可以認真看一下這一點 個人感覺還是比較有意思的東西

回表問題

上面其實對於回表查詢沒有過多的解釋 就再提一什麽是回表查詢

回表查詢一般發生在非主鍵索引上面 需要進行兩次樹查詢 所以效率會有所折扣 我們要想解決這個行為就可以使用 聯合索引去最佳化

ICP 索引下推

這個是在MySQL 5.6 之後提供的特性 這個如果面試中問到 我們平常面試的時候 面試官都有喜歡問什麽版本 增加了什麽 如果問你 MySQL 5.6 之後增加什麽最佳化 不知道大家都能說出什麽 這個就是一個很加分點 你能說明白什麽是索引下推 面試官會對你增加好感 至少說明你還是有點東西在身上的 不啰嗦了 開始研究

我們先看一下 5.6 之前 和 5.6 之後 查詢流程會有什麽變化

假設 我我們需要查詢 select * from table1 where b like '3%' and c = 3

5.6 之前

5.6 之後

上面都會進行回表查詢但是 5.6 之前沒有完全去利用 二級緩存進行數據過濾 如果 3 開頭的數據非常多 那就要一直回表 但是 5.6 之後去利用後續索引欄位進行查詢

怎麽說呢 就是為什麽索引下推要和聯合索引進行使用 普通所以沒有 索引下推就是充分利用 聯合索引的欄位進過濾 盡量減少需要回表的數據 來增加查詢效率 感覺思路是很簡單的

對於Innodb 引擎的ICP 只適合 二級索引

小細節：

索引下推除了依賴 聯合索引之外 還不能在子查詢下面進行使用 儲存函式也不能使用

怎麽檢視是否使用索引下推

來源：juejin.cn/post/7161964571853815822

>>

END

精品資料，超贊福利，免費領

微信掃碼/長按辨識 添加【技術交流群】

群內每天分享精品學習資料

最近開發整理了一個用於速刷面試題的小程式；其中收錄了上千道常見面試題及答案(包含基礎、並行、JVM、MySQL、Redis、Spring、SpringMVC、SpringBoot、SpringCloud、訊息佇列等多個型別)，歡迎您的使用。

👇👇

👇點選"閱讀原文"，獲取更多資料（持續更新中）