MySQL Indexing Strategies — Thiết kế Index hiệu quả cho mọi trường hợp

· 13 min read
#mysql #database #performance #indexing #optimization #b-tree

Index là "cuốn mục lục" giúp MySQL tìm dữ liệu nhanh hơn hàng ngàn lần so với quét toàn bộ bảng. Nhưng index không phải "càng nhiều càng tốt" — mỗi index đều có chi phí về storage và write performance.

Bài viết này sẽ giúp bạn hiểu sâu về cách index hoạt động và thiết kế index strategy phù hợp cho từng use case cụ thể.

Index hoạt động như thế nào?

B-Tree Index (Mặc định)

MySQL InnoDB sử dụng B+Tree làm cấu trúc dữ liệu chính cho index. Hãy tưởng tượng như một cây phân nhánh:

                    [50]
                   /    \
              [20,30]   [70,80]
              /  |  \    /  |  \
           [10][25][35][60][75][90]
            ↓   ↓   ↓   ↓   ↓   ↓
          Data Data Data Data Data Data

Đặc điểm của B+Tree:

  • Tất cả data nằm ở leaf nodes (lá)
  • Các leaf nodes được liên kết với nhau (linked list)
  • Độ sâu cây rất nông (thường 3-4 levels cho hàng triệu rows)
  • Hiệu quả cho: =, <, >, <=, >=, BETWEEN, LIKE 'prefix%'

Ví dụ tìm kiếm: Tìm id = 35

  1. Bắt đầu từ root: 35 < 50 → đi trái
  2. 30 < 35 → đi phải
  3. Tìm thấy leaf node chứa 35 → Trả về data

Chỉ 3 operations để tìm trong hàng triệu records!

Hash Index

CREATE TABLE sessions (
    session_id VARCHAR(64),
    user_id INT,
    data TEXT,
    INDEX USING HASH (session_id)  -- Chỉ hoạt động với MEMORY engine
) ENGINE=MEMORY;

Hash Index chỉ hỗ trợ phép so sánh =, không hỗ trợ range queries.

InnoDB sử dụng Adaptive Hash Index tự động — MySQL tự tạo hash index trong memory cho các B-Tree pages được truy cập thường xuyên.

Full-Text Index

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT INDEX idx_content (title, content)
);

-- Tìm kiếm full-text
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('mysql performance' IN NATURAL LANGUAGE MODE);

Sử dụng cho tìm kiếm văn bản, không phải exact match.

Primary Key vs Secondary Index

Primary Key (Clustered Index)

Trong InnoDB, Primary Key IS the table. Dữ liệu được tổ chức theo thứ tự của Primary Key:

Primary Key Index (Clustered):
[1] → {id:1, name:"Alice", email:"alice@example.com", ...toàn bộ row}
[2] → {id:2, name:"Bob", email:"bob@example.com", ...toàn bộ row}
[3] → {id:3, name:"Charlie", email:"charlie@example.com", ...toàn bộ row}

Best practices cho Primary Key:

  • Sử dụng AUTO_INCREMENT integer (tránh UUID nếu có thể)
  • Không bao giờ update Primary Key
  • Giữ Primary Key nhỏ gọn

Tại sao tránh UUID làm Primary Key?

-- ❌ BAD: UUID random gây page split liên tục
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(100)
);

-- ✅ GOOD: Auto-increment, insert luôn ở cuối
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) UNIQUE DEFAULT (UUID()),
    name VARCHAR(100)
);

Secondary Index

Secondary index chứa giá trị index + Primary Key, không chứa toàn bộ row:

Secondary Index on email:
["alice@example.com"] → PK: 1
["bob@example.com"] → PK: 2
["charlie@example.com"] → PK: 3

Khi query: SELECT * FROM users WHERE email = 'bob@example.com'
1. Tìm trong secondary index → PK = 2
2. Dùng PK để lookup trong clustered index → Lấy full row

Bước 2 gọi là "bookmark lookup" hoặc "key lookup" — đây là lý do tại sao Covering Index quan trọng.

Composite Index (Index đa cột)

