Bekerja dengan database seringkali merupakan hal yang paling penting titik lemah dalam produktivitas banyak orang aplikasi web. Dan bukan hanya DBA yang harus mengkhawatirkan hal ini. Pemrogram harus memilih struktur yang benar tabel, menulis kueri yang dioptimalkan dan kode yang bagus. Berikut ini adalah metode untuk mengoptimalkan pekerjaan dengan MySQL untuk programmer.

1. Optimalkan Kueri untuk Cache Kueri

Paling Server MySQL Caching kueri diaktifkan. Salah satu cara terbaik peningkatan kinerja hanya dengan menyerahkan caching ke database itu sendiri. Ketika query diulang berkali-kali, hasilnya diambil dari cache, yang jauh lebih cepat dibandingkan mengakses database secara langsung. Masalah utamanya adalah banyak orang hanya menggunakan kueri yang tidak dapat di-cache:

"PILIH nama pengguna DARI pengguna WHERE tanggal_pendaftaran >= "$hari ini"" Alasannya adalah query pertama menggunakan fungsi CURDATE(). Ini berlaku untuk semua fungsi seperti SEKARANG(), RAND() dan lainnya yang hasilnya non-deterministik. Jika hasil suatu fungsi dapat berubah, maka MySQL tidak menyimpan kueri tersebut dalam cache. DI DALAM dalam contoh ini

hal ini dapat dicegah dengan menghitung tanggal sebelum menjalankan kueri.

// mendapatkan data

$stmt ->ambil();
printf("%s berasal dari %s\n", $namapengguna, $negara bagian);

"mysql_unbuffered_query() mengirimkan kueri SQL ke MySQL tanpa mengambil atau secara otomatis melakukan buffering pada baris hasil seperti yang dilakukan mysql_query(). Di satu sisi, ini menghemat sejumlah besar memori untuk kueri SQL yang menghasilkan kumpulan hasil yang besar. Di sisi lain, Anda dapat mulai mengerjakan kumpulan hasil pemotongan setelah baris pertama diambil: Anda tidak perlu menunggu hingga kueri SQL lengkap dijalankan."

Namun ada batasan tertentu. Anda harus membaca semua catatan atau menelepon mysql_free_result() sebelum Anda dapat menjalankan kueri lainnya. Selain itu, Anda tidak dapat menggunakan mysql_num_rows() atau mysql_data_seek() pada hasil suatu fungsi.

14. Simpan IP di UNSIGNED INT

Banyak programmer menyimpan alamat IP dalam field bertipe VARCHAR(15), tanpa mengetahui bahwa alamat tersebut dapat disimpan dalam bentuk integer. INT menempati 4 byte dan memiliki ukuran bidang tetap.
Pastikan untuk menggunakan UNSIGNED INT karena IP dapat ditulis sebagai nomor 32-bit yang tidak ditandatangani.
Gunakan INET_ATON() dalam permintaan Anda untuk mengonversi alamat IP menjadi angka, dan INET_NTOA() untuk mengonversinya kembali. Fungsi yang sama ada di PHP - ip2long() dan long2ip() (di PHP fungsi ini juga dapat mengembalikan nilai negatif. Catatan dari The_Lion).

15. Tabel ukuran tetap (statis) lebih cepat

Jika setiap kolom dalam tabel mempunyai ukuran tetap, maka tabel tersebut disebut “statis” atau “ ukuran tetap" Contoh kolom dengan panjang tidak tetap: VARCHAR, TEXT, BLOB. Jika Anda memasukkan bidang seperti itu ke dalam tabel, bidang tersebut tidak lagi diperbaiki dan akan diproses secara berbeda oleh MySQL.
Penggunaan tabel seperti itu akan meningkatkan efisiensi, karena... MySQL dapat mencari catatan di dalamnya lebih cepat. Kapan harus memilih garis yang diinginkan tabel, MySQL dapat menghitung posisinya dengan sangat cepat. Jika ukuran record tidak tetap, maka dicari berdasarkan indeks.
Tabel ini juga lebih mudah untuk di-cache dan dipulihkan setelah database crash. Misalnya, jika Anda mengonversi VARCHAR(20) ke CHAR(20), entri tersebut akan menempati 20 byte, terlepas dari konten sebenarnya.
Dengan menggunakan metode "pemisahan vertikal", Anda dapat memindahkan kolom dari panjang variabel baris ke dalam tabel terpisah.

16. Pemisahan vertikal

Partisi vertikal mengacu pada membagi tabel menjadi kolom untuk meningkatkan kinerja.
Contoh 1. Jika alamat disimpan di tabel pengguna, maka bukan fakta bahwa Anda akan sering membutuhkannya. Anda dapat membagi tabel dan menyimpan alamat meja terpisah. Dengan demikian, ukuran tabel pengguna akan diperkecil. Produktivitas akan meningkat.
Contoh 2: Anda memiliki kolom "last_login" di tabel. Itu diperbarui setiap kali pengguna masuk ke situs. Namun semua perubahan pada tabel menghapus cache-nya. Dengan menyimpan bidang ini di tabel lain, Anda akan meminimalkan perubahan pada tabel pengguna.
Namun jika Anda terus-menerus menggunakan gabungan pada tabel ini, hal itu akan menyebabkan kinerja buruk.

17. Pisahkan kueri DELETE dan INSERT yang besar

Jika Anda perlu membuat permintaan besar untuk menghapus atau memasukkan data, Anda harus berhati-hati agar tidak merusak aplikasi. Eksekusi permintaan besar dapat mengunci meja dan mengarah ke kerusakan seluruh aplikasi.
Apache dapat melakukan banyak hal proses paralel serentak. Oleh karena itu, ini bekerja lebih efisien jika skrip dijalankan secepat mungkin.
Jika Anda mengunci meja jangka panjang(misalnya, selama 30 detik atau lebih), kemudian dengan lalu lintas situs yang tinggi, antrian besar proses dan permintaan mungkin timbul, yang dapat menyebabkan kerja lambat situs atau bahkan server crash.
Jika Anda memiliki pertanyaan seperti ini, gunakan LIMIT untuk menjalankannya dalam jumlah kecil.

gunakan(50000); )

