創(chuàng)建高性能索引

索引是什么？有什么作用？

索引是存儲引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)

如書的目錄索引一般，數(shù)據(jù)庫中的索引也是另外存儲一些數(shù)據(jù)表的關(guān)鍵信息，在查詢的時(shí)候可以更方便定位到想要找的那一些內(nèi)容頁

在數(shù)據(jù)量小、負(fù)載低的時(shí)候，索引的作用可能并不明顯。但一旦到達(dá)一定量級，正確的索引往往可以輕易將性能提升幾個(gè)數(shù)量級。所以，索引優(yōu)化是查詢性能優(yōu)化最有效的手段

大多數(shù)框架都實(shí)現(xiàn)了ORM，而ORM可能很難兼顧到 每個(gè)開發(fā)者、每個(gè)業(yè)務(wù) 專門定制化的索引。但這并不意味著使用框架和ORM就不需要理解索引！最好的方法是：

簡單的、數(shù)據(jù)量較小的查詢可以直接使用ORM，當(dāng)某些復(fù)雜的查詢出現(xiàn)性能問題時(shí)，考慮舍棄ORM，采用原生的SQL語句去優(yōu)化性能

索引的類型

索引是在存儲引擎層上實(shí)現(xiàn)的，而非服務(wù)器層。所以每個(gè)引擎支持的索引類型、實(shí)現(xiàn)方法都有所不同。在使用上需要有所注意。這里主要討論的是InnoDB引擎的索引

B-Tree 索引

最常見的索引類型，絕大多數(shù)MySQL引擎都支持。一般沒有特殊說明，索引即B-Tree索引（這類索引采用類似B-Tree的結(jié)構(gòu)來存儲數(shù)據(jù)，如 B樹、B+樹 等）

InnoDB使用B+ Tree的結(jié)構(gòu)存儲索引

B+樹

參考鏈接：理解B+樹算法和Innodb索引

為什么通過索引查找數(shù)據(jù)，可以加快訪問的速度？

B-Tree 結(jié)構(gòu)存儲索引，通常意味著所有數(shù)據(jù)按照一定的順序存儲。在查找的時(shí)候，引擎就可以從根結(jié)點(diǎn)比較節(jié)點(diǎn)頁的值和要查找的值，找到合適的指針進(jìn)入下層子節(jié)點(diǎn)，不斷重復(fù)比較最終找到相應(yīng)值。避免了使用全表掃描

特別的，在B+ Tree結(jié)構(gòu)中，所有的非葉子節(jié)點(diǎn)都用作“查找”，葉子節(jié)點(diǎn)則指向具體的數(shù)據(jù)。如：有100條記錄，對于一個(gè)索引樹，有且僅有100個(gè)葉子節(jié)點(diǎn)，它們分別指向每一條記錄，這些記錄本身是有序且用指針相連

所以，索引在按照索引順序去查找范圍數(shù)據(jù)的時(shí)候，效率也非常高

索引對多個(gè)值進(jìn)行排序的記錄順序

如，組合索引key(last_name, first_name, birth)，這個(gè)索引對應(yīng)的記錄的順序是，先按照last_name排序，如果相同，則按first_name，最后按birth。

這就是為什么條件的順序必須與組合索引的順序一致

order by也可以使用索引

因?yàn)樗饕龢渲械墓?jié)點(diǎn)是有序的，所以除了按值查找之外，索引還可以用于查詢中的order by操作

可以使用B-Tree 索引的查詢類型

假設(shè)有一索引，key(last_name, first_name, birth)

• 全值匹配 last_name='ABC' and first_name='DEF' and birth='1999-01-01'
• 匹配最左前綴 last_name='ABC'
• 匹配列前綴 last_name LIKE 'AB%'
• 匹配范圍值 last_name >= 'A' and last_name <= 'C'
• 精確匹配某一列，并范圍匹配另一列 last_name='ABC' and first_name > 'C'
• 只訪問索引(不訪問值)的查詢 (覆蓋索引) select last_name, first_name, birth from xxtbl

B-Tree 索引的查詢限制

• 必須從最左列開始匹配，否則該索引不可用
• 不能跳過索引的某一列
• 如果查詢中有某個(gè)列是范圍匹配，則右邊的其他列無法使用索引

哈希索引

• 基于哈希表實(shí)現(xiàn)，只有精確匹配索引所有列的查詢才有效
• 存儲引擎針對每一行計(jì)算出一個(gè)哈希碼，與行指針保存成一張表，便于查詢
• 常用的InnoDB和MyISAM引擎并不支持，但I(xiàn)nnoDB引擎有自適應(yīng)哈希索引，它會根據(jù)查詢，動態(tài)優(yōu)化（不需要自己設(shè)置索引）

自定義(偽)哈希索引

不支持哈希索引的引擎，可以通過新增一個(gè)被索引的hash列，用crc32等算法做哈希，可以優(yōu)化查詢

例子：需要存儲并查詢一個(gè)url列

select * from tbl where url = 'http://xx.com/xx/yy/?zz=aa&bb=cc'

上面的語句明顯在比較上非常吃力，如果對url列設(shè)置索引，需要比較大的代價(jià)。如果引入一個(gè)被索引的hash列，在查找的時(shí)候，性能會變得很高

select * from tbl where url_crc = CRC32('URL...') and url = 'URL...'

注意：

• 不要直接使用強(qiáng)加密的hash算法，如md5、sha1等，否則生成過長的字符串，效果不好
• 也需要注意避免過多的哈希沖突，如果crc32不滿足，可以使用fnv64或自行實(shí)現(xiàn)一些哈希方案
• 精確匹配查詢的時(shí)候，必須在條件中同時(shí)帶上原列和hash列，如where crc=CRC32('xx') and url='xx'
• 如果不想維護(hù)hash列，可以創(chuàng)建觸發(fā)器，在創(chuàng)建的時(shí)候自動填充

全文索引

一種特殊類型的索引，查找的是文本中的關(guān)鍵詞，而不是直接比較索引中的值。它更類似于搜索引擎做的事情。另外，在列上同時(shí)創(chuàng)建全文索引和B-Tree索引，不會有沖突

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

• 索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量
• 索引可以幫助服務(wù)器避免排序和臨時(shí)表
• 索引可以將隨機(jī)I/O變?yōu)轫樞騃/O

另外需要注意的是，索引的建立和使用需要一些空間和額外工作。所以，如果表本身很小且可控，全表掃描或許更快

高性能的索引策略（如何在SQL語句中使用索引）

獨(dú)立的列

如果列本身是表達(dá)式的一部分，或是函數(shù)的參數(shù)，這種“不獨(dú)立的列”無法使用此列索引。如：select * from tbl where id + 1 < 5

前綴索引

有時(shí)需要索引一個(gè)較長的字符列，這樣會使索引變得大且慢，解決方案可以是之前說的哈希索引，但有時(shí)這樣仍然不夠好。這時(shí)候需要考慮使用前綴索引。

對于TEXT、BLOB和較長的VARCHAR，如果確實(shí)需要索引，則必須創(chuàng)建前綴索引，設(shè)置方法如下：

alter table tbl add key (city(7))

這樣就設(shè)置了一個(gè)針對city字段前7個(gè)字符做索引的前綴索引。

前綴索引是一種能使索引更小、更快的有效方法。但缺點(diǎn)是不支持order by, group by和覆蓋掃描

難點(diǎn)：如何確定一個(gè)較好的前綴值？(上面的例子是7)

-- 首先確定所有記錄中，distinct的記錄占比
select count(distinct city)/count(*) from tbl;

-- 假設(shè)上面得到的結(jié)果值為0.3，則表示城市平均重復(fù)2次左右
-- 為了前綴索引有良好的區(qū)分度，要求使用索引之后，占比較接近實(shí)際的0.3
select count(distinct left(city, 3))/count(*) as city3,
       count(distinct left(city, 4))/count(*) as city4,
       count(distinct left(city, 5))/count(*) as city5,
       ...
from tbl;
-- 最后取一個(gè)接近0.3，且前綴值不太大的一個(gè)值，作為最后的前綴值

多列索引

一個(gè)錯誤的索引設(shè)置方案：WHERE條件的列都建立一個(gè)單獨(dú)的索引

示例： create table t (c1 int, c2 int, c3 int, KEY(c1), KEY(c2), KEY(c3));

導(dǎo)致的結(jié)果是在多條件查詢的時(shí)候，無論怎么選擇，都只能使用其中一個(gè)索引，而之后的條件則沒有索引可以走

在新版本的MySQL中，查詢會被優(yōu)化成多條走索引的語句，然后聯(lián)合或相交得出結(jié)果。但這樣做相當(dāng)于多次查詢，明顯不是最優(yōu)的索引策略

