Bỏ qua nội dung chính

Khóa chính tự tăng của MySQL có nhất thiết liên tục không?

GuideDatabaseMySQLPhỏng vấn công ty lớnKhoảng 3183 từKhoảng 11 phút

Tác giả: Phi Thiên Tiểu Ngưu Nhục

Bài viết gốc: https://mp.weixin.qq.com/s/qci10h9rJx_COZbHV3aygQ

Như ai cũng biết, khóa chính tự tăng có thể giúp clustered index được insert theo thứ tự tăng dần hết mức có thể, tránh truy vấn ngẫu nhiên, từ đó nâng cao hiệu suất query.

Nhưng trên thực tế, khóa chính tự tăng của MySQL không thể bảo đảm luôn tăng liên tục.

Hãy xem một ví dụ dưới đây. Trước tiên, tạo một bảng như sau:

Giá trị tự tăng được lưu ở đâu?

Dùng insert into test_pk values(null, 1, 1) để insert một dòng, sau đó thực thi lệnh show create table để xem định nghĩa cấu trúc của bảng:

Định nghĩa cấu trúc của bảng trên được lưu trong file cục bộ có hậu tố .frm. Có thể tìm thấy file .frm này trong thư mục data của thư mục cài đặt MySQL:

Từ cấu trúc bảng trên có thể thấy trong định nghĩa bảng xuất hiện AUTO_INCREMENT=2, nghĩa là khi insert dữ liệu lần tiếp theo, nếu cần tự động tạo giá trị tự tăng thì sẽ tạo id = 2.

Tuy nhiên, cần lưu ý rằng giá trị tự tăng không được lưu trong định nghĩa bảng, tức file .frm. Các engine khác nhau có chiến lược lưu giá trị tự tăng khác nhau:

  1. Giá trị tự tăng của engine MyISAM được lưu trong file dữ liệu.

  2. Giá trị tự tăng của engine InnoDB thực ra được lưu trong memory nhưng không được persist. Mỗi khi mở bảng lần đầu, engine sẽ tìm giá trị lớn nhất của id, tức max(id), sau đó lấy max(id)+1 làm giá trị tự tăng hiện tại của bảng.

Ví dụ: hiện tại dòng có id lớn nhất trong bảng của chúng ta là 1, AUTO_INCREMENT=2, đúng không? Lúc này, chúng ta xóa dòng có id=1, AUTO_INCREMENT vẫn là 2.

Nhưng nếu lập tức restart MySQL instance, sau khi restart, AUTO_INCREMENT của bảng này sẽ trở thành 1. Nói cách khác, việc restart MySQL có thể thay đổi giá trị AUTO_INCREMENT của một bảng.

Trên đây là thử nghiệm trên phiên bản MySQL 5.x local của tôi. Trên thực tế, từ MySQL 8.0, bản ghi thay đổi của giá trị tự tăng được ghi vào redo log, giúp persist giá trị tự tăng, nghĩa là nếu xảy ra restart, giá trị tự tăng của bảng có thể được khôi phục về giá trị trước khi MySQL restart dựa trên redo log.

Nói cách khác, với ví dụ trên, sau khi restart instance, AUTO_INCREMENT của bảng này vẫn là 2.

Sau khi hiểu giá trị tự tăng của MySQL được lưu ở đâu, chúng ta hãy xem tiếp cơ chế thay đổi giá trị tự tăng, từ đó dẫn đến scenario đầu tiên khiến giá trị tự tăng không liên tục.

Các scenario khiến giá trị tự tăng không liên tục

Scenario 1: Giá trị tự tăng không liên tục

Trong MySQL, nếu cột id được định nghĩa là AUTO_INCREMENT, khi insert một dòng, giá trị tự tăng hoạt động như sau:

  • Nếu cột id được chỉ định là 0, null hoặc không chỉ định giá trị khi insert dữ liệu, giá trị AUTO_INCREMENT hiện tại của bảng sẽ được điền vào cột này.
  • Nếu cột id được chỉ định một giá trị cụ thể khi insert dữ liệu, câu lệnh sẽ dùng trực tiếp giá trị đó.

Quan hệ giữa giá trị cần insert và giá trị tự tăng hiện tại sẽ quyết định kết quả thay đổi của giá trị tự tăng. Giả sử giá trị cần insert là insert_num, giá trị tự tăng hiện tại là autoIncrement_num:

  • Nếu insert_num < autoIncrement_num, giá trị tự tăng của bảng không thay đổi.
  • Nếu insert_num >= autoIncrement_num, cần thay đổi giá trị tự tăng hiện tại thành giá trị tự tăng mới.

Nói cách khác, nếu id cần insert là 100, giá trị tự tăng hiện tại là 90, insert_num >= autoIncrement_num, thì giá trị tự tăng sẽ được thay đổi thành 101.

Có nhất thiết là như vậy không?

Không hẳn.

Những bạn đã tìm hiểu về distributed id chắc chắn biết rằng, để tránh primary key được tạo bởi hai database bị conflict, có thể thiết lập id tự tăng của một database đều là số lẻ, còn id tự tăng của database kia đều là số chẵn.

