搜尋

首頁 >資料庫 >mysql教程 >我所理解的MySQL之三：執行計劃

我所理解的MySQL之三：執行計劃

coldplay.xixi
coldplay.xixi轉載
2020-10-22 15:37:591773瀏覽

今天MySQL資料庫欄位介紹相關執行計畫。

我所理解的MySQL之三：執行計劃

MySQL 系列的第三篇博客，主要內容是MySQL 中關於Explain 執行計劃的分析，假如你已經知道如何分析執行計劃，那麼對於SQL調優也就信手拈來了。

縱觀眾多一二線大廠招募時的職位要求，但凡設計資料庫的必定會要求有SQL 調優的經驗，這幾乎已經成為與Spring 不相上下的「八股文」類面試題。

要想進行SQL 調優，首先需要知道SQL 的執行情況，最直觀的感覺當然是SQL 語句執行的時間，然而除此之外，我們還可以透過執行計畫來分析SQL 語句的執行情況，從而進行調優。

1. Explain 簡述

Explain 語句可以查看MySQL 是如何執行這條SQL 語句的，包括使用索引情況、掃描行數等，這些資訊對於SQL 調優來說十分重要，所以首先得看懂執行計劃。 

mysql> explain select * from user where name='one';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | user  | NULL       | ref  | a             | a    | 13      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)复制代码

以上是一條簡單查詢語句的執行計劃，這張表一共有12個字段，分別代表不同的意義，下面一一敘述。

  • id: 表示 SQL 執行的順序，值越大，優先權越高。若值相同，執行順序由最佳化器決定。
  • select_type: 表示select 查詢語句的型別
  • table: SQL 語句查詢的表名（或該表的別名），也可能是臨時表等不存在的表
  • partitions: 查詢語句涉及的分區資訊
  • type: 關聯類型（存取類型），決定了MySQL 是如何查找表中行的。效能從最差到最優依序是ALL, index, range, index_merge, ref, eq_ref, const, system, NULL
  • possible_keys: 展示了查詢語句可以使用的所有索引
  • key: 展示了優化器決定採用的索引名稱
  • #key_len: 展示了MySQL 使用索引長度的位元組數
  • ref: 在key 列記錄的索引中尋找值所使用的列或常數
  • rows: 掃描行數的估價
  • filtered: 最終滿足查詢語句行數佔儲存引擎傳回總行數的百分比
  • ##Extra: 其他執行資訊
以上只是對執行計劃表各個欄位的名詞解釋，接下來我會透過實際的例子來幫助大家（我自己）更好地理解其中

select_type, type, key_len, rows, Extra 這些重要的欄位。

2. Explain 詳述

2.1 範例表結構

#首先介紹本文中將以的範例表表結構以及資料行：

