SQLkonstruksi untuk kueri paralel di Aurora My SQL

Di bagian berikut, Anda dapat menemukan detail lebih lanjut tentang mengapa SQL pernyataan tertentu menggunakan atau tidak menggunakan query paralel. Bagian ini juga merinci bagaimana SQL fitur Aurora My berinteraksi dengan query paralel. Informasi ini dapat membantu Anda mendiagnosis masalah performa untuk klaster yang menggunakan kueri paralel atau memahami cara kueri paralel berlaku untuk beban kerja tertentu Anda.

Keputusan untuk menggunakan kueri paralel bergantung pada banyak faktor yang terjadi pada saat pernyataan itu berjalan. Dengan demikian, kueri paralel dapat digunakan untuk kueri tertentu selalu, tidak pernah, atau hanya dalam kondisi tertentu.

Tip

Ketika Anda melihat contoh-contoh ini diHTML, Anda dapat menggunakan widget Salin di sudut kanan atas setiap daftar kode untuk menyalin kode untuk mencoba SQL sendiri. Dengan menggunakan widget Salin, Anda tidak perlu menyalin karakter tambahan di sekitar prompt mysql> dan baris lanjutan ->.

EXPLAINpernyataan

Seperti yang ditunjukkan dalam contoh di seluruh bagian ini, pernyataan EXPLAIN menunjukkan apakah setiap tahap kueri saat ini memenuhi syarat untuk kueri paralel. Pernyataan itu juga menunjukkan aspek mana dari kueri yang dapat diturunkan ke lapisan penyimpanan. Berikut ini adalah item terpenting dalam rencana kueri:

• Nilai selain NULL untuk kolom key menunjukkan bahwa kueri dapat dilakukan secara efisien menggunakan pencarian indeks, dan kueri paralel tidak memungkinkan.

• Nilai kecil untuk kolom rows (nilai bukan dalam jutaan) menunjukkan bahwa kueri tersebut tidak mengakses cukup data untuk membuat kueri paralel yang bermanfaat. Ini berarti kueri paralel tidak memungkinkan.

• Kolom Extra menunjukkan jika kueri paralel yang diharapkan akan digunakan. Output ini terlihat seperti contoh berikut.

  Using parallel query (A columns, B filters, C exprs; D extra)
  

  Angka columns menunjukkan jumlah kolom yang dimaksud dalam blok kueri.

  Angka filters menunjukkan jumlah predikat WHERE yang menunjukkan perbandingan sederhana antara nilai kolom dan konstanta. Perbandingannya dapat berupa kesetaraan, ketidaksetaraan, atau rentang. Aurora dapat memaralelkan jenis predikat ini dengan sangat efektif.

  Angka exprs menunjukkan jumlah ekspresi seperti panggilan fungsi, operator, atau ekspresi lainnya yang juga dapat diparalelkan, meskipun tidak sama efektifnya dengan ketentuan filter.

  Angka extra menunjukkan jumlah ekspresi yang tidak dapat diturunkan dan dilakukan oleh simpul kepala.

Contohnya, pertimbangkan output EXPLAIN berikut ini.

mysql> explain select p_name, p_mfgr from part
    -> where p_brand is not null
    -> and upper(p_type) is not null
    -> and round(p_retailprice) is not null;
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+
| id | select_type | table |...| rows     | Extra                                                                      |
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+
|  1 | SIMPLE      | part  |...| 20427936 | Using where; Using parallel query (5 columns, 1 filters, 2 exprs; 0 extra) |
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+

Informasi dari kolom Extra menunjukkan bahwa lima kolom diekstraksi dari setiap baris untuk mengevaluasi ketentuan kueri dan menyusun set hasil. Satu predikat WHERE mencakup sebuah filter, yaitu, kolom yang langsung diuji dalam klausa WHERE. Dua klausa WHERE memerlukan evaluasi ekspresi yang lebih rumit, dalam hal ini mencakup panggilan fungsi. Bidang 0 extra mengonfirmasi bahwa semua operasi dalam klausa WHERE diturunkan ke lapisan penyimpanan sebagai bagian dari pemrosesan kueri paralel.

Dalam kasus di mana kueri paralel tidak dipilih, Anda biasanya dapat menyimpulkan alasan dari output kolom EXPLAIN lain. Contohnya, nilai rows mungkin terlalu kecil, atau kolom possible_keys mungkin menunjukkan bahwa kueri dapat menggunakan pencarian indeks alih-alih pemindaian sarat data. Contoh berikut menunjukkan sebuah kueri di mana pengoptimal dapat memperkirakan bahwa kueri tersebut hanya akan memindai sejumlah kecil baris. Hal itu dilakukan berdasarkan karakteristik kunci primer. Dalam hal ini, kueri paralel tidak diperlukan.

mysql> explain select count(*) from part where p_partkey between 1 and 100;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | part  | range | PRIMARY       | PRIMARY | 4       | NULL |   99 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

