Làm cách nào để lấy kích thước hàng trong MySQL?
|
Nguyên nhân sâu xa là do InnoDB có kích thước hàng tối đa gần tương đương với một nửa giá trị của biến hệ thống. Xem để biết thêm thông tin Show
Các định dạng hàng của InnoDB hoạt động xung quanh giới hạn này bằng cách lưu trữ một số loại cột có độ dài thay đổi trên các trang tràn. Tuy nhiên, các định dạng hàng khác nhau có thể lưu trữ các loại dữ liệu khác nhau trên các trang tràn. Một số định dạng hàng có thể lưu trữ nhiều dữ liệu hơn trong các trang tràn hơn các định dạng khác. Ví dụ: các định dạng hàng NĂNG ĐỘNG và NÉN có thể lưu trữ nhiều dữ liệu nhất trong các trang tràn. Để tìm hiểu cách các định dạng hàng InnoDB khác nhau sử dụng các trang tràn, hãy xem các trang sau Kiểm tra các bảng hiện có cho vấn đềInnoDB hiện không có cách dễ dàng để kiểm tra tất cả các bảng hiện có để xác định bảng nào gặp sự cố này. Xem MDEV-20400 để biết thêm thông tin Một phương pháp để kiểm tra một bảng hiện có cho vấn đề này là bật chế độ nghiêm ngặt của InnoDB, sau đó thử tạo một bản sao của bảng bằng. Nếu bảng có vấn đề này, thì hoạt động sẽ thất bại. Ví dụ SET SESSION innodb_strict_mode=ON; CREATE TABLE tab_dup LIKE tab; ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. Tìm tất cả các bảng hiện có vấn đềTập lệnh shell sau đây sẽ đọc qua máy chủ MariaDB để xác định mọi bảng có định nghĩa kích thước hàng quá lớn so với định dạng hàng và kích thước trang của máy chủ. Nó chạy trên hầu hết các bản phân phối phổ biến của Linux Để chạy tập lệnh, hãy sao chép mã bên dưới vào tập lệnh shell có tên [Warning] InnoDB: Cannot add field col in table db1.tab because after adding it, the row size is 8478 which is greater than maximum allowed size (8126) for a record on index leaf page.3, làm cho tập lệnh đó có thể thực thi được bằng lệnh [Warning] InnoDB: Cannot add field col in table db1.tab because after adding it, the row size is 8478 which is greater than maximum allowed size (8126) for a record on index leaf page.4 và gọi tập lệnh đó bằng các tham số sau ./rowsize.sh host user password Khi tập lệnh chạy, nó sẽ hiển thị tên của cơ sở dữ liệu tạm thời mà nó tạo, để nếu tập lệnh bị gián đoạn trước khi dọn dẹp, cơ sở dữ liệu có thể dễ dàng được xác định và xóa thủ công Khi tập lệnh chạy, nó sẽ xuất ra một dòng báo cáo cơ sở dữ liệu và tên bảng cho mỗi bảng mà nó thấy có vấn đề về hàng quá khổ. Nếu nó không tìm thấy, nó sẽ in thông báo sau. "Không tìm thấy bảng có kích thước hàng quá lớn. " Trong cả hai trường hợp, tập lệnh in một dòng cuối cùng để thông báo khi hoàn thành. [Warning] InnoDB: Cannot add field col in table db1.tab because after adding it, the row size is 8478 which is greater than maximum allowed size (8126) for a record on index leaf page.5 #!/bin/bash
[ -z "$3" ] && echo "Usage: $0 host user password" >&2 && exit 1
dt="tmp_$RANDOM$RANDOM"
mysql -h $1 -u $2 -p$3 -ABNe "create database $dt;"
[ $? -ne 0 ] && echo "Error: $0 terminating" >&2 exit 1
echo
echo "Created temporary database ${dt} on host $1"
echo
c=0
for d in $(mysql -h $1 -u $2 -p$3 -ABNe "show databases;" | egrep -iv "information_schema|mysql|performance_schema|$dt")
do
for t in $(mysql -h $1 -u $2 -p$3 -ABNe "show tables;" $d)
do
tc=$(mysql -h $1 -u $2 -p$3 -ABNe "show create table $t\\G" $d | egrep -iv "^\*|^$t")
echo $tc | grep -iq "ROW_FORMAT"
if [ $? -ne 0 ]
then
tf=$(mysql -h $1 -u $2 -p$3 -ABNe "select row_format from information_schema.innodb_sys_tables where name = '${d}/${t}';")
tc="$tc ROW_FORMAT=$tf"
fi
ef="/tmp/e$RANDOM$RANDOM"
mysql -h $1 -u $2 -p$3 -ABNe "set innodb_strict_mode=1; set foreign_key_checks=0; ${tc};" $dt >/dev/null 2>$ef
[ $? -ne 0 ] && cat $ef | grep -q "Row size too large" && echo "${d}.${t}" && let c++ || mysql -h $1 -u $2 -p$3 -ABNe "drop table if exists ${t};" $dt
rm -f $ef
done
done
mysql -h $1 -u $2 -p$3 -ABNe "set innodb_strict_mode=1; drop database $dt;"
[ $c -eq 0 ] && echo "No tables with rows size too large found." || echo && echo "$c tables found with row size too large."
echo
echo "$0 done."
Giải quyết vấn đềCó một số giải pháp tiềm năng có sẵn để giải quyết vấn đề này Chuyển đổi bảng sang định dạng hàng DYNAMICNếu bảng đang sử dụng định dạng hàng DỰ PHÒNG hoặc NHỎ GỌN, thì một giải pháp tiềm năng cho vấn đề này là chuyển đổi bảng để sử dụng định dạng hàng ĐỘNG thay thế Nếu ban đầu các bảng của bạn được tạo trên phiên bản cũ hơn của MariaDB hoặc MySQL, thì bảng của bạn có thể đang sử dụng một trong các định dạng hàng cũ hơn của InnoDB
Định dạng hàng NĂNG ĐỘNG có thể lưu trữ nhiều dữ liệu hơn trên các trang tràn so với các định dạng hàng cũ hơn này, do đó, định dạng hàng này thực sự có thể lưu trữ dữ liệu của bảng một cách an toàn. Xem để biết thêm thông tin Do đó, một giải pháp tiềm năng cho lỗi Kích thước hàng quá lớn là chuyển đổi bảng sang sử dụng định dạng hàng NĂNG ĐỘNG. Ví dụ ALTER TABLE tab ROW_FORMAT=DYNAMIC; Bạn có thể sử dụng bảng INNODB_SYS_TABLES trong cơ sở dữ liệu information_schema để tìm tất cả các bảng sử dụng định dạng hàng REDUNDANT hoặc COMPACT. Điều này hữu ích nếu bạn muốn chuyển đổi tất cả các bảng mà bạn vẫn sử dụng các định dạng hàng cũ hơn sang định dạng hàng NĂNG ĐỘNG. Ví dụ: truy vấn sau có thể tìm thấy các bảng đó, đồng thời loại trừ SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE ROW_FORMAT IN('Redundant', 'Compact')
AND NAME NOT IN('SYS_DATAFILES', 'SYS_FOREIGN', 'SYS_FOREIGN_COLS', 'SYS_TABLESPACES', 'SYS_VIRTUAL', 'SYS_ZIP_DICT', 'SYS_ZIP_DICT_COLS');
Trong MariaDB 10. 2 trở lên, định dạng hàng NĂNG ĐỘNG là định dạng hàng mặc định. Nếu ban đầu các bảng của bạn được tạo trên một trong những phiên bản mới hơn này thì có thể chúng đã sử dụng định dạng hàng này. Trong trường hợp đó, bạn có thể cần thử giải pháp tiếp theo Lắp thêm cột trên các trang trànNếu bảng đã sử dụng định dạng hàng NĂNG ĐỘNG, thì một giải pháp tiềm năng khác cho vấn đề này là thay đổi lược đồ bảng để định dạng hàng có thể lưu trữ nhiều cột hơn trên các trang tràn Để InnoDB lưu trữ một số cột có độ dài thay đổi trên các trang tràn, có thể cần tăng độ dài của các cột đó Do đó, một giải pháp phản trực giác cho lỗi Kích thước hàng quá lớn trong nhiều trường hợp thực tế là tăng độ dài của một số cột có độ dài thay đổi, để định dạng hàng của InnoDB có thể lưu trữ chúng trên các trang tràn Một số cách có thể để thay đổi lược đồ bảng được liệt kê dưới đây Chuyển đổi một số cột thành |
