Giới hạn kích thước bảng MySQL InnoDB

Gần đây tôi đã làm việc với một trường hợp khách hàng trong đó vào những thời điểm dường như ngẫu nhiên, các phần chèn không thành công với lỗi Innodb 139. Đây là một vấn đề khá đơn giản, nhưng do bản chất của nó, nó chỉ có thể ảnh hưởng đến bạn sau khi bạn đã có một hệ thống chạy trong sản xuất được một thời gian

Giả sử bạn có cấu trúc bảng sau

Vỏ bọc

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

TẠO BẢNG ví dụ (

id INT CHƯA KÝ KHÔNG NULL AUTO_INCREMENT,

fname TEXT NOT NULL,

fbình luận TEXT,

ftitle VĂN BẢN KHÔNG NULL,

fsubtitle TEXT NOT NULL,

fcontent TEXT NOT NULL,

fheader TEXT,

ffooter TEXT,

fdisclaimer TEXT,

fcopyright TEXT,

fstylesheet TEXT,

các điều khoản TEXT,

PRIMARY KEY (id)

) Công cụ = InnoDB;

Bây giờ bạn chèn một số dữ liệu thử nghiệm vào đó
mysql> INSERT INTO example
-> VALUES (
->   NULL,
->   'First example',
->   'First comment',
->   'First title',
->   'First subtitle',
->   'First content',
->   'First header',
->   'First footer',
->   'First disclaimer',
->   'First copyright',
->   'First stylesheet',
->   'First terms'
-> );
Query OK, 1 row affected (0.03 sec)

Tất cả đều ổn

Bây giờ bạn bắt đầu sử dụng cấu trúc này trong sản xuất và thỉnh thoảng gặp lỗi này.
ERROR 1030 (HY000) at line 1: Got error 139 from storage engine

Điều gì đang xảy ra?

Innodb cung cấp cho bạn lỗi này khi nó không thể lưu trữ tất cả các cột có độ dài thay đổi cho một hàng nhất định trên một trang cơ sở dữ liệu

