暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 最佳实践

最佳实践

胖橘 2024-11-12
374

本文将模拟业务生产环境，在大数据量环境下，针对单表以及多表查询效率低下的情况，如何通过列存索引（In-Memory Column Index，简称IMCI）来提升查询性能。

什么是列存索引

列存索引是将表的全部或部分列按照列式存储格式在PolarDB的只读节点上保存一份，形成行列混合存储。同时，针对查询优化器进行了优化，并新增面向列式存储的执行算子。这使得在处理大规模数据集时，数据分析和复杂查询的性能能够显著提高。更多信息，请参见什么是列存索引（IMCI）。

操作流程

准备工作

模拟业务生产环境场景，以便作为测试参考。

  • 集群
  • 产品版本：企业版。
  • 系列：集群版（独享规格）。
  • 内核版本：8.0.1.1.45.2。
  • 开启热备集群：开启存储热备集群。
  • 计算节点：32核256 GB (polar.mysql.x8.4xlarge)，一个主节点一个只读节点（热备）。
  • 存储类型：PSL5。
  • 参数模板：MySQL_InnoDB_8.0_标准版_默认参数模板。
  • 数据
  • 基于TPC-H基准测试，数据量100 GB。
-- 查询数据库内表的行数和大小。
+----------+----------+-----------+-----------------+
| Database | Table    | Rows      | Total Size (GB) |
+----------+----------+-----------+-----------------+
| tpch     | customer |  13179406 |            2.59 |
| tpch     | lineitem | 590446240 |           87.52 |
| tpch     | nation   |        25 |            0.00 |
| tpch     | orders   | 142929780 |           18.70 |
| tpch     | part     |  19354445 |            3.11 |
| tpch     | partsupp |  67862725 |           20.45 |
| tpch     | region   |         5 |            0.00 |
| tpch     | supplier |    986923 |            0.17 |
+----------+----------+-----------+-----------------+

说明

  • 数据库内表的行数和大小受到集群内数据库的索引、存储引擎、统计信息以及系统表等多种因素的影响。因此，实际输出结果与上述查询结果之间可能存在差异。
  • 本文的TPC-H的实现基于TPC-H的基准测试，并不能与已发布的TPC-H基准测试结果相比较，本文中的测试并不符合TPC-H基准测试的所有要求。

配置列存索引

添加一个只读列存节点，本文添加的节点规格与主节点相同，即32核256 GB (polar.mysql.x8.4xlarge)。具体信息，请参见添加一个只读列存节点。

单表查询

  1. 模拟业务中某条SQL执行效率低下的场景。在未创建列存索引的情况下执行下列单表查询SQL，并记录其执行时间。
--单表扫描及过滤
SELECT * FROM lineitem WHERE L_COMMENT > 'aaaaaaaa' AND L_COMMENT < 'aaaaaaz';
-- 执行结果
Empty set (8 min 47.29 sec)

-- 单列聚合（AGG）
SELECT SUM(L_DISCOUNT) from lineitem;

-- 执行结果
+-----------------+
| SUM(L_DISCOUNT) |
+-----------------+
| 30001636.44 |
+-----------------+
1 row in set (2 min 6.64 sec)

-- 分组聚合（GROUP BY）
SELECT AVG(L_DISCOUNT) FROM lineitem WHERE L_SHIPDATE <= date '1998-12-01' - interval '90' day GROUP BY L_RETURNFLAG, L_LINESTATUS;

-- 执行结果
+-----------------+
| AVG(L_DISCOUNT) |
+-----------------+
| 0.049998 |
| 0.050001 |
| 0.050002 |
| 0.049985 |
+-----------------+
4 rows in set (6 min 28.96 sec)

-- 极限深翻页性能 (ORDER BY+LIMIT)
SELECT L_ORDERKEY, SUM(L_QUANTITY) FROM lineitem GROUP BY L_ORDERKEY ORDER BY SUM(L_QUANTITY) DESC LIMIT 1000000, 100;

