MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.7 データ型のストレージ要件

8.0 English
5.7 English
5.6 English
5.5 English

MySQL 5.6 リファレンスマニュアル / データ型 / データ型のストレージ要件

11.7 データ型のストレージ要件

ディスク上のテーブルデータのストレージ要件は、複数の要因によって異なります。別々のストレージエンジンは異なる方法でデータ型を表し、ローデータを格納します。カラムか行全体のどちらかでテーブルデータを圧縮できますが、テーブルまたはカラムのストレージ要件の計算が複雑になります。

ディスク上のストレージレイアウトが違っていても、テーブル行に関する情報を通信および交換する内部 MySQL API は、すべてのストレージエンジンにわたって適用される一貫したデータ構造を使用します。

このセクションでは、データ型の固定サイズ表現を使用するストレージエンジンの内部形式およびサイズを含め、MySQL がサポートするデータ型ごとのストレージ要件に関するガイドラインおよび情報について説明します。情報はカテゴリまたはストレージエンジンごとに示します。

テーブルの内部表現の最大行サイズは 65,535 バイトであり、ストレージエンジンがこれ以上のサイズの行をサポートできる場合でもこのサイズになります。BLOB または TEXT カラムはこのサイズに 9 から 12 バイトしか関与しないので、これらのカラムはこのサイズに含まれません。BLOB および TEXT データについての情報は、行バッファーとは異なるメモリー領域に内部的に格納されます。それぞれのストレージエンジンは、対応する型の処理に使用する方法に従って異なる方法で、このデータの割り当ておよびストレージを扱います。詳細は、第15章「代替ストレージエンジン」およびセクションD.10.4「テーブルカラム数と行サイズの制限」を参照してください。

InnoDB テーブルのストレージ要件

InnoDB テーブルのストレージ要件の詳細は、セクション14.2.13.7「物理的な行構造」を参照してください。

NDBCLUSTER テーブルのストレージ要件

重要

NDB テーブルは、4 バイトアライメントを使用します。すべての NDB データストレージは、4 バイトの倍数で行われます。したがって、通常であれば 15 バイトを使用するカラム値は、NDB テーブルでは 16 バイトを必要とします。たとえば、NDB テーブルでは、TINYINT、SMALLINT、MEDIUMINT、および INTEGER (INT) カラム型はそれぞれ、アライメント係数により、レコードあたり 4 バイトのストレージが必要になります。

各 BIT(M) カラムは M ビットのストレージ領域を使用します。各 BIT カラムは 4 バイトアライメントが行われていませんが、NDB は、BIT カラムに必要な最初の 1 から 32 ビットに行あたり 4 バイト (32 ビット) を、33 から 64 ビットに別の 4 ビットを、というように予約します。

NULL 自体はストレージ領域を必要としませんが、NDB は、テーブル定義に NULL として定義されたカラム (最大 32 の NULL カラム) が含まれる場合、行あたり 4 バイトを予約します。(MySQL Cluster テーブルが 32 以上の NULL カラムから 64 の NULL カラムで定義されている場合、行あたり 8 バイトが予約されます。)

NDB ストレージエンジンを使用するすべてのテーブルで主キーが必要になります。主キーを定義していない場合、「非表示」の主キーが NDB によって作成されます。この非表示の主キーはテーブルレコードあたり 31 から 35 バイトを消費します。

ndb_size.pl Perl スクリプトを使用して、NDB ストレージ要件を評価します。これは、(MySQL Cluster ではなく) 現在の MySQL データベースに接続し、そのデータベースが NDB ストレージエンジンを使用した場合にどれだけの領域を必要とするかについてレポート作成します。詳細は、セクション18.4.25「ndb_size.pl — NDBCLUSTER サイズ要件エスティメータ」を参照してください。

数値型のストレージ要件

