Pengoptimalan laju pengambilan sampel data

6.1 Pendahuluan

Sekarang mari beralih dari teori ke praktik.

Kita hidup di dunia nyata dan semua produk perangkat lunak pada akhirnya dibuat untuk orang yang masih hidup. Dan orang-orang yang masih hidup ini sangat terganggu oleh situs yang memuat dengan lambat, dan program yang melambat.

Dan jika kueri basis data memakan waktu lebih dari satu detik, ini tidak dapat diterima . Pengguna tidak akan menggunakan produk yang memiliki halaman/fungsi yang sangat lambat.

Namun seringkali, untuk menampilkan satu halaman, Anda perlu melakukan beberapa lusin kueri ke database. Dan jika dieksekusi secara berurutan, maka Anda tidak lagi memiliki batas kedua, tetapi katakanlah 100 md per permintaan.

Berikut adalah 5 cara teratas pemrogram mempercepat kueri basis data:

1. Menambahkan indeks ke tabel dalam database.
2. Menulis ulang dan mengoptimalkan kueri.
3. Aktifkan (dan konfigurasikan) caching di sisi database.
4. Aktifkan caching di sisi klien.
5. Melakukan denormalisasi basis data.

Anda sudah terbiasa dengan semua hal ini sebagian besar, jadi berikut ini hanya saran praktis.

6.2 Indeks

Bukan rahasia lagi bahwa bekerja dengan database menghabiskan sebagian besar pekerjaan di hampir semua situs. Dan itu bekerja dengan database yang paling sering menjadi penghambat aplikasi web.

Pada artikel ini saya ingin memberikan saran praktis dalam menggunakan MySQL.

Saya akan langsung mengatakan:

• artikel ini ditulis tentang MySQL, meskipun hal-hal umum cenderung benar untuk DBMS apa pun.
• semua yang tertulis di artikel adalah sudut pandang pribadi saya, dan bukan kebenaran tertinggi.
• Nasihat tidak berpura-pura baru dan merupakan hasil generalisasi dari literatur yang dibaca dan pengalaman pribadi.
• dalam kerangka artikel ini, saya tidak akan menyentuh masalah konfigurasi MySQL.

Masalah saat menggunakan MySQL dapat dibagi menjadi tiga kelompok berikut (dalam urutan kepentingan):

1. Non-penggunaan atau penyalahgunaan indeks.
2. Struktur basis data yang salah.
3. Kueri SQL \ suboptimal salah.

Mari kita lihat lebih dekat masing-masing kelompok ini.

Menggunakan Indeks

Tidak menggunakan atau menyalahgunakan indeks adalah hal yang paling sering memperlambat kueri. Bagi mereka yang tidak terbiasa dengan mekanisme cara kerja indeks atau belum membacanya di manual, saya sangat menyarankan Anda untuk membacanya.

Tips untuk menggunakan indeks:

• Anda tidak perlu mengindeks semuanya . Seringkali, tanpa memahami artinya, orang hanya mengindeks semua bidang tabel. Indeks mempercepat pengambilan, tetapi memperlambat penyisipan dan pembaruan baris, sehingga pilihan setiap indeks harus bermakna.
• Salah satu parameter utama yang menjadi ciri indeks adalah selektivitas, yaitu jumlah elemen yang berbeda dalam indeks. Tidak masuk akal untuk mengindeks bidang yang memiliki dua atau tiga kemungkinan nilai. Akan ada sedikit manfaat dari indeks semacam itu.
• Pilihan indeks harus dimulai dengan analisis semua kueri terhadap tabel yang diberikan. Sangat sering, setelah analisis seperti itu, alih-alih tiga atau empat indeks, Anda dapat membuat satu indeks gabungan.
• Saat menggunakan indeks gabungan, urutan bidang dalam indeks sangat penting.
• Jangan lupa tentang menutupi indeks. Jika semua data dalam kueri dapat diambil dari indeks, maka MySQL tidak akan mengakses tabel secara langsung. Permintaan seperti itu akan dieksekusi dengan sangat cepat. Misalnya, untuk kueri SELECT name FROM user WHERE login='test'dengan indeks (login, nama), akses ke tabel tidak diperlukan. Terkadang masuk akal untuk menambahkan bidang tambahan ke indeks gabungan, yang akan membuat indeks menutupi dan mempercepat kueri.
• Untuk indeks baris, seringkali cukup hanya mengindeks sebagian dari baris. Ini dapat secara signifikan mengurangi ukuran indeks.
• Jika %di awal, LIKE(SELECT * FROM table WHERE field LIKE '%test')indeks tidak akan digunakan.
• Indeks FULLTEXT hanya digunakan dengan sintaks MATCH ... AGAINST .