Cú pháp

CREATE INDEX idx_country_city ON addresses(country, city);
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

Quy tắc "Leftmost Prefix"

Composite index chỉ có thể được sử dụng nếu query sử dụng các cột từ trái sang phải, liên tục:

-- Index: (country, city, district)

-- ✅ Sử dụng được index
SELECT * FROM addresses WHERE country = 'VN';
SELECT * FROM addresses WHERE country = 'VN' AND city = 'Hanoi';
SELECT * FROM addresses WHERE country = 'VN' AND city = 'Hanoi' AND district = 'CauGiay';

-- ❌ KHÔNG sử dụng được index
SELECT * FROM addresses WHERE city = 'Hanoi';  -- Thiếu country
SELECT * FROM addresses WHERE country = 'VN' AND district = 'CauGiay';  -- Bỏ qua city
SELECT * FROM addresses WHERE district = 'CauGiay';  -- Chỉ có column cuối

Thứ tự cột trong Composite Index

Quy tắc vàng: Đặt cột có selectivity cao (ít giá trị duplicate) trước.

-- Giả sử: status chỉ có 3 giá trị (pending, completed, cancelled)
--         user_id có hàng ngàn giá trị khác nhau

-- ✅ GOOD: user_id trước (high selectivity)
CREATE INDEX idx_user_status ON orders(user_id, status);

-- ❌ LESS OPTIMAL: status trước (low selectivity)
CREATE INDEX idx_status_user ON orders(status, user_id);

Ngoại lệ: Nếu query pattern luôn có status = ? nhưng user_id optional, đặt status trước.

Composite Index cho ORDER BY

-- Query thường xuyên
SELECT * FROM products 
WHERE category_id = 5 
ORDER BY created_at DESC 
LIMIT 20;

-- Index tối ưu: WHERE columns + ORDER BY columns
CREATE INDEX idx_category_created ON products(category_id, created_at DESC);

Lưu ý về sort direction:

-- Index: (a ASC, b DESC)
-- ✅ Hoạt động với: ORDER BY a ASC, b DESC
-- ✅ Hoạt động với: ORDER BY a DESC, b ASC (đọc ngược)
-- ❌ KHÔNG hoạt động với: ORDER BY a ASC, b ASC

Covering Index

Covering Index là index chứa TẤT CẢ các cột mà query cần, không cần quay lại clustered index.

-- Query
SELECT user_id, status, total_amount 
FROM orders 
WHERE user_id = 123 AND status = 'completed';

-- Covering Index
CREATE INDEX idx_user_status_amount ON orders(user_id, status, total_amount);

Khi EXPLAIN:

Extra: Using index  -- ← Dấu hiệu của Covering Index

Khi nào dùng Covering Index?

  • Query được chạy rất thường xuyên
  • Chỉ cần một số ít columns
  • Performance là ưu tiên cao nhất

Trade-off: Index lớn hơn = tốn storage + chậm INSERT/UPDATE.

Partial Index (Index một phần) — MySQL 8.0+

MySQL 8.0 hỗ trợ Functional Index có thể đạt hiệu ứng tương tự partial index:

-- Chỉ index các orders chưa xử lý (giả sử processed_at NULL nghĩa là chưa xử lý)
CREATE INDEX idx_unprocessed ON orders((CASE WHEN processed_at IS NULL THEN 1 END));

-- Index chỉ first 10 chars của một cột
CREATE INDEX idx_email_prefix ON users((LEFT(email, 10)));

Index cho JSON columns

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    attributes JSON,
    INDEX idx_brand ((CAST(attributes->>'$.brand' AS CHAR(50)) COLLATE utf8mb4_bin))
);

-- Query sử dụng index
SELECT * FROM products 
WHERE attributes->>'$.brand' = 'Apple';

Các Anti-patterns cần tránh

1. Function trên Indexed Column

-- ❌ BAD: Index không được sử dụng
SELECT * FROM users WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- ✅ GOOD: Index được sử dụng
SELECT * FROM users 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

SELECT * FROM users WHERE email = 'john@example.com';  -- Nếu collation case-insensitive

