mysql explain 的分析

+—-+————-+——-+——+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+————-+
| 1 | SIMPLE | admin | ALL | NULL | NULL | NULL | NULL | 440 | Using where |
+—-+————-+——-+——+—————+——+———+——+——+————-+

1、id列。
每行对应一个表, id 列表示执行顺序,id 越大,越先执行,id 相同,由上至下执行。
①单表分析,等于1
②多表分析,看sql语句情况。如存在子查询,如例1
mysql> explain select * from users where users_group_id = 3 and id in (select user_id from users_info where id>100) limit 100;
+—-+——————–+————+—————-+———————–+———————–+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+——————–+————+—————-+———————–+———————–+———+——-+——+————-+
| 1 | PRIMARY | users | ref | fk_pople_people_group | fk_pople_people_group | 5 | const | 55 | Using where |
| 2 | DEPENDENT SUBQUERY | users_info | index_subquery | PRIMARY,user_id_idx | user_id_idx | 4 | func | 1 | Using where |
+—-+——————–+————+—————-+———————–+———————–+———+——-+——+————-+
③普通联表,[left/right] join,id的值都是1,如例2
mysql> explain select * from users u,users_info ui where u.id=ui.user_id and u.users_group_id = 3 limit 100;
+—-+————-+——-+——+——————————-+———————–+———+————-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+——————————-+———————–+———+————-+——+————-+
| 1 | SIMPLE | u | ref | PRIMARY,fk_pople_people_group | fk_pople_people_group | 5 | const | 55 | Using where |
| 1 | SIMPLE | ui | ref | user_id_idx | user_id_idx | 4 | qdpm80.u.id | 1 | |
+—-+————-+——-+——+——————————-+———————–+———+————-+——+————-+

2、select_type列。
SIMPLE:查询中不包含子查询或者UNION
SUBQUERY:子查询(见例1)
PRIMARY:子查询上层
DERIVED:在FROM列表中包含子查询, MySQL 会递归执行这些子查询,把结果放在临时表里。如例3
mysql> explain select * from (select user_id from users_info where id>100) as ui;
+—-+————-+————+——+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——+—————+——+———+——+——+————-+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 668 | |
| 2 | DERIVED | users_info | ALL | PRIMARY | NULL | NULL | NULL | 763 | Using where |
+—-+————-+————+——+—————+——+———+——+——+————-+

3、table列。
显示被查询的表的表明;如果使用的别名,则显示别名;存在临时表(例3)

4、type列。
| All | index | range | ref | eq_ref | const,system | null 依次变好。

①All,全表扫描。从头到尾扫描整张表查找行。如果加了limit,扫描方式不变。如例4
mysql> explain select * from users limit 10;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL |  777 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

②index,按索引次序扫描表,就是先读索引,再读实际的行,其实还是全表扫描。主要优点是避免了排序,因为索引是排好序的。如果加了limit,扫描制定行数停止
mysql> explain select * from users order by id asc limit 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | users | index | NULL          | PRIMARY | 4       | NULL |   10 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

③range,以范围的形式扫描索引
mysql> explain select * from users where id >0 limit 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | users | range | PRIMARY       | PRIMARY | 4       | NULL |  777 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
另外,in操作符,也是range。
mysql> explain select * from users where id in (1,2,3,4,5);
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | users | range | PRIMARY       | PRIMARY | 4       | NULL |    5 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

加不加limit,还是扫描5行。
mysql> explain select * from users where id in (1,2,3,4,5) limit 4;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | users | range | PRIMARY       | PRIMARY | 4       | NULL |    5 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

④ref,非唯一性索引访问
mysql> explain select * from users where users_group_id=3 limit 10;
+----+-------------+-------+------+-----------------------+-----------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys         | key                   | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------------+-----------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | fk_pople_people_group | fk_pople_people_group | 5       | const |   55 | Using where |
+----+-------------+-------+------+-----------------------+-----------------------+---------+-------+------+-------------+

