MySQL INFORMATION_SCHEMA.PROFILING的BLOCK_OPS_IN含义及高值优化方法

字段含义 #

官方文档对该字段的基础说明为：BLOCK_OPS_IN: indicate the number of block input and output operations

两个字段实际是MySQL采集的操作系统层面的磁盘块IO计数，单位是操作次数：

• BLOCK_OPS_IN：对应当前SQL执行阶段触发的磁盘块读操作总次数，每次从磁盘读取1个或多个文件系统块，会计为1次操作，块大小通常为4KB/8KB/16KB，由操作系统和文件系统配置决定
• BLOCK_OPS_OUT：对应当前SQL执行阶段触发的磁盘块写操作总次数，每次向磁盘写入1个或多个文件系统块，会计为1次操作

从你提供的 profiling 结果可以看到，99%以上的IO操作都集中在Sending data阶段，该阶段是SQL扫描数据、计算、返回结果的核心执行阶段，千万级别的块IO是导致该阶段耗时超过227秒的核心原因。

降低IO、提升性能的优化措施 #

SQL优化方向 #

• 给查询添加合适的索引，避免全表扫描：全表扫描需要将整个表的磁盘数据加载到内存，会产生巨量的读IO，给查询条件、关联条件、排序字段添加匹配的索引，可大幅降低需要扫描的数据量
• 避免使用SELECT *查询：只返回业务需要的字段，减少需要读取的数据体积
• 拆分超大查询：如果是聚合类大查询，可拆分为多个小查询分批执行，或提前做数据预聚合，避免单次查询扫描过量数据

数据库配置优化方向 #

• 增大innodb_buffer_pool_size配置：建议设置为服务器物理内存的50%~70%，让热点数据尽量缓存在内存中，降低磁盘读取需求
• 合理调整写入相关参数：写入压力大的场景，可根据业务对数据可靠性的要求，调整innodb_log_file_size、innodb_flush_log_at_trx_commit等参数，减少频繁的磁盘刷写操作

硬件与架构优化方向 #

• 更换更高IO性能的存储介质：将机械硬盘替换为SSD或NVMe硬盘，可大幅提升单IO的处理速度
• 大表做分库分表：单表数据量超过千万甚至过亿时，拆分后可大幅降低单次查询需要扫描的数据量
• 提升服务器内存配置：更大的物理内存可以让缓冲池缓存更多热点数据，进一步减少磁盘IO需求

内容的提问来源于stack exchange，提问作者santhosh