基礎數據準備

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

聯合索引 sname， s_code， address

主鍵索引 id

普通索引 height

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_code` int(100) NULL DEFAULT NULL,
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `height` double NULL DEFAULT NULL,
  `classid` int(11) NULL DEFAULT NULL,
  `create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `普通索引`(`height`) USING BTREE,
  INDEX `聯合索引`(`sname`, `s_code`, `address`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '學生1', 1, '上海', 170, 1, '2022-11-02 2014');
INSERT INTO `student` VALUES (2, '學生2', 2, '北京', 180, 2, '2022-11-02 2016');
INSERT INTO `student` VALUES (3, '變成派大星', 3, '京東', 185, 3, '2022-11-02 2019');
INSERT INTO `student` VALUES (4, '學生4', 4, '聯通', 190, 4, '2022-11-02 2025');

上面的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 = 1 and address = "上海"  -- 會走索引嗎？

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

走索引例子

EXPLAINselectcreate_timefromstudentwheresname="變成派大星"--會走索引嗎？

未走索引例子

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

走的全表掃描 rows = 4

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

最左匹配原則顧名思義：最左優先，以最左邊的為起點任何連續的索引都能匹配上。同時遇到范圍查詢(>、<、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

圖解：

我們可以看出幾個特點

• A 是有順序的 1，1，2，2，3，4
• B 是沒有順序的 1，2，1，4，1，2 這個是散列的
• 如果A是等值的時候 B是有序的 例如 （1，1），（1，2） 這里的B有序的 （2，1）,(2,4) B 也是有序的

這里應該就能看出 如果沒有A的支持 B的索引是散列的 不是連續的

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

DROP TABLE IF EXISTS `leftaffix`;

CREATE TABLE `leftaffix`  (

  `a` int(11) NOT NULL AUTO_INCREMENT,

  `b` int(11) NULL DEFAULT NULL,

  `c` int(11) NULL DEFAULT NULL,

  `d` int(11) NULL DEFAULT NULL,

  `e` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

  PRIMARY KEY (`a`) USING BTREE,

  INDEX `聯合索引`(`b`, `c`, `d`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of leftaffix
-- ----------------------------
INSERT INTO `leftaffix` VALUES (1, 1, 1, 1, '1');

INSERT INTO `leftaffix` VALUES (2, 2, 2, 2, '2');

INSERT INTO `leftaffix` VALUES (3, 3, 2, 2, '3');

INSERT INTO `leftaffix` VALUES (4, 3, 1, 1, '4');

INSERT INTO `leftaffix` VALUES (5, 2, 3, 5, '5');

INSERT INTO `leftaffix` VALUES (6, 6, 4, 4, '6');

INSERT INTO `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 聯合索引面

• 如果 我where 后面的條件是c = 1 and d = 1為什么不能走索引呢 如果沒有b的話 你查詢的值相當于 *11 我們都知道*是所有的意思也就是我能匹配到所有的數據
• 如果 我 where 后面是 b = 1 and d =1 為什么會走索引呢？你等于查詢的數據是 1*1 我可以通過前面 1 進行索引匹配 所以就可以走索引
• 最左綴匹配原則的最重要的就是 第一個字段

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

select *

思考

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

解釋

• 增加查詢分析器解析成本。
• 增減字段容易與 resultMap 配置不一致。
• 無用字段增加網絡 消耗，尤其是 text 類型的字段。

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

在使用Select * 索引使用正常

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

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

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

上圖就是索引失效的情況

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

小總結

• select * 會走索引
• 范圍查找有概率索引失效但是在特定的情況下會生效 范圍小就會使用 也可以理解為 返回結果集小就會使用索引
• mysql中連接查詢的原理是先對驅動表進行查詢操作，然后再用從驅動表得到的數據作為條件，逐條的到被驅動表進行查詢。
• 每次驅動表加載一條數據到內存中，然后被驅動表所有的數據都需要往內存中加載一遍進行比較。效率很低，所以mysql中可以指定一個緩沖池的大小，緩沖池大的話可以同時加載多條驅動表的數據進行比較，放的數據條數越多性能io操作就越少，性能也就越好。所以，如果此時使用select * 放一些無用的列，只會白白的占用緩沖空間。浪費本可以提高性能的機會。
• 按照評論區老哥的說法 select * 不是造成索引失效的直接原因 大部分原因是 where 后邊條件的問題 但是還是盡量少去使用select * 多少還是會有影響的

使用函數

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

因為索引保存的是索引字段的原始值，而不是經過函數計算后的值，自然就沒辦法走索引了。

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

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

計算操作

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

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

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

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

小總結

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

Like %

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

• %百分號通配符: 表示任何字符出現任意次數(可以是0次).
• _下劃線通配符: 表示只能匹配單個字符,不能多也不能少,就是一個字符.
• 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';

在左不走 在右走

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

左：這個范圍非常大 所以沒有使用索引的必要了 這個可能不是很好優化 還好不是一直拼接上面的

小總結

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

使用Or導致索引失效

這個原因就更簡單了

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

優化

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

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

in使用不當

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

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

not in 和 In的失效場景相同

order By

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

• 走索引 + 回表
• 不走索引 直接全表掃描

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

子查詢會走索引嗎

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

大總結

審核編輯：郭婷