柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫 MySQL的索引

http://yzkb.51969.com/

MySQL 的索引

1 索引的使用1.1 什么是索引1.2 如何創(chuàng)建索引1.2.1 ALTER TABLE1.2.2 CREATE INDEX1.2.3 CREATE TABLE1.2.4 創(chuàng)建索引的注意事項(xiàng)

1.3 索引類型1.4 刪除索引1.5 查看索引1.6 如何選擇合適的列建立索引

2 InnoDB 的索引模型2.1 基于主鍵索引和普通索引的查詢有什么區(qū)別。

3 索引維護(hù)3.1 為什么要使用自增主鍵？3.2 業(yè)務(wù)主鍵在什么情況下可以使用？3.3 索引的優(yōu)化3.3.1 重復(fù)和冗余的索引3.3.2 索引的維護(hù)刪除

4 覆蓋索引5 最左前綴原則5.1 在建立聯(lián)合索引的時(shí)候，如何安排索引內(nèi)的字段順序？5.2 前綴索引的影響5.2.1 前綴索引對(duì)掃描行的影響5.2.1.1 給字符串創(chuàng)建前綴索引時(shí)，怎么能夠確定使用多長的前綴呢？5.2.1.2 其他方式5.2.1.2.1 倒序存儲(chǔ)5.2.1.2.2 Hash字段5.2.1.2.3 使用倒序存儲(chǔ)和使用 Hash 字段這兩種方法的異同點(diǎn)

5.2.2 前綴索引對(duì)覆蓋索引的影響

6 索引下推7 普通索引和唯一索引在業(yè)務(wù)中應(yīng)該如何選擇？7.1 示例7.2 查詢過程7.3 更新過程7.3.1 change buffer7.3.2 什么條件下可以使用 change buffer 呢？7.3.2 更新 InnoDB 的處理流程7.3.3 change buffer 的使用場景7.3.4 change buffer 和 redo log的區(qū)別

1 索引的使用

1.1 什么是索引

??索引的作用相當(dāng)于圖書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。數(shù)據(jù)庫使用索引以找到特定值，然后順指針找到包含該值的行。在表中建立索引，然后在索引中找到符合查詢條件的索引值，最后通過保存在索引中的 ROWID（相當(dāng)于頁碼）快速找到表中對(duì)應(yīng)的記錄。索引的建立是表中比較有指向性的字段，相當(dāng)于目錄，比如說行政區(qū)域代碼，同一個(gè)地域的行政區(qū)域代碼都是相同的，那么給這一列加上索引，避免讓它重復(fù)掃描，從而達(dá)到優(yōu)化的目的。

1.2 如何創(chuàng)建索引

??在執(zhí)行 CREATE TABLE 語句時(shí)可以創(chuàng)建索引，也可以單獨(dú)用 CREATE INDEX 或 ALTER TABLE 來為表增加索引。

1.2.1 ALTER TABLE

??ALTER TABLE用來創(chuàng)建普通索引、UNIQUE 索引或 PRIMARY KEY 索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

??其中 table_name 是要增加索引的表名，column_list 指出對(duì)哪些列進(jìn)行索引，多列時(shí)各列之間用逗號(hào)分隔。索引名 index_name 可選，缺省時(shí)，MySQL 將根據(jù)第一個(gè)索引列賦一個(gè)名稱。另外，ALTER TABLE 允許在單個(gè)語句中更改多個(gè)表，因此可以在同時(shí)創(chuàng)建多個(gè)索引。

1.2.2 CREATE INDEX

??CREATE INDEX 可對(duì)表增加普通索引或 UNIQUE 索引。

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

??table_name 、index_name 和 column_list 具有與 ALTER TABLE 語句中相同的含義，索引名不可選。另外，不能用 CREATE INDEX 語句創(chuàng)建 PRIMARY KEY 索引。

1.2.3 CREATE TABLE

create table T(

id int primary key,

k int not null,

name varchar(16),

index (k)

)engine=InnoDB;

1.2.4 創(chuàng)建索引的注意事項(xiàng)

??只要列中包含有 NULL 值都將不會(huì)被包含在索引中，復(fù)合索引中只要有一列含有 NULL 值，那么這一列對(duì)于此復(fù)合索引就是無效的。所以我們?cè)跀?shù)據(jù)庫設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL。 ??MySQL 查詢只使用一個(gè)索引，因此如果 where 子句中已經(jīng)使用了索引的話，那么 order by 中的列是不會(huì)使用索引的。因此數(shù)據(jù)庫默認(rèn)排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個(gè)列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引。

1.3 索引類型

??在創(chuàng)建索引時(shí)，可以規(guī)定索引能否包含重復(fù)值。如果不包含，則索引應(yīng)該創(chuàng)建為 PRIMARY KEY 或 UNIQUE 索引。對(duì)于單列唯一性索引，這保證單列不包含重復(fù)的值。對(duì)于多列唯一性索引，保證多個(gè)值的組合不重復(fù)。 ??PRIMARY KEY 索引和 UNIQUE 索引非常類似。事實(shí)上，PRIMARY KEY 索引僅是一個(gè)具有名稱 PRIMARY 的 UNIQUE 索引。這表示一個(gè)表只能包含一個(gè) PRIMARY KEY，因?yàn)橐粋€(gè)表中不可能具有兩個(gè)同名的索引。

1.4 刪除索引

