Penalaan Prestasi MySQL untuk SysAdmins: Daripada Pertanyaan Lambat kepada Pangkalan Data Lebih Pantas

Permohonan anda baik-baik saja. Pelayan web anda baik. Tetapi pangkalan data sukar bernafas, dan setiap papan pemuka yang anda sentuh mengambil masa tiga saat untuk dimuatkan. Jika anda pernah menjadi sysadmin itu, merenung `atas` semasa mysqld memakan 400% CPU dan tertanya-tanya di mana untuk bermula — panduan ini adalah untuk anda.

Penalaan prestasi pangkalan data ialah salah satu kemahiran yang memisahkan pengendali pelayan daripada pelayan jurutera. Berita baik? Anda tidak perlu menjadi DBA untuk membuat penambahbaikan dramatik. Kebanyakan masalah prestasi MySQL dan MariaDB datang daripada beberapa sebab yang boleh berulang: indeks yang hilang, pertanyaan yang ditulis dengan teruk, penampan tersalah konfigurasi dan jadual yang direka bentuk tanpa memikirkan cara ia akan disoal. Dalam artikel ini, kita akan menelusuri aliran kerja yang praktikal dan teratur — daripada mencari pertanyaan perlahan, memahami mengapa pertanyaan itu lambat, hingga membetulkannya pada peringkat skema, pertanyaan dan konfigurasi.

Langkah 1: Cari Pertanyaan Lambat Dahulu — Jangan Sekali-kali Tune Blind

Kesilapan terbesar yang sysadmin lakukan ialah mengedit `my.cnf` sebelum mereka tahu apa yang sebenarnya lambat. Perubahan konfigurasi tanpa bukti hanyalah tekaan dengan langkah tambahan. Mulakan dengan menghidupkan log pertanyaan perlahan.

```ini
slowquerylog = HIDUP
slowquerylog_file = /var/log/mysql/mysql-slow.log
masapertanyaanpanjang = 2
logqueriesnotusingindexes = HIDUP
```

Dengan `longquerytime = 2`, sebarang pertanyaan yang mengambil masa lebih daripada dua saat akan dilog. Pada pelayan yang sibuk, tetapkannya kepada 1 selama beberapa hari, kumpulkan data, kemudian naikkan semula. Bendera `logqueriesnotusingindexes` ialah emas — ia menangkap pertanyaan yang mengimbas keseluruhan jadual secara senyap.

Sebaik sahaja anda mempunyai log, agregatnya dan bukannya membacanya baris demi baris. `mysqldumpslow` dihantar dengan MySQL:

```bash
mysqldumpslow -s di /var/log/mysql/mysql-slow.log | kepala -20
```

Ini mengisih mengikut masa purata dan mengumpulkan pertanyaan yang sama (mengabaikan nilai literal), jadi anda serta-merta melihat pesalah teratas. Untuk analisis yang serius, `pt-query-digest` Percona menghasilkan pertanyaan pengelompokan laporan yang indah mengikut jumlah masa tindak balas — cerapan klasik "95% kesakitan anda datang daripada 5% pertanyaan anda" yang dibuat secara konkrit.

Langkah 2: JELASKAN — Baca Pelan Pertanyaan Seperti Pro

Memandangkan anda mempunyai pertanyaan penyebabnya, anda perlu memahami mengapa mereka merangkak. Awalan pertanyaan dengan `EXPLAIN` dan MySQL akan menunjukkan kepada anda pelan pelaksanaannya dan bukannya menjalankannya.

```sql
JELASKAN PILIH o.id, c.nama DARI pesanan o
SERTAI pelanggan c ON o.customer_id = c.id
DI MANA o.status = 'pending' PESANAN OLEH o.created_at DESC;
```

Lajur yang paling penting:

• jenis — ini ialah laluan akses anda. `const` dan `ref` adalah sangat baik (pencarian indeks). `julat` adalah baik. `SEMUA` bermaksud imbasan meja penuh — biasanya perkara yang membunuh anda. `indeks` ialah imbasan indeks penuh — lebih baik daripada `SEMUA` tetapi masih buruk.
• kunci — indeks manakah yang sebenarnya dipilih oleh MySQL. Jika ia adalah NULL dengan klausa `WHERE`, anda sedang mengimbas.
• rows — anggaran bilangan baris yang diperiksa. Bandingkan dengan kiraan baris sebenar jadual. Pertanyaan yang memeriksa 2 juta baris untuk mengembalikan 20 adalah masalah reka bentuk.
• Tambahan — tonton untuk `Menggunakan filesort` dan `Menggunakan sementara`. "Filesort" tidak bermaksud fail cakera (selalunya ia adalah pengisihan dalam memori), tetapi ia memang bermakna MySQL sedang mengisih set hasil yang mungkin boleh dihantar oleh indeks anda sebelum diisih. `Menggunakan sementara` bermakna MySQL membina jadual temp — biasa dengan `GROUP BY` atau `DISTINCT` pada lajur tidak diindeks.

