我們在上篇 高效能MySQL實戰（一）：表結構 中已經建立好了表結構，這篇我們則是針對已有的表結構和搜尋條件為表建立索引。

我們還是先將表結構的初始化 SQL 拿過來：

CREATE TABLE `service_log` (  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',  `service_type` smallint NOT NULL DEFAULT -1 COMMENT '介面型別',  `service_name` varchar(30) DEFAULT '' COMMENT '介面名稱',  `service_method` tinyint NOT NULL DEFAULT -1 COMMENT '介面方式 1-HTTP 2-TCP',  `serial_no` int DEFAULT -1 COMMENT '訊息序號',  `service_caller` tinyint DEFAULT -1 COMMENT '呼叫方',  `service_receiver` tinyint DEFAULT -1 COMMENT '接收方',  `status` tinyint DEFAULT 10 COMMENT '狀態 10-成功 20-異常',  `error_message` varchar(200) DEFAULT '' COMMENT '異常資訊',  `message` varchinar(1000) DEFAULT '' COMMENT '報文內容',  `create_user` varchar(50) DEFAULT '' COMMENT '建立者',  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',  `update_user` varchar(50) DEFAULT '' COMMENT '更新者',  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',  `is_delete` tinyint NOT NULL DEFAULT 0 COMMENT '刪除標誌',  `ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '時間戳',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='介面呼叫日誌';

現有如下搜尋條件：

1. 根據 介面名稱 來搜尋對應的呼叫日誌

2. 根據 狀態 查詢成功或異常的呼叫日誌

3. 根據 介面名稱 和 狀態 查詢呼叫日誌

4. 根據 訊息序號 來獲取一組呼叫日誌

5. 根據 建立時間 的時間範圍查詢呼叫日誌

6. 根據 報文內容 查詢呼叫日誌

索引是提升查詢效能最有效的手段，它可以快速定位到記錄，大大減少需要掃描的資料量，將隨機 I/O 變為順序 I/O，而且 B+ Tree 索引會根據索引值順序儲存，所以也能夠應用於排序和分組。

為了在檢視這些介面呼叫日誌時有更好的體驗，那麼我們就需要根據搜尋條件來建立索引。

索引的型別儘量小

我們先關注下介面名稱的搜尋條件，我們可以發現：介面名稱和介面型別這兩個欄位都能查詢到相同型別介面的日誌資料，只不過它們的型別不同，前者是字串型別，後者是整型。

這時我們需要注意：選擇建立索引的列的型別要儘量小。因為每建立一個索引就相當於建立了“一棵 B 樹”，資料型別越小，那麼索引佔用的儲存空間就越少，在一個資料頁內就能存放更多的記錄，因此磁碟 I/O 帶來的效能損耗也就越少。

除此之外，在 MySQL 內部整型資料的比較相比於字串型別的比較更簡單，效率也更高。所以，我們會選擇為介面型別建立索引而不是為介面名稱建立索引。

為介面型別列新增索引的 SQL 如下：

alter table service_log add index index_service_type(`service_type`);

根據條件 4，訊息序號列也需要新增索引：

alter table service_log add index index_serial_no(`serial_no`);
冗餘和重複索引

同樣地，根據搜尋條件 2，我們為狀態列新增上索引：

alter table service_log add index index_status(`status`);

這時，我們再看看條件 3，需要為介面型別和狀態新增聯合索引。不過需要注意的是：該聯合索引和已經新增的介面型別索引是重複索引，根據聯合索引的最左匹配原則，第一列為介面型別的聯合索引同樣能為查詢條件只有介面型別的查詢服務，所以我們需要將原有為介面型別新增的索引刪掉，再建立新的介面型別和狀態的聯合索引。

-- 刪除 index_service_typealter table service_log drop index index_service_type;
-- 新增聯合索引alter table service_log add index index_service_type_status(`service_type`, `status`);

建立聯合索引時有一個重要的經驗性法則：將列值重複率最低的放到索引的最前列。如果重複的值過多，那麼掃描到的資料行數也就越多，這樣就會使得回表的壓力很大。

通常情況下，把WHERE 條件裡面的列都獨立地建立多個單列索引，在大部分情況下並不能提高MySQL的查詢效能。我們應該儘可能地去考慮索引列的順序或者建立一個全覆蓋索引。

為重複率低的列建立索引

在我們的實際業務中，介面呼叫的狀態幾乎所有都是成功，很少會出現失敗的情況，所以這時我們為狀態列建立索引並不是很合適。因為如果我們查詢所有狀態為成功的資料，那麼它可能會執行太多次的回表操作，導致查詢效率下降，可能還不如執行全表掃描來的快。但是我們再考慮另一種情況，有時我們會根據狀態為失敗的記錄做業務分析或排查問題，失敗的資料是比較少的，如果我們透過索引查詢就會非常高效，所以該列索引還有必要保留。

只不過我們在這裡需要做一個處理：如果狀態為成功時，我們為生成的 SQL 語句新增上忽略索引的關鍵字 ignore index(index_name)，那麼這樣我們就能達到在查詢成功狀態的資料時全表掃描，而在查詢失敗狀態的資料時使用索引了。

select * from service_log ignore index(index_status)where status = 10;

全值匹配和按值範圍匹配的時間列

條件 5 根據建立時間來進行全值匹配和按值範圍匹配非常適合建立索引：

alter table service_log add index index_create_time(`create_time`);

全文索引

FULLTEXT 全文索引是一種特殊型別的索引，它查詢的是文字中的關鍵詞，而不是直接比較索引中的值，更類似於搜尋引擎所做的事情。在查詢時適用於 MATCH AGAINST 操作，而不是普通的 WHERE 條件。

對於條件 5，我們需要在介面請求的報文中根據關鍵字，比如說包裹號來查詢特定的資料，這就使得我們需要為報文內容列建立全文索引，SQL 如下：

alter table service_log add fulltext fulltext_message(`message`);
-- 執行查詢時的語句select * from service_log where match(message) against('123456');

全文索引在日常使用的並不多，它有許多需要注意的細節，如停用詞、詞幹、複數和布林搜尋等，具體的詳情資訊可以檢視文末的參考文獻。

那麼，最終初始化表結構的 DDL 語句如下：

CREATE TABLE `service_log` (  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',  `service_type` smallint NOT NULL DEFAULT -1 COMMENT '介面型別',  `service_name` varchar(30) DEFAULT '' COMMENT '介面名稱',  `service_method` tinyint NOT NULL DEFAULT -1 COMMENT '介面方式 1-HTTP 2-TCP',  `serial_no` int DEFAULT -1 COMMENT '訊息序號',  `service_caller` tinyint DEFAULT -1 COMMENT '呼叫方',  `service_receiver` tinyint DEFAULT -1 COMMENT '接收方',  `status` tinyint DEFAULT 10 COMMENT '狀態 10-成功 20-異常',  `error_message` varchar(200) DEFAULT '' COMMENT '異常資訊',  `message` varchar(1000) DEFAULT '' COMMENT '報文內容',  `create_user` varchar(50) DEFAULT '' COMMENT '建立者',  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',  `update_user` varchar(50) DEFAULT '' COMMENT '更新者',  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',  `is_delete` tinyint NOT NULL DEFAULT 0 COMMENT '刪除標誌',  `ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '時間戳',  PRIMARY KEY (`id`),  index index_serial_no(`serial_no`),  index index_status(`status`),  index index_create_time(`create_time`),  index index_service_type_status(`service_type`, `status`),  fulltext fulltext_message(`message`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='介面呼叫日誌';

字首索引

一般情況下，對於 VARCHAR、BLOB 和 TEXT 等相關型別的列建立索引時，為了提升索引的效能和節省索引空間，會只對欄位的前一部分字元進行索引，不過這樣做的缺點是使得索引的選擇性降低。

索引的選擇性是指不重複的索引值和記錄總數的比值，可以理解為重複率越低選擇性越高，唯一索引的選擇性為 1。

在我們的資料庫表示例中，並沒有欄位適合建立字首索引。其中報文內容列也並不適合建立字首索引，因為這些報文的字首都很相似，而且我們在執行查詢時並不會帶上字首，而是隻使用關鍵詞資訊查詢。

但是，字首索引比較重要，所以我們在這裡也對建立字首索引的方法介紹一下。

MySQL 並不支援對這些長字元型別列的完整內容進行索引，我們選擇字首長度的關鍵點在於：既要保證選擇足夠長的字首使得選擇性較高，同時又不能太長防止佔用太多的空間。

可以根據如下方法來確定字首的長度：

首先，檢視要新增索引的列出現最頻繁的一些值：

select count(0) as c, specific_columnfrom specific_tablegroup by specific_columnorder by c desclimit 10;

之後先從 3 個字首字母開始匹配嘗試：

select count(0) as c, left(specific_column, 3) as preffrom specific_tablegroup by preforder by c desclimit 10;

慢慢地增加字首長度，直到這個字首的選擇性接近我們首次查詢的完整列的選擇性即可。

或者，採用如下的方法，先計算出完整列的選擇性：

select count(distinct specific_column) / count(0)from specific_table;

然後分別計算不同字首的選擇性，直到找到與完整列接近的選擇性字首長度即可：

select count(distinct left(specific_column, 3)) / count(0) as sel3,count(distinct left(specific_column, 4)) / count(0) as sel4,count(distinct left(specific_column, 5)) / count(0) as sel5,count(distinct left(specific_column, 6)) / count(0) as sel6,count(distinct left(specific_column, 7)) / count(0) as sel7from specific_table;

不過，也有例外的情況，那就是即使現在我們選擇了比較接近完整列選擇性的字首，但資料的分佈仍然很不均勻。

這時我們需要用該字首執行如下查詢，並與完整列查詢出的數目作比較，觀察這些出現頻率最高的字首值與完整列出現頻率是否接近，否的話需要再將字首值調大。

select count(0) as c, left(specific_column, 5) as preffrom specific_tablegroup by preforder by c desclimit 10;
-- 完整列的出現頻率select count(0) as c, specific_columnfrom specific_tablegroup by specific_columnorder by c desclimit 10;

最後，找到合適的字首數建立字首索引可以使用如下 SQL：

alter table specific_table add index index_specific_column(specific_column(7));

雖然字首索引能夠使索引更小，更快，但是我們不能使用字首索引做 ORDER BY 和 GROUP BY 操作，也無法使用字首索引做索引覆蓋。

下文中我們所說的索引如果沒有特別指明型別，那麼就代表我們說的是 B+ Tree 索引，它使用 B+ Tree 資料結構來儲存資料。

B+ Tree 會將所有的資料儲存在葉子節點上，並且透過雙向連結串列將葉子節點連線起來。

聚簇索引

聚簇索引並不是一種單獨的索引型別，而是一種資料儲存方式，InnoDB 聚簇索引在資料頁中同時儲存索引和資料行，這使得它的資料訪問相比於非聚簇索引（二級索引）要快。

聚簇的意思是說資料行和相鄰的鍵值緊湊的儲存在一起，因為無法同時把資料行放在兩個不同的地方，所以一個表只能有一個聚簇索引。InnoDB 根據 主鍵 聚簇資料，如果沒有定義主鍵，InnoDB 會自動生成一個唯一的隱式主鍵作為聚簇索引。

我們建立一個簡單的表，並插入一些資料，來看一下 B+ Tree 索引的資料結構圖：

create table demo (    c1 int,    c2 int,    c3 char(1),    primary key(c1))engine=InnoDB;

MySQL 是透過資料頁來儲存資料的，每個頁的大小預設為 16KB，在每個資料頁中都預設有最小記錄Infimum和最大記錄Supremum，如下圖所示：

我們可以發現在葉子節點中儲存了所有資料行，每個頁之間透過頁檔案頭部（File Header）記錄的雙向連結串列指標進行連線，資料記錄之間透過單向連結串列連線，單向連結串列的指標記錄在每行資料記錄的記錄頭資訊中。

在非葉子節點中，我們可以發現記錄的資訊只有主鍵值和對應的頁號，因此資料頁能存放的資料更多，B+ Tree 也就能更加 “矮胖”，這樣就能使得磁碟 I/O 更少。一般情況下我們用到的 B+ Tree 不會超過 4 層。

B+ Tree 按照索引列資料的大小順序排序儲存，所以很適合按照範圍來查詢。每次搜尋資料都從索引的根節點開始，透過比較節點中的值和要查詢的值來找到合適的指標進入下層子節點，最終在葉子節點中找到或找不到對應的記錄。

聚簇索引能夠加快我們訪問資料的速度，但是它也有一些侷限性我們需要了解一下：

• 聚簇索引最大限度地提高了 I/O 密集型應用的效能，但如果資料全部都放在記憶體中，則訪問的順序就沒那麼重要了，聚簇索引也就沒什麼優勢了

隨著 RAM 變得更便宜，而且許多資料集不是那麼大，所以將它們全部儲存在記憶體中是非常可行的，包括可能分佈在多個伺服器上，這也促進了記憶體資料庫的發展。

• 插入速度嚴重依賴於插入順序。按照主鍵的順序插入行是將資料載入到 InnoDB 表中最快的方式。但如果不是按照主鍵的順序插入，會因頁分裂影響插入速度。最好避免隨機的聚簇索引，特別是對於 I/O 密集型的應用

• 聚簇索引列更新的代價很高，因為它會強制 InnoDB 將每個被更新的行移動到新的位置，這也會發生頁分裂，導致效能下降

二級索引

二級索引是非聚簇索引，InnoDB 引擎在 B+ Tree 的葉子節點儲存的不是完成的資料記錄，而只是索引列和主鍵列的值。如果在查詢時沒有發生覆蓋索引的話，需要根據主鍵值進行回表操作以獲取需要的結果。

二級索引是關係型資料庫的基礎，並且在文件資料庫中也很普遍。許多鍵值儲存（如 HBase 和 Volde-mort）為了減少實現的複雜度而放棄了二級索引，但是一些（如 Riak）已經開始新增它們，因為它們對於資料模型實在是太有用了。並且次級索引也是 Solr 和 Elasticsearch 等搜尋伺服器的基石。

實際上，有兩種用二級索引對文件資料庫進行分割槽的方法：基於文件（document-based）的分割槽和基於關鍵詞（term-based）的分割槽。

*基於文件的分割槽

假設我們有一個汽車銷售網站，每條資料都有唯一的 ID，我們稱之為文件 ID。我們使用文件 ID 進行分割槽，併為汽車顏色欄位建立二級索引，分割槽結果如下圖所示：

這樣的二級索引分配方法，使得每個分割槽都是獨立的：每個分割槽自己維護自己的索引，它不關心其他分割槽的資料，這種文件分割槽索引也被稱為本地索引。

當我們查詢紅色的汽車時，需要將請求釋出到所有的分割槽，併合並所有返回的結果，這種查詢資料庫的方法被稱為 分散/聚集，可能會使得二級索引查詢資料比較耗時。

*基於關鍵詞的分割槽

我們也可以構建一個覆蓋所有分割槽資料的全域性索引，比如我們將 a 到 r 開頭的顏色的二級索引儲存在分割槽 0 中，將 s 到 z 的儲存在分割槽 1 中，如下圖所示：

我們將這種分割槽方法稱為關鍵詞分割槽，根據關鍵詞本身分割槽對於範圍掃描非常有用，比如說我現在想獲取 a 到 r 開頭的顏色的所有汽車資料；而對關鍵詞的雜湊分割槽又能夠提供分割槽負載均衡的能力。

基於關鍵詞分割槽的全域性索引優於文件分割槽索引的地方在於它的讀取更加高效，並不需要將請求打到所有分割槽上，只需要將請求傳送到含有對應關鍵詞的分割槽即可，而它的缺點在於對單個分割槽文件的寫入可能會產生多個分割槽的索引的資料變更，需要協調跨分割槽的分散式事務。

覆蓋索引

覆蓋索引可以簡單地理解成查詢只需要訪問索引列而無需訪問其他資料列。

優秀的索引設計不單單隻考慮 WHERE 條件，也會根據想要查詢的列去綜合分析。如果只需要索引列的話，那麼覆蓋索引是非常有用的工具，它能避免回表操作，這樣 MySQL 就會極大地減少資料訪問量，而且索引佔用的空間很小，將這些資料快取在記憶體中的壓力遠小於快取所有相關資料行。

如果業務無需查詢其他列，那麼我們最好把業務需要的列放在查詢列表中，以實現覆蓋索引，而不是簡單地以 * 來替代；在某些情況下，可以根據想要查詢的列，對所使用的索引進行擴充套件，即增加想要查詢的列達到覆蓋索引的目的。

當執行一個覆蓋索引的查詢時，在 EXPLAIN 的 Extra 列可以看到 Using index 的資訊。

自適應雜湊索引

它是 InnoDB 的一個特性，當 InnoDB 發現某些索引值被非常頻繁的訪問時，它會在原有的 B+ Tree 索引之上，再在記憶體中構建一個雜湊索引，以此來加快對應資料的訪問。這個過程是自動化的，我們無法進行干預，不過可以透過引數配置將其關閉。

參考資料:

[1] 《資料密集型應用系統設計》：第三章、第六章

[2] 《高效能 MySQL 第四版》：第七章

[3] 《MySQL 是怎樣執行的》：第四、五、六、七章

[4] 14.6.2.4 InnoDB Full-Text Indexes