MyISAM和InnoDB批量插入1万数据速度比较

最新推荐文章于 2024-10-09 20:02:36 发布

llC2018

最新推荐文章于 2024-10-09 20:02:36 发布

阅读量8.7k

点赞数 1

分类专栏： MySQL 文章标签： InnoDB MyISAM

本文链接：https://blog.csdn.net/QWERDF10010/article/details/79770764

版权

MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

创建测试表

CREATE TABLE testBinlog(id int PRIMARY KEY auto_increment ,name CHAR(16));//MySQL默认存储引擎为InnoDB

CREATE TABLE testMyISAM(id int PRIMARY KEY AUTO_INCREMENT, name CHAR(16))ENGINE = MyISAM;

表结构信息：

创建测试存储过程

//向MyISAM存储引擎的表添加数据 10000条的存储过程
create procedure pwhile()
begin
declare i int;
set i = 0;
while i <10000 do

insert into testBinlog (name) values ('testBinlog');

set i = i+1;
end while;
end;

//向MyISAM存储引擎的表添加数据 10000条的存储过程
create procedure pwhile1()
begin
declare i int;
set i = 0;
while i <10000 do

insert into testMyISAM (name) values ('testBinlog');

set i = i+1;
end while;
end;

测试

call pwhile1();
CALL pwhile();

第一次开启日志比较（binlog）

结果：

关掉日志后速度比较

结果：

从上面结果可以看出，当打开日志后，MyISAM存储引擎的插入速度降低了很多；写日志还是会影响不少数据插入速度。

如果不需要支持事务，没有并发写入操作，MyISAM存储引擎速度优于InnoDB;但是如果涉及到事务，图像保存，InnoDB才是首选。

优化配置参数

my.ini 或者my.cnf增加下面参数配置

 #有所提升速度  MyISAM较多提升
 innodb_log_buffer_size = 16M //5.7默认16M 最小为256K 最大为2G
 innodb_log_file_size = 128M  //5.7默认5M
 innodb_autoextend_increment = 1000 //5.7默认64 设置后最大为1000  这个参数影响好像不是很大，
 #下面这个参数可以显著提高Innodb插入数据速度
 innodb_flush_log_at_trx_commit = 0 //5.7 默认1 这个参数设0比设1速度提升显著  默认的1速度是最慢的、设0速度最快
 #有所提升速度  MyISAM较多提升

然后对上面存储过程调用速度

这样看起来InnoDB插入速度比MyISAM更加快速；

由https://blog.csdn.net/qq_31065001/article/details/71519197博客参考，测试结果与我之前的对比；

将 innodb_flush_log_at_trx_commit 配置设定为0；按过往经验设定为0（默认为1，但是2安全性能更好，速度较0慢一点点，0速度最快，安全性能最差），插入速度会有很大提高。

参数意义

0: Write the log buffer to the log file and flush the log file every second, but do nothing at transaction commit.

将日志缓冲区写到日志文件中，并每秒钟刷新日志文件，但是在事务提交时什么也不做。

1：the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file

在每个事务提交上，日志缓冲区被写到日志文件中，并且日志文件刷新到磁盘上

2：the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it

在每个事务提交上，日志缓冲区被写到日志文件中，但是日志文件没有刷新到磁盘上

innodb_autoextend_increment

此配置项作用主要是当tablespace 空间已经满了后，需要MySQL系统需要自动扩展多少空间，每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。

innodb_log_buffer_size

此配置项作用设定innodb 数据库引擎写日志缓存区；将此缓存段增大可以减少数据库写数据文件次数。

innodb_log_file_size

此配置项作用设定innodb 数据库引擎UNDO日志的大小；从而减少数据库checkpoint操作。

经过以上调整，系统插入速度由于原来10分钟几万条提升至1秒1W左右；注：以上参数调整，需要根据不同机器来进行实际调整。特别是 innodb_flush_log_at_trx_commit、innodb_log_buffer_size和 innodb_log_file_size 需要谨慎调整；因为涉及MySQL本身的容灾处理

提高数据库插入性能中心思想：
1、尽量使数据库一次性写入Data File
2、减少数据库的checkpoint 操作
3、程序上尽量缓冲数据，进行批量式插入与提交

4、减少系统的IO冲突

经过测试，我总结innodb_flush_log_at_trx_commit，对于InnoDB存储引擎来说，对插入的速度影响非常关键，其余的那三个参数几乎没有什么太大的影响；还有就是电脑配置，配置好的电脑速度要快很多；在公司电脑350s，回家自己电脑只要10s；优化参数后，感觉速度差距，电脑配置影响就没有特别大了；

上面超链接博客写的非常好，值的去看看！