-- 执行结果
+------------+-----------------+
| L_ORDERKEY | SUM(L_QUANTITY) |
+------------+-----------------+
| 25226310 | 244.00 |
| ... | ... |
| 494738146 | 244.00 |
+------------+-----------------+
100 rows in set (12 min 24.22 sec)

2. 创建列存索引。具体信息，请参见创建列存索引。

ALTER TABLE lineitem COMMENT 'COLUMNAR=1 lineitem表注释';

-- 执行结果
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

3. 查看列存索引构建的执行进度，等待列存索引构建完成。具体信息，请参见查看列存索引构建的执行进度。

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;
-- 执行结果，列存索引构建中。
+-------------+------------+---------------------+---------------------+-------------+----------+------------------+--------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| SCHEMA_NAME | TABLE_NAME | CREATED_AT | STARTED_AT | FINISHED_AT | STATUS | APPROXIMATE_ROWS | SCANNED_ROWS | SCAN_SECOND | SORT_ROUNDS | SORT_SECOND | BUILD_ROWS | BUILD_SECOND | AVG_SPEED | SPEED_LAST_SECOND | ESTIMATE_SECOND |
+-------------+------------+---------------------+---------------------+-------------+----------+------------------+--------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| tpch | lineitem | 2024-10-21 13:44:02 | 2024-10-21 13:44:02 | | Building | 590446240 | 36718757(6%) | 19 | 0 | 0 | 0(0%) | 0 | 1848522 | 0 | 299 |
+-------------+------------+---------------------+---------------------+-------------+----------+------------------+--------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
1 row in set, 1 warning (0.00 sec)
-- 执行结果，列存索引构建完成。
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| SCHEMA_NAME | TABLE_NAME | CREATED_AT | STARTED_AT | FINISHED_AT | STATUS | APPROXIMATE_ROWS | SCANNED_ROWS | SCAN_SECOND | SORT_ROUNDS | SORT_SECOND | BUILD_ROWS | BUILD_SECOND | AVG_SPEED | SPEED_LAST_SECOND | ESTIMATE_SECOND |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| tpch | lineitem | 2024-10-21 13:44:02 | 2024-10-21 13:44:02 | 2024-10-21 13:50:11 | Safe to read | 590446240 | 600037902(100%) | 369 | 0 | 0 | 0(0%) | 0 | 1625058 | 0 | 0 |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

4. 在表lineitem创建列存索引情况下执行单表查询SQL，并记录执行时间。

-- 单表扫描及过滤
SELECT * FROM lineitem WHERE L_COMMENT > 'aaaaaaaa' AND L_COMMENT < 'aaaaaaz';
-- 执行结果
Empty set (1.47 sec)

-- 单列聚合（AGG）
SELECT SUM(L_DISCOUNT) from lineitem;
-- 执行结果
+-----------------+
| SUM(L_DISCOUNT) |
+-----------------+
| 30001636.44 |
+-----------------+
1 row in set (0.06 sec)

-- 分组聚合（GROUP BY）
SELECT AVG(L_DISCOUNT) FROM lineitem WHERE L_SHIPDATE <= date '1998-12-01' - interval '90' day GROUP BY L_RETURNFLAG, L_LINESTATUS;
-- 执行结果
+-----------------+
| AVG(L_DISCOUNT) |
+-----------------+
| 0.050001 |
| 0.050002 |
| 0.049985 |
| 0.049998 |
+-----------------+
4 rows in set (2.54 sec)

-- 极限深翻页性能 (ORDER BY+LIMIT)
SELECT L_ORDERKEY, SUM(L_QUANTITY) FROM lineitem GROUP BY L_ORDERKEY ORDER BY SUM(L_QUANTITY) DESC LIMIT 1000000, 100;
-- 执行结果
+------------+-----------------+
| L_ORDERKEY | SUM(L_QUANTITY) |
+------------+-----------------+
| 299074498 | 244.00 |
| ... | ... |
| 168679332 | 244.00 |
+------------+-----------------+
100 rows in set (12.80 sec)

