AnalyticDB：Gunakan kunci pengurutan dan indeks himpunan kasar untuk mempercepat kueri dalam tabel berorientasi kolom

Informasi latar belakang

Saat membuat tabel, Anda dapat menentukan satu atau beberapa kolom sebagai kunci pengurutan. Setelah data ditulis ke tabel, Anda dapat mengurutkan data tabel berdasarkan kunci pengurutan tersebut.

Pengurutan mempercepat kueri yang dibatasi rentang. Nilai minimum dan maksimum untuk setiap kolom disimpan di database. Jika kueri membatasi rentang dalam klausa WHERE, prosesor kueri AnalyticDB for PostgreSQL dapat menggunakan nilai minimum dan maksimum untuk melewati blok di luar rentang tertentu selama pemindaian tabel.

Sebagai contoh, asumsikan bahwa sebuah tabel menyimpan tujuh tahun data yang diurutkan berdasarkan tanggal, dan kueri menentukan rentang tanggal satu bulan. Dalam hal ini, hanya 1/(7 × 12) dari data tabel yang perlu dipindai, dan 98,8% blok disk dapat dieliminasi dari pemindaian. Jika data tidak diurutkan berdasarkan tanggal, semua blok disk mungkin dipindai.

• Perbandingan Kinerja

  • Berikut adalah contoh bagaimana pengurutan majemuk meningkatkan kinerja kueri untuk indeks himpunan kasar dibandingkan dengan pemindaian tabel penuh.

    Tabel TPC-H Lineitem yang menyimpan tujuh tahun data digunakan dalam contoh ini. Kemudian, kueri pada data yang menggunakan dan tidak menggunakan bidang l_shipdate dibandingkan. Kedua kueri tersebut dibatasi rentang.

    Catatan Implementasi TPC ini berasal dari TPC Benchmark dan tidak dapat dibandingkan dengan hasil TPC Benchmark yang diterbitkan, karena implementasi ini tidak mematuhi semua persyaratan TPC Benchmark.
    Prosedur Pengujian:

    1. Buat instance 32-node.
    2. Tulis 13 miliar baris ke tabel Lineitem.
    3. Kueri data dalam rentang waktu dari 1997-09-01 hingga 1997-09-30.
       • Data tidak diurutkan berdasarkan l_shipdate.
       • Data diurutkan berdasarkan l_shipdate.

Tentukan kolom sebagai kunci pengurutan saat Anda membuat tabel

Contoh

create table test(date text, time text, open float, high float, low float, volume int) with(APPENDONLY=true,ORIENTATION=column) ORDER BY (volume);

Sintaks

CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE nama_tabel (
[ { nama_kolom tipe_data  ...} ]
)
[ DISTRIBUTED BY (kolom, [ ... ] ) | DISTRIBUTED RANDOMLY ]
[ ORDER BY (kolom, [ ... ] )]

Jika versi kernel lebih awal dari 20210326, gunakan pernyataan berikut untuk menentukan kunci pengurutan: SORTKEY (kolom, [ ... ])

Urutkan tabel

• Gunakan Pengurutan Majemuk untuk Mengurutkan Data

  • SORT [namatabel]

  • Jika versi kernel lebih awal dari 20210326, gunakan pernyataan berikut untuk menentukan kunci pengurutan:

    VACUUM SORT ONLY [namatabel]

• Gunakan Pengurutan Berseling untuk Mengurutkan Data

  • MULTISORT [namatabel]

  • Jika versi kernel lebih awal dari 20210326, gunakan pernyataan berikut untuk menentukan kunci pengurutan:

    VACUUM REINDEX [namatabel]

Setelah Anda menjalankan pernyataan SORT atau MULTISORT pada tabel, tabel akan diurutkan berdasarkan kunci pengurutan yang ditentukan. Saat Anda menambahkan baris ke tabel yang telah diurutkan, jumlah data yang tidak diurutkan bertambah dan kinerja penyaringan dalam himpunan kasar mungkin menurun. Untuk memastikan kinerja penyaringan dalam himpunan kasar, Anda harus menjalankan pernyataan SORT atau VACUUM REINDEX (MULTISORT) secara berkala untuk mengurutkan ulang tabel.

Ubah kunci pengurutan

Anda dapat menjalankan pernyataan berikut untuk mengubah kunci pengurutan tabel berorientasi kolom yang ada sesuai dengan kebutuhan bisnis Anda:

ALTER [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE nama_tabel SET ORDER BY (kolom, [ ... ] )

Pernyataan ini hanya mengubah katalog dan tidak segera mengurutkan data. Untuk mengurutkan data, Anda harus menjalankan pernyataan SORT nama_tabel.

Contoh

ALTER TABLE test SET ORDER BY(high,low);

Jika versi kernel lebih awal dari 20210326, gunakan pernyataan berikut untuk mengubah kunci pengurutan:

ALTER TABLE test SET SORTKEY(high,low);

Tentukan kunci pengurutan dan pilih metode pengurutan

Jika Anda selalu perlu menjalankan kueri SQL yang berisi satu atau lebih nilai kolom atau yang dibatasi rentang, seperti kolom tanggal, Anda dapat menggunakan kolom-kolom tersebut sebagai kunci pengurutan. Ini mempercepat kueri SQL yang disebutkan sebelumnya dengan mengurutkan data berdasarkan indeks himpunan kasar.

Kami merekomendasikan Anda untuk menggunakan pengurutan majemuk dalam kasus umum. Pengurutan majemuk perlu melakukan analisis tambahan pada data. Oleh karena itu, VACUUM REINDEX bisa memakan waktu lebih lama daripada VACUUM SORT ONLY untuk tabel berseling.

Jika Anda jarang perlu menjalankan kueri SQL yang berisi kolom tertentu, Anda dapat menggunakan pengurutan berseling untuk mempercepat kueri. Kunci pengurutan berseling dapat menggunakan hingga delapan kolom.

Perbandingan kinerja antara pengurutan majemuk dan pengurutan berseling

Dalam bagian ini, dua tabel yang berisi data yang sama diurutkan secara terpisah menggunakan pengurutan majemuk dan pengurutan berseling. Hasil kueri pada tabel menunjukkan bahwa kedua metode pengurutan menunjukkan tingkat kinerja yang berbeda dalam skenario yang berbeda.

Dua tabel dengan empat kolom yang sama (id, num1, num2, dan value) digunakan dalam contoh ini. (id,num1,num2) ditentukan sebagai kunci pengurutan. Setiap tabel berisi total 10 juta baris. Tabel yang digunakan dalam contoh ini bukan tabel besar khusus untuk AnalyticDB for PostgreSQL, tetapi perbedaan kinerja antara pengurutan majemuk dan pengurutan berseling dapat dilihat dengan tabel-tabel tersebut. Perbedaan kinerja lebih signifikan dalam tabel besar.

• Buat Dua Tabel dan Atur Kunci Pengurutan yang Sama untuk Mereka

  • CREATE TABLE test(id int, num1 int, num2 int, value varchar) 
    with(APPENDONLY=TRUE, ORIENTATION=column)
    DISTRIBUTED BY(id)
    ORDER BY(id, num1, num2);
    
    CREATE TABLE test_multi(id int, num1 int, num2 int, value varchar) 
    with(APPENDONLY=TRUE, ORIENTATION=column)
    DISTRIBUTED BY(id)
    ORDER BY(id, num1, num2);

• Tulis 10 Juta Baris Data ke Setiap Tabel

  • INSERT INTO test(id, num1, num2, value) select g, 
    (random()*10000000)::int, 
    (random()*10000000)::int,
    (array['foo', 'bar', 'baz', 'quux', 'boy', 'girl', 'mouse', 'child', 'phone'])[floor(random() * 10 +1)]
    FROM generate_series(1, 10000000) as g;
    
    INSERT INTO test_multi SELECT * FROM test;
    
    adbpgadmin=# SELECT count(*) FROM test;
      count
    ----------
     10000000
    (1 row)
    
    adbpgadmin=# SELECT count(*) FROM test_multi;
      count
    ----------
     10000000
    (1 row)

• Urutkan Dua Tabel Secara Terpisah Menggunakan Pengurutan Majemuk dan Pengurutan Berseling

  • SORT test;
    MULTISORT test_multi;

• Bandingkan Kinerja Kueri Titik

  • • Kueri difilter pada kolom utama.

      -- Q1 difilter pada kolom utama.
      select * from test where id = 100000;
      select * from test_multi where id = 100000;

    • Kueri difilter pada kolom kedua.

      -- Q2 difilter pada kolom kedua.
      select * from test where num1 = 8766963;
      select * from test_multi where num1 = 8766963;

    • Kueri difilter pada kolom kedua dan ketiga.

      -- Q3 difilter pada kolom kedua dan ketiga.
      select * from test where num1 = 100000 and num2=2904114;
      select * from test_multi where num1 = 100000 and num2=2904114;

    Tabel 1. Hasil Perbandingan Kinerja

    Metode pengurutan	Q1	Q2	Q3
    Pengurutan majemuk	0,026 detik	3,95 detik	4,21 detik
    Pengurutan berseling	0,55 detik	0,42 detik	0,071 detik

• Bandingkan Kinerja Kueri Rentang

  • • Kueri difilter pada kolom utama.

      -- Q1 difilter pada kolom utama.
      select count(*) from test where id>5000 and id < 100000;
      select count(*) from test_multi where id>5000 and id < 100000;

    • Kueri difilter pada kolom kedua.

      -- Q2 difilter pada kolom kedua.
      select count(*) from test where num1 >5000 and num1 <100000;
      select count(*) from test_multi where num1 >5000 and num1 <100000;

    • Kueri difilter pada kolom kedua dan ketiga.

      -- Q3 difilter pada kolom kedua dan ketiga.
      select count(*) from test where num1 >5000 and num1 <100000; and num2 < 100000;
      select count(*) from test_multi where num1 >5000 and num1 <100000 and num2 < 100000;

    Tabel 2. Hasil Perbandingan Kinerja

    Metode pengurutan	Q1	Q2	Q3
    Pengurutan majemuk	0,07 detik	3,35 detik	3,64 detik
    Pengurutan berseling	0,44 detik	0,28 detik	0,047 detik

• Kesimpulan Pengujian

  • • Q1 menggunakan kolom utama dari kunci pengurutan untuk memfilter data. Dalam hal ini, pengurutan majemuk memiliki waktu respons kueri yang lebih singkat daripada pengurutan berseling.
    • Q2 menggunakan kolom non-kunci utama dari kunci pengurutan untuk memfilter data. Dalam hal ini, pengurutan majemuk tidak efektif dan pengurutan berseling memiliki kinerja kueri yang jauh lebih baik.
    • Q3 menggunakan kolom non-kunci utama dari kunci pengurutan untuk memfilter data. Dalam hal ini, pengurutan berseling lebih cepat dan lebih efektif daripada pengurutan majemuk. Semakin banyak kolom kunci pengurutan berseling yang digunakan, semakin baik kinerja kueri pengurutan berseling.