mysql 执行计划 extra 详解
文章目录
- 前言
- using where
- using index
- using filesort
- no matching row in const table
- Using temporary
- Using join buffer
前言
当前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)
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！