改善 MyISAM 性能措施

MyISAM 存储引擎需要监控的信息很少。这是因为 MyISAM 存储引擎是建立在 Web 应用程序上的，主要致力于快速查询，因此，对于该存储引擎，你只需要调节服务器上的一个功能——Key cache。这并不意味着没有其他措施可以用来提高性能，相反，有很多事情可以做。提高性能的方法大致分为三类：优化磁盘存储、通过监控和优化 Key cache 来有效地使用内存，以及优化数据库表。

 

主要从以下几个方面来讨论如何提高性能：

 

• 优化磁盘存储；
• 优化数据库表；
• 使用 MyISAM 实用工具；
• 按照索引顺序存储表；
• 压缩表；
• 对数据表进行碎片整理；
• 监控 Key Cache；
• 预加载 Key Cache；
• 使用多个 Key Cache；
• 其他参数的考虑。

优化磁盘存储

MyISAM 的磁盘空间优化是系统配置项，而非 MyISAM 特有的调优参数。MyISAM 中的数据表有自己的存储形式，即 .myd（数据文件）和一个或多个 .myi （索引）文件。这些文件与 .frm 文件一起存储在以数据库命名的文件目录下，该目录由 --datadir 启动选项指定。因此，MyISAM 的磁盘空间优化方法与服务器上的磁盘空间优化方法相同。也就是说，可以将数据目录移到其所在磁盘上以提高数据库性能。还可以使用 RAID 或其他高可用性存储磁盘来进一步提高性能。

 

优化数据库表

可以使用以下几种SQL命令优化数据库表：ANALYZE TABLE、 OPTIMIZE TABLE 和 REPAIR TABLE

 

ANALYZE TABLE 命令用于检测和重组表的关键字分布情况。当通过字段而非常量的方式进行表连接时，MySQL 通过关键字的分布情况决定表的连接顺序。关键字分布还决定了查询时所使用的索引。

 

REPAIR TABLE 命令其实不是一个优化工具，可以使用它为 MyISAM 、Archive 和 CVS 存储引擎恢复奔溃的表。该命令用于恢复那些崩溃的或运行很慢的表（这通常表明该表已经退化，需要重组或修复）。OPTIMIZE TABLE 命令与 myisamchk --recover<table name>命令功能相同。

 

OPTIMIZE TABLE 命令用于恢复被删除的块和重组表，从而提高数据库性能。可以在MyISAM 、BDB 和 InnoDB 表上使用该命令。

 

 使用 MyISAM 实用工具

MySQL 发布包中包含了很多管理 MyISAM 存储引擎（表）的专用工具。

 

• myisam_ftdump：显示全文索引信息
• myisamchk：执行 MyISAM 表的分析
• myisamlog：查看 MyISAM 表的更改日志
• myisampack：压缩表以减少存储量

 

myisampack 是监控 MyISAM 的主力工具，用于显示 MyISAM 表的信息，并对 MyISAM 表进行分析、修复和优化。可以在一个或多个表上运行该命令，但是只能在脱机状态（即关闭表和服务器）下使用。

 

注意：在运行该工具前，请务必备份表，防止修复或优化失败。这样可以避免表损坏或不可修复。

 

有关性能改进的选项说明如下。参看 MySQL 的网上参考手册，以了解这些可选项的完整说明。

 

analyze

分析索引的关键字分布以提高查询性能。

 

backup

在改变表之前复制一份副本（即 .myd 文件）。

 

check

检查表的错误信息并报告之。

 

extended-check

检查表（包括索引）的错误信息，并报告之。

 

force

查找错误并修复之。

 

information

显示表的统计信息。在运行 recover 回复表之前，使用该命令查看表的状况。

 

medium-check

更深入地检查表，并修复之。这比 extended-check 检查的信息少。

 

recover

全面修复表（修复数据结构）。执行除了唯一键重复的所有修复工作。

 

safe-recover

执行传统形式的修复，即有序地读取所有行，并更新所有索引。

 

sort-index

降序排列索引树。这样能够减少索引结构的查找时间，加快索引的访问速度。

 

sort records

按指定的索引序列排序记录信息。这样可以提高某些基于索引的查询的性能。

 

