Menu
📱 Lihat versi lengkap (non-AMP)
MariaDB Query Optimization Performance

Optimasi Query MariaDB: Teknik Advanced untuk Performa Maksimal

Editor: Hendra WIjaya
Update: 3 February 2026
Baca: 5 menit

Optimasi Query MariaDB: Teknik Advanced untuk Performa Maksimal

MariaDB membawa banyak optimization dibanding MySQL, terutama dalam query optimizer dan execution engine. Berdasarkan pengalaman mengoptimasi query di production dengan jutaan row, artikel ini membahas teknik-teknik advanced yang spesifik untuk MariaDB.

Keunggulan Query Optimizer MariaDB

MariaDB memiliki beberapa improvement query optimizer:

  • Better cost model
  • Index Condition Pushdown (ICP)
  • Batched Key Access (BKA)
  • Block Nested Loop (BNL) optimization

Teknik Analisis Query

1. Extended EXPLAIN

MariaDB mendukung EXPLAIN yang lebih detail:

-- EXPLAIN dengan output JSON
EXPLAIN FORMAT=JSON 
SELECT o.*, c.name 
FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE o.status = 'completed' 
ORDER BY o.created_at DESC 
LIMIT 100;

2. EXPLAIN ANALYZE (MariaDB 10.1+)

-- Lihat actual execution time
EXPLAIN ANALYZE
SELECT * FROM products 
WHERE category_id = 5 
ORDER BY price ASC;

3. ANALYZE Statement

-- Kumpulkan statistik runtime
ANALYZE SELECT * FROM orders WHERE order_date > '2024-01-01';

Advanced Indexing Strategies

1. Descending Index (MariaDB 10.3+)

-- Index yang optimized untuk ORDER BY ... DESC
CREATE INDEX idx_created_desc ON orders(created_at DESC, customer_id);

-- Query yang ter-optimize
SELECT * FROM orders 
WHERE customer_id = 123 
ORDER BY created_at DESC 
LIMIT 10;

2. Invisible Index (MariaDB 10.3.8+)

-- Buat index tapi jangan pakai dulu (untuk testing)
CREATE INDEX idx_test ON orders(total_amount) INVISIBLE;

-- Test apakah index membantu
SET SESSION optimizer_switch='use_invisible_indexes=on';
EXPLAIN ANALYZE SELECT * FROM orders WHERE total_amount > 1000;

-- Jika membantu, aktifkan
ALTER TABLE orders ALTER INDEX idx_test VISIBLE;

3. Indexed Virtual Columns (MariaDB 5.2+)

-- Buat virtual column dan index
ALTER TABLE orders 
ADD COLUMN order_year INT AS (YEAR(order_date)) PERSISTENT,
ADD INDEX idx_year (order_year);

-- Query yang ter-optimize
SELECT * FROM orders WHERE order_year = 2024;

4. Multi-Source Index

Untuk tabel dengan banyak kolom pencarian:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    category VARCHAR(100),
    price DECIMAL(10,2),
    FULLTEXT INDEX ft_name_desc (name, description),
    INDEX idx_category_price (category, price)
) ENGINE=InnoDB;

Query Rewriting Techniques

1. Materialized Subquery

-- Query lambat dengan subquery
SELECT * FROM orders 
WHERE customer_id IN (
    SELECT id FROM customers WHERE country = 'Indonesia'
);

-- Rewrite dengan JOIN
SELECT o.* FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'Indonesia';

2. UNION Optimization

-- UNION dengan ALL lebih cepat jika tidak ada duplikat
SELECT id, name FROM customers WHERE country = 'Indonesia'
UNION ALL
SELECT id, name FROM customers WHERE country = 'Malaysia';

-- Daripada UNION (yang melakukan DISTINCT)

3. Window Functions (MariaDB 10.2+)

-- Mengganti subquery dengan window function
SELECT 
    id,
    customer_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id) as customer_total,
    RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) as rank
FROM orders
WHERE order_date >= '2024-01-01';

4. Common Table Expressions (CTE)

WITH recent_orders AS (
    SELECT * FROM orders 
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
),
customer_stats AS (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        SUM(total_amount) as total_spent
    FROM recent_orders
    GROUP BY customer_id
)
SELECT c.name, cs.order_count, cs.total_spent
FROM customers c
JOIN customer_stats cs ON c.id = cs.customer_id
WHERE cs.order_count > 5;