5. 执行时间对比（单位：秒）。

查询类型

PolarDB 列存索引（IMCI）

PolarDB 行存

单表扫描及过滤

1.47

527.29

单列聚合（AGG）

0.06

126.64

分组聚合（GROUP BY）

2.54

388.96

极限深翻页性能 (ORDER BY+LIMIT)

12.80

744.22

可以观察到，在添加列存索引后，单表查询SQL的执行效率显著提高。

说明

以上数据仅作为评估SQL执行效率的参考基准，而非绝对标准。实际上，SQL的具体执行时间会受到多种因素的动态影响，这些因素包括但不限于集群的配置参数、当前的连接数、并发查询的数量，以及系统资源的实时负载状况。

多表查询及子查询

  1. 模拟业务中某条SQL执行效率低下的场景。当SQL中所需的表或列未被列存索引完全覆盖时，执行以下SQL并记录其执行时间。

说明

  • 在下列SQL中，仅表lineitem创建了列存索引，而其他表则未创建列存索引。
  • 当SQL所需的表或列未被列存索引完全覆盖时，列存索引将无法生效。
  • 若您不清楚当前SQL所需的表或列是否被列存索引完全覆盖，可以通过dbms_imci.check_columnar_index('');存储过程来检查SQL语句中的表是否已创建列存索引。同时，PolarDB为您提供相关存储过程用于获取创建列存索引的DDL语句和批量获取创建列存索引的DDL语句，助您快速创建列存索引。具体信息，请参见列存索引DDL辅助工具。

-- 多表关联 (JOIN) 性能
SELECT
  COUNT(l3.L_DISCOUNT)
FROM
  (
    (
      (
        (
          (
            nation n1 STRAIGHT_JOIN nation n2 on n1.N_NATIONKEY = n2.N_NATIONKEY
          )
          STRAIGHT_JOIN supplier on n2.N_NATIONKEY = supplier.S_NATIONKEY and S_SUPPKEY < 2000
        )
        STRAIGHT_JOIN lineitem AS l1 on l1.L_SUPPKEY = supplier.S_SUPPKEY
      )
      STRAIGHT_JOIN lineitem AS l2 on l1.L_ORDERKEY = l2.L_ORDERKEY and l1.L_LINENUMBER = l2.L_LINENUMBER
    )
    STRAIGHT_JOIN lineitem AS l3 on l2.L_ORDERKEY = l3.L_ORDERKEY and l2.L_LINENUMBER = l3.L_LINENUMBER
  )
GROUP BY 
  n1.N_NAME;

因查询超时（查询超时时间为7200秒），任务终止。因此，执行时间按超过7200秒进行记录。

-- 关联子查询性能
SELECT 
  O_ORDERPRIORITY, COUNT(*) as ORDER_COUNT 
FROM
  orders
WHERE
  O_ORDERDATE >= '1995-01-01' AND
  O_ORDERDATE < date_add('1995-01-01', interval '3' month) AND
  EXISTS 
    (
      SELECT * FROM lineitem WHERE L_ORDERKEY = O_ORDERKEY AND L_COMMITDATE < L_RECEIPTDATE
    )
GROUP BY 
  O_ORDERPRIORITY
ORDER BY
  O_ORDERPRIORITY;
-- 执行结果
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT | 1028353 |
| 2-HIGH | 1030059 |
| 3-MEDIUM | 1028615 |
| 4-NOT SPECIFIED | 1028496 |
| 5-LOW | 1029615 |
+-----------------+-------------+
5 rows in set (4 min 9.51 sec)
-- 多表关联带子查询性能
SELECT 
  C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, SUM(L_QUANTITY)
FROM 
  (
    SELECT * FROM orders WHERE O_ORDERKEY IN 
      (
        SELECT L_ORDERKEY FROM lineitem GROUP BY L_ORDERKEY HAVING SUM(L_QUANTITY) > 300
      ) 
  ) AS tmp, customer, lineitem 
