调整MySQL以便在开发期间快速创建列/索引 [英] Tuning MySQL for speedy column/index creation during development

问题描述

假设一个MySQL MyISAM表有一千兆字节的数据和一千兆字节的索引。

Assume a MySQL MyISAM table with one gigabyte of data and one gigabyte of indexes.

此外，假设在开发过程中列和索引将被添加到/从中删除经常到桌子旁边。由于数据库的大小，使用标准的非调优MySQL设置时列/索引创建速度很慢。

Furthermore, assume that during development columns and indexes will be added and removed from/to the table quite frequently. Due to the size of the database the column/index creation is slow when using the standard non-tuned MySQL settings.

应调整哪些MySQL服务器参数为了最大限度地减少添加新列/索引所需的时间？

推荐答案

我相信你应该做的关键设置看看 key_buffer_size ， myisam_max_sort_file_size 和 myisam_sort_buffer_size 。 key_buffer_size 是MyISAM最重要的设置之一，但其他两个可能需要一些解释。

I believe the key settings you should look at are key_buffer_size, myisam_max_sort_file_size and myisam_sort_buffer_size. key_buffer_size is one of the most important settings overall for MyISAM, but the other two might need some explanation.

来自 http://dev.mysql.com/doc/ refman / 5.0 / en / server-system-variables.html ：

myisam_max_sort_file_size

允许MySQL在重新创建MyISAM索引
时使用
的临时
文件的最大大小（在REPAIR TABLE期间， ALTER TABLE，或
LOAD DATA INFILE）。如果文件大小
大于此值，则使用密钥缓存
创建
索引，这样会更慢。值为
，以字节为单位。

The maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes.

默认值为2GB。如果MyISAM
索引文件超过此大小且磁盘
空间可用，则增加
值可能有助于提高性能。

The default value is 2GB. If MyISAM index files exceed this size and disk space is available, increasing the value may help performance.

myisam_sort_buffer_size

排序MyISAM时分配的
缓冲区的大小在REPAIR TABLE期间或使用CREATE INDEX或ALTER
表创建
索引时索引
。

The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.

$ b的最大允许设置$ b myisam_sort_buffer_size是4GB。

The maximum allowable setting for myisam_sort_buffer_size is 4GB.

所以基本上，如果你希望你的索引大于 myisam_max_sort_file_size ，你可能想看看调整一下。如果没有， myisam_sort_buffer_size 可能值得关注。

So basically, if you expect your indexes to be larger than myisam_max_sort_file_size, you might want to look at tweaking that up. If not, the myisam_sort_buffer_size might warrant some attention.

请记住，在MyISAM表上创建索引会锁定整张桌子。如果您经常在大型桌面上执行此操作，则无论您调整了多少设置，都可能会出现严重的性能问题。

Keep in mind that creating indexes on a MyISAM table locks the entire table. If you're doing this constantly on large tables, you could have some serious performance issues, no matter how many settings you tweak.

这篇关于调整MySQL以便在开发期间快速创建列/索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！