Oblicz rozmiar wiersza i maksymalny rozmiar wiersza dla tabeli

Problem:

Czy istnieje jakiś sposób obliczenia liczby bajtów zajmowanych przez tworzenie tabeli, wiem, że możesz uzyskać pewne informacje z information_schema.tables, ale ta informacja nie jest wystarczająco dokładna.

Faktycznie wymagana jest liczba bajtów zgodnie z definicją tabeli tylko dla innodb, a sortowanie można również uznać za utf-8-general-ci

Na przykład test tabeli wygląda następująco

utworzyć test tabeli
(
col1 varchar (25),
col2 int,
col3 varchar (3),
col4 char (15),
col5 datetime
);

Teraz wymagałaby znajomości całkowitego rozmiaru wiersza, który można akumulować w jednym rzędzie zgodnie z typami kolumn w tabeli.

Znalazłem podobne rozwiązanie w MSSQL, ale potrzebuję jego wersji MySQL

Skrypt do oszacowania rozmiarów wierszy dla dowolnej tabeli

Każda pomoc jest mile widziana.

Po wielu myślach i badaniach znalazłem jedną odpowiedź, która naprawdę pomogła w osiągnięciu tego, co było wymagane. Jest to skrypt perla, a odnośnikiem jest

http://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html

#!/usr/bin/perl
use strict;
$| = 1;

my %DataType = (
"TINYINT"=>1, "SMALLINT"=>2, "MEDIUMINT"=>3, "INT"=>4, "INTEGER"=>4, "BIGINT"=>8,
"FLOAT"=>'$M<=24?4:8', "DOUBLE"=>8,
"DECIMAL"=>'int(($M-$D)/9)*4+int(((($M-$D)%9)+1)/2)+int($D/9)*4+int((($D%9)+1)/2)',
"NUMERIC"=>'int(($M-$D)/9)*4+int(((($M-$D)%9)+1)/2)+int($D/9)*4+int((($D%9)+1)/2)',
"BIT"=>'($M+7)>>3',
"DATE"=>3, "TIME"=>3, "DATETIME"=>8, "TIMESTAMP"=>4, "YEAR"=>1,
"BINARY"=>'$M',"CHAR"=>'$M*$CL',
"VARBINARY"=>'$M+($M>255?2:1)', "VARCHAR"=>'$M*$CL+($M>255?2:1)',
"ENUM"=>'$M>255?2:1', "SET"=>'($M+7)>>3',
"TINYBLOB"=>9, "TINYTEXT"=>9,
"BLOB"=>10, "TEXT"=>10,
"MEDIUMBLOB"=>11, "MEDIUMTEXT"=>11,
"LONGBLOB"=>12, "LONGTEXT"=>12
);

my %DataTypeMin = (
"VARBINARY"=>'($M>255?2:1)', "VARCHAR"=>'($M>255?2:1)'
);

my ($D, $M, $S, $C, $L, $dt, $dp ,$bc, $CL);
my $fieldCount = 0;
my $byteCount = 0;
my $byteCountMin = 0;
my @fields = ();
my $fieldName;
my $tableName;
my $defaultDbCL = 1;
my $defaultTableCL = 1;
my %charsetMaxLen;
my %collationMaxLen;

open (CHARSETS, "mysql -B --skip-column-names information_schema -e 'select CHARACTER_SET_NAME,MAXLEN from CHARACTER_SETS;' |");
%charsetMaxLen = map ( ( /^(\w+)/ => /(\d+)$/ ), <CHARSETS>);
close CHARSETS;

open (COLLATIONS, "mysql -B --skip-column-names information_schema -e 'select COLLATION_NAME,MAXLEN from CHARACTER_SETS INNER JOIN COLLATIONS USING(CHARACTER_SET_NAME);' |");
%collationMaxLen = map ( ( /^(\w+)/ => /(\d+)$/ ), <COLLATIONS>);
close COLLATIONS;

open (TABLEINFO, "mysqldump -d --compact ".join(" ",@ARGV)." |");

while (<TABLEINFO>) {
chomp;
if ( ($S,$C) = /create database.*?`([^`]+)`.*default\scharacter\sset\s+(\w+)/i ) {
$defaultDbCL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : 1;
print "Database: $S".($C?" DEFAULT":"").($C?" CHARSET $C":"")." (bytes per char: $defaultDbCL)\n\n";
next;
}
if ( /^create table\s+`([^`]+)`.*/i ) {
$tableName = $1;
@fields = ();
next;
}
if ( $tableName && (($C,$L) = /^\)(?:.*?default\scharset=(\w+))?(?:.*?collate=(\w+))?/i) ) {
$defaultTableCL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : (exists $collationMaxLen{$L} ? $collationMaxLen{$L} : $defaultDbCL);
print "Table: $tableName".($C||$L?" DEFAULT":"").($C?" CHARSET $C":"").($L?" COLLATION $L":"")." (bytes per char: $defaultTableCL)\n";
$tableName = "";
$fieldCount = 0;
$byteCount = 0;
$byteCountMin = 0;
while ($_ = shift @fields) {
if ( ($fieldName,$dt,$dp,$M,$D,$S,$C,$L) = /\s\s`([^`]+)`\s+([a-z]+)(\((\d+)(?:,(\d+))?\)|\((.*)\))?(?:.*?character\sset\s+(\w+))?(?:.*?collate\s+(\w+))?/i ) {
$dt = uc $dt;
if (exists $DataType{$dt}) {
if (length $S) {
$M = ($S =~ s/(\'.*?\'(?!\')(?=,|$))/$1/g);
$dp = "($M : $S)"
}
$D = 0 if !$D;
$CL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : (exists $collationMaxLen{$L} ? $collationMaxLen{$L} : $defaultTableCL);
$bc = eval($DataType{$dt});
$byteCount += $bc;
$byteCountMin += exists $DataTypeMin{$dt} ? $DataTypeMin{$dt} : $bc;
} else {
$bc = "??";
}
$fieldName.="\t" if length($fieldName) < 8;
print "bytes:\t".$bc."\t$fieldName\t$dt$dp".($C?" $C":"").($L?" COLL $L":"")."\n";
++$fieldCount;
}
}
print "total:\t$byteCount".($byteCountMin!=$byteCount?"\tleast: $byteCountMin":"\t\t")."\tcolumns: $fieldCount\n\n";
next;
}
push @fields, $_;
}
close TABLEINFO;

Dziękuję wszystkim za wspaniałą pomoc.

Musisz znać rozmiar w bajtach każdego pola zgodnie z typem danych ( tutaj odwołanie do MySQL ), a następnie zsumować te wartości razem.

Krok 1:

col1 varchar(25),  2 + avg_byte_len
col2 int,          4
col4 char(15),     1*15 or 3*15 or...
col5 datetime      Pre-5.6: 8; then 5

SELECT AVG(LENGTH(col1)) as avg_byte_len,
       AVG(CHAR_LENGTH(col1) as avg_num_chars FROM ...;

20 znaków angielskich: 2 + 1 * 20
20 znaków środkowo-wschodnich / słowiańskich: 2 + 2 * 20
20 znaków azjatyckich: 2 + 3 * 20
20 znaków emoji: 2 + 4 * 20 (i potrzebujesz utf8mb4)

Krok 2: Dodaj je.

Krok 3: Pomnóż przez około 2 do 3, aby pozwolić na obciążenie InnoDB. Przekonałem się, że ten czynnik zwykle działa. (Ale nie dla małych tabel i niekoniecznie dobrze dla tabel podzielonych na partycje).

Nie widzę powodu, by przyjmować maksymalny rozmiar każdej kolumny.

Możesz zbliżyć się SHOW TABLE STATUSlub information_schemadane równoważne :

Krok 1: SELECT COUNT(*)- nam to zamiastRows

Krok 2: Zdobądź Data_length + Index_length + Data_free

Krok 3: Podziel.

Zrobiłem szorstki skrypt bash, aby obliczyć rozmiar wiersza i ostrzec, jeśli przekroczy limit oparty na schemacie:

#!/bin/bash
​
#
# usage: mysqldump --no-data | check_row_size.sh
#
​
#
#
# https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html#row-size-limits
#
# The maximum row size for an InnoDB table, which applies to data stored locally within a database page, is slightly less than half a page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings.
# For example, the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size.
#
#
# MariaDB [(none)]> show variables like 'innodb_page_size';
#+------------------+-------+
#| Variable_name    | Value |
#+------------------+-------+
#| innodb_page_size | 16384 |
#+------------------+-------+
#1 row in set (0.00 sec)
#
#
# Options:
# 1. Change default innodb_page_size to 32k
# 2. Change storage engine to DYNAMIC for tables
# 3. ?
#
​
#===========================================================================================
# Functions
#===========================================================================================
RETVAL=0
​
calc_row_size() {
    local -n TABLE_FIELDS=$1
    local -n TABLE_CHARSET=$2
    local FIELD_TYPE=""
    local FIELD_SIZE=""
    local FIELD=""
    local ROW_SIZE=0
    local IFS=$'|' # To split the vars using set
    for FIELD in "${TABLE_FIELDS[@]}"  
    do  
        set $FIELD
        FIELD_NAME=$1
        FIELD_TYPE=$2
        FIELD_SIZE=$3        
        calc_field_size_in_bytes $FIELD_TYPE $FIELD_SIZE $TABLE_CHARSET
        ROW_SIZE=$((ROW_SIZE + RETVAL))
        [ $DEBUG -gt 0 ] && echo "DEBUG1: Field name: $FIELD_NAME type: $FIELD_TYPE lenght: $FIELD_SIZE size: $RETVAL bytes Row size: $ROW_SIZE"
    done  
    RETVAL=$ROW_SIZE
}
​
calc_field_size_in_bytes() {
    local TYPE=$1
    local SIZE=$2
    local CHARSET=$3

    case $FIELD_TYPE in
        varchar)
            # https://adayinthelifeof.nl/2010/12/04/about-using-utf-8-fields-in-mysql/
            # Max 3 bytes per utf-8 chat in mysql
            case $CHARSET in
                utf8)
                    RETVAL=$((SIZE * 3))  # 3 bytes per character for utf8 
                ;;
                latin1)
                    RETVAL=$((SIZE))  # 1 byte per character for latin1
                ;;
                *)
                    echo "Unknown charset ($CHARSET), please fix the script"
                    exit 1
                ;;
            esac
        ;;
        smallint|int|bigint|tinyint|varbinary)
            RETVAL=$SIZE
        ;;
        blob)
            # https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html#row-size-limits
            # BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.
            RETVAL=9
        ;;
        text)
            RETVAL=12
        ;;
        timestamp)
            RETVAL=4 
        ;; 
        decimal)
            # https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-numeric
            # Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes. 
            if [[ $SIZE =~ ([0-9]+),([0-9]+) ]] 
            then
              INTEGER_PART=${BASH_REMATCH[1]}
              FRACTIONAL_PART=${BASH_REMATCH[2]}
​
              INTEGER_BYTES=$((INTEGER_PART / 9 * 4))
              REMAINDER=$((INTEGER_PART % 9))
              case $REMAINDER in
                  0) INTEGER_BYTES=$((INTEGER_BYTES + 0)); ;;
                  1) INTEGER_BYTES=$((INTEGER_BYTES + 1)); ;;
                  2) INTEGER_BYTES=$((INTEGER_BYTES + 1)); ;;
                  3) INTEGER_BYTES=$((INTEGER_BYTES + 2)); ;;
                  4) INTEGER_BYTES=$((INTEGER_BYTES + 2)); ;;
                  5) INTEGER_BYTES=$((INTEGER_BYTES + 3)); ;;
                  6) INTEGER_BYTES=$((INTEGER_BYTES + 3)); ;;
                  7) INTEGER_BYTES=$((INTEGER_BYTES + 4)); ;;
                  8) INTEGER_BYTES=$((INTEGER_BYTES + 4)); ;;
              esac
​
              FRACTIONAL_BYTES=$((FRACTIONAL_PART / 9 * 4))
              REMAINDER=$((FRACTIONAL_PART % 9))
              case $REMAINDER in
                  0) FRACTIONAL_BYTES=$((FRACTIONAL_BYTES + 0)); ;;
                  1) FRACTIONAL_BYTES=$((FRACTIONAL_BYTES + 1)); ;;
                  2) FRACTIONAL_BYTES=$((FRACTIONAL_BYTES + 1)); ;;
                  3) FRACTIONAL_BYTES=$((FRACTIONAL_BYTES + 2)); ;;
                  4) FRACTIONAL_BYTES=$((FRACTIONAL_BYTES + 2)); ;;
                  5) FRACTIONAL_BYTES=$((FRACTIONAL_BYTES + 3)); ;;
                  6) FRACTIONAL_BYTES=$((FRACTIONAL_BYTES + 3)); ;;
                  7) FRACTIONAL_BYTES=$((FRACTIONAL_BYTES + 4)); ;;
                  8) FRACTIONAL_BYTES=$((FRACTIONAL_BYTES + 4)); ;;
              esac
              [ $DEBUG -gt 0 ] && echo "DEBUG1: Calulation of decimal: SIZE: $SIZE INTEGER_PART:$INTEGER_PART FRACTIONAL_PART:$FRACTIONAL_PART TOTAL = INTEGER_BYTES($INTEGER_BYTES) + FRACTIONAL_BYTES($FRACTIONAL_BYTES)"
              RETVAL=$((INTEGER_BYTES + FRACTIONAL_BYTES)) 
            else
                echo "Seems like SIZE ($SIZE) for a decimal field doesn't match pattern ([0-9]+),([0-9]+). Please investigate"
                exit 1
            fi
        ;;
        *)
            echo "Found a field type that is not handled: $TYPE. Please fix before proceeding."
            exit 1
        ;;
    esac
}
​
​
#===========================================================================================
# INIT
#===========================================================================================
INSIDE_CREATE_TABLE_STATEMENT=false # True if we are within a create table statement
TABLE_NAME=''  # Current table name
ROW_SIZE=0 # Current row size being calculated
DEBUG=0
VERBOSE=0
MAX_SIZE=8126 # Default
declare -a FIELDS # List of fields from the current CREATE TABLE statement
​
#===========================================================================================
# Parameters
#===========================================================================================
OPTIND=1         # Reset in case getopts has been used previously in the shell.
​
while getopts "hvdt:" opt; do
    case "$opt" in
    h)
        echo "Usage: mysqldump --no-data | ./check_row_size [-v|-d] [-t threshold]"
        exit 0
        ;;
    v) VERBOSE=1
        ;;
    d) DEBUG=2
        ;;
    t) MAX_SIZE=$OPTARG
        ;;
    esac
done
​
​
#===========================================================================================
# MAIN Loop - parses schema then calc row_size based on charset
#===========================================================================================
while IFS= read -r LINE
do
    [ $DEBUG -gt 1 ] && echo "DEBUG2: Read: $LINE"
    # Are we within a CREATE TABLE statement?
    if [ $INSIDE_CREATE_TABLE_STATEMENT == "false" ]
    then
        # Nope, is the current line a 'CREATE TABLE' statement?
        if [[ $LINE =~ ^"CREATE TABLE \`"([^\`]+) ]] 
        then
            [ $DEBUG -gt 0 ] && echo "CREATE TABLE FOUND!: $TABLE_NAME"
            TABLE_NAME=${BASH_REMATCH[1]} # What has been caught between pattern parenthesis
            INSIDE_CREATE_TABLE_STATEMENT='true'
            FIELDS=()
        fi
        continue # Ok, next line 
    fi
    # Is this a create table field definition line?
    if [[ $LINE =~ ^' '+'`'([^'`']+)'` '([a-z]+)'('([^')']+) ]]
    then
        FIELD_NAME=${BASH_REMATCH[1]}
        FIELD_TYPE=${BASH_REMATCH[2]}
        FIELD_SIZE=${BASH_REMATCH[3]}
        FIELDS+=( "$FIELD_NAME|$FIELD_TYPE|$FIELD_SIZE" )
        continue
    fi
    # Have we reached the end of the CREATE TABLE statement?
    if [[ $LINE =~ ^") ENGINE=InnoDB DEFAULT CHARSET="([^ ]+) ]] 
    then
        CHARSET=${BASH_REMATCH[1]}
        [ $DEBUG -gt 0 ] && echo "End of CREATE TABLE statement"
        calc_row_size FIELDS CHARSET
        ROW_SIZE=$RETVAL
        if [ $ROW_SIZE -gt $MAX_SIZE ]
        then
            echo "Table: $TABLE_NAME has a row size: $ROW_SIZE Bytes > $MAX_SIZE Bytes Charset: $CHARSET"
            # and is going to cause problem if the we upgrade to tables in ROW_FORMAT compact. See https://mariadb.com/kb/en/library/troubleshooting-row-size-too-large-errors-with-innodb/ for more details."
        fi
        INSIDE_CREATE_TABLE_STATEMENT='false'
    fi
done 

W pobliżu

Jest już kilka pytań tego typu, na przykład jedno: Jak oszacować / przewidzieć rozmiar danych i rozmiar indeksu tabeli w MySQL

Jedną różnicą między tym pytaniem a tabelą jest obecność ciągów o zmiennej długości w twoim - pamiętaj, aby uwzględnić maksymalny rozmiar, jaki mogą być.

Pamiętaj również, że od wersji 5, która varchar(25)ma do 25 znaków, a nie do 25 bajtów, więc jeśli w ciągach znaków można zobaczyć znaki inne niż ASCII, rozmiar kolumny może się zwiększyć do maksymalnie 100 bajtów, ponieważ niektóre znaki zajmują cztery bajty reprezentować - na przykład „stos emoji poo” (który, nie jestem, nie istnieje) - jeśli twoja przeglądarka + obsługa czcionek wygląda tak: it) to 0xF0 0x9F 0x92 0xA9. Przed wersją v5 mySQL zliczał bajty, a nie znaki podczas określania długości typu łańcucha.

Edytuj w zakresie automatyzacji

Jeśli chodzi o automatyzację procesu, powinieneś być w stanie uzyskać wszystkie potrzebne informacje z INFORMATION_SCHEMAtabel w sposób podobny do skryptu znalezionego dla MS SQL Server. Zobacz https://dev.mysql.com/doc/refman/5.0/en/information-schema.html, aby uzyskać dokumentację na ten temat.