explain 用于MySQL中查询性能分析的工具, 可判断是否使用索引, 扫描记录数等
使用语法
explain sql语句;
返回信息
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
id 查询序列号, 也代表了查询顺序 id越大越先执行
select_type 查询类型
SIMPLE 简单查询, 不包含子查询或者UNION
mysql> explain select * from t_second_kill;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t_second_kill | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
PRIMARY 查询中包含子部分, 最外层查询为PRIMARY
SUBQUERY 在SELECT或WHERE列表中包含了子查询
mysql> explain select * from t_second_kill where id = (select id from t_goods where id = 10);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
2 rows in set, 1 warning (0.00 sec)
DERIVED 在FROM列表中包含的子查询, MySQL把结果放在临时表中
UNION 若第二个SELECT出现在UNION之后, 则被标记为UNION; 若UNION包含在FROM子句的子查询中, 外层SELECT将被标记为DERIVED
UNION RESULT 从UNION表获取结果的SELECT
mysql> explain select * from (select * from t_second_kill where id = 15 UNION select * from t_second_kill where id = 19) tmp;
+----+--------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
| 2 | DERIVED | t_second_kill | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| 3 | UNION | t_second_kill | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)
table 查询的表名称
partitions 匹配到的分区
type 连接类型
system > const > eq_ref > ref > range > index > all 性能依次降低 一般要到range, 最好到ref
system 一般不会出现
const eq_ref 在主键或唯一索引键查询时会出现, 锁定一条记录, 速度快
ref 一般索引 组合索引做匹配
range 索引范围查询 like查询
index 使用索引全表扫描 只查询索引
all 全表磁盘扫描
CREATE TABLE `t_second_kill` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`goods_id` bigint(20) NOT NULL COMMENT '商品ID',
`goods_name` varchar(255) NOT NULL COMMENT '商品名称',
`store` int(11) NOT NULL COMMENT '秒杀库存',
`price` decimal(10,2) NOT NULL COMMENT '秒杀价格',
`order_count` int(11) NOT NULL DEFAULT '0' COMMENT '已成单数量',
`start_time` datetime NOT NULL COMMENT '开始时间',
`end_time` datetime NOT NULL COMMENT '结束时间',
`notes` text NOT NULL COMMENT '备注',
`version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COMMENT='秒杀表';
const
UNIQUE KEY `goods_id` (`goods_id`) USING BTREE
mysql> explain select * from t_second_kill where goods_id=1;
+----+-------------+---------------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_second_kill | NULL | const | goods_id | goods_id | 8 | const | 1 | 100.00 | NULL |
+----+-------------+---------------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
ref
mysql> explain select * from t_second_kill where goods_id=1;
+----+-------------+---------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_second_kill | NULL | ref | goods_id | goods_id | 8 | const | 1 | 100.00 | NULL |
+----+-------------+---------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
range
KEY `goods_id` (`goods_id`) USING BTREE
mysql> explain select * from t_second_kill where goods_id>=1;
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t_second_kill | NULL | range | goods_id | goods_id | 8 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_second_kill where goods_name like '小米%';
+----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t_second_kill | NULL | range | goods_name | goods_name | 1022 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
index
KEY `goods_id` (`goods_id`) USING BTREE
mysql> explain select goods_id from t_second_kill;
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_second_kill | NULL | index | NULL | goods_id | 8 | NULL | 1 | 100.00 | Using index |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
all
mysql> explain select * from t_second_kill;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t_second_kill | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
possible_keys 可能选择的索引
key 实际使用的索引
key_len 实际使用的索引的长度 字节数 越短性能越好
复合索引时(abc), 条件a的长度就会短于条件a+b
KEY `goods_id` (`goods_id`,`store`) USING BTREE
mysql> explain select * from t_second_kill where goods_id=1;
+----+-------------+---------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_second_kill | NULL | ref | goods_id | goods_id | 8 | const | 1 | 100.00 | NULL |
+----+-------------+---------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_second_kill where goods_id=1 and store =10;
+----+-------------+---------------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | t_second_kill | NULL | ref | goods_id | goods_id | 12 | const,const | 1 | 100.00 | NULL |
+----+-------------+---------------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
ref 和索引进行比较的列
rows 需要被检索的大致行数 扫描行数越少性能越好
filtered 按表条件过滤的行百分比
Extra 额外信息