列存索引資料排序的排序流程是什麼,如何使用 - PolarDB

本文介紹了列存索引資料的排序流程、使用方法以及構建和查詢有序列存索引資料的時間對比等內容。

簡介

列存索引資料是按照行組進行組織，預設包含64K行。每個行組中不同的列會各自打包形成列資料區塊，列資料區塊按照行存未經處理資料的主鍵次序並行構建，更新資料則按照追加次序寫入，總體上是無序的。

列存索引支援粗糙索引，每一個列資料區塊的中繼資料套件含全部資料的最小值和最大值等資訊。查詢資料時，正常情況下需要遍曆指定列的所有列資料區塊。開啟Pruner後，會根據查詢條件與中繼資料資訊將所有列資料區塊分為相關、可能相關和不相關三大類。讀取資料時只考慮相關和可能相關的列資料區塊。列資料區塊有不同次序的組織方式，進而會產生不同組合的列資料區塊集合，Pruner也會有不同的過濾效果，因此，使用者可以根據查詢條件來修改列資料區塊的排列順序，以提高查詢效能。

列存索引重排序如上圖所示，當執行以下SQL語句時，針對無序列資料區塊集合，需要載入所有列資料區塊並進行處理。而有序資料區塊集合則可以通過每個列資料區塊記憶體中中繼資料的最大值和最小值等資訊過濾掉第一個列資料區塊，只需要針對第二個列資料區塊進行處理即可。

SELECT * FROM t WHERE c >= 8;

適用範圍

開啟新增列存索引時資料排序功能，企業版叢集版本需滿足以下條件之一：
- PolarDB MySQL版8.0.1版本且修訂版本為8.0.1.1.32及以上。
- PolarDB MySQL版8.0.2版本且修訂版本為8.0.2.2.12及以上。
增量資料排序功能，企業版叢集版本需滿足以下條件之一：
- PolarDB MySQL版8.0.1版本且修訂版本為8.0.1.1.39.1及以上。
- PolarDB MySQL版8.0.2版本且修訂版本為8.0.2.2.20.1及以上。

您可以通過查詢版本號碼來確認叢集版本。

注意事項

不支援將BLOB、JSON和GEOMETRY類型的資料作為排序鍵。
增量資料排序功能不支援無符號整型和Decimal類型的排序鍵。
增量排序只按照排序鍵的第一列維護有序性。
增量資料排序會佔用一定的資源。因此，當叢集的寫入負載較高時，為了讓出更多的資源供前台寫入資料，增量資料排序的速度會變慢。

排序流程

建立列存索引時資料排序流程
列存索引資料排序總體上實現與DDL過程中二級索引的排序演算法類似，支援單線程與多線程排序。單線程使用標準二路歸併排序，多線程使用敗者樹多路外排且支援抽樣排序法策略。總體流程如下：
1. 按照主鍵索引遍曆並將讀取到的完整資料儲存至資料檔案，然後將排序列添加到排序緩衝區，其中每個線程使用不同的資料檔案，累積達到一定資料後再寫入；
2. 不斷遍曆並插入到排序緩衝區，當排序緩衝區滿時，在記憶體中根據排序鍵組合進行排序並儲存到合并檔案中；
3. 遍曆完成後，對合并檔案按段兩兩排序，並將排序後的資料儲存在臨時檔案中，隨後將合并檔案與臨時檔案進行切換；
4. 重複執行步驟3，直到合并檔案有序排列，然後讀取合并檔案中的每一行記錄，根據位移值讀取資料檔案中對應的記錄並追加到列存索引中。
增量資料排序流程
增量資料的排序流程是漸進式的，不能保證資料完全有序。總體流程如下：
1. 將所有的資料區塊進行兩兩分組，挑選出多個資料範圍重合度較高的資料區塊組。
2. 將每個資料區塊組進行歸併排序，產生兩個有序的資料區塊。
3. 重複執行步驟2，直到所有的資料區塊有序排列。

參數說明

您需要在資料庫中設定下表中參數的值，來開啟或關閉列存索引排序功能，以及根據實際業務需求設定線程數量等資訊。

說明

PolarDB叢集參數在控制台與會話中修改方式存在差異，詳細區別如下：

在PolarDB控制台上修改：
- 相容性說明：部分叢集參數在PolarDB控制台上均已添加MySQL設定檔的相容性首碼loose_。
- 操作方法：找到並修改這些帶loose_首碼的參數。
在資料庫會話中修改（使用命令列或用戶端）：
- 操作方法：當您串連到資料庫，使用SET命令修改參數時，請去掉loose_首碼，直接使用參數的原始名稱進行修改。

參數	說明
loose_imci_enable_pack_order_key	建立列存索引時資料排序功能控制開關。取值如下： ON（預設）：開啟新增列存索引時資料排序功能。 OFF：關閉新增列存索引時資料排序功能。
loose_imci_enable_pack_order_key_changed_rebuild	列存索引在索引排序發生變化時，是否需要重建表。取值範圍如下： ON：列存索引在索引排序發生變化時，需要重建表。 OFF（預設）：列存索引在索引排序發生變化時，不需要重建表。
loose_imci_parallel_build_threads_per_table	構建列存索引資料的單張表的線程數量。取值範圍：1~128。預設值為8。

使用說明

您可以按照以下步驟來使用列存索引資料排序功能：

開啟列存索引排序功能：
將參數imci_enable_pack_order_key的值設定為ON，來開啟建立列存索引時資料排序功能。
在以下SQL語句的comment中添加order_key屬性來構建有序列存索引資料。
```
ALTER TABLE table_name COMMENT 'columnar=1 order_key=column_name[,column_name]';
```
參數說明：
參數
說明
table_name
表名。
column_name
列名。您可以配置多個列名，多個列名之間使用英文逗號（,）分隔。
您可以在INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS表中查看列存索引資料的構建進度。INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS表的詳細資料請參見查看列存索引構建的執行進度。

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

列存索引資料排序與DDL排序的區別

列存索引資料排序的本質是按照指定鍵組合進行排序，類似於其他普通二級索引DDL排序過程，但兩者也有不同之處，具體區別如下：

列存索引排序不使用索引列作為排序鍵，而是可以指定任意組合的排序鍵。
列存索引排序後，需要讀取完整的資料。而二級索引DDL僅需要儲存索引部分的資料。如VARCHAR欄位只儲存首碼部分作為索引資料。

有序列存索引資料構建和查詢時間對比

以TPCH 100 GB為例，測試構建和查詢有序列存索引資料的時間。

測試構建有序列存索引資料的時間。
以lineitem表為例，來構建有序列存索引資料，且並行線程數量為16。樣本如下：
```
ALTER TABLE lineitem COMMENT='columnar=1 order_key=l_receiptdate,l_shipmode';
```
構建時間如下：
無序資料集
有序資料集
6分鐘
35分鐘

測試有序列存索引資料的查詢時間。

以TCPH的Q12為例，在LRU緩衝與執行器記憶體均為10 GB的配置下，執行如下查詢語句：

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;

查詢時間如下：

無序資料集	有序資料集
7.47s	1.25s

添加排序鍵與分區表的查詢時間對比

在TPC-H使用的資料集為1 TB，節點規格為32核256 GB情境下，測試開啟列存索引功能（列存）以及開啟列存索引功能（添加分區和排序列）的查詢效能。

測試使用的標準建表語句如下：

CREATE TABLE region ( r_regionkey  BIGINT NOT NULL,
                      r_name       CHAR(25) NOT NULL,
                      r_comment    VARCHAR(152)) COMMENT 'COLUMNAR=1';

CREATE TABLE nation ( n_nationkey  BIGINT NOT NULL,
                      n_name       CHAR(25) NOT NULL,
                      n_regionkey  BIGINT NOT NULL,
                      n_comment    VARCHAR(152)) COMMENT 'COLUMNAR=1';

CREATE TABLE part ( p_partkey     BIGINT NOT NULL,
                    p_name        VARCHAR(55) NOT NULL,
                    p_mfgr        CHAR(25) NOT NULL,
                    p_brand       CHAR(10) NOT NULL,
                    p_type        VARCHAR(25) NOT NULL,
                    p_size        BIGINT NOT NULL,
                    p_container   CHAR(10) NOT NULL,
                    p_retailprice DECIMAL(15,2) NOT NULL,
                    p_comment     VARCHAR(23) NOT NULL) COMMENT 'COLUMNAR=1';

CREATE TABLE supplier ( s_suppkey     BIGINT NOT NULL,
                        s_name        CHAR(25) NOT NULL,
                        s_address     VARCHAR(40) NOT NULL,
                        s_nationkey   BIGINT NOT NULL,
                        s_phone       CHAR(15) NOT NULL,
                        s_acctbal     DECIMAL(15,2) NOT NULL,
                        s_comment     VARCHAR(101) NOT NULL) COMMENT 'COLUMNAR=1';

CREATE TABLE partsupp ( ps_partkey     BIGINT NOT NULL,
                        ps_suppkey     BIGINT NOT NULL,
                        ps_availqty    BIGINT NOT NULL,
                        ps_supplycost  DECIMAL(15,2)  NOT NULL,
                        ps_comment     VARCHAR(199) NOT NULL) COMMENT 'COLUMNAR=1';

CREATE TABLE customer ( c_custkey     BIGINT NOT NULL,
                        c_name        VARCHAR(25) NOT NULL,
                        c_address     VARCHAR(40) NOT NULL,
                        c_nationkey   BIGINT NOT NULL,
                        c_phone       CHAR(15) NOT NULL,
                        c_acctbal     DECIMAL(15,2)   NOT NULL,
                        c_mktsegment  CHAR(10) NOT NULL,
                        c_comment     VARCHAR(117) NOT NULL) COMMENT 'COLUMNAR=1';

