全面指南：使用JMeter进行性能压测与性能优化（中间件压测、数据库压测、分布式集群压测、调优）

目录

一、性能测试的指标

1、并发量

2、响应时间

3、错误率

4、吞吐量

5、资源使用率

二、压测全流程

三、其他注意点

1、并发和吞吐量的关系

2、并发和线程的关系

四、调优及分布式集群压测（待仔细学习）

1.线程数量超过单机承载能力时的解决方案

2. 如何搭建分布式集群

3. 实施集群压测及监控

4. 处理集群中单台施压机报错的情况

5. 长时间压测（10小时）的注意事项

6. 处理混合场景：用户思考时间及多个服务同时压测

7. 开发压测监控大屏

8. 汇总多个测试报告

9. 监控服务器的 CPU、内存、磁盘

10. 监控 Java 程序、Nginx、MySQL 数据库及 JVM 指标

11. 性能分析及测试结论

12. 区分压测问题与程序问题

13. 内存溢出与性能问题标注

14. 与 BI 项目的关联

四、调优（待仔细学习）

1. 缓存调优

2. 集群调优

3. MQ（消息队列）中间件调优

4. 分布式微服务全链路压测

五、连接数据库进行数据库压测（待仔细学习）

1、步骤

2、性能测试指标

3.性能瓶颈发现方法

---

一、性能测试的指标

1、并发量

• 定义：描述一个系统所面临的压力，服务器收到多少请求（多少/秒）

• 用的人多，服务器收到请求多，并发量就高。

• 用来描述场景

2、响应时间

• 定义：请求开始到获取结果的时长（毫秒 1000ms=1s）

• 直观反映了用户体验

• 统计方式：平均响应时间 （按响应时间分布 90% 95% 99%）

• 平均响应时间：是对所有请求的响应时间取平均值，代表整体性能的一个平均水平。

  百分位数（90%、95%、99%）：

  • 90%百分位数：表示90%的请求响应时间都小于这个值，也就是说有10%的请求响应时间是比这个值更长的。

  • 95%百分位数：表示95%的请求响应时间都小于这个值，也就是说有5%的请求响应时间比这个值更长。

  • 99%百分位数：表示99%的请求响应时间都小于这个值，也就是说有1%的请求响应时间比这个值更长。

3、错误率

• 定义：高并发海量请求场景，系统出错误的比例。

  错误率=出错请求数量/整体请求数量

4、吞吐量

• 定义：服务器1秒内处理了多少请求

• 吞吐量和并发量的区别：并发量是服务器收到请求，吞吐量是服务器处理请求

• 细分概念

  • QPS (Queries Per Second)：QPS 指的是每秒能够处理的查询数量，通常用于描述Web服务**或数据库在一定时间内处理请求的能力。

  • TPS (Transactions Per Second)：TPS 指的是每秒能够处理的事务数量，这里的事务通常指的是一系列逻辑上的操作，这些操作可能包含多个查询、插入、更新等。一个事务需要满足ACID属性（原子性、一致性、隔离性、持久性）。

5、资源使用率

• 定义：程序在测压中，服务器资源的占用情况

• 程序运行代码需要占用服务器资源，CPU/内存、磁盘、网络…

这个是网络的指标 不是性能测试的指标：

1、带宽

• 定义：网络吞吐量，系统或网络在单位时间内能够传输的数据量

• 单位：比特每秒（bps）_为单位，常见的单位有_10mb/s（兆比特每秒）

2、时延

二、压测全流程

(压力测试 及 压力测试前的接口测试 详细请看另一个文章)

1. 压测场景分析

2. 在做性能测试之前，先做接口测试

3. 收集性能指标

4. 分析性能数据

5. 梳理性能报告

三、其他注意点

1、并发和吞吐量的关系

• 并发请求：发送给服务器的请求数量

• 吞吐量：服务器每秒能处理的请求数量

(1) 先有并发，再有吞吐量（现有请求再有处理）。

