Panduan dan Tips Menetapkan Indeks pada Jadual MySQL

Indeks pada Jadual MySQL

MySQL akan membuat carian baris demi baris setiap kali kita membuat carian menggunakan arahan WHERE. Masa untuk carian ini akan menjadi lebih panjang setelah jadual kita menjadi semakin besar dan data semakin banyak.

Fungsi indeks digunakan untuk mengurangkan masa carian dan mengelakkan MySQL daripada membuat carian keseluruh baris dalam jadual (full table scan) kerana ianya akan memakan masa, memori dan pemprosesan yang tinggi.

Indeks akan mengambil data daripada kolum di jadual anda, menyusun semula mengikut susuan huruf atau nombor secara menaik mengikut jenis data.

MySQL akan membuat carian pada indeks terlebih dahulu, kemudian barulah ia mengambil baris yang spesifik pada jadual sebenar. Baris yang lain, diabaikan

Apa tujuan kita meletakkan indeks di jadual MySQL?

• Carian lebih laju
• Baris yang tidak berkenaan, diabaikan
• Jika indeks ada banyak kolum, mana mana satu gabungan kolum dengan kolum pertama boleh dijadikan satu indeks.
• Contoh:

indeks : | col a | col b | col c | col d |

1 : | col a | col b |

2 : | col a | col c |

3 : | col a | col d |

• mySQL JOINS lebih cepat. Lebih efektif jika indeks pada kedua-dua jadual yang melakukan JOINS adalah daripada jenis dan saiz yang sama.
• Pencarian MIN() dan MAX() sesuatu kolum yang lebih cepat menyusun (SORT) dan menumpulkan (GROUP) dengan lebih cepat

Bagaimana cara melakukan Indeks?

Yang ni aku akan unjuk berdasarkan contoh.

• Jika anda menggunakan framework seperti laravel, django atau Ruby on Rails, sila ikuti panduan framework masing-masing untuk meletakkan indeks pada kolum.

Berikut adalah cara-cara dengan menggunakan pernyataan SQL

Katakan kita ada jadual Patients (pesakit):

Jadual patients:

id	mrn	first_name	last_name	nric	town_id
1	A219323	Ahmad	Abu	201125036551	1
2	A219324	Lisa	Ismail	341125036551	2
3	A219325	Karen	Hart	441125036551	1
4	A219326	Ahmad Khairi	Jalil	541125036551	1

CREATE TABLE `patients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mrn` varchar(7) NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `nric` varchar(15) NOT NULL,
  `town_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

Sistem kita akan membuat carian ke atas jadual ini menggunakan SQL berikut:

• carian pesakit menggunakan id
• carian pesakit menggunakan mrn
• carian pesakit menggunakan first_name
• carian pesakit menggunakan nric
• carian pesakit mengikut bandar (untuk statistik)

Setiap pernyataan SQL ini membuat carian menggunakan kolum tunggal, maka kita kena pastikan

 >  1. letak indeks pada kolum yang kita lakukan carian

Carian yang menggunakan kolum id tidak perlu dilakukan apa-apa tindakan kerana telah sebarang carian dengan kueri id = "nilai" adalah khusus kepada baris tersebut sahaja. Ini adalah cara membuat carian yang paling optimum dan terbaik.

Kolum kedua, ketiga, kelima dan keenam masih belum dikalifikasikan sebagai indeks.

Cara untuk menetapkan kolum tersebut sebagai indeks:

CREATE INDEX by_mrn ON patients (`mrn`);
CREATE INDEX by_first_name ON patients (`first_name`);
CREATE INDEX by_nric ON patients (`nric`);
CREATE INDEX by_town_id ON patients (`town_id`);

dengan ini, setiap carian yang menggunakan kolum tersebut akan menjadi lebih cepat dan efisyen.

Bagaimana pula dengan carian banyak kolum?

seperti contoh di atas tu, katakanlah sql nya macam ni:

select * from patients where first_name like ("Muhd%") and town_id = 1

Pernyataan tersebut akan menyenaraikan semua pesakit yang namanya bermula dengan Muhd dan tinggal di bandar 1, katakanlah town_id=1 adalah kota bharu.

Oleh kerana mungkin terdapat ramai pesakit yang bernama tersebut di Kota Bharu, maka carian ini akan menjadi perlahan. Oleh itu, kita boleh gabungkan beberapa kolum untuk menjadi indeks.

DROP INDEX by_town_id ON patients;
CREATE INDEX by_town_id_and_first_name ON patients (town_id, first_name);

atau

DROP INDEX by_first_name ON patients;
CREATE INDEX by_first_name_and_town_id ON patients (first_name, town_id);

Kita boleh buang indeks tunggal pada kolum town_id kerana sebarang indeks gabungan kolum akan turut berfungsi sebagai indek tunggal mengikut kolum yang paling kiri.

Kenapa kita tak buang indeks first_name? Kerana MySQL hanya akan mengenali indeks mengikut nama kolum yang pertama.

Jadi, indeks dibezakan mengikut nama kolum pertama. Jika anda ada banyak indeks pada jadual tapi indeks tersebut bermula dengan kolum yang sama, anda hanya perlu tinggalkan satu sahaja yang dapat merangkumi semua.

Jadual Terhubung (JOINS)

SELECT p.*, t.name
FROM `patients` p 
LEFT JOIN `towns` t on p.town_id = t.id
WHERE p.first_name like ("Ahmad%") and p.town_id = 2

oleh kerana kita telah membuat carian mengikut town_id dalam contoh sebelum ini, kita telahpun meletakkan indeks pada kolum tersebut.

Satu contoh lain penggunaan indeks adalah apabila kita menggabungkan carian dengan jadual lain menggunakan pernyataan JOINS

setiap kolum yang kita gunakan dalam carian JOINS perlulah diindekskan

Jadi, kolum mana biasanya kita indeks?

1. Kolum yang menjadi rujukan kepada jadual lain (biasanya diakhiri oleh _id)
2. kolum untuk carian seperti nama, nostaf, no pendaftaran, no kereta, no pelajar, nama jenama dll.
3. mana-mana kolum yang digunakan dalam URL.

Peningkatan Prestasi Pangkalan Data apabila menggunakan indeks

• menulis (write) menjadi lebih laju
• carian lebih laju
• join lebih laju

Sekian dahulu, semoga bermanfaat

https://gist.github.com/yusdirman/15d892a626a9f91b5ef3a8e9f15ebfb3