文章目錄

看完本篇文章你能學到什么？
一、MySQL執行計劃
- 1.1 id欄位
- 1.2 select_type 欄位
- 1.3 table 欄位
- 1.4 partitions 欄位
- 1.5 type欄位
- 1.6 possible_keys 欄位
- 1.7 key 欄位
- 1.8 key_len 欄位
- 1.9 ref 欄位
- 1.10 rows 欄位
- 1.11 filtered 欄位
- 1.12 extra 欄位
二、總結
- 好了，本篇就說到這里了，看完覺得有幫助的童鞋記得點贊！點贊！點贊！（重要的事情說三遍）

看完本篇文章你能學到什么？

1、掌握MySQL執行計劃所有欄位含義以及內容，為后續SQL調優提供幫助

2、對SQL調優有初步認識

一、MySQL執行計劃

專案開發中，性能往往都是是我們重點關注的問題，其實很多時候一個SQL往往是整個請求中瓶頸最大的地方，因此我們必須了解SQL陳述句的執行程序、資料庫中是如何掃描表、如何使用索引的、是否命中索引等資訊來幫助我們做SQL陳述句的優化，MySQL提供了explain/desc陳述句，來顯示這條SQL陳述句的執行計劃，執行計劃可以幫助我們查看SQL陳述句的執行情況，我們可以根據反饋的結果來進行SQL的優化，

準備測驗資料

use test;
CREATE TABLE `test`.`role`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `test`.`user`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

explain和desc效果一樣，都是幫我們列出SQL陳述句的執行計劃，

mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

兩個陳述句的效果是一模一樣的，我們后面就統一使用explain來查看SQL陳述句的執行計劃，

1.1 id欄位

id欄位存在三種情況：

1）id相同：id越靠前的表越先執行

explain select * from user u left join role r on u.role_id=r.id;

在這里插入圖片描述
2）id不同：id越大的表越先執行

explain select * from user u where u.role_id=(select id from role r where r.id=1);

在這里插入圖片描述
3）id有相同，也有不同：id相同越大的表越先執行，在id相同的表中，id越靠前的表越先執行

1.2 select_type 欄位

SIMPLE：簡單的 select 查詢，查詢中不包含子查詢或者 union

explain select * from user;

在這里插入圖片描述

PRIMARY：查詢條件中包含有子查詢時最外層的表（u1）

explain select * from user u1 where u1.id =(select id from user u2 where u2.id=1);

在這里插入圖片描述

UNION（u2）：使用到union關聯時，union關聯的表
UNION RESULT（<union1,2>）：使用union時，最終的結果集表

explain select * from user u1 union select * from user u2;

在這里插入圖片描述

DEPENDENT UNION（u3,u4）：在子查詢中使用到union的第二個以上的表
DEPENDENT SUBQUERY（u2）：在子查詢中,使用到union的第一表

EXPLAIN SELECT
	* 
FROM
	user u1 
WHERE
	u1.id IN ( 
		SELECT id FROM user u2 WHERE u2.id = 1 
		UNION 
		SELECT id FROM user u3 WHERE u3.id = 2 
		UNION 
		SELECT id FROM user u4 WHERE u4.id = 2  
	);

在這里插入圖片描述

SUBQUERY（u2）：條件子查詢中的表

explain select * from user u1 where u1.id =(select id from user u2 where u2.id=1);

在這里插入圖片描述

SUBQUERY（u2,u3）：條件中的子查詢中的表（包括多重層級）

explain select * from user u1 
where u1.name =(
	select name from user u2 where u2.name=(select name from user u3 where u3.name='zs')
);

在這里插入圖片描述

DEPENDENT SUBQUERY（r1）: 子查詢中的條件依賴于外部的查詢（r1的條件是u1表中的資料）

explain select * from user u1 where u1.role_id=(select id from role r1 where u1.id=1);

在這里插入圖片描述

DERIVED（u1）：衍生表的from子表（該子表必須使用union關聯其他表）

explain select * from 
(select * from user u1 where u1.role_id=1 union select * from user u2 where u2.name='zs') temp;

在這里插入圖片描述

1.3 table 欄位

表示該SQL陳述句是作用于那張表的，取值為：表名、表別名、衍生表名等，

explain select * from user;

explain select * from user u1;

在這里插入圖片描述

1.4 partitions 欄位

涉及到磁區的表

準備資料

create table goods_partitions (
	id int auto_increment, 
	name varchar(12),
    primary key(id)
)
partition by range(id)(
		partition p0 values less than(10000),
		partition p1 values less than MAXVALUE
);

查看MySQL的物理存盤路徑：

show variables like '%dir%';

在這里插入圖片描述
查看物理存盤檔案，發現多了不同的檔案來存盤

查看查詢陳述句所使用到的磁區：

explain select * from goods_partitions;

在這里插入圖片描述

整個goods_partitions使用到了兩個磁區，

查詢id<1000的記錄（屬于p0磁區）

explain select * from goods_partitions where id<1000;

在這里插入圖片描述

1.5 type欄位

反應一段SQL陳述句性能指標的重要引數，可以通過此引數來判斷是否使用到了索引、是否全表掃描、是否范圍查詢等，

插入測驗資料：

insert into role values(1,'保潔');
insert into role values(2,'保安');
insert into role values(3,'廚師');
insert into user values(1,'zs',1);

null：代表不訪問任何表

explain select 1;

在這里插入圖片描述

system：表中只有一條記錄,并且此表為系統表(一般很少出現)

use mysql;			-- 切換到mysql資料庫
explain select * from db where host='localhost';

在這里插入圖片描述

const：通過唯一索引查詢到的資料,只查詢一次就查詢到了

explain select * from user where id=1;
explain select * from user where name='zs';

在這里插入圖片描述

分別根據name和id查詢，發現只有id的type為const，

給name欄位加上唯一索引（必須要是唯一索引，普通索引不行）：

create unique index user_name_unique on user(name);

在這里插入圖片描述
測驗完畢洗掉唯一索引：

drop index user_name_unique on user;

eq_ref：使用主鍵的關聯查詢，并且表中只有一條記錄與主表匹配；

explain select * from user u left join role r on u.role_id=r.id;

在這里插入圖片描述
代表有其他表參考了r表的主鍵，

如果主表有多條記錄與之匹配那么type將不再是eq_ref

首先查看兩個表的資料：

mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  1 | zs   |       1 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> select * from role;
+----+--------+
| id | name   |
+----+--------+
|  1 | 保潔   |
|  2 | 保安   |
|  3 | 廚師   |
+----+--------+
3 rows in set (0.00 sec)

mysql> explain select * from user u left join role r on u.role_id=r.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
|  1 | SIMPLE      | u     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.u.role_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql>

user表示是1條記錄，role是3條記錄

在user表中新增一條記錄，再次查看執行計劃：

mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  1 | zs   |       1 |
|  2 | ls   |       1 |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> select * from role;
+----+--------+
| id | name   |
+----+--------+
|  1 | 保潔   |
|  2 | 保安   |
|  3 | 廚師   |
+----+--------+
3 rows in set (0.00 sec)

mysql> explain select * from user u left join role r on u.role_id=r.id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               |
|  1 | SIMPLE      | r     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

發現type欄位變為了ALL，而不是原來的eq_ref，

ref：通過非唯一索引查詢到的資料

創建普通索引：

create index user_name_index on user(name);

查詢執行計劃：

explain select * from user where name='zs';

在這里插入圖片描述
測驗完畢洗掉索引：

drop index user_name_index on user;

range：使用索引的范圍查詢（普通列的范圍查詢不會是range）

我們執行如下兩句sql查看執行計劃：

explain select * from user u where u.id>20;				-- 使用索引列進行范圍查詢

explain select * from user u where u.role_id>20;		-- 使用普通列進行范圍查詢

在這里插入圖片描述
給role_id列添加索引，再次執行sql，查看執行計劃：

create index user_role_id_index on user(role_id);			

explain select * from user u where u.role_id>20;

在這里插入圖片描述

index：查詢的是索引列，遍歷了索引樹

explain select id from user;

在這里插入圖片描述

ALL：效率最低,遍歷全表

explain select * from user;

在這里插入圖片描述
查詢效率從高到底的取值為：

-- 所有的type欄位取值：
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

-- 一般情況下type欄位取值：
system > const > eq_ref > ref > range > index > ALL

1.6 possible_keys 欄位

查詢陳述句中，可能應用到的索引，并非實際使用到的索引，實際使用到的索引根據key欄位來反應，

例如：

-- 給name列加索引
create index idx_name on user(name); 

explain select * from user where user='1' or user='2';

在這里插入圖片描述

可能用到了idx_name索引，但實際沒有使用到，

測驗完畢洗掉索引：

drop index idx_name on user;

1.7 key 欄位

key欄位反應sql陳述句實際使用的索引，為null代表沒有使用索引

