Optimalkan kunci sortir IMCI untuk analitik yang lebih cepat pada PolarDB-PolarDB-Alibaba Cloud - PolarDB

Topik ini mensimulasikan lingkungan produksi dan menunjukkan cara menggunakan In-Memory Column Index (IMCI) untuk meningkatkan performa kueri pada dataset berskala besar, baik untuk kueri lambat pada tabel tunggal maupun beberapa tabel.

Apa itu In-Memory Column Index (IMCI)

In-Memory Column Index (IMCI) menyimpan seluruh atau sebagian kolom suatu tabel dalam format kolom di node read-only PolarDB for MySQL, menciptakan model penyimpanan hibrida baris-kolom. Pengoptimal kueri juga ditingkatkan dengan operator eksekusi baru yang dirancang khusus untuk penyimpanan kolom, sehingga secara signifikan meningkatkan performa analitik data dan kueri kompleks pada dataset besar. Untuk informasi lebih lanjut, lihat Apa itu In-Memory Column Index (IMCI)?.

Prosedur

Prasyarat

Kluster
- Versi produk: Edisi Perusahaan.
- Seri: Edisi Kluster (Dedikasi).
- Versi kernel: 8.0.1.1.45.2.
- Kluster hot standby: Diaktifkan.
- Node komputasi: 32 core 256 GB (polar.mysql.x8.4xlarge), satu node primary dan satu node read-only (hot standby).
- Jenis penyimpanan: PSL5.
- Template parameter: Template parameter default MySQL_InnoDB_8.0_Edisi Standar.

Data

Dataset berukuran 100 GB berdasarkan benchmark TPC-H.

-- Kueri jumlah baris dan ukuran tabel dalam database.
+----------+----------+-----------+-----------------+
| 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 |
+----------+----------+-----------+-----------------+

Catatan

Jumlah baris dan ukuran tabel dipengaruhi oleh berbagai faktor, seperti indeks, mesin penyimpanan, statistik, dan tabel sistem. Output aktual Anda mungkin berbeda dari hasil yang ditampilkan.
Workload TPC-H dalam topik ini didasarkan pada benchmark TPC-H tetapi tidak memenuhi semua persyaratannya. Oleh karena itu, hasil pengujian dalam topik ini tidak dapat dibandingkan dengan hasil benchmark TPC-H yang dipublikasikan.

Konfigurasikan IMCI

Tambahkan node read-only untuk IMCI. Dalam topik ini, node yang ditambahkan memiliki spesifikasi yang sama dengan node primary: 32 core dan memori 256 GB (polar.mysql.x8.4xlarge). Untuk informasi lebih lanjut, lihat Tambahkan node read-only untuk IMCI.

Kueri tabel tunggal

Simulasikan skenario dengan kueri SQL yang berjalan lambat. Jalankan kueri tabel tunggal berikut sebelum membuat IMCI dan catat waktu eksekusinya.

Pemindaian dan filter tabel tunggal

SELECT * FROM lineitem WHERE L_COMMENT > 'aaaaaaaa' AND L_COMMENT < 'aaaaaaz';

-- Hasil eksekusi
Empty set (8 min 47.29 sec)

Agregasi kolom tunggal (AGG)

SELECT SUM(L_DISCOUNT) from lineitem;

-- Hasil eksekusi
+-----------------+
| SUM(L_DISCOUNT) |
+-----------------+
|     30001636.44 |
+-----------------+
1 row in set (2 min 6.64 sec)

Agregasi berkelompok (GROUP BY)

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

-- Hasil eksekusi
+-----------------+
| AVG(L_DISCOUNT) |
+-----------------+
|        0.049998 |
|        0.050001 |
|        0.050002 |
|        0.049985 |
+-----------------+
4 rows in set (6 min 28.96 sec)

Paginasi dalam (ORDER BY+LIMIT)

SELECT L_ORDERKEY, SUM(L_QUANTITY) FROM lineitem GROUP BY L_ORDERKEY ORDER BY SUM(L_QUANTITY) DESC LIMIT 1000000, 100;