Output yang menunjukkan apakah kueri paralel akan digunakan mempertimbangkan semua faktor yang tersedia saat pernyataan EXPLAIN dijalankan. Pengoptimal mungkin mengambil pilihan lain saat kueri benar-benar dijalankan, jika situasi berubah pada saat itu. Contohnya, EXPLAIN dapat melaporkan bahwa suatu pernyataan akan menggunakan kueri paralel. Namun saat kueri benar-benar dijalankan nantinya, kueri tersebut mungkin tidak menggunakan kueri paralel berdasarkan kondisi pada saat itu. Kondisi tersebut dapat mencakup beberapa kueri paralel lainnya yang berjalan secara serentak. Kondisi lainnya juga dapat mencakup baris yang dihapus dari tabel, indeks baru yang dibuat, terlalu banyak waktu yang terlewati dalam transaksi terbuka, dan sebagainya.

Klausul WHERE

Agar menggunakan pengoptimalan kueri paralel, suatu kueri harus mencakup klausa WHERE.

Pengoptimalan kueri paralel mempercepat berbagai jenis ekspresi yang digunakan dalam klausa WHERE:

• Perbandingan sederhana antara nilai kolom dengan konstanta, yang disebut filter. Perbandingan ini sangat diuntungkan ketika diturunkan ke lapisan penyimpanan. Jumlah ekspresi filter dalam sebuah kueri dilaporkan dalam output EXPLAIN.

• Jenis ekspresi lain dalam klausa WHERE juga diturunkan ke lapisan penyimpanan jika memungkinkan. Jumlah ekspresi tersebut dalam sebuah kueri dilaporkan dalam output EXPLAIN. Ekspresi ini dapat berupa panggilan fungsi, operator LIKE, ekspresi CASE, dan sebagainya.

• Fungsi dan operator tertentu saat ini tidak diturunkan oleh kueri paralel. Jumlah ekspresi tersebut dalam sebuah kueri dilaporkan sebagai penghitung extra dalam output EXPLAIN. Sisa kueri tersebut masih dapat menggunakan kueri paralel.

• Meskipun ekspresi dalam daftar pilihan tidak diturunkan, kueri yang berisi fungsi tersebut masih dapat diuntungkan dari pengurangan lalu lintas jaringan untuk hasil perantara dari kueri paralel. Contohnya, kueri yang memanggil fungsi agregat dalam daftar pilihan dapat diuntungkan dari kueri paralel, meskipun fungsi agregasi tidak diturunkan.

Contohnya, kueri berikut ini melakukan pemindaian tabel lengkap dan memproses semua nilai untuk kolom P_BRAND. Akan tetapi, kueri tersebut tidak menggunakan kueri paralel karena tidak mencakup klausa WHERE apa pun.

mysql> explain select count(*), p_brand from part group by p_brand;
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
|  1 | SIMPLE      | part  | ALL  | NULL          | NULL | NULL    | NULL | 20427936 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+

Sebaliknya, kueri berikut mencakup predikat WHERE yang memfilter hasil, sehingga kueri paralel dapat diterapkan:

mysql> explain select count(*), p_brand from part where p_name is not null
    ->   and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000
    -> group by p_brand;
+----+...+----------+-------------------------------------------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                                                       |
+----+...+----------+-------------------------------------------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using temporary; Using filesort; Using parallel query (5 columns, 1 filters, 2 exprs; 0 extra) |
+----+...+----------+-------------------------------------------------------------------------------------------------------------+

Jika pengoptimal memperkirakan bahwa baris yang dikembalikan untuk suatu blok kueri jumlahnya kecil, kueri paralel tidak digunakan untuk blok kueri tersebut. Contoh berikut menunjukkan kasus di mana operator lebih-besar-daripada di kolom kunci primer berlaku untuk jutaan baris, yang menyebabkan kueri paralel digunakan. Pengujian kurang-dari yang berkebalikan diperkirakan berlaku hanya pada beberapa baris dan tidak menggunakan kueri paralel.

mysql> explain select count(*) from part where p_partkey > 10;
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------+----------------------------------------------------------------------------+

mysql> explain select count(*) from part where p_partkey < 10;
+----+...+------+--------------------------+
| id |...| rows | Extra                    |
+----+...+------+--------------------------+
|  1 |...|    9 | Using where; Using index |
+----+...+------+--------------------------+

Bahasa definisi data (DDL)

Di Aurora My SQL versi 2, query paralel hanya tersedia untuk tabel yang tidak ada operasi bahasa definisi data cepat (DDL) yang tertunda. Di Aurora My SQL versi 3, Anda dapat menggunakan query paralel pada tabel pada saat yang sama sebagai operasi instanDDL.

Instan DDL di Aurora SQL Versi saya 3 menggantikan fitur cepat DDL di Aurora My versi 2. SQL Untuk informasi tentang instanDDL, lihatDDL instan (Aurora MySQL versi 3).

Jenis data kolom

Di Aurora My SQL versi 3, query paralel dapat bekerja dengan tabel yang berisi kolom dengan tipe dataTEXT,, BLOBJSON, dan. GEOMETRY Ini juga dapat bekerja dengan kolom VARCHAR dan CHAR dengan panjang maksimum yang dinyatakan melebihi 768 byte. Jika kueri Anda mengacu pada kolom yang berisi jenis objek besar seperti itu, tugas tambahan untuk mengambilnya akan memunculkan overhead ke dalam pemrosesan kueri. Dalam hal ini, periksa apakah kueri dapat menghilangkan referensi ke kolom-kolom tersebut. Jika tidak, jalankan tolok ukur untuk mengonfirmasi apakah kueri tersebut lebih cepat dengan kueri paralel diaktifkan atau dinonaktifkan.

Di Aurora My SQL versi 2, query paralel memiliki batasan ini untuk tipe objek besar:

• Jenis data TEXT, BLOB, JSON, dan GEOMETRY tidak didukung dengan kueri paralel. Kueri yang mengacu pada kolom apa pun dari jenis ini tidak dapat menggunakan kueri paralel.

• Kolom dengan panjang variabel (jenis data VARCHAR dan CHAR) kompatibel dengan kueri paralel hingga panjang maksimum yang dinyatakan 768 byte. Kueri yang mengacu pada kolom apa pun dari jenis yang dinyatakan dengan panjang maksimum yang lebih panjang tidak dapat menggunakan kueri paralel. Untuk kolom yang menggunakan set karakter multibyte, batas byte mempertimbangkan jumlah maksimum byte dalam set karakter. Contohnya, untuk set karakter utf8mb4 (yang memiliki panjang karakter maksimum 4 byte), kolom VARCHAR(192) kompatibel dengan kueri paralel tetapi kolom VARCHAR(193) tidak.

Tabel yang dipartisi

Anda dapat menggunakan tabel yang dipartisi dengan query paralel di Aurora My versi 3. SQL Karena tabel yang dipartisi direpresentasikan secara internal sebagai beberapa tabel yang lebih kecil, kueri yang menggunakan kueri paralel pada tabel yang tidak dipartisi mungkin tidak menggunakan kueri paralel pada tabel yang dipartisi identik. Aurora My SQL mempertimbangkan apakah setiap partisi cukup besar untuk memenuhi syarat untuk optimasi query paralel, alih-alih mengevaluasi ukuran seluruh tabel. Periksa apakah variabel status Aurora_pq_request_not_chosen_small_table bertambah jika kueri pada tabel yang dipartisi tidak menggunakan kueri paralel padahal Anda mengharapkannya.

Misalnya, pertimbangkan satu tabel yang dipartisi dengan PARTITION BY HASH (column) PARTITIONS 2 dan tabel lain dipartisi dengan PARTITION BY HASH (column) PARTITIONS 10. Dalam tabel dengan dua partisi, partisinya lima kali lebih besar daripada tabel dengan sepuluh partisi. Dengan demikian, kueri paralel lebih memungkinkan untuk digunakan untuk mengueri tabel dengan lebih sedikit partisi. Dalam contoh berikut, tabel PART_BIG_PARTITIONS memiliki dua partisi dan PART_SMALL_PARTITIONS memiliki sepuluh partisi. Dengan data yang identik, kueri paralel lebih memungkinkan untuk digunakan untuk tabel dengan lebih sedikit partisi besar.

mysql> explain select count(*), p_brand from part_big_partitions where p_name is not null
    ->   and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000 group by p_brand;
+----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+
| id | select_type | table               | partitions | Extra                                                                                                             |
+----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | part_big_partitions | p0,p1      | Using where; Using temporary; Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra; 1 group-bys, 1 aggrs) |
+----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+

mysql> explain select count(*), p_brand from part_small_partitions where p_name is not null
    ->   and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000 group by p_brand;
+----+-------------+-----------------------+-------------------------------+------------------------------+
| id | select_type | table                 | partitions                    | Extra                        |
+----+-------------+-----------------------+-------------------------------+------------------------------+
|  1 | SIMPLE      | part_small_partitions | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | Using where; Using temporary |
+----+-------------+-----------------------+-------------------------------+------------------------------+

Fungsi agregat, klausa GROUP BY, dan klausa HAVING

Kueri yang mencakup fungsi agregat sering kali merupakan kandidat yang baik untuk kueri paralel, karena kueri tersebut memindai baris dalam jumlah besar dalam tabel besar.

Di Aurora My SQL 3, query paralel dapat mengoptimalkan panggilan fungsi agregat dalam daftar pilih dan klausa. HAVING

Sebelum Aurora My SQL 3, panggilan fungsi agregat dalam daftar pilih atau HAVING klausa tidak ditekan ke lapisan penyimpanan. Akan tetapi, kueri paralel tetap dapat meningkatkan performa kueri dengan fungsi agregat tersebut. Hal itu dilakukan pertama-tama dengan mengekstraksi nilai kolom dari halaman data mentah secara paralel pada lapisan penyimpanan. Kueri paralel tersebut kemudian mengirim kembali nilai itu ke simpul kepala dalam format urutan yang padat, bukan sebagai keseluruhan halaman data. Seperti biasa, kueri memerlukan setidaknya satu predikat WHERE agar kueri paralel dapat diaktifkan.

Contoh sederhana berikut mengilustrasikan jenis kueri agregat yang dapat diuntungkan dari kueri paralel. Hal tersebut dilakukan dengan mengembalikan hasil menengah dalam bentuk ringkas ke simpul kepala, memfilter baris yang tidak cocok dari hasil menengah, atau keduanya.