WHERE 
  C_CUSTKEY = O_CUSTKEY AND 
  O_ORDERKEY = L_ORDERKEY
GROUP BY 
  C_NAME, 
  C_CUSTKEY, 
  O_ORDERKEY, 
  O_ORDERDATE, 
  O_TOTALPRICE
ORDER BY 
  O_TOTALPRICE DESC, 
  O_ORDERDATE;
-- 执行结果+--------------------+-----------+------------+-------------+--------------+-----------------+
| C_NAME             | C_CUSTKEY | O_ORDERKEY | O_ORDERDATE | O_TOTALPRICE | SUM(L_QUANTITY) |
+--------------------+-----------+------------+-------------+--------------+-----------------+
| Customer#011472112 |  11472112 |  458304292 | 1998-02-05  |    591036.15 |          322.00 |
|        ...         |     ...   |     ...    |     ...     |       ...    |            ...  |
| Customer#003777694 |   3777694 |  470363105 | 1997-04-06  |    349914.00 |          302.00 |
| Customer#009446411 |   9446411 |  592379937 | 1995-12-29  |    343496.05 |          304.00 |
+--------------------+-----------+------------+-------------+--------------+-----------------+
6398 rows in set (12 min 46.15 sec)

2. 为tpch数据库批量添加列存索引。具体信息，请参见批量添加列存索引。

CREATE COLUMNAR INDEX FOR TABLES IN tpch;
-- 执行结果
+------------+-------------------+
| Table_Name | Result            |
+------------+-------------------+
| customer   | Ok                |
| lineitem   | Skip by no change |
| nation     | Ok                |
| orders     | Ok                |
| part       | Ok                |
| partsupp   | Ok                |
| region     | Ok                |
| supplier   | Ok                |
+------------+-------------------+
8 rows in set (56.74 sec)

3.查看列存索引构建的执行进度，等待列存索引构建完成。具体信息，请参见查看列存索引构建的执行进度。

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;
-- 执行结果，列存索引构建完成。
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| SCHEMA_NAME | TABLE_NAME | CREATED_AT | STARTED_AT | FINISHED_AT | STATUS | APPROXIMATE_ROWS | SCANNED_ROWS | SCAN_SECOND | SORT_ROUNDS | SORT_SECOND | BUILD_ROWS | BUILD_SECOND | AVG_SPEED | SPEED_LAST_SECOND | ESTIMATE_SECOND |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| tpch | region | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | Safe to read | 5 | 5(100%) | 0 | 0 | 0 | 0(0%) | 0 | 150 | 0 | 0 |
| tpch | lineitem | 2024-10-21 14:36:13 | 2024-10-21 14:36:13 | 2024-10-21 14:42:23 | Safe to read | 590446240 | 600037902(100%) | 370 | 0 | 0 | 0(0%) | 0 | 1620776 | 0 | 0 |
| tpch | supplier | 2024-10-21 14:44:16 | 2024-10-21 14:44:16 | 2024-10-21 14:44:17 | Safe to read | 986923 | 1000000(100%) | 1 | 0 | 0 | 0(0%) | 0 | 784971 | 0 | 0 |
| tpch | part | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:43:38 | Safe to read | 19354445 | 20000000(100%) | 11 | 0 | 0 | 0(0%) | 0 | 1784854 | 0 | 0 |
| tpch | customer | 2024-10-21 14:43:19 | 2024-10-21 14:43:19 | 2024-10-21 14:43:27 | Safe to read | 13179406 | 15000000(100%) | 7 | 0 | 0 | 0(0%) | 0 | 2051651 | 0 | 0 |
| tpch | nation | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | Safe to read | 25 | 25(100%) | 0 | 0 | 0 | 0(0%) | 0 | 739 | 0 | 0 |
| tpch | partsupp | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:44:16 | Safe to read | 67862725 | 80000000(100%) | 49 | 0 | 0 | 0(0%) | 0 | 1620131 | 0 | 0 |
| tpch | orders | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:44:27 | Safe to read | 142929780 | 150000000(100%) | 59 | 0 | 0 | 0(0%) | 0 | 2501701 | 0 | 0 |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
8 rows in set, 1 warning (0.00 sec)