CREATE TABLE `user`  (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',  `name` varchar(36) DEFAULT NULL COMMENT '姓名',  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',  `email` varchar(36) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`) USING BTREE,  INDEX `idx_age_name`(`age`, `name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;复制代码
#透過函數向表中插入1000000條測試資料。 

CREATE DEFINER=`root`@`localhost` PROCEDURE `idata`()begin 
  declare i int; 
  set i=1; 
  while(i<=1000000)do 
    insert into user(id,name,age,email) values(i, CONCAT(&#39;name&#39;,i), i % 50, concat(i,&#39;name@email.cn&#39;));    set i=i+1; 
  end while;end复制代码

2.2 select_type in Explain

執行計劃中

select_type 欄位表示select 查詢語句的類型，常見類型有：

  • #SIMPLE: 簡單的查詢語句，不包含子查詢與關聯，如：
    • mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码
    2.2.1 PRIMARY

    若查詢語句中包含任何複雜的子部分，那麼最外層部分會被標記為

    PRIMARY，如：

    mysql> explain select * from user where id=(select id from user where id=1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+|  1 | PRIMARY     | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)复制代码
    在這條SQL 語句的執行計劃中，第一條執行的SQL，即

    select * from yser where id = (...) 就被標記為PRIMARY

    #2.2.2 SUBQUERY

    包含在select 或where 內容中的子查詢會被標記為

    SUBQUERY，如上一條範例SQL 的執行計劃中第二條語句，即select id from user where id=1 的select_type 就被標記為了SUBQUERY。

    2.2.3 DERIVED

    包含在FROM 關鍵字後的子查詢（即將子查詢的結果視為「表」），被視為「表」的子查詢會被標記為

    DERIVED，其結果將被存放在臨時表中，如：

    mysql> explain select * from (select id,name,count(*) from user where id=1) as user_1 where id=1;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | user       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)复制代码
    從執行計劃中可以看到，第二條執行的SQL，即

    select id, name,count(*) from user where id=1 的查詢類型是DERIVED。

    select_type 一共有12中查詢類型，具體釋義可以看官方文件-explain_select_type

    2.3 type in Explain

    #type 欄位是執行計畫中衡量SQL 非常重要的依據，它展示了SQL 語句的關聯類型（存取類型），決定了MySQL 是如何尋找表中所行的。

    type 字段的值性能从最差到最优依次是 ALL, index, range, index_merge, ref, eq_ref, const, system。

    为了能更好地理解各个类型的含义，我对上述每一种类型都举出了相应的示例。

    并未全部列出，完整的解释可以看官方文档-EXPLAIN Join Types

    2.3.1 ALL

    ALL 表示全表扫描，意味着存储引擎查找记录时未走索引，所以它是性能最差的一种访问类型，如

    mysql> explain select * from user where age+2=20;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1002301 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec)复制代码

    可以看到 rows 行的值为1002301，即扫描了全表的所有数据（扫描行数的值实际为估算），如果在生产环境有这样的 SQL，绝对是要优化的。

    我们知道在 where 查询条件中，不应该对查询字段使用函数或表达式（应该写在等号不等号右侧），不了解此内容的可以看看我的上一篇博客 —— 我所理解的MySQL(二)索引。

    这条查询语句在优化后应该是: select * from user where age=18，去掉等号左侧的表达式，优化后的执行计划如下:

    mysql> explain select * from user where age=18;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+|  1 | SIMPLE      | user  | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 39360 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码

    2.3.2 index

    index 表示全索引树扫描，由于扫描的是索引树，所以比 ALL 形式的全表扫描性能要好。

    同时，由于索引树本身就是有序的，可以避免排序。

    mysql> explain select id,age from user where name='name1';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+|  1 | SIMPLE      | user  | NULL       | index | NULL          | idx_age_name | 116     | NULL | 1002301 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)复制代码

    示例查询语句如上所述，当查询条件存在于联合索引 idx_age_name 中，但又无法直接使用该索引（由于最左前缀原则），同时查询列 id,age 也存在于联合索引中，无须通过回表来获取时，执行计划中的访问类型 type 列就会是 index。

    2.3.3 range

    range 表示范围扫描，准确的说是基于索引树的范围扫描，扫描的是部分索引树，所以性能比 index 稍好。

    需要注意的是，若使用 in 或者 or 时，也可以使用范围扫描。

    mysql> explain select * from user where age>18 and age<20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+|  1 | SIMPLE      | user  | NULL       | range | idx_age_name  | idx_age_name | 5       | NULL | 36690 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.01 sec)

mysql> explain select * from user where age=18 or age=20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+|  1 | SIMPLE      | user  | NULL       | range | idx_age_name  | idx_age_name | 5       | NULL | 78720 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)复制代码

    2.3.4 index_merge

    index_merge 即索引合并，它表示在查询时 MySQL 会使用多个索引。

    MySQL 在 where 语句中存在多个查询条件，并且其中存在多个字段可以分别使用到多个不同的索引，在这种情况下 MySQL 可以对多个索引树同时进行扫描，最后将它们的结果进行合并，如：

    mysql> explain select * from user where id=1 or age=18;
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+| id | select_type | table | partitions | type        | possible_keys        | key                  | key_len | ref  | rows  | filtered | Extra                                               |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+|  1 | SIMPLE      | user  | NULL       | index_merge | PRIMARY,idx_age_name | idx_age_name,PRIMARY | 5,4     | NULL | 39361 |   100.00 | Using sort_union(idx_age_name,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+1 row in set, 1 warning (0.00 sec)复制代码

    上面这条查询语句中的 id=1 和 age=18 分别使用到了 PRIMARY 主键索引和 idx_age_name 联合索引，最后再将满足这两个条件的记录进行合并。

    2.3.5 ref

    ref 表示索引访问（索引查找），这种访问类型会出现在查询条件中以非聚簇索引列的常量值进行查询的情况。

    比如在介绍全表扫描中优化后 SQL 的访问类型就是 ref。

    2.3.6 eq_ref

    eq_ref 这种访问类型会出现在连接查询时，通过聚簇索引进行连接的情况，此类型最多只返回一条符合条件的记录。若表的聚簇索引为联合索引，所有的索引列必须是等值查询，如：

    mysql> explain select * from user user1 inner join user user2 where user1.id=user2.id limit 10;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows    | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+|  1 | SIMPLE      | user1 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                | 1002301 |   100.00 | NULL  |
|  1 | SIMPLE      | user2 | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | all_in_one.user1.id |       1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+2 rows in set, 1 warning (0.00 sec)复制代码

    2.3.7 const

    const 这种访问类型会出现在通过聚簇索引进行常量等值查询的情况，如：

    mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码

    2.4 key_len in Explain

    在上一篇博客 —— 我所理解的MySQL(二)索引 中 5.2 部分字段匹配 中已经提到过关于索引长度的计算方式，这里再来总结一下。

    2.4.1 字符类型

    字符类型的字段若作为索引列，它的索引长度 = 字段定义长度 字符长度 + 是否默认NULL + 是否是变长字段*，其中：

    • 字段定义长度 就是定义表结构时跟在字段类型后括号中的数字
    • 字符长度 是常数，utf8=3, gbk=2, latin1=1
    • 是否默认NULL 也是常数，若字段默认值为 NULL，该值为1，因为 NULL 需要额外的一个字节来表示；否则该值为0
    • 是否是变长字段 也是常数，若该字段为变长字段，该值为2；否则该值为0

    所谓的变长字段就是 varchar，它所占用的就是字段实际内容的长度而非定义字段时的长度。而定长字段，也就是 char 类型，它所占用的空间就是自定字段时的长度，若超过会被截取。

    举个例子，为上述实例表中添加一个字符类型字段的索引。

    alter table user add index idx_name(`name`);复制代码

    然后通过 name 字段去做查询，查看执行计划。

    mysql> explain select * from user where name='name1';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+|  1 | SIMPLE      | user  | NULL       | ref  | idx_name      | idx_name | 111     | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)复制代码

    可以看到，执行计划中 key_len 一列的值为 111。

    根据上述索引长度的计算公式，name 列字段定义长度为36，字符集类型为默认的 utf8，该字段默认允许 NULL，同时该字段是可变长字段 varchar。

    所以 idx_name 索引的索引长度=36*3+1+2=111，恰如执行计划中显示的值。

    2.4.2 其他定长类型

    对于定长类型的字段，其索引长度与它的数据类型长度是一致的。

    数据类型 长度
    int 4
    bigint 8
    date 3
    datetime 8
    timestamp 4
    float 4
    double 8

    需要注意的是，若该字段允许默认值为 NULL，与字符类型一样，其索引长度也需要加上1。

    mysql> explain select * from user where age=1;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+|  1 | SIMPLE      | user  | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 39366 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码

    如上面这个示例（本示例中索引只用到了 age 字段），age 字段为 int 类型，其索引长度本应为 4，但由于 age 字段默认允许为 NULL，所以它的索引长度就变成了5。

    2.5 rows in Explain

    扫描行数在执行计划中其实是一个估值，MySQL 会选择 N 个不同的索引数据页，计算平均值得到单页索引基数，然后再乘以索引页面数，就得到了扫描行数的估值。

    扫描行数就是优化器考量索引执行效率的因素之一，一般而言扫描行数越少，执行效率越高。

    2.6 Extra in Explain

    执行计划中 Extra 字段的常见类型有：

    • Using index: 使用了覆盖索引，以避免回表
    • Using index condition: 使用了索引下推，具体可以看我的上一篇博客 —— 我所理解的MySQL(二)索引
    • Using where: 表示MySQL 会通过 where 条件过滤记录
      • 全表扫描：where 中有该表字段作为搜索条件
      • 扫描索引树：where 中包含索引字段之外的其他字段作为搜索条件
    • Using temporary: MySQL 在对查询结果排序时会使用临时表
    • Using filesort: 对结果进行外部索引排序（文件排序），排序不走索引
      • 数据较少时在内存中排序，数据较多时在磁盘中排序
      • 尽量避免该信息出现在执行计划中

    相关免费学习推荐：mysql数据库(视频)

    以上是我所理解的MySQL之三：執行計劃的詳細內容。更多資訊請關注PHP中文網其他相關文章！

sql mysql spring 数据类型 NULL 常量 count select const char int using table 数据库
陳述：
本文轉載於：juejin.im。如有侵權，請聯絡admin@php.cn刪除
上一篇：資料庫管理系統是什麼下一篇：資料庫管理系統是什麼

相關文章

看更多