MySQL支持的索引類型
B-tree索引的特點
1锈死、B-tree索引以B+樹的結(jié)構(gòu)存儲數(shù)據(jù)
2、B-tree索引能夠加快數(shù)據(jù)的查詢速度
3穆壕、B-tree索引更適合進行行范圍查找
B-tree結(jié)構(gòu)圖
在什么情況下可以用到B樹索引待牵?
1、全值匹配的查詢
例如我們有個訂單號喇勋,訂單號為 order_sn='98764322119900'
2缨该、匹配最左前綴的查詢
3、匹配列前綴查詢
例如:order_sn like '9876%'
4川背、匹配范圍值的查找
例如:order_sn > '98764322119900' and order_sn < '98764322119999'
5贰拿、精確匹配左前列并范圍匹配另外一列
6、只訪問索引的查詢
Btree索引的使用限制
1熄云、如果不是按著索引最左列開始查找膨更,則無法使用索引
2、使用索引時不能跳過索引中的列
3缴允、Not in 和 <> 操作無法使用索引
4荚守、如果查詢中有某個列的查詢范圍,則其右邊所有列都無法使用索引
Hash索引的特點
1练般、Hash索引是基于Hash表實現(xiàn)的矗漾,只有查詢條件精確匹配Hash索引中的所有列時,才能夠使用到hash索引
2踢俄、對于Hash索引中的所有列缩功,存儲引擎都會為每一行計算一個Hash碼,Hash索引中存儲的就是Hash碼
Hash索引的限制
1都办、使用hash索引需要二次查找
2嫡锌、Hash索引無法用于排序
3虑稼、Hash索引不支持部分索引查找也不支持范圍查找
為什么要使用索引?
1势木、索引大大減少了存儲引擎需要掃描的數(shù)據(jù)量
2蛛倦、索引可以幫助我們進行排序以比喵使用臨時表的IO消耗,提升MySQL的能力
3啦桌、索引可以把隨機I/O變?yōu)轫樞騃/O
索引是不是越多越好溯壶?
場景:開發(fā)人員要求來創(chuàng)建更多的索引,問:“為何要添加更多的索引甫男?”且改,答:”因為導(dǎo)入速度的時候很慢,以此來加快導(dǎo)入的速度0宀怠S瞩恕!“若治,so..開發(fā)人員對索引的理解不是那么的透徹慨蓝,雖然索引能夠為查找?guī)硭俣壬系奶嵘且矔π阅苡幸恍p失端幼,衡量標(biāo)準(zhǔn)礼烈,當(dāng)創(chuàng)建索引帶來的好處多過于消耗的時候,才是最優(yōu)的選擇~
1婆跑、索引會增加寫操作的成本
2此熬、太多的索引會增加查詢優(yōu)化器的選擇時間
索引的優(yōu)化策略
索引列上不能使用表達式或函數(shù)
上圖中的第一個select語句中使用表達式,因此不滿足創(chuàng)建索引的要求洽蛀,改為第二個select語句即可
前綴索引和索引列的選擇性
聯(lián)合索引
如何選擇索引列的順序
1摹迷、經(jīng)常會被使用到的列優(yōu)先
2疟赊、選擇性高的列優(yōu)先
覆蓋索引
優(yōu)點:
1郊供、可以優(yōu)化緩存,減少磁盤IO操作
2近哟、可以減少隨機IO驮审,使隨機IO操作變?yōu)轫樞騃O操作
3、可以避免對Innodb主鍵索引的二次查詢
4吉执、可以避免MyISAM表進行系統(tǒng)調(diào)用
無法使用覆蓋索引的情況
1恨溜、存儲引擎不支持覆蓋索引
2囊卜、查詢中使用了太多的列
3、使用了雙%號的like查詢
使用索引優(yōu)化查詢
使用索引掃描來優(yōu)化排序
通過排序操作
按著索引順序掃描數(shù)據(jù)
注意事項:
1、索引的列順序和order by子句的順序完全一致
2流妻、索引中所有列的方向(升序,降序)和order by子句完全一樣
3烂翰、order by中的字段全部在關(guān)聯(lián)表中的第一張表中
利用索引優(yōu)化
1、索引可以減少鎖定的行數(shù)
2蜡秽、索引以加快處理速度,同事也加快了鎖的釋放
索引的維護和優(yōu)化
刪除重復(fù)復(fù)制和容易的索引
更新索引統(tǒng)計信息及減少索引碎片
analyzetable table_name