MySQL数据插入insert性能优化说明
发布时间：2022-06-20 13:23:19 所属栏目：MySql教程 来源：互联网
导读：可能用很多朋友会发现如果使用insert插入较小数据量我们会很慢,但如果上万条记录同时插入时会发现很慢,下面我来给大家介绍为什么会这样及数据插入insert性能优化方法,有需要的朋友可进入参考. 对于一些数据量较大的系统,面临的问题除了是查询效率低下,还有一
|
可能用很多朋友会发现如果使用insert插入较小数据量我们会很慢,但如果上万条记录同时插入时会发现很慢,下面我来给大家介绍为什么会这样及数据插入insert性能优化方法,有需要的朋友可进入参考. 对于一些数据量较大的系统,面临的问题除了是查询效率低下,还有一个很重要的问题就是插入时间长,我们就有一个业务系统,每天的数据导入需要4-5个钟,这种费时的操作其实是很有风险的,假设程序出了问题,想重跑操作那是一件痛苦的事情,因此,提高大数据量系统的MySQL insert效率是很有必要的. 经过对MySQL的测试，发现一些可以提高insert效率的方法，供大家参考参考。 1.一条SQL语句插入多条数据. 常用的插入语句,代码如下: INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0); INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('1','userid_1','content_1',1); //修改成： INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0), ('1','userid_1','content_1',1); 修改后的插入操作能够提高程序的插入效率,这里第二种SQL执行效率高的主要原因有两个,一是减少SQL语句解析的操作,只需要解析一次就能进行数据的插入操作,二是SQL语句较短,可以减少网络传输的IO. 这里提供一些测试对比数据,分别是进行单条数据的导入与转化成一条SQL语句进行导入,分别测试1百、1千、1万条数据记录. 记录数 单条数据插入 多条数据插入 1百 0.149s 0.011s 1千 1.231s 0.047s 1万 11.678s 0.218s 2.在事物中进行插入处理,把插入修改成如下代码: STARTTRANSACTION; INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0); INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('1','userid_1','content_1',1); ... COMMIT; 使用事物可以提高数据的插入效率,这是因为进行一个INSERT操作时,MySQL内部会建立一个事物,在事物内进行真正插入处理,通过使用事物可以减少创建事物的消耗,所有插入都在执行后才进行提交操作. 这里也提供了测试对比,分别是不使用事物与使用事物在记录数为1百、1千、1万的情况. 记录数 不使用事物 使用事物 1百 0.149s 0.033s 1千 1.231s 0.115s 1万 11.678s 1.050s 性能测试:这里提供了同时使用上面两种方法进行INSERT效率优化的测试,即多条数据合并为同一个SQL,并且在事物中进行插入. 记录数 单条数据插入 合并数据+事物插入 1万 0m15.977s 0m0.309s 10万 1m52.204s 0m2.271s 100万 18m31.317s 0m23.332s 从测试结果可以看到,insert的效率大概有50倍的提高,这个一个很客观的数字. 如果要在同一个客户端在同一时间内插入很多记录，可以使用INSERT语句附带有多个values值。这种做法比使用单一值的INSERT语句快多了（在一些情况下比较快）。如果是往一个非空数据表增加记录，可以调整变量bulk_insert_buffer_size的值使其更快。 如果要从不用的客户端插入大量记录，使用INSERT DELAYED语句也可以提高速度。 对应MyISAM，可以在SELECT语句正在运行时插入记录，只要这时候没有正在删除记录。 想要将一个文本文件加载到数据表中，可以使用LOAD DATA INFILE。这通常是使用大量INSERT语句的20倍。 通过一些额外工作,就可以让LOAD DATA INFILE在数据表有大量索引的情况下运行更快,步骤如下: 用create table随表建一个表,执行FLUSH TABLES语句或admin flush-tables命令,执行myisamchk –keys-used=0 -rq /path/to/db/tbl_name命令,删除数据表所有索引. 执行LOAD DATA INFILE,数据插入到表中,由于无需更新表索引,因此这将非常快,如果将来只是读取该表,运行myisampack让数据表更小. 运行myisamchk -r -q /path/to/db/tbl_name重建索引,创建的索引树在写入磁盘前先保存在内存中,这省去了磁盘磁盘搜索,因此速度快很多,重建后的索引树分布非常均衡. 执行FLUSH TABLES语句或mysqladmin flush-tables命令 注意,在Mysql 4.0起，可以运行ALTER TABLE tbl_name DISABLE KEYS来代替myisamchk –keys-used=0 -rq /path/to/db/tbl_name.运行ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q /path/to/db/tbl_name.这么做就可以省去FLUSH TABLES步骤. 可以在锁表后,一起执行几个语句来加速INSERT操作: LOCK TABLES a WRITE; INSERT INTO a VALUES(1,23),(2,23); //phpfensi.com INSERT INTO a VALUES(8,7); UNLOCK TABLES; 这对性能提高的好处在于:直到所有的INSERT语句都完成之后,索引缓存一次性刷新到磁盘中,通常情况下,有多少次INSERT语句就会有多少次索引缓存刷新到磁盘中的开销,如果能在一个语句中一次性插入多个值的话,显然锁表操作也没有必要了,对于事务表而言,用BEGIN/COMMIT代替LOCK TABLES来提高速度,锁表也会降低多次连接测试的总时间,尽管每个独立连接为了等待锁的最大等待时间也会增加. Connection 1 does 1000 inserts Connection 2,3 and 4 do 1 insert Connection 5 does 1000 inserts 如果没有锁表，则连接2，3，4会在1，5之前完成。如果锁表了，则连接2，3，4可能在1，5之后才能完成，但总时间可能只需要40%。Mysql的INSERT、UPDATE、DELETE操作都非常快，不过在一个语句中如果超过5个插入或者更新时最好加锁以得到更好的性能。如果要一次性做很多次插入，最好在每个循环的前后加上LOCK TABLES和UNLOCK TABLES，从而让其他进程也能访问数据表；这么做性能依然不错。INSERT总比LOAD DATA INFILE插入数据慢，因为二者实现策略有分明的不同。 想要MyISAM表更快,在LOAD DATA INFILE和INSERT时都可以增加系统变量key_buffer_size的值. 注意事项: 1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packe配置可以修改，默认是1M。 2. 事物需要控制大小，事物太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会日志会使用磁盘数据，这时，效率会有所下降。所以比较好的做法是，在事物大小达到配置项数据级前进行事物提交。 （编辑：开发网_开封站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330459号