データ型	必要なストレージ
`TINYINT`	1 バイト
`SMALLINT`	2 バイト
`MEDIUMINT`	3 バイト
`INT`、`INTEGER`	4 バイト
`BIGINT`	8 バイト
`FLOAT(p)`	0 <= `p` <= 24 の場合は 4 バイト、25 <= `p` <= 53 の場合は 8 バイト
`FLOAT`	4 バイト
`DOUBLE [PRECISION]`、`REAL`	8 バイト
`DECIMAL(M,D)`、`NUMERIC(M,D)`	変動; 次の説明を参照
`BIT(M)`	約 (`M`+7)/8 バイト

DECIMAL (および NUMERIC) カラムの値は、9 桁の 10 進数 (10 進法) を 4 バイトにパックするバイナリ形式を使用して表現されます。各値の整数部と小数部のストレージは、個別に決定されます。9 桁の倍ごとに 4 バイトが必要であり、「余りの」桁には 4 バイトのうちの一部が必要です。余りの桁に必要なストレージ要件を次の表に示します。

余りの桁	バイト数
0	0
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4

日付と時間型のストレージ要件

TIME、DATETIME、および TIMESTAMP カラムの場合、MySQL 5.6.4 よりも前に作成されたテーブルに必要なストレージは、5.6.4 以降で作成されたテーブルとは異なります。これは、5.6.4 で、0 から 3 バイトを必要とする小数部をこれらの型が持つことを許可するように変更されたためです。

データ型	MySQL 5.6.4 より前で必要なストレージ	MySQL 5.6.4 以降で必要なストレージ
`YEAR`	1 バイト	1 バイト
`DATE`	3 バイト	3 バイト
`TIME`	3 バイト	3 バイト + 小数秒ストレージ
`DATETIME`	8 バイト	5 バイト + 小数秒ストレージ
`TIMESTAMP`	4 バイト	4 バイト + 小数秒ストレージ

MySQL 5.6.4 以降、YEAR および DATE のストレージは変更ありません。ただし、TIME、DATETIME、および TIMESTAMP は異なって表現されます。DATETIME はより効率的にパックされ、非小数部に必要なバイト数は 8 バイトではなく 5 バイトであり、3 つの部分すべてに、格納値の小数秒精度に応じて 0 から 3 バイトが必要な小数部があります。

小数秒精度	必要なストレージ
0	0 バイト
1、2	1 バイト
3、4	2 バイト
5、6	3 バイト

たとえば、TIME(0)、TIME(2)、TIME(4)、および TIME(6) はそれぞれ 3、4、5、6 バイトを使用します。TIME と TIME(0) は同等で、必要なストレージは同じです。

時間値の内部表現の詳細は、「MySQL Internals: Important Algorithms and Structures」を参照してください。

文字列型のストレージ要件

次の表では、M は宣言されたカラムの長さを、非バイナリ文字列型の場合は文字数で、バイナリ文字列型の場合はバイト数で表します。L は指定された文字列値の実際の長さをバイト数で表します。

データ型	必要なストレージ
`CHAR(M)`	`M` × `w` バイト、0 `<= M <=` 255、ここで `w` は、文字セット内の最大長の文字に必要なバイト数です。`InnoDB` テーブルの `CHAR` データ型のストレージ要件の詳細は、セクション14.2.13.7「物理的な行構造」を参照してください。
`BINARY(M)`	`M` バイト、0 `<= M <=` 255
`VARCHAR(M)`、`VARBINARY(M)`	カラム値が 0 から 255 バイトを必要とする場合は、`L` + 1 バイト、値が 255 バイト以上を必要とする可能性のある場合は、`L` + 2 バイト
`TINYBLOB`、`TINYTEXT`	`L` + 1 バイト、ここで `L` < 2⁸
`BLOB`、`TEXT`	`L` + 2 バイト、ここで `L` < 2¹⁶
`MEDIUMBLOB`、`MEDIUMTEXT`	`L` + 3 バイト、ここで `L` < 2²⁴
`LONGBLOB`、`LONGTEXT`	`L` + 4 バイト、ここで `L` < 2³²
`ENUM('value1','value2',...)`	列挙値の数 (最大 65,535 個の値) により 1 または 2 バイト
`SET('value1','value2',...)`	セットメンバーの数 (最大 64 メンバー) により、1、2、3、4、または 8 バイト

