MySQL導(dǎo)致索引失效的原因及分析
MySQL什么情況下會(huì)導(dǎo)致索引失效
索引（Index）是數(shù)據(jù)庫中一種用于快速查找和訪問表中數(shù)據(jù)的結(jié)構(gòu)，它類似于書的目錄，通過索引可以快速定位到目標(biāo)數(shù)據(jù)，而無需遍歷整個(gè)表，索引的存在可以顯著提高查詢速度，尤其是在處理大量數(shù)據(jù)時(shí)
有時(shí)我們?yōu)榱吮苊獬霈F(xiàn)回表查詢，我們就會(huì)以多個(gè)字段來創(chuàng)建索引，即覆蓋索引，具體可看：聚簇索引、非聚簇索引、覆蓋索引
使用覆蓋索引最容易遇到的問題就是索引失效問題，那么哪些情況下會(huì)出現(xiàn)索引失效，又該如何避免索引失效呢？
索引命中
以一個(gè)tb_users表為例子：
我們以其中三個(gè)字段建立索引：
CREATE INDEX tb_user_index ON tb_users(name, status,username)
可以利用show語句可以查看我們剛剛為tb_users建立的索引：
SHOW INDEX FROM tb_users
索引字段也是有順序的，順序就是我們創(chuàng)建索引時(shí)的順序，使用explain可以查看SQL語句的執(zhí)行計(jì)劃：
1.單個(gè)條件
EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英'
命中索引的長(zhǎng)度是43，此時(shí)我們的查詢條件只有name，因此命中的索引也只有name
此時(shí)查詢條件中添加一個(gè)status字段：
2.兩個(gè)字段
EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND status = 1
此時(shí)命中索引的長(zhǎng)度為48，表示命中了name和status
再次添加一個(gè)字段：
3.三個(gè)字段條件
EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND status = 1 AND username = 'Joe Edwards'
此時(shí)三個(gè)索引字段全部命中，索引長(zhǎng)度為131
因此命中索引的數(shù)量不一樣，key_len也不一樣，可以利用它來判斷索引是否失效
索引失效
1.違反最左前綴法則會(huì)導(dǎo)致索引失效
指的是查詢從索引的最左前列開始，中間不跳過索引中的列，比如：
EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND username = 'Joe Edwards'
這條查詢語句中，我們用兩個(gè)索引字段作為了查詢條件，那么理論上應(yīng)該命中兩個(gè)索引
但是key_len的值為43，只命中了name字段，username字段索引失效了，因?yàn)橹虚g跳過了索引中的一列status
2.范圍查詢某個(gè)索引，其右邊索引失效
當(dāng)where條件中用索引范圍查詢，這個(gè)索引右邊的字段會(huì)失效
EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND status > 1 AND username = 'Joe Edwards'
這條查詢語句使用了三個(gè)索引條件，理論上key_len為131
但是實(shí)際上命中索引長(zhǎng)度為48，username字段未命中，原因是status使用了范圍查詢，因此他右邊的username失效了
3.在索引列上進(jìn)行運(yùn)算操作，會(huì)導(dǎo)致索引失效
EXPLAIN SELECT * FROM tb_users WHERE SUBSTRING(0,1,name) = '崔秀英'
這里對(duì)name字段進(jìn)行了截取運(yùn)算操作
因此name字段并未命中，導(dǎo)致索引失效
4.字符串不加單引號(hào)，導(dǎo)致索引失效
比如說status字段，上表中我們使用的是int類型，假如我們使用了varchar類型，在查詢的時(shí)候就要加單引號(hào)，如：
EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND status = '1'
如果沒有加單引號(hào)，MySQL就會(huì)進(jìn)行多余的類型轉(zhuǎn)換步驟，該步驟會(huì)導(dǎo)致索引失效
5.以%開頭的like模糊查詢，會(huì)導(dǎo)致索引失效
如果僅僅是尾部模糊匹配，索引不會(huì)失效，如果是頭部模糊匹配，索引會(huì)失效
EXPLAIN SELECT * FROM tb_users WHERE name LIKE '%秀英'
可以看到索引命中長(zhǎng)度為null，索引失效了
總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
大家應(yīng)該都知道LIMIT子句可以被用于強(qiáng)制SELECT語句返回指定的記錄數(shù),這篇文章主要給大家介紹了關(guān)于MySQL中LIMIT語句的相關(guān)資料,需要的朋友可以參考下2021-10-10
這篇文章主要介紹了mysql 安全管理，MySQL服務(wù)器的安全基礎(chǔ)是用戶應(yīng)該對(duì)他們需要的數(shù)據(jù)具有適當(dāng)?shù)脑L問權(quán)，既不能多也不能少，換句話說，用戶不能對(duì)過多的數(shù)據(jù)具有過多的訪問權(quán)，下面?zhèn)z看看文章詳細(xì)內(nèi)容吧2021-10-10
這篇文章主要介紹了MySQL中UPDATE語句使用的實(shí)例教程,包括UPDATE的使用中所容易引起的性能問題的分析,需要的朋友可以參考下2015-11-11
MySQL數(shù)據(jù)庫操作DQL正則表達(dá)式
這篇文章主要介紹了MySQL數(shù)據(jù)庫操作DQL正則表達(dá)式,正則表達(dá)式描述了一種字符串匹配的規(guī)則，正則表達(dá)式本身就是一個(gè)字符串，使用這個(gè)字符串來描述、用來定義匹配規(guī)則，匹配一系列符合某個(gè)句法規(guī)則的字符串2022-07-07
這篇文章主要介紹了MySQL顯式類型轉(zhuǎn)換的基礎(chǔ)知識(shí)，通過實(shí)例給出了分析結(jié)果，一起學(xué)習(xí)下吧。2017-12-12
這篇文章主要介紹了OEL7.6源碼安裝MYSQL5.7 的教程，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-07-07
MySQL是支持前綴索引的,也就是說你可以定義字符串的一部分作為索引,下面這篇文章主要給大家介紹了關(guān)于MySQL前綴索引的相關(guān)資料,需要的朋友可以參考下2022-04-04
根據(jù)mysql慢日志監(jiān)控SQL語句執(zhí)行效率
根據(jù)mysql慢日志監(jiān)控SQL語句執(zhí)行效率 啟用MySQL的log-slow-queries(慢查詢記錄)。2012-11-11
Mysql使用函數(shù)json_extract處理Json類型數(shù)據(jù)的方法實(shí)例
在日常業(yè)務(wù)開發(fā)中通常mysql數(shù)據(jù)庫中某個(gè)字段會(huì)需要存儲(chǔ)json格式字符串,下面這篇文章主要給大家介紹了關(guān)于Mysql使用函數(shù)json_extract處理Json類型數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下2022-09-09
MySql中取前幾行數(shù)據(jù)使用limit來完成
在mysql中是沒有top關(guān)鍵字的，不過可以用limit來完成此功能，下面舉例為大家詳細(xì)介紹下它的使用方法，不會(huì)的朋友可以學(xué)習(xí)下2013-07-07