(2) 并发量**>**吞吐量

2、并发和线程的关系

（1）并发量 不等于 线程数

• 有时候 一个线程 一秒钟 能产生多次请求

• 有时候 一个线程 一秒钟 不能完成一次请求

（2）线程数量=并发量*最大响应时间（秒）

四、调优及分布式集群压测（待仔细学习）

（性能测试需要剥夺业务层的干扰，有时候也需要对中间件直接压测，查看其性能）

1.线程数量超过单机承载能力时的解决方案

当单台运行 JMeter 的机器无法再增加线程数量时，可以采用 分布式集群 的方式，通过多台施压机（JMeter Server）共同承担压测任务。

2. 如何搭建分布式集群

（1）分布式集群搭建步骤如下：

1. 准备多台施压机： 确保所有施压机和控制机（JMeter Controller）在同一网络中，能够相互通信。

2. 配置 JMeter：

   • 在所有施压机上安装与控制机相同版本的 JMeter。

   • 修改

     jmeter.properties

     文件，确保

     remote_hosts

     配置项包含所有施压机的 IP 地址。例如：

     remote_hosts=192.168.1.2,192.168.1.3,192.168.1.4

3. 启动 JMeter Server：

   • 在每台施压机上，通过命令行启动 JMeter Server：

     jmeter-server

4. 启动测试：

   • 在控制机上打开测试计划，选择 Run > Remote Start All 或选择特定的施压机启动测试。

3. 实施集群压测及监控

集群实施步骤：

• 测试计划设计： 确保测试计划是分布式友好的，例如避免使用非线程安全的元素。

• 同步资源： 所有施压机应使用相同的测试脚本和资源文件。

• 启动测试： 通过控制机统一启动所有施压机的测试。

监控压测情况：

• 实时监控工具： 使用 JMeter 自带的监听器或更高级的工具（如 Grafana 与 InfluxDB）进行实时监控。

• 集中监控平台： 可以开发一个监控大屏，将各施压机的性能指标汇总展示。

4. 处理集群中单台施压机报错的情况

应对策略：

1. 自动化监控与报警： 实时监控每台施压机的状态，若发现某台施压机报错或宕机，立即触发报警。

2. 自动恢复机制： 配置自动重启脚本，确保施压机故障后能自动重启 JMeter Server。

3. 测试任务再分配： 如果施压机长时间故障，可以手动或自动将其负载转移到其他施压机。

5. 长时间压测（10小时）的注意事项

关键点：

• 资源稳定性： 确保施压机和被测系统在长时间压测下资源不泄漏（如内存、文件句柄）。

• 断点续测： 设计测试计划时考虑断点续测机制，以防测试中断后能够恢复。

• 日志管理： 合理配置日志级别，避免长时间压测产生过多日志，影响系统性能。

• 定期检查： 在压测过程中定期检查施压机和被测系统的性能指标，及时发现潜在问题。

6. 处理混合场景：用户思考时间及多个服务同时压测

实现方法：

• 用户思考时间： 在 JMeter 中使用 Timers（定时器） 元素，如 Gaussian Random Timer 或 Constant Timer，模拟用户思考时间。

• 多个服务压测： 在测试计划中设计多线程组，每个线程组针对不同的服务进行压测，或在同一线程组中配置不同的请求，确保多个服务同时承受压力。

• 逻辑控制： 使用 Controllers（控制器） 元素，如 Transaction Controller 或 Module Controller，管理复杂的测试逻辑。

7. 开发压测监控大屏

监控大屏开发步骤：

1. 数据收集：

   • 使用 JMeter Backend Listener 将性能数据发送到时序数据库，如 InfluxDB。

   • 配置监控工具（如 Grafana）连接 InfluxDB 以实时获取数据。

2. 展示内容：

   • 施压机性能指标： CPU、内存、磁盘使用率。

   • 被测服务指标： 响应时间、吞吐量、错误率。

   • 应用层指标： JVM 内存使用、垃圾回收情况、数据库性能指标（如 MySQL 的连接数、查询性能）。

3. 可视化设计：

   • 使用 Grafana 创建仪表板，将各类指标以图表、仪表盘等形式展示。

   • 设置阈值和警报规则，实时标注异常情况。

8. 汇总多个测试报告

实现方法：

• 集中化报告生成：

  • 使用 JMeter Plugins 中的 Aggregate Report 或 Summary Report 进行数据汇总。

  • 将各施压机的测试结果通过脚本或工具（如 JMeter Dashboard）汇总到统一的报告中。

• 自动化脚本：

  • 编写脚本在测试结束后自动收集各施压机的结果文件（如 JTL 文件），并进行汇总处理。

9. 监控服务器的 CPU、内存、磁盘

监控工具选择：

• Prometheus + Grafana： 通过 Node Exporter 采集服务器的 CPU、内存、磁盘等指标，并在 Grafana 中展示。

• 其他监控工具： 如 Zabbix、Nagios 等，也可以实现类似的监控功能。

实施步骤：

1. 在每台服务器上安装监控代理（如 Node Exporter）。

2. 配置 Prometheus 抓取各服务器的指标。

3. 在 Grafana 中创建仪表板，实时展示各项资源使用情况。

10. 监控 Java 程序、Nginx、MySQL 数据库及 JVM 指标

Java 程序（JVM）监控：

• JMX（Java Management Extensions）：

  • 启用 JVM 的 JMX 功能，允许远程监控。

• 监控工具：

  • 使用 Prometheus JMX Exporter 将 JVM 指标导出到 Prometheus。

• 关键指标：

  • 垃圾回收（GC）： GC 次数、GC 时间。

  • 内存使用： 新生代（Young Generation）、老年代（Old Generation）、堆外内存。

  • 线程数： 活动线程数。

Nginx 监控：

• 状态模块：

  • 启用 Nginx 的 Stub Status Module，获取当前连接数、请求数等信息。

• 监控工具：

  • 使用 Prometheus Nginx Exporter 获取并导出 Nginx 指标。

• 关键指标：

  • 活动连接数、总请求数、每秒请求数、响应时间。

MySQL 数据库监控：

• 性能指标：

  • 连接数： 当前活动连接数、最大连接数。

  • 查询性能： 每秒查询数、慢查询数。

  • 资源使用： CPU、内存、磁盘 I/O。

• 监控工具：

  • 使用 Prometheus MySQL Exporter 或 Percona Monitoring and Management (PMM) 进行监控。

实施步骤：

1. 在 Java 应用、Nginx、MySQL 服务器上安装相应的监控 Exporter。

2. 配置 Prometheus 抓取这些 Exporter 的指标。

3. 在 Grafana 中创建综合仪表板，展示所有关键指标。

11. 性能分析及测试结论

性能分析步骤：

1. 数据汇总： 收集所有施压机和被测系统的性能数据。

2. 指标对比： 将实际指标与预设的性能指标（如响应时间、吞吐量）进行对比。

3. 瓶颈识别： 通过分析 CPU、内存、磁盘、网络等资源的使用情况，识别性能瓶颈所在。

4. 异常检测： 标注在压测过程中出现的任何异常情况，如响应时间飙升、错误率增加、资源耗尽等。

5. 结论判定：

   • 测试通过： 所有关键指标在预期范围内，系统稳定。

   • 测试不通过： 某些关键指标超出预期范围，存在性能问题。

6. 问题定位： 进一步分析是测试本身的问题（如施压机资源不足）还是被测系统的问题（如内存泄漏、数据库瓶颈）。

12. 区分压测问题与程序问题

诊断步骤：

1. 施压机健康检查：

   • 确认所有施压机的 CPU、内存、磁盘等资源未达到极限。

   • 确保网络带宽充足，无网络瓶颈。

