Menu
📱 Lihat versi lengkap (non-AMP)
MySQL Partitioning Performance

MySQL Partitioning: Panduan Lengkap untuk Tabel Besar dengan Performa Optimal

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

MySQL Partitioning: Panduan Lengkap untuk Tabel Besar dengan Performa Optimal

Ketika tabel mencapai jutaan atau bahkan miliaran rows, performa query akan menurun drastis. Partitioning adalah solusi untuk membagi tabel besar menjadi bagian-bagian yang lebih kecil dan manageable. Setelah mengimplementasikan partitioning untuk tabel 500 juta rows, saya akan berbagi pengalaman dan best practices.

Mengapa Partitioning?

Masalah Tabel Besar

  • Query lambat (full table scan)
  • Maintenance berat (backup, optimize)
  • Index maintenance mahal
  • Lock contention tinggi
  • Purging data lama sulit

Benefits Partitioning

  • Query pruning: Hanya scan partition yang relevan
  • Maintenance: Operasi per partition lebih cepat
  • Data lifecycle: Drop/truncate partition mudah
  • Performance: Parallel read untuk queries
  • Storage: Compress/archive partitions lama

Jenis-Jenis Partitioning

1. RANGE Partitioning

Untuk data dengan range values (dates, IDs)

-- Partitioning berdasarkan tahun
CREATE TABLE sales (
    id INT NOT NULL AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p_2022 VALUES LESS THAN (2023),
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Alternative dengan COLUMNS
CREATE TABLE sales_v2 (
    id INT NOT NULL AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE COLUMNS(order_date) (
    PARTITION p_2022 VALUES LESS THAN ('2023-01-01'),
    PARTITION p_2023 VALUES LESS THAN ('2024-01-01'),
    PARTITION p_2024 VALUES LESS THAN ('2025-01-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

2. LIST Partitioning

Untuk data dengan discrete values (regions, categories)

-- Partitioning berdasarkan region
CREATE TABLE orders (
    id INT NOT NULL AUTO_INCREMENT,
    region ENUM('North', 'South', 'East', 'West'),
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, region)
) PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('North'),
    PARTITION p_south VALUES IN ('South'),
    PARTITION p_east VALUES IN ('East'),
    PARTITION p_west VALUES IN ('West')
);

3. HASH Partitioning

Untuk distribusi data yang even (load balancing)

-- Hash partitioning untuk user_id
CREATE TABLE user_activities (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    activity_type VARCHAR(50),
    activity_date TIMESTAMP,
    data JSON,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id)
PARTITIONS 8;

-- Linear hash (untuk partisi lebih fleksibel)
CREATE TABLE user_activities_v2 (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    activity_type VARCHAR(50),
    activity_date TIMESTAMP,
    PRIMARY KEY (id, user_id)
) PARTITION BY LINEAR HASH(user_id)
PARTITIONS 8;

4. KEY Partitioning

Mirip hash tapi menggunakan internal MySQL hashing function

CREATE TABLE events (
    id INT NOT NULL AUTO_INCREMENT,
    event_type VARCHAR(50),
    event_date TIMESTAMP,
    data JSON,
    PRIMARY KEY (id, event_type)
) PARTITION BY KEY(event_type)
PARTITIONS 4;

5. Composite (Subpartitioning)

Partition dalam partition

-- Range partition dengan hash subpartition
CREATE TABLE large_logs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    log_date DATE NOT NULL,
    server_id INT NOT NULL,
    log_level ENUM('DEBUG', 'INFO', 'WARN', 'ERROR'),
    message TEXT,
    PRIMARY KEY (id, log_date, server_id)
) PARTITION BY RANGE (YEAR(log_date))
SUBPARTITION BY HASH(server_id)
SUBPARTITIONS 4 (
    PARTITION p_2022 VALUES LESS THAN (2023),
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_2024 VALUES LESS THAN (2025)
);

Managing Partitions

1. Menambah Partition Baru

-- Add new partition untuk tahun 2025
ALTER TABLE sales ADD PARTITION (
    PARTITION p_2025 VALUES LESS THAN (2026)
);

-- Split partition
ALTER TABLE sales REORGANIZE PARTITION p_future INTO (
    PARTITION p_2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

2. Menghapus Partition

-- Drop partition (sangat cepat, tidak seperti DELETE)
ALTER TABLE sales DROP PARTITION p_2022;

-- Truncate partition (kosongkan tapi pertahankan struktur)
ALTER TABLE sales TRUNCATE PARTITION p_2022;

3. Exchange Partition

-- Exchange partition dengan non-partitioned table
CREATE TABLE sales_archive LIKE sales;

-- Pindahkan data dari partition ke archive
ALTER TABLE sales EXCHANGE PARTITION p_2022 WITH TABLE sales_archive;

4. Reorganize Partition

-- Merge partitions
ALTER TABLE sales REORGANIZE PARTITION p_2022, p_2023 INTO (
    PARTITION p_2022_2023 VALUES LESS THAN (2024)
);

-- Split partition
ALTER TABLE sales REORGANIZE PARTITION p_2023 INTO (
    PARTITION p_2023_q1 VALUES LESS THAN ('2023-04-01'),
    PARTITION p_2023_q2 VALUES LESS THAN ('2023-07-01'),
    PARTITION p_2023_q3 VALUES LESS THAN ('2023-10-01'),
    PARTITION p_2023_q4 VALUES LESS THAN ('2024-01-01')
);

Partition Pruning (Query Optimization)

1. How Pruning Works

MySQL secara otomatis hanya scan partition yang diperlukan:

-- Query akan hanya scan partition 2024
SELECT * FROM sales 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';

-- Verifikasi pruning dengan EXPLAIN
EXPLAIN PARTITIONS SELECT * FROM sales 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';
-- Output akan menunjukkan: partitions: p_2024

2. Pruning dengan Functions

-- Works (pruning berfungsi)
SELECT * FROM sales WHERE YEAR(order_date) = 2024;

-- Works (pruning berfungsi dengan BETWEEN)
SELECT * FROM sales 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Tidak work (no pruning dengan functions di WHERE)
SELECT * FROM sales WHERE MONTH(order_date) = 6;
-- Partition pruning tidak berfungsi!

3. FORCE INDEX untuk Partitioning

-- Gunakan index spesifik
SELECT * FROM sales 
USE INDEX (idx_order_date)
WHERE order_date >= '2024-01-01';

Monitoring Partitions

1. Partition Information

-- List all partitions
SELECT 
    TABLE_NAME,
    PARTITION_NAME,
    SUBPARTITION_NAME,
    PARTITION_METHOD,
    SUBPARTITION_METHOD,
    PARTITION_EXPRESSION,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, PARTITION_NAME;

2. Check Partition Size

-- Size per partition
SELECT 
    PARTITION_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH/1024/1024, 2) AS data_size_mb,
    ROUND(INDEX_LENGTH/1024/1024, 2) AS index_size_mb,
    ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024, 2) AS total_size_mb
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database' 
  AND TABLE_NAME = 'sales'
ORDER BY PARTITION_NAME;

3. Analyze Partition Statistics

-- Analyze specific partition
ALTER TABLE sales ANALYZE PARTITION p_2024;

-- Check partition statistics
SHOW TABLE STATUS LIKE 'sales';

Use Cases dan Contoh

1. Time-Series Data (Logs, Events)

-- Monthly partitioning untuk logs
CREATE TABLE system_logs (
    id BIGINT AUTO_INCREMENT,
    log_timestamp TIMESTAMP NOT NULL,
    log_level ENUM('DEBUG', 'INFO', 'WARN', 'ERROR'),
    service_name VARCHAR(100),
    message TEXT,
    metadata JSON,
    PRIMARY KEY (id, log_timestamp)
) PARTITION BY RANGE (UNIX_TIMESTAMP(log_timestamp)) (
    PARTITION p_202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')),
    PARTITION p_202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01')),
    PARTITION p_202403 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

2. Large User Tables

-- Hash partitioning untuk user table
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    PRIMARY KEY (id, username),
    UNIQUE KEY uk_email (email)
) PARTITION BY KEY(username)
PARTITIONS 16;

3. Financial Transactions

-- Quarterly partitioning dengan subpartitioning
CREATE TABLE transactions (
    transaction_id BIGINT NOT NULL AUTO_INCREMENT,
    transaction_date DATE NOT NULL,
    account_id INT NOT NULL,
    transaction_type ENUM('debit', 'credit'),
    amount DECIMAL(15,2) NOT NULL,
    currency CHAR(3) DEFAULT 'IDR',
    description VARCHAR(255),
    PRIMARY KEY (transaction_id, transaction_date, account_id),
    INDEX idx_account_date (account_id, transaction_date)
) PARTITION BY RANGE (YEAR(transaction_date) * 100 + QUARTER(transaction_date))
SUBPARTITION BY KEY(account_id)
SUBPARTITIONS 8 (
    PARTITION p_2023_q1 VALUES LESS THAN (202302),
    PARTITION p_2023_q2 VALUES LESS THAN (202305),
    PARTITION p_2023_q3 VALUES LESS THAN (202308),
    PARTITION p_2023_q4 VALUES LESS THAN (202311),
    PARTITION p_2024_q1 VALUES LESS THAN (202402),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Maintenance Partitioning

1. Automated Partition Rotation

#!/bin/bash
# /usr/local/bin/rotate_partitions.sh

DB_NAME="production"
TABLE_NAME="sales"
RETENTION_MONTHS=24

# Add new partition for next month
NEXT_MONTH=$(date -d "+1 month" +%Y%m)
NEXT_MONTH_FIRST=$(date -d "+1 month" +%Y-%m-01)

mysql -e "
ALTER TABLE $DB_NAME.$TABLE_NAME 
ADD PARTITION (
    PARTITION p_${NEXT_MONTH} VALUES LESS THAN (UNIX_TIMESTAMP('$NEXT_MONTH_FIRST'))
);
"

# Drop old partitions
OLD_MONTH=$(date -d "-${RETENTION_MONTHS} months" +%Y%m)
mysql -e "
ALTER TABLE $DB_NAME.$TABLE_NAME 
DROP PARTITION p_${OLD_MONTH};
" 2>/dev/null || echo "Partition p_${OLD_MONTH} not found"

2. Backup Per Partition

# Backup specific partition
mysqldump --no-data mydb sales --where "order_date >= '2024-01-01' AND order_date < '2025-01-01'" \
    > backup_sales_2024.sql

Best Practices

  1. Pilih partitioning key yang tepat: Harus ada di WHERE clause queries
  2. Don’t over-partition: 100+ partitions bisa jadi overhead
  3. PK harus include partition key: Required by MySQL
  4. Monitor partition sizes: Keep them balanced
  5. Plan partition lifecycle: Automate add/drop
  6. Test query performance: EXPLAIN PARTITIONS untuk verify pruning
  7. Consider subpartitioning: Untuk very large tables

Limitations

  • FOREIGN KEY: Tidak supported dengan partitioning
  • FULLTEXT Index: Hanya supported di MySQL 5.6+ dengan caveats
  • SPATIAL columns: Tidak supported
  • Temporary tables: Tidak bisa partitioned

Kesimpulan

Partitioning adalah tool powerful untuk manage tabel besar:

  • Query pruning untuk performa
  • Easy data lifecycle management
  • Parallel maintenance operations

Tetapi perlu planning yang matang untuk avoid pitfalls.

Artikel Terkait

Bagikan:

Link Postingan: https://www.tirinfo.com/mysql-partitioning-tabel-besar-performa-optimal/