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

墨蓝 2022-08-22 00:04 191阅读 0赞

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  
  
3.测试情况：  
表t(id int auto\_increment primary key,content1 varchar(30),content2 int);  
create index ind\_of\_t on t(content1);  
  
a. 单条插入空表：  
1千条，耗时:24秒  
5千条，耗时:160秒  
1万条，耗时:277秒  
  
b. 一次1000个values list插空表  
1千条，耗时:2秒  
5千条，耗时:6秒  
1万条，耗时:11秒  
5万条，耗时:51秒  
10万条，耗时:99秒  
  
c. 10个线程同时插空表，一次1000个values list  
4,Begin ...  
8,Begin ...  
6,Begin ...  
0,Begin ...  
7,Begin ...  
9,Begin ...  
3,Begin ...  
5,Begin ...  
2,Begin ...  
6,1千条，耗时:6秒  
3,1千条，耗时:5秒  
9,1千条，耗时:6秒  
8,1千条，耗时:7秒  
2,1千条，耗时:7秒  
5,1千条，耗时:7秒  
0,1千条，耗时:10秒  
1,Begin ...  
4,1千条，耗时:12秒  
1,1千条，耗时:2秒  
7,1千条，耗时:17秒  
6,5千条，耗时:27秒  
2,5千条，耗时:28秒  
5,5千条，耗时:30秒  
4,5千条，耗时:31秒  
0,5千条，耗时:34秒  
8,5千条，耗时:35秒  
7,5千条，耗时:36秒  
1,5千条，耗时:30秒  
9,5千条，耗时:46秒  
3,5千条，耗时:49秒  
2,1万条，耗时:49秒  
8,1万条，耗时:60秒  
7,1万条，耗时:61秒  
0,1万条，耗时:63秒  
5,1万条，耗时:65秒  
6,1万条，耗时:67秒  
1,1万条，耗时:61秒  
4,1万条，耗时:79秒  
3,1万条，耗时:78秒  
9,1万条，耗时:84秒  
6,5万条，耗时:275秒  
1,5万条，耗时:285秒  
0,5万条，耗时:306秒  
8,5万条，耗时:314秒  
2,5万条，耗时:316秒  
4,5万条，耗时:330秒  
5,5万条，耗时:351秒  
3,5万条，耗时:364秒  
9,5万条，耗时:377秒  
7,5万条，耗时:403秒  
6,10万条，耗时:552秒  
6,End  
0,10万条，耗时:558秒  
0,End  
1,10万条，耗时:573秒  
1,End  
4,10万条，耗时:615秒  
4,End  
3,10万条，耗时:615秒  
3,End  
5,10万条，耗时:623秒  
5,End  
8,10万条，耗时:625秒  
8,End  
7,10万条，耗时:643秒  
7,End  
9,10万条，耗时:648秒  
9,End  
2,10万条，耗时:654秒  
2,End  
  
d. 10个线程同时插表（已有100万条记录），一次1000个values list，再插900万条记录  
5,Begin ...on 1236937010秒  
1,Begin ...on 1236937010秒  
8,Begin ...on 1236937010秒  
4,Begin ...on 1236937010秒  
7,Begin ...on 1236937010秒  
2,Begin ...on 1236937010秒  
3,Begin ...on 1236937010秒  
9,Begin ...on 1236937010秒  
0,Begin ...on 1236937011秒  
6,Begin ...on 1236937011秒  
8,10万条，耗时:499秒  
0,10万条，耗时:518秒  
3,10万条，耗时:519秒  
7,10万条，耗时:556秒  
9,10万条，耗时:565秒  
2,10万条，耗时:578秒  
5,10万条，耗时:654秒  
1,10万条，耗时:709秒  
0,20万条，耗时:1006秒  
9,20万条，耗时:1070秒  
3,20万条，耗时:1091秒  
8,20万条，耗时:1141秒  
5,20万条，耗时:1146秒  
2,20万条，耗时:1157秒  
7,20万条，耗时:1185秒  
1,20万条，耗时:1291秒  
0,30万条，耗时:1510秒  
3,30万条，耗时:1616秒  
9,30万条，耗时:1649秒  
7,30万条，耗时:1690秒  
8,30万条，耗时:1701秒  
5,30万条，耗时:1767秒  
1,30万条，耗时:1778秒  
2,30万条，耗时:1898秒  
0,40万条，耗时:2066秒  
3,40万条，耗时:2109秒  
8,40万条，耗时:2197秒  
9,40万条，耗时:2213秒  
1,40万条，耗时:2235秒  
5,40万条，耗时:2266秒  
0,50万条，耗时:2461秒  
3,50万条，耗时:2502秒  
9,50万条，耗时:2607秒  
1,50万条，耗时:2655秒  
8,50万条，耗时:2663秒  
5,50万条，耗时:2739秒  
3,60万条，耗时:2876秒  
0,60万条，耗时:2921秒  
1,60万条，耗时:3055秒  
8,60万条，耗时:3101秒  
5,60万条，耗时:3178秒  
9,60万条，耗时:3201秒  
3,70万条，耗时:3312秒  
0,70万条，耗时:3358秒  
1,70万条，耗时:3437秒  
8,70万条，耗时:3523秒  
9,70万条，耗时:3645秒  
5,70万条，耗时:3694秒  
3,80万条，耗时:3731秒  
0,80万条，耗时:3799秒  
8,80万条，耗时:3906秒  
1,80万条，耗时:3915秒  
5,80万条，耗时:4062秒  
3,90万条，耗时:4101秒  
3,End  
0,90万条，耗时:4209秒  
0,End  
8,90万条，耗时:4227秒  
8,End  
1,90万条，耗时:4241秒  
1,End  
5,90万条，耗时:4288秒  
5,End