印刻数据库

外观

MySQL 存储 IO 优化 ​

存储设备选择与规划 ​

1. 存储设备类型 ​

  • 机械硬盘 (HDD)

    • 特点：容量大、成本低、IOPS 较低
    • 适用场景：历史数据归档、低频访问数据
    • 注意事项：避免用于高并发写入场景

  • 固态硬盘 (SSD)

    • 特点：IOPS 高、延迟低、成本高
    • 类型：SATA SSD、NVMe SSD、PCIe SSD
    • 适用场景：高并发数据库、频繁读写的业务
    • 注意事项：关注写入寿命，选择合适的擦除寿命 (TBW)

  • 存储阵列

    • 特点：高可靠性、可扩展性强、提供多种 RAID 级别
    • 适用场景：企业级数据库、关键业务系统
    • 注意事项：选择合适的 RAID 级别，考虑读写性能平衡

2. RAID 级别选择 ​

  • RAID 0

    • 优点：读写性能高
    • 缺点：无冗余，一块硬盘故障导致数据丢失
    • 适用场景：对性能要求极高、有其他备份机制的场景

  • RAID 1

    • 优点：读性能好、冗余性高
    • 缺点：写性能一般、容量利用率低 (50%)
    • 适用场景：对数据安全性要求高的场景

  • RAID 5

    • 优点：容量利用率高 (n-1/n)、读性能好
    • 缺点：写性能一般、重建时间长
    • 适用场景：读多写少、容量要求较高的场景

  • RAID 6

    • 优点：允许两块硬盘同时故障、容量利用率较高 (n-2/n)
    • 缺点：写性能较差、重建时间长
    • 适用场景：对数据安全性要求极高的场景

  • RAID 10

    • 优点：读写性能优异、冗余性高、重建时间短
    • 缺点：容量利用率低 (50%)
    • 适用场景：高并发、对性能和可靠性要求都高的场景

3. 存储规划建议 ​

  • 数据与日志分离：将数据文件和日志文件存储在不同的物理设备上
  • 热数据与冷数据分离：将频繁访问的数据存储在高性能设备上
  • 考虑未来扩展性：预留足够的存储空间，考虑存储设备的扩展能力
  • 监控存储健康状态：定期检查存储设备的健康状况，及时更换故障设备

文件系统优化 ​

1. 文件系统选择 ​

  • EXT4

    • 优点：成熟稳定、广泛使用、支持大文件
    • 适用场景：一般 MySQL 部署

  • XFS

    • 优点：高性能、支持更大的文件系统和文件、并发性能好
    • 适用场景：大型数据库、高并发场景

  • Btrfs

    • 优点：支持快照、校验和、动态扩展
    • 缺点：稳定性相对较差
    • 适用场景：需要快照功能的场景

2. 文件系统挂载参数优化 ​

  • noatime：禁止更新文件的访问时间，减少 IO 操作

  • nodiratime：禁止更新目录的访问时间，减少 IO 操作

  • discard：启用 TRIM 功能，提高 SSD 性能和寿命

  • barrier=0：关闭写屏障，提高写性能（注意：可能影响数据安全性）

  • data=writeback：数据写回模式，提高写性能（注意：可能影响数据一致性）

  • 挂载示例：

    bash
    # EXT4 文件系统挂载参数
/dev/sda1 /var/lib/mysql ext4 noatime,nodiratime,barrier=0,data=writeback 0 2

# XFS 文件系统挂载参数
/dev/sdb1 /var/lib/mysql xfs noatime,nodiratime,discard 0 2

3. 文件系统调优 ​

  • EXT4 调优：

    bash
    # 调整文件系统块大小（创建文件系统时设置）
mkfs.ext4 -b 4096 /dev/sda1

# 调整预留空间比例（默认 5%，可减少至 1%）
tune2fs -m 1 /dev/sda1

  • XFS 调优：

    bash
    # 创建文件系统时设置合适的 blocksize
mkfs.xfs -b size=4096 /dev/sdb1

# 调整日志大小
xfs_growfs -L 512m /dev/sdb1

存储引擎配置优化 ​