-- Hasil eksekusi
+------------+-----------------+
| L_ORDERKEY | SUM(L_QUANTITY) |
+------------+-----------------+
|   25226310 |          244.00 |
|     ...    |            ...  |
|  494738146 |          244.00 |
+------------+-----------------+
100 rows in set (12 min 24.22 sec)

Buat IMCI. Untuk informasi lebih lanjut, lihat Buat In-Memory Column Index.

ALTER TABLE lineitem COMMENT 'COLUMNAR=1 lineitem table comment';

-- Hasil eksekusi
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Monitor progres pembuatan IMCI dan tunggu hingga selesai. Untuk informasi lebih lanjut, lihat Periksa progres pembuatan IMCI.

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

-- Hasil berikut menunjukkan proses pembuatan IMCI sedang berlangsung.
+-------------+------------+---------------------+---------------------+-------------+----------+------------------+--------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| 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)

-- Hasil berikut menunjukkan pembuatan IMCI telah selesai.
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| 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)

Setelah membuat IMCI untuk tabel lineitem, jalankan kembali kueri tabel tunggal dan catat waktu eksekusinya.

Pemindaian dan filter tabel tunggal

SELECT * FROM lineitem WHERE L_COMMENT > 'aaaaaaaa' AND L_COMMENT < 'aaaaaaz';

-- Hasil eksekusi
Empty set (1.47 sec)

Agregasi kolom tunggal (AGG)

SELECT SUM(L_DISCOUNT) from lineitem;

-- Hasil eksekusi
+-----------------+
| SUM(L_DISCOUNT) |
+-----------------+
|     30001636.44 |
+-----------------+
1 row in set (0.06 sec)

Agregasi berkelompok (GROUP BY)

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

-- Hasil eksekusi
+-----------------+
| AVG(L_DISCOUNT) |
+-----------------+
|        0.050001 |
|        0.050002 |
|        0.049985 |
|        0.049998 |
+-----------------+
4 rows in set (2.54 sec)

Paginasi dalam (ORDER BY+LIMIT)

SELECT L_ORDERKEY, SUM(L_QUANTITY) FROM lineitem GROUP BY L_ORDERKEY ORDER BY SUM(L_QUANTITY) DESC LIMIT 1000000, 100;

-- Hasil eksekusi
+------------+-----------------+
| L_ORDERKEY | SUM(L_QUANTITY) |
+------------+-----------------+
|  299074498 |          244.00 |
|     ...    |            ...  |
|  168679332 |          244.00 |
+------------+-----------------+
100 rows in set (12.80 sec)

Perbandingan waktu eksekusi (dalam detik).

Jenis kueri	PolarDB (IMCI)	PolarDB (Row store)
Pemindaian dan filter tabel tunggal	1.47	527.29
Agregasi kolom tunggal (AGG)	0.06	126.64
Agregasi berkelompok (GROUP BY)	2.54	388.96
Performa paginasi dalam (ORDER BY+LIMIT)	12.80	744.22

Menambahkan IMCI secara signifikan meningkatkan performa kueri SQL tabel tunggal.

Catatan

Data ini merupakan benchmark untuk mengevaluasi performa eksekusi SQL, bukan standar mutlak. Waktu eksekusi SQL aktual bergantung pada berbagai faktor dinamis, termasuk konfigurasi kluster, jumlah koneksi saat ini, volume kueri konkuren, dan beban sistem real-time.

Kueri multi-tabel dan subkueri

Pada bagian ini, kami mensimulasikan skenario kueri lambat di mana IMCI tidak mencakup semua tabel dan kolom yang diperlukan. Jalankan kueri SQL berikut dan catat waktu eksekusinya.

Catatan

Pada kueri SQL berikut, IMCI hanya dibuat untuk tabel lineitem. Tabel lain tidak memiliki IMCI.
Jika tabel atau kolom yang diperlukan oleh kueri tidak sepenuhnya dicakup oleh IMCI, akselerasi tidak berlaku.
Jika Anda tidak yakin apakah tabel atau kolom yang diperlukan oleh kueri telah sepenuhnya dicakup, Anda dapat menggunakan prosedur tersimpan dbms_imci.check_columnar_index('<query_string>'); untuk memeriksanya. Untuk membantu Anda membuat IMCI dengan cepat, PolarDB menyediakan prosedur tersimpan untuk mendapatkan pernyataan DDL guna membuat IMCI. Untuk informasi lebih lanjut, lihat Alat bantu DDL untuk IMCI.

Penggabungan multi-tabel (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;

Kueri mengalami timeout dan dihentikan. Periode timeout kueri adalah 7.200 detik. Oleh karena itu, waktu eksekusi dicatat lebih dari 7.200 detik.

Subkueri berkorelasi

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;

-- Hasil eksekusi
+-----------------+-------------+
| 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)

Penggabungan multi-tabel dengan subkueri

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)

Buat IMCI secara batch untuk database tpch. Untuk informasi lebih lanjut, lihat Buat IMCI secara batch.

CREATE COLUMNAR INDEX FOR TABLES IN tpch;

-- Hasil eksekusi
+------------+-------------------+
| 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)

Monitor progres pembuatan IMCI dan tunggu hingga selesai. Untuk informasi lebih lanjut, lihat Periksa progres pembuatan IMCI.

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

-- Hasil berikut menunjukkan bahwa pembuatan IMCI untuk semua tabel telah selesai.
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| 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)

Setelah membuat IMCI secara batch untuk database tpch, jalankan kembali kueri multi-tabel dan subkueri serta catat waktu eksekusinya.

Penggabungan multi-tabel (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)

Subkueri berkorelasi

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;

-- Hasil eksekusi
+-----------------+-------------+
| 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)

Penggabungan multi-tabel dengan subkueri

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;

-- Hasil eksekusi
+--------------------+-----------+------------+-------------+--------------+-----------------+
| 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)

Perbandingan waktu eksekusi (dalam detik).
Jenis kueri
PolarDB (IMCI)
PolarDB (Row store)
Performa penggabungan multi-tabel (JOIN)
6.25
>7200
Performa subkueri berkorelasi
2.49
249.51
Performa penggabungan multi-tabel dengan subkueri
16.16
766.15
Menambahkan IMCI secara signifikan meningkatkan performa kueri multi-tabel dan subkueri.
Catatan
Data ini merupakan benchmark untuk mengevaluasi performa eksekusi SQL, bukan standar mutlak. Waktu eksekusi SQL aktual bergantung pada berbagai faktor dinamis, termasuk konfigurasi kluster, jumlah koneksi saat ini, volume kueri konkuren, dan beban sistem real-time.

Routing permintaan HTAP

Setelah Anda menambahkan node IMCI read-only, Cluster Endpoint dikonfigurasi untuk Cluster Endpoint secara default. Konfigurasi ini cocok untuk skenario di mana permintaan online analytical processing (OLAP) dan online transaction processing (OLTP) mengakses database melalui aplikasi yang sama. Permintaan baca secara otomatis diarahkan ke node IMCI atau node penyimpanan baris berdasarkan jumlah baris yang dipindai. Jika workload OLAP dan OLTP berasal dari aplikasi yang berbeda, Anda dapat mengonfigurasi routing manual dengan membuat endpoint terpisah untuk setiap aplikasi dan menetapkan node penyimpanan baris serta node IMCI ke Node Settings dari endpoint yang sesuai. Hal ini memastikan pemisahan workload yang efektif antara penyimpanan baris dan penyimpanan kolom. Untuk informasi lebih lanjut, lihat Distribusi permintaan berbasis HTAP di antara node penyimpanan baris dan IMCI.

Diagram berikut menggambarkan routing permintaan otomatis dan manual:

Penggunaan lanjutan

Untuk informasi lebih lanjut, lihat Penggunaan IMCI lanjutan.

Sort key

Untuk informasi lebih lanjut, lihat Tetapkan sort key untuk IMCI.

Data IMCI diorganisasikan dalam grup baris, dan setiap grup baris berisi 64.000 baris secara default. Di dalam setiap grup baris, kolom yang berbeda dikemas ke dalam blok data kolom terpisah. Blok-blok ini dibangun secara paralel berdasarkan urutan kunci primer dari data penyimpanan baris asli, menghasilkan keadaan tidak terurut. Menetapkan sort key akan mengurutkan ulang blok data kolom untuk meningkatkan performa kueri.

