Lindorm:FAQ Lindorm SQL

T: Bagaimana cara mengatasi atau menghindari kueri yang tidak efisien?

J: Jika kueri Anda mengembalikan error This query may be a full table scan and thus may have unpredictable performance, kueri tersebut dianggap tidak efisien.

Apa itu kueri yang tidak efisien? Pada LindormTable, kueri dengan kondisi filter yang tidak dapat memanfaatkan primary key atau indeks yang ada secara efektif akan memicu pemindaian tabel penuh. Kueri semacam ini dianggap tidak efisien. Secara default, LindormTable memblokirnya untuk melindungi performa dan stabilitas database.

Aturan pencocokannya mengikuti aturan awalan paling kiri (leftmost prefix rule) — aturan yang sama yang digunakan MySQL untuk indeks komposit. Sistem mencocokkan kolom dalam klausa WHERE terhadap kolom primary key (atau kunci indeks) mulai dari kolom paling kiri. Jika kueri melewatkan kolom pertama, kunci tersebut tidak digunakan dan hasilnya adalah pemindaian tabel penuh.

Sebagai contoh, jika tabel test memiliki primary key komposit (p1, p2, p3) dan Anda menjalankan:

SELECT * FROM test WHERE p2 < 30;

Kueri tersebut melewatkan p1, sehingga LindormTable tidak dapat menggunakan primary key. Seluruh tabel dipindai untuk memenuhi kondisi p2 < 30.

Untuk memperbaiki atau menghindarinya:

• Sertakan kolom pertama primary key dalam klausa WHERE, sesuai aturan awalan paling kiri.

• Rancang ulang primary key tabel. Lihat Cara merancang primary key untuk tabel lebar.

• Buat secondary index pada kolom yang Anda kueri. Lihat Secondary indexes.

• Untuk kueri multidimensi yang mencakup beberapa kolom, buat search index. Lihat Search indexes.

• Untuk memaksa eksekusi kueri yang tidak efisien, tambahkan petunjuk /*+ _l_allow_filtering_ */:

  SELECT /*+ _l_allow_filtering_ */ * FROM dt WHERE nonPK = 100;

T: Mengapa kueri GROUP BY gagal dengan error "subPlan groupby keys"?

J: Pesan error:

The diff group keys of subPlan is over lindorm.aggregate.subplan.groupby.keys.limit=..., it may cost a lot memory so we shutdown this SubPlan

Operasi GROUP BY menghasilkan terlalu banyak kelompok. Jumlah kelompok yang besar mengonsumsi memori berlebihan dan meningkatkan beban instans, sehingga LindormTable menghentikan subplan tersebut.

Untuk mengatasinya:

• Tambahkan kondisi filter untuk mengurangi jumlah kelompok sebelum agregasi.

• Untuk skenario agregasi multidimensi, buat search index untuk mengalihkan beban kerja. Lihat Search indexes.

• Untuk menaikkan ambang batas jumlah kelompok, hubungi dukungan teknis Lindorm (DingTalk ID: s0s3eg3).

T: Mengapa SELECT * pada tabel kolom dinamis gagal dengan error "Limit not set"?

J: Pesan error:

Limit of this select statement is not set or exceeds config when select all columns from table with property DYNAMIC_COLUMNS=true

Tabel dengan kolom dinamis tidak memiliki skema tetap dan dapat berisi jumlah kolom yang besar serta tidak dapat diprediksi. Menjalankan SELECT * tanpa batasan baris menyebabkan I/O tinggi dan meningkatkan beban instans, sehingga LindormTable mewajibkan klausa LIMIT pada kueri semacam ini.

Tambahkan klausa LIMIT pada pernyataan SELECT:

SELECT * FROM test LIMIT 10;

T: Mengapa kueri gagal dengan pesan "Code grows beyond 64 KB"?

J: Mesin Lindorm SQL menggunakan kompilasi Just-In-Time (JIT): ia menghasilkan bytecode dari rencana fisik kueri dan mengompilasinya saat waktu proses. Error ini berarti bytecode untuk metode yang dihasilkan melebihi batas 64 KB yang diberlakukan oleh Java Virtual Machine (JVM).

Penyebab paling umum adalah predikat dalam pernyataan SQL yang terlalu panjang atau terlalu kompleks, sehingga menghasilkan bytecode yang terlalu besar untuk dieksekusi.

Sederhanakan ekspresi predikat dalam pernyataan SQL. Pecah kondisi kompleks menjadi bagian-bagian yang lebih kecil atau tulis ulang logikanya untuk mengurangi ukuran bytecode.

T: Mengapa kueri gagal dengan pesan "The estimated memory used by the query exceeds the maximum limit"?

J: Mesin SQL mengonsumsi memori signifikan saat memproses set hasil — selama agregasi, pengurutan, atau deduplikasi. Karena Lindorm SQL dirancang untuk beban kerja online dengan konkurensi tinggi, setiap kueri dibatasi hingga 8 MB memori secara default. Melebihi batas ini memicu exception overflow memori.

Langkah 1 — Diagnosis sebelum bertindak:

Periksa rencana eksekusi untuk menentukan apakah operator agregasi dan pengurutan didorong ke mesin penyimpanan atau dieksekusi di mesin SQL. Lihat Interpret an execution plan.

• Jika operator berat dijalankan di mesin SQL, optimasi kueri adalah solusi yang tepat (lihat Opsi 1).

• Jika operator sudah didorong ke bawah dan kueri sudah dioptimalkan, naikkan batas memori (lihat Opsi 2).

Opsi 1 — Optimalkan kueri (disarankan):

Dorong agregasi dan pengurutan ke mesin penyimpanan menggunakan indeks, dan perketat kondisi filter untuk mengurangi jumlah data yang diproses oleh mesin SQL.

Opsi 2 — Naikkan batas memori:

Jika kueri sudah dioptimalkan dan Anda memerlukan batas yang lebih tinggi, sesuaikan QUERY_MAX_MEM menggunakan ALTER SYSTEM:

ALTER SYSTEM SET QUERY_MAX_MEM = 8388608;

Periksa nilai saat ini dengan pernyataan SHOW VARIABLES.

Jika versi mesin SQL Anda lebih awal dari 2.9.6.0, hubungi dukungan teknis Lindorm (DingTalk ID: s0s3eg3) untuk menaikkan batas tersebut.

T: Mengapa tidak disarankan menggunakan banyak kondisi IS NULL dalam satu klausa WHERE?

J: Di LindormTable, IS NULL harus menangani dua kasus: "kolom ada dengan nilai NULL" dan "kolom tidak ada atau belum pernah ditulis". Ketika pernyataan SQL berisi banyak predikat IS NULL, mesin SQL dapat mengembangkan kondisi tersebut menjadi kombinasi selama kompilasi. N predikat IS NULL secara teoretis dapat menghasilkan hingga 2^N cabang, yang secara signifikan meningkatkan waktu kompilasi dan penggunaan memori. Dalam kasus parah, hal ini dapat memengaruhi eksekusi kueri dan stabilitas instans.

Rekomendasi: Hindari menggabungkan banyak kondisi column IS NULL dalam satu pernyataan SELECT, UPDATE, atau DELETE. Pertimbangkan pendekatan berikut:

• Persempit rentang kueri menggunakan primary key, secondary index, atau search index bila memungkinkan.

• Jika logika bisnis Anda sering memeriksa apakah bidang kosong atau ada, nyatakan status bisnis tersebut secara eksplisit saat menulis — misalnya, dengan nilai default atau kolom status.

• Untuk pemrosesan batch, pecah pekerjaan menjadi beberapa pernyataan SQL dengan kondisi yang lebih sederhana, atau ambil seluruh primary key terlebih dahulu lalu proses secara batch berdasarkan primary key.

T: Mengapa tidak disarankan menggunakan banyak grup OR yang digabungkan dengan AND dalam klausa WHERE?

Masalah: Pernyataan SQL yang klausa WHERE-nya menghubungkan beberapa grup OR dalam tanda kurung dengan AND, misalnya:

SELECT * FROM orders
WHERE (status = 1 OR status = 2)
  AND (pay_type = 'wechat' OR pay_type = 'alipay')
  AND (region = 'CN' OR region = 'US')
  AND ...;

Semakin dalam tingkat nesting-nya, semakin besar risikonya. Hal ini berlaku untuk pernyataan SELECT, UPDATE, dan DELETE.

J: Sebelum mengeksekusi kueri, pengoptimal harus mengubah klausa WHERE menjadi Disjunctive Normal Form (DNF) — kumpulan OR dari kondisi AND — agar dapat memilih jalur akses indeks optimal untuk setiap kondisi independen. Saat pengoptimal menemui (A OR B) AND (C OR D), ia harus menerapkan hukum distributif untuk mengembangkannya:

(A∨B)∧(C∨D) ⇒ (A∧C)∨(A∧D)∨(B∧C)∨(B∧D)

(A or B) and (C or D) ⇒ (A and C) or (A and D) or (B and C) or (B and D)

• 2 grup, masing-masing dengan 2 cabang: 2×2 = 4 suku kombinasi.

• 3 grup, masing-masing dengan 2 cabang: 2×2×2 = 8 suku kombinasi.

Saat jumlah grup dan cabang per grup bertambah, jumlah suku kombinasi meningkat secara eksponensial. Hal ini dapat secara signifikan meningkatkan waktu kompilasi dan penggunaan memori oleh pengoptimal, dan dalam kasus parah dapat memengaruhi eksekusi kueri dan stabilitas instans.

Rekomendasi:

• Hindari menggabungkan banyak grup OR dalam tanda kurung dengan AND dalam satu pernyataan SQL.

• Tulis ulang kondisi OR pada kolom yang sama dengan IN — misalnya, status IN (1, 2) — untuk mengurangi jumlah cabang yang dikembangkan.

• Persempit rentang kueri menggunakan primary key, secondary index, atau search index bila memungkinkan.

• Untuk skenario pencocokan multidimensi, gunakan search index. Lihat Search indexes.

• Jika kondisi kompleks diperlukan oleh logika bisnis, pecah pekerjaan menjadi beberapa pernyataan SQL yang lebih sederhana, atau ambil seluruh primary key terlebih dahulu lalu proses secara batch berdasarkan primary key.

T: Bagaimana cara mendiagnosis dan mengatasi error batas memori untuk kueri tertentu?

A: Jika kueri gagal karena kesalahan batas memori, ikuti langkah-langkah berikut untuk mendiagnosis penyebab utama dan memilih solusi yang sesuai:

Langkah 1 — Periksa rencana eksekusi:

Jalankan EXPLAIN pada kueri yang gagal untuk melihat rencana eksekusinya. Lihat Interpret an execution plan untuk detailnya.

Dalam rencana eksekusi, identifikasi apakah operator yang intensif memori — seperti agregasi, pengurutan, dan deduplikasi — dijalankan di mesin SQL atau didorong ke mesin penyimpanan. Operator yang dijalankan di mesin SQL mengonsumsi memori dari batas per kueri, sedangkan operator yang didorong ke bawah menggunakan sumber daya mesin penyimpanan.

Langkah 2 — Coba optimalkan kueri:

Jika operator yang intensif memori dijalankan di mesin SQL, coba pendekatan berikut untuk mengurangi konsumsi memori:

• Untuk operator agregasi (GROUP BY, COUNT, SUM, AVG): Buat secondary index atau search index pada kolom yang dikelompokkan atau diagregasi untuk mengaktifkan pushdown operator ke mesin penyimpanan.

• Untuk operator pengurutan (ORDER BY): Pastikan kolom pengurutan sejajar dengan indeks yang ada untuk menghindari pengurutan di memori. Atau, tambahkan kondisi WHERE yang lebih ketat untuk mengurangi ukuran dataset sebelum pengurutan.

• Untuk operator deduplikasi (DISTINCT): Gunakan search index untuk kueri pada kolom dengan kardinalitas tinggi, atau tambahkan kondisi filter selektif untuk mengurangi jumlah baris yang diproses.

