Việc thay đổi cấu trúc bảng trong MySQL thường liên quan đến vấn đề khóa bảng ở cấp độ toàn bảng.
Điều này đặc biệt quan trọng đối với các bảng có lượng dữ liệu lớn vì nó có thể ảnh hưởng đáng kể đến hiệu suất của hệ thống nghiệp vụ.
Bằng cách tối ưu hóa các thao tác thay đổi cấu trúc bảng, lập trình viên có thể tránh hoặc giảm thiểu thời gian bảng bị khóa, từ đó đảm bảo hệ thống hoạt động trơn tru.
Giới thiệu về khóa bảng
Khóa bảng là việc MySQL khóa toàn bộ bảng trong một số thao tác nhất định để đảm bảo tính nhất quán dữ liệu.
Cụ thể:
Khi thực hiện câu lệnh ALTER TABLE, MySQL theo mặc định sẽ khóa bảng, ngăn chặn các giao dịch khác đọc hoặc ghi dữ liệu cho đến khi thao tác hoàn tất.
Việc này ít ảnh hưởng đến các bảng nhỏ hoặc hệ thống có độ đồng thời thấp.
Tuy nhiên, khi xử lý các tập dữ liệu lớn hoặc hệ thống có độ đồng thời cao, việc khóa bảng có thể gây ra nghẽn cổ chai nghiêm trọng và thậm chí dẫn đến sập hệ thống.
Tác động cụ thể của việc khóa bảng
Khi MySQL thực hiện các thao tác như thêm cột, khóa bảng có thể gây ra các vấn đề sau:
- Chậm truy vấn: Tất cả các truy vấn SQL trên bảng đó sẽ bị chặn cho đến khi bảng được mở khóa.
- Chặn thao tác ghi: Tất cả các thao tác ghi (INSERT, UPDATE, DELETE) sẽ bị tạm dừng.
- Làm chậm hệ thống: Nếu thao tác ALTER TABLE mất nhiều thời gian, hiệu suất toàn hệ thống sẽ giảm rõ rệt, thậm chí có thể gây ngừng hoạt động.
Tuy nhiên, trong các phiên bản mới hơn của MySQL, khi thêm cột vào bảng sử dụng InnoDB, việc khóa bảng có thể không xảy ra.
Trước MySQL 5.6
Trong các phiên bản cũ, sử dụng ALTER TABLE để thêm cột vào bảng InnoDB sẽ khóa toàn bảng.
Các bước thực hiện gồm:
- Tạo bảng tạm có cấu trúc mới.
- Nhập dữ liệu từ bảng cũ vào bảng tạm.
- Xóa bảng cũ.
- Đổi tên bảng tạm thành tên bảng ban đầu.
Việc này gây tốn thời gian và tài nguyên, đặc biệt với bảng lớn.
MySQL 5.6
Từ phiên bản 5.6, InnoDB hỗ trợ DDL trực tuyến (Online DDL), cho phép một số thao tác được thực hiện mà không khóa bảng.
Ví dụ:
ALTER TABLE your_table ADD COLUMN new_column INT ALGORITHM=INPLACE, LOCK=NONE;
ALGORITHM=INPLACE: Chỉnh sửa cấu trúc bảng trực tiếp mà không tạo bản sao mới.LOCK=NONE: Không khóa bảng, cho phép các phiên khác tiếp tục đọc và ghi dữ liệu.
Hai trường hợp:
- Thêm cột nullable: Thường không khóa bảng.
- Thêm cột NOT NULL: Cũng được tối ưu để không cần sao chép dữ liệu.
Tuy nhiên, vẫn có ảnh hưởng nhỏ về hiệu suất do việc cập nhật metadata hoặc ghi log.
MySQL 8.0
Phiên bản 8.0 giới thiệu thêm nhiều cải tiến:
✅ Atomic DDL
Giảm khóa bảng bằng cách đảm bảo DDL là nguyên tử (atomic) — thực hiện và hoàn thành ngay lập tức hoặc hoàn tác toàn bộ.
✅ Cập nhật Metadata Tức Thì
Cập nhật metadata khi thao tác thêm cột được thực hiện, không cần chờ hoàn tất toàn bộ thao tác.
✅ Tối ưu hóa InnoDB
- Thêm cột không cần sao chép dữ liệu.
- Thao tác nhẹ hơn, nhanh hơn.
✅ Cập nhật Metadata Gia Tăng
Chỉ cập nhật phần metadata bị ảnh hưởng, không phải toàn bộ bảng.
DDL trực tuyến (Online DDL)
Online DDL là việc thực hiện thao tác tạo, sửa, xóa bảng mà không làm gián đoạn hoạt động hệ thống.
Ba thuật toán chính:
- COPY – Cũ, không online (trước MySQL 5.6).
- INPLACE – MySQL 5.6.
- INSTANT – MySQL 8.0.12 (được đóng góp bởi đội ngũ DBA của Tencent).
Nguyên lý thực thi DDL
Mọi DDL đều có 3 giai đoạn:
- Chuẩn bị
- Thực thi
- Cam kết
Khác biệt nằm ở cách MySQL tối ưu hóa mỗi giai đoạn tùy vào thuật toán.
Tóm tắt
- Kể từ MySQL 5.6, việc thêm cột hoặc sửa bảng thường không cần khóa bảng.
- Tuy nhiên, với bảng cực lớn hoặc phiên bản MySQL cũ, có thể vẫn xảy ra khóa bảng.
- Khuyến nghị: Thực hiện ALTER TABLE vào giờ thấp điểm và luôn kiểm tra trước bằng:
EXPLAIN ALTER TABLE your_table ADD COLUMN new_column INT;
Cảm ơn các bạn đã theo dõi!