三、使用explain分析索引
在 不确定应该在哪些数据列上创建索引的时候,我们可以从EXPLAIN SELECT命令那里往往可以获得一些帮助。这其实只是简单地给一条普通的SELECT命令加一个EXPLAIN关键字作为前缀而已。有了这个关键 字,MySQL将不是去执行那条SELECT命令,而是去对它进行分析。MySQL将以表格的形式把查询的执行过程和用到的索引(如果有的话)等信息列出 来。这里我基本阐述下每个信息字段含义,不展开阐述,我们只要注意几个关键点(关键点以下用红色加粗显示)能大概看懂即可呵呵~~
1.id:SQL执行的顺利的标识。
sql从里向外执行,通过以上观察发现sql是按照id从大到小执行的。
2.select_type:SELECT类型
1)简单SELECT(不使用UNION或子查询等)
2) PRIMARY:最外层的select
3)DERIVED:派生表的SELECT(FROM子句的子查询)
4)UNION:UNION中的第二个或后面的SELECT语句
5)UNION RESULT:UNION的结果。
6)DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
7)SUBQUERY:子查询中的第一个SELECT
8)DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
PS:这里我总结了下子查询的in语句会用到DEPENDENT关键字,如果子查询是union则是DEPENDENT UNION;如果子查询是简单的条件语句则是DEPENDENT SUBQUERY。这里不一定准确是我自己总结的哈~~如果不对望指正
3.table:表的名字。
有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)
4.type:连接操作的类型。
这列很重要,显示了连接使用了哪种类别,有无使用索引。在各种类型的关联关系当中,效率最高的是system,然后依次是const、eq_ref、ref、range、index和 All。一般来说,得保证查询至少达到range级别,最好能达到ref,否则就可能会出现性能问题。
1)system
表只有一行:system表。这是const连接类型的特殊情况
2)const
表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待
3)eq_ref
在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用
4)ref
这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少(越少越好)
5)range
这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况
6)index
这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据)
7)ALL
这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免。因为它要扫描整个表。你可以加入更多的索引来解决这个问题。
5.possible_keys:MySQL在搜索数据记录时可以选用的各个索引的名字。
这里的索引名字是创建索引时指定的索引昵称;如果索引没有昵称,则默认显示的是索引中第一个列的名字(在上一节举的例子中是“firstname”)。默认索引名字的含义往往不是很明显。
6.key:它显示了MySQL实际使用的索引的名字。
key数据列是MySQL实际选用的索引,如果它为空(或NULL),则MySQL不使用索引。
7.key_len:索引中被使用部分的长度,以字节计。
key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。 在上例中,key_len是102,其中firstname占50字节,lastname占50字节,age占2字节(smallint存储大小为2字节)。如果MySQL只使用索引中的firstname部分,则key_len将是50。 在不损失精确性的情况下 ,key_len数据列里的值越小越好(意思是更快)。
8.ref:显示使用哪个列或常数与key一起从表中选择行。
ref数据列给出了关联关系中另一个数据表里的数据列的名字。
9.rows:MySQL所认为的它在找到正确的结果之前必须扫描的记录数。
显然,这里最理想的数字就是1。
10.extra:附加信息
Using index和Using where会遇到的比较多,可以重点记下,其他的我没怎么遇到过了解即可,遇到具体问题可以查阅哈
1)Distinct
一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
2)Not exists
MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了
3)Range checked for each
没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一
4)Using filesort
看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
5)Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候
6)Using temporary
看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上
7)Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题
先说到这,下面一篇给大家总结下如何选择索引列以及使用索引的注意事项
相关推荐
SQL优化实践.pdf
MySQL 最佳优化实践手册内容:例如btree索引,hash索引,聚簇索引和费举措索引,多列索引、重复索引和冗余索引等如何用,count、unicon等优化查询的方法
第六部分 SQL优化实践 查看执行计划的工具和方法 第六部分 SQL优化实践 查看执行计划的工具和方法 第六部分 SQL优化实践 查看执行计划的工具和方法 第六部分 SQL优化实践 干预执行计划 第六部分 SQL优化实践 干预...
sql优化视频 学习sql优化必备 适合初中级开发人员 包括笔记 视频 脚本
SQL Server 2008性能优化实践探究
对DB2SQL优化有很大的帮助
SQL优化公开课1 倾力打造。书中包含了详细的案例研究、最佳实践和丰富的0racle新的调整特性的代码示例。
sql查询优化规则,左右连接,执行计划使用,以提高查询效率
数据库优化实践, 数据库优化理论, 数据操作
本书希望能够通过一步步详细介绍SQL优化的方法,帮助读者分 析和调优有问题的SQL语句。 主要内容 ● 找出收集和诊断问题必备的分析命令 ● 创建MySQL索引来改进查询性能 ● 掌握MySQL的查询执行计划 ● 找...
详细介绍慢查询日志及示例演示,MySQL查询优化器介绍及特定SQL的查询优化等 1.获取有性能问题的SQL的三种方法 2.慢查询日志概述 3.慢查询日志实例 4.实时获取性能问题SQL 5.SQL的解析预处理及生成的执行计划 6.如何...
Spark SQL技术架构优化实践.pptx
倾力打造。书中包含了详细的案例研究、最佳实践和丰富的0racle新的调整特性的代码示例。
使用性能监视器、SQL Trace以及动态管理视图和函数建立性能基线 理解一般系统中发生瓶颈的...SQL Server 2008查询性能优化》不是理论书籍,它的目的是帮助你避免数据库出现性能低下的状况,它还能帮助你保住你的工作。
SQL优化最佳实践:构建高效率Oracle数据库的方法与技巧.docx
SQL Server 2008性能优化实践探讨.pdf
SQLAdvisor架构和实践 SQLAdvisor release notes SQLAdvisor开发规范 FAQ SQLAdvisor是由美团点评公司技术工程部DBA团队(北京)开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析,结合...
阐述了在sql优化方面的细节和实践,有比较实际的指导意义
SqlServer性能优化 DB性能优化 数据库性能优化 MSSQL性能优化
Oracle SQL性能优化最佳实践.docx