??可利用 ALTER TABLE 或 DROP INDEX 語句來刪除索引。類似于 CREATE INDEX 語句，DROP INDEX 可以在 ALTER TABLE 內(nèi)部作為一條語句處理，語法如下。

DROP INDEX index_name ON talbe_name;

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE table_name DROP PRIMARY KEY;

??其中，前兩條語句是等價(jià)的，刪除掉 table_name 中的索引 index_name。第3條語句只在刪除 PRIMARY KEY 索引時(shí)使用，因?yàn)橐粋€(gè)表只可能有一個(gè) PRIMARY KEY 索引，因此不需要指定索引名。如果沒有創(chuàng)建 PRIMARY KEY 索引，但表具有一個(gè)或多個(gè) UNIQUE 索引，則 MySQL 將刪除第一個(gè) UNIQUE 索引。 ??如果從表中刪除了某列，則索引會(huì)受到影響。對(duì)于多列組合的索引，如果刪除其中的某列，則該列也會(huì)從索引中刪除。如果刪除組成索引的所有列，則整個(gè)索引將被刪除。

1.5 查看索引

show index from table_name;

1.6 如何選擇合適的列建立索引

在 where 從句，group by 從句，order by 從句，on 從句中虛線的列添加索引。索引字段越小越好（因?yàn)閿?shù)據(jù)庫數(shù)據(jù)存儲(chǔ)單位是以“頁”為單位的，數(shù)據(jù)存儲(chǔ)的越多，IO 也會(huì)越大）。查詢中與其它表關(guān)聯(lián)的字段需要添加索引。對(duì)一些經(jīng)常處理的業(yè)務(wù)表應(yīng)在查詢?cè)试S的情況下盡量減少索引。假如一個(gè)表有10萬行記錄，有一個(gè)字段A只有T和F兩種值，且每個(gè)值的分布概率大約為50%，那么對(duì)這種表A字段建索引一般不會(huì)提高數(shù)據(jù)庫的查詢速度。

2 InnoDB 的索引模型

??在 InnoDB 中，表都是根據(jù)主鍵順序以索引的形式存放的，這種存儲(chǔ)方式的表稱為索引組織表。InnoDB 使用了 B+ 樹索引模型，所以數(shù)據(jù)都是存儲(chǔ)在 B+ 樹中的。 ??每一個(gè)索引在 InnoDB 里面對(duì)應(yīng)一棵 B+ 樹。 ??假設(shè)，我們有一個(gè)主鍵列為 ID 的表，表中有字段 k，并且在 k 上有索引：如下

create table T(

id int primary key,

k int not null,

name varchar(16),

index (k)

)engine=InnoDB;

insert into T (id,k) values (100,1);

insert into T (id,k) values (200,2);

insert into T (id,k) values (300,3);

insert into T (id,k) values (500,5);

insert into T (id,k) values (600,6);

??從圖中不難看出，根據(jù)葉子節(jié)點(diǎn)的內(nèi)容，索引類型分為主鍵索引和非主鍵索引。主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)。在 InnoDB 里，主鍵索引也被稱為聚簇索引。非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級(jí)索引。

2.1 基于主鍵索引和普通索引的查詢有什么區(qū)別。

??如上圖，如果要查詢 id = 500 的數(shù)據(jù)，即主鍵查詢方式，則只需要搜索 id 這棵 B+ 樹。但是如果要查詢 k = 5 的數(shù)據(jù)，即普通索引查詢方式，則需要先搜索 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜索一次。這個(gè)過程稱為回表。 ??也就是說，基于非主鍵索引的查詢需要多掃描一棵索引樹。因此，我們?cè)趹?yīng)用中應(yīng)該盡量使用主鍵查詢。

3 索引維護(hù)

??B+ 樹為了維護(hù)索引有序性，在插入新值的時(shí)候需要做必要的維護(hù)。以上面這個(gè)圖為例，如果插入新的行 ID 值 為 700，則只需要在 R5 的記錄后面插入一個(gè)新記錄。如果新插入的 ID 值 為400，就相對(duì)麻煩了，需要邏輯上挪動(dòng)后面的數(shù)據(jù)空出位置。而更糟的情況是，如果 R5 所在的數(shù)據(jù)頁已經(jīng)滿了，根據(jù) B+ 樹的算法，這時(shí)候需要申請(qǐng)一個(gè)新的數(shù)據(jù)頁，然后挪動(dòng)部分?jǐn)?shù)據(jù)過去。這個(gè)過程稱為頁分裂。在這種情況下，性能自然會(huì)受影響。除了性能外，頁分裂操作還影響數(shù)據(jù)頁的利用率。原本放在一個(gè)頁的數(shù)據(jù)，現(xiàn)在分到兩個(gè)頁中，整體空間利用率降低大約 50%。當(dāng)然有分裂就有合并。當(dāng)相鄰兩個(gè)頁由于刪除了數(shù)據(jù)，利用率很低之后，會(huì)將數(shù)據(jù)頁做合并。合并的過程，可以認(rèn)為是分裂過程的逆過程。

3.1 為什么要使用自增主鍵？

