mysql性能调优分析之Explain详解

简介

explain显示了mysql如何使用索引来处理DML语句以及连接表，explain显示的信息可以帮助选择更好的索引和写出更优化的查询语句。

explain列解释

• table:显示这一行的数据是关于哪张表的
• type：显示连接使用了何种类型，从最好到最差的连接类型顺序：const，eq_reg，ref,range, index ,ALL
• possible_keys:显示可能应用在这张表中的索引，如果为空，没有可能的索引，可以为相关的域从WHERE语句中选择一个合适的语句。
• key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，mysql会选择优化不足的索引。这种情况下，可以在select语句中使用USE INDEX（indexName）来强制使用一个索引或者用IGNORE INDEX（indexName）来强制mysql忽略索引。
• key_len:使用的索引长度。在不损失精确性的情况下，长度越短越好。
• ref:显示索引的哪一列被使用了，如果可能的话，是一个常数。
• rows：mysql认为必须检查的用来返回请求数据的行数。
• Extra：关于mysql如何解析查询的额外信息，坏的例子是Using temporary 和 Using filesort，意思mysql根本不能使用索引，结果是检索会很慢。

Extra列返回的描述意义

• Distinct:一旦mysql找到了与之相联合匹配的行，就不再搜索了
• Not exists:mysql优化了LEFTJOIN ,一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
• Range checked for each Record(index map:#):没有找到理想的索引，因此对于从前面表中的每一行组合，mysql检查都是用那个索引，并用它从表中返回行。这是使用索引的最慢连接之一。
• Using filesort:看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行指针来排序全部行。
• Using index：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部请求列都是同一个索引的部分的时候。
• Using temporary：看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
• Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低点顺序排序）
• system表只有一行：system表，这是const连接类型的特殊情况
• const：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或唯一索引）。因为只有一行，这个值实际就是常数，因为mysql先读这个值后把它当做常数来对待。
• eq_ref：在连接中，mysql在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或唯一键的全部时使用。
• ref：这个链接类型只有在查询使用了不是唯一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少，越少越好。
• range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生。
• index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
• ALL：这个连接类型对前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。

案例

优化器中range的使用案例:

在寻求优化的时候需了解3点：

1、表的DDL

2、SQL语句

3、EXPLIAN

CREATE TABLE `account` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户资金账户',

  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',

  `amount` decimal(10,2) DEFAULT '0.00' COMMENT '总金额',

  `available` decimal(10,2) DEFAULT '0.00' COMMENT '可用余额',

  `presented` decimal(10,2) DEFAULT '0.00' COMMENT '活动赠送金额',

  `deposit_amount` decimal(10,2) DEFAULT '0.00' COMMENT '押金金额',

  `freeze_amount` decimal(10,2) DEFAULT NULL COMMENT '冻结金额',

  `status` tinyint(4) DEFAULT NULL COMMENT '账户状态',

  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

  PRIMARY KEY (`id`),

  UNIQUE KEY `uid_index` (`user_id`) USING BTREE,

  KEY `create_time` (`create_time`)

) ENGINE=InnoDB AUTO_INCREMENT=6184535 DEFAULT CHARSET=utf8

EXPLAIN SELECT * FROM account WHERE id>1

id  select_type  table typepossible_keyskey  key_lenref   rowsExtra

1 SIMPLE    account rangePRIMARY     PRIMARY 8       2808936Using where

看起来好像走了RANGE…

EXPLAIN SELECT * FROM account WHERE user_id>1

id  select_typetabletypepossible_keyskeykey_lenref rows   Extra

1 SIMPLE   accountALL    uid_index        5617872  Using where

这里出问题了吧…

EXPLAIN SELECT * FROM account WHERE user_id>1 AND user_id<2222

id  select_typetable typepossible_keyskey    key_lenref rows    Extra

1 SIMPLE   account range uid_index uid_index  9     2218Using index condition

#第一个看起来是走了range,实际是因为primary key 起决定性因素

在第2个有索引但全表扫描，只有开区间没有闭区间

在第3个包含了开区间和闭区间直接走了range

由此：在SQL优化时候，如果要使用range来实现索引，必须满足有开始点和结束点，俗称开区间和闭区间。

比如在时间字段上添加2级索引，如：

SELECT * FROM account WHERE create_time>DATE_FORMAT(“2017-01-26”,‘%Y-%m-%d’) AND create_time<DATE_FORMAT(“2017-01-29”,‘%Y-%m-%d’)

SELECT * FROM account WHERE create_time>DATE(“2017-01-26”)AND create_time <DATE(“2017-01-29”)

SELECT * FROM account WHERE create_time>STR_TO_DATE(“2017-01-26”,‘%Y-%m-%d’)AND create_time <STR_TO_DATE(“2017-01-29”,‘%Y-%m-%d’)

id   select_typetabletypepossible_keyskey     key_lenrefrowsExtra

1     SIMPLE   accountrangecreate_time  create_time6     129610Using index condition

一般WHERE条件里包含函数是不能走索引的，前提是不靠近WHERE时候就能生效