2. 被测系统监控：

   • 检查被测系统的资源使用情况，如 CPU 是否达到 100%、内存是否溢出。

   • 通过 JVM 指标分析是否存在内存泄漏或频繁的垃圾回收。

3. 日志分析：

   • 查看被测系统的日志，检查是否有异常错误（如 OutOfMemoryError）。

   • 查看 JMeter 的测试日志，确认是否有请求超时或连接失败等错误。

4. 错误分类：

   • 压测问题： 施压机资源不足、网络不稳定、JMeter 配置错误等。

   • 程序问题： 被测系统存在性能瓶颈、内存泄漏、数据库慢查询等。

5. 验证与复现：

   • 如果怀疑施压机问题，可以在另一台施压机上复现相同的测试，看问题是否依旧存在。

   • 如果问题在多台施压机上均存在，倾向于被测系统的问题。

13. 内存溢出与性能问题标注

实施方法：

• 自动标注： 在监控大屏上设置阈值，当某项指标（如 CPU 使用率、内存使用量）超过设定值时，自动高亮或标注异常。

• 日志关联： 将性能指标异常与应用日志中的错误关联起来，帮助快速定位问题原因。

• 报告生成： 在测试报告中详细记录所有异常情况，并说明其可能的原因及影响。

14. 与 BI 项目的关联

整合 BI 项目的建议：

• 数据汇总与分析： 将压测数据汇总到 BI 平台（如 Tableau、Power BI），进行更深入的数据分析与可视化。

• 自动化报告： 利用 BI 工具自动生成定期的性能测试报告，方便团队查看和决策。

• 交互式大屏： 在 BI 平台上创建交互式仪表板，实时展示压测与系统性能指标，支持多维度数据分析。

四、调优（待仔细学习）

在性能测试和系统优化过程中，调优是确保系统在高负载下依然稳定、高效运行的关键步骤。以下是关于 缓存、集群、MQ 中间件调优 以及 分布式微服务全链路压测 的详细解释和优化建议。

---

1. 缓存调优

1.1 什么是缓存

缓存是一种存储机制，用于临时存储经常访问的数据，以减少数据获取的延迟和降低数据库或后端服务的负载。缓存可以存在于客户端（如浏览器缓存）、服务器端（如内存缓存）或分布式缓存系统中。

1.2 缓存的类型

• 本地缓存： 存储在应用程序所在的同一台机器上，如使用 Java 的 ConcurrentHashMap、Caffeine、Guava 等。

• 分布式缓存： 存储在独立的缓存服务器上，支持多节点访问和高可用性，如 Redis、Memcached。

• 浏览器缓存： 存储在客户端浏览器中，通过设置 HTTP 头（如 Cache-Control）进行管理。

1.3 缓存调优策略

• 缓存淘汰策略：

  • LRU（Least Recently Used）： 移除最近最少使用的项。

  • LFU（Least Frequently Used）： 移除使用频率最低的项。

  • FIFO（First In First Out）： 按照进入缓存的顺序移除项。

• 缓存一致性：

  • 数据失效： 设置合理的 TTL（Time-To-Live），确保缓存数据不过期。

  • 缓存更新： 使用发布/订阅机制或消息队列通知缓存更新。

• 缓存预热： 在系统启动或部署后，提前将常用数据加载到缓存中，减少首次访问的延迟。

• 分片与分区： 对于大规模缓存，进行分片或分区管理，提高缓存的扩展性和访问效率。

1.4 缓存监控与优化

• 命中率监控： 通过监控缓存命中率，评估缓存的有效性，命中率低可能需要调整缓存策略或增加缓存容量。

• 内存使用监控： 确保缓存服务器有足够的内存，避免频繁的垃圾回收或内存溢出。

• 延迟监控： 监控缓存访问的响应时间，确保缓存系统本身不会成为性能瓶颈。

---

2. 集群调优

2.1 什么是集群

