Menguruskan Pertanyaan Jangka Panjang dalam MySQL

Pertanyaan berpanjangan boleh menjadi duri yang serius dalam prestasi pangkalan data MySQL anda, menyebabkan segala-galanya daripada masa tindak balas yang lembap kepada kesesakan sepenuhnya yang menjejaskan setiap pengguna. Menjawab pertanyaan yang menjengkelkan ini—mengetahui apa itu, sebab ia berlaku dan cara mengurusnya — adalah kunci untuk memastikan pangkalan data anda berjalan lancar.

Sama ada mengesan mereka lebih awal, menghentikan mereka dalam jejak mereka atau menyediakan cara untuk mengendalikannya secara automatik, panduan ini telah membantu anda.

Apakah Pertanyaan Jangka Panjang?

Pertanyaan jangka panjang dalam MySQL ialah pertanyaan yang mengambil masa yang luar biasa lama untuk dilaksanakan.
Tempoh khusus yang mengklasifikasikan pertanyaan sebagai "berjalan lama" boleh berbeza-beza, bergantung pada standard prestasi aplikasi anda. Secara amnya, jika pertanyaan berjalan lebih lama daripada biasa dan mula memperlahankan pangkalan data anda, ia dianggap berjalan lama.

Punca Punca Pertanyaan Lama

Punca pertanyaan yang berpanjangan boleh menjadi pelbagai:

• Kurang Pengindeksan yang Betul – Tanpa pengindeksan yang sesuai, MySQL mesti mengimbas keseluruhan jadual untuk mendapatkan semula data yang diperlukan. Proses ini sangat tidak cekap, terutamanya untuk meja besar, kerana ia memakan masa dan sumber yang banyak.

• Situasi Beban Berat – Apabila pelayan mengendalikan jumlah pertanyaan yang tinggi atau memproses beberapa pertanyaan yang kompleks secara serentak, sumber yang tersedia (seperti CPU dan memori) menjadi nipis. Persaingan untuk mendapatkan sumber ini boleh menangguhkan pelaksanaan pertanyaan, yang membawa kepada masa berjalan yang lebih lama, terutamanya semasa tempoh penggunaan puncak.

• Lock Contention – Ini berlaku apabila berbilang transaksi memerlukan akses kepada data yang sama secara serentak tetapi disekat kerana operasi lain memegang kunci yang diperlukan. Contohnya, jika satu transaksi mengemas kini baris, transaksi lain yang ingin membaca atau mengemas kini baris yang sama perlu menunggu sehingga transaksi pertama selesai dan melepaskan kunci.

• Penormalan Tidak Wajar – Walaupun penormalan membantu mengelakkan lebihan data dan meningkatkan integriti data, pangkalan data yang terlalu dinormalisasi boleh membawa kepada pertanyaan rumit yang melibatkan berbilang gabungan. Ini boleh merendahkan prestasi. Sebaliknya, kurang normalisasi boleh menyebabkan pertindihan data yang berlebihan, menghasilkan jadual yang lebih besar dan pertanyaan yang lebih perlahan.

• Gabungan Besar – Pertanyaan yang melibatkan penggabungan jadual besar, terutamanya tanpa indeks yang betul, boleh menjadi perlahan. Pangkalan data mesti sepadan dengan baris merentas jadual berdasarkan syarat gabungan, satu proses yang boleh menjadi sangat intensif sumber dan perlahan tanpa pengindeksan yang cekap.

Mengenalpasti Pertanyaan Jangka Panjang

Untuk mengurus pertanyaan jangka panjang dengan berkesan, anda perlu mengenal pastinya terlebih dahulu. Berikut adalah beberapa kaedah:

1. Menggunakan SHOW PROCESSLIST

SENARAI PROSES TAYANGAN; arahan ialah cara cepat untuk mendapatkan gambaran semua pertanyaan aktif yang dijalankan pada pelayan anda. Perintah ini memaparkan setiap pertanyaan bersama-sama dengan beberapa maklumat penting, termasuk berapa lama setiap pertanyaan telah dijalankan. Mereka yang mempunyai nilai "Masa" yang tinggi berkemungkinan merupakan pertanyaan jangka panjang anda. Begini cara anda boleh menggunakan arahan ini:

TUNJUKKAN SENARAI PROSES PENUH;

Arahan ini akan menyenaraikan semua proses semasa, menunjukkan siapa yang memulakannya, jenis arahan yang mereka jalankan dan, yang penting, berapa lama mereka telah melakukannya. Jika anda melihat sebarang pertanyaan yang telah dijalankan untuk masa yang luar biasa, itu adalah pertanyaan anda yang sudah lama berjalan. Anda kemudiannya boleh membuat keputusan sama ada untuk menggali lebih mendalam untuk mengoptimumkannya atau hanya membunuhnya jika mereka menyeret prestasi sistem anda ke bawah.

