Posted inMySQL

MySQL Generated Columns: Tuyệt chiêu ‘hack’ tốc độ Query mà không cần thay đổi Code

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

Tại sao bạn nên quan tâm đến Generated Columns?

Viết đi viết lại cụm (price * quantity) AS total_price trong hàng chục câu Query không chỉ gây nhàm chán mà còn rất dễ sai sót. Tệ hơn, nếu bạn lưu dữ liệu JSON và thường xuyên lọc theo một trường bên trong, hàm JSON_EXTRACT sẽ khiến CPU quá tải khi bảng đạt ngưỡng vài triệu dòng.

Trước đây, chúng ta thường dùng Trigger để cập nhật các cột phụ hoặc tính toán trực tiếp ở Backend. Tuy nhiên, Trigger giống như một “hộp đen” khó bảo trì. Trong khi đó, tính toán ở tầng App lại khiến việc đánh Index để tăng tốc tìm kiếm trở nên bất khả thi.

Thực tế tại một dự án mình từng tham gia: Với bảng orders khoảng 10 triệu record (dung lượng 50GB), việc chuyển các phép tính từ Query sang Generated Columns đã giúp giảm tải CPU từ 70% xuống còn 25%. Generated Columns (có từ MySQL 5.7) cho phép tạo ra các cột tự động tính toán từ dữ liệu sẵn có trong cùng một hàng.

Bạn cần phân biệt rõ hai loại cột này để tránh lãng phí tài nguyên:

  • Virtual Generated Columns (Mặc định): Không tốn dung lượng ổ cứng vì giá trị chỉ được tính khi bạn đọc dữ liệu. Điểm mấu chốt là bạn vẫn có thể đánh Index trên cột ảo này.
  • Stored Generated Columns: Giá trị được lưu vật lý vào ổ cứng ngay khi INSERT hoặc UPDATE. Nó tốn thêm bộ nhớ nhưng bù lại tốc độ đọc cực nhanh vì MySQL không cần tính toán lại.

Triển khai thực tế: Cú pháp và Cách dùng

Bạn không cần cài thêm bất kỳ Extension nào vì đây là tính năng lõi. Dưới đây là cách định nghĩa cột tự động ngay khi tạo bảng:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2),
    quantity INT,
    -- Cột ảo: Tối ưu bộ nhớ
    total_price DECIMAL(10, 2) AS (price * quantity) VIRTUAL,
    -- Cột lưu trữ: Tối ưu CPU
    total_price_stored DECIMAL(10, 2) AS (price * quantity) STORED
);

Nếu hệ thống đang chạy, bạn có thể dùng ALTER TABLE. Mình thường dùng cách này để tối ưu các truy vấn chậm mà không làm gián đoạn cấu trúc logic của ứng dụng:

ALTER TABLE products 
ADD COLUMN discounted_price DECIMAL(10, 2) 
AS (original_price * 0.9) VIRTUAL;

Lưu ý quan trọng: Bạn không thể INSERT dữ liệu thủ công vào các cột này. MySQL sẽ nắm quyền kiểm soát hoàn toàn. Mọi nỗ lực ghi đè từ phía Application sẽ bị hệ thống từ chối ngay lập tức.

02 Case Study “cứu cánh” cho hệ thống lớn

1. Tăng tốc tìm kiếm dữ liệu JSON gấp 25 lần

Đây là ứng dụng thực tế nhất. Giả sử bạn lưu cấu hình người dùng trong cột settings (JSON). Bạn muốn tìm tất cả User đang để theme = 'dark'.

-- Query này sẽ quét toàn bộ bảng (Full Table Scan), cực chậm!
SELECT * FROM user_profiles WHERE settings->"$.theme" = 'dark';

Giải pháp là tạo một Virtual Column trích xuất theme và đánh Index cho nó:

ALTER TABLE user_profiles
ADD COLUMN user_theme VARCHAR(20) AS (settings->>"$.theme") VIRTUAL,
ADD INDEX idx_user_theme (user_theme);

Sau khi áp dụng, MySQL sẽ dùng B-Tree Index để tìm kiếm thay vì parse từng file JSON. Thời gian phản hồi có thể giảm từ 500ms xuống dưới 20ms.

2. Tìm kiếm theo tên đầy đủ (Full Name)

Thay vì dùng CONCAT(first_name, ' ', last_name) trong mệnh đề WHERE (làm vô hiệu hóa Index), hãy dùng Stored Column:

ALTER TABLE employees
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name)) STORED,
ADD INDEX idx_full_name (full_name);

Khi nào nên chọn Virtual, khi nào dùng Stored?

Hãy cân nhắc dựa trên tài nguyên server của bạn:

  • Dùng Virtual khi: Bạn muốn tiết kiệm Disk. Cột này cực kỳ hiệu quả khi cần đánh Index cho các trường dữ liệu ít thay đổi nhưng thường xuyên dùng để lọc (Filter).
  • Dùng Stored khi: Các phép toán quá phức tạp, tốn nhiều tài nguyên CPU để xử lý chuỗi. Hoặc khi bạn dùng các phiên bản MySQL cũ chưa hỗ trợ Index tốt trên cột ảo.

Kiểm tra hiệu quả tối ưu

Sau khi thay đổi, hãy luôn dùng EXPLAIN để kiểm chứng. Nếu cột key hiển thị tên Index bạn vừa tạo, bạn đã thành công.

EXPLAIN SELECT * FROM user_profiles WHERE user_theme = 'dark';

Để quản lý danh sách các cột tự động trong hệ thống, bạn có thể truy vấn nhanh qua information_schema:

SELECT table_name, column_name, generation_expression 
FROM information_schema.columns
WHERE is_generated = 'ALWAYS' AND table_schema = 'your_db_name';

Kinh nghiệm xương máu: Hãy theo dõi kỹ dung lượng ổ cứng (Data_length) khi dùng Stored Columns trên các bảng lớn. Ngược lại, nếu dùng Virtual Columns với các hàm Regex phức tạp, hãy chú ý đến biểu đồ CPU của server.

Tóm lại, Generated Columns là giải pháp cân bằng hoàn hảo giữa tính tiện dụng và hiệu năng. Nó giúp code SQL gọn gàng hơn và tận dụng tối đa sức mạnh của Index mà không cần thay đổi logic code Backend quá nhiều.

Share:
Tags: