文档/教程【免费下载链接】90DaysOfDevOpsThis repository started out as a learning in public project for myself and has now become a structured learning map for many in the community. We have 3 years under our belt covering all things DevOps, including Principles, Processes, Tooling and Use Cases surrounding this vast topic.项目地址https://gitcode.com/gh_mirrors/90/90DaysOfDevOps点击查看免费下载本篇是 90DaysOfDevOps 2023 数据库系列(第 63–69 天)的第五篇,系统讲解数据库性能调优的完整方法论:从服务器硬件与运行环境(物理机、虚拟机、Kubernetes)、到数据库引擎配置、再到查询级别的执行计划与索引调优,并在 PostgreSQL 的 dvdrental 示例库上用可复现的步骤验证为什么索引没被使用以及如何强制验证索引效果。读完后你将掌握一套服务器 → 引擎 → 查询的三层调优排查路径,以及一套在开发环境中用工作负载对比基线进行调优的实操流程。一、系列定位与前置条件本文承接 Day 66:High availability and disaster recovery,是数据库系列的第五篇。整个七天的数据库系列按以下顺序组织(完整目录见 Day 63:An introduction to databases):Day 63 数据库入门(关系型 vs NoSQL)Day 64 数据查询(SQL 基础、DML、建表与数据导入)Day 65 备份与恢复Day 66 高可用与灾难恢复Day 67 性能调优(本文)Day 68 数据库安全Day 69 监控与故障排查性能调优是数据库领域的一个巨大主题,原文指出有成千上万的书、博客、视频和会议报告在讲它,很多人靠它吃一辈子饭,因此本文不追求面面俱到,而是聚焦确保数据库系统达到性能目标时的主要关注点,给出可落地的调优框架。要跟随本文的动手实验,需要准备:Docker(Docker Desktop 或替代品如 Rancher Desktop、Finch),用于运行演示用的 PostgreSQL 容器;pgAdmin(免费),作为连接与查询 PostgreSQL 的图形工具;演示使用系列作者自制的 PostgreSQL 自定义镜像ghcr.io/dbafromthecold/demo-postgres:latest,其中预置了 PostgreSQL 及示例数据库dvdrental(约 437MB 镜像,网络受限时拉取稍慢);本系列 Day 64 已经讲过 SQL 的 SELECT/INSERT/UPDATE/DELETE 基础,本文实验默认读者已掌握。二、第一层:服务器性能调优性能调优的第一步是彻底了解你的环境。这意味着先从数据库运行在其上的硬件说起。2.1 物理硬件三大要素:CPU、内存、存储在传统部署模式下,是一台物理服务器挂接存储,安装操作系统后再安装数据库引擎。此时需要确认三个核心规格:CPU—— 服务器的算力是否足以支撑数据库引擎将要执行的事务量?内存—— 数据库系统会把数据缓存到内存中来执行操作(某些数据库甚至完全工作在内存中,例如 Redis)。服务器是否有足够的内存来处理将要操作的数据量?存储—— 服务器可用的存储是否足够快,以便数据从磁盘请求时能以最小延迟返回?2.2 虚拟化层:宿主机成为新的考量维度如今数据库服务器最常见的运行方式是虚拟机:物理机被切分成若干虚拟机,以提高资源利用率、提升可管理性并降低成本。但这带来了另一层性能调优必须考虑的对象——虚拟机所在宿主机:除了虚拟机自身的 CPU、内存、存储之外,还必须评估宿主机是否有足够资源承载该虚拟机的流量;同一宿主机上还有哪些其他虚拟机(邻居噪声);宿主机是否超卖(oversubscribed)——即分配给虚拟机的资源总量超过了物理机实际拥有的资源。若超卖严重,数据库性能会不可预期地波动。2.3 容器与 Kubernetes:集群级视角把数据库引擎跑在容器里正变得越来越流行(本系列 Day 66 的复制演示就是在容器里完成的)。但原文强调:单容器跑数据库在生产场景会有问题(参见高可用篇),生产负载通常由容器编排器托管,而其中走到前台的就是Kubernetes。于是性能调优的关注点又扩展为集群级问题:Kubernetes 集群的节点(宿主机)是什么规格?能否承载数据库引擎的流量?集群上还跑了哪些其他工作负载(与数据库争抢 CPU/内存/IO)?数据库引擎的 Deployment 清单中的资源配置(settings/requests/limits)是否正确?原文给出的结论是:彻底了解环境是构建一台能达到性能标准的服务器的第一步;确认服务器能扛住打向数据库的事务之后,才能进入下一步——调优数据库引擎本身。三、第二层:数据库引擎性能调优3.1 引擎参数分两类数据库系统自带海量可调参数。原文将其分为两类:通用型参数:DBA 会认为无论什么负载都必须调整的设置;负载相关型参数:取值取决于具体工作负载本身的设置。一个典型的负载相关型例子是内存配置:若分配给引擎的内存不足以支撑负载,需要调大;反之,若完全没有上限限制,数据库引擎可能吃满整台服务器的内存,把操作系统饿死引发更严重的问题——这时反而需要限制它。原文用这个双向陷阱说明:同一个参数,取小了不行,不限制也不行,拿到正确的配置对初次接触某个数据库系统的人尤其有挑战性。3.2 方法论:开发环境 工作负载模拟 基线对比正确的做法是借助开发环境:搭建一个(大致)接近生产环境的开发库,DBA 在其上修改配置并观察效果。但开发环境天然有两个短板:吞吐量通常远低于生产,数据量也更小。为此,原文给出调优的标准工作流:使用工作负载模拟工具(workload simulation tools)向数据库打入可控的模拟流量;先跑一轮工具,拿到系统性能的基线(baseline);做一组配置变更;再跑一轮同样的工具,对比性能提升了多少(或是否变差)。这套基线 → 变更 → 复测的闭环,是数据库引擎调优能够客观、可验证的关键。当引擎配置确定后,就进入最后一层——查询性能调优。四、第三层:查询性能调优即使服务器足够强大、引擎配置得当,单条查询的性能依然可能很差。这一层是原文动手实验的主战场。4.1 执行计划与统计信息数据库厂商提供了大量工具,可以捕获打到数据库上的查询并报告其性能;当某条查询开始变慢时,DBA 需要分析哪里出了问题。核心机制是:查询到达数据库后,优化器会为它生成执行计划(execution plan)——即数据将从数据库中如何取出的路线。而计划是从存放在数据库中的统计信息(statistics)推导出来的,这些统计信息可能过期。一旦过期,生成的计划就可能使查询低效。原文给的例子:往一张表里插入了大批新数据后,统计信息若未更新,引擎不知道新数据的存在,对该表的所有查询都会拿到不理想的计划。4.2 索引:查询性能的第二个关键因素若一条查询打到一张大表上却没有辅助索引,它只能逐行扫描整张表直到找到目标行——取数效率极低。索引的作用是指向正确行,经典的比喻是书的目录:读者不必从第一页翻到最后一页,而是先翻到目录,查到条目指向的页码,直接翻到那一页。同时要清醒地认识到索引的代价:表数据被更新时,其上每一个索引都需要同步更新,因此 INSERT/UPDATE/DELETE 会付出额外开销。原文的提醒是:不要为了覆盖所有查询就无节制地加索引,核心是在读取收益与写入代价之间找到平衡。4.3 DBA 排查查询性能的两个关键问题综合 4.1 与 4.2,排查查询性能时 DBA 会反复问自己两个问题:统计信息是最新的吗?(Statistics up to date?)这条查询有没有辅助索引?(Supporting indexes?)五、动手实验:在 dvdrental 上验证索引(完整步骤)下面完整复现原文在dvdrental示例库上的索引实验。5.1 启动演示容器docker run -d \ --publish 5432:5432 \ --env POSTGRES_PASSWORDTesting1122 \ --name demo-container \ ghcr.io/dbafromthecold/demo-postgres:latest然后用 pgAdmin 连接(服务器localhost,密码Testing1122),连入dvdrental库并打开查询窗口。5.2 实验一:last_name 查询——有索引,但优化器选择了顺序扫描执行查询:SELECT * FROM actor WHERE last_name Cage返回 200 行。在 pgAdmin 中选中该语句点击Explain查看执行计划:可以看到计划非常简单,只有一个操作:对actor表的扫描。然而,在 pgAdmin 左侧菜单查看actor表的结构时,会发现last_name列上明明存在一个索引:为什么索引没被使用?原因在于表的规模:actor表只有 200 行,数据库引擎通过成本评估后认为,对这么小的表做全表扫描比走索引查找更高效。原文特意指出,这正是查询性能的众多微妙之处之一。5.3 实验二:强制使用索引,验证索引可用先禁用顺序扫描:SET enable_seqscan false注意:原文特别强调,enable_seqscan这个设置只用于开发阶段,用来验证在更大的数据集上这条查询是否会走索引。千万不要在生产环境里这么干!再次选中那条 SELECT 并点击 Explain:此时计划变成:先走索引定位,再回到表中取数据。由此确认:last_name查询确实有一条可用的索引路径,如果未来这张表的数据量增长,引擎的成本评估自然会转向索引。5.4 实验三:first_name 查询——没有索引,补一个再看换一条查询:SELECT * FROM actor WHERE first_name NickExplain 的结果退回为顺序扫描——因为first_name列上没有辅助索引:手动创建一个索引:CREATE INDEX idx_actor_first_name ON public.actor (first_name)再次对 first_name 的 SELECT 执行 Explain:查询现在拥有了辅助索引,计划相应更新。5.5 实验小结这组实验完整演示了 DBA 排查查询性能的闭环:捕获慢查询 → 看执行计划(Seq Scan 还是 Index Scan);核对表上是否有覆盖谓词列的索引;没有就建,有却没走就用enable_seqscan等手段验证索引路径是否可用(仅限开发环境);结合统计信息的新鲜度判断计划是否被过期统计带偏。同时它揭示了一个重要事实:索引是否被使用是成本驱动(cost-based)的决策——200 行的小表走全表扫描是正确决策,而不是配置错误。六、总结本文给出了性能调优的三层排查框架:层级关注点关键动作服务器物理 CPU/内存/存储、虚拟机宿主与超卖、K8s 集群规格与清单配置彻底了解运行环境,确认算力足够承接事务量数据库引擎通用参数 负载相关参数(如内存上限的双向陷阱)开发环境 工作负载模拟 基线→变更→复测闭环查询执行计划、统计信息时效、索引问两个问题:统计信息最新吗?查询有辅助索引吗?并配套了基于dvdrentaldemo-postgres镜像的完整可复现实验,覆盖有索引但没走(小表成本决策)、强制验证索引与缺索引补齐三种典型场景。下一篇 Day 68:Database security 将讨论数据库安全的三层(服务器、引擎、数据)访问控制与数据加密;如需回顾复制方案请见 Day 66,系列收尾的监控与排障见 Day 69。赞分享文档/教程【免费下载链接】90DaysOfDevOpsThis repository started out as a learning in public project for myself and has now become a structured learning map for many in the community. We have 3 years under our belt covering all things DevOps, including Principles, Processes, Tooling and Use Cases surrounding this vast topic.项目地址https://gitcode.com/gh_mirrors/90/90DaysOfDevOps点击查看免费下载相关推荐httpbin数据库性能调优索引与查询优化httpbin数据库性能调优索引与查询优化 在现代Web开发中API服务的响应速度直接影响用户体验和系统稳定性。httpbin作为一款广泛使用的HTTP请求开发工具测试API设计Ubicloud数据库性能索引优化与查询调优Ubicloud数据库性能索引优化与查询调优 引言数据库性能瓶颈的隐形代价 在云原生架构中数据库性能直接决定了服务响应速度与资源利用率。Ubicloud作云原生后端运维highlight.io数据库性能调优索引优化与查询重写案例highlight.io数据库性能调优索引优化与查询重写案例 在当今数字化时代应用程序的性能对于用户体验和业务发展至关重要。而数据库作为应用程序的核心组成部可观测性后端上一篇通达信缠论量化插件终极指南3分钟掌握智能K线分析下一篇3步免费实现Windows电脑变身AirPlay接收器airplay2-win完整指南创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考