mysql引擎（InnoDB和MyISAM）批量插入对比-CSDN博客

本文链接：https://blog.csdn.net/liuyanqun_q/article/details/141357996

背景

系统中为了记录逻辑日志信息（IM信令），一次信令的传输可能要10次的日志结构化数据记录到mysql中，暂不考虑使用es或其他存储数据系统。

要求IM信令系统的并发TPS要到5000,意味着每次5000*10次的数据库IO，在插入数据库做了优化，模仿redis持久化模式

日志记录累计一定数量：有限的阻塞队列(500条)，队列满则进行一次批量插入
定时持久化：定时每5秒进行一次数据库IO

该系统的日志记录表只有插入数据，没有更新，后台很少的查询操作

设计过程

消息记录表结构

mysql

CREATE TABLE `t_im_message` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID', `msg_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '消息唯一标识', `sender` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '发送者id', `receiver` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '接收者id', `event` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '事件', `content` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '消息内容', `event_time` bigint NOT NULL DEFAULT '0' COMMENT '事件时间', PRIMARY KEY (`id`), KEY `t_im_message_msg_id_IDX` (`msg_id`) USING BTREE, KEY `t_im_message_event_time_IDX` (`event_time`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1573501 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='IM消息记录表';

对比InnoDB和MyISAM

除了mysql引擎不同，其他条件都相同，进行压力测试，项目详见github

场景一：插入100万条数据

条件：线程池并发，500条记录批量插入，总共执行2000次

MyISAM的监控数据。耗时：41s多

据库数据和包含索引数据占用空间

InnoDB监控页面耗时：80s

总结：

MyISAM引擎效率是InnoDB的1倍
MyISAM存储空间相比于InnoDB小百分之三十

场景二：插入500万条数据

条件：线程池并发，500条记录批量插入，总共执行10000次

InnoDB的监控信息，耗时15分40s。

数据存储占用和索引占用

MyISAM的监控信息，耗时3分41s。

数据存储和索引占用空间

总结：

MyISAM比Innodb 速度快不到5倍，存储占用小

随着数据量越来越大，MyISAM的优势越来越好

插入数据性能对比：

MyISAM：
- 通常在插入操作（尤其是批量插入）中性能较好。
- 使用表级锁定，在插入时会锁定整个表，适合写多读少的场景。
InnoDB：
- 在高并发环境下插入性能较好，因为它使用行级锁定。
- 支持事务，使得插入操作更加安全，但在大量单线程插入操作中可能不如MyISAM快

为什么MYISAM批量插入性能好

表级锁定：
- MyISAM使用表级锁定，这意味着在进行插入操作时，整个表会被锁定。在批量插入时，这种锁定机制可以减少锁定和解锁的开销，因为整个表在插入过程中始终被锁定，不需要频繁地进行锁定和解锁操作。
数据文件和索引文件分离：
- MyISAM的存储结构将数据文件（.MYD）和索引文件（.MYI）分开存储。在进行批量插入时，可以更高效地管理这些文件，特别是在处理大量数据时，这种分离可以减少文件I/O操作的复杂性。
数据写入方式：
- MyISAM在插入数据时，通常会在表的末尾追加数据，而不是在表的中间插入。这种追加写入的方式可以减少页面分裂和数据重组的开销，特别是在处理大量数据时，可以显著提高插入速度。
索引构建：
- 在批量插入数据时，MyISAM可以更高效地构建索引。由于数据是顺序写入的，索引的构建也可以按照顺序进行，减少了索引构建过程中的随机访问和重组操作。
缓存机制：
- MyISAM支持高速缓存，可以缓存索引和数据。在批量插入时，缓存可以减少对磁盘的访问次数，提高数据插入的速度。
数据压缩：
- MyISAM支持数据压缩，这可以减少存储空间的占用，并提高数据读取和写入的速度。在批量插入时，压缩数据可以减少磁盘I/O操作，提高插入效率。