轉(zhuǎn)自：https://juejin.im/post/6867180058549682184
索引優(yōu)化規(guī)則
1助赞、like語句的前導(dǎo)模糊查詢不能使用索引
select * from doc where title like '%XX'叨叙； --不能使用索引
select * from doc where title like 'XX%'聋呢； --非前導(dǎo)模糊查詢鬓椭，可以使用索引
復(fù)制代碼

因?yàn)轫撁嫠阉鲊?yán)禁左模糊或者全模糊盛嘿，如果需要可以使用搜索引擎來解決昨寞。

2瞻惋、union、in援岩、or 都能夠命中索引歼狼，建議使用 in

union能夠命中索引，并且MySQL 耗費(fèi)的 CPU 最少享怀。

select * from doc where status=1
union all
select * from doc where status=2;
復(fù)制代碼

in能夠命中索引羽峰，查詢優(yōu)化耗費(fèi)的 CPU 比 union all 多，但可以忽略不計(jì)添瓷，一般情況下建議使用 in梅屉。

select * from doc where status in (1, 2);
復(fù)制代碼

or 新版的 MySQL 能夠命中索引，查詢優(yōu)化耗費(fèi)的 CPU 比 in多鳞贷，不建議頻繁用or履植。

select * from doc where status = 1 or status = 2
復(fù)制代碼

補(bǔ)充：有些地方說在where條件中使用or，索引會(huì)失效悄晃，造成全表掃描玫霎，這是個(gè)誤區(qū)：

①要求where子句使用的所有字段凿滤，都必須建立索引；

②如果數(shù)據(jù)量太少庶近，mysql制定執(zhí)行計(jì)劃時(shí)發(fā)現(xiàn)全表掃描比索引查找更快翁脆，所以會(huì)不使用索引；

③確保mysql版本5.0以上鼻种，且查詢優(yōu)化器開啟了index_merge_union=on, 也就是變量optimizer_switch里存在index_merge_union且為on反番。

3、負(fù)向條件查詢不能使用索引

負(fù)向條件有：!=叉钥、<>罢缸、not in、not exists投队、not like 等枫疆。

例如下面SQL語句：

select * from doc where status != 1 and status != 2;
復(fù)制代碼

可以優(yōu)化為 in 查詢：

select * from doc where status in (0,3,4);
復(fù)制代碼
4、聯(lián)合索引最左前綴原則

如果在(a,b,c)三個(gè)字段上建立聯(lián)合索引敷鸦，那么他會(huì)自動(dòng)建立 a| (a,b) | (a,b,c)組索引息楔。

登錄業(yè)務(wù)需求，SQL語句如下：

select uid, login_time from user where login_name=? andpasswd=?
復(fù)制代碼

可以建立(login_name, passwd)的聯(lián)合索引扒披。因?yàn)闃I(yè)務(wù)上幾乎沒有passwd 的單條件查詢需求值依，而有很多l(xiāng)ogin_name 的單條件查詢需求，所以可以建立(login_name, passwd)的聯(lián)合索引碟案，而不是(passwd, login_name)愿险。

建立聯(lián)合索引的時(shí)候，區(qū)分度最高的字段在最左邊

存在非等號(hào)和等號(hào)混合判斷條件時(shí)价说，在建立索引時(shí)拯啦，把等號(hào)條件的列前置。如 where a>? and b=?熔任，那么即使a 的區(qū)分度更高褒链，也必須把 b 放在索引的最前列。

最左前綴查詢時(shí)疑苔，并不是指SQL語句的where順序要和聯(lián)合索引一致甫匹。

下面的 SQL 語句也可以命中 (login_name, passwd) 這個(gè)聯(lián)合索引：

select uid, login_time from user where passwd=? andlogin_name=?
復(fù)制代碼