集群是由多台计算机（节点）通过网络连接组成的一个统一系统，旨在通过分布式计算和资源共享，提高系统的可靠性、可扩展性和性能。常见的集群类型包括负载均衡集群、高可用集群和计算集群。

2.2 集群的组成

• 控制节点（Master）： 负责管理和协调集群中的其他节点，分发任务和监控集群状态。

• 工作节点（Worker）： 执行具体的计算任务或服务请求。

• 负载均衡器： 分发客户端请求到不同的工作节点，确保负载均衡和高可用性。

2.3 集群调优策略

• 负载均衡优化：

  • 均衡算法选择： 使用合适的负载均衡算法，如轮询（Round Robin）、最少连接（Least Connections）、哈希（Hash-based）。

  • 会话保持： 对于需要会话保持的应用，配置负载均衡器支持粘性会话或使用分布式会话管理。

• 资源分配与管理：

  • 自动扩展： 根据负载情况自动增加或减少工作节点，使用 Kubernetes、Docker Swarm 等容器编排工具实现弹性伸缩。

  • 资源限制： 设置每个节点的 CPU、内存、存储等资源限制，防止单个节点资源被过度占用。

• 高可用性配置：

  • 冗余设计： 部署多个控制节点和负载均衡器，避免单点故障。

  • 故障转移： 配置自动故障转移机制，确保节点故障时请求能自动转移到其他正常节点。

• 网络优化：

  • 网络带宽： 确保集群内部网络带宽充足，避免网络瓶颈。

  • 延迟优化： 使用低延迟的网络设备和协议，减少节点间通信的延迟。

2.4 集群监控与优化

• 性能监控： 监控各节点的 CPU、内存、磁盘和网络使用情况，确保资源均衡。

• 健康检查： 定期检查节点的健康状态，及时发现并处理故障节点。

• 日志管理： 集中收集和分析集群日志，排查性能问题和故障原因。

---

3. MQ（消息队列）中间件调优

3.1 什么是消息队列（MQ）中间件

消息队列是一种异步通信机制，允许不同系统或服务之间通过发送和接收消息进行通信。常见的 MQ 中间件有 RabbitMQ、Apache Kafka、ActiveMQ、RocketMQ 等。

3.2 消息队列的作用

• 解耦系统： 使生产者和消费者独立运行，降低系统耦合度。

• 提高可靠性： 消息队列可以持久化消息，确保消息不丢失。

• 缓冲流量： 在高峰期，消息队列可以缓冲大量请求，平滑系统负载。

• 异步处理： 提高系统响应速度，适合处理耗时任务。

3.3 MQ 中间件调优策略

• 队列设计优化：

  • 合理划分队列： 根据业务功能划分不同的队列，避免单个队列过于繁忙。

  • 消息分区： 对于分布式 MQ（如 Kafka），合理设计分区数，平衡负载和并行度。

• 生产者与消费者优化：

  • 批量发送与接收： 使用批量操作减少网络开销，提高吞吐量。

  • 并发处理： 增加消费者的并发数，提升消息处理能力。

• 持久化与可靠性：

  • 消息持久化： 配置合理的持久化策略，确保消息不丢失，但也要注意持久化带来的性能影响。

  • 确认机制： 配置合理的消息确认机制，确保消息被成功消费。

• 性能参数调优：

  • 内存与缓存： 调整 MQ 中间件的内存缓存大小，提高消息处理速度。

  • 网络配置： 优化网络参数，减少消息传输延迟。

• 监控与限流：

  • 监控指标： 监控队列长度、消息吞吐量、延迟等指标，及时发现和处理性能瓶颈。

  • 限流机制： 在高负载情况下，使用限流策略防止 MQ 过载，保护下游系统。

3.4 MQ 中间件监控与优化

• 实时监控： 使用监控工具（如 Prometheus + Grafana）监控 MQ 的运行状态和性能指标。