CREATE TABLE orders ( o_orderkey       BIGINT NOT NULL,
                      o_custkey        BIGINT NOT NULL,
                      o_orderstatus    CHAR(1) NOT NULL,
                      o_totalprice     DECIMAL(15,2) NOT NULL,
                      o_orderdate      DATE NOT NULL,
                      o_orderpriority  CHAR(15) NOT NULL,
                      o_clerk          CHAR(15) NOT NULL,
                      o_shippriority   BIGINT NOT NULL,
                      o_comment        VARCHAR(79) NOT NULL) COMMENT 'COLUMNAR=1'
                      PARTITION BY RANGE (year(`o_orderdate`))
                      (PARTITION p0 VALUES LESS THAN (1992) ENGINE = InnoDB,
                       PARTITION p1 VALUES LESS THAN (1993) ENGINE = InnoDB,
                       PARTITION p2 VALUES LESS THAN (1994) ENGINE = InnoDB,
                       PARTITION p3 VALUES LESS THAN (1995) ENGINE = InnoDB,
                       PARTITION p4 VALUES LESS THAN (1996) ENGINE = InnoDB,
                       PARTITION p5 VALUES LESS THAN (1997) ENGINE = InnoDB,
                       PARTITION p6 VALUES LESS THAN (1998) ENGINE = InnoDB,
                       PARTITION p7 VALUES LESS THAN (1999) ENGINE = InnoDB);

CREATE TABLE lineitem ( l_orderkey       BIGINT NOT NULL,
                        l_partkey        BIGINT NOT NULL,
                        l_suppkey        BIGINT NOT NULL,
                        l_linenumber     BIGINT NOT NULL,
                        l_quantity       DECIMAL(15,2) NOT NULL,
                        l_extendedprice  DECIMAL(15,2) NOT NULL,
                        l_discount       DECIMAL(15,2) NOT NULL,
                        l_tax            DECIMAL(15,2) NOT NULL,
                        l_returnflag     CHAR(1) NOT NULL,
                        l_linestatus     CHAR(1) NOT NULL,
                        l_shipdate       DATE NOT NULL,
                        l_commitdate     DATE NOT NULL,
                        l_receiptdate    DATE NOT NULL,
                        l_shipinstruct   CHAR(25) NOT NULL,
                        l_shipmode       CHAR(10) NOT NULL,
                        l_comment        VARCHAR(44) NOT NULL) COMMENT 'COLUMNAR=1'
                        PARTITION BY RANGE (year(`l_shipdate`))
                        (PARTITION p0 VALUES LESS THAN (1992) ENGINE = InnoDB,
                         PARTITION p1 VALUES LESS THAN (1993) ENGINE = InnoDB,
                         PARTITION p2 VALUES LESS THAN (1994) ENGINE = InnoDB,
                         PARTITION p3 VALUES LESS THAN (1995) ENGINE = InnoDB,
                         PARTITION p4 VALUES LESS THAN (1996) ENGINE = InnoDB,
                         PARTITION p5 VALUES LESS THAN (1997) ENGINE = InnoDB,
                         PARTITION p6 VALUES LESS THAN (1998) ENGINE = InnoDB,
                         PARTITION p7 VALUES LESS THAN (1999) ENGINE = InnoDB);

資料匯入後，為表設定排序列。設定排序列的操作方法請參見設定列索引的排序鍵。

ALTER TABLE customer COMMENT='COLUMNAR=1 order_key=c_mktsegment';
ALTER TABLE nation COMMENT='COLUMNAR=1 order_key=n_name';
ALTER TABLE part COMMENT='COLUMNAR=1 order_key=p_brand,p_container,p_type';
ALTER TABLE region COMMENT='COLUMNAR=1 order_key=r_name';
ALTER TABLE orders COMMENT='COLUMNAR=1 order_key=o_orderkey,o_custkey,o_orderdate';
ALTER TABLE lineitem COMMENT='COLUMNAR=1 order_key=l_orderkey,l_linenumber,l_receiptdate,l_shipdate,l_partkey';

挑選TPC-H的部分查詢語句進行測試，查詢時間結果見下表：

查詢SQL	無序資料集（秒）	有序資料集（添加分區和排序列）（秒）
Q3	71.951	36.566
Q4	46.679	32.015
Q6	34.652	4.4
Q7	74.749	34.166
Q12	86.742	28.586
Q14	50.248	12.56
Q15	79.22	21.113
Q20	51.746	10.178
Q21	216.942	148.459

參數	說明
table_name	表名。
column_name	列名。您可以配置多個列名，多個列名之間使用英文逗號（,）分隔。

無序資料集	有序資料集
6分鐘	35分鐘