Mysql中unionall的max下推案例

mingjie

发布于 2022-05-12 09:50:06

7970

长求总 max …(union all)中max无法自动下推，要跟库业务场景手动下推。改造为 Max…(union all max…union all…max…)

1 构造测试数据

/home/mingjie.gmj/bin/sysbench-1.0.16/bin/sysbench oltp_common --threads=64 --events=0 --mysql-socket=xxx --mysql-user=xxx --mysql-db=xxx --tables=2 --table_size=2000000  prepare

select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=utf8mb4;

2 问题复现

select count(*) from sbtest1 where k<1000000;
+----------+
| count(*) |
+----------+
|   750243 |
+----------+
explain select count(*) from sbtest1 where k<1000000;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: range
possible_keys: k_1
          key: k_1
      key_len: 4
          ref: NULL
         rows: 960984
     filtered: 100.00
        Extra: Using where; Using index

union的一个条件返回大量数据

explain select max(un.m) from
(select k as m from sbtest1 where k<1000000
union all
select k as m from sbtest2 where k=1000000) un;
+----+-------------+------------+------------+-------+---------------+------+---------+-------+--------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+------+---------+-------+--------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL | NULL    | NULL  | 961088 |   100.00 | NULL                     |
|  2 | DERIVED     | sbtest1    | NULL       | range | k_1           | k_1  | 4       | NULL  | 960984 |   100.00 | Using where; Using index |
|  3 | UNION       | sbtest2    | NULL       | ref   | k_2           | k_2  | 4       | const |    104 |   100.00 | Using index              |
+----+-------------+------------+------------+-------+---------------+------+---------+-------+--------+----------+--------------------------+

0.32649700s

优化

手动下推max

explain select max(un.m) from
(select max(k) as m from sbtest1 where k<1000000
union all
select max(k) as m from sbtest2 where k=1000000) un;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                         |
|  2 | DERIVED     | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
|  3 | UNION       | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

0.00045525s