1. InnoDB 存储引擎优化 ​

1.1 缓冲池配置 ​

  • innodb_buffer_pool_size：设置 InnoDB 缓冲池大小，建议为系统内存的 50%-70%

  • innodb_buffer_pool_instances：将缓冲池划分为多个实例，减少锁竞争

    • 建议：每个实例大小不小于 1GB
    • 公式：innodb_buffer_pool_instances = innodb_buffer_pool_size / 1GB

  • innodb_buffer_pool_chunk_size：缓冲池块大小，影响缓冲池的动态调整

  • 配置示例：

    ini
    # 8GB 内存服务器配置
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 5
innodb_buffer_pool_chunk_size = 128M

1.2 日志配置 ​

  • innodb_log_file_size：设置 redo log 文件大小，建议为 256MB-2GB

    • 考虑因素：更大的日志文件减少 checkpoint 频率，提高写性能
    • 注意事项：过大的日志文件会增加崩溃恢复时间

  • innodb_log_files_in_group：设置 redo log 文件组数量，建议为 2-4

  • innodb_log_buffer_size：设置 redo log 缓冲大小，建议为 16MB-128MB

  • 配置示例：

    ini
    innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M

1.3 写入策略 ​

  • innodb_flush_log_at_trx_commit：控制 redo log 刷新策略

    • 0：每秒刷新到磁盘，可能丢失最多 1 秒数据
    • 1：每次事务提交刷新到磁盘，最安全但性能最低
    • 2：每次事务提交写入 OS 缓存，每秒刷新到磁盘

  • innodb_flush_method：控制数据文件和日志文件的刷新方法

    • fsync：使用 fsync() 刷新数据和日志
    • O_DIRECT：直接写入磁盘，绕过 OS 缓存（适合使用 RAID 或硬件缓存的场景）
    • O_DSYNC：写入日志时使用 O_SYNC，写入数据时使用 O_DIRECT

  • innodb_doublewrite：双写缓冲区，提高数据安全性

    • 建议：生产环境启用，测试环境可禁用以提高性能

1.4 并发控制 ​

  • innodb_write_io_threads：设置写入 IO 线程数，建议为 8-64
  • innodb_read_io_threads：设置读取 IO 线程数，建议为 8-64
  • innodb_purge_threads：设置 purge 线程数，建议为 4-16
  • innodb_page_cleaners：设置页面清理线程数，建议与缓冲池实例数相同

2. MyISAM 存储引擎优化 ​

  • key_buffer_size：设置 MyISAM 索引缓冲大小，建议为系统内存的 10%-20%
  • myisam_sort_buffer_size：设置 MyISAM 索引排序缓冲大小
  • myisam_max_sort_file_size：设置 MyISAM 索引排序临时文件的最大大小
  • myisam_repair_threads：设置 MyISAM 表修复时的线程数

缓存优化 ​

1. OS 缓存优化 ​

  • vm.swappiness：控制内核交换内存的倾向

    • 建议：设置为 0-10，减少 MySQL 进程被换出的可能性
    • 配置：sysctl -w vm.swappiness=10

  • vm.dirty_ratio：设置脏页比例阈值，达到后触发同步写盘

    • 建议：设置为 20-30
    • 配置：sysctl -w vm.dirty_ratio=25

  • vm.dirty_background_ratio：设置后台写脏页的比例阈值

    • 建议：设置为 5-10
    • 配置：sysctl -w vm.dirty_background_ratio=5

  • vm.dirty_expire_centisecs：设置脏页过期时间（百分之一秒）

    • 建议：设置为 3000（30秒）
    • 配置：sysctl -w vm.dirty_expire_centisecs=3000

2. MySQL 缓存设置 ​

  • query_cache_type：查询缓存开关

    • 注意：MySQL 8.0 已移除查询缓存
    • 建议：高并发场景下禁用，因为会导致锁竞争

  • query_cache_size：查询缓存大小

    • 建议：如果启用，设置为 64MB-256MB
    • 注意：过大的查询缓存会增加维护开销

磁盘 IO 调度策略 ​

