Optimasi SQL Query: 5 Tips Terbaik untuk Optimasi Query Modern

Ini adalah bagian pertama dari seri artikel optimasi kueri SQL kami. Pada artikel ini, kita akan fokus pada bagaimana mengoptimalkan query MySQL, tetapi konsep yang sama dapat diterapkan ke banyak database relasional lainnya.

Sekarang, lebih dari sebelumnya, insinyur perangkat lunak harus memiliki pengetahuan luas dalam optimasi kueri SQL.
Pergeseran ini terjadi baik pada startup kecil maupun perusahaan besar. Saat ini, pengembanglah yang menulis kueri SQL dan lapisan akses basis data.

Tidak masalah jika Anda menggunakan lapisan abstraksi basis data (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django, atau lainnya) atau menulis kueri SQL asli, pada akhirnya Anda akan ditantang dengan menyetel kueri yang Anda kirim. ke basis data Anda.

Jadi apa yang dapat Anda lakukan untuk mengoptimalkan kueri SQL Anda?

Buat indeks, tetapi lakukan dengan bijak

Pengindeksan mungkin merupakan bagian terpenting dari proses optimasi SQL. Jadi pertama-tama, pastikan Anda terbiasa dengan berbagai aspek yang harus Anda pertimbangkan ketika memilih indeks optimal. untuk database Anda.

Saat memikirkan indeks mana yang akan dibuat, Anda harus memperhatikan dengan seksama klausa WHERE dan tabel BERGABUNG, karena pernyataan-pernyataan itu menyertakan bagian-bagian penting yang dapat diindeks oleh indeks dari kueri.

Juga, kemacetan utama dapat berasal dari bagian GROUP BY dan ORDER BY. Mengatakan bahwa, cegukan potensial adalah bahwa Anda mungkin tidak dapat mengindeksnya dalam beberapa kasus, seperti yang kami jelaskan di sini . Oleh karena itu, Anda mungkin perlu memikirkan kembali desain kueri Anda sebelum membuat indeks, untuk memastikan Anda menulis kueri yang hebat, tetapi juga menulis kueri yang dapat diindeks.

Setelah Anda menemukan indeks untuk satu permintaan, jangan berhenti di situ. Perluas pandangan Anda dan lihat pertanyaan penting lainnya dalam aplikasi Anda. Pastikan Anda menggabungkan indeks bila memungkinkan, dan menghapus indeks yang tidak digunakan. Melihat seluruh ruang lingkup aplikasi akan selalu lebih baik daripada melihat pada ruang lingkup permintaan tunggal.

Anda juga harus ingat bahwa membuat lebih banyak indeks daripada yang Anda butuhkan juga dapat menjadi bumerang bagi Anda, karena mereka dapat memperlambat operasi penulisan (seperti pernyataan INSERT / UPDATE). Jadi buat indeks untuk mengoptimalkan kueri SQL Anda, tetapi lakukan dengan bijak.

Jangan menghalangi indeks

Kami banyak didekati oleh pelanggan yang bertanya kepada kami "mengapa database tidak menggunakan indeks saya?". Nah, itu pertanyaan yang bagus, dengan kemungkinan jawaban yang tak ada habisnya. Namun, dalam artikel ini, kami akan mencoba membahas beberapa skenario umum yang sering kita lihat, jadi semoga saja, mereka akan berguna untuk kasus penggunaan Anda sendiri.

Contoh # 1 - Hindari membungkus kolom yang diindeks dengan fungsi

Pertimbangkan permintaan ini, yang menghitung jumlah hot dog yang dibeli di AS pada tahun 2018. Jika Anda penasaran, 18.000.000.000 hot dog dijual di AS pada tahun 2018.

1 2 3 4 5 6

SELECT     COUNT(*) FROM     us_hotdog_purchases WHERE     YEAR(purchase_time) = ‘2018’

Seperti yang Anda lihat, kami menggunakan fungsi YEAR untuk mengambil bagian tahun dari kolom purchase_time . Panggilan fungsi ini akan mencegah database dari dapat menggunakan indeks untuk pencarian kolom purchase_time , karena kami mengindeks nilai purchase_time , tetapi bukan nilai pengembalian YEAR (purchase_time).

Untuk mengatasi tantangan ini dan menyetel kueri SQL ini, Anda dapat mengindeks hasil fungsi, dengan menggunakan Generated Columns , yang tersedia mulai MySQL 5.7.5.

Solusi lain dapat menemukan cara alternatif untuk menulis permintaan yang sama, tanpa menggunakan pemanggilan fungsi. Dalam contoh ini, kita bisa mengubah kondisi itu menjadi kondisi rentang 2 arah, yang akan mengembalikan hasil yang sama:

1 2 3 4 5 6 7

SELECT     COUNT(*) FROM     us_hotdog_purchases WHERE     purchased_at >= '2018-01-01'         AND purchased_at < '2019-01-01'

Contoh # 2 - hindari kondisi ATAU

Pertimbangkan permintaan ini, yang memilih jumlah posting di Facebook yang diposting setelah malam tahun baru, atau diposting oleh pengguna bernama Mark.

1 2 3 4 5 6 7

SELECT     COUNT(*) FROM     fb_posts WHERE     username = ‘Mark’         OR post_time > ‘2018-01-01’

Memiliki indeks pada kolom nama pengguna dan post_time mungkin terdengar membantu, tetapi dalam kebanyakan kasus, basis data tidak akan menggunakannya, setidaknya tidak secara penuh. Alasannya adalah hubungan antara dua kondisi - operator OR, yang membuat database mengambil hasil dari setiap bagian kondisi secara terpisah.