應(yīng)該采用的方式是，根據(jù)查詢，設(shè)置組合索引

選擇合適的索引列順序

我們已知查詢的順序會影響到索引列順序的設(shè)置

• 將選擇性最高的列放在前面通常是比較好的
• 除了考慮WHERE條件的優(yōu)化，也需要考慮GROUP BY, ORDER BY, DISTINCT
• 考慮值的分布情況?？梢园褏^(qū)分度大的列放在前面，適當(dāng)去調(diào)整語句的編寫
• 極端的一些情況。比如某一列中，某個(gè)值出現(xiàn)的概率極高，這種可能不太適合建立索引

針對第三個(gè)情況的例子：

假設(shè)有一張表有customer_id和staff_id，這兩列經(jīng)常作為條件用于篩選。此時(shí)可以通過實(shí)際情況或SQL進(jìn)行查詢，不同的值更多的列可以考慮放在前面

select
count(distinct staff_id)/count(*) as staff_id_selectivity,
count(distinct customer_id)/count(*) as customer_id_selectivity,
count(*)
from payment;

-- res:
-- staff_id_selectivity: 0.0001, customer_id_selectivity: 0.0373
-- 此時(shí)應(yīng)該選擇 KEY(customer_id, staff_id)

聚簇索引

聚簇索引并不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲方式。對于InnoDB來說，聚簇索引實(shí)際上是同一個(gè)結(jié)構(gòu)中保存了B-Tree索引和數(shù)據(jù)行

一張表只能有一個(gè)聚簇索引，因?yàn)橐环輸?shù)據(jù)只能保存在一個(gè)地方。在InnoDB中，不能直接選擇某一列成為聚簇索引，而是直接通過主鍵作為聚簇索引

聚簇索引的優(yōu)點(diǎn)：

• 可以把相關(guān)的數(shù)據(jù)保存在一起。例如通過用戶id獲取用戶的數(shù)據(jù)，如果用戶id不是聚簇索引，則找到用戶id節(jié)點(diǎn)之后，還需要一次I/O才能拿到用戶的數(shù)據(jù)
• 相比非聚簇索引，數(shù)據(jù)訪問更快，理由同上
• 使用覆蓋索引掃描的查詢，可以直接使用頁節(jié)點(diǎn)中的(其他)主鍵值

聚簇索引的缺點(diǎn)：

• 聚簇索引可以最大限度提高I/O密集型應(yīng)用的效率。但如果所有數(shù)據(jù)都已經(jīng)加載到內(nèi)存中，則訪問順序沒那么重要，聚簇索引也就沒什么優(yōu)勢
• 插入速度嚴(yán)重依賴插入排序，如果不是按照主鍵順序加載數(shù)據(jù)，那么在加載完成后最好使用OPTIMAZE TABLE命令重新組織一下表
• 更新聚簇索引列的代價(jià)很高，因?yàn)闀?qiáng)制InnoDB將每個(gè)被更新的行移動到新的位置
• 基于聚簇索引的表在插入新行，或者主鍵被更新導(dǎo)致需要移動行的時(shí)候，可能面臨“頁分裂”的問題。當(dāng)行的主鍵值要求必須將這一行插入到某個(gè)已滿的頁中時(shí)，存儲引擎會將該頁分裂成兩個(gè)頁面來容納該行，這就是一次頁分裂操作，頁分裂會導(dǎo)致表占用更多的磁盤空間
• 聚簇索引可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導(dǎo)致數(shù)據(jù)存儲不連續(xù)的時(shí)候
• 二級索引（非聚簇索引）可能比想象中的要更大，因?yàn)樵诙壦饕娜~子節(jié)點(diǎn)包含了引用行的主鍵列
• 二級索引訪問需要兩次索引查找，而不是一次

對于InnoDB引擎的表來說

• 如果沒有什么數(shù)據(jù)需要聚集，可以設(shè)置一個(gè)自增列作為主鍵。保證順序?qū)懭牒鸵恍┎僮鞯谋憬菪?/li>
• 最好避免隨機(jī)的（不連續(xù)且值的分布范圍非常大）聚簇索引，特別是對于I/O密集型的應(yīng)用。從性能上來說，類似UUID這種隨機(jī)性很大的內(nèi)容，作為聚簇索引會非常糟糕

所以通常情況下，保證InnoDB的主鍵是順序插入的，減少不必要的調(diào)整和頁分裂，性能更優(yōu)

在高并發(fā)的時(shí)候，順序的主鍵可能會造成爭用，這種情況下不使用順序的主鍵

覆蓋索引

如果一個(gè)索引包含（覆蓋）所有需要查詢的字段的值，我們就稱之為“覆蓋索引”

好處：索引中已經(jīng)包含了全部數(shù)據(jù)，不需要回表查詢。通常來說，索引沒有太多多余的數(shù)據(jù)，規(guī)模小，所以查起來速度很快（MySQL中只有B-Tree 索引可以做覆蓋索引）

當(dāng)發(fā)起一個(gè)覆蓋索引的查詢時(shí)，在EXPLAIN的Extra列可以看到“Using index”的信息

一則簡單明了的例子：

有一張products表，有主鍵prod_id，以及關(guān)聯(lián)其他表的的兩個(gè)id：aid、bid，這兩個(gè)id形成一個(gè)key(aid, bid)，然后還有其他的一些產(chǎn)品信息

select aid, bid from products;

上述語句就是最簡單的覆蓋索引查詢，涉及的列只有索引列

復(fù)雜一些的例子：

products表中有一個(gè)key(actor, title)，但是現(xiàn)在要查出滿足條件的所有數(shù)據(jù)項(xiàng)

select * from products where actor='abc' and title like '%abc%';

上述語句并不能覆蓋索引。且title前面也有%，故最終只能使用actor索引數(shù)據(jù)，然后回表查滿足條件的title，再返回整行數(shù)據(jù)

解決方案：

建立索引key(actor, title, prod_id)

select * from products join (
  select prod_id from products where actor='abc' and title like '%abc%'
) t1 on (t1.prod_id = products.prod_id);

上述語句的子查詢滿足覆蓋索引的查詢（一個(gè)索引包含所有需要查詢的字段的值）。另外，where條件也可以匹配最左前綴actor

如果在上述子查詢中，過濾掉了大多數(shù)的不符合條件的products，且本身此表的規(guī)模有一定大小，則優(yōu)化效果會很好，否則可能適得其反

示例：

1. abc這個(gè)actor的條目有30000條，其中20000條title包含有abc => 這個(gè)屬于規(guī)模大、過濾少的，效果不好
2. abc這個(gè)actor的條目有30000條，其中40條title包含有abc => 這個(gè)屬于規(guī)模大、過濾多的，效果很好
3. abc這個(gè)actor的條目有50條，其中10條title包含有abc => 這個(gè)屬于規(guī)模小，效果不好（子查詢需要耗費(fèi)一些性能）

另外的：

InnoDB引擎的二級索引的葉子節(jié)點(diǎn)是包含主鍵的值的，所以，主鍵是可以作為查詢列覆蓋索引的

使用索引掃描來做排序

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

• 通過排序操作
• 按索引順序掃描

當(dāng)按索引順序掃描排序時(shí)，在EXPLAIN的type列會顯示index。這里注意區(qū)分之前的覆蓋索引，覆蓋索引是在Extra列顯示“Using index”

掃描索引本身速度很快，但是如果不能覆蓋查詢的所有列，就不得不每掃描一條索引記錄就回表查詢一次對應(yīng)的行，這樣會造成隨機(jī)I/O，這種情況是不如順序地全表掃描的

所以，只有以下條件同時(shí)滿足，引擎才會按索引順序掃描：

• 滿足最左前綴的要求，即order by的列必須是索引列的最左前綴子列
• order by的列順序和索引列順序一致，且所有列的排列方向（正序、倒序）必須一致
• 如果查詢需要關(guān)聯(lián)多張表，則只有當(dāng)order by的列都是第一張表的字段才可以

有一種情況可以不滿足最左前綴，就是前導(dǎo)列為常量的時(shí)候。即假設(shè)有KEY(a,b,c)，WHERE或JOIN子句對a已經(jīng)指定了定值，則ORDER BY可以只針對b和c字段

MySQL可以使用同一個(gè)索引滿足排序和查找行的任務(wù)，如果可能，盡量設(shè)計(jì)這樣的索引

例子：假設(shè)有KEY(rental_date, inventory_id, customer_id)

以下方式可以實(shí)現(xiàn)索引掃描：

• 前導(dǎo)列為常量，最左前綴：... where rental_date = '2005-05-25' order by inventory_id desc
• 純粹的最左前綴（與where無關(guān)）：... where rental_date > '2005-05-25' order by rental_date, inventory_id

