高效利用mysql索引指南

前言

mysql 相信大部分人都用過(guò)，索引肯定也是用過(guò)的，但是你知道如何創(chuàng)建恰當(dāng)?shù)乃饕龁幔吭跀?shù)據(jù)量小的時(shí)候，不合適的索引對(duì)性能并不會(huì)有太大的影響，但是當(dāng)數(shù)據(jù)逐漸增大時(shí)，性能便會(huì)急劇的下降。

本篇是對(duì) mysql 索引的一個(gè)歸納總結(jié)，如果有錯(cuò)誤的地方，記得評(píng)論指出哦。

索引基礎(chǔ)

我們都有都知道查字典的步驟，是先在索引頁(yè)中找到這個(gè)字的頁(yè)碼，然后再到對(duì)應(yīng)的頁(yè)碼中查看這個(gè)字的信息。mysql 的索引方法也是和這個(gè)類似的，先在索引中找到對(duì)應(yīng)值，然后根據(jù)匹配的索引記錄找到對(duì)應(yīng)的數(shù)據(jù)行。假如有下面的 sql 語(yǔ)句：

select * from student where code='2333'

加入 code 列上建立有索引，mysql 將使用該索引找到值為'2333'的數(shù)據(jù)行，然后讀取數(shù)據(jù)行的所有數(shù)據(jù)返回。

索引類型

B-Tree 索引

（不是 B 減樹(shù)，就是 B 樹(shù)），絕大多數(shù)的索引類型都是 B-Tree 的（或者是 B-Tree 的變體），通常我們使用的也是這類索引。Mysql 中 MyISAM 存儲(chǔ)引擎使用的是 B-tree，InnoDB 使用的是 B+Tree，B 樹(shù)和 B+樹(shù)的區(qū)別自行百度。

樹(shù)結(jié)構(gòu)的索引能夠加快訪問(wèn)數(shù)據(jù)的速度，存儲(chǔ)引擎不再需要全表掃描來(lái)獲取所需的數(shù)據(jù)，取而代之的是從樹(shù)的根節(jié)點(diǎn)來(lái)進(jìn)行二分搜索，總所周知二分搜索的速度是相當(dāng)快的，因此我們能夠利用索引來(lái)極大的提高查詢速度。B-Tree 支持以下幾種類型的查詢：

假設(shè)再 student 表中僅有：name,age,weight 這樣一個(gè)多列索引,下面的查詢都能利用到此索引

全值匹配

和索引列中的所有列進(jìn)行匹配。比如查詢name='abc' and age=12,這里用到了第一列和第二列

匹配最左前列

只是用索引的開(kāi)頭部分，比如查詢name='ggg'只使用索引的第一列，查詢name='ggg' and age=12是用索引的第一、二列。

匹配列前綴

也可以只匹配某一列的開(kāi)頭部分，比如查詢name lik 'g%',查詢 name 以 g 開(kāi)頭的記錄。這里用到了第一列

匹配范圍值

可用于匹配范圍值，比如查詢name > 'abc' and name 'bcd'

精確匹配某一列并范圍匹配另外一列

用于匹配多列，比如查詢name='abc' and age > 12。

總的來(lái)看，可以發(fā)現(xiàn) B-Tree 索引適用于根據(jù)最左前綴的查找，也就是查詢字段字段順序要和索引字段順序一樣，且以第一個(gè)索引字段開(kāi)頭。比如查詢name,name and age,name and age and weight都能使用索引，但是查詢age,age and name不能使用索引。

哈希索引

hash 索引基于 hash 表實(shí)現(xiàn)，只有精確匹配索引所有列才會(huì)生效。MySQL 中只有 Memory 引擎顯示支持哈希索引，同時(shí)也是其默認(rèn)索引。

InnoDB 無(wú)法創(chuàng)建 hash 索引，但是它有一個(gè)功能叫自適應(yīng)hash索引,當(dāng)某些索引值使用非常頻繁時(shí)，引擎會(huì)在內(nèi)存中基于 B-Tree 索引之上再創(chuàng)建一個(gè) hash 索引，這樣就讓 B-Tree 索引也有了一點(diǎn) hash 索引的優(yōu)點(diǎn)。這個(gè)功能是一個(gè)完全自動(dòng)的、內(nèi)部的行為，也就是無(wú)法手動(dòng)控制或配置。

高性能索引策略

