Performa kueri profil di Azure Database for MySQL - Server Fleksibel dengan menggunakan EXPLAIN

  • Artikel

BERLAKU UNTUK: Azure Database for MySQL - Server Tunggal Database Azure untuk MySQL - Server Fleksibel

Penting

Server tunggal Azure Database for MySQL berada di jalur penghentian. Kami sangat menyarankan Agar Anda meningkatkan ke server fleksibel Azure Database for MySQL. Untuk informasi selengkapnya tentang migrasi ke server fleksibel Azure Database for MySQL, lihat Apa yang terjadi pada Server Tunggal Azure Database for MySQL?

EXPLAIN adalah alat berguna yang dapat membantu Anda mengoptimalkan kueri. Anda dapat menggunakan pernyataan EXPLAIN untuk mendapatkan informasi tentang bagaimana pernyataan SQL dijalankan. Berikut ini menunjukkan contoh output dari menjalankan pernyataan EXPLAIN.

mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 10.00
        Extra: Using where

Dalam contoh ini, nilai kunci adalah NULL, yang berarti bahwa server fleksibel Azure Database for MySQL tidak dapat menemukan indeks apa pun yang dioptimalkan untuk kueri. Akibatnya, ia melakukan pemindaian tabel penuh. Mari kita optimalkan kueri ini dengan menambahkan indeks pada kolom ID, lalu jalankan pernyataan EXPLAIN lagi.

mysql> ALTER TABLE tb1 ADD KEY (id);
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ref
possible_keys: id
          key: id
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

Sekarang, output menunjukkan bahwa server fleksibel Azure Database for MySQL menggunakan indeks untuk membatasi jumlah baris menjadi 1, yang secara dramatis mempersingkat waktu pencarian.

Indeks penutup

Indeks penutup mencakup semua kolom kueri untuk mengurangi pengambilan nilai dari tabel data. Pernyataan GROUP BY berikut dan output terkait menggambarkan hal ini.

mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using temporary; Using filesort

Output menunjukkan bahwa server fleksibel Azure Database for MySQL tidak menggunakan indeks apa pun, karena indeks yang tepat tidak tersedia. Output juga menunjukkan Menggunakan sementara; Menggunakan filesort, yang menunjukkan bahwa server fleksibel Azure Database for MySQL membuat tabel sementara untuk memenuhi klausa GROUP BY .

Membuat indeks hanya pada kolom c2 tidak ada bedanya, dan server fleksibel Azure Database for MySQL masih perlu membuat tabel sementara:

mysql> ALTER TABLE tb1 ADD KEY (c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using temporary; Using filesort

Dalam hal ini, Anda dapat membuat indeks tertutup pada c1dan c2 dengan menambahkan nilai c2" langsung dalam indeks, yang dapat menghapus pencarian data lebih lanjut.

mysql> ALTER TABLE tb1 ADD KEY covered(c1,c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: index
possible_keys: covered
          key: covered
      key_len: 108
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using index

Seperti yang ditunjukkan oleh output EXPLAIN di atas, server fleksibel Azure Database for MySQL sekarang menggunakan indeks tercakup dan menghindari harus membuat tabel sementara.

Indeks gabungan

Indeks gabungan terdiri dari nilai dari beberapa kolom dan dapat dianggap sebagai array baris yang diurutkan dengan menggabungkan nilai kolom terindeks. Metode ini dapat berguna dalam pernyataan GROUP BY.

mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using filesort

Server fleksibel Azure Database for MySQL melakukan operasi pengurutan file yang cukup lambat, terutama ketika harus mengurutkan banyak baris. Untuk mengoptimalkan kueri ini, buat indeks gabungan pada kedua kolom yang sedang diurutkan.

mysql> ALTER TABLE tb1 ADD KEY my_sort2 (c1, c2);
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: index
possible_keys: NULL
          key: my_sort2
      key_len: 108
          ref: NULL
         rows: 10
     filtered: 11.11
        Extra: Using where; Using index

Output pernyataan EXPLAIN sekarang menunjukkan bahwa server fleksibel Azure Database for MySQL menggunakan indeks gabungan untuk menghindari pengurutan tambahan karena indeks sudah diurutkan.

Kesimpulan

Anda dapat meningkatkan performa secara signifikan dengan menggunakan EXPLAIN bersama dengan berbagai jenis indeks. Memiliki indeks pada tabel tidak selalu berarti bahwa server fleksibel Azure Database for MySQL dapat menggunakannya untuk kueri Anda. Selalu validasi asumsi Anda menggunakan EXPLAIN dan optimalkan kueri menggunakan indeks.

Langkah berikutnya

  • Untuk menemukan jawaban serekan atas pertanyaan Anda yang paling penting atau memposting atau menjawab pertanyaan, kunjungi Stack Overflow.