在项目中验证sql语句执行效率的时候最直观的方式就是查看其执行时间，但是在线上环境中如果不慎运行一个效率十分低下的sql导致数据库down掉了，那就悲剧了。并且只看执行时间，并无法有效的定位影响效率的原因。因此通过EXPLAIN命令查看SQL语句的执行计划，根据执行计划可以对SQL进行相应的优化。理解SQL执行计划各个字段的含义这时候显得十分重要。 如下图 EXPLAIN SELECT COUNT(*) FROM blog这是一个简单的sql的执行计划，可以看到其包含十个字段来描述这个执行计划。其中比较重要的字段有select_type、Type、ref、Extra 下面为更好的理解执行计划，这里对每个字段进行相应的解释。
1.id一个复杂的sql会生成多执行计划如下图： EXPLAIN SELECT COUNT(*) FROM (SELECT id from blog where id = 1) a 图1
可以看到含有子查询的sql产生了两条记录，分别表示该条sql的执行顺序。
2.select_type查询类型，有如下几种值 2.1 simple 表示简单查询，没有子查询和union 如图1所示 2.2 primary 最外边的select，在有子查询的情况下最外边的select查询就是这种类型如图2所示 2.3 union union语句的后一个语句执行的时候为该类型如图2.1所示
EXPLAIN SELECT COUNT(*) FROM blog UNION SELECT id from blog where id = 1 图2.1
2.4 union result union语句的结果 如图2.1所示。 。。。。。。
3.table使用的表名
4.type连接类型，十分重要的字段 按照代表的效果由最优到最差情况进行介绍。 4.1、system 表仅有一行 const的特例。 4.2、const 最多匹配一行并且使用primarykey 或 unique索引，才会是const。 EXPLAIN SELECT * FROM blog where id =1 下面这种情况搜索到一条数据但是没有用到主键或索引 所以type不是const 关于all的含义将在下文介绍 EXPLAIN SELECT * FROM blog LIMIT 14.3、eq_ref 根据mysql官方手册的解释： "对于每个来自于前面的表的行组合，从该表中读取一行。这可能是除了const类型最好的联接类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY" 。eq_ref可以用于使用=比较带索引的列。看下面的语句 EXPLAIN SELECT * FROM blog , author where blog.blog_author_id = author.idEXPLAIN SELECT * FROM author,blog where blog_author_id = author.id 4.4、ref 对于所有取自前表的行组合，所有的匹配项都是通过索引读出的。 也可以理解为连接不能基于关键字选择单个行,可能查找到多个符合条件的行。叫做 ref 是因为索引要跟某个参考值相比较。这个参考值或者是一 个常数,或者是来自一个表里的多表查询的 结果值。 如下图。 EXPLAIN SELECT * FROM blog where blog_author_id = 2 其中blog_author_id有索引写到这里 相信大家还是对以上各种类型的解释有点迷迷糊糊。下面看一个等值连接的例子，会加深对索引和以上解释的理解。 SELECT * FROM author,blog where author.id=blog.blog_author_id and author.id = 2 这条语句查出作者2发表的所有博客。id为author表主键，mysql会自动为主键创建唯一索引。而blog_author_id是blog一个普通字段，如果对其加个索引看一下运行的效果。先观察下一下这个执行计划，可以看出mysql对sql语句的执行已经做了很好的优化.这里可以看到其中一条优化规则，先做选择操作缩小连接操作的集合维度，再做连接操作，详细可查看mysql生成执行计划的优化策略。 解释一下：第一行代表mysql生成的第一个执行计划。即select * from author where id= 2. 由于id是author表的主键，且表包含多条数据但仅命中一行，所以其类型为const。 第二行:对于blog表中auhorid为2的记录有多个，且是通过索引读出的。满足ref的条件。 自然而然 如果把blog表中的author_id所以去除掉，则其类型应该不会再是ref。让我们来验证这个想法。 drop index author_id on blog 再来执行以下查询语句可以看到type类型变为ALL了，这种类型的效率非常慢，同时你可以看到rows这一行数据也发生了变化。由于没有索引，所以需要扫描全表。详细关于ALL类型和rows列的含义将在下文中介绍。 下面接着看下一个类型。 4.5 ref_or_null
如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。 或解释为MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找。 这种类型没搞明白 做实验都没出现这种类型 希望各位朋友给个例子。
但是上面说的这五种类型是属于总体来说效果很不错的了。如果能满足以上类型的查询 基本上不需要太大的优化、下面介绍效率较低几种类型 当出现以下几种类型的查询 就要好好考虑做做优化了。
4.6 index_merge
该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。查看下面这条sql EXPLAIN SELECT * FROM blog where blog_title = "first" and blog_author_id = 1大致解释一下索引和并优化的概念，这时mysql针对sql使用多个索引进行查询时的优化方案。通俗的说就是mysql会把同一个表的多个索引扫描的结果进行合并。详细的去看看相关博客。 解释一下上述的例子，分别对blog_title和authorid创建索引，这时用and查询满足以上两种条件的结果，如果查到一条的话它就是ref 但是如果匹配多条的话他就会进行索引合并。 4.7unique_subquery 顾名思义 subquery可以看出这种类型跟子查询有关系，同时大家知道子查询在mysql中是十分不建议使用的一种查询方式，当遇到子查询时多思考如果通过连接查询来优化。尽可能少的使用IN语句。 在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr) EXPLAIN SELECT * FROM blog where blog_author_id in (SELECT id from author where author_name = "test1")
即使对authorname创建索引也是相同的执行计划对于这种情况你可以将其改写成一个left join语句
SELECT blog.* FROM blog LEFT JOIN author ON blog_author_id = author.id WHERE author_name = "test1"
一样的执行结果 但是执行计划就是不同的如下图
可见这种查询就是用到了索引。效率可想而知。 4.8 index_subquery 在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique_subquery 类似,但是查询的是非唯一性索引:
value IN (SELECT key_column FROM single_table WHERE some_expr) EXPLAIN SELECT * FROM author where id in ( SELECT blog_author_id from blog where blog_title = "secend")
同样的要尽量避免使用这种方式的查询。 4.9 range 顾名思义，range意思就是范围。因此可以解释为：只检索给定范围的行,使用一个索引来选择 行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可 以使用 range。 这种类型解释的很清楚了 稍微举个栗子大家看看吧。 EXPLAIN SELECT * FROM blog where id > 2
4.10 index 这种类型的意思也十分明显，查询过程中使用到了索引。解释为： 全表扫描,只是扫描表的时候按照索引次序 进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。举个栗子 EXPLAIN SELECT * FROM blog ORDER BY id4.11 all 最坏的情况,从头到尾全表扫描 。性能最差的一种类型 遇到这种类型 你得想想 为什么不建索引！ 为什么 不改造 sql！ 改造sql也是为了让mysql运行的时候尽可能的使用到索引， 这里又牵扯出一个问题 如何建索引 数据库维护索引也是一件十分费时费力的事情。详细内容自行查询 本人还未总结~~~ 这个就不举例子了 大家看看上边的例子 有很多连接查询计划中都存在all类型，顺便想想如何优化。
解释到这里大家对执行计划所代表的效率含义基本上有个认识了，现在对后面的字段进行介绍。5.possible_keys很明显了 它的意思就是有可能使用到的索引。6.keyMySQL 实际从 possible_key 选择使用的索引。 如果为 NULL,则没有使用索引。很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT 语句中使用 USE INDEX (indexname)来强制使用一个索引或者用 IGNORE INDEX(indexname)来强制 MYSQL 忽略索引。7.key_length使用索引的长度。当然在不失精度的情况下 长度越小越好！8.ref显示索引的那一列被引用到了。9.rowsMYSQL 认为必须检查的用来返回请求数据的行数，越大越不好。说明没有很好的使用到索引。10 Extra表示mysql解决查询的详细信息。 10.1 Using Index 表示使用到索引 10.2 using filesort 表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序” 常见于 order by 和group by语句中。 注意如果你对排序列创建索引mysql仍然会提示你使用的是filesort，所以对于这个字段应该有自己的判断。
EXPLAIN SELECT * FROM blog order by blog_title10.3 Using temporary 表示进行查询时使用到临时表。当使用到临时表时，表示sql的效率需要进行相应的优化了。这种类型可能会在连接排序查询中出现。
为了便于理解先举一个例子。
EXPLAIN SELECT * FROM author,blog where author.id=blog.blog_author_id and blog.blog_title="first" order by author.id desc
这条语句是要查出写first这篇博客的博主信息，并按用户id排序。 先来看看mysql连接查询算法 Nested Loop Join 通过驱动表的结果集，一条一条的按照连接条件查询下个表中的记录。 这里出现了一个名词驱动表 驱动表定义： 1.当连接条件确定时，查询条件筛选后记录少的为驱动表。 2.当连接条件不确定时，行数少的表为驱动表。 按照上述定义，由于blog_tiltle经过筛选条件后查询得到的记录数为2，而未对author表进行条件过滤，因此该sql的驱动表为blog。 将过滤后的blog表的记录一条条的对author表查询，而后合并，这时需要按照author表的id字段进行排序，因此需要对合并结果(临时表)进行排序。 如果按照驱动表排序，则可以直接排序而无需临时表。 EXPLAIN SELECT * FROM author,blog where author.id=blog.blog_author_id and blog.blog_title="first" order by blog.id desc
如果觉得《Mysql查询性能优化-善用Explain语句》对你有帮助，请点赞、收藏，并留下你的观点哦！
