Memahami eksekusi SQL di MySQL Server


Tulisan ini akan menjadi tulisan paling akhir di tahun 2014. Sudah tiba saatnya untuk mengucapkan selamat tinggal pada 2014 dan bersiap-siap menyongsong 2015. Happy new year! Sembari menunggu tahun baru tiba, saya akan memeriksa slow query log di MySQL Server Server.

MySQL Server selalu mencatat query lambat yang pernah dikerjakannya pada slow query log yang memiliki nama dengan format nama_komputer-slow.log. Sebagai contoh, bila nama komputer saya adalah PC-Snake, maka saya dapat menemukan slow query log di lokasi C:\ProgramData\MySQL\MySQL Server 5.6\data\PC-Snake-slow.log. Lalu, apa kriteria untuk sebuah query yang dianggap lambat sehingga perlu dicatat di slow query log? Hal ini tergantung pada nilai variabel long_query_time yang default-nya adalah 10 detik dan nilai min_examined_row_limit yang default-nya adalah 0. Dengan demikian, secara default, query yang eksekusinya memakan waktu lebih dari 10 detik akan dicatat di slow query log.

Sebagai contoh, saya menemukan sebuah query lambat yang sering terjadi:

SELECT DISTINCT
    produk0_.id AS id1_39_0_,
    daftarstok1_.id AS id1_52_1_,
    listperiod2_.id AS id1_33_2_,
    produk0_.createdBy AS createdB2_39_0_,
    produk0_.createdDate AS createdD3_39_0_,
    produk0_.deleted AS deleted4_39_0_,
    produk0_.hargaDalamKota AS hargaDal5_39_0_,
    produk0_.hargaLuarKota AS hargaLua6_39_0_,
    produk0_.jumlah AS jumlah7_39_0_,
    produk0_.jumlahAkanDikirim AS jumlahAk8_39_0_,
    produk0_.jumlahRetur AS jumlahRe9_39_0_,
    produk0_.jumlahTukar AS jumlahT10_39_0_,
    produk0_.keterangan AS keteran11_39_0_,
    produk0_.levelMinimum AS levelMi12_39_0_,
    produk0_.modifiedBy AS modifie13_39_0_,
    produk0_.modifiedDate AS modifie14_39_0_,
    produk0_.nama AS nama15_39_0_,
    produk0_.poin AS poin16_39_0_,
    produk0_.satuan_id AS satuan_17_39_0_,
    produk0_.supplier_id AS supplie18_39_0_,
    daftarstok1_.jumlah AS jumlah2_52_1_,
    daftarstok1_.createdBy AS createdB3_52_1_,
    daftarstok1_.createdDate AS createdD4_52_1_,
    daftarstok1_.deleted AS deleted5_52_1_,
    daftarstok1_.gudang_id AS gudang_i8_52_1_,
    daftarstok1_.modifiedBy AS modified6_52_1_,
    daftarstok1_.modifiedDate AS modified7_52_1_,
    daftarstok1_.produk_id AS produk_i9_52_1_,
    daftarstok1_.produk_id AS produk_i9_39_0__,
    daftarstok1_.id AS id1_52_0__,
    daftarstok1_.gudang_id AS formula0_0__,
    listperiod2_.arsip AS arsip2_33_2_,
    listperiod2_.jumlah AS jumlah3_33_2_,
    listperiod2_.saldo AS saldo4_33_2_,
    listperiod2_.tanggalMulai AS tanggalM5_33_2_,
    listperiod2_.tanggalSelesai AS tanggalS6_33_2_,
    listperiod2_.createdBy AS createdB7_33_2_,
    listperiod2_.createdDate AS createdD8_33_2_,
    listperiod2_.deleted AS deleted9_33_2_,
    listperiod2_.modifiedBy AS modifie10_33_2_,
    listperiod2_.modifiedDate AS modifie11_33_2_,
    listperiod2_.riwayat_id AS riwayat12_52_1__,
    listperiod2_.id AS id1_33_1__,
    listperiod2_.listPeriodeRiwayat_ORDER AS listPer13_1__
FROM
    Produk produk0_
        LEFT OUTER JOIN
    StokProduk daftarstok1_ ON produk0_.id = daftarstok1_.produk_id
        LEFT OUTER JOIN
    PeriodeItemStok listperiod2_ ON daftarstok1_.id = listperiod2_.riwayat_id
WHERE
    1 = 1
ORDER BY produk0_.nama ASC;