Aktifkan fitur pengurutan IMCI dengan mengatur parameter imci_enable_pack_order_key ke ON. Ini mengaktifkan pengurutan data saat IMCI baru dibuat.
Catatan
- Nilai default parameter imci_enable_pack_order_key adalah ON. Jika Anda belum memodifikasi parameter ini, Anda dapat melewati langkah ini.
- Di PolarDB console, parameter kluster diberi awalan loose_ untuk kompatibilitas dengan file konfigurasi MySQL. Jika Anda perlu memodifikasi parameter imci_enable_pack_order_key di PolarDB console, pilih parameter dengan awalan loose_ (loose_imci_enable_pack_order_key). Untuk informasi lebih lanjut, lihat Tetapkan parameter kluster dan node.

Sebelum menambahkan sort key, jalankan kueri SQL berikut dan catat waktu eksekusinya.

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;

-- Hasil eksekusi
+------------+-----------------+----------------+
| L_SHIPMODE | high_line_count | low_line_count |
+------------+-----------------+----------------+
| MAIL       |          623115 |         934713 |
| SHIP       |          622979 |         934534 |
+------------+-----------------+----------------+
2 rows in set (4.35 sec)

Tambahkan atribut order_key ke tabel lineitem untuk membangun data IMCI terurut.

ALTER TABLE lineitem COMMENT='COLUMNAR=1 order_key=L_RECEIPTDATE,L_SHIPMODE lineitem table comment';

Tunggu hingga pembuatan data IMCI terurut selesai. Untuk informasi lebih lanjut, lihat Bangun data IMCI terurut dan bandingkan waktu kueri.

Jalankan kembali kueri SQL dari Langkah 2 dan catat waktu eksekusinya.

-- Hasil eksekusi
+------------+-----------------+----------------+
| L_SHIPMODE | high_line_count | low_line_count |
+------------+-----------------+----------------+
| MAIL       |          623115 |         934713 |
| SHIP       |          622979 |         934534 |
+------------+-----------------+----------------+
2 rows in set (0.88 sec)

Perbandingan waktu eksekusi (dalam detik).
Dataset terurut
Dataset tidak terurut
0.88
4.35
Catatan
Data ini merupakan benchmark untuk mengevaluasi performa eksekusi SQL, bukan standar mutlak. Waktu eksekusi SQL aktual bergantung pada berbagai faktor dinamis, termasuk konfigurasi kluster, jumlah koneksi saat ini, volume kueri konkuren, dan beban sistem real-time.

IMCI serverless

Untuk informasi lebih lanjut, lihat Aktifkan serverless untuk node IMCI read-only.

Fitur serverless dari database cloud-native PolarDB menyediakan kemampuan skalabilitas elastis yang dinamis. Node dalam kluster dapat melakukan scaling elastis dalam hitungan detik untuk menangani lonjakan workload mendadak tanpa mengganggu operasi bisnis Anda. Selama periode workload rendah, mekanisme ini secara otomatis mengurangi sumber daya untuk menekan biaya. Untuk informasi lebih lanjut tentang fitur serverless, lihat Serverless.

Jika bisnis Anda mengalami fluktuasi workload yang signifikan, atau jika Anda khawatir konfigurasi kluster saat ini tidak mampu menangani lonjakan workload mendadak, Anda dapat Enable Serverless di bagian Basic Information > Database Nodes kluster Anda. Untuk informasi lebih lanjut, lihat Aktifkan fitur serverless untuk kluster spesifikasi tetap.

Informasi lebih lanjut

Penagihan

Fitur IMCI gratis digunakan. Anda hanya dikenai biaya untuk node IMCI read-only, yang ditagih sebagai node komputasi standar. Untuk informasi lebih lanjut, lihat Penagihan node komputasi. IMCI juga mengonsumsi storage space. Untuk informasi lebih lanjut, lihat Penagihan storage space.

Catatan

Dibandingkan dengan penyimpanan baris, IMCI biasanya mencapai rasio kompresi antara 3:1 hingga 10:1, sehingga hanya menempati sekitar 10% hingga 30% dari storage space penyimpanan baris setara. Hal ini mengakibatkan peningkatan penggunaan penyimpanan data tambahan sebesar 10% hingga 30%.

Performa

Performa kueri
- IMCI secara signifikan mempercepat sebagian besar kueri kompleks, dengan peningkatan performa kadang mencapai 100 kali lipat.
- Dibandingkan dengan database OLAP tradisional ClickHouse, performa kluster PolarDB for MySQL dengan IMCI yang diaktifkan setara, bahkan unggul dalam beberapa skenario tertentu. IMCI unggul dalam skenario seperti pemindaian tabel tunggal, agregasi (AGG), dan penggabungan (join). Versi IMCI mendatang akan terus dioptimalkan untuk akselerasi agregasi, fungsi jendela, dan fitur lainnya.
Catatan
Untuk informasi lebih lanjut, lihat Peningkatan performa.
Performa tulis
Dampak penambahan IMCI terhadap performa tulis umumnya dalam kisaran 5%. Saat menguji workload oltp_insert dengan suite pengujian Sysbench, performa tulis menurun sekitar 3% setelah IMCI ditambahkan.

Dapatkan dukungan ahli

Jika Anda memiliki pertanyaan tentang IMCI, Anda dapat bergabung dengan grup DingTalk kami dengan mencari ID grup 27520023189. Anda dapat langsung bertanya kepada ahli di grup tersebut menggunakan fitur @ mention.

FAQ

Kueri tidak menggunakan IMCI

Setelah Anda menambahkan node IMCI read-only, kueri hanya menggunakan IMCI untuk akselerasi jika kondisi berikut terpenuhi: IMCI harus dibuat untuk semua tabel dalam kueri, biaya eksekusi perkiraan kueri harus melebihi ambang batas tertentu, dan kueri harus diarahkan ke node IMCI read-only. Jika kueri tidak menggunakan IMCI, pecahkan masalah tersebut dengan mengikuti langkah-langkah berikut:

Konfirmasi bahwa kueri SQL diarahkan ke node IMCI read-only.
- Periksa apakah Node Settings dari endpoint mencakup node IMCI read-only.
- Gunakan fitur SQL Explorer untuk mengonfirmasi apakah kueri diarahkan ke node IMCI read-only.
Jika Anda menggunakan Cluster Endpoint dengan Transactional/Analytical Processing Splitting diaktifkan, proksi database akan mengarahkan kueri ke node IMCI read-only jika biaya eksekusi perkiraan-nya melebihi ambang batas yang ditetapkan oleh loose_imci_ap_threshold atau loose_cost_threshold_for_imci. Anda juga dapat menambahkan /*FORCE_IMCI_NODES*/ hint sebelum kata kunci SELECT untuk memaksa kueri diarahkan ke node IMCI read-only. Untuk informasi lebih lanjut, lihat Konfigurasikan ambang batas untuk routing permintaan otomatis. Contoh:
Untuk versi kernel 8.0.1.1.39 dan 8.0.2.2.23 atau yang lebih baru, parameter loose_imci_ap_threshold sudah tidak digunakan lagi. Gunakan parameter loose_cost_threshold_for_imci sebagai gantinya.
```
/*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;
```
Membuat endpoint baru dapat memastikan bahwa kueri SQL selalu diarahkan ke node IMCI read-only untuk dieksekusi. Untuk informasi lebih lanjut, lihat Buat endpoint kustom.
Konfirmasi bahwa biaya eksekusi perkiraan kueri melebihi ambang batas yang dikonfigurasi.
Di node IMCI read-only, pengoptimal memperkirakan biaya kueri. Jika biaya eksekusi perkiraan lebih tinggi daripada ambang batas yang ditetapkan oleh loose_imci_ap_threshold atau loose_cost_threshold_for_imci, kueri akan menggunakan IMCI. Jika tidak, kueri akan menggunakan indeks berbasis baris asli.
Setelah Anda mengonfirmasi bahwa kueri SQL diteruskan ke node read-only In-Memory Column Index (IMCI), jika Anda menggunakan EXPLAIN untuk melihat rencana eksekusi dan menemukan bahwa IMCI masih tidak digunakan, Anda dapat membandingkan biaya eksekusi perkiraan dengan ambang batas yang telah ditetapkan untuk menentukan apakah IMCI tidak digunakan karena biaya eksekusi perkiraan terlalu rendah. Anda dapat memperoleh biaya eksekusi perkiraan dari kueri SQL terakhir dengan mengkueri variabel Last_query_cost:
```
-- Gunakan EXPLAIN untuk melihat rencana eksekusi kueri SQL.
EXPLAIN SELECT * FROM t1;
-- Dapatkan biaya eksekusi perkiraan dari kueri terakhir.
SHOW STATUS LIKE 'Last_query_cost';
```
Jika Anda menggunakan cluster endpoint untuk terhubung ke database, kami menyarankan Anda menambahkan sintaks HINT /*ROUTE_TO_LAST_USED*/ sebelum SHOW STATUS LIKE 'Last_query_cost' untuk memastikan Anda dapat mengkueri biaya eksekusi perkiraan dari pernyataan sebelumnya di node yang benar. Misalnya, /*ROUTE_TO_LAST_USED*/SHOW STATUS LIKE 'Last_query_cost';
Jika biaya eksekusi perkiraan kueri berada di bawah ambang batas, pertimbangkan untuk menyesuaikan nilai loose_imci_ap_threshold atau loose_cost_threshold_for_imci. Misalnya, Anda dapat menggunakan hint untuk menyesuaikan ambang batas untuk kueri tunggal:
```
/*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;
```
Konfirmasi bahwa tabel dan kolom dalam kueri sepenuhnya dicakup oleh IMCI.
Anda dapat menggunakan prosedur tersimpan bawaan dbms_imci.check_columnar_index('<query_string>') untuk memeriksa cakupan IMCI terhadap tabel dan kolom dalam kueri. Untuk informasi lebih lanjut, lihat Periksa apakah IMCI dibuat untuk tabel dan kolom dalam kueri. Contoh:
```
CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');
```
Jika kueri tidak sepenuhnya dicakup, prosedur tersebut akan mengembalikan tabel dan kolom yang tidak tercakup. Anda kemudian harus membuat IMCI untuk masing-masing. Jika kueri sepenuhnya dicakup, prosedur tersebut akan mengembalikan set hasil kosong.
Periksa fitur SQL yang tidak didukung.
Tinjau sintaks dan batasan IMCI untuk mengonfirmasi apakah fitur SQL tertentu didukung oleh IMCI. Untuk informasi lebih lanjut, lihat Sintaks dan batasan IMCI.