4. 在为tpch数据库批量添加列存索引后，执行多表查询及子查询SQL，并记录执行时间。

-- 多表关联 (JOIN) 性能
SELECT
  COUNT(l3.L_DISCOUNT)
FROM
  (
    (
      (
        (
          (
            nation n1 STRAIGHT_JOIN nation n2 on n1.N_NATIONKEY = n2.N_NATIONKEY
          )
          STRAIGHT_JOIN supplier on n2.N_NATIONKEY = supplier.S_NATIONKEY and S_SUPPKEY < 2000
        )
        STRAIGHT_JOIN lineitem AS l1 on l1.L_SUPPKEY = supplier.S_SUPPKEY
      )
      STRAIGHT_JOIN lineitem AS l2 on l1.L_ORDERKEY = l2.L_ORDERKEY and l1.L_LINENUMBER = l2.L_LINENUMBER
    )
    STRAIGHT_JOIN lineitem AS l3 on l2.L_ORDERKEY = l3.L_ORDERKEY and l2.L_LINENUMBER = l3.L_LINENUMBER
  )
GROUP BY 
  n1.N_NAME;
+----------------------+
| COUNT(l3.L_DISCOUNT) |
+----------------------+
| 56930 |
| ... |
| 49995 |
+----------------------+
25 rows in set (6.25 sec)
-- 关联子查询性能
SELECT 
  O_ORDERPRIORITY, COUNT(*) as ORDER_COUNT 
FROM
  orders
WHERE
  O_ORDERDATE >= '1995-01-01' AND
  O_ORDERDATE < date_add('1995-01-01', interval '3' month) AND
  EXISTS 
    (
      SELECT * FROM lineitem WHERE L_ORDERKEY = O_ORDERKEY AND L_COMMITDATE < L_RECEIPTDATE
    )
GROUP BY 
  O_ORDERPRIORITY
ORDER BY
  O_ORDERPRIORITY;
-- 执行结果
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT | 1028353 |
| 2-HIGH | 1030059 |
| 3-MEDIUM | 1028615 |
| 4-NOT SPECIFIED | 1028496 |
| 5-LOW | 1029615 |
+-----------------+-------------+
5 rows in set (2.49 sec)
-- 多表关联带子查询性能
SELECT 
  C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, SUM(L_QUANTITY)
FROM 
  (
    SELECT * FROM orders WHERE O_ORDERKEY IN 
      (
        SELECT L_ORDERKEY FROM lineitem GROUP BY L_ORDERKEY HAVING SUM(L_QUANTITY) > 300
      ) 
  ) AS tmp, customer, lineitem 
WHERE 
  C_CUSTKEY = O_CUSTKEY AND 
  O_ORDERKEY = L_ORDERKEY
GROUP BY 
  C_NAME, 
  C_CUSTKEY, 
  O_ORDERKEY, 
  O_ORDERDATE, 
  O_TOTALPRICE
ORDER BY 
  O_TOTALPRICE DESC, 
  O_ORDERDATE;
-- 执行结果
+--------------------+-----------+------------+-------------+--------------+-----------------+
| C_NAME | C_CUSTKEY | O_ORDERKEY | O_ORDERDATE | O_TOTALPRICE | SUM(L_QUANTITY) |
+--------------------+-----------+------------+-------------+--------------+-----------------+
| Customer#011472112 | 11472112 | 458304292 | 1998-02-05 | 591036.15 | 322.00 |
| ... | ... | ... | ... | ... | ... |
| Customer#003777694 | 3777694 | 470363105 | 1997-04-06 | 349914.00 | 302.00 |
| Customer#009446411 | 9446411 | 592379937 | 1995-12-29 | 343496.05 | 304.00 |
+--------------------+-----------+------------+-------------+--------------+-----------------+
6398 rows in set (16.16 sec)

