牛B程序員在“創(chuàng)建索引”時都會注意啥？

不得不說，如何創(chuàng)建索引已經是我們開發(fā)人員必須掌握的技能之一了。在設計系統(tǒng)數據表時，你可能會根據具體業(yè)務需求，給對應的某個表字段添加普通索引或唯一索引；也可能根據最左前綴原則、索引下推特性和覆蓋索引，將多個列揉成一個聯(lián)合索引來使用。

??當同事問我一些創(chuàng)建索引的經驗時，作為一個久經沙場的老程序員，我建議盡量讓每條SQL中的where、group by、order by條件都能最大化使用索引。當然，在寫多讀少和讀多寫少的不同場景下使用方式也不盡相同。我們在保證SQL執(zhí)行效率的同時，還要關注到數據庫對索引文件的維護成本，從容應對那些常見又很惹人煩的場景諸如：模糊查詢、大文本檢索、超大分頁等。

一、明確索引的優(yōu)缺點

??知己知彼，百戰(zhàn)不殆。想正確的使用索引，首先我們要知道索引的特性以及他的優(yōu)缺點。

1-1、優(yōu)點

索引大大減小了服務器需要掃描的數據量（數據頁）

索引可以幫助服務器避免排序和臨時表

索引可以將隨機I/O變成順序I/O

1-2、缺點

雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存索引文件。

建立索引會占用磁盤空間的索引文件。一般情況這個問題不算嚴重，但如果你在一個大表上創(chuàng)建了多種組合索引，且伴隨大量數據量插入，索引文件大小也會快速膨脹。

如果某個數據列包含許多重復的內容，為它建立索引就沒有太大的實際效果。

對于非常小的表，大部分情況下簡單的全表掃描更高效；

只需為最經常查詢和最經常排序的數據列建立索引。（MySQL里同一個數據表里的索引總數上限為16個）

??索引的目的是提高查詢效率，和我們在圖書館借書一樣：需先定位到分類區(qū) → 書架 → 書 → 章節(jié) →頁數。圖書館可以看做數據庫，如果將所有數據亂放，相信一天你也找不到你想要的那篇《葵花寶典》。換位思考，其實服務器也很累，對它好點~

??其實索引的本質都是通過不斷地縮小想要獲取數據的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是說，有了這種索引機制，我們可以高效鎖定某數據的同時，還可以快速定位范圍以及排序工作。

??一般應用系統(tǒng)中的讀寫比例會在10:1 ~ 15:1甚至更高，而插入操作和更新刪除操作（我們成為DML操作）很少在性能上出問題，多只是在事務處理方面。在生產環(huán)境中，我們遇到更多的性能問題還是出現(xiàn)在一些復雜的查詢SQL中。因此，對查詢語句的索引優(yōu)化顯然是重中之重。

??說到索引，我們一定要了解他的數據結構以及他的存儲和查詢方式。拿MysQL來說，InnoDB、MyISAM、Memory每個存儲引擎的都有所不同。

二叉排序樹 → 二叉平衡樹 → B-Tree（B樹） → B+Tree（B+樹）

??對于MySQL最常用的InnoDB引擎，數據結構為B+Tree，選用B+樹是經歷了漫長的演化（如上）。



??需要說明的是，B+Tree的特性是N叉樹+有序存儲。B+樹的葉子節(jié)點間按順序建立了鏈指針，加強了區(qū)間訪問性，所以B+樹對索引對范圍查詢和排序有天然的優(yōu)勢。

二、開發(fā)中創(chuàng)建索引時要注意哪些（經驗之談）

咱們本文的示例我們構造一張簡單的LOL英雄信息表，如下：

#來源公眾號【碼農編程進階筆記】
mysql> select * from t_lol;
+----+--------------+--------------+-------+
| id | hero_title   | hero_name    | price |
+----+--------------+--------------+-------+
|  1 | 刀鋒之影     | 泰隆         |  6300 |
|  2 | 迅捷斥候     | 提莫         |  6300 |
|  3 | 光輝女郎     | 拉克絲       |  1350 |
|  4 | 發(fā)條魔靈     | 奧莉安娜     |  6300 |
|  5 | 至高之拳     | 李青         |  6300 |
|  6 | 無極劍圣     | 易           |   450 |
|  7 | 疾風劍豪     | 亞索         |  6300 |
|  8 | 女槍         | 好運         |  1350 |
+----+--------------+--------------+-------+
8 rows in set (0.00 sec)


2-1、盡量構造覆蓋索引

