MySQL細數發生索引失效的情況實例分析

索引的儲存結構

先了解索引的儲存結構，知道了索引的儲存結構，才方便我們更能理解索引失效的問題。

索引的儲存結構跟MySQL的儲存引擎有關，儲存引擎的不同採用的結構也會不同。

MySQL預設的儲存引擎InnoDB採用B Tree作為索引的資料結構，在建立資料表時，InnoDB會預設建立一個主鍵索引，這是一個聚集索引，其他索引都屬於二級索引。

MyISAM儲存引擎在建立表格時，預設是用的是B 樹索引。

雖然和InnoDB一樣都支援B 樹索引，但是他們儲存資料的方式不同；

InnoDB是叢集索引（B 樹索引的葉子結點保存資料本身）

MyISAM是非聚集索引（B 樹的葉子結點保存資料的實體位址）

如下圖所示：

InnoDB儲存引擎可以分為【聚集索引】和【二級索引】，它們的差異在於聚集索引的葉子結點存放的是實際數據，所有完整的數據都存放在叢集索引的葉子結點，二級索引的葉子結點存放的是主鍵值。

在使用二級索引欄位作為查詢條件，查詢資料在叢集索引上的時候，

會先根據條件在二級索引上找到對應的葉子結點得到主鍵值，

再根據主鍵值去叢集索引上找到對應的葉子結點然後查詢到對應的數據，

這個過程叫回表

#使用二級索引作為查詢條件，查詢的數據在二級索引的葉子結點上的時候，那麼只需找到二級索引的B 樹對應的葉子結點，讀取數據，這個過程叫做覆蓋索引

上面這些查詢條件都用到了索引列，但並不表示用到索引列索引就一定會生效，我們再來看一看索引失效的情況

不合理的模糊查詢條件

使用左或左右模糊查詢的時候，也就是like "%張"或like "%張%"這兩種模糊查詢方式都會導致索引失效

因為B 樹是根據索引值進行排列的，前綴不確定的時候可能是，“小張”，"二張"之類的所有的情況，就只能透過全表掃描的方式來查詢

對索引使用函數

#例如：SELECT * FROM sys_user WHERE LENGTH(user_id) = 3 ;

因為索引保存的是索引欄位的原始值，而不是經過函數計算後的值，所以使用函數的時候就不會走索引了

不過從MySQL8.0開始，索引特性增加了函數索引，也就是針對該函數計算後的值建立一個索引，這樣就可以透過掃描索引來查詢資料了；

alter table t_user add key idx_name_length ((length(name)));

對索引進行表達式計算

例如：select * from sys_user where user_id 1 =3;

##但是如果是

SELECT * FROM sys_user WHERE user_id = 1 1 ;這樣的不在索引欄位上進行計算，就又會走索引了

原因跟對索引使用函數差不多，索引保存的是索引欄位的原始值，而不是運算後的值，所以無法走索引

#對索引使用隱式轉換

這裡的

phone欄位是二級索引，且是varchar型別的

#使用整數型作為查詢參數的時候，執行計畫中type為ALL，也就是透過全表掃描查詢的，但如果是字串類型，還是走索引查詢的

我們再看一個例子

這裡

user_id是bigint類型，但是使用字串作為查詢參數還是走了索引的

#

为什么第一个例子导致了索引失效，而第二个不会呢？

这里就要了解一下MySQL的字符转换规则了，看是数字转字符串，还是字符串转数字

我们可以用select "10">9来测试一下

如果是数字转字符串，那么就相当于select "10">"9"结果应该是0

如果是字符串转数字，那么就相当于select 10>9，结果是1

在MySQL中的执行结果如下：

这就说明，MySQL在遇到数字与字符串的比较的时候，会自动把字符串转换为数字，然后进行比较

也就是说，在第一个例子中

SELECT * FROM sys_user WHERE phone = 18200000000 ;

相当于

SELECT * FROM sys_user WHERE CAST(phone AS UNSIGNED) = 18200000000 ;

这就在索引字段上使用了函数，所以导致索引失效

而在第二个例子中

SELECT * FROM sys_user WHERE user_id = "1" ;

相当于

SELECT * FROM sys_user WHERE user_id = CAST("1" AS UNSIGNED) ;

函数式作用在查询参数上的，并没有作用在索引字段上，所以还是走索引的

联合索引非最左匹配

多个普通字段组合在一起创建的索引叫做联合索引（组合索引）

在使用联合索引的时候，一定要注意顺序问题，联合索引的使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引匹配。

例如，创建了一个(a,b,c)联合索引，那么如果查询条件是一下几种，就可以匹配上联合索引

where a = 1
where a = 1 and b = 2
where a = 1 and b = 2 and c = 3

需要注意的是，因为有查询优化器，所以a字段在where子句中的顺序不重要

若缺少a字段，则以下几种情况由于不符合最左匹配原则将无法匹配联合索引，导致该联合索引失效

where b = 2
where c = 3
where b = 2 and c = 3

还有一个比较特殊的查询条件：where a = 1 and c = 3

在MySQL5.5的话，前面的a 会走索引，在联合索引找到主键值，然后回表，到主键索引读取数据行，然后在比对c字段的值

在MySQL5.6之后，有一个索引下推的功能，

下推就是将部分上层(服务层)负责的事情，交给了下层(引擎层)处理

存储引擎直接在联合索引里按照c=3过滤，按照过滤后的数据在进行回表扫描，减少了回表的次数，从而提升了性能

在执行计划中Extra = Using index condition就表示使用了索引下推

联合索引不遵循最左匹配原则的原因：在联合索引中，数据按照第一列索引进行排序，第一列数据相同时，才会按照第二列进行排序，以此类推，所以直接使用第二列进行查询的时候，联合索引就会失效

where子句中的or

where子句中or的条件列有不是索引列会导致索引失效

例如：下图中id是索引列，email不是索引列，从执行计划来看，进行了全文扫描并没有使用到索引

因为or关键字只满足一个条件就可以，因此只要有一个列不是索引列，其他索引列也就没有意义了，就会进行全表扫描

在email列上建立索引之后，可以看到执行计划中使用到了两个索引

type = index_merge表示对id 和email都进行了扫描，然后进行了合并

以上是MySQL細數發生索引失效的情況實例分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！