以下方式不能實(shí)現(xiàn)索引掃描：

• 排列方向不一致：... where rental_date = '2005-05-25' order by inventory_id desc, customer_id asc
• 引用了不在索引的列：... where rental_date = '2005-05-25' order by inventory_id, staff_id
• 不能組合成最左前綴：... where rental_date = '2005-05-25' order by customer_id
• 前導(dǎo)列為范圍值：... where rental_date > '2005-05-25' order by inventory_id, customer_id
• 前導(dǎo)列使用了IN，相當(dāng)于范圍值：... where rental_date = '2005-05-25' and inventory_id in (1, 2) order by customer_id

壓縮（前綴壓縮）索引

MyISAM使用前綴壓縮來減少索引的大小，從而讓更多的索引放入內(nèi)存

特點(diǎn)：

• 默認(rèn)只壓縮字符串
• 方式大致是：對于一個(gè)索引塊，先保存一個(gè)完整的字符串，然后后面如果有前綴相同的字符串，則只存儲不相同的值。比如，存儲一個(gè)“perform”，之后存儲“performance”只需要存儲“7,ance”即可
• 無法在索引塊中使用二分查找，如果倒序掃描——order by xx desc的話，由于需要從頭開始掃描，所以會比較慢
• 適合I/O密集型的應(yīng)用，不適合CPU密集型的應(yīng)用

重復(fù)索引、冗余索引

MySQL允許在相同列創(chuàng)建多個(gè)索引，并需要維護(hù)重復(fù)的索引，在優(yōu)化器優(yōu)化查詢時(shí)，逐個(gè)考慮（這種情況是影響性能的。有些沒有意義，需要去掉，但有時(shí)候是需要的）

重復(fù)索引：在相同的列按照相同的順序創(chuàng)建相同類型的索引。這種是必須去掉的

錯誤示范：

create table t(
  ID int NOT NULL PRIMARY KEY,
  A  int NOT NULL,
  UNIQUE(ID),
  INDEX(ID)
) ENGINE=InnoDB;

上面三種索引（主鍵、唯一、普通索引）是同類型的B-Tree 索引，必須去掉（如果索引分別是INDEX和FULLTEXT，它們屬于不同類型的索引，也不是重復(fù)索引，是完全沒問題的）

冗余索引：如果已有KEY(A, B)，這時(shí)再創(chuàng)建一個(gè)KEY(A)，由于前者的左前綴包含A，所以后者是冗余索引。這種需要按實(shí)際情況分析

另外，在InnoDB中，主鍵默認(rèn)已經(jīng)包含在二級索引中，所以 KEY(A)
和 KEY(A, ID) 如果同時(shí)存在，也屬于冗余索引

大多數(shù)情況，冗余索引是不需要的，應(yīng)該盡量擴(kuò)展已有的索引而不是建立新的索引。但如果擴(kuò)展索引會導(dǎo)致索引太大，影響原有的查詢，這時(shí)就需要考慮冗余索引了

例子：如果在整數(shù)列上有一個(gè)索引，現(xiàn)在需要額外加一個(gè)長的VARCHAR列來擴(kuò)展索引

假設(shè)原來有以下需求，記作Q1。對應(yīng)有一個(gè)索引KEY(state_id)

select count(*) from userinfo where state_id = 5;

現(xiàn)在有另外的新需求，記作Q2。如果引入覆蓋索引，對應(yīng)的索引是KEY(state_id, city, address)

select city, address from userinfo where state_id = 5;

由于MyISAM前綴索引的原因，如果采用直接擴(kuò)展的方式，性能下降很嚴(yán)重；InnoDB影響不太大。書上例子的測試結(jié)果：

有兩個(gè)索引的缺點(diǎn)就是，索引成本高，增刪改記錄的效率變低。書上例子的測試結(jié)果：

在決定刪除“認(rèn)為無用”的冗余索引時(shí)，需要檢查是否有采用到主鍵作為二級索引的查詢，如... where A = 1 order by id。如果有，當(dāng)去掉
KEY(A) ，只保留 KEY(A, B)，會導(dǎo)致此查詢不能用索引掃描排序

可以使用一些工具檢測重復(fù)和冗余的索引

未使用的索引

可能會有服務(wù)器永遠(yuǎn)不用的索引，這種也建議刪除。同樣可以通過工具檢測出