PolarDB：Konfigurasikan kunci pengurutan untuk indeks penyimpanan kolom

Pendahuluan

Data indeks penyimpanan kolom diorganisasi berdasarkan grup baris. Secara default, setiap grup baris berisi 64.000 baris. Di dalam setiap grup baris, kolom-kolom yang berbeda dikemas ke dalam blok data kolom. Blok-blok ini dibangun secara paralel berdasarkan urutan kunci primer dari data penyimpanan baris asli. Karena data yang diperbarui ditulis dalam urutan tambahan (append order), data tersebut umumnya tidak terurut.

Indeks penyimpanan kolom mendukung indeks kasar. Metadata setiap blok data kolom mencakup informasi seperti nilai minimum dan maksimum dari seluruh data dalam blok tersebut. Saat melakukan kueri, semua blok data kolom dari kolom tertentu biasanya dilalui (traversed). Setelah Pemangkas IMCI diaktifkan, semua blok data kolom diklasifikasikan ke dalam tiga kategori berdasarkan kondisi kueri dan metadata: relevan, mungkin relevan, dan tidak relevan. Hanya blok data kolom yang relevan dan mungkin relevan yang dibaca. Susunan blok data kolom dapat diatur dalam urutan berbeda, menghasilkan kombinasi koleksi blok yang berbeda dan memengaruhi efisiensi pemfilteran oleh Pemangras IMCI. Oleh karena itu, Anda dapat mengatur susunan blok data kolom berdasarkan kondisi kueri untuk meningkatkan kinerja kueri.

Seperti yang ditunjukkan pada gambar sebelumnya, ketika pernyataan SQL berikut dieksekusi pada kumpulan blok data yang tidak terurut, semua blok data kolom harus dimuat dan diproses. Untuk kumpulan blok data yang terurut, blok data kolom pertama dapat difilter berdasarkan nilai maksimum dan minimum dalam metadatanya, sehingga hanya blok data kolom kedua yang perlu diproses.

SELECT * FROM t WHERE c >= 8;

Kesesuaian

• Untuk menggunakan fitur Pengurutan data saat membuat indeks penyimpanan kolom baru, Anda memerlukan kluster Edisi Perusahaan yang memenuhi salah satu persyaratan berikut:

  • PolarDB for MySQL 8.0.1 dengan versi revisi 8.0.1.1.32 atau lebih baru.

  • PolarDB for MySQL 8.0.2 dengan versi revisi 8.0.2.2.12 atau lebih baru.

• Untuk menggunakan fitur Pengurutan data inkremental, Anda memerlukan kluster Edisi Perusahaan yang memenuhi salah satu persyaratan berikut:

  • PolarDB for MySQL 8.0.1 dengan versi revisi 8.0.1.1.39.1 atau lebih baru.

  • PolarDB for MySQL 8.0.2 dengan versi revisi 8.0.2.2.20.1 atau lebih baru.

Anda dapat memeriksa versi kluster. Untuk informasi selengkapnya, lihat Kueri nomor versi.

Perhatian

• Kolom dengan tipe data BLOB, JSON, atau GEOMETRY tidak dapat digunakan sebagai kunci pengurutan.

• Fitur pengurutan data inkremental tidak mendukung kunci pengurutan dengan tipe data integer tak bertanda (unsigned integer) atau Decimal.

• Pengurutan inkremental hanya mempertahankan urutan berdasarkan kolom pertama dari kunci pengurutan.

• Pengurutan data inkremental mengonsumsi sejumlah sumber daya tertentu. Oleh karena itu, ketika beban kerja penulisan (write workload) kluster tinggi, kecepatan pengurutan data inkremental melambat untuk melepaskan lebih banyak sumber daya bagi operasi penulisan data.

Prosedur pengurutan

• Prosedur pengurutan data saat membuat indeks penyimpanan kolom baru

  Pengurutan data untuk indeks penyimpanan kolom diimplementasikan dengan cara yang mirip dengan algoritma pengurutan untuk indeks sekunder selama proses Data Definition Language (DDL). Pengurutan single-threaded maupun multi-threaded didukung. Pengurutan single-threaded menggunakan pengurutan penggabungan dua arah standar. Pengurutan multi-threaded menggunakan k-way external merge sort dengan pohon loser dan mendukung kebijakan pengurutan pengambilan sampel. Proses keseluruhan adalah sebagai berikut:

  1. Lalui (traverse) data berdasarkan indeks kunci primer dan simpan data lengkap yang dibaca ke file data. Kemudian, tambahkan kolom kunci pengurutan ke buffer pengurutan. Setiap thread menggunakan file data yang berbeda dan menulis data setelah jumlah tertentu terkumpul.

  2. Lanjutkan melalui dan memasukkan data ke dalam buffer pengurutan. Ketika buffer pengurutan penuh, urutkan data di memori berdasarkan kombinasi kunci pengurutan dan simpan data ke file penggabungan (merge files).

  3. Setelah traversal selesai, urutkan file penggabungan segmen demi segmen secara berpasangan. Simpan data yang telah diurutkan ke file sementara, lalu ganti file penggabungan dengan file sementara tersebut.

  4. Ulangi Langkah 3 hingga file penggabungan diurutkan. Kemudian, baca setiap catatan dalam file penggabungan, ambil catatan yang sesuai dari file data berdasarkan nilai offset, dan tambahkan ke indeks penyimpanan kolom.

