面试官:MySQL一次到底插入多少条数据合适啊?

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 本文探讨了数据库插入操作的基础知识、批量插入的优势与挑战,以及如何确定合适的插入数据量。通过面试对话的形式,详细解析了单条插入与批量插入的区别,磁盘I/O、内存使用、事务大小和锁策略等关键因素。最后,结合MyBatis框架,提供了实际应用中的批量插入策略和优化建议。希望读者不仅能掌握技术细节,还能理解背后的原理,从而更好地优化数据库性能。

前言

大家好!在互联网时代,我们的每一个动作,无论是浏览网页、分享动态、点赞、购物或者搜索信息,都会在背后产生数据。这些数据,根据其用途和重要性,可能会被储存到不同的地方,其中最常见的存储载体就是——数据库。

不过,数据库并非一成不变的。根据应用场景和数据特性,我们有关系型数据库如MySQL,也有非关系型数据库,例如Redis。比如说,当你在社交网络上点赞一条动态时,为了快速响应,可能是一个基于内存的数据库如Redis首先记录这一动作,而后台可能会周期性地同步这些动作到持久化的存储系统中。

那么,当面对巨大的数据流入时,我们如何高效、稳定地将这些数据存储到数据库中呢?“我每次应该插入多少数据才最合适?” 这个问题,尽管看似简单,但涉及到的策略和技术都颇为丰富。

所以,本文的目的,就是带领大家一同探索这个话题。不论你是初涉数据库的新手,还是有经验的开发者,我都希望你能从这篇文章中获得有价值的信息。那么,不再赘述,我们现在就开始吧!

说在开头

在开始讨论这个话提前,我们先看面试场景中的对话:

👨 面试官: 在你之前的工作经验中,当你们需要向数据库中插入大量数据时,你们是如何操作的?

👦 候选者: 噢,我们使用批量插入来优化性能。

👨 面试官: 很好。那你们每次批量插入大约多少条数据?

👦 候选者: 通常我们每次批量插入超过2000万条数据。

👨 面试官: 2000万条?你确定每次都插入这么多数据?不担心资源过载或事务延迟等问题吗?

👦 候选者: 我这系统插入2000w条数据没问题啊!不信你可以回访我们Leader

👨 面试官: 但是,你有没有考虑过为什么2000w条数据可以?2000w条数据是基于什么方式算出来的?

👦 候选者: 是不是数据量?

👨 面试官: 数据量只是其中一个因素。但2000万条数据对于不同的数据库配置、硬件环境、甚至数据本身的复杂性来说,可能有不同的影响。只是简单地说“我们的系统可以处理”并不足以说明问题。真正的关键是,你知道为什么你的系统可以处理这么大的数据量吗?或者说,你们是怎么确定2000万是一个合适的数字的?

👦 候选者: 呃...这个...我不太清楚,是我们之前的一位资深工程师定的。

👨 面试官: 这就是问题所在。我们在工作中不仅要知道如何做,还要知道为什么这么做。只有了解背后的原理和策略,我们才能更好地优化和应对各种问题。

👦 候选者: 明白了,我以后会注意这个问题。

👨 面试官: 很好,对于这个问题你可以回去深入研究一下。你先回去等通知。

从上面的对话中,我们可以看到一个很现实的问题:很多人可能知道批量插入可以提高性能,但真正了解背后原因的却不多。而一个优秀的工程师,应该不仅仅满足于“这样做可以工作”,而是要探求背后的“为什么”。

所以,为了不让你们变成上面的候选者,在这篇文章中,我们将深入探讨数据库插入的各种策略、技术以及背后的原理。不过在此之前,我们还是得先了解一些基础。

数据库插入操作的基础知识

插入数据是数据库操作中的基础。但是,我们程序员将面临随之而来的问题:如何快速有效地插入数据,并保持数据库性能?当你向数据库中插入数据时,这些数据直接存储到硬盘上吗?

1.1 插入数据的原理

深入了解插入数据时背后发生的事情是优化数据库性能的关键。

1.1.1 写入缓存与磁盘同步

当数据被写入数据库时,它首先应该被写入缓存中,而不是缓慢的磁盘中。然后后台线程在适当的时间点将数据同步到磁盘上。

这样做的主要原因有以下几点:

  • 速度差异: RAM(随机存取存储器)的速度远远快于磁盘。RAM对数据的读写几乎是瞬时的。而磁盘,无论是传统的机械硬盘还是现代的固态硬盘,其读写速度都远慢于 RAM。
  • 磁盘 I/O 的成本: 每次进行磁盘 I/O操作都有一定的开销。如果数据库频繁地进行小批量的磁盘写入,这会导致大量的 I/O 开销,得不偿失哇。
  • 合并写入: 首先将数据写入 RAM,在数据库可以把数据同步到磁盘之前,累积多个写入操作。最后一次性将大量数据写入磁盘,从而减少 I/O 操作的次数和开销。

总结: 总的来说嘛,为了最大化性能,数据库首先将数据写入缓存,并在适当的时间点将这些数据同步到磁盘。这种策略不仅加速了写入操作,还有效地减少了磁盘 I/O,提高数据库性能。

👨:那脏页还没有来得及刷入到磁盘时,MySQL 宕机了,数据不就莫得了?

👦:这我懂!InnoDB 在进行更新操作时采用了 Write Ahead Log(先写日志)策略。这意味着在数据被写入磁盘之前,相关的操作会首先被记录到 redo log 日志中。这种策略赋予了 MySQL 在系统崩溃后的恢复能力。

1.1.2 事务日志与数据持久化

为了确保数据的完整性,数据库首先将插入操作写入事务日志。只有当数据被安全地写入日志后,它才被移动到实际的数据表中。

👨:那为什么数据库要用“页”来存储数据呢?

👦:我画个图,你看下

90f12750ec25e7cd1400efe98e96431.png


👨:没看出来啊,你这基础还不错。

👦:谢谢,我接着往下说:

1.2 数据存储单位:页

操作系统为了管理物理内存和虚拟内存,使用一个称为“页”的结构来管理,说白了其实就是一块固定的连续内存空间而已。这些页有固定的大小,如 4KB、8KB 或 16KB。这个大小一般是块的整数倍。

使用页进行存储有多种优势,如减少磁盘I/O、高效的空间管理以及缓存优化。了解你的数据库页的大小可以帮助你优化插入操作和空间管理!

👨 :那么,单条数据插入和批量数据插入在速度和效率上有什么不同呢?

1.3 单条数据与批量数据插入的差异

1.3.1 速度和效率比较

📘 知识点:我们的业务系统的CUD操作,每次都要伴随着事务开销。如果你在应用中执行单条插入,插入了1000次数据,那么你就有1000次事务开销。而批量插入可以将这些数据在一个事务中插入,大大减少了总的事务开销。

单条插入虽然简单明了,但在大量数据插入时,其性能上的缺陷会逐渐显现。与之相对,批量插入可以显著提高性能,但它也引入了其它问题,数据的验证和错误处理变得更为复杂。(鱼与熊掌不可兼得)

1.3.2 对数据库性能的影响

💡 小贴士:批量插入可以减少磁盘I/O次数,从而提高性能。但是,如果一次插入的数据量过大,它可能会暂时阻塞其他操作,影响数据库的响应时间。

为了达到最佳性能,您可能需要根据实际情况调整批量插入的数据量。过少的数据可能导致性能优化不足,而过多的数据可能导致数据库响应时间增加。

👨 :数据库的锁机制和并发控制策略在插入操作中起到关键作用。如果多个进程或线程试图同时插入数据,可能会发生锁争用,进而影响性能。我们又该如何优化这些机制进一步提高批量插入的性能呢?

如何决定合适的插入数据量?

为了实现数据库的最大效能,确定合适的插入数据量至关重要。但这并不是一项简单的任务,需要考虑多种因素。

👨 :很好啊,能考虑这个说明你有在思考了,那当你决定插入一大批数据时,你通常是如何选择具体的数量的?

2.1 考虑硬件和系统资源

在考虑合适的插入数据量时,首先需要考虑的是硬件和系统的限制。

磁盘I/O:

磁盘I/O是插入数据时的主要瓶颈之一。过多的插入操作会导致磁盘I/O饱和,降低系统的响应时间。

🚀 优化建议:监控磁盘I/O使用情况,确保在高插入量时不超过其峰值。

内存使用:

大量的插入操作可能会增加RAM的使用量。如果内存使用接近或达到了系统限制,可能会导致性能下降,甚至导致系统崩溃。

💡 小贴士:定期检查系统的内存使用情况,确保有足够的可用资源来处理大量的插入操作。

2.2 数据库的内部机制

数据库本身也有一些内部机制,这些机制在决定插入数据量时也应该考虑。

事务大小

数据库事务的大小直接影响其性能。较大的事务可能会导致长时间的锁定,从而影响其他查询的性能。

💡 小贴士:找到合适的事务大小平衡点是提高插入性能的关键。太小的事务可能会增加总的事务数量,而太大的事务可能会导致系统资源的饱和。

锁策略

考虑到数据库的锁策略也很重要。过多的锁争用可能会导致性能下降。

🔍 深入探讨:优化数据库的锁策略和并发控制可以进一步提高插入性能。

👨 : ?你先别管事务和锁的问题,你是通过监控这些硬件性能去调整合适的插入量,那生产怎么办?没有可以估算的大小?我不是很满意你这个回答,你思考思考再回答,我出去接个水。

👦: 这.....(拿起手机google)....

2.3 估算插入量

为了进行这个估算,我们首先要确定一条记录的结构。假设我们有以下的记录结构:

  • 整型字段 (int): 4 字节
  • 变长字符字段 (varchar): 假设平均长度为 50 字节,最大长度为 255 字节
  • 日期字段 (date): 3 字节
  • 浮点数字段 (float): 4 字节

基于上述的结构,一条记录的平均大小可以估算为:

77048c684f7a2cb495c72a16e28ca5e.png


为了考虑到某些记录可能使用 varchar 的最大长度,我们也可以计算最大记录大小:

aa5f8aecdfbf5b7df2bbc0685f97977.png


内存分析:

假设给定 8G 内存,并且预留 20% 的空间,我们可以使用的内存为:

5edd79c0f09747bc9d37c26a27579aa.png


由此,我们可以存储的最大记录数为:

89248c768100948362ce1144d68e78e.png


硬盘分析:

考虑 512G 硬盘,我们可以存储的最大记录数为:

b19c2e33259d871cfed8201fadcda15.png


👦: 差不多就这样(为自己的计算沾沾自喜中)

👨 : 可以啊,功底不错,虽然有点瑕疵(刮目相看,这轮面试差不多就让你过了)

实际应用中的策略与建议:结合MyBatis

👨 : 上面说的都是理论,你项目中一般怎么使用批量插入的?

👦: 我想下啊, 大概就这些点:

使用<foreach>标签进行批量插入

在MyBatis的映射文件中,通常使用<foreach>标签来进行批量插入。

<insert id="insertMultiple" parameterType="list">
    INSERT INTO tableName (column1, column2, ...)
    VALUES
    <foreach collection="list" item="record" separator=",">
        (#{record.column1}, #{record.column2}, ...)
    </foreach>
</insert>

ExecutorType.BATCH

Mybatis Plus也有相关的批量插入的方法。不过你也可以设置ExecutorTypeBATCH来开启批处理模式。这样,所有的SQL语句都会被积累,直到手动提交或关闭会话。

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);

使用BATCH模式时,MyBatis允许你设置一个batchSize。累积到多少数量的SQL语句时,MyBatis就会会将它们批量执行。合理设置batchSize可以避免OOM(Out of Memory)问题。一帮情况下,我们项目组就是用这个办法,怕有些新手程序员批量单条插入,导致性能缓慢。

避免频繁的会话提交

在批量插入期间,频繁提交会话可能会导致性能下降。一般在插入完所有数据后再进行一次会话提交。

👦:  大概就这些

👦:  好,了解了

总结

最后,我们来总结下扒。在本文中,我为你提供一个参与面试的视角,帮助你理解和优化数据库的插入操作。

不管你是数据库新手还是经验丰富的开发者,我希望这些建议能够为你在实际应用中带来价值。感谢你的阅读!如有任何疑问或建议,请随时与我分享!

参考文献

https://downloads.mysql.com/docs/refman-5.7-en.a4.pdf

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
22天前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
2月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
26天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
131 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
1月前
|
SQL 缓存 关系型数据库
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴因未能系统梳理MySQL缓存机制而在美团面试中失利。为此,尼恩对MySQL的缓存机制进行了系统化梳理,包括一级缓存(InnoDB缓存)和二级缓存(查询缓存)。同时,他还将这些知识点整理进《尼恩Java面试宝典PDF》V175版本,帮助大家提升技术水平,顺利通过面试。更多技术资料请关注公号【技术自由圈】。
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
|
26天前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
在项目中,为了解决Redis与Mysql的数据一致性问题,我们采用了多种策略:对于低一致性要求的数据,不做特别处理;时效性数据通过设置缓存过期时间来减少不一致风险;高一致性但时效性要求不高的数据,利用MQ异步同步确保最终一致性;而对一致性和时效性都有高要求的数据,则采用分布式事务(如Seata TCC模式)来保障。
58 14
|
29天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
53 9
|
2月前
|
存储 缓存 关系型数据库
滴滴面试:单表可以存200亿数据吗?单表真的只能存2000W,为什么?
40岁老架构师尼恩在其读者交流群中分享了一系列关于InnoDB B+树索引的面试题及解答。这些问题包括B+树的高度、存储容量、千万级大表的优化、单表数据量限制等。尼恩详细解释了InnoDB的存储结构、B+树的磁盘文件格式、索引数据结构、磁盘I/O次数和耗时,以及Buffer Pool缓存机制对性能的影响。他还提供了实际操作步骤,帮助读者通过元数据找到B+树的高度。尼恩强调,通过系统化的学习和准备,可以大幅提升面试表现,实现“offer直提”。相关资料和PDF可在其公众号【技术自由圈】获取。
|
1月前
|
SQL 算法 关系型数据库
面试:什么是死锁,如何避免或解决死锁;MySQL中的死锁现象,MySQL死锁如何解决
面试:什么是死锁,死锁产生的四个必要条件,如何避免或解决死锁;数据库锁,锁分类,控制事务;MySQL中的死锁现象,MySQL死锁如何解决
|
2月前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
1月前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
47 1