MySQL中的Rows,Row_format以及MyISAM和InnoDB的一点区别

最新推荐文章于 2024-04-17 11:16:00 发布

小藤椅

最新推荐文章于 2024-04-17 11:16:00 发布

阅读量2k

点赞数 1

分类专栏： Mysql基础知识文章标签： mysql

Mysql基础知识专栏收录该内容

40 篇文章 1 订阅

订阅专栏

前面说到的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.