Posted inMySQL

Hướng dẫn sử dụng Multi-Valued Indexes trong MySQL 8: Tối ưu truy vấn mảng JSON

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Bối cảnh thực tế: Khi mảng JSON bóp nghẹt database

Đợt Black Friday vừa rồi, hệ thống e-commerce bên mình dính alert đỏ lòm. CPU con master database (AWS RDS db.r6g.2xlarge) vọt thẳng lên 98%. Nhiều API endpoint trả về timeout 504 liên tục.

Nguyên nhân nằm ở cột assigned_roles trong bảng app_users và cột tags ở bảng products. Team thiết kế lưu role IDs dưới dạng mảng JSON như [101, 204, 305]. Hồi staging test vài chục nghìn record, các hàm MEMBER OF() hay JSON_CONTAINS() chạy vèo vèo dưới 10ms.

Lên production với hơn 10 triệu row, mọi thứ vỡ vụn. Mỗi query lọc quyền user ngốn từ 1.8s đến gần 3s. MySQL phải duyệt Full Table Scan (ALL) cả 10 triệu dòng. Engine phải bóc tách từng mảng JSON trong từng record chỉ để tìm một con số. Hệ thống nghẽn cổ chai ngay lập tức.

Với các bản MySQL cũ, cách xử lý quen thuộc là chuẩn hóa dữ liệu: tách riêng bảng quan hệ 1-N hoặc dùng Generated Column. Tuy nhiên, B-Tree index truyền thống chỉ map một row với đúng một giá trị scalar. Nó không thể đánh chỉ mục từng phần tử riêng lẻ bên trong một mảng JSON.

MySQL 8.0.17 đã giải quyết triệt để vấn đề này bằng Multi-Valued Index. Tính năng này cho phép cây B-Tree tạo nhiều index record khác nhau nhưng cùng trỏ về một Primary Key duy nhất.

Yêu cầu môi trường & Dữ liệu mẫu

Tính năng này yêu cầu tối thiểu MySQL 8.0.17 (hoặc các bản 8.0.x mới hơn, 8.4 LTS) chạy trên InnoDB engine.

Bạn kiểm tra nhanh version hiện tại:

mysql -u root -p -e "SELECT VERSION();"

Dưới đây là schema mô phỏng lại đúng cấu trúc bảng gặp sự cố:

CREATE DATABASE IF NOT EXISTS demo_indexing;
USE demo_indexing;

CREATE TABLE app_users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    assigned_roles JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

Mỗi bản ghi chứa danh sách quyền của user, chẳng hạn: [1, 5, 12, 45].

Cách cấu hình Multi-Valued Index

1. Cú pháp khởi tạo index trên JSON array

Chúng ta dùng hàm CAST(... AS type ARRAY) đặt trong khai báo Functional Index:

ALTER TABLE app_users 
ADD INDEX idx_user_roles ( (CAST(assigned_roles AS UNSIGNED ARRAY)) );

Cặp ngoặc đơn kép ( (...) ) là cú pháp bắt buộc. Nếu thiếu một lớp ngoặc, MySQL sẽ báo lỗi cú pháp ngay.

Cơ chế hoạt động rất trực quan. Giả sử user ID 999 có mảng [1, 5, 12]. B-Tree sẽ tạo ra 3 index key riêng biệt gồm 1, 5 và 12. Cả 3 key này đều trỏ thẳng về khóa chính 999.

2. Ba hàm truy vấn kích hoạt được index

Optimizer của MySQL chỉ chọn Multi-Valued Index cho đúng 3 hàm dưới đây:

  • MEMBER OF(): Kiểm tra một giá trị scalar có nằm trong mảng hay không.
  • JSON_CONTAINS(): Kiểm tra mảng có chứa tập con chỉ định hay không.
  • JSON_OVERLAPS(): Kiểm tra hai mảng có phần tử chung nào không. Hàm này cực kỳ tiện khi bạn cần filter user thuộc role 12 HOẶC 45.

Ví dụ truy vấn thực tế:

-- 1. Tìm user có role ID 12
SELECT id, username, assigned_roles 
FROM app_users 
WHERE 12 MEMBER OF (assigned_roles->'$');

-- 2. Tìm user chứa đồng thời cả 2 role 12 và 45
SELECT id, username, assigned_roles 
FROM app_users 
WHERE JSON_CONTAINS(assigned_roles, '[12, 45]');

-- 3. Tìm user có ít nhất một trong hai role 12 hoặc 45 (giao nhau)
SELECT id, username, assigned_roles 
FROM app_users 
WHERE JSON_OVERLAPS(assigned_roles, CAST('[12, 45]' AS JSON));

3. Cạm bẫy Type Mismatch

Đây là lỗi phổ biến nhất khiến index bị vô hiệu hóa trong âm thầm.

Lúc tạo index, bạn khai báo kiểu UNSIGNED ARRAY. Nhưng khi query, code backend lại truyền string: '12' MEMBER OF (...). Do sai lệch kiểu dữ liệu giữa chuỗi và số nguyên, MySQL sẽ bỏ qua index ngay lập tức. Câu lệnh lại rơi về Full Table Scan quen thuộc.

Hãy luôn ép kiểu tham số truyền vào khớp 100% với kiểu mảng đã index.

Đo lường & Đánh đổi hiệu năng

Đừng vội tin tưởng lệnh ALTER TABLE vừa chạy xong. Hãy dùng EXPLAIN để kiểm tra kế hoạch thực thi.

1. So sánh Execution Plan

Query khi chưa có index:

EXPLAIN SELECT * FROM app_users WHERE 12 MEMBER OF (assigned_roles->'$');
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | app_users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10245100 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+

MySQL phải quét toàn bộ 10.2 triệu rows với type = ALL.

Sau khi thêm idx_user_roles:

EXPLAIN SELECT * FROM app_users WHERE 12 MEMBER OF (assigned_roles->'$');
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | app_users | NULL       | ref  | idx_user_roles | idx_user_roles | 9       | const |   42 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

Access type chuyển thành ref. Số dòng cần đọc giảm từ hơn 10 triệu xuống đúng 42 dòng. Độ trễ query trên hệ thống production bên mình hạ từ 1.8s xuống còn 2ms.

2. Chi phí Write Amplification và dung lượng đĩa

Index nhanh thì sướng đọc, nhưng khổ ghi. Tính năng này không hề miễn phí.

Bảng 10 triệu dòng, nếu mỗi mảng JSON chứa trung bình 5 role ID, MySQL sẽ phải duy trì tới 50 triệu index entries trên B-Tree.

Bạn kiểm tra dung lượng index chiếm dụng bằng query:

SELECT 
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'demo_indexing' AND table_name = 'app_users';

Kinh nghiệm thực tế: Chỉ nên áp dụng Multi-Valued Index cho mảng nhỏ và cố định (dưới 20 phần tử) như role IDs, permissions, category IDs. Nếu mảng chứa hàng trăm phần tử và bảng có tần suất INSERT/UPDATE dồn dập, đĩa I/O sẽ tăng vọt rất nhanh.

Share:
Tags: