索引使用的基本原則

今天你做題了嗎發表於2020-06-27
索引

　　我們在建表的時候最好建立自增主鍵，這樣非主鍵索引佔用的空間就比較小。這種方式插入資料都是追加資料，不涉及到資料頁分裂。但也不是所有場景下都需要自增主鍵，比如有些業務場景的需求是只有一個索引並且該索引必須是唯一索引，由於沒有其他索引，就不用考慮輔助索引的儲存空間消耗，可以直接將這個索引設為主鍵。

覆蓋索引

　　比如ID、k是表T中的主鍵索引和輔助索引，現在有一條查詢需求:

select ID from T where k between 3 and 5;

　　因為k索引樹中葉子結點值存放的就是ID值，可以直接查詢到結果，這時就不需要再回表。如果查詢的結果是聯合索引的欄位，同樣不需要回表操作。索引k覆蓋了我們的查詢需求，這種索引就叫覆蓋索引。由於覆蓋索引可以減少搜尋索引樹的次數，因此這是一個常用的資料庫效能優化方法。

聯合索引

　　簡要的說就是由多個欄位組成的索引，假設現在有張市民資訊表T：

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=I

　　裡面有身份證號 id_card、姓名 name等欄位，有個高頻請求：根據身份證號查詢姓名，那麼我們就可以建立 (id_card, name) 聯合索引，這裡會用到覆蓋索引，不再需要回表查詢整行記錄。索引的維護是有代價的，怎樣建立聯合索引需要考慮具體的業務場景。

最左字首原則

　　有時候我們會遇到不常見的查詢請求，比如根據身份證號查詢市民的家庭住址。如果我們走全表掃描效率太低，單獨建立一個索引又浪費空間，這會就可以用B+樹索引的最左字首原則。用聯合索引 (name, age) 來說明這個概念：

        

　　比如需求是查詢所有名字是張三的人，可以快速定位到 ID4，然後向後掃描所有滿足條件的資料。如果查詢姓張的人，sql裡條件部分可以這樣寫 “ where name like '張%' ”，同樣也能用到上面的聯合索引。這裡最左字首可以是聯合索引的最左N個欄位，也可以是字串索引的最左M個字元。

　　那我們該如何建立聯合索引呢？首先要遵循一個原則：如果通過調整索引順序，可以少維護一個索引，那麼這個順序就是優先考慮的。比如上面的（id_card，name）索引，根據 id_card 查詢家庭住址就不要再建立 聯合索引了。

如果既有聯合查詢，又有基於a、b各自的查詢，比如查詢語句中只有索引b是不能用聯合索引 (a，b)的。那麼就要建立兩個索引，考慮的原則是索引佔用空間。比如市民資訊表中 name 欄位比 age 佔用空間大，就建立一個 (name, age)聯合索引和一個 (age)的單欄位索引。

　　對於聯合索引，還有一個問題：欄位中不符合最左字首的部分會怎麼樣？同樣用上面的 (name, age) 索引來說明，現在有一個需求：“查詢出名字第一個字是張，年齡為10歲的男孩”。sql是這樣的：

select * from T where name like '張%' and age = 10 and ismale = 1;

在MySQL5.6之前，只能不停的回表查到最後的結果；5.6之後引入了索引下推優化，就是在索引遍歷過程中，對索引中包含的欄位中先做判斷，過濾掉不滿足條件的記錄，減少回表的次數。用兩個圖來解釋：分別是優化前後

可以看到原來是4次回表，優化後變成2次，減少了對資源的訪問。

相關文章