背景
系统中为了记录逻辑日志信息(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操作,提高插入效率。
综上分析
应用场景主要是批量数据插入,选择MyISAM引擎