mysql索引失效的幾種情況

一、索引失效的七種情況

在討論mysql索引失效的幾種情況之前，首先需要了解索引失效的七種情況。這七種情況包括：

1、全值匹配未使用索引

SELECT * FROM `users` WHERE `name` = 'John'

上面這個例子中，如果用戶表的name欄位沒有建立索引，那麼查詢將會全表掃描，導致索引失效。

2、最左前綴原則被破壞

SELECT * FROM `users` WHERE `name` LIKE '%John%'

上面這個例子中，如果用戶表的name欄位建立了索引，但是like操作中使用了通配符，那麼只有最左前綴能夠匹配，索引會失效。

3、範圍查詢左側未使用索引

SELECT * FROM `users` WHERE `age` > 20

上面這個例子中，如果用戶表的age欄位建立了索引，但是使用了大於號（或小於號），那麼左側的索引將不會被使用，導致索引失效。

4、使用函數或表達式索引失效

SELECT * FROM `users` WHERE YEAR(create_time) = 2021;

上面這個例子中，如果用戶表的create_time欄位建立了索引，但是在查詢中使用了函數或表達式（如YEAR函數），那麼索引將不會被使用，導致索引失效。

5、索引列上有計算

SELECT * FROM `users` WHERE `age` * 2 = 40

上面這個例子中，如果用戶表的age欄位建立了索引，但是在查詢中使用了計算（如乘以2），那麼索引將不會被使用，導致索引失效。

6、不同類型之間的比較

SELECT * FROM `users` WHERE `age` = '20'

上面這個例子中，如果用戶表的age欄位是integer類型，但是在查詢中使用了字元串類型（如’20’），那麼索引將不會被使用，導致索引失效。

7、索引需要被強制類型轉換

SELECT * FROM `users` WHERE CAST(`age` AS CHAR) = '20'

上面這個例子中，如果用戶表的age欄位是integer類型，但是在查詢中使用了char類型，那麼索引將不會被使用，導致索引失效。

二、mysql索引失效的情況

在理解索引失效的七種情況的基礎上，我們可以來看一些mysql索引失效的情況。

1、全文檢索

mysql提供了全文檢索（FULLTEXT）功能，但是全文檢索不支持LIKE、通配符、正則等操作，因此如果使用了這些操作，就會導致索引失效。

SELECT * FROM `articles` WHERE MATCH(`title`) AGAINST('hello mysql' IN NATURAL LANGUAGE MODE);

2、聯合索引未使用最左前綴

在使用聯合索引時，需要注意最左前綴原則。如果不是按照最左前綴的順序使用索引，那麼將會導致索引失效。

SELECT * FROM `users` WHERE `name` = 'John' AND `age` = 20;

上面這個例子中，如果用戶表使用了聯合索引（name, age），但是查詢時只使用了name欄位，那麼age欄位的索引將不會被使用，導致索引失效。

3、多表關聯查詢

在進行多表關聯查詢時，如果其中一張表沒有建立索引，那麼將會導致索引失效。

SELECT `users`.`name`, `articles`.`title` FROM `users` JOIN `articles` ON `users`.`id` = `articles`.`user_id` WHERE `users`.`name` = 'John';

上面這個例子中，如果用戶表（users）的name欄位有索引，但是文章表（articles）的user_id欄位沒有索引，那麼查詢將會進行全表掃描，導致索引失效。

三、mysql中索引失效的情況

除了上面提到的情況外，還有一些特殊情況也會導致mysql中索引失效。

1、欄位類型

在mysql中，欄位類型對於索引的使用非常重要。如果使用了不合適的欄位類型，那麼會導致索引失效。

例如，將字元串類型的欄位作為聯合索引的第一項，將會比整數類型的欄位慢很多。

2、數據分布的不均勻

如果數據分布不均勻，那麼索引失效的可能性將會增加。

例如，當一個表中有大量的相同數據時，如所有用戶的性別都是男性，那麼查詢性別為女性的記錄將會導致索引失效，因為大部分數據都是男性。

3、表中數據量很少

如果表中的數據量非常少，那麼索引失效的可能性也會增加。

這是因為當數據量很少時，全表掃描的代價非常小，因此mysql可能會選擇進行全表掃描而不是使用索引。

四、mysql的索引失效選取

為了避免索引失效，需要合理地選取索引。以下是一些選取索引的方法。

1、聯合索引

在使用聯合索引時，需要按照最左前綴的順序使用索引。

例如，對於聯合索引(name, age)，如果只查詢name欄位，那麼age欄位的索引將不會被使用，因為它不是最左前綴。

2、優先選擇前綴索引

如果只需要匹配字元串的前綴，那麼建立前綴索引可能會更加高效。

ALTER TABLE `users` ADD INDEX `name` (`name`(10));

上面這個例子中，為name欄位建立長度為10的前綴索引。

3、擴展索引的使用範圍

如果一個索引可以被多個查詢所使用，那麼可以考慮對這個索引進行擴展，以便可以更加高效地使用。

例如，對於索引(name, age)，如果查詢中只使用了name欄位，那麼可以將age欄位加入到索引中，以便在查詢時可以更加高效地使用索引。

ALTER TABLE `users` ADD INDEX `name_age` (`name`, `age`);

4、避免使用函數或表達式

在查詢時，盡量避免使用函數或表達式。如果必須使用，可以考慮將函數或表達式的結果存儲到另一個欄位中，以便可以使用索引。

5、避免使用大範圍的範圍查詢

在進行範圍查詢時，盡量避免大範圍的範圍查詢。如果必須使用，可以對被查詢的欄位建立單獨的索引。

6、注意選擇合適的欄位類型

在創建表時，需要注意選擇合適的欄位類型。如果選擇不合適的欄位類型，那麼可能會導致索引失效。

例如，在使用字元串類型時，需要注意字符集、長度等因素。

7、數據分布均勻

為了避免索引失效，需要保證數據的分布均勻。如果數據分布不均勻，可以考慮將數據拆分到多個表中，或者使用分區表來避免數據傾斜。

8、避免多表關聯查詢

在進行多表關聯查詢時，需要注意多表關聯的複雜度。如果關聯的表比較多，或者關聯的表中有表沒有建立索引，那麼將會導致索引失效。

因此，需要盡量避免多表關聯查詢，或者將多表關聯查詢拆分成多次的單表查詢。

總結

在mysql中，索引是優化查詢性能的重要手段。然而，如果使用不當，也會導致索引失效，進而降低查詢性能。因此，在創建索引時需要注意選取合適的欄位、遵守最左前綴原則、避免大範圍的範圍查詢、避免使用函數或表達式、保證數據分布均勻等因素。