MySQL查询优化系列讲座之数据类型与效率

无论采用哪种方法，你得到的好处都是相同的：索引在所有的语句都被执行之后才刷新一次，而不是每个INSERT语句刷新一次索引。后面介绍了在自动提交模式下或数据表没有被锁定的时候发生的情况。

　　· 对于MyISAM数据表，减少索引刷新的另外一个策略是使用DELAYED_KEY_WRITE表选项。使用这个选项的时候，数据行会像平常一样立即写入数据文件中，但是键缓存只是偶尔刷新一次，而不是在每次插入操作之后都需要刷新。如果要在服务器上全面地使用延迟索引刷新，那么就需要使用--delay-key-write选项来启动mysqld。在这种情况下，每个数据表的索引块写入操作都会被延迟，直到这些数据块必须为其它的索引值提供空间、或者执行了FLUSH TABLES命令、或者数据表被关闭的时候才执行操作。

　　如果你选择了对MyISAM数据表使用延迟键写入，那么不正常的服务器关闭可能会引起索引值的丢失。这不是致命的问题，因为MyISAM索引可以依据数据行来进行修复，但是如果想让修复过程出现，你就必须使用--myisam-recover=FORCE选项来启动服务器。这个选项会使服务器在打开MyISAM数据表的时候检查它们，如果有必要就自动地修复它们。

　　对于复制（replication）从属服务器，你可能希望使用--delay-key-write=ALL来延迟所有的MyISAM数据表索引的刷新，不管在主服务器上最初是如何建立它们的。

　　· 使用压缩的客户端/服务器协议来减少网络上数据传输的数量。对于大多数MySQL客户端来说，我们都可以使用--compress命令行选项来指定它。通常，这个选项只是在较慢的网络上使用，这是因为压缩操作会花费大量的处理器时间。

　　· 让MySQL替你插入默认值。也就是说，无论如何都不要给INSERT语句中那些可以赋予默认值的列指定值。平均起来，你的语句更短，减少了通过网络发送到服务器的字符数量。此外，由于语句包含的值较少，服务器执行的分析和值转换操作也较少。

　　· 对于MyISAM数据表，如果你必须把大量的数据载入一个新表，最好建立不带索引的表，载入数据，然后建立索引，这样的工作次序的速度要快一些。一次性地建立索引比每行都更新索引的速度要快一些。对于已经带有索引的表，如果预先删除或禁止索引，后来再重新建立或者激活索引，那么数据载入的速度也要快一些。这些策略不能应用于InnoDB或BDB表，它们没有对分离的索引建立过程进行优化。

　　如果你考虑使用删除或禁止索引的策略，把数据载入MyISAM数据表，那么在评估获得的优势的时候，就需要考虑整个环境。如果你把少量的数据载入大型的数据表中，那么在没有任何特殊准备工作的情况下，重新建立索引花费的时间可能比载入数据的时间还要长。

　　要删除并且重新建立索引，需要使用DROP INDEX和CREATE INDEX，或者使用与索引相关的ALTER TABLE。禁止和激活索引有两种办法：

　　· 你可用使用ALTER TABLE的DISABLE KEYS和ENABLE KEYS形式：

ALTER TABLE tbl_name DISABLE KEYS; ALTER TABLE tbl_name ENABLE KEYS;

　　这些语句关闭或打开表中非唯一（non-unique）索引的更新过程。

　　ALTER TABLE的DISABLE KEYS和ENABLE KEYS子句是索引禁止和激活操作的推荐方法，因为服务器也是这样操作的（如果你使用LOAD DATA语句把数据载入空的MyISAM表中，服务器会自动地执行这样的优化操作）。

　　· Myisamchk工具可以执行索引维护。它直接在数据表文件上进行操作，因此使用它的时候，你必须拥有数据表文件的写入权限。
使用myisamchk禁止MyISAM表的索引的方法是，首先你要确保已经告诉了服务器让该数据表独立出来，接着把它移动到适当的数据库目录中，并运行下面的命令：

% myisamchk --keys-used=0 tbl_name

　　载入数据之后，重新激活索引：

% myisamchk --recover --quick --keys-used=n tbl_name

　　其中的n是位掩码（bitmask），它指明了要激活的索引。Bit 0（第一个位）与索引1对应。例如，如果某张表拥有三个索引，那么n的值应该是7（二进制的111）。你也可以使用--description选项来检测索引的数量：

% myisamchk --description tbl_name

　　前面的数据载入原则也可以应用于混合查询环境（客户端执行多种不同的操作）。例如，你应该避免在那些频繁被修改（写入）的数据表上运行长时间的SELECT查询。这会引发大量的争用（contention），导致写入操作的性能较差。一个可能的解决办法是，如果你的写入操作主要是INSERT操作，那么把新记录添加到辅助表中，接着周期性地把这些记录添加到主表中。如果你必须立即访问这些新记录，那么这个策略是不行的，但是如果你能够承担得起短期内不访问这些数据的代价，那么使用辅助表可以在两个方面带来好处。首先，它减少了主表上的SELECT查询争用的问题，因此它们执行得更快。其次，把辅助表中的批量数据载入主表中所花费的时间总和也比单独载入记录花费的时间总和要小一些；键缓存只需要在每次批量载入结束后刷新一次，而不用每个数据行载入后都刷新一次。

　　使用这种策略的一个应用是把Web服务器的Web页面访问日志载入MySQL数据库的时候。在这种情况下，保证实体立即进入主表的优先级并不高（没有这个必要性）。

　　如果你在MyISAM表上使用了混合的INSERT和SELECT语句，你就可以利用并发性插入操作的优点了。这个特性允许插入和检索操作同时进行，而不需要使用辅助表。你可以查看"使用并发性插入操作"部分。

查看本文来源