mysql表能有多大?

cocolala

MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the MyISAM storage engine in MySQL 3.23, the maximum table size was increased to 65536 terabytes (2567 – 1 bytes). With this larger allowed table size, the maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits.

mysql3.22起表尺寸最大为4GB, 如果用myisam,最大为65536TB,实际尺寸受OS限制(见下面表格)

The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which allows extremely large tables. The maximum tablespace size is 64TB.

Innodb表数据是存储在表空间(由多个数据文件组成),所以Innodb单表能跨越OS单文件大小限制,如果采用裸分区作为数据文件,则可进一步扩大表尺寸(因为没有file system大小的限制,实际上只受限于磁盘大小).
而最大表空间为64TB
The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.
下表列出了常见操作系统的文件大小及文件系统大小限制(file system限制暂缺),
ext3相关信息可参见:
http://en.wikipedia.org/wiki/Ext3
LFS相关信息参见:
http://en.wikipedia.org/wiki/Large_file_support

Operating System
File-Size limit
File System Limit
Linux 2.2-Intel 32-bit
2GB (LFS: 4GB)

Linux 2.4+
(using ext3 filesystem) 4TB
32TiB
Solaris 9/10
16TB

NetWare w/NSS filesystem
8TB

win32 w/ FAT/FAT32
2GB/4GB

win32 w/ NTFS
2TB (possibly larger)

MacOS X w/ HFS+
2TB




On Linux 2.2, you can get MyISAM tables larger than 2GB in size by using the Large File Support (LFS) patch for the ext2 filesystem. On Linux 2.4, patches also exist for ReiserFS to get support for big files (up to 2TB). Most current Linux distributions are based on kernel 2.4 and include all the required LFS patches. With JFS and XFS, petabyte and larger files are possible on Linux. However,
the maximum available file size still depends on several factors, one of them being the filesystem used to store MySQL tables.

在Linux2.2中,在ext2文件系统中,使用LFS patch可以让Myisam表大于2GB;
在linux2.4中,ReiserFS使用patch,可以支持2TB大文件;
目前大部分linux系统都是基于2.4(已经包括LFS patch);
用JFS,xfs,可以支持更大的文件,比如PB级;
但是,linux上最大文件尺寸取决于几个因素,其中之一便是所采用的file system及block size
For a detailed overview about LFS in Linux, have a look at Andreas Jaeger's Large File Support in Linux page at
http://www.suse.de/~aj/linux_lfs.html
.
Windows users please note: FAT and VFAT (FAT32) are not considered suitable for production use with MySQL. Use NTFS instead.

Windows用户应注意,不要采用FAT/FAT32作为mysql存储数据.建议使用NTFS
By default, MySQL creates MyISAM tables with an internal structure that allows a maximum size of about 4GB. You can check the maximum table size for a table with the SHOW TABLE STATUS statement or with myisamchk -dv tbl_name. See Section 13.5.4, “SHOW Syntax”.

mysql创建myisam表时,内部结构限定表最大尺寸为4GB,可以使用show table status来检查,或者
用myisam -dv tbl_name
mysql4.1.14-log查得结果如下,记录格式是fixed length时,最大600GB;packed时,最大4GB
mysql> show table status like 'tmp_total_20071215'\G
Max_data_length: 644245094399  -->600GB

# /usr/local/mysql/bin/myisamchk -dv tmp_total_20071215
MyISAM file:         tmp_total_20071215
Record format:       Fixed length
Character set:       latin1_swedish_ci (8)
File-version:        1
Creation time:       2007-12-15  1:03:30
Status:              changed,analyzed,optimized keys,sorted index pages
Data records:                66840  Deleted blocks:                 0
Datafile parts:              66840  Deleted data:                   0
Datafile pointer (bytes):        4  Keyfile pointer (bytes):        3
Datafile length:          10026000  Keyfile length:            645120
Max datafile length:  644245094398  Max keyfile length:   17179868159
Recordlength:                  150
table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
1   2     4   unique  unsigned long                  1        99328       1024

# /usr/local/mysql/bin/myisamchk -dv xxx
MyISAM file:         xxx
Record format:       Packed
Character set:       latin1_swedish_ci (8)
File-version:        1
Creation time:       2008-01-23  4:06:27
Recover time:        2008-01-23  4:06:27
Status:              open,changed
Data records:                11293  Deleted blocks:              1799
Datafile parts:              25370  Deleted data:              373036
Datafile pointer (bytes):        4  Keyfile pointer (bytes):        4
Datafile length:           2828976  Keyfile length:            508928
Max datafile length:    4294967294  Max keyfile length: 4398046510079
Recordlength:                  299
table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
1   1     4   unique  unsigned long                  1        91136       1024
2   96    8   multip. longlong                       5       250880       1024
3   24    4   multip. unsigned long                 47       358400       1024
    21    1           int8                          33

第一个表经过了optimize,第二个没有,对于可支持的最大表尺寸应该没有影响.
If you need a MyISAM table that is larger than 4GB in size (and your operating system supports large files), the CREATE TABLE statement allows AVG_ROW_LENGTH and MAX_ROWS options. See Section 13.1.5, “CREATE TABLE Syntax”. You can also change these options with ALTER TABLE after the table has been created, to increase the table's maximum allowable size. See Section 13.1.2, “ALTER TABLE Syntax”.

如果希望改变myisam表的缺省大小,可以在create table中定义avg_row_length和max_rows
这两个选项也可用alter table改变
Other ways to work around file-size limits for MyISAM tables are as follows:
• If your large table is read-only, you can use myisampack to compress it.    myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. myisampack also can merge multiple tables into a single table. See Section 8.2, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.

对于只读表,可以采用myisampack进行压缩(压缩率最少50%);

• MySQL includes a MERGE library that allows you to handle a collection of MyISAM tables that have identical structure as a single MERGE table. See Section 14.2, “The MERGE Storage Engine”.

另外,可以用merge引擎聚合多个一样的myisam表



本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/67276/showart_639890.html