但還是建議 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等骑科。
索引最多用于一個(gè)范圍列，如果查詢條件中有兩個(gè)范圍列則無法全用到索引构拳。
假如有聯(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'
復(fù)制代碼
6凫海、不要在索引列上面做任何操作（計(jì)算呛凶、函數(shù)），否則會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描

例如下面的 SQL 語句盐碱，即使 date 上建立了索引，也會(huì)全表掃描：

select * from doc where YEAR(create_time) <= '2016';
復(fù)制代碼

可優(yōu)化為值計(jì)算沪伙，如下：

select * from doc where create_time <= '2016-01-01';
復(fù)制代碼

比如下面的 SQL 語句：

select * from order where date < = CURDATE()瓮顽；
復(fù)制代碼

可以優(yōu)化為：

select * from order where date < = '2018-01-2412:00:00';
復(fù)制代碼
7、強(qiáng)制類型轉(zhuǎn)換會(huì)全表掃描

字符串類型不加單引號(hào)會(huì)導(dǎo)致索引失效围橡，因?yàn)閙ysql會(huì)自己做類型轉(zhuǎn)換,相當(dāng)于在索引列上進(jìn)行了操作暖混。
如果 phone 字段是 varchar 類型，則下面的 SQL 不能命中索引翁授。

select * from user where phone=13800001234
復(fù)制代碼

可以優(yōu)化為：

select * from user where phone='13800001234';
復(fù)制代碼
8拣播、更新十分頻繁、數(shù)據(jù)區(qū)分度不高的列不宜建立索引

更新會(huì)變更 B+ 樹收擦，更新頻繁的字段建立索引會(huì)大大降低數(shù)據(jù)庫性能贮配。

“性別”這種區(qū)分度不大的屬性，建立索引是沒有什么意義的塞赂，不能有效過濾數(shù)據(jù)泪勒，性能與全表掃描類似。

一般區(qū)分度在80%以上的時(shí)候就可以建立索引宴猾，區(qū)分度可以使用 count(distinct(列名))/count(*) 來計(jì)算圆存。

9、利用覆蓋索引來進(jìn)行查詢操作仇哆，避免回表沦辙，減少select * 的使用

覆蓋索引：查詢的列和所建立的索引的列個(gè)數(shù)相同，字段相同讹剔。
被查詢的列油讯，數(shù)據(jù)能從索引中取得详民，而不用通過行定位符 row-locator 再到 row 上獲取，即“被查詢列要被所建的索引覆蓋”撞羽，這能夠加速查詢速度阐斜。
例如登錄業(yè)務(wù)需求，SQL語句如下诀紊。

Select uid, login_time from user where login_name=? and passwd=?
復(fù)制代碼

可以建立(login_name, passwd, login_time)的聯(lián)合索引谒出，由于 login_time 已經(jīng)建立在索引中了，被查詢的 uid 和 login_time 就不用去 row 上獲取數(shù)據(jù)了邻奠，從而加速查詢笤喳。

10、索引不會(huì)包含有NULL值的列

只要列中包含有NULL值都將不會(huì)被包含在索引中碌宴，復(fù)合索引中只要有一列含有NULL值杀狡，那么這一列對(duì)于此復(fù)合索引就是無效的。所以我們?cè)跀?shù)據(jù)庫設(shè)計(jì)時(shí)贰镣，盡量使用not null 約束以及默認(rèn)值呜象。

11、is null, is not null無法使用索引
12碑隆、如果有order by恭陡、group by的場(chǎng)景，請(qǐng)注意利用索引的有序性

order by 最后的字段是組合索引的一部分上煤，并且放在索引組合順序的最后休玩，避免出現(xiàn)file_sort 的情況，影響查詢性能劫狠。

例如對(duì)于語句 where a=? and b=? order by c拴疤，可以建立聯(lián)合索引(a,b,c)。

如果索引中有范圍查找独泞，那么索引有序性無法利用呐矾，如 WHERE a>10 ORDER BY b;，索引(a,b)無法排序懦砂。

13凫佛、使用短索引（前綴索引）

對(duì)列進(jìn)行索引，如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度孕惜。例如愧薛，如果有一個(gè)CHAR(255)的列，如果該列在前10個(gè)或20個(gè)字符內(nèi)衫画，可以做到既使得前綴索引的區(qū)分度接近全列索引毫炉，那么就不要對(duì)整個(gè)列進(jìn)行索引。因?yàn)槎趟饕粌H可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作削罩，減少索引文件的維護(hù)開銷瞄勾》鸭椋可以使用count(distinct leftIndex(列名, 索引長(zhǎng)度))/count(*) 來計(jì)算前綴索引的區(qū)分度。

