[圖片上傳失敗...(image-5ecabb-1541744995168)]

內(nèi)容整理于網(wǎng)絡(luò)

一导饲、EXPLAIN

做MySQL優(yōu)化姐扮，我們要善用 EXPLAIN 查看SQL執(zhí)行計(jì)劃。

EXPLAIN 輸出格式

EXPLAIN 命令的輸出內(nèi)容大致如下:

mysql root@localhost:youdi_auth> explain select * from  auth_user\G;
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | auth_user
partitions    | <null>
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 19
filtered      | 100.0
Extra         | <null>
1 row in set
Time: 0.008s

各列的含義如下:

• id: SELECT 查詢的標(biāo)識符. 每個 SELECT 都會自動分配一個唯一的標(biāo)識符.
• select_type: SELECT 查詢的類型.
• table: 查詢的是哪個表
• partitions: 匹配的分區(qū)
• type: join 類型
• possible_keys: 此次查詢中可能選用的索引
• key: 此次查詢中確切使用到的索引.
• ref: 哪個字段或常數(shù)與 key 一起被使用
• rows: 顯示此查詢一共掃描了多少行. 這個是一個估計(jì)值.
• filtered: 表示此查詢條件所過濾的數(shù)據(jù)的百分比
• extra: 額外的信息

接下來我們來重點(diǎn)看一下比較重要的幾個字段.

select_type

select_type 表示了查詢的類型, 它的常用取值有:

• SIMPLE, 表示此查詢不包含 UNION 查詢或子查詢
• PRIMARY, 表示此查詢是最外層的查詢
• UNION, 表示此查詢是 UNION 的第二或隨后的查詢
• DEPENDENT UNION, UNION 中的第二個或后面的查詢語句, 取決于外面的查詢
• UNION RESULT, UNION 的結(jié)果
• SUBQUERY, 子查詢中的第一個 SELECT
• DEPENDENT SUBQUERY: 子查詢中的第一個 SELECT, 取決于外面的查詢. 即子查詢依賴于外層查詢的結(jié)果.

最常見的查詢類別應(yīng)該是 SIMPLE 了, 比如當(dāng)我們的查詢沒有子查詢, 也沒有 UNION 查詢時, 那么通常就是 SIMPLE 類型, 例如:

mysql root@localhost:youdi_auth> explain select * from  auth_user where id = 396\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | auth_user
partitions    | <null>
type          | const
possible_keys | PRIMARY
key           | PRIMARY
key_len       | 4
ref           | const
rows          | 1
filtered      | 100.0
Extra         | <null>
1 row in set
Time: 0.008s

如果我們使用了 UNION 查詢, 那么 EXPLAIN 輸出 的結(jié)果類似如下:

mysql root@localhost:youdi_auth> explain (select * from  auth_user where id in (1,2,3,4)) UNION (select * from auth_user where id in (4,5,6,7,8))\G
***************************[ 1. row ]***************************
id            | 1
select_type   | PRIMARY
table         | auth_user
partitions    | <null>
type          | range
possible_keys | PRIMARY
key           | PRIMARY
key_len       | 4
ref           | <null>
rows          | 4
filtered      | 100.0
Extra         | Using where
***************************[ 2. row ]***************************
id            | 2
select_type   | UNION
table         | auth_user
partitions    | <null>
type          | range
possible_keys | PRIMARY
key           | PRIMARY
key_len       | 4
ref           | <null>
rows          | 5
filtered      | 100.0
Extra         | Using where
***************************[ 3. row ]***************************
id            | <null>
select_type   | UNION RESULT
table         | <union1,2>
partitions    | <null>
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | <null>
filtered      | <null>
Extra         | Using temporary
3 rows in set
Time: 0.009s

table

表示查詢涉及的表或衍生表

type

type 字段比較重要, 它提供了判斷查詢是否高效的重要依據(jù)依據(jù). 通過 type 字段, 我們判斷此次查詢是 全表掃描 還是 索引掃描等.

type 常用類型

type 常用的取值有:

• system: 表中只有一條數(shù)據(jù). 這個類型是特殊的 const 類型.
• const: 針對主鍵或唯一索引的等值查詢掃描, 最多只返回一行數(shù)據(jù). const 查詢速度非撤什眩快, 因?yàn)樗鼉H僅讀取一次即可.
  例如下面的這個查詢, 它使用了主鍵索引, 因此 type 就是 const 類型的.

mysql root@localhost:youdi_auth> explain select * from  auth_user where id = 394\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | auth_user
partitions    | <null>
type          | const
possible_keys | PRIMARY
key           | PRIMARY
key_len       | 4
ref           | const
rows          | 1
filtered      | 100.0
Extra         | <null>
1 row in set
Time: 0.008s

