MYSQL教程:查询优化之有效加载数据

有效地加载数据

有时我们需大量地把数据加载到数据表，采用批量加载的方式比一个一个记录加载效率高，因为MySQL不用每加载一条记录就刷新一次索引。下面介绍几个有助于加快数据加载的操作：

使用LOAD DATA语句要比INSERT语句的加载速度快。

LOAD DATA比LOAD DATA LOCAL语句的效率高。前者可由服务器直接从本地磁盘读取加载数据，后者需由客户程序去读取文件并通过网络传送到服务器。

如果一定要用INSERT语句，应尽量在一条语句中插入多个数据行。

如果必须使用多条INSERT语句，则应尽量把它们集中在一起放到一个事务中进行处理，而不是在自动提交模式下执行它们：如：

BEGIN;

INSERT INTO table_name values (...);

INSERT INTO table_name values (...);

INSERT INTO table_name values (...);

...

COMMIT;

对于不支持事务的表，应对表进行写锁定，然后在表锁定期间对表进行INSERT操作，如：

LOCK TABLES table_name WRITE;

INSERT INTO table_name ...;

INSERT INTO table_name ...;

INSERT INTO table_name ...;

...

UNLOCK TABLES;

利用客户/服务器通信协议中的压缩功能以减少网络传输的数据量。但该压缩会消耗大量的系统资源，所以小心使用。

尽量让MySQL插入默认值。不要在INSERT中写太多值，以减少网络传输量和服务器端的语法分析时间。

对于MyISAM和ISAM数据表，如果需加载大量数据，应先建立一个没索引的表，加载数据后再创建索引。该方法不适用于InnoDB或BDB数据表。

禁用和重新激活索引的方法有两种：

使用ALTER TABLE语句的DISABLE KEYS和ENABLE KEYS命令，如：

ALTER TABLE table_name DISABLE KEYS;

ALTER TABLE table_name ENABLE KEYS;

使用myisamchk或isamchk工具。如：

$ myisamchk --keys-used=0 table_name #禁止

$ myisamchk --recover --quick --key-used=n table_name #激活

n是用来表明需要激活索引的位掩码，第0位对应第一个索引，如果有三个索引，n值就是7(二进制111)。索引编号可以下命令确定：

$ myisamchk --description table_name