2. Log Pertanyaan Perlahan

Menyediakan log pertanyaan perlahan ialah satu lagi strategi hebat untuk menangkap pertanyaan bermasalah tersebut. Ciri MySQL yang berguna ini merekodkan sebarang pertanyaan yang mengambil masa lebih lama untuk dilaksanakan daripada ambang tertentu. Ia bukan hanya tentang menangkap pertanyaan yang sudah lama dijalankan – ia juga boleh membantu anda mengenal pasti pertanyaan yang tidak menggunakan indeks dengan cekap.

Untuk mendapatkan log masuk dan dijalankan pertanyaan perlahan, anda perlu mengubah suai beberapa tetapan dalam fail konfigurasi MySQL anda (sama ada my.cnf atau my.ini):

• Slow_query_log – Tetapkan ini kepada 1 untuk mendayakan log.
• Slow_query_log_file – Tentukan laluan fail yang anda mahu simpan log.
• Long_query_time – Tetapkan masa pelaksanaan minimum (dalam saat) yang melayakkan pertanyaan untuk dilog. Sebagai contoh, menetapkannya kepada '2' akan mencatat sebarang pertanyaan yang mengambil masa lebih daripada dua saat untuk dilaksanakan.

3. Skema Prestasi

Skema Prestasi MySQL tidak ternilai untuk penyiasatan yang lebih terperinci. Alat ini direka bentuk untuk memantau acara pelayan dan menjejaki metrik prestasi, memberikan anda paparan yang lebih jelas tentang pelaksanaan pertanyaan dan prestasi sistem keseluruhan.

Pastikan ia didayakan dalam konfigurasi MySQL anda dengan menambah baris berikut:

[mysqld]
skema_prestasi = HIDUP

Sebaik sahaja ia diaktifkan, anda boleh meneroka pelbagai jadual Skema Prestasi untuk menganalisis prestasi pertanyaan anda. Sebagai contoh, jika anda ingin menentukan pertanyaan yang sudah lama dijalankan, anda mungkin mahu melihat ke dalam jadual events_statements_history_long. Begini cara anda boleh membuat pertanyaan:

PILIH EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 SEBAGAI 'Tempoh (saat)'
DARI performance_schema.events_statements_history_long
DI MANA PEMASA_TUNGGU > 10000000000;

Pertanyaan ini membantu anda mencari sebarang pertanyaan yang telah dijalankan selama lebih daripada 10 saat. Ia memberikan anda butiran seperti teks SQL dan berapa lama setiap pertanyaan telah dijalankan.

Membunuh Pertanyaan Lama Secara Manual

Apabila anda telah mengenal pasti pertanyaan yang mengambil masa terlalu lama dan membebankan sumber sistem anda, anda mempunyai pilihan untuk menamatkannya secara manual. Ini dilakukan menggunakan arahan KILL diikuti dengan ID proses khusus pertanyaan.

Anda boleh mencari ID proses dengan menjalankan perintah SHOW PROCESSLIST, yang memaparkan semua proses yang sedang berjalan dan ID masing-masing. Lihat senarai untuk sebarang pertanyaan yang menunjukkan nilai "Masa" yang tinggi, yang menunjukkan berapa lama ia telah dijalankan.

Setelah anda mengenal pasti pertanyaan bermasalah dan mencatat ID prosesnya, anda boleh menamatkannya dengan menggunakan arahan KILL:

BUNUH [ID proses];

Ganti [ID proses] dengan nombor sebenar daripada output SHOW PROCESSLIST.

Berhati-hati dengan pendekatan ini. Menghentikan pertanyaan secara tiba-tiba kadangkala boleh menyebabkan isu, seperti membiarkan data anda dalam keadaan tidak konsisten jika pertanyaan itu berada di tengah-tengah penulisan atau mengemas kini maklumat.

Bagaimana untuk mengautomasikan Pembunuhan Pertanyaan Lama

Menyediakan automasi untuk mengendalikan pertanyaan jangka panjang boleh menjadi penyelamat sebenar, menghalang pertanyaan yang lembap atau tidak dioptimumkan itu daripada memendam sumber pangkalan data anda dan memperlahankan, malah mengunci keseluruhan sistem. Tetapi berhati-hati—menggunakan alat ini tanpa pemeriksaan yang betul sebenarnya boleh menyembunyikan masalah prestasi yang lebih mendalam yang memerlukan perhatian anda.

Sentiasa pastikan anda mempunyai pengelogan dan pemantauan yang komprehensif untuk menganalisis kesan pertanyaan terbunuh pada aplikasi anda dan pertimbangkan untuk memperbaik pertanyaan tersebut dan bukannya membunuhnya secara automatik. Fikirkan penamatan automatik sebagai sebahagian daripada strategi yang lebih besar untuk mengoptimumkan prestasi, bukan sebagai penyelesaian yang baik.