Langkah 3 — Naikkan batas memori jika diperlukan:

Jika semua operator sudah didorong ke bawah dan kueri tidak dapat dioptimalkan lebih lanjut, naikkan nilai QUERY_MAX_MEM menggunakan ALTER SYSTEM:

ALTER SYSTEM SET QUERY_MAX_MEM = <new_value_in_bytes>;

Periksa nilai saat ini dengan pernyataan SHOW VARIABLES.

T: Mengapa pembuatan indeks sekunder gagal dengan pesan “Executing job number exceed, max job number = 8”?

J: Setiap instans Lindorm mengizinkan maksimal 8 tugas pembuatan secondary index secara konkuren. Jika 8 tugas sudah berjalan, upaya pembuatan indeks baru akan gagal.

Hindari membuat banyak secondary index secara bersamaan. Jika Anda perlu membuat banyak indeks sekaligus, hubungi dukungan teknis Lindorm (DingTalk ID: s0s3eg3).

T: Setelah menghapus kolom, mengapa menambahkan kembali kolom dengan nama yang sama gagal dengan "column is under deleting"?

A: Setelah Anda menghapus kolom, Mesin Tabel Lebar secara asinkron membersihkan data kolom tersebut dari memori, penyimpanan panas, dan penyimpanan dingin. Sistem mencegah penambahan kembali kolom dengan nama yang sama hingga proses pembersihan selesai guna menghindari data kotor akibat ketidakcocokan tipe atau tabrakan data.

Pembersihan berjalan di latar belakang dan dapat memakan waktu lama. Untuk mempercepatnya, jalankan pernyataan berikut pada tabel (ganti dt dengan nama tabel Anda):

-- Flush residual in-memory data to storage.
ALTER TABLE dt FLUSH;

-- Run compaction to merge and remove deleted data.
ALTER TABLE dt COMPACT;

Setelah pembersihan selesai, tambahkan kembali kolom tersebut.

T: Setelah membuat secondary index, mengapa penulisan data gagal dengan error "User-Defined-Timestamp"?

J: Pesan error:

Performing put operations with User-Defined-Timestamp in indexed column on MULTABLE_LATEST table is unsupported

Saat Anda menulis data dengan timestamp kustom eksplisit — misalnya, menggunakan petunjuk /*+ _l_ts */ dalam pernyataan UPSERT — baik tabel utama maupun tabel secondary index harus memiliki mutabilitas yang diatur ke MUTABLE_ALL. Namun, Lindorm secara default mengatur tabel dan indeks baru ke MUTABLE_LATEST demi alasan performa. Menulis dengan timestamp kustom ke tabel indeks dengan mutabilitas MUTABLE_LATEST memicu error ini.

Properti MUTABILITY tidak dapat diubah setelah tabel indeks dibuat. Anda harus menghapus indeks yang ada, memperbarui mutabilitas tabel utama, lalu membuat ulang indeks tersebut.

1. Nonaktifkan dan hapus secondary index yang ada:

   -- Disable the index.
   ALTER INDEX IF EXISTS <original_secondary_index_name> ON <primary_table_name> DISABLED;
   
   -- Drop the index.
   DROP INDEX IF EXISTS <original_secondary_index_name> ON <primary_table_name>;

   Lihat Hapus indeks sekunder.

2. Atur mutabilitas tabel utama ke MUTABLE_ALL:

   ALTER TABLE IF EXISTS <primary_table_name> SET MUTABILITY='MUTABLE_ALL';

3. Buat secondary index baru. Lihat CREATE INDEX.

T: Mengapa pembaruan batch tidak didukung, atau mengapa muncul error "Update's WHERE clause can only contain PK columns"?

J: Secara default, hanya pembaruan satu baris yang didukung. Untuk informasi tentang cara mengaktifkan pembaruan batch, lihat Batch operations FAQ.

T: Bagaimana cara mengaktifkan penghapusan batch?

J: Untuk informasi tentang cara mengaktifkan dan mengonfigurasi penghapusan batch, lihat Batch operations FAQ.