3.MySQL优化Optimizing REPAIR TABLE Statements

介绍

REPAIR TABLE对于 MyISAM表类似于使用 myisamchk进行修复操作，并且一些相同的性能优化适用：

• myisamchk具有控制内存分配的变量。您可以通过设置这些变量来提高性能。
• 对于REPAIR TABLE，他与myisamchk的原理相同，但是因为修复是由服务器完成的，所以设置服务器系统变量而不是myisamchk变量。
• 此外，除了设置内存相关参数之外，还可以增加 myisam_max_sort_file_size 参数的值，还可能增加使用快速的filesort修复方式替代较慢的 key cache方式的修复方式。这个变量的最大值，是你能保证硬盘有足够的空间保存数据的副本，将变量设置为系统的最大文件大小。必须在包含原始表文件的文件系统中提供可用空间。

假设使用以下选项完成myisamchk表修复操作以设置其内存分配变量：

--key_buffer_size=128M --myisam_sort_buffer_size=256M
--read_buffer_size=64M --write_buffer_size=64M

其中一些myisamchk变量对应于服务器系统变量：

myisamchk变量	系统变量
key_buffer_size	key_buffer_size
myisam_sort_buffer_size	myisam_sort_buffer_size
read_buffer_size	read_buffer_size
write_buffer_size	none

每个服务器系统变量都可以在运行时设置，其中一些（myisam_sort_buffer_size， read_buffer_size）除了全局值之外还有会话值,设置会话值会限制更改对当前会话的影响，并且不会影响其他用户。更改仅全局变量（key_buffer_size， myisam_max_sort_file_size）也会影响其他用户。对于key_buffer_size，您必须考虑缓冲区与这些用户共享。例如，假如你设置 myisamchk的key_buffer_size为128M，您可以将相应的key_buffer_size系统变量设置为大于该值，允许其他活动中的会话使用key buffer。但是更改 global key buffer size会使buffer失效，使磁盘I/O增长，并影响其他session执行效率。避免此问题的方法是使用单独的key cache，并为表修复分配索引，使用完之后释放。

如前所述，REPAIR TABLE 指令与myisamchk使用方式相似，下面分配了128M的key buffer，并且文件系统有大于100G磁盘空间：

SET SESSION myisam_sort_buffer_size = 256*1024*1024;
SET SESSION read_buffer_size = 64*1024*1024;
SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;
SET GLOBAL repair_cache.key_buffer_size = 128*1024*1024;
CACHE INDEX tbl_name IN repair_cache;
LOAD INDEX INTO CACHE tbl_name;
REPAIR TABLE tbl_name ;
SET GLOBAL repair_cache.key_buffer_size = 0;

如果您打算更改全局变量但希望仅在REPAIR TABLE操作期间执行此操作以最小程度地影响其他用户，请将其值保存在用户变量中，然后将其还原。例如：

SET @old_myisam_sort_buffer_size = @@GLOBAL.myisam_max_sort_file_size;
SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;
REPAIR TABLE tbl_name ;
SET GLOBAL myisam_max_sort_file_size = @old_myisam_max_sort_file_size;

REPAIR TABLE如果您希望默认情况下值有效，则可以在服务器启动时全局设置 影响的系统变量。例如，将这些行添加到服务器my.cnf文件中：

[mysqld]
myisam_sort_buffer_size=256M
key_buffer_size=1G
myisam_max_sort_file_size=100G

这些设置不包括 read_buffer_size。将read_buffer_size全局设置为较大的值会对所有会话执行此操作，并且可能会因为具有多个同时会话的服务器的过多内存分配而导致性能受损。