Optimasi Join

1. Index Nested Loop Join

-- Pastikan semua kolom join ter-index
CREATE INDEX idx_orders_customer ON orders(customer_id, status, created_at);
CREATE INDEX idx_customers_id ON customers(id);

-- Query ter-optimize
SELECT o.id, o.created_at, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 100;

2. Hash Join (MariaDB 10.4+)

-- Enable hash join untuk large tables
SET optimizer_switch='hash_join=on';

-- Query akan menggunakan hash join jika lebih efisien
SELECT * FROM large_table_1 a
JOIN large_table_2 b ON a.join_col = b.join_col;

Optimasi Aggregation

1. Covering Index untuk GROUP BY

CREATE INDEX idx_category_date_total ON orders(category, order_date, total_amount);

-- Query ter-cover
SELECT 
    category,
    DATE(order_date) as order_day,
    SUM(total_amount) as daily_total,
    COUNT(*) as order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY category, DATE(order_date);

2. Loose Index Scan (MariaDB 10.0+)

-- Untuk MIN/MAX dengan GROUP BY
SELECT category, MIN(price), MAX(price)
FROM products
GROUP BY category;

-- Akan menggunakan loose index scan jika index tersedia
CREATE INDEX idx_category_price ON products(category, price);

Optimasi Pagination

1. Keyset Pagination (Cursor-based)

-- Lebih efisien dari OFFSET untuk dataset besar
-- Page 1
SELECT * FROM orders 
WHERE created_at <= '2024-01-31 23:59:59'
ORDER BY created_at DESC, id DESC 
LIMIT 20;

-- Page 2 (gunakan last record dari page 1)
SELECT * FROM orders 
WHERE created_at < '2024-01-30 14:25:10' 
   OR (created_at = '2024-01-30 14:25:10' AND id < 12345)
ORDER BY created_at DESC, id DESC 
LIMIT 20;

2. Deferred Join untuk Large Offset

-- Untuk offset besar, ambil ID dulu
SELECT o.*
FROM orders o
JOIN (
    SELECT id FROM orders 
    ORDER BY created_at DESC 
    LIMIT 100000, 20
) sub ON o.id = sub.id;

Monitoring dan Profiling

1. Enable Query Profiling

SET profiling = 1;

-- Jalankan query
SELECT * FROM orders WHERE customer_id = 123;

-- Lihat profiling
SHOW PROFILES;

-- Detail profiling
SHOW PROFILE FOR QUERY 1;

2. Performance Schema Queries

-- Query dengan highest latency
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000000 AS avg_latency_sec,
    SUM_ROWS_SENT,
    SUM_ROWS_EXAMINED,
    SUM_CREATED_TMP_TABLES
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

3. Index Usage Statistics

-- Index yang paling efisien
SELECT 
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    SUM_TIMER_WAIT/1000000000 AS total_latency_ms
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

Optimasi Configuration

1. Buffer Pool Size

# my.cnf
[mysqld]
innodb_buffer_pool_size = 4G  # 70-80% dari RAM
innodb_buffer_pool_instances = 4  # Untuk pool > 1G

2. Query Cache (Tidak untuk MariaDB 10.1+, gunakan alternative)

# Gunakan Query Cache Plugin jika perlu
plugin-load-add = query_cache_info.so
query_cache_type = ON
query_cache_size = 256M

3. Thread Pool (MariaDB Enterprise)

thread_handling = pool-of-threads
thread_pool_size = 16

Best Practices Summary

  1. Selalu gunakan EXPLAIN ANALYZE sebelum deploy query baru
  2. Test query dengan production-like data volume
  3. Monitor slow query log secara berkala
  4. Review dan drop index yang tidak digunakan
  5. Gunakan covering index untuk query frequent
  6. Pertimbangkan partitioning untuk tabel > 10 juta rows

Kesimpulan

MariaDB menyediakan tools yang powerful untuk optimasi query. Dari EXPLAIN ANALYZE hingga window functions, Anda memiliki banyak opsi untuk meningkatkan performa. Kunci sukses adalah measurement, optimization, dan monitoring secara berkelanjutan.

Artikel Terkait

Bagikan:

Link Postingan: https://www.tirinfo.com/optimasi-query-mariadb-performa-maksimal/