mysql支持的索引
索引是在存儲(chǔ)引擎層實(shí)現(xiàn)逗嫡。而不是在mysql內(nèi)實(shí)現(xiàn)
- B-tree索引
index 普通索引 沒(méi)有限制
primary 主鍵索引 必須唯一救鲤，且不能為空
unique key 唯一索引 必須唯一
index 前綴索引
當(dāng)索引的字段長(zhǎng)度很大弄抬，則使用前綴索引，使用更少的空間奈嘿，并綜合索引的選擇性暮胧，達(dá)到平衡。
//如下面该押。根據(jù)字段列的長(zhǎng)度疗杉，進(jìn)行統(tǒng)計(jì)
select count(*) as cnt,left(city,7) as pref from city group by pref order by cnt desc limit 10
//計(jì)算索引選擇性
select count(distinct left(city,7))/count(*) from city
//添加索引
alter table tablename add key (keyname(length))
alter table country add key(country(3))
B-tree索引
特點(diǎn)
- B-tree 索引是以B+樹(shù)結(jié)構(gòu)存儲(chǔ)數(shù)據(jù)
- B-tree 索引能夠加快數(shù)據(jù)的查詢速度
- B-tree索引適合進(jìn)行范圍查找 索引是順序存儲(chǔ)的
在什么情況下使用到了b-tree索引
- 全值匹配的查詢
- 匹配最左前綴的查詢
- 匹配列前綴查詢
- 匹配范圍值查找
- 精確匹配左前列并范圍匹配另外一列
- 只訪問(wèn)索引的查詢
B-tree索引的限制
- 如果不是按照最左列開(kāi)始查找，則無(wú)法使用索引
- 使用索引時(shí)不能跳過(guò)索引中的列
- not in 和<> 操作無(wú)法使用索引
- 如果查詢中有某個(gè)列的范圍查詢蚕礼，則其右邊所有的列都無(wú)法使用索引
Hash 索引
特點(diǎn)
- 基于hash表實(shí)現(xiàn)的烟具，只有查詢條件精確匹配hash索引中所有列才能使用到hash索引
- 對(duì)于hash索引中的所有列，存儲(chǔ)引擎都會(huì)為每一行計(jì)算一個(gè)hash碼奠蹬，hash索引中存儲(chǔ)的就是hash碼
限制
- hash索引必須進(jìn)行二次查詢
- hash索引無(wú)法用于排序
- hash索引無(wú)法用于范圍查找也不支持部分索引查找
- hash索引中hash碼的計(jì)算可能存在hash沖突
使用索引的好處
- 索引可以減少存儲(chǔ)引擎需要掃描數(shù)據(jù)量
- 索引可以幫助排序朝聋，避免使用臨時(shí)表
- 索引可以把隨機(jī)io變?yōu)轫樞騣o
索引會(huì)增加寫(xiě)操作的成本
過(guò)多的索引會(huì)增加查詢優(yōu)化器的選擇時(shí)間
索引的優(yōu)化策略
索引列上不能使用表達(dá)式或者函數(shù)
前綴索引和索引列的選擇性
聯(lián)合索引
列的順序選擇
- 經(jīng)常被使用的列優(yōu)先
- 列的選擇性高的優(yōu)先
- 寬度小的列優(yōu)先
覆蓋索引
在btree索引上直接獲得查詢中所需數(shù)據(jù)
- 可以優(yōu)化緩存，減少磁盤(pán)io操作
- 可以減少隨機(jī)io囤躁，變隨機(jī)io為順序io
- 可以避免對(duì)innodb主鍵索引的二次查詢
- 可以避免myisam表進(jìn)行系統(tǒng)調(diào)用
無(wú)法使用覆蓋索引
- 存儲(chǔ)引擎不支持覆蓋索引
- 查詢中使用了太多的列
- 使用了雙%的like查詢 雙%無(wú)法使用索引
聚簇索引
聚簇索引是一種數(shù)據(jù)的存儲(chǔ)方式冀痕。聚簇表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲(chǔ)在一起。innodb是通過(guò)主鍵聚集數(shù)據(jù)
優(yōu)點(diǎn)
- 可以把相關(guān)的數(shù)據(jù)保存在一起
- 數(shù)據(jù)訪問(wèn)更快
- 使用索引覆蓋掃描查詢時(shí)狸演，可以直接使用頁(yè)節(jié)點(diǎn)中的主鍵值
缺點(diǎn)
- 聚簇索引最大限度地提升了io密集型的性能言蛇，但是如果數(shù)據(jù)都放入內(nèi)存，則訪問(wèn)的順序并沒(méi)有那么重要
- 插入的速度嚴(yán)重依賴于插入的順序
- 更新的代價(jià)很高宵距，會(huì)強(qiáng)制innodb把被更新的行移動(dòng)到新位置
- 基于聚簇索引的表猜极，在插入新行，或者主鍵被更新導(dǎo)致需要移動(dòng)行時(shí)消玄，可能會(huì)導(dǎo)致頁(yè)分裂
- 聚簇索引可能導(dǎo)致全表掃描變慢
- 二級(jí)索引會(huì)比想象的大跟伏，因?yàn)樵诙?jí)索引中含有主鍵
- 二級(jí)索引需要兩次訪問(wèn)，而不是一次