5. 执行时间对比（单位：秒）。

查询类型

PolarDB 列存索引（IMCI）

PolarDB 行存

多表关联 (JOIN) 性能

6.25

>7200

关联子查询性能

2.49

249.51

多表关联带子查询性能

16.16

766.15

可以观察到，在添加列存索引后，多表查询及子查询SQL的执行效率显著提高。

说明

以上数据仅作为评估SQL执行效率的参考基准，而非绝对标准。实际上，SQL的具体执行时间会受到多种因素的动态影响，这些因素包括但不限于集群的配置参数、当前的连接数、并发查询的数量，以及系统资源的实时负载状况。

行列分流

在添加只读列存节点后，集群地址的行列分流方案默认配置为行列自动分流。该配置适用于您在业务中，当OLAP类型与OLTP类型的请求通过同一应用程序访问数据库时，可以实现将两类业务的读请求按照扫描的行数进行自动分流，分别分流至列存节点或行存节点。如果在您的业务中，OLAP类型与OLTP类型的请求通过不同的应用程序访问数据库，则可以配置行列手动分流，为这些应用程序配置不同的数据库连接地址，并将行存节点和列存节点分别配置到不同数据库连接地址的服务节点，从而实现行存与列存的有效分流。具体信息，请参见HTAP行列分流方案。

行列自动分流与手动分流的示意图如下所示：

image

进阶使用

更多信息，请参见进阶使用列存索引。

设置列存索引的排序键

详细信息，请参见设置列索引的排序键。

列存索引数据组织的基本单位为行组（Row Group），每个行组默认包含64K行数据。在每个行组中，不同的列会各自打包形成列数据块，这些列数据块按照行存原始数据的主键顺序并行构建，整体上呈现无序状态。您可以通过设置排序键来修改列数据块的排列顺序，以提高查询性能。

  1. 开启列存索引排序功能，将参数imci_enable_pack_order_key的值设置为ON，来开启新建列存索引时数据排序功能。

说明

  • 参数imci_enable_pack_order_key的默认值为ON。如您此前未对该参数进行过修改，请忽略当前步骤。
  • 集群参数在PolarDB控制台上都已加上MySQL配置文件的兼容性前缀loose_。如果您需要在PolarDB控制台修改参数imci_enable_pack_order_key，请选择带有前缀loose_的参数（即loose_imci_enable_pack_order_key）进行修改。具体信息，请参见设置集群参数和节点参数。

2. 在未添加排序键时，执行下列SQL并记录其执行时间。

SELECT
  L_SHIPMODE,
  SUM(CASE
      WHEN O_ORDERPRIORITY = '1-URGENT' OR O_ORDERPRIORITY = '2-HIGH'
      THEN 1
      ELSE 0
      END) AS high_line_count,
  SUM(CASE
      WHEN O_ORDERPRIORITY <> '1-URGENT' AND O_ORDERPRIORITY <> '2-HIGH'
      THEN 1
      ELSE 0
      END) AS low_line_count
FROM
    orders,
    lineitem
WHERE
    O_ORDERKEY = L_ORDERKEY
    AND L_SHIPMODE in ('MAIL', 'SHIP')
    AND L_COMMITDATE <  L_RECEIPTDATE
    AND L_SHIPDATE < L_COMMITDATE
    AND L_RECEIPTDATE >= date '1994-01-01'
    AND L_RECEIPTDATE < date '1994-01-01' + interval '1' year
GROUP BY
    L_SHIPMODE
ORDER BY
    L_SHIPMODE;
-- 执行结果
+------------+-----------------+----------------+
| L_SHIPMODE | high_line_count | low_line_count |
+------------+-----------------+----------------+
| MAIL       |          623115 |         934713 |
| SHIP       |          622979 |         934534 |
+------------+-----------------+----------------+
2 rows in set (4.35 sec)

3.对表lineitem添加order_key属性来构建有序列存索引数据。

