MySQL索引最左比對原則及優化原理（上）

1 索引的好處

• 大大減少存儲引擎需要掃描的資料量
• 排序以避免使用臨時表
• 把随機I/O變為順序I/O

2 執行個體

執行 select * from T where k between 3 and 5，需要幾次樹的搜尋，掃描多少行？

• 建立表

• 

• 插入資料

• 

• InnoDB索引組織結構

• 

• SQL查詢語句的執行流程：

在k索引樹找到k=3，取得 ID 300

再到ID樹查到ID 300對應的R3

在k樹取下個值5，取得ID 500

再回到ID樹查到ID 500對應R4

在k樹取下個值6，不滿足條件，循環結束

回到主鍵索引樹搜尋的過程，稱為回表。

查詢過程讀了k索引樹的3條記錄（步驟135），回表兩次（24）

由于查詢結果所需資料隻在主鍵索引有，不得不回表。那麼，有無可能經過索引優化，避免回表？

3 覆寫索引

執行語句

select ID from T where k between 3 and 5

隻需查ID值，而ID值已在k索引樹，是以可直接提供結果，不需回表。即在該查詢，索引k已“覆寫”我們的查詢需求，稱為覆寫索引。

覆寫索引可減少樹的搜尋次數，顯著提升查詢性能，使用覆寫索引是個常用性能優化手段。

使用覆寫索引在索引k上其實讀了三個記錄，R3~R5（對應的索引k上的記錄項）

但對于Server層，就是找引擎拿到兩條記錄，是以MySQL認為掃描行數是2。

問題

在一個市民資訊表，有必要将身份證号和名字建立聯合索引？

假設這個市民表的定義：

CREATE TABLE `tuser` (
  `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` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB      

身份證号是市民唯一辨別。有根據身份證号查詢市民資訊的，隻要在身份證号字段建立索引即可。再建立一個（身份證号、姓名）聯合索引，是不是浪費空間？

如果現在有一個高頻請求，根據身份證号查詢姓名，聯合索引就有意義了。可在這個高頻請求上用到覆寫索引，不再回表查整行記錄，減少了執行時間。

當然索引字段的維護總是有代價。建立備援索引支援覆寫索引就需權衡考慮。

2 何時用索引

(1) 定義有主鍵的列一定要建立索引 : 主鍵可以加速定位到表中的某行

(2) 定義有外鍵的列一定要建立索引 : 外鍵列通常用于表與表之間的連接配接，在其上建立索引可以加快表間的連接配接

(3) 對于經常查詢的資料列最好建立索引

① 對于需要在指定範圍内快速或頻繁查詢的資料列，因為索引已經排序，其指定的範圍是連續的，查詢可以利用索引的排序，加快查詢的時間

② 經常用在 where子句中的資料列，将索引建立在where子句的集合過程中，對于需要加速或頻繁檢索的資料列，可以讓這些經常參與查詢的資料列按照索引的排序進行查詢，加快查詢的時間

如果為每一種查詢都設計個索引，索引是不是太多？

如果我現在要按身份證号去查家庭位址？雖然該需求機率不高，但總不能讓它全表掃描？

但單獨為一個不頻繁請求建立（身份證号，位址）索引又有點浪費。怎麼做？

B+樹這種索引，可利用索引的“最左字首”，來定位記錄。

為了直覺地說明這個概念，用（name，age）聯合索引分析。

索引項按照索引定義出現的字段順序排序。

當邏輯需求是查到所有名字“張三”的，可快速定位到ID4，然後向後周遊得到所有結果。

要查所有名字第一個字“張”的，條件"where name like ‘張%’"。也能夠用上索引，查找到第一個符合條件的記錄是ID3，然後向後周遊，直到不滿足。

不隻是索引的全部定義，隻要滿足最左字首，就可利用索引加速。

最左字首可以是

聯合索引的最左N個字段

字元串索引的最左M個字元

聯合索引内的字段順序

• 标準

  索引的複用能力。因為可以支援最左字首，是以當已經有了(a,b)這個聯合索引後，一般就不需要單獨在a上建立索引了。

• 原則

  如果調整順序，可少維護一個索引，那麼這順序優先考慮。

• 為高頻請求建立(身份證号，姓名）聯合索引，并用這索引支援“身份證号查位址”需求。

如果既有聯合查詢，又有基于a、b各自的查詢？

查詢條件裡隻有b的，無法使用(a,b)聯合索引，這時不得不維護另外一個索引，即需同時維護(a,b)、(b) 兩個索引。

這時要考慮原則就是空間

比如市民表，name字段比age字段大 ，建議建立一個（name,age)的聯合索引和一個(age)的單字段索引

3 索引優化

MySQL的優化主要分為

• 結構優化（Scheme optimization）
• 查詢優化（Query optimization）
• 讨論的高性能索引政策主要屬于結構優化。

為了讨論索引政策，需要一個資料量不算小的資料庫作為示例

選用MySQL官方文檔中提供的示例資料庫之一：employees

這個資料庫關系複雜度适中，且資料量較大。下圖是這個資料庫的E-R關系圖（引用自MySQL官方手冊）：

3.1 最左字首原理與相關優化

要知道什麼樣的查詢會用到索引，和B+Tree中的“最左字首原理”有關。

聯合索引（又名複合索引）

MySQL中的索引可以以一定順序引用多列，這種索引叫做

聯合索引

，是個有序元組<a1, a2, …, an>。

如何選擇索引列的順序

• 經常會被使用到的列優先
• 選擇性高的列優先
• 寬度小的列優先

覆寫索引(Covering Indexes)

包含滿足查詢的所有列。隻通路索引的查詢，隻需讀索引而不用讀資料，大大提高查詢性能。

優點

索引項通常比記錄要小，使得MySQL通路更少資料

索引都按值排序存儲，相對于随機通路記錄，需要更少I/O

大多資料引擎能更好的緩存索引。比如MyISAM隻緩存索引

因為InnoDB使用聚集索引組織資料，若二級索引中包含查詢所需的資料，就無需回表

可以優化緩存，減少磁盤IO操作

可以減少随機IO，變随機IO操作變為順序IO操作

可以避免MyISAM表進行系統調用

覆寫索引隻有B-TREE索引存儲相應的值，并非所有存儲引擎都支援覆寫索引(Memory/Falcon就不支援)。

對于索引覆寫查詢(index-covered query)，使用EXPLAIN時，可以在Extra列中看到Using index。

在大多數引擎中，隻有當查詢語句所通路的列是索引的一部分時，索引才會覆寫

但是，InnoDB不限于此，InnoDB的二級索引在葉節點中存儲了primary key的值

覆寫索引失效場景

存儲引擎不支援覆寫索引

查詢中使用了太多的列

使用了雙%号的like查詢

使用覆寫索引查詢資料

select *不能用覆寫索引

以employees.titles表為例，下面先檢視其上都有哪些索引：

從結果中可以看到titles表的主索引為<emp_no, title, from_date>，還有一個輔助索引<emp_no>

為了避免多個索引使事情變複雜（MySQL的SQL優化器在多索引時行為比較複雜），我們将輔助索引drop掉

ALTER TABLE employees.titles DROP INDEX emp_no;      

這樣就可以專心分析索引PRIMARY