2. Implicit Type Conversion

-- phone_number là VARCHAR
-- ❌ BAD: MySQL chuyển phone_number thành số để so sánh
SELECT * FROM users WHERE phone_number = 0912345678;

-- ✅ GOOD: So sánh cùng type
SELECT * FROM users WHERE phone_number = '0912345678';

3. Leading Wildcard trong LIKE

-- ❌ BAD: Full table scan
SELECT * FROM products WHERE name LIKE '%phone%';

-- ✅ OK: Có thể dùng index
SELECT * FROM products WHERE name LIKE 'phone%';

-- ✅ BETTER: Dùng Full-Text Search cho tìm kiếm flexible
SELECT * FROM products WHERE MATCH(name) AGAINST('phone');

4. OR với các cột khác nhau

-- ❌ BAD: Thường không dùng được index hiệu quả
SELECT * FROM products WHERE category_id = 5 OR brand_id = 10;

-- ✅ GOOD: Dùng UNION
SELECT * FROM products WHERE category_id = 5
UNION
SELECT * FROM products WHERE brand_id = 10;

5. NOT IN / NOT EXISTS / <>

-- ❌ Index ít hiệu quả với negative conditions
SELECT * FROM users WHERE status != 'deleted';
SELECT * FROM orders WHERE user_id NOT IN (1, 2, 3);

-- ✅ Cân nhắc restructure query hoặc chấp nhận scan nếu điều kiện loại trừ ít records

6. Quá nhiều Index

-- ❌ BAD: Mỗi INSERT/UPDATE phải cập nhật tất cả index
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    category_id INT,
    brand_id INT,
    price DECIMAL(10,2),
    stock INT,
    created_at DATETIME,
    updated_at DATETIME,
    INDEX idx_name (name),
    INDEX idx_category (category_id),
    INDEX idx_brand (brand_id),
    INDEX idx_price (price),
    INDEX idx_stock (stock),
    INDEX idx_created (created_at),
    INDEX idx_updated (updated_at),
    INDEX idx_category_brand (category_id, brand_id),
    INDEX idx_category_price (category_id, price),
    INDEX idx_brand_price (brand_id, price)
    -- ... quá nhiều!
);

Hướng dẫn: Bắt đầu với ít index, thêm dựa trên actual slow queries.

Phân tích Index Usage

Kiểm tra index có được sử dụng không

-- Xem tất cả index của table
SHOW INDEX FROM orders;

-- Xem index usage statistics (MySQL 8.0+)
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY count_read DESC;

Tìm Unused Indexes

-- Index không được đọc nhưng vẫn phải write (candidates to drop)
SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
    AND count_read = 0
    AND count_write > 0
    AND object_schema = 'your_database';

Tìm Duplicate Indexes

SELECT 
    t.table_schema,
    t.table_name,
    GROUP_CONCAT(t.index_name) as duplicate_indexes,
    t.column_names
FROM (
    SELECT 
        table_schema,
        table_name,
        index_name,
        GROUP_CONCAT(column_name ORDER BY seq_in_index) as column_names
    FROM information_schema.statistics
    WHERE table_schema = 'your_database'
    GROUP BY table_schema, table_name, index_name
) t
GROUP BY t.table_schema, t.table_name, t.column_names
HAVING COUNT(*) > 1;

Index Strategy cho các Use Cases phổ biến

Use Case 1: E-commerce Product Listing

-- Query pattern
SELECT * FROM products 
WHERE category_id = ? 
  AND price BETWEEN ? AND ?
  AND status = 'active'
ORDER BY created_at DESC
LIMIT 20;

-- Recommended indexes
CREATE INDEX idx_category_status_created ON products(category_id, status, created_at DESC);
CREATE INDEX idx_category_status_price ON products(category_id, status, price);

Use Case 2: User Authentication

-- Query pattern
SELECT * FROM users WHERE email = ? AND deleted_at IS NULL;

