上一篇介紹了痴晦，MySQL的索引南吮，這次介紹如何對(duì)索引進(jìn)行優(yōu)化。

1. 工具使用

首先介紹一個(gè)工具誊酌，可以查看SQL語句的執(zhí)行情況部凑，是不是用到了索引，用到了哪個(gè)索引碧浊。

1.1 Explain工具

使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL語句涂邀，分析你的查詢語句或是結(jié)構(gòu)的性能瓶頸 在 select 語句之前增加 explain 關(guān)鍵字，MySQL 會(huì)在查詢上設(shè)置一個(gè)標(biāo)記箱锐，執(zhí)行查詢會(huì)返回執(zhí)行計(jì)劃的信息比勉，而不是執(zhí)行這條SQL。

注意:如果 from 中包含子查詢，仍會(huì)執(zhí)行該子查詢敷搪，將結(jié)果放入臨時(shí)表中兴想。

1.1.1 Explain中的列

首先看一張官網(wǎng)的總體圖。

Explain輸出行

接下來我們將展示explain中每個(gè)列的信息赡勘。

1.1.1.1 id列

id列的編號(hào)是 select 的序列號(hào)嫂便，有幾個(gè) select 就有幾個(gè)id，并且id的順序是按 select 出現(xiàn)的順序增長(zhǎng)的闸与。 id列越大執(zhí)行優(yōu)先級(jí)越高毙替，id相同則從上往下執(zhí)行，id為NULL最后執(zhí)行践樱。

1.1.1.2 select_type列

select_type 表示對(duì)應(yīng)行是簡(jiǎn)單還是復(fù)雜的查詢厂画。

• simple：簡(jiǎn)單查詢。查詢不包含子查詢和union
• primary：復(fù)雜查詢中最外層的 select
• subquery：包含在 select 中的子查詢（不在 from 子句中）
• derived：包含在 from 子句中的子查詢拷邢。MySQL會(huì)將結(jié)果存放在一個(gè)臨時(shí)表中袱院，也稱為派生表（derived的英文含 義）
• union：在 union 中的第二個(gè)和隨后的 select

1.1.1.3 table列

這一列表示 explain 的一行正在訪問哪個(gè)表。 當(dāng) from 子句中有子查詢時(shí)瞭稼，table列是 <derivenN> 格式忽洛，表示當(dāng)前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查 詢环肘。當(dāng)有 union 時(shí)欲虚，UNION RESULT 的 table 列的值為<union1,2>，1和2表示參與 union 的 select 行id悔雹。

1.1.1.4 type列

這一列表示關(guān)聯(lián)類型或訪問類型复哆，即MySQL決定如何查找表中的行，查找數(shù)據(jù)行記錄的大概范圍腌零。

依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index > ALL

一般來說梯找，得保證查詢達(dá)到range級(jí)別，最好達(dá)到ref

• NULL：mysql能夠在優(yōu)化階段分解查詢語句莱没，在執(zhí)行階段用不著再訪問表或索引初肉。例如：在索引列中選取最小值，可 以單獨(dú)查找索引來完成饰躲，不需要在執(zhí)行時(shí)訪問表
• const, system：mysql能對(duì)查詢的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)化成一個(gè)常量（可以看show warnings 的結(jié)果）牙咏。用于 primary key 或 unique key 的所有列與常數(shù)比較時(shí)，所以表最多有一個(gè)匹配行嘹裂，讀取1次妄壶，速度比較快。system是 const的特例寄狼，表里只有一條元組匹配時(shí)為system
• eq_ref：primary key 或 unique key 索引的所有部分被連接使用 丁寄，最多只會(huì)返回一條符合條件的記錄氨淌。這可能是在 const 之外最好的聯(lián)接類型了，簡(jiǎn)單的 select 查詢不會(huì)出現(xiàn)這種 type伊磺。
• ref：相比 eq_ref盛正，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴屑埋，索引要和某個(gè)值相比較豪筝，可能會(huì) 找到多個(gè)符合條件的行。
• range：范圍掃描通常出現(xiàn)在 in(), between ,> ,<, >= 等操作中摘能。使用一個(gè)索引來檢索給定范圍的行续崖。
• index：掃描全索引就能拿到結(jié)果，一般是掃描某個(gè)二級(jí)索引团搞，這種掃描不會(huì)從索引樹根節(jié)點(diǎn)開始快速查找严望，而是直接 對(duì)二級(jí)索引的葉子節(jié)點(diǎn)遍歷和掃描，速度還是比較慢的逻恐，這種查詢一般為使用覆蓋索引像吻，二級(jí)索引一般比較小，所以這 種通常比ALL快一些复隆。
• ALL：即全表掃描萧豆，掃描你的聚簇索引的所有葉子節(jié)點(diǎn)。通常情況下這需要增加索引來進(jìn)行優(yōu)化了昏名。

1.1.1.5 possible_keys列

這一列顯示查詢可能使用哪些索引來查找。

explain 時(shí)可能出現(xiàn) possible_keys有列阵面，而key顯示NULL的情況轻局，這種情況是因?yàn)楸碇袛?shù)據(jù)不多，mysql認(rèn)為索引 對(duì)此查詢幫助不大样刷，選擇了全表查詢仑扑。

如果該列是NULL，則沒有相關(guān)的索引置鼻。在這種情況下镇饮，可以通過檢查 where 子句看是否可以創(chuàng)造一個(gè)適當(dāng)?shù)乃饕齺硖?高查詢性能，然后用 explain 查看效果箕母。

1.1.1.6 key列

這一列顯示mysql實(shí)際采用哪個(gè)索引來優(yōu)化對(duì)該表的訪問储藐。 如果沒有使用索引，則該列是 NULL嘶是。如果想強(qiáng)制mysql使用或忽視possible_keys列中的索引巩割，在查詢中使用 force index须眷、ignore index。

1.1.1.7 key_len列

這一列顯示了mysql在索引里使用的字節(jié)數(shù)，通過這個(gè)值可以算出具體使用了索引中的哪些列勋锤。

key_len計(jì)算規(guī)則如下：

• 字符串嚼贡，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符數(shù)酝蜒，而不是字節(jié)數(shù)，如果是utf-8矾湃，一個(gè)數(shù)字 或字母占1個(gè)字節(jié)亡脑，一個(gè)漢字占3個(gè)字節(jié)
  • char(n)：如果存漢字長(zhǎng)度就是 3n 字節(jié)
  • varchar(n)：如果存漢字則長(zhǎng)度是 3n + 2 字節(jié)，加的2字節(jié)用來存儲(chǔ)字符串長(zhǎng)度洲尊，因?yàn)?varchar是變長(zhǎng)字符串
• 數(shù)值類型
  • tinyint：1字節(jié)
  • smallint：2字節(jié)
  • int：4字節(jié)
  • bigint：8字節(jié)
• 時(shí)間類型
  • date：3字節(jié)
  • timestamp：4字節(jié)
  • datetime：8字節(jié)
• 如果字段允許為 NULL远豺，需要1字節(jié)記錄是否為 NULL

1.1.1.8 ref列

這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量坞嘀，常見的有：const（常量）躯护，字段名（例：film.id）

1.1.1.9 rows列

這一列是mysql估計(jì)要讀取并檢測(cè)的行數(shù)，注意這個(gè)不是結(jié)果集里的行數(shù)丽涩。

1.1.1.10 Extra列

這一列展示的是額外信息棺滞。常見的重要值如下：

• Using index：使用覆蓋索引
• Using where：使用 where 語句來處理結(jié)果，并且查詢的列未被索引覆蓋
• Using index condition：查詢的列不完全被索引覆蓋矢渊，where條件中是一個(gè)前導(dǎo)列的范圍
• Using temporary：mysql需要?jiǎng)?chuàng)建一張臨時(shí)表來處理查詢继准。出現(xiàn)這種情況一般是要進(jìn)行優(yōu)化的，首先是想到用索 引來優(yōu)化矮男。
• Using filesort：將用外部排序而不是索引排序移必，數(shù)據(jù)較小時(shí)從內(nèi)存排序，否則需要在磁盤完成排序毡鉴。這種情況下一 般也是要考慮使用索引來優(yōu)化的崔泵。
• Select tables optimized away：使用某些聚合函數(shù)（比如 max、min）來訪問存在索引的某個(gè)字段是

2. 索引最佳實(shí)踐

首先先插入測(cè)試數(shù)據(jù):

 CREATE TABLE `employees` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', 
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡', 
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位', 
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時(shí)間', 
  PRIMARY KEY (`id`), 
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE 
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';
  
  INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
  INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
  INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

2.1 全值匹配

explain select * from employees where name = 'LiLei';

[圖片上傳失敗...(image-43215a-1606990177870)]

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

[圖片上傳失敗...(image-6fc3a5-1606990177870)]

 EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manage r';

[圖片上傳失敗...(image-e86f73-1606990177870)]

2.2 最左前綴法則

如果索引了多列猪瞬，要遵守最左前綴法則憎瘸。指的是查詢從索引的最左前列開始并且不跳過索引中的列。

EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;

[圖片上傳失敗...(image-b066b0-1606990177870)]

EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';

[圖片上傳失敗...(image-e1b1b5-1606990177870)]

 EXPLAIN SELECT * FROM employees WHERE position = 'manager';

[圖片上傳失敗...(image-dc9a57-1606990177870)]

只有第一個(gè)語句走了索引(觀察type字段)

2.3 不在索引列上做任何操作（計(jì)算陈瘦、函數(shù)幌甘、（自動(dòng)or手動(dòng)）類型轉(zhuǎn)換），會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

[圖片上傳失敗...(image-7b07a4-1606990177870)]

2.4 存儲(chǔ)引擎不能使用索引中范圍條件右邊的列

 EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manage r';

[圖片上傳失敗...(image-5f3767-1606990177870)]
觀察key_len字段痊项，可以看出只用到了name和age索引锅风。

2.5 盡量使用覆蓋索引（只訪問索引的查詢（索引列包含查詢列）），減少 select * 語句

EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

[圖片上傳失敗...(image-4f0c7e-1606990177871)]

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manage r';

[圖片上傳失敗...(image-4caffc-1606990177871)]

2.6 mysql在使用不等于（鞍泉！=或者<>）遏弱，not in ，not exists 的時(shí)候無法使用索引會(huì)導(dǎo)致全表掃描 < 小于塞弊、 > 大于漱逸、 <=泪姨、>= 這些，mysql內(nèi)部?jī)?yōu)化器會(huì)根據(jù)檢索比例饰抒、表大小等多個(gè)因素整體評(píng)估是否使用索引

 EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

[圖片上傳失敗...(image-b3f615-1606990177871)]

2.7 is null,is not null 一般情況下也無法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null

[圖片上傳失敗...(image-1dad79-1606990177871)]

2.8 like以通配符開頭（'$abc...'）mysql索引失效會(huì)變成全表掃描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

[圖片上傳失敗...(image-abf4ce-1606990177871)]

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'

[圖片上傳失敗...(image-2945dd-1606990177871)]

2.9 字符串不加單引號(hào)索引失效

EXPLAIN SELECT * FROM employees WHERE name = 1000;

[圖片上傳失敗...(image-a853f4-1606990177871)]
相當(dāng)于做了一次類型轉(zhuǎn)換肮砾。

2.10 少用or或in，用它查詢時(shí)袋坑，mysql不一定使用索引仗处，mysql內(nèi)部?jī)?yōu)化器會(huì)根據(jù)檢索比例、表大小等多個(gè)因素整體評(píng) 估是否使用索引枣宫，詳見范圍查詢優(yōu)化

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

[圖片上傳失敗...(ttp://note.youdao.com/yws/res/17680/1A5C6693DC2B48E992D03D929CA8F6DD)]

2.11 范圍查詢優(yōu)化

給年齡添加單值索引

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;  explain select * from employees where age >=1 and age <=2000;

[圖片上傳失敗...(image-1d7952-1606990177871)]

沒走索引原因：mysql內(nèi)部?jī)?yōu)化器會(huì)根據(jù)檢索比例婆誓、表大小等多個(gè)因素整體評(píng)估是否使用索引。比如這個(gè)例子也颤，可能是由于單次數(shù)據(jù)量查詢過大導(dǎo)致優(yōu)化器最終選擇不走索引洋幻。

優(yōu)化方法：可以將大的范圍拆分成多個(gè)小范圍

 explain select * from employees where age >=1001 and age <=2000;

[圖片上傳失敗...(image-828336-1606990177871)]

還原最初索引狀態(tài)

ALTER TABLE `employees` DROP INDEX `idx_age`;

2.12 索引使用總結(jié)

[圖片上傳失敗...(image-4e6d77-1606990177871)]
like KK%相當(dāng)于=常量，%KK和%KK% 相當(dāng)于范圍