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

56次阅读
没有评论

共计 5839 个字符,预计需要花费 15 分钟才能阅读完成。

这篇文章主要介绍 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: 119160832
Max_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 中慢查询优化的示例分析”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注丸趣 TV 行业资讯频道!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-27发表,共计5839字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)