Cara alternatif untuk melihat kueri ini adalah dengan 'memecah' kondisi ATAU dan 'menggabungkannya' menggunakan klausa UNION. Alternatif ini akan memungkinkan Anda untuk mengindeks setiap kondisi secara terpisah, sehingga basis data akan menggunakan indeks untuk mencari hasil dan kemudian menggabungkan hasilnya dengan klausa UNION.

1 2 3 4 5 6 7

SELECT … FROM … WHERE username = ‘Mark’     UNION SELECT … FROM … WHERE post_time > ‘2018-01-01’

Harap perhatikan bahwa jika Anda tidak keberatan dengan catatan duplikat di set hasil Anda, Anda juga dapat menggunakan UNION ALL (yang akan berkinerja lebih baik daripada UNION DISTINCT default).

Contoh # 3 - Hindari menyortir dengan urutan campuran

Pertimbangkan kueri ini, yang memilih semua posting dari Facebook dan mengurutkannya berdasarkan nama pengguna dalam urutan naik, dan kemudian berdasarkan tanggal posting dalam urutan menurun.

1 2 3 4 5

SELECT     username, post_type FROM     fb_posts ORDER BY username ASC , post_type DESC

MySQL (dan banyak basis data relasional lainnya), tidak dapat menggunakan indeks ketika mengurutkan dengan urutan campuran (baik ASC dan DESC dalam klausa ORDER BY yang sama). Ini berubah dengan merilis fungsionalitas indeks terbalik dan MySQL 8.x.

Jadi apa yang dapat Anda lakukan jika Anda belum meng-upgrade ke versi MySQL terbaru? Pertama, kami sarankan untuk mempertimbangkan kembali jenis pesanan campuran. Apakah Anda benar-benar membutuhkannya? Jika tidak, hindari.

Jadi Anda memutuskan Anda membutuhkannya, atau manajer produk Anda berkata: "Tidak mungkin kami bisa mengelola tanpanya"? Pilihan lain adalah menggunakan kolom Generated (tersedia di MySQL 5.7.5+) untuk membuat kolom terbalik dan mengurutkan pada kolom itu daripada yang asli. Sebagai contoh, anggap Anda mengurutkan pada kolom angka, Anda bisa membuat kolom yang dihasilkan dengan nilai numerik negatif yang berkorelasi dengan angka asli dan mengurutkan pada kolom baru itu dalam urutan yang berlawanan. Dengan begitu, semua kolom akan memiliki urutan pengurutan yang sama dalam klausa ORDER BY, tetapi pengurutan akan terjadi seperti yang awalnya ditentukan oleh persyaratan produk Anda.

Solusi potensial terakhir tidak akan selalu menjadi pilihan, jadi upaya terakhir Anda akan meningkatkan ke versi MySQL terbaru yang mendukung pengurutan pesanan campuran menggunakan indeks.

Contoh # 4 - Hindari kondisi dengan berbagai jenis kolom

Pertimbangkan pertanyaan ini, yang memilih jumlah buah merah di hutan.

1 2 3 4 5 6

SELECT     COUNT(*) FROM     forest WHERE     fruit_color = 5;   /* 5 = red */

Dengan asumsi tipe buah fruit_color kolom adalah VARCHAR, atau hanya apa saja non-numerik, pengindeksan kolom yang tidak akan sangat membantu, karena pemeran implisit yang diperlukan akan mencegah database menggunakan indeks untuk proses penyaringan.

Jadi, bagaimana Anda bisa menyetel kueri SQL ini? Anda memiliki dua opsi untuk mengoptimalkan kueri ini. Yang pertama adalah membandingkan kolom dengan nilai konstan yang cocok dengan tipe kolom, jadi jika itu adalah kolom VARCHAR, bandingkan dengan '5' (dengan tanda kutip tunggal) dan bukan ke 5 (yang merupakan perbandingan numerik yang akan menghasilkan dalam pemeran implisit).

Opsi yang lebih baik adalah menyesuaikan tipe kolom agar sesuai dengan tipe yang paling cocok untuk nilai yang dipegang kolom. Dalam contoh ini, kolom harus diubah ke tipe INT. Harap perhatikan bahwa mengubah jenis kolom bisa menjadi tugas yang rumit, jadi bacalah tentang tantangan tugas itu sebelum menuju ke sana.

Hindari pencarian SEPERTI dengan wildcard awalan

Pertimbangkan kueri ini, yang mencari semua posting Facebook dari nama pengguna yang menyertakan string 'Mar' , jadi kami sedang mencari semua posting yang ditulis oleh pengguna bernama Mark, Marcus , Almar, dll.

1 2 3 4 5 6

SELECT     * FROM     fb_posts WHERE     username LIKE '%Mar%'

Memiliki wildcard '%' di awal pola akan mencegah database menggunakan indeks untuk pencarian kolom ini. Pencarian semacam itu bisa memakan waktu cukup lama ..

Dalam hal ini, ada dua opsi untuk meningkatkan kinerja kueri ini. Yang pertama sepele - pertimbangkan apakah wildcard awalan cukup penting. Jika Anda dapat mengatur tanpanya, singkirkan.

Pilihan lain adalah menggunakan indeks teks lengkap . Harap dicatat, bahwa indeks dan MATCH ini ... TERHADAP sintaksis tidak bebas dari tantangan dan memiliki beberapa perbedaan bila dibandingkan dengan ekspresi LIKE yang familiar di MySQL.

Kesimpulan

Pada bagian pertama dari seri optimasi kueri SQL ini, kami membahas pentingnya pengindeksan yang bijak , kami melewati beberapa contoh kendala yang mungkin terjadi saat menggunakan kolom yang diindeks dalam kueri, dan kami juga merinci beberapa tips dan trik lain yang dapat membantu untuk kueri yang lebih baik kinerja. Sampai jumpa di posting selanjutnya.