Innodb có giới hạn cho kích thước hàng tối đa, ít hơn một nửa trang cơ sở dữ liệu (toán học thực tế là 16k-(tiêu đề trang + đoạn giới thiệu trang)/2. Đối với kích thước trang mặc định là 16kb. điều này đặt giới hạn ~ 8000 byte cho kích thước hàng (8126 theo báo cáo của bản cài đặt thử nghiệm của tôi). Giới hạn này là hệ quả của việc InnoDB lưu trữ hai hàng trên một trang. Tuy nhiên, nếu bạn đang sử dụng nén, bạn có thể lưu trữ một hàng trên một trang
Nếu hàng của bạn có các cột có độ dài thay đổi và kích thước hàng hoàn chỉnh vượt quá giới hạn này, thì InnoDB sẽ chọn các cột có độ dài thay đổi để lưu trữ ngoài trang

Trong những trường hợp này, 768 byte đầu tiên của mỗi cột có độ dài thay đổi được lưu trữ cục bộ và phần còn lại được lưu trữ bên ngoài trang (hành vi này là phiên bản cụ thể, xem https. //www. percona. com/blog/2010/02/09/blob-storage-in-innodb/ để tham khảo thêm)

Điều đáng nói là giới hạn này áp dụng cho kích thước byte của các giá trị, không phải kích thước ký tự. Điều này có nghĩa là nếu bạn chèn một chuỗi 500 ký tự với tất cả các ký tự nhiều byte vào cột VARCHAR(500), thì giá trị đó cũng sẽ được chọn để lưu trữ ngoài trang. Điều này có nghĩa là có thể bạn gặp phải lỗi này sau khi chuyển đổi từ bộ ký tự ngôn ngữ bản địa sang utf8, vì nó có thể tăng kích thước đáng kể

Nếu bạn có hơn 10 cột có độ dài thay đổi và mỗi cột vượt quá 768 byte thì bạn sẽ có ít nhất 8448 byte cho bộ nhớ cục bộ, không tính các cột có độ dài cố định khác. Điều này vượt quá giới hạn và do đó bạn gặp lỗi

Bạn không cần phải lấy nó luôn, vì điều này không được đánh giá ở định nghĩa bảng, mà ở phần chèn hàng. Bạn có thể chỉ có một bảng với 50 cột có độ dài thay đổi, nhưng nếu độ dài tối đa của chúng là 50 byte, thì bạn vẫn còn nhiều chỗ cho bộ nhớ cục bộ

Điều đầu tiên cần nhấn mạnh ở đây là sự cần thiết phải kiểm tra thích hợp. Nếu bạn chỉ chèn dữ liệu giả vào cơ sở dữ liệu của mình, bạn có thể không phát hiện được các lỗi phụ thuộc vào dữ liệu quan trọng như lỗi này

Bây giờ, làm thế nào bạn có thể giải quyết vấn đề này?

Dưới đây là một vài cách tiếp cận bạn có thể thực hiện để giải quyết vấn đề này

Nâng cấp lên định dạng Barracuda

Đây có lẽ là cách tiếp cận đơn giản nhất và vì Barracuda chỉ sử dụng con trỏ 20 byte cho các loại có độ dài thay đổi, nên vấn đề này sẽ biến mất

Giới hạn kích thước của các cột có độ dài thay đổi

Đây là một cách tiếp cận khá rõ ràng và nếu dữ liệu của bạn cho phép thì đó cũng là một trong những cách đơn giản nhất để thực hiện
Chỉ vì bạn cần một trường có độ dài thay đổi, điều đó không có nghĩa là bạn cần có khả năng lưu trữ dữ liệu với giá trị tối đa cho phép của trường đó. Vì vậy, nếu miền vấn đề của bạn cho phép bạn giới hạn kích thước của các trường này mà không ảnh hưởng đến các trường hợp sử dụng của bạn, thì đây là cách nên làm

Sử dụng các chức năng COMPRESS/UNCOMPRESS

Điều này cũng tương đối đơn giản để thực hiện, mặc dù điều đó phụ thuộc vào vị trí trong mã của bạn mà bạn sử dụng các trường này. Nếu bạn có API truy cập dữ liệu theo mô-đun, bạn không cần phải thay đổi mã ở nhiều nơi. Bạn có thể giành được bao nhiêu tiền với phương pháp này phụ thuộc rất nhiều vào loại dữ liệu bạn đang chèn (gợi ý. dữ liệu của bạn càng ngẫu nhiên thì quá trình nén càng tệ hơn). Một điều quan trọng cần lưu ý là kết quả của hàm COMPRESS luôn ở dạng nhị phân, vì vậy, chẳng hạn, bạn nên sử dụng BLOB thay vì cột TEXT để lưu trữ. Bạn có thể tìm thêm thông tin về chức năng này tại đây

Chia bảng theo cách mà bạn không có nhiều hơn 10 cột có độ dài thay đổi trên mỗi bảng

Cách tiếp cận này có nhiều tác động hơn đối với mã hiện tại của bạn, vì nó yêu cầu bạn viết lại dưới dạng truy vấn nối truy cập tất cả các cột trong bảng gốc. Nó cũng yêu cầu bạn thay đổi cách bạn chèn/sửa đổi dữ liệu, vì có nhiều bảng hơn và ví dụ: bạn sẽ phải chạy bất kỳ bản cập nhật nhiều bảng nào trong một giao dịch. Nó cũng sẽ tác động đến cả tốc độ đọc và sửa đổi, chính xác là do độ phức tạp của phép nối được thêm vào. Tuy nhiên, nếu bạn có quá nhiều cột có độ dài thay đổi và bạn không thể giới hạn kích thước của chúng thì đây có thể là giải pháp khả thi duy nhất

Kết hợp tất cả các trường có độ dài thay đổi của bạn thành một BLOB duy nhất và thực hiện phân tách ở cấp ứng dụng

Điều này sẽ luôn hoạt động (trừ khi bạn có quá nhiều cột có độ dài cố định mà bạn vẫn vượt quá 8000 byte, nhưng trong trường hợp đó, tôi nghĩ bạn gặp vấn đề lớn hơn các giới hạn bên trong của Innodb), mặc dù bạn vẫn cần thay đổi cách ứng dụng của mình sử dụng dữ liệu này. Bạn có thể giới hạn các thay đổi đối với ứng dụng của mình nếu bạn chọn định dạng XML và bạn sử dụng các hàm tích hợp sẵn của MySQL để xử lý dữ liệu này. Tuy nhiên, cách tiếp cận này có tác động tiêu cực khác, vì nó ngăn cản bạn sử dụng trực tiếp các toán tử/hàm SQL trên các trường riêng lẻ mà bạn muốn lưu trữ. Nó cũng buộc bạn phải đọc/ghi nhiều dữ liệu hơn cùng một lúc, trong khi một trong những điều thú vị về lưu trữ blob tràn là nếu các cột không được yêu cầu như một phần của quá trình đọc theo hàng, thì chúng hoàn toàn không cần phải chạm vào.

Tất nhiên, những cách tiếp cận này có thể được kết hợp. Ví dụ: sử dụng trường BLOB duy nhất với COMPRESS/UNCOMPRESS có thể mang lại kết quả tuyệt vời

Cuối cùng, hãy để tôi đề cập rằng việc xử lý tràn trang liên quan đến những thứ khác mà người ta nên xem xét. Không có trang tràn nào được chia sẻ, do đó, một đốm màu 769 byte sẽ nhận được trang tràn 16k cho chính nó. Ngoài ra, các trang tràn được phân bổ mỗi lần 1 trang cho đến khi bạn đạt 32 trang. Sau đó, chúng được phân bổ một mức độ tại một thời điểm (64 trang)

InnoDB có thể xử lý bao nhiêu hàng?

Trong InnoDB, với giới hạn về kích thước bảng là 64 terabyte và giới hạn kích thước hàng của MySQL là 65.535, có thể có 1.073.741.824 hàng .

Kích thước giới hạn của bảng MySQL là gì?

Biểu diễn bên trong của bảng MySQL có giới hạn kích thước hàng tối đa là 65.535 byte , ngay cả khi công cụ lưu trữ có khả năng hỗ trợ lớn hơn . Các cột BLOB và TEXT chỉ đóng góp từ 9 đến 12 byte vào giới hạn kích thước hàng vì nội dung của chúng được lưu trữ riêng biệt với phần còn lại của hàng.

Giới hạn kích thước tệp cho Mysqldump là bao nhiêu?

kích thước tệp tối đa là 16TB (vi. wikipedia. org/wiki/Ext4).

Kích thước trang của MySQL InnoDB là bao nhiêu?

Kích thước trang InnoDB mặc định là 16384 (16KB) . Nếu đĩa của bạn đầy, bạn có thể thêm tệp dữ liệu trên đĩa khác.