首页 / MYSQL / mysql 执行计划的理解
mysql 执行计划的理解
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql 执行计划的理解，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含12506字，纯文字阅读大概需要18分钟。
内容图文
1、执行计划就是在sql语句之前加上explain，使用desc 也可以。
2、desc有两个选项extended和partitions，desc extended 将原sql语句进行优化，通过show warnings 可以看到优化后的sql语句。
desc partitions 可以查看使用分区表的信息。
3、比如：
mysql> desc select * from student;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 5 | |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
id 是执行计划的编号，可以理解为方法的调用堆栈，调用堆栈先进后出，id越大，越先执行，如下：
mysql> desc select * from student where id>1 union select * from student where id<5;
+------+--------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | student | ALL | ID | NULL | NULL | NULL | 5 | Using where |
| 2 | UNION | student | ALL | ID | NULL | NULL | NULL | 5 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+------+--------------+------------+------+---------------+------+---------+------+------+-------------+
mysql> desc select * from student where id in (select id from student where name=‘Andy‘);
+----+--------------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | student | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
| 2 | DEPENDENT SUBQUERY | student | ALL | ID | NULL | NULL | NULL | 5 | Using where |
+----+--------------------+---------+------+---------------+------+---------+------+------+-------------+
2 rows in set
select_type 查询类型，主要的取值如下：
1、simple，只是简单的查询。如下：
mysql> desc select * from student;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
2、primary/union/union result，组合查询
mysql> desc select * from student where id>1 union select * from student where id<4;
+------+--------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | student | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where |
| 2 | UNION | student | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+------+--------------+------------+-------+---------------+---------+---------+------+------+-------------+
3、primary/dependent subquery，依赖子查询
mysql> desc select * from student where id in (select sid from sc where score>80);
+----+--------------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | student | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
| 2 | DEPENDENT SUBQUERY | sc | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
+----+--------------------+---------+------+---------------+------+---------+------+------+-------------+
4、primary/derived, 查询的目标不是物理表，也就是使用了临时表
mysql> desc select min(id) from (select id from student where name=‘Andy‘) t1;
+----+-------------+------------+------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+-----------+---------+------+------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | |
| 2 | DERIVED | student | ref | stu_index | stu_index | 51 | | 2 | Using where; Using index |
+----+-------------+------------+------+---------------+-----------+---------+------+------+--------------------------+
table 引用的表
type是指访问类型，主要的取值有 all，index，range，ref，eq_ref，const，system，null
1、all 遍历全表
mysql> desc select name from student;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
2、index 使用索引，遍历索引树
mysql> create index index_name on student(name);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | ID | A | 3 | NULL | NULL | | BTREE | | |
| student | 1 | index_name | 1 | NAME | A | 3 | NULL | NULL | YES | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set
mysql> desc select name from student;
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | student | index | NULL | index_name | 51 | NULL | 4 | Using index |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
3、range 使用索引，扫描范围
mysql> desc select name from student where name in (‘Andy‘,‘Bill‘);
+----+-------------+---------+-------+---------------+------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------------+---------+------+------+--------------------------+
| 1 | SIMPLE | student | range | index_name | index_name | 51 | NULL | 3 | Using where; Using index |
+----+-------------+---------+-------+---------------+------------+---------+------+------+--------------------------+
4、ref 非唯一性索引扫描，找到所有匹配的索引值
会有多个name为Andy的记录
mysql> desc select name from student where name =‘Andy‘;
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | student | ref | index_name | index_name | 51 | const | 2 | Using where; Using index |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
id为2的查询，对于teacher的一个name，从student找到找到所有name相同的记录
mysql> desc select * from student,teacher where student.name=teacher.name;
+----+-------------+---------+-------+---------------+------------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------------+---------+---------------------+------+-------------+
| 1 | SIMPLE | teacher | index | index_name | index_name | 195 | NULL | 1 | Using index |
| 1 | SIMPLE | student | ref | index_name | index_name | 51 | testsc.teacher.NAME | 1 | Using where |
+----+-------------+---------+-------+---------------+------------+---------+---------------------+------+-------------+
5、eq_ref，对于前一个表的字段，只有一个记录会匹配，如下：
因为id是student主键，student只有一条记录匹配teacher的id。
mysql> desc select * from student,teacher where student.id=teacher.id;
+----+-------------+---------+--------+---------------+------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+---------------+------------+---------+-------------------+------+-------------+
| 1 | SIMPLE | teacher | index | PRIMARY | index_name | 195 | NULL | 1 | Using index |
| 1 | SIMPLE | student | eq_ref | PRIMARY | PRIMARY | 4 | testsc.teacher.ID | 1 | |
+----+-------------+---------+--------+---------------+------------+---------+-------------------+------+-------------+
6、const mysql对查询优化，转化为常量
mysql> desc select * from student where student.id=10001;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | student | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set
7、system 是const的一个特例，查询的记录只有一条
mysql> desc select id from (select * from student where student.id=10001) t1;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | student | const | PRIMARY | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
2 rows in set
8、null 没有记录，都不需要执行查询
mysql> desc select 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set
possible_keys 可能用到的索引
key 用到的索引
key_len 用到的索引长度，和三个因素有关：
是否允许为null，允许为null 多出一个字节标识是不是null
是否变长，变长多出两个字节表示长度
字符编码，不同字符编码，同一个字符占用的内存不一样latin1[1]，gb2312[2]，gbk[2]，utf8[3]
ref 匹配的条件
mysql> desc select * from student,teacher where student.name=teacher.name;
+----+-------------+---------+-------+---------------+------------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------------+---------+---------------------+------+-------------+
| 1 | SIMPLE | teacher | index | index_name | index_name | 195 | NULL | 1 | Using index |
| 1 | SIMPLE | student | ref | index_name | index_name | 51 | testsc.teacher.NAME | 1 | Using where |
+----+-------------+---------+-------+---------------+------------+---------+---------------------+------+-------------+
rows 估算扫描的行数
Extra 额外信息，主要有
Using index
Using where
Using temporary
Using filesort 无法利用索引完成的排序
MySQL执行计划的局限性：
不考虑触发器、存储过程的信息或用户自定义函数对查询的影响情况
不考虑各种Cache
不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
只能解释SELECT操作，其他操作要重写为SELECT后查看。
原文：http://www.cnblogs.com/nzbbody/p/4605665.html
内容总结
以上是互联网集市为您收集整理的mysql 执行计划的理解全部内容，希望文章能够帮你解决mysql 执行计划的理解所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。