按索引顺序存储表

按照索引顺序存储表数据可以提高大量数据范围查询（例如 WHERE a > 5 AND a < 5）的效率。这种排序允许查询时有序地访问数据，而无须搜索数据的磁盘页。如果需要按照索引顺序排序表，可以使用 myisamchk 工具排序记录选项（-R），并指定所使用的索引，索引编号从 1 开始。下面的命令按照第二个索引的顺序对 test 数据库的表 table1 进行排序.

 

myisamchk -R 2 /usr/local/mysql/data/test/table1

 

也可以使用 ALTER TABLE 和 ORDER BY 命令实现同样的效果。

 

当数据表中添加新行时，这样排序表的方式无法保证表数据仍按索引顺序存储。删除操作不影响排序，但是当添加新行时，表将变得无序，导致数据库性能下降。如果在经常变更的表上采用按索引顺序存储表的技术来提高性能，可能需要定期运行该命令以确保表的存储顺序最佳。

 

压缩表

压缩数据库可以节约空间。虽然 MySQL 中压缩数据的方法很多，但是 MyISAM 存储引擎只能压缩只读表，因为 MyISAM 不能解压、重新排序，也不能对新增 （或删除）数据进行压缩。MyISAM 中使用 myisampack 来压缩表，如下所示：

 

myisampack -b /usr/local/mysql/data/test/table1

 

在压缩数据表前，一般使用备份选项（-b）来创建表的备份副本。这样可以在不需要重新运行 myisampack 命令的情况下，使表变为可写的。

 

为什么要压缩只读表？有两个原因。首先，它可以为易于压缩的数据（如文本）节约存储空间。其次，当查询读取压缩后的表，并通过主键或唯一索引来查找表中数据行时，在比较其他条件之前，仅对单行数据进行解压缩。

 

myisampack 命令有许多选项。如果你对压缩只读表有兴趣，请参阅 MySQL 在线文档，以了解怎样操控该命令的压缩功能。

 

对数据表进行碎片整理

对 MyISAM 数据表有很多删除和插入操作时，其物理储存将变得很零散。例如，已删除数据带来物理存储上的空缺，而插入操作可能破坏原来的存储顺序。为了优化数据表，将其重新组织为期望的顺序和形式，可以使用 OPTIMIZE TABLE 命令或 myisamchk 实用工具。

 

对于有指定排序的表而言，应该定期运行这些命令，以确保这些表的存储形式最优。另外，如果某段时间内数据进行了很多更新，也应该运行这些命令。

 

监控 Key Cache

MySQL 的 Key Cache 是一个高效的存储结构，用于存储频繁使用的索引数据。Key Cache 仅用于 MyISAM，并使用快速查找机制（通常是 B-tree）存储关键字。索引作为链接列表存储在内存中，可以被快速检索到。Key Cache 在第一个 MyISAM 数据表被读取时自动创建。每次查询 MyISAM 数据表前，都会检查一遍 Key Cache。如果在缓存中找到索引，则直接在内存中执行索引检查，而不需要先从磁盘上读取索引。Key Cache 是使 MyISAM 的快速查询比其他存储引擎都快的秘密武器。

 

MyISAM 中有许多变量用于控制 Key Cache。可以使用 SHOW VARIABLES 和 SHOW STATUS 命令或 MySQL 管理器监控这些变量。以下例子显示了用 SHOW 命令监控这些变量的实例。

 

主缓存的状态和系统变量

mysql> SHOW STATUS LIKE 'Key%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| Key_blocks_not_flushed | 0     |

| Key_blocks_unused      | 13396 |

| Key_blocks_used        | 0     |

| Key_read_requests      | 0     |

| Key_reads              | 0     |

| Key_write_requests     | 0     |

| Key_writes             | 0     |

+------------------------+-------+

7 rows in set (0.00 sec)

 

 mysql> SHOW VARIABLES LIKE 'Key%';

+--------------------------+----------+

| Variable_name            | Value    |

+--------------------------+----------+

| key_buffer_size          | 16777216 |

| key_cache_age_threshold  | 300      |

| key_cache_block_size     | 1024     |

| key_cache_division_limit | 100      |

+--------------------------+----------+