??自增主鍵是指自增列上定義的主鍵，插入新記錄的時(shí)候可以不指定 ID 的值，系統(tǒng)會(huì)獲取當(dāng)前 ID 最大值加 1 作為下一條記錄的 ID 值。也就是說，自增主鍵的插入數(shù)據(jù)模式，正符合了我們前面提到的遞增插入的場景。每次插入一條新記錄，都是追加操作，都不涉及到挪動(dòng)其他記錄，也不會(huì)觸發(fā)葉子節(jié)點(diǎn)的分裂。而有業(yè)務(wù)邏輯的字段做主鍵，則往往不容易保證有序插入，這樣寫數(shù)據(jù)成本相對(duì)較高。 ??除了考慮性能外，我們還可以從存儲(chǔ)空間的角度來看。假設(shè)你的表中確實(shí)有一個(gè)唯一字段，比如字符串類型的身份證號(hào)，那應(yīng)該用身份證號(hào)做主鍵，還是用自增字段做主鍵呢？ ??由于每個(gè)非主鍵索引的葉子節(jié)點(diǎn)上都是主鍵的值。如果用身份證號(hào)做主鍵，那么每個(gè)二級(jí)索引的葉子節(jié)點(diǎn)占用約 20 個(gè)字節(jié)，而如果用整型做主鍵，則只要 4 個(gè)字節(jié)，如果是長整型則是 8 個(gè)字節(jié)。顯然，主鍵長度越小，普通索引的葉子節(jié)點(diǎn)就越小，普通索引占用的空間也就越小。 ??所以，從性能和存儲(chǔ)空間方面考量，自增主鍵往往是更合理的選擇。

3.2 業(yè)務(wù)主鍵在什么情況下可以使用？

??當(dāng)業(yè)務(wù)所在的場景為 KV 場景時(shí)，也就是整個(gè)表只有一個(gè)索引，并且是唯一索引。這時(shí)候我們就要優(yōu)先考慮“盡量使用主鍵查詢”原則，直接將這個(gè)索引設(shè)置為主鍵，可以避免每次查詢需要搜索兩棵樹。

3.3 索引的優(yōu)化

??增加索引會(huì)有利于查詢效率，但會(huì)降低 insert，update，delete 的效率，但實(shí)際上往往不是這樣的，過多的索引會(huì)不但會(huì)影響使用效率，同時(shí)會(huì)影響查詢效率，這是由于數(shù)據(jù)庫進(jìn)行查詢分析時(shí)，首先要選擇使用哪一個(gè)索引進(jìn)行查詢，如果索引過多，分析過程就會(huì)越慢，這樣同樣的減少查詢的效率，因此我們要知道如何增加，有時(shí)候要知道維護(hù)和刪除不需要的索引。

3.3.1 重復(fù)和冗余的索引

重復(fù)索引：重復(fù)索引是指相同的列以相同的順序建立的同類型的索引。 冗余索引：冗余索引是指多個(gè)索引的前綴列相同，或是在聯(lián)合索引中包含了主鍵的索引。因?yàn)槠胀ㄋ饕娜~子節(jié)點(diǎn)存儲(chǔ)的是主鍵索引，如果建立的聯(lián)合索引，又人為的把主鍵包含進(jìn)去，那么這個(gè)時(shí)候就是一個(gè)冗余索引。

3.3.2 索引的維護(hù)刪除

??由于業(yè)務(wù)變更，某些索引是后續(xù)不需要使用的，就要進(jìn)行刪除。在 MySQL 中，目前只能通過慢查詢?nèi)罩九浜蟨t-index-usage工具來進(jìn)行索引使用情況的分析。具體方式請(qǐng)查看這篇文章。

4 覆蓋索引

??先看如下示例：

create table T (

ID int primary key,

k int NOT NULL DEFAULT 0,

s varchar(16) NOT NULL DEFAULT '',

index k(k)

)engine=InnoDB;

insert into T (id,k,s) values (100,1,'a');

insert into T (id,k,s) values (200,2,'b');

insert into T (id,k,s) values (300,3,'c');

insert into T (id,k,s) values (500,5,'d');

insert into T (id,k,s) values (600,6,'e');

??在上面這個(gè)表中如果要查詢 SELECT * FROM T WHERE k BETWEEN 3 AND 5;這個(gè) SQL 語句的話，索引樹和上面的相同。SQL 的執(zhí)行流程是怎么樣的？ ??1. 在 k 索引樹上找到 k = 3 的記錄，取得 ID = 300。 ??2. 再到 ID 索引樹查到 ID = 300 對(duì)應(yīng)的 R3。 ??3. 在 k 索引樹取下一個(gè)值 k = 5，取得 ID = 500。 ??4. 再回到 ID 索引樹查到 ID = 500 對(duì)應(yīng)的 R4。 ??5. 在 k 索引樹取下一個(gè)值 k = 6，不滿足條件，循環(huán)結(jié)束。 ??在這個(gè)過程中，回到主鍵索引樹搜索的過程，我們稱為回表?？梢钥吹?，這個(gè)查詢過程讀了 k 索引樹的 3 條記錄（步驟1、3 和 5），回表了兩次（步驟 2 和 4）。在這個(gè)例子中，由于查詢結(jié)果所需要的數(shù)據(jù)只在主鍵索引上有，所以不得不回表。那么，有沒有可能經(jīng)過索引優(yōu)化，避免回表過程呢？ ??如果執(zhí)行的語句是SELECT ID FROM T WHERE K BETWEEN 3 AND 5;，這時(shí)只需要查 ID 的值，而 ID 的值已經(jīng)在 k 索引樹上了，因此可以直接提供查詢結(jié)果，不需要回表。也就是說，在這個(gè)查詢里面，索引 k 已經(jīng)“覆蓋了”我們的查詢需求，我們稱為覆蓋索引。 ??由于覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能，所以使用覆蓋索引是一個(gè)常用的性能優(yōu)化手段。 ??需要注意的是，在引擎內(nèi)部使用覆蓋索引在索引 k 上其實(shí)讀了三個(gè)記錄，R3~R5（對(duì)應(yīng)的索引 k 上的記錄項(xiàng)），但是對(duì)于 MySQL 的 Server 層來說，它就是找引擎拿到了兩條記錄，因此 MySQL 認(rèn)為掃描行數(shù)是 2。

5 最左前綴原則

??為了直觀地說明這個(gè)概念，我們用（name，age）這個(gè)聯(lián)合索引來分析。如上圖，可以看到，索引項(xiàng)是按照索引定義里面出現(xiàn)的字段順序排序的。當(dāng)你的邏輯需求是查到所有名是“張三”的人時(shí)，可以快速定位到 ID4，然后向后遍歷得到所有需要的結(jié)果。 ??如果你要查的是所有名字第一個(gè)字是“張”的人，你的 SQL 語句的條件是" where name like ‘張%’"。這時(shí)，你也能夠用上這個(gè)索引，查找到第一個(gè)符合條件的記錄是 ID3，然后向后遍歷，直到不滿足條件為止?？梢钥吹?，不只是索引的全部定義，只要滿足最左前綴，就可以利用索引來加速檢索。這個(gè)最左前綴可以是聯(lián)合索引的最左 N 個(gè)字段，也可以是字符串索引的最左 M 個(gè)字符。

5.1 在建立聯(lián)合索引的時(shí)候，如何安排索引內(nèi)的字段順序？

??這里我們的評(píng)估標(biāo)準(zhǔn)是，索引的復(fù)用能力。因?yàn)榭梢灾С肿钭笄熬Y，所以當(dāng)已經(jīng)有了(a,b)這個(gè)聯(lián)合索引后，一般就不需要單獨(dú)在 a 上建立索引了。因此，第一原則是，如果通過調(diào)整順序，可以少維護(hù)一個(gè)索引，那么這個(gè)順序往往就是需要優(yōu)先考慮采用的。 ??那么，如果既有聯(lián)合查詢，又有基于a、b各自的查詢呢？查詢條件里面只有b的語句，是無法使用(a,b)這個(gè)聯(lián)合索引的，這時(shí)候你不得不維護(hù)另外一個(gè)索引，也就是說你需要同時(shí)維護(hù)(a,b)、(b) 這兩個(gè)索引。這時(shí)候，我們要考慮的原則就是空間了。比如上面這個(gè)示例，name 字段是比 age 字段大的 ，那我就建議你創(chuàng)建一個(gè)（name,age)的聯(lián)合索引和一個(gè)(age)的單字段索引。

5.2 前綴索引的影響

5.2.1 前綴索引對(duì)掃描行的影響

??先看如下示例：

create table SUser(

ID bigint unsigned primary key,

email varchar(64),

...

)engine=innodb;

-- 有如上的一張表，如果要根據(jù)email進(jìn)行查詢。MySQL 是支持前綴索引的，也就是說，你可以定義字符串的一部分作為索引。默認(rèn)地，如果你創(chuàng)建索引的語句不指定前綴長度，那么索引就會(huì)包含整個(gè)字符串。

alter table SUser add index index1(email);

-- 如果指定索引長度，那么索引就不會(huì)包含整個(gè)字符串。

alter table SUser add index index2(email(6));

??第一個(gè)語句創(chuàng)建的 index1 索引里面，包含了每個(gè)記錄的整個(gè)字符串。而第二個(gè)語句創(chuàng)建的 index2 索引里面，對(duì)于每個(gè)記錄都是只取前 6 個(gè)字節(jié)。那么，這兩種不同的定義在數(shù)據(jù)結(jié)構(gòu)和存儲(chǔ)上有什么區(qū)別呢？

??如上圖，由于 email(6) 這個(gè)索引結(jié)構(gòu)中每個(gè)郵箱字段都只取前 6 個(gè)字節(jié)（即：zhangs），所以占用的空間會(huì)更小，這就是使用前綴索引的優(yōu)勢。但這同時(shí)帶來的損失是，可能會(huì)增加額外的記錄掃描次數(shù)。 ??如果有如下SELECT id,name,email FROM SUser WHERE email='zhangssxyz@xxx.com';SQL，這個(gè) SQL 在這兩個(gè)索引定義下分別是怎么執(zhí)行的？ ??index1： ????1. 從 index1 索引樹找到滿足索引值是’zhangssxyz@xxxx.com’的這條記錄，取得 ID2 的值； ????2. 到主鍵上查到主鍵值是 ID2 的行，判斷 email 的值是正確的，將這行記錄加入結(jié)果集； ????3. 取 index1 索引樹上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)已經(jīng)不滿足email=‘zhangssxyz@xxxx.com’的條件了，循環(huán)結(jié)束。 ????這個(gè)過程中，只需要回主鍵索引取一次數(shù)據(jù)，所以系統(tǒng)認(rèn)為只掃描了一行。 ??index2： ????1. 從 index2 索引樹找到滿足索引值是’zhangs’的記錄，找到的第一個(gè)是 ID1； ????2. 到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值不是’zhangssxyz@xxx.com’，這行記錄丟棄； ????3. 取 index2 上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判斷，這次值對(duì)了，將這行記錄加入結(jié)果集； ????4. 重復(fù)上一步，直到在 index2 上取到的值不是’zhangs’時(shí)，循環(huán)結(jié)束。 ????在這個(gè)過程中，要回主鍵索引取 4 次數(shù)據(jù)，也就是掃描了 4 行。 ??通過這個(gè)對(duì)比，你很容易就可以發(fā)現(xiàn)，使用前綴索引后，可能會(huì)導(dǎo)致查詢語句讀數(shù)據(jù)的次數(shù)變多。但是，對(duì)于這個(gè)查詢語句來說，如果你定義的 index2 不是 email(6) 而是 email(7），也就是說取 email 字段的前 7 個(gè)字節(jié)來構(gòu)建索引的話，即滿足前綴’zhangss’的記錄只有一個(gè)，也能夠直接查到 ID2，只掃描一行就結(jié)束了。 ??也就是說使用前綴索引，定義好長度，就可以做到既節(jié)省空間，又不用額外增加太多的查詢成本。

5.2.1.1 給字符串創(chuàng)建前綴索引時(shí)，怎么能夠確定使用多長的前綴呢？

??實(shí)際上，我們?cè)诮⑺饕龝r(shí)關(guān)注的是區(qū)分度，區(qū)分度越高越好。因?yàn)閰^(qū)分度越高，意味著重復(fù)的鍵值越少。因此，我們可以通過統(tǒng)計(jì)索引上有多少個(gè)不同的值來判斷要使用多長的前綴。

-- 1. 首先，你可以使用下面這個(gè)語句，算出這個(gè)列上有多少個(gè)不同的值。

select count(distinct email) as L from SUser;

-- 2. 然后，依次選取不同長度的前綴來看這個(gè)值，比如我們要看一下4~7個(gè)字節(jié)的前綴索引，可以用這個(gè)語句：

select

count(distinct left(email,4)）as L4,

count(distinct left(email,5)）as L5,

count(distinct left(email,6)）as L6,

count(distinct left(email,7)）as L7,

from SUser;

??使用前綴索引很可能會(huì)損失區(qū)分度，所以你需要預(yù)先設(shè)定一個(gè)可以接受的損失比例，比如 5%。然后，在返回的 L4 ~ L7 中，找出不小于 L * 95% 的值，假設(shè)這里 L6、L7 都滿足，你就可以選擇前綴長度為 6。

5.2.1.2 其他方式

??如果前綴字符串的區(qū)分度很低，可以使用如下的方式進(jìn)行索引的創(chuàng)建。

5.2.1.2.1 倒序存儲(chǔ)

??比如要?jiǎng)?chuàng)建索引的是身份證號(hào)，身份證號(hào)前 6 個(gè)是地址碼，如果要?jiǎng)?chuàng)建區(qū)分度高的索引，可能需要?jiǎng)?chuàng)建 12 個(gè)字符以上的索引，但是如果存儲(chǔ)的時(shí)候把身份證號(hào)倒過來存儲(chǔ)，每次查詢的時(shí)候通過 reverse 函數(shù)轉(zhuǎn)換一下。由于身份證號(hào)的最后 6 位沒有地址碼這樣的重復(fù)邏輯，所以最后這 6 位很可能就提供了足夠的區(qū)分度。

5.2.1.2.2 Hash字段

??你可以在表上再創(chuàng)建一個(gè)整數(shù)字段，來保存身份證的校驗(yàn)碼，同時(shí)在這個(gè)字段上創(chuàng)建索引。然后每次插入新記錄的時(shí)候，都同時(shí)用 crc32() 這個(gè)函數(shù)得到校驗(yàn)碼填到這個(gè)新字段。由于校驗(yàn)碼可能存在沖突，也就是說兩個(gè)不同的身份證號(hào)通過 crc32() 函數(shù)得到的結(jié)果可能是相同的，所以你的查詢語句 where 部分要判斷 id_card 的值是否精確相同。這樣，索引的長度變成了4個(gè)字節(jié)，比原來小了很多。

-- 增加一個(gè)字段并在這個(gè)字段上添加索引。

alter table t add id_card_crc int unsigned, add index(id_card_crc);

-- 使用新創(chuàng)建的字段進(jìn)行索引查詢并且再加上精確查詢。

select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string';

5.2.1.2.3 使用倒序存儲(chǔ)和使用 Hash 字段這兩種方法的異同點(diǎn)

??首先，它們的相同點(diǎn)是，都不支持范圍查詢。倒序存儲(chǔ)的字段上創(chuàng)建的索引是按照倒序字符串的方式排序的，已經(jīng)沒有辦法利用索引方式查出身份證號(hào)碼在[ID_X, ID_Y]的所有市民了。同樣地，Hash 字段的方式也只能支持等值查詢。 ??它們的區(qū)別，主要體現(xiàn)在以下三個(gè)方面： ??1. 從占用的額外空間來看，倒序存儲(chǔ)方式在主鍵索引上，不會(huì)消耗額外的存儲(chǔ)空間，而 Hash 字段方法需要增加一個(gè)字段。當(dāng)然，倒序存儲(chǔ)方式使用 4 個(gè)字節(jié)的前綴長度應(yīng)該是不夠的，如果再長一點(diǎn)，這個(gè)消耗跟額外這個(gè) Hash 字段也差不多抵消了。 ??2. 在 CPU 消耗方面，倒序方式每次寫和讀的時(shí)候，都需要額外調(diào)用一次 reverse 函數(shù)，而 Hash字段的方式需要額外調(diào)用一次 crc32() 函數(shù)。如果只從這兩個(gè)函數(shù)的計(jì)算復(fù)雜度來看的話，reverse 函數(shù)額外消耗的 CPU 資源會(huì)更小些。 ??3. 從查詢效率上看，使用 Hash 字段方式的查詢性能相對(duì)更穩(wěn)定一些。因?yàn)?crc32 算出來的值雖然有沖突的概率，但是概率非常小，可以認(rèn)為每次查詢的平均掃描行數(shù)接近 1。而倒序存儲(chǔ)方式畢竟還是用的前綴索引的方式，也就是說還是會(huì)增加掃描行數(shù)。

5.2.2 前綴索引對(duì)覆蓋索引的影響

??先看看如下的SELECT id,email FROM SUser WHERE email='zhangssxyz@xxx.com';，這個(gè) SQL 如果使用 index1 的話，可以利用覆蓋索引，從 index1 查到結(jié)果后直接就返回了，不需要回到 ID 索引再去查一次。而如果使用 index2，就不得不回到 ID 索引再去判斷 email 字段的值。即使你將 index2 的定義修改為 email(18) 的前綴索引，這時(shí)候雖然 index2 已經(jīng)包含了所有的信息，但 InnoDB 還是要回到 id 索引再查一下，因?yàn)橄到y(tǒng)并不確定前綴索引的定義是否截?cái)嗔送暾畔?。也就是說，使用前綴索引就用不上覆蓋索引對(duì)查詢性能的優(yōu)化了，這也是你在選擇是否使用前綴索引時(shí)需要考慮的一個(gè)因素。

6 索引下推

CREATE TABLE t_user (

id int(11) NOT NULL,

id_card varchar(32) DEFAULT NULL,

name varchar(32) DEFAULT NULL,

age int(11) DEFAULT NULL,

ismale tinyint(1) DEFAULT NULL,

PRIMARY KEY (id),

KEY id_card_index (id_card),

KEY name_age_index (name,age)

) ENGINE = InnoDB

??如上面的表，如果要查詢 SELECT * FROM t_user WHERE name like '張%' AND age=10 AND ismale=1;這個(gè) SQL 的加話，根據(jù)上面提到的最左前綴原則，它會(huì)使用name_age_index的索引信息進(jìn)行查詢，那是否是查詢出所有的張姓姓名之后在主鍵索引上進(jìn)行條件篩查呢？ ??在 MySQL5.6 之前，只能是把性別為張的所有數(shù)據(jù)查詢出來，到主鍵索引上找出數(shù)據(jù)行，再對(duì)比字段值。 ??而 MySQL 5.6 引入的索引下推優(yōu)化， 可以在索引遍歷過程中，對(duì)索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。 ??如果在這個(gè)表中有如下信息

INSERT INTO t_user (id,id_card,name,age,ismale) values(1,101,'張三',10,1);

INSERT INTO t_user (id,id_card,name,age,ismale) values(2,201,'張六',30,1);

INSERT INTO t_user (id,id_card,name,age,ismale) values(3,301,'張四',10,2);

INSERT INTO t_user (id,id_card,name,age,ismale) values(4,401,'張五',10,1);

??那么根據(jù)分析會(huì)有如下情況：

??在上面兩個(gè)圖里面，每一條虛線箭頭表示回表一次。 ??在 MySQL5.6 之前，在(name,age)索引里面我特意去掉了 age 的值，這個(gè)過程 InnoDB 并不會(huì)去看 age 的值，只是按順序把" name 第一個(gè)字是’張’"的記錄一條條取出來回表。因此，需要回表 4 次。 ??在 MySQL5.6 開始，InnoDB 在(name,age)索引內(nèi)部就判斷了 age 是否等于 10，對(duì)于不等于 10 的 記錄，直接判斷并跳過。在我們的這個(gè)例子中，只需要對(duì)R1、R3、R4這三條記錄回表取數(shù)據(jù)判斷，就只需要回表 3 次。

7 普通索引和唯一索引在業(yè)務(wù)中應(yīng)該如何選擇？

7.1 示例

-- 假設(shè)你在維護(hù)一個(gè)市民系統(tǒng)，每個(gè)人都有一個(gè)唯一的身份證號(hào)，而且業(yè)務(wù)代碼已經(jīng)保證了不會(huì)寫入兩個(gè)重復(fù)的身份證號(hào)。如果市民系統(tǒng)需要按照身份證號(hào)查姓名，就會(huì)執(zhí)行類似這樣的SQL語句：

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

-- 所以，你一定會(huì)考慮在id_card字段上建索引。

-- 由于身份證號(hào)字段比較大，不建議把身份證號(hào)當(dāng)做主鍵，那么現(xiàn)在你有兩個(gè)選擇，要么給id_card字段創(chuàng)建唯一索引，要么創(chuàng)建一個(gè)普通索引。如果業(yè)務(wù)代碼已經(jīng)保證了不會(huì)寫入重復(fù)的身份證號(hào)，那么這兩個(gè)選擇邏輯上都是正確的。那么應(yīng)該怎么選擇呢？

??接下來，我們就從這兩種索引對(duì)查詢語句和更新語句的性能影響來進(jìn)行分析。

7.2 查詢過程

