Untuk meningkatkan kinerja operasi SELECT atau mempercepat hasil query, Anda bisa menambahkan index pada satu atau lebih kolom yang digunakan dalam query.
Berikut adalah beberapa jenis index pada MySQL yang akan dibahas beserta implementasinya.
Saya asumsikan Anda telah menginstall MySQL di server. Lalu Anda bisa menggunakan database Anda sendiri atau download sample database di GitHub - datacharmer/test_db
Primary Index #
Primary keys adalah jenis index yang menyimpan actual row data secara bersamaan dengan key-nya (disimpan pada leaf).
Contoh sederhana dari penggunaan primary key adalah jika Anda membuat schema tabel dengan awalan kolom seperti id kemudian menambahkan
AUTO_INCREMENT dan Primary key, maka pengambilan data menggunakan WHERE id tersebut akan dieksekusi dengan sangat cepat. Ini dikarenakan MySQL tidak perlu melakukan scan
keseluruhan data dan hanya mengambil dari id yang diinginkan. Ditambah dengan penggunaan AUTO_INCREMENT yang membuat data menjadi terurut.
Untuk implementasinya saya menggunakan tabel employees yang berisi 300024 row dengan schema berikut.
mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
Jika melakukan query menggunakan emp_no sebagai where clause, maka hasil dari query tersebut bisa lebih cepat dikarenakan MySQL tidak perlu melakukan scan pada 300 ribu baris
melainkan hanya mengambil dari emp_no yang diinginkan. Perhatikan pada bagian key dan rows.
mysql> EXPLAIN SELECT * FROM employees WHERE emp_no=15000;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Waktu yang dibutuhan saat menjalankan query sekitar 0.0006 seconds
mysql> SELECT * FROM employees WHERE emp_no=15000;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 15000 | 1959-11-29 | Thanasis | Bahi | F | 1988-03-27 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
Secondary Index #
Berbeda dengan primary index, secondary index tidak menyimpan row, tapi menyimpan primary key pada leaf dan menggunakan primary key sebagai locator data. Jadi ini akan menduplikasi primary key pada setiap secondary index sehingga tidak disarankan apabila primary key adalah varchar yang menyimpan cukup banyak data per row.
Biasanya jenis index ini digunakan karena adanya kebutuhan melakukan query secara terus menerus/cukup sering yang menggunakan spesifik column sebagai filternya.
Untuk implementasinya masih menggunakan tabel employees dengan struktur seperti berikut.
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
Misalnya untuk menjalankan query menggunakan column hire_date sebagai filternya.
mysql> SELECT * FROM employees WHERE hire_date='1991-05-26';
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-------------+--------+------------+
| 20243 | 1953-07-19 | Eberhardt | Danner | F | 1991-05-26 |
| 20555 | 1963-08-04 | Christophe | Valette | F | 1991-05-26 |
| 21084 | 1952-06-19 | Srinidhi | Belinskaya | M | 1991-05-26 |
| 24726 | 1957-05-09 | Rance | Blokdijk | M | 1991-05-26 |
| 27836 | 1957-08-02 | Werner | Thiria | M | 1991-05-26 |
| 45191 | 1964-08-22 | Kazunori | Rothenberg | M | 1991-05-26 |
| 47591 | 1956-01-05 | Rildo | Cummings | M | 1991-05-26 |
...
65 rows in set (0.30 sec)
Bila dicoba explain query, MySQL akan scan keseluruhan rows yang ada di tabel untuk menemukan data yang diinginkan hire_date=1991-05-26
mysql> EXPLAIN SELECT * FROM employees WHERE hire_date='1991-05-26';
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299600 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Karena query tersebut akan selalu digunakan pada aplikasi atau query secara manual, maka perlu ditambahkan index pada column hire_date untuk mempercepat query.
ALTER TABLE employees ADD INDEX `idx_hire_date` (`hire_date`)
Untuk melihat penggunaan space INDEX gunakan perintah.
SELECT database_name, table_name, index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name='size'
AND index_name !='PRIMARY'
AND table_name="employees";
+---------------+------------+---------------+------------+
| database_name | table_name | index_name | size_in_mb |
+---------------+------------+---------------+------------+
| employees | employees | idx_hire_date | 4.52 |
+---------------+------------+---------------+------------+
1 row in set (0.00 sec)
Setelah index ditambahkan, ketika explain query kembali dapat dilihat pada jumlah rows sudah berbeda menandakan MySQL hanya melakukan scan pada 65 rows saja dan tidak
melakukan scan pada keseluruhan data tabel.
mysql> EXPLAIN SELECT * FROM employees WHERE hire_date='1991-05-26';
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ref | idx_hire_date | idx_hire_date | 3 | const | 65 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Waktu yang diperlukan untuk menjalankan query menjadi 0.0008 seconds
mysql> SELECT * FROM employees WHERE hire_date='1991-05-26';
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-------------+--------+------------+
| 20243 | 1953-07-19 | Eberhardt | Danner | F | 1991-05-26 |
| 20555 | 1963-08-04 | Christophe | Valette | F | 1991-05-26 |
| 21084 | 1952-06-19 | Srinidhi | Belinskaya | M | 1991-05-26 |
| 24726 | 1957-05-09 | Rance | Blokdijk | M | 1991-05-26 |
| 27836 | 1957-08-02 | Werner | Thiria | M | 1991-05-26 |
| 45191 | 1964-08-22 | Kazunori | Rothenberg | M | 1991-05-26 |
| 47591 | 1956-01-05 | Rildo | Cummings | M | 1991-05-26 |
...
65 rows in set (0.00 sec)
Composite Index #
Jenis index ini memungkinkan penambahan beberapa column sekaligus sebagai key (up to 16 keys) sehingga sering disebut sebagai Multi-column/Composite/Compound index.
Pada composite index, index key akan di construct berdasarkan gabungan dari column yang digunakan pada tabel tersebut, Misal index yang dibentu dari gabungan (c1,c2,c3) akan
membentuk key c1c2c3. Hal inilah yang menyebabkan pencarian menggunakan index ini harus selalu mengikut sertakan prefix urutan awal/sebelumnya dalam where clause nya.
Contoh jika melakukan pencarian pada index(c1,c2) akan menjadi seperti berikut:
- where col1 = “a” -> using index
- where col2 = “b” -> not using index
- where col1 = “a” and col2 = “b” -> using index
Untuk implementasinya masih menggunakan tabel employees dengan struktur seperti berikut.
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
Buat query untuk memfilter berdasarkan column last_name dan gender.
mysql> EXPLAIN SELECT * FROM `employees` WHERE last_name='Aamodt' AND gender='F';
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299202 | 5.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Dapat diketahui jika pencarian menggunakan column last_name dan gender akan membuat MySQL melakukan scan keseluruhan rows yang ada di tabel.
Kemudian buat Composite Index untuk mempercepat query.
ALTER TABLE employees ADD INDEX `idx_lastname_gender` (last_name,gender);
Jalankan query sebelumnya.
mysql> EXPLAIN SELECT * FROM `employees` WHERE last_name='Aamodt' AND gender='F';
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | ref | idx_lastname_gender | idx_lastname_gender | 67 | const,const | 85 | 100.00 | Using index condition |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
Jumlah rows menjadi lebih sedikit menandakan Index bekerja sehingga memperkecil scope data yang perlu dicari MySQL.
Sesuai cara kerja composite index yang telah dijelaskan, apabila Anda mencoba filter menggunakan last_name sebegai where clause maka Index akan tetap bekerja.
mysql> EXPLAIN SELECT * FROM `employees` WHERE last_name='Aamodt';
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ref | idx_lastname_gender | idx_lastname_gender | 66 | const | 205 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Sedangkan jika menggunakan gender sebagai awalan where clause maka Index tidak akan bekerja yang menyebabkan MySQL perlu scan keseluruhan rows.
mysql> EXPLAIN SELECT * FROM `employees` WHERE gender='F';
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299202 | 50.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
UNIQUE Key Index #
Unique key index cukup berbeda dengan index yang lain. Index jenis ini digunakan untuk menjaga data integrity pada suatu tabel, dengan cara memastikan tidak ada data pada sebuah tabel yang dupicate entry.
Biasanya dengan menambahkan Primary Key pada column, maka Unique akan secara default aktif, Namun Unique key dapat ditambahkan juga di non primary key column.
Untuk implementasinya saya membuat tabel user dengan struktur seperti berikut.
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| user | varchar(64) | NO | | NULL | |
| name | varchar(128) | NO | | NULL | |
| email | varchar(128) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
Lalu Anda ingin user tidak dapat mendaftar 2x dengan akun email yang sama sehingga perlu ditambahkan Unique key.
ALTER TABLE `user` ADD UNIQUE `uniq_email` (`email`);
Kemudian test insert data dengan akun email yang sama beberapa kali.
mysql> INSERT INTO `user` VALUES (NULL, 'jonh', 'jonh', '[email protected]');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `user` VALUES (NULL, 'jonh', 'jonh', '[email protected]');
ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'user.uniq_email'
mysql> INSERT INTO `user` VALUES (NULL, 'jonh', 'jonh', '[email protected]');
ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'user.uniq_email'