Tips:
1. Bagaimana cara mendeteksi pernyataan yang tidak efisien?
Di MySQL, dengan menyetel --log-slow-queries=[file name] di parameter startup, Anda dapat merekam pernyataan SQL yang waktu eksekusinya melebihi long_query_time (defaultnya adalah 10 detik) di file log yang ditentukan. Anda juga dapat mengubah waktu kueri yang lama di file konfigurasi startup, seperti:
# Tetapkan waktu kueri yang lama menjadi 8 detik
long_query_time=8
2. Bagaimana cara menanyakan indeks tabel?
Anda dapat menggunakan pernyataan SHOW INDEX, seperti:
SHOW INDEX FROM [nama tabel]
3. Bagaimana cara menanyakan penggunaan indeks dari pernyataan tertentu?
Anda dapat menggunakan pernyataan EXPLAIN untuk melihat penggunaan indeks dari pernyataan SELECT tertentu. Jika berupa pernyataan UPDATE atau DELETE, maka perlu diubah menjadi pernyataan SELECT terlebih dahulu.
4. Bagaimana cara mengekspor konten mesin INNODB ke file log kesalahan?
Kita dapat menggunakan perintah SHOW INNODB STATUS untuk melihat banyak informasi berguna tentang mesin INNODB, seperti proses saat ini, transaksi, kesalahan kunci asing, kebuntuan masalah dan statistik lainnya. Bagaimana cara mengaktifkan informasi ini untuk dicatat dalam file log? Selama Anda membuat tabel innodb_monitor menggunakan pernyataan berikut, MySQL akan menulis sistem ke file log kesalahan setiap 15 detik:
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
Jika Anda tidak perlu lagi mengekspor ke file log kesalahan , hapus saja Tabel ini dapat berupa:
DROP TABLE innodb_monitor
5. Bagaimana cara menghapus file log besar secara teratur?
Cukup atur waktu kedaluwarsa log di file konfigurasi startup:
expired_logs_days=10
Catatan:
1. Fokus pada indeks
Berikut ini menggunakan tabel TSK_TASK sebagai contoh untuk mengilustrasikan proses optimasi pernyataan SQL. Tabel TSK_TASK digunakan untuk menyimpan tugas pemantauan sistem. Bidang dan indeks yang relevan adalah sebagai berikut:
ID: kunci utama;
MON_TIME
: waktu pemantauan yang dibuat;
Catatan: MySQL akan secara otomatis membuat indeks untuk kunci asing. Selama proses optimasi ini, ditemukan bahwa indeks kunci asing yang dibuat secara otomatis akan menyebabkan gangguan yang tidak perlu terhadap efisiensi pernyataan SQL.
Pertama, kami menemukan di file log bahwa eksekusi pernyataan berikut relatif lambat, lebih dari 10 detik:
# Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143
pilih * dari TSK_TASK WHERE STATUS_ID = 1064 dan MON_TIME >= ' 22-11-2007' dan MON_TIME < '23-11-2007';
Ternyata perlu ditemukan 295 record yang memenuhi syarat di antara 88143 record, yang tentu saja lambat. Gunakan pernyataan EXPLAIN dengan cepat untuk memeriksa penggunaan indeks:
+----+-------------+----------+------+- ---------
|.id |.pilih_tipe |.tabel |.ketik|
.mungkin_kunci |.kunci_len |
----------+------+-----------
|.SEDERHANA |.ref |
. +-------------+----------+------+--------- --Dapat
dilihat bahwa ada Ada dua indeks yang tersedia: FK_task_status_id_TO_SYS_HIER_INFO, TSK_TASK_KEY_MON_TIME, dan indeks kunci asing pada STATUS_ID digunakan ketika pernyataan akhirnya dieksekusi.
Mari kita lihat lagi indeks tabel TSK_TASK:
+----------+-------------------------- -- --------
|. Tabel |. Nama_kunci |. Nama_Kolom |
. -- --------------
|.TSK_TASK |.UTAMA |.ID |.999149
|
.
-------------------------Di bawah Oracle atau database relasional lainnya, kondisi WHERE Urutan bidang dalam indeks memainkan peran penting dalam pemilihan indeks. Mari kita sesuaikan urutan bidang, letakkan STATUS_ID di akhir, dan JELASKAN lagi:
JELASKAN pilih * dari TSK_TASK WHERE MON_TIME >= '22-11-2007' dan MON_TIME < '23-11-2007' dan STATUS_ID = 1064
; tidak berpengaruh, MySQL masih menggunakan indeks kunci asing STATUS_ID yang dibuat oleh sistem.
Setelah dianalisis dengan cermat, tampaknya atribut Cardinality (yaitu, jumlah nilai unik dalam indeks) memainkan peran yang sangat penting dalam pemilihan indeks. MySQL memilih indeks dengan jumlah nilai unik yang lebih sedikit dalam indeks sebagai indeks seluruh pernyataan.
Untuk pernyataan ini, jika Anda menggunakan FK_task_status_id_TO_SYS_HIER_INFO sebagai indeks dan tabel TSK_TASK menyimpan data selama berhari-hari, jumlah catatan yang dipindai akan banyak dan kecepatannya akan lambat. Ada beberapa solusi optimasi yang tersedia:
Jika tidak banyak tugas dalam sehari, kita hapus indeks FK_task_status_id_TO_SYS_HIER_INFO, kemudian MySQL akan menggunakan indeks TSK_TASK_KEY_MON_TIME, lalu memindai catatan dengan STATUS_ID 1064 pada data hari itu, yang tidak lambat ;
Jika ada banyak tugas dalam sehari, kita perlu menghapus indeks FK_task_status_id_TO_SYS_HIER_INFO dan TSK_TASK_KEY_MON_TIME, lalu membuat indeks gabungan STATUS_ID, MON_TIME, yang tentunya akan sangat efisien.
Oleh karena itu, disarankan untuk tidak menggunakan kunci asing untuk tabel dengan jumlah rekaman yang banyak untuk menghindari penurunan efisiensi kinerja yang serius.
2. Usahakan untuk mengontrol jumlah record pada setiap tabel.
Jika jumlah record dalam suatu tabel banyak, pengelolaan dan pemeliharaannya akan sangat merepotkan. Misalnya pemeliharaan indeks akan memakan waktu lama yang akan menyebabkan gangguan yang besar pada tabel gangguan pada pengoperasian normal sistem.
Untuk tabel yang volume datanya terus bertambah seiring berjalannya waktu, kita dapat membedakan data real-time dan data historis berdasarkan waktu. Kita dapat menggunakan program layanan latar belakang untuk secara teratur memindahkan data dalam tabel real-time ke tabel historis, sehingga dapat dikontrol jumlah catatan dalam tabel waktu nyata dan meningkatkan kinerja kueri dan efisiensi operasional. Namun perlu diingat bahwa waktu setiap perpindahan harus cukup singkat agar tidak mempengaruhi penulisan data pada program normal. Jika terlalu lama, dapat menyebabkan masalah kebuntuan.
3. Strategi hashing data (partisi):
Ketika jumlah pelanggan mencapai skala tertentu, satu database tidak akan mampu mendukung akses bersamaan yang lebih tinggi. Saat ini, Anda dapat mempertimbangkan untuk melakukan hashing (mempartisi) data pelanggan ke dalam beberapa database berbagi beban. Meningkatkan kinerja dan efisiensi sistem secara keseluruhan.