【MySQL】MySQL 怎样读写数据？

目次
一、数据页大小
二、磁盘 I/O 过程
1、读取数据
2、写入数据
3、磁道切换
三、Q&A
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%
    
  
  

   数据页大小：InnoDB 存储引擎中数据页的默认大小为 16 KB，重要是为了平衡性能和内存利用服从
  脏页刷新策略：默认情况下，InnoDB 在每个事务提交时同步重做日志到磁盘，而且设置了脏页的最大百分比来控制何时将脏页写回到磁盘
   

一  页  知  秋，奥  妙  玄  心 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。