列存索引统计信息如何进行查询优化_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

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

简介

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

bloom filter
利用BIT数组表示一个集合，并且可以判断某个元素是否属于该集合。
minmax indexes
用于统计数据块的最值。利用过滤条件与最值进行比较，从而判断是否需要扫描数据块。
token bloom filter
用于过滤字符串，按照非字母和非数字字符进行分割。如“I am IMCI”，将被分割为I |am|IMCI来存储，适用于LIKE模糊查询。
ngram bloom filter
用于过滤字符串，按照指定长度进行分割。如“我是列存数据库”，在指定ngram大小为3时将被分割为我是列|是列存|列存数|存数据|数据库来存储，适用于LIKE模糊查询。

适用场景

Bloom filter：适用于等值条件以及IN条件，对于过滤性较强的等值条件，具有较好的过滤效果。如使用字符串ID进行等值过滤。
Minmax indexes：适用于对列数据分布有较好的局部性的场景，对于范围过滤条件和等值过滤条件具有较好的过滤效果。如WHERE条件中带有日期或排序字段。
Token bloom filter和ngram bloom filter适用于LIKE模糊查询，来快速过滤未命中的数据块。

存储开销

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

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

注意事项

集群版本为PolarDB MySQL版8.0.1.1.32及以下或8.0.2.2.13及以下的版本时，对包含NULL值的数据块不会构建任何类型的pruner，并且不支持IS NULL或IS NOT NULL过滤条件。
集群版本为PolarDB MySQL版8.0.1.1.35及以上或8.0.2.2.16及以上的版本时，在创建列存索引时字符串类型的字段会默认构建pruner，且bloom filter使用LRU Cache（Least Recently Used Cache）进行内存管理。对于从低版本升级到8.0.1.1.35及以上或8.0.2.2.16及以上版本的集群，需要重建列存索引才会为字符串列构建pruner。
集群版本为PolarDB MySQL版8.0.1.1.34及以下或8.0.2.2.15及以下的版本时，构建的pruner会常驻内存。且在创建列存索引时字符串类型的字段不会默认构建pruner。
如果需要为字符串类型的字段构建pruner，则需要确保字符串中不包含'\0'，如'polar\0db'。
系统会默认为int、decimal和datetime等数值类型的数据构建minmax indexes。
不支持为JSON类型以及GEOMETRY类型的字段构建minmax。
不支持为数值类型字段（例如INT、DECIMAL、DATETIME等）、JSON、BLOB、TEXT类型的字段构建bloomfilter。

语法说明

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

说明

您可以通过DDL语句修改表的Schema中的COMMENT来为表中字符串类型的字段构建或删除pruner。
所有的列级别的COMMENT属性优先级均大于表级别的COMMENT属性。

创建表的同时构建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及以上或8.0.2.2.13及以上的版本时，支持使用PRUNER_BLOOM属性。

构建pruner（minmax indexes）
由于字符串类型的字段长度可能会非常长，为了减少minmax占用的内存空间，系统默认截取字符串类型字段的前20个字符，最多255个字符与最值进行比较。您可以通过PRUNER_MINMAX属性控制是否构建字符串minmax，通过PREFIX_LEN控制前缀长度。
说明
字符个数与编码长度无关，例如：“阿里云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及以上或8.0.2.2.13及以上的版本时，支持使用PRUNER_MINMAX和PREFIX_LEN属性。
构建token bloom filter
- 为表中所有支持的字段构建token bloom filter。示例如下：
```
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10),
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1 PRUNER_TOKEN_BLOOM=1";
```
- 为表中的某列构建token bloom filter。示例如下：
```
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10) "PRUNER_TOKEN_BLOOM=1",
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1";
```
说明
当集群版本满足以下条件时，支持使用PRUNER_TOKEN_BLOOM属性：
- 集群版本为PolarDB MySQL版8.0.1版本，且修订版本为8.0.1.1.39及以上。
- 集群版本为PolarDB MySQL版8.0.2版本，且修订版本为8.0.2.2.20及以上。
构建ngram bloom filter
- 为表中所有支持的字段构建ngram bloom filter。示例如下：
```
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10),
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1 PRUNER_NGRAM_BLOOM=2";
```
- 为表中的某列构建ngram bloom filter。示例如下：
```
CREATE TABLE
  t1 (
    id INT PRIMARY KEY,
    str_col1 char(10) "PRUNER_NGRAM_BLOOM=3",
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1";
```
说明
- 当集群版本满足以下条件时，支持使用PRUNER_TOKEN_BLOOM属性：
  - 集群版本为PolarDB MySQL版8.0.1版本，且修订版本为8.0.1.1.39及以上。
  - 集群版本为PolarDB MySQL版8.0.2版本，且修订版本为8.0.2.2.20及以上。
