MySQL Large Scale

บทนำ: ทำไมต้องออกแบบฐานข้อมูลขนาดใหญ่?

เมื่อตาราง MySQL มีขนาดใหญ่ขึ้นเรื่อยๆ จนถึงหลายล้านเรคคอร์ด คุณจะเริ่มเจอปัญหา Performance ที่ลดลงอย่างมาก ไม่ว่าจะเป็น Query ที่ช้าลง, Insert ที่ใช้เวลานาน, หรือแม้แต่ระบบที่ Down เนื่องจาก Load เกินขีดจำกัด บทความนี้จะสอนวิธีออกแบบและแก้ไขปัญหาเหล่านี้อย่างเป็นระบบ

สิ่งที่ต้องเตรียม (Prerequisites)

ปัญหาที่พบบ่อยในตารางขนาดใหญ่

-- Query ที่ช้าเมื่อตารางใหญ่
SELECT * FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY created_at DESC
LIMIT 100;

-- EXPLAIN แสดงว่าใช้ filesort และ scan ทั้งตาราง
EXPLAIN SELECT ... -- rows: 50,000,000 (Full Table Scan!)

-- Deadlock scenario
-- Transaction 1
UPDATE orders SET status = 'processing' WHERE id = 1;
UPDATE orders SET status = 'processing' WHERE id = 2;

-- Transaction 2 (พร้อมกัน)
UPDATE orders SET status = 'processing' WHERE id = 2;
UPDATE orders SET status = 'processing' WHERE id = 1;
-- ERROR 1213 (40001): Deadlock found

กลยุทธ์การสร้าง Index สำหรับตารางใหญ่

-- สร้าง Composite Index สำหรับ orders table
CREATE INDEX idx_orders_user_status_date 
ON orders(user_id, status, created_at);

-- Query ที่ใช้ Index ได้ (Leftmost Prefix)
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed' AND created_at > '2024-01-01';

-- Query ที่ใช้ Index ไม่ได้ (ข้าม user_id)
SELECT * FROM orders WHERE status = 'completed'; -- ❌ ไม่ใช้ index
SELECT * FROM orders WHERE created_at > '2024-01-01'; -- ❌ ไม่ใช้ index

-- Query ที่ต้องการเฉพาะ user_id, status, total_amount
SELECT user_id, status, total_amount 
FROM orders 
WHERE user_id = 123 AND status = 'completed';

-- สร้าง Covering Index (รวม columns ที่ SELECT ด้วย)
CREATE INDEX idx_orders_covering 
ON orders(user_id, status, total_amount);

-- EXPLAIN แสดง "Using index" = Covering Index ✅
EXPLAIN SELECT user_id, status, total_amount 
FROM orders WHERE user_id = 123 AND status = 'completed';
-- Extra: Using index (ไม่มี "Using filesort" หรือ "Using temporary")

-- Query เดิม (ช้า)
SELECT o.id, o.total, o.status, o.created_at,
       u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at BETWEEN '2024-01-01' AND '2024-12-31'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 100;
-- Execution time: 30+ seconds 😱

-- Solution: Composite + Covering Index
CREATE INDEX idx_orders_status_date_user 
ON orders(status, created_at, user_id, id, total);

-- หลังสร้าง Index
-- Execution time: 50ms 🚀 (600x เร็วขึ้น)

MySQL Partitioning: แบ่งตารางใหญ่เป็นส่วนย่อย

ประเภทของ Partitioning

-- สร้างตารางที่มี RANGE Partitioning (แบ่งตามปี)
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(12,2),
    status VARCHAR(50),
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at)  -- created_at ต้องอยู่ใน PK
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

-- Query ที่ใช้ Partition Pruning (สแกนเฉพาะ partition 2024)
SELECT * FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
-- Partitions: p2024 (แทนที่จะสแกนทั้งหมด)

-- เพิ่ม Partition ใหม่
ALTER TABLE orders ADD PARTITION (
    PARTITION p2026 VALUES LESS THAN (2027)
);

-- ลบข้อมูลเก่าได้เร็วมาก (ไม่ต้อง DELETE)
ALTER TABLE orders DROP PARTITION p2022; -- Instant! 🚀