比如你創(chuàng)建了hero_name，price 的索引 idx_name_price(hero_name,price)，查詢數據時使用這種姿勢：

SELECT * from t_lol where hero_name = '亞索' and price = 6300;
??由于該索引中只存有hero_name、price和主鍵列，命中索引后，select *的其他字段怎么辦呢？數據庫還必須回到聚集索引中通過主鍵查找其他列數據，這就是回表，這也是你背的那條：少用select * 的原因，他會使SQL錯失對覆蓋索引的使用。



??我們通過EXPLAIN檢查一下SQL執(zhí)行情況，發(fā)現(xiàn)雖然使用上了索引，但確實未達到覆蓋索引，發(fā)生了回表。當數據量很大時，回表耗時可能會達到覆蓋索引的十倍以上。


mysql> EXPLAIN SELECT * from t_lol where hero_name = '亞索' and price = 6300;
+----+-------------+-------+------------+------+--------------------------+----------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys            | key            | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------+----------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_lol | NULL       | ref  | idx_price,idx_name_price | idx_name_price | 136     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+--------------------------+----------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

而如果只查select hero_name,price兩列，或再加上主鍵id這列，都可以使用上覆蓋索引不用再回表。即key=idx_name_price;Extra=Using index;


mysql> EXPLAIN SELECT hero_name,price from t_lol where hero_name = '亞索' and price = 6300;
+----+-------------+-------+------------+------+--------------------------+----------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys            | key            | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+--------------------------+----------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t_lol | NULL       | ref  | idx_price,idx_name_price | idx_name_price | 136     | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+--------------------------+----------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


2-2、創(chuàng)建復用性強的索引

??還是這張t_lol表，如果增加一個高頻接口，通過價格（price）查詢英雄綽號（hero_title），那我們創(chuàng)建的idx_name_price(hero_name,price)索引還能用么？


mysql> explain select * from t_lol where price =6300;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_lol | NULL       | ALL  | idx_price     | NULL | NULL    | NULL |    8 |    62.50 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec







??key=NULL;Extra=Using where;顯然是沒有用上索引idx_name_price(hero_name,price)，因為在MySQL中索引履行最左前綴原則。這個最左前綴可以是聯(lián)合索引的最左X個字段，也可以是字符串索引的最左Y個字符。

最左前綴原則

??B+樹的節(jié)點存儲索引順序是從左向右存儲（說明一下，這個左到右只是說邏輯上的單向有序，并不是左邊和右邊。。別犟），在匹配的時候自然也要滿足從左向右匹配；

??通常我們在建立聯(lián)合索引的時候，也就是對多個字段建立索引，相信建立過索引的同學們會發(fā)現(xiàn)，無論是Oracle還是 MySQL 都會讓我們選擇索引的順序，比如我們想在a,b,c三個字段上建立一個聯(lián)合索引，我們可以選擇自己想要的優(yōu)先級，a、b、c,或者是b、a、c 或者是c、a、b等順序。為什么數據庫會讓我們選擇字段的順序呢？不都是三個字段的聯(lián)合索引么？這里就引出了數據庫索引的最左前綴原理。

??在我們開發(fā)中經常會遇到明明這個字段建了聯(lián)合索引，但是SQL查詢該字段時卻不會使用索引的問題。比如索引abc_index:(a,b,c)是a,b,c三個字段的聯(lián)合索引，下列sql執(zhí)行時都無法命中索引abc_index的；

select * from table where c = '1';

select * from table where b ='1' and c ='2'

以下三種情況卻會走索引:

select * from table where a = '1';

select * from table where a = '1' and b = '2';

select * from table where a = '1' and b = '2'  and c='3';


從上面兩個例子大家是否闊以看出點眉目？

??是的，索引abc_index:(a,b,c)，只會在(a)、(a,b)、(a,b,c) 三種類型的查詢中使用。其實這里說的有一點歧義，其實(a,c)也會走，但是只走a字段索引，不會走c字段。
??另外還有一個特殊情況說明下，下面這種類型的也只會有 a與b 走索引，c不會走。
select * from table where a = '1' and b > '2'  and c='3'

??像上面這種類型的sql語句，在a、b走完索引后，c已經是無序了，所以c就沒法走索引，優(yōu)化器會認為還不如全表掃描c字段來的快。
??最左前綴：顧名思義，就是最左優(yōu)先，上例中我們創(chuàng)建了a_b_c多列索引,相當于創(chuàng)建了(a)單列索引，(a,b)組合索引以及(a,b,c)組合索引。
??因此，在創(chuàng)建多列索引時，要根據業(yè)務需求，where子句中使用最頻繁的一列放在最左邊。
??我們明白最左前綴原則后發(fā)現(xiàn)，根本無法做到讓每個請求都最大化利用到索引，總不能一個接口就加一個索引吧？


mysql> select * from t_lol;
+----+--------------+--------------+-------+
| id | hero_title   | hero_name    | price |
+----+--------------+--------------+-------+
|  1 | 刀鋒之影     | 泰隆         |  6300 |
|  2 | 迅捷斥候     | 提莫         |  6300 |
|  3 | 光輝女郎     | 拉克絲       |  1350 |
|  4 | 發(fā)條魔靈     | 奧莉安娜     |  6300 |
|  5 | 至高之拳     | 李青         |  6300 |
|  6 | 無極劍圣     | 易           |   450 
|  7 | 疾風劍豪     | 亞索         |  6300 |
|  8 | 女槍         | 好運         |  1350 |
+----+--------------+--------------+-------+
8 rows in set (0.00 sec)

回到我們上面提到的問題，如果有一個高頻接口：通過價格（price）查詢英雄綽號（hero_title），那我豈不是還要新建一個單獨的index(price)索引？

??其實這里引出了一個問題，在建立聯(lián)合索引的時候，如何安排索在引內的字段順序？ 也就是索引的復用能力。

??因為可以支持最左前綴，所以當已經有了idx_name_price(hero_name,price)這個聯(lián)合索引后，一般就不需要單獨在hero_name上建立索引了。但單獨查price時是無法使用該聯(lián)合索引的，那么如果要使用該索引還能滿足通過price列查詢的需求。怎么辦？正如你所想的，修改索引列順序。
??因此，第一原則是，如果通過調整順序，可第以少維護一個索引，那么這個順序往往就是需要優(yōu)先考慮采用的。
??所以你應該知道，這段開頭的問題里，我們既要為高頻請求創(chuàng)建(price,hero_name)這個聯(lián)合索引，并用這個索引支持根據price查詢hero_title的需求。那么我們只需將聯(lián)合索引順序修改為 idx_name_price(price,hero_name)即可。
2-3、索引不是越多越好
??很顯然，我們在文章前面提到的索引缺點處就做出了說明，索引是把雙刃劍，提高查詢效率的同時還需要使用數據庫中大量資源去維護他。越來越大的索引文件、越來越慢的DML操作都是需要考慮的后果。
??因此我們在創(chuàng)建索引時需要根據實際場景的需求，是讀多寫少還是讀少寫多？數據量創(chuàng)建索引的必要性？索引的硬傷？等。
??有同學問我數據量少時（幾十條？）創(chuàng)建索引和不創(chuàng)建索引查詢效率和維護成本上會有多少區(qū)別？
??搞得我一時不知道怎么回答。。作為一名老程序員，建議大家把眼光放長遠些，別在這種問題上花太多時間研究。只能說是，如果有業(yè)務會使用到，建議都按照我們開發(fā)時創(chuàng)建索引的規(guī)范來創(chuàng)建，后續(xù)總會用得上。數據少索引維護成本也可以忽略不計，別留坑就行。
2-4、使用索引的一些暖心建議

1、索引不會包含有null值的列

??只要列中包含有null值都將不會被包含在索引中，復合索引中只要有一列含有null值，那么這一列對于此復合索引就是無效的。所以我們在數據庫設計時建議不要讓字段的默認值為null。

2、使用短索引

??對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個char(255)的列，如果在前10個或20個字符內，多數值是惟一的，那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。

3、索引列排序

??查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那么order by中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創(chuàng)建復合索引。

4、like語句操作

??一般情況下不推薦使用like操作，如果非使用不可，如何使用也是一個問題。like %陳% 不會使用索引而like 陳%可以使用索引。

5、不要在列上進行運算

??這將導致索引失效而進行全表掃描，例如
SELECT * FROM table_name WHERE YEAR(column_name)<2017;

6、不使用not in和<>這類非集操作

??這不屬于支持的范圍查詢條件，不會使用索引。

總結

??在我們實際操作索引前，建議根據實際需求，結合搜索引擎索引特性，先設計好每張表的索引類型和結構，盡量避免邊寫邊改。數據量劇增后再想修改索引是很麻煩的，需要很長的修改時間，且修改時會鎖表。對了，千萬不要隨意修改線上庫的索引，別問我為什么。。

作者：碼農編程進階筆記


歡迎關注微信公眾號 ：碼農編程進階筆記