mysql 慢查询过多_MySQL慢查询优化之多范围查询优化

慢查询分析

笔者在开发中有时候会遇到多范围查询，举一个相似的例子，比如查询2019年注册的18-25岁的年轻用户，查询sql如下所示

SELECT

COUNT(*)

FROM

tb_user

WHERE

age BETWEEN 18

AND 25

AND register_time BETWEEN 20190101

AND 20191231

上述涉及的tb_user这张表是笔者在开发环境中自己创建的表，表中总共存在1000万数据量，在上述查询涉及的age和register_time上均已经创建了索引，使用explain分析慢查询，发现mysql使用了如下查询策略

+----+-------------+--------------+------------+-------+-----------------------------------------------------+------------------------+---------+------+---------+----------+-----------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------------+------------+-------+-----------------------------------------------------+------------------------+---------+------+---------+----------+-----------------------------------------------+

| 1 | SIMPLE | tb_user | NULL | range | age_idx,register_time_idx | age_idx | 4 | NULL | 2245240 | 100.00 | Using index condition; Using where; Using MRR |

+----+-------------+--------------+------------+-------+-----------------------------------------------------+------------------------+---------+------+---------+----------+-----------------------------------------------+

因为mysql在执行查询的时候只会使用一个索引，所以虽然在age和register_time上均已经创建了索引，mysql查询优化器也只使用了age_idx索引，在Extra列中涉及的Using index condition指的是mysql先进行了age_idx索引条件过滤，而涉及的Using MRR指的是mysql进行索引过滤后取得一批id再回表查找。

笔者随后创建了age和register_time的复合索引，笔者希望mysql在执行查询的时候首先会对age进行范围索引扫描，然后在register_time上进行范围索引扫描所以笔者将register_time索引作为复合索引的第二列索引。

笔者再次执行上述查询，发现mysql没有使用新创建的age_register_time_idx，笔者认为mysql查询优化器使用了错误的索引，于是笔者添加FORCE INDEX强制mysql使用age_register_time_idx索引。随后笔者再次执行查询，发现mysql执行仍然非常缓慢。使用Explain分析后发现Extra列有如下信息

Using where; Using index;

Using index;说明了mysql使用了age_register_time_idx进行了条件过滤，但是Using where;说明mysql没有使用age_register_time_idx组合索引的register_time部分。查询资料后发现原因是mysql不支持松散索引扫描。也就无法实现从第一个范围age开始扫描到第一个范围age结束，然后扫描第二个register_time范围开始和第二个register_time范围结束。

思考之后，笔者有如下三种优化思路

优化方法一使用子查询

Mysql不支持松散索引扫描，每一个查询都只能使用一个索引进行范围扫描，那么我们是否可以将上述的查询拆分为两个子查询，其中一个子查询使用age索引进行范围扫描，而另一个子查询使用register_time索引进行范围扫描，然后取这两个子查询的交集id呢，然后在利用id回表查找用户信息。

SELECT

count( * )

FROM

tb_user

WHERE

id IN (

SELECT

tb1.id

FROM

( SELECT id FROM tb_user FORCE INDEX ( `age_idx` ) WHERE age BETWEEN 18 AND 25 ) tb1

INNER JOIN

( SELECT id FROM tb_user FORCE INDEX ( `register_time_idx` ) WHERE register_time BETWEEN 20190101 AND 20191231 ) tb2

ON tb1.id = tb2.id

)

笔者发现这个查询性能非常慢，执行时长超过了30秒。

笔者发现这个查询性能非常慢，执行时长超过了30秒。这种优化方式失败，原因是因为满足条件的tb1和满足条件的tb2数据量都非常大，对这样的大的临时表取交集性能自然就非常的差。

优化方式二之使用散列值

Mysql不支持松散索引扫描，所以优化的思路是将多个范围查询优化为一个范围查询。对于上述这个例子来说，我们可以将age字段使用in来代替，如此便可以避免其中一个字段的范围查询

SELECT

COUNT(*)

FROM

tb_user

WHERE

age IN ( 18, 19, 20, 21, 22, 23, 24, 25 )

AND register_time BETWEEN 20190101 AND 20191231

优化之后，该查询使用了age_register_time_idx索引，该查询耗时为100ms左右。Explain分析如下

+----+-------------+--------------+------------+-------+-----------------------------------------------------+------------------------+---------+------+-------+----------+--------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------------+------------+-------+-----------------------------------------------------+------------------------+---------+------+-------+----------+--------------------------+

| 1 | SIMPLE | tb_user | NULL | range | age_idx,register_time_idx,age_register_time_idx | age_register_time_idx | 8 | NULL | 16940 | 100.00 | Using where; Using index |

+----+-------------+--------------+------------+-------+-----------------------------------------------------+------------------------+---------+------+-------+----------+--------------------------+

上述的Explain分析有一个奇怪的地方在于Extra信息中涉及到where，按道理讲mysql count在索引就可以完成，没有必要回表，难道是mysql只使用了组合索引的前半部分age么？笔者强制上述查询使用age索引进行了验证，如果查询使用age索引和查询使用age_register_time_idx索引性能一样说明mysql只使用了组合索引age_register_time的前半部分。

mysql> SELECT

-> count(*)

-> FROM

-> tb_user FORCE INDEX(`age_idx`)

-> WHERE

-> age IN ( 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25 )

-> AND register_time BETWEEN 20190101 AND 20191231;

+----------+

| count(*) |

+----------+

| 16940 |

+----------+

1 row in set (6.76 sec)

mysql> SELECT

-> count(*)

-> FROM

-> tb_user FORCE INDEX(`age_register_time_idx`)

-> WHERE

-> age IN ( 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25 )

-> AND register_time BETWEEN 20190101 AND 20191231;

+----------+

| count(*) |

+----------+

| 16940 |

+----------+

1 row in set (0.01 sec)

优化方式三之使用冗余字段

冗余字段是解决慢查询的利器，上述的业务是查询2019注册的年轻用户的总数。我们也可以利用两个冗余字段，比如加入一个age_type字段，1代表年轻用户，2代表中年用户，3代表老年用户。同时创建一个age_type和register_time的复合索引，需要注意的是mysql不支持松散索引扫描，所以要将范围扫描的register_time字段放在组合索引的后半部分。查询语句如下所示

SELECT

COUNT(*)

FROM

tb_user

WHERE

age_type = 1

AND register_time BETWEEN 20190101 AND 20191231

虽然冗余字段为我们带来了便利，但是也为我们带来了管理上的麻烦。我们如何维护age_type字段呢？MySQL自带的触发器是一个比较好的方法，在age字段被更新的时候，触发器同时更新age_type字段。但是触发器可维护性比较差，我们也可以在业务层面手动维护age_type。这个业务需求实时性不是很高，我们可以开启一个异步线程每天凌晨扫描一遍表，更新错误的age_type字段。