ALTER TABLE lineitem COMMENT='COLUMNAR=1 order_key=L_RECEIPTDATE,L_SHIPMODE lineitem表注释';

4.等待有序列存索引数据构建。具体信息，请参见有序列存索引数据构建和查询时间对比。

5.再次执行第二步的SQL并记录其执行时间。

-- 执行结果
+------------+-----------------+----------------+
| L_SHIPMODE | high_line_count | low_line_count |
+------------+-----------------+----------------+
| MAIL       |          623115 |         934713 |
| SHIP       |          622979 |         934534 |
+------------+-----------------+----------------+
2 rows in set (0.88 sec)
6. 执行时间对比（单位：秒）。
有序数据集无序数据集
0.884.35

说明

以上数据仅作为评估SQL执行效率的参考基准，而非绝对标准。实际上，SQL的具体执行时间会受到多种因素的动态影响，这些因素包括但不限于集群的配置参数、当前的连接数、并发查询的数量，以及系统资源的实时负载状况。

只读列存节点开启Serverless

详细信息，请参见只读列存节点开启Serverless。

Serverless是指云原生数据库PolarDB所具备的动态弹性扩缩容能力。集群中的各个节点能够在秒级内实现弹性扩展，能够从容应对业务负载的突增，且全程对业务运行无任何影响。在业务低负载期间，该机制能够自动进行弹性缩容，从而有效降低业务成本。关于Serverless具体信息，请参见Serverless。

若您的业务存在明显的波峰波谷，或担心当前集群配置无法应对业务负载的突增，您可在集群基本信息 > 数据库节点区域开启Serverless功能。具体信息，请参见开启固定规格集群的Serverless功能。

更多信息

费用

列存索引功能可免费使用，仅收取只读列存节点的费用。只读列存节点按照普通的计算节点收费。具体信息，请参见计算节点计费规则。同时，列存索引也会占用一定的存储空间。具体信息，请参见存储空间计费规则。

说明

列存索引数据以列的形式进行组织，其压缩比相对更高。与行存相比，通常可以实现3至10倍的压缩比，所占用的存储空间约为行存的10%至30%。因此，存储空间将额外增加10%至30%的数据量。

性能

  • 查询性能
  • 列存索引对大多数的复杂查询操作都有加速作用，查询性能提升非常明显，甚至可达到百倍。
  • 与传统OLAP数据库ClickHouse相比：PolarDB MySQL版集群开启列存索引后，与ClickHouse性能相比各有优劣。其中在单表Scan/AGG、Join等场景中表现突出。未来的列存索引特性将在聚合加速、窗口函数等方面持续优化和突破。

说明

详细说明，请参见性能提升情况。

  • 写入性能

增加列存索引对写入性能的影响基本在5%以内。使用Sysbench测试集测试oltp_insert workload，增加列存索引后写入性能下降约为3%。

专家面对面

若您对列存索引有任何问题，可通过钉钉搜索群号入群咨询。您可以直接@群内专家，并附上您要咨询的问题。钉钉群号：27520023189。

常见问题

为什么SQL语句并没有走列存索引？

新增只读列存节点后，需要为SQL语句中所查询的表都增加列存索引，且SQL语句的预估执行代价超过一定阈值，该SQL语句才会使用列索引进行查询。另外，SQL语句需要被转发到只读列存节点，才可以使用列存索引进行查询加速。一般来说，如果一条SQL语句无法使用列存索引进行查询，可以按照以下步骤进行问题排查：

  1. 确认SQL是否被转发至只读列存节点。
  • 使用的数据库连接地址中的服务节点是否包含只读列存节点。
  • 通过SQL洞察功能，可以确认SQL是否被转发到了只读列存节点。具体信息，请参见SQL洞察。

如果使用集群地址并开启行存/列存自动引流，同时SQL的预估执行代价高于设定的阈值loose_imci_ap_threshold或loose_cost_threshold_for_imci，则数据库代理将会将SQL转发至只读列存节点。此外，您可以在SQL语句的SELECT关键字前添加HINT语法/*FORCE_IMCI_NODES*/以强制指定将SQL转发到只读列存节点。具体信息，请参见配置自动引流阈值。示例如下：