Sebaik sahaja anda melihat `SEMUA`, `Menggunakan filesort` atau `Menggunakan sementara` pada pertanyaan hangat, anda telah menemui pistol merokok anda.

Langkah 3: Indeks Dilakukan Betul

Indeks ialah satu-satunya pembetulan leverage tertinggi dalam keseluruhan artikel ini. Indeks yang tiada boleh menukar pertanyaan 5-milisaat menjadi pertanyaan 5 saat. Tetapi indeks tidak percuma - setiap penulisan mesti mengekalkannya, dan setiap orang menggunakan cakera dan memori. Jadi anda mahukan indeks betul, bukan lebih daripadanya.

Susunan lajur indeks komposit penting. Untuk `WHERE a = ? DAN b = ?`, indeks pada `(a, b)` adalah sempurna. Untuk `WHERE a = ? DAN c = ?`, indeks pada `(a, b, c)` tidak berguna di luar lajur pertama — MySQL hanya boleh menggunakan awalan paling kiri. Peraturan praktikal: letakkan lajur kesamaan dahulu, kemudian lajur digunakan untuk julat atau susunan.

Meliputi indeks ialah kuasa besar tersembunyi. Jika setiap lajur pertanyaan perlu hidup di dalam indeks itu sendiri, MySQL tidak pernah menyentuh jadual sama sekali. `EXPLAIN` akan menunjukkan `Menggunakan indeks` dalam Tambahan. Untuk pertanyaan papan pemuka yang berulang kali membaca segelintir lajur yang sama, indeks penutup boleh menjadi 10–100x lebih pantas.

Pemesanan boleh disampaikan oleh indeks. Indeks pada `(status, createdat)` menjadikan `WHERE status = 'pending' ORDER BY createdat DESC` membaca baris yang sudah dalam susunan yang betul — tiada failsort.

Jangan terlebih indeks. Setiap indeks melambatkan INSERT/KEMASKINI. Jika jadual mempunyai lapan indeks dan hanya tiga yang pernah digunakan (semak dengan statistik penggunaan `skema_prestasi` atau `SHOW INDEX`), lepaskan indeks yang mati. Juga ingat: indeks berlebihan `(a)` dilindungi sepenuhnya oleh `(a, b)` — lepaskan indeks yang lebih pendek.

Langkah 4: Kolam Penampan InnoDB — Bang Terbaik Anda untuk Buck

MySQL moden dan MariaDB lalai kepada InnoDB, dan InnoDB hidup atau mati dengan kolam penimbalnya — kawasan memori yang menyimpan cache data dan indeks jadual. Jika data anda muat dalam kumpulan penimbal, pertanyaan mengenai memori. Jika tidak, setiap ketinggalan adalah bacaan cakera.

```ini
innodbbufferpool_size = 8G # ~70-80% daripada RAM pelayan DB khusus
innodbbufferpool_instances = 8 # 1GB setiap contoh ialah tempat yang menarik
```

Pelayan pangkalan data khusus dengan 16GB RAM sepatutnya memberikan 12GB kepada kumpulan penimbal dengan selesa. Pada kotak kongsi, jadilah lebih konservatif. Cara klasik untuk memeriksa sama ada kolam itu cukup besar:

```sql
TUNJUKKAN STATUS GLOBAL SEPERTI 'Innodbbufferpoolreadrequests';
TUNJUKKAN STATUS GLOBAL SEPERTI 'Innodbbufferpool_reads';
```

`baca` ialah bacaan cakera; `read_requests` adalah jumlah. Jika nisbah hit anda (`(permintaan - dibaca) / permintaan`) adalah di bawah 99%, kumpulan penimbal adalah terlalu kecil — atau pertanyaan anda mengimbas terlalu banyak data (betulkan pertanyaan dahulu; melemparkan RAM pada pertanyaan imbasan jadual penuh hanya menjadikannya mengimbas lebih pantas).

Dua lagi tombol InnoDB yang patut diketahui:

• `innodblogfile_size` — log buat semula. Terlalu kecil menyebabkan siram yang kerap dan amaran "usia pusat pemeriksaan". 256MB–1GB bagi setiap fail log adalah munasabah untuk beban kerja berat tulis.
• `innodbflushlogattrx_commit` — pertukaran ketahanan/prestasi klasik. `1` (lalai) menyegerak pada setiap komit — selamat tetapi perlahan. `2` mengalir ke cache OS sahaja — lebih pantas, kehilangan paling banyak 1 saat transaksi apabila kehilangan kuasa. Untuk beban kerja bukan kewangan, `2` ialah pilihan yang sah dan digunakan secara meluas.