6.3 Struktur basis data

Basis data yang dirancang dengan baik adalah kunci untuk bekerja cepat dan efisien dengan basis data. Di sisi lain, database yang dirancang dengan buruk selalu memusingkan pengembang.

Kiat desain basis data:

1. Gunakan tipe data sekecil mungkin. Semakin besar tipe datanya, semakin besar tabelnya, semakin banyak akses disk yang diperlukan untuk mendapatkan datanya. Gunakan prosedur yang sangat nyaman: SELECT * FROM table_name PROCEDURE ANALYSE();untuk menentukan tipe data seminimal mungkin.
2. Amati bentuk normal selama fase desain. Seringkali pemrogram menggunakan denormalisasi pada tahap ini. Namun, dalam banyak kasus, pada awal proyek, masih jauh dari jelas bagaimana hasilnya. Denormalisasi tabel jauh lebih mudah daripada menderita denormalisasi yang kurang optimal. Dan JOINterkadang ini bekerja lebih cepat daripada tabel yang didenormalisasi secara tidak benar.
3. Jangan gunakan NULLkolom kecuali Anda secara sadar membutuhkannya.

6.4 kueri SQL.

Seringkali ada keinginan untuk menulis ulang semua kueri dalam SQL asli sehingga kueri tersebut secepat mungkin. Jika Anda memutuskan untuk melakukan ini, berikut beberapa tipnya:

1. Hindari permintaan dalam satu lingkaran. SQL adalah bahasa himpunan, dan kueri penulisan harus didekati bukan dalam bahasa fungsi, tetapi dalam bahasa himpunan.
2. Hindari *(tanda bintang) dalam kueri. Jangan ragu untuk mencantumkan dengan tepat bidang yang Anda pilih. Ini akan mengurangi jumlah data yang diambil dan dikirim. Juga, jangan lupa tentang menutupi indeks. Bahkan jika Anda memilih semua bidang dalam tabel, lebih baik untuk mencantumkannya. Pertama , ini meningkatkan keterbacaan kode. Saat menggunakan tanda bintang, tidak mungkin mengetahui bidang apa yang ada di tabel tanpa melihatnya. Kedua , hari ini tabel Anda memiliki lima kolom INT , dan sebulan kemudian ditambahkan satu lagi TEXT dan BLOB , dan asterisk tetap seperti semula.
3. Saat diberi nomor halaman, untuk mendapatkan jumlah total rekaman, gunakan SQL_CALC_FOUND_ROWSdan SELECT FOUND_ROWS();When used SQL_CALC_FOUND_ROWS MySQL, cache jumlah baris yang dipilih (sebelum LIMIT diterapkan), dan saat digunakan, SELECT FOUND_ROWS()hanya kembalikan nilai yang di-cache ini tanpa harus mengeksekusi ulang kueri.
4. Jangan lupa bahwa ada INSERTsintaks untuk banyak sisipan. Satu kueri akan menjalankan urutan besarnya lebih cepat daripada beberapa kueri dalam satu lingkaran.
5. Gunakan LIMITdi mana Anda tidak membutuhkan semua data.
6. Gunakan INSERT… ON DUPLICATE KEY UPDATE…di tempat dan INSERTatau UPDATEsetelah seleksi, dan sering di tempat REPLACE.
7. Jangan lupakan fitur luar biasa ini GROUP_CONCAT. Ini dapat membantu dengan kueri yang kompleks.