【MySQL】MySQL 如何读写数据？

最新推荐文章于 2024-08-05 17:34:36 发布

qx_java_1024

最新推荐文章于 2024-08-05 17:34:36 发布

阅读量780

点赞数 8

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_43551213/article/details/140862538

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1、为什么 InnoDB 数据页大小是 16KB 而不是其他值？

2、默认的脏页刷新策略

MySQL 数据库执行查询或写入数据时，涉及到了复杂的磁盘 I/O 操作

一、数据页大小

InnoDB 存储引擎中，默认的数据页大小为 16 KB

由于现代硬件的性能特点，较大的页面可以减少磁盘 I/O 操作的次数，从而提高性能

数据页的大小可以通过配置参数 innodb_page_size 进行调整，但通常不建议更改此默认值，除非有特殊的性能需求

二、磁盘 I/O 过程

1、读取数据

查询解析
- MySQL 解析 SQL 查询，确定需要访问哪个表以及如何访问数据
查询优化
- MySQL 的查询优化器确定最有效的执行计划，包括是否使用索引、如何连接表等
定位数据页
- InnoDB 根据表名和索引定位到相应的物理文件
- 如果查询涉及到的表没有索引或者查询条件无法利用现有索引，MySQL 将执行全表扫描
读取数据页到缓冲池
- InnoDB 使用改进的 LRU 算法管理缓冲池中的数据页
- 如果数据页已经在缓冲池中，则直接从内存读取
- 如果数据页不在缓冲池中，则从磁盘读取到缓冲池
- 每次读取一个数据页，大小为 16 KB
磁盘I/O操作
- 当需要从磁盘读取数据页时，InnoDB 会发起磁盘 I/O 请求
- I/O 操作包括寻道时间和传输时间
- 寻道时间是指磁头移动到正确的磁道上所需的时间
- 传输时间是指将数据从磁盘读取到内存所需的时间
- 为了减少磁盘 I/O 次数，InnoDB 会尽量批量读取数据页，即一次读取多个连续的数据页
处理数据页
- MySQL 遍历数据页中的数据行，根据查询条件筛选数据
- 处理后的数据被收集并返回给客户端

2、写入数据

写入数据页到缓冲池：
- 插入、更新或删除操作首先在缓冲池中进行。
- 如果需要的数据页不在缓冲池中，先从磁盘读取到缓冲池。
- 修改后的数据暂时只存在于缓冲池中。
脏页刷新：
- 被修改的数据页称为脏页（Dirty Page）。
- InnoDB会定期将脏页写回磁盘，以确保数据的一致性。
- 写回磁盘的过程称为脏页刷新（Flush Dirty Pages）。
- 刷新策略可以根据配置参数调整，例如 innodb_flush_log_at_trx_commit 和 innodb_flush_method。
磁盘I/O操作：
- 写入操作同样包括寻道时间和传输时间。
- 为了提高性能，InnoDB支持预写日志（Write-Ahead Logging, WAL）机制，即先写入重做日志（Redo Log），再写入数据页。
- 重做日志用于恢复未提交事务的数据。

3、磁道切换

磁道切换是指磁头从一个磁道移动到另一个磁道的过程

在传统的机械硬盘（HDD）中，磁盘 I/O 操作受到磁头移动速度的限制

磁道切换时间通常占磁盘 I/O 总时间的很大一部分

以下是一些关于磁道切换的重要点：

寻道时间：磁头移动到正确的磁道上所需的时间。这是磁道切换的主要组成部分
旋转等待时间：磁盘旋转到正确的位置所需的时间
传输时间：一旦磁头定位到正确的磁道和位置，数据就开始传输

在固态硬盘（SSD）中，由于没有物理磁头和磁盘旋转，寻道时间和旋转等待时间几乎可以忽略不计。因此，SSD 的读写速度通常比 HDD 快很多

总结

读取数据：涉及查询解析、优化、定位数据页、读取到缓冲池以及处理数据页。
写入数据：涉及修改缓冲池中的数据页、脏页刷新以及磁盘 I/O 操作。
磁道切换：仅在传统 HDD 中显著，SSD 则不受此限制。

