Sự cố cơ sở dữ liệu lúc 2 giờ sáng
Đó là lúc 2 giờ sáng khi các cảnh báo giám sát đổ chuông trên điện thoại của tôi. CPU của cơ sở dữ liệu production đã vọt lên 99%, và thời gian phản hồi API kéo dài tới 10 giây thay vì 200ms như thường lệ. Tôi kiểm tra slow query log và phát hiện ra một mớ hỗn độn. Chúng tôi đang lọc một bảng dữ liệu 5GB với 2,5 triệu dòng bằng logic nghiệp vụ nằm sâu bên trong một cột JSON.
Truy vấn trông như thế này:
SELECT * FROM orders WHERE JSON_EXTRACT(order_details, '$.status') = 'shipped';Vì MySQL không thể đánh chỉ mục (index) trực tiếp một đường dẫn cụ thể bên trong tài liệu JSON, mọi yêu cầu đều buộc phải quét toàn bộ bảng (full table scan). Engine phải phân tích cú pháp JSON cho từng dòng một, mọi lúc. Đây là một nguyên nhân gây giảm hiệu suất phổ biến, nhưng đó chính xác là những gì mà Generated Columns được thiết kế để giải quyết.
Virtual vs. Stored: Hai cách để tối ưu hóa
MySQL cung cấp hai chiến lược để xử lý dữ liệu được tạo tự động (generated data). Việc chọn đúng phương pháp sẽ quyết định hệ thống của bạn có mở rộng mượt mà hay sẽ gặp tắc nghẽn khi dữ liệu tăng trưởng.
Virtual Generated Columns (Cột ảo)
Cột Virtual là tùy chọn mặc định. Nó không chiếm thêm không gian trên đĩa cứng. Thay vào đó, MySQL sẽ tính toán giá trị ngay lập tức (on the fly) mỗi khi bạn đọc dòng đó. Bạn có thể lo lắng rằng việc tính toán lại sẽ chậm, nhưng có một ưu điểm lớn: bạn có thể đánh chỉ mục một cột Virtual. Khi bạn làm điều này, MySQL sẽ lưu trữ các giá trị chỉ mục một cách vật lý. Bạn sẽ có được tốc độ của một index tiêu chuẩn mà không cần phải nhân đôi dung lượng lưu trữ cho dữ liệu cột thực tế.
Stored Generated Columns (Cột lưu trữ)
Cột Stored tính toán giá trị trong quá trình INSERT hoặc UPDATE và ghi kết quả vào đĩa cứng. Nó hoạt động giống như một cột thông thường nhưng vẫn được quản lý bởi cơ sở dữ liệu. Đây là lựa chọn tốt hơn cho các tính toán cực kỳ phức tạp và tiêu tốn nhiều tài nguyên CPU. Nó cũng hữu ích nếu bạn sử dụng các công cụ báo cáo cũ không thể hiểu được các trường ảo.
So sánh hai phương pháp
| Tính năng | Virtual Columns | Stored Columns |
|---|---|---|
| Dung lượng đĩa | Thấp (Chỉ Index) | Cao hơn (Lưu toàn bộ dữ liệu) |
| Tốc độ ghi | Nhanh (Không tính toán khi ghi) | Chậm hơn (Tính toán trên mỗi lần ghi) |
| Tốc độ đọc | Nhanh (khi được đánh index) | Nhanh nhất (luôn là dữ liệu vật lý) |
| Tốt nhất cho | Đánh index JSON, Toán học đơn giản | Logic CPU nặng, Đọc không qua index |
Chiến lược được đề xuất
Đối với khoảng 90% khối lượng công việc thực tế, Virtual Generated Columns là lựa chọn thông minh hơn. Chúng cho phép bạn đánh index các trường JSON mà không làm phình kích thước các tệp .ibd. Việc giữ cho dung lượng đĩa nhỏ gọn là rất quan trọng để duy trì tốc độ sao lưu nhanh và nằm trong giới hạn của memory buffer pool.
Gần đây tôi đã chuyển đổi một bộ dữ liệu CSV cũ sang kiến trúc dựa trên JSON. Để xử lý việc nhập dữ liệu ban đầu, tôi đã sử dụng toolcraft.app/vi/tools/data/csv-to-json. Công cụ này chạy hoàn toàn trong trình duyệt, vì vậy không có dữ liệu nhạy cảm nào rời khỏi máy của bạn. Sau khi dữ liệu được cấu trúc dưới dạng JSON, tôi đã thêm các cột Virtual để giúp các trường chính có thể tìm kiếm được.
Triển khai: Tối ưu hóa JSON của bạn
Hãy xem xét một ví dụ thực tế. Giả sử chúng ta có bảng users lưu trữ thông tin hồ sơ trong JSON và chúng ta cần lọc theo thành phố thường xuyên.
Bước 1: Định nghĩa cột Virtual
Thay vì dựa vào JSON thô, chúng ta định nghĩa một cột ảo để trích xuất chuỗi tên thành phố.
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
profile_data JSON,
-- Trích xuất thành phố dưới dạng một trường ảo
user_city VARCHAR(100) GENERATED ALWAYS AS (profile_data->>"$.address.city") VIRTUAL
);Toán tử ->> là rất quan trọng ở đây. Nó là viết tắt của JSON_UNQUOTE(JSON_EXTRACT(...)). Điều này đảm bảo bạn nhận được một chuỗi sạch như “Chicago” thay vì một giá trị JSON nằm trong dấu ngoặc kép như “\”Chicago\””.
Bước 2: Thêm Index
Bản thân cột ảo không giải quyết được vấn đề tốc độ. Bạn phải thêm index để ngăn chặn việc quét toàn bộ bảng.
CREATE INDEX idx_user_city ON users(user_city);Bước 3: Tự động hóa Logic nghiệp vụ
Generated columns cũng ngăn chặn tình trạng “lệch dữ liệu” (data drift), nơi ứng dụng và cơ sở dữ liệu không thống nhất về một giá trị. Ví dụ, bạn có thể tự động tính toán giá cuối cùng:
ALTER TABLE products
ADD COLUMN final_price DECIMAL(10,2)
GENERATED ALWAYS AS (base_price - (base_price * discount_percent / 100)) STORED;Trong kịch bản này, tôi đã sử dụng STORED. Điều này cho phép chúng ta chạy các báo cáo tài chính trên hàng triệu dòng mà không buộc CPU phải tính toán lại cho từng dòng một trong quá trình xuất dữ liệu.
Xác minh kết quả
Sau khi triển khai cột ảo và index trong sự cố lúc 2 giờ sáng đó, tôi đã chạy EXPLAIN cho truy vấn. Kết quả khác biệt một trời một vực. Kiểu truy cập (access type) đã chuyển từ quét toàn bộ bảng (ALL) sang tìm kiếm theo ref bằng cách sử dụng idx_user_city. Thời gian thực thi giảm từ 8 giây xuống còn chỉ 1,2 mili giây. Tải CPU của RDS ngay lập tức giảm xuống còn 15%, và cuối cùng tôi đã có thể đi ngủ.
Những điểm chính cần nhớ
Đừng coi JSON như một “hộp đen” nếu bạn đang xây dựng các ứng dụng hiện đại với MySQL. Hãy sử dụng Virtual Generated Columns để hiển thị các trường mà bạn thường xuyên truy vấn. Điều này giúp mã nguồn ứng dụng của bạn sạch sẽ hơn bằng cách loại bỏ các lời gọi JSON_EXTRACT lộn xộn khỏi ORM. Quan trọng nhất, nó giữ cho cơ sở dữ liệu của bạn luôn nhanh. Hãy dùng Virtual để đánh index và Stored cho các tính toán nặng.
