2018-10-22

mysql

mysql优化实战-范围查询转点查询

反直觉优化

慢查询:

select *
    from layer_data.layers_push b
    where group_key='nopushtest'
    AND utype='10w1'
    AND dt>=20180620 AND dt<=20181020
    AND bucket_id=60

上述SQL执行时间约在2.10秒,通过查看执行计划,可以发现有一个异常的指标,预估扫描的行数(rows)为96W，而实际结果行数应该在几十条。(查询涉及到的列合起来是联合主键)
此外type为range，执行的是范围扫描。
查询计划:(explain exntended select ...)

+----+-------------+-------------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | layers_push | range | PRIMARY       | PRIMARY | 216     | NULL | 989236 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+------+--------+-------------+

快查询:（反直觉优化）

基于上面观察到预估行数异常，一个自然的思路就是引入一个很小的辅助表来帮助优化器减少扫描的行数。辅助表的主键与原表分布相同:

select b.*
from layer_data.layers__nopushtest a
join layer_data.layers_push b
 ON a.dt=b.dt
AND a.bucket_id=b.bucket_id
AND b.group_key='nopushtest'
AND b.dt between 20180620 AND 20181020
AND b.utype='10w1'
    where a.dt>=20180620 and a.dt<=20181020
    and a.utype='w1'
    and a.bucket_id=60

优化后,执行时间大约在0.05秒,快了40多倍。
优化后的执行计划:

+----+-------------+-------+--------+---------------+---------+---------+-----------------------------------+-------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                               | rows  | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------------+-------+--------------------------+
|  1 | SIMPLE      | a     | range  | PRIMARY       | PRIMARY | 38      | NULL                              | 23925 | Using where; Using index |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 216     | layer_data.a.dt,const,const,const |     1 | Using where              |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------------+-------+--------------------------+

执行流程:
第一步:范围扫描表a,得到连接键的所有可能取值;
第二步: 表b的type是eq_ref(等值连接,而且是唯一键),ref是layer_data.a.dt,const,const,const，预估扫描的行数变成只有1。

原理分析

慢查询的执行计划:

预估的行数有96w，执行计划是从dt为20180620的数据页顺序扫描到dt为20181020的数据页。

范围查询的扫描行数预估：

这里由于使用的mysql版本是5.6(可以通过select version();语句查看)，预估的流程:

取符合where条件的最左边的数据页及后续的8个页，再加上符合where条件的最右边的数据页,共计10个页进行采样;
根据上述采样结果，以及与总数据的采样比，估算出需要扫描的行数。

快查询的执行计划:

因为有辅助的小表帮忙，扫描的行数减少。执行计划是先范围扫描小表，得到所有连接键的取值后，用表b的索引挨个儿进行点查询（type是eq_ref，而不是range）,扫描的数据页大大减少。

range类型查询:

以下操作符会导致查询计划为range:
=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN (…)

符合直觉的优化

由于mysql对in有特殊的优化: 把范围查询转换为多次点查询。
此时虽然查询计划是range，但实际执行时候不会进行范围扫描，而会进行点查询。
因此也可以把查询改写成:

select   *
    from layer_data.layers_push b
    where group_key='nopushtest'
    AND utype='10w1'
    AND dt  in  (20180805,20180806,20180807,20180808,20180809,20180810,20180811,20180812,20180813,20180814,20180815,20180816,20180817,20180818,20180819,20180820,20180821,20180822,20180823,20180824,20180825,20180826,20180827,20180828,20180829,20180830,20180831
,20180901,20180902,20180903,20180904,20180905,20180906,20180907,20180908,20180909,20180910,20180911,20180912,20180913,20180914,20180915,20180916,20180917,20180918,20180919,20180920,20180921,20180922,20180923,20180924,20180925,20180926,20180927,20180928,20180929,20180930
,20181001,20181002,20181003,20181004,20181005,20181006,20181007,20181008,20181009,20181010,20181011,20181012,20181013,20181014,20181015,20181016,20181017,20181018,20181019,20181020,20181021,20181022,20181023,20181024,20181025,20181026,20181027)
    AND bucket_id=60

优点:
直观,速度最快。
缺点:
啰嗦，改写起来比较麻烦，只适用于能使用代码预处理sql的地方，而且有最大长度限制。

in查询的扫描行数预估

有两种方式:

精确计算(index_dive): 直接查询对应的行;
采样预估(index statistics): 采样10个数据页,通过索引的选择性统计数据，预估总行数。

具体采取哪种方式，由eq_range_index_dive_limit参数控制(默认是10)。
小于eq_range_index_dive_limit的时候，采用方法1，精确计算；
大于等于eq_range_index_dive_limit的时候，采用方法2, 采样预估。

执行计划耗时

可以通过命令查看某个查询的具体耗时及具体发生了哪些操作:

set profiling=1;
-- select * from xxx ;
show profiles;
show profile for query 1;
show profile all for query 1; -- cpu,io等详细信息

这里可以看到之前的慢查询发生了51w个block io input。

优化器查询计划选择

可以通过命令查看某个查询生成执行计划时，考虑过哪些方案，以及对于where条件的等价变换，最终选择了哪个方案(chosen:true)。

set optimizer_trace="enabled=on";
SET optimizer_trace_max_mem_size=1000000;
explain select xxx
select * from information_schema.OPTIMIZER_TRACE\G

可以看到对于之前慢查询的查询条件只用到了dt条件，其他where条件是在服务器层进行过滤的。

笔记本

风梦七