• Prosedur pengurutan data inkremental

  Proses pengurutan untuk data inkremental bersifat progresif dan tidak menjamin bahwa data sepenuhnya terurut. Proses keseluruhan adalah sebagai berikut:

  1. Kelompokkan semua blok data secara berpasangan dan pilih beberapa kelompok blok data dengan tingkat tumpang tindih yang tinggi dalam rentang waktu (timestamp range)-nya.

  2. Lakukan pengurutan gabungan (merge sort) pada setiap kelompok blok data untuk menghasilkan dua blok data terurut baru.

  3. Ulangi Langkah 2 hingga semua blok data diurutkan.

Deskripsi parameter

Anda harus mengatur parameter dalam tabel berikut di database untuk mengaktifkan atau menonaktifkan fitur pengurutan indeks penyimpanan kolom dan mengonfigurasi jumlah thread sesuai kebutuhan.

Catatan penggunaan

Anda dapat menggunakan fitur pengurutan data indeks penyimpanan kolom dengan mengikuti langkah-langkah berikut:

1. Aktifkan fitur pengurutan indeks penyimpanan kolom.

   Atur parameter imci_enable_pack_order_key ke ON untuk mengaktifkan fitur Pengurutan data saat membuat indeks penyimpanan kolom baru.

2. Tambahkan properti order_key ke comment pada pernyataan SQL berikut untuk membangun data indeks penyimpanan kolom yang terurut.

   ALTER TABLE table_name COMMENT 'columnar=1 order_key=column_name[,column_name]';

   Deskripsi parameter:

   Parameter	Deskripsi
   table_name	Nama tabel.
   column_name	Nama kolom. Anda dapat mengonfigurasi beberapa nama kolom. Pisahkan beberapa nama kolom dengan koma (,).

   Anda dapat memantau kemajuan pembuatan IMCI di tabel INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS. Untuk informasi selengkapnya tentang tabel INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS, lihat Lihat kecepatan eksekusi DDL dan kemajuan pembuatan IMCI.

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

Differences Between Columnstore Index Data Sorting And DDL Sorting

Pengurutan data indeks penyimpanan kolom pada dasarnya adalah pengurutan berdasarkan kombinasi kunci tertentu, yang mirip dengan proses pengurutan DDL untuk indeks sekunder lainnya. Namun, keduanya berbeda dalam aspek-aspek berikut:

• Pengurutan indeks penyimpanan kolom tidak menggunakan kolom indeks sebagai kunci pengurutan. Sebaliknya, Anda dapat menentukan kombinasi kolom apa pun sebagai kunci pengurutan.

• Setelah pengurutan indeks penyimpanan kolom, data lengkap harus dibaca. Untuk DDL indeks sekunder, hanya bagian indeks dari data yang perlu disimpan. Misalnya, untuk bidang VARCHAR, hanya awalannya yang disimpan sebagai data indeks.

Perbandingan waktu pembuatan dan kueri untuk data indeks penyimpanan kolom yang diurutkan

Contoh ini menggunakan dataset TPC-H 100 GB untuk menguji waktu yang diperlukan untuk membangun dan melakukan kueri terhadap data indeks penyimpanan kolom yang diurutkan.

• Uji waktu pembuatan data indeks penyimpanan kolom yang diurutkan.

  Contoh ini membangun data indeks penyimpanan kolom yang diurutkan untuk tabel lineitem dengan 16 thread paralel. Berikut adalah contoh pernyataan:

  ALTER TABLE lineitem COMMENT='columnar=1 order_key=l_receiptdate,l_shipmode';

  Waktu pembuatan adalah sebagai berikut:

  Dataset tidak terurut	Dataset terurut
  6 menit	35 menit

• Uji waktu kueri data indeks penyimpanan kolom yang diurutkan.

  Contoh ini menjalankan kueri TPC-H Q12. Cache Least Recently Used (LRU) dan memori pelaksana (executor) keduanya diatur menjadi 10 GB. Berikut adalah pernyataan kuerinya:

  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;

  Waktu kueri adalah sebagai berikut:

  Dataset tidak terurut	Dataset terurut
  7,47 detik	1,25 detik

Perbandingan waktu kueri dengan kunci pengurutan dan tabel partisi

Pengujian ini menggunakan dataset TPC-H 1 TB pada node dengan 32 core dan memori 256 GB. Pengujian ini menguji kinerja kueri saat fitur indeks penyimpanan kolom diaktifkan (column store) dan saat fitur indeks penyimpanan kolom diaktifkan dengan partisi dan kolom kunci pengurutan.

Pernyataan pembuatan tabel standar berikut digunakan untuk pengujian:

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

Setelah data diimpor, atur kolom kunci pengurutan untuk tabel-tabel tersebut. Untuk informasi selengkapnya, lihat Konfigurasikan kunci pengurutan untuk indeks penyimpanan kolom.

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

Sejumlah pernyataan kueri TPC-H dipilih untuk pengujian. Waktu kueri ditampilkan dalam tabel berikut: