索引概述
索引即key
在存儲(chǔ)引擎層實(shí)現(xiàn)拙毫,不同引擎工作方式不同
索引優(yōu)化--最好的查詢優(yōu)化手段,可提效幾個(gè)數(shù)量級(jí)
兩步查找數(shù)據(jù):
磁盤(pán)查找索引節(jié)點(diǎn)(頁(yè))步藕,將其調(diào)入內(nèi)存垮卓;
內(nèi)存內(nèi)業(yè)內(nèi)查找數(shù)據(jù)
一. 索引類型
B-Tree
Hash
R-Tree空間數(shù)據(jù)索引
全文索引
1. B-tree索引
- 支持引擎:InnoDB鹉戚,MyISAM鲜戒,Memory
- 所有葉子值順序存儲(chǔ),且到root高度一樣
- InnoDB崩瓤,MyISAM B-tree工作方式異同:
InnoDB按原格式存儲(chǔ)數(shù)據(jù)袍啡,MYISAM用前綴壓縮技術(shù)
InnoDB用主鍵key索引數(shù)據(jù)行,MyISAM用物理位置索引數(shù)據(jù)行
- 加速:存儲(chǔ)引擎從root節(jié)點(diǎn)掃描却桶,代替全表掃描
- 葉節(jié)點(diǎn)指針-->被索引數(shù)據(jù)(data record)
1)B-Tree適用場(chǎng)景
1 全值匹配查詢
所有列都匹配
2 最左前綴匹配
組合索引第一列
3 列前綴匹配
某列值開(kāi)頭
4 范圍值匹配
5 一列精確一列范圍匹配
6 覆蓋索引查詢
只訪問(wèn)索引即可取data境输,無(wú)須訪問(wèn)數(shù)據(jù)行
7 Order by排序
蔗牡?
2) B-Tree不適用場(chǎng)景
- 非最左列
- 跳列
A C
- 某列進(jìn)行范圍查詢,其右邊所有列無(wú)法再用索引
2. Hash索引
- 訪問(wèn)哈希索引的數(shù)據(jù)很快
f(key)=slot
1) 支持引擎
Memory嗅剖,NDB集群
2) 適用場(chǎng)景
索引全列匹配
3) 不適用場(chǎng)景
- 不能從索引直接取data
哈希索引=哈希值+行指針辩越,不存儲(chǔ)字段值
- 不能用于排序
哈希值有序,但索引數(shù)據(jù)無(wú)序
- 不支持部分索引列匹配
- 不支持范圍查詢
僅支持等值匹配 =,<=>,IN()
<=> NULL安全等于----操作數(shù)可為NULL
4) InnoDB自適應(yīng)Hash索引
某些索引值被引用很頻繁信粮,InnoDB自動(dòng)在內(nèi)存B-Tree索引上創(chuàng)建一個(gè)Hash索引
用戶無(wú)法控制和配置黔攒,但可關(guān)閉
5) 自定義hash索引
存儲(chǔ)引擎不支持時(shí),模擬創(chuàng)建hash
如何創(chuàng)建强缘?
B-tree上創(chuàng)建偽hash索引
- 仍在Btree上查找督惰,但用hash索引值代替原Key(偽hash)
- 須在where指定hash函數(shù),不要用MD5(),SHA1()
select id from url
where url="www.mysql.com"
and
url_crc=CRC32("www.mysql.com")
其中urc_crc列為索引列
6) 處理Hash沖突
使用hash索引查詢時(shí)旅掂,須在where指定常量
select id from url
where
url_crc=CRC32("www.mysql.com")
and
url="www.mysql.com"
select word,crc from words
where
crc=CRC32("gnu")
and
word="gnu"
@birthday problem
In probability theory, the birthday problem or birthday paradox concerns the
probability that,
in a set of {\displaystyle n} n randomly chosen people, some pair of them will have the same birthday.
By the pigeonhole principle, the probability reaches 100% when the number of people reaches 367 (since there are only 366 possible birthdays, including February 29). However, 99.9% probability is reached with just 70 people, and 50% probability with 23 people.
3. 空間數(shù)據(jù)索引 R-Tree
支持引擎:MyISAM
用作地理數(shù)據(jù)存儲(chǔ)赏胚,如美團(tuán),滴滴定位服務(wù)
任意維度組合查詢
須使用GIS函數(shù)維護(hù)數(shù)據(jù)商虐,MySQL做的不好
4. 全文索引
- 查找文本關(guān)鍵字觉阅,非比較索引鍵值
- 類似搜索引擎
- 相同列創(chuàng)建全文索引和B-Tree索引,不沖突
5. 其他索引
第三方引擎TokuDB
二. 索引好處
1. 好處
1) 減少掃描數(shù)據(jù)量
2) 避免排序和臨時(shí)表
3) 隨機(jī)IO轉(zhuǎn)為順序IO
2. 索引三星評(píng)價(jià)
評(píng)價(jià)索引是否適合某查詢
第一星
索引將相關(guān)data行放到一起
第二星
索引的data行按查詢所需順序排序
第三星
索引含 查詢?nèi)苛?/p>
三 .高性能索引策略
1. 獨(dú)立的列
獨(dú)立
索引列非表達(dá)式子式秘车,或函數(shù)參數(shù)
兩個(gè)錯(cuò)誤:
1)索引列為表達(dá)式
select id from actor
where id + 1 = 5
2)
select ...
where
TO_DAYS(Current_DATE) - TO_DAYS(date_col) <= 10典勇;
2. 前綴索引和索引選擇性
1) 前綴索引
很長(zhǎng)字符串,可索引開(kāi)始的部分字符串
適用場(chǎng)景
BLOB叮趴,TEXT割笙,很長(zhǎng)的VARCHAR列
2) 優(yōu)缺點(diǎn)
優(yōu)點(diǎn)
節(jié)約索引空間
缺點(diǎn)
無(wú)法使用之做order by,Group by
無(wú)法使用做覆蓋索引
3) 索引選擇性
=不重復(fù)索引值/數(shù)據(jù)表記錄總數(shù)
- 不重復(fù)索引值<-->基數(shù)<-->cardinality
- 記錄總數(shù)<-->#T
- 取值范圍 [1/#T ,1]
- 越高越好
選擇性越高疫向,過(guò)濾掉的行越多
4) 如何找到 前綴索引長(zhǎng)度
思想
足夠長(zhǎng)(接近完整列)咳蔚,又不能太長(zhǎng)(節(jié)約空間)
方法1 試驗(yàn)法
先算完整列頻次豪嚎,然后一個(gè)一個(gè)前綴試驗(yàn)
計(jì)算完整列頻次
試驗(yàn)前3前綴
方法2 計(jì)算完整列選擇性
使前綴選擇性接近完整列選擇性
5) 如何創(chuàng)建前綴索引
ALTER TABLE sakila
ADD KEY(city(7));
KEY(city(7))
3. 多列索引
1) 常見(jiàn)錯(cuò)誤
每個(gè)列都創(chuàng)建單獨(dú)索引搔驼,導(dǎo)致索引合并
create table t(
c1 int,
c2 int侈询,
KEY(c1)舌涨,
KEY(c2)
);
2) 索引合并表示索引建的不好,待優(yōu)化
- 多個(gè)索引相交(AND)不如組合索引好
- 多個(gè)索引聯(lián)合(OR)耗費(fèi)CPU和內(nèi)存資源
- 優(yōu)化器不計(jì)算(耗費(fèi)CPU和內(nèi)存資源)到查詢成本中
4. 選擇合適的索引列順序
僅適用于BTree索引(按順序存儲(chǔ)數(shù)據(jù))
Btree索引按從左到右順序囊嘉,依次掃描
索引可按升震檩、降序掃描博其,滿足Order by,Group by背伴,Distinct
如何選擇合適的索引列順序
經(jīng)驗(yàn)法則
無(wú)order by和Group by時(shí)傻寂,選擇性最高的列放在前面
select * from payment
where staff_id=2 and customer_id=584;
key(staff_id,customer_id)還是key(customer_id,staff_id)?
5. 聚簇索引(clustered index)
InnoDB支持个绍,MyISAM為非聚簇
聚簇
數(shù)據(jù)行,鍵值存儲(chǔ)在一起
一個(gè)表只能有一個(gè)聚簇索引
聚簇特點(diǎn)
- InnoDB通過(guò)主鍵聚集數(shù)據(jù)
主鍵未定義死遭,用唯一非空索引聚集
無(wú)唯一非空索引钠署,則隱式定義主鍵
- 只聚集同一頁(yè)面記錄
聚簇優(yōu)點(diǎn)
- 相關(guān)數(shù)據(jù)保存在一起狸棍,如電子郵件(用戶ID和全部郵件)
- 數(shù)據(jù)訪問(wèn)更快(索引和數(shù)據(jù)都在BTree中)
- 覆蓋索引查詢直接取頁(yè)節(jié)點(diǎn)鍵值
聚簇缺點(diǎn)
數(shù)據(jù)全放內(nèi)存時(shí)無(wú)優(yōu)勢(shì)(訪問(wèn)順序不再重要)
插入速度依賴于插入順序
InnoDB按主鍵順序插入最快(否則插入后用optimize table優(yōu)化表)
更新聚簇索引列代價(jià)很高
強(qiáng)制將每個(gè)更新行移動(dòng)到新位置
頁(yè)分裂問(wèn)題
插入新行或丙猬,主鍵更新導(dǎo)致需移動(dòng)行時(shí)
全表掃描慢
二級(jí)索引需兩次索引查找
二級(jí)索引(secondary index咐低,輔助索引)
葉節(jié)點(diǎn)保存行主鍵值,非指向data行的物理記錄的指針
二級(jí)索引查找行步驟
- 葉子節(jié)點(diǎn)找到主鍵值
- 在聚簇索引找數(shù)據(jù)行
1 . InnoDB和MyISAM數(shù)據(jù)分布對(duì)比
InnoDB數(shù)據(jù)分布
InnoDB就是表翘鸭,不用再像Myisam用單獨(dú)列存儲(chǔ)
聚簇索引葉子節(jié)點(diǎn)包含:
主鍵值
事物ID
回滾指針(用于事物和MVCC)
其他剩余列
聚簇和非聚簇表對(duì)比
2. InnoDB表按主鍵順序插入行
無(wú)數(shù)據(jù)聚集,使用AUTO INCREMENT作為主鍵--保證按順序?qū)懭?br> 避免使用UUID(universal unique identifier)聚簇索引--導(dǎo)致插入變得隨機(jī)
6. 覆蓋索引
索引直接包含所需查詢數(shù)據(jù)行戳葵,不需要回記錄表(數(shù)據(jù)表)
只能用BTree做覆蓋索引
支持InnoDB,myisam
Explain顯示 Extra:Using index
覆蓋索引優(yōu)點(diǎn)
- 索引條目小于數(shù)據(jù)行就乓,減少了數(shù)據(jù)訪問(wèn)量
- 范圍查詢IO少(索引列值順序存儲(chǔ))
- 對(duì)InnoDB表(聚簇索引)特別有用
二級(jí)主鍵能覆蓋查詢可避免對(duì)主鍵索引的二次查詢
MyISAM覆蓋索引可能會(huì)導(dǎo)致系統(tǒng)問(wèn)題
MyISAM引擎內(nèi)存只緩存索引,數(shù)據(jù)由OS緩存
ICP索引條件推送(index condition pushdown)
MySQL5.6開(kāi)始支持
條件過(guò)濾推到存儲(chǔ)引擎層完成拱烁,減少IO訪問(wèn)
7. 用索引掃描做排序
MySQL生成有序結(jié)果的兩種方式
- 排序
- 按順序掃描索引
Exlain Type:Using index
為何索引掃描比全表掃描慢生蚁?
如果索引不能覆蓋查詢?nèi)苛校瑒t每掃一條索引記錄必須回表(隨機(jī)IO)
同一索引戏自,既滿足排序邦投,又滿足查找是最好的
何時(shí)能用索引進(jìn)行排序?
- 索引列序和order by順序一致時(shí)
- 且所有列排序方向一樣
不能使用索引進(jìn)行排序的場(chǎng)景
- order by出現(xiàn)不同排序方向
- order by引用非索引列
- where和order by中的列無(wú)法組合為最左前綴
- where第一列是范圍條件
- where出現(xiàn)IN(多個(gè)相等條件視為范圍)
8. 壓縮(前綴壓縮)索引
MyISAM使用
減少索引大衅滞(1/10磁盤(pán)空間)讓更多索引進(jìn)入內(nèi)存
默認(rèn)只壓縮字符串尼摹,也可設(shè)置整數(shù)
只能從頭開(kāi)始掃描,無(wú)法二分
隨機(jī)掃描導(dǎo)致適用于IO密集型(OLTP)剂娄,不適用CPU密集型(OLAP)?
index1:perform
index2:performance-->7,ance
9. 冗余和重復(fù)索引
應(yīng)刪除重復(fù)索引
1) 重復(fù)索引
相同列創(chuàng)建多個(gè)索引
三個(gè)重復(fù)索引--unique玄呛,primary限制均通過(guò)索引實(shí)現(xiàn)
2) 冗余索引
應(yīng)該刪除冗余索引
兩種冗余
已有key(A阅懦,B),再建key(A)
ID為主鍵,擴(kuò)展索引為(A,ID)
建議
盡量擴(kuò)展現(xiàn)有索引徘铝,而不是創(chuàng)建新索引耳胎,那樣會(huì)導(dǎo)致冗余索引
10.刪除未使用的索引
percona Toolkit--
pt-index-usage工具
11. 索引和鎖
InnoDB存儲(chǔ)引擎層完成條件過(guò)濾時(shí)(ICP--MySQL 5.6及以后)惯吕,索引可減少訪問(wèn)行數(shù),從而減少加鎖數(shù)量
否則全表掃描并鎖住所有行
覆蓋索引失效:
- InnoDB二級(jí)索引上用共享(讀)鎖怕午,訪問(wèn)主鍵索引需要排他(寫(xiě))鎖
-
select for update比lock in share mode或非鎖定查詢慢
四. 索引和表維護(hù)
維護(hù)表三目的
找到并修復(fù)損壞的表
維護(hù)準(zhǔn)確的索引統(tǒng)計(jì)信息
減少碎片
1. 找到并修復(fù)表
1) MyISAM表
check table--檢查表
repair table--修復(fù)損壞的表
2) InnoDB表使用no-op ALTER
Alter TABLE innodb_tb ENGINE=INNODB;
2. 維護(hù)索引統(tǒng)計(jì)信息
優(yōu)化器有時(shí)用索引統(tǒng)計(jì)信息估算掃描行數(shù)
ANALYZE TABLE更新統(tǒng)計(jì)信息避免錯(cuò)誤
memory引擎不存儲(chǔ)統(tǒng)計(jì)信息
MyISAM引擎存儲(chǔ)統(tǒng)計(jì)信息在磁盤(pán)
Show Index from table查看索引基數(shù)(cardinality,索引列不同取值個(gè)數(shù))
觸發(fā)索引統(tǒng)計(jì)信息更新的三種情形
SHOW TABLE STATUS
SHOW INDEX
打開(kāi)某些INFORMATION_SCHEMA表
3.減少索引和數(shù)據(jù)碎片
1)BTree索引會(huì)碎片化废登,降低查詢效率
BTree隨機(jī)訪問(wèn)是必須的,因?yàn)閺膔oot節(jié)點(diǎn)隨機(jī)磁盤(pán)訪問(wèn)才能定位到葉子節(jié)點(diǎn)
2)三種數(shù)據(jù)碎片
行碎片
數(shù)據(jù)行存儲(chǔ)在多個(gè)地方多個(gè)碎片中
行間碎片
邏輯上順序的頁(yè)或行郁惜,在磁盤(pán)上非順序存儲(chǔ)
剩余空間碎片
數(shù)據(jù)頁(yè)中有大量不用的空余空間
MyISAM三種碎片都有堡距,InnoDB無(wú)小碎片
3)如何消除碎片?
- OPTIMIZE TABLE
- ALTER TABLE tb ENGINE=<engine>;
- 刪除所有索引-->重建表 -->重建索引
五. 總結(jié)
索引三原則
1.單行訪問(wèn)很慢
最好一個(gè)數(shù)據(jù)塊讀取多行
2. 按順序訪問(wèn)范圍行很快
- 順序IO無(wú)需多次磁盤(pán)尋道兆蕉,比隨機(jī)IO快很多
- 服務(wù)器按順序讀取數(shù)據(jù)羽戒,則不需要額外排序
3. 索引覆蓋查詢很快
避免了大量單行訪問(wèn)
2017-4-10 sz