MySQL的大数据量快速插入方式和语句优化是我们本文要介绍的主要内容。INSERT语句的速度插入一条记录所需的时间由以下因素组成，其中数字代表大致比例：连接：(3)向服务器发送查询：(2)分析查询：(2)插入记录：(1x记录大小)InsertIndexes:(1xIndexes)Closed:(1)这不考虑打开表的初始开销，它是为每个并发运行的查询打开的。表的大小减慢了logN(B-tree)的索引插入。加快插入速度的一些方法如果您同时从同一客户端插入多行，请使用具有多个VALUE的INSERT语句同时插入多行。这比使用单行INSERT语句更快（在某些情况下快几倍）。如果是向非空表中添加数据，可以调整bulk_insert_buffer_size变量，使数据插入速度更快。如果您从不同的客户端插入许多行，您可以使用INSERTDELAYED语句加快速度。使用MyISAM，如果表中没有删除的行，则可以在SELECT语句运行时插入行。从文本文件加载表时，使用LOADDATAINFILE。这通常比使用许多INSERT语句快20倍。请参阅第13.2.5节，“LOADDATAINFILE语法”。当表有很多索引时，可以做更多的工作来使LOADDATAINFILE更快。使用以下过程：使用CREATETABLE选择性地创建表执行FLUSHTABLES语句或命令mysqladminflush-tables。使用myisamchk–keys-used=0-rq/path/to/db/tbl_name。这将从表中删除所有索引。使用LOADDATAINFILE向表中插入数据，因为不更新任何索引，所以速度非常快。如果以后只想看表，用myisampack压缩一下。请参阅第15.1.3.3节，“压缩表功能”。使用myisamchk-r-q/path/to/db/tbl_name重新创建索引。这将在写入磁盘之前在内存中创建索引树，并且速度更快，因为避免了很多磁盘查找。生成的索引树也是完美平衡的。执行FLUSHTABLES语句或mysqladminflush-tables命令。请注意，如果插入一个空的MyISAM表，LOADDATAINFILE也可以执行前面的优化；主要区别在于，当执行LOADDATAINFILE语句需要更多时，您可以让myisamchk为索引创建分配比服务器重新创建索引更多的临时内存。您还可以使用ALTERTABLEtbl_nameDISABLEKEYS代替myisamchk–keys-used=0-rq/path/to/db/tbl_name和ALTERTABLEtbl_nameENABLEKEYS代替myisamchk-r-q/path/to/db/tbl_name。这样也可以跳过FLUSHTABLES。锁定表可以加快使用多个语句执行的INSERT操作：LOCKTABLESaWRITE；INSERTINTOaVALUES(1,23),(2,34),(4,33);INSERTINTOaVALUES(8,26),(6,29);UNLOCKTABLES;性能得到改进，因为索引缓存仅在所有INSERT语句完成后刷新到磁盘一次。通常，INSERT语句的数量与索引缓冲区刷新的数量一样多。如果您可以用一条语句插入所有行，则不需要锁定。对于事务表，应该使用BEGIN和COMMIT而不是LOCKTABLES来加速插入。锁定也将减少多连接测试的总时间，尽管延迟会增加，因为它们等待锁定***。例如：Connection1does1000insertsConnections2,3,and4do1insertConnection5does1000inserts如果没有加锁，2、3、4会先于1、5完成。如果使用加锁，2、3、4很可能不会在1或5之前完成，但总的时间应该是大约快40%。MySQL中的INSERT、UPDATE和DELETE操作速度很快，通过锁定连续5次以上的连续插入或更新可以获得更好的整体性能。如果您在一行中执行多个插入，您可以执行锁定表，然后立即执行解锁表（大约每1000行）以允许其他线程访问该表。这也产生了良好的性能。INSERT加载数据比LOADDATAINFILE慢得多，即使采用上述策略也是如此。为了更快地在MyISAM表上加载数据INFILE和INSERT，通过增加key_buffer_size系统变量来扩大键缓存。INSERT语法INSERT[LOW_PRIORITY|DELAYED|HIGH_PRIORITY][IGNORE][INTO]tbl_name[(col_name,...)]VALUES({expr|DEFAULT},...),(...),...[ONDUPLICATEKEYUPDATEcol_name=expr,...]或：INSERT[LOW_PRIORITY|DELAYED|HIGH_PRIORITY][IGNORE][INTO]tbl_nameSETcol_name={expr|DEFAULT},...[ONDUPLICATEKEYUPDATEcol_name=expr,...]或：INSERT[LOW_PRIORITY|HIGH_PRIORITY][IGNORE][INTO]tbl_name[(col_name,...)]SELECT...[ONDUPLICATEKEYUPDATEcol_name=expr,...]1.DELAYED的使用延迟插入操作DELAYED修饰符应用于INSERT和REPLACE语句.当DELAYED插入操作到达时，服务器将行放入队列并立即向客户端返回状态消息，以便客户端可以在实际插入表之前进行处理。如果有读者从数据表中读取数据，则队列中的数据会一直保持到没有读者为止。然后服务器开始在延迟行队列中插入数据行。在插入操作的同时，服务端还会检查是否有新的读请求到达并等待。如果是，则延迟行队列被挂起，允许读取器继续。当没有读者时，服务器再次开始插入延迟行。这个过程一直持续到队列为空。需要注意的几点INSERTDELAYED只能用于指定值列表的INSERT语句。服务器忽略DELAYED的INSERTDELAYED…SELECT语句。服务器忽略DELAYEDforINSERTDELAYED…ONDUPLICATEUPDATEstatements。因为语句在插入行之前立即返回，所以不能使用LAST_INSERT_ID()来获取AUTO_INCREMENT值。AUTO_INCREMENT值可能由语句生成。对于SELECT语句，DELAYED行在实际插入之前是不可见的。DELAYED在副本replicas上被忽略，因为DELAYED不会在slave和master上产生不同的数据。请注意，当前在队列中的行只保留在内存中，直到它们被插入到表中。这意味着如果您强行终止mysqld（例如，使用kill-9）或者如果mysqld意外停止，任何未写入磁盘的行都将丢失。二、IGNORE的使用IGNORE是MySQL相对于标准SQL的扩展。如果新表中存在重复的关键字，或者在启用STRICT模式时出现警告，则使用IGNORE来控制ALTERTABLE的操作。如果未指定IGNORE，当出现重复关键字错误时，复制操作将中止并返回上一步。如果指定了IGNORE，只有***行用于关键字重复的行，其他冲突的行被删除。并且，对错误值进行修正，使其尽可能接近正确值。insertignoreintotb(...)value(...)这样就不用检查是否存在，存在则忽略，不存在则添加。3.ONDUPLICATEKEYUPDATE的使用如果指定ONDUPLICATEKEYUPDATE，插入一行会导致UNIQUE索引或PRIMARYKEY中出现重复值，执行旧行UPDATE。例如，如果列a定义为UNIQUE并包含值1，则以下两个语句具有相同的效果：mysql>INSERTITOtable(a,b,c)VALUES(1,2,3)->ONDUPLICATEKEYUPDATEcc=c+1;mysql>UPDATEtableSETcc=c+1WHEREa=1;如果该行作为新记录插入，则受影响行的值为1；如果原始记录被更新，则受影响的行的值为2。注意：如果列b也是唯一的列，则INSERT等效于此UPDATE语句：mysql>UPDATEtableSETcc=c+1WHEREa=1ORb=2LIMIT1;如果a=1ORb=2匹配多行，则只更新一行。通常，您应该尽量避免在具有多个唯一键的表上使用ONDUPLICATEKEY子句。您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说，如果不发生重复键冲突，UPDATE子句中的VALUES(col_name)可以引用插入的col_name的值。这个函数特别适合插入多行。VALUES()函数只在INSERT...UPDATE语句中有意义，其他时候返回NULL。示例：mysql>INSERTINTTOtable(a,b,c)VALUES(1,2,3),(4,5,6)->ONDUPLICATEKEYUPDATEc=VALUES(a)+VALUES(b);该语句与下面两条语句的效果相同：mysql>INSERTINTOtable(a,b,c)VALUES(1,2,3)->ONDUPLICATEKEYUPDATEc=3;mysql>INSERTINTOtable(a,b,c)VALUES(4,5,6)->ONDUPLICATEKEYUPDATEc=9;当您使用ONDUPLICATEKEYUPDATE时，DELAYED选项将被忽略。