• eq_ref: 此類型通常出現(xiàn)在多表的 join 查詢, 表示對于前表的每一個結(jié)果, 都只能匹配到后表的一行結(jié)果. 并且查詢的比較操作通常是 =, 查詢效率較高. 例如:

mysql root@localhost:youmi_auth> explain select * from auth_user,auth_user_groups where auth_user.id = auth_user_groups.user_id\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | auth_user_groups
partitions    | <null>
type          | index
possible_keys | user_group_id
key           | user_group_id
key_len       | 8
ref           | <null>
rows          | 2
filtered      | 100.0
Extra         | Using index
***************************[ 2. row ]***************************
id            | 1
select_type   | SIMPLE
table         | auth_user
partitions    | <null>
type          | eq_ref
possible_keys | PRIMARY
key           | PRIMARY
key_len       | 4
ref           | youdi_auth.auth_user_groups.user_id
rows          | 1
filtered      | 100.0
Extra         | <null>
2 rows in set
Time: 0.008s

• ref: 此類型通常出現(xiàn)在多表的 join 查詢, 針對于非唯一或非主鍵索引, 或者是使用了 最左前綴 規(guī)則索引的查詢.
  例如下面這個例子中, 就使用到了 ref 類型的查詢:

mysql root@localhost:youdi_auth> explain select * from auth_user,auth_user_groups where auth_user.id = auth_user_groups.user_id and auth_user.id = 6\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | auth_user
partitions    | <null>
type          | const
possible_keys | PRIMARY
key           | PRIMARY
key_len       | 4
ref           | const
rows          | 1
filtered      | 100.0
Extra         | <null>
***************************[ 2. row ]***************************
id            | 1
select_type   | SIMPLE
table         | auth_user_groups
partitions    | <null>
type          | ref
possible_keys | user_group_id
key           | user_group_id
key_len       | 4
ref           | const
rows          | 2
filtered      | 100.0
Extra         | Using index
2 rows in set
Time: 0.008s

• range: 表示使用索引范圍查詢, 通過索引字段范圍獲取表中部分?jǐn)?shù)據(jù)記錄. 這個類型通常出現(xiàn)在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
  當(dāng) type 是 range 時, 那么 EXPLAIN 輸出的 ref 字段為 NULL, 并且 key_len 字段是此次查詢中使用到的索引的最長的那個.

例如下面的例子就是一個范圍查詢:

mysql root@localhost:youmi_auth> explain select * from  auth_user where id between 2 and 400\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | auth_user
partitions    | <null>
type          | range
possible_keys | PRIMARY
key           | PRIMARY
key_len       | 4
ref           | <null>
rows          | 18
filtered      | 100.0
Extra         | Using where
1 row in set
Time: 0.008s

• index: 表示全索引掃描(full index scan), 和 ALL 類型類似, 只不過 ALL 類型是全表掃描, 而 index 類型則僅僅掃描所有的索引, 而不掃描數(shù)據(jù).
  index 類型通常出現(xiàn)在: 所要查詢的數(shù)據(jù)直接在索引樹中就可以獲取到, 而不需要掃描數(shù)據(jù). 當(dāng)是這種情況時, Extra 字段 會顯示 Using index.

例如:

mysql root@localhost:youdi_auth> explain select name from  auth_user\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | auth_user
partitions    | <null>
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 19
filtered      | 100.0
Extra         | <null>
1 row in set
Time: 0.008s

上面的例子中, 我們查詢的 name 字段恰好是一個索引, 因此我們直接從索引中獲取數(shù)據(jù)就可以滿足查詢的需求了, 而不需要查詢表中的數(shù)據(jù). 因此這樣的情況下, type 的值是 index, 并且 Extra 的值是 Using index.

• ALL: 表示全表掃描, 這個類型的查詢是性能最差的查詢之一. 通常來說, 我們的查詢不應(yīng)該出現(xiàn) ALL 類型的查詢, 因?yàn)檫@樣的查詢在數(shù)據(jù)量大的情況下, 對數(shù)據(jù)庫的性能是巨大的災(zāi)難. 如一個查詢是 ALL 類型查詢, 那么一般來說可以對相應(yīng)的字段添加索引來避免.
  下面是一個全表掃描的例子, 可以看到, 在全表掃描時, possible_keys 和 key 字段都是 NULL, 表示沒有使用到索引, 并且 rows 十分巨大, 因此整個查詢效率是十分低下的.

mysql root@localhost:youdi_auth> explain select name from  auth_user where name='liangchangyou'\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | auth_user
partitions    | <null>
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 19
filtered      | 10.0
Extra         | Using where
1 row in set
Time: 0.008s

type 類型的性能比較

通常來說, 不同的 type 類型的性能關(guān)系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL 類型因?yàn)槭侨頀呙? 因此在相同的查詢條件下, 它是速度最慢的.
而 index 類型的查詢雖然不是全表掃描, 但是它掃描了所有的索引, 因此比 ALL 類型的稍快.
后面的幾種類型都是利用了索引來查詢數(shù)據(jù), 因此可以過濾部分或大部分?jǐn)?shù)據(jù), 因此查詢效率就比較高了.

possible_keys

possible_keys 表示 MySQL 在查詢時, 能夠使用到的索引. 注意, 即使有些索引在 possible_keys 中出現(xiàn), 但是并不表示此索引會真正地被 MySQL 使用到. MySQL 在查詢時具體使用了哪些索引, 由 key 字段決定.

key

此字段是 MySQL 在當(dāng)前查詢時所真正使用到的索引.

key_len

表示查詢優(yōu)化器使用了索引的字節(jié)數(shù). 這個字段可以評估組合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的計(jì)算規(guī)則如下:

• 字符串
  • char(n): n 字節(jié)長度
  • varchar(n): 如果是 utf8 編碼, 則是 3 *n + 2字節(jié); 如果是 utf8mb4 編碼, 則是 4 *n + 2 字節(jié).
• 數(shù)值類型:
  • TINYINT: 1字節(jié)
  • SMALLINT: 2字節(jié)
  • MEDIUMINT: 3字節(jié)
  • INT: 4字節(jié)
  • BIGINT: 8字節(jié)
• 時間類型
  • DATE: 3字節(jié)
  • TIMESTAMP: 4字節(jié)
  • DATETIME: 8字節(jié)
• 字段屬性: NULL 屬性 占用一個字節(jié). 如果一個字段是 NOT NULL 的, 則沒有此屬性.

我們來舉兩個簡單的栗子:

mysql root@localhost:youmi_auth> explain select * from auth_user where id > 10 and name = 'liangchangyou' and status = 0\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | auth_user
partitions    | <null>
type          | range
possible_keys | PRIMARY,id-name-status
key           | PRIMARY
key_len       | 4
ref           | <null>
rows          | 12
filtered      | 5.26
Extra         | Using where
1 row in set
Time: 0.007s

上面的例子是從表 order_info 中查詢指定的內(nèi)容, 而我們從此表的建表語句中可以知道, 表 order_info 有一個聯(lián)合索引:

 index `id-name-status` (`id`, `name`, `status`);

不過此查詢語句 where id > 10 and name = 'liangchangyou' and status = 0\G 中, 因?yàn)橄冗M(jìn)行 id 的范圍查詢, 而根據(jù) 最左前綴匹配 原則, 當(dāng)遇到范圍查詢時, 就停止索引的匹配, 因此實(shí)際上我們使用到的索引的字段只有 id,

上面因?yàn)?最左前綴匹配 原則, 我們的查詢僅僅使用到了聯(lián)合索引的 id 字段, 因此效率不算高

rows

rows 也是一個重要的字段. MySQL 查詢優(yōu)化器根據(jù)統(tǒng)計(jì)信息, 估算 SQL 要查找到結(jié)果集需要掃描讀取的數(shù)據(jù)行數(shù).
這個值非常直觀顯示 SQL 的效率好壞, 原則上 rows 越少越好.

Extra

EXplain 中的很多額外的信息會在 Extra 字段顯示, 常見的有以下幾種內(nèi)容:

• Using filesort
  當(dāng) Extra 中有 Using filesort 時, 表示 MySQL 需額外的排序操作, 不能通過索引順序達(dá)到排序效果. 一般有 Using filesort, 都建議優(yōu)化去掉, 因?yàn)檫@樣的查詢 CPU 資源消耗大.

• Using index
  "覆蓋索引掃描", 表示查詢在索引樹中就可查找所需數(shù)據(jù), 不用掃描表數(shù)據(jù)文件, 往往說明性能不錯

• Using temporary
  查詢有使用臨時表, 一般出現(xiàn)于排序, 分組和多表 join 的情況, 查詢效率不高, 建議優(yōu)化.

二跳纳、SQL語句中IN包含的值不應(yīng)過多

