目錄
- 1. 前置知識
- 1.1 InnoDB 索引結構
- 2. 什么情況下不能使用索引
- 2.1 索引列在運算式或函式中【必】
- 2.2 聯合索引中，非最左前綴【必】
- 2.3 聯合索引中，最左前綴，但是中間有范圍查詢，那么范圍查詢后面的列都用不到索引【必】
- 2.4 join... on...，主鍵和外鍵的索引資料型別不一致【必】
- 2.5 在無索引的列上使用了 or 那么有索引的列也用不上了【可】
- 2.6 in 查詢陳述句中多個值的資料型別不一致的情況，如：in (1, 2, 3, 4, "5")【必】
- 2.7 in 陳述句中超過 200 個值【可】
1. 前置知識
1.1 InnoDB 索引結構
InnoDB 索引使用的資料結構是 B+ 樹，
百度百科中的結構圖：
一個 m 階 B+ 樹的幾個特點：
- 每個節點可能有最多 m 個子節點
- 除根結點外，每個結點至少有
[m/2]個子女，根結點至少有兩個子女 - 有 k 個子女的結點必有 k 個關鍵字
可以類比字典，通過筆畫找到一個字怎么辦？總不能一頁一頁去翻吧？當然不能，
字典的修訂者會加上字筆畫目錄，只要查清楚字的筆畫數，然后去對應的筆畫目錄下去找就可以了，
咦~ 怎么這個筆畫下面還有這么多字？總不能一頁一頁去翻吧？當然不會，
找到對應的筆畫數之后，目錄下還有部首的目錄，部首的目錄是按照部首筆畫數排序的，查清部首的筆畫數，然后去挨個找部首就行了，
找到部首之后，就會定位到具體的字了，
當然 B+ 樹和字典目錄還是有很多不一樣的地方，只是為了比較好理解
每次搞不懂 B+ 樹的時候，可以想想小時候怎么查字典的，
2. 什么情況下不能使用索引
簡單來說，我們能使用索引進行高效查詢是基于索引的以下特性
- 多級目錄
- 有序性（根據比較規則排序）
- 使用成本較低（成本計算）
2.1 索引列在運算式或函式中【必】
這個很好理解，因為函式會改變索引本身的值，不再具有有序性
2.2 聯合索引中，非最左前綴【必】
聯合索引中，非最左前綴是無序的
2.3 聯合索引中，最左前綴，但是中間有范圍查詢，那么范圍查詢后面的列都用不到索引【必】
a, b, c 三列索引，先按照 a 排序，后按照 b 排序，再按照 c 排序
陳述句 a=1 and b > 1 and c=2 只能使用 a, b 索引進行篩選，c=1 條件需要將前面篩選之后的索引結果逐一比較之后回傳結果，
a 索引過濾之后是有序的，所以可以使用 b 索引進行過濾，b 過濾之后是無序的（也有可能是有序的，但是 innodb 不會再去判斷是否有序）
2.4 join... on...，主鍵和外鍵的索引資料型別不一致【必】
強型別下，資料型別不一致，需要特殊處理才能比較
2.5 在無索引的列上使用了 or 那么有索引的列也用不上了【可】
這個只是有可能，因為 innodb 底層是基于成本選擇使用索引的，
因為在無索引的列上使用 or 會使成本變大，所以很容易無法使用索引，
2.6 in 查詢陳述句中多個值的資料型別不一致的情況，如：in (1, 2, 3, 4, "5")【必】
強型別下，資料型別不一致，需要特殊處理才能比較
但是如果 in 里面都是字串或者都是數字，innodb 的優化器會將其統一轉成索引所需型別，
2.7 in 陳述句中超過 200 個值【可】
在 in 陳述句中 5.7 版本上 超過 200 個值，就會放棄使用 index_dive 方式計算cost，從而導致估算不準確，很容易用不上索引
5.6 以下版本 req_index_dive_limit 為 10，可以酌情修改成 200
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/37873.html
標籤：MySQL
下一篇：珍藏的資料庫SQL基礎練習題答案