相關文章
資料庫系列：MySQL慢查詢分析和效能最佳化
資料庫系列：MySQL索引最佳化總結（綜合版）
資料庫系列：高併發下的資料欄位變更
資料庫系列：覆蓋索引和規避回表
資料庫系列：資料庫高可用及無損擴容
資料庫系列：使用高區分度索引列提升效能
資料庫系列：字首索引和索引長度的取捨
資料庫系列：MySQL引擎MyISAM和InnoDB的比較
資料庫系列：InnoDB下實現高併發控制
資料庫系列：事務的4種隔離級別
資料庫系列：RR和RC下，快照讀的區別
資料庫系列：MySQL InnoDB鎖機制介紹
資料庫系列：MySQL不同操作分別用什麼鎖？
資料庫系列：業內主流MySQL資料中介軟體梳理
資料庫系列：大廠使用資料庫中介軟體解決什麼問題？
1 介紹
在筆者的這篇文章《構建高效能索引（策略篇）》中，我們詳細討論瞭如何設計高質量索引，裡面多個地方提及可能導致索引失效的場景。
這邊咱們重新梳理下，以列舉的方式來梳理出所有可能出現索引失效的點，避免RD同學們踩坑。
2 資料準備及驗證
2.1 資料準備
1、建立兩個表：員工表和部門表
/*部門表,存在則進行刪除 */
drop table if EXISTS dep;
create table dep(
id int unsigned primary key auto_increment,
depno mediumint unsigned not null default 0,
depname varchar(20) not null default "",
memo varchar(200) not null default ""
);
/*員工表,存在則進行刪除*/
drop table if EXISTS emp;
create table emp(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,
empname varchar(20) not null default "",
job varchar(9) not null default "",
mgr mediumint unsigned not null default 0,
hiredate datetime not null,
sal decimal(7,2) not null,
comn decimal(7,2) not null,
depno mediumint unsigned not null default 0
);
2、建立兩個函式：生成隨機字串和隨機編號
/* 產生隨機字串的函式*/
DELIMITER $
drop FUNCTION if EXISTS rand_string;
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i+1;
END WHILE;
RETURN return_str;
END $
DELIMITER;
/*產生隨機部門編號的函式*/
DELIMITER $
drop FUNCTION if EXISTS rand_num;
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(RAND()*100+RAND()*20);
RETURN i;
END $
DELIMITER;
3、編寫儲存過程，模擬500W的員工資料
/*建立儲存過程：往emp表中插入資料*/
DELIMITER $
drop PROCEDURE if EXISTS insert_emp;
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
/*set autocommit =0 把autocommit設定成0，把預設提交關閉*/
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),'SALEMAN',0001,now(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $
DELIMITER;
/*插入500W條資料*/
call insert_emp(0,5000000);
4、編寫儲存過程，模擬120條部門資料
/*建立儲存過程：往dep表中插入資料*/
DELIMITER $
drop PROCEDURE if EXISTS insert_dept;
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i+1;
INSERT INTO dep( depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $
DELIMITER;
/*插入120條資料*/
call insert_dept(0,120);
5、建立關鍵欄位的索引，這邊是跑完資料之後再建索引，會導致建索引耗時長，但是跑資料就會快一些。
# 這邊建立一個複合索引，包含 depno（部門編號）、empname（員工姓名）、job（工作崗位）。
create index idx_depno_empname_job on emp(depno,empname,job);
2.2 驗證過程
在 MySQL 中建設合理高效的索引是提升檢索效能的最有效方式，因為索引可以快速地定位表中的某條記錄，達到提高資料庫查詢的速度的目的。
大多數情況下都（預設）採用B+樹來構建索引，我們下面也預設使用InnoDB引擎來舉例。
2.2.1 違反最左匹配原則
如下圖，b+樹的資料項是複合的資料結構，比如(empname,depno,job)這種（即構建一個聯合索引）時，b+樹是按照從左到右的順序來建立搜尋樹的。
示例：
1、當以('brand',106,'SALEMAN')這樣的資料來檢索的時候，b+樹會優先比較empname來確定下一步的所搜方向，如果empname相同再依次比較depno和job，最後得到檢索的資料。
2、但如果是(106,'SALEMAN')這樣，沒有empname的資料來的時候，b+樹就不知道下一步該查哪個節點，因為empname就是第一個比較因子，必須要先根據empname來搜尋才能知道下一步去哪裡查詢。
3、再比如當('brand','SALEMAN')這樣的資料來檢索時，b+樹可以用empname來指定搜尋方向，但下一個欄位depno的缺失，所以只能把名字等於 'brand' 的資料都掃描出來，然後再匹配職位是SALEMAN的資料了。
這個重要特徵就是索引的最左匹配原則，按照這個原則執行索引效率特別高。
我們試試在b+樹上分析和舉例：
下圖中是3個欄位(depno,empname,job)的聯合索引，資料以depno asc,empname asc,job asc這種排序方式儲存在節點中的，
排序原則：
1、索引以depno欄位升序
2、depno相同時，以empname欄位升序，
3、empname相同的時候，以job欄位升序
第一位置
檢索depno=7的記錄
由於頁中的記錄是以depno asc,empname asc,job asc這種排序方式儲存的，所以depno欄位是有序的，可以透過二分法快速檢索到，步驟如下：
1、將Disk1載入到記憶體中
2、在記憶體中對Disk1中的記錄採用二分法找，可以確定depno=7的記錄位於{7,Brand,1}和{7,dyny,1}關聯的範圍內，這兩個所指向的頁分別是 Disk2 和 Disk4。
3、載入頁Disk2，在Disk2中採用二分法快速找到第一條depno=7的記錄，然後透過連結串列向下一條及下一頁開始掃描，直到在Disk4中找到第一個不滿足depno=7的記錄為止。
第一+第二位置
檢索depno=7 and empname like 'B%'的記錄
步驟跟上面是一致的，可以確定depno=1 and empname like 'B%'的記錄位於{7,Band,1}和{7,Bec,1}關聯的範圍內，查詢過程和depno=7查詢步驟類似。
第二位置
檢索empname like 'C%'的記錄
這種情況透過Disk1頁中的記錄，無法判斷empname like 'C%' 的記錄在哪些頁中的，只能逐個載入索引樹的頁，對所有記錄進行遍歷，然後進行過濾，此時索引無效。
# 驗證指令碼：未使用到了索引，全表掃描
mysql> explain select empno,empname,job from emp where empname like 'C%';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4982087 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
第三位置
檢索job=8的記錄
這種情況和查詢 empname like 'C%' 也一樣，也只能掃描所有葉子節點，索引也無效。
# 驗證指令碼：未使用到了索引，全表掃描
mysql> explain select empno,empname,job from emp where job=8;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4982087 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
第二+第三位置
empname和job一起查
這種原理跟前面兩個一致，無法使用索引，只能對所有資料進行掃描。
第一+第三位置
按照(depno,job)欄位順序檢索
這種僅使用到索引中的depno欄位了，透過depnon確定範圍之後，載入所有depno下的資料，再對job條件進行過濾。如果的depno查出來的資料基數巨大，也會慢。
比如我們的測試資料中 depno=16 的資料有50W左右，也是比較多的。
# 驗證指令碼：未使用到了索引，但僅覆蓋了depno，所以掃描行數也有 37626 行
mysql> explain select empno,empname,job from emp where depno=7 and job=8;
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+-------+----------+-----------------------+
| 1 | SIMPLE | emp | NULL | ref | idx_depno_empname_job | idx_depno_empname_job | 3 | const | 37626 | 10.00 | Using index condition |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+-------+----------+-----------------------+
1 row in set, 2 warnings (0.01 sec)
停止匹配的條件
檢索depno=1 and empname>'' and job=1的記錄
據上面的圖，這種檢索方式只能先確定depno=1 and empname>''所在頁的範圍，然後對這個範圍的所有頁進行遍歷，job欄位在這個查詢的過程中，是無法確定資料在哪些頁的，此時我們說job是不走索引的，只有depno、empname能夠有效的確定索引頁的範圍。
2.2.2 索引列使用函式
當我們不恰當的使用索引所對應的欄位的時候，可能會導致索引失效，比如查詢的過程沒有保證獨立的列，
這個獨立的列是指索引對應的列不能作用在函式中。如下：
mysql> select * from emp where id = 4990000;
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id | empno | empname | job | mgr | hiredate | sal | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4990000 | 4990000 | PWmulY | SALEMAN | 1 | 2021-01-23 16:46:24 | 2000 | 400 | 102 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set (0.002 sec)
mysql> select * from emp where ABS(id) = 4990001;
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id | empno | empname | job | mgr | hiredate | sal | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4990001 | 4990001 | fXtdiH | SALEMAN | 1 | 2021-01-23 16:46:24 | 2000 | 400 | 107 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set (2.007 sec)
耗時分別是 0.002、2.007，使用explain分析後發現作用在函式的時候沒有走索引，變成全表掃描：
mysql> explain select * from emp where id = 4990000;
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | emp | const | PRIMARY,idx_emp_id | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
1 row in set
mysql> explain select * from emp where ABS(id) = 4990001;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 4952492 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set
2.2.3 計算表示式導致索引無效
索引對應的列也不能作用於計算表示式中：
mysql> select * from emp where id = 4990000;
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id | empno | empname | job | mgr | hiredate | sal | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4990000 | 4990000 | PWmulY | SALEMAN | 1 | 2021-01-23 16:46:24 | 2000 | 400 | 102 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set (0.002 sec)
mysql> select * from emp where id+1 = 4990001;
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id | empno | empname | job | mgr | hiredate | sal | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4990000 | 4990000 | PWmulY | SALEMAN | 1 | 2021-01-23 16:46:24 | 2000 | 400 | 102 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set (1.762 sec)
耗時分別是 0.002、1.762，使用explain分析後發現作用在表示式的時候沒有走索引，變成全表掃描：
mysql> explain select * from emp where id = 4990000;
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | emp | const | PRIMARY,idx_emp_id | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
1 row in set
# 下面這種是不行的
mysql> explain select * from emp where id+1 = 4990001;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 4952492 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set
# 下面這種是可以的
mysql> explain select * from emp where id = 4990001-1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
2.2.4 型別轉換(自動或手動)導致索引失效
mysql> select * from emp where empname ='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id | empno | empname | job | mgr | hiredate | sal | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN | 1 | 2021-01-23 16:46:03 | 2000 | 400 | 106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set
從這個資料中我們可以看出 empname為字串型別的，depno為數值型別的，這兩個上面都有獨立的索引，我們來看兩個語句：
mysql> select * from emp where empname =1;
Empty set, 65535 warnings (2.57 sec)
mysql> explain select * from emp where empname =1;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4982087 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
mysql> select count(*) from emp where depno ='106';
+----------+
| count(*) |
+----------+
| 500195 |
+----------+
1 row in set (0.000 sec)
mysql> select count(*) from emp where depno =106;
+----------+
| count(*) |
+----------+
| 500195 |
+----------+
1 row in set (0.001 sec)
1、第一個查詢，即便是在empname上建了索引，耗時依舊達到2s多。那是因為empname是字串型別，字串和數字比較的時候，會將字串強制轉換為數字，然後進行比較，所以整個查詢變成了全表掃描，一個個抽出每條資料，將empname轉換為數字和1進行比較。從第二個explain語句中也印證了這個演算法。
2、 第三個和第四個查詢，depno是int型別的，兩個查詢效率一致，都是正常利用索引快速檢索。這是因為數值型別的欄位，查詢匹配的值無論是字串還是數值都會走索引。
2.2.5 模糊查詢(Like)左邊包含%號
下面看兩個查詢，都採用了模糊查詢，但是使用%開頭會造成無法從頁面確定掃描的位置，導致索引無效，繼而全表掃描。
mysql> select * from emp where empname like 'LsHfFJA%';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id | empno | empname | job | mgr | hiredate | sal | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN | 1 | 2021-01-23 16:46:03 | 2000 | 400 | 106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set (0.000 sec)
mysql> select * from emp where empname like '%LsHfFJA%';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id | empno | empname | job | mgr | hiredate | sal | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN | 1 | 2021-01-23 16:46:03 | 2000 | 400 | 106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set (2.034 sec)
上面第一個查詢可以利用到name欄位上面的索引，下面的查詢是無法確定需要查詢的值所在的範圍的，只能全表掃描，無法利用索引，所以速度比較慢，這個過程上面有說過。
2.2.6 條件使用or關鍵字（OR 前後存在非索引的列）
在 WHERE 子句中，OR 前後的條件列不屬於索引列，那麼索引會失效。
以下面的語句為例子，使用And就可以使用到索引，Or就會全表掃描。
原理其實很好理解，使用And的時候，我們在搜尋樹上先找到第一個條件欄位（就是覆蓋索引的depno），然後再縮小範圍查詢mgr欄位。
但如果使用了Or，代表我的條件是兩個，都得搜尋，才能找出所有資料。而未覆蓋索引的那個條件依舊需要全表掃描。
# 語句1：是用到索引
mysql> explain select empno,empname,job from emp where depno=1 and mgr=1;
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ref | idx_depno_empname_job | idx_depno_empname_job | 3 | const | 3705 | 10.00 | Using where |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 語句2：全表掃描
mysql> explain select empno,empname,job from emp where depno=1 or mgr=1;
+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | idx_depno_empname_job | NULL | NULL | NULL | 4982087 | 10.74 | Using where |
+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 語句3：全表掃描
mysql> explain select empno,empname,job from emp where mgr=1 or depno=7 ;
+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | idx_depno_empname_job | NULL | NULL | NULL | 4982087 | 10.74 | Using where |
+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
這種情況的最佳化方式就是在Or的時候兩邊列都覆蓋入索引，就可以避免全表掃描
2.2.7 條件使用in關鍵字
in方式檢索資料，我們還是經常用的。
平時我們做專案的時候，建議少用表連線，比如電商中需要查詢訂單的資訊和訂單中商品的名稱，可以先查詢查詢訂單表，然後訂單表中取出商品的id列表，採用in的方式到商品表檢索商品資訊，由於商品id是商品表的主鍵，所以檢索速度還是比較快的。
透過id在500萬資料中檢索100條資料，看看效果：
mysql> select * from emp a where
a.id in (800000, 800001, 800002, 800003, 800004, 800005, 800006, 800007, 800008, 800009, 800010, 800011, 800012, 800013,
800014, 800015, 800016, 800017, 800018, 800019, 800020, 800021, 800022, 800023, 800024, 800025, 800026, 800027, 800028,
800029, 800030, 800031, 800032, 800033, 800034, 800035, 800036, 800037, 800038, 800039, 800040, 800041, 800042, 800043, 800044,
800045, 800046, 800047, 800048, 800049, 800050, 800051, 800052, 800053, 800054, 800055, 800056, 800057, 800058, 800059, 800060,
800061, 800062, 800063, 800064, 800065, 800066, 800067, 800068, 800069, 800070, 800071, 800072, 800073, 800074, 800075, 800076,
800077, 800078, 800079, 800080, 800081, 800082, 800083, 800084, 800085, 800086, 800087, 800088, 800089, 800090, 800091, 800092,
800093, 800094, 800095, 800096, 800097, 800098, 800099);
+--------+--------+---------+---------+-----+---------------------+------+------+-------+
| id | empno | empname | job | mgr | hiredate | sal | comn | depno |
+--------+--------+---------+---------+-----+---------------------+------+------+-------+
| 800000 | 800000 | qVFqPY | SALEMAN | 1 | 2021-01-23 16:43:02 | 2000 | 400 | 105 |
| 800001 | 800001 | KVzJXL | SALEMAN | 1 | 2021-01-23 16:43:02 | 2000 | 400 | 107 |
| 800002 | 800002 | vWvpkj | SALEMAN | 1 | 2021-01-23 16:43:02 | 2000 | 400 | 102 |
............
| 800099 | 800099 | roxtAx | SALEMAN | 1 | 2021-01-23 16:43:02 | 2000 | 400 | 107 |
+--------+--------+---------+---------+-----+---------------------+------+------+-------+
100 rows in set (0.001 sec)
耗時1毫秒左右，還是相當快的。
這個相當於多個分解為多個唯一記錄檢索，然後將記錄合併。所以這個其實也是快的，只要in裡面的資料不是極端海量的即可。
單次查詢選擇的資料範圍很大，比如佔整個表的30%以上時，MySQL最佳化器可能會認為全表掃描比使用索引更快，因此選擇不使用索引。這是因為全表掃描可能避免了索引查詢和回表的開銷，從而在某些情況下提供了更好的效能。
所以使用 in 在結果集中不能超過30%。
2.2.8 使用 not in 或 not exists
情況同於 2.2.7 節
2.2.9 條件中使用比較演算法
如下程式碼：
- 第一個語句中使用了不等比較 (!= 或者<>) 導致索引失效，不等於需要所有索引資料拿出來比較，所以等同於全表掃描，也是慢的。
- 第二個語句中使用了比較符，雖然走索引，但是掃描資料超過30%，編譯器會認為全表掃描效能比走索引更好，就不走索引了。這點可參考 2.2.7 節
- 第三個語句掃描的資料量（122054）遠低於500w的30%，走索引。查詢執行計劃中包含Using index condition和Using MRR時，意味著正在使用高效的索引和儲存引擎最佳化技術來加速查詢。
# 使用不等比較(!= 或者<>) 導致索引失效
mysql> explain select * from emp where depno <> 7;
+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | idx_depno_empname_job | NULL | NULL | NULL | 4982087 | 52.45 | Using where |
+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 掃描資料量超30%，不走索引
mysql> explain select * from emp where depno > 7;
+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | idx_depno_empname_job | NULL | NULL | NULL | 4982087 | 50.00 | Using where |
+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 掃描資料量少，走索引
mysql> explain select * from emp where depno < 7;
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | emp | NULL | range | idx_depno_empname_job | idx_depno_empname_job | 3 | NULL | 122054 | 100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+--------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
3 總結
總結一下常見失效場景：
- 違反最左匹配原則
- 索引列使用函式計算
- 索引列使用計算表示式
- 索引列進行型別轉換(自動或手動)
- 模糊查詢(Like)左邊包含%號
- 條件使用OR關鍵字，且在 OR 前後存在非索引的列
- 條件使用in關鍵字，且查詢結果超過30%資料比
- 條件使用 not in 或 not exists，且查詢結果超過30%資料比
- 條件中使用不等號（!= 或 <>）
除此之外，還有一些索引覆蓋，規避回表的策略，我們後面的篇章再討論。