内核版本8.0.1.1.39与8.0.2.2.23及之后的版本，参数loose_imci_ap_threshold被弃用，统一使用参数loose_cost_threshold_for_imci。

/*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;

创建一个新的数据库连接地址，可以保证SQL语句一定会被转发到只读列存节点上执行。具体信息，请参见创建一个新的数据库连接地址。

2. SQL的预估执行代价是否高于设定的阈值。

在只读列存节点上，优化器会对SQL进行执行代价预估，如果预估执行代价高于设定的阈值loose_imci_ap_threshold或loose_cost_threshold_for_imci，则使用列存索引进行查询，否则将使用原有的行索引进行查询。

在确认SQL被转发到只读列存节点后，如果通过EXPLAIN查看执行计划依然没有使用列存索引，可以通过比较预估执行代价与预设的阈值，判断是否是预估执行代价过小而没有使用列存索引。您可以通过查询Last_query_cost变量获取“上一条SQL的预估执行代价”：

-- 使用EXPLAIN查看SQL的执行计划
EXPLAIN SELECT * FROM t1;
-- 获取上一条SQL的预估执行代价
SHOW STATUS LIKE 'Last_query_cost';

如果使用集群地址连接数据库，建议您在SHOW STATUS LIKE 'Last_query_cost'前添加HINT语法/*ROUTE_TO_LAST_USED*/，以确保能够在正确的节点上查询到上一条语句的预估执行代价。例如，/*ROUTE_TO_LAST_USED*/SHOW STATUS LIKE 'Last_query_cost';

若SQL的预估执行代价小于预设的阈值，可以考虑调整loose_imci_ap_threshold或loose_cost_threshold_for_imci的值。例如，使用HINT语法调整单条SQL的预设阈值：

/*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

3.SQL中所涉及的表或列是否被列存索引完全覆盖。

可以通过内置的存储过程dbms_imci.check_columnar_index('')，检查SQL语句中的表或列是否已创建列存索引。具体信息，请参见检查SQL语句中的表或列是否已创建列存索引。示例如下：

CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');

如果SQL未被列存索引完全覆盖，调用该存储过程将返回未被覆盖的表和列，您需对返回的表和列逐一创建列存索引。如果已经完全被列存索引覆盖，则调用该存储过程将返回空结果集。

4. 是否有不支持的SQL特性。

通过查看列存索引语法使用限制，确认某个SQL特性是否支持列存索引。具体信息，请参见列存索引语法使用限制。

如按上述步骤排查后，SQL语句仍然没有走列存索引，可通过专家面对面进行咨询或联系我们。

如何为SQL增加合适的列存索引？

建议您为SQL中所需的列添加列存索引。具体信息，请参见检查SQL语句中的表或列是否已创建列存索引。

当一条SQL语句所需的列被列存索引完全覆盖时，该SQL语句才能使用列存索引进行查询。如果SQL语句中所需的列未被列存索引完全覆盖，可以通过ALTER TABLE语句来增加列存索引。PolarDB为您提供了一系列内置存储过程以辅助此操作。

说明

  • 使用dbms_imci.columnar_advise()存储过程可以获得某个SQL语句所需的创建列存索引DDL语句。按照这个DDL语句构建列存索引，可以保证该SQL语句完全被列存索引覆盖。具体信息，请参见获取创建列存索引的DDL语句。

dbms_imci.columnar_advise('');

  • 使用dbms_imci.columnar_advise_begin()、dbms_imci.columnar_advise_end()以及dbms_imci.columnar_advise()存储过程，可以获得一批SQL语句所需的创建列存索引DDL语句。具体信息，请参见批量获取创建列存索引的DDL语句。


polardb
最后修改时间：2024-11-13 11:37:14
文章转载自胖橘，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论