mysql索引失效的幾種情況及分析
索引的類型
針對InnoDB
- 聚簇索引 ：主鍵索引，b+樹葉子節(jié)點(diǎn)指向的是真實(shí)數(shù)據(jù)；
- 非聚簇索引：非主鍵索引，b+樹葉子節(jié)點(diǎn)存儲的是聚簇索引和非聚簇索引值；
全值索引 最佳左前綴法則失效
最佳左前綴法則：如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。
但常量查詢時(shí)，mysql會對搜索條件順序進(jìn)行優(yōu)化
index（a,b,c） where a= “a” and c= “c” and b = “b” 可以使用索引 where a= “a” order b,c 可以用到索引 where a= “a” order c,b 無法使用 where a= “a” order b,c 可以使用索引 where a= “a” and b = “b” order c,b 可以使用索引，因?yàn)閎此時(shí)已經(jīng)為常量； where b=“b” 或者 where b=“b” and c=“c” 或者 where c =“c” ，無法使用索引，因?yàn)樘^了a列；
group by 基本上都需要進(jìn)行排序，會有臨時(shí)表產(chǎn)生。
不在索引列上做任何操作
（計(jì)算、函數(shù)、自動or手動類型轉(zhuǎn)換），會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。
存儲引擎不能使用索引中范圍條件右邊的列
在范圍條件之后索引全失效
盡量使用覆蓋索引
mysql在使用不等于的時(shí)候無法使用索引，會導(dǎo)致全表掃描
is null 和is not null 也無法使用索引
like以通配符開頭mysql索引時(shí)效會變成全表掃描
加在右邊是范圍查詢；加在左邊是全表掃描；對于必須使用兩邊百分號的數(shù)據(jù)，使用覆蓋索引解決，查詢字段與索引字段個(gè)數(shù)順序一致；
index（a,b,c） where a= “a” and b like"b%" and c=“c” ,可以使用索引，使用到a,b,c where a= “a” and b like"%b" and c=“c” ,可以使用索引，只使用到a where a= “a” and b like"%b%" and c=“c” ,可以使用索引，只使用到a where a= “a” and b like"b%b%" and c=“c” ,可以使用索引，使用到a,b,c
字符串不加單引號索引失效
使用了mysql隱式的類型轉(zhuǎn)換
少用or，用它來連接時(shí)會索引失效
左右連接查詢索引建立方式
- 左連接，加右表外鍵索引；因?yàn)槭怯勺筮B接特性決定的，left join 條件用于確定如何從右表搜索行，左邊一定有，而右邊是我們的關(guān)鍵點(diǎn)。
- 右連接，加左表外鍵索引；右連接相反同上。
一般性建議
對于單建索引，盡量選擇針對當(dāng)前query過濾性更好的索引
在選擇組合索引的時(shí)候，當(dāng)前query中過濾性最好的字段在索引字段順序中，位置越靠前越好
在選擇組合索引的時(shí)候，盡量選擇可以包含當(dāng)前query中的where字句中更多字段的索引；
盡可能通過分析統(tǒng)計(jì)信息和調(diào)整query的寫法來達(dá)到選擇合適索引的目的
總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要介紹了MySQL事務(wù)的隔離級別與MVCC的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-04-04
MySQL數(shù)據(jù)庫遠(yuǎn)程訪問權(quán)限如何打開(兩種方法)
本文通過兩種方法給大家介紹MySQL數(shù)據(jù)庫遠(yuǎn)程訪問權(quán)限的打開方法，非常不錯(cuò)，實(shí)用性非常高，感興趣的朋友一起看看吧2016-05-05
這篇文章主要介紹了MySQL的左連接、右連接、內(nèi)連接用法,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-03-03
這篇文章主要介紹了MySQL的事件調(diào)度器使用介紹,本文講解了事件調(diào)度器的開啟、創(chuàng)建、修改、刪除等操作的使用實(shí)例,需要的朋友可以參考下2015-06-06
這篇文章主要介紹了MySQL存儲過程參數(shù)的用法及說明，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-08-08
淺析MySQL如何實(shí)現(xiàn)百萬級數(shù)據(jù)的高效查詢
在當(dāng)下的數(shù)據(jù)庫技術(shù)背景下,處理 MySQL 百萬級數(shù)據(jù)的查詢需要綜合考慮數(shù)據(jù)庫設(shè)計(jì)、查詢優(yōu)化、硬件配置和高級技術(shù),下面我們就來看看具體的實(shí)現(xiàn)方案吧2025-07-07
MySQL中的系統(tǒng)庫(sys系統(tǒng)庫、information_schema)調(diào)優(yōu)方法
MySQL性能調(diào)優(yōu)涉及數(shù)據(jù)庫設(shè)計(jì)、查詢優(yōu)化、配置及硬件調(diào)整,MySQL性能調(diào)優(yōu)是一個(gè)復(fù)雜且多維度的過程,下面從數(shù)據(jù)庫設(shè)計(jì)、查詢優(yōu)化、配置參數(shù)調(diào)整、硬件優(yōu)化幾個(gè)方面為你介紹相關(guān)的調(diào)優(yōu)方法,感興趣的朋友跟隨小編一起看看吧2025-08-08
MySQL有多種存儲引擎,MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。2011-09-09
MYSQL關(guān)聯(lián)關(guān)系查詢方式
文章詳細(xì)介紹了MySQL中如何使用內(nèi)連接和左外連接進(jìn)行表的關(guān)聯(lián)查詢,并展示了如何選擇列和使用別名,文章還提供了一些關(guān)于查詢優(yōu)化的建議,并鼓勵(lì)讀者參考和支持腳本之家2025-02-02
安裝使用Percona XtraBackup來備份恢復(fù)MySQL的教程
這篇文章主要介紹了安裝使用Percona XtraBackup來備份恢復(fù)MySQL的教程,文中的示例環(huán)境基于CentOS系統(tǒng),需要的朋友可以參考下2015-12-12