但缺點(diǎn)是不能用于 ORDER BY 和 GROUP BY 操作进陡，也不能用于覆蓋索引愿阐。

不過很多時(shí)候沒必要對(duì)全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長(zhǎng)度即可趾疚。

14缨历、利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁場(chǎng)景

MySQL 并不是跳過 offset 行，而是取 offset+N 行糙麦，然后返回放棄前 offset 行辛孵，返回 N 行，那當(dāng) offset 特別大的時(shí)候赡磅，效率就非常的低下魄缚，要么控制返回的總頁數(shù)，要么對(duì)超過特定閾值的頁數(shù)進(jìn)行 SQL 改寫焚廊。
示例如下冶匹，先快速定位需要獲取的id段，然后再關(guān)聯(lián):

selecta.* from 表1 a,(select id from 表1 where 條件 limit100000,20 ) b where a.id=b.id咆瘟；
復(fù)制代碼
15嚼隘、如果明確知道只有一條結(jié)果返回，limit 1 能夠提高效率

比如如下 SQL 語句：

select * from user where login_name=?;
復(fù)制代碼

可以優(yōu)化為：

select * from user where login_name=? limit 1
復(fù)制代碼

自己明確知道只有一條結(jié)果搞疗，但數(shù)據(jù)庫并不知道嗓蘑，明確告訴它须肆，讓它主動(dòng)停止游標(biāo)移動(dòng)匿乃。

16、超過三個(gè)表最好不要 join

需要 join 的字段豌汇，數(shù)據(jù)類型必須一致幢炸，多表關(guān)聯(lián)查詢時(shí)，保證被關(guān)聯(lián)的字段需要有索引拒贱。

例如：left join是由左邊決定的宛徊，左邊的數(shù)據(jù)一定都有，所以右邊是我們的關(guān)鍵點(diǎn)逻澳，建立索引要建右邊的闸天。當(dāng)然如果索引在左邊，可以用right join斜做。

17苞氮、單表索引建議控制在5個(gè)以內(nèi)
18、SQL 性能優(yōu)化 explain 中的 type：至少要達(dá)到 range 級(jí)別瓤逼，要求是 ref 級(jí)別笼吟，如果可以是 consts 最好

consts：?jiǎn)伪碇凶疃嘀挥幸粋€(gè)匹配行（主鍵或者唯一索引）库物，在優(yōu)化階段即可讀取到數(shù)據(jù)。

ref：使用普通的索引（Normal Index）贷帮。

range：對(duì)索引進(jìn)行范圍檢索戚揭。

當(dāng) type=index 時(shí)，索引物理文件全掃撵枢，速度非常慢民晒。

19、業(yè)務(wù)上具有唯一特性的字段诲侮，即使是多個(gè)字段的組合镀虐，也必須建成唯一索引

不要以為唯一索引影響了 insert 速度，這個(gè)速度損耗可以忽略沟绪，但提高查找速度是明顯的刮便。另外，即使在應(yīng)用層做了非常完善的校驗(yàn)控制绽慈，只要沒有唯一索引恨旱，根據(jù)墨菲定律，必然有臟數(shù)據(jù)產(chǎn)生坝疼。

20.創(chuàng)建索引時(shí)避免以下錯(cuò)誤觀念

索引越多越好搜贤，認(rèn)為需要一個(gè)查詢就建一個(gè)索引。
寧缺勿濫钝凶，認(rèn)為索引會(huì)消耗空間仪芒、嚴(yán)重拖慢更新和新增速度。
抵制惟一索引耕陷，認(rèn)為業(yè)務(wù)的惟一性一律需要在應(yīng)用層通過“先查后插”方式解決掂名。
過早優(yōu)化，在不了解系統(tǒng)的情況下就開始優(yōu)化哟沫。

索引選擇性與前綴索引

既然索引可以加快查詢速度饺蔑，那么是不是只要是查詢語句需要，就建上索引嗜诀？答案是否定的猾警。因?yàn)樗饕m然加快了查詢速度，但索引也是有代價(jià)的：索引文件本身要消耗存儲(chǔ)空間隆敢，同時(shí)索引會(huì)加重插入发皿、刪除和修改記錄時(shí)的負(fù)擔(dān)，另外拂蝎，MySQL在運(yùn)行時(shí)也要消耗資源維護(hù)索引穴墅，因此索引并不是越多越好。一般兩種情況下不建議建索引。

