高性能MySQL--索引 筆記




索引概述

  • 索引即key

  • 在存儲(chǔ)引擎層實(shí)現(xiàn)，不同引擎工作方式不同

  • 索引優(yōu)化--最好的查詢(xún)優(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ù)

一. 索引類(lèi)型

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 全值匹配查詢(xún)

所有列都匹配

2 最左前綴匹配

組合索引第一列

3 列前綴匹配

某列值開(kāi)頭

4 范圍值匹配
5 一列精確一列范圍匹配
6 覆蓋索引查詢(xún)

只訪問(wèn)索引即可取data，無(wú)須訪問(wèn)數(shù)據(jù)行

7 Order by排序

？

2) B-Tree不適用場(chǎng)景

  • 非最左列
  • 跳列

A C

  • 某列進(jìn)行范圍查詢(xú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ú)序

  • 不支持部分索引列匹配
  • 不支持范圍查詢(xún)

僅支持等值匹配 =,<=>,IN()

<=> NULL安全等于----操作數(shù)可為NULL

4) InnoDB自適應(yīng)Hash索引

某些索引值被引用很頻繁，InnoDB自動(dòng)在內(nèi)存B-Tree索引上創(chuàng)建一個(gè)Hash索引
用戶(hù)無(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索引查詢(xún)時(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ù)
任意維度組合查詢(xún)
須使用GIS函數(shù)維護(hù)數(shù)據(jù)，MySQL做的不好

4. 全文索引

  • 查找文本關(guān)鍵字，非比較索引鍵值
  • 類(lèi)似搜索引擎
  • 相同列創(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à)索引是否適合某查詢(xún)
第一星

索引將相關(guān)data行放到一起

第二星

索引的data行按查詢(xún)所需順序排序

第三星

索引含 查詢(xún)?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)存資源）到查詢(xún)成本中

4. 選擇合適的索引列順序

僅適用于BTree索引（按順序存儲(chǔ)數(shù)據(jù)）
Btree索引按從左到右順序，依次掃描
索引可按升、降序掃描，滿(mǎn)足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ù)保存在一起，如電子郵件（用戶(hù)ID和全部郵件）
  • 數(shù)據(jù)訪問(wèn)更快（索引和數(shù)據(jù)都在BTree中）
  • 覆蓋索引查詢(xún)直接取頁(yè)節(jié)點(diǎn)鍵值

聚簇缺點(diǎn)

數(shù)據(jù)全放內(nèi)存時(shí)無(wú)優(yōu)勢(shì)（訪問(wèn)順序不再重要）
插入速度依賴(lài)于插入順序

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. 覆蓋索引

索引直接包含所需查詢(xún)數(shù)據(jù)行，不需要回記錄表（數(shù)據(jù)表）
只能用BTree做覆蓋索引
支持InnoDB,myisam
Explain顯示 Extra：Using index

覆蓋索引優(yōu)點(diǎn)

  • 索引條目小于數(shù)據(jù)行，減少了數(shù)據(jù)訪問(wèn)量
  • 范圍查詢(xún)IO少（索引列值順序存儲(chǔ)）
  • 對(duì)InnoDB表（聚簇索引）特別有用

二級(jí)主鍵能覆蓋查詢(xún)可避免對(duì)主鍵索引的二次查詢(xún)

MyISAM覆蓋索引可能會(huì)導(dǎo)致系統(tǒng)問(wèn)題

MyISAM引擎內(nèi)存只緩存索引，數(shù)據(jù)由OS緩存

ICP索引條件推送（index condition pushdown）

MySQL5.6開(kāi)始支持
條件過(guò)濾推到存儲(chǔ)引擎層完成，減少I(mǎi)O訪問(wèn)


7. 用索引掃描做排序

MySQL生成有序結(jié)果的兩種方式

  • 排序
  • 按順序掃描索引

Exlain Type：Using index

為何索引掃描比全表掃描慢？

如果索引不能覆蓋查詢(xún)?nèi)苛?，則每掃一條索引記錄必須回表（隨機(jī)IO）

同一索引，既滿(mǎn)足排序，又滿(mǎn)足查找是最好的

何時(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使用
減少索引大?。?/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ù)索引

１) 重復(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或非鎖定查詢(xún)慢


四. 索引和表維護(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ì)碎片化，降低查詢(xún)效率

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. 索引覆蓋查詢(xún)很快

避免了大量單行訪問(wèn)

2017-4-10 sz

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容