MYSQL中关于insert语句速度的优化

源自

http://www.ej38.com/showinfo/mysql-155336.html

 

1.分析
插入一行分下面几个动作，括号后面是其大约比例额
Connecting(3)
Sendint query to server(2)
Parsing query(2)
Inserting row(1*size of row)
Inserting indexes(1*number of indexes)
Closing(1)
插入索引的速度随表的大小减慢，LogN

2.优化方法
a. 一个客户端在一个时候要插多条数据，那么用多个values
insert into t1 values(...),(...),(...)
如果是往一个非空的表里插数据，可调节bulk_insert_buffer_size（缺省为8388608字节=8M）
b. 如果多个客户端在同时插许多条数据，那么用insert delayed语句
利：客户端马上返回，数据排成一队；数据整齐的写到一个块里，而不是分散。
弊：如果这个表被查获删数据，那么插入会变慢，另外，为这个表起一个handler线程来处理这些数据也要耗费一些额外资源
待插的数据放在内存里，一旦数据库被意外终止（如kill -9），那么数据会丢失。
这个方法只适用于myisam,memory,archive,blackhole引擎类表。
可调节delayed_insert_limit（缺省为一次100条）
delayed_insert_timeout（缺省为300）秒内，若无新的insert delayed语句，则handler线程退出。
delayed_queue_size（缺省为1000条）一旦满了，客户端的insert delayed会阻塞。
比第一个方法要慢。
而且对Myisam来说，在可以使用方法c时，不需用此方法b。
c. 对Myisam表来说，如果一个表中间没有删除过数据，那么，在Select语句执行时，可以同时执行insert语句将数据插在文件最后。
concurrent_insert必须为1（缺省就是1）
d. 从文本文件执行load data infile一般要比用insert语句快20倍。
如果表有索引，可以先去掉索引，load完后，再加上索引。可以提高速度（相比load同时建索引，可以减少disk seek）。
这个事后建索引的方法在msisam表为空时自动执行。
e. 如果插入多条语句，可以先lock tables t write，插入后再unlock tables(索引会只flush一次）; 但如果当中只有1条insert，那么不需要。
f. 要提高Myisam表的load data和insert速度，可提高key_buffer_size（缺省为8M）
如果机器有256M以上内存，那么可以设key_buffer_size为64M，table_open_cache可以调高为256（缺省为64）
如果有128M以上内存，可以设key_buffer_size为16M