Quick start: Làm ngay trong 5 phút
Mỗi lần tính tiền đơn hàng, ghép chuỗi họ tên hay bóc tách field từ JSON, anh em thường làm ở đâu? Đẩy hết về backend xử lý hay viết trigger trong DB? Cả hai cách này đều cồng kềnh. MySQL có sẵn một tính năng rất bén cho vụ này: Generated Columns (cột sinh tự động).
Hiểu đơn giản, MySQL sẽ tự tính giá trị của cột dựa vào biểu thức từ các cột khác trong cùng row. Hãy xem ví dụ bảng đơn hàng dưới đây:
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
item_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
quantity INT NOT NULL,
discount_percent INT DEFAULT 0,
-- Cột sinh tự động tính tổng tiền sau chiết khấu
total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (
(price * quantity) * (1 - discount_percent / 100)
) VIRTUAL
);
-- Chèn dữ liệu thử, bỏ qua cột total_amount
INSERT INTO order_items (item_name, price, quantity, discount_percent)
VALUES ('Bàn phím cơ', 1500000, 2, 10);
-- Kiểm tra kết quả
SELECT id, item_name, total_amount FROM order_items;
Chạy lệnh xong, total_amount tự động hiện 2700000.00. Bạn không cần chạm tay vào việc tính toán, MySQL tự lo từ A đến Z.
Bản chất: Virtual Column khác gì Stored Column?
Khi tạo Generated Column, bạn phải chọn một trong hai chế độ: VIRTUAL hoặc STORED. Chọn sai có thể khiến DB phình to hoặc ngốn sạch CPU.
1. Virtual Column (Chế độ mặc định)
Với VIRTUAL, MySQL không lưu dữ liệu cột này xuống ổ cứng. Mỗi khi câu lệnh SELECT gọi tới nó, MySQL sẽ tính toán tức thì (on-the-fly) ngay trong RAM.
- Ưu điểm: Không tốn một byte dung lượng đĩa. Tốc độ
INSERTvàUPDATEgiữ nguyên vì engine không phải ghi thêm cột xuống đĩa. - Nhược điểm: Tốn thêm chu kỳ CPU khi query. Nếu phải scan cả triệu row mà không có index, CPU sẽ chịu tải lớn.
- Đòn bẩy hiệu năng: MySQL cho phép đánh Secondary Index (B-Tree) trực tiếp trên cột
VIRTUAL. Giá trị tính toán chỉ được lưu vào cây index chứ không lưu ở bảng chính. Cách này vừa tiết kiệm ổ cứng, vừa tăng tốc truy vấn tối đa.
2. Stored Column
Ngược lại, STORED sẽ tính toán giá trị và ghi đè thẳng xuống đĩa mỗi khi bản ghi được thêm mới hoặc cập nhật.
- Ưu điểm: Đọc cực nhanh. Dữ liệu nằm sẵn trên đĩa như một cột vật lý bình thường, CPU không phải tính lại.
- Nhược điểm: Tốn dung lượng ổ đĩa. Thao tác ghi (
INSERT/UPDATE) chậm hơn do chi phí I/O tăng lên.
ALTER TABLE users
ADD COLUMN full_name VARCHAR(150)
GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED;
Thực chiến: Đánh Index trên JSON và Composite Index
Trên hệ thống log production 50GB mình từng vận hành, bảng audit_events phình lên 12 triệu dòng. Lúc này, mọi câu query lọc theo thuộc tính JSON đều biến thành ác mộng: mất từ 2.8s đến 4s cho mỗi câu lệnh vì Full Table Scan.
Tăng tốc truy vấn JSON với Functional Index
MySQL không cho đánh B-Tree index trực tiếp lên toàn bộ cột kiểu JSON. Muốn query mượt, chuẩn bài nhất là tạo một Virtual Column bóc tách field cần lọc, sau đó đánh index lên cột ảo đó:
CREATE TABLE user_profiles (
id INT AUTO_INCREMENT PRIMARY KEY,
profile_data JSON NOT NULL,
-- Bóc tách email từ JSON ra cột ảo
user_email VARCHAR(255) GENERATED ALWAYS AS (
profile_data->>'$.contact.email'
) VIRTUAL,
-- Đánh index trên cột ảo
INDEX idx_user_email (user_email)
);
-- Hoặc thêm nhanh vào bảng có sẵn
ALTER TABLE user_profiles
ADD COLUMN phone_number VARCHAR(20)
GENERATED ALWAYS AS (profile_data->>'$.contact.phone') VIRTUAL,
ADD INDEX idx_phone (phone_number);
Thử kiểm tra lại bằng EXPLAIN:
EXPLAIN SELECT id FROM user_profiles WHERE user_email = '[email protected]';
Cột type trong kết quả EXPLAIN sẽ chuyển từ ALL sang ref. Thay vì quét trọn hàng triệu row, MySQL chỉ lookup vài chục bản ghi trên cây index. Thời gian phản hồi hạ từ 2.8 giây xuống còn vỏn vẹn 4 mili-giây.
Gom Composite Index cho bộ lọc phức tạp
Bạn hoàn toàn có thể kẹp chung Generated Column với các cột vật lý thông thường vào một Composite Index:
ALTER TABLE order_items
ADD INDEX idx_discount_amount (discount_percent, total_amount);
Những cạm bẫy cần tránh trên Production
Dù tiện lợi, việc lạm dụng Generated Columns có thể dẫn đến những sự cố khó lường nếu bỏ qua các quy tắc sau:
- Hàm bắt buộc phải là Deterministic: Biểu thức sinh cột phải đảm bảo: cùng input thì output luôn giống hệt nhau. Bạn tuyệt đối không được dùng các hàm biến thiên theo thời gian hay ngẫu nhiên như
NOW(),RAND(),UUID(), hoặc lồng subquery sang bảng khác. - Mặc định chọn VIRTUAL trước: Đừng vội dùng
STOREDtrừ khi biểu thức tính toán quá phức tạp và cần đọc liên tục với tần suất cực cao. Cặp bài trùngVIRTUAL+ Index luôn là lựa chọn tối ưu nhất cho cả dung lượng lẫn I/O đĩa. - Khai báo kiểu dữ liệu vừa đủ: Khi bóc text từ JSON, hãy định nghĩa kích thước sát với thực tế, ví dụ
VARCHAR(100)thay vì quăng vàoVARCHAR(255)hayTEXT. Việc này giúp tiết kiệm bộ nhớ đệm (buffer pool) và giảm kích thước index. - Cẩn trọng khi ALTER bảng lớn: Thêm cột
VIRTUALtrên MySQL 8 là Instant/In-Place DDL, chạy gần như tức thì và không lock bảng. Nhưng nếu thêm cộtSTOREDtrên bảng hàng chục triệu row, MySQL sẽ quét toàn bộ dữ liệu để ghi đĩa, gây nghẽn I/O và lock bảng. Hãy test kỹ trên môi trường Staging trước khi chạy migration trên production.

