Cara menggunakan mysql 5.6 order by

Question

A very inefficient method for determining one or two items, but a good solution for those who are working with PANDAS and Python and have many similar queries, is to download your SQL query to a Dataframe and then use Pandas' Rank and query tools - in 2 steps:

Table of Contents Show

Menentukan instans DB mana yang terhubung ke Anda
Praktik terbaik untuk performa dan penskalaan Aurora MySQL
Menggunakan kelas instans T untuk pengembangan dan pengujian
Mengoptimalkan kueri bergabung yang diindeks Amazon Aurora dengan prefetch kunci asinkron
Mengoptimalkan besar Aurora MySQL bergabung kueri dengan hash bergabung
Penggunaan Amazon Aurora untuk mengukur pembacaan database MySQL Anda
Praktik terbaik untuk ketersediaan tinggi Aurora MySQL
Menggunakan Amazon Aurora untuk Pemulihan Bencana dengan database MySQL Anda
Bermigrasi dari MySQL ke Amazon Aurora MySQL dengan waktu henti yang berkurang
Menghindari kinerja lambat, restart otomatis, dan failover untuk instans Aurora MySQL DB
Rekomendasi untuk fitur MySQL
Menggunakan replikasi multithreaded di Aurora MySQL versi 3
MemintaAWS Lambdafungsi menggunakan fungsi MySQL asli
Menghindari transaksi XA dengan Amazon Aurora MySQL
Menjaga kunci asing dihidupkan selama pernyataan DML*
Mengkonfigurasi seberapa sering buffer log memerah

Step 1 - Run @GMB 's answer from above:

conn = pymysql.connect(host='localhost',
                       database='db',
                       user='user',
                       password='pass')
cur = conn.cursor()    
rank_scores = """SELECT x.*, @curRank := @curRank + 1 AS rank
                            FROM (
                                SELECT c.id, c.score, i.sheetscore
                                FROM chart c 
                                LEFT JOIN indicator i ON c.indicator_id = i.id
                                ORDER BY c.score DESC, i.sheetscore DESC
                            ) x
                            CROSS JOIN (SELECT @curRank :=0) q
                            ORDER BY score DESC, sheetscore DESC ;"""
 df_scorerank = pd.read_sql(rank_scores, conn)
 conn.close()
 cur.close()

Step 2 - extract the Rank(s) you need from the Pandas dataframe:

chart_rank = df_scorerank.loc[df_scorerank['id'] == chart_id, 'rank'].item()

As above, the dataframe now contains:

'ID','SCORE','SHEETSCORE', 'RANK'
'11767', '1', '0.7325', '1'
'11765', '1', '0.7325', '2'
'8365', '1', '0.6925', '3'
'8363', '1', '0.6925', '4'
'8615', '1', '0.6875', '5'
'8617', '1', '0.6875', '6'
'11646', '1', '0.685455', '7'
'11647', '1', '0.685455', '8'

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Topik ini mencakup informasi tentang praktik terbaik dan opsi untuk menggunakan atau memigrasi data ke klaster DB Amazon Aurora MySQL. Informasi dalam topik ini merangkum dan mengulangi beberapa pedoman dan prosedur yang dapat Anda temukanMengelola klaster DB Amazon Aurora.

Menentukan instans DB mana yang terhubung ke Anda

Untuk menentukan instans DB mana di klaster DB Aurora MySQL koneksi terhubung, periksa variabel global innodb_read_only, seperti yang ditunjukkan dalam contoh berikut.

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';

Klasterinnodb_read_onlyvariabel diatur keONjika Anda terhubung ke instans DB. Pengaturan ini adalah


mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

0jika Anda terhubung ke instans DB penulis, seperti instance utama dalam klaster yang disediakan.

Pendekatan ini dapat membantu jika Anda ingin menambahkan logika ke kode aplikasi Anda untuk menyeimbangkan beban kerja atau untuk memastikan bahwa operasi tulis menggunakan koneksi yang tepat. Teknik ini hanya berlaku untuk klaster Aurora menggunakan replikasi satu-master. Untuk klaster multi-master, semua instans DB memiliki pengaturan


mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

1.

Praktik terbaik untuk performa dan penskalaan Aurora MySQL

Anda dapat menerapkan praktik terbaik berikut untuk meningkatkan kinerja dan skalabilitas cluster Aurora MySQL Anda.

Menggunakan kelas instans T untuk pengembangan dan pengujian

Instans MySQL Amazon Aurora yang menggunakan


mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

2,


mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

3, atau


mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