18. Kolom kecil lebih cepat
Untuk database, bekerja dengan hard drive mungkin merupakan titik terlemahnya. Rekaman yang kecil dan kompak biasanya lebih baik dalam hal kinerja karena... mengurangi kerja disk.
Dokumentasi MySQL memiliki daftar persyaratan penyimpanan data untuk semua tipe data.
Jika tabel Anda akan menyimpan beberapa baris, maka tidak masuk akal untuk menjadikan kunci utama bertipe INT; mungkin lebih baik menjadikannya MEDIUMINT, SMALLINT, atau bahkan TINYINT. Jika Anda tidak perlu menyimpan waktu, gunakan DATE, bukan DATETIME.

Namun, berhati-hatilah agar hal-hal tidak menjadi seperti Slashdot.

19. Pilih jenis tabel yang tepat

20. Gunakan ORM

21. Hati-hati dengan koneksi yang persisten
mysql_pconnect() di PHP
Tapi ini hanya terdengar bagus secara teori. Dari pengalaman pribadi saya (dan pengalaman orang lain), penggunaan fitur ini tidak dibenarkan. Anda akan mengalami masalah serius dengan batas koneksi, batas memori, dan sebagainya.
Apache membuat banyak thread paralel. Inilah alasan utama mengapa koneksi persisten tidak berfungsi sebaik yang kita inginkan. Sebelum menggunakan mysql_pconnect(), konsultasikan dengan administrator sistem Anda.

Bagaimana cara mengoptimalkan permintaan MySQL?

Untuk situs biasa yang tidak terlalu dikunjungi, tidak banyak perbedaan apakah kueri MySQL ke database dioptimalkan atau tidak. Namun untuk server produksi dengan beban berat, perbedaan antara SQL yang benar dan salah sangat besar, dan pada waktu proses hal tersebut dapat memengaruhi perilaku dan keandalan layanan secara signifikan. Pada artikel ini, saya akan melihat cara menulis kueri cepat dan faktor yang membuatnya lambat.

Mengapa MySQL?

Saat ini banyak perbincangan tentang Dig Data dan teknologi baru lainnya. Solusi NoSQL dan cloud memang bagus, tetapi banyak perangkat lunak populer (seperti WordPress, phpBB, Drupal) masih berjalan di MySQL. Migrasi ke solusi terbaru dapat menghasilkan lebih dari sekedar perubahan konfigurasi pada server. Selain itu, efisiensi MySQL masih pada level tertentu, terutama versi Percona.

Jangan membuat kesalahan umum dengan menggunakan lebih banyak perangkat keras untuk menyelesaikan masalah kueri yang lambat dan beban server yang tinggi - lebih baik langsung ke akar masalahnya. Meningkatkan kekuatan prosesor dan hard drive serta menambah RAM juga merupakan jenis optimasi tertentu, namun bukan ini yang akan kita bahas di artikel ini. Selain itu, dengan mengoptimalkan situs dan menyelesaikan masalah perangkat keras, beban hanya akan bertambah secara eksponensial. Oleh karena itu, ini hanyalah solusi jangka pendek.

Pemahaman yang baik tentang SQL adalah alat paling penting bagi pengembang web; ini akan memungkinkan Anda mengoptimalkan dan menggunakan database relasional secara efektif. Pada artikel ini kita akan fokus pada database open source populer yang sering digunakan bersama dengan PHP, MySQL.

Untuk siapa artikel ini?

Untuk pengembang web, arsitek dan pengembang basis data, serta administrator sistem yang familiar dengan MySQL. Jika Anda belum pernah menggunakan MySQL sebelumnya, artikel ini mungkin tidak banyak berguna bagi Anda, namun saya akan tetap berusaha seinformatif dan bermanfaat mungkin, bahkan bagi mereka yang baru mengenal MySQL.

Cadangkan terlebih dahulu

Saya sarankan melakukan langkah-langkah berikut berdasarkan MySQL yang Anda gunakan, tetapi pastikan untuk membuat cadangan. Jika Anda tidak memiliki database untuk digunakan, saya akan memberikan contoh untuk membuat database Anda sendiri jika diperlukan.

Membuat cadangan MySQL mudah menggunakan utilitas mysqldump:

$ mysqldump myTab > myTab-backup.sql Anda dapat mempelajari lebih lanjut tentang mysqldump.

Apa yang membuat kueri menjadi lambat?

Berikut adalah daftar umum faktor yang mempengaruhi kecepatan permintaan dan beban server:

• indeks tabel;
• Kondisi WHERE (dan menggunakan fungsi internal MySQL, seperti IF atau DATE);
• urutkan berdasarkan ORDER BY;
• seringnya pengulangan permintaan yang sama;
• jenis mekanisme penyimpanan data (InnoDB, MyISAM, Memory, Blackhole);
• tidak menggunakan versi Percona;
• konfigurasi server (my.cnf / my.ini);
• keluaran data besar (lebih dari 1000 baris);
• koneksi tidak stabil;
• konfigurasi terdistribusi atau cluster;
• Desain meja yang buruk.

Apa itu indeks?

Indeks digunakan di MySQL untuk mencari baris dengan nilai kolom tertentu, seperti klausa WHERE. Tanpa indeks, MySQL harus, dimulai dari baris pertama, membaca seluruh tabel untuk mencari nilai yang relevan. Semakin besar tabelnya, semakin tinggi pula biayanya.

Jika sebuah tabel memiliki indeks pada kolom yang akan digunakan dalam query, MySQL akan dengan cepat menemukan lokasi informasi yang dibutuhkan tanpa memindai seluruh tabel. Ini jauh lebih cepat daripada mencari setiap baris secara berurutan.

Koneksi tidak stabil?

Saat aplikasi Anda terhubung ke database dan koneksi stabil dikonfigurasi, aplikasi akan digunakan setiap saat tanpa harus membuka koneksi baru setiap saat. Ini adalah solusi optimal untuk lingkungan kerja.

Mengurangi seringnya pengulangan permintaan yang identik

Cara tercepat dan paling efisien yang saya temukan untuk melakukan ini adalah dengan membuat penyimpanan kueri dan hasilnya menggunakan Memcached atau Redis. Dengan Memcache Anda dapat dengan mudah menyimpan hasil query Anda dalam cache, misalnya seperti ini:

sambungkan("localhost",11211); $cacheResult = $cache->get("nama-kunci"); if($cacheResult)( //tidak memerlukan kueri $result = $cacheResult; ) else ( //jalankan kueri Anda $mysqli = mysqli("p:localhost","nama pengguna","kata sandi","tabel" ); //tambahkan p: untuk penyimpanan jangka panjang $sql = "PILIH * DARI postingan KIRI GABUNG info pengguna menggunakan (UID) WHERE posts.post_type = "post" || posts.post_type = "article" ORDER BY kolom LIMIT 50" ; $mysqli->query($sql); $memc->set("nama-kunci", $hasil->fetch_array(), MEMCACHE_COMPRESSED,86400); posting", $cacheResult); ?> Sekarang query berat yang menggunakan LEFT JOIN akan dieksekusi hanya sekali setiap 86.400 detik (yaitu, sekali sehari), yang secara signifikan akan mengurangi beban pada server MySQL, menyisakan sumber daya untuk koneksi lain.

Catatan: Tambahkan p: di awal argumen host MySQLi untuk membuat koneksi persisten.

Konfigurasi terdistribusi atau berkerumun

Ketika data menjadi semakin banyak, dan kecepatan layanan Anda menurun, kepanikan bisa menguasai Anda. Solusi cepatnya adalah distribusi sumber daya (sharding). Namun, saya tidak menyarankan melakukan ini kecuali Anda sangat berpengalaman, karena distribusi pada dasarnya membuat struktur data menjadi rumit.

Desain Meja Lemah

Membuat skema database bukanlah pekerjaan sulit jika Anda mengikuti aturan utama dalam bekerja dengan batasan dan mengetahui apa yang akan berhasil. Misalnya, menyimpan gambar dalam sel BLOB sangat membingungkan - menyimpan jalur file dalam sel VARCHAR adalah solusi yang jauh lebih baik.

Memastikan desain yang tepat untuk penggunaan yang tepat adalah hal terpenting dalam membuat aplikasi Anda. Simpan data yang berbeda dalam tabel yang berbeda (misalnya, kategori dan artikel) dan pastikan bahwa hubungan banyak ke satu dan satu ke banyak dapat dengan mudah dikaitkan dengan ID. Menggunakan FOREIGN KEY di MySQL sangat ideal untuk menyimpan data berjenjang dalam tabel.

Saat membuat tabel, ingat hal berikut:

• Buat tabel yang efektif untuk memecahkan masalah Anda, daripada mengisi tabel dengan data dan hubungan yang tidak perlu.
• Jangan berharap MySQL mengeksekusi logika bisnis atau pemrograman Anda - datanya harus siap untuk disisipkan baris oleh bahasa skrip Anda. Misalnya, jika Anda perlu mengurutkan daftar dalam urutan acak, lakukan itu dalam array PHP, tanpa menggunakan ORDER BY dari gudang MySQL.
• Gunakan tipe indeks UNIK untuk kumpulan data unik, dan gunakan ON DUPLICATE KEY UPDATE untuk selalu memperbarui tanggal, misalnya untuk mengetahui kapan baris terakhir diubah.
• Gunakan tipe data INT untuk menyimpan bilangan bulat. Jika Anda tidak menentukan ukuran tipe data, MySQL akan melakukannya untuk Anda.

Untuk mengoptimalkan secara efektif, kami harus menerapkan tiga pendekatan pada aplikasi Anda:

1. Analisis (mencatat kueri yang lambat, mempelajari sistem, analisis kueri, dan desain basis data)
2. Persyaratan eksekusi (berapa banyak pengguna)
3. Keterbatasan teknologi (kecepatan perangkat keras, penggunaan MySQL yang salah)

Kolom, seperti yang Anda lihat, menyimpan informasi penting tentang permintaan tersebut. Kolom yang paling harus Anda perhatikan adalah kemungkinan_kunci dan Ekstra.

Kolom might_keys akan menampilkan indeks yang dapat diakses oleh MySQL untuk menjalankan kueri. Terkadang Anda perlu menetapkan indeks untuk membuat kueri berjalan lebih cepat. Kolom Ekstra akan menunjukkan apakah tambahan WHERE atau ORDER BY digunakan. Hal yang paling penting untuk diperhatikan adalah apakah Menggunakan Filesort ada di output.

Apa yang dilakukan Menggunakan Filesort dinyatakan dalam bantuan MySQL:

MySQL harus melakukan langkah ekstra untuk mengetahui cara mengembalikan baris dalam bentuk yang diurutkan. Pengurutan ini terjadi dengan mengulangi semua baris sesuai dengan tipe gabungan dan menyimpan kunci pengurutan dan penunjuk baris untuk semua baris yang cocok dengan klausa WHERE. Kunci diurutkan dan baris dikembalikan dalam urutan yang benar.

Untuk menghindari masalah dalam Menggunakan Filesort kita harus memastikan bahwa MySQL menggunakan INDEX. Saat ini ada beberapa kunci yang ditentukan dalam kemungkinan_kunci yang dapat dipilih, tetapi MySQL hanya dapat memilih satu indeks untuk permintaan akhir. Selain itu, indeks dapat terdiri dari beberapa kolom, dan Anda juga dapat memasukkan petunjuk untuk pengoptimal MySQL, yang menunjuk ke indeks yang telah Anda buat.

Petunjuk indeks

Pengoptimal MySQL akan menggunakan statistik berdasarkan kueri tabel untuk memilih indeks terbaik untuk menjalankan kueri. Cara kerjanya cukup sederhana, berdasarkan logika statistik bawaan, sehingga dengan adanya beberapa opsi, ia tidak selalu membuat pilihan yang tepat tanpa bantuan petunjuk. Untuk memastikan bahwa kunci yang digunakan benar (atau salah), gunakan kata kunci FORCE INDEX, USE INDEX, dan IGNORE INDEX dalam kueri Anda. Anda dapat membaca lebih lanjut tentang petunjuk indeks di bantuan MySQL.

Untuk menampilkan kunci tabel, gunakan perintah SHOW INDEX. Anda dapat menentukan beberapa petunjuk untuk digunakan oleh pengoptimal.

Selain EXPLAIN, ada kata kunci DESCRIBE. Dengan DESCRIBE, Anda dapat melihat informasi tabel sebagai berikut:

Menambahkan indeks

Untuk menambahkan indeks di MySQL, Anda harus menggunakan sintaks CREATE INDEX. Ada beberapa jenis indeks. FULLTEXT digunakan untuk pencarian teks lengkap, dan UNIQUE digunakan untuk menyimpan data unik.

Untuk menambahkan indeks ke tabel Anda, gunakan sintaks berikut:

Mysql> BUAT INDEKS idx_bookname PADA `buku` (nama buku(10)); Ini akan membuat indeks pada tabel buku yang akan menggunakan 10 huruf pertama dari kolom yang menyimpan judul buku dan bertipe varchar. Dalam hal ini, pencarian apa pun dengan kueri WHERE pada judul buku dengan kecocokan hingga 10 karakter akan menghasilkan hasil yang sama seperti memindai seluruh tabel dari awal hingga akhir.

Indeks komposit

Indeks berdampak besar pada kecepatan eksekusi kueri. Menetapkan kunci unik master saja tidak cukup - kunci komposit adalah kasus penggunaan nyata dalam penyetelan MySQL, yang terkadang memerlukan beberapa pemeriksaan A/B menggunakan EXPLAIN.

Misalnya, jika kita perlu mereferensikan dua kolom dalam kondisi klausa WHERE, kunci komposit akan menjadi solusi ideal.

Mysql> BUAT INDEX idx_composite PADA pengguna (nama pengguna, aktif); Setelah kita membuat kunci berdasarkan kolom nama pengguna, yang menyimpan nama pengguna dan kolom aktif bertipe ENUM, yang menentukan apakah akunnya aktif. Sekarang semuanya dioptimalkan untuk query yang akan menggunakan WHERE untuk menemukan nama pengguna yang valid dengan akun aktif (aktif = 1).

Seberapa cepat MySQL Anda?

Mari aktifkan pembuatan profil untuk melihat lebih dekat kueri MySQL. Ini dapat dilakukan dengan menjalankan perintah set profiling=1, setelah itu Anda perlu menjalankan show profiles untuk melihat hasilnya.

Jika Anda menggunakan PDO, jalankan kode berikut:

$db->query("setel pembuatan profil=1"); $db->query("pilih judul, isi, tag dari postingan"); $rs = $db->query("tampilkan profil"); $db->query("setel pembuatan profil=0"); // menonaktifkan pembuatan profil setelah eksekusi kueri $records = $rs->fetchAll(PDO::FETCH_ASSOC); // dapatkan hasil pembuatan profil $errmsg = $rs->errorInfo(); //Menangkap beberapa kesalahan di sini Hal yang sama dapat dilakukan dengan menggunakan mysqli:

$db = mysqli baru($host,$nama pengguna,$kata sandi,$namadb);

$db->query("setel pembuatan profil=1"); $db->query("pilih judul, isi, tag dari postingan"); if ($result = $db->query("SHOW profiles", MYSQLI_USE_RESULT)) ( while ($row = $result->fetch_row()) ( var_dump($row); ) $result->close(); ) if ($result = $db->query("tampilkan profil untuk kueri 1", MYSQLI_USE_RESULT)) ( while ($row = $result->fetch_row()) ( var_dump($row); ) $result->close( ); ) $db->query("setel pembuatan profil=0"); Ini akan mengembalikan Anda data yang diprofilkan yang berisi waktu eksekusi kueri di elemen kedua array asosiatif.

Array(3) ( => string(1) "1" => string(10) "0.00024300" => string(17) "pilih judul, isi, tag dari kiriman" ) Kueri ini membutuhkan waktu 0,00024300 detik untuk diselesaikan. Ini cukup cepat, jadi jangan khawatir. Namun ketika jumlahnya semakin besar, kita harus melihat lebih dalam. Buka aplikasi Anda untuk berlatih dengan contoh kerja. Periksa konstanta DEBUG dalam konfigurasi database Anda, lalu mulailah menjelajahi sistem dengan mengaktifkan keluaran pembuatan profil menggunakan fungsi var_dump atau print_r. Dengan cara ini Anda dapat berpindah dari halaman ke halaman dalam aplikasi Anda, mendapatkan pembuatan profil sistem yang nyaman.

Untuk melakukan audit penuh atas permintaan Anda, aktifkan logging. Beberapa pengembang situs web khawatir bahwa logging akan berdampak besar pada eksekusi dan semakin memperlambat permintaan. Namun, praktik menunjukkan bahwa perbedaannya tidak signifikan.

Untuk mengaktifkan logging di MySQL 5.1.6 gunakan variabel global log_slow_queries, Anda juga dapat menandai file untuk logging menggunakan variabel slow_query_log_file. Hal ini dapat dilakukan dengan menjalankan query berikut:

Tetapkan global log_slow_queries = 1; atur global slow_query_log_file = /dev/slow_query.log; Anda juga dapat menentukannya di file konfigurasi /etc/my.cnf atau my.ini di server Anda.

Setelah melakukan perubahan, jangan lupa untuk me-restart server MySQL dengan perintah yang diperlukan, misalnya service mysql restart jika Anda menggunakan Linux.

Dalam versi MySQL setelah 5.6.1, variabel log_slow_queries tidak digunakan lagi dan slow_query_log digunakan sebagai gantinya. Selain itu, untuk proses debug yang lebih nyaman, Anda dapat mengaktifkan keluaran tabel dengan menyetel variabel log_output ke TABLE, namun fungsi ini hanya tersedia sejak MySQL 5.6.1.

Log_output = TABEL; log_queries_not_using_indexes = 1; waktu_kueri_panjang = 1; Variabel long_query_time menentukan jumlah detik setelah kueri dianggap lambat. Nilainya adalah 10 dan nilai minimumnya adalah 0. Anda juga dapat menentukan milidetik menggunakan pecahan; sekarang saya menunjukkan satu detik. Dan sekarang setiap permintaan yang akan dieksekusi lebih dari 1 detik dicatat dalam log di tabel.

Pencatatan log akan dilakukan di tabel mysql.slow_log dan mysql.general_log di database MySQL Anda. Untuk mematikan logging, ubah log_output menjadi NONE.

Masuk ke server produksi

Pada server produksi yang melayani klien, sebaiknya gunakan logging hanya untuk jangka waktu singkat dan pantau bebannya agar tidak menimbulkan beban yang tidak perlu. Jika layanan Anda kelebihan beban dan memerlukan perhatian segera, coba isolasi masalahnya dengan menjalankan SHOW PROCESSLIST, atau akses tabel information_schema.PROCESSLIST dengan menjalankan SELECT * FROM information_schema.PROCESSLIST;.

Mencatat semua permintaan di server produksi dapat memberi Anda banyak informasi dan merupakan alat yang baik untuk tujuan penelitian saat meninjau proyek, namun pencatatan dalam jangka waktu lama tidak akan memberi Anda banyak informasi berguna dibandingkan dengan pencatatan dalam jangka waktu hingga 48 jam (cobalah memantau beban puncak agar memiliki kesempatan untuk mengeksplorasi eksekusi kueri dengan lebih baik).

Catatan: Jika Anda memiliki situs yang mengalami gelombang lalu lintas dan terkadang hampir tidak ada lalu lintas, seperti situs olahraga saat musim sepi, gunakan informasi ini untuk membuat dan mempelajari logging.

Mencatat beberapa permintaan

Penting untuk tidak hanya menyadari kueri yang membutuhkan waktu lebih dari satu detik untuk dieksekusi, namun Anda juga perlu menyadari kueri yang dieksekusi ratusan kali. Bahkan jika kueri dijalankan dengan cepat, dalam sistem yang sibuk, kueri tersebut dapat menghabiskan semua sumber daya.

Inilah sebabnya mengapa Anda harus selalu waspada setelah melakukan perubahan dalam proyek langsung - ini adalah waktu paling kritis untuk pengoperasian database apa pun.

Cache panas dan dingin

Jumlah permintaan dan beban server mempunyai dampak yang kuat terhadap eksekusi dan juga dapat mempengaruhi waktu eksekusi permintaan. Saat mengembangkan, Anda harus membuat aturan bahwa setiap permintaan tidak boleh lebih dari sepersekian milidetik (0,0xx atau lebih cepat) untuk diselesaikan di server gratis.

Penggunaan Memcache memiliki pengaruh yang kuat pada beban server dan akan membebaskan sumber daya yang menjalankan permintaan. Pastikan Anda menggunakan Memcached secara efektif dan telah menguji aplikasi Anda dengan cache panas (data yang dimuat) dan cache dingin.

Untuk menghindari berjalan di server produksi dengan cache kosong, sebaiknya miliki skrip yang mengumpulkan semua cache yang diperlukan sebelum memulai server, sehingga masuknya klien dalam jumlah besar tidak mengurangi waktu boot sistem.

Memperbaiki Kueri yang Lambat

Sekarang setelah logging disiapkan, Anda mungkin menemukan beberapa pertanyaan lambat di situs Anda. Mari kita perbaiki! Sebagai contoh, saya akan menunjukkan beberapa masalah umum, dan Anda dapat melihat logika untuk memperbaikinya.

Jika Anda belum menemukan kueri yang lambat, periksa pengaturan long_query_time jika Anda menggunakan metode logging ini. Jika tidak, setelah memeriksa semua kueri pembuatan profil Anda (setel profiling=1), buatlah daftar kueri yang memerlukan waktu lebih dari sepersekian milidetik (0,000x detik) dan mulai dari sana.

Masalah umum

Berikut adalah enam masalah paling umum yang saya temukan saat mengoptimalkan kueri MySQL:

ORDER BY dan pengurutan file

Mencegah pengurutan file terkadang tidak dapat dilakukan karena klausa ORDER BY. Untuk pengoptimalan, simpan hasilnya di Memcache, atau lakukan pengurutan dalam logika aplikasi Anda.

Menggunakan ORDER BY dengan WHERE dan LEFT JOIN

ORDER BY membuat kueri menjadi sangat lambat. Jika memungkinkan, usahakan untuk tidak menggunakan ORDER BY. Jika Anda perlu menyortir, gunakan pengurutan berdasarkan indeks.

Menggunakan ORDER BY pada kolom sementara

Jangan lakukan itu. Jika Anda perlu menggabungkan hasil, lakukan itu dalam logika aplikasi Anda; Jangan gunakan pemfilteran atau pengurutan pada tabel sementara kueri MySQL. Ini membutuhkan banyak sumber daya.

Mengabaikan indeks FULLTEXT

Menggunakan LIKE adalah cara terbaik untuk memperlambat pencarian teks lengkap.

Memilih baris dalam jumlah besar secara tidak perlu

Melupakan LIMIT dalam kueri Anda dapat sangat meningkatkan waktu yang diperlukan untuk mengambil dari database, bergantung pada ukuran tabel.

Penggunaan JOIN yang berlebihan dibandingkan membuat tabel atau tampilan gabungan

Saat Anda menggunakan lebih dari tiga atau empat operator LEFT JOIN dalam satu kueri, tanyakan pada diri Anda: apakah semuanya benar di sini? Lanjutkan kecuali Anda memiliki alasan bagus, misalnya - kueri tidak sering digunakan untuk keluaran di panel admin, atau hasil keluaran dapat di-cache. Jika Anda perlu melakukan kueri dengan sejumlah besar operasi penggabungan tabel, lebih baik pertimbangkan untuk membuat tabel komposit dari kolom yang diperlukan atau menggunakan tampilan.

Jadi

Kami membahas dasar-dasar pengoptimalan dan alat yang diperlukan untuk menyelesaikan pekerjaan. Kami memeriksa sistem menggunakan profiling dan pernyataan EXPLAIN untuk melihat apa yang terjadi dengan database dan bagaimana kami dapat meningkatkan desainnya.

Kami juga melihat beberapa contoh dan kendala klasik yang mungkin Anda alami saat menggunakan MySQL. Dengan menggunakan petunjuk indeks, kita dapat memastikan bahwa MySQL akan memilih indeks yang diperlukan, terutama ketika beberapa pilihan dibuat pada tabel yang sama. Untuk terus mempelajari topik ini, saya menyarankan Anda untuk melihat proyek Percona.

Setelah menjelajahi Internet di waktu luang, saya terkejut karena tidak ada artikel panduan khusus tentang cara mengoptimalkan kueri SQL. Setelah membaca berbagai informasi dan buku, saya akan mencoba memberikan beberapa panduan yang akan membantu Anda mempelajari cara menulis pertanyaan yang benar.

1. Optimalisasi tabel. Hal ini diperlukan ketika banyak perubahan telah dilakukan pada tabel: sebagian besar data telah dihapus, atau banyak perubahan telah dilakukan pada string dengan panjang variabel - teks, varchar, blob. Intinya adalah bahwa catatan yang dihapus tetap disimpan dalam file indeks, dan ketika catatan baru kemudian disisipkan, posisi catatan lama digunakan. Untuk mendefrag file data, gunakan perintah OPTIMIZE.

   OPTIMALKAN TABEL `tabel1`, `tabel2`…

   Jangan lupa bahwa selama optimasi, akses ke tabel diblokir.

Membangun kembali data dalam sebuah tabel. Setelah sering terjadi perubahan pada tabel, perintah ini dapat meningkatkan kinerja data. Ini mengatur ulang mereka dalam tabel dan mengurutkannya berdasarkan bidang tertentu.

ALTER TABLE `table1` ORDER BY `id`

Tipe data. Yang terbaik adalah tidak mengindeks bidang yang bertipe string, terutama bidang TEKS. Untuk tabel yang datanya sering berubah, disarankan untuk menghindari penggunaan bidang VARCHAR dan BLOB, karena jenis ini menciptakan panjang baris dinamis, sehingga meningkatkan waktu akses data. Dalam hal ini, disarankan untuk menggunakan bidang VARCHAR daripada TEXT, karena bekerja dengannya lebih cepat.

BUKAN NULL dan bidang default. Yang terbaik adalah menandai bidang sebagai NOT NULL, karena ini menghemat ruang dan menghilangkan pemeriksaan yang tidak perlu. Dalam hal ini, ada baiknya mengatur nilai default bidang dan memasukkan data baru hanya jika berbeda. Ini akan mempercepat penambahan data dan mengurangi waktu yang dihabiskan untuk menganalisis tabel. Dan perlu diingat bahwa tipe bidang BLOB dan TEXT tidak boleh berisi nilai default.

Koneksi permanen ke server database. Memungkinkan Anda menghindari membuang-buang waktu untuk menyambung kembali. Namun, perlu diingat bahwa server mungkin memiliki batasan jumlah koneksi, dan jika lalu lintas situs sangat tinggi, koneksi yang konstan dapat menimbulkan lelucon yang kejam.

Pemisahan data. Disarankan untuk memisahkan bidang non-kunci yang panjang ke dalam tabel terpisah jika tabel sumber digunakan untuk terus-menerus mengambil data dan sering berubah. Metode ini akan mengurangi ukuran bagian variabel dalam tabel, sehingga mengurangi pencarian informasi.
Hal ini terutama berlaku dalam kasus di mana sebagian informasi dalam tabel dimaksudkan untuk hanya dibaca, dan bagian lainnya dimaksudkan tidak hanya untuk dibaca, tetapi juga untuk modifikasi (jangan lupa bahwa saat menulis informasi, seluruh tabel terkunci. ). Contoh yang mencolok adalah konter kunjungan.
Ada tabel (nama depan) dengan bidang id, konten, acara. Yang pertama adalah kunci dengan kenaikan_otomatis, yang kedua adalah teks, dan yang ketiga adalah numerik - ini menghitung jumlah tayangan. Setiap kali Anda memuat laman, +1 ditambahkan ke bidang terakhir. Mari kita pisahkan bidang terakhir ke dalam tabel kedua. Jadi, tabel pertama (pertama) akan memiliki kolom id dan konten, dan tabel kedua (kedua) akan memiliki kolom show dan first_id. Bidang pertama jelas, menurut saya bidang kedua juga - referensi ke bidang id kunci dari tabel pertama.
Sekarang pembaruan terus-menerus akan terjadi di tabel kedua. Pada saat yang sama, lebih baik mengubah jumlah kunjungan bukan secara terprogram, tetapi melalui permintaan:

Dan pemilihan akan dilakukan menggunakan kueri yang rumit, tetapi satu atau dua tidak diperlukan:

PILIH first.id, first.content, second.first_id, second.shows DARI INNER kedua GABUNG first ON (first.id = second.first_id)

Perlu diingat bahwa semua ini tidak relevan untuk situs dengan lalu lintas rendah dan sedikit informasi.

Nama bidang, yang dengannya, misalnya, dua tabel ditautkan, sebaiknya keduanya memiliki nama yang sama. Kemudian secara bersamaan memperoleh informasi dari tabel yang berbeda melalui satu query akan lebih cepat. Misalnya, dari paragraf sebelumnya, diinginkan bahwa pada tabel kedua, bidang tersebut memiliki nama bukan id_pertama, tetapi hanya id, mirip dengan tabel pertama. Namun, dengan nama yang sama, secara lahiriah tidak begitu jelas apa, di mana dan bagaimana. Oleh karena itu, nasihat ini bukan untuk semua orang.

Membutuhkan lebih sedikit data. Jika memungkinkan, hindari pertanyaan seperti:

PILIH * DARI `tabel1`

Kueri ini tidak efisien karena kemungkinan besar menghasilkan lebih banyak data daripada yang dibutuhkan agar dapat berfungsi. Pilihan yang lebih baik adalah desainnya:

PILIH id, nama DARI tabel1 ORDER BY id LIMIT 25

Saya akan segera menambahkan tentang perlunya menggunakan LIMIT. Perintah ini membatasi jumlah baris yang dikembalikan oleh kueri. Artinya, query menjadi “lebih mudah” dan lebih produktif.
Jika LIMIT disetel ke 10, maka setelah menerima sepuluh baris, permintaan dibatalkan.
Jika kueri menggunakan pengurutan ORDER BY, kueri tersebut tidak muncul pada seluruh tabel, namun hanya pada pilihan.
Jika LIMIT digunakan bersama dengan DISTINCT, kueri akan dibatalkan setelah jumlah baris unik yang ditentukan ditemukan.
Jika Anda menggunakan LIMIT 0, nilai kosong akan dikembalikan (terkadang diperlukan untuk menentukan jenis bidang atau sekadar memeriksa pengoperasian permintaan).

Batasi penggunaan DISTINCT. Perintah ini menghilangkan baris duplikat pada hasilnya. Perintah ini memerlukan peningkatan waktu pemrosesan. Paling baik dikombinasikan dengan LIMIT.
Ada sedikit trik. Jika Anda perlu melihat dua tabel untuk mencari kecocokan, perintah di atas akan berhenti segera setelah kecocokan pertama ditemukan.

Batasi penggunaan SELECT untuk tabel yang terus berubah.

2. Jangan lupa tentang tabel sementara HEAP. Meskipun tabel memiliki keterbatasan, akan lebih mudah untuk menyimpan data perantara di dalamnya, terutama ketika Anda perlu membuat pilihan lain dari tabel tanpa mengaksesnya kembali. Faktanya adalah tabel ini disimpan dalam memori dan oleh karena itu akses ke sana sangat cepat.
3. Cari berdasarkan pola. Tergantung pada ukuran bidang dan jika Anda mengurangi ukuran dari 400 byte menjadi 300, waktu pencarian akan berkurang sebesar 25%.

Penggunaan database sangat memudahkan kehidupan dan pekerjaan seseorang dengan data, memungkinkan mereka dengan cepat memperoleh informasi yang diperlukan dari database atau menuliskannya ke dalamnya. Namun, bekerja dengan data memerlukan pendekatan yang tepat; programmer harus mempertimbangkan beberapa aspek interaksi dengan database. Secara khusus, kita berbicara tentang MySQL. Selanjutnya, mari kita lihat ringkasan tips untuk mengoptimalkan interaksi dengan database MySQL.

Jadikan Kueri MySQL Ramah Caching

Mekanisme cache kueri bawaan di server MySQL dapat meningkatkan kinerja secara signifikan. Sebagian besar server database MySQL menyertakan mekanisme caching. Banyaknya kueri identik ke database dalam waktu singkat dapat menyebabkan penurunan kinerja yang signifikan; mekanisme caching mampu menyimpan kueri tersebut dalam cache, mengembalikan data dari cache. Ada kueri yang tidak dapat di-cache oleh MySQL, dan disarankan untuk melakukan kueri ini sedikit berbeda.

// MySQL tidak akan dapat menyimpan kueri ini dalam cache $res = mysql_query("PILIH nama pengguna DARI pengguna WHERE tanggal_pendaftaran >= CURDATE()"); // Anda dapat melakukannya secara berbeda $today = date("Y-m-d"); $res = mysql_query("PILIH nama pengguna DARI pengguna WHERE tanggal_pendaftaran >= "$hari ini"");

Faktanya adalah bahwa permintaan pertama menggunakan fungsi CURDATE(); kekhasan operasinya tidak memungkinkan penempatan hasil kueri ke dalam cache. Nilai tanggal dapat ditulis sebelumnya ke dalam string kueri, ini akan menghilangkan penggunaan fungsi CURDATE() dalam kueri.
Secara analogi, ada fungsi lain yang tidak di-cache oleh server MySQL itu sendiri, termasuk RAND(), NOW(), serta fungsi lain yang hasilnya non-deterministik.

Lihat bagaimana kueri Anda berjalan menggunakan sintaks EXPLAIN

Anda dapat melihat bagaimana MySQL menjalankan kueri Anda menggunakan sintaks EXPLAIN. Penggunaannya dapat membantu mengidentifikasi kelemahan dalam kinerja kueri, serta struktur tabel. Sebagai hasil kueri, EXPLAIN akan mengembalikan data yang menunjukkan indeks apa yang digunakan, bagaimana data dipilih dari tabel, bagaimana data diurutkan, dll. Untuk melakukan ini, cukup tambahkan kata kunci EXPLAIN di awal query SELECT, setelah itu tabel dengan data akan ditampilkan.

Saat Anda membutuhkan satu catatan, tetapkan LIMIT 1

Ada banyak kasus ketika Anda perlu memeriksa keberadaan setidaknya satu catatan dari sebuah tabel; dalam hal ini, disarankan untuk menambahkan parameter LIMIT 1 ke kueri. Ini akan membuatnya lebih optimal, karena Mesin database akan berhenti mengambil data setelah menemukan record pertama, bukan mengambil semua data. Anda menghemat sumber daya.

// menanyakan kota dengan kode Shymkent dari database $res = mysql_query("SELECT * FROM location WHERE city = "Shymkent""); if (mysql_num_rows($res) > 0)( ) // tambahkan LIMIT 1 untuk mengoptimalkan kueri $res = mysql_query("SELECT * FROM location WHERE city = "Shymkent" LIMIT 1"); jika (mysql_num_rows($res) > 0)( )

Indeks bidang yang Anda cari

Dalam kasus tertentu, indeks berarti indeks bidang yang Anda cari, ini akan meningkatkan kecepatan pencarian. Omong-omong, indeks reguler tidak dapat bekerja dengan kondisi dalam bentuk ekspresi reguler:

// kota indeks LIKE 'shym%' akan berfungsi di sini // indeks tidak akan digunakan di sini kota LIKE '%shymkent%'

Untuk membuat indeks untuk kondisi dengan ekspresi reguler, Anda harus menggunakan , atau memikirkan sistem indeks Anda sendiri.

Indeks bidang yang digunakan untuk menggabungkan tabel

Jika Anda menggunakan beberapa gabungan tabel, Anda mungkin ingin mempertimbangkan untuk memastikan bahwa bidang yang terlibat dalam gabungan tersebut diindeks di kedua tabel. Hal ini mempengaruhi bagaimana MySQL akan melakukan optimasi internal gabungan bidang tabel. Bidang gabungan harus memiliki jenis dan pengkodean yang sama. Itu. misalnya, jika satu bidang bertipe DECIMAL dan bidang lainnya bertipe INT, maka MySQL tidak akan dapat menggunakan indeks tersebut.

Temukan alternatif selain ORDER BY RAND()

Menggunakan pengurutan acak memang sangat mudah, dan banyak programmer pemula memiliki pendapat yang sama tentang hal ini. Namun, ada kendala di sini, dan yang sangat signifikan; menggunakan metode pengambilan sampel serupa dalam kueri Anda, Anda meninggalkan hambatan dalam kinerja. Di sini disarankan untuk menggunakan kode tambahan daripada menggunakan ORDER BY RAND(), sebagai alternatif, untuk menghilangkan hambatan kinerja yang akan mengingatkan dirinya sendiri seiring dengan peningkatan volume data.

Gunakan memilih bidang tertentu alih-alih SELECT *

Jangan malas untuk menunjukkan bidang spesifik yang diperlukan dalam kueri saat memilih, daripada menggunakan "*" - memilih semua bidang, faktanya semakin banyak data yang dibaca dari tabel, semakin lambat kueri Anda.

Tambahkan bidang ID untuk semua tabel

Setiap tabel dalam eksekusi yang baik harus memiliki field id bertipe INT, yang merupakan kunci utama (PRIMARY_KEY), dan AUTO_INCREMENT. Selain itu, Anda harus menentukan parameter UNSIGNED untuk bidang tersebut, yang berarti nilainya akan selalu positif.
MySQL memiliki operasi internal yang dapat menggunakan kunci utama, ini berguna untuk konfigurasi database yang kompleks seperti cluster, paralelisasi, dll.
Selain itu, jika ada beberapa tabel dan Anda perlu melakukan kueri gabungan, maka ID tabel akan berguna.

ENUM sebagai alternatif untuk VARCHAR

Bayangkan Anda ingin menambahkan bidang ke tabel yang harus berisi sekumpulan nilai tertentu. Secara tradisional, banyak pemrogram menyetel tipe VARCHAR untuk bidang. Namun, ada jenis bidang lain yang jauh lebih cepat dan kompak. Nilai dalam tipe ini disimpan dengan cara yang sama seperti TINYINT, tetapi ditampilkan sebagai tipe string.

Gunakan BUKAN NULL, bukan NULL

Bidang NULL memakan lebih banyak ruang dalam catatan karena kebutuhan untuk menandai nilai NULL. Tabel MyISAM, field NULL disimpan sedemikian rupa sehingga setiap field menempati 1 bit tambahan, yang dibulatkan ke byte terdekat. Jika penggunaan NULL pada suatu field tidak penting, maka disarankan menggunakan NOT NULL.

Gunakan Pernyataan yang Disiapkan

Gunakan tabel statis

Tabel statis adalah tabel biasa dalam database, kecuali setiap bidang dalam tabel memiliki ukuran tetap. Jika tabel memiliki kolom yang panjangnya tidak tetap, misalnya: VARCHAR, TEXT, BLOB, maka tabel tersebut tidak lagi statis dan akan diproses oleh MySQL sedikit berbeda. Tabel statis, atau bisa juga disebut tabel berukuran tetap, bekerja lebih cepat dibandingkan tabel non-statis. Catatan dari tabel tersebut akan dilihat lebih cepat; jika Anda perlu memilih baris yang diinginkan, MySQL akan dengan cepat menghitung posisinya. Jika bidang tidak memiliki ukuran tetap, maka dalam hal ini pencarian dilakukan berdasarkan indeks. Ada keuntungan lain menggunakan tabel statis; faktanya adalah tabel ini lebih mudah di-cache dan juga dapat dipulihkan setelah database crash.

Gunakan pemisahan vertikal

Partisi vertikal – melibatkan pembagian tabel menjadi kolom-kolom untuk meningkatkan kinerja tabel. Misalnya, jika Anda memiliki bidang di tabel Anda yang sangat jarang digunakan, atau bidang dengan panjang variabel, maka bidang tersebut dapat ditempatkan di tabel terpisah, sehingga membongkar tabel, sehingga meningkatkan kecepatan pengerjaannya.

Pisahkan kueri INSERT dan DELETE yang besar

Mengeksekusi kueri semacam ini dalam jumlah besar dapat menyebabkan penguncian tabel, yang mengakibatkan pengoperasian aplikasi secara keseluruhan salah. Permintaan paralel ke server web dapat menghasilkan akses tambahan ke tabel. Jika tabel diblokir oleh permintaan sebelumnya, permintaan berikutnya akan berbaris dalam antrian, dan akibatnya, ini memanifestasikan dirinya dalam bentuk perlambatan situs, atau bahkan server crash.
Jika Anda perlu membuat banyak permintaan, cobalah untuk mengontrolnya dengan mengirimkannya dalam jumlah kecil daripada membuang semuanya ke database. Proses ini mungkin memerlukan waktu lebih lama untuk menyelesaikan permintaan Anda, namun dampaknya terhadap pengguna lain akan lebih kecil.
Contoh:

Sementara (1)( mysql_query("HAPUS DARI log WHERE log_date<= "2015-07-20" LIMIT 1000"); if (mysql_affected_rows() == 0){ // записи удалены успешно break; } usleep(50000); // делаем небольшую паузу }

Cobalah untuk menggunakan bidang kecil

Seperti yang Anda ketahui, data database disimpan di harddisk, hal ini seringkali menjadi salah satu titik lemah dalam sebuah aplikasi web. Faktanya adalah rekaman berukuran kecil lebih disukai, karena... menggunakannya mengurangi beban kerja pada hard drive. Jika Anda yakin bahwa tabel tertentu akan menyimpan beberapa baris, maka solusi rasional adalah menggunakan tipe bidang dengan nilai seminimal mungkin. Misalnya, jika kunci utama bertipe INT, dan Anda hanya akan menyimpan sedikit data di tabel, maka lebih baik membuatnya bertipe MEDIUMINT, SMALLINT, atau bahkan TINYINT.

Pilih jenis tabel yang sesuai dengan tugas Anda

Dua jenis tabel yang banyak dikenal saat ini adalah MyISAM dan InnoDB, masing-masing memiliki kelebihan dan kekurangannya masing-masing. Misalnya MyISAM pandai membaca data dari tabel dalam jumlah banyak, namun lebih lambat saat menulis. Ini juga berkinerja baik pada kueri seperti SELECT COUNT(*).
Mekanisme penyimpanan data InnoDB lebih kompleks daripada MyISAM, namun mendukung penguncian baris, yang merupakan sisi positif dari penskalaan. Oleh karena itu, tidak mungkin untuk mengatakan bahwa yang satu lebih baik dari yang lain, dan itu tidak benar;