三、Q&A

1、为什么 InnoDB 数据页大小是 16KB 而不是其他值？

InnoDB 选择 16KB 作为数据页的默认大小，主要是基于以下几个方面的考虑：

性能与内存使用效率的平衡：
- 较大的数据页可以减少磁盘 I/O 操作的次数，因为每次磁盘 I/O 操作都有一定的开销，包括寻道时间和传输时间
- 但是，数据页过大可能会导致内存使用效率降低，因为每个数据页都有一定的固定开销（如页头部信息）
现代硬件特性：
- 16KB 是一个比较适中的大小，可以较好地适应现代硬件的特性
- 大多数现代服务器的内存容量较大，能够支持较大的数据页
- 16KB的数据页大小可以充分利用现代磁盘的高速缓存，减少磁盘 I/O 次数
兼容性和历史因素：
- InnoDB 最初的设计是在 16KB 的数据页大小下进行了优化
- 这个大小已经成为了 InnoDB 的一个标准配置，并且在实践中证明了其性能优势
- 改变默认数据页大小可能会引入兼容性问题，并且需要对 InnoDB 内部的许多数据结构和算法进行调整
与其他系统和软件的兼容性：
- 16KB 是一个常见的块大小，许多操作系统和其他软件系统也使用类似的块大小
- 使用 16KB 的数据页可以更好地与其他系统集成，并减少潜在的性能瓶颈

其他值

8KB：
- 8KB 的数据页大小仍然可以减少磁盘 I/O 次数，但相比于 16KB，它可能需要更多的磁盘 I/O 操作，尤其是在数据量较大时
- 8KB 的数据页可能会导致更高的内存使用率，因为每个数据页的固定开销占比更高
32KB：
- 32KB 的数据页大小可以进一步减少磁盘I/O次数，但对于内存有限的系统而言，可能会导致内存使用效率降低
- 32KB 的数据页可能会占用更多的内存，尤其是当缓冲池大小受限时
更大值：
- 更大的数据页（例如64KB或更大）可能会导致更高的内存使用率，因为每个数据页的固定开销占比更高
- 对于较小的数据表，更大的数据页可能会导致内存浪费，因为每个数据页可能不会被充分利用

InnoDB 选择 16KB 作为数据页的默认大小是经过精心考虑的结果

这个大小在大多数情况下提供了良好的性能和内存使用效率之间的平衡

当然，也可以根据具体的硬件环境和应用需求来调整数据页的大小，但这通常需要仔细评估对性能的影响

如果有特殊的需求或者想要进一步优化数据库性能，可以考虑调整数据页大小，但通常建议保持默认值不变，除非有明确的理由和测试结果支持改变

2、默认的脏页刷新策略

InnoDB 存储引擎中，默认的脏页刷新策略是由几个配置参数控制的，主要包括：

innodb_flush_log_at_trx_commit：
- 控制何时将重做日志（redo log）写入磁盘
- 默认值为1，表示在每个事务提交时都同步重做日志到磁盘，以确保数据的持久性
- 可选值还包括0（每秒一次同步）和2（只在事务提交时将日志标记为已提交，但不一定立即写入磁盘）
innodb_flush_method：
- 控制 InnoDB 如何将数据写入磁盘
- 默认值取决于操作系统，但在大多数情况下默认为使用操作系统的默认 I/O 方法
innodb_lru_scan_depth：
- 控制 LRU 列表扫描的深度，用于决定哪些数据页应该被刷新到磁盘
- 默认值为 1024，表示在每次需要释放内存时，InnoDB会在 LRU 列表中扫描最多 1024 个页，以确定哪些页可以被刷新到磁盘
innodb_max_dirty_pages_pct 和 innodb_max_dirty_pages_pct_lwm：
- 控制脏页的最大百分比。
- innodb_max_dirty_pages_pct 设置脏页占缓冲池的最大比例，默认为75%
- innodb_max_dirty_pages_pct_lwm 设置一个较低的阈值，当脏页比例达到这个值时，InnoDB 会开始主动刷新脏页，默认为70%