数据库性能优化的两大基石

数据库性能优化是一个永恒的话题DBA们似乎永远在讨论它。究其原因性能问题是最终用户抱怨最多的一类技术问题——没有之一。如果DBA能迅速解决性能瓶颈他们就是团队里的英雄如果迟迟无法定位问题再好的架构设计也可能被用户否定。那么问题来了面对一个性能不佳的数据库应该优先关注什么我认为有两个关键要点是任何性能优化工作的起点。一、保持统计信息最新1.1 为什么统计信息如此重要没有统计信息关系型优化器就无法做出准确的执行计划决策。数据库统计信息提供了关于数据状态和组织结构的情报优化器利用这些情报来判断如何最高效地获取数据。可以把统计信息理解为数据库的人口普查数据没有它优化器就像一个盲人只能随机选择执行路径。1.2 统计信息的核心组成统计信息类型包含内容对优化器的影响表级统计信息总行数、压缩率、总数据块数估算全表扫描成本列级统计信息离散值数量、数据分布直方图判断谓词选择性表空间统计信息活动页数、聚簇率评估I/O成本索引统计信息叶子页数量、索引层级、离散键值数判断索引扫描成本1.3 统计信息的收集时机统计信息通过特定命令生成——不同数据库有不同语法DB2RUNSTATSSQL ServerUPDATE STATISTICSOracleGATHER_TABLE_STATSMySQLANALYZE TABLE关键原则是统计信息必须在数据发生显著变化后及时更新。以下场景建议立即收集统计信息批量数据导入/导出后数据量变化超过10%大量数据删除后表结构变更后如新增索引、修改列类型定期的维护窗口如每周/每月避坑提醒统计信息过期是导致SQL突然变慢的最常见原因之一。原本运行良好的查询在数据量增长后突然变慢大概率是统计信息没有及时更新。1.4 统计信息过期的典型症状现象可能的原因执行计划突然变化统计信息未反映真实数据分布查询耗时从毫秒级变秒级优化器选择了错误的连接顺序或访问路径同一个查询有时快有时慢统计信息不稳定或采样率过低二、构建合适的索引2.1 索引设计的核心原则与收集最新统计信息同等重要的是为表创建正确的索引。索引是提升查询性能最直接的手段但也需要谨慎设计索引并非越多越好。一个简单的查询示例SELECT LASTNAME, SALARY FROM EMP WHERE EMPNO 000010 AND DEPTNO D01;2.2 索引候选方案评估对于这个查询可以创建多种索引索引方案索引列适用性分析Index1(EMPNO)可快速定位EMPNO匹配的行但仍需在结果中过滤DEPTNOIndex2(DEPTNO)可快速定位DEPTNO匹配的行但仍需过滤EMPNOIndex3(EMPNO, DEPTNO)最佳可直接定位同时满足两个条件的行为什么Index3是最佳选择Index3允许DBMS通过一次索引查找定位到同时满足EMPNO000010和DEPTNOD01的精确行无需额外过滤。关键细节是索引中列的顺序至关重要。在此场景下EMPNO应放在首位等值查询DEPTNO放在第二位。2.3 索引设计的权衡因素权衡一查询性能 vs 修改性能DBMS必须自动维护每个创建的索引每插入一行 → 更新所有索引每删除一行 → 更新所有索引更新索引列 → 更新对应索引因此索引越多插入、删除、更新的速度越慢。在OLTP环境中需要在这两者之间找到平衡点。权衡二是否复用现有索引如果EMPNO或DEPTNO上已有单列索引某些DBMS可以同时使用两个单列索引通过Bitmap Index Merge或Index Join来满足查询不一定需要新建复合索引。决策依据查询的重要性。CEO每天运行的查询值得专门创建最佳索引相比之下普通职员的临时查询使用现有索引即可。权衡三索引重载如果SQL所需的所有数据都包含在索引中DBMS可以仅通过索引满足请求无需访问表数据。之前查询中我们只查询LASTNAME和SALARY而EMPNO和DEPTNO已经是查询条件-- 创建覆盖索引 CREATE INDEX idx_emp_covering ON EMP(EMPNO, DEPTNO, LASTNAME, SALARY);现在DBMS可以仅通过索引返回所有数据再不触碰EMP表。技术术语叫仅索引访问。试图让每个查询都实现仅索引访问既不现实也不明智。应保留此技术给特别重要或频繁执行的SQL语句。2.4 索引设计速查表场景推荐策略注意事项等值查询将等值列放在索引前列区分度高的列优先范围查询、、BETWEEN范围列放在等值列之后范围列之后的其他列无法利用索引ORDER BY索引列顺序与ORDER BY一致考虑升降序匹配高频修改的表控制索引数量建议≤5个每个索引都会拖慢写操作重要查询考虑覆盖索引平衡存储成本与查询性能三、统计信息与索引的协同作用统计信息和索引不是孤立工作的——它们之间存在紧密的协同关系场景统计信息的作用索引的作用优化器评估索引扫描成本提供索引统计信息叶子页数、层级、离散键值提供数据结构支持判断是否使用索引提供列统计信息数据分布、选择性提供访问路径评估连接顺序提供表大小、行数估计提供连接键索引协同工作的最佳实践创建索引后立即更新统计信息让优化器能评估新索引的价值定期更新统计信息确保优化器掌握最新数据分布监控索引使用情况删除从未被使用的索引减少维护开销四、总结如果您是数据库性能管理的初学者请务必从本文介绍的两个核心要点开始优先级优化要点核心任务预期收益第一统计信息管理确保统计信息最新、准确优化器能做出正确的执行计划决策第二索引设计为重要查询创建合适索引显著减少数据扫描量但请记住我们对这两个领域的探讨仅是冰山一角。统计信息收集策略采样率、直方图精度和索引设计方法论复合索引列顺序、覆盖索引的使用场景都值得深入钻研。即使是资深DBA重新审视这些问题也绝无坏处新的数据库版本可能引入你尚未使用过的特性或者巩固已有的知识体系。最后一个经验之谈当你遇到数据库性能问题时不要急于调优SQL。先检查统计信息是否最新再确认索引设计是否合理。80%的性能问题答案都在这里。