1. 首頁 > 生活百科 > >

MySQL 索引失效-模糊查詢，最左匹配原則，OR條件等。

生活百科

索引失效

介紹
索引失效就是我們明明在查詢時的條件為索引列（包括自己新建的索引），但是索引不能起效，走的是全表掃描 。explain 后可查看type=ALL 。
這是為什么呢？
首先介紹有以下幾種情況索引會出現失效：
  • 當我們使用了左模糊匹配和左右模糊匹配的時候，像like ‘%str’或者‘%str%’ 。
  • 當我們使用聯合索引沒有遵守最左匹配原則的時候 。
  • 當我們使用索引時對其索引字段進行計算、函數、類型轉換的操作 。
  • 當我們在where條件子句中使用了OR運算，同時OR前為索引列，OR后的條件不是索引列 。以上幾種情況均會引起索引失效 。
下面我們來談談具體的原因和其中的細節
1、第一種情況：左模糊匹配和左右模糊匹配我們都知道在mysql中innodb存儲引擎會對我們的索引以B+樹存儲 。
InnoDB 存儲引擎根據索引類型不同 ， 分為聚簇索引（主鍵索引）和二級索引 。它們區別在于 ， 聚簇索引的葉子節點存放的是實際數據，所有完整的用戶數據都存放在聚簇索引的葉子節點，而二級索引的葉子節點存放的是主鍵值，而不是實際數據 。
主鍵索引一般來說葉子結點存儲的都是數據本身 。二級索引一般來說葉子結點存儲的都是數據的物理地址 。
因為索引 B+ 樹是按照「索引值」有序排列存儲的，只能根據前綴進行比較 。
下面看看轉載：
來自：[小林Code][https://mp.weixin.qq.com/s/lEx6iRRP3MbwJ82Xwp675w]
MySQL 索引失效-模糊查詢，最左匹配原則，OR條件等。

文章插圖
索引存儲結構長什么樣？我們先來看看索引存儲結構長什么樣？因為只有知道索引的存儲結構，才能更好的理解索引失效的問題 。
索引的存儲結構跟 MySQL 使用哪種存儲引擎有關，因為存儲引擎就是負責將數據持久化在磁盤中，而不同的存儲引擎采用的索引數據結構也會不相同 。
MySQL 默認的存儲引擎是 InnoDB，它采用 B+Tree 作為索引的數據結構，至于為什么選擇B+ 樹作為索引的數據結構 ，詳細的分析可以看我這篇文章：為什么 MySQL 喜歡 B+ 樹？
在創建表時，InnoDB 存儲引擎默認會創建一個主鍵索引 ， 也就是聚簇索引，其它索引都屬于二級索引 。
MySQL 的 MyISAM 存儲引擎支持多種索引數據結構，比如 B+ 樹索引、R 樹索引、Full-Text 索引 。MyISAM 存儲引擎在創建表時 ， 創建的主鍵索引默認使用的是 B+ 樹索引 。
雖然，InnoDB 和 MyISAM 都支持 B+ 樹索引 ， 但是它們數據的存儲結構實現方式不同 。不同之處在于：
  • InnoDB 存儲引擎：B+ 樹索引的葉子節點保存數據本身；
  • MyISAM 存儲引擎：B+ 樹索引的葉子節點保存數據的物理地址；
接下來，我舉個例子，給大家展示下這兩種存儲引擎的索引存儲結構的區別 。
這里有一張 t_user 表，其中 id 字段為主鍵索引，其他都是普通字段 。
MySQL 索引失效-模糊查詢，最左匹配原則，OR條件等。

文章插圖
如果使用的是 MyISAM 存儲引擎，B+ 樹索引的葉子節點保存數據的物理地址 ， 即用戶數據的指針，如下圖：
MySQL 索引失效-模糊查詢，最左匹配原則，OR條件等。

文章插圖
如果使用的是 InnoDB 存儲引擎， B+ 樹索引的葉子節點保存數據本身，如下圖所示：
MySQL 索引失效-模糊查詢，最左匹配原則，OR條件等。

文章插圖
InnoDB 存儲引擎根據索引類型不同，分為聚簇索引（上圖就是聚簇索引）和二級索引 。它們區別在于，聚簇索引的葉子節點存放的是實際數據，所有完整的用戶數據都存放在聚簇索引的葉子節點，而二級索引的葉子節點存放的是主鍵值，而不是實際數據 。
如果將 name 字段設置為普通索引，那么這個二級索引長下圖這樣，葉子節點僅存放主鍵值 。
MySQL 索引失效-模糊查詢，最左匹配原則，OR條件等。

文章插圖
知道了 InnoDB 存儲引擎的聚簇索引和二級索引的存儲結構后，接下來舉幾個查詢語句 ， 說下查詢過程是怎么選擇用哪個索引類型的 。
在我們使用「主鍵索引」字段作為條件查詢的時候，如果要查詢的數據都在「聚簇索引」的葉子節點里，那么就會在「聚簇索引」中的 B+ 樹檢索到對應的葉子節點，然后直接讀取要查詢的數據 。如下面這條語句：
// id 字段為主鍵索引select * from t_user where id=1;在我們使用「二級索引」字段作為條件查詢的時候，如果要查詢的數據都在「聚簇索引」的葉子節點里，那么需要檢索兩顆B+樹：

推薦閱讀