Cara menggunakan show index mysql

Salah satu penyebab aplikasi menjadi lambat bisa diakibatkan oleh jumlah record pada database yang membengkak. Jumlah record yang banyak apalagi sudah menyentuh jutaan akan mempengaruhi performa query terutama pada saat pencarian. Penambahan Index pada column MySQL dapat membuat query pencarian menjadi lebih cepat.

Apa itu Index ?

Secara umum, indeks adalah suatu daftar kata – kata yang diurutkan disertai dengan rujukan atau referensi ke halaman yang menyebutkan. Prinsip Index pada MySQL sama persis dengan yang ada pada buku pada umumnya. Index pada MySQL berisi pointer ke data yang berelasi sehingga dapat membuat query pencarian menjadi lebih cepat.

Mempercepat Pencarian Data

Index dapat mempercepat pencarian pada kolom yang digunakan pada klausa WHERE. Query dapat menjadi lebih cepat karena dengan adanya Index MySQL tidak perlu melakukan Full Table Scan atau mebandingkan keseluruhan data untuk menemukan data yang sesuai.

Penambahan index akan sangat terasa manfaatnya pada table memiliki banyak record dan sering dilakukan query pencarian. Pada kasus saya dengan tabel yang memiliki 1.5 juta record, kecepatan query pencarian bisa berubah dari 0.5 detik menjadi 0.0003 detik setelah menambahkan Index pada salah satu kolom nya.

Kolom yang Di Index

Index dapat ditambahkan pada setiap column pada table, namun penggunaan Index yang berlebihan akan berdampak pada performa query saat melakukan INSERT, UPDATE, dan DELETE karena INDEX juga perlu diperbaharui. Oleh sebab itu sangat penting untuk menentukan mana kolom yang dirasa sangat perlu di Index. Biasanya Index ditambahkan pada kolom yang sering dijadikan acuan dalam klausa query WHERE. Contohnya apabila anda memiliki table seperti dibawah ini:

id, kode_pendaftaran, nama, tanggal_lahir, sekolah_asal

Bila sering dilakukan query yang mencari data berdasarkan kode_pendaftaran dan nama, maka tambahkan Index pada kedua kolom tersebut. Sederhananya seperti itu, informasi mengenai jenis – jenis index dapat anda baca selengkapnya pada dokumentasi MySQL: How MySQL Uses Indexes

Untuk membuat Index pada salah satu kolom dapat digunakan query dibawah ini:

ALTER TABLE `nama_tabel` ADD INDEX(`nama_field`)

Sementara untuk menghapus index dapat digunakan query:

ALTER TABLE `nama_tabel` DROP INDEX `nama_field`;

query diatas dapat digunakan untuk Single Column Index yang sederhana, apabila anda membutuhkan Multi-column index dengan konfigurasi lanjutan dapat dilihat pada dokumentasi MySQL : Create Index Statement

apabila anda pengguna phpMyAdmin maka pembuatan index akan lebih mudah dilakukan. Pilih table yang akan ditambahkan index, kemudian klik menu tab Structure, kemudian klik menu Lainnya pada kolom yang akan diberi index.

Menemukan Query yang Lambat

Query yang lambat pada umumnya akan membutuhkan waktu lama untuk selesai. Untuk menemukan query yang lambat anda dapat memeriksa prosess mysql dengan query:

show processlist;

Hasil outputnya kurang lebih seperti dibawah ini:

+-----+------+-----------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
 | Id  | User | Host            | db   | Command | Time | State        | Info                                                                                                 | Progress |
 +-----+------+-----------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
 |  70 | root | localhost       | NULL | Query   |    0 | starting     | show processlist                                                                                     |    0.000 |
 | 359 | root | localhost:50176 | test | Query   |    2 | Sending data | SELECT *  FROM table WHERE code LIKE 'xx' AND deleted_at IS NOT NULL                             |    0.000 |
 | 360 | root | localhost:50177 | NULL | Sleep   |    1 |              | NULL                                                                                                 |    0.000 |
 +-----+------+-----------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
 3 rows in set (0.000 sec)

Cari proses yang memiliki Time paling lama dari hasil query tersebut, kemudian perhatikan di bagian kanan ada Info pendek mengenai query yang sedang dikerjakan. Analisa dengan baik query tersebut sebelum memutuskan untuk menambahkan index.

Indeks database MySQL mengacu pada jenis struktur data yang digunakan sebagai organisasi data dalam database dan untuk membantu memajukan kecepatan berbagai operasi yang dilakukan di MySQL.

Indeks sangat membantu. Tanpa mereka, MySQL harus memindai seluruh tabel untuk menemukan baris dan kolom yang relevan, yang bisa sangat tidak efisien dalam database besar.

Tutorial ini akan fokus pada cara melihat informasi indeks dengan menggunakan klausa SHOW INDEXES di MySQL.

Tampilkan indeks Tabel

Untuk menampilkan informasi indeks pada sebuah tabel, kita menggunakan klausa SHOW INDEXES diikuti dengan nama tabel yang ingin kita dapatkan informasi indeksnya.

Sintaks umum ditampilkan sebagai:

SHOW INDEXES tbl_name;

Misalnya, pertimbangkan salah satu tabel di database sampel Sakila. Kita bisa mendapatkan informasi indeks seperti yang ditunjukkan pada query di bawah ini:

USE sakila;
 
 SHOW INDEXES FROM film;

Query di atas akan menampilkan informasi indeks dari tabel film di database Sakila. Outputnya adalah:

Memahami Informasi Indeks

Perintah SHOW INDEXES menampilkan informasi yang relevan tentang indeks dalam tabel yang ditentukan.

Berikut adalah istilah-istilah berikut dan informasi masing-masing yang diberikan:

1. Table:Ini adalah kolom pertama dari output. Ini menunjukkan nama tabel tempat indeks berada.
2. Non-Unique:Kolom kedua menunjukkan apakah indeks dapat berisi duplikat. Nilainya adalah Boolean, dengan 1 menunjukkan indeks dapat berisi duplikat dan 0 jika sebaliknya.
3. Key_name:Kolom ketiga menunjukkan nama indeks. Dengan konvensi, kunci utama mengambil nama indeks PRIMARY.
4. Seq_in_index: Kolom keempat menampilkan nomor urut kolom dalam indeks mulai dari nilai 1.
5. Column_name: Kolom kelima hanyalah nama kolom.
6. Collation:Kolom keenam adalah bagian yang menunjukkan bagaimana kolom diurutkan dalam indeks. Ada tiga nilai pengurutan, dengan A sebagai urutan Ascending, B menunjukkan urutan Descending, dan NULL sebagai non-urutan.
7. Cardinality:Kolom ketujuh menunjukkan keunikan nilai data. Dalam indeks, ini menunjukkan perkiraan jumlah nilai unik dalam indeks tertentu.
8. Sub_part: Kolom kedelapan menampilkan awalan indeks dengan NULL, menunjukkan seluruh kolom diindeks.
9. Packed: Kolom kesembilan menunjukkan bagaimana kunci indeks dikemas, dengan NULL menunjukkan kunci tidak dikemas.
10. Null:Kolom kesepuluh menentukan apakah kolom dapat berisi nilai NULL. Ya, jika kolom dapat berisi nilai nol, dan kosongkan jika tidak.
11. Index_type: Kolom kesebelas menunjukkan metode indeks seperti BTREE, HASH, RTREE, dan FULLTEXT.
12. Comment: Kolom kedua belas menunjukkan informasi tentang indeks yang tidak dijelaskan dalam kolomnya.
13. Index_comment: Kolom ketiga belas menunjukkan informasi tambahan tentang indeks yang ditentukan menggunakan atribut COMMENT saat dibuat.
14. Visible: Kolom keempat belas adalah indeks yang terlihat oleh pengoptimal kueri, dengan nilai Ya dan Tidak.
15. Expression: Kolom kelima belas ditampilkan jika indeks menggunakan ekspresi dan bukan nilai awalan kolom atau kolom.

PETUNJUK: Informasi tentang indeks dari kueri SHOW INDEXES mirip dengan SQLStatistics.

Tampilkan Indeks Skema

Anda juga bisa mendapatkan informasi indeks tentang skema. Sintaks umum untuk mencapai hasil ini adalah sebagai berikut:

SELECT table_name, index_name FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = “schema_name”;

Perhatikan query di bawah ini yang menampilkan informasi tentang skema Sakila:

SELECT table_name, index_name FROM information_schema.statistics WHERE table_schema = "sakila";

Ini akan menampilkan informasi tentang indeks dalam skema Sakila seperti yang ditunjukkan pada output di bawah ini:

+---------------+-----------------------------+
 
 | TABLE_NAME | INDEX_NAME |
 
 +---------------+-----------------------------+
 
 | actor | PRIMARY |
 
 | actor | idx_actor_last_name |
 
 | address | PRIMARY |
 
 | address | idx_fk_city_id |
 
 | address | idx_location |
 
 | category | PRIMARY |
 
 | city | PRIMARY |
 
 | city | idx_fk_country_id |
 
 | country | PRIMARY |
 
 | customer | PRIMARY |
 
 | customer | idx_fk_store_id |
 
 | customer | idx_fk_address_id |
 
 | customer | idx_last_name |
 
 | film | PRIMARY |
 
 | film | idx_title |
 
 | film | idx_fk_language_id |
 
 | film | idx_fk_original_language_id |
 
 | film_actor | PRIMARY |
 
 | film_actor | PRIMARY |
 
 | film_actor | idx_fk_film_id |
 
 | film_category | PRIMARY |
 
 | film_category | PRIMARY |
 
 | film_category | fk_film_category_category |
 
 | film_text | PRIMARY |
 
 | film_text | idx_title_description |
 
 | film_text | idx_title_description |
 
 | inventory | PRIMARY |
 
 | inventory | idx_fk_film_id |
 
 | inventory | idx_store_id_film_id |
 
 | inventory | idx_store_id_film_id |
 
 |-----------------------------OUTPUT TRUNCATED--------------------------

Anda juga bisa mendapatkan informasi dari semua skema di server menggunakan kueri yang ditunjukkan di bawah ini:

SELECT table_name, index_name FROM information_schema.statistics;

CATATAN : Kueri di atas membuang banyak informasi. Anda jarang perlu mendapatkan indeks dari semua skema. Namun, contoh output di bawah ini:

+--------------------+------------+
 
 | TABLE_NAME | INDEX_NAME |
 
 +--------------------+------------+
 
 | innodb_table_stats | PRIMARY |
 
 | innodb_table_stats | PRIMARY |
 
 | innodb_index_stats | PRIMARY |
 
 | innodb_index_stats | PRIMARY |
 
 | innodb_index_stats | PRIMARY |
 
 +--------------------+------------+

Kesimpulan

Dalam tutorial ini, kita membahas bagaimana menggunakan query MySQL SHOW INDEXES untuk mendapatkan informasi tentang indeks dalam sebuah tabel. Kami juga melihat menggunakan information_schema untuk mendapatkan informasi tentang indeks dari satu atau semua skema di server MySQL.

Apa itu Index di MySQL?

Apa gunanya index pada sebuah tabel?

Bagaimana Index mempengaruhi performa database?

Langkah langkah membuat database di MySQL?