Posted inMySQL

Thêm Column vào bảng 200 triệu bản ghi trong 0.1 giây: Sức mạnh của ALGORITHM=INSTANT trong MySQL 8.0

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

Nỗi ám ảnh mang tên ALTER TABLE và bảng dữ liệu 100GB

Nếu từng toát mồ hôi khi thực hiện lệnh ALTER TABLE trên một bảng orders chứa khoảng 200 triệu bản ghi, bạn sẽ hiểu cảm giác này. Chỉ một thao tác thêm cột đơn giản cũng có thể khiến hệ thống “đứng hình”. Database nghẽn, ứng dụng trả về lỗi timeout, và áp lực từ phía người dùng bắt đầu đổ dồn vào team vận hành.

Trước đây, với MySQL 5.7, mình từng phải chờ hơn 2 tiếng đồng hồ chỉ để thêm một cột status vào bảng 80GB. Trong suốt thời gian đó, bảng bị khóa chặt (table lock), coi như toàn bộ tính năng liên quan đến đơn hàng phải tạm dừng. Tuy nhiên, từ phiên bản MySQL 8.0, ALGORITHM=INSTANT đã biến nỗi ác mộng này thành một thao tác nhẹ nhàng trong tích tắc.

Ba thế hệ thay đổi cấu trúc bảng: COPY, INPLACE và INSTANT

Để thấy được bước tiến này, hãy nhìn vào cách InnoDB xử lý dữ liệu qua từng thời kỳ:

  • ALGORITHM=COPY: MySQL tạo bảng mới, copy từng dòng dữ liệu sang rồi xóa bảng cũ. Với bảng 100 triệu dòng, việc này cực kỳ tốn IO và khóa cứng quyền ghi (Write Lock).
  • ALGORITHM=INPLACE: Dữ liệu được sửa trực tiếp trên file nhưng vẫn yêu cầu rebuild lại bảng trong nhiều kịch bản. Nó giảm thời gian khóa bảng nhưng vẫn tiêu tốn tài nguyên server đáng kể.
  • ALGORITHM=INSTANT: Xuất hiện từ MySQL 8.0.12, đây là một cuộc cách mạng. MySQL chỉ cập nhật metadata trong Data Dictionary mà không đụng vào dữ liệu thực tế trên đĩa. Lệnh chạy xong trong mili giây, bất kể bảng nặng 1GB hay 1TB.

Tại sao INSTANT lại nhanh đến mức vô lý?

Bí mật nằm ở cơ chế “lazy update”. Thay vì ghi giá trị mặc định cho hàng trăm triệu dòng cũ ngay lập tức, MySQL thực hiện một thủ thuật thông minh. Nó chỉ ghi chú vào metadata: “Kể từ bản ghi số N, bảng có thêm cột X với giá trị mặc định là Y”.

Dữ liệu cũ trên đĩa vẫn giữ nguyên trạng thái. MySQL chỉ thực sự ghi dữ liệu cho cột mới khi bạn thực hiện lệnh UPDATE trên dòng đó hoặc thêm bản ghi mới. Nhờ bỏ qua bước quét toàn bộ bảng, thao tác ALTER diễn ra gần như tức thì.

Điều kiện để kích hoạt sức mạnh INSTANT

Dù rất mạnh, bạn cần nắm rõ luật chơi để tránh việc MySQL tự động chuyển về thuật toán cũ:

  1. Phiên bản: Tối thiểu 8.0.12. Đặc biệt, hãy nâng cấp lên 8.0.29 để hỗ trợ cả DROP COLUMN và thêm cột ở bất kỳ vị trí nào (thay vì chỉ thêm vào cuối bảng).
  2. Định dạng dòng: Bảng phải ở dạng DYNAMIC (mặc định của MySQL 8.0), REDUNDANT hoặc COMPACT.
  3. Thao tác: Các lệnh thêm cột (có giá trị mặc định), đổi tên cột hoặc sửa metadata là những ứng viên hoàn hảo cho INSTANT.

Thực hành: Thao tác an toàn trên Production

Thay vì chạy lệnh ALTER và cầu nguyện, hãy chỉ định rõ thuật toán. Nếu không thỏa mãn điều kiện, MySQL sẽ báo lỗi ngay thay vì tự ý dùng COPY làm treo hệ thống:

-- Kiểm tra nhanh phiên bản
SELECT VERSION();

-- Thêm cột loyalty_points vào bảng users khổng lồ
ALTER TABLE users 
ADD COLUMN loyalty_points INT DEFAULT 0, 
ALGORITHM=INSTANT;

Kết quả thường thấy là Query OK, 0 rows affected (0.01 sec). Con số 0.01 giây trên bảng hàng trăm triệu bản ghi chính là giá trị mà INSTANT mang lại.

Cách kiểm tra bảng đã sử dụng INSTANT chưa

Bạn có thể soi vào “nội tạng” của Database để biết bảng đang chứa bao nhiêu cột được tạo nhanh:

SELECT NAME, INSTANT_COLS 
FROM information_schema.INNODB_TABLES 
WHERE NAME LIKE '%users%';

Nếu INSTANT_COLS lớn hơn 0, nghĩa là bảng của bạn đã được tối ưu bằng metadata.

Kinh nghiệm thực chiến với bảng dữ liệu lớn

Sau nhiều lần vận hành các DB Production hàng Terabyte, mình rút ra 4 lưu ý quan trọng:

  • Hạn chế thêm cột vào giữa: Dù 8.0.29 đã hỗ trợ, nhưng thêm vào cuối bảng vẫn là cách an toàn và ổn định nhất cho mọi phiên bản.
  • Độ trễ truy vấn: Sau khi dùng INSTANT, các câu lệnh SELECT * có thể chậm hơn vài miligiây do MySQL phải tính toán giá trị mặc định on-the-fly. Đừng quá lo lắng, con số này rất nhỏ.
  • Dọn dẹp Metadata: Sau hàng chục lần ALTER INSTANT, metadata sẽ trở nên cồng kềnh. Hãy lên lịch OPTIMIZE TABLE vào khung giờ vắng khách (ví dụ 2h sáng) để đưa bảng về trạng thái sạch nhất.
  • Backup là sống còn: Công nghệ có hiện đại đến đâu cũng không thay thế được một bản backup. Luôn đảm bảo có snapshot dữ liệu trước khi tác động vào cấu trúc bảng lớn.

Tạm kết

ALGORITHM=INSTANT là lý do lớn nhất để bạn cân nhắc nâng cấp lên MySQL 8.0 nếu vẫn đang ở 5.7. Nó giúp team Tech linh hoạt hơn trong việc phát triển tính năng mà không phải thấp thỏm lo âu mỗi khi cần thay đổi Schema.

Hy vọng những chia sẻ này giúp bạn tự tin hơn khi xử lý các bảng dữ liệu khổng lồ. Nếu có bất kỳ thắc mắc nào về tối ưu MySQL, đừng ngần ngại để lại comment bên dưới nhé!

Share:
Tags: