Praktik terbaik untuk memecahkan masalah Azure Database for MySQL - Server Fleksibel

  • 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?

Gunakan bagian berikut untuk menjaga database server fleksibel Azure Database for MySQL Anda berjalan dengan lancar dan gunakan informasi ini sebagai prinsip panduan untuk memastikan bahwa skema dirancang secara optimal dan memberikan performa terbaik untuk aplikasi Anda.

Memeriksa jumlah indeks

Di lingkungan database yang sibuk, Anda mungkin mengamati penggunaan I/O tinggi, yang dapat menjadi indikator pola akses data yang buruk. Indeks yang tidak digunakan dapat berdampak negatif pada performa karena mengonsumsi ruang disk dan cache, dan memperlambat operasi tulis (INSERT / DELETE / UPDATE). Indeks yang tidak digunakan tidak perlu mengonsumsi lebih banyak ruang penyimpanan dan meningkatkan ukuran cadangan.

Sebelum Anda menghapus indeks apa pun, pastikan untuk mengumpulkan informasi yang cukup guna memverifikasi bahwa indeks tidak lagi digunakan. Verifikasi ini dapat membantu Anda menghindari penghapusan indeks yang penting secara tidak sengaja untuk kueri yang hanya berjalan setiap triwulanan atau tahunan. Selain itu, pastikan untuk mempertimbangkan apakah indeks digunakan untuk memberlakukan keunikan atau pengurutan.

Catatan

Ingatlah untuk meninjau indeks secara berkala dan melakukan pembaruan yang diperlukan berdasarkan modifikasi apa pun pada data tabel.

SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;

(Atau)

use information_schema; select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows from tables join statistics on (statistics.table_name = tables.table_name and statistics.table_schema = '<YOUR DATABASE NAME HERE>' and ((tables.table_rows / statistics.cardinality) > 1000));

Mencantumkan indeks tersibuk di server

Output dari kueri berikut ini menyediakan informasi tentang indeks yang paling banyak digunakan di semua tabel dan skema di server database. Informasi ini sangat membantu dalam mengidentifikasi rasio penulisan untuk membaca terhadap setiap indeks dan angka latensi untuk bacaan serta operasi penulisan individu, yang dapat menunjukkan bahwa penyetelan lebih lanjut diperlukan terhadap tabel yang mendasari dan kueri dependen.

SELECT 
object_schema AS table_schema, 
object_name AS table_name, 
index_name, count_star AS all_accesses, 
count_read, 
count_write, 
Concat(Truncate(count_read / count_star * 100, 0), ':', 
Truncate(count_write / count_star * 100, 0)) AS read_write_ratio, 
 count_fetch AS rows_selected , 
 count_insert AS rows_inserted, 
 count_update AS rows_updated, 
 count_delete AS rows_deleted, 
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency , 
 Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency, 
 Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency, 
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency, 
 Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS  delete_latency 
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE index_name IS NOT NULL AND count_star > 0 
ORDER BY sum_timer_wait DESC

Meninjau desain kunci primer

Server fleksibel Azure Database for MySQL menggunakan mesin penyimpanan InnoDB untuk semua tabel nontemporer. Dengan InnoDB, data disimpan dalam indeks berkluster menggunakan struktur pohon B. Tabel diatur secara fisik berdasarkan nilai kunci primer, yang berarti bahwa baris disimpan dalam urutan kunci primer.

Setiap entri kunci sekunder dalam tabel InnoDB berisi pointer ke nilai kunci primer tempat data disimpan. Dengan kata lain, entri indeks sekunder berisi salinan nilai kunci primer tempat entri menunjuk. Oleh karena itu, pilihan kunci primer memiliki efek langsung pada jumlah overhead penyimpanan dalam tabel Anda.

Jika berasal dari data aktual (misalnya, nama pengguna, email, SSN, dll.), kunci ini disebut kunci alami. Jika kunci adalah buatan dan tidak berasal dari data (misalnya, bilangan bulat autoincremented), kunci tersebut disebut sebagai kunci sintetis atau kunci pengganti.

Umumnya, disarankan untuk menghindari penggunaan kunci primer alami. Kunci ini sering kali sangat lebar dan berisi nilai panjang dari satu atau beberapa kolom. Hal ini nantinya dapat mengakibatkan overhead penyimpanan yang parah karena nilai kunci primer disalin ke setiap entri kunci sekunder. Selain itu, kunci alami biasanya tidak mengikuti urutan yang telah ditentukan, yang secara dramatis mengurangi performa dan memprovokasi fragmentasi halaman ketika baris dimasukkan atau diperbarui. Untuk menghindari masalah ini, gunakan kunci pengganti yang meningkat secara monoton sebagai ganti kunci alami. Kolom integer autoincrement (besar) adalah contoh yang baik dari kunci pengganti yang meningkat secara monoton. Jika Anda memerlukan kombinasi kolom tertentu, gunakan cara alternatif seperti mendeklarasikan kolom tersebut sebagai kunci sekunder yang unik.

