前言

SQL 寫不好 加班少不了 日常工作中SQL 是必不可少的一項(xiàng)技術(shù) 但是很多人不會(huì)過多的去關(guān)注SQL問題 一是數(shù)據(jù)量小 二是沒有意識(shí)到索引的重要性 本文主要是整理 SQL失效場(chǎng)景 如果里面的細(xì)節(jié)你都知道 那你一定是學(xué)習(xí)能力比較好的人 膜拜 寫完這篇文章 我感覺自己之前知道的真的是 “目錄” 沒有明白其中的內(nèi)容 如果你能跟著節(jié)奏看完文章 一定會(huì)有收獲 至少我寫完感覺思維通透很多 以后百分之九十的 SQl索引問題 和 面試這方面問題都能拿捏兩

文章 字?jǐn)?shù) 四千余字 觀看時(shí)長(zhǎng)十分鐘 練習(xí)時(shí)長(zhǎng)兩個(gè)半小時(shí)

基礎(chǔ)數(shù)據(jù)準(zhǔn)備

準(zhǔn)備一個(gè)數(shù)據(jù)表作為 數(shù)據(jù)演示 這里面一共 創(chuàng)建了三個(gè)索引

• 聯(lián)合索引 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 `聯(lián)合索引`(`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, '學(xué)生1', 1, '上海', 170, 1, '2022-11-02 20:44:14');
INSERT INTO `student` VALUES (2, '學(xué)生2', 2, '北京', 180, 2, '2022-11-02 20:44:16');
INSERT INTO `student` VALUES (3, '變成派大星', 3, '京東', 185, 3, '2022-11-02 20:44:19');
INSERT INTO `student` VALUES (4, '學(xué)生4', 4, '聯(lián)通', 190, 4, '2022-11-02 20:44:25');

正文

上面的SQL 我們已經(jīng)創(chuàng)建好基本的數(shù)據(jù) 在驗(yàn)證之前 先帶著幾個(gè)問題

我們先從上往下進(jìn)行驗(yàn)證

最左匹配原則

寫在前面：我很早之前就聽說過數(shù)據(jù)庫的最左匹配原則逼龟，當(dāng)時(shí)是通過各大博客論壇了解的齐帚，但是這些博客的局限性在于它們對(duì)最左匹配原則的描述就像一些數(shù)學(xué)定義一樣，往往都是列出123點(diǎn)欲账，滿足這123點(diǎn)就能匹配上索引五鲫，否則就不能溺职。 最左匹配原則就是指在聯(lián)合索引中，如果你的 SQL 語句中用到了聯(lián)合索引中的最左邊的索引位喂，那么這條 SQL 語句就可以利用這個(gè)聯(lián)合索引去進(jìn)行匹配浪耘，我們上面建立了聯(lián)合索引 可以用來測(cè)試最左匹配原則 sname, s_code, address

請(qǐng)看下面SQL語句 進(jìn)行思考 是否會(huì)走索引

-- 聯(lián)合索引 sname,s_code,address

1、select create_time from student where sname = "變成派大星"  -- 會(huì)走索引嗎塑崖？

2七冲、select create_time from student where s_code = 1   -- 會(huì)走索引嗎？

3弃舒、select create_time from student where address = "上海"  -- 會(huì)走索引嗎癞埠？

4、select create_time from student where address = "上海" and s_code = 1 -- 會(huì)走索引嗎聋呢？

5苗踪、select create_time from student where address = "上海" and sname = "變成派大星"  -- 會(huì)走索引嗎？

6削锰、select create_time from student where sname = "變成派大星" and address = "上海"  -- 會(huì)走索引嗎通铲？

7、select create_time from student where sname = "變成派大星" and s_code = 1 and address = "上海"  -- 會(huì)走索引嗎器贩？
復(fù)制代碼

憑你的經(jīng)驗(yàn) 哪些會(huì)使用到索引呢 颅夺？ 可以先思考一下 在心中記下數(shù)字

走索引例子

EXPLAIN  select create_time from student where sname = "變成派大星"  -- 會(huì)走索引嗎？

未走索引例子

EXPLAIN select create_time from student where address = "上海" and s_code = 1 -- 會(huì)走索引嗎蛹稍？

如果你內(nèi)心的答案沒有全部說對(duì)就接著往下看

最左匹配原則顧名思義：最左優(yōu)先吧黄，以最左邊的為起點(diǎn)任何連續(xù)的索引都能匹配上。同時(shí)遇到范圍查詢(>唆姐、<拗慨、between、like)就會(huì)停止匹配奉芦。
例如：s_code = 2 如果建立(sname, s_code)順序的索引赵抢，是匹配不到(sname, s_code)索引的;

但是如果查詢條件是sname = "變成派大星" and s_code = 2或者a=1(又或者是s_code = 2 and sname = "變成派大星" )就可以，因?yàn)閮?yōu)化器會(huì)自動(dòng)調(diào)整sname, s_code的順序声功。再比如sname = "變成派大星" and s_code > 1 and address = "上海" address是用不到索引的烦却，因?yàn)閟_code字段是一個(gè)范圍查詢，它之后的字段會(huì)停止匹配先巴。

帶范圍使用類型

ref 和range的含義 級(jí)別還是相差很多的

思考

為什么左鏈接一定要遵循最左綴原則呢其爵？

驗(yàn)證

看過一個(gè)比較好玩的回答

你可以認(rèn)為聯(lián)合索引是闖關(guān)游戲的設(shè)計(jì)
例如你這個(gè)聯(lián)合索引是state/city/zipCode
那么state就是第一關(guān) city是第二關(guān)冒冬， zipCode就是第三關(guān)
你必須匹配了第一關(guān)，才能匹配第二關(guān)摩渺，匹配了第一關(guān)和第二關(guān)窄驹，才能匹配第三關(guān)

這樣描述不算完全準(zhǔn)確 但是確實(shí)是這種思想

要想理解聯(lián)合索引的最左匹配原則，先來理解下索引的底層原理证逻。索引的底層是一顆B+樹，那么聯(lián)合索引的底層也就是一顆B+樹抗斤，只不過聯(lián)合索引的B+樹節(jié)點(diǎn)中存儲(chǔ)的是鍵值囚企。由于構(gòu)建一棵B+樹只能根據(jù)一個(gè)值來確定索引關(guān)系，所以數(shù)據(jù)庫依賴聯(lián)合索引最左的字段來構(gòu)建 文字比較抽象 我們看一下

加入我們建立 A,B 聯(lián)合索引 他們?cè)诘讓觾?chǔ)存是什么樣子呢瑞眼？

• 橙色代表字段 A
• 淺綠色 代表字段B

我們可以看出幾個(gè)特點(diǎn)

• A 是有順序的 1龙宏，1，2伤疙，2银酗，3，4
• B 是沒有順序的 1徒像，2黍特，1，4锯蛀，1灭衷，2 這個(gè)是散列的
• 如果A是等值的時(shí)候 B是有序的 例如 （1，1）旁涤，（1翔曲，2） 這里的B有序的 （2，1）,(2,4) B 也是有序的

這里應(yīng)該就能看出 如果沒有A的支持 B的索引是散列的 不是連續(xù)的

再細(xì)致一點(diǎn) 我們重新創(chuàng)建一個(gè)表

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 `聯(lián)合索引`(`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;
復(fù)制代碼

在創(chuàng)建索引樹的時(shí)候會(huì)對(duì)數(shù)據(jù)進(jìn)行排序 根據(jù)最左綴原則 會(huì)先通過 B 進(jìn)行排序 也就是 如果出現(xiàn)值相同就 根據(jù) C 排序 如果 C相同就根據(jù)D 排序 排好順序之后就是如下圖：

索引的生成就會(huì)根據(jù)圖二的順序進(jìn)行生成 我們看一下 生成后的樹狀數(shù)據(jù)是什么樣子

解釋一些這個(gè)樹狀圖 首先根據(jù)圖二的排序 我們知道順序 是 1111a 2222b 所以 在第三層 我們可以看到 1111a 在第一層 2222b在第二層 因?yàn)?111 < 222 所以 111 進(jìn)入第二層 然后得出第一層

簡(jiǎn)化一下就是這個(gè)樣子

但是這種順序是相對(duì)的劈愚。這是因?yàn)镸ySQL創(chuàng)建聯(lián)合索引的規(guī)則是首先會(huì)對(duì)聯(lián)合索引的最左邊第一個(gè)字段排序瞳遍，在第一個(gè)字段的排序基礎(chǔ)上，然后在對(duì)第二個(gè)字段進(jìn)行排序菌羽。所以B=2這種查詢條件沒有辦法利用索引掠械。

看到這里還可以明白一個(gè)道理 為什么我們建立索引的時(shí)候不推薦建立在經(jīng)常改變的字段 因?yàn)檫@樣的話我們的索引結(jié)構(gòu)就要跟著你的改變而改動(dòng) 所以很消耗性能

小總結(jié)

前提 如果創(chuàng)建 b,c,d 聯(lián)合索引面

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

我們接著看下一個(gè)失效場(chǎng)景

select *

思考

首先提出問題 select * 一定會(huì)索引失效嗎算凿？

解釋

如果你的心里答案是 會(huì)失效那就接著往下看

細(xì)心的同學(xué)能夠發(fā)現(xiàn) 即便我使用了select * 依然會(huì)走索引 這是為什么呢份蝴？

首先我們?cè)谏弦粋€(gè)驗(yàn)證中創(chuàng)建了聯(lián)合索引 我們使用B=1 會(huì)走索引

也就是 Select * 在一些情況下是會(huì)走索引的 那么什么時(shí)候不會(huì)走索引呢

經(jīng)過測(cè)試 在查詢返回結(jié)果集大約總數(shù)據(jù)的25%就不會(huì)走索引了 進(jìn)而全表掃描 這里也有一個(gè)知識(shí)點(diǎn) 也就是為什么范圍查找會(huì)索引失效的原因

