sql执行分析

约 2844 字大约 9 分钟

2025-05-18

测试数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.5     |
+-----------+
1 row in set (0.01 sec)

测试数据表结构

以一个常见的用户表为例, 表结构如下:

CREATE DATABASE `blog_test`;
USE `blog_test`;
CREATE TABLE `user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(128) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户名称',
  `mail` varchar(128) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户邮箱',
  `role_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '用户角色ID',
  `status` int unsigned COLLATE utf8mb4_general_ci NOT NULL DEFAULT '1' COMMENT '状态 0 - 待启用 1 - 可用中 2 - 禁用 3 - 下线 4 - 删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mail` (`mail`),
  KEY `name` (`name`),
  KEY `role_id` (`role_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户信息表';
CREATE TABLE `article` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
  `title` varchar(128) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '文章标题',
  `content` varchar(1024) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '文章内容',
  `status` int unsigned COLLATE utf8mb4_general_ci NOT NULL DEFAULT '1' COMMENT '状态 0 - 待启用 1 - 可用中 2 - 禁用 3 - 下线 4 - 删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mail` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户信文章息表';
CREATE TABLE `article_comment` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
  `article_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '文章ID',
  `title` varchar(128) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '评论标题',
  `content` varchar(1024) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '评论内容',
  `status` int unsigned COLLATE utf8mb4_general_ci NOT NULL DEFAULT '1' COMMENT '状态 0 - 待启用 1 - 可用中 2 - 禁用 3 - 下线 4 - 删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mail` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户信文章息表';

通过以下shell数据, 向数据表中批量生成数据:

#!/bin/bash
for i in {1..1000000}; do
  name="user_$i"
  mail="emain_$i@qq.com"
  role_id=$i
  status=`expr $i % 5`
  mysql -h127.0.0.1 -uroot -p123456 blog_test -e "INSERT INTO user (name, mail, role_id, status) VALUES ('$name', '$mail', $role_id, $status)"
done

sql分析

mysql 提供了 EXPLAIN 命令, 可以用来分析 sql 的执行计划, 从而了解 sql 的执行情况, 从而优化 sql 的执行效率, 从而提高系统的性能. explain执行结果信息如下:

mysql> explain select * from user where role_id = "6787";
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | ref  | role_id       | role_id | 8       | const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

id: select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
select_type: 表示查询中每个select子句的类型
table: 正在访问哪个表
partitions: 匹配的分区
type: 访问的类型
possible_keys: 表示查询中可能使用的索引, 可能为多个, 但是索引不一定会使用到
key: 实际使用的索引, 如果没有索引, 则显示为null
key_len: 使用的索引长度, 如果没有索引, 则显示为null
ref: 显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
rows: 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
filtered: 表示返回结果的行数占需读取行数的百分比
Extra: 执行情况的描述和说明

id与table字段

id与table需要放到一起说, 这两个属性综合到一起, 才能完整得出sql执行顺序

简单查询

mysql> explain select * from user where role_id = "6787";
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | ref  | role_id       | role_id | 8       | const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

简单查询没有过多需要说的, 就是单表查询相关信息, 不存在过多问题

JOIN查询(JOIN语句)

mysql> EXPLAIN
SELECT u.id,u.name,u.mail,a.id,a.title, ac.id,ac.title
FROM user u 
JOIN article a 
ON u.id = a.user_id
JOIN article_comment ac 
ON a.id = ac.user_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
|  1 | SIMPLE      | ac    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                 |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | blog_test.ac.user_id |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | blog_test.a.user_id  |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
3 rows in set, 1 warning (0.01 sec)

我们看到id全是1，那就说明光看id这个值是看不出来每个表的读取顺序的，那我们就来看table这一行，它的 读取顺序是自上向下 的，所以，这三个表的读取顺序应当是：ac - a - u。

子查询(IN)

mysql> EXPLAIN
SELECT * 
FROM article_comment AS ac 
WHERE ac.article_id
IN (
    SELECT a.id  
    FROM article AS a 
    WHERE a.user_id 
    IN (
        SELECT 
            u.`id` 
        FROM user AS u
        WHERE  u.`name` = "李白"
    )
);
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | ac    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                    |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | blog_test.ac.article_id |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY,name  | PRIMARY | 8       | blog_test.a.user_id     |    1 |     5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

其数据表查询顺序结果与JOIN语句一致

子查询(=)

mysql> EXPLAIN
SELECT * 
FROM article_comment AS ac 
WHERE ac.article_id
= (
    SELECT a.id  
    FROM article AS a 
    WHERE a.user_id 
    = (
        SELECT 
            u.`id` 
        FROM user AS u
        WHERE  u.`name` = "李白"
    )
);
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | ac    | NULL       | ALL  | NULL          | NULL | NULL    | NULL  |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL  |    1 |   100.00 | Using where |
|  3 | SUBQUERY    | u     | NULL       | ref  | name          | name | 514     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

从sql语句中, 我们可以知道, 查询顺序为 u -> a -> ac, 说明当ID不同时, ID值越大的表越在前面查询

子查询(IN/=)

mysql> EXPLAIN
SELECT * 
FROM article_comment AS ac 
WHERE ac.article_id
= (
    SELECT a.id  
    FROM article AS a 
    WHERE a.user_id 
    IN (
        SELECT 
            u.`id` 
        FROM user AS u
        WHERE  u.`name` = "李白"
    )
);
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
|  1 | PRIMARY     | ac    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | u     | NULL       | eq_ref | PRIMARY,name  | PRIMARY | 8       | blog_test.a.user_id |    1 |     5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

sql表查询顺序为 a -> u -> ac

总结

当id相同时, 则按照从上至下的顺序执行
当id不同时, id值越大的表越在前面查询

select_type说明

SIMPLE: 简单的select查询, 不使用子查询或union
PRIMARY: 查询中若包含任何复杂的子部分, 最外层的select被标记为PRIMARY
SUBQUERY: 在select或where列表中包含了子查询
DERIVED: 在from列表中包含的子查询被标记为DERIVED(衍生), MySQL会递归执行这些子查询, 从中可以派生出临时表
UNION:
- 若第二个select出现在union之后，则被标记为UNION；
- 若union包含在from子句的子查询中，外层select将被标记为：DERIVED
UNION RESULT(去重结果): 从union表获取结果的select, 注意: UNION RESULT 出现在UNION语句中, 如果UNION ALL, 则不会返回 UNION RESULT, 因为 UNION ALL 不会去重

partitions说明

该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

type字段

type字段一共有10个值, 分别为:

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

NULL

该值表示MySQL在优化过程中分解语句, 执行时甚至不用访问表或索引, 例如从一个索引列里选取最小值, 可以通过单独索引查找完成

system

该值表示表只有一行记录(等于系统表), 这是const类型的特例, 平时不会出现, 这个也可以忽略不计

const

表示通过索引一次就找到了, const用于比较primary key 或 unique索引, 因为只匹配一行数据, 所以很快, 如将主键置于where列表中, MySQL就能将该查询转换为一个常量简单来说, const就是根据主键或者唯一索引进行查询

eq_ref

用于联表查询的情况，按联表的主键或唯一键联合查询。

ref

非唯一性索引扫描, 返回匹配某个单独值的所有行, 本质上也是一种索引访问, 它返回所有匹配某个单独值的行, 但它可能会找到多个符合条件的行, 所以它应该属于查找和扫描的混合体

ref_or_null

该值类似ref, 区别就在使用的索引列上有null值

index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。

range

索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

index

ndex只遍历索引树，通常比All快。因为，索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。

ALL

如果一个查询的type是All,并且表的数据量很大，那么请解决它！！ 这是全表扫描

possible_keys 说明

显示可能应用在这张表中的索引, 一个或多个, 但不一定实际使用

key说明

实际使用的索引，如果为null,则没有使用索引，否则会显示你使用了哪些索引，查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。

ref说明

显示哪些列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

rows字段和Filter字段

rows是根据表的统计信息和索引的选用情况，优化器大概帮你估算出你执行这行函数所需要查询的行数。

Filter是查询的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。

extra属性

这一字段包含不适合在其他列显示，但是也非常重要的额外信息。

Using filesort: 表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序，外部排序就不断的在磁盘和内存中交换数据，这样就摆脱不了很多次磁盘IO，以至于SQL执行的效率很低。反之呢？由于索引的底层是B+Tree实现的，他的叶子节点本来就是有序的，这样的查询能不爽吗？

# status 字段没有索引, 使用 Using filesort
mysql> explain select * from article order by status;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

Using tempporary: 表示在对MySQL查询结果进行排序时，使用了 临时表 ,这样的查询效率是 比外部排序更低 的，常见于order by和group by。

# 对status字段进行分组, 使用 Using temporary
mysql> explain select status from article group by status;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

Using index: 表示查询使用了索引, 是理想情况. 但是是否使用的理想索引，具体得看 key 属性
Using where: 使用了where但是好像没啥用。
Using join buffer: 表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
impossible where: 筛选条件没能筛选出任何东西
distinct: 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

版权所有

版权归属：白茶清欢

许可证：署名 4.0 国际 (CC-BY-4.0)