• 日志分析： 分析 MQ 日志，排查消息积压、消费失败等问题。

• 故障恢复： 配置高可用架构，如 MQ 集群和镜像队列，确保消息服务的连续性。

---

4. 分布式微服务全链路压测

4.1 什么是分布式微服务

分布式微服务架构将应用程序拆分为多个独立的服务，每个服务负责特定的业务功能，通过网络进行通信和协作。这样的架构具有高可扩展性、灵活性和容错性。

4.2 全链路压测的概念

全链路压测（End-to-End Performance Testing）是指对整个分布式微服务系统进行全面的性能测试，模拟真实用户行为，评估系统在高负载下的响应能力、稳定性和整体性能。全链路压测涵盖了从前端到后端所有服务的性能测试。

4.3 全链路压测的关键要素

• 用户行为模拟： 模拟真实用户的操作流程和使用习惯，包括访问频率、并发数和思考时间。

• 服务依赖分析： 识别和分析各微服务之间的依赖关系，确保压测覆盖所有关键路径。

• 性能指标监控： 监控各微服务的响应时间、吞吐量、错误率及系统资源使用情况。

• 数据一致性： 确保在压测过程中，数据的一致性和完整性不受影响。

4.4 全链路压测的实施步骤

1. 测试计划设计：

   • 业务流程定义： 确定需要压测的业务流程，编写详细的测试用例。

   • 并发用户数设定： 根据业务需求和预期负载，确定并发用户数和测试持续时间。

   • 数据准备： 准备测试所需的输入数据和测试环境。

2. 测试环境搭建：

   • 环境一致性： 确保测试环境与生产环境尽可能一致，包括硬件配置、网络拓扑和服务版本。

   • 隔离测试环境： 使用独立的测试环境，避免对生产环境造成影响。

3. 测试工具配置：

   • 选择合适的测试工具： 使用 JMeter、Gatling、Locust 等性能测试工具进行压测。

   • 分布式测试配置： 配置分布式测试架构，确保能够模拟大规模的并发用户。

4. 执行压测：

   • 逐步加载： 采用逐步增加负载的方法，观察系统在不同负载下的表现。

   • 全链路覆盖： 确保测试覆盖所有关键微服务和依赖组件，避免遗漏关键路径。

5. 监控与分析：

   • 实时监控： 使用监控工具（如 Prometheus + Grafana）实时监控系统性能指标。

   • 日志分析： 收集并分析各微服务的日志，识别性能瓶颈和错误。

   • 链路追踪： 使用分布式追踪工具（如 Jaeger、Zipkin）追踪请求在各微服务间的传播，分析响应时间和瓶颈点。

6. 结果评估与优化：

   • 性能报告生成： 汇总测试结果，生成详细的性能报告。

   • 瓶颈定位与优化： 根据测试结果，定位性能瓶颈，进行针对性的优化。

   • 复测验证： 在优化后进行再次压测，验证优化效果。

4.5 分布式微服务全链路压测的优化建议

• 服务解耦与独立部署： 确保每个微服务独立部署，减少服务间的耦合，提高系统的可维护性和扩展性。

• 容错与降级机制： 实现服务的容错和降级机制，确保部分服务故障时，系统整体仍能保持稳定运行。

• 自动化测试与持续集成： 将全链路压测集成到 CI/CD 流程中，确保每次代码变更后都进行性能验证。

• 资源弹性管理： 使用容器化和编排工具（如 Kubernetes）实现资源的弹性管理，动态调整服务实例数应对负载变化。

• 安全性考虑： 在压测过程中，确保数据的安全性和隐私保护，避免敏感数据泄露。

五、连接数据库进行数据库压测（待仔细学习）

1、步骤

1. 下载JDBC驱动

   • 获取所需的JDBC驱动（JAR包），并将其放入JMeter的指定目录下。

2. 配置JDBC原件

   • 在JMeter中添加配置元件（Config Element）中的JDBC配置。

