列存索引统计信息查询优化_云原生关系型数据库 PolarDB-阿里云帮助中心

执行查询语句时，您可以通过列存索引的pruner功能，过滤掉数据库中不需要访问的数据块，以提高SQL语句的查询速度。本文介绍pruner的适用场景、注意事项、语法和相关参数等内容。

简介

列存索引数据以单列数据块（默认包含64K行，可以通过表的总行数/64K来估算数据块数量）粒度存储，扫描数据时需要遍历指定列的所有数据块，并根据过滤条件来获取满足条件的数据。查询数据量比较大的表时，扫描代价很大，如果表中的数据不能全部放在内存中，扫描代价会进一步加大。事实上，您可以通过访问统计信息，再结合特定的过滤条件来过滤掉不需要访问的数据块以加快查询速度。在PolarDB列存索引中，这种方法称之为pruner。

Pruner有以下两种类型：

bloom filter
利用BIT数组表示一个集合，并且可以判断某个元素是否属于这个集合。目前仅支持字符串类型的字段。列存索引不会默认构建bloom filter，您可以通过DDL语句为指定的列构建bloom filter。
minmax indexes
用于统计数据块的最值。利用过滤条件与最值进行比较，从而判断是否需要扫描数据块。
- 对于数值类型的数据（如int、decimal和datetime等），列存索引会默认构建minmax indexes。
- 对于字符串类型的数据（String），列存索引不会默认构建minmax indexes，您可以通过DDL语句为指定的列构建minmax indexes ，并且可以通过参数来调整前缀的长度。
说明集群版本为PolarDB MySQL版8.0.1版本且修订版本为8.0.1.1.32及以上版本时，支持使用字符串类型的minmax indexes。

适用场景

Bloom filter适用于等值条件以及IN条件，对于过滤性较强的等值条件，具有较好的过滤效果。
Minmax indexes适用于对列数据分布有较好的局部性的场景，对于范围过滤条件和等值过滤条件具有较好的过滤效果。

存储开销

对于字符串类型的数据，开启列存索引pruner查询优化功能后会占用一定的内存空间，您可以根据适用场景，选择为指定的列构建bloom filter或minmax indexes。bloom filter和minmax indexes内存占用计算公式如下：

bloom filter
- 默认数据块为64 KB，distinct值占总行数的比例大于3%时。计算公式如下：
  占用内存=1.2*构建bloom filter的列数*表行数（单位：Byte）
  例如，表行数为20亿，对其中10列构建bloom filter，则占用的最大内存为24 GB。
- 默认数据块为64 KB，distinct值占总行数的比例小于等于3%时。计算公式如下：
  占用内存=1.2*构建bloom filter的列数*distinct值个数（单位：Byte）
  该场景下，列存索引会对数据进行优化，占用内存较少。但bloom filter过滤效果依赖数据的局部性，对于数据均匀分布的场景效果较差。
minmax indexes
占用内存=2*构建minmax indexes的列数*（表行数/数据块大小）*前缀长度*字符集编码长度
例如，表行数为20亿，对其中10列构建minmax indexes，前缀长度为20，数据块大小为64 KB，采用默认字符集utf8mb4（编码长度为4），则占用的内存约为46 MB。

说明 pruner会常驻内存，如果有构建pruner的需求，建议您评估集群内存规格，必要时适当升级集群内存规格，以避免因内存溢出影响集群的正常使用。

注意事项

系统会默认为int、decimal和datetime等数值类型的数据构建minmax indexes，且支持在执行查询操作时关闭pruner。
不支持为int、decimal和datetime等数值类型的数据构建bloom filter。
对于字符串类型的数据，默认不会构建任何pruner，如果需要为字符串类型的数据构建pruner，您需要通过DDL语句为指定的列构建pruner，且需要确保字符串中不显式包含'\0'，如'polar\0db'。

语法说明

您可以在创建表的同时构建pruner，也可以在已创建的表上构建或删除pruner。在已创建的表上构建或删除pruner时，需要先删除列索引，然后重建列索引。语法如下：

创建表时构建pruner（bloom filter）

为表中所有字符串类型的字段构建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";

说明集群版本为PolarDB MySQL版8.0版本且修订版本为8.0.1.1.32及以上版本时，支持使用PRUNER_BLOOM属性。

创建表时构建pruner（minmax indexes）

由于字符串类型的字段长度可能会非常长，为了减少pruner占用的内存空间，系统默认截取字符串类型字段的前20个字符，最多255个字符与最值进行比较。

说明字符个数与编码长度无关，例如：“阿里云PolarDB”的前2个字符为”阿里“，前5个字符为”阿里云Po"。

为表中所有字符串类型的字段构建minmax indexes。示例如下：

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个字符*/

为表中的某列构建minmax indexes。示例如下：

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";

说明集群版本为PolarDB MySQL版8.0版本且修订版本为8.0.1.1.32及以上版本时，支持使用PRUNER_MINMAX和PREFIX_LEN属性。

创建表时同时构建bloom filter和minmax indexes

为表中所有字符串类型的字段同时构建bloom filter和minmax indexes。

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 indexes。

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";

