知识库

《数据库设计与实践》第10章 - 数据库性能调优

15分钟阅读

《数据库设计与实践》第10章 — 数据库性能调优

来源：田浩然上传的资料/0A102 数据库设计与实践/chap10 数据库性能调优.ppt 北京大学软件与微电子学院 陈立军 课程 文件格式：PowerPoint 97-2003 二进制格式（图片型 PPT，无文本层） 状态：partial_extraction — 基于数据库性能调优标准知识体系 + 课程框架整理

一、数据库性能调优概述

1.1 什么是数据库性能调优

为了满足应用系统的性能目标，对数据库系统的配置、设计、运行进行调整和优化的过程。性能调优贯穿数据库系统的整个生命周期：

  • 设计阶段：表结构设计、索引设计、查询设计
  • 部署阶段：系统配置、内存分配、存储布局
  • 运行阶段：SQL 调优、参数调整、负载均衡

1.2 性能度量指标

指标说明
响应时间（Response Time）从请求发出到结果返回的总时间
吞吐量（Throughput）单位时间内完成的事务数（TPS/QPS）
并发数（Concurrency）系统同时支持的用户/会话数
资源利用率（Utilization）CPU、内存、磁盘 IO、网络的使用效率
可扩展性（Scalability）负载增加时性能下降的平缓程度

1.3 响应时间的组成

响应时间 = 服务时间 + 等待时间
        = CPU 时间 + IO 时间 + 锁等待 + 网络延迟

调优的目标是减少等待时间，提高服务效率。

二、性能调优的层次与方法

2.1 调优层次（从高到低）

  1. 应用层调优（收益最大）

    • SQL 语句优化
    • 应用架构优化（连接池、缓存）
    • 减少数据库访问次数

  2. 数据库设计层调优

    • 逻辑设计：范式/反范式权衡
    • 物理设计：索引、分区、聚簇
    • 视图、存储过程使用策略

  3. 数据库系统层调优

    • 内存参数配置（缓冲池、共享池）
    • 进程/线程模型
    • 事务隔离级别选择

  4. 操作系统层调优

    • 文件系统选择与挂载参数
    • 内存管理（共享内存、交换区）
    • IO 调度算法

  5. 硬件层调优（成本最高）

    • CPU 升级
    • 内存扩展
    • 存储升级（SSD、RAID）

2.2 调优方法论

自上而下原则：优先在高层调优，投入产出比更高。

  • 应用层改动收益 > 设计层 > 系统层 > OS层 > 硬件层

二八法则：80% 的性能问题由 20% 的因素导致。

  • 找到瓶颈，集中火力

循序渐进：每次只改一个变量，观察效果。

三、SQL 语句调优

3.1 查询处理的基本过程

SQL 语句 → 语法分析 → 语义检查 → 查询重写 → 查询优化 → 执行计划 → 执行 → 结果

查询优化器选择执行计划的优劣直接决定查询性能。

3.2 常见的 SQL 性能问题

问题类型典型表现优化方法
全表扫描大表无索引或索引失效建索引、改写查询条件
索引使用不当有索引但未命中调整 WHERE 条件、避免函数操作
嵌套循环效率低大表 join 用 NL选择合适的 join 算法
排序开销大ORDER BY / GROUP BY建索引避免排序、减少排序量
子查询低效相关子查询重复执行改写为 JOIN / CTE
SELECT ***返回不必要的列明确指定列名
模糊查询低效%xxx 前缀模糊全文检索、反向索引

3.3 索引调优

索引的作用：加速查询、保证唯一性、加速排序。

索引的代价：

  • 占用存储空间
  • 增删改操作需要维护索引（写入性能下降）
  • 过多索引导致优化器选择困难

索引设计原则：

  1. 为 WHERE 条件和 JOIN 字段建索引
  2. 复合索引遵循最左前缀原则
  3. 选择性高的字段作为索引前缀
  4. 避免对频繁更新的表建过多索引
  5. 定期检查无用索引，及时清理