??1. 對(duì)于普通索引來說，查找到滿足條件的第一個(gè)記錄(5,500)后，需要查找下一個(gè)記錄，直到碰到第一個(gè)不滿足 k = 5 條件的記錄。 ??2. 對(duì)于唯一索引來說，由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止繼續(xù)檢索。 ??那么，這個(gè)不同帶來的性能差距會(huì)有多少呢？答案是，微乎其微。 ??InnoDB 的數(shù)據(jù)是按數(shù)據(jù)頁為單位來讀寫的。也就是說，當(dāng)需要讀一條記錄的時(shí)候，并不是將這個(gè)記錄本身從磁盤讀出來，而是以頁為單位，將其整體讀入內(nèi)存。在 InnoDB 中，每個(gè)數(shù)據(jù)頁的大小默認(rèn)是 16KB。 ??因?yàn)橐媸前错撟x寫的，所以說，當(dāng)找到 k = 5 的記錄的時(shí)候，它所在的數(shù)據(jù)頁就都在內(nèi)存里了。那么，對(duì)于普通索引來說，要多做的那一次“查找和判斷下一條記錄”的操作，就只需要一次指針尋找和一次計(jì)算。當(dāng)然，如果 k = 5 這個(gè)記錄剛好是這個(gè)數(shù)據(jù)頁的最后一個(gè)記錄，那么要取下一個(gè)記錄，必須讀取下一個(gè)數(shù)據(jù)頁，這個(gè)操作會(huì)稍微復(fù)雜一些。但是，對(duì)于整型字段，一個(gè)數(shù)據(jù)頁可以放近千個(gè) key，因此出現(xiàn)這種情況的概率會(huì)很低。所以，我們計(jì)算平均性能差異時(shí)，仍可以認(rèn)為這個(gè)操作成本對(duì)于現(xiàn)在的 CPU 來說可以忽略不計(jì)。

7.3 更新過程

7.3.1 change buffer

??當(dāng)需要更新一個(gè)數(shù)據(jù)頁時(shí)，如果數(shù)據(jù)頁在內(nèi)存中就直接更新，而如果這個(gè)數(shù)據(jù)頁還沒有在內(nèi)存中的話，在不影響數(shù)據(jù)一致性的前提下，InooDB 會(huì)將這些更新操作緩存在 change buffer 中，這樣就不需要從磁盤中讀入這個(gè)數(shù)據(jù)頁了。在下次查詢需要訪問這個(gè)數(shù)據(jù)頁的時(shí)候，將數(shù)據(jù)頁讀入內(nèi)存，然后執(zhí)行 change buffer 中與這個(gè)頁有關(guān)的操作。通過這種方式就能保證這個(gè)數(shù)據(jù)邏輯的正確性。 ??需要說明的是，雖然名字叫作 change buffer，實(shí)際上它是可以持久化的數(shù)據(jù)。也就是說，change buffer 在內(nèi)存中有拷貝，也會(huì)被寫入到磁盤上。 ??將 change buffer 中的操作應(yīng)用到原數(shù)據(jù)頁，得到最新結(jié)果的過程稱為 merge。除了訪問這個(gè)數(shù)據(jù)頁會(huì)觸發(fā) merge 外，系統(tǒng)有后臺(tái)線程會(huì)定期 merge。在數(shù)據(jù)庫正常關(guān)閉的過程中，也會(huì)執(zhí)行 merge 操作。 ??顯然，如果能夠?qū)⒏虏僮飨扔涗浽?change buffer，減少讀磁盤，語句的執(zhí)行速度會(huì)得到明顯的提升。而且，數(shù)據(jù)讀入內(nèi)存是需要占用 buffer pool 的，所以這種方式還能夠避免占用內(nèi)存，提高內(nèi)存利用率。

7.3.2 什么條件下可以使用 change buffer 呢？

??對(duì)于唯一索引來說，所有的更新操作都要先判斷這個(gè)操作是否違反唯一性約束。比如，要插入(4,400)這個(gè)記錄，就要先判斷現(xiàn)在表中是否已經(jīng)存在 k = 4 的記錄，而這必須要將數(shù)據(jù)頁讀入內(nèi)存才能判斷。如果都已經(jīng)讀入到內(nèi)存了，那直接更新內(nèi)存會(huì)更快，就沒必要使用 change buffer 了。因此，唯一索引的更新就不能使用change buffer，實(shí)際上也只有普通索引可以使用。 ??change buffer 用的是 buffer pool 里的內(nèi)存，因此不能無限增大。 change buffer 的大小，可以通過參數(shù)innodb_change_buffer_max_size 來動(dòng)態(tài)設(shè)置。這個(gè)參數(shù)設(shè)置為 50 的時(shí)候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

7.3.2 更新 InnoDB 的處理流程

??更新的目標(biāo)頁在內(nèi)存中: ??1. 對(duì)于唯一索引來說，找到 3 和 5 之間的位置，判斷到?jīng)]有沖突，插入這個(gè)值，語句執(zhí)行結(jié)束； ??2. 對(duì)于普通索引來說，找到 3 和 5 之間的位置，插入這個(gè)值，語句執(zhí)行結(jié)束。 ??這樣看來，普通索引和唯一索引對(duì)更新語句性能影響的差別，只是一個(gè)判斷，只會(huì)耗費(fèi)微小的 CPU 時(shí)間。 ??要更新的目標(biāo)頁不在內(nèi)存中: ??1. 對(duì)于唯一索引來說，需要將數(shù)據(jù)頁讀入內(nèi)存，判斷到?jīng)]有沖突，插入這個(gè)值，語句執(zhí)行結(jié)束； ??2. 對(duì)于普通索引來說，則是將更新記錄在 change buffer，語句執(zhí)行就結(jié)束了。 ??將數(shù)據(jù)從磁盤讀入內(nèi)存涉及隨機(jī) IO 的訪問，是數(shù)據(jù)庫里面成本最高的操作之一。 change buffer 因?yàn)闇p少了隨機(jī)磁盤訪問，所以對(duì)更新性能的提升是會(huì)很明顯的。

7.3.3 change buffer 的使用場景

??因?yàn)?merge 的時(shí)候是真正進(jìn)行數(shù)據(jù)更新的時(shí)刻，而 change buffer 的主要目的就是將記錄的變更動(dòng)作緩存下來，所以在一個(gè)數(shù)據(jù)頁做 merge 之前，change buffer 記錄的變更越多（也就是這個(gè)頁面上要更新的次數(shù)越多），收益就越大。 ??因此，對(duì)于寫多讀少的業(yè)務(wù)來說，頁面在寫完以后馬上被訪問到的概率比較小，此時(shí) change buffer 的使用效果最好。這種業(yè)務(wù)模型常見的就是賬單類、日志類的系統(tǒng)。 ??反過來，假設(shè)一個(gè)業(yè)務(wù)的更新模式是寫入之后馬上會(huì)做查詢，那么即使?jié)M足了條件，將更新先記錄在 change buffer，但之后由于馬上要訪問這個(gè)數(shù)據(jù)頁，會(huì)立即觸發(fā) merge 過程。這樣隨機(jī)訪問 IO 的次數(shù)不會(huì)減少，反而增加了 change buffer 的維護(hù)代價(jià)。所以，對(duì)于這種業(yè)務(wù)模式來說，change buffer 反而起到了副作用。這個(gè)時(shí)候應(yīng)該關(guān)閉 change buffer。而在其他情況下，change buffer 都能提升更新性能。 ??在實(shí)際使用中，你會(huì)發(fā)現(xiàn)，普通索引和 change buffer 的配合使用，對(duì)于數(shù)據(jù)量大的表的更新優(yōu)化還是很明顯的。特別地，在使用機(jī)械硬盤時(shí)，change buffer 這個(gè)機(jī)制的收效是非常顯著的。所以，當(dāng)你有一個(gè)類似’歷史數(shù)據(jù)’的庫，并且出于成本考慮用的是機(jī)械硬盤時(shí)，那你應(yīng)該特別關(guān)注這些表里的索引，盡量使用普通索引，然后把 change buffer 盡量開大，以確保這個(gè)“歷史數(shù)據(jù)”表的數(shù)據(jù)寫入速度。

7.3.4 change buffer 和 redo log的區(qū)別

??redo log相關(guān)的內(nèi)容請(qǐng)查看這篇文章。 ??假設(shè)要執(zhí)行下面這個(gè) SQL。

insert into t(id,k) values(id1,k1),(id2,k2);

??我們假設(shè)當(dāng)前 k 索引樹的狀態(tài)，查找到位置后，k1 所在的數(shù)據(jù)頁在內(nèi)存中，k2 所在的數(shù)據(jù)頁不在內(nèi)存中。如下圖所示是帶change buffer的更新狀態(tài)圖。

??分析這條更新語句，你會(huì)發(fā)現(xiàn)它涉及了四個(gè)部分：內(nèi)存、redo log（ib _log_fileX）、 數(shù)據(jù)表空間（t.ibd）、系統(tǒng)表空間（ibdata1）。這條更新語句做了如下的操作（按照?qǐng)D中的數(shù)字順序）： ??1. Page 1 在內(nèi)存中，直接更新內(nèi)存； ??2. Page 2 沒有在內(nèi)存中，就在內(nèi)存的 change buffer 區(qū)域，記錄下“我要往 Page 2 插入一行”這個(gè)信息。 ??3. 將上述兩個(gè)動(dòng)作記入redo log中（圖中3和4）。 ??做完上面這些，事務(wù)就可以完成了。所以，你會(huì)看到，執(zhí)行這條更新語句的成本很低，就是寫了兩處內(nèi)存，然后寫了一處磁盤（兩次操作合在一起寫了一次磁盤），而且還是順序?qū)懙?。同時(shí)，圖中的兩個(gè)虛線箭頭，是后臺(tái)操作，不影響更新的響應(yīng)時(shí)間。 ??下面是在發(fā)生更新語句不久之后的讀請(qǐng)求：

??1. 讀 Page 1 的時(shí)候，直接從內(nèi)存返回。 ??2. 要讀 Page 2 的時(shí)候，需要把 Page 2 從磁盤讀入內(nèi)存中，然后應(yīng)用 change buffer 里面的操作日志，生成一個(gè)正確的版本并返回結(jié)果?？梢钥吹剑钡叫枰x Page 2 的時(shí)候，這個(gè)數(shù)據(jù)頁才會(huì)被讀入內(nèi)存。 ??所以，如果要簡單地對(duì)比這兩個(gè)機(jī)制在提升更新性能上的收益的話，redo log 主要節(jié)省的是隨機(jī)寫磁盤的IO消耗（轉(zhuǎn)成順序?qū)懀?，而change buffer主要節(jié)省的則是隨機(jī)讀磁盤的IO消耗。

柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫 MySQL的索引

http://yzkb.51969.com/

精彩文章