PolarDB IMCI的统计信息查询优化技术

手机用户2895 2023-06-24

182

概述

列存数据以pack（单列数据块，默认包含64k行）粒度组织，扫描数据的方式是遍历指定列的所有pack，并根据过滤条件来获取满足条件的数据。由于需要列的所有pack，对于大表这个代价较高，如果数据不能全部放内存，将使得扫描代价进一步加大。事实上，通过访问统计信息，结合特定的过滤条件，可以提前过滤掉不需要访问的pack。类似于clickhouse的skipping index的概念，在PolarDB 列存索引中称之为Pruner。

功能介绍

目前，IMCI的pruner有两种类型，分别有各自的适用场景。
一种是bloom filter，它利用bit数组很简洁地表示一个集合，并能判断一个元素是否属于这个集合。目前仅支持string类型的字段，bloom filter默认不会构建，也可以像string的minmax前缀索引那样通过ddl指定某列是否构建bloom filter（全版本支持）。
另一种是minmax indexes，它统计pack的最值，然后用过滤条件与最值比较，从而判断是否要扫描pack。针对数值类型，例如int/decimal/date等，默认会构建minmax，而对于string类型，默认不会构建minmax，但可通过ddl（参考下文《SQL语法&参数》章节）指定某列是否构建minmax。并且，还可以通过参数控制前缀的长度。（1.1.32及以上版本支持）

适用场景

bloom filter 用于等值条件以及IN条件pruning，对于过滤性较强的等值条件，一般具有比较好的过滤效果。
minmax一般需要该列数据分布有较好的局部性，此时对于范围过滤条件以及等值过滤条件都具有较好的过滤效果。

性能提升

以线上实际案例说明，1.2亿数据表，2c4g规格。某字符串列col数据distinct值为8000万，对该列建立bloom filter。查询 select count(1) from t1 where col='xxx' 性能测试提升显著。

	构建bloom filter	不构建bloom filter
查询时间	0.15秒	18.6秒

解释：原始表包含约1800个pack，由于col='xxx'条件结合bloom filter可以过滤绝大部分pack，实际仅扫描少量pack，性能大大提升。

存储开销

对于字符串类型的pruner，开启后会带来一定的存储开销以及内存占用，建议根据其适用场景，指定合适的列进行构建。不同类型的pruner，其占用内存开销计算方式不同，按如下方式进行。

Bloom Filter

对于bloom filter，其内存开销为：
占用内存 = 1.2 * 构建bloom filter的列数 * 表行数（Byte）
例如，对于20亿数据的表，对其中10列构建bloom filter，则占用的最大内存约为24G。
以上计算方法是假设每列的distinct值比较多（在默认pack 64k大小下，distinct值占总行数>3%，对于20亿数据，该列的distinct值超过6000万）。如果distinct值较少（在默认pack 64k大小下，distinct值占总行数<= 3%），IMCI会进行优化，内存占用降低，内存开销为：
占用内存 = 1.2 * 构建bloom filter的列数 * distinct值个数（Byte）
不过此时，bloom filter过滤效果依赖数据的局部性，对于均匀分布的场景效果较差。

Min-Max

对于minmax，其存储开销可以按照如下公式进行估算：
**占用内存 = 2 * 构建minmax的列数 * 表行数/pack大小 * 前缀长度 * 字符集编码长度 **
例如，对于20亿数据的表，对其中10列构建minmax，前缀长度为20，pack大小为64k（默认），采用默认字符集utf8mb4（编码长度为4），则其占用的内存约为46MB。

注意事项

1.1.32及以下的版本对于包含NULL的pack，不会构建pruner（任何类型），因此字段中不应该包含大量的NULL，否则会影响pruner效果。
1.1.32及以下版本，pruner会常驻内存，因此如果有构建pruner的需求（特别是bloom filter），建议评估实例内存规格，必要时适当升级实例内存规格，否则可能影响正常使用。
1.1.32及以下的版本不支持 IS NULL/ IS NOT NULL 这类谓词的过滤条件。

SQL语法&参数

对于int/decimal/datetime等数值类型默认会构建minmax，不可更改，但可以在查询时关闭pruner。
bloom filter 不支持int/decimal/datetime等数值类型，仅支持字符串类型
字符串类型默认不会构建任何pruner，且需要确保字符串中不显示包含’\0’，如’polar\0db’

建表时添加pruner

同时添加bloom filter与minmax

comment中带pruner属性，会同时构建bloom filter与minmax。

-- 全表所有string字段构建bloom filter 与 minmax
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10),
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1 PRUNER=1"; /*表comment中带pruner属性*/


-- 指定某列构建bloom filter 与 minmax
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10) "PRUNER=1 ", /*列comment中带pruner属性*/
    str_col2 varchar(10) "PRUNER=1 " /*列comment中带pruner属性*/
  ) ENGINE InnoDB COMMENT "COLUMNAR=1";

通过该注释构建min-max索引只有1.1.32以上版本才支持，对于前面的版本，这种方式仅会构建bloom filter。

添加bloom filter

-- 全表所有string字段构建bloom filter
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10),
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1 PRUNER_BLOOM=1"; /*表comment中带pruner_bloom属性*/
  
-- 指定某列构建 bloom filter
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10) "PRUNER_BLOOM=1", /*列comment中带pruner_bloom属性*/
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1";

仅在1.1.32版本以上支持通过该注释构建bloom filter，在更早的版本，该注释将被忽略。

添加min-max pruner

由于字符串类型的字段长度可能非常大，为了降低pruner的开销，默认截取前20个字符作进行比较，最多截取255个字符（字符个数与编码长度无关，例如"阿里云Polardb"前2个字符是“阿里”，前5个字符是“阿里云Po”）。

-- 全表所有string字段构建min-max
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10),
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1 PRUNER_MINMAX=1 PREFIX_LEN=30"; /*表comment中带pruner_minmax属性，并指定前缀长度为30个字符*/

-- 指定某列构建 min-max
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10) "PRUNER_MINMAX=1 PREFIX_LEN=30", /*列comment中带pruner_minmax属性，并指定前缀长度为30*/
    str_col2 varchar(10) "PRUNER_MINMAX=1 PREFIX_LEN=10" /*列comment中带pruner_minmax属性，并指定前缀长度为10*/
  ) ENGINE InnoDB COMMENT "COLUMNAR=1";

PRUNER_MINMAX、PREFIX_LEN属性需要1.1.32以上版本，否则会忽略该属性。

现有表增加/删除pruner

对于现有表增加/删除pruner的操作，都需要重新构建列索引，即先删除列索引，再构建列索引。在构建列索引之前，需要把相关的pruner comment属性加上/去除，下面以PRUNER属性为例进行说明。

增加pruner

假设原始表结构如下，现在需要对全表所有字符串字段构建pruner:

mysql[test]>show create table t1 full \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

第一步，先删除已有的列索引

mysql[test]>alter table t1 comment = "COLUMNAR=0";
mysql[test]>show create table t1 full \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=0'

第二步：表comment增加pruner 属性，重建列索引

mysql[test]>alter table t1 comment = "COLUMNAR=1 PRUNER=1"; 
mysql[test]>show create table t1 full \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 PRUNER=1'

如果仅仅要对某一列增加列索引，如本例中的str_col1，第一步仍需要删除已有的列索引，第二部为对应的列添加pruner属性

mysql[test]>alter table t1 modify column str_col1 char(10) comment 'PRUNER=1';
mysql[test]>show create table t1 full \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL COMMENT 'PRUNER=1',
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=0'

第三步，重建列索引

mysql[test]>alter table t1 comment = "COLUMNAR=1";
mysql[test]>show create table t1 full \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL COMMENT 'PRUNER=1',
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

删除pruner

与增加列索引类似，只需要删去表/列上comment中相关pruner的属性即可
第一步与增加pruner相同，删除列索引

mysql[test]>alter table t1 comment = "COLUMNAR=0";
mysql[test]>show create table t1 full \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL COMMENT 'PRUNER=1',
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=0'

第二步，删除对应列上的pruner属性

mysql[test]>alter table t1 modify column str_col1 char(10) comment '';
mysql[test]>show create table t1 full \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=0'

第三步，重建列索引

mysql[test]>alter table t1 comment = "COLUMNAR=1";
mysql[test]>show create table t1 full \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

查询时开启/关闭pruner功能

可以根据需要，开启或关闭pruner查询优化。

参数	说明
imci_enable_pruner	列存索引pruner查询优化总开关。取值如下：

ON(默认)：开启pruner查询优化功能
OFF：关闭对应功能
|
| imci_enable_str_minmax_pruner | 列存索引string minmax查询优化独立开关。取值如下：
ON(默认)：开启pruner在字符串列上通过min-max进行查询优化的功能
OFF: 关闭对应功能

注意：无论该参数取值如何，只要imci_enable_pruner被设置为OFF，该功能都不会被开启 |

观察通过pruner加速的效果

在查询执行前后，可以通过执行show status like 'imci_pruner%'来观察pruner优化的效果

状态名	说明	粒度
imci_pruner_accepted	pruner预判为恒真的pack数目，即pack内数据全部满足过滤条件	会话级别
imci_pruner_rejected	pruner预判为恒假的pack数目，即pack内数据全部不满足过滤条件	会话级别

被accept的数据块，无需在每条记录上使用过滤条件进行过滤，如果部分列需要被物化，则仍然需要访问该数据块；被reject的数据块，直接被跳过，不会产生任何IO。
通过以下查询为例，表结构如下

mysql[test]>show create table t1 full \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL,
  `str_col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 PRUNER=1'

设表中包含10个pack，符合条件str_col1='polardb'的记录集中在一个pack中，则可以通过以下方式查看pruner是否对select count(1) from t1 where str_col1='polardb'这个查询生效:

-- 先查看当前的pruner status状态信息
mysql[test]>show status like  'imci_pruner%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| imci_pruner_accepted | 0     |
| imci_pruner_rejected | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)
-- 执行查询sql
mysql[test]>select count(1) from t1 where str_col1='polardb';
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

-- 再次检查pruner status状态信息
mysql[test]>show status like  'imci_pruner%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| imci_pruner_accepted | 0     |
| imci_pruner_rejected | 9     |
+----------------------+-------+
2 rows in set (0.00 sec)

-- imci_pruner_accepted增加了0， imci_pruner_rejected增加了9， 说明通过pruner，查询在执行时跳过了0+9=9个pack

默认一个pack包含64k条记录，可以通过总行数/64k 来估算总pack的数量。

polardb

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者