索引失效的常见情况：

  • 对索引列使用函数或运算：WHERE YEAR(date) = 2024
  • 使用 !=、<>、NOT IN 操作符
  • 隐式类型转换
  • LIKE '%xxx' 前缀通配
  • OR 条件中有非索引列

3.4 Join 优化

三种基本 Join 算法：

算法适用场景特点
嵌套循环（Nested Loop）小表驱动大表，索引高效适合索引连接，可提前返回
归并连接（Merge Sort Join）两表都按连接键有序需要排序或已有索引，适合大数据量
哈希连接（Hash Join）大数据量无索引等值连接内存需求大，只支持等值连接

3.5 子查询优化

  • 相关子查询通常效率低（每行执行一次）
  • 尽量改写为 JOIN 操作
  • 合理使用 EXISTS / IN / JOIN 的选择
  • CTE（公用表表达式）可提高可读性，部分数据库也会优化

四、数据库设计与性能

4.1 范式与反范式

范式化设计（第三范式 BCNF）优点：

  • 减少数据冗余
  • 更新操作更快
  • 表更小，缓存效率高

反范式化（适当冗余）优点：

  • 减少 JOIN 操作
  • 查询更简单更快
  • 适合读多写少的场景

权衡策略：

  • OLTP 系统：偏范式化，保证数据一致性
  • OLAP / 报表系统：偏反范式化，提高查询性能
  • 适度冗余常用的汇总字段

4.2 分区技术

分区的好处：

  • 提高查询性能（分区裁剪）
  • 方便数据管理（按时间滚动删除）
  • 提高可用性（单个分区故障不影响全局）
  • 平衡 IO（分散到不同磁盘）

常见分区方式：

  • 范围分区（Range）：按时间、ID 范围 — 最常用
  • 散列分区（Hash）：均匀分布，均衡 IO
  • 列表分区（List）：按枚举值分区
  • 复合分区（Range-Hash, Range-List）

4.3 聚簇与组织

  • 索引组织表（IOT）：数据按主键顺序存储，适合主键查询
  • 聚簇表（Cluster）：多表共享存储，关联查询快
  • 分区索引：本地索引 vs 全局索引

五、数据库系统配置调优

5.1 内存管理

关键内存区域：

区域作用调优要点
数据缓冲区（Buffer Cache）缓存数据页，减少磁盘 IO命中率 > 95% 为目标
共享池（Shared Pool）缓存 SQL 执行计划、数据字典避免硬解析，使用绑定变量
日志缓冲区（Log Buffer）缓存重做日志大事务考虑增大
排序区（Sort Area）内存排序 vs 磁盘排序尽量让排序在内存中完成
临时表空间大排序、临时结果集放在高速磁盘上

缓冲池命中率计算：

命中率 = 1 - (物理读 / 逻辑读)

5.2 事务与锁调优

  • 缩小事务范围，减少锁持有时间
  • 选择合适的隔离级别（能用读提交就不用可重复读）
  • 避免大事务，分批提交
  • 合理使用乐观锁 / 悲观锁
  • 注意死锁预防（统一加锁顺序）

5.3 并发控制调优

  • 读写分离：主从复制，读操作走从库
  • 连接池管理：合理设置最大连接数
  • 减少锁竞争：热点行拆分、队列化
  • MVCC（多版本并发控制）：提高读并发

六、存储与 IO 调优

6.1 IO 性能瓶颈识别

  • 磁盘 IO 等待时间过长
  • 磁盘利用率持续 > 80%
  • 大量随机 IO

6.2 存储优化策略

  1. 使用 SSD：随机 IO 性能提升显著
  2. RAID 配置：
    • RAID 0：性能好，无冗余
    • RAID 1：镜像，读性能提升
    • RAID 5/6：校验冗余，适合多读少写
    • RAID 10：性能+冗余，成本高
  3. 数据文件分布：数据、索引、日志、临时表空间分盘
  4. 条带化（Striping）：大表分散到多块磁盘
  5. 数据库文件布局：
    • 重做日志放在高速独立磁盘
    • 临时表空间放在高速磁盘
    • 归档日志与数据文件分离

6.3 SQL 层面减少 IO

  • 索引访问代替全表扫描
  • 覆盖索引（不需要回表）
  • 减少返回的数据量（行和列）
  • 分区裁剪（Partition Pruning）

七、应用层调优

7.1 连接池

  • 复用连接，避免频繁创建销毁
  • 合理设置最大连接数（不是越大越好）
  • 监控连接池使用率和等待时间

7.2 缓存策略

  • 应用层缓存热点数据（Redis/Memcached）
  • 减少数据库访问次数
  • 注意缓存一致性问题

7.3 SQL 批量操作

  • 批量 INSERT / UPDATE
  • 使用批量接口（JDBC batch）
  • 减少网络往返次数

7.4 读写分离

  • 主库：写操作
  • 从库：读操作（可多个）
  • 注意主从延迟问题

7.5 分库分表

  • 垂直拆分：按业务模块分库
  • 水平拆分：按某个键分片
  • 中间件：Sharding-JDBC、MyCat 等
  • 引入复杂度，必要时才做

八、性能监控与诊断

8.1 常用诊断工具

工具用途
EXPLAIN / 执行计划分析单条 SQL 的执行方式
慢查询日志发现耗时 SQL
AWR / Statspack（Oracle）时间段性能报告
SHOW PROFILE（MySQL）语句执行各阶段耗时
pg_stat_statements（PostgreSQL）SQL 统计
操作系统工具top / iostat / vmstat / sar

8.2 EXPLAIN 重点关注

  • 访问类型：ALL（全表） < index < range < ref < eq_ref < const
  • 扫描行数（rows）与实际返回行数的差距
  • 是否 Using filesort（需要优化排序）
  • 是否 Using temporary（需要优化临时表）
  • 多表 join 的顺序和驱动表选择

8.3 调优步骤

  1. 发现问题：监控告警、用户反馈、慢查询
  2. 定位瓶颈：是 CPU、IO、锁、还是网络
  3. 分析原因：SQL 问题？设计问题？配置问题？
  4. 制定方案：修改 SQL / 加索引 / 调参数 / 改架构
  5. 验证效果：对比调优前后的性能指标
  6. 持续监控：防止性能回退

九、常见性能问题与解决方案速查

问题现象可能原因解决方案
查询慢，全表扫描缺索引 / 索引失效加索引 / 改写 SQL
CPU 使用率高复杂计算 / 排序 / 全表扫描优化 SQL / 加索引
IO 等待严重随机 IO 多 / 缓存命中率低扩大内存 / 优化 SQL / SSD
锁等待严重长事务 / 热点行缩小事务 / 读写分离 / 拆分热点
连接数打满连接泄漏 / 慢查询堆积检查连接池 / 优化慢查询
插入慢索引太多 / 逐条插入批量插入 / 减少索引 / 延迟索引维护

十、性能调优的误区与原则

常见误区

  1. 过早优化：没有测量就优化
  2. 只调 SQL 不看设计：治标不治本
  3. 盲目加索引：写入性能下降，索引维护成本高
  4. 认为硬件能解决一切：很多问题是设计和使用问题
  5. 调优参数靠感觉：没有数据支撑，瞎调
  6. 一次改多个参数：无法确定哪个有效

核心原则

  1. 先测量，再优化 — 用数据说话
  2. 找瓶颈，抓重点 — 二八法则
  3. 自上而下调优 — 应用层性价比最高
  4. 小步迭代验证 — 一次改一个变量
  5. 平衡取舍 — 读写平衡、空间时间平衡、一致性与性能平衡
  6. 考虑全生命周期 — 开发、测试、运维全阶段

关联笔记

备注

  • 本文件为图片型 PPT，无文本层可提取，内容基于数据库性能调优标准知识体系 + 陈立军课程框架整理
  • 与 chap02 ER模型、chap03 关系模型情况相同，状态为 partial_extraction
  • 如需精确课程内容，需 OCR 或获取原始课件文档

关系图谱

反向链接