第一種情況是表記錄比較少封救，例如一兩千條甚至只有幾百條記錄的表拇涤，沒必要建索引，讓查詢做全表掃描就好了誉结。至于多少條記錄才算多鹅士，這個(gè)個(gè)人有個(gè)人的看法，我個(gè)人的經(jīng)驗(yàn)是以2000作為分界線惩坑，記錄數(shù)不超過 2000可以考慮不建索引掉盅，超過2000條可以酌情考慮索引。

另一種不建議建索引的情況是索引的選擇性較低以舒。所謂索引的選擇性（Selectivity）趾痘，是指不重復(fù)的索引值（也叫基數(shù)，Cardinality）與表記錄數(shù)（#T）的比值：

Index Selectivity = Cardinality / #T
復(fù)制代碼

顯然選擇性的取值范圍為(0, 1]``蔓钟，選擇性越高的索引價(jià)值越大永票，這是由B+Tree的性質(zhì)決定的。例如滥沫，employees.titles表侣集，如果title`字段經(jīng)常被單獨(dú)查詢，是否需要建索引兰绣，我們看一下它的選擇性：

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
| 0.0000 |
+-------------+
復(fù)制代碼

title的選擇性不足0.0001（精確值為0.00001579）世分，所以實(shí)在沒有什么必要為其單獨(dú)建索引。

有一種與索引選擇性有關(guān)的索引優(yōu)化策略叫做前綴索引缀辩，就是用列的前綴代替整個(gè)列作為索引key臭埋，當(dāng)前綴長(zhǎng)度合適時(shí)，可以做到既使得前綴索引的選擇性接近全列索引臀玄，同時(shí)因?yàn)樗饕齥ey變短而減少了索引文件的大小和維護(hù)開銷瓢阴。下面以employees.employees表為例介紹前綴索引的選擇和使用。

假設(shè)employees表只有一個(gè)索引<emp_no>镐牺，那么如果我們想按名字搜索一個(gè)人炫掐，就只能全表掃描了：

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 |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
復(fù)制代碼

如果頻繁按名字搜索員工魁莉，這樣顯然效率很低睬涧，因此我們可以考慮建索引。有兩種選擇旗唁，建<first_name>或<first_name, last_name>畦浓，看下兩個(gè)索引的選擇性：

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 |
+-------------+
復(fù)制代碼

<first_name>顯然選擇性太低，`<first_name, last_name>選擇性很好检疫，但是first_name和last_name加起來長(zhǎng)度為30讶请，有沒有兼顧長(zhǎng)度和選擇性的辦法？可以考慮用first_name和last_name的前幾個(gè)字符建立索引，例如<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 |
+-------------+

復(fù)制代碼

選擇性還不錯(cuò)论巍，但離0.9313還是有點(diǎn)距離，那么把last_name前綴加到4：

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
| 0.9007 |
+-------------+
復(fù)制代碼

這時(shí)選擇性已經(jīng)很理想了风响，而這個(gè)索引的長(zhǎng)度只有18嘉汰，比<first_name, last_name>短了接近一半，我們把這個(gè)前綴索引建上：

ALTER TABLE employees.employees
ADD INDEX first_name_last_name4 (first_name, last_name(4));
復(fù)制代碼

此時(shí)再執(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' |
+----------+------------+---------------------------------------------------------------------------------+
復(fù)制代碼

性能的提升是顯著的鞋怀，查詢速度提高了120多倍。

前綴索引兼顧索引大小和查詢速度持搜，但是其缺點(diǎn)是不能用于ORDER BY和GROUP BY操作密似，也不能用于Covering index（即當(dāng)索引本身包含查詢所需全部數(shù)據(jù)時(shí)，不再訪問數(shù)據(jù)文件本身）葫盼。

作者：不才陳某
鏈接：https://juejin.im/post/6867180058549682184
來源：掘金
著作權(quán)歸作者所有残腌。商業(yè)轉(zhuǎn)載請(qǐng)聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請(qǐng)注明出處贫导。