索引的相信大家都聽說過忧吟，但是真正會用的又有幾人？平時工作中寫SQL真的會考慮到這條SQL如何能夠用上索引斩披，如何能夠提升執(zhí)行效率溜族？
此篇文章詳細的講述了索引優(yōu)化的幾個原則，只要在工作中能夠隨時應(yīng)用到垦沉，相信你寫出的SQL一定是效率最高煌抒，最牛逼的。
文章的腦圖如下：
索引優(yōu)化規(guī)則
1厕倍、like語句的前導(dǎo)模糊查詢不能使用索引寡壮。
select * from doc where title like '%XX'； --不能使用索引
select * from doc where title like 'XX%'讹弯； --非前導(dǎo)模糊查詢况既，可以使用索引
因為頁面搜索嚴禁左模糊或者全模糊，如果需要可以使用搜索引擎來解決闸婴。
2坏挠、union芍躏、in邪乍、or 都能夠命中索引，建議使用 in对竣。
union能夠命中索引庇楞，并且MySQL 耗費的 CPU 最少。
select * from doc where status=1
union all
select * from doc where status=2;
in能夠命中索引否纬，查詢優(yōu)化耗費的 CPU 比 union all 多吕晌，但可以忽略不計，一般情況下建議使用 in临燃。
select * from doc where status in (1, 2);
or 新版的 MySQL 能夠命中索引睛驳，查詢優(yōu)化耗費的 CPU 比 in多，不建議頻繁用or膜廊。
select * from doc where status = 1 or status = 2
補充：有些地方說在where條件中使用or乏沸，索引會失效，造成全表掃描爪瓜，這是個誤區(qū)：
要求where子句使用的所有字段蹬跃，都必須建立索引；
如果數(shù)據(jù)量太少铆铆，mysql制定執(zhí)行計劃時發(fā)現(xiàn)全表掃描比索引查找更快蝶缀，所以會不使用索引丹喻；
確保mysql版本5.0以上，且查詢優(yōu)化器開啟了index_merge_union=on, 也就是變量optimizer_switch里存在index_merge_union且為on翁都。
3碍论、負向條件查詢不能使用索引。
負向條件有：!=柄慰、<>骑冗、not in、not exists先煎、not like 等贼涩。
例如下面SQL語句：
select * from doc where status != 1 and status != 2;
可以優(yōu)化為 in 查詢：
select * from doc where status in （0,3,4）;
4、聯(lián)合索引最左前綴原則薯蝎。
如果在(a,b,c)三個字段上建立聯(lián)合索引遥倦，那么他會自動建立 a| (a,b) | (a,b,c)組索引。
登錄業(yè)務(wù)需求占锯，SQL語句如下：
select uid, login_time from user where login_name=? andpasswd=?
可以建立(login_name, passwd)的聯(lián)合索引袒哥。因為業(yè)務(wù)上幾乎沒有passwd 的單條件查詢需求，而有很多l(xiāng)ogin_name 的單條件查詢需求消略，所以可以建立(login_name, passwd)的聯(lián)合索引堡称，而不是(passwd, login_name)。
建立聯(lián)合索引的時候艺演，區(qū)分度最高的字段在最左邊却紧。
存在非等號和等號混合判斷條件時，在建立索引時胎撤，把等號條件的列前置晓殊。如 where a>? and b=?，那么即使a 的區(qū)分度更高伤提，也必須把 b 放在索引的最前列巫俺。
最左前綴查詢時，并不是指SQL語句的where順序要和聯(lián)合索引一致肿男。
下面的 SQL 語句也可以命中 (login_name, passwd) 這個聯(lián)合索引：
select uid, login_time from user where passwd=? andlogin_name=?
但還是建議 where 后的順序和聯(lián)合索引一致介汹，養(yǎng)成好習(xí)慣。
假如index(a,b,c), where a=3 and b like 'abc%' and c=4舶沛，a能用嘹承，b能用，c不能用冠王。
5赶撰、不能使用索引中范圍條件右邊的列（范圍列可以用到索引），范圍列之后列的索引全失效。
范圍條件有：<豪娜、<=餐胀、>、>=瘤载、between等否灾。
索引最多用于一個范圍列，如果查詢條件中有兩個范圍列則無法全用到索引鸣奔。
假如有聯(lián)合索引 (empno墨技、title、fromdate)挎狸，那么下面的 SQL 中 emp_no 可以用到索引扣汪，而title 和 from_date 則使用不到索引。
select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'
6锨匆、不要在索引列上面做任何操作（計算崭别、函數(shù)），否則會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描恐锣。
例如下面的 SQL 語句茅主，即使 date 上建立了索引，也會全表掃描：
select * from doc where YEAR(create_time) <= '2016';
可優(yōu)化為值計算土榴，如下：
select * from doc where create_time <= '2016-01-01';
比如下面的 SQL 語句：
select * from order where date < = CURDATE()诀姚；
可以優(yōu)化為：
select * from order where date < = '2018-01-2412:00:00';
7、強制類型轉(zhuǎn)換會全表掃描玷禽。
字符串類型不加單引號會導(dǎo)致索引失效赫段，因為mysql會自己做類型轉(zhuǎn)換,相當于在索引列上進行了操作。
如果 phone 字段是 varchar 類型论衍，則下面的 SQL 不能命中索引瑞佩。
select * from user where phone=13800001234
可以優(yōu)化為：
select * from user where phone='13800001234';
8聚磺、更新十分頻繁坯台、數(shù)據(jù)區(qū)分度不高的列不宜建立索引。
更新會變更 B+ 樹瘫寝，更新頻繁的字段建立索引會大大降低數(shù)據(jù)庫性能蜒蕾。
“性別”這種區(qū)分度不大的屬性，建立索引是沒有什么意義的焕阿，不能有效過濾數(shù)據(jù)咪啡，性能與全表掃描類似。
一般區(qū)分度在80%以上的時候就可以建立索引暮屡，區(qū)分度可以使用 count(distinct(列名))/count(*) 來計算撤摸。
9、利用覆蓋索引來進行查詢操作，避免回表准夷，減少select * 的使用钥飞。
覆蓋索引：查詢的列和所建立的索引的列個數(shù)相同，字段相同衫嵌。
被查詢的列读宙，數(shù)據(jù)能從索引中取得，而不用通過行定位符 row-locator 再到 row 上獲取楔绞，即“被查詢列要被所建的索引覆蓋”结闸，這能夠加速查詢速度。
例如登錄業(yè)務(wù)需求酒朵，SQL語句如下桦锄。
Select uid, login_time from user where login_name=? and passwd=?
可以建立(login_name, passwd, login_time)的聯(lián)合索引，由于 login_time 已經(jīng)建立在索引中了蔫耽，被查詢的 uid 和 login_time 就不用去 row 上獲取數(shù)據(jù)了察纯，從而加速查詢。
10针肥、索引不會包含有NULL值的列饼记。
只要列中包含有NULL值都將不會被包含在索引中，復(fù)合索引中只要有一列含有NULL值慰枕，那么這一列對于此復(fù)合索引就是無效的具则。所以我們在數(shù)據(jù)庫設(shè)計時，盡量使用not null 約束以及默認值具帮。
11博肋、is null, is not null無法使用索引。
12蜂厅、如果有order by匪凡、group by的場景，請注意利用索引的有序性掘猿。
order by 最后的字段是組合索引的一部分病游，并且放在索引組合順序的最后，避免出現(xiàn)file_sort 的情況稠通，影響查詢性能衬衬。
例如對于語句 where a=? and b=? order by c，可以建立聯(lián)合索引(a,b,c)改橘。
如果索引中有范圍查找滋尉，那么索引有序性無法利用，如 WHERE a>10 ORDER BY b;飞主，索引(a,b)無法排序狮惜。
13高诺、使用短索引（前綴索引）。
對列進行索引碾篡，如果可能應(yīng)該指定一個前綴長度懒叛。例如，如果有一個CHAR(255)的列耽梅，如果該列在前10個或20個字符內(nèi)薛窥，可以做到既使得前綴索引的區(qū)分度接近全列索引，那么就不要對整個列進行索引眼姐。因為短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作诅迷，減少索引文件的維護開銷≈谄欤可以使用count(distinct leftIndex(列名, 索引長度))/count(*) 來計算前綴索引的區(qū)分度罢杉。
但缺點是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆蓋索引贡歧。
不過很多時候沒必要對全字段建立索引滩租，根據(jù)實際文本區(qū)分度決定索引長度即可。
14利朵、利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁場景律想。
MySQL 并不是跳過 offset 行，而是取 offset+N 行绍弟，然后返回放棄前 offset 行技即，返回 N 行，那當 offset 特別大的時候樟遣，效率就非常的低下而叼，要么控制返回的總頁數(shù)，要么對超過特定閾值的頁數(shù)進行 SQL 改寫豹悬。
示例如下葵陵，先快速定位需要獲取的id段，然后再關(guān)聯(lián):
selecta.* from 表1 a,(select id from 表1 where 條件 limit100000,20 ) b where a.id=b.id瞻佛；
15脱篙、如果明確知道只有一條結(jié)果返回，limit 1 能夠提高效率涤久。
比如如下 SQL 語句：
select * from user where login_name=?;
可以優(yōu)化為：
select * from user where login_name=? limit 1
自己明確知道只有一條結(jié)果涡尘，但數(shù)據(jù)庫并不知道，明確告訴它响迂，讓它主動停止游標移動。
16细疚、超過三個表最好不要 join蔗彤。
需要 join 的字段川梅，數(shù)據(jù)類型必須一致，多表關(guān)聯(lián)查詢時然遏，保證被關(guān)聯(lián)的字段需要有索引贫途。
例如：left join是由左邊決定的，左邊的數(shù)據(jù)一定都有待侵，所以右邊是我們的關(guān)鍵點丢早，建立索引要建右邊的。當然如果索引在左邊秧倾，可以用right join怨酝。
17、單表索引建議控制在5個以內(nèi)那先。
18农猬、SQL 性能優(yōu)化 explain 中的 type：至少要達到 range 級別，要求是 ref 級別售淡，如果可以是 consts 最好斤葱。
consts：單表中最多只有一個匹配行（主鍵或者唯一索引），在優(yōu)化階段即可讀取到數(shù)據(jù)揖闸。
ref：使用普通的索引（Normal Index）揍堕。
range：對索引進行范圍檢索。
當 type=index 時汤纸，索引物理文件全掃鹤啡，速度非常慢。
19蹲嚣、業(yè)務(wù)上具有唯一特性的字段递瑰，即使是多個字段的組合，也必須建成唯一索引隙畜。
不要以為唯一索引影響了 insert 速度抖部，這個速度損耗可以忽略，但提高查找速度是明顯的议惰。另外慎颗，即使在應(yīng)用層做了非常完善的校驗控制，只要沒有唯一索引言询，根據(jù)墨菲定律俯萎，必然有臟數(shù)據(jù)產(chǎn)生。
20.創(chuàng)建索引時避免以下錯誤觀念运杭。
索引越多越好夫啊，認為需要一個查詢就建一個索引。
寧缺勿濫辆憔，認為索引會消耗空間撇眯、嚴重拖慢更新和新增速度报嵌。
抵制惟一索引，認為業(yè)務(wù)的惟一性一律需要在應(yīng)用層通過“先查后插”方式解決熊榛。
過早優(yōu)化锚国，在不了解系統(tǒng)的情況下就開始優(yōu)化。
索引選擇性與前綴索引
既然索引可以加快查詢速度玄坦，那么是不是只要是查詢語句需要血筑，就建上索引？答案是否定的煎楣。因為索引雖然加快了查詢速度豺总，但索引也是有代價的：索引文件本身要消耗存儲空間，同時索引會加重插入转质、刪除和修改記錄時的負擔园欣，另外，MySQL在運行時也要消耗資源維護索引休蟹，因此索引并不是越多越好沸枯。一般兩種情況下不建議建索引。
第一種情況是表記錄比較少赂弓，例如一兩千條甚至只有幾百條記錄的表绑榴，沒必要建索引，讓查詢做全表掃描就好了盈魁。至于多少條記錄才算多翔怎，這個個人有個人的看法，我個人的經(jīng)驗是以2000作為分界線杨耙，記錄數(shù)不超過 2000可以考慮不建索引赤套，超過2000條可以酌情考慮索引。
另一種不建議建索引的情況是索引的選擇性較低珊膜。所謂索引的選擇性（Selectivity）容握，是指不重復(fù)的索引值（也叫基數(shù)，Cardinality）與表記錄數(shù)（#T）的比值：
Index Selectivity = Cardinality / #T
顯然選擇性的取值范圍為(0, 1]``车柠，選擇性越高的索引價值越大剔氏，這是由B+Tree的性質(zhì)決定的。例如竹祷，employees.titles表谈跛，如果title`字段經(jīng)常被單獨查詢，是否需要建索引塑陵，我們看一下它的選擇性：
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
| 0.0000 |
+-------------+
title的選擇性不足0.0001（精確值為0.00001579）感憾，所以實在沒有什么必要為其單獨建索引。
有一種與索引選擇性有關(guān)的索引優(yōu)化策略叫做前綴索引猿妈，就是用列的前綴代替整個列作為索引key吹菱，當前綴長度合適時巍虫，可以做到既使得前綴索引的選擇性接近全列索引彭则，同時因為索引key變短而減少了索引文件的大小和維護開銷鳍刷。下面以employees.employees表為例介紹前綴索引的選擇和使用。
假設(shè)employees表只有一個索引<emp_no>俯抖，那么如果我們想按名字搜索一個人输瓜，就只能全表掃描了：
EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 300024 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
如果頻繁按名字搜索員工，這樣顯然效率很低芬萍，因此我們可以考慮建索引尤揣。有兩種選擇，建<first_name>或<first_name, last_name>柬祠，看下兩個索引的選擇性：
SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
| 0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
| 0.9313 |
+-------------+
<first_name>顯然選擇性太低北戏，``<first_name, last_name>選擇性很好，但是first_name和last_name加起來長度為30漫蛔，有沒有兼顧長度和選擇性的辦法嗜愈？可以考慮用first_name和last_name的前幾個字符建立索引，例如<first_name, left(last_name, 3)>`莽龟，看看其選擇性：
SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
| 0.7879 |
+-------------+
選擇性還不錯蠕嫁，但離0.9313還是有點距離，那么把last_name前綴加到4：
SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
| 0.9007 |
+-------------+
這時選擇性已經(jīng)很理想了毯盈，而這個索引的長度只有18剃毒，比<first_name, last_name>短了接近一半，我們把這個前綴索引建上：
ALTER TABLE employees.employees
ADD INDEX first_name_last_name4 (first_name, last_name(4));
此時再執(zhí)行一遍按名字查詢搂赋，比較分析一下與建索引前的結(jié)果：
SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------+
| 87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
| 90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+
性能的提升是顯著的赘阀，查詢速度提高了120多倍。
前綴索引兼顧索引大小和查詢速度脑奠，但是其缺點是不能用于ORDER BY和GROUP BY操作基公，也不能用于Covering index（即當索引本身包含查詢所需全部數(shù)據(jù)時，不再訪問數(shù)據(jù)文件本身）捺信。
作者丨不才陳某
來源丨碼猿技術(shù)專欄（ID：oneswholife）