4Kelas instans DB paling cocok untuk aplikasi yang tidak mendukung beban kerja yang tinggi untuk waktu yang lebih lama. Instans T dirancang untuk memberikan performa dasar yang moderat dan kemampuan untuk meningkatkan performa yang secara signifikan lebih tinggi sesuai dengan yang dibutuhkan beban kerja Anda. Tujuannya adalah untuk beban kerja yang tidak menggunakan CPU penuh secara sering atau konsisten, tetapi kadang-kadang perlu runtutan. Kami menyarankan hanya menggunakan kelas instans DB untuk server pengembangan dan pengujian, atau server non-produksi lainnya. Untuk rincian selengkapnya tentang kelas instans T, lihatInstance kinerja yang dapat melonjak.

Jika klaster Aurora Anda lebih besar dari 40 TB, jangan gunakan kelas instans T. Ketika database Anda memiliki volume data yang besar, overhead memori untuk mengelola objek skema dapat melebihi kapasitas instance T.

Jangan aktifkan Skema Kinerja MySQL pada instans Amazon Aurora MySQL T. Jika Skema Kinerja diaktifkan, instans dapat kehabisan memori.

Jika database Anda terkadang menganggur tetapi di lain waktu memiliki beban kerja yang cukup besar, Anda dapat menggunakannyaAurora Serverless v2sebagai alternatif untuk contoh T. DenganAurora Serverless v2, Anda menentukan rentang kapasitas dan Aurora secara otomatis menskalakan database Anda naik atau turun tergantung pada beban kerja saat ini. Untuk rincian penggunaan, lihatMenggunakan Aurora Serverless v2. Untuk versi mesin database yang dapat Anda gunakanAurora Serverless v2, lihatPersyaratan untuk Aurora Serverless v2.

Saat Anda menggunakan instans T sebagai instans DB di klaster DB Aurora MySQL, kami merekomendasikan hal berikut:

Jika Anda menggunakan instans T sebagai kelas instans DB dalam klaster DB Anda, gunakan kelas instans DB yang sama untuk semua instans dalam klaster DB Anda. Misalnya, jika Anda menggunakan
```
mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';
```
5untuk instans penulis Anda, kami sarankan Anda menggunakan
```
mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';
```
5untuk contoh pembaca Anda juga.
Jangan sesuaikan setelan konfigurasi terkait memori, seperti
```
mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';
```
7. Aurora menggunakan serangkaian nilai default yang sangat disetel untuk buffer memori pada instance T. Default khusus ini diperlukan agar Aurora dapat berjalan pada instance yang dibatasi memori. Jika Anda mengubah setelan terkait memori pada instans T, kemungkinan besar Anda akan menjumpainya out-of-memory kondisi, bahkan jika perubahan Anda dimaksudkan untuk meningkatkan ukuran buffer.
Pantau saldo kredit CPU Anda (
```
mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';
```
8) untuk memastikannya pada tingkat berkelanjutan. Artinya, kredit CPU sedang diakumulasi pada kecepatan yang sama dengan yang digunakan.
Ketika Anda telah menggunakan kredit CPU untuk instans, Anda melihat penurunan yang cepat pada CPU yang tersedia dan peningkatan latensi baca dan tulis untuk instans. Situasi ini menyebabkan penurunan yang parah dalam kinerja keseluruhan instans.
Jika saldo kredit CPU Anda tidak berada pada tingkat berkelanjutan, maka kami menyarankan untuk memodifikasi instans DB Anda agar menggunakan salah satu kelas instans DB R yang didukung (komputasi skala).
Untuk informasi selengkapnya tentang pemantauan metrik, lihat Melihat metrik di konsol Amazon RDS.
Untuk klaster DB Aurora MySQL Anda menggunakan replikasi satu-master, pantau di lag replika (
```
mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';
```
9) antara instance penulis dan instance pembaca.
Jika instance pembaca kehabisan kredit CPU sebelum instance penulis melakukannya, lag yang dihasilkan dapat menyebabkan instance pembaca sering memulai ulang. Hasil ini biasa jika aplikasi memiliki beban operasi baca berat yang didistribusikan di antara instans pembaca, pada saat yang sama bahwa instans penulis memiliki beban operasi tulis minimal.
Jika Anda melihat peningkatan berkelanjutan dalam lag replika, pastikan saldo kredit CPU Anda untuk instans pembaca dalam klaster DB Anda tidak habis.

Jika saldo kredit CPU Anda tidak berada pada tingkat berkelanjutan, maka kami menyarankan untuk memodifikasi instans DB Anda agar menggunakan salah satu kelas instans DB R yang didukung (komputasi skala).
Simpan jumlah sisipan per transaksi di bawah 1 juta untuk klaster DB yang mengaktifkan pencatatan biner.
Jika grup parameter klaster DB untuk klaster DB Anda memiliki
```
mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';
```
0parameter diatur ke nilai selain
```
mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';
```
0, maka klaster DB Anda mungkin mengalami out-of-memorykondisi jika klaster DB menerima transaksi yang berisi lebih dari 1 juta baris untuk disisipkan. Anda dapat memantau metrik memori yang bebas (
```
mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';
```
2) untuk menentukan apakah klaster DB Anda kehabisan memori. Kemudian Anda memeriksa metrik operasi tulis (
```
mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';
```
3) untuk melihat apakah instans penulis menerima beban berat dari operasi tulis. Jika demikian, kami menyarankan agar Anda memperbarui aplikasi Anda untuk membatasi jumlah sisipan dalam transaksi hingga kurang dari 1 juta. Atau, Anda dapat memodifikasi instans Anda untuk menggunakan salah satu dari kelas instans DB yang didukung (komputasi skala).

Mengoptimalkan kueri bergabung yang diindeks Amazon Aurora dengan prefetch kunci asinkron

Fitur prefetch (AKP) kunci asinkron tersedia untuk Amazon Aurora MySQL versi 1.15 dan lebih baru. Untuk informasi selengkapnya tentang versi Aurora MySQL, lihat Pembaruan engine basis data untuk Amazon Aurora MySQL.

Amazon Aurora dapat menggunakan AKP untuk meningkatkan kinerja kueri yang menggabungkan tabel di seluruh indeks. Fitur ini meningkatkan kinerja dengan mengantisipasi baris yang diperlukan untuk menjalankan kueri di mana kueri JOIN memerlukan penggunaan fitur optimalisasi Batched Key Access (BKA) dan Multi-Range Read (MRR). Untuk informasi lebih lanjut tentang BKA dan MRR, lihat Nested-loop blok and join akses kunci batchdan Optimalisasi baca multi threaddalam dokumentasi MySQL.

Untuk memanfaatkan fitur AKP, kueri harus menggunakan BKA dan MRR. Biasanya, kueri tersebut terjadi saat klausul JOIN dari queri menggunakan indeks sekunder, tetapi juga memerlukan beberapa kolom dari indeks primer. Misalnya, Anda dapat menggunakan AKP ketika klausul JOIN mewakili nilai indeks antara tabel luar kecil dan besar, dan indeks sangat selektif pada tabel yang lebih besar. AKP bekerja bersama dengan BKA dan MRR untuk melakukan pencarian indeks sekunder hingga utama selama evaluasi klausul JOIN. AKP mengidentifikasi baris yang diperlukan untuk menjalankan kueri selama evaluasi klausul JOIN. Kemudian menggunakan thread latar belakang untuk memuat halaman yang berisi baris tersebut ke dalam memori secara serempak sebelum menjalankan kueri.

Mengaktifkan prefetch kunci asinkron

Anda dapat mengaktifkan fitur AKP dengan mengatur


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

4, sebuah variabel server MySQL, ke


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

5. Secara default, nilai ini diatur ke


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

5. Namun, AKP tidak dapat diaktifkan sampai Anda juga mengaktifkan algoritma Gabung BKA dan menonaktifkan fungsionalitas MRR berbasis biaya. Untuk melakukannya, Anda harus menetapkan nilai berikut untuk


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

7, variabel server MySQL:

Atur


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

8 ke


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

5. Nilai ini mengontrol penggunaan algoritma BKA Join. Secara default, nilai ini diatur ke


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

0.

Atur


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

1 ke


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

0. Nilai ini mengontrol penggunaan fungsionalitas MRR berbasis biaya. Secara default, nilai ini diatur ke


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

5.

Saat ini, Anda dapat mengatur nilai ini hanya di tingkat sesi. Contoh berikut menggambarkan cara menetapkan nilai-nilai ini untuk mengaktifkan AKP untuk sesi saat ini dengan melaksanakan pernyataan SET.


mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

Demikian pula, Anda dapat menggunakan pernyataan SET untuk menonaktifkan AKP dan algortima gabung BKA serta fungsi MRR berbasis biaya yang dapat diaktifkan ulang untuk sesi saat ini, seperti yang ditunjukkan dalam contoh berikut.


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

Untuk informasi selengkapnya tentang switch pengoptimal batched_key_access dan mrr_cost_based lihat Pengoptimalan yang dapat dialihkandalam dokumentasi MySQL.