说明

集群版本为PolarDB MySQL版8.0版本且修订版本为8.0.1.1.32及以上版本时，会同时构建bloom filter和minmax indexes。
集群版本为PolarDB MySQL版8.0版本且修订版本为8.0.1.1.32以下版本时，仅构建bloom filter。

在已创建的表上构建pruner

假设原表结构如下：

       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'

为表t1中所有字符串类型的字段构建pruner，操作步骤如下：

执行以下命令，删除表t1上的列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```

执行以下命令，查看删除列索引后的表结构。

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'

执行以下命令，为表t1中所有字符串类型的字段构建pruner。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=1 PRUNER=1";
```

执行以下命令，查看构建pruner后的表结构。

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'

由以上表结构可以看出，表t1中已添加PRUNER属性。

为表t1中的str_col1列构建pruner，操作步骤如下：

执行以下命令，删除表t1上的列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```

执行以下命令，查看删除列索引后的表结构。

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'

执行以下命令，为str_col1列构建pruner。

ALTER TABLE t1 MODIFY COLUMN str_col1 char(10) COMMENT 'PRUNER=1';

执行以下命令，查看构建pruner后的表结构。

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'

执行以下命令，为表t1重建列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=1";
```

执行以下命令，查看重建列索引后的表结构。

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'

由以上表结构可以看出，已为str_col1列添加PRUNER属性。

删除pruner

删除表上的PRUNER属性。

假设表t1的表结构如下：

      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'

删除表t1中的PRUNER属性，操作步骤如下：

执行以下命令，删除表t1上的列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```

执行以下命令，查看删除列索引后的表结构。

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'

执行以下命令，重建列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=1";
```

执行以下命令，查看重建列索引后的表结构。

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'

由以上表结构可以看出，表t1中的PRUNER属性已被删除。

删除列上的PRUNER属性。

假设表t1中的str_col1列上存在PRUNER属性。表结构如下：

       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'

删除str_col1列上的PRUNER属性，操作步骤如下：

执行以下命令，删除列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```

执行以下命令，查看删除列索引后的表结构。

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'

执行以下命令，删除str_col1列上的PRUNER属性。
```
ALTER TABLE t1 MODIFY COLUMN str_col1 char(10) COMMENT '';
```

执行以下命令，查看删除PRUNER属性后的表结构。

SHOW CREATE TABLE t1 FULL \G

查询结果如下：

       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'

执行以下命令，重建列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=1";
```

执行以下命令，查看重建列索引后的表结构。

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'

由以上表结构可以看出，str_col1列上的PRUNER属性已被删除。

参数说明

您需要在数据库中设置下表中参数的值，来开启或关闭pruner查询优化功能。


参数	说明
imci_enable_pruner	列存索引pruner查询优化控制开关。取值如下： ON（默认）：开启列存索引pruner查询优化功能。 OFF：关闭列存索引pruner查询优化功能。
imci_enable_str_minmax_pruner	是否需要为字符串类型的数据自动构建minmax indexes。取值如下： ON（默认）：为字符串类型的数据自动构建minmax indexes。 OFF：不为字符串类型的数据构建minmax indexes。

判断pruner对查询语句是否生效

您可以在执行查询语句前后，执行SHOW STATUE LIKE 'imci_pruner%'命令查看数据块过滤情况，以此来判断pruner对该查询是否生效。查询结果中的状态值说明如下：

imci_pruner_accepted：满足过滤条件的数据块数量。
imci_pruner_rejected：不满足过滤条件的数据块数量。

通过列存索引pruner功能跳过扫描的数据块数量=accepted数量+rejected数量

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

示例

以表t1为例，判断pruner对查询是语句是否生效。表t1的表结构如下：

       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个数据块，符合条件str_col1='polardb'的记录集中在其中一个数据块中，执行以下步骤，查看pruner是否对SELECT COUNT(1) FROM t1 WHERE str_col1='polardb'查询语句生效。

执行以下命令，查看当前的pruner状态信息。

SHOW STATUS LIKE  'imci_pruner%';

查询结果如下：

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| imci_pruner_accepted | 0     |
| imci_pruner_rejected | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)

执行以下命令，查询符合条件str_col1='polardb'的数量。

SELECT COUNT(1) FROM t1 WHERE str_col1='polardb';

查询结果如下：

+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

执行以下命令，再次查看pruner状态信息。

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，查询过程中跳过了9个数据块，以此判断出pruner对该查询生效。

性能测试

以包含1.2亿行数据的表为例，表中包含约1800个数据块，且集群内存为2核4 GB，表中字符串类型的字段col的distinct值为8000万，分别测试在该列构建bloom filter和不构建bloom filter两种场景下的查询性能。查询语句如下：

SELECT COUNT(1) FROM t1 WHERE col='xxx'

查询时间见下表：


构建bloom filter	不构建bloom filter
0.15s	18.6s

col='xxx'条件结合bloom filter过滤掉了绝大部分的数据块，实际的查询过程中仅需要扫描几个数据块，从而提升了查询性能。