mysql中慢查询优化的示例分析

这篇文章主要介绍mysql中慢查询优化的示例分析，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

一个用户反映线上一个SQL语句执行时间慢得无法接受。SQL语句看上去很简单（本文描述中修改了表名和字段名):SELECT count(*)  FROM  a  JOIN  b ON  a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ; 且查询需要的字段都建了索引，表结构如下：CREATE TABLE `a` (  `L` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',  `I` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  `A` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  `S` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  `F` tinyint(4) DEFAULT NULL,  `V` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '',  `N` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  KEY `IX_L` (`L`),  KEY `IX_I` (`I`),  KEY `IX_S` (`S`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `b` (  `R` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',  `V` varchar(32) DEFAULT NULL,  `U` varchar(32) DEFAULT NULL,  `C` varchar(16) DEFAULT NULL,  `S` varchar(64) DEFAULT NULL,  `I` varchar(64) DEFAULT NULL,  `E` bigint(32) DEFAULT NULL,  `ES` varchar(128) DEFAULT NULL,  KEY `IX_R` (`R`),  KEY `IX_C` (`C`),  KEY `IX_S` (`S`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;从语句看，这个查询计划很自然的，就应该是先用a作为驱动表，先后使用 a.L和b.S这两个索引。而实际上explain的结果却是：    +----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+| id | select_type | table | type  | possible_keys | key  | key_len | ref      | rows    | Extra       |+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+|  1 | SIMPLE      | b     | index | IX_S          | IX_S | 195     | NULL     | 1038165 | Using index ||  1 | SIMPLE      | a     | ref   | IX_L,IX_S     | IX_S | 195     | test.b.S |       1 | Using where |+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+

分析

从explain的结果看，查询用了b作为驱动表。上一篇文章我们介绍到，MySQL选择jion顺序是分别分析各种join顺序的代价后，选择最小代价的方法。这个join只涉及到两个表，自然也与optimizer_search_depth无关。于是我们的问题就是，我们预期的那个join顺序的为什么没有被选中？MySQL Tips: MySQL提供straight_join语法，强制设定连接顺序。 explain SELECT count(*)  FROM  a  straight_join  b ON  a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ;            +----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra                                       |+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+|  1 | SIMPLE      | a     | range | IX_L,IX_S     | IX_L | 4       | NULL |      63 | Using where                                 ||  1 | SIMPLE      | b     | index | IX_S          | IX_S | 195     | NULL | 1038165 | Using where; Using index; Using join buffer |+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+

MySQL Tips: explain结果中，join的查询代价可以用依次连乘rows估算。

join顺序对了，简单的分析查询代价：普通join是1038165*1, straight_join是 63*1038165. 貌似MySQL没有错。但一定哪里不对！

发现异常

回到我们最初的设想。我们预计表a作为驱动表，是因为认为表b能够用上IX_S索引，而实际上staight_join的时候确实用上了，但这个结果与我们预期的又不同。我们知道，索引的过滤性是决定了一个索引在查询中是否会被选中的重要因素，那么是不是b.S的过滤性不好呢？

MySQL Tips: show index from tbname返回结果中Cardinality的值可以表明一个索引的过滤性。

show index的结果太多，也可以从information_schema表中取。mysql> select * from information_schema.STATISTICS where table_name='b' and index_name='IX_S'\G*************************** 1. row ***************************TABLE_CATALOG: def TABLE_SCHEMA: test   TABLE_NAME: b   NON_UNIQUE: 1 INDEX_SCHEMA: test   INDEX_NAME: IX_S SEQ_IN_INDEX: 1  COLUMN_NAME: S    COLLATION: A  CARDINALITY: 1038165 SUB_PART: NULL       PACKED: NULL     NULLABLE: YES   INDEX_TYPE: BTREE      COMMENT: INDEX_COMMENT: 可以这个索引的CARDINALITY: 1038165，已经很大了。那这个表的估算行是多少呢。show table status like 'b'\G*************************** 1. row ***************************           Name: b         Engine: InnoDB        Version: 10     Row_format: Compact           Rows: 1038165 Avg_row_length: 114    Data_length: 119160832Max_data_length: 0   Index_length: 109953024      Data_free: 5242880 Auto_increment: NULL    Create_time: 2014-05-23 00:24:25    Update_time: NULL     Check_time: NULL      Collation: utf8_general_ci       Checksum: NULL Create_options:         Comment: 1 row in set (0.00 sec)从Rows: 1038165看出，IX_S这个索引的区分度被认为非常好，已经近似于唯一索引。MySQL Tips: 在show table status结果中看到的Rows用于表示表的当前行数。对于MyISAM表这是一个精确值，但对InnoDB这是个估算值。 虽然是估算值，但优化器是以此为指导的，也就是说，上面的某个explain里面的数据完全不符合期望：staight_join结果中第二行的rows。

目前为止

我们发现整个错误的逻辑是这样的：以a为驱动表的执行计划，由于索引b.S的rows估计为1038165导致优化器认为代价大于以b为驱动表。而实际上这个索引的区分度为1.（当然对explan结果比较熟悉的同学会发现，第二行的type字段和Extra字段一起诡异了)也就是说，straight_join得到的每一行去b中查询的时候，都走了全表扫描。在MySQL里面出现这种情况的最常见的是类型转换。比如一个字符串字段，虽然包含的是全数字，但查询的时候传入的不是字符串格式。在这个case里面，两个都是字符串。因此，就是字符集相关了。回到两个表结构，发现S字段的声明差别在于 COLLATE utf8_bin -- 这个就是本case的根本原因了：a表得到的S值是utf8_bin，优化器认为类型不同，无法直接用上索引b.IX_S过滤。至于为什么还会用上索引，这个是因为覆盖索引带来"误解"。

MySQL Tips：若查询的所有结果能够从某个索引完全得到，则会优先用遍历索引替代遍历数据。

作为验证，mysql> explain SELECT *  FROM  a  straight_JOIN  b ON  binary a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ;

+--+-----+---+---+-----+--+---+--+---+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+--+-----+---+---+-----+--+---+--+---+----------------+
| 1 | SIMPLE | a | range | IX_L | IX_L | 4 | NULL | 63 | Using where |
| 1 | SIMPLE | b | ALL | IX_S | NULL | NULL | NULL | 1038165 | Range checked for each record (index map: 0x4) |
+--+-----+---+---+-----+--+---+--+---+----------------+
由于结果是select *， 无法使用覆盖索引，因此第二行的key就显示为NULL. (笔者泪：要是早出这个结果查起来可方便多了）。

优化

当然最直接的想法就是修改两个表的S字段的定义，改成相同即可。这个方法可以避免修改业务代码，但DDL代价略大。这里提供两种在SQL语句方面的优化。1、select count(*) from  b join (select s from  a  WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00') ta on b.S=ta.s;这个写法比较直观，需要注意最后b.S和ta.S的顺序2、SELECT count(*)  FROM  a  JOIN  b ON  binary a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ;从前面的分析知道是由于b.S定义为utf8_bin.MySQL Tips: MySQL中字符集命名规则中, XXX_bin与XXX的区别为大小写是否敏感。这里我们将A.s全部增加binary限定，先转为小写，就是将临时结果集转成utf8_bin，之后使用b.S匹配时就能够直接利用索引。其实两个改写方法的本质相同，区别是写法1是隐式转换。理论上说写法2速度更快些。

以上是"mysql中慢查询优化的示例分析"这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注行业资讯频道！