MySQL對于IN做了相應(yīng)的優(yōu)化，即將IN中的常量全部存儲在一個數(shù)組里面绝骚，而且這個數(shù)組是排好序的耐版。但是如果數(shù)值較多，產(chǎn)生的消耗也是比較大的压汪。再例如：select id from t where num in(1,2,3) 對于連續(xù)的數(shù)值粪牲，能用 between 就不要用 in 了；再或者使用連接來替換止剖。

三腺阳、SELECT語句務(wù)必指明字段名稱

SELECT *增加很多不必要的消耗（cpu落君、io、內(nèi)存亭引、網(wǎng)絡(luò)帶寬）绎速；增加了使用覆蓋索引的可能性；當(dāng)表結(jié)構(gòu)發(fā)生改變時焙蚓，前斷也需要更新纹冤。所以要求直接在select后面接上字段名。

四购公、當(dāng)只需要一條數(shù)據(jù)的時候萌京，使用limit 1

這是為了使EXPLAIN中type列達(dá)到const類型

五、如果排序字段沒有用到索引君丁，就盡量少排序

六枫夺、如果限制條件中其他字段沒有索引，盡量少用or

or兩邊的字段中绘闷，如果有一個不是索引字段橡庞，而其他條件也不是索引字段，會造成該查詢不走索引的情況印蔗。很多時候使用 union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果

七扒最、盡量用union all代替union

union和union all的差異主要是前者需要將結(jié)果集合并后再進(jìn)行唯一性過濾操作，這就會涉及到排序华嘹，增加大量的CPU運(yùn)算吧趣，加大資源消耗及延遲。當(dāng)然耙厚，union all的前提條件是兩個結(jié)果集沒有重復(fù)數(shù)據(jù)强挫。

八、不使用ORDER BY RAND()

select id from `dynamic` order by rand() limit 1000;

上面的sql語句薛躬，可優(yōu)化為

select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nid limit 1000;

九俯渤、區(qū)分in和exists， not in和not exists

select * from 表A where id in (select id from 表B)

上面sql語句相當(dāng)于

select * from 表A where exists(select * from 表B where 表B.id=表A.id)

區(qū)分in和exists主要是造成了驅(qū)動順序的改變（這是性能變化的關(guān)鍵）型宝，如果是exists八匠，那么以外層表為驅(qū)動表，先被訪問趴酣，如果是IN梨树，那么先執(zhí)行子查詢。所以IN適合于外表大而內(nèi)表小的情況岖寞；EXISTS適合于外表小而內(nèi)表大的情況抡四。關(guān)于not in和not exists，推薦使用not exists仗谆，不僅僅是效率問題床嫌，not in可能存在邏輯問題跨释。如何高效的寫出一個替代not exists的sql語句胸私？

原sql語句

select colname … from A表 where a.id not in (select b.id from B表)

高效的sql語句

select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

取出的結(jié)果集如下圖表示厌处，A表不在B表中的數(shù)據(jù)

[圖片上傳失敗...(image-138524-1541744995167)]

十、使用合理的分頁方式以提高分頁的效率

select id,name from product limit 866613, 20

使用上述sql語句做分頁的時候岁疼，可能有人會發(fā)現(xiàn)阔涉，隨著表數(shù)據(jù)量的增加，直接使用limit分頁查詢會越來越慢捷绒。

優(yōu)化的方法如下：可以取前一頁的最大行數(shù)的id瑰排，然后根據(jù)這個最大的id來限制下一頁的起點(diǎn)。比如此列中暖侨，上一頁最大的id是866612椭住。sql可以采用如下的寫法：

select id,name from product where id> 866612 limit 20

十一、分段查詢

在一些用戶選擇頁面中字逗，可能一些用戶選擇的時間范圍過大京郑，造成查詢緩慢。主要的原因是掃描行數(shù)過多葫掉。這個時候可以通過程序些举，分段進(jìn)行查詢，循環(huán)遍歷俭厚，將結(jié)果合并處理進(jìn)行展示户魏。

掃描的行數(shù)成百萬級以上的時候就可以使用分段查詢

十二、避免在 where 子句中對字段進(jìn)行 null 值判斷

對于null的判斷會導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描挪挤。

十三叼丑、不建議使用%前綴模糊查詢

例如LIKE “%name”或者LIKE “%name%”，這種查詢會導(dǎo)致索引失效而進(jìn)行全表掃描扛门。但是可以使用LIKE “name%”鸠信。

那如何查詢%name%？

雖然給字段添加了索引尖飞，但在explain結(jié)果果并沒有使用

[圖片上傳失敗...(image-ae7d37-1541744995167)]

那么如何解決這個問題呢症副，答案：使用全文索引

在我們查詢中經(jīng)常會用到select id,fnum,fdst from dynamic_201606 where user_name like '%zhangsan%'; 。這樣的語句政基，普通索引是無法滿足查詢需求的贞铣。慶幸的是在MySQL中，有全文索引來幫助我們沮明。

創(chuàng)建全文索引的sql語法是：

ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

使用全文索引的sql語句是：

select id,fnum,fdst from dynamic_201606 where match(user_name) against('zhangsan' in boolean mode);

注意：在需要創(chuàng)建全文索引之前辕坝，請聯(lián)系DBA確定能否創(chuàng)建。同時需要注意的是查詢語句的寫法與普通索引的區(qū)別

十四荐健、避免在where子句中對字段進(jìn)行表達(dá)式操作

比如

select user_id,user_project from user_base where age*2=36;

中對字段就行了算術(shù)運(yùn)算酱畅，這會造成引擎放棄使用索引琳袄，建議改成

select user_id,user_project from user_base where age=36/2;

十五、避免隱式類型轉(zhuǎn)換

where 子句中出現(xiàn) column 字段的類型和傳入的參數(shù)類型不一致的時候發(fā)生的類型轉(zhuǎn)換纺酸，建議先確定where中的參數(shù)類型

[圖片上傳失敗...(image-f9f86-1541744995167)]

十六窖逗、對于聯(lián)合索引來說，要遵守最左前綴法則

舉列來說索引含有字段id,name,school餐蔬，可以直接用id字段碎紊，也可以id,name這樣的順序，但是name;school都無法使用這個索引樊诺。所以在創(chuàng)建聯(lián)合索引的時候一定要注意索引字段順序仗考，常用的查詢字段放在最前面

十七、必要時可以使用force index來強(qiáng)制查詢走某個索引

有的時候MySQL優(yōu)化器采取它認(rèn)為合適的索引來檢索sql語句词爬，但是可能它所采用的索引并不是我們想要的秃嗜。這時就可以采用force index來強(qiáng)制優(yōu)化器使用我們制定的索引。

十八顿膨、注意范圍查詢語句

對于聯(lián)合索引來說锅锨，如果存在范圍查詢，比如between,>,<等條件時虽惭，會造成后面的索引字段失效橡类。

十九、關(guān)于JOIN優(yōu)化

[圖片上傳失敗...(image-fe4fde-1541744995167)]

• LEFT JOIN A表為驅(qū)動表
• INNER JOIN MySQL會自動找出那個數(shù)據(jù)少的表作用驅(qū)動表
• RIGHT JOIN B表為驅(qū)動表

注意：MySQL中沒有full join芽唇，可以用以下方式來解決

select * from A left join B on B.name = A.name 
where B.name is null
 union all
select * from B;

盡量使用inner join顾画，避免left join

參與聯(lián)合查詢的表至少為2張表，一般都存在大小之分匆笤。如果連接方式是inner join研侣，在沒有其他過濾條件的情況下MySQL會自動選擇小表作為驅(qū)動表，但是left join在驅(qū)動表的選擇上遵循的是左邊驅(qū)動右邊的原則炮捧，即left join左邊的表名為驅(qū)動表庶诡。

合理利用索引

被驅(qū)動表的索引字段作為on的限制字段。

利用小表去驅(qū)動大表

[圖片上傳失敗...(image-8efd37-1541744995167)]

從原理圖能夠直觀的看出如果能夠減少驅(qū)動表的話咆课，減少嵌套循環(huán)中的循環(huán)次數(shù)末誓，以減少 IO總量及CPU運(yùn)算的次數(shù)。

巧用STRAIGHT_JOIN

inner join是由mysql選擇驅(qū)動表书蚪，但是有些特殊情況需要選擇另個表作為驅(qū)動表喇澡，比如有g(shù)roup by、order by等「Using filesort」殊校、「Using temporary」時晴玖。STRAIGHT_JOIN來強(qiáng)制連接順序，在STRAIGHT_JOIN左邊的表名就是驅(qū)動表，右邊則是被驅(qū)動表呕屎。在使用STRAIGHT_JOIN有個前提條件是該查詢是內(nèi)連接让簿，也就是inner join。其他鏈接不推薦使用STRAIGHT_JOIN秀睛，否則可能造成查詢結(jié)果不準(zhǔn)確尔当。

這個方式有時可能減少3倍的時間。

作者：若與
鏈接：http://www.reibang.com/p/5a8bd14434ae
來源：簡書
簡書著作權(quán)歸作者所有琅催，任何形式的轉(zhuǎn)載都請聯(lián)系作者獲得授權(quán)并注明出處居凶。