-- สร้างตารางที่มี LIST Partitioning (แบ่งตามภูมิภาค)
CREATE TABLE sales (
    id BIGINT AUTO_INCREMENT,
    region_id INT NOT NULL,
    amount DECIMAL(12,2),
    sale_date DATE,
    PRIMARY KEY (id, region_id)
) PARTITION BY LIST (region_id) (
    PARTITION p_bangkok VALUES IN (1, 2, 3),     -- กรุงเทพและปริมณฑล
    PARTITION p_central VALUES IN (4, 5, 6, 7),  -- ภาคกลาง
    PARTITION p_north VALUES IN (8, 9, 10),    -- ภาคเหนือ
    PARTITION p_northeast VALUES IN (11, 12, 13), -- ภาคอีสาน
    PARTITION p_south VALUES IN (14, 15, 16)   -- ภาคใต้
);

-- Query ที่ใช้ Partition Pruning
SELECT * FROM sales WHERE region_id = 1;
-- Partitions: p_bangkok เท่านั้น

-- สร้างตารางที่มี HASH Partitioning
CREATE TABLE user_logs (
    id BIGINT AUTO_INCREMENT,
    user_id INT NOT NULL,
    action VARCHAR(100),
    log_time DATETIME,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id)
  PARTITIONS 16;  -- แบ่งเป็น 16 partitions

-- MySQL จะคำนวณ partition จาก: MOD(user_id, 16)
-- user_id = 100 → partition 4
-- user_id = 200 → partition 8

-- Query ที่ใช้ Partition Pruning
SELECT * FROM user_logs WHERE user_id = 100;
-- สแกนเฉพาะ partition ที่ user_id=100 อยู่

-- สร้างตารางที่มี KEY Partitioning
CREATE TABLE sessions (
    session_id VARCHAR(64) NOT NULL,
    user_id INT,
    data JSON,
    expires_at DATETIME,
    PRIMARY KEY (session_id)
) PARTITION BY KEY(session_id)
  PARTITIONS 32;  -- แบ่งเป็น 32 partitions

-- เหมาะสำหรับ session_id ที่เป็น UUID/Random String

-- สร้างตาราง logs ที่มี RANGE Partitioning (แบ่งตามเดือน)
CREATE TABLE application_logs (
    id BIGINT AUTO_INCREMENT,
    log_level VARCHAR(10),
    message TEXT,
    source VARCHAR(100),
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at),
    INDEX idx_level_time (log_level, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p_2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p_2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p_2024_03 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p_2024_04 VALUES LESS THAN (TO_DAYS('2024-05-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Query logs ของเดือนมกราคม 2024 (สแกนเฉพาะ p_2024_01)
SELECT * FROM application_logs
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'
  AND log_level = 'ERROR';

-- ลบ logs เก่า (เก็บไว้ 3 เดือน)
ALTER TABLE application_logs DROP PARTITION p_2024_01;
-- Execution time: < 1 second (สำหรับ 30 ล้าน rows!)

MySQL Sharding: กระจายข้อมูลข้ามหลายเซิร์ฟเวอร์

Sharding Strategies

# Application-level Sharding Logic (Node.js Example)

function getShardForUser(userId, totalShards = 4) {
    // Simple hash function
    const shardNumber = userId % totalShards;
    return `shard_${shardNumber}`;
}

// Example
getShardForUser(12345);  // → "shard_1"
getShardForUser(12346);  // → "shard_2"
getShardForUser(12347);  // → "shard_3"
getShardForUser(12348);  // → "shard_0"

// Connection Pool for Multiple Shards
const shards = {
    shard_0: mysql.createPool({ host: 'db-shard-1.example.com', ... }),
    shard_1: mysql.createPool({ host: 'db-shard-2.example.com', ... }),
    shard_2: mysql.createPool({ host: 'db-shard-3.example.com', ... }),
    shard_3: mysql.createPool({ host: 'db-shard-4.example.com', ... }),
};

async function getUser(userId) {
    const shardName = getShardForUser(userId);
    const connection = shards[shardName];
    return await connection.query('SELECT * FROM users WHERE id = ?', [userId]);
}

# Range-based Sharding Logic

const shardRanges = [
    { min: 1, max: 1000000, shard: 'shard_users_1' },
    { min: 1000001, max: 2000000, shard: 'shard_users_2' },
    { min: 2000001, max: 3000000, shard: 'shard_users_3' },
    { min: 3000001, max: Infinity, shard: 'shard_users_default' },
];

function getShardForUser(userId) {
    for (const range of shardRanges) {
        if (userId >= range.min && userId <= range.max) {
            return range.shard;
        }
    }
    return 'shard_users_default';
}

// Example
getShardForUser(500000);   // → "shard_users_1"
getShardForUser(1500000);  // → "shard_users_2"
getShardForUser(2500000);  // → "shard_users_3"

# Geography-based Sharding

const geoShards = {
    'TH': 'shard_thailand',      // Thailand → Singapore Data Center
    'US': 'shard_us_east',       // USA → Virginia Data Center
    'EU': 'shard_eu_west',       // Europe → Frankfurt Data Center
    'JP': 'shard_asia_east',     // Japan → Tokyo Data Center
    'default': 'shard_global',   // Others → Global Shard
};

function getShardByCountry(countryCode) {
    return geoShards[countryCode] || geoShards['default'];
}

// Example: ลูกค้าจากประเทศต่างๆ
getShardByCountry('TH');  // → "shard_thailand" (Singapore DC)
getShardByCountry('US');  // → "shard_us_east" (Virginia DC)
getShardByCountry('DE');  // → "shard_eu_west" (Frankfurt DC)

MySQL Performance Tuning สำหรับตารางใหญ่

# /etc/mysql/mysql.conf.d/mysqld.cnf
# Optimized for Large Tables (32GB RAM Server)

[mysqld]
# === InnoDB Settings ===
# Buffer Pool: 70-80% of RAM (สำหรับเก็บ data + index ใน memory)
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8

# Log File Settings
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M

# Flush Settings (Performance vs Durability)
innodb_flush_log_at_trx_commit = 1  # 1 = Safe, 2 = Faster
innodb_flush_method = O_DIRECT

# Thread Concurrency
innodb_thread_concurrency = 0  # Auto
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# === Query Cache (Disable สำหรับ Large Tables) ===
query_cache_type = 0
query_cache_size = 0

# === Connection Settings ===
max_connections = 500
max_connect_errors = 1000
wait_timeout = 600
interactive_timeout = 600

# === Buffer Sizes ===
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M

# === Table Settings ===
table_open_cache = 4000
table_definition_cache = 2000
open_files_limit = 65535

# === Slow Query Log ===
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123
AND created_at > '2024-01-01';

-- ตรวจสอบ:
-- type: ref/eq_ref = Good
-- type: ALL = Bad (Full Scan)
-- rows: ตัวเลขน้อย = Good
-- Extra: Using index = Great!

-- ❌ Bad: SELECT * ดึงทุก column
SELECT * FROM orders WHERE ...;

-- ✅ Good: เลือกเฉพาะที่ต้องการ
SELECT id, user_id, total, status
FROM orders WHERE ...;

-- ✅ Better: ใช้ Covering Index
CREATE INDEX idx_covering
ON orders(user_id, id, total, status);

-- ❌ Bad: Insert ทีละ row
INSERT INTO logs VALUES (...);
INSERT INTO logs VALUES (...);

-- ✅ Good: Batch Insert
INSERT INTO logs VALUES
 (...), (...), (...), (...),
 (...), (...), (...), (...);
-- 1,000 rows per batch = Optimal

-- ❌ Bad: OFFSET ใหญ่มาก
SELECT * FROM orders
LIMIT 1000000, 100;

-- ✅ Good: Cursor-based Pagination
SELECT * FROM orders
WHERE id > 1000000
ORDER BY id LIMIT 100;
-- Uses index, no offset scan!

-- 1. ดู Top 10 Slow Queries
SELECT query_time, lock_time, rows_sent, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 10;

-- 2. ดู Table Sizes
SELECT 
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
    table_rows
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY (data_length + index_length) DESC;

-- 3. ดู Index Usage
SELECT 
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'your_database'
AND stat_name = 'size'
ORDER BY stat_value DESC;

-- 4. ดู Running Queries
SHOW PROCESSLIST;

-- 5. ดู Partition Information
SELECT 
    partition_name,
    partition_expression,
    partition_description,
    table_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb
FROM information_schema.partitions
WHERE table_name = 'orders'
ORDER BY partition_ordinal_position;

Checklist: เมื่อไหร่ต้องใช้เทคนิคไหน?

คำถามที่พบบ่อย (FAQ)