4 rows in set (0.00 sec)

 

如你所想，Key Cache 非常复杂。所以  Key Cache 调优将是一个挑战。建议监控 Key Cache 的利用率并根据情况改变  Key Cache 的大小，最好不要改变其运行方式，因为默认配置已能够满足运行要求。

 

如果想提高缓存命中率，可以使用以下两种方法：（1）预加载缓存；（2）使用多个  Key Cache 并为默认  Key Cache 分配更多的内存。

 

预加载 Key Cache

 将索引预加载到 Key Cache 可以加快查询速度，因为索引已经加载到缓存，并且是按顺序加载的（而不是随机的，例如并发操作下的 Key Cache 就是随机加载的）。然而，必须保证缓存中有足够的空间存储索引。对某些特定应用或使用模型来说，预加载是提高查询速度的有效方法。比如，如果在一个应用程序（例如典型的工资审计程序）执行过程中，某个特定的表被查询很多次，这时你可以将该表的相关索引预加载到 Key Cache 中，从而提高数据库性能。使用 LOAD INDEX 命令执行预加载功能，如下例所示：

 

预加载索引到主内存中

mysql> LOAD INDEX INTO CACHE ecs_goods IGNORE LEAVES;

+------------------+--------------+----------+----------+

| Table            | Op           | Msg_type | Msg_text |

+------------------+--------------+----------+----------+

| ecshop.ecs_goods | preload_keys | status   | OK       |

+------------------+--------------+----------+----------+

1 row in set (0.01 sec)

 

该例将 ecs_goods 表的索引加载到 Key Cache 中，IGNORE LEAVES 语句表明只预加载索引的非叶子节点。虽然没有特殊的命令用于刷新 Key Cache ，但是可以通过修改表（例如重组索引或删除并重建索引）强行从 Key Cache 中移除相关索引。

 

使用多个 Key Cache

MySQL 有一个鲜为人知的高级特性，即创建多个 Key Cache 或自定义 Key Cache，以减少对默认 Key Cache 的争用。该特性允许将一个或多个表的索引加载到自定义的特殊缓存中。这意味着按任务分配内存，需要认真规划。如果某段时间内对一组表执行大量查询操作，且频繁引用这些表上的索引，那么使用这种策略将大大提高数据库系统的性能。

 

要创建一个二级 Key Cache，首先需要使用 SET 命令分配内存，然后执行一个或多个 Key Cache 不同的是，可以通过将二级 Key Cache 的大小设为 0 将其刷新或移除。例子显示了如何创建二级 Key Cache，然后添加表索引到缓存中。

 

使用二级主缓存

mysql> SET GLOBAL emp_cache.key_buffer_size=128*1024;

Query OK, 0 rows affected (0.03 sec)

 

mysql> CACHE INDEX ecs_goods IN emp_cache;

+------------------+--------------------+----------+----------+

| Table            | Op                 | Msg_type | Msg_text |

+------------------+--------------------+----------+----------+

| ecshop.ecs_goods | assign_to_keycache | status   | OK       |

+------------------+--------------------+----------+----------+

1 row in set (0.00 sec)

 

mysql> SET GLOBAL emp_cache.key_buffer_size=0;

Query OK, 0 rows affected (0.00 sec)

 

请注意，二级 Key Cache 定义一个名为 emp_cache 的新变量，并将其大小设置为 128 KB。这是 SET 命令的一个特定语法，虽然看起来像是创建一个新的系统变量，但实际上，是创建了一个新的全局用户变量。可以通过以下方式确定一个二级缓存是否存在或其大小：

 

mysql> select @@global.emp_cache.key_buffer_size;

+------------------------------------+

| @@global.emp_cache.key_buffer_size |

+------------------------------------+

|                                  0 |

+------------------------------------+

1 row in set (0.00 sec)

 

由于二级 Key Cache 是全局的，因此只有将其大小设置为 0 或重新启动服务器时，才存在二级 Key Cache。

 

注：可以通过以下方法保存多个 Key Cache 的配置情况：将配置语句保存在一个文件中，然后在系统启动时，使用 MySQL 选项文件的 [mysql] 部分的 init-file=<path_to_file>命令执行该文件。