1.数据库准备
1.1 建表
CREATE TABLE test (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
a TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'a',
b BIGINT(20) NOT NULL COMMENT 'b',
c BIGINT(20) NOT NULL COMMENT 'c',
time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'time',
PRIMARY KEY (id),
UNIQUE KEY test (a,b,c)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='测试表'
1.2 模拟数据
INSERT INTO test (a, b, c)
VALUES
('1', '1', '1'), ('1', '1', '2'), ('1', '1', '3'), ('1', '1', '4'),
('1', '2', '1'), ('1', '2', '2'), ('1', '2', '3'), ('1', '2', '4'),
('1', '3', '1'), ('1', '3', '2'), ('1', '3', '3'), ('1', '3', '4'),
('1', '4', '1'), ('1', '4', '2'), ('1', '4', '3'), ('1', '4', '4'),
('2', '1', '1'), ('2', '1', '2'), ('2', '1', '3'), ('2', '1', '4'),
('2', '2', '1'), ('2', '2', '2'), ('2', '2', '3'), ('2', '2', '4'),
('2', '3', '1'), ('2', '3', '2'), ('2', '3', '3'), ('2', '3', '4'),
('2', '4', '1'), ('2', '4', '2'), ('2', '4', '3'), ('2', '4', '4');
2. Explain执行计划
2.1 示例
image-20210204175210175.png
2.2 列说明
| 列名 | 说明 |
|---|---|
| id | 序号 |
| select_type | 查询类型 |
| table | 表名 |
| partitions | 匹配的分区 |
| type | 类型 |
| possible_keys | 可能使用的索引 |
| key_len | 索引字段的长度 |
| ref | 索引类型 |
| rows | 预估查询行数 |
| filtered | 按条件过滤的行百分比 |
| Extra | 执行情况的描述和说明 |
2.3 列明细值
2.3.1 type
| 值 | 说明 |
|---|---|
| all | 全表扫描，在大数据量的时候效率极低 |
| index | 索引全扫描。Mysql遍历整个索引来查询匹配的行。需要结合extra列进行进一步分析。 |
| range | 索引范围扫描。 常见于：<、<=、>、>=、between |
| index_subquery | 利用索引关联子查询，不再全表扫描子查询中的表。 常见于in形式子查询或者in常数列表。 |
| unique_subquery | 类似index_subquery，只是索引用到的是唯一索引。 |
| index_merge | 查询过程中需要多个索引组合使用，通常出现在有or的关键字的sql中。 对多个索引分别进行条件的查询，最后对查询结果进行合并交集运算 |
| ref | 使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录 |
| ref_or_null | 类似ref，区别是会额外的搜索包含null的记录，会进行一些优化 |
| eq_ref | 类似ref，区别是对于唯一索引的每个索引键值，表中只有一条记录匹配； 例如多表连接中使用primary key或者unique index作为关联条件 |
| const | 查询条件是主键或者非NULL的UNIQUE索引，结果只有一条，优化过程中查询列值会转成常量 |
| system | 表中只有一行数据 |
| NULL | 无需访问表至直接获取结果，如：SELECT 1
|
备注：加粗为常见的类型
all
EXPLAIN SELECT * FROM test;
image.png
EXPLAIN SELECT * FROM test WHERE b=2
image.png
index
EXPLAIN SELECT a FROM test
image.png
EXPLAIN SELECT a FROM test WHERE b=2
image.png
range
EXPLAIN SELECT * FROM test WHERE id > 1
image.png
EXPLAIN SELECT a FROM test WHERE a > 1
image.png
ref
EXPLAIN SELECT a FROM test WHERE a = 1
image.png
eq_ref
EXPLAIN SELECT * FROM test t1, test t2 WHERE t1.id=t2.id
image.png
const
EXPLAIN SELECT * FROM test WHERE id = 1
image.png
2.3.2 Extra
| 值 | 说明 |
|---|---|
| Using index | SQL语句使用了覆盖索引，查询效率最高 |
| Using where | 搜索到记录后进行了后过滤，如果查询未使用索引，提醒mysql使用了where过滤结果集。 |
| Using temporary | 使用了临时表保存中间结果。常见于order by 和 group by。 |
| Using filesort | 无法使用索引直接完成排序，用缓冲空间来进行排序。 例如order by 的字段为非索引 |
| Using join buffer | 获取连接条件时没有用到索引，且需要连接缓冲区来存储中间结果。 |
| Impossible where | where子句值为false，不能查询到任何数据。 如WHERE id = 1 and id = 2； |
| Using index condition | 二级索引使用了like查找 |
