Bagaimana cara menyetel kueri mysql?

Question

Pengoptimal kueri MySQL bawaan melakukan pekerjaan yang sangat baik untuk mengoptimalkan eksekusi kueri Anda

Table of Contents Show

Pengantar cepat untuk pemrosesan kueri
Identifikasi dan analisis kueri lambat di MySQL
INFORMATION_SCHEMA. tabel PROFILING
Slow_query_log
Pernyataan mysql> SELECT * FROM customer;_9
Pernyataan JELASKAN ANALISIS
Memeriksa variabel sesi last_query_cost
Optimalkan kueri lambat
Hanya mengambil data yang Anda butuhkan
Hindari ekspresi LIKE dengan wildcard terkemuka
UNION vs. UNI SEMUA
Optimalkan kueri GABUNG
GROUP BY dan ORDER BY
Optimalkan COUNT() kueri
Bagaimana cara membantu pengoptimal MySQL
Langkah selanjutnya. optimasi skema dan pengindeksan
Bacaan lebih lanjut
Pos terkait
Bereaksi-tooltip. Panduan Cara
Cara Menangani State Management di Vue Menggunakan Pinia
Cara Memanipulasi Data SQL Menggunakan SQLAlchemy dan Pandas
Bagaimana cara meningkatkan kinerja kueri MySQL?
Bagaimana cara menyetel kueri SQL?
Mengapa kueri MySQL saya terlalu lambat?
Bagaimana Anda mengoptimalkan kueri pemilihan?

Namun, kueri yang ditulis dengan buruk dapat mencegah pengoptimal bekerja dengan baik. Bahkan jika Anda melakukan teknik pengoptimalan lainnya seperti desain skema atau pengindeksan yang baik, jika kueri Anda salah, mereka tetap akan memengaruhi kinerja basis data Anda. Panduan ini bertujuan untuk membantu Anda meningkatkan kecepatan database MySQL dengan mengoptimalkan kueri Anda

Dalam panduan ini, Anda akan

Pelajari cara mengidentifikasi kueri yang lambat di MySQL menggunakan beberapa teknik pembuatan profil kueri
Pelajari cara mengoptimalkan kueri untuk waktu respons yang lebih cepat
Pahami bagaimana MySQL mengoptimalkan dan mengeksekusi kueri Anda
Pelajari cara mengontrol dan mengubah rencana eksekusi kueri default dari kueri Anda

Pelajari bagaimana kata kunci

mysql> SELECT * FROM customer;

9 dan

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

0 memberikan informasi tentang bagaimana database MySQL mengeksekusi kueri

Pengantar cepat untuk pemrosesan kueri

MySQL memproses kueri Anda dalam serangkaian langkah. Memahami cara kerja langkah-langkah ini dapat membuka wawasan yang kuat tentang cara mengoptimalkan kueri Anda. Langkah-langkah ini rumit secara internal tetapi dapat diringkas di bawah ini

Siklus hidup kueri MySQL oleh Elvis Duru

Klien MySQL mengirimkan kueri Anda ke server MySQL menggunakan Protokol Klien/Server MySQL
Kueri diuraikan, diproses sebelumnya, dan akhirnya dioptimalkan menjadi rencana eksekusi kueri oleh pengoptimal kueri MySQL. Pengoptimal mungkin meminta mesin Penyimpanan untuk statistik tentang tabel yang direferensikan dalam kueri Anda sebelum dieksekusi
Mesin Eksekusi Kueri menjalankan rencana dengan melakukan panggilan ke mesin Penyimpanan melalui antarmuka penangan khusus
Server MySQL mengirimkan hasilnya ke klien MySQL

Di bagian berikut, kita akan melihat bagaimana kita bisa mendapatkan informasi tentang rencana eksekusi default dan mencari cara untuk mengoptimalkan kueri kita untuk memengaruhi rencana tersebut

Identifikasi dan analisis kueri lambat di MySQL

Kueri yang lambat dapat mengganggu, tetapi kabar baiknya adalah kami dapat mengidentifikasi dan memperbaikinya

Jumlah waktu yang diperlukan MySQL untuk mengeksekusi kueri Anda dikenal sebagai waktu responsnya. Ini adalah metrik paling penting yang digunakan untuk mengukur kecepatan kueri Anda. Dengan kata lain, kinerja permintaan atau transaksi individual berbanding lurus dengan waktu responsnya

Ada beberapa alasan mengapa kueri mungkin lambat. Alasan ini dapat berkisar dari konfigurasi perangkat keras Anda saat ini, hingga izin yang mungkin telah Anda tetapkan, penggunaan indeks yang tidak tepat, desain skema yang buruk, atau bahkan maksud dari kueri Anda. Panduan ini berfokus pada poin terakhir – kueri

Setiap kali Anda mengirim kueri ke server MySQL, Anda mengirimkan serangkaian instruksi yang ingin Anda lakukan

Beberapa instruksi sederhana — e. g. , kueri yang mencari menggunakan parameter tunggal — dan beberapa instruksi bisa rumit — e. g. , kueri yang melibatkan gabungan kompleks dan subkueri. Dalam bentuk apa pun server MySQL menerima kueri Anda, mereka akan menghabiskan waktu

Untungnya, beberapa alat bawaan memungkinkan Anda melihat bagaimana kinerja kueri Anda. Proses mencari tahu bagaimana MySQL menghabiskan waktu untuk memproses dan mengeksekusi kueri Anda dikenal sebagai pembuatan profil kueri. Bagian berikut mengeksplorasi beberapa alat pemrofilan kueri dan menjelaskan bagaimana kami dapat menggunakannya dalam menganalisis kinerja kueri Anda. Jika Anda ingin mengikuti, Anda dapat mengunduh dan menginstal database sampel sakila yang kami gunakan di seluruh artikel ini

INFORMATION_SCHEMA. tabel PROFILING

Tabel

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

1 menyimpan informasi pembuatan profil tentang kueri yang Anda jalankan dalam sesi interaktif saat ini. Ini dinonaktifkan secara default, tetapi Anda dapat mengaktifkan profil kueri untuk sesi Anda saat ini dengan mengatur variabel sesi

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

