让MYSQL有效地装载数据[MySQL防范]

多时刻关心的是优化SELECT 查询,因为它们是最常用的查询,并且肯定怎样优化它们并不老是直截了当.相对来说,将数据装入数据库是直截了当的.但是,也存在可用来改进数据装载操作效率的战略,其基本原理以下:

成批装载较单行装载更快,因为在装载每个记录后,不需求革新索引高速缓存;可在成批记录装入后才革新.

在表无索引时装载比索引后装载更快.假若有索引,不但必须增添记录到数据文件,并且还要改正每个索引以反映增添了的新记录.

较短的SQL 语句对比长的SQL 语句要快,因为它们触及服务器方的解析较少,并且还因为将它们通过网络从客户机发送到服务器更快.这些因素中有一些仿佛微不足道（分外是最后一个因素）,但假如要装载大量的数据,即便是很小的因素也会产生很大的差别后果.我们可以操纵上述的普通原理推导出几个关于若何最快地装载数据的实际结论:

LOAD DATA（包含其全部情势）比INSERT 效率高,因为其成批装载行.索引革新较少,并且服务器只需解析和注释一条语句而不是几条语句.

LOAD DATA 比LOAD DATA LOCAL 效率更高.操纵LOAD DATA,文件必须定位在服务器上,并且必须具有FILE 权限,但服务器可从磁盘直接读取文件.操纵LOAD DATA LOCAL,客户机读取文件并将其通过网络发送给服务器,这样做很慢.

假如必须利用INSERT,应当操纵答应在单个语句中指定多行的情势,比方:



可在语句中指定的行越多越好.这样会削减所需的语句数目,降低索引革新量.假如利用MySQLdump 生成数据库备份文件,应当利用--extended-insert 选项,使转储文件包含多行INSERT 语句.还可以利用- - o p t（优化） ,它启用--extended-insert 选项.

反之,应当避免利用mysqldump 的--complete-insert 选项;此选项会招致INSERT 语句为单行,履行时间更长,比不用--complete-insert 选项生成的语句需求更多的解析.

利用紧缩了的客户机/服务器协议以削减网络数据流量.关于大大都MySQL客户机,可以用--compress 号令行选项来指定.它普通只用于较慢的网络,因为紧缩需求占用大量的处理器时间.

让MySQL插入缺省值;不要在INSERT 语句中指定将以肆意方法赋予缺省值的列.平均来说,这样做语句会更短,能削减通过网络传送给服务器的字符数.此外,语句包含的值较少,服务器所举行的解析和转换就会较少.

假如表是索引的,则可操纵批量插入（ LOAD DATA 或多行的INSERT 语句）来削减索引的开销.这样会最小化索引更新的影响,因为索引只需求在全部行处理过期才举行革新,而不是在每行处理后就革新.

假如需求将大量数据装入一个新表,应当成立该表且在未索引时装载,装载数据后才成立索引,这样做较快.一次成立索引（而不是每行改正一次索引）较快.

假如在装载之前删除或禁用索引,装入数据后再重新成立或启用索引大概使装载更快.假如想对数据装载利用删除或禁用战略,一定要做一些实行,看这样做能否值得（假如将少量数据装入一个大表中,重建和索引所耗费的时间大概比装载数据的时间还要长）.

可用DROP INDEX 和CREATE INDEX 来删除和重建索引.另一种可供挑选的办法是操纵myisamchk 或isamchk 禁用和启用索引.这需求在MySQL服务器主机上有一个帐户,并对表文件有写入权.为了禁用表索引,可进入呼应的数据库目录,履行下列号令之一:

对具有.MYI 扩大名的索引文件的MyISAM 表利用myisamchk,对具有.ISM 扩大名的索引文件的ISAM 表利用isamchk.在向表中装入数据后,按以下激活索引:

假如断定利用索引禁用和激活,应当利用第13章中介绍的表修复锁定协议以禁止服务器同时更改锁（固然此时不对表举行修复,但要对它像表修复历程一样举行改正,因此需求利用相同的锁定协议）.

上述数据装载原理也实用于与需求履行差别操作的客户机有关的固定查询.比方,普通但愿避免在频繁更新的表上长时间运行SELECT 查询.长时间运行SELECT 查询会产生大量争用,并降低写入程序的性能.一种大概的办理办法为,假如履行写入的主如果INSERT 操作,那么先将记录存入一个暂时表,然后按期地将这些记录加入主表中.假如需求当即拜候新记录,这不是一个可行的办法.但只要能在一个较短的时间内不拜候它们,便可以利用这个办法.利用暂时表有两个方面的好处.首先,它削减了与主表上SELECT 查询语句的争用,因此,履行更快.其次,从暂时表将记录装入主表的总时间较辨别装载记录的总时间少;呼应的索引高速缓存只需在每个批量装载完毕时举行革新,而不是在每行装载后革新.这个战略的一个利用是进入Web 服务器的Web 页拜候MySQL数据库.在此情形下,大概没有保证记录当即进入主表的较高权限.

假如数据并不美满是那种在系统非正常关闭事件中插入的单个记录,那么削减索引革新的另一战略是利用MyISAM 表的DELAYED_KEY_WRITE 表成立选项（假如将MySQL用于某些数据录入工作时大概会呈现这种情形）.此选项使索引高速缓存只无意革新,而不是在每次插入后都要革新.

假如但愿在服务器范围内操纵耽误索引革新,只要操纵--delayed-key-write 选项启动mysqld 便可.在此情形下,索引块写操作耽误到必须革新块以便为其他索引值腾出空间为止,或耽误到履行了一个flush-tables 号令后,或耽误到该索引表关闭.