可変長の文字列型は、長さプリフィクスが付いたデータを使用して格納されます。長さプリフィクスにはデータ型に応じて 1 から 4 バイトが必要で、プリフィクスの値は L (文字列のバイト長) です。たとえば、MEDIUMTEXT 値のストレージには、値を格納するための L バイトに加えて、値の長さを格納するための 3 バイトが必要です。

特定の CHAR、VARCHAR、または TEXT カラム値の格納に使用されるバイト数を計算するには、そのカラムに使用される文字セットと、値にマルチバイト文字が含まれるかどうかを考慮する必要があります。特に、utf8 (または utf8mb4) Unicode 文字セットを使用する場合、すべての文字セットが同じバイト数を使用するわけではなく、文字あたり最大 3 (4) バイトを必要とするわけではないことに注意する必要があります。utf8 または utf8mb4 文字の異なるカテゴリに使用されるストレージの詳細は、セクション10.1.10「Unicode のサポート」を参照してください。

VARCHAR、VARBINARY、および BLOB と TEXT 型は可変長型です。それぞれのストレージ要件は次の要因によって決まります。

カラム値の実際の長さ
カラムの可能な最大の長さ
カラムに使用される文字セット。一部の文字セットにはマルチバイト文字が含まれるため。

たとえば、VARCHAR(255) カラムには最大 255 文字の長さの文字列を格納できます。そのカラムが latin1 文字セット (1 文字あたり 1 バイト) を使用すると仮定すると、実際に必要なストレージは文字列の長さ (L) に、文字列の長さを記録するための 1 バイトを加えた大きさとなります。文字列 'abcd' の場合、L は 4 で、ストレージ要件は 5 バイトになります。同じカラムが代わりにダブルバイト文字セット ucs2 を使用するように宣言されている場合、ストレージ要件は 10 バイトになります。'abcd' の長さは 8 バイトで、カラムの最大長が 255 よりも大きい (最大 510 バイト) ため、長さを格納するために 2 バイト必要になります。

VARCHAR または VARBINARY カラムに格納できる有効な最大バイト数は最大行サイズ (65,535 バイト、すべてのカラムで共有される) によって決まります。複数バイト文字を格納する VARCHAR カラムの場合、文字の有効な最大数は少なくなります。たとえば、utf8 の文字は 1 文字につき最大 3 バイトを必要とする場合があるため、utf8 の文字セットを使用する VARCHAR カラムは、最大 21,844 文字になるように宣言できます。セクションD.10.4「テーブルカラム数と行サイズの制限」を参照してください。

NDB ストレージエンジンは可変幅カラムをサポートします。これは、MySQL Cluster テーブル内の VARCHAR カラムは、このような値に対して 4 バイトアライメントが行われる点を除き、ほかのストレージエンジンと同じ容量のストレージを必要とするということを意味します。したがって、latin1 文字セットを使用して VARCHAR(50) カラムに格納された文字列 'abcd' は、(MyISAM テーブル内の同じカラム値に対する 5 バイトではなく) 8 バイトを必要とします。

TEXT と BLOB カラムは、NDB ストレージエンジンでは異なって実装されます。ここでは、TEXT カラム内の各行は 2 つの別々の部分から構成されています。そのうちの 1 つは固定サイズ (256 バイト) で、実際に元のテーブルに格納されます。もう 1 つは 256 バイトを超えるデータで構成され、非表示のテーブルに格納されます。2 番目のテーブルの行の長さは常に 2,000 バイトです。これは、size <= 256 (ここで size は行のサイズを表します) の場合、TEXT カラムのサイズが 256 であり、それ以外の場合はサイズが 256 + size + (2000 − (size − 256) % 2000) であることを意味します。

ENUM オブジェクトのサイズは異なる列挙値の数によって決まります。最大 255 の値を持つ列挙に 1 バイトが使用されます。256 から 65,535 の値を持つ列挙に 2 バイトが使用されます。セクション11.4.4「ENUM 型」を参照してください。

SET オブジェクトのサイズは異なるセットメンバーの数によって決まります。セットサイズが N である場合、オブジェクトは 1、2、3、4、または 8 バイトに丸められた (N+7)/8 バイトを占めます。SET は最大 64 メンバーを持つことができます。セクション11.4.5「SET 型」を参照してください。

PREV HOME UP NEXT

User Comments

Posted by Marc Bouffard on August 4, 2004

Had a lot of trouble finding the maximum table size in bytes for capacity planning. More specifically it was InnoDB tables that I had a problem with. Average row size is good, but I wanted maximum row size.

I checked several products and could not find what I wanted. Some of the tables I deal with are 300+ fields and so manual calculation was not practical.

So I wrote a little perl script that does it. Thought it might be of some use, so I include it here...it does all field types except enum/set types. It does not calculate anything regarding index size.

Just do a mysqldump -d (just the schema) of your DB to a file, and run this perl script specifying the schema file as the only argument.
----------------------------------------------------------------
#!/usr/bin/perl
use Data::Dumper;
use strict;
$| = 1;

my %DataType =
("TINYINT"=>1,
"SMALLINT"=>2,
"MEDIUMINT"=>3,
"INT"=>4,
"BIGINT"=>8,
"FLOAT"=>'if ($M <= 24) {return 4;} else {return 8;}',
"DOUBLE"=>8,
"DECIMAL"=>'if ($M < $D) {return $D + 2;} elsif ($D > 0) {return $M + 2;} else {return $M + 1;}',
"NUMERIC"=>'if ($M < $D) {return $D + 2;} elsif ($D > 0) {return $M + 2;} else {return $M + 1;}',
"DATE"=>3,
"DATETIME"=>8,
"TIMESTAMP"=>4,
"TIME"=>3,
"YEAR"=>1,
"CHAR"=>'$M',
"VARCHAR"=>'$M+1',
"TINYBLOB"=>'$M+1',
"TINYTEXT"=>'$M+1',
"BLOB"=>'$M+2',
"TEXT"=>'$M+2',
"MEDIUMBLOB"=>'$M+3',
"MEDIUMTEXT"=>'$M+3',
"LONGBLOB"=>'$M+4',
"LONGTEXT"=>'$M+4');

my $D;
my $M;
my $dt;

my $fieldCount = 0;
my $byteCount = 0;
my $fieldName;
open (TABLEFILE,"< $ARGV[0]");
LOGPARSE:while (<TABLEFILE>)
{
chomp;
if ( $_ =~ s/create table[ ]*([a-zA-Z_]*).*/$1/i )
{
print "Fieldcount: $fieldCount Bytecount: $byteCount\n" if $fieldCount;
$fieldCount = 0;
$byteCount = 0;
print "\nTable: $_\n";
next;
}
next if $_ !~ s/(.*)[ ]+(TINYINT[ ]*$*[0-9,]*$*|SMALLINT[ ]*$*[0-9,]*$*|MEDIUMINT[ ]*$*[0-9,]*$*|INT[ ]*$*[0-9,]*$*|BIGINT[ ]*$*[0-9,]*$*|FLOAT[ ]*$*[0-9,]*$*|DOUBLE[ ]*$*[0-9,]*$*|DECIMAL[ ]*$*[0-9,]*$*|NUMERIC[ ]*$*[0-9,]*$*|DATE[ ]*$*[0-9,]*$*|DATETIME[ ]*$*[0-9,]*$*|TIMESTAMP[ ]*$*[0-9,]*$*|TIME[ ]*$*[0-9,]*$*|YEAR[ ]*$*[0-9,]*$*|CHAR[ ]*$*[0-9,]*$*|VARCHAR[ ]*$*[0-9,]*$*|TINYBLOB[ ]*$*[0-9,]*$*|TINYTEXT[ ]*$*[0-9,]*$*|BLOB[ ]*$*[0-9,]*$*|TEXT[ ]*$*[0-9,]*$*|MEDIUMBLOB[ ]*$*[0-9,]*$*|MEDIUMTEXT[ ]*$*[0-9,]*$*|LONGBLOB[ ]*$*[0-9,]*$*|LONGTEXT[ ]*$*[0-9,]*$*).*/$2/gix;
$fieldName=$1;
$_=uc;
$D=0;
($D = $_) =~ s/.*\,([0-9]+).*/$1/g if ( $_ =~ m/\,/ );
$_ =~ s/\,([0-9]*)//g if ( $_ =~ m/\,/ );
($M = $_) =~ s/[^0-9]//g;
$M=0 if ! $M;
($dt = $_) =~ s/[^A-Za-z_]*//g;
print "$fieldName $_:\t".eval($DataType{"$dt"})." bytes\n";
++$fieldCount;
$byteCount += eval($DataType{"$dt"});
}
print "Fieldcount: $fieldCount Bytecount: $byteCount\n";

Posted by Rich Tomasso on April 5, 2005

Here's a modification of Marc's script above that also handles ENUM's. Enjoy.

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

my %DataType =
("TINYINT"=>1, "SMALLINT"=>2, "MEDIUMINT"=>3,
"INT"=>4, "BIGINT"=>8,
"FLOAT"=>'if ($M <= 24) {return 4;} else {return 8;}',
"DOUBLE"=>8,
"DECIMAL"=>'if ($M < $D) {return $D + 2;} elsif ($D > 0) {return $M + 2;} else {return $M + 1;}',
"NUMERIC"=>'if ($M < $D) {return $D + 2;} elsif ($D > 0) {return $M + 2;} else {return $M + 1;}',
"DATE"=>3, "DATETIME"=>8, "TIMESTAMP"=>4, "TIME"=>3, "YEAR"=>1,
"CHAR"=>'$M', "VARCHAR"=>'$M+1',
"ENUM"=>1,
"TINYBLOB"=>'$M+1', "TINYTEXT"=>'$M+1',
"BLOB"=>'$M+2', "TEXT"=>'$M+2',
"MEDIUMBLOB"=>'$M+3', "MEDIUMTEXT"=>'$M+3',
"LONGBLOB"=>'$M+4', "LONGTEXT"=>'$M+4');

my ($D, $M, $dt);

my $fieldCount = 0;
my $byteCount = 0;
my $fieldName;

open (TABLEFILE,"< $ARGV[0]");

LOGPARSE:while (<TABLEFILE>) {
chomp;
if ( $_ =~ s/create table[ ]`*([a-zA-Z_]*).*`/$1/i ) {
print "Fieldcount: $fieldCount Bytecount: $byteCount\n" if $fieldCount;
$fieldCount = 0;
$byteCount = 0;
print "\nTable: $_\n";
next;
}
next if $_ !~ s/(.*)[ ]+(TINYINT[ ]*$*[0-9,]*$*|SMALLINT[ ]*$*[0-9,]*$*|MEDIUMINT[ ]*$*[0-9,]*$*|INT[ ]*$*[0-9,]*$*|BIGINT[ ]*$*[0-9,]*$*|FLOAT[ ]*$*[0-9,]*$*|DOUBLE[ ]*$*[0-9,]*$*|DECIMAL[ ]*$*[0-9,]*$*|NUMERIC[ ]*$*[0-9,]*$*|DATE[ ]*$*[0-9,]*$*|DATETIME[ ]*$*[0-9,]*$*|TIMESTAMP[ ]*$*[0-9,]*$*|TIME[ ]*$*[0-9,]*$*|YEAR[ ]*$*[0-9,]*$*|CHAR[ ]*$*[0-9,]*$*|VARCHAR[ ]*$*[0-9,]*$*|TINYBLOB[ ]*$*[0-9,]*$*|TINYTEXT[ ]*$*[0-9,]*$*|ENUM[ ]*$*['A-Za-z_,]*$*|BLOB[ ]*$*[0-9,]*$*|TEXT[ ]*$*[0-9,]*$*|MEDIUMBLOB[ ]*$*[0-9,]*$*|MEDIUMTEXT[ ]*$*[0-9,]*$*|LONGBLOB[ ]*$*[0-9,]*$*|LONGTEXT[ ]*$*[0-9,]*$*).*/$2/gix;
$fieldName=$1;
$_=uc;
$D=0;
($D = $_) =~ s/.*\,([0-9]+).*/$1/g if ( $_ =~ m/\,/ );
$_ =~ s/\,([0-9]*)//g if ( $_ =~ m/\,/ );
($M = $_) =~ s/[^0-9]//g;
$M=0 if ! $M;
($dt = $_) =~ s/$.*$//g;
$dt =~ s/[^A-Za-z_]*//g;
print "$fieldName $_:\t".eval($DataType{"$dt"})." bytes\n";
++$fieldCount;
$byteCount += eval($DataType{"$dt"});
}
print "Fieldcount: $fieldCount Bytecount: $byteCount\n";

Posted by Alex Gheorghiu on January 30, 2009

The above scripts are not taking into account several important information (so they are outdated)

1. the database/table encoding.
If you have an UTF8 encoding for a varchar(100) that it will take up 300 bytes (3 bytes per UTF symbol)
"[...]As of MySQL 4.1, to calculate the number of bytes used to store a particular CHAR, VARCHAR, or TEXT column value, you must take into account the character set used for that column and whether the value contains multi-byte characters. In particular, when using the utf8 Unicode character set, you must keep in mind that not all utf8 characters use the same number of bytes and can require up to three bytes per character."

2. enum can have either 1 or 2 bytes
"[...]The size of an ENUM object is determined by the number of different enumeration values. One byte is used for enumerations with up to 255 possible values. Two bytes are used for enumerations having between 256 and 65,535 possible values."

Posted by Rafał Michalski on February 19, 2009

Here I wrote another script based on Marc's, that takes into account what Alex wrote and more.
It calculates VARCHAR/CHAR/TEXT taking CHARSET or COLLATION into account, calculates properly SET and ENUM size, DECIMAL/NUMERIC is calculated according to >5.0.3 packed standard.
Calculates also least row byte size for dynamic row length tables.
It uses "mysql" and "mysqldump" tools internally.
Any argument to this script is provided as an argument for mysqldump.
Example: {scriptname} --all-databases
Please report any bug, especially when it comes to size calculations. Enjoy.

----------- copy here --------------
#!/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;

Posted by Jake Drew on May 26, 2011

It appears that TEXT fields with no length specified default to a length of 10 Bytes in your script output. However, information_schema.columns.character_maximum_length lists all my text fields as 65535?

ex:
bytes: 10 abstract TEXT COLL utf8_unicode_ci

Is this a space calculation bug in the script?

Posted by Jake Drew on June 9, 2011

Here is an SQL script that can be used to determine maximum space per row for InnoDB tables using the COMPACT row format.

I have tested the results against my database structures loaded with maximum length records @ 100,000 , 500,000 , and 1,000,000 records. The results seem to be fairly accurate.

I based the maximum space calculations for fields using the following MySQL reference above. I based the calculations for InnoDB Compact row format primary and secondary index record headers using the following MySQL reference:

http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-record.html

Notes:

The SQL produces all sizes in Bytes. If the SQL encounters an unknown data type, it assigns a byte value of 999999999999999 Bytes for that field. You must update TABLE_SCHEMA = 'Your Schema Name' in two places. The query add no overhead factor to it's results. Any overhead factor must be added to the results produced by this query.

SQL Below:

SELECT B.TABLE_SCHEMA
, B.TABLE_NAME
, (CASE WHEN SUM(PK_BYTES) = 0 THEN 6 ELSE SUM(PK_BYTES) END) + 18 AS PK_BYTES_TOT -- 18 = Index Record Header (5) + Transaction ID (6) + Roll Pointer (7)
, SUM(FIELD_BYTE_SPACE) AS FIELD_BYTES_TOT
, SUM(IX_BYTES) AS IX_FIELD_BYTES_TOT
, SUM(CASE WHEN IX_BYTES > 0 THEN 1 ELSE 0 END) AS IX_FIELD_COUNT
, ((CASE WHEN SUM(PK_BYTES) = 0 THEN 6 ELSE SUM(PK_BYTES) END) + 18) + SUM(FIELD_BYTE_SPACE) + SUM(IX_BYTES) AS TABLE_BYTES_TOT
FROM
(
SELECT A.*
, CASE WHEN COLUMN_KEY = 'PRI'THEN FIELD_BYTE_SPACE ELSE 0 END AS PK_BYTES
, CASE WHEN A.COLUMN_KEY <> 'PRI'
AND A.COLUMN_KEY <> '' THEN (PK_BYTE_SPACE + FIELD_BYTE_SPACE) ELSE 0 END AS IX_BYTES
FROM (
SELECT PK_SP.TABLE_SCHEMA
, PK_SP.TABLE_NAME
, PK_SP.COLUMN_NAME
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, NUMERIC_PRECISION
, IS_NULLABLE
, COLUMN_KEY
, CHARACTER_SET_NAME
, CHARACTER_OCTET_LENGTH
, (CASE -- CHARACTER FIELDS
WHEN DATA_TYPE = 'varchar' THEN CHARACTER_MAXIMUM_LENGTH + 1
WHEN DATA_TYPE = 'char' THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'tinyblob'
OR DATA_TYPE = 'tinytext' THEN CHARACTER_MAXIMUM_LENGTH + 1
WHEN DATA_TYPE = 'blob'
OR DATA_TYPE = 'text' THEN CHARACTER_MAXIMUM_LENGTH + 2
WHEN DATA_TYPE = 'mediumblob'
OR DATA_TYPE = 'mediumtext' THEN CHARACTER_MAXIMUM_LENGTH + 3
WHEN DATA_TYPE = 'largeblob'
OR DATA_TYPE = 'largetext' THEN CHARACTER_MAXIMUM_LENGTH + 4
-- NUMERIC FIELDS
WHEN DATA_TYPE = 'tinyint' THEN 1
WHEN DATA_TYPE = 'smallint' THEN 2
WHEN DATA_TYPE = 'mediumint' THEN 3
WHEN DATA_TYPE = 'int'
OR DATA_TYPE = 'integer' THEN 4
WHEN DATA_TYPE = 'bigint' THEN 8
WHEN DATA_TYPE = 'float'
AND (NUMERIC_PRECISION <= 24
OR NUMERIC_PRECISION IS NULL) THEN 4
WHEN DATA_TYPE = 'float'
AND NUMERIC_PRECISION > 24 THEN 8
WHEN DATA_TYPE = 'bit' THEN (NUMERIC_PRECISION + 7) / 8
WHEN DATA_TYPE = 'double'
OR DATA_TYPE = 'numeric' THEN
(FLOOR(NUMERIC_PRECISION/9)*4) + ROUND((NUMERIC_PRECISION- FLOOR(NUMERIC_PRECISION/9)*9)*.5,0)
-- DATETIME FIELDS
WHEN DATA_TYPE = 'date'
OR DATA_TYPE = 'time' THEN 3
WHEN DATA_TYPE = 'datetime' THEN 8
WHEN DATA_TYPE = 'timestamp' THEN 4
WHEN DATA_TYPE = 'year' THEN 1
-- BINARY FIELDS
WHEN DATA_TYPE = 'binary' THEN CHARACTER_MAXIMUM_LENGTH
ELSE 999999999999999 END) +
(CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END) AS FIELD_BYTE_SPACE
, CASE WHEN PK_BYTE_SPACE IS NULL THEN 6 + 18 ELSE PK_BYTE_SPACE + 18 END AS PK_BYTE_SPACE
FROM information_schema.columns AS PK_SP
LEFT OUTER JOIN
(SELECT TABLE_SCHEMA
, TABLE_NAME
, SUM((CASE -- CHARACTER FIELDS
WHEN DATA_TYPE = 'varchar' THEN CHARACTER_MAXIMUM_LENGTH + 1
WHEN DATA_TYPE = 'char' THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'tinyblob'
OR DATA_TYPE = 'tinytext' THEN CHARACTER_MAXIMUM_LENGTH + 1
WHEN DATA_TYPE = 'blob'
OR DATA_TYPE = 'text' THEN CHARACTER_MAXIMUM_LENGTH + 2
WHEN DATA_TYPE = 'mediumblob'
OR DATA_TYPE = 'mediumtext' THEN CHARACTER_MAXIMUM_LENGTH + 3
WHEN DATA_TYPE = 'largeblob'
OR DATA_TYPE = 'largetext' THEN CHARACTER_MAXIMUM_LENGTH + 4
-- NUMERIC FIELDS
WHEN DATA_TYPE = 'tinyint' THEN 1
WHEN DATA_TYPE = 'smallint' THEN 2
WHEN DATA_TYPE = 'mediumint' THEN 3
WHEN DATA_TYPE = 'int'
OR DATA_TYPE = 'integer' THEN 4
WHEN DATA_TYPE = 'bigint' THEN 8
WHEN DATA_TYPE = 'float'
AND (NUMERIC_PRECISION <= 24
OR NUMERIC_PRECISION IS NULL) THEN 4
WHEN DATA_TYPE = 'float'
AND NUMERIC_PRECISION > 24 THEN 8
WHEN DATA_TYPE = 'bit' THEN (NUMERIC_PRECISION + 7) / 8
WHEN DATA_TYPE = 'double'
OR DATA_TYPE = 'numeric' THEN
(FLOOR(NUMERIC_PRECISION/9)*4) + ROUND((NUMERIC_PRECISION- FLOOR(NUMERIC_PRECISION/9)*9)*.5,0)
-- DATETIME FIELDS
WHEN DATA_TYPE = 'date'
OR DATA_TYPE = 'time' THEN 3
WHEN DATA_TYPE = 'datetime' THEN 8
WHEN DATA_TYPE = 'timestamp' THEN 4
WHEN DATA_TYPE = 'year' THEN 1
-- BINARY FIELDS
WHEN DATA_TYPE = 'binary' THEN CHARACTER_MAXIMUM_LENGTH
ELSE 999999999999999 END) +
(CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END)) AS PK_BYTE_SPACE
FROM information_schema.columns COL_SP
WHERE COLUMN_KEY = 'PRI'
AND TABLE_SCHEMA = 'studypods_dev'
GROUP BY TABLE_SCHEMA
, TABLE_NAME) AS IX_SP
ON PK_SP.TABLE_SCHEMA = IX_SP.TABLE_SCHEMA
AND PK_SP.TABLE_NAME = IX_SP.TABLE_NAME
WHERE PK_SP.TABLE_SCHEMA = 'studypods_dev') AS A
) AS B
GROUP BY B.TABLE_SCHEMA
, B.TABLE_NAME

Posted by Rick James on September 15, 2012

The formulas above apply to MyISAM. For InnoDB data, the quick answer is to calculate for MyISAM, then double or triple that value.

The more complex way is something like:
Step 1: Compute basic length of each field (without length field for VAR fields); add 1 or 2 to that length. (1 if all the fields are 'short')
Step 2: Add those together, plus 29 bytes for record overhead.
Step 3: Add 40% for the blocks not being full.
Step 4: Multiply by the number of rows.

That contorted computation can easily be off by a significant amount, either way.

Posted by Sean Nolan on October 17, 2012

This page lists the BLOB and TEXT types and gives a formula for calculating the storage required, but it does not give the different maximum sizes. Here they are:

TINYTEXT - 255 bytes
TEXT - 65535 bytes
MEDIUMTEXT - 16,777,215 bytes (2^24 - 1)
LONGTEXT - 4G bytes (2^32 – 1)

TINYBLOB - 255 bytes
BLOB - 65535 bytes
MEDIUMBLOB - 16,777,215 bytes (2^24 - 1)
LONGBLOB - 4G bytes (2^32 – 1)