欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

一个优化MySQL查询操作的具体案例分析

程序员文章站 2024-02-28 13:33:34
问题描述 一个用户反映先线一个sql语句执行时间慢得无法接受。sql语句看上去很简单(本文描述中修改了表名和字段名): select count(*) from a j...

问题描述

一个用户反映先线一个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速度更快些。

小结

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