3. 连接数据库

   • 配置并测试与目标数据库的连接，确保连接正常。

4. 编写SQL操作

   • 编写需要执行的SQL语句，用于压测过程中模拟实际的数据库操作。

5. 设置线程属性

   • 配置压测的线程属性，包括线程数、持续时间和循环次数，以模拟并发用户行为。

6. 执行数据库压测

   • 启动压测，监控测试过程中的各项性能指标。

2、性能测试指标

1. 执行效率

   • 定义：评估数据库操作的整体性能和响应时间。

   • 关注点：查询执行时间、事务处理时间等。

2. 慢查询

   • 定义：执行时间超过预设阈值的SQL语句。

   • 分析内容：

     • 哪些语句存在慢查询。

     • 慢查询的原因（如缺乏索引、复杂查询等）。

3. 组件问题

   • 定义：数据库系统中各组件（如缓冲池、查询优化器等）可能存在的性能瓶颈。

   • 分析内容

     ：

     • 缓冲池使用情况。

     • 查询优化器的效率。

4. 锁问题

   • 定义：多个事务同时访问同一数据时，因锁机制导致的等待、阻塞或死锁。

   • 分析内容：

     • 哪行代码出现锁的问题。

     • 哪条语句导致锁。

     • 哪张表存在锁的问题。

5. 缓冲区（Buffer）

   • 定义：用于缓存数据和索引的内存区域（如InnoDB缓冲池）。

   • 关注点：缓冲池大小、命中率、读写次数等。

6. 表结构问题

   • 定义：数据库表设计不合理，导致查询性能低下或存储空间浪费。

   • 分析内容：

     • 表的大小和增长速度。

     • 索引设计是否合理。

     • 数据分布和访问模式。

7. 分库分表

   • 水平分表（Sharding）：

     • 定义：将一张大表按照某个规则（如ID范围、哈希值）拆分为多个表，每个表存储部分数据。

     • 优点：减少单表数据量，提高查询性能，便于水平扩展。

     • 缺点：增加查询复杂性，需修改应用逻辑。

   • 垂直分表：

     • 定义：将表的不同列拆分为多个表，每个表存储部分字段。

     • 优点：减少单表宽度，提高查询效率，分离热数据和冷数据。

     • 缺点：增加表之间的关联查询，需维护多个表的完整性。

3.性能瓶颈发现方法

在进行数据库压测后，发现性能瓶颈并确定哪些SQL语句存在慢查询或锁问题是优化数据库性能的关键步骤

一、启用并配置慢查询日志

1. 启用慢查询日志

慢查询日志记录了执行时间超过指定阈值的SQL语句。通过分析这些日志，可以识别出性能较差的查询。

– 启用慢查询日志
SET GLOBAL slow_query_log = ‘ON’;

– 设置慢查询时间阈值（例如，记录执行时间超过2秒的查询）
SET GLOBAL long_query_time = 2;

– 可选：记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ‘ON’;

2. 配置慢查询日志文件路径

在MySQL配置文件（my.cnf或my.ini）中设置慢查询日志文件路径和其他相关参数：

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = ON

3. 分析慢查询日志

使用工具如 mysqldumpslow 或 pt-query-digest 来分析慢查询日志，找出最频繁和耗时最长的查询。

使用 mysqldumpslow
mysqldumpslow -s t /var/log/mysql/slow-query.log

使用 pt-query-digest

pt-query-digest /var/log/mysql/slow-query.log

二、使用 Performance Schema 进行深入分析

1. 启用 Performance Schema

确保 performance_schema 已启用。在MySQL配置文件中：

[mysqld]
performance_schema = ON

2. 查询慢查询和锁信息

利用 performance_schema 提供的表格，可以查询到详细的执行情况，包括等待锁的信息。