-- Recommended index (covering if possible)
CREATE UNIQUE INDEX idx_email ON users(email) WHERE deleted_at IS NULL;  -- PostgreSQL syntax
-- MySQL equivalent:
CREATE INDEX idx_email_deleted ON users(email, deleted_at);

Use Case 3: Analytics / Reporting

-- Query pattern
SELECT 
    DATE(created_at) as date,
    COUNT(*) as order_count,
    SUM(total_amount) as revenue
FROM orders
WHERE created_at >= ? AND created_at < ?
GROUP BY DATE(created_at);

-- Recommended index
CREATE INDEX idx_created_at ON orders(created_at);
-- Consider summary tables for heavy reporting

Use Case 4: Activity Feed / Timeline

-- Query pattern
SELECT * FROM activities 
WHERE user_id IN (?, ?, ?) 
ORDER BY created_at DESC 
LIMIT 50;

-- Recommended index
CREATE INDEX idx_user_created ON activities(user_id, created_at DESC);

Use Case 5: Full-Text Search

-- Query pattern
SELECT * FROM articles WHERE title LIKE '%keyword%' OR content LIKE '%keyword%';

-- Better approach: Full-Text Index
CREATE FULLTEXT INDEX idx_ft_articles ON articles(title, content);

SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('keyword' IN NATURAL LANGUAGE MODE);

Index Maintenance

Rebuild Index (Optimize Table)

-- Rebuild indexes và reclaim space
OPTIMIZE TABLE orders;

-- Hoặc với ALTER TABLE (online trong MySQL 8.0)
ALTER TABLE orders ENGINE=InnoDB;

Analyze Table

-- Cập nhật index statistics (giúp optimizer chọn đúng index)
ANALYZE TABLE orders;

Monitor Index Size

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

Indexing trong Laravel

Migration

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained();
    $table->string('status');
    $table->decimal('total_amount', 10, 2);
    $table->timestamps();
    
    // Composite index
    $table->index(['user_id', 'status', 'created_at']);
    
    // Unique index
    $table->unique(['user_id', 'order_number']);
});

// Add index to existing table
Schema::table('products', function (Blueprint $table) {
    $table->index(['category_id', 'price'], 'idx_category_price');
});

Raw Index cho cases phức tạp

Schema::table('products', function (Blueprint $table) {
    // Descending index (MySQL 8.0+)
    DB::statement('CREATE INDEX idx_category_created ON products(category_id, created_at DESC)');
    
    // Functional index (MySQL 8.0+)
    DB::statement('CREATE INDEX idx_year ON products((YEAR(created_at)))');
});

Checklist thiết kế Index

Khi thiết kế index cho một table mới:

  1. Xác định query patterns

    • Những query nào chạy thường xuyên nhất?
    • WHERE clause sử dụng columns nào?
    • ORDER BY columns nào?
    • JOIN trên columns nào?

  2. Primary Key

    • Sử dụng AUTO_INCREMENT integer?
    • Nếu cần UUID, có cột riêng và index UNIQUE?

  3. Foreign Keys

    • Tất cả FK columns có index?

  4. Composite Index

    • Thứ tự columns đúng (high selectivity first)?
    • Có bao gồm ORDER BY columns?

  5. Covering Index

    • Query hot nhất có thể dùng covering index?

  6. Kiểm tra

    • EXPLAIN cho các query quan trọng
    • Không có duplicate/redundant indexes?

Kết luận

Index là công cụ mạnh mẽ nhưng không phải "silver bullet". Hiểu cách index hoạt động giúp bạn:

  • Thiết kế schema tốt hơn từ đầu
  • Debug performance issues nhanh hơn
  • Tránh các anti-patterns phổ biến

Key takeaways:

  1. B+Tree index hiệu quả cho range queries
  2. Primary Key = Clustered Index trong InnoDB
  3. Composite index tuân theo "leftmost prefix rule"
  4. Covering index tránh bookmark lookup
  5. Tránh functions trên indexed columns
  6. Bắt đầu với ít index, thêm dựa trên actual queries

Bài tiếp theo, chúng ta sẽ khám phá MySQL Slow Query Log — cách tìm và fix các query chậm trong production.

Bình luận