Langkah 5: Kebersihan Skema — Betulkan Jadual, Bukan Sekadar Pertanyaan

Kadangkala tiada indeks di dunia yang boleh menyimpan skema yang direka dengan buruk. Beberapa corak yang perlu dicari:

• PILIH \* — pada jadual lebar ini menyeret megabait lajur yang tidak diperlukan merentasi rangkaian dan ke dalam jadual temp. Pilih hanya apa yang anda perlukan.
• Jenis data yang salah — mencari `VARCHAR(255)` untuk nombor, atau menyimpan tarikh sebagai rentetan, menghalang perbandingan yang cekap dan indeks mengembang. Gunakan `INT`, `DATETIME`, `ENUM` di mana ia berada.
• TEXT/BLOB abuse — Lajur `TEXT` tidak boleh diindeks sepenuhnya tanpa indeks awalan dan InnoDB menyimpan nilai besar di luar halaman. Jika anda hanya memerlukan 200 aksara, `VARCHAR(200)` mengalahkan `TEXT`.
• Tiada `NOT NULL` — lajur boleh batal menjadikan perancangan pengindeksan dan pertanyaan lebih sukar daripada yang anda fikirkan. Jika lajur tidak pernah batal, katakan demikian.
• Penormalan berlebihan pada laluan panas — kadangkala lajur ringkasan nyahnormal atau jadual agregat yang diprakira menyimpan sambungan yang berjalan 50,000 kali sehari. Peraturan adalah untuk pemula; ukur dahulu, kemudian bengkokkannya dengan sengaja.

Langkah 6: Uji, Pantau dan Buktikan Perubahan Anda

Jangan sekali-kali menggunakan "petua penalaan daripada blog" dan panggil ia sehari. Ukur sebelum, pakai, ukur selepas.

Ujian muat dengan penanda aras: `sysbench` ialah alat standard.

```bash
sysbench oltpreadwrite --table-size=1000000 --mysql-db=bench \
--benang=16 --masa=60 sediakan
sysbench oltpreadwrite --table-size=1000000 --mysql-db=bench \
--threads=16 --time=60 run
```

Jalankannya sebelum perubahan anda, rekodkan persentil transaksi-sesaat dan kependaman, kemudian jalankannya semula selepas itu. Jika TPS meningkat dan kependaman p95 menurun, anda telah memperbaik sesuatu yang sebenar.

Gunakan penasihat terbina dalam: `mysqltuner.pl` ialah skrip Perl tunggal yang bersambung ke pelayan anda dan mencetak senarai pengesyoran yang diutamakan. Ia adalah pendapat, tetapi sebagai senarai semak permulaan ia menangkap klasik - saiz kolam penimbal, nisbah hit cache dan had sambungan.

Tonton `performanceschema`: Instrumen terbina dalam MySQL boleh memberitahu anda indeks yang digunakan (`sys.schemaunusedindexes`) dan pertanyaan yang menggunakan paling banyak masa (`sys.statementanalysis`). Jika MySQL anda mempunyai skema `sys`, pandangan ini menukar arkeologi prestasi menjadi pernyataan SELECT.

Langkah 7: Senarai Semak Kemenangan Pantas

Akhir sekali, berikut ialah senarai "buat ini sebelum makan tengah hari" yang membetulkan bilangan pangkalan data pengeluaran yang mengejutkan:

Seperkara lagi: cache pertanyaan MySQL hilang dalam MySQL 8.0 dan MariaDB 10.1.4+ (tidak digunakan lagi). Jika tutorial lama memberitahu anda untuk menghidupkan `querycachesize`, abaikan — pelayan moden mendapat manfaat daripada kumpulan penimbal dan lapisan cache yang lebih baik (Redis, Varnish atau cache peringkat aplikasi) sebaliknya.

Kesimpulan

Penalaan pangkalan data bukanlah sihir — ia adalah penyiasatan yang teratur. Dayakan log pertanyaan perlahan, cari pesalah, baca rancangan pelaksanaan mereka, tambah indeks yang betul, saiz kumpulan penimbal secara jujur ​​dan buktikan setiap perubahan dengan penanda aras. Anda akan terkejut betapa kerap pelayan MySQL yang "sekarat" sebenarnya kehilangan satu indeks dan satu nilai `my.cnf` yang salah daripada menjadi sihat sepenuhnya.

Pengendali pintar membuat keputusan termaklum. Pangkalan data anda akan berterima kasih kepada anda — dan begitu juga pembangun yang berhenti menghantar ping kepada anda tentang halaman yang perlahan.

Infografik: Penalaan Prestasi MySQL