高性能MySQL--索引 筆記




索引概述

  • 索引即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ù)很快
哈希表結(jié)構(gòu)

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ì)算完整列選擇性

使前綴選擇性接近完整列選擇性


完整列選擇性

前綴長(zhǎng)度為7接近完整列

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í)索引查找行步驟
  1. 葉子節(jié)點(diǎn)找到主鍵值
  2. 在聚簇索引找數(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

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市虎韵,隨后出現(xiàn)的幾起案子易稠,更是在濱河造成了極大的恐慌,老刑警劉巖包蓝,帶你破解...
    沈念sama閱讀 206,311評(píng)論 6 481
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件驶社,死亡現(xiàn)場(chǎng)離奇詭異,居然都是意外死亡测萎,警方通過(guò)查閱死者的電腦和手機(jī)衬吆,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,339評(píng)論 2 382
  • 文/潘曉璐 我一進(jìn)店門(mén),熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)绳泉,“玉大人逊抡,你說(shuō)我怎么就攤上這事×憷遥” “怎么了冒嫡?”我有些...
    開(kāi)封第一講書(shū)人閱讀 152,671評(píng)論 0 342
  • 文/不壞的土叔 我叫張陵,是天一觀的道長(zhǎng)四苇。 經(jīng)常有香客問(wèn)我孝凌,道長(zhǎng),這世上最難降的妖魔是什么月腋? 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 55,252評(píng)論 1 279
  • 正文 為了忘掉前任蟀架,我火速辦了婚禮,結(jié)果婚禮上榆骚,老公的妹妹穿的比我還像新娘片拍。我一直安慰自己,他們只是感情好妓肢,可當(dāng)我...
    茶點(diǎn)故事閱讀 64,253評(píng)論 5 371
  • 文/花漫 我一把揭開(kāi)白布捌省。 她就那樣靜靜地躺著,像睡著了一般碉钠。 火紅的嫁衣襯著肌膚如雪纲缓。 梳的紋絲不亂的頭發(fā)上卷拘,一...
    開(kāi)封第一講書(shū)人閱讀 49,031評(píng)論 1 285
  • 那天,我揣著相機(jī)與錄音祝高,去河邊找鬼栗弟。 笑死,一個(gè)胖子當(dāng)著我的面吹牛工闺,可吹牛的內(nèi)容都是我干的乍赫。 我是一名探鬼主播,決...
    沈念sama閱讀 38,340評(píng)論 3 399
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼斤寂,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼耿焊!你這毒婦竟也來(lái)了?” 一聲冷哼從身側(cè)響起遍搞,我...
    開(kāi)封第一講書(shū)人閱讀 36,973評(píng)論 0 259
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤罗侯,失蹤者是張志新(化名)和其女友劉穎,沒(méi)想到半個(gè)月后溪猿,有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體钩杰,經(jīng)...
    沈念sama閱讀 43,466評(píng)論 1 300
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 35,937評(píng)論 2 323
  • 正文 我和宋清朗相戀三年诊县,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了讲弄。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 38,039評(píng)論 1 333
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡依痊,死狀恐怖避除,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情胸嘁,我是刑警寧澤瓶摆,帶...
    沈念sama閱讀 33,701評(píng)論 4 323
  • 正文 年R本政府宣布,位于F島的核電站性宏,受9級(jí)特大地震影響群井,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜毫胜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,254評(píng)論 3 307
  • 文/蒙蒙 一书斜、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧酵使,春花似錦荐吉、人聲如沸。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 30,259評(píng)論 0 19
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至搓劫,卻和暖如春瞧哟,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背枪向。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 31,485評(píng)論 1 262
  • 我被黑心中介騙來(lái)泰國(guó)打工勤揩, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人秘蛔。 一個(gè)月前我還...
    沈念sama閱讀 45,497評(píng)論 2 354
  • 正文 我出身青樓陨亡,卻偏偏與公主長(zhǎng)得像,于是被迫代替她去往敵國(guó)和親深员。 傳聞我的和親對(duì)象是個(gè)殘疾皇子负蠕,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 42,786評(píng)論 2 345

推薦閱讀更多精彩內(nèi)容