关于一个用户SQL慢查询问题的分析及优化-阿里云开发者社区

开发者社区> 阿里云柳璃> 正文

关于一个用户SQL慢查询问题的分析及优化

简介:
+关注继续查看

问题描述

一个用户反映先前一个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: 0×4) |

+—-+————-+——-+——-+—————+——+———+——+———+————————————————+

由于结果是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速度更快些。

小结

做join的字段尽量设计为类型完全相同。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Elasticsearch-doc基本操作
document是es中比较重要的一个概念,相当于数据库中的行数据,es可以对document进行操作,主要包括新增doc、删除doc、更新doc、更新doc的某个部分等。
4 0
PHP与Mysql环境搭建
小白还是使用小皮面板或者宝塔面板之类的吧,当然你也可以自己去搭建一个环境,笔者学习的环境是小皮面板:https://www.xp.cn/
8 0
阿里云数据库:帮用户承担一切数据库风险|学习笔记
快速学习 阿里云数据库:帮用户承担一切数据库风险
4 0
声明式事务讲解| 学习笔记
快速学习声明式事务讲解
6 0
阿里云云服务器 ECS 介绍|学习笔记
快速学习 阿里云云服务器 ECS 介绍
5 0
ssm 整合| 学习笔记
快速学习 ssm 整合
11 0
案例分享——钉钉|学习笔记
快速学习 案例分享——钉钉
8 0
spring 整合 mybatis 03| 学习笔记
快速学习 spring 整合 mybatis 03
5 0
Spring 整合 mybatis04| 学习笔记
快速学习 Spring 整合 mybatis04
5 0
声明式事务| 学习笔记
快速学习声明式事务
6 0
282
文章
25
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载