1. Pilih atribut bidang yang paling dapat diterapkan.
MySQL dapat mendukung akses data dalam jumlah besar, tetapi secara umum, semakin kecil tabel dalam database, semakin cepat kueri yang dijalankan pada tabel tersebut. Oleh karena itu, saat membuat tabel, untuk mendapatkan performa yang lebih baik, kita dapat mengatur lebar field pada tabel sekecil mungkin. Misalnya, ketika menentukan bidang kode pos, jika Anda menyetelnya ke CHAR(255), itu jelas akan menambah ruang yang tidak perlu ke database. Bahkan menggunakan tipe VARCHAR adalah mubazir, karena CHAR(6) baik-baik saja. Demikian pula, jika memungkinkan, kita harus menggunakan MEDIUMINT daripada BIGIN untuk mendefinisikan bidang bilangan bulat.
Cara lain untuk meningkatkan efisiensi adalah dengan menyetel kolom ke NOT NULL jika memungkinkan, sehingga database tidak perlu membandingkan nilai NULL saat menjalankan kueri di masa mendatang.
Untuk beberapa kolom teks, seperti "provinsi" atau "gender", kita dapat mendefinisikannya sebagai tipe ENUM. Karena di MySQL, tipe ENUM diperlakukan sebagai data numerik, dan data numerik diproses jauh lebih cepat dibandingkan tipe teks. Dengan cara ini, kita dapat meningkatkan kinerja database.
2. Gunakan gabungan (JOIN) alih-alih sub-kueri (Sub-Kueri).
MySQL mendukung sub-kueri SQL mulai dari 4.1. Teknik ini memungkinkan Anda menggunakan pernyataan SELECT untuk membuat satu kolom hasil kueri, lalu menggunakan hasil ini sebagai kondisi filter di kueri lain. Misalnya, jika kita ingin menghapus pelanggan yang tidak memiliki pesanan apa pun di tabel informasi pelanggan dasar, kita dapat menggunakan subkueri untuk terlebih dahulu mengambil ID semua pelanggan yang mengeluarkan pesanan dari tabel informasi penjualan, lalu meneruskan hasilnya ke query utama, seperti gambar dibawah ini :
DELETE FROM customerinfo
DI MANA ID Pelanggan TIDAK di (PILIH ID Pelanggan DARI info penjualan)
Menggunakan subkueri dapat menyelesaikan banyak operasi SQL yang secara logis memerlukan beberapa langkah untuk diselesaikan sekaligus. Ini juga dapat menghindari kunci transaksi atau tabel, dan juga mudah untuk ditulis. Namun, dalam beberapa kasus, subkueri dapat diganti dengan gabungan yang lebih efisien (JOIN).. Misalnya kita ingin mengambil semua pengguna yang tidak memiliki catatan pesanan, kita dapat menggunakan query berikut:
SELECT * FROM customerinfo
DI MANA ID Pelanggan TIDAK ada di (PILIH ID Pelanggan DARI salesinfo)
Jika Anda menggunakan koneksi (JOIN).. untuk menyelesaikan query ini, kecepatannya akan jauh lebih cepat. Apalagi jika ada indeks pada CustomerID di tabel salesinfo, performanya akan lebih baik. Querynya seperti berikut:
SELECT * FROM customerinfo
KIRI GABUNG salesinfoON customerinfo.CustomerID=salesinfo.
ID Pelanggan
DIMANA salesinfo.CustomerID IS koneksi NULL
(JOIN).. Alasan mengapa ini lebih efisien adalah karena MySQL tidak perlu membuat tabel sementara di memori untuk menyelesaikan kueri dua langkah logis ini.
3. Gunakan union (UNION) untuk menggantikan tabel sementara yang dibuat secara manual.
MySQL mendukung query UNION mulai dari versi 4.0, yang dapat menggabungkan dua atau lebih query SELECT yang memerlukan penggunaan tabel sementara menjadi satu query. Ketika sesi query klien berakhir, tabel sementara akan otomatis dihapus untuk memastikan database rapi dan efisien. Saat menggunakan UNION untuk membuat query, kita hanya perlu menggunakan UNION sebagai kata kunci untuk menghubungkan beberapa pernyataan SELECT. Perlu diperhatikan bahwa jumlah field di semua pernyataan SELECT harus sama. Contoh berikut menunjukkan kueri menggunakan UNION.
PILIH Nama, Telepon DARI klien
SERIKAT
PILIH Nama, Tanggal Lahir DARI penulis
SERIKAT
SELECT Nama, Pemasok DARI produk
4. Transaksi
Meskipun kita dapat menggunakan sub-query, koneksi (JOIN) dan gabungan (UNION) untuk membuat berbagai query, tidak semua operasi database dapat dilakukan hanya dengan satu atau dapat diselesaikan hanya dengan satu beberapa pernyataan SQL. Seringkali, serangkaian pernyataan diperlukan untuk menyelesaikan suatu jenis pekerjaan tertentu. Namun dalam kasus ini, ketika pernyataan tertentu dalam blok pernyataan ini dijalankan dengan tidak benar, pengoperasian seluruh blok pernyataan akan menjadi tidak pasti. Bayangkan Anda ingin memasukkan data tertentu ke dalam dua tabel terkait secara bersamaan. Hal ini mungkin terjadi: setelah tabel pertama berhasil diperbarui, situasi yang tidak terduga terjadi di database, menyebabkan operasi di tabel kedua tidak selesai. Dengan cara ini data akan menjadi tidak lengkap bahkan data yang ada di database pun akan musnah. Untuk menghindari situasi ini, Anda harus menggunakan transaksi. Fungsinya adalah: setiap pernyataan di blok pernyataan berhasil atau gagal. Dengan kata lain konsistensi dan integritas data dalam database dapat terjaga. Semuanya dimulai dengan kata kunci BEGIN dan diakhiri dengan kata kunci COMMIT. Jika operasi SQL gagal selama periode ini, perintah ROLLBACK dapat memulihkan database ke keadaan sebelum BEGIN dimulai.
MULAI;
MASUKKAN KE info penjualan SET ID Pelanggan=14;
PERBARUI jumlah SET inventaris=11
WHERE item='buku';
COMMIT;
Peran penting lainnya dari transaksi adalah ketika banyak pengguna menggunakan sumber data yang sama pada saat yang sama, mereka dapat menggunakan metode penguncian database untuk menyediakan metode akses yang aman kepada pengguna, yang dapat memastikan bahwa operasi pengguna tidak diblokir. oleh pengguna lain.
5. Mengunci tabel
Meskipun transaksi adalah cara yang sangat baik untuk menjaga integritas database, karena eksklusivitasnya, transaksi terkadang mempengaruhi kinerja database, terutama dalam sistem aplikasi besar. Karena database akan dikunci selama eksekusi transaksi, permintaan pengguna lain hanya dapat menunggu hingga transaksi berakhir. Jika suatu sistem database hanya digunakan oleh beberapa pengguna maka dampak transaksi tidak akan menjadi masalah yang besar, namun jika ribuan pengguna mengakses suatu sistem database dalam waktu yang bersamaan, seperti mengakses website e-commerce maka akan menimbulkan masalah serius; keterlambatan respons.
Faktanya, dalam beberapa kasus kita bisa mendapatkan performa yang lebih baik dengan mengunci meja. Contoh berikut menggunakan metode tabel kunci untuk menyelesaikan fungsi transaksi pada contoh sebelumnya.
TULIS inventaris TABEL KUNCI
PILIH Kuantitas DARI inventaris
WHEREItem='buku';
...
PERBARUI jumlah SET inventaris=11
WHEREItem='buku';
UNLOCK TABLES
Di sini, kita menggunakan pernyataan SELECT untuk mengambil data awal, dan melalui beberapa perhitungan, menggunakan pernyataan UPDATE untuk memperbarui nilai baru ke dalam tabel. Pernyataan LOCK TABLE yang berisi kata kunci WRITE memastikan bahwa tidak akan ada akses lain ke inventaris untuk disisipkan, diperbarui, atau dihapus sebelum perintah UNLOCK TABLES dijalankan.
6.
Cara penggunaan kunci asing untuk mengunci tabel dapat menjaga integritas data, namun tidak dapat menjamin relevansi data. Saat ini kita bisa menggunakan kunci asing. Misalnya, kunci asing dapat memastikan bahwa setiap catatan penjualan mengarah ke pelanggan yang sudah ada. Di sini, kunci asing dapat memetakan CustomerID di tabel customerinfo ke CustomerID di tabel salesinfo. Catatan apa pun tanpa CustomerID yang valid tidak akan diperbarui atau dimasukkan ke salesinfo.
BUAT TABEL info pelanggan
(
ID Pelanggan INT BUKAN NULL,
KUNCI UTAMA (ID Pelanggan)
) TYPE = INNODB;
BUAT TABEL info penjualan
(
ID Penjualan INT BUKAN NULL,
ID Pelanggan INT BUKAN NULL,
KUNCI UTAMA (ID Pelanggan, ID Penjualan),
REFERENSI KUNCI ASING (ID Pelanggan) info pelanggan
(ID Pelanggan) DI DELETECASCADE
) TYPE = INNODB;
Perhatikan parameter "ON DELETE CASCADE" pada contoh. Parameter ini memastikan bahwa ketika catatan pelanggan di tabel info pelanggan dihapus, semua catatan yang terkait dengan pelanggan di tabel info penjualan juga akan dihapus secara otomatis. Jika Anda ingin menggunakan kunci asing di MySQL, Anda harus ingat untuk mendefinisikan tipe tabel sebagai tipe InnoDB yang aman untuk transaksi saat membuat tabel. Tipe ini bukan tipe default untuk tabel MySQL. Metode yang ditentukan adalah menambahkan TYPE=INNODB ke pernyataan CREATE TABLE. Seperti yang ditunjukkan pada contoh.
7. Menggunakan indeks
adalah metode umum untuk meningkatkan kinerja database. Hal ini memungkinkan server database untuk mengambil baris tertentu lebih cepat dibandingkan tanpa indeks, terutama jika pernyataan kueri berisi MAX(), MIN() dan ORDERBY. peningkatan kinerja lebih jelas. Jadi bidang mana yang harus diindeks? Secara umum, indeks harus dibangun pada bidang yang akan digunakan untuk penilaian JOIN, WHERE dan ORDER BY. Cobalah untuk tidak mengindeks bidang dalam database yang berisi banyak nilai duplikat. Untuk bidang tipe ENUM, sangat mungkin untuk memiliki sejumlah besar nilai duplikat, seperti bidang "provinsi".. di info pelanggan. Membangun indeks pada bidang seperti itu tidak akan membantu, sebaliknya, mungkin saja Mengurangi kinerja basis data. Kita bisa membuat indeks yang sesuai pada saat yang sama saat membuat tabel, atau kita bisa menggunakan ALTER TABLE atau CREATE INDEX untuk membuat indeks nanti. Selain itu, MySQL mendukung pengindeksan dan pencarian teks lengkap mulai versi 3.23.23. Indeks teks lengkap adalah indeks tipe FULLTEXT di MySQL, tetapi hanya dapat digunakan untuk tabel tipe MyISAM. Untuk database yang besar, akan sangat cepat untuk memuat data ke dalam tabel tanpa indeks FULLTEXT dan kemudian menggunakan ALTER TABLE atau CREATE INDEX untuk membuat indeks. Namun jika memuat data ke dalam tabel yang sudah memiliki indeks FULLTEXT, maka proses eksekusinya akan sangat lambat.
8. Pernyataan kueri yang dioptimalkan
Dalam kebanyakan kasus, penggunaan indeks dapat meningkatkan kecepatan kueri, namun jika pernyataan SQL digunakan secara tidak tepat, indeks tidak akan dapat memainkan perannya sebagaimana mestinya. Berikut beberapa aspek yang patut diperhatikan. Pertama, yang terbaik adalah melakukan operasi perbandingan antar bidang dengan tipe yang sama. Sebelum MySQL versi 3.23, ini bahkan merupakan kondisi yang diperlukan. Misalnya, bidang INT yang diindeks tidak dapat dibandingkan dengan bidang BIGINT; tetapi sebagai kasus khusus, ketika bidang tipe CHAR dan bidang tipe VARCHAR memiliki ukuran yang sama, keduanya dapat dibandingkan. Kedua, cobalah untuk tidak menggunakan fungsi untuk beroperasi pada bidang yang diindeks.
Misalnya, saat menggunakan fungsi YEAE() pada bidang tipe DATE, indeks tidak akan berfungsi sebagaimana mestinya. Oleh karena itu, meskipun dua kueri berikut memberikan hasil yang sama, kueri terakhir jauh lebih cepat dibandingkan kueri sebelumnya.
SELECT * FROM order WHERE YEAR(OrderDate)＜2001;
SELECT * FROM order WHEREOrderDate
＜"2001-01-01";
Situasi yang sama juga akan terjadi saat menghitung kolom numerik:
SELECT * FROM inventory WHERE Jumlah/ 7<24;
* FROM inventory WHERE Jumlah<24*7;
Kedua kueri di atas juga memberikan hasil yang sama, tetapi kueri terakhir akan jauh lebih cepat daripada kueri sebelumnya. Ketiga, saat mencari bidang karakter, terkadang kami menggunakan kata kunci LIKE dan wildcard. Meskipun pendekatan ini sederhana, hal ini juga mengorbankan kinerja sistem. Misalnya, kueri berikut akan membandingkan setiap record dalam tabel.
PILIH * DARI buku
Nama WHERE seperti "MySQL%"
Tetapi jika Anda menggunakan kueri berikut, hasil yang dikembalikan sama, tetapi kecepatannya jauh lebih cepat:
SELECT * FROM books
WHERE name>="MySQL" dan name<"MySQM"
Terakhir, Anda harus berhati-hati untuk menghindari membiarkan MySQL melakukan konversi tipe otomatis dalam kueri, karena proses konversi juga akan membuat indeks tidak efektif.