– 查看慢查询
SELECT
EVENT_ID,
SQL_TEXT,
TIMER_WAIT,
LOCK_TIME,
ROWS_SENT,
ROWS_EXAMINED
FROM
performance_schema.events_statements_history
WHERE
TIMER_WAIT > 2000000000; – 时间单位为皮秒（这里表示超过2秒）

– 查看锁等待
SELECT
thd.PROCESSLIST_ID,
thd.PROCESSLIST_USER,
thd.PROCESSLIST_HOST,
thd.PROCESSLIST_DB,
thd.EVENT_NAME,
thd.STATE,
thd.SQL_TEXT
FROM
performance_schema.threads thd
JOIN
performance_schema.events_waits_current ewc
ON thd.THREAD_ID = ewc.THREAD_ID
WHERE
ewc.EVENT_NAME LIKE ‘wait/lock/%’;

三、使用 EXPLAIN 分析查询计划

对发现的慢查询，使用 EXPLAIN 分析其执行计划，找出查询的瓶颈，如全表扫描、缺失索引等。

EXPLAIN ANALYZE
SELECT * FROM your_table WHERE some_column = ‘value’;

关键指标：

• type：访问类型，尽量使用 const、eq_ref 或 ref，避免 ALL（全表扫描）。

• key：使用的索引，确保查询使用了合适的索引。

• rows：扫描的行数，行数越少越好。

• Extra：查看是否有 Using temporary 或 Using filesort，这可能影响性能。

四、监控和分析锁问题

1. 查看当前锁情况

SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM
information_schema.innodb_lock_waits w
JOIN
information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN
information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

2. 使用 SHOW ENGINE INNODB STATUS

该命令提供了当前InnoDB引擎的详细状态，包括锁等待信息。

SHOW ENGINE INNODB STATUSG

在输出中，查找 LATEST DETECTED DEADLOCK 和 TRANSACTIONS 部分，分析死锁和锁等待的详细信息，包括涉及的SQL语句和表。

五、结合压测工具的监控功能

如果你使用的是JMeter等压测工具，可以结合其监控插件或第三方监控工具（如Prometheus、Grafana）来实时监控数据库的性能指标。

1. 设置JMeter监控

• 使用JMeter的监听器（Listener）如 JDBC Request、View Results Tree，实时查看查询的响应时间和错误。

• 使用 JMeter Plugins 中的监控插件，如 PerfMon，监控服务器的CPU、内存、磁盘I/O等指标，关联到数据库性能问题。

2. 使用第三方监控工具

• Percona Monitoring and Management (PMM)：一个开源的监控解决方案，专为MySQL设计，提供实时查询分析和性能指标。

• Grafana + Prometheus：通过配置MySQL Exporter，收集数据库的性能指标，并在Grafana中可视化展示，帮助识别性能瓶颈。

六、优化发现的问题

1. 优化慢查询

• 添加或优化索引：确保查询中使用的列有合适的索引。

• 重写查询：简化复杂的查询，避免不必要的子查询和JOIN操作。

• 分区表：对于大表，使用分区技术减少查询的扫描范围。

2. 解决锁问题

• 优化事务：缩短事务的执行时间，避免长时间持有锁。

• 隔离级别调整：在保证数据一致性的前提下，适当降低事务隔离级别（如从 REPEATABLE READ 调整为 READ COMMITTED）。

• 索引优化：确保查询使用索引，减少锁的范围和数量。

3. 缓冲池和表结构优化

• 调整 innodb_buffer_pool_size：确保缓冲池足够大，以容纳大部分活跃数据，减少磁盘I/O。

• 分库分表

  • 水平分表：将表的数据按某个键值分散到多个表中，减小单表的数据量，提升查询性能。

  • 垂直分表：将表的不同列分散到多个表中，减少每个表的宽度，提升查询效率。

七、持续监控和迭代优化

性能优化是一个持续的过程，应定期进行压测和监控，及时发现和解决新的性能瓶颈。同时，结合业务发展和数据增长，动态调整数据库配置和架构，确保系统始终保持高效稳定。