上圖就是索引失效的情況

范圍查找也不是一定會(huì)索引失效 下面情況就會(huì)索引生效就是 級(jí)別低 生效的原因是因?yàn)榭s小了范圍

小總結(jié)

• select * 只有在返回結(jié)果集數(shù)量大于總結(jié)果集的25% 就會(huì)造成索引失效 如果小于 不會(huì)造成索引失效但是會(huì)降低索引的效率
• 范圍查找有概率索引失效但是 但是在特定的情況下會(huì)生效 范圍小就會(huì)使用 也可以理解為 返回結(jié)果集小就會(huì)使用索引

使用函數(shù)

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

因?yàn)樗饕４娴氖撬饕侄蔚脑贾担皇墙?jīng)過函數(shù)計(jì)算后的值氓轰，自然就沒辦法走索引了婚夫。

不過，從 MySQL 8.0 開始署鸡，索引特性增加了函數(shù)索引案糙，即可以針對(duì)函數(shù)計(jì)算后的值建立一個(gè)索引限嫌，也就是說該索引的值是函數(shù)計(jì)算后的值，所以就可以通過掃描索引來查詢數(shù)據(jù)时捌。

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

計(jì)算操作

這個(gè)情況和上面一樣 之所以會(huì)導(dǎo)致索引失效是因?yàn)楦淖兞怂饕瓉淼闹?在樹中找不到對(duì)應(yīng)的數(shù)據(jù)只能全表掃描

因?yàn)樗饕４娴氖撬饕侄蔚脑贾蹬剑皇?b - 1 表達(dá)式計(jì)算后的值，所以無法走索引奢讨，只能通過把索引字段的取值都取出來稚叹，然后依次進(jìn)行表達(dá)式的計(jì)算來進(jìn)行條件判斷，因此采用的就是全表掃描的方式拿诸。

下面這種計(jì)算方式就會(huì)使用索引

Java比較熟悉的可能會(huì)有點(diǎn)疑問扒袖，這種對(duì)索引進(jìn)行簡(jiǎn)單的表達(dá)式計(jì)算，在代碼特殊處理下亩码，應(yīng)該是可以做到索引掃描的季率，比方將 b - 1 = 6 變成 b = 6 - 1。 是的描沟，是能夠?qū)崿F(xiàn)飒泻，但是 MySQL 還是偷了這個(gè)懶，沒有實(shí)現(xiàn)吏廉。

小總結(jié)

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

Like %

這個(gè)真的是難受哦 因?yàn)榻?jīng)常使用這個(gè) 所以還是要小心點(diǎn) 在看為什么失效之前 我們先看一下 Like % 的解釋

1. %百分號(hào)通配符: 表示任何字符出現(xiàn)任意次數(shù)(可以是0次).
2. _下劃線通配符: 表示只能匹配單個(gè)字符,不能多也不能少,就是一個(gè)字符.
3. like操作符: LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配進(jìn)行比較.

注意: 如果在使用like操作符時(shí),后面的沒有使用通用匹配符效果是和=一致的,

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

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

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

3.匹配以"Li"結(jié)尾的記錄(包括記錄"Li",不包括記錄"Li ",也就是Li后面有空格的記錄,這里需要注意)

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

在左不走 在右走

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

小總結(jié)

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

使用Or導(dǎo)致索引失效

這個(gè)原因就更簡(jiǎn)單了

在 WHERE 子句中泞遗，如果在 OR 前的條件列是索引列，而在 OR 后的條件列不是索引列迟蜜，那么索引會(huì)失效 舉個(gè)例子刹孔，比如下面的查詢語句，b 是主鍵娜睛，e 是普通列髓霞，從執(zhí)行計(jì)劃的結(jié)果看，是走了全表掃描畦戒。

優(yōu)化

這個(gè)的優(yōu)化方式就是 在Or的時(shí)候兩邊都加上索引

in使用不當(dāng)

首先使用In 不是一定會(huì)造成全表掃描的 IN肯定會(huì)走索引方库，但是當(dāng)IN的取值范圍較大時(shí)會(huì)導(dǎo)致索引失效，走全表掃描

in 在結(jié)果集 大于30%的時(shí)候索引失效

not in 和 In的失效場(chǎng)景相同

order By

這一個(gè)主要是Mysql 自身優(yōu)化的問題 我們都知道OrderBy 是排序 那就代表我需要對(duì)數(shù)據(jù)進(jìn)行排序 如果我走索引 索引是排好序的 但是我需要回表 消耗時(shí)間 另一種 我直接全表掃描排序 不用回表 也就是

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

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

子查詢會(huì)走索引嗎

答案是會(huì) 但是使用不好就不會(huì)

大總結(jié)

• 如果你是直接跳到這里 看看文章有多長(zhǎng) 建議收藏
• 如果你一步步看到這里 感覺有點(diǎn)幫助 贊贊來一個(gè)
• 如果感覺文章有問題 建議評(píng)論區(qū)指出 會(huì)修正