Mengoptimalkan kueri untuk prefetch kunci asinkron

Anda dapat mengonfirmasi apakah queri dapat memanfaatkan fitur AKP. Untuk melakukannya, gunakan


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

4untuk memprofilkan kueri sebelum menjalankannya. Pernyataan


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

4 memberikan informasi tentang rencana pelaksanaan yang akan digunakan untuk kueri tertentu.

Pada output pernyataan


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

4, kolom


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

7 menjelaskan informasi tambahan termasuk dengan rencana pelaksanaan. Jika fitur AKP berlaku pada tabel yang digunakan dalam pencarian, kolom ini mencakup salah satu nilai berikut:


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

8


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

9

Contoh berikut menunjukkan penggunaan


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

4untuk melihat rencana eksekusi untuk kueri yang dapat memanfaatkan AKP.


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

Untuk informasi lebih lanjut tentang


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

4Format output, lihatDiperpanjang EXPLAIN format outputdalam dokumentasi produk MySQL.

Untuk versi MySQL 5.6—kompatibel dan versi yang kompatibel dengan 5.7 yang lebih lama, Anda menggunakan


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

4dengan


For Aurora MySQL version 1 and 2:
mysql> SET optimizer_switch='hash_join=on';

For Aurora MySQL version 3:
mysql> SET optimizer_switch='block_nested_loop=on';

3kata kunci. Kata kunci ini telah usang di MySQL 5.7 dan 8.0.

Untuk informasi lebih lanjut tentang


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

4Format output dalam MySQL 5.6, lihatDiperpanjang EXPLAIN format outputdalam dokumentasi produk MySQL.

Mengoptimalkan besar Aurora MySQL bergabung kueri dengan hash bergabung

Anda harus menggabungkan sejumlah besar data dengan menggunakan equijoin, hash join dapat meningkatkan kinerja kueri. Anda dapat mengaktifkan hash join untuk Aurora MySQL.

Kolom hash join dapat berupa ekspresi rumit apa pun. Dalam kolom hash join, Anda dapat membandingkan jenis data dengan cara berikut:

Anda dapat membandingkan apa pun dalam kategori jenis data numerik yang tepat, seperti


For Aurora MySQL version 1 and 2:
mysql> SET optimizer_switch='hash_join=on';

For Aurora MySQL version 3:
mysql> SET optimizer_switch='block_nested_loop=on';

5,


For Aurora MySQL version 1 and 2:
mysql> SET optimizer_switch='hash_join=on';

For Aurora MySQL version 3:
mysql> SET optimizer_switch='block_nested_loop=on';

6,


For Aurora MySQL version 1 and 2:
mysql> SET optimizer_switch='hash_join=on';

For Aurora MySQL version 3:
mysql> SET optimizer_switch='block_nested_loop=on';

7, dan


For Aurora MySQL version 1 and 2:
mysql> SET optimizer_switch='hash_join=on';

For Aurora MySQL version 3:
mysql> SET optimizer_switch='block_nested_loop=on';

8.

Anda dapat membandingkan apa pun dalam kategori perkiraan jenis data numerik, seperti


For Aurora MySQL version 1 and 2:
mysql> SET optimizer_switch='hash_join=on';

For Aurora MySQL version 3:
mysql> SET optimizer_switch='block_nested_loop=on';

9 dan


mysql> SET optimizer_switch='hash_join_cost_based=off';

0.

Anda dapat membandingkan item di seluruh jenis string jika jenis string memiliki kumpulan karakter dan susunan yang sama.
Anda dapat membandingkan item dengan jenis data tanggal dan stempel waktu jika jenisnya sama.

Anda tidak dapat membandingkan tipe data dalam kategori yang berbeda.

Batasan berikut berlaku bagi hash joins untuk Aurora MySQL:

Gabungan luar kiri-kanan tidak didukung untuk Aurora MySQL versi 1 dan 2, tetapi didukung untuk versi 3.
Semijoin seperti subkueri tidak didukung, kecuali subkuerinya dimaterialkan terlebih dahulu.
Pembaruan atau penghapusan multi tabel tidak didukung.
Pembaruan atau penghapusan satu tabel didukung.
Kolom jenis data spasial dan BLOB tidak dapat menjadi kolom bergabung dalam hash join.

Mengaktifkan hash join

Untuk mengaktifkan hash bergabung, mengatur variabel server MySQL


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

7kepada


mysql> SET optimizer_switch='hash_join_cost_based=off';

2(Aurora MySQL versi 1 dan 2) atau


mysql> SET optimizer_switch='hash_join_cost_based=off';

3(Aurora MySQL versi 3). Hash join diaktifkan secara default di Aurora MySQL versi 3. Optimasi ini dimatikan secara default di Aurora MySQL versi 1 dan 2. Contoh berikut menggambarkan cara mengaktifkan hash join. Anda dapat mengeluarkan pernyataan


mysql> SET optimizer_switch='hash_join_cost_based=off';

4pertama untuk melihat apa pengaturan lain yang hadir di


mysql> SET optimizer_switch='hash_join_cost_based=off';

5parameter string. Memperbarui satu pengaturan di


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

7parameter tidak menghapus atau memodifikasi pengaturan lainnya.


For Aurora MySQL version 1 and 2:
mysql> SET optimizer_switch='hash_join=on';

For Aurora MySQL version 3:
mysql> SET optimizer_switch='block_nested_loop=on';

Untuk Aurora MySQL versi 3, dukungan hash join tersedia di semua versi minor dan diaktifkan secara default.

Untuk Aurora MySQL versi 2, dukungan hash join tersedia dalam versi 2.06 dan lebih tinggi. Pada Aurora MySQL versi 2, fitur hash join selalu dikendalikan oleh nilai


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

7.

Sebelum Aurora MySQL versi 1.22, cara mengaktifkan hash join di Aurora MySQL versi 1 adalah dengan mengaktifkan pengaturan tingkat sesi


mysql> SET optimizer_switch='hash_join_cost_based=off';

8. Pada versi Aurora MySQL tersebut, pengaturan


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

7 untuk hash join diaktifkan secara default dan Anda hanya perlu mengaktifkan


mysql> SET optimizer_switch='hash_join_cost_based=off';

8.

Dengan pengaturan ini, optimalisasi memilih untuk menggunakan hash join berdasarkan biaya, karakteristik kueri, dan ketersediaan sumber daya. Jika estimasi biaya salah, Anda dapat memaksa optimalisasi untuk memilih hash join. Caranya adalah dengan mengatur


mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2
    ->     WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                                          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
|  1 | SIMPLE      | hj_small | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort                                |
|  1 | SIMPLE      | hj_big   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Hash Join Outer table hj_big)  |
|  1 | SIMPLE      | hj_big2  | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using join buffer (Hash Join Inner table hj_big2) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
3 rows in set (0.04 sec)

1, sebuah variabel server MySQL, ke


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

0. Contoh berikut mengilustrasikan cara memaksa pengoptimal untuk memilih hash join.


mysql> SET optimizer_switch='hash_join_cost_based=off';

Pengaturan ini menimpa keputusan pengoptimal berbasis biaya. Meskipun pengaturan dapat berguna untuk pengujian dan pengembangan, kami menyarankan Anda untuk tidak menggunakannya dalam produksi.

Mengoptimalkan kueri untuk hash join

Untuk mengetahui apakah queri dapat memanfaatkan hash join, gunakan pernyataan


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

4 untuk membuat profil kueri terlebih dahulu. Pernyataan


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

4 memberikan informasi tentang rencana pelaksanaan yang akan digunakan untuk kueri tertentu.

Pada output pernyataan


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

4, kolom


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

7 menjelaskan informasi tambahan termasuk dengan rencana pelaksanaan. Jika hash join berlaku pada tabel yang digunakan dalam queri, kolom ini mencakup nilai yang mirip dengan berikut ini:


mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2
    ->     WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                                          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
|  1 | SIMPLE      | hj_small | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort                                |
|  1 | SIMPLE      | hj_big   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Hash Join Outer table hj_big)  |
|  1 | SIMPLE      | hj_big2  | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using join buffer (Hash Join Inner table hj_big2) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
3 rows in set (0.04 sec)

7)


mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2
    ->     WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                                          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
|  1 | SIMPLE      | hj_small | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort                                |
|  1 | SIMPLE      | hj_big   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Hash Join Outer table hj_big)  |
|  1 | SIMPLE      | hj_big2  | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using join buffer (Hash Join Inner table hj_big2) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
3 rows in set (0.04 sec)

8)

Contoh berikut menunjukkan penggunaan EXPLAIN untuk melihat rencana pelaksanaan untuk kueri hash join.


mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2
    ->     WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                                          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
|  1 | SIMPLE      | hj_small | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort                                |
|  1 | SIMPLE      | hj_big   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Hash Join Outer table hj_big)  |
|  1 | SIMPLE      | hj_big2  | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using join buffer (Hash Join Inner table hj_big2) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
3 rows in set (0.04 sec)

Dalam output,


mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2
    ->     WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                                          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
|  1 | SIMPLE      | hj_small | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort                                |
|  1 | SIMPLE      | hj_big   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Hash Join Outer table hj_big)  |
|  1 | SIMPLE      | hj_big2  | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using join buffer (Hash Join Inner table hj_big2) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
3 rows in set (0.04 sec)

9 adalah tabel yang digunakan untuk membangun tabel hash, dan innodb_read_only0 adalah tabel yang digunakan untuk menyelidiki tabel hash.

Untuk informasi lebih lanjut tentang format output


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

4 yang diperluas, lihat Format output Extended EXPLAINdalam dokumentasi produk MySQL.

Di Aurora MySQL 2.08 dan yang lebih tinggi, Anda dapat menggunakan petunjuk SQL untuk memengaruhi apakah kueri menggunakan hash join atau tidak, dan tabel mana yang digunakan untuk sisi build dan probe join. Untuk detail, lihat .

Penggunaan Amazon Aurora untuk mengukur pembacaan database MySQL Anda

Anda dapat menggunakan Amazon Aurora dengan instans DB MySQL Anda untuk memanfaatkan kemampuan penskalaan baca Amazon Aurora dan memperluas beban kerja baca untuk instans DB MySQL Anda. Untuk menggunakan Aurora untuk menskala baca instans DB MySQL Anda, buat klaster DB Aurora MySQL dan buat menjadi replika baca dari instans DB MySQL Anda. Lalu hubungkan ke klaster Aurora MySQL untuk memproses kueri baca. Database sumber dapat berupa instans DB RDS for MySQL, atau sebuah database MySQL yang dijalankan secara eksternal pada Amazon RDS. Untuk informasi selengkapnya, lihat .

Praktik terbaik untuk ketersediaan tinggi Aurora MySQL

Anda dapat menerapkan praktik terbaik berikut untuk meningkatkan ketersediaan kluster MySQL Aurora Anda.

Menggunakan Amazon Aurora untuk Pemulihan Bencana dengan database MySQL Anda

Anda dapat menggunakan Amazon Aurora dengan instans DB MySQL Anda untuk membuat backup di luar lokasi untuk pemulihan bencana. Untuk menggunakan Aurora untuk pemulihan bencana instans DB MySQL Anda, buat klaster DB Amazon Aurora dan buat replika baca instans DB MySQL Anda. Ini berlaku untuk satu instans DB RDS for MySQL, atau sebuah database MySQL yang dijalankan secara eksternal pada Amazon RDS.

Saat Anda mengatur replikasi di antara instans DB MySQL dan klaster DB Amazon Aurora MySQL, Anda harus memantau replikasi untuk memastikan agar tetap sehat dan perbaiki jika perlu.

Untuk petunjuk tentang cara membuat klaster DB Amazon Aurora MySQL dan membuat menjadi replika baca dari instans DB MySQL Anda, ikuti prosedur dalam .

Untuk informasi lebih lanjut tentang model pemulihan bencana, lihatBagaimana memilih opsi pemulihan bencana terbaik untuk klaster Amazon Aurora MySQL Anda.

Bermigrasi dari MySQL ke Amazon Aurora MySQL dengan waktu henti yang berkurang

Saat mengimpor data dari database MySQL yang mendukung aplikasi langsung ke klaster DB Amazon Aurora MySQL, Anda mungkin ingin mengurangi waktu gangguan layanan saat Anda bermigrasi. Untuk melakukannya, Anda dapat menggunakan prosedur yang didokumentasikan dalam Mengimpor data ke instans MySQL atau MariaDB DB dengan waktu henti yang berkurang dalam Panduan Pengguna Amazon Relational Database Service. Prosedur ini terutama dapat membantu jika Anda bekerja dengan basis data yang sangat besar. Anda dapat menggunakan prosedur untuk mengurangi biaya impor dengan meminimalkan jumlah data yang dilewatkan melalui jaringan ke AWS.

Prosedur ini mencantumkan langkah-langkah untuk mentransfer salinan data database Anda ke instans Amazon EC2 dan mengimpor data ke RDS baru untuk instans DB MySQL. Karena Amazon Aurora kompatibel dengan MySQL, Anda dapat menggunakan klaster DB Amazon Aurora untuk instans DB MySQL Amazon RDS target.

Menghindari kinerja lambat, restart otomatis, dan failover untuk instans Aurora MySQL DB

Jika Anda menjalankan beban kerja berat atau beban kerja yang melonjak melampaui sumber daya yang dialokasikan dari instans DB Anda, Anda dapat menghabiskan sumber daya tempat Anda menjalankan aplikasi dan database Aurora. Untuk mendapatkan metrik pada instans database Anda seperti pemanfaatan CPU, penggunaan memori, dan jumlah koneksi database yang digunakan, Anda dapat merujuk ke metrik yang disediakan oleh Amazon CloudWatch, Performance Insights, dan Pemantauan yang Ditingkatkan. Untuk informasi selengkapnya tentang pemantauan instans DB, lihatMetrik pemantauan di klaster Amazon Aurora.

Jika beban kerja Anda menghabiskan sumber daya yang Anda gunakan, instans DB Anda mungkin melambat, memulai ulang, atau bahkan gagal ke instans DB lain. Untuk menghindari hal ini, pantau pemanfaatan sumber daya Anda, periksa beban kerja yang berjalan pada instans DB Anda, dan buat pengoptimalan jika diperlukan. Jika pengoptimalan tidak meningkatkan metrik instans dan mengurangi kelelahan sumber daya, pertimbangkan untuk meningkatkan instans DB Anda sebelum mencapai batasnya. Untuk informasi selengkapnya tentang kelas instans DB yang tersedia, lihatKelas contoh Aurora DB.

Rekomendasi untuk fitur MySQL

Fitur berikut tersedia di Aurora MySQL untuk kompatibilitas MySQL. Namun, mereka memiliki masalah kinerja, skalabilitas, stabilitas, atau kompatibilitas di lingkungan Aurora. Oleh karena itu, kami menyarankan Anda mengikuti pedoman tertentu dalam penggunaan fitur-fitur ini. Misalnya, kami rekomendasikan agar Anda tidak menggunakan fitur tertentu untuk penerapan Aurora produksi.

Menggunakan replikasi multithreaded di Aurora MySQL versi 3

Secara default, Aurora menggunakan replikasi utas tunggal saat klaster DB Aurora MySQL digunakan sebagai replika baca untuk replikasi log biner.

Meskipun Aurora MySQL tidak melarang replikasi multithreaded, fitur ini hanya didukung di Aurora MySQL versi 3 dan yang lebih tinggi.

Aurora MySQL versi 1 dan 2 mewarisi beberapa masalah terkait replikasi multiutas dari MySQL. Untuk versi tersebut, kami rekomendasikan agar Anda tidak menggunakan replikasi multiutas dalam produksi.

Jika Anda menggunakan replikasi multi thread, kami sarankan agar Anda menguji setiap penggunaan secara menyeluruh.

Untuk informasi selengkapnya tentang replikasi di Amazon Aurora, lihat Replikasi dengan Amazon Aurora. Untuk informasi tentang replikasi multithreaded di Aurora MySQL versi 3, lihat.

MemintaAWS Lambdafungsi menggunakan fungsi MySQL asli

Jika Anda menggunakan Amazon Aurora versi 1.16 atau lebih baru, kami menyarankan menggunakan fungsi MySQL asliinnodb_read_only2daninnodb_read_only3untuk memanggil fungsi Lambda.

Jika Anda menggunakan prosedur innodb_read_only4 yang usang, kami sarankan agar Anda menutup panggilan ke prosedur innodb_read_only4 dalam prosedur yang disimpan. Anda dapat memanggil prosedur yang disimpan ini dari berbagai sumber, seperti pemicu atau kode klien. Pendekatan ini dapat membantu menghindari masalah ketidakcocokan impedansi dan memudahkan pemrogram database Anda untuk memanggil fungsi Lambda.

Untuk informasi selengkapnya tentang menjalankan fungsi Lambda dari Amazon Aurora, lihat Memanggil fungsi Lambda dari klaster DB Amazon Aurora MySQL.

Menghindari transaksi XA dengan Amazon Aurora MySQL

Sebaiknya jangan gunakan transaksi eXtended Architecture (XA) dengan Aurora MySQL, karena dapat menyebabkan waktu pemulihan yang lama jika XA berada di status innodb_read_only6. Jika Anda harus menggunakan transaksi XA dengan Aurora MySQL, ikuti praktik terbaik ini:

Jangan tinggalkan transaksi XA terbuka pada status innodb_read_only6.
Pertahankan transaksi XA sekecil mungkin.

Untuk informasi lebih lanjut tentang menggunakan transaksi XA dengan MySQL, lihat Transaksi XAdalam dokumentasi MySQL.

Menjaga kunci asing dihidupkan selama pernyataan DML*