1. 调度策略类型 ​

  • CFQ (Completely Fair Queueing)：公平调度，默认策略

    • 特点：为每个进程维护一个 IO 队列，轮询处理
    • 适用场景：通用场景，适合 HDD

  • NOOP (No Operation)：简单的 FIFO 队列

    • 特点：不进行排序，直接转发 IO 请求
    • 适用场景：SSD、存储阵列等本身具备调度能力的设备

  • Deadline：按截止时间排序

    • 特点：为读请求设置更短的截止时间，优先处理读请求
    • 适用场景：读多写少的场景，适合 HDD

  • Kyber：基于令牌桶的调度策略

    • 特点：低延迟，适合混合读写场景
    • 适用场景：现代 SSD 和 HDD

2. 调度策略设置 ​

  • 查看当前调度策略：

    bash
    cat /sys/block/sda/queue/scheduler

  • 临时设置调度策略：

    bash
    # 为 SSD 设置 NOOP 策略
echo noop > /sys/block/sda/queue/scheduler

# 为 HDD 设置 Deadline 策略
echo deadline > /sys/block/sdb/queue/scheduler

  • 永久设置调度策略（通过 grub 配置）：

    bash
    # 编辑 grub 配置文件
vi /etc/default/grub

# 在 GRUB_CMDLINE_LINUX 中添加
GRUB_CMDLINE_LINUX="... elevator=noop"

# 更新 grub 配置
update-grub

表结构与数据访问优化 ​

1. 表结构优化 ​

  • 选择合适的数据类型：

    • 使用最小的合适数据类型
    • 避免使用 TEXT/BLOB 存储频繁访问的数据
    • 使用 VARCHAR 代替 CHAR，节省存储空间

  • 合理设置主键：

    • 使用自增主键，减少页分裂
    • 避免使用 UUID 作为主键，因为会导致随机 IO
    • 主键长度不宜过长，影响二级索引大小

  • 分区表设计：

    • 按时间或范围分区，减少单表数据量
    • 热点数据放在一个分区，提高查询性能
    • 注意事项：分区键的选择至关重要

2. 数据访问优化 ​

  • 减少全表扫描：

    • 为查询条件创建合适的索引
    • 使用 LIMIT 限制返回结果数量
    • 避免 SELECT * 查询

  • 优化 JOIN 查询：

    • 小表驱动大表
    • 为 JOIN 条件创建索引
    • 避免过多的表 JOIN

  • 批量操作优化：

    • 使用批量插入代替单行插入
    • 使用 LOAD DATA INFILE 导入大量数据
    • 批量更新时使用适当的 WHERE 条件

IO 性能监控与诊断 ​

1. 系统层面监控 ​

  • iostat：查看磁盘 IO 统计信息

    bash
    # 每秒刷新一次，显示扩展统计信息
iostat -x 1

  • iotop：实时查看进程 IO 使用情况

    bash
    # 按 IO 使用排序
iotop -o

  • vmstat：查看虚拟内存和 IO 统计信息

    bash
    vmstat 1

2. MySQL 层面监控 ​

  • SHOW ENGINE INNODB STATUS：查看 InnoDB 状态信息

    sql
    SHOW ENGINE INNODB STATUS\G

  • INNODB_METRICS：查看 InnoDB 性能指标

    sql
    -- 查看 IO 相关指标
SELECT name, value FROM information_schema.innodb_metrics WHERE name LIKE '%io%';

  • PERFORMANCE_SCHEMA：查看详细的性能统计信息

    sql
    -- 查看文件 IO 统计
SELECT * FROM performance_schema.file_summary_by_instance;

3. 常见 IO 性能问题诊断 ​

  • IO 利用率过高：

    • 检查是否有大量全表扫描
    • 检查是否有频繁的写入操作
    • 考虑增加缓存或使用 SSD

  • IO 延迟过高：

    • 检查存储设备健康状况
    • 检查 RAID 控制器缓存设置
    • 考虑更换为更快的存储设备

  • 读写不平衡：

    • 分析读写比例
    • 调整存储设备或 RAID 级别
    • 考虑读写分离架构

不同 MySQL 版本的差异 ​

MySQL 5.7 ​

  • InnoDB 改进：

    • 支持原生表空间加密
    • 改进了缓冲池管理
    • 支持更多的缓冲池实例

  • 性能提升：

    • 改进了查询执行计划
    • 优化了连接管理
    • 提高了并发处理能力

MySQL 8.0 ​

  • InnoDB 增强：

    • 支持即时添加列
    • 改进了死锁检测
    • 优化了缓冲池的动态调整

  • 新特性：

    • 移除了查询缓存
    • 支持窗口函数
    • 支持通用表表达式

  • IO 相关优化：

    • 改进了 redo log 写入机制
    • 优化了数据页的读写
    • 支持并行查询

常见问题（FAQ） ​

Q1: 如何确定合适的 innodb_buffer_pool_size？ ​

A1: 建议设置为系统内存的 50%-70%，具体需要考虑：

  • 系统运行的其他服务占用的内存
  • MySQL 的连接数和每个连接占用的内存
  • 操作系统缓存需要的内存
  • 可以通过监控 innodb_buffer_pool_pages_free 指标来调整

Q2: SSD 和 HDD 应该选择哪种调度策略？ ​

A2: 建议：

  • SSD：使用 NOOP 或 Kyber 调度策略
  • HDD：使用 Deadline 或 CFQ 调度策略
  • 存储阵列：使用 NOOP 调度策略，因为阵列本身具备调度能力

Q3: 如何优化大量小文件的 IO 性能？ ​

A3: 优化建议：

  • 合并小文件，减少文件数量
  • 使用合适的文件系统（如 XFS）
  • 调整文件系统的 inode 数量
  • 考虑使用 innodb_file_per_table=OFF（但不推荐，不利于管理）

Q4: 如何减少 MySQL 的写 IO？ ​

A4: 减少写 IO 的方法：

  • 优化 SQL，减少不必要的更新操作
  • 合理设置 innodb_flush_log_at_trx_commit 参数
  • 使用批量插入代替单行插入
  • 启用二进制日志压缩（MySQL 8.0+）
  • 优化索引，减少索引维护带来的写 IO

Q5: 为什么有时候增加 innodb_buffer_pool_size 性能反而下降？ ​

A5: 可能的原因：

  • 缓冲池过大导致系统内存不足，产生 swap
  • 缓冲池实例数量不足，导致锁竞争
  • 缓冲池块大小设置不合理
  • 缓存了大量不常用的数据，导致缓存命中率下降

Q6: 如何监控 MySQL 的 IO 性能？ ​

A6: 监控方法：

  • 使用 iostat、iotop 等系统工具
  • 查看 InnoDB 状态信息
  • 使用 Performance Schema 收集详细的 IO 统计
  • 使用监控工具如 Prometheus+Grafana、Zabbix 等

Q7: 分区表能提高 IO 性能吗？ ​

A7: 分区表的优势：

  • 减少单表数据量，提高查询性能
  • 可以将热点数据放在单独的分区
  • 方便管理，如快速删除旧数据
  • 注意：分区键的选择至关重要，不合理的分区可能导致性能下降

Q8: 如何优化 InnoDB 的日志性能？ ​

A8: 优化建议：

  • 设置合适的 innodb_log_file_size 和 innodb_log_files_in_group
  • 合理设置 innodb_log_buffer_size
  • 考虑将 redo log 放在单独的存储设备上
  • 根据业务需求选择合适的 innodb_flush_log_at_trx_commit 值

Q9: 什么是 InnoDB 双写缓冲区？是否可以禁用？ ​

A9: InnoDB 双写缓冲区是一个安全机制，防止部分写失败导致数据损坏。

  • 生产环境建议启用
  • 测试环境可以禁用以提高性能
  • 配置参数：innodb_doublewrite=ON/OFF

Q10: 如何选择合适的 RAID 级别？ ​

A10: 选择建议：

  • 高性能要求：RAID 0、RAID 10
  • 高可靠性要求：RAID 1、RAID 6
  • 平衡性能和可靠性：RAID 5
  • 大型数据库：RAID 10
  • 成本敏感：RAID 5