2 seperti yang ditunjukkan di bawah ini. Perhatikan bahwa informasi pembuatan profil akan hilang saat sesi berakhir

mysql> SET SESSION profiling = 1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

_

Setelah itu, Anda dapat memilih database yang ingin Anda gunakan

mysql> USE sakila;

Database changed
Code language: PHP (php)

Selanjutnya, Anda menjalankan kueri Anda (Pastikan Anda tidak menggunakan pernyataan

mysql> SELECT * FROM customer;

9)

mysql> SELECT * FROM customer;

Jalankan kueri

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

_4

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

_

Kueri

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

4 mengambil semua daftar kueri di sesi saat ini. Ini memiliki tiga kolom

Query_ID – pengidentifikasi numerik unik untuk kueri
Durasi adalah waktu yang diperlukan untuk menjalankan kueri
Kolom Query menampilkan query yang dijalankan oleh server MySQL

Untuk melihat informasi lebih lanjut tentang kueri tertentu, kita dapat menjalankan perintah berikut

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=4;

+----------+-----+--------------------------------+----------+----------+------------+-->| QUERY_ID | SEQ | STATE                          | DURATION | CPU_USER | CPU_SYSTEM | C>+----------+-----+--------------------------------+----------+----------+------------+-->|        4 |   2 | starting                       | 0.000107 | 0.000046 |   0.000019 |  >|        4 |   3 | Executing hook on transaction  | 0.000005 | 0.000003 |   0.000001 |  >|        4 |   4 | starting                       | 0.000007 | 0.000005 |   0.000002 |  >|        4 |   5 | checking permissions           | 0.000007 | 0.000004 |   0.000002 |  >|        4 |   6 | Opening tables                 | 0.000337 | 0.000238 |   0.000100 |  >|        4 |   7 | init                           | 0.000007 | 0.000005 |   0.000001 |  >|        4 |   8 | System lock                    | 0.000010 | 0.000006 |   0.000003 |  >|        4 |   9 | optimizing                     | 0.000004 | 0.000003 |   0.000001 |  >|        4 |  10 | statistics                     | 0.000011 | 0.000008 |   0.000004 |  >|        4 |  11 | preparing                      | 0.000016 | 0.000011 |   0.000004 |  >|        4 |  12 | executing                      | 0.000802 | 0.000573 |   0.000239 |  >|        4 |  13 | end                            | 0.000017 | 0.000005 |   0.000002 |  >|        4 |  14 | query end                      | 0.000003 | 0.000002 |   0.000001 |  >|        4 |  15 | waiting for handler commit     | 0.000008 | 0.000006 |   0.000002 |  >|        4 |  16 | closing tables                 | 0.000007 | 0.000005 |   0.000002 |  >|        4 |  17 | freeing items                  | 0.000029 | 0.000020 |   0.000009 |  >|        4 |  18 | cleaning up                    | 0.000019 | 0.000013 |   0.000005 |  >+----------+-----+--------------------------------+----------+----------+------------+-->(END)

Hasilnya menunjukkan beberapa informasi menarik tentang bagaimana MySQL memproses kueri Anda dan waktu yang dibutuhkan setiap langkah. Kami membahas beberapa langkah tersebut sebelumnya saat menjelaskan siklus hidup kueri MySQL. Perhatikan baik-baik SEQ (Urutan) 9 – 12

Slow_query_log

Cara lain kami dapat mengidentifikasi kueri yang lambat adalah dengan memeriksa log kueri yang lambat. Fitur bawaan ini memungkinkan Anda mencatat kueri yang melebihi batas waktu yang Anda tetapkan menggunakan variabel sistem

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

6. Batas waktu default adalah 10 detik, mis. e. , MySQL akan mencatat setiap kueri yang berjalan lebih dari 10 detik. Log kueri yang lambat ini, seperti INFORMATION_SCHEMA. PROFILING, tidak diaktifkan secara default. Untuk menggunakannya, Anda harus mengaktifkannya terlebih dahulu dengan menyetel variabel global

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

7 ke

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

8

mysql> SET GLOBAL slow_query_log = 'ON';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

Untuk mengonfirmasi, Anda selalu dapat melihat nilai variabel global dengan cara ini

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+



Code language: PHP (php)

Anda dapat mengubah batas waktu dalam hitungan detik dengan mengetikkan perintah berikut

mysql> SET GLOBAL long_query_time = 60;

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

Anda juga dapat mengubah lokasi default untuk file log kueri yang lambat, biasanya ditemukan di var/lib/mysql/hostname-slow. log, ke tujuan mana pun pilihan Anda

mysql> SET GLOBAL slow_query_log_file = '/somepath/filename.log';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

Setelah Anda menjalankan kueri apa pun yang melebihi batas waktu yang Anda konfigurasikan, kueri tersebut akan dicatat dalam log kueri lambat oleh server MySQL. Anda selalu dapat memeriksa file untuk melihat kueri yang lambat tersebut.

Untuk mendemonstrasikan ini, saya telah menyetel file log permintaan lambat saya ke jalur “/tmp/slow_queries. catatan". Saya menggunakan long_query_time default, yaitu 10 detik. Kita bisa memaksakan kueri lambat yang berjalan selama 15 detik menggunakan fungsi SLEEP() seperti yang ditunjukkan di bawah ini

mysql> SELECT SLEEP(15);

+-----------+
| SLEEP(15) |
+-----------+
|         0 |
+-----------+
1 row in set (15.00 sec)
Code language: JavaScript (javascript)

Mari kita periksa log kueri yang lambat dan lihat apakah server MySQL mencatat kueri baru kita

mysql> USE sakila;

Database changed
Code language: PHP (php)

0

Untuk file log yang lebih besar, MySQL menyediakan alat

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

_9 untuk mengurai dan meringkas file log kueri yang lambat

mysql> USE sakila;

Database changed
Code language: PHP (php)

1

Jika sudah selesai memeriksa, Anda dapat menonaktifkan fitur dengan menyetel variabel global

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

7 ke

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=4;

+----------+-----+--------------------------------+----------+----------+------------+-->| QUERY_ID | SEQ | STATE                          | DURATION | CPU_USER | CPU_SYSTEM | C>+----------+-----+--------------------------------+----------+----------+------------+-->|        4 |   2 | starting                       | 0.000107 | 0.000046 |   0.000019 |  >|        4 |   3 | Executing hook on transaction  | 0.000005 | 0.000003 |   0.000001 |  >|        4 |   4 | starting                       | 0.000007 | 0.000005 |   0.000002 |  >|        4 |   5 | checking permissions           | 0.000007 | 0.000004 |   0.000002 |  >|        4 |   6 | Opening tables                 | 0.000337 | 0.000238 |   0.000100 |  >|        4 |   7 | init                           | 0.000007 | 0.000005 |   0.000001 |  >|        4 |   8 | System lock                    | 0.000010 | 0.000006 |   0.000003 |  >|        4 |   9 | optimizing                     | 0.000004 | 0.000003 |   0.000001 |  >|        4 |  10 | statistics                     | 0.000011 | 0.000008 |   0.000004 |  >|        4 |  11 | preparing                      | 0.000016 | 0.000011 |   0.000004 |  >|        4 |  12 | executing                      | 0.000802 | 0.000573 |   0.000239 |  >|        4 |  13 | end                            | 0.000017 | 0.000005 |   0.000002 |  >|        4 |  14 | query end                      | 0.000003 | 0.000002 |   0.000001 |  >|        4 |  15 | waiting for handler commit     | 0.000008 | 0.000006 |   0.000002 |  >|        4 |  16 | closing tables                 | 0.000007 | 0.000005 |   0.000002 |  >|        4 |  17 | freeing items                  | 0.000029 | 0.000020 |   0.000009 |  >|        4 |  18 | cleaning up                    | 0.000019 | 0.000013 |   0.000005 |  >+----------+-----+--------------------------------+----------+----------+------------+-->(END)

1

mysql> USE sakila;

Database changed
Code language: PHP (php)

2

Pernyataan mysql> SELECT * FROM customer;_9

Alat lain yang disediakan MySQL untuk analisis kueri adalah pernyataan

mysql> SELECT * FROM customer;

9. Pernyataan ini menunjukkan kepada kita bagaimana server MySQL akan menjalankan sebuah rencana. Ini cukup mudah digunakan. Kapan pun Anda ingin melihat bagaimana MySQL akan mengeksekusi kueri Anda sebelum menjalankannya, tambahkan saja pernyataan

mysql> SELECT * FROM customer;

9 ke dalamnya

mysql> USE sakila;

Database changed
Code language: PHP (php)

_3

Mengakhiri pernyataan SQL dengan "\G" alih-alih ";"

Contoh di atas memberi tahu kita bahwa server hanya perlu memindai 599 baris untuk mendapatkan hasilnya. Kasus penggunaan umum untuk

mysql> SELECT * FROM customer;

_9 adalah menganalisis bagaimana MySQL akan mengeksekusi gabungan. Mari jalankan kueri yang menggabungkan beberapa tabel di database sakila kita untuk didemonstrasikan

mysql> USE sakila;

Database changed
Code language: PHP (php)

_4

Permintaan kami sederhana. Kami bergabung dengan tabel

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=4;

+----------+-----+--------------------------------+----------+----------+------------+-->| QUERY_ID | SEQ | STATE                          | DURATION | CPU_USER | CPU_SYSTEM | C>+----------+-----+--------------------------------+----------+----------+------------+-->|        4 |   2 | starting                       | 0.000107 | 0.000046 |   0.000019 |  >|        4 |   3 | Executing hook on transaction  | 0.000005 | 0.000003 |   0.000001 |  >|        4 |   4 | starting                       | 0.000007 | 0.000005 |   0.000002 |  >|        4 |   5 | checking permissions           | 0.000007 | 0.000004 |   0.000002 |  >|        4 |   6 | Opening tables                 | 0.000337 | 0.000238 |   0.000100 |  >|        4 |   7 | init                           | 0.000007 | 0.000005 |   0.000001 |  >|        4 |   8 | System lock                    | 0.000010 | 0.000006 |   0.000003 |  >|        4 |   9 | optimizing                     | 0.000004 | 0.000003 |   0.000001 |  >|        4 |  10 | statistics                     | 0.000011 | 0.000008 |   0.000004 |  >|        4 |  11 | preparing                      | 0.000016 | 0.000011 |   0.000004 |  >|        4 |  12 | executing                      | 0.000802 | 0.000573 |   0.000239 |  >|        4 |  13 | end                            | 0.000017 | 0.000005 |   0.000002 |  >|        4 |  14 | query end                      | 0.000003 | 0.000002 |   0.000001 |  >|        4 |  15 | waiting for handler commit     | 0.000008 | 0.000006 |   0.000002 |  >|        4 |  16 | closing tables                 | 0.000007 | 0.000005 |   0.000002 |  >|        4 |  17 | freeing items                  | 0.000029 | 0.000020 |   0.000009 |  >|        4 |  18 | cleaning up                    | 0.000019 | 0.000013 |   0.000005 |  >+----------+-----+--------------------------------+----------+----------+------------+-->(END)

_6 kami dengan tabel

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=4;

