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

高效率地载入数据

　　在大多数情况下，你所关注的是SELECT查询的优化，因为SELECT查询是最常见的查询类型，而且如何优化它们又不是太简单。与此形成对比，把数据载入数据库的操作就相对直接了。然而，你仍然可以利用某些策略来改善数据载入操作的效率。基本的原理如下所示：

　　· 批量载入比单行载入的效率高，因为在每条记录被载入后，键缓存（key cache）不用刷新（flush）；可以在这批记录的末尾刷新键缓存。键缓存刷新的频率减少得越多，数据载入的速度就越快。

　　· 没有索引的数据表的载入速度比有索引的要快一些。如果存在索引，不但要把记录添加到数据文件中，还必须修改索引来反映新增的记录。

　　· 较短的SQL语句比较长的SQL语句快，因为它们所涉及到服务器端分析过程较少，同时通过网络把它们从客户端发送到服务器上的速度也更快。

　　其中有些因素看起来是次要的（尤其是最后一个），但是如果你载入的数据很多，那么即使很小的效率差异也会导致一定的性能差别。我们可以从前面的一般原理得出几条如何快速载入数据的实践结论：

　　· LOAD DATA（所有形式的）比INSERT效率高，因为它是批量载入数据行的。服务器只需要分析和解释一条语句，而不是多条语句。同样，索引只需要在所有的数据行被处理过之后才刷新，而不是每行刷新一次。

　　· 不带LOCAL的LOAD DATA比带有LOCAL的LOAD DATA的速度要快。不带LOCAL的时候，文件必须位于服务器上，而且你必须拥有FILE权限，但是服务器却可以直接从磁盘上读取文件。使用LOAD DATA LOCAL的时候，客户端读取文件并通过网络把它发送给服务器，速度慢一些。

　　· 如果你必须使用INSERT，那么试着使用在一个语句中指定多个数据行的形式：

INSERT INTO tbl_name VALUES(...),(...),... ;

　　在这个语句中指定的数据行越多，效果就越好。这会减少必要的语句数量，并最小化索引刷新的次数。这一条结论看起来与前面所讨论的"语句越短，执行速度越快"相矛盾，但是实际上并不矛盾。这儿所讨论的是同时插入多个数据行的一个INSERT语句所花费的开销比功能相同的多个单行INSERT语句的花费的开销要小一些，并且多行语句消耗的索引刷新开销也少一些。

　　如果你使用mysqldump生成数据库备份文件，那么MySQL 4.1会默认地生成多行INSERT语句：它会激活--opt (优化)选项，而这个选项会激活--extended-insert选项，该选项生成多行INSERT语句，还存在其它一些选项也可以使数据被载入的时候，转储文件被处理的效率更高。对于MySQL 4.1以前的版本，你可以明确地指定--opt或--extended-insert选项。

　　使用mysqldump的时候要避免使用--complete-insert选项；它生成的INSERT语句是每个数据行一条语句的，语句总共会很长，比多行语句需要的分析操作更多。

　　· 如果你必须使用INSERT语句，那么在可能的情况下，对它们进行分组以减少索引的刷新。对于事务性的存储引擎，在单个事务中提交，而不是在自动提交（autocommit）模式下提交INSERT语句可以实现这样的功能：

START TRANSACTION; INSERT INTO tbl_name ... ; INSERT INTO tbl_name ... ; INSERT INTO tbl_name ... ; COMMIT;

　　对于非事务性的存储引擎，获取数据表上的写入锁，它被锁定的时候提交INSERT语句：

LOCK TABLES tbl_name WRITE; INSERT INTO tbl_name ... ; INSERT INTO tbl_name ... ; INSERT INTO tbl_name ... ; UNLOCK TABLES;