转自:http://blog.csdn.net/xiaotengyi2012/article/details/19368979
前面说到的count(*)的数字MyISAM是存储在一个地方就是表的Rows中,从下面的话中我们可以看出MyISAM存的是一个准确的数字,而InnoDB则存储的是粗略的数字。
Rows
The number of rows in the table. For nontransactional tables, this number is
always accurate. For transactional tables, it is usually an estimate.
--------------------------------------------------------朴实的分割线----------------------------------------------------------------------------------
Row_format
The row format. For a MyISAM table, this can be Dynamic, Fixed,or Compressed.
Dynamic rows vary in length because they contain variable-length fields such as
VARCHAR or BLOB. Fixed rows, which are always the same size, are made up of
fields that don’t vary in length, such as CHAR and INTEGER. Compressed rows exist
only in compressed tables.
MyISAM是可以修改row_format的,InnoDB
测试了一下不行,都是
Default
的
COMPACT
。
下面的内容摘自MySQL官网,解释了COMPACT
和
REDUNDANT
的区别。
http://dev.mysql.com/doc/refman/5.1/en/data-size.html
InnoDB
tables use a compact storage format. In versions of MySQL earlier than 5.0.3, InnoDB
rows contain some redundant information, such as the number of columns and the length of each column, even for fixed-size columns. By default, tables are created in the compact format (ROW_FORMAT=COMPACT
). If you wish to downgrade to older versions of MySQL, you can request the old format with ROW_FORMAT=REDUNDANT
.
The presence of the compact row format decreases row storage space by about 20% at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed it is likely to be faster. If it is a rare case that is limited by CPU speed, it might be slower.
The compact InnoDB
format also changes how CHAR
columns containing UTF-8 data are stored. WithROW_FORMAT=REDUNDANT
, a UTF-8 CHAR(N)
occupies 3 × N
bytes, given that the maximum length of a UTF-8 encoded character is three bytes. Many languages can be written primarily using single-byte UTF-8 characters, so a fixed storage length often wastes space. With ROW_FORMAT=COMPACT
format, InnoDB
allocates a variable amount of storage in the range from N
to 3 × N
bytes for these columns by stripping trailing spaces if necessary. The minimum storage length is kept as N
bytes to facilitate in-place updates in typical cases.
其实REDUNDANT
相当于固长,有冗余。
COMPACT
更灵活,更紧凑。
转自:http://www.cnblogs.com/isql/archive/2009/12/17/Row_format.html