-- 根據id查詢
explain select * from user where id=1;

-- 根據普通列查詢
explain select * from user where name='zs';

-- 給name列加上索引
create index idx_name on user(name);

-- 根據索引查詢
explain select * from user where name='zs';

在這里插入圖片描述
測驗完畢洗掉索引：

drop index idx_name on user;

1.8 key_len 欄位

表示索引中使用的位元組數

explain select * from user where id=1;

在這里插入圖片描述

我的id型別為int型別，因此占用4個位元組，

我們把id型別改為bigint（Long），再次查看索引使用位元組數：

alter table user modify column id bigint;

explain select * from user where id=1;

在這里插入圖片描述
測驗完畢更改回來：

alter table user modify column id int;

1.9 ref 欄位

表示某表的某個欄位參考到了本表的索引欄位

mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  1 | zs   |       1 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> select * from role;
+----+--------+
| id | name   |
+----+--------+
|  1 | 保潔   |
|  2 | 保安   |
|  3 | 廚師   |
+----+--------+
3 rows in set (0.00 sec)

mysql> explain select * from user u left join role r on u.role_id=r.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
|  1 | SIMPLE      | u     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.u.role_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql>

在這里插入圖片描述

表示u表的role_id參考了本表（r表）的索引欄位（PRIMARY）

使用其他索引列關聯表：

create index role_name_index on role(name);		-- 給name列加索引，

explain select * from user u left join role r on u.name=r.name;   -- 使用name列來關聯

在這里插入圖片描述

表示u表的name欄位參考了本表（r表）的索引欄位（role_name_index

測驗完畢洗掉索引：

drop index role_name_index on role;

1.10 rows 欄位

根據表統計資訊及選用情況，大致估算出找到所需的記錄或所需讀取的行數，

explain select * from user;

explain select * from role;

在這里插入圖片描述

user表中有1條記錄，role表中有3條記錄，

1.11 filtered 欄位

回傳結果與實際結果的差值占總記錄數的百分比

insert into user values(2,'ls','4');

explain select * from user u inner join role r on u.role_id=r.id;

在這里插入圖片描述

r表實際記錄3條，上述sql陳述句關聯查詢出來的結果只能得出一條結果集，因此命中率為33.33%，

查詢此SQL陳述句查詢的記錄數，

select * from user u inner join role r on u.role_id=r.id;

在這里插入圖片描述

只有一條記錄

1.12 extra 欄位

顯示其他擴展資訊

Using filesort：排序時無法使用到索引時，就會出現這個，常見于order by和group by陳述句中，效率低

explain select name from user order by name;

在這里插入圖片描述

Using temporary：表示SQL陳述句的操作使用到了臨時表，

explain select name from user group by name;

在這里插入圖片描述

Using index：代表使用到了索引，效率高

create index user_name_index on user(name);			-- 創建索引

explain select name from user order by name;

在這里插入圖片描述
測驗完畢洗掉索引：

drop index user_name_index on user;

Using where：掃描全表，通常是查詢條件中不是索引欄位，

explain select * from user where name='zs';

在這里插入圖片描述

NULL：沒有用到額外的附加條件

explain select * from user where id=1;

在這里插入圖片描述

性能對比：

Using index > NULL > Using where >= Using temporary > Using filesort

二、總結

MySQL執行計劃的內容是SQL調優時的一個重要依據，我們想要優化SQL陳述句就必須得先掌握執行計劃，這一篇主要是理論知識，也沒什么好總結的，

總結一句話吧，想做MySQL調優，執行計劃是必須要掌握的，切記切記！

好了，本篇就說到這里了，看完覺得有幫助的童鞋記得點贊！點贊！點贊！（重要的事情說三遍）

轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/218222.html

標籤：其他

上一篇：Mysql基礎操作

下一篇：從采樣到結果報告，未來的核酸檢測要實作「無人化」

MySQL執行計劃（MySQL調優的重要利器）

文章目錄

看完本篇文章你能學到什么？

一、MySQL執行計劃

1.1 id欄位

1.2 select_type 欄位

1.3 table 欄位

1.4 partitions 欄位

1.5 type欄位

1.6 possible_keys 欄位

1.7 key 欄位

1.8 key_len 欄位

1.9 ref 欄位

1.10 rows 欄位

1.11 filtered 欄位

1.12 extra 欄位

二、總結

好了，本篇就說到這里了，看完覺得有幫助的童鞋記得點贊！點贊！點贊！（重要的事情說三遍）