Mysql执行计划
来源:互联网 发布:htc d820u数据连接 编辑:程序博客网 时间:2024/04/29 14:08
前段时间了解了下mysql的执行计划,今天整理了下。
介绍前先把涉及到的几张表简单介绍下:
员工表:employees,该表的主键是emp_no,无其他索引。
员工表信息
部门表:departments,主键是dept_no,还有一个唯一索引dept_name。
部门表
dept_name索引信息
员工部门表:dept_emp,主键是(emp_no, dept_no),另外还有2个普通索引:emp_no索引和dept_no索引
员工部门表
emp_no索引和dept_no索引
工资表:salaries,主键是(emp_no,from_date),还有一个普通索引:emp_no.
工资表
emp_no索引
现在开始介绍执行计划,首先说一下执行计划包含的信息:
怎么查看执行计划?只需在语句前面加上explain
EXPLAIN SELECT ……
变体: EXPLAIN EXTENDED SELECT ……
将执行计划“反编译”成SELECT语句,运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句;
下面介绍一下,执行计划中各个字段的含义:
1、id
包含一组数字,表示查询中执行select子句或操作表的顺序;
有2种情况:id相同和id递增
1.1、id相同
当id相同时,执行顺序由上至下;
比如:
EXPLAINSELECT *FROM employees emp,departments dept, dept_emp deptEmpWHERE emp.emp_no=deptEmp.emp_no and dept.dept_no=deptEmp.dept_noand emp.gender='M'
执行计划为:
图1-1
执行顺序由上到下
1.2、id递增
如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行;
SELECT emp.*FROM (SELECT dept_emp.emp_no FROM dept_emp WHERE dept_no='002') deptEmp, employees empWHERE deptEmp.emp_no=emp.emp_no
图1-2
先执行dept_emp表的查询
2、select_type
表示查询中每个select子句的类型(简单 OR复杂)
a.SIMPLE:查询中不包含子查询或者UNION,如图1-1,都是simple
b.查询中若包含任何复杂的子部分,最外层查询则被标记为:PRIMARY,如图1-2,id=1的都是primary
c.在SELECT或WHERE列表中包含了子查询,该子查询被标记为:SUBQUERY
d.在FROM列表中包含的子查询被标记为:DERIVED(衍生),如图1-2,id=2的查询
e.若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在 FROM子句的子查询中,外层SELECT将被标记为:DERIVED
f.从UNION表获取结果的SELECT被标记为:UNION RESULT
3、type
表示MySQL在表中找到所需行的方式,又称“访问类型”,常见类型如下:
all,index,range,ref,eq_ref,const,system,null
由左至右,由最差到最好,下面分别介绍一下这几种访问类型:
3.1、all
Full Table Scan, MySQL将遍历全表以找到匹配的行,比如:
EXPLAINSELECT * FROM employees empWHERE emp.gender='M'
图3-1
3.2、index:
Full Index Scan,index与ALL区别为index类型只遍历索引树,比如图3-2-1和图3-2-2
EXPLAINSELECT emp_noFROM employees emp
图3-2-1
图3-2-2
图3-2-3
图3-2-3中,走的是全表扫描,因为first_name不是索引。
3.3、range
索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询,如图3-3-1和图3-3-2
EXPLAINSELECT * FROM employeesWHERE emp_no BETWEEN 10001 AND 10010
图3-3-1
图3-3-2
3.4、ref
非唯一性索引扫描,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找。
PRIMARY KEY (`emp_no`,`dept_no`)
EXPLAINSELECT *FROM dept_empWHERE dept_emp.dept_no='d001'
图3-4-1
图3-4-2
图3-4-3
3.5、eq_ref
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描,如图3-5-1
图3-5-1
但是下面这条语句,emp表走的却是全表扫描,不知道是不是数据库优化造成的:
图3-5-2
3.6、const、system
当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量
EXPLAINSELECT *FROM (SELECT * FROM employees emp WHERE emp.emp_no=10001) t
图3-6
system是const类型的特例,当查询的表只有一行的情况下, 使用system
3.7、NULL
MySQL在优化过程中分解语句,执行时甚至不用访问表或索引
EXPLAIN EXTENDEDSELECT *FROM employees empWHERE emp_no=(SELECT MIN(emp_no) FROM salaries)
图3-7-1
图3-7-2
4、possible_keys
指出MySQL能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用,可以根据上面的执行计划,进行校验;
5、key
显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
TIPS:查询中若使用了覆盖索引,则该索引仅出现在key列表中
EXPLAINSELECT dept_no,emp_noFROM dept_emp
6、key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
EXPLAINSELECT *FROM dept_empWHERE dept_no='d001'
图6-1
EXPLAINSELECT *FROM dept_empWHERE dept_no='d001' and emp_no=10017
图6-2
key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的
7、ref
表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
EXPLAINSELECT *FROM employees emp, dept_emp deptEmpWHERE emp.emp_no=deptEmp.emp_no and dept_no='d001'
图7-1
8、rows
表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数
9、Extra
包含不适合在其他列中显示但十分重要的额外信息,取值主要有using index, using where, using temporary, using filesort;
下面介绍一下,这几个取值的含义:
9.1、Using index
该值表示相应的select操作中使用了覆盖索引(Covering Index)
EXPLAIN EXTENDEDSELECT emp_no,dept_noFROM dept_emp
图9-1
TIPS:覆盖索引(Covering Index)
MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件
包含所有满足查询需要的数据的索引称为 覆盖索引(Covering Index)
注意:
如果要使用覆盖索引,一定要注意select列表中只取出需要的列,不可select *,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降。
9.2、Using where
表示MySQL服务器在存储引擎收到记录后进行“后过滤”(Post-filter),
如果查询未能使用索引,Using where的作用只是提醒我们MySQL将用where子句来过滤结果集;
上面的例子中,很多带有Using where,这里列出一个
图9-2
9.3、Using temporary
表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询
EXPLAINSELECT emp_noFROM dept_empWHERE emp_no IN (10001,10005,10007)GROUP BY dept_no
图9-3-1
图9-3-2
9.4、Using filesort
MySQL中无法利用索引完成的排序操作称为“文件排序”;
Using filesort 是Mysql里一种速度比较慢的外部排序,如果能避免是最好的了,很多时候,我们可以通过优化索引来尽量避免出现Using filesort,从而提高速度。
比如可以通过建立联合索引来避免“Using filesort”,参考:
http://blog.csdn.net/yangyu112654374/article/details/4251624
http://blog.sina.com.cn/s/blog_3f2a82610100tqfc.html
MySQL执行计划的局限
•EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
•EXPLAIN不考虑各种Cache
•EXPLAIN不能显示MySQL在执行查询时所作的优化工作
•部分统计信息是估算的,并非精确值
•EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划
参考:http://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765237.html
- explain mysql 执行计划
- Mysql查看执行计划
- MySQL执行计划解读
- MySQL执行计划解读
- Mysql执行计划
- MySQL执行计划
- MYSQL 执行计划
- MySQL执行计划解读
- Mysql执行计划
- MySQL执行计划
- MySQL执行计划解读
- MySQL执行计划解读
- MySQL执行计划解读
- MySQL执行计划介绍
- MySQL执行计划解读
- MySQL执行计划解读
- MySQL执行计划解读
- mysql的执行计划
- nginx中的超时设置
- Nginx介绍
- c++ primer 学习笔记-第五章
- Android中自定义样式与View的构造函数中的第三个参数defStyle的意义
- UNITY_引擎_UGUI
- Mysql执行计划
- 真机上调试的错误
- 28. Location replace() 方法
- 第一个python程序(os模块使用)
- 112.Path Sum
- dyld: Library not loaded: @rpath/Dynamic.framework/Dynamic Referenced from: /Users/XXX/Librar
- java入门基础笔记
- Floyd-Warshall算法
- HDU 5339 Untitled (状态压缩枚举)