⑤eq_ref,使用有唯一性索引查找(主键或唯一性索引unique)(貌似只存在与多表查询状态下,待验证)
唯一性索引才会出现eq_ref(非唯一性索引会出现ref),因为唯一,所以最多只返回一条记录,找到后无需继续查找,因此比 ref 更快。

⑥const
特征:只会返回一条匹配的记录,如使用主键id=xxx。
mysql> explain select * from users where id=10 limit 10;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | users | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

⑦system,当表只有一条记录的时候出现。

5、possible_keys列。可能会被用到的索引

6、key列。查询过程中,实际应用到的索引列。

7、key_len列。索引字段最大可能使用的长度。比如int行,长度是4字节,所以ken_len为4

8、ref列。
①指出对 key 列所选择的索引的查找方式,常见的值有 const, func, NULL, 具体字段名。
②当 key 列为 NULL ,即不使用索引时,此值也相应的为 NULL。
③有时候,会看到key为主键,但ref为null,比如使用了in的时候,见上上上例。不知具体原因
④ref为func,存在与子查询中。如例1

9、rows列。预估需要扫描的行数,仅是个估值。

10、Extra列。
①Using index。此查询使用了覆盖索引(Covering Index),即通过索引就能返回结果,无需访问表。若没显示”Using index”表示读取了表数据
mysql> explain select id from users;
+—-+————-+——-+——-+—————+———+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——+——+————-+
| 1 | SIMPLE | users | index | NULL | PRIMARY | 4 | NULL | 777 | Using index |
+—-+————-+——-+——-+—————+———+———+——+——+————-+

mysql> explain select users_group_id from users;
+----+-------------+-------+-------+---------------+-----------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key                   | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-----------------------+---------+------+------+-------------+
|  1 | SIMPLE      | users | index | NULL          | fk_pople_people_group | 5       | NULL |  777 | Using index |
+----+-------------+-------+-------+---------------+-----------------------+---------+------+------+-------------+

②Using where。“后过滤”规则,先读后查。即先读取整行数据,在根据where条件筛选,符合留下,不符合丢弃。
有一点,使用了唯一索引的where条件,不会出现Using where。(待验证)
如果索引列,允许null,哪怕是唯一索引,也会出现Using where(待验证)

③Using temporary。使用distinct。
mysql> explain select distinct users_group_id from users limit 10;
+----+-------------+-------+-------+---------------+-----------------------+---------+------+------+------------------------------+
| id | select_type | table | type  | possible_keys | key                   | key_len | ref  | rows | Extra                        |
+----+-------------+-------+-------+---------------+-----------------------+---------+------+------+------------------------------+
|  1 | SIMPLE      | users | index | NULL          | fk_pople_people_group | 5       | NULL |  779 | Using index; Using temporary |
+----+-------------+-------+-------+---------------+-----------------------+---------+------+------+------------------------------+

④Using filesort。使用文件排序。
对于没有索引的表,只要 order by 必会出现 Using filesort。
取全表数据根据ID排序,走索引一定不如直接查,因为可以减少因为需要索引改变数据访问顺序造成随机IO的概率,数据库放弃索引是应该的
type 为 rang、 ref 或者 index 的时候才有可能利用索引排序,其它,如 ALL ,都无法通过索引排序,此时若有 order by ,便会出现 Using filesort 。
mysql> explain select * from users where users_group_id=3 order by email desc limit 10;
+----+-------------+-------+------+-----------------------+-----------------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys         | key                   | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+-----------------------+-----------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | users | ref  | fk_pople_people_group | fk_pople_people_group | 5       | const |   55 | Using where; Using filesort |
+----+-------------+-------+------+-----------------------+-----------------------+---------+-------+------+-----------------------------+

总结:
1、先看表结构,是否用到了索引;
2、看字段数,字段类型中是否包含varchar,text等
3、看表数据,是否超过某个固定数据量。结合字段类型参考
4、explain,主要看type,key,ref,row,extra。

相关推荐
©️2020 CSDN 皮肤主题: 撸撸猫 设计师:马嘣嘣 返回首页