+----------+-----+--------------------------------+----------+----------+------------+-->| QUERY_ID | SEQ | STATE                          | DURATION | CPU_USER | CPU_SYSTEM | C>+----------+-----+--------------------------------+----------+----------+------------+-->|        4 |   2 | starting                       | 0.000107 | 0.000046 |   0.000019 |  >|        4 |   3 | Executing hook on transaction  | 0.000005 | 0.000003 |   0.000001 |  >|        4 |   4 | starting                       | 0.000007 | 0.000005 |   0.000002 |  >|        4 |   5 | checking permissions           | 0.000007 | 0.000004 |   0.000002 |  >|        4 |   6 | Opening tables                 | 0.000337 | 0.000238 |   0.000100 |  >|        4 |   7 | init                           | 0.000007 | 0.000005 |   0.000001 |  >|        4 |   8 | System lock                    | 0.000010 | 0.000006 |   0.000003 |  >|        4 |   9 | optimizing                     | 0.000004 | 0.000003 |   0.000001 |  >|        4 |  10 | statistics                     | 0.000011 | 0.000008 |   0.000004 |  >|        4 |  11 | preparing                      | 0.000016 | 0.000011 |   0.000004 |  >|        4 |  12 | executing                      | 0.000802 | 0.000573 |   0.000239 |  >|        4 |  13 | end                            | 0.000017 | 0.000005 |   0.000002 |  >|        4 |  14 | query end                      | 0.000003 | 0.000002 |   0.000001 |  >|        4 |  15 | waiting for handler commit     | 0.000008 | 0.000006 |   0.000002 |  >|        4 |  16 | closing tables                 | 0.000007 | 0.000005 |   0.000002 |  >|        4 |  17 | freeing items                  | 0.000029 | 0.000020 |   0.000009 |  >|        4 |  18 | cleaning up                    | 0.000019 | 0.000013 |   0.000005 |  >+----------+-----+--------------------------------+----------+----------+------------+-->(END)

7 menggunakan kunci

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=4;

+----------+-----+--------------------------------+----------+----------+------------+-->| QUERY_ID | SEQ | STATE                          | DURATION | CPU_USER | CPU_SYSTEM | C>+----------+-----+--------------------------------+----------+----------+------------+-->|        4 |   2 | starting                       | 0.000107 | 0.000046 |   0.000019 |  >|        4 |   3 | Executing hook on transaction  | 0.000005 | 0.000003 |   0.000001 |  >|        4 |   4 | starting                       | 0.000007 | 0.000005 |   0.000002 |  >|        4 |   5 | checking permissions           | 0.000007 | 0.000004 |   0.000002 |  >|        4 |   6 | Opening tables                 | 0.000337 | 0.000238 |   0.000100 |  >|        4 |   7 | init                           | 0.000007 | 0.000005 |   0.000001 |  >|        4 |   8 | System lock                    | 0.000010 | 0.000006 |   0.000003 |  >|        4 |   9 | optimizing                     | 0.000004 | 0.000003 |   0.000001 |  >|        4 |  10 | statistics                     | 0.000011 | 0.000008 |   0.000004 |  >|        4 |  11 | preparing                      | 0.000016 | 0.000011 |   0.000004 |  >|        4 |  12 | executing                      | 0.000802 | 0.000573 |   0.000239 |  >|        4 |  13 | end                            | 0.000017 | 0.000005 |   0.000002 |  >|        4 |  14 | query end                      | 0.000003 | 0.000002 |   0.000001 |  >|        4 |  15 | waiting for handler commit     | 0.000008 | 0.000006 |   0.000002 |  >|        4 |  16 | closing tables                 | 0.000007 | 0.000005 |   0.000002 |  >|        4 |  17 | freeing items                  | 0.000029 | 0.000020 |   0.000009 |  >|        4 |  18 | cleaning up                    | 0.000019 | 0.000013 |   0.000005 |  >+----------+-----+--------------------------------+----------+----------+------------+-->(END)

8; . Sekarang perhatikan baik-baik urutan kueri kita. Mari kita lihat rencana yang digunakan MySQL dalam menjalankan kueri kita dengan menambahkan kata kunci

mysql> SELECT * FROM customer;

9 di kueri kita

mysql> USE sakila;

Database changed
Code language: PHP (php)

5

Jadi, ini berbeda dari pesanan yang kami harapkan. Menurut hasil ini, kita dapat melihat bahwa MySQL ingin memulai dengan tabel

mysql> SET GLOBAL slow_query_log = 'ON';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

0 dan kemudian memindai tabel

mysql> SET GLOBAL slow_query_log = 'ON';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

1, tabel

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=4;

+----------+-----+--------------------------------+----------+----------+------------+-->| QUERY_ID | SEQ | STATE                          | DURATION | CPU_USER | CPU_SYSTEM | C>+----------+-----+--------------------------------+----------+----------+------------+-->|        4 |   2 | starting                       | 0.000107 | 0.000046 |   0.000019 |  >|        4 |   3 | Executing hook on transaction  | 0.000005 | 0.000003 |   0.000001 |  >|        4 |   4 | starting                       | 0.000007 | 0.000005 |   0.000002 |  >|        4 |   5 | checking permissions           | 0.000007 | 0.000004 |   0.000002 |  >|        4 |   6 | Opening tables                 | 0.000337 | 0.000238 |   0.000100 |  >|        4 |   7 | init                           | 0.000007 | 0.000005 |   0.000001 |  >|        4 |   8 | System lock                    | 0.000010 | 0.000006 |   0.000003 |  >|        4 |   9 | optimizing                     | 0.000004 | 0.000003 |   0.000001 |  >|        4 |  10 | statistics                     | 0.000011 | 0.000008 |   0.000004 |  >|        4 |  11 | preparing                      | 0.000016 | 0.000011 |   0.000004 |  >|        4 |  12 | executing                      | 0.000802 | 0.000573 |   0.000239 |  >|        4 |  13 | end                            | 0.000017 | 0.000005 |   0.000002 |  >|        4 |  14 | query end                      | 0.000003 | 0.000002 |   0.000001 |  >|        4 |  15 | waiting for handler commit     | 0.000008 | 0.000006 |   0.000002 |  >|        4 |  16 | closing tables                 | 0.000007 | 0.000005 |   0.000002 |  >|        4 |  17 | freeing items                  | 0.000029 | 0.000020 |   0.000009 |  >|        4 |  18 | cleaning up                    | 0.000019 | 0.000013 |   0.000005 |  >+----------+-----+--------------------------------+----------+----------+------------+-->(END)

7, dan terakhir tabel

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=4;

+----------+-----+--------------------------------+----------+----------+------------+-->| QUERY_ID | SEQ | STATE                          | DURATION | CPU_USER | CPU_SYSTEM | C>+----------+-----+--------------------------------+----------+----------+------------+-->|        4 |   2 | starting                       | 0.000107 | 0.000046 |   0.000019 |  >|        4 |   3 | Executing hook on transaction  | 0.000005 | 0.000003 |   0.000001 |  >|        4 |   4 | starting                       | 0.000007 | 0.000005 |   0.000002 |  >|        4 |   5 | checking permissions           | 0.000007 | 0.000004 |   0.000002 |  >|        4 |   6 | Opening tables                 | 0.000337 | 0.000238 |   0.000100 |  >|        4 |   7 | init                           | 0.000007 | 0.000005 |   0.000001 |  >|        4 |   8 | System lock                    | 0.000010 | 0.000006 |   0.000003 |  >|        4 |   9 | optimizing                     | 0.000004 | 0.000003 |   0.000001 |  >|        4 |  10 | statistics                     | 0.000011 | 0.000008 |   0.000004 |  >|        4 |  11 | preparing                      | 0.000016 | 0.000011 |   0.000004 |  >|        4 |  12 | executing                      | 0.000802 | 0.000573 |   0.000239 |  >|        4 |  13 | end                            | 0.000017 | 0.000005 |   0.000002 |  >|        4 |  14 | query end                      | 0.000003 | 0.000002 |   0.000001 |  >|        4 |  15 | waiting for handler commit     | 0.000008 | 0.000006 |   0.000002 |  >|        4 |  16 | closing tables                 | 0.000007 | 0.000005 |   0.000002 |  >|        4 |  17 | freeing items                  | 0.000029 | 0.000020 |   0.000009 |  >|        4 |  18 | cleaning up                    | 0.000019 | 0.000013 |   0.000005 |  >+----------+-----+--------------------------------+----------+----------+------------+-->(END)

6. Tapi mengapa memilih rute ini?

Jawabannya adalah pengoptimal gabungan. Pengoptimalan gabungan adalah salah satu cara paling ampuh MySQL membantu mengoptimalkan kueri yang melibatkan gabungan. Ini dilakukan dengan mengatur gabungan dalam beberapa pesanan dan kemudian memperkirakan berbagai biaya untuk setiap pesanan sebelum akhirnya memilih opsi paling murah yang memberikan hasil yang sama dengan urutan awal kueri.

Kembali ke contoh terakhir kami, dengan menggunakan

mysql> SELECT * FROM customer;

_9, kami melihat bahwa MySQL hanya perlu memindai 109 baris di tabel

mysql> SET GLOBAL slow_query_log = 'ON';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

0 sebelum memindai sejumlah kecil baris di tabel lain menggunakan kolom yang diindeks – masing-masing 5, 1, dan 1

Kami dapat memaksa MySQL untuk menjalankan kueri kami sesuai urutan yang kami inginkan dengan menggunakan klausa

mysql> SET GLOBAL slow_query_log = 'ON';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

6. Mari kita lihat informasi tentang cara menggunakannya

mysql> USE sakila;

Database changed
Code language: PHP (php)

_6

Dilihat dari jumlah baris yang akan dipindai oleh server MySQL pada tabel pelanggan (599) dalam contoh ini, kita dapat mengetahui bahwa rencana yang disarankan oleh pengoptimal gabungan lebih berkinerja. Perhatikan bahwa masih ada beberapa kali pengoptimal yang salah;

Pernyataan

mysql> SELECT * FROM customer;

9 dapat membantu Anda mengidentifikasi di mana Anda harus menambahkan indeks ke tabel sehingga kueri dapat bekerja lebih cepat dengan menggunakan indeks untuk menemukan baris dalam tabel tersebut

Pernyataan JELASKAN ANALISIS

Teknik pembuatan profil yang telah kita diskusikan tidak akan lengkap tanpa menyebutkan

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

0

Ini bekerja mirip dengan pernyataan

mysql> SELECT * FROM customer;

_9. Perbedaan utama adalah bahwa yang pertama lebih bertele-tele. Anda mendapatkan lebih banyak statistik tentang eksekusi kueri

Perbedaan lainnya adalah

mysql> SELECT * FROM customer;

_9 tidak menjalankan kueri, tetapi

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

0 tidak. Itu karena untuk mengambil statistik yang hilang, perlu berkomunikasi dengan mesin Penyimpanan. Kami akan menjelaskan cara kerja komunikasi ini di bagian selanjutnya. Sementara itu, mari kita tunjukkan teknik pembuatan profil ini. Untuk membuat profil kueri dengan pernyataan

mysql> SHOW PROFILES;

+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02364600 | SELECT DATABASE()      |
|        2 | 0.04395425 | show databases         |
|        3 | 0.00854575 | show tables            |
|        4 | 0.00213000 | SELECT * FROM customer |
+----------+------------+------------------------+
4 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

_0, Anda hanya perlu menambahkan kueri dengan teks seperti ini

mysql> USE sakila;

Database changed
Code language: PHP (php)

_7

Seperti yang Anda lihat, hasilnya menunjukkan bagaimana server MySQL menjalankan rencana tersebut. Ini memperkenalkan beberapa statistik lagi

Waktu sebenarnya untuk mengambil baris pertama dalam tabel (dalam milidetik)
Waktu sebenarnya untuk mengambil semua baris dalam tabel (dalam milidetik)
Perkiraan biaya kueri
Jumlah baris yang sebenarnya terbaca
Jumlah sebenarnya dari loop yang dibuat

Estimasi biaya rencana eksekusi kueri adalah jumlah kumulatif dari biaya semua operasi dalam rencana, seperti operasi I/O atau CPU

Memeriksa variabel sesi last_query_cost

Alat cepat lain untuk mengetahui perkiraan biaya kueri adalah dengan memeriksa variabel sesi

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
Code language: PHP (php)

3

mysql> USE sakila;

Database changed
Code language: PHP (php)