Terlihat kompleks? Tenang saja, saya tidak menulis SQL tersebut secara manual melainkan memakai Hibernate JPA untuk menghasilkan query secara otomatis. Pada domain class, saya memiliki hierarki composition (whole-part relationship) berupa Produk memiliki StokProduk yang selanjutnya memiliki PeriodeItemStok. Karena MySQL Server adalah database relasional yang tidak mendukung composition, maka ia akan diterjemahkan menjadi LEFT JOIN. Ini adalah apa yang disebut sebagai memakai OOP sebagai paradigma tetapi menerapkan murni dalam bentuk relasional (Taniar, Pardede & Rahayu (2005), Composition in Object-Relational Database, http://www.irma-international.org/viewtitle/14285/).

Untuk mendapatkan bayangan bagaimana MySQL Server akan mengerjakan query diatas, saya dapat menggunakan EXPLAIN untuk melihat bagaimana ‘pola pikir’ MySQL dalam menjalankan query. Untuk itu, saya perlu menambahkan EXPLAIN sebelum SELECT seperti pada query berikut ini:

mysql> EXPLAIN SELECT ... /G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: produk0_
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 443
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: daftarstok1_
         type: ref
possible_keys: FK_qdv4fq1uprbpkool88p9y6h44
          key: FK_qdv4fq1uprbpkool88p9y6h44
      key_len: 8
          ref: inventory.produk0_.id
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: listperiod2_
         type: ref
possible_keys: FK_ls70le3nhwwxfw0gudwcpv4l3
          key: FK_ls70le3nhwwxfw0gudwcpv4l3
      key_len: 9
          ref: inventory.daftarstok1_.id
         rows: 1
        Extra: NULL
3 rows in set (0.00 sec)

Bagi yang tidak suka membaca informasi dalam bentuk tabel, MySQL Workbench dapat menyajikan hasil EXPLAIN dalam bentuk visual (secara default) seperti pada gambar berikut ini:

Hasil EXPLAIN dalam bentuk visual

Hasil EXPLAIN dalam bentuk visual

Pada tampilan visual di atas, saya perlu menghindari kotak merah yang disebut Full Table Scan. Ini adalah operasi yang paling berat karena harus mencari isi tabel satu per satu tanpa memakai index sama sekali.

Bila saya membiarkan pointer mouse agak lama di kotak tersebut, saya akan memperoleh informasi lebih lanjut seperti pada gambar berikut ini:

Tooltip yang berisi informasi

Tooltip yang berisi informasi

Full table scan akan semakin sia-sia bila saya melakukan pencarian pada tabel yang memiliki banyak record tetapi tidak membutuhkan seluruh baris yang ada. Pada contoh yang saya peroleh, seluruh 443 record yang dicari di tabel produk akan dikembalikan sehingga kerja keras full table scan tidak akan sia-sia.

Operasi LEFT JOIN sudah memakai index. Hal ini terlihat pada dua kotak hijau bertuliskan Non-Unique Key Lookup. Ini adalah foreign key index yang dihasilkan secara otomatis oleh Hibernate (melalui klausa FOREIGN KEY pada saat CREATE TABLE).

Berikutnya, saya menemukan bahwa SELECT DISTINCT pada dasarnya adalah sebuah operasi yang tidak jauh berbeda dengan GROUP BY. Karena SQL ini melibatkan operasi LEFT JOIN, maka MySQL Server tidak bisa begitu saja memakai index. Untuk itu, ia terpaksa harus membuat internal temporary table di memori. Hal ini terlihat dari tulisan tmp_table di bawah kotak DISTINCT.

Internal temporary table adalah tabel di memori yang berisi hasil proses sementara sebelum hasil akhir diperoleh. Saya bisa melihat berapa banyak jumlah internal temporary table yang sudah dibuat oleh MySQL Server (sejak ia dinyalakan) dengan memberikan perintah SQL berikut ini:

SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

Bila seandainya ukuran internal temporary table mencapai batas tertentu, maka ia akan disimpan ke dalam harddisk dalam bentuk tabel MyISAM. Tentu saja akibatnya adalah query akan menjadi lebih lambat. Untuk melihat jumlah temporay table di memori yang akhirnya disimpan ke harddisk, saya dapat memberikan perintah SQL berikut ini:

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

Kapan MySQL Server menyimpan temporary table ke disk? Hal ini tergantung pada nilai tmp_table_size dan max_heap_table_size. Pada sistem dengan jumlah memori yang berlimpah, meningkatkan kedua variabel tersebut akan mencegah MySQL Server untuk menyimpan temporary table di disk sehingga bisa meningkatkan kinerja bila terdapat banyak query yang mengandung DISTINCT dan GROUP BY.

Pada bagian ORDER, saya menemukan tulisan filesort. Ini adalah algoritma yang akan dipakai oleh MySQL Server bila pengurutan tidak dapat dilakukan melalui index. Seperti yang bisa ditebak, filesort akan lebih lambat dibandingkan dengan pengurutan yang dilakukan dengan menggunakan index.

Lalu apa yang harus saya lakukan untuk mengoptimalkan query ini? Cara yang paling realistis adalah dengan melakukan perubahan pada sisi aplikasi, bukan pada sisi database. Apakah pengguna perlu melihat seluruh produk yang ada setiap kali menampilkan screen produk? Pada banyak kasus, jawabannya adalah ‘tidak’.

Seandainya saya menambahkan kriteria pencarian berdasarkan nama pada query di atas, maka saya akan memperoleh hasil seperti berikut ini:

EXPLAIN SELECT ... 
WHERE produk0_.nama = 'namaprodukdicari' 
ORDER BY produk0_.nama ASC;
Hasil EXPLAIN setelah menambahkan kondisi WHERE

Hasil EXPLAIN setelah menambahkan kondisi WHERE

Terlihat bahwa full table scan masih dilakukan! Hanya saja kini tidak dibutuhkan lagi filesort pada ORDER. Mengapa demikian? Hal ini terjadi karena saya tidak memiliki index untuk kolom nama di tabel produk. Memberikan index pada kolom nama untuk produk adalah sesuatu yang lumrah karena biasanya tabel produk lebih sering dibaca daripada ditulis. Oleh sebab itu, saya segera menambahkan index dengan memberikan perintah berikut ini:

ALTER TABLE produk ADD INDEX idx_nama(nama ASC);

Sekarang, hasil visualisasi EXPLAIN untuk query sebelumnya akan terlihat seperti:

Hasil visualisasi EXPLAIN setelah penambahan index pada kolom nama

Hasil visualisasi EXPLAIN setelah penambahan index pada kolom nama

Ini adalah hasil yang paling optimal karena semuanya berwarna ‘hijau’🙂

Walaupun sempurna dari sisi kinerja, mucul permasalahan baru yang berkaitan dengan produktifitas. Kondisi pencarian yang saya gunakan pada WHERE adalah pencarian sama dengan seperti WHERE produk0_.nama = 'namaprodukdicari'. Ini berarti pengguna harus memasukkan nama produk secara lengkap dan sama persis seperti yang tersimpan di database! Program yang lebih user-friendly seharusnya memungkinkan pencarian berdasarkan bagian dari nama. Oleh sebab itu, saya perlu mengubah kriteria pada query menjadi WHERE produk0_.nama LIKE '%bagian_nama_produk%' seperti:

EXPLAIN SELECT ... 
WHERE produk0_.nama LIKE '%bagian_nama_produk%' 
ORDER BY produk0_.nama ASC;

Hasil EXPLAIN kini tiba-tiba menjadi tidak se-‘hijau’ sebelumnya! Full table scan kembali terjadi lagi. Mengapa demikian? Kriteria seperti LIKE '%abc%' tidak dapat memanfaatkan index biasa dengan baik karena abc bisa ada dimana saja di nama produk.

Sebagai gantinya, saya bisa melakukan pengorbanan dengan menghilangkan tanda persen (%) di awal. Dengan demikian, query akan mencari nama produk yang diawali dengan apa yang diketik oleh pengguna:

EXPLAIN SELECT ... 
WHERE produk0_.nama LIKE 'namadepan_produk%' 
ORDER BY produk0_.nama ASC;

Hasil visualisasi akan terlihat seperti pada gambar berikut ini:

Index yang membantu kondisi LIKE

Index yang membantu kondisi LIKE

Index Range Scan menunjukkan bahwa index dipakai untuk membantu mencari nama produk yang diawali oleh kriteria pencarian. Ini jauh lebih baik daripada full table scan. Bila seandainya saya tetap ingin mengoptimalkan kriteria pencarian seperti nama LIKE '%abc%', maka saya perlu menggunakan index khusus yang disebut sebagai Full-Text Index. Saya juga perlu mengganti kondisi LIKE menjadi seperti MATCH(nama) AGAINST('abc'). Fasilitas yang disebut sebagai Full-Text Searching (FTS) ini tersedia di InnoDB sejak versi 5.6.4.

Perihal Solid Snake
I'm nothing...

2 Responses to Memahami eksekusi SQL di MySQL Server

  1. Ping-balik: Memakai Full-Text Search Di MySQL Server | The Solid Snake

  2. Ping-balik: Memakai Full-Text Search Di MySQL Server | Programming Logic And Technology

Apa komentar Anda?

Please log in using one of these methods to post your comment:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: