当前位置：首页 > news >正文

mysql 执行计划 type详解

news 来源：原创 2024/7/5 4:17:04

文章目录

- - - 前言
    - all
    - index
    - range
    - index_subquery
    - unique_subquery
    - index_merge
    - ref_or_null
    - ref
    - eq_ref
    - const
    - system

前言

当前mysql版本为8.0.23

# 测试数据和结构如下

# 创建user表
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 '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_user_deleted_at` (`deleted_at`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

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

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

# log表插入测试数据
INSERT INTO `log` (`id`, `user_name`) VALUES (2,'rui'),(1,'xie');

执行效率 system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

all

全表扫描

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 |    6 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

index

全索引扫描，扫描全部索引来获取数据

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

range

扫描部分索引来获取数据。
注意数据要稍微多一点，否则mysql可能认为还不如走全索引呢，导致出现的type 是 index

mysql> desc select id from user where id between 1 and 2;
+----+-------------+-------+------------+-------+-----------------------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys               | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+-----------------------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY,idx_user_deleted_at | PRIMARY | 8       | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-----------------------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

index_subquery

使用索引关联字查询

mysql> explain select * from user where name not in (select name from user);
+----+--------------------+-------+------------+----------------+---------------+------------+---------+------+------+----------+-------------------------------------------------+
| id | select_type        | table | partitions | type           | possible_keys | key        | key_len | ref  | rows | filtered | Extra                                           |
+----+--------------------+-------+------------+----------------+---------------+------------+---------+------+------+----------+-------------------------------------------------+
|  1 | PRIMARY            | user  | NULL       | ALL            | NULL          | NULL       | NULL    | NULL |    6 |   100.00 | Using where                                     |
|  2 | DEPENDENT SUBQUERY | user  | NULL       | index_subquery | index_name    | index_name | 63      | func |    2 |   100.00 | Using where; Using index; Full scan on NULL key |
+----+--------------------+-------+------------+----------------+---------------+------------+---------+------+------+----------+-------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

unique_subquery

和 index_subquery 类似，只不过 unique_subquery 提供的是唯一索引，index_subquery 提供的是普通索引

mysql> explain select * from user where name not in (select id from user);
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------------------------------------------+
| id | select_type        | table | partitions | type            | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                           |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------------------------------------------+
|  1 | PRIMARY            | user  | NULL       | ALL             | NULL          | NULL    | NULL    | NULL |    6 |   100.00 | Using where                                     |
|  2 | DEPENDENT SUBQUERY | user  | NULL       | unique_subquery | PRIMARY       | PRIMARY | 8       | func |    1 |   100.00 | Using where; Using index; Full scan on NULL key |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

index_merge

查询中使用到多个索引组合使用

explain select * from user where id between 1 and 3  and name = "xie";
+----+-------------+-------+------------+-------------+--------------------+--------------------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                                            |
+----+-------------+-------+------------+-------------+--------------------+--------------------+---------+------+------+----------+--------------------------------------------------+
|  1 | SIMPLE      | user  | NULL       | index_merge | PRIMARY,index_name | index_name,PRIMARY | 71,8    | NULL |    1 |   100.00 | Using intersect(index_name,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+--------------------+--------------------+---------+------+------+----------+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

ref_or_null

允许值可以为null(可以是null也可以是其它值)

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

ref

使用非唯一性索引进行查询

mysql> explain select * 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 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

eq_ref

使用唯一性索引进行查找

mysql> explain select * from user where id in (select id from log);
+----+-------------+-------+------------+--------+---------------+-----------------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key             | key_len | ref          | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+-----------------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | log   | NULL       | index  | PRIMARY       | index_user_name | 603     | NULL         |    2 |   100.00 | Using index |
|  1 | SIMPLE      | user  | NULL       | eq_ref | PRIMARY       | PRIMARY         | 8       | proxy.log.id |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+-----------------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

const

这个表最多只有一个匹配行

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