- PRUNER_NGRAM_BLOOM=N，当N大于等于2时，为分割长度。N一般推荐不小于LIKE "%字符串%" 中的字符长度。当LIKE "%字符串%" 中的字符长度小于N时，则无法使用ngram bloom filter。

在已创建的表上构建或删除pruner

在已创建的表上构建或删除pruner，都需要重新构建列索引，即先删除列索引，再重新构建列索引。在构建列索引之前，需要先增加或删除COMMENT中的pruner属性，此处以PRUNER_MINMAX属性为例进行说明。

构建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中所有字符串类型的字段构建minmax pruner，操作步骤如下：

执行以下命令，删除表t1上的列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```
执行以下命令，为表t1中所有字符串类型的字段构建minmax pruner。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=1 PRUNER_MINMAX=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_MINMAX=1'

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

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

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

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

按顺序执行以下命令，为表t1重建列索引。

ALTER TABLE t1 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 COMMENT 'PRUNER_MINMAX=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_MINMAX属性。

删除pruner

删除表上的PRUNER_MINMAX属性。

假设表t1的表结构如下：

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_MINMAX=1'

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

执行以下命令，删除表t1上的列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```
执行以下命令，将COMMENT中的PRUNER_MINMAX设置为0，并重建列索引。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=1 PRUNER_MINMAX=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`),
  COLUMNAR INDEX (`id`,`str_col1`,`str_col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'

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

删除列上的PRUNER属性。

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

       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `str_col1` char(10) DEFAULT NULL COMMENT 'PRUNER_MINMAX=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_MINMAX属性，操作步骤如下：

执行以下命令，删除str_col1列上的PRUNER_MINMAX属性。

ALTER TABLE t1 MODIFY COLUMN str_col1 char(10) COMMENT 'PRUNER_MINMAX=0';

按顺序执行以下命令，重建列索引。

ALTER TABLE t1 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_MINMAX属性已被删除。

查看pruner是否生效

查看表上的字符串类型的字段是否构建了pruner

您可以通过imci_secondary_indexes表中的STR_BLOOM_PRUNER和STR_MINMAX_PRUNER字段的状态值来判断表中的字符串列是否构建了pruner。当状态值为1时，表示创建了对应的pruner。示例如下：

SELECT * FROM information_schema.imci_secondary_indexes WHERE schema_name='test_tmp' AND table_name='t1'\G

查询结果如下：

*************************** 1. row ***************************
         TABLE_ID: 1091
      SCHEMA_NAME: test_tmp
       TABLE_NAME: t1
      COLUMN_NAME: str_col1
 STR_BLOOM_PRUNER: 1 --str_col1 建了bloom filter
STR_MINMAX_PRUNER: 1 --str_col1 建了minmax
    SINDEX_SWITCH: 0
*************************** 2. row ***************************
         TABLE_ID: 1091
      SCHEMA_NAME: test_tmp
       TABLE_NAME: t1
      COLUMN_NAME: str_col2
 STR_BLOOM_PRUNER: 1 --str_col2 建了bloom filter
STR_MINMAX_PRUNER: 1 --str_col2 建了minmax
    SINDEX_SWITCH: 0
2 rows in set (0.00 sec)

由以上查询结果可以看出：STR_BLOOM_PRUNER字段的状态值为1，表示对str_col1和str_col2构建了bloom filter。STR_MINMAX_PRUNER状态值为1，表示对str_col1和str_col2构建了minmax indexes。

查看构建的pruner对查询语句是否生效
您可以在执行查询语句前后，执行SHOW STATUS 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'查询语句生效。
1. 执行以下命令，查看当前的pruner状态信息。
```
SHOW STATUS LIKE  'imci_pruner%';
```
  查询结果如下：
```
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| imci_pruner_accepted | 0     |
| imci_pruner_rejected | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)
```
2. 执行以下命令，查询符合条件str_col1='polardb'的数量。
```
SELECT COUNT(1) FROM t1 WHERE str_col1='polardb';
```
  查询结果如下：
```
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)
```
3. 执行以下命令，再次查看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

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