1. Dayakan Penjadual Acara

Pertama sekali, anda perlu mendayakan Penjadual Acara MySQL, yang dilumpuhkan secara lalai. Penjadual Acara membolehkan anda membuat dan menjadualkan tugas yang anda mahu pelayan laksanakan secara automatik pada masa yang telah ditetapkan. Jalankan arahan berikut:

SET GLOBAL event_scheduler = HIDUP;

2. Buat Acara untuk Membunuh Pertanyaan Yang Berlarutan

Dengan penjadual didayakan, langkah seterusnya ialah mentakrifkan acara sebenar yang akan memantau dan mematikan pertanyaan yang telah lama dijalankan. Acara akan dijalankan setiap minit untuk menyemak pertanyaan berjalan lebih lama daripada ambang yang ditentukan (katakan 60 saat). Setelah dikenal pasti, ia akan mematikan pertanyaan ini secara automatik. Berikut ialah pecahan kod SQL untuk menyediakan acara ini:

`BUAT ACARA kill_long_running_queries
PADA JADUAL SETIAP 1 MINIT -- Menentukan kekerapan acara dijalankan
LAKUKAN
BERMULA
ISYTIHKAN selesai INT lalai SALAH;
DECLARE proc_id INT; -- Pembolehubah untuk menyimpan ID proses setiap pertanyaan
ISYTIHKAN KURSOR cur1 UNTUK ID PILIH DARIPADA information_schema.processlist
WHERE Perintah = 'Query' AND Time > 60; -- Tukar '60' kepada ambang anda dalam beberapa saat
ISYTIHKAN TERUSKAN PENGENDALI UNTUK TIDAK DITEMUI SET selesai = BENAR;

BUKA cur1;

gelung_baca: LOOP
AMBIL cur1 KE dalam proc_id;
JIKA selesai MAKA
TINGGALKAN gelung_baca;
TAMAT JIKA;
BUNUH proc_id; -- Membunuh proses yang dikenal pasti oleh proc_id
GULUNG TAMAT;

TUTUP cur1;
TAMAT;`

3. Sediakan Masa Pelaksanaan Pertanyaan Maks dalam MySQL

Mengawal masa pelaksanaan maksimum untuk pertanyaan membantu menghalang pangkalan data daripada terikat oleh pertanyaan yang terlalu lama dijalankan. Ini dilakukan menggunakan pembolehubah sistem max_execution_time dalam MySQL 5.7.8 dan versi yang lebih baru dengan menetapkan had masa pelaksanaan seluruh sistem untuk semua pertanyaan PILIH baca sahaja:

SET GLOBAL max_execution_time = 2000;

Ini menetapkan had kepada 2000 milisaat (2 saat)

Ingat, tetapan ini tidak digunakan pada prosedur tersimpan, fungsi atau pencetus dan ditetapkan semula kepada lalai apabila pelayan dimulakan semula melainkan ditambahkan pada fail konfigurasi MySQL anda:

[mysqld]
max_execution_time = 2000

4. Sediakan Masa Penyata Maks dalam MariaDB

MariaDB, sementara bercabang daripada MySQL, menawarkan pendekatan yang serupa tetapi berbeza untuk menguruskan masa pelaksanaan pertanyaan. Bermula dari MariaDB 10.1.1, anda boleh menggunakan pembolehubah sistem max_statement_time untuk tujuan ini:

TETAPKAN masa_penyata maks GLOBAL = 2;

Ini mengehadkan masa pelaksanaan kepada 2 saat untuk semua pertanyaan.

Untuk konfigurasi berterusan melalui permulaan semula pelayan, tambahkan baris ini pada fail konfigurasi MariaDB anda:

[mysqld]
max_statement_time = 2

Kenal pasti dan Selesaikan Pertanyaan Jangka Panjang dengan Analitis Pertanyaan daripada Releem

Alat analitis pertanyaan Releem merevolusikan cara anda memantau dan mengoptimumkan prestasi pangkalan data anda. Ia secara automatik mengumpulkan maklumat terperinci tentang 100 pertanyaan teratas, menyediakan metrik utama seperti purata masa pelaksanaan dan kesan keseluruhan setiap pertanyaan terhadap kecekapan operasi pangkalan data anda.

Dengan Releem, tidak perlu mengorek output PROCESSLIST secara manual atau melihat log pertanyaan perlahan untuk mengenal pasti pertanyaan berprestasi rendah. Alat ini mempunyai papan pemuka intuitif yang membolehkan anda mengisih dan mengesan pertanyaan yang ketinggalan atau memakan masa yang berlebihan dengan mudah. Cerapan segera ini membantu anda mengenal pasti dan menyelesaikan kesesakan dalam masa yang singkat.

Atas ialah kandungan terperinci Menguruskan Pertanyaan Jangka Panjang dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!