在一条 SELECT 语句前放上关键字 EXPLAIN ,MySQL 解释它将如何处理 SELECT,提供有关表如何联合和以什么次序联合的信息。
借助 EXPLAIN 可以知道
- 什么时候必须为表加入索引,以得到一个使用索引找到记录的更快 select 方法
- 优化器是否以一个最佳次序联结表。
为了强制优化器对一个 SELECT 语句使用一个特定的联接次序,需增加一个 STRAIGHT_JOIN 子句。
举一个MySQL数据库表的例子:
CREATE TABLE `test`.`tfr` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`teacher_feedback_id` bigint(20) unsigned NOT NULL COMMENT '教师反馈id',
`teacher_feedback_create_time` timestamp NOT NULL COMMENT '教师反馈创建时间',
`subject_id` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '学科id',
`parent_type_id` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '问题一级分类',
`type_id` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '问题二级分类',
`content_type` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '内容分类,1题目,2知识点',
`status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '处理状态,-1暂不处理,1等待处理,2处理完成',
`msg` varchar(1000) NOT NULL DEFAULT '' COMMENT '暂不处理的原因',
`create_user` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建人用户id',
`update_user` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '最后更改人用户id',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated` timestamp COMMENT '最后修改时间',
`autoutime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
MySQL [test]> explain select * from tfr;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | tfr | NULL | ALL | NULL | NULL | NULL | NULL | 992591 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)
各属性含义如下:
- id:查询的序列号。
- select_type:查询的类型,主要包括普通查询、联合查询和子查询。
- table:所访问的数据库中表的名称。
- partitions:匹配的分区。
- type:联合查询使用的类型,是较重要的指标。一般来说,保证查询至少达到range级,最好能达到ref级别。ALL为全表扫描是最坏的情况,这种情况往往是没用上索引。结果值从好到坏依次是:
-
- system(系统表):该表只有一行。这是const连接类型的特例。
-
- const(读常量):该表最多具有一个匹配行,改行在查询开始时读取。因为只有一行,所以优化器的其余部分可以将这一行中的列的值视为常量。const表非常快,因为它们只能读取一次。例如:
explain SELECT * FROM `test`.`tfr` WHERE id = 1;
- const(读常量):该表最多具有一个匹配行,改行在查询开始时读取。因为只有一行,所以优化器的其余部分可以将这一行中的列的值视为常量。const表非常快,因为它们只能读取一次。例如:
-
- eq_ref(最多一条匹配结果,通常是通过主键访问):对于先前表中的每行组合,从此表中读取一行。除了 system和 const类型,这是最好的联接类型。当联接使用索引的所有部分并且索引为a PRIMARY KEY或UNIQUE NOT NULLindex时使用。
-
- ref(被驱动表索引引用)
-
- fulltext(全文索引检索)
-
- ref_or_null(带空值的索引查询)
-
- index_merge(合并索引结果集)
-
- unique_subquery(子查询中返回的字段是唯一组合或索引)
-
- index_subquery(子查询返回的是索引,但非主键)
-
- range(索引范围扫描)
-
- index(全索引扫描)
-
- ALL(全表扫描)
- possible_keys:指出 MySQL 能使用哪个索引在该表中找到该行。如果这个值是空的则表示没有相关的索引。这时要提高性能,可通过检测where子句,看看是否引用了某些字段,或者检查字段是否适合索引。
- key:MySQL 实际决定使用的键。如果没有索引被选择,键是 NULL。
- key_len:显示 MySQL 决定使用的键长度。如果键是 NULL,长度就是NULL。注意这个值可以反映出一个多主键(复合主键)里的 MySQL 实际使用了哪些部分。
- ref:显示哪个字段或常数与 key 一起被使用。
- rows:这个值表示 MySQL 要遍历多少数据才能找到所需的结果集,在 InnoDB 上是不准确的。
- filtered:按表条件过滤的行百分比
- Extra:附加信息
-
- Only index:表示信息只能用索引树中的信息检索,这比扫描整个表要快。
-
- where used:表示使用了 where 限制,但是用索引还不够。
-
- impossible where:表示通过收集到的统计信息判断出不可能存在结果
-
- using filesort:表示包含 order by 且无法使用索引进行排序操作,不得不使用相应的排序算法实现。
-
- using temporary:使用临时表,常见于 order by 和 group by
-
- select tables optimized way:使用聚合函数,并且MySQL进行了快速定位。通常是MAX,MIN,COUNT(*)等函数。
还需要补充不同type的sql示例
在这里插入代码片