Kami sangat menyarankan agar Anda tidak menjalankan pernyataan data definition language (DDL) saat variabel innodb_read_only8 diatur menjadi innodb_read_only9 (nonaktif).

Jika Anda perlu menyisipkan atau memperbarui baris yang memerlukan pelanggaran sementara terhadap kunci asing, ikuti langkah-langkah beriku:

Atur innodb_read_only8 ke innodb_read_only9.
Membuat perubahan data manuplation language (DML).
Pastikan bahwa perubahan yang telah Anda selesaikan tidak melanggar kendala kunci asing.
Atur innodb_read_only8 ke innodb_read_only3 (aktif).

Selain itu, ikuti praktik terbaik lainnya untuk kendala kunci asing:

Pastikan bahwa aplikasi klien tidak mengatur variabel innodb_read_only8 ke innodb_read_only9 sebagai bagian dari variabel innodb_read_only6.
Jika pemulihan dari backup logis seperti innodb_read_only7 gagal atau tidak lengkap, pastikan bahwa innodb_read_only8 diatur menjadi innodb_read_only3 sebelum memulai operasi lain pada sesi yang sama. backup logis diatur innodb_read_only8 ke innodb_read_only9 saat dimulai.

Mengkonfigurasi seberapa sering buffer log memerah

Di MySQL Community Edition, untuk membuat transaksi tahan lama, buffer log InnoDB harus disiram ke penyimpanan yang tahan lama. Anda menggunakanON2parameter untuk mengkonfigurasi seberapa sering buffer log memerah ke disk.

Ketika Anda mengaturON2parameter ke nilai default dari 1, buffer log memerah pada setiap transaksi komit. Pengaturan ini membantu untuk menjaga databasepatuh. Kami menyarankan agar Anda tetap mempertahankan pengaturan default 1.

PerubahanON2ke nilai nondefault, 0 atau 2, dapat membantu mengurangi latensi bahasa manipulasi data (DMS), tetapi mengorbankan daya tahan catatan log. Kurangnya daya tahan membuat database ACID noncompliant. Kami menyarankan agar basis data Anda mematuhi ACID untuk menghindari risiko kehilangan data jika server dimulai ulang. Untuk informasi selengkapnya tentang parameter ini, lihatdalam dokumentasi MySQL.

Di Aurora MySQL, pemrosesan log ulang diturunkan ke lapisan penyimpanan, jadi tidak ada pembilasan untuk log file yang terjadi pada instans DB. Saat penulisan dikeluarkan, log ulang dikirim dari instans DB penulis langsung ke volume klaster Aurora. Satu-satunya menulis bahwa lintas jaringan adalah mengulang catatan log. Tidak ada halaman yang pernah ditulis dari tingkat database.

Secara default, di Aurora MySQL,ON2parameter diatur ke 1. Setiap thread yang melakukan transaksi menunggu konfirmasi dari volume klaster Aurora. Konfirmasi ini menunjukkan bahwa catatan ini dan semua catatan log redo sebelumnya ditulis dan telah dicapaikuorum. Mempertahankan catatan log dan mencapai kuorum membuat transaksi tahan lama, baik melalui autocommit atau komit eksplisit. Untuk informasi selengkapnya tentang arsitektur penyimpanan Aurora, lihatPenyimpanan Amazon Aurora didemistifikasi.

Aurora MySQL tidak menyiram log ke file data seperti MySQL Community Edition. Namun, Anda dapat menggunakanON2parameter untuk mengendurkan kendala daya tahan saat menulis catatan log ulang ke volume klaster Aurora. Ketika Anda mengaturON2ke nilai nondefault (0 atau 2), database tidak menunggu konfirmasi bahwa catatan log ulang ditulis ke volume klaster Aurora. Sementara pengaturan ini dapat menurunkan latensi DML-ke klien, mereka juga dapat mengakibatkan kehilangan data dalam hal failover atau restart. Oleh karena itu, kami rekomendasikan agar Anda tetap mempertahankanON2parameter diatur ke nilai default dari 1.

Meskipun kehilangan data dapat terjadi di MySQL Community Edition dan Aurora MySQL, perilaku berbeda di setiap database karena arsitekturnya yang berbeda. Perbedaan arsitektur ini dapat menyebabkan berbagai tingkat kehilangan data. Untuk memastikan bahwa database Anda sesuai dengan ACID, selalu aturON2ke 1.

Anda tidak dapat mengonfigurasiON2parameter di Aurora MySQL versi 3. Aurora MySQL versi 3 selalu menggunakan pengaturan default 1, yang sesuai dengan ACID.

kode mysql