MySQL + MyISAM 表大小问题

Question

我有一张测试表。测试表如下:

CREATE TABLE  `mytest` (
  `num1` int(10) unsigned NOT NULL,
  KEY `key1` (`num1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

我在此表中插入了 5000 万行。

当我显示表状态时，avg_row_length 是 7。我期望看到 4，因为 mySQL 使用 4 个字节作为整数。 key 对 avg_row_length 有影响吗？当我查看我的 .MYD 文件时，大小为 334 MB，这正是 avg_row_length 为 7 时应该给出的大小，但是我真的希望看到 190 MB，因为我只有一个 int。

+----------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name           | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+----------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| mytest         | MyISAM |      10 | Fixed      | 50000000 |              7 |   350000000 | 1970324836974591 |    600518656 |         0 |           NULL | 2010-05-22 09:15:06 | 2010-05-22 19:32:53 | NULL       | latin1_swedish_ci |     NULL |                |         |

我已经包含了 mytest 表的显示表状态的输出。对不起格式:D提前致谢!

亚历山德罗·费鲁奇

Answer 1

我认为问题在于 MySQL 默认使用的指针大小。

来自 MySQL reference 的引文:

AVG_ROW_LENGTH

An approximation of the average row length for your table. You need to set this only for large tables with variable-size rows.

When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table is. If you don't specify either option, the maximum size for MyISAM data and index files is 256TB by default. (If your operating system does not support files that large, table sizes are constrained by the file size limit.) If you want to keep down the pointer sizes to make the index smaller and faster and you don't really need big files, you can decrease the default pointer size by setting the myisam_data_pointer_size system variable. (See Section 5.1.4, “Server System Variables”.) If you want all your tables to be able to grow above the default limit and are willing to have your tables slightly slower and larger than necessary, you can increase the default pointer size by setting this variable. Setting the value to 7 allows table sizes up to 65,536TB.

尝试在创建表时或使用 ALTER TABLE 语句自行设置 MAX_ROWS 和 AVG_ROW_LENGTH，看看是否有帮助。