_8

Hasil menunjukkan biaya rencana kueri terkompilasi terakhir yang dihitung oleh pengoptimal kueri. Ini adalah cara yang berguna untuk membandingkan dua atau lebih versi kueri Anda. Dalam contoh berikut, kami menjalankan dua kueri serupa dan membandingkan biaya untuk masing-masing kueri. Hasilnya menunjukkan bahwa rencana eksekusi kueri pertama kami akan lebih murah

mysql> USE sakila;

Database changed
Code language: PHP (php)

_9

Optimalkan kueri lambat

Sekarang kita telah melihat beberapa cara untuk mengidentifikasi dan menganalisis kueri yang lambat, hal selanjutnya adalah memperbaiki kueri yang berkinerja lambat. Secara naluriah, hal pertama yang kami lakukan adalah memeriksa kueri itu sendiri. Teknik berikut menunjukkan cara mengoptimalkan kueri yang lambat

Hanya mengambil data yang Anda butuhkan

Sementara server memindai semua baris permintaan Anda, itu mengunci semua sumber daya yang tersedia di sesi, dan klien tidak dapat menghentikan proses tanpa Anda keluar dari sesi secara paksa. Selain itu, klien tidak dapat mengakses data secara selektif hingga server memindai semua baris

Kueri yang Anda kirim mungkin meminta terlalu banyak data, yang menyebabkan server MySQL membuang waktu untuk memindai banyak baris data. Tapi kita bisa memperbaikinya dengan melakukan hal berikut

Ambil jumlah baris yang Anda butuhkan saja (penomoran halaman)

Bayangkan Anda sedang membangun situs web basis data film seperti IMDB yang hanya perlu mengambil sekitar 10 baris informasi film sekaligus (per halaman). Biasanya, Anda tidak akan mengambil semua data seperti ini

mysql> SELECT * FROM customer;

0

Melakukan hal itu akan membuat server MySQL memindai 1000 baris lengkap tabel untuk data kecil yang kita butuhkan

Kami dapat memperbaiki kueri kami menggunakan teknik basis data umum yang dikenal sebagai pagination. Kita dapat menggunakan klausa

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
Code language: PHP (php)

4 dan

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
Code language: PHP (php)

5 untuk menentukan jumlah baris yang kita butuhkan dan dari baris mana data harus mulai diambil. Tujuan dari panduan ini bukan untuk mengajari Anda penomoran halaman secara mendalam (kami akan membicarakannya di artikel lain), tetapi versi kueri kami yang dioptimalkan akan terlihat seperti ini

mysql> SELECT * FROM customer;

1

mysql> SELECT * FROM customer;

2

Ambil hanya kolom yang Anda butuhkan

Kueri kami pada contoh terakhir masih belum sepenuhnya dioptimalkan. Jika Anda mengamatinya, Anda akan melihat kami mengambil data kami di semua kolom tabel menggunakan

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
Code language: PHP (php)

6 (pilih semua). Cara yang lebih baik untuk mengakses data kami adalah dengan menamai kolom yang kami perlukan dalam kueri kami. Mari kita lihat kolom

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
Code language: PHP (php)

_7 yang dimiliki tabel kita

mysql> SELECT * FROM customer;

_3

Untuk aplikasi data film kami, kami mungkin hanya memerlukan judul, deskripsi, peringkat, dan tahun rilis film. Inilah cara kami meminta data tersebut

mysql> SELECT * FROM customer;

_4

Ambil kolom yang Anda perlukan saat menggabungkan tabel

Sama seperti contoh sebelumnya. Anda dapat membuat penggabungan Anda lebih cepat hanya dengan meminta kolom yang Anda perlukan di kumpulan hasil. Katakanlah Anda hanya ingin mengambil nama depan dan belakang dari semua pelanggan dalam contoh

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
Code language: PHP (php)

8 kami sebelumnya. Kami akan menulis kueri kami dengan cara ini

mysql> SELECT * FROM customer;

5

Melakukannya akan mencegah server MySQL memindai semua kolom tabel yang digabungkan

Gunakan strategi caching untuk mencegah pengambilan data yang sama berulang kali

Jika aplikasi Anda sering menggunakan potongan data yang sama, alih-alih menjalankan kueri setiap kali aplikasi membutuhkan data, Anda dapat meng-cache data saat pertama kali kueri dijalankan dan cukup menggunakan kembali data setelahnya

Satu-satunya pengecualian adalah jika data sering berubah atau ketika Anda membutuhkan informasi yang diminta untuk diperbarui setiap saat. Dulu ada lapisan Cache built-in di MySQL yang disebut "The Query Cache", tetapi sudah tidak digunakan lagi karena alasan skalabilitas. Untungnya, beberapa alat dapat membantu menyimpan data, seperti Memcached dan Redis.

Hindari ekspresi LIKE dengan wildcard terkemuka

Wildcard digunakan bersama dengan klausa

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
Code language: PHP (php)

9. Mereka adalah karakter yang digunakan untuk membantu mencari data yang sesuai dengan kriteria kompleks

Posisi karakter wildcard dalam klausa

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
Code language: PHP (php)

9 dapat menyebabkan perilaku kinerja yang tidak terduga. Menggunakan wildcard terkemuka telah diketahui sebagai penyebab paling sering, dan ini karena MySQL tidak dapat menggunakan indeks secara efisien ketika Anda memiliki wildcard terkemuka

mysql> SELECT * FROM customer;

_6

UNION vs. UNI SEMUA

Pengoptimal kueri MySQL tidak menerapkan banyak pengoptimalan untuk kueri

mysql> SET GLOBAL long_query_time = 60;

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

1 seperti kueri lain, tetapi Anda dapat menentukan klausa seperti

mysql> SET GLOBAL long_query_time = 60;

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

2,

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
Code language: PHP (php)

4,

mysql> SET GLOBAL long_query_time = 60;

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

4, dll. , untuk membantu pengoptimal. Tetapi opsi yang lebih baik lagi adalah menggunakan klausa

mysql> SET GLOBAL long_query_time = 60;

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

5 jika Anda perlu menghilangkan baris duplikat dalam hasil kueri Anda

Itu karena menghilangkan

mysql> SET GLOBAL long_query_time = 60;

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

6 menambahkan opsi

mysql> SET GLOBAL long_query_time = 60;

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

7 ke tabel sementara, yang mengakibatkan server MySQL melakukan pemindaian mahal pada semua baris untuk menentukan keunikan. Kuncinya adalah menghindari penggunaan

mysql> SET GLOBAL long_query_time = 60;

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

7 dan

mysql> SET GLOBAL long_query_time = 60;

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

1 kecuali diperlukan

Optimalkan kueri GABUNG

Anda harus menggunakan

mysql> SET GLOBAL slow_query_log_file = '/somepath/filename.log';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

_0 alih-alih

mysql> SET GLOBAL slow_query_log_file = '/somepath/filename.log';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

1 jika memungkinkan. Anda juga dapat mengoptimalkan kueri

mysql> SET GLOBAL slow_query_log_file = '/somepath/filename.log';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

_2 Anda dengan memastikan bahwa indeks ada pada kolom yang ditentukan dalam klausa

mysql> SET GLOBAL slow_query_log_file = '/somepath/filename.log';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

3 dan

mysql> SET GLOBAL slow_query_log_file = '/somepath/filename.log';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

4

GROUP BY dan ORDER BY

mysql> SET GLOBAL slow_query_log_file = '/somepath/filename.log';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

5 dan

mysql> SET GLOBAL long_query_time = 60;

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

4 ekspresi harus selalu merujuk ke kolom dari satu tabel sehingga server dapat menggunakan indeks untuk operasi tersebut

Optimalkan COUNT() kueri

Fungsi

mysql> SET GLOBAL slow_query_log_file = '/somepath/filename.log';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

_7 adalah fungsi agregat khusus yang digunakan dalam dua cara. Pertama, saat Anda menentukan nama kolom atau ekspresi dalam fungsi

mysql> SET GLOBAL slow_query_log_file = '/somepath/filename.log';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

7, fungsi ini menghitung berapa kali ekspresi memiliki nilai (i. e. ekspresi non-NULL)

Cara kedua untuk menggunakan

mysql> SET GLOBAL slow_query_log_file = '/somepath/filename.log';

Query OK, 0 rows affected (0.08 sec)



Code language: PHP (php)

_7 adalah menghitung jumlah baris dalam hasil kueri menggunakan ekspresi

mysql> SELECT SLEEP(15);

+-----------+
| SLEEP(15) |
+-----------+
|         0 |
+-----------+
1 row in set (15.00 sec)
Code language: JavaScript (javascript)

0. Itu mengabaikan kolom dalam tabel dan sebagai gantinya menghitung baris;

Bagaimana cara membantu pengoptimal MySQL

Ada beberapa kasus di mana pengoptimal mungkin tidak memilih jalur eksekusi terbaik. Alasannya mungkin karena MySQL tidak memiliki informasi yang cukup tentang data saat ini dan harus membuat tebakan "terpelajar" tentang data tersebut

Dalam kasus tersebut, ada beberapa metode yang tersedia untuk membantu MySQL

Menggunakan pernyataan
```
mysql> SELECT * FROM customer;
```
_9 untuk melihat informasi tentang bagaimana MySQL memproses kueri Anda. Saya menunjukkan kepada Anda bagaimana melakukan itu sebelumnya

Menggunakan

mysql> SELECT SLEEP(15);

+-----------+
| SLEEP(15) |
+-----------+
|         0 |
+-----------+
1 row in set (15.00 sec)
Code language: JavaScript (javascript)

_2,

mysql> SELECT SLEEP(15);

+-----------+
| SLEEP(15) |
+-----------+
|         0 |
+-----------+
1 row in set (15.00 sec)
Code language: JavaScript (javascript)

3, dan

mysql> SELECT SLEEP(15);

+-----------+
| SLEEP(15) |
+-----------+
|         0 |
+-----------+
1 row in set (15.00 sec)
Code language: JavaScript (javascript)

4 (Petunjuk indeks) untuk tabel yang dipindai untuk memberi tahu MySQL bahwa pemindaian tabel mahal dibandingkan dengan menggunakan indeks yang diberikan. Petunjuk indeks memberi Anda kontrol penuh atas cara pengoptimal memilih indeks untuk rencana eksekusi kueri. Sebaiknya gunakan ini sebagai upaya terakhir setelah mencoba teknik pengoptimalan lain yang dibahas. Sintaks untuk menggunakan petunjuk indeks terlihat seperti ini

mysql> SELECT * FROM customer;

_7

Menerapkan Petunjuk Pengoptimal pada tingkat cakupan yang berbeda. global, blok kueri, tingkat tabel, atau tingkat indeks untuk mengontrol strategi pengoptimal berdasarkan kriteria tertentu. Petunjuk pengoptimal biasanya diterapkan per pernyataan dan dapat digunakan bersama dengan petunjuk Indeks. Mereka terlihat seperti komentar tetapi dikenali oleh pengurai kueri. Sintaks untuk menambahkan petunjuk pengoptimal terlihat seperti ini

mysql> SELECT * FROM customer;

_8

Menggunakan

mysql> SELECT SLEEP(15);

+-----------+
| SLEEP(15) |
+-----------+
|         0 |
+-----------+
1 row in set (15.00 sec)
Code language: JavaScript (javascript)

_5 untuk memperbarui distribusi kunci dari tabel yang dipindai

Menggunakan Global dan tingkat tabel

mysql> SET GLOBAL slow_query_log = 'ON';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)

6

Menyetel variabel sistem global atau khusus utas. Misalnya, Anda dapat memulai

mysql> SELECT SLEEP(15);

+-----------+
| SLEEP(15) |
+-----------+
|         0 |
+-----------+
1 row in set (15.00 sec)
Code language: JavaScript (javascript)

7dengan opsi atau menggunakan

mysql> SELECT SLEEP(15);

+-----------+
| SLEEP(15) |
+-----------+
|         0 |
+-----------+
1 row in set (15.00 sec)
Code language: JavaScript (javascript)

9

mysql> USE sakila;

Database changed
Code language: PHP (php)

00 untuk memberi tahu pengoptimal agar berasumsi bahwa tidak ada pemindaian kunci yang menyebabkan lebih dari 1.000 pencarian kunci

Menulis ulang kueri Anda. Terkadang, kueri Anda mungkin memerlukan pemfaktoran ulang untuk meningkatkan waktu responsnya. Kami membahas beberapa skenario yang bisa kami lakukan di bagian sebelumnya
Menambahkan Indeks ke tabel Anda. Untuk tabel yang tidak memerlukan penyisipan atau pembaruan batch yang sering, Anda mungkin ingin menggunakan indeks. Indeks juga mempercepat
```
mysql> SET GLOBAL slow_query_log_file = '/somepath/filename.log';

Query OK, 0 rows affected (0.08 sec)
Code language: PHP (php)
```
2 operasi dan membantu MySQL mengambil data lebih cepat, sehingga meningkatkan waktu respons kueri Anda. Untuk tabel yang lebih kecil, menambahkan indeks mungkin tidak optimal. Selain itu, Anda tidak boleh mengindeks semuanya, karena ini meningkatkan penggunaan ruang disk dan memperlambat pembaruan atau penyisipan catatan
Mendesain ulang skema Anda. Untuk mengakses tabel dengan kecepatan terbaik, Anda harus mempertimbangkan pengoptimalan skema. Skema basis data yang dirancang dengan buruk memaksa MySQL untuk memakan waktu lebih lama dan menggunakan lebih banyak sumber daya sistem saat memproses kueri Anda

Langkah selanjutnya. optimasi skema dan pengindeksan

Optimalisasi kueri adalah subjek penting dalam database relasional seperti MySQL. Memahami bagaimana MySQL mengeksekusi kueri memungkinkan Anda menentukan di mana ia menghabiskan waktu untuk kueri Anda. Anda juga memahami bagaimana waktu respons dapat dipengaruhi oleh kualitas kueri yang Anda kirim ke server MySQL.

Optimalisasi kueri hanyalah salah satu bagian dari teka-teki dalam meningkatkan kinerja MySQL. Pengoptimalan dan pengindeksan skema adalah opsi lain untuk dipertimbangkan. Faktanya, skema dan pengindeksan dapat memengaruhi kecepatan kueri dan sebaliknya. Artikel kami selanjutnya akan menjelaskan bagaimana pengoptimalan dan pengindeksan skema dapat memengaruhi kinerja database MySQL dan kecepatan eksekusi kueri Anda

Saya Elvis Duru. Saya membuat konten bermanfaat untuk pengembang web dengan fokus pada React, Node. js, Database SQL/NoSQL, dan lainnya. Mari terhubung di Twitter

Bacaan lebih lanjut

Pengoptimal Kueri MySQL
Petunjuk Pengoptimal
Petunjuk Indeks
Statistik Indeks
Ringkas File Log Kueri Lambat
Pernyataan JELASKAN
– SQL Kinerja Tinggi oleh Silvia Botros, Jeremy Tinley

Pos terkait

1 Februari 2023 • Pengembangan

Oleh Siddhant Varma

Tooltips adalah cara yang bagus untuk menyampaikan informasi tambahan untuk tindakan pengguna. Dalam postingan ini, kita akan mempelajari cara membuat komponen tooltip khusus yang dapat digunakan kembali sepenuhnya untuk aplikasi React Anda

Cara Menangani State Management di Vue Menggunakan Pinia

27 Januari 2023 • Pengembangan

Oleh David Herbert

Manajemen status sering kali diperlukan saat membangun aplikasi, tetapi dapat menjadi tantangan untuk diterapkan, terutama dengan aplikasi berskala besar. Posting blog ini akan mengajari Anda cara menangani manajemen status di Vue menggunakan Pinia

Cara Memanipulasi Data SQL Menggunakan SQLAlchemy dan Pandas

23 Januari 2023 • Pengembangan

Oleh EzzEddin Abdullah

Berurusan dengan database melalui Python mudah dicapai dengan menggunakan SQLAlchemy. Memanipulasi data melalui SQLAlchemy dapat dilakukan di sebagian besar tugas, tetapi ada beberapa kasus yang Anda perlukan untuk mengintegrasikan solusi database Anda dengan pustaka Pandas. Di postingan blog ini, Anda akan belajar cara memanipulasi data SQL menggunakan SQLAlchemy dan Pandas

Bagaimana cara meningkatkan kinerja kueri MySQL?

Mengoptimalkan Kueri Dengan Panduan Pengoptimalan Kueri MySQL .

Hindari menggunakan fungsi dalam predikat. .

Hindari penggunaan wildcard (%) di awal predikat. .

Hindari kolom yang tidak perlu di klausa SELECT. .

Gunakan gabungan dalam, alih-alih gabungan luar jika memungkinkan. .

Gunakan DISTINCT dan UNION hanya jika diperlukan

Bagaimana cara menyetel kueri SQL?

Sangatlah penting untuk mengoptimalkan kueri Anda untuk dampak minimum pada kinerja database. .

Tentukan persyaratan bisnis terlebih dahulu. .

Bidang SELECT alih-alih menggunakan SELECT *.

Hindari PILIH BERBEDA. .

Buat gabungan dengan INNER JOIN (bukan WHERE).

Gunakan DI MANA alih-alih HARUS untuk menentukan filter. .

Gunakan wildcard di akhir frasa saja

Mengapa kueri MySQL saya terlalu lambat?

Kueri bisa menjadi lambat karena berbagai alasan mulai dari penggunaan indeks yang tidak benar hingga bug di mesin penyimpanan itu sendiri . Namun, dalam banyak kasus, kueri menjadi lambat karena pengembang atau administrator database MySQL lalai memantau dan mengawasi kinerjanya.