Selama tahap awal membangun aplikasi, Anda mungkin tidak berpikir ke depan untuk membayangkan waktu ketika tabel Anda mulai mendekati memiliki dua miliar baris. Akibatnya, Anda mungkin memilih untuk menggunakan bilangan bulat 4 byte yang ditandatangani untuk jenis data kolom ID (kunci primer). Pastikan untuk memeriksa semua kunci primer tabel dan beralih untuk menggunakan kolom bilangan bulat 8 byte (BIGINT) untuk mengakomodasi potensi volume atau pertumbuhan yang tinggi.

Catatan

Untuk informasi selengkapnya tentang jenis data dan nilai maksimumnya, di Manual Referensi MySQL, lihat Jenis Data.

Menggunakan indeks penutup

Bagian sebelumnya menjelaskan bagaimana indeks di MySQL disusun sebagai pohon B dan dalam indeks berkluster, node leaf berisi halaman data dari tabel dasar. Indeks sekunder memiliki struktur pohon B yang sama dengan indeks berkluster, dan Anda dapat menentukannya pada tabel atau melihatnya dengan indeks berkluster atau tumpukan. Setiap baris indeks dalam indeks sekunder berisi nilai kunci non-kluster dan pencari baris. Pencari ini menunjuk ke baris data dalam indeks berkluster atau tumpukan yang memiliki nilai kunci. Hasilnya, setiap pencarian yang melibatkan indeks sekunder harus menavigasi mulai dari node akar melalui node cabang ke node leaf yang benar untuk mengambil nilai kunci primer. Sistem kemudian menjalankan operasi baca IO acak pada indeks kunci primer (yang sekali lagi menavigasi dari node akar melalui node cabang ke node leaf yang benar) untuk mendapatkan baris data.

Untuk menghindari operasi baca IO acak tambahan ini pada indeks kunci utama untuk mendapatkan baris data, gunakan indeks penutup, yang menyertakan semua bidang yang diperlukan oleh kueri. Umumnya, menggunakan pendekatan ini bermanfaat untuk beban kerja terikat I/O dan beban kerja yang di-cache. Jadi sebagai praktik terbaik, gunakan indeks penutup karena cocok dalam memori dan lebih kecil serta lebih efisien untuk dibaca daripada memindai semua baris.

Pertimbangkan, misalnya, tabel yang Anda gunakan untuk mencoba menemukan semua karyawan yang bergabung ke perusahaan setelah 1 Januari 2000.

mysql> show create table employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(10) DEFAULT NULL,
  `lname` varchar(10) DEFAULT NULL,
  `joindate` datetime DEFAULT NULL,
  `department` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)`

`mysql> select empid, fname, lname from employee where joindate > '2000-01-01';

Jika menjalankan paket EXPLAIN pada kueri ini, Anda akan melihat bahwa saat ini tidak ada indeks yang digunakan, dan klausul where saja yang digunakan untuk memfilter data karyawan.

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

Namun, jika Anda menambahkan indeks yang mencakup kolom di klausa where, bersama dengan kolom yang diproyeksikan, Anda akan melihat bahwa indeks sedang digunakan untuk menemukan kolom jauh lebih cepat dan efisien.

mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);

Sekarang, jika Anda menjalankan paket EXPLAIN pada kueri yang sama, nilai "Menggunakan Indeks" akan muncul di bidang “Ekstra”, yang berarti bahwa InnoDB menjalankan kueri menggunakan indeks yang kami buat sebelumnya, yang mengonfirmasi ini sebagai indeks penutup.

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: range
possible_keys: cvg_idx_ex
          key: cvg_idx_ex
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

Catatan

Anda harus memilih urutan kolom yang benar dalam indeks penutup untuk melayani kueri dengan benar. Aturan umumnya adalah memilih kolom untuk pemfilteran terlebih dahulu (klausul WHERE), lalu mengurutkan/mengelompokkan (ORDER BY dan GROUP BY), dan akhirnya proyeksi data (SELECT).

Dari contoh sebelumnya, kami telah melihat bahwa memiliki indeks penutup untuk kueri memberikan jalur pengambilan data yang lebih efisien dan mengoptimalkan performa di lingkungan database dengan banyak operasi bersamaan.

Langkah berikutnya

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