全面了解MySQL九種索引失效場景
MySQL索引是提高查詢性能的重要工具，但是如果不合理使用或者不恰當?shù)脑O(shè)計索引，就有可能出現(xiàn)索引失效的情況。本篇博客將介紹九種可能導致MySQL索引失效的場景，并給出相應(yīng)的例子。
1.在索引列上進行函數(shù)操作
如果在索引列上進行函數(shù)操作，MySQL就無法使用該索引。例如，下面的查詢將會導致索引失效：
SELECT?*?FROM?users?WHERE?YEAR(create_time)?=?2022;
可以將查詢條件改為不對索引列進行函數(shù)操作的形式：
SELECT?*?FROM?users?WHERE?create_time?BETWEEN?'2022-01-01?00:00:00'?AND?'2022-12-31?23:59:59';
2.對索引列進行類型轉(zhuǎn)換
如果對索引列進行類型轉(zhuǎn)換，MySQL同樣無法使用該索引。例如，下面的查詢將會導致索引失效：
SELECT?*?FROM?users?WHERE?CAST(id?AS?CHAR)?=?'123';
可以將查詢條件改為不對索引列進行類型轉(zhuǎn)換的形式：
SELECT?*?FROM?users?WHERE?id?=?123;
3.使用LIKE操作符的通配符開頭
如果使用LIKE操作符的通配符開頭，MySQL無法使用索引。例如，下面的查詢將會導致索引失效：
SELECT?*?FROM?users?WHERE?name?LIKE?'%john';
可以將查詢條件改為不使用通配符開頭的形式：
SELECT?*?FROM?users?WHERE?name?LIKE?'john%';
4.對NULL值進行查詢
如果對NULL值進行查詢，MySQL無法使用索引。例如，下面的查詢將會導致索引失效：
SELECT?*?FROM?users?WHERE?name?IS?NULL;
可以將查詢條件改為不對NULL值進行查詢的形式：
SELECT?*?FROM?users?WHERE?name?=?'';
5.使用OR連接多個條件
如果使用OR連接多個條件，MySQL無法使用索引。例如，下面的查詢將會導致索引失效：
SELECT?*?FROM?users?WHERE?name?=?'john'?OR?age?=?18;
可以將查詢條件改為使用UNION ALL連接多個子查詢的形式：
SELECT?*?FROM?users?WHERE?name?=?'john'
UNION?ALL
SELECT?*?FROM?users?WHERE?age?=?18;
6.在ORDER BY子句中對索引列
如果在ORDER BY子句中對索引列進行排序，MySQL可能無法使用該索引。例如，下面的查詢將會導致索引失效：
SELECT?*?FROM?users?WHERE?age?>?18?ORDER?BY?age;
可以將查詢條件改為使用其他列進行排序的形式：
SELECT?*?FROM?users?WHERE?age?>?18?ORDER?BY?create_time;
7.在GROUP BY子句中對索引列進行分組
如果在GROUP BY子句中對索引列進行分組，MySQL可能無法使用該索引。例如，下面的查詢將會導致索引失效：
SELECT?name,?COUNT(*)?FROM?users?GROUP?BY?name;
可以將查詢條件改為使用其他列進行分組的形式：
SELECT?age,?COUNT(*)?FROM?users?GROUP?BY?age;
8.多表關(guān)聯(lián)時使用非索引列進行連接
如果在多表關(guān)聯(lián)時使用非索引列進行連接，MySQL可能無法使用索引。例如，下面的查詢將會導致索引失效：
SELECT?*?FROM?users?JOIN?orders?ON?users.id?=?orders.user_id?WHERE?orders.create_time?>?'2022-01-01';
可以將查詢條件改為使用索引列進行連接的形式：
SELECT?*?FROM?users?JOIN?orders?ON?users.id?=?orders.user_id?WHERE?orders.user_id?>?0?AND?orders.create_time?>?'2022-01-01';
9.最左匹配原則
對于索引列的順序，最左匹配原則是MySQL使用索引的基本原則之一，要求查詢條件中使用的索引列必須按照索引順序的左側(cè)列開始匹配。如果查詢條件中的列沒有按照索引列的順序從左往右進行匹配，MySQL可能無法使用索引，導致索引失效。
如果使用的索引是(name, age)，而查詢條件為WHERE age > 18 AND name = 'Tom'，由于age并不在索引列的最左側(cè)，索引可能會失效，因此需要重新設(shè)計索引或者改變查詢條件。為了遵守最左匹配原則，可以將索引改為(age, name)，這樣就可以避免索引失效了。
綜上所述，MySQL索引失效的場景有很多，開發(fā)者在設(shè)計和使用索引時需要注意避免上述情況的出現(xiàn)，從而提高查詢性能和效率。
end
* 版權(quán)聲明： 轉(zhuǎn)載文章和圖片均來自公開網(wǎng)絡(luò)，版權(quán)歸作者本人所有，推送文章除非無法確認，我們都會注明作者和來源。如果出處有誤或侵犯到原作者權(quán)益，請與我們聯(lián)系刪除或授權(quán)事宜。
長按識別圖中二維碼
關(guān)注獲取更多資訊
不點關(guān)注，我們哪來故事？