mysql引擎（InnoDB和MyISAM）批量插入对比

海边捡石子
327 阅读4分钟

背景

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

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

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

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

设计过程

消息记录表结构


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多

image.png

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

image.png

InnoDB监控页面 耗时：80s

image.png

image.png

image.png

总结：

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

场景二：插入500万条数据

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

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

image.png 数据存储占用和索引占用

image.png

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

image.png

数据存储和索引占用空间 image.png

总结：

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

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

插入数据性能对比：

  1. MyISAM：

    • 通常在插入操作（尤其是批量插入）中性能较好。
    • 使用表级锁定，在插入时会锁定整个表，适合写多读少的场景。

  2. InnoDB：

    • 在高并发环境下插入性能较好，因为它使用行级锁定。
    • 支持事务，使得插入操作更加安全，但在大量单线程插入操作中可能不如MyISAM快

为什么MYISAM批量插入性能好

  1. 表级锁定：

    • MyISAM使用表级锁定，这意味着在进行插入操作时，整个表会被锁定。在批量插入时，这种锁定机制可以减少锁定和解锁的开销，因为整个表在插入过程中始终被锁定，不需要频繁地进行锁定和解锁操作。

  2. 数据文件和索引文件分离：

    • MyISAM的存储结构将数据文件（.MYD）和索引文件（.MYI）分开存储。在进行批量插入时，可以更高效地管理这些文件，特别是在处理大量数据时，这种分离可以减少文件I/O操作的复杂性。

  3. 数据写入方式：

    • MyISAM在插入数据时，通常会在表的末尾追加数据，而不是在表的中间插入。这种追加写入的方式可以减少页面分裂和数据重组的开销，特别是在处理大量数据时，可以显著提高插入速度。

  4. 索引构建：

    • 在批量插入数据时，MyISAM可以更高效地构建索引。由于数据是顺序写入的，索引的构建也可以按照顺序进行，减少了索引构建过程中的随机访问和重组操作。

  5. 缓存机制：

    • MyISAM支持高速缓存，可以缓存索引和数据。在批量插入时，缓存可以减少对磁盘的访问次数，提高数据插入的速度。

  6. 数据压缩：

    • MyISAM支持数据压缩，这可以减少存储空间的占用，并提高数据读取和写入的速度。在批量插入时，压缩数据可以减少磁盘I/O操作，提高插入效率。

综上分析

应用场景主要是批量数据插入，选择MyISAM引擎

avatar
文章
阅读
粉丝