下面是一些常見(jiàn)的索引策略。

獨(dú)立的列

這個(gè)很簡(jiǎn)單，如果查詢中的列不是獨(dú)立，便無(wú)法使用索引，比如：

select * from student where age+1=12

即使 age 列有索引，上面的查詢語(yǔ)句也是無(wú)法利用索引的。

前綴索引和索引選擇性

如果需要索引很長(zhǎng)的字符串列，直接創(chuàng)建索引，會(huì)讓索引占用更多的空間且速度較慢。一個(gè)優(yōu)化策略是模擬 hash 索引：給列計(jì)算一個(gè) hash 值，并在 hash 值列建立索引。

另外一個(gè)辦法就是建立前綴索引。只索引這個(gè)字段開(kāi)始的部分字符，這樣可以極大的解決空間占用，索引建立速度也會(huì)快很多。但是這樣也有如下弊端：

降低了索引選擇性，如果多個(gè)字符串前綴相同便無(wú)法區(qū)分，還需要進(jìn)行字符串對(duì)比。
不支持order by,group by,原因顯而易見(jiàn)，只索引了部分字符，無(wú)法完全區(qū)分。

這里的關(guān)鍵是確定索引多少個(gè)字符合適。既要避免長(zhǎng)度過(guò)大，還要有足夠的索引選擇性。有以下兩種辦法來(lái)幫助確定索引字符數(shù)：

索引字段前綴數(shù)據(jù)分布均勻。也就是以索引字符開(kāi)頭的字符串?dāng)?shù)目分布均勻，比如索引 name 字段的前 3 個(gè)字符，下面的結(jié)果是比較合理(只取排名前 8 的）：

數(shù)目	索引前三個(gè)字符
500	abc
465	asd
455	acd
431	zaf
430	aaa
420	vvv
411	asv
512	pdf

如果每一列的數(shù)據(jù)都比較大，說(shuō)明區(qū)分度還不高需要增大索引字符數(shù)，直到這個(gè)前綴的選擇性接近完整列的索引性，也就是前面的數(shù)據(jù)要盡可能的小。

計(jì)算完整列的選擇性，并使前綴的選擇性接近于完整列的選擇性。下面語(yǔ)句用戶計(jì)算完整列選擇性：

-- 不同字符串的數(shù)目/總的數(shù)目就是完整列選擇性
select count(distinct name)/count(*) from person;

下面語(yǔ)句計(jì)算索引前 3 個(gè)字段選擇性：

-- 前3個(gè)字符不同的字符串?dāng)?shù)據(jù)/總的數(shù)據(jù)
select count(distincy left(city,3))/count(*) from person

不斷增大索引字符數(shù)目，直到選擇性接近完整列選擇性且繼續(xù)增大數(shù)據(jù)選擇性提升幅度不大的時(shí)候。

創(chuàng)建方法

-- 假設(shè)最佳長(zhǎng)度為4
alter table person add key (name(4));

多列索引

不少人有這樣的誤解，如果一個(gè)查詢用有多個(gè)字段 ‘a(chǎn)nd'查詢，那么給每個(gè)字段都建立索引不就能最大化提高效率了？事實(shí)并不是如此，mysql 只會(huì)選擇其中一個(gè)字段來(lái)進(jìn)行索引查找。這種情況下應(yīng)該建立多列索引（又叫聯(lián)合索引），就能利用多個(gè)索引字段了，注意索引列順序要和查詢的順序一致。

在 5.0 及以上版本中引入了“索引合并”的策略。一定程度上也可以使用多個(gè)單列索引，比如下面的查詢：

-- mysql會(huì)分別使用name和age索引查出數(shù)據(jù)然后合并
-- 如果使and則查出數(shù)據(jù)后再對(duì)比取交集
select * from person where name = "bob" or age=12

但是不推薦這么做，and 或 or條件過(guò)多會(huì)耗費(fèi)大量的 CPU 和內(nèi)存在算法的緩存、排序和合并操作上。

選擇合適的索引列順序

在一個(gè)多列 B-Tree 索引中，索引列的順序意味著索引首先是按照最左列進(jìn)行排序，然后是第二列…索引一個(gè)良好的多列索引應(yīng)該是將選擇性最高的索引放在最前面，然后依次降低，這樣才能更好的利于索引。選擇性計(jì)算方發(fā)見(jiàn)：前綴索引小節(jié)。

聚族索引

聚族索引不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲(chǔ)方法，具體的細(xì)節(jié)依賴其實(shí)現(xiàn)方式。

InnoDB 的聚族索引實(shí)際是在同一個(gè)結(jié)構(gòu)中保存索引值和數(shù)據(jù)行。因?yàn)椴荒芡瑫r(shí)將數(shù)據(jù)行放在兩個(gè)不同的地方，所以一個(gè)表只能有一個(gè)聚族索引。InnoDB 的聚族索引列為“主鍵列”。

如果沒(méi)有定義主鍵,InnoDB 會(huì)選擇一個(gè)唯一的非空索引代替。如果這樣的索引也沒(méi)有，InnoDB 會(huì)隱式定義一個(gè)主鍵來(lái)作為聚族索引。

聚族索引的主要優(yōu)點(diǎn)是：可以把相關(guān)數(shù)據(jù)保存在一起，減少磁盤 IO,提高查詢效率。但是也有缺點(diǎn)：

插入順序嚴(yán)重依賴于插入順序。按照主鍵的順序插入是速度最快的方式，否則可能會(huì)導(dǎo)致頁(yè)分裂的問(wèn)題出現(xiàn)，會(huì)占用更多的磁盤空間，掃描速度也會(huì)變慢?？赏ㄟ^(guò)OPTIMIZE TABLE重新組織表。
更新聚族索引列代價(jià)很高，因?yàn)樗饕底兞?，行?shù)據(jù)也會(huì)跟著索引移動(dòng)到新的位置上。
二級(jí)索引（非聚族索引）訪問(wèn)行數(shù)據(jù)需要兩次索引查找，因?yàn)槎?jí)索引葉子節(jié)點(diǎn)存儲(chǔ)的并不是行數(shù)據(jù)的物理位置，而是行的主鍵值，再通過(guò)主鍵值到聚族索引中取行數(shù)據(jù)。

覆蓋索引

簡(jiǎn)單來(lái)說(shuō)就是一個(gè)索引覆蓋了需要查詢的列字段，這樣就不需要再到聚族索引中利用主鍵進(jìn)行二次查找，在一個(gè)二級(jí)索引中就能取到所需的數(shù)據(jù)。

InnoDB 的索引會(huì)在葉子節(jié)點(diǎn)中保存索引值，因此如果要查詢的字段全部包含在某個(gè)索引中，且這個(gè)索引被使用了，那么就能極大的提高查詢速度。比如如下查詢語(yǔ)句：

-- name有索引的情況下，直接從索引的葉子節(jié)點(diǎn)中取name值返回，無(wú)需二次查找
select name from person where name = 'abc'
-- 如果存在`name,age`聚合索引，也會(huì)直接返回?cái)?shù)據(jù)，無(wú)需二次查找
select name,age from person where name='abc' and age=12

使用索引進(jìn)行排序

mysql 的排序操作也是可以利用索引的，只有當(dāng)索引的列順序和ORDER BY的順序完全一致，并且所有列的排序方法（正序或者倒序）也一樣時(shí)，才能夠使用索引來(lái)進(jìn)行排序。注意：排序的字段可以比對(duì)應(yīng)的索引字段少，但是順序必須一致。如下:

-- 假設(shè)有：(name,age,sex)聯(lián)合索引
-- 可使用索引排序
select ... order by name desc,age desc
select ... order by name desc,age desc,sex desc
-- 不可使用排序
select ... order by name desc,sex desc
select ... order by name desc,age asc

結(jié)束

本篇基于 mysql 5.5 的版本，更新的版本可能會(huì)有不一樣的策略。

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章:

新手學(xué)習(xí)MySQL索引
由不同的索引更新解決MySQL死鎖套路
通過(guò)唯一索引S鎖與X鎖來(lái)了解MySQL死鎖套路
分享幾道關(guān)于MySQL索引的重點(diǎn)面試題
Mysql中的索引精講
簡(jiǎn)單談?wù)凪ysql索引與redis跳表
MySQL學(xué)習(xí)（七）：Innodb存儲(chǔ)引擎索引的實(shí)現(xiàn)原理詳解
使用shell腳本來(lái)給mysql加索引的方法
MySQL批量插入和唯一索引問(wèn)題的解決方法

標(biāo)簽：銅川衡水湖南崇左蘭州黃山仙桃湘潭

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《高效利用mysql索引指南》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。