如果你從網上或者教程中，查詢索引設計相關規範，常常會看到一些不知所云的規則和注意事項，這些規則看似很有道理，但等你應用時，會發現仍然讓你一頭霧水，不知如何下手，就像我們提到的知識的邊界一樣，這些乾澀的知識點，對我們的索引設計並沒有任何幫助，只能令DB新手望而卻步。
本篇嘗試從《Relational Database index design and the optimizers》書中提到的三星索引，提煉出索引設計的準則和原理，期待能夠對學習索引設計時候提供一個錨點，期待大家一起探討學習。
從本文，你可以學習到:
什麼是三星索引
三星索引的原理是什麼
如何設計最佳索引
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(100) DEFAULT NULL,
`sex` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`c_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
# 索引
KEY `id_name_sex` (`id`,`user_name`,`sex`),
KEY `name_sex_age` (`user_name`,`sex`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;一、失敗的索引應用
我們假設 talbe test 裡面有10000行數據，其中 user_name 有100種不同的值，其FF=1%,我們查看以下兩個sql：
sql1: select user_name,sex,c_date from test where user_name ='test0' order by sex
sql2: select user_name,sex,c_date from test
以上我們可以預測到：
sql1 要比sql2 的查詢速度快，因為sql用到了索引name_sex_age ，而sql2 是全表掃描。
那麼真實情況是這樣麼？
我們加上查詢列印時間，查看下：
select user_name,sex,c_date from test where user_name ='test0' order by sex；
101 rows in set (0.02 sec)
select user_name,sex,c_date from test；
10013 rows in set (0.01 sec)
以上我們可以看到，全表掃描（sql2）的耗時要低於用索引(sql1)的耗時，這是為何？
sql1 中，雖然用到了索引，但用的是輔助索引name_sex_age, 再加上 select的時候是全行查詢，所以從索引片檢索出數據之後，還要去聚簇索引中查詢一次，這就產生了大量的隨機IO，從上一篇《DB——數據的讀取和存儲方式》中，我們知道隨機IO會佔用大量的查詢時間的。
我們不考慮資料庫緩存以及磁碟緩存
sql1 查詢耗時=10ms(第一次查索引IO)+0.01ms100（索引行，順序讀取）+10010ms(主表隨機讀) =1010 ms
sql2 查詢耗時=10ms(第一次查索引IO)+0.01ms*10000（索引行，順序讀取）=110 ms
那麼我該如何設計我們的索引，以及查詢？
二、三星索引
對於一個查詢而言，一個三星索引，可能是其最好的索引。如果查詢使用三星索引，一次查詢通常只需要進行一次磁碟隨機讀以及一次窄索引片的掃描，因此其相應時間通常比使用一個普通索引的響應時間少幾個數量級。
那麼索引的星級是如何定義？
第一顆星：與查詢相關的索引行是相鄰的，也就是where後面的等值謂詞，可以匹配索引列順序
第二顆星：索引行的順序與查詢語句需求一致，也就是order by 中的排序和索引順序是否一致
第三顆星：索引行包含查詢語句中所有的列
三顆星的意義
第一顆星，也是我們上篇文章所提的匹配列，where後面的謂詞和索引列匹配的越多，索引片越窄，最終掃描的數據行也是越小
第二顆星，是避免排序，如果結果集採用現有順序讀取，那麼就會避免一次排序，避免提前物化結果集
第三顆星，避免每一個索引行查詢，都需要去聚簇索引進行一次隨機IO查詢
上述中第三顆星也是我們常說的寬索引，它可以保證查詢只需訪問索引而無需訪問表。
比如我們上述的查詢:
select user_name,sex,c_date from test where user_name ='test0' order by sex；
最終需要的三星索引是:
(user_name,sex ,c_date)三、最佳索引——多種方案
三星索引是一種理想的索引設計方式，真實情況中往往很難達到，它是一個標尺或者是引路人的方式，要求我們設計索引時必須要注意的要素。
現實情況往往很難達到三星索引，我們分情況來進行說明。通常情況下，第三顆星（索引行包含查詢語句中所有的列）是最容易達成的，第二、三星往往不能夠一起達成。
select user_name,sex,age from test where user_name like 'test%' and sex =1 ORDER BY age索引1，（滿足第一、三顆星，無排序）
(user_name,sex,age)
三星索引對齊:
上述我們看到，此時索引(user_name,sex,age)並不能滿足三星索引中的第二顆星（排序），想要滿足必須得讓 age 列在 user_name 列前面，索引我們才去方案2
索引2（滿足第二、三顆星，無窄索引片
(sex,age，user_name)
三星索引對齊:
對於索引(sex,age，user_name)我們可以看到，此時無法滿足第一顆星，窄索引片的需求。
以上2個索引，都是無法同時滿足三星索引設計中的三個需求的，我們只能盡力滿足2個。而在多數情況下，能夠滿足2顆星，已經能縮小很大的查詢範圍了，具體最終要保留那一顆星（排序星 or 窄索引片星），這個就需要看查詢者自己的著重點了，無法給出標準答案。
三、寬索引在三星索引中，第三顆星要求（索引行包含查詢語句中所有的列），也就是常說的寬索引。
這顆星很好達到，又很難把控。或者說，為了達到這顆星，是否應該把所有的列都設計到索引列裡面？
如果每個索引中包含表的所有列，首先能帶來的優勢是，匹配到的sql查詢更多，查詢到的數據不用在進行聚簇索引查詢，節省了隨機IO。
但劣勢也很明顯，長列索引會造成頻繁的page split 和 page merge ，每個page能夠存入的索引數據更少，而且會有更多的 page split，這對插入和查詢來說，效率都會降低不少。
那我們應該怎麼做，我們要回顧索引設計的本心，設計索引是為了方便查詢的，而不是只為設計索引，脫離了查詢的索引設計，並不高效。
寬索引要求我們儘量包含多的列在索引中，並不是所有，所以除了針對where 後的常用謂詞，我們要設計索引以外，我們還得為常用select 後面的謂詞，加入到索引，以加快查詢速度。
最近相關文章:《Sql優化器究竟幫你做了哪些工作？》《DB——數據的讀取和存儲方式》關注遊戲研發和個人成長，掃描二維碼，關注更多原創文章。