mysql 执行计划 extra 详解

最新推荐文章于 2023-12-25 19:34:00 发布

西直门三太子

最新推荐文章于 2023-12-25 19:34:00 发布

阅读量515

点赞数 1

分类专栏： # Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_29744347/article/details/126841245

版权

Mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

文章目录

前言

当前mysql版本8.0.23
测试数据如下


CREATE TABLE `log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(200) DEFAULT NULL,
  `number` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_user_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `log` (`id`, `user_name`, `number`) VALUES (1,'xie','1001');
INSERT INTO `log` (`id`, `user_name`, `number`) VALUES (2,'rui','1002');

CREATE TABLE `user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `name` varchar(20) DEFAULT 'xie' COMMENT '名称',
  `age` int unsigned DEFAULT '18' COMMENT '年龄',
  `number` varchar(200) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_age` (`age`),
  KEY `idx_user_deleted_at` (`deleted_at`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`, `created_at`, `updated_at`, `deleted_at`, `name`, `age`, `number`) VALUES (1,'2022-09-05 19:27:09.468','2022-09-05 19:27:09.468',NULL,'xie',27,'1001'),(2,'2022-09-05 19:27:09.468','2022-09-05 19:27:09.468',NULL,'rui',28,'1002'),(3,'2022-09-05 19:27:09.468','2022-09-05 19:27:09.468',NULL,'rui',29,'1001'),(4,'2022-09-12 13:41:05.000','2022-09-12 13:41:08.000',NULL,'m',22,'1003'),(5,'2022-09-12 13:41:17.000','2022-09-12 13:41:20.000',NULL,'g',21,'1002'),(6,'2022-09-12 13:41:30.000','2022-09-12 13:41:32.000',NULL,'l',20,'1003');

using where

name上是有索引的，但是由于查询的不是字符串，导致索引不能用，使用全表扫描 + where 条件过滤数据

mysql> explain select * from user where name = 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | index_name    | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

using index

覆盖索引，避免回表

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

using filesort

无法使用索引排序，只能使用排序算法进行排序，会产生额外的消耗

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

no matching row in const table

在唯一性索引上无法匹配到数据

mysql> explain select * from user where age = 33;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

Using temporary

使用了临时表


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

Using join buffer

由于 user.number 和 log.number 上没有索引，故使用了连接缓存

mysql> explain select * from user left join log on user.number = log.number;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | NULL                                       |
|  1 | SIMPLE      | log   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)