Artikel ini mengeksplorasi ide untuk meningkatkan kinerja database MySQL dan memberikan solusi spesifik dari 8 aspek.
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 NULL
Atur jalur data dan tempatkan file data database di direktori NFS bersama (server NAS).
File PID dan innioDB harus ditempatkan di direktori lokal server untuk memulai dan menghentikan layanan secara normal:
1125 vi /etc/ my.cnf
[mysqld]
#Tempat memasang database data
datadir=/data/mysqldata
#Tempat memasang mesin innoDB
innodb_data_home_dir = /usr/local/mysql/data
innodb_log_group_home_dir= /usr/local/mysql/data
innodb_data_file_path=ibdata1: 50M;ibdata2:50M:autoextend
1106 cp ./support-files/mysql.server /etc/rc.d/init.d/
vi /etc/rc.d/init.d/mysql.server
Terkait kompilasi item mulai dari 222 Dua baris, tempatkan file PID di direktori lokal server:
pid_file=/usr/local/mysql/data/mysqlmanager-`/bin/hostname`.pid
server_pid_file=/usr/local/mysql /data/`/bin/ hostname`.pid
menginstal database dasar MySQL:
1123 mount 10.4.66.251:/data /data
1124 mkdir /data/mysqldata
1127 ./scripts/mysql_install_db --user=mysql
1145 chown -R mysql .mysql /data/mysqldata/
jika Jika normal, Anda dapat melihat bahwa mysql dimulai secara normal;
1146 /etc/rc.d/init.d/mysql.server mulai
1146 /etc/rc.d/init.d/mysql.server hentikan
konfigurasi HA ketersediaan tinggi, jangan atur NFS mount dan layanan mysql berjalan secara otomatis saat memulai server;
5. Mengunci tabel
Meskipun transaksi adalah cara yang sangat baik untuk menjaga integritas database, karena eksklusivitasnya, terkadang hal ini memengaruhi integritas database. kinerja database, terutama dalam jangka waktu yang sangat lama pada sistem aplikasi yang 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';
BUKA TABEL
Di sini, kami 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
) JENIS = 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
Pengindeksan dan pencarian teks lengkap didukung 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.
PILIH * DARI pesanan DIMANA TAHUN(Tanggal Pesanan)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01";
Situasi yang sama juga akan terjadi ketika menghitung kolom numerik:
SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<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 query 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.