上一篇介紹了，MySQL的索引，這次介紹如何對索引進行優(yōu)化。

1. 工具使用

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

1.1 Explain工具

使用EXPLAIN關鍵字可以模擬優(yōu)化器執(zhí)行SQL語句，分析你的查詢語句或是結構的性能瓶頸在 select 語句之前增加 explain 關鍵字，MySQL 會在查詢上設置一個標記，執(zhí)行查詢會返回執(zhí)行計劃的信息，而不是執(zhí)行這條SQL。

注意:如果 from 中包含子查詢，仍會執(zhí)行該子查詢，將結果放入臨時表中。

1.1.1 Explain中的列

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

Explain輸出行

接下來我們將展示explain中每個列的信息。

1.1.1.1 id列

id列的編號是 select 的序列號，有幾個 select 就有幾個id，并且id的順序是按 select 出現(xiàn)的順序增長的。 id列越大執(zhí)行優(yōu)先級越高，id相同則從上往下執(zhí)行，id為NULL最后執(zhí)行。

1.1.1.2 select_type列

select_type 表示對應行是簡單還是復雜的查詢。

simple：簡單查詢。查詢不包含子查詢和union
primary：復雜查詢中最外層的 select
subquery：包含在 select 中的子查詢（不在 from 子句中）
derived：包含在 from 子句中的子查詢。MySQL會將結果存放在一個臨時表中，也稱為派生表（derived的英文含義）
union：在 union 中的第二個和隨后的 select

1.1.1.3 table列

這一列表示 explain 的一行正在訪問哪個表。當 from 子句中有子查詢時，table列是 <derivenN> 格式，表示當前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查詢。當有 union 時，UNION RESULT 的 table 列的值為<union1,2>，1和2表示參與 union 的 select 行id。

1.1.1.4 type列

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

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

一般來說，得保證查詢達到range級別，最好達到ref

NULL：mysql能夠在優(yōu)化階段分解查詢語句，在執(zhí)行階段用不著再訪問表或索引。例如：在索引列中選取最小值，可以單獨查找索引來完成，不需要在執(zhí)行時訪問表
const, system：mysql能對查詢的某部分進行優(yōu)化并將其轉化成一個常量（可以看show warnings 的結果）。用于 primary key 或 unique key 的所有列與常數(shù)比較時，所以表最多有一個匹配行，讀取1次，速度比較快。system是 const的特例，表里只有一條元組匹配時為system
eq_ref：primary key 或 unique key 索引的所有部分被連接使用，最多只會返回一條符合條件的記錄。這可能是在 const 之外最好的聯(lián)接類型了，簡單的 select 查詢不會出現(xiàn)這種 type。
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值相比較，可能會找到多個符合條件的行。
range：范圍掃描通常出現(xiàn)在 in(), between ,> ,<, >= 等操作中。使用一個索引來檢索給定范圍的行。
index：掃描全索引就能拿到結果，一般是掃描某個二級索引，這種掃描不會從索引樹根節(jié)點開始快速查找，而是直接對二級索引的葉子節(jié)點遍歷和掃描，速度還是比較慢的，這種查詢一般為使用覆蓋索引，二級索引一般比較小，所以這種通常比ALL快一些。
ALL：即全表掃描，掃描你的聚簇索引的所有葉子節(jié)點。通常情況下這需要增加索引來進行優(yōu)化了。

1.1.1.5 possible_keys列

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

explain 時可能出現(xiàn) possible_keys有列，而key顯示NULL的情況，這種情況是因為表中數(shù)據(jù)不多，mysql認為索引對此查詢幫助不大，選擇了全表查詢。

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

1.1.1.6 key列

這一列顯示mysql實際采用哪個索引來優(yōu)化對該表的訪問。如果沒有使用索引，則該列是 NULL。如果想強制mysql使用或忽視possible_keys列中的索引，在查詢中使用 force index、ignore index。

1.1.1.7 key_len列

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

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

字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符數(shù)，而不是字節(jié)數(shù)，如果是utf-8，一個數(shù)字或字母占1個字節(jié)，一個漢字占3個字節(jié)
- char(n)：如果存漢字長度就是 3n 字節(jié)
- varchar(n)：如果存漢字則長度是 3n + 2 字節(jié)，加的2字節(jié)用來存儲字符串長度，因為 varchar是變長字符串
數(shù)值類型
- tinyint：1字節(jié)
- smallint：2字節(jié)
- int：4字節(jié)
- bigint：8字節(jié)
時間類型
- 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估計要讀取并檢測的行數(shù)，注意這個不是結果集里的行數(shù)。

1.1.1.10 Extra列

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

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

2. 索引最佳實踐

首先先插入測試數(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 '入職時間', 
  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)]

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

2.3 不在索引列上做任何操作（計算、函數(shù)、（自動or手動）類型轉換），會導致索引失效而轉向全表掃描

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

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

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

 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 的時候無法使用索引會導致全表掃描 < 小于、 > 大于、 <=、>= 這些，mysql內(nèi)部優(yōu)化器會根據(jù)檢索比例、表大小等多個因素整體評估是否使用索引

 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索引失效會變成全表掃描操作

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

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

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

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

2.9 字符串不加單引號索引失效

EXPLAIN SELECT * FROM employees WHERE name = 1000;

[圖片上傳失敗...(image-a853f4-1606990177871)]
相當于做了一次類型轉換。

2.10 少用or或in，用它查詢時，mysql不一定使用索引，mysql內(nèi)部優(yōu)化器會根據(jù)檢索比例、表大小等多個因素整體評估是否使用索引，詳見范圍查詢優(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)部優(yōu)化器會根據(jù)檢索比例、表大小等多個因素整體評估是否使用索引。比如這個例子，可能是由于單次數(shù)據(jù)量查詢過大導致優(yōu)化器最終選擇不走索引。

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

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

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

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

ALTER TABLE `employees` DROP INDEX `idx_age`;

2.12 索引使用總結

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

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MySQL索引優(yōu)化(一)

MySQL索引優(yōu)化(一)

1. 工具使用

1.1 Explain工具

1.1.1 Explain中的列

1.1.1.1 id列

1.1.1.2 select_type列

1.1.1.3 table列

1.1.1.4 type列

1.1.1.5 possible_keys列

1.1.1.6 key列

1.1.1.7 key_len列

1.1.1.8 ref列

1.1.1.9 rows列

1.1.1.10 Extra列

2. 索引最佳實踐

2.1 全值匹配

2.2 最左前綴法則

2.3 不在索引列上做任何操作（計算、函數(shù)、（自動or手動）類型轉換），會導致索引失效而轉向全表掃描

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

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

2.6 mysql在使用不等于（！=或者<>），not in ，not exists 的時候無法使用索引會導致全表掃描 < 小于、 > 大于、 <=、>= 這些，mysql內(nèi)部優(yōu)化器會根據(jù)檢索比例、表大小等多個因素整體評估是否使用索引

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

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

2.9 字符串不加單引號索引失效

2.10 少用or或in，用它查詢時，mysql不一定使用索引，mysql內(nèi)部優(yōu)化器會根據(jù)檢索比例、表大小等多個因素整體評估是否使用索引，詳見范圍查詢優(yōu)化

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

2.12 索引使用總結

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

MySQL索引優(yōu)化(一)

1. 工具使用

1.1 Explain工具

1.1.1 Explain中的列

1.1.1.1 id列

1.1.1.2 select_type列

1.1.1.3 table列

1.1.1.4 type列

1.1.1.5 possible_keys列

1.1.1.6 key列

1.1.1.7 key_len列

1.1.1.8 ref列

1.1.1.9 rows列

1.1.1.10 Extra列

2. 索引最佳實踐

2.1 全值匹配

2.2 最左前綴法則

2.3 不在索引列上做任何操作（計算、函數(shù)、（自動or手動）類型轉換），會導致索引失效而轉向全表掃描

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

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

2.6 mysql在使用不等于（！=或者<>），not in ，not exists 的時候無法使用索引會導致全表掃描 < 小于、 > 大于、 <=、>= 這些，mysql內(nèi)部優(yōu)化器會根據(jù)檢索比例、表大小等多個因素整體評估是否使用索引

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

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

2.9 字符串不加單引號索引失效

2.10 少用or或in，用它查詢時，mysql不一定使用索引，mysql內(nèi)部優(yōu)化器會根據(jù)檢索比例、表大小等多個因素整體評 估是否使用索引，詳見范圍查詢優(yōu)化

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

2.12 索引使用總結

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

2.3 不在索引列上做任何操作（計算、函數(shù)、（自動or手動）類型轉換），會導致索引失效而轉向全表掃描

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

2.6 mysql在使用不等于（！=或者<>），not in ，not exists 的時候無法使用索引會導致全表掃描 < 小于、 > 大于、 <=、>= 這些，mysql內(nèi)部優(yōu)化器會根據(jù)檢索比例、表大小等多個因素整體評估是否使用索引

2.10 少用or或in，用它查詢時，mysql不一定使用索引，mysql內(nèi)部優(yōu)化器會根據(jù)檢索比例、表大小等多個因素整體評估是否使用索引，詳見范圍查詢優(yōu)化