Jika kueri SQL masih tidak menggunakan IMCI setelah Anda mengikuti langkah-langkah ini, dapatkan dukungan ahli atau hubungi kami.

Membuat IMCI yang tepat

Tambahkan In-Memory Column Index (IMCI) untuk kolom yang diperlukan dalam pernyataan SQL Anda. Untuk informasi lebih lanjut, lihat Periksa apakah IMCI dibuat untuk tabel atau kolom dalam pernyataan SQL.

Pernyataan SQL hanya dapat menggunakan IMCI untuk kueri jika IMCI sepenuhnya mencakup semua kolom yang diperlukan. Jika kolom yang diperlukan dalam pernyataan SQL tidak sepenuhnya tercakup, gunakan pernyataan ALTER TABLE untuk menambahkan IMCI. PolarDB menyediakan serangkaian prosedur tersimpan bawaan untuk membantu operasi ini.

Catatan

Gunakan prosedur tersimpan dbms_imci.columnar_advise() untuk mendapatkan pernyataan Data Definition Language (DDL) yang diperlukan untuk membuat IMCI bagi pernyataan SQL tertentu. Jika Anda membuat IMCI menggunakan pernyataan DDL ini, pernyataan SQL tersebut dijamin sepenuhnya tercakup oleh IMCI. Untuk informasi lebih lanjut, lihat Dapatkan pernyataan DDL untuk membuat IMCI.
```
dbms_imci.columnar_advise('<query_string>');
```
Gunakan prosedur tersimpan dbms_imci.columnar_advise_begin(), dbms_imci.columnar_advise_end(), dan dbms_imci.columnar_advise() untuk mendapatkan pernyataan DDL yang diperlukan untuk membuat IMCI bagi sejumlah pernyataan SQL. Untuk informasi lebih lanjut, lihat Dapatkan pernyataan DDL untuk membuat IMCI secara batch.

Dataset terurut	Dataset tidak terurut
0.88	4.35