共計 5706 個字符，預計需要花費 15 分鐘才能閱讀完成。
本篇內容主要講解“索引失效的原因是什么”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“索引失效的原因是什么”吧!
MySQL 數據是如何存儲的?
聚集索引
我們先建如下的一張表
CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 學號 , `name` varchar(10) NOT NULL COMMENT 學生姓名 , `age` int(11) NOT NULL COMMENT 學生年齡 , PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB;插入如下 sql
insert into student (`name`, `age`) value(a , 10); insert into student (`name`, `age`) value(c , 12); insert into student (`name`, `age`) value(b , 9); insert into student (`name`, `age`) value(d , 15); insert into student (`name`, `age`) value(h , 17); insert into student (`name`, `age`) value(l , 13); insert into student (`name`, `age`) value(k , 12); insert into student (`name`, `age`) value(x , 9);數據如下
圖片 mysql 是按照頁來存儲數據的，每個頁的大小為 16k。
在 MySQL 中可以通過執行如下語句，看到一個頁的大小
show global status like innodb_page_size結果為 16384，即 16kb
在 InnoDB 存儲引擎中，是以主鍵為索引來組織數據的。記錄在頁中按照主鍵從小到大的順序以單鏈表的形式連接在一起。
可能有小伙伴會問，如果建表的時候，沒有指定主鍵呢?
如果在創建表時沒有顯示的定義主鍵，則 InnoDB 存儲引擎會按如下方式選擇或創建主鍵。
首先判斷表中是否有非空的唯一索引，如果有，則該列即為主鍵。如果有多個非空唯一索引時，InnoDB 存儲引擎將選擇建表時第一個定義的非空唯一索引作為主鍵
如果不符合上述條件，InnoDB 存儲引擎自動創建一個 6 字節大小的指針作為索引
頁和頁之間以雙鏈表的形式連接在一起。并且下一個數據頁中用戶記錄的主鍵值必須大于上一個數據頁中用戶記錄的主鍵值
假設一個頁只能存放 3 條數據，則數據存儲結構如下。
可以看到我們想查詢一個數據或者插入一條數據的時候，需要從最開始的頁開始，依次遍歷每個頁的鏈表，效率并不高。
我們可以給這頁做一個目錄，保存主鍵和頁號的映射關系，根據二分法就能快速找到數據所在的頁。但這樣做的前提是這個映射關系需要保存到連續的空間，如數組。如果這樣做會有如下幾個問題
隨著數據的增多，目錄所需要的連續空間越來越大，并不現實
當有一個頁的數據全被刪除了，則相應的目錄項也要刪除，它后面的目錄項都要向前移動，成本太高
我們可以把目錄數據放在和用戶數據類似的結構中，如下所示。目錄項有 2 個列，主鍵和頁號。
數據很多時，一個目錄項肯定很多，畢竟一個頁的大小為 16k，我們可以對數據建立多個目錄項目，在目錄項的基礎上再建目錄項，如下圖所示
這其實就是一顆 B + 樹，也是一個聚集索引，即數據和索引在一塊。葉子節點保存所有的列值
以 InnoDB 的一個整數字段索引為例，這個 N 差不多是 1200。這棵樹高是 4 的時候，就可以存 1200 的 3 次方個值，這已經 17 億了。考慮到樹根的數據塊總是在內存中的，一個 10 億行的表上一個整數字段的索引，查找一個值最多只需要訪問 3 次磁盤。其實，樹的第二層也有很大概率在內存中，那么訪問磁盤的平均次數就更少了。
非聚集索引
聚集索引和非聚集索引非常類似，區別如下
聚集索引葉子節點的值為所有的列值非聚集索引葉子節點的值為索引列 + 主鍵
當我們查詢 name 為 h 的用戶信息時 (學號，姓名，年齡)，因為 name 上建了索引，先從 name 非聚集索引上，找到對應的主鍵 id，然后根據主鍵 id 從聚集索引上找到對應的記錄。
從非聚集索引上找到對應的主鍵值然后到聚集索引上查找對應記錄的過程為回表
聯合索引 / 索引覆蓋
假設 teacher 表定義如下，在 name 和 age 列上建立聯合索引
CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 教師編號 , `name` varchar(10) NOT NULL COMMENT 教師姓名 , `age` int(11) NOT NULL COMMENT 教師年齡 , `ismale` tinyint(3) NOT NULL COMMENT 是否男性 , PRIMARY KEY (`id`), KEY `idx_name_age` (`name`, `age`) ) ENGINE=InnoDB;插入如下 sql
insert into teacher (`name`, `age`, `ismale`) value(aa , 10, 1); insert into teacher (`name`, `age`, `ismale`) value(dd , 12, 0); insert into teacher (`name`, `age`, `ismale`) value(cb , 9, 1); insert into teacher (`name`, `age`, `ismale`) value(cb , 15, 1); insert into teacher (`name`, `age`, `ismale`) value(bc , 17, 0); insert into teacher (`name`, `age`, `ismale`) value(bb , 15, 1); insert into teacher (`name`, `age`, `ismale`) value(dd , 15, 1); insert into teacher (`name`, `age`, `ismale`) value(dd , 12, 0);對 name 和 age 列建立聯合索引
目錄頁由 name 列，age 列，頁號這三部分組成。目錄會先按照 name 列進行排序，當 name 列相同的時候才對 age 列進行排序。
數據頁由 name 列，age 列，主鍵值這三部分組成。同樣的，數據頁會先按照 name 列進行排序，當 name 列相同的時候才對 age 列進行排序。
當執行如下語句的時候，會有回表的過程
select * from student where name = aa當執行如下語句的時候，沒有回表的過程
select name, age from student where name = aa為什么不需要回表呢?
因為 idx_name_age 索引的葉子節點存的值為主鍵值，name 值和 age 值，所以從 idx_name_age 索引上就能獲取到所需要的列值，不需要回表，即索引覆蓋
仔細看一下聯合索引這個圖，你就基本上能明白為什么不滿足最左前綴原則的索引會失效?
索引下推
當執行如下語句的時候
select * from student where name like 張 % and age = 10 and ismale = 1;在 5.6 版本之前的執行過程如下，先從 idx_name_age 索引上找到對應的主鍵值，然后回表找到對應的行，判斷其他字段的值是否滿足條件
在 5.6 引入了索引下推優化，可以在遍歷索引的過程中，對索引中包含的字段做判斷，直接過濾掉不滿足條件的數據，減少回表次數，如下圖
最左前綴原則
加速查詢
主要針對組合索引，滿足如下 2 個條件即可滿足左前綴原則
需要查詢的列和組合索引的列順序一致
查詢不要跨列
構造數據如下，其中在 name，address，country 上建了聯合索引
CREATE TABLE `people` ( `name` varchar(50) NOT NULL, `address` varchar(50) NOT NULL, `country` varchar(50) NOT NULL, KEY `idx_name_addr_country` (`name`,`address`,`country`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;舉幾個例子，下面涉及到一些 explain 相關的知識，后面單開一篇長文來做介紹
例子一
explain select * from people where name = jack and address = beijing and country = china
type 為 ref，key_len 為 456=(50*3+2)*3，聯合索引的所有列都使用了
例子二
explain select * from people where name = jack
type 為 ref，key_len 為 152=50*3+2，聯合索引只使用了 name 列
例子三
explain select * from people where address = beijing
type 為 index，表明查詢的時候對整個索引進行了掃描，并沒有加速查找。
假設有如下的聯合索引 key idx_a_b_c(a,b,c)
sql 是否使用索引 where a = x and b = x and c = x 是 where a = x and b = x 是，部分索引 where a = x 是，部分索引 where b = x 否，不包含最左列 namewhere b = x and c = x 否，不包含最左列 name
如果你仔細看了前面聯合索引是如何存儲的，那你一定能看懂是否使用索引的介紹
目錄頁是按照 a b c 列的順序依次遞增排序的。先按照 a 列排序，如果 a 列相同，再按照 b 列排序，如果 b 列相同，才按照 c 列排序
所以查詢列值 a b c，則這個排序規則能用到，即會走索引。如果只查列值 b，并不能用到這個排序規則，所以得遍歷所有的記錄
加速排序
最左前綴原則不僅用在查詢中，還能用在排序中。MySQL 中，有兩種方式生成有序結果集：
通過有序索引順序掃描直接返回有序數據
Filesort 排序，對返回的數據進行排序
因為索引的結構是 B + 樹，索引中的數據是按照一定順序進行排列的，所以在排序查詢中如果能利用索引，就能避免額外的排序操作。EXPLAIN 分析查詢時，Extra 顯示為 Using index。
所有不是通過索引直接返回排序結果的操作都是 Filesort 排序，也就是說進行了額外的排序操作。EXPLAIN 分析查詢時，Extra 顯示為 Using filesort，當出現 Using filesort 時對性能損耗較大，所以要盡量避免 Using filesort
還是先舉 2 個例子，然后總結
explain select * from people order by name
Extra 列只有 Using index，即根據索引順序進行掃描
explain select * from people order by address
在這里插入圖片描述
Extra 列有 Using filesort
總結：假如說有如下聯合索引，key idx_a_b_c(a,b,c)
order by 能使用索引排序
order by a order by a,b order by a,b,c order by a desc, b desc, c desc where a = const order by b,c where a = const and b = const order by c where a = const and b const order by b,corder by 不能使用索引進行排序
order by b order by c order by b, c order by a asc, b desc, c desc // 排序不一致 where g = const order by b,c // 丟失 a 索引 where a = const order by c // 丟失 b 索引 where a = const order by a,d // d 不是索引的一部分 where a in (...) order by b,c // 范圍查詢 這個原因就不用我解釋了把，相信你一定看懂了
聯合索引的好處
索引覆蓋，減少了很多回表的操作，提高了查詢的效率
索引下推，索引列越多，通過索引篩選出的數據越少。有 1000W 條數據的表，有如下 sql:select * from table where col1=1 and col2=2 and col3=3, 假設假設每個條件可以篩選出 10% 的數據，如果只有單值索引，那么通過該索引能篩選出 1000W10%=100w 條數據，然后再回表從 100w 條數據中找到符合 col2=2 and col3= 3 的數據; 如果是聯合索引，通過索引篩選出 1000w*10%*10% *10%=1w，效率提升可想而知!
索引為什么會失效?
當別人問我索引在什么條件下會失效時，我能背出一大堆規則
不要在索引列上進行運算或使用函數
前導模糊查詢不會使用索引，例如 like % 李
負向條件索引不會使用索引，建議用 in。負向條件有：!=、、not in、not exists、not like 等
索引是按照一定規則排好序的，如果對索引列使用函數，或者 like % 李，具體的值都不知道，它怎么在 B + 樹上加速查詢?
到此，相信大家對“索引失效的原因是什么”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
