数据库 | 面试官：一次到底插入多少条数据合适啊？.....面试连环炮

叁歲伎倆 2024-05-23 20:32 31阅读 0赞

## 数据库 | 面试官：一次到底插入多少条数据合适啊？…面试连环炮 ##

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

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

#### 插入数据的原理 ####

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

#### 写入缓存与磁盘同步 ####

*  当数据被写入数据库时，它首先应该被写入**缓存**中，而不是缓慢的**磁盘**中。然后**后台线程**在适当的时间点将数据**同步**到磁盘上。
 *  这样做的主要原因有以下几点：
    
     *  **速度差异**：RAM（随机存取存储器）的速度远远快于磁盘。RAM对数据的读写几乎是瞬时的。而磁盘，无论是传统的机械硬盘还是现代的固态硬盘，其读写速度都远慢于 RAM。
     *  **磁盘 I/O 的成本**：每次进行磁盘 I/O操作都有一定的开销。如果数据库频繁地进行小批量的磁盘写入，这会导致大量的 I/O 开销，得不偿失哇。
     *  **合并写入**：首先将数据写入 RAM，在数据库可以把数据同步到磁盘之前，累积**多个写入操作**。最后一次性将大量数据写入磁盘，从而减少 I/O 操作的次数和开销。
 *  **总结**: 总的来说嘛，为了最大化性能，数据库首先将数据写入缓存，并在适当的时间点将这些数据**同步**到磁盘。这种策略不仅加速了写入操作，还有效地减少了磁盘 I/O，提高数据库性能。
 *  ?：?那脏页还没有来得及刷入到磁盘时，MySQL 宕机了，数据不就莫得了？
 *  ?：这我懂！InnoDB 在进行更新操作时采用了 Write Ahead Log（先写日志）策略。这意味着在数据被写入磁盘之前，相关的操作会首先被记录到 redo log 日志中。这种策略赋予了 MySQL 在系统崩溃后的恢复能力。

#### 事务日志与数据持久化 ####

*  为了确保数据的完整性，数据库首先将插入操作写入**事务日志**。只有当数据被安全地写入日志后，它才被移动到实际的数据表中。
 *  ![在这里插入图片描述][30ce7bb435014a93a5d196cd1fd412a5.png]

### 数据存储单位：页 ###

*  操作系统为了管理物理内存和虚拟内存，使用一个称为“页”的结构来管理，说白了其实就是一块固定的连续内存空间而已。这些页有固定的大小，如 4KB、8KB 或 16KB。这个大小一般是块的整数倍。 使用页进行存储有多种优势，如减少磁盘I/O、高效的空间管理以及缓存优化。了解你的数据库页的大小可以帮助你优化插入操作和空间管理！

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

#### 速度和效率比较 ####

*  **知识点**：我们的业务系统的CUD操作，每次都要伴随着事务开销。如果你在应用中执行单条插入，插入了1000次数据，那么你就有1000次事务开销。而批量插入可以将这些数据在一个事务中插入，大大减少了总的事务开销。 单条插入虽然简单明了，但在大量数据插入时，其性能上的缺陷会逐渐显现。与之相对，批量插入可以显著提高性能，但它也引入了其它问题，**数据的验证**和**错误处理**变得更为复杂。（鱼与熊掌不可兼得）

#### 对数据库性能的影响 ####

*  **小贴士**：批量插入可以减少磁盘I/O次数，从而提高性能。但是，如果一次插入的数据量过大，它可能会暂时阻塞其他操作，影响数据库的响应时间。 为了达到最佳性能，您可能需要根据实际情况调整批量插入的数据量。过少的数据可能导致性能优化不足，而过多的数据可能导致数据库响应时间增加。
 *  ? ：?数据库的锁机制和并发控制策略在插入操作中起到关键作用。如果多个进程或线程试图同时插入数据，可能会发生锁争用，进而影响性能。我们又该如何优化这些机制进一步提高批量插入的性能呢？

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

*  为了实现数据库的最大效能，确定合适的插入数据量至关重要。但这并不是一项简单的任务，需要考虑多种因素。
 *  ? ：?很好啊，能考虑这个说明你有在思考了，那当你决定插入一大批数据时，你通常是如何选择具体的数量的？

### 考虑硬件和系统资源 ###

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

#### 磁盘I/O: ####

*  磁盘I/O是插入数据时的主要瓶颈之一。过多的插入操作会导致磁盘I/O饱和，降低系统的响应时间。 ? **优化建议**：监控磁盘I/O使用情况，确保在高插入量时不超过其峰值

#### 内存使用: ####

*  大量的插入操作可能会增加RAM的使用量。如果内存使用接近或达到了系统限制，可能会导致性能下降，甚至导致系统崩溃。
 *  **小贴士**：定期检查系统的内存使用情况，确保有足够的可用资源来处理大量的插入操作。

### 数据库的内部机制 ###

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

#### 事务大小 ####

*  数据库事务的大小直接影响其性能。较大的事务可能会导致长时间的锁定，从而影响其他查询的性能。
 *  **小贴士**：找到合适的事务大小平衡点是提高插入性能的关键。太小的事务可能会增加总的事务数量，而太大的事务可能会导致系统资源的饱和。

#### 锁策略 ####

*  考虑到数据库的锁策略也很重要。过多的锁争用可能会导致性能下降。 ? **深入探讨**：优化数据库的锁策略和并发控制可以进一步提高插入性能。

### 估算插入量 ###

*  为了进行这个估算，我们首先要确定一条记录的结构。假设我们有以下的记录结构：
 *  整型字段 (`int`): 4 字节
 *  变长字符字段 (`varchar`): 假设平均长度为 50 字节，最大长度为 255 字节
 *  日期字段 (`date`): 3 字节
 *  浮点数字段 (`float`): 4 字节
 *  基于上述的结构，一条记录的平均大小可以估算为：
 *  记录大小=4+50+3+4=61字节
 *  为了考虑到某些记录可能使用 `varchar` 的最大长度，我们也可以计算最大记录大小：
 *  最大记录大小=4+255+3+4=266字节

#### 内存分析： ####

*  假设给定 8G 内存，并且预留 20% 的空间，我们可以使用的内存为：
 *  可用内存=0.8×8G=6.4G
 *  由此，我们可以存储的最大记录数为：
 *  最大记录数 (平均大小)=61字节/记录6.4×109字节
 *  最大记录数 (最大大小)=266字节/记录6.4×109字节

#### 硬盘分析： ####

*  考虑 512G 硬盘，我们可以存储的最大记录数为：
 *  最大记录数 (平均大小)=61字节/记录512×109字节
 *  最大记录数 (最大大小)=266字节/记录512×109字节

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

#### 使用\`\`标签进行批量插入 ####

*  在MyBatis的映射文件中，通常使用\`\`标签来进行批量插入。
 *      <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也有相关的批量插入的方法。不过你也可以设置`ExecutorType`为`BATCH`来开启批处理模式。这样，所有的SQL语句都会被积累，直到手动提交或关闭会话。
 *      SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
 *  使用`BATCH`模式时，MyBatis允许你设置一个`batchSize`。累积到多少数量的SQL语句时，MyBatis就会会将它们批量执行。合理设置`batchSize`可以避免OOM（Out of Memory）问题。一帮情况下，我们项目组就是用这个办法，怕有些新手程序员批量单条插入，导致性能缓慢。

#### 避免频繁的会话提交 ####

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

[30ce7bb435014a93a5d196cd1fd412a5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/3b9f0e5ca1804620b336e054fc295822.png