Việc dùng số lẻ hay số chẵn được quyết định bởi hai parameter auto_increment_offset và auto_increment_increment. Hai parameter này lần lượt biểu thị giá trị khởi đầu và bước nhảy của giá trị tự tăng, giá trị mặc định đều là 1.

Vì vậy, trong ví dụ trên, các bước tạo giá trị tự tăng mới thực tế là: bắt đầu từ auto_increment_offset, liên tục cộng thêm auto_increment_increment làm bước nhảy, cho đến khi tìm được giá trị đầu tiên lớn hơn 100, dùng giá trị đó làm giá trị tự tăng mới.

Vì vậy, trong trường hợp này, giá trị tự tăng có thể là 102, 103, v.v., dẫn đến primary key id không liên tục.

Đáng tiếc hơn, ngay cả khi giá trị khởi đầu và bước nhảy của giá trị tự tăng đều được đặt là 1, primary key tự tăng cũng không nhất thiết liên tục.

Scenario 2: Giá trị tự tăng không liên tục

Ví dụ, hiện tại chúng ta insert một bản ghi (null,1,1) vào bảng, primary key tạo ra là 1, AUTO_INCREMENT=2, đúng không?

Lúc này, nếu thực thi thêm một lệnh insert (null,1,1), hiển nhiên sẽ báo lỗi Duplicate entry, vì chúng ta đã thiết lập unique index cho cột a:

Nhưng bạn sẽ ngạc nhiên khi phát hiện rằng, dù insert thất bại, giá trị tự tăng vẫn tăng từ 2 lên 3!

Tại sao lại như vậy?

Hãy phân tích quy trình thực thi của câu lệnh insert này:

  1. Executor gọi interface của engine InnoDB để chuẩn bị insert một bản ghi (null,1,1).
  2. InnoDB phát hiện user không chỉ định giá trị của id tự tăng, nên lấy giá trị tự tăng hiện tại 2 của bảng test_pk.
  3. Đổi bản ghi được truyền vào thành (2,1,1).
  4. Đổi giá trị tự tăng của bảng thành 3.
  5. Tiếp tục thực thi thao tác insert dữ liệu. Do bản ghi có a=1 đã tồn tại, nên báo Duplicate key error và trả về.

Có thể thấy thao tác thay đổi giá trị tự tăng diễn ra trước thao tác insert dữ liệu thực sự.

Khi câu lệnh này thực sự được thực thi, do gặp xung đột unique key a, dòng có id = 2 không insert thành công, nhưng giá trị tự tăng cũng không được đổi lại. Vì vậy, khi insert dòng mới sau đó, id tự tăng nhận được sẽ là 3. Nói cách khác, primary key tự tăng không liên tục.

Đến đây, chúng ta đã liệt kê hai trường hợp khiến primary key tự tăng không liên tục:

  1. Giá trị khởi đầu và bước nhảy của giá trị tự tăng khác 1.
  2. Xung đột unique key.

Ngoài ra, rollback transaction cũng dẫn đến tình trạng này.

Scenario 3: Giá trị tự tăng không liên tục

Hiện tại trong bảng của chúng ta có một bản ghi (1,1,1), AUTO_INCREMENT = 3:

Trước tiên, chúng ta insert một dòng (null, 2, 2), tức là (3, 2, 2), đồng thời AUTO_INCREMENT thay đổi thành 4:

Sau đó thực thi đoạn SQL sau:

Mặc dù chúng ta đã insert một bản ghi (null, 3, 3), nhưng do thực hiện rollback nên trong database không có bản ghi này:

Trong trường hợp rollback transaction này, giá trị tự tăng không rollback theo! Như hình dưới đây, giá trị tự tăng vẫn tăng từ 4 lên 5:

Vì vậy, nếu tiếp tục insert một dòng (null, 3, 3), primary key id sẽ được tự động gán là 5:

Vậy tại sao khi xảy ra xung đột unique key hoặc rollback, MySQL không đổi giá trị tự tăng của bảng trở lại? Nếu rollback thì chẳng phải id tự tăng sẽ không bị gián đoạn sao?

Trên thực tế, nguyên nhân chính để làm như vậy là nâng cao performance.

Hãy trực tiếp dùng phương pháp phản chứng để kiểm chứng: giả sử MySQL sẽ thay đổi giá trị tự tăng trở lại khi transaction rollback, điều gì sẽ xảy ra?

Hiện có hai transaction A và B thực thi song song. Khi xin giá trị tự tăng, để tránh hai transaction nhận trùng id tự tăng, chắc chắn cần lock để cấp lần lượt, đúng không?

  1. Giả sử transaction A nhận được id = 1, transaction B nhận được id=2, lúc này giá trị tự tăng của bảng t là 3, sau đó tiếp tục thực thi.
  2. Transaction B commit thành công, nhưng transaction A gặp xung đột unique key, tức dòng có id = 1 insert thất bại. Nếu cho phép transaction A rollback id tự tăng, tức thay đổi giá trị tự tăng hiện tại của bảng trở lại 1, sẽ xảy ra tình huống sau: bảng đã có dòng với id = 2, nhưng giá trị id tự tăng hiện tại là 1.
  3. Khi các transaction khác tiếp tục thực thi, chúng sẽ nhận được id=2. Lúc này, câu lệnh insert sẽ báo “primary key conflict”.

Để giải quyết xung đột primary key này, có hai cách:

  1. Trước mỗi lần request id, kiểm tra trước xem id này đã tồn tại trong bảng hay chưa. Nếu đã tồn tại thì bỏ qua id này.
  2. Mở rộng phạm vi lock của id tự tăng: phải chờ một transaction thực thi xong và commit thì transaction tiếp theo mới được request id tự tăng.

Hiển nhiên, chi phí của hai cách trên đều khá cao và sẽ gây ra vấn đề performance. Xét đến cùng, nguyên nhân nằm ở giả định “cho phép id tự tăng rollback”.

Vì vậy, InnoDB từ bỏ thiết kế này: câu lệnh thực thi thất bại cũng không rollback id tự tăng. Cũng chính vì vậy, MySQL chỉ bảo đảm id tự tăng tăng dần, chứ không bảo đảm liên tục.

Tóm lại, chúng ta đã phân tích ba trường hợp khiến giá trị tự tăng không liên tục. Còn trường hợp thứ tư là insert dữ liệu hàng loạt.

Scenario 4: Giá trị tự tăng không liên tục

Đối với câu lệnh insert dữ liệu hàng loạt, MySQL có chiến lược cấp phát id tự tăng theo batch:

  1. Trong quá trình thực thi câu lệnh, lần đầu request id tự tăng sẽ được phân bổ 1 id.
  2. Sau khi dùng hết 1 id, lần thứ hai câu lệnh này request id tự tăng sẽ được phân bổ 2 id.
  3. Sau khi dùng hết 2 id, vẫn là câu lệnh này, lần thứ ba sẽ được phân bổ 4 id.
  4. Cứ tiếp tục như vậy, mỗi lần cùng một câu lệnh request id tự tăng, số lượng id tự tăng được phân bổ sẽ gấp đôi lần trước.

Cần lưu ý, insert dữ liệu hàng loạt được nói ở đây không phải là câu lệnh insert thông thường chứa nhiều giá trị value!!! Vì khi request id tự tăng cho loại câu lệnh này, có thể tính chính xác cần bao nhiêu id, sau đó request một lần; sau khi request xong thì có thể release lock.

Còn với các loại câu lệnh như insert … select, replace …… select và load data, MySQL không biết chính xác cần request bao nhiêu id, nên dùng chiến lược cấp phát theo batch này. Dù sao request từng id một thực sự quá chậm.

Ví dụ, giả sử hiện tại bảng của chúng ta có dữ liệu như sau:

Chúng ta tạo một bảng test_pk2 có cùng định nghĩa cấu trúc với bảng test_pk hiện tại:

Sau đó dùng insert...select để insert dữ liệu hàng loạt vào bảng teset_pk2:

Có thể thấy dữ liệu đã được import thành công.

Tiếp theo hãy xem giá trị tự tăng của test_pk2 là bao nhiêu:

Theo phân tích trên, giá trị này là 8 chứ không phải 6.

Cụ thể, insert……select thực tế đã insert 5 dòng vào bảng: (1,1), (2,2), (3,3), (4,4), (5,5). Tuy nhiên, 5 dòng này được cấp phát id tự tăng qua 3 lần. Kết hợp với chiến lược cấp phát theo batch, số lượng id tự tăng nhận được mỗi lần gấp đôi lần trước, nên:

  • Lần cấp phát đầu tiên nhận được 1 id: id=1.
  • Lần cấp phát thứ hai được phân bổ 2 id: id=2 và id=3.
  • Lần cấp phát thứ ba được phân bổ 4 id: id=4, id = 5, id = 6, id=7.

Vì câu lệnh này thực tế chỉ dùng 5 id, nên id=6 và id=7 bị lãng phí. Sau đó, khi thực thi insert into test_pk2 values(null,6,6), dữ liệu thực tế được insert là (8,6,6):

Tóm tắt

Bài viết này tổng hợp 4 trường hợp khiến giá trị tự tăng không liên tục:

  1. Giá trị khởi đầu và bước nhảy của giá trị tự tăng không được đặt là 1.
  2. Xung đột unique key.
  3. Rollback transaction.
  4. Insert hàng loạt (chẳng hạn câu lệnh insert...select).

Lời cuối

Nếu nội dung hữu ích với bạn, hãy tiện tay tặng JavaGuide một Star miễn phí để ủng hộ: GitHub | Gitee.

JavaGuide đã được duy trì gần bảy năm, tích lũy 6100+ commit, với sự chung tay hoàn thiện của 620+ contributor. Star, phản hồi và PR của bạn đều là động lực để dự án tiếp tục cập nhật.

Nếu bạn đang chuẩn bị phỏng vấn backend / phát triển ứng dụng AI, có thể tham khảo Knowledge Planet của tôi, bao gồm các project thực tế về backend và AI, tối ưu CV, hỏi đáp 1-1 và tài liệu về các trọng điểm thường gặp, đã được duy trì liên tục sáu năm.