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

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

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

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

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

1-1、優(yōu)點(diǎn)

索引大大減小了服務(wù)器需要掃描的數(shù)據(jù)量（數(shù)據(jù)頁）

索引可以幫助服務(wù)器避免排序和臨時(shí)表

索引可以將隨機(jī)I/O變成順序I/O

1-2、缺點(diǎn)

雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存索引文件。

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

如果某個(gè)數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒有太大的實(shí)際效果。

對(duì)于非常小的表，大部分情況下簡(jiǎn)單的全表掃描更高效；

只需為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引。（MySQL里同一個(gè)數(shù)據(jù)表里的索引總數(shù)上限為16個(gè)）

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

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

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

??說到索引，我們一定要了解他的數(shù)據(jù)結(jié)構(gòu)以及他的存儲(chǔ)和查詢方式。拿MysQL來說，InnoDB、MyISAM、Memory每個(gè)存儲(chǔ)引擎的都有所不同。

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

??對(duì)于MySQL最常用的InnoDB引擎，數(shù)據(jù)結(jié)構(gòu)為B+Tree，選用B+樹是經(jīng)歷了漫長(zhǎng)的演化（如上）。



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

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

咱們本文的示例我們構(gòu)造一張簡(jiǎn)單的LOL英雄信息表，如下：

#來源公眾號(hào)【碼農(nóng)編程進(jì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 | 疾風(fēng)劍豪     | 亞索         |  6300 |
|  8 | 女槍         | 好運(yùn)         |  1350 |
+----+--------------+--------------+-------+
8 rows in set (0.00 sec)


2-1、盡量構(gòu)造覆蓋索引

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

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



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


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)建復(fù)用性強(qiáng)的索引

??還是這張t_lol表，如果增加一個(gè)高頻接口，通過價(jià)格（price）查詢英雄綽號(hào)（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)，因?yàn)樵贛ySQL中索引履行最左前綴原則。這個(gè)最左前綴可以是聯(lián)合索引的最左X個(gè)字段，也可以是字符串索引的最左Y個(gè)字符。

最左前綴原則

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

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

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

select * from table where c = '1';

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

以下三種情況卻會(huì)走索引:

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';


從上面兩個(gè)例子大家是否闊以看出點(diǎn)眉目？

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

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


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 | 疾風(fēng)劍豪     | 亞索         |  6300 |
|  8 | 女槍         | 好運(yùn)         |  1350 |
+----+--------------+--------------+-------+
8 rows in set (0.00 sec)

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

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

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

1、索引不會(huì)包含有null值的列

??只要列中包含有null值都將不會(huì)被包含在索引中，復(fù)合索引中只要有一列含有null值，那么這一列對(duì)于此復(fù)合索引就是無效的。所以我們?cè)跀?shù)據(jù)庫設(shè)計(jì)時(shí)建議不要讓字段的默認(rèn)值為null。

2、使用短索引

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

3、索引列排序

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

4、like語句操作

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

5、不要在列上進(jìn)行運(yùn)算

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

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

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

總結(jié)

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

作者：碼農(nóng)編程進(jìn)階筆記


歡迎關(guān)注微信公眾號(hào) ：碼農(nóng)編程進(jìn)階筆記