mysql> explain select sql_no_cache count(distinct p_brand) from part where p_mfgr = 'Manufacturer#5';
+----+...+----------------------------------------------------------------------------+
| id |...| Extra                                                                      |
+----+...+----------------------------------------------------------------------------+
|  1 |...| Using where; Using parallel query (2 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------------------------------------------------------------------------+

mysql> explain select sql_no_cache p_mfgr from part where p_retailprice > 1000 group by p_mfgr having count(*) > 100;
+----+...+-------------------------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                                       |
+----+...+-------------------------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using temporary; Using filesort; Using parallel query (3 columns, 0 filters, 1 exprs; 0 extra) |
+----+...+-------------------------------------------------------------------------------------------------------------+

Panggilan fungsi dalam WHERE klausa

Aurora dapat menerapkan pengoptimalan kueri paralel pada panggilan ke sebagian besar fungsi default dalam klausa WHERE. Memparalelkan panggilan fungsi ini menurunkan beberapa CPU pekerjaan dari node kepala. Mengevaluasi fungsi predikat secara paralel selama tahap kueri paling awal akan membantu Aurora meminimalkan jumlah data yang dikirim dan diproses selama tahap berikutnya.

Saat ini, paralelisasi tidak berlaku untuk panggilan fungsi dalam daftar pilihan. Fungsi-fungsi tersebut dievaluasi oleh simpul kepala, bahkan jika panggilan fungsi yang identik muncul dalam klausa WHERE. Nilai asli dari kolom yang relevan disertakan dalam urutan yang dikirim dari simpul penyimpanan kembali ke simpul kepala. Simpul kepala melakukan transformasi apa pun seperti UPPER, CONCATENATE, dan seterusnya untuk menghasilkan nilai akhir untuk set hasil.

Dalam contoh berikut, kueri paralel memaralelkan panggilan dengan LOWER karena muncul di klausa WHERE. Kueri paralel tidak memengaruhi panggilan ke SUBSTR dan UPPER karena muncul dalam daftar pilihan.

mysql> explain select sql_no_cache distinct substr(upper(p_name),1,5) from part
    -> where lower(p_name) like '%cornflower%' or lower(p_name) like '%goldenrod%';
+----+...+---------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                       |
+----+...+---------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using temporary; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
+----+...+---------------------------------------------------------------------------------------------+

Pertimbangan yang sama berlaku untuk ekspresi lain, seperti ekspresi CASE atau operator LIKE. Misalnya, contoh berikut menunjukkan bahwa kueri paralel mengevaluasi ekspresi CASE dan operator LIKE di klausa WHERE.

mysql> explain select p_mfgr, p_retailprice from part
    -> where p_retailprice > case p_mfgr
    ->   when 'Manufacturer#1' then 1000
    ->   when 'Manufacturer#2' then 1200
    ->   else 950
    -> end
    -> and p_name like '%vanilla%'
    -> group by p_retailprice;
+----+...+-------------------------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                                       |
+----+...+-------------------------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using temporary; Using filesort; Using parallel query (4 columns, 0 filters, 2 exprs; 0 extra) |
+----+...+-------------------------------------------------------------------------------------------------------------+

Klausul LIMIT

Saat ini, kueri paralel tidak digunakan untuk blok kueri yang mencakup klausa LIMIT. Kueri paralel masih dapat digunakan untuk fase kueri sebelumnya dengan GROUP by, ORDER BY, atau gabungan.

Operator perbandingan

Pengoptimal memperkirakan jumlah baris yang harus dipindai untuk mengevaluasi operator perbandingan, dan menentukan apakah akan menggunakan kueri paralel berdasarkan estimasi tersebut.

Contoh pertama berikut ini menunjukkan bahwa perbandingan setara terhadap kolom kunci primer dapat dilakukan secara efisien tanpa kueri paralel. Contoh kedua berikut menunjukkan bahwa perbandingan yang serupa terhadap kolom yang tidak diindeks memerlukan pemindaian jutaan baris, sehingga dapat diuntungkan dari kueri paralel.

mysql> explain select * from part where p_partkey = 10;
+----+...+------+-------+
| id |...| rows | Extra |
+----+...+------+-------+
|  1 |...|    1 | NULL  |
+----+...+------+-------+

mysql> explain select * from part where p_type = 'LARGE BRUSHED BRASS';
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (9 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------+----------------------------------------------------------------------------+

Pertimbangan yang sama berlaku untuk pengujian tidak sama dan untuk perbandingan rentang seperti kurang dari, lebih besar dari, atau sama dengan, atau BETWEEN. Pengoptimal memperkirakan jumlah baris yang akan dipindai, dan menentukan apakah kueri paralel bermanfaat berdasarkan volume keseluruhan I/O.

Gabungan

Kueri gabungan dengan tabel besar biasanya mencakup operasi sarat data yang diuntungkan dari pengoptimalan kueri paralel. Perbandingan nilai kolom di antara beberapa tabel (yaitu, predikat gabungan itu sendiri) saat ini tidak diparalelkan. Namun, kueri paralel dapat menurunkan beberapa pemrosesan internal untuk fase penggabungan lainnya, seperti membangun filter Bloom selama hash join. Kueri paralel dapat diterapkan pada kueri gabungan tanpa klausa WHERE. Oleh karena itu, kueri gabungan adalah pengecualian untuk aturan bahwa klausa WHERE diperlukan untuk menggunakan kueri paralel.

Setiap fase pemrosesan gabungan dievaluasi untuk memeriksa apakah fase tersebut memenuhi syarat untuk kueri paralel. Jika lebih dari satu fase dapat menggunakan kueri paralel, fase-fase ini dilakukan secara berurutan. Dengan demikian, setiap kueri gabungan dihitung sebagai satu sesi kueri paralel dalam hal batas keserentakan.

Contohnya, saat kueri gabungan mencakup predikat WHERE untuk memfilter baris dari salah satu tabel yang digabungkan, opsi pemfilteran tersebut dapat menggunakan kueri paralel. Sebagai contoh lain, anggaplah kueri gabungan menggunakan mekanisme hash join, contohnya untuk menggabungkan tabel besar dengan tabel kecil. Dalam hal ini, pemindaian tabel untuk menghasilkan struktur data filter Bloom mungkin dapat menggunakan kueri paralel.

catatan

Kueri paralel biasanya digunakan untuk jenis kueri sarat sumber daya yang diuntungkan dari pengoptimalan hash join. Metode untuk mengaktifkan optimasi bergabung hash tergantung pada versi Aurora MySQL. Untuk detail untuk setiap versi, lihat Mengaktifkan hash join untuk klaster kueri paralel. Untuk informasi tentang cara menggunakan hash join secara efektif, lihat Mengoptimalkan Aurora besar Kueri bergabung SQL saya dengan gabungan hash.

mysql> explain select count(*) from orders join customer where o_custkey = c_custkey;
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+
| id |...| table    | type  | possible_keys | key         |...| rows      | Extra                                                                                                           |
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+
|  1 |...| customer | index | PRIMARY       | c_nationkey |...|  15051972 | Using index                                                                                                     |
|  1 |...| orders   | ALL   | o_custkey     | NULL        |...| 154545408 | Using join buffer (Hash Join Outer table orders); Using parallel query (1 columns, 0 filters, 1 exprs; 0 extra) |
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+

Untuk kueri gabungan yang menggunakan mekanisme nested loop, blok nested loop terluar mungkin menggunakan kueri paralel. Penggunaan kueri paralel bergantung pada faktor yang sama seperti biasanya, seperti adanya ketentuan filter tambahan dalam klausa WHERE.

mysql> -- Nested loop join with extra filter conditions can use parallel query.
mysql> explain select count(*) from part, partsupp where p_partkey != ps_partkey and p_name is not null and ps_availqty > 0;
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+
| id | select_type | table    |...| rows     | Extra                                                                      |
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+
|  1 | SIMPLE      | part     |...| 20427936 | Using where; Using parallel query (2 columns, 1 filters, 0 exprs; 0 extra) |
|  1 | SIMPLE      | partsupp |...| 78164450 | Using where; Using join buffer (Block Nested Loop)                         |
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+

Subkueri

Blok kueri luar dan blok subkueri dalam masing-masing dapat menggunakan kueri paralel, atau tidak. Untuk setiap blok, menggunakan kueri paralel atau tidak, didasarkan pada karakteristik umum tabel, klausa WHERE, dan sebagainya. Contohnya, kueri berikut menggunakan kueri paralel untuk blok subkueri tetapi tidak untuk blok luar.

mysql> explain select count(*) from part where
   --> p_partkey < (select max(p_partkey) from part where p_name like '%vanilla%');
+----+-------------+...+----------+----------------------------------------------------------------------------+
| id | select_type |...| rows     | Extra                                                                      |
+----+-------------+...+----------+----------------------------------------------------------------------------+
|  1 | PRIMARY     |...|     NULL | Impossible WHERE noticed after reading const tables                        |
|  2 | SUBQUERY    |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
+----+-------------+...+----------+----------------------------------------------------------------------------+

Saat ini, subkueri yang berkorelasi tidak dapat menggunakan pengoptimalan kueri paralel.

UNION

Setiap blok kueri dalam kueri UNION dapat menggunakan kueri paralel atau tidak, berdasarkan karakteristik umum tabel, klausul WHERE, dan sebagainya, untuk setiap bagian UNION.

mysql> explain select p_partkey from part where p_name like '%choco_ate%'
    -> union select p_partkey from part where p_name like '%vanil_a%';
+----+----------------+...+----------+----------------------------------------------------------------------------+
| id | select_type    |...| rows     | Extra                                                                      |
+----+----------------+...+----------+----------------------------------------------------------------------------+
|  1 | PRIMARY        |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
|  2 | UNION          |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
| NULL | UNION RESULT | <union1,2> |...|     NULL | Using temporary                                           |
+----+--------------+...+----------+----------------------------------------------------------------------------+

catatan

Setiap klausa UNION dalam kueri dijalankan secara berurutan. Bahkan jika kueri mencakup beberapa tahap yang semuanya menggunakan kueri paralel, kueri tersebut hanya menjalankan satu kueri paralel dalam satu waktu. Oleh karena itu, bahkan kueri multitahap yang kompleks hanya dihitung sebagai 1 terhadap batas kueri paralel yang serentak.

Tampilan

Pengoptimal menulis ulang kueri apa pun menggunakan tampilan sebagai kueri yang lebih panjang menggunakan tabel yang mendasarinya. Dengan demikian, kueri paralel bekerja dengan cara yang sama baik referensi tabelnya berupa tampilan atau tabel nyata. Semua pertimbangan yang sama tentang apakah akan menggunakan kueri paralel untuk suatu kueri, dan bagian mana yang diturunkan, berlaku untuk kueri terakhir yang ditulis ulang.

Contohnya, rencana kueri berikut menunjukkan definisi tampilan yang biasanya tidak menggunakan kueri paralel. Ketika tampilan ditanyakan dengan WHERE klausa tambahan, Aurora My SQL menggunakan query paralel.

mysql> create view part_view as select * from part;
mysql> explain select count(*) from part_view where p_partkey is not null;
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (1 columns, 0 filters, 0 exprs; 1 extra) |
+----+...+----------+----------------------------------------------------------------------------+

Pernyataan bahasa manipulasi data (DML)

Pernyataan INSERT dapat menggunakan kueri paralel untuk fase pemrosesan SELECT, jika bagian SELECT sesuai dengan ketentuan lainnya untuk kueri paralel.

mysql> create table part_subset like part;
mysql> explain insert into part_subset select * from part where p_mfgr = 'Manufacturer#1';
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (9 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------+----------------------------------------------------------------------------+

catatan

Biasanya, setelah pernyataan INSERT, data untuk baris yang baru dimasukkan ada di dalam buffer pool. Oleh karena itu, suatu tabel mungkin tidak memenuhi syarat untuk kueri paralel segera setelah memasukkan baris dalam jumlah besar. Lalu, setelah data dikosongkan dari buffer pool selama operasi normal, kueri terhadap tabel tersebut dapat mulai menggunakan kueri paralel.

Pernyataan CREATE TABLE AS SELECT tidak menggunakan kueri paralel, meskipun bagian SELECT dari pernyataan tersebut akan memenuhi syarat untuk kueri paralel. DDLAspek pernyataan ini membuatnya tidak kompatibel dengan pemrosesan query paralel. Sebaliknya, dalam pernyataan INSERT ... SELECT, bagian SELECT dapat menggunakan kueri paralel.

Kueri paralel tidak pernah digunakan untuk pernyataan DELETE atau UPDATE, terlepas dari ukuran tabel dan predikat dalam klausa WHERE.

mysql> explain delete from part where p_name is not null;
+----+-------------+...+----------+-------------+
| id | select_type |...| rows     | Extra       |
+----+-------------+...+----------+-------------+
|  1 | SIMPLE      |...| 20427936 | Using where |
+----+-------------+...+----------+-------------+

Transaksi dan penguncian

Anda dapat menggunakan semua tingkat isolasi pada instans utama Aurora.

Pada instans pembaca Aurora DB, kueri paralel berlaku untuk pernyataan yang dilakukan dalam tingkat isolasi REPEATABLE READ. Aurora SQL Versi saya 2.09 atau lebih tinggi juga dapat menggunakan tingkat READ COMMITTED isolasi pada instans DB pembaca. REPEATABLE READadalah tingkat isolasi default untuk instans DB pembaca Aurora. Untuk menggunakan tingkat isolasi READ COMMITTED pada instans reader DB membutuhkan pengaturan opsi konfigurasi aurora_read_replica_read_committed pada tingkat sesi. Tingkat READ COMMITTED isolasi untuk instance pembaca sesuai dengan perilaku SQL standar. Namun, isolasi ini lebih longgar pada instans pembaca dibandingkan ketika kueri menggunakan tingkat isolasi READ COMMITTED pada instans penulis.

Untuk informasi lebih lanjut tentang tingkat isolasi Aurora, terutama perbedaan dalam hal READ COMMITTED antara instans penulis dan pembaca, lihat Aurora Tingkat isolasi saya SQL.

Setelah transaksi besar selesai, statistik tabel mungkin menjadi usang. Statistik yang usang tersebut mungkin memerlukan pernyataan ANALYZE TABLE sebelum Aurora dapat secara akurat memperkirakan jumlah baris. DMLPernyataan skala besar mungkin juga membawa sebagian besar data tabel ke dalam kumpulan buffer. Memiliki data ini di dalam buffer pool dapat menyebabkan kueri paralel menjadi lebih jarang dipilih untuk tabel tersebut sampai data dikosongkan dari pool.

Jika sesi Anda berada dalam transaksi yang berlangsung lama (secara default, 10 menit), kueri lebih lanjut di dalam sesi tersebut tidak menggunakan kueri paralel. Waktu habis juga dapat terjadi dalam satu kueri yang berlangsung lama. Jenis waktu habis ini dapat terjadi jika kueri berjalan lebih lama dari interval maksimum (saat ini 10 menit) sebelum pemrosesan kueri paralel dimulai.

Anda dapat mengurangi kemungkinan memulai transaksi yang berlangsung lama secara tidak sengaja dengan mengatur autocommit=1 dalam sesi mysql di mana Anda melakukan kueri ad hoc (satu waktu). Bahkan pernyataan SELECT terhadap suatu tabel akan memulai transaksi dengan membuat tampilan baca. Tampilan baca merupakan rangkaian data yang konsisten untuk kueri berikutnya yang tetap ada hingga transaksi dilakukan. Waspadai pembatasan ini juga saat menggunakan JDBC atau ODBC aplikasi dengan Aurora, karena aplikasi tersebut mungkin berjalan dengan pengaturan autocommit dimatikan.

Contoh berikut ini menunjukkan bagaimana, dengan pengaturan autocommit yang dinonaktifkan, menjalankan suatu kueri terhadap tabel akan menciptakan tampilan baca yang secara implisit memulai transaksi. Kueri yang dijalankan segera sesudahnya masih dapat menggunakan kueri paralel. Namun, setelah jeda beberapa menit, kueri tidak lagi memenuhi syarat untuk kueri paralel. Mengakhiri transaksi dengan COMMIT atau ROLLBACK akan memulihkan eligilibilitas kueri paralel.

mysql> set autocommit=0;

mysql> explain select sql_no_cache count(*) from part where p_retailprice > 10.0;
+----+...+---------+----------------------------------------------------------------------------+
| id |...| rows    | Extra                                                                      |
+----+...+---------+----------------------------------------------------------------------------+
|  1 |...| 2976129 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+---------+----------------------------------------------------------------------------+

mysql> select sleep(720); explain select sql_no_cache count(*) from part where p_retailprice > 10.0;
+------------+
| sleep(720) |
+------------+
|          0 |
+------------+
1 row in set (12 min 0.00 sec)

+----+...+---------+-------------+
| id |...| rows    | Extra       |
+----+...+---------+-------------+
|  1 |...| 2976129 | Using where |
+----+...+---------+-------------+

mysql> commit;

mysql> explain select sql_no_cache count(*) from part where p_retailprice > 10.0;
+----+...+---------+----------------------------------------------------------------------------+
| id |...| rows    | Extra                                                                      |
+----+...+---------+----------------------------------------------------------------------------+
|  1 |...| 2976129 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+---------+----------------------------------------------------------------------------+

Untuk melihat berapa kali kueri tidak memenuhi syarat untuk kueri paralel karena kueri tersebut berada dalam transaksi yang berlangsung lama, periksa variabel status Aurora_pq_request_not_chosen_long_trx.

mysql> show global status like '%pq%trx%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Aurora_pq_request_not_chosen_long_trx | 4     |
+-------------------------------+-------+

Setiap pernyataan SELECT yang memperoleh kunci, seperti sintaks SELECT FOR UPDATE atau SELECT LOCK IN SHARE MODE, tidak dapat menggunakan kueri paralel.

Kueri paralel dapat berfungsi untuk tabel yang dikunci oleh pernyataan LOCK TABLES.

mysql> explain select o_orderpriority, o_shippriority from orders where o_clerk = 'Clerk#000095055';
+----+...+-----------+----------------------------------------------------------------------------+
| id |...| rows      | Extra                                                                      |
+----+...+-----------+----------------------------------------------------------------------------+
|  1 |...| 154545408 | Using where; Using parallel query (3 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+-----------+----------------------------------------------------------------------------+

mysql> explain select o_orderpriority, o_shippriority from orders where o_clerk = 'Clerk#000095055' for update;
+----+...+-----------+-------------+
| id |...| rows      | Extra       |
+----+...+-----------+-------------+
|  1 |...| 154545408 | Using where |
+----+...+-----------+-------------+

Indeks pohon B

Statistik yang dikumpulkan oleh pernyataan ANALYZE TABLE membantu pengoptimal untuk memutuskan waktu untuk menggunakan kueri paralel atau pencarian indeks, berdasarkan karakteristik data untuk setiap kolom. Pertahankan statistik terkini dengan menjalankan ANALYZE TABLE setelah DML operasi yang membuat perubahan besar pada data dalam tabel.

Jika pencarian indeks dapat melakukan kueri secara efisien tanpa pemindaian sarat data, Aurora mungkin menggunakan pencarian indeks. Melakukan hal ini akan menghindari pengeluaran tambahan dari pemrosesan kueri paralel. Terdapat juga batas keserentakan pada jumlah kueri paralel yang dapat berjalan secara bersamaan di klaster Aurora DB mana pun. Pastikan Anda menggunakan praktik terbaik untuk mengindeks tabel, sehingga kueri yang paling sering dan paling sering muncul bersamaan menggunakan pencarian indeks.

Indeks pencarian teks lengkap (FTS)

Saat ini, kueri paralel tidak digunakan untuk tabel yang berisi indeks pencarian teks penuh, terlepas dari apakah kueri tersebut mengacu pada kolom yang diindeks tersebut atau menggunakan operator MATCH.

Kolom virtual

Saat ini, kueri paralel tidak digunakan untuk tabel yang berisi kolom virtual, terlepas dari apakah kueri tersebut merujuk pada kolom virtual mana pun.

Mekanisme caching default

Aurora mencakup mekanisme caching default, yaitu buffer pool dan cache kueri. Pengoptimal Aurora memilih antara mekanisme caching ini dan kueri paralel bergantung pada mana yang paling efektif untuk kueri tertentu.

Saat kueri paralel memfilter baris dan mengubah serta mengekstraksi nilai kolom, data dikirim kembali ke simpul kepala sebagai urutan dan bukan sebagai halaman data. Oleh karena itu, menjalankan kueri paralel tidak akan menambahkan halaman apa pun ke dalam buffer pool, atau mengosongkan halaman yang sudah ada di dalam buffer pool.

Aurora memeriksa jumlah halaman data tabel yang ada dalam buffer pool, dan bagian mana dari data tabel tersebut yang direpresentasikan oleh angka tersebut. Aurora menggunakan informasi tersebut untuk menentukan apakah lebih efisien untuk menggunakan kueri paralel (dan mem-bypass data dalam buffer pool). Sebagai alternatif, Aurora mungkin menggunakan jalur pemrosesan kueri nonparalel, yang menggunakan cache data dalam buffer pool. Halaman mana yang dibuat cache dan bagaimana kueri sarat data dapat memengaruhi caching dan pengosongan bergantung pada pengaturan konfigurasi yang terkait dengan buffer pool. Oleh karena itu, akan sulit untuk memprediksi apakah kueri tertentu menggunakan kueri paralel, karena pilihannya bergantung pada data yang selalu berubah dalam buffer pool.

Selain itu, Aurora menerapkan batas keserentakan pada kueri paralel. Karena tidak setiap kueri menggunakan kueri paralel, tabel yang diakses oleh beberapa kueri secara serentak biasanya memiliki bagian yang substansial dari datanya dalam buffer pool. Oleh karena itu, Aurora sering kali tidak memilih tabel ini untuk kueri paralel.

Ketika Anda menjalankan urutan kueri nonparalel pada tabel yang sama, kueri pertama mungkin lambat karena data tidak ada dalam buffer pool. Lalu kueri kedua dan selanjutnya jauh lebih cepat karena buffer pool sekarang sudah "dipanaskan". Kueri paralel biasanya menunjukkan performa yang konsisten dari kueri pertama terhadap tabel. Saat melakukan uji performa, buat tolok ukur kueri nonparalel dengan buffer pool yang dingin dan hangat. Dalam beberapa kasus, hasil dari buffer pool hangat dapat menghasilkan perbandingan yang baik dengan waktu kueri paralel. Dalam kasus ini, pertimbangkan faktor seperti frekuensi kueri terhadap tabel tersebut. Pertimbangkan juga apakah bermanfaat untuk menyimpan data untuk tabel tersebut di dalam buffer pool.

Cache kueri menghindari menjalankan ulang kueri saat kueri yang identik dikirimkan dan data tabel yang mendasarinya tidak berubah. Kueri yang dioptimalkan dengan fitur kueri paralel dapat masuk ke cache kueri, yang secara efektif menjadikannya instan saat dijalankan lagi.

catatan

Saat melakukan perbandingan performa, cache kueri dapat menghasilkan jumlah pengaturan waktu yang rendah secara buatan. Oleh karena itu, dalam situasi serupa tolok ukur, Anda dapat menggunakan petunjuk sql_no_cache. Petunjuk ini mencegah hasil tersebut tersaji dari cache kueri, meskipun kueri yang sama telah dijalankan sebelumnya. Petunjuk segera muncul setelah pernyataan SELECT dalam kueri. Banyak contoh kueri paralel dalam topik ini mencakup petunjuk ini, untuk menjadikan waktu kueri sebanding antara versi kueri dengan kueri paralel yang diaktifkan dan dinonaktifkan.

Pastikan Anda menghapus petunjuk ini dari sumber Anda saat beralih ke penggunaan kueri paralel untuk produksi.

Petunjuk pengoptimal

Cara lain untuk mengontrol pengoptimal adalah dengan menggunakan petunjuk pengoptimal, yang dapat ditentukan dalam pernyataan individual. Misalnya, Anda dapat mengaktifkan pengoptimalan untuk satu tabel dalam sebuah pernyataan, kemudian mematikan pengoptimalan untuk tabel yang berbeda. Untuk informasi selengkapnya tentang petunjuk ini, lihat Petunjuk Pengoptimal di Manual Referensi Saya SQL.

Anda dapat menggunakan SQL petunjuk dengan kueri Aurora SQL My untuk menyempurnakan kinerja. Anda juga dapat menggunakan petunjuk agar rencana eksekusi untuk kueri penting tidak berubah karena kondisi yang tidak dapat diprediksi.

Kami telah memperluas fitur SQL petunjuk untuk membantu Anda mengontrol pilihan pengoptimal untuk paket kueri Anda. Petunjuk ini berlaku untuk kueri yang menggunakan pengoptimalan kueri paralel. Untuk informasi selengkapnya, lihat Aurora Petunjuk saya SQL.

Meja ISAM sementara saya

Pengoptimalan kueri paralel hanya berlaku untuk tabel InnoDB. Karena Aurora My SQL menggunakan My ISAM behind the scenes untuk tabel sementara, fase kueri internal yang melibatkan tabel temporer tidak pernah menggunakan query paralel. Fase kueri ini ditunjukkan oleh Using temporary dalam output EXPLAIN.