MySQL 筆記整理（5） --深入淺出索引（下）

筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》

5） --深入淺出索引（下）

　　這次的筆記從一個簡單的查詢開始：

　　建表語句是這樣的

mysql> 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 values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

　　如果要執行 select * from T where k between 3 and 5這條語句，需要執行幾次搜索操作呢，會掃描多少行呢？由上面的建表及初始化語句我們很容易可以看出，表T上有ID欄位的主鍵索引，也有K欄位上的非主鍵索引。數據中滿足條件K在3和5之間的記錄有兩條，分別是 (300,3,'cc'),(500,5,'ee')。我們在K欄位上創建了索引，所以在執行這條語句時，MySQL就會使用這個索引。如果你看了我上篇筆記的話應該知道，K上的索引是非主鍵索引，而非主鍵索引存儲的其實是主鍵的值。所以這條語句的執行流程大致是下麵這個樣子的。

1. 在K索引樹上找到K=3的記錄，取得ID=300.
2. 再到ID索引樹查到ID=300對應的記錄。（第一次回表）
3. 在K索引樹上找到K=5的記錄，取得ID=500.
4. 再到ID索引樹查到ID=500對應的記錄。（第二次回表）
5. 在K索引樹取下一個值K=6，不符合條件，查詢結束。

所以上面這條語句查詢了3條記錄K=3，5，6.回表了兩次。

　　我們註意到一個細節，select * from T where k between 3 and 5這條語句查詢的結果是*，也就是所有的欄位的內容都會返回。而如果只使用K上的索引，則只能查詢到ID的值與K的值，並不能返回符合要求的所有欄位的值。那麼如果K上的索引查詢的結果可以滿足要求，是不是就不需要回表了呢?答案是肯定的。 如語句 SELECT ID from T where k between 3 and 5; ID的值已經在K索引樹上了，不需要回表就能返回結果。即索引“K”已經覆蓋了我們的查詢需求，我們稱為覆蓋索引。

　　由於覆蓋索引可以減少樹的搜索次數，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優化手段。

　　上面的例子中還有一個小細節需要註意一下：在引擎內部使用索引K其實讀了三個記錄，但對於MySQL的Server層來說，它就是找引擎拿到了兩條記錄。

最左首碼原則：

　　InnoDB使用B+樹這種索引模型，由於B+樹的索引結構，可以利用索引的"最左首碼"來定位記錄。如你在‘name’欄位上建立了索引，當你的語句是 where name like ‘張%’時是可以使用索引的。MySQL會利用這個索引向後遍歷，直到不滿足條件為止。不止是索引的全部定義，只要滿足最左首碼，就可以利用索引來加速檢索。這個最左首碼可以是聯合索引的最左N個欄位，也可以是字元串索引的最左N個字元。索引建立聯合索引的時候，如何安排索引內欄位的順序就很重要了。 有一個原則是，如果通過調整順序，可以少維護一個索引，那麼這個順序就往往是應該優先採用的。

　　如果既有聯合查詢，又有基於a，b各自的查詢呢？如果你建立的聯合索引是（a,b），那麼在這種情況下，只使用b來查詢是沒辦法使用索引的。這種時候我們需要優先考慮的就是空間了。如（name，age）,name欄位一般來說會比age欄位占用更多的空間，那麼我們建立一個（name，age）的索引再加上一個（age）的索引就好了。

索引下推：

　　還是上面的（name，age）索引為例，如果你查詢的條件是 姓張，且年齡小於30歲的所有男生，那麼你的查詢語句應該這麼寫。 select * from T where name like '張%'  and age = 10 and ismale = true; 由於索引首碼的規則，只能使用‘張’來找到滿足條件的記錄，然後再判斷查詢的其他條件是否滿足。在MySQL5.6之前，每條滿足條件 like '張%'的記錄都會回表進行判斷。MySQL5.6之後引入了索引下推（index condition pushdown）,可以在索引遍歷的過程中對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表的次數。舉個例子來說明 對於記錄 A（張一，40，男）,B（張二，25，男），C（張三，26，女）。沒有索引下推時，A,B,C都需要進行回表判斷。有索引下推以後，A記錄中的age值為40，age欄位在查詢條件中，也在索引（name，age）中，觸發了索引下推，不進行回表。B,C仍需要回表。

上篇問題答案：

　　對於普通索引k，重建時可以這麼寫：

　　alter table T drop index k; alter table T add Index(k);

　　對於主鍵索引，可以這麼寫：

　　alter table T drop primary key; alter table T add primary key(id);

　　對於上面的重建索引的作法，說出你的理解。如果有不合時的，為什麼?更好的作法是什麼？

　　首先來回答一下為什麼要重建索引？ 索引可能因為記錄的刪除，或者頁分裂等原因，導致數據頁有空洞，重建索引的過程會創建一個新的索引，把數據按順序插入，這樣頁面的利用率最高，也就是索引更緊湊，更省空間。

　　重建索引K的做法是合理的。但重建主鍵過程不合理。不論是刪除主鍵還是創建主鍵，都會將整個表進行重建，所以連續執行這兩條語句，相當於第一個語句白做了。這兩條語句可以使用 alter table T engine =InnoDB來代替。

問題：

　　表結構如下所示：

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

　　由於歷史原因， a和b需要做聯合主鍵。那麼既然主鍵包括了a,b這兩個欄位，又單獨在c上創建了一個索引，索引就已經包含了三個欄位了，為什麼還要創建ca，cb索引呢？有人給出的理由是業務里有這樣兩個查詢：

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

　　這個理由對嗎？為了這兩個查詢，這兩個索引是否都必須呢？為什麼呢？