MYSQL执行计划详解（explain）及SQL优化

MYSQL执行计划详解（explain）及SQL优化

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，Mysql提供了explain 命令可以用来分析select 语句的运行效果。

	explain select * from account;

执行计划包含的信息

不同版本的Mysql和不同的存储引擎执行计划不完全相同，但基本信息都差不多。mysql执行计划主要包含以下信息:

• id
  由一组数字组成。表示一个查询中各个子查询的执行顺序;
  id相同执行顺序由上至下。
  id不同，id值越大优先级越高，越先被执行。
  id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。
  select_type：每个子查询的查询类型，一些常见的查询类型。

• table
  查询涉及到的数据表。如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

• type：访问类型
  ALL
  扫描全表数据
  index
  遍历索引
  range
  索引范围查找
  index_subquery
  在子查询中使用 ref
  unique_subquery
  在子查询中使用eq_re
  fref_or_null
  对Null进行索引的优化的 ref
  fulltext
  使用全文索引
  ref
  使用非唯一索引查找数据
  eq_ref
  在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。
  const
  使用主键或者唯一索引，且匹配的结果只有一条记录。
  system const
  连接类型的特例，查询的表为系统表。

• possible_keys
  可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为NULL时就要考虑当前的SQL是否需要优化了。

• key
  显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。
  TIPS:
  查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中。select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

• key_length
  索引长度

• ref
  表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

• rows
  返回估算的结果集数目，注意这并不是一个准确值。

• extra
  extra的信息非常丰富，常见的有：
  1.Using index 使用覆盖索引
  2.Using where 使用了用where子句来过滤结果集
  3.Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
  4.Using temporary 使用了临时表。

一些SQL优化建议

1、SQL语句不要写的太复杂。

一个SQL语句要尽量简单，不要嵌套太多层。

2、使用『临时表』缓存中间结果。简化SQL语句的重要方法就是采用临时表暂存中间结果，这样可以避免程序中多次扫描主表，也大大减少了阻塞，提高了并发性能。

3、使用like的时候要注意是否会导致全表扫有的时候会需要进行一些模糊查询比如：

select id from table where username like‘%zhangsan%’。

关键词%zhangsan%，由于zhangsan前面用到了“%”，因此该查询会使用全表扫描，除非必要，否则不要在关键词前加%，

4、尽量避免使用!=或<>操作符

在where语句中使用!=或<>，引擎将放弃使用索引而进行全表扫描。

5、尽量避免使用 or 来连接条件

在 where 子句中使用 or 来连接条件，引擎将放弃使用索引而进行全表扫描。

6、尽量避免使用in和not in在 where 子句中使用 in和not in，引擎将放弃使用索引而进行全表扫描，可以使用exists和not exists。

7、可以考虑强制查询使用索引

8、尽量避免使用表达式、函数等操作作为查询条件

9、尽量避免大事务操作，提高系统并发能力。

10、尽量避免使用游标

11、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

12、尽可能的使用 varchar/nvarchar 代替 char/nchar

13、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

14、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率

15、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引