【特性指南】分区表查询增强特性用户指南

特性介绍

分区表查询增强特性是针对使用生成列作为分区键的表，当查询语句未提供分区键条件，但提供了作为分区键的生成列所有base列的等值条件，优化器可以选择目标分区查询，无需全分区扫描，从而优化查询性能。可以帮助客户在不改动业务sql语句的前提下，对单个大表进行分区改造，提升业务sql查询性能，灵活扩展业务。下面给出一个具体的例子。

对于表和查询：

                    
                    Copied!
                
1Table 1:
2CREATE TABLE t1 (
3 a INT,
4 b INT,
5 c INT,
6 d INT GENERATED ALWAYS AS (a+b+c) STORED,
7 PRIMARY KEY(a,d)
8)
9PARTITION BY HASH (d)
10PARTITIONS 6;
11 
12Query 1: 
13SELECT * FROM t1 WHERE a = 4 AND b=10 AND c=11;

Table 1使用生成列（d）作为分区键，Query 1未提供分区键（d）条件，但提供了作为分区键的生成列（d）所有base列（a，b，c）的等值条件。

引入该特性后，执行计划如下：

• 开启特性:

                    
                    Copied!
                
1mysql> SET optimizer_switch='gen_col_partition_prune=on';
2mysql> explain SELECT * FROM t1 WHERE a = 4 AND b=10 AND c=11;
3+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+----------+-------+
4| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref         | rows | filtered | Extra |
5+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+----------+-------+
6|  1 | SIMPLE      | t1    | p1         | const | PRIMARY       | PRIMARY | 8       | const,const |    1 |   100.00 | NULL  |
7+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+----------+-------+
81 row in set, 1 warning (0.00 sec)

• 关闭特性：

                    
                    Copied!
                
1mysql> SET optimizer_switch='gen_col_partition_prune=off';
2mysql> explain SELECT * FROM t1 WHERE a = 4 AND b=10 AND c=11;
3+----+-------------+-------+-------------------+------+---------------+---------+---------+-------+------+----------+-------------+
4| id | select_type | table | partitions        | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
5+----+-------------+-------+-------------------+------+---------------+---------+---------+-------+------+----------+-------------+
6|  1 | SIMPLE      | t1    | p0,p1,p2,p3,p4,p5 | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
7+----+-------------+-------+-------------------+------+---------------+---------+---------+-------+------+----------+-------------+
81 row in set, 1 warning (0.00 sec)
9 
10 

可以看出，引入分区表查询增强特性后，对于Query 1这类查询，优化器可以选择单个目标分区查询，无需全表扫描，从而提升整体查询性能。

特性开关

通过参数optimizer_switch开关控制，分为session级别和global级别，用户可以根据业务需要，在控制台修改，立即生效，无需重启。

• 开启：SET optimizer_switch='gen_col_partition_prune=on';
• 关闭：SET optimizer_switch='gen_col_partition_prune=off';

默认为关闭状态。

特性说明

支持场景：

• 支持分区键包含单个、多个生成列、生成列和正常列混合的场景；
• 支持分区表子分区键包含生成列的场景；
• 分区键包含多个生成列时，对于这些列，查询条件只需要包含未提供的生成列的base列，例如：分区键（c，d），c的base列为a，d的base列为b，以下查询条件均支持：WHERE c=1 AND b=1，WHERE a=1 AND b=1；

使用约束：

• 仅支持生成列所有base列的等值条件场景，其他条件暂不支持；
• 不支持等值条件存在隐式转换，例如：WHERE a='1', 但a是INT类型的base列;
• 作为分区键的生成列，必须为STORED形式，如果VIRTUAL形式的生成列作为分区键，暂时不支持；
• 作为分区键的生成列，如果存在VIRTUAL形式的base列，暂时不支持；
• 作为分区键的生成列，如果存在string类型的base列，只有这些base列的collation为binary类型时，才支持，否则暂不支持；
• 以上不支持的场景，优化器行为同社区版MySQL；

场景举例

客户在使用数据库时，经常遇到这种问题：

• 随着业务系统的演进，以及业务负载的升高，对于数据量有持续增长的表，其查询性能，可能会随着数据量的增加而出现劣化，但是由于系统已经上线很长时间，重新规划设计表结构对业务系统的侵入非常大，此时，往往希望在尽量不改动业务sql语句的前提下，提升业务sql查询性能，例如对单个大表进行水平拆分，分区改造。
• 对于某些业务场景，希望对一些表按照某一规则进行分类或分批清理，例如时间段、工厂类型等。直接在表上清理，会影响查询和更新效率，采用分区表效率高，但对于不包含分区键的查询，性能往往不尽如人意；

分区表查询增强特性针对上述场景，有较好的解决方案，下面给出示例。

单个大表进行分区改造，业务sql无需改动

业务原始表结构和查询语句如下：

                    
                    Copied!
                
1Table 2:
2CREATE TABLE t1 (
3    id varchar(20) NOT NULL, // id中包含日期子串
4    plant varchar(6) NOT NULL, // 工厂名
5    content varchar(20) NULL,
6    PRIMARY KEY (id)
7);
8 
9Query 2:
10select * from t1 where id='ABC20210109-abzxxxx' AND plant='H';

业务述求：Table 2数据量有大量增长，部分查询性能出现劣化，希望在尽量不改动业务sql语句的前提下，提升业务sql查询性能，同时对数据有过期删除功能(根据工厂名，按周或按月清理)，从而对表的数据进行有效管理。

引入该特性后，可以对客户表进行如下改造：

• 新增STORED形式的生成列plant_week，其base列为plant和id，其值含义为每条记录对应的工厂和周次，然后以plant_week为分区键，进行分区。

                    
                    Copied!
                
1Table 3:
2CREATE TABLE t1 (
3    id varchar(20) NOT NULL,
4    plant varchar(6) NOT NULL,
5    plant_week varchar(20) GENERATED ALWAYS AS (concat(plant, week(substring(id, 4, 8)))) STORED,
6    content varchar(20) NULL,
7    PRIMARY KEY (id, plant_week)
8) PARTITION BY LIST COLUMNS(plant_week) (
9    PARTITION p1 VALUES in ('H1'),
10    PARTITION p2 VALUES in ('H2'),
11    PARTITION p3 VALUES in ('H3'),
12    ...
13    ...
14    PARTITION p5 VALUES in ('H52')
15);

客户表改造后，可以很好实现业务述求：

1. 原业务sql Query 2无需改动，可以实现在Table 3的指定分区查询记录，即只在相应工厂和周次查询记录，Query 2搜索的数据量显著降低，查询性能提升；
2. 客户可以通过删除指定分区的方式，实现过期删除功能(根据工厂名，按周清理)，且删除分区时，对其他分区数据的更新和查询操作几乎无影响；

修订记录