数据库性能测试过程的关注点

思想是一切事物的源头

869人浏览 · 2025-11-06 14:23:44

思想是一切事物的源头 · 2025-11-06 14:23:44 发布

数据库性能测试过程的关注点

关键指标

关键指标

1.QPS

queries per seconds 每秒钟查询数量

首先，需要明确一个核心概念：数据库本身通常不会直接提供一个名为“当前QPS”的实时计数器。QPS 是一个需要通过在特定时间间隔内采样两次状态变量，然后计算差值来得到的动态指标。

其基本计算规则为：

QPS = (本次采样查询总数 - 上次采样查询总数) / 两次采样的时间间隔(秒)

Mysql QPS的查询及计算过程

MySQL 的性能指标主要存储在 SHOW GLOBAL STATUS 的结果中。我们关注 Questions 或 Queries 这两个状态变量。

Questions: 表示服务器自启动以来执行的所有 SQL 语句总数，包括由客户端发送的查询以及存储程序内部的语句，但不包括 COM_PING、COM_STATISTICS 等与数据库无关的命令。

Queries: 表示服务器自启动以来执行的所有语句总数，它包括了 Questions 以及所有在存储过程中执行的语句，还包含了诸如 SHOW STATUS 这种命令。通常这个值会比 Questions 大。

注意：对于衡量数据库的查询压力，通常使用 Questions

计算规则举例

操作步骤

第一步：采样

执行如下语句：

mysql> SHOW GLOBAL STATUS LIKE 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 20000 |
+---------------+-------+

如上查询出来的数量为20000，当前的时间记录为T1

第二步：隔一段时间假设过10秒再采样一次

执行如下语句

mysql> SHOW GLOBAL STATUS LIKE 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 30450 |
+---------------+-------+

如上查询出来的数量为30450，当前的时间记录为T2

计算TPS

从上面的查询结果可以得出
第二次查询到的数量为：30450
第一次查询到的数量为：20000
可以计算出10秒的总查询量为 30450 - 20000 = 10450
时间间隔 = T2 - T1 = 10 秒
QPS = 10450 / 10 = 1045
提示：这里可以添加本文要记录的大概内容：

一键查询脚本

# 第一步，获取初始值
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Questions';" | grep Questions | awk '{print $2}' > /tmp/q1.txt

# 等待10秒
sleep 10

# 第二步，获取结束值并计算
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Questions';" | grep Questions | awk '{print $2}' > /tmp/q2.txt
Q1=$(cat /tmp/q1.txt)
Q2=$(cat /tmp/q2.txt)
QPS=$(echo "scale=2; ($Q2 - $Q1) / 10" | bc)
echo "Current QPS: $QPS"

达梦数据库 QPS的查询及计算过程

达梦数据库的系统动态性能视图 V$SYSSTAT 中存储了类似的指标。我们关注 total statements 或 user statements。

total statements: 自数据库启动以来执行的所有SQL语句总数（包括系统内部的）

user statements: 自数据库启动以来用户执行的SQL语句总数

对于衡量用户发起的查询压力，通常使用 user statements

计算规则举例

计算规则与 MySQL 完全相同

操作步骤

第一步：第一次采样（在时间T1）

SQL> SELECT STAT_VAL FROM V$SYSSTAT WHERE NAME = 'user statements';

假设返回 STAT_VAL_1 = 50000，记录时间 T1

第二步：第二次采样 (在时间 T2)

SQL> SELECT STAT_VAL FROM V$SYSSTAT WHERE NAME = 'user statements';

假设返回 STAT_VAL_2 = 50200，记录时间 T2

第三步：计算QPS

总查询增量 = STAT_VAL_2 - STAT_VAL_1 = 50200 - 50000 = 200

时间间隔 = T2 - T1 = 5 秒

QPS = 200 / 5 = 40

这意味着在过去5秒内，数据库平均每秒处理了40个用户查询

一键查询脚本（达梦）

# 假设已经配置了达梦的命令行工具 disql 的环境
# 第一步，获取初始值
echo "SELECT STAT_VAL FROM V\$SYSSTAT WHERE NAME = 'user statements';" | disql USER/PASSWORD@HOST:PORT -s | grep -v ‘^$’ | tail -1 > /tmp/dm1.txt

# 等待5秒
sleep 5

# 第二步，获取结束值并计算
echo "SELECT STAT_VAL FROM V\$SYSSTAT WHERE NAME = 'user statements';" | disql USER/PASSWORD@HOST:PORT -s | grep -v ‘^$’ | tail -1 > /tmp/dm2.txt
S1=$(cat /tmp/dm1.txt)
S2=$(cat /tmp/dm2.txt)
QPS=$(echo "scale=2; ($S2 - $S1) / 5" | bc)
echo "Current DM QPS: $QPS"

总结与关键点

在这里插入图片描述

重要注意事项

平均值而非瞬时值：这种方法得到的是采样时间窗口内的平均值，而不是一个瞬时的精确值。时间窗口越短，越能反映瞬时的波动；窗口越长，数据越平滑。
数据库重启：如果数据库在两次采样之间发生了重启，计数器会被重置，导致计算出的QPS毫无意义（会是一个巨大的负数或正数）。
监控工具原理：市面上所有的数据库监控系统（如 Prometheus + Grafana, Zabbix等）都是基于这个原理，定期（如每1秒、15秒）采集这些状态值，然后通过计算差值来绘制出QPS的曲线图。
选择哪个指标：选择 Questions/user statements 通常更能代表外部客户端的请求压力，而 Queries/total statements 则反映了数据库实例内部整体的繁忙程度。根据你的监控目的进行选择。

2.TPS

Tranaction per seconds :每秒业务数量

核心概念澄清

数据库查询数量：指数据库接收并执行的所有SQL语句数量（如SELECT, INSERT, UPDATE, DELETE等）。

业务数量：指完成一个业务操作所对应的数据库操作。通常以事务为单位来衡量，因为一个业务操作通常对应一个数据库事务。

核心计算规则

每秒业务数量 = (本次采样事务总数 - 上次采样事务总数) / 两次采样的时间间隔(秒)

MySQL的计算过程

MySQL中可以通过以下方式获取事务相关的统计信息

方法1：通过 Com_commit 和 Com_rollback（推荐）

SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';

方法2：通过事务相关的计数器

SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_commit', 'Com_rollback');

计算规则与举例

计算规则：
TPS(每秒业务数量) = [(本次Com_commit + 本次Com_rollback) - (上次Com_commit + 上次Com_rollback)] / 时间间隔

操作步骤

第一步：第一次采样(时间 T1)

mysql> SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_commit', 'Com_rollback');
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_commit    | 1000  |
| Com_rollback  | 50    |
+---------------+-------+

记录：Commit_1 = 1000, Rollback_1 = 50, 时间 T1

第二步：第二次采样 (时间 T2)

mysql> SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_commit', 'Com_rollback');
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_commit    | 1250  |
| Com_rollback  | 60    |
+---------------+-------+

记录：Commit_2 = 1250, Rollback_2 = 60, 时间 T2

第三步：TPS

总事务增量 = (1250 + 60) - (1000 + 50) = 1310 - 1050 = 260

时间间隔 = 10 秒

TPS = 260 / 10 = 26

这意味着在过去10秒内，平均每秒处理了26个业务事务。

一键脚本

# 第一次采样
mysql -u root -p -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_commit', 'Com_rollback');" > /tmp/status1.txt
commit1=$(grep Com_commit /tmp/status1.txt | awk '{print $2}')
rollback1=$(grep Com_rollback /tmp/status1.txt | awk '{print $2}')
total1=$((commit1 + rollback1))

sleep 10

# 第二次采样
mysql -u root -p -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_commit', 'Com_rollback');" > /tmp/status2.txt
commit2=$(grep Com_commit /tmp/status2.txt | awk '{print $2}')
rollback2=$(grep Com_rollback /tmp/status2.txt | awk '{print $2}')
total2=$((commit2 + rollback2))

tps=$(( (total2 - total1) / 10 ))
echo "Current TPS: $tps"

达梦数据库的计算过程

方法1：通过动态性能视图 V$SYSSTAT

-- 查询事务提交和回滚次数
SELECT STAT_VAL FROM V$SYSSTAT WHERE NAME = 'commit times';
SELECT STAT_VAL FROM V$SYSSTAT WHERE NAME = 'rollback times';

方法2：综合查询

SELECT 
    (SELECT STAT_VAL FROM V$SYSSTAT WHERE NAME = 'commit times') as commit_times,
    (SELECT STAT_VAL FROM V$SYSSTAT WHERE NAME = 'rollback times') as rollback_times
FROM DUAL;

计算规则与举例

计算规则：
TPS(每秒业务数量) = [(本次commit_times + 本次rollback_times) - (上次commit_times + 上次rollback_times)] / 时间间隔

操作步骤

第一步：第一次采样（时间T1）

SQL> SELECT 
     (SELECT STAT_VAL FROM V$SYSSTAT WHERE NAME = 'commit times') as commit_times,
     (SELECT STAT_VAL FROM V$SYSSTAT WHERE NAME = 'rollback times') as rollback_times
FROM DUAL;

COMMIT_TIMES   ROLLBACK_TIMES
------------   --------------
5000           200在这里插入代码片

记录：Commit_1 = 5000, Rollback_1 = 200, 时间 T1

第二步：第二次采样（时间T2）

SQL> SELECT 
     (SELECT STAT_VAL FROM V$SYSSTAT WHERE NAME = 'commit times') as commit_times,
     (SELECT STAT_VAL FROM V$SYSSTAT WHERE NAME = 'rollback times') as rollback_times
FROM DUAL;

COMMIT_TIMES   ROLLBACK_TIMES
------------   --------------
5125           205

记录：Commit_2 = 5125, Rollback_2 = 205, 时间 T2

第三步：计算TPS

总事务增量 = (5125 + 205) - (5000 + 200) = 5330 - 5200 = 130

时间间隔 = 5 秒

TPS = 130 / 5 = 26

这意味着在过去5秒内，平均每秒处理了26个业务事务。

一键脚本

# 第一次采样
echo "SELECT (SELECT STAT_VAL FROM V\$SYSSTAT WHERE NAME = 'commit times') as commit_times, (SELECT STAT_VAL FROM V\$SYSSTAT WHERE NAME = 'rollback times') as rollback_times FROM DUAL;" | disql USER/PASSWORD@HOST:PORT -s > /tmp/dm_txn1.txt
commit1=$(grep -A 1 'COMMIT_TIMES' /tmp/dm_txn1.txt | tail -1 | awk '{print $1}')
rollback1=$(grep -A 1 'COMMIT_TIMES' /tmp/dm_txn1.txt | tail -1 | awk '{print $2}')
total1=$((commit1 + rollback1))

sleep 5

# 第二次采样
echo "SELECT (SELECT STAT_VAL FROM V\$SYSSTAT WHERE NAME = 'commit times') as commit_times, (SELECT STAT_VAL FROM V\$SYSSTAT WHERE NAME = 'rollback times') as rollback_times FROM DUAL;" | disql USER/PASSWORD@HOST:PORT -s > /tmp/dm_txn2.txt
commit2=$(grep -A 1 'COMMIT_TIMES' /tmp/dm_txn2.txt | tail -1 | awk '{print $1}')
rollback2=$(grep -A 1 'COMMIT_TIMES' /tmp/dm_txn2.txt | tail -1 | awk '{print $2}')
total2=$((commit2 + rollback2))

tps=$(( (total2 - total1) / 5 ))
echo "Current DM TPS: $tps"

重要注意事项

事务与业务的关系：这种方法假设每个业务操作都对应一个明确的事务（提交或回滚）。如果业务逻辑中使用的是自动提交模式，那么每个SQL语句都会被计为一个事务。
长事务的影响：如果有长时间运行的事务（比如运行几分钟甚至几小时），这些事务只有在提交或回滚时才会被计数，因此这种方法反映的是完成的业务数量，而不是进行中的业务数量。
监控精度：与QPS监控一样，这得到的是时间窗口内的平均值。采样间隔越短，越能反映瞬时的业务压力变化。
实际业务指标：对于更复杂的业务场景，可能需要通过应用层的日志分析或自定义数据库表的计数器来获取更精确的业务指标（如：每秒订单数、每秒支付数等）。
数据库重启：如果数据库在采样期间重启，计数器会归零，计算结果将无效。

3.线程连接数

Mysql

查看当前的连接数

SHOW STATUS LIKE 'Threads_connected';

查看最大连接数

SHOW VARIABLES LIKE 'max_connections';

查看详细的连接信息

-- 查看所有连接的详细信息
SHOW PROCESSLIST;

-- 查看不同用户和状态的连接数统计
SELECT user, command, state, COUNT(*) 
FROM information_schema.processlist 
GROUP BY user, command, state;

-- 查看不同状态的连接数
SELECT state, COUNT(*) 
FROM information_schema.processlist 
GROUP BY state;

达梦数据库

查看当前连接数：

SELECT COUNT(*) FROM V$SESSIONS WHERE STATE = 'ACTIVE';

查看最大连接数

-- 达梦8版本
SELECT * FROM V$PARAMETER WHERE NAME = 'MAX_SESSIONS';

-- 或者直接查询
SELECT PARA_VALUE FROM V$DM_INI WHERE PARA_NAME = 'MAX_SESSIONS';

查看详细的连接信息：

-- 查看所有会话的详细信息
SELECT SESS_ID, SQL_TEXT, STATE, CREATE_TIME, LAST_RECV_TIME 
FROM V$SESSIONS 
WHERE STATE = 'ACTIVE';

-- 查看不同状态的会话统计
SELECT STATE, COUNT(*) AS CONN_COUNT 
FROM V$SESSIONS 
GROUP BY STATE;

性能测试中线程数的关注点

关注点1：连接数增长趋势

测试场景：模拟1000个并发用户执行登录和查询操作

监控指标：

当前连接数 (Threads_connected / V$SESSIONS计数)
连接数随时间的变化趋势

举例说明

-- 测试前基准
MySQL: Threads_connected = 25
达梦: SELECT COUNT(*) FROM V$SESSIONS = 30

-- 测试中峰值
MySQL: Threads_connected = 280
达梦: SELECT COUNT(*) FROM V$SESSIONS = 295

-- 测试后恢复
MySQL: Threads_connected = 35
达梦: SELECT COUNT(*) FROM V$SESSIONS = 32

影响分析：

如果连接数不能恢复到测试前水平，可能存在连接泄漏
连接数增长与并发用户数是否匹配

关注点2：连接状态分布

监控指标：

Sleep / Idle 状态的连接数
Execute / Active 状态的连接数
Locked 状态的连接数

举例说明：

-- MySQL 连接状态分析
SELECT COMMAND, STATE, COUNT(*) 
FROM information_schema.PROCESSLIST 
GROUP BY COMMAND, STATE;

-- 结果可能显示：
-- Sleep: 150个（空闲连接）
-- Query: 80个（正在执行查询）
-- Locked: 5个（发生锁等待）

-- 达梦连接状态分析
SELECT STATE, COUNT(*) 
FROM V$SESSIONS 
GROUP BY STATE;

-- 结果可能显示：
-- ACTIVE: 85个
-- IDLE: 155个
-- LOCKED: 3个、

影响分析：

大量Sleep/Idle连接：应用层可能没有正确关闭连接，浪费资源
Locked连接：存在锁竞争，影响系统并发性能

关注点3：最大连接数限制

测试场景：进行压力测试，逐步增加并发用户直到达到系统极限。

监控指标：

当前连接数 vs 最大连接数
连接拒绝错误数

举例说明

-- 查看最大连接数设置
MySQL: SHOW VARIABLES LIKE 'max_connections'; -- 返回 500
达梦: SELECT PARA_VALUE FROM V$DM_INI WHERE PARA_NAME = 'MAX_SESSIONS'; -- 返回 1000

-- 压力测试中观察
-- 当连接数接近最大值时，开始出现错误：
-- MySQL: "Too many connections"
-- 达梦: "超出最大会话数"

影响分析：

连接数达到上限会导致新的连接请求被拒绝
需要根据业务峰值合理设置最大连接数

关注点4：连接建立和销毁频率

监控指标：

每秒新建连接数
连接存活时间
连接池效果评估

举例说明：

-- MySQL 查看连接相关统计
SHOW GLOBAL STATUS LIKE 'Connections'; -- 总连接数
SHOW GLOBAL STATUS LIKE 'Threads_created'; -- 创建的线程数
SHOW GLOBAL STATUS LIKE 'Aborted_connects'; -- 异常连接数

-- 达梦查看连接统计
SELECT NAME, STAT_VAL FROM V$SYSSTAT 
WHERE NAME IN ('login count', 'logouts count');

影响分析：

高频的连接建立/销毁会消耗大量CPU资源
连接池可以显著改善性能，减少连接建立开销

连接数对性能的具体影响

资源消耗影响
一.内存消耗：

-- 估算每个连接的内存占用
-- MySQL: 每个连接约 256KB - 2MB (取决于配置和使用的内存)
-- 达梦: 每个连接约 200KB - 1.5MB

-- 总连接内存 = 连接数 × 每个连接的内存
-- 示例：500个连接 × 1MB = 500MB 内存专用于连接管理

二.CPU消耗：

上下文切换开销：连接数越多，CPU在连接间切换的开销越大
锁竞争：高并发下系统表锁竞争加剧

实际性能测试案例

测试场景：电商系统大促性能测试

连接数监控观察：

时间点         MySQL连接数   达梦连接数   系统TPS   平均响应时间
-----------   -----------   ---------   ------   ----------
10:00:00      45            50          120      180ms
10:05:00      280           295         850      350ms  (压力上升)
10:10:00      480           520         620      650ms  (接近连接限制)
10:15:00      490           530         480      980ms  (性能下降)

问题分析：

连接数达到80%上限时，TPS开始下降
响应时间显著增加，出现连接等待
观察到部分连接处于Locked状态

优化建议：

-- MySQL 优化
SET GLOBAL max_connections = 1000;  -- 增加最大连接数
SET GLOBAL thread_cache_size = 100; -- 增加线程缓存

-- 达梦优化
SP_SET_PARA_VALUE(2, 'MAX_SESSIONS', 2000);  -- 修改最大会话数

连接池配置建议

应用层连接池配置：

# 示例：合理的连接池配置
maxActive: 100           # 最大连接数
maxIdle: 20             # 最大空闲连接
minIdle: 10             # 最小空闲连接
maxWait: 30000          # 获取连接超时时间(ms)
testOnBorrow: true      # 借出连接时验证

连接数监控脚本

#!/bin/bash
# 监控数据库连接数的脚本

# MySQL
mysql_conn=$(mysql -u root -p密码 -e "SHOW STATUS LIKE 'Threads_connected'" | grep Threads_connected | awk '{print $2}')
mysql_max_conn=$(mysql -u root -p密码 -e "SHOW VARIABLES LIKE 'max_connections'" | grep max_connections | awk '{print $2}')
mysql_conn_usage=$(echo "scale=2; $mysql_conn * 100 / $mysql_max_conn" | bc)

echo "MySQL连接数: $mysql_conn/$mysql_max_conn ($mysql_conn_usage%)"

# 达梦
dm_conn=$(echo "SELECT COUNT(*) FROM V\$SESSIONS;" | disql user/password@host:port -s | grep -v '^$' | tail -1)
dm_max_conn=$(echo "SELECT PARA_VALUE FROM V\$DM_INI WHERE PARA_NAME='MAX_SESSIONS';" | disql user/password@host:port -s | grep -v '^$' | tail -1)
dm_conn_usage=$(echo "scale=2; $dm_conn * 100 / $dm_max_conn" | bc)

echo "达梦连接数: $dm_conn/$dm_max_conn ($dm_conn_usage%)"

总结

在数据库性能测试中，连接数监控是识别系统瓶颈的关键环节。需要特别关注：

连接数趋势：是否与业务并发匹配，是否存在连接泄漏
连接状态：活跃连接与空闲连接的比例，是否存在阻塞
资源消耗：连接数对内存、CPU的影响
极限测试：系统在连接数接近上限时的表现
连接池效果：连接复用是否充分，连接建立频率是否合理

4.缓存命中率

缓存命中率的查看方式

Mysql

MySQL 主要使用 InnoDB 存储引擎，其缓存体系的核心是 InnoDB Buffer Pool。

查看缓存命中率

可以通过以下 SQL 语句查询 InnoDB Buffer Pool 的命中率和相关状态

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

关键指标：

Innodb_buffer_pool_read_requests: 逻辑读取请求数（从缓冲池读取）。
Innodb_buffer_pool_reads: 物理读取请求数（从磁盘读取）。

缓存命中率计算公式：

Hit Rate = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%

一个更详细的查询示例，可以直接得到命中率：

SELECT
    (1 - (variable_value / (SELECT variable_value
                           FROM information_schema.global_status
                           WHERE variable_name = 'Innodb_buffer_pool_read_requests')))
    * 100 AS 'InnoDB Buffer Pool Hit Rate (%)'
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_reads';

结果解读：

大于99%：优秀，表明绝大多数数据都来自内存。
95% - 99%：良好。
小于 90%：可能需要考虑增大 innodb_buffer_pool_size 参数的值。

达梦数据库

达梦数据库的缓存体系称为缓冲区，主要包括数据缓冲区、SQL缓冲区、字典缓冲区等。

查看缓存命中率

可以使用达梦的性能视图 V $B U FFERPOO L 和 V$ SYSSTAT 来查询。

数据缓冲区命中率查询：

SELECT
    name AS "缓冲池名称",
    page_size AS "页大小",
    (1 - (disk_reads / (reads + 0.000001))) * 100 AS "缓冲区命中率(%)"
FROM V$BUFFERPOOL;

系统级缓存命中率查询（更宏观的视角）：

SELECT
    NAME,
    VALUE
FROM V$SYSSTAT
WHERE NAME IN ('BUFFER HIT', 'BUFFER GET');

达梦的 BUFFER HIT 统计本身就是一个命中率的累计值，可以直接观察其趋势。在性能测试中，更常用的是通过两次采样计算差值来得到测试期间的命中率：

-- 第一次采样
CREATE TABLE SNAP1 AS SELECT * FROM V$SYSSTAT WHERE NAME IN ('BUFFER HIT', 'BUFFER GET');

-- 运行测试负载...

-- 第二次采样
CREATE TABLE SNAP2 AS SELECT * FROM V$SYSSTAT WHERE NAME IN ('BUFFER HIT', 'BUFFER GET');

-- 计算测试期间的命中率
SELECT
    ( (b.VALUE - a.VALUE) / (c.VALUE - d.VALUE) ) * 100 AS "期间命中率(%)"
FROM
    (SELECT VALUE FROM SNAP2 WHERE NAME = 'BUFFER HIT') b,
    (SELECT VALUE FROM SNAP1 WHERE NAME = 'BUFFER HIT') a,
    (SELECT VALUE FROM SNAP2 WHERE NAME = 'BUFFER GET') c,
    (SELECT VALUE FROM SNAP1 WHERE NAME = 'BUFFER GET') d;

DROP TABLE SNAP1;
DROP TABLE SNAP2;

结果解读：

与 MySQL 类似，通常要求稳定在 98% 甚至 99% 以上为佳。如果过低，需要调大达梦的 BUFFER、BUFFER_POOLS 等初始化参数。

5.数据库中的锁

锁状态查看方式

Mysql

MySQL (以 InnoDB 为例)

InnoDB 的锁信息主要存储在信息模式和性能模式中

方式一：查看当前事务和锁信息（最常用）

-- 查看当前所有正在运行的事务及其锁状态
SELECT * FROM information_schema.INNODB_TRX;

-- 查看当前出现的锁（MySQL 5.7及以上，信息更直观）
SELECT * FROM information_schema.INNODB_LOCKS; -- MySQL 5.7
SELECT * FROM performance_schema.data_locks;   -- MySQL 8.0+

-- 查看锁的等待关系（谁阻塞了谁）
SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- MySQL 5.7
SELECT * FROM performance_schema.data_lock_waits;   -- MySQL 8.0+

一个更实用的综合查询，用于直接找出阻塞关系：

SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

方式二：使用 SHOW ENGINE INNODB STATUS 命令（经典方法）

这是一个非常强大的命令，可以获取 InnoDB 状态的完整快照，其中包含一个专门的 LATEST DETECTED DEADLOCK 部分，用于显示最近一次死锁的详细信息。

SHOW ENGINE INNODB STATUS\G

在输出结果中，重点关注 TRANSACTIONS 和 LATEST DETECTED DEADLOCK 这两个部分

达梦数据库

达梦数据库提供了丰富的动态性能视图来监控锁。

方式一：查看当前会话和锁信息

-- 查看当前会话信息
SELECT sess_id, sql_text, state, lock_wait FROM v$sessions WHERE state = 'LOCK WAIT';

-- 查看当前系统中的所有锁
SELECT * FROM v$lock;

-- 查看锁的详细信息，包括持有锁和等待锁的会话
SELECT * FROM v$locked_object;

-- 查看被阻塞的会话（正在等待锁的会话）
SELECT * FROM v$sessions WHERE lock_wait IS NOT NULL;

一个实用的综合查询，用于分析锁等待关系

SELECT
    lh.sess_id AS holding_sess_id,
    lh.ltype AS holding_lock_type,
    lh.table_name AS holding_table,
    lw.sess_id AS waiting_sess_id,
    lw.ltype AS waiting_lock_type,
    lw.table_name AS waiting_table,
    ws.sql_text AS waiting_sql
FROM
    v$lock lw,
    v$lock lh,
    v$sessions ws
WHERE
    lw.blocked = 1          -- 被阻塞的锁
    AND lh.sess_id = lw.trx_id -- 阻塞者的事务/会话ID
    AND lw.sess_id = ws.sess_id; -- 关联等待会话的SQL信息

查看死锁历史
达梦会将死锁信息记录在日志文件中，同时也可以通过视图查询

-- 查看死锁记录（需要开启相关监控）
SELECT * FROM v$deadlock_history;

测试过程中的有关数据库锁注意事项及举例说明

在性能测试、压力测试或高并发业务测试中，数据库锁是导致性能瓶颈、超时和失败的最常见原因之一

测试前：设计与预防

一.事务设计：

原则：事务要“短小精悍”，尽快提交或回滚。避免在事务中包含不必要的业务逻辑、远程调用或人工交互。

反例：在一个事务中，先查询用户信息，然后调用风控系统，再更新订单，最后记录日志。这个长事务会长时间持有行锁，阻塞其他会话。

正例：将事务拆解。先查询和调用风控（非事务型操作），然后在最后一个数据库操作中开启短事务进行更新和记录。

二.SQL 编写：

原则：合理使用索引，避免全表扫描。没有索引的更新操作会升级为表锁。

反例：UPDATE orders SET status = ‘expired’ WHERE user_name = ‘张三’，如果 user_name 字段没有索引，InnoDB 会对整个 orders 表加锁。

正例：为 user_name 字段添加索引，或者使用有索引的字段（如主键 id）进行更新。

三.访问模式：

原则：以一致的顺序访问多个资源，预防死锁。

反例：会话A先锁表T1，再锁表T2；会话B先锁表T2，再锁表T1。这种交叉访问极易引发死锁。

正例：所有业务逻辑都约定为先操作T1，再操作T2。

测试中：监控与分析

一.实时监控锁等待：

操作：在测试脚本运行期间，定期执行上述“锁状态查看”的综合查询。

关注点：是否有持续的锁等待（lock_wait 不为空）？waiting_time 是否很长？一个长时间的锁等待会像高速公路上的事故，导致后方大量“车辆”（请求）拥堵。

二.识别并解决死锁：

现象：测试日志中间歇性出现“Deadlock found when trying to get lock”或“死锁”错误，但系统并未完全挂死。

分析：立即检查 SHOW ENGINE INNODB STATUS（MySQL）或数据库日志（达梦），分析死锁图谱，找到涉及的两个SQL和资源。

解决：根据死锁信息调整业务逻辑（如统一访问顺序）或SQL（如减少锁的粒度）。

三.关注锁的粒度：

现象：一个不复杂的更新操作，却导致了大量会话被阻塞。

分析：检查该SQL的执行计划，确认是否由于缺失索引导致了锁升级（如行锁升级为表锁）。

测试后：复盘与优化

日志分析：汇总测试期间所有的数据库错误日志，重点分析死锁和锁超时的记录。

慢查询分析：结合锁等待信息分析慢查询日志，很多SQL执行慢不是因为本身计算慢，而是因为等待锁释放。

6.数据库中慢查询

Mysql慢查询

开启方式

主要有两种方式：修改配置文件（永久生效）和动态设置（临时生效）。

方式一：修改配置文件 my.cnf (或 my.ini)

[mysqld]
# 开启慢查询日志
slow_query_log = 1

# 指定慢查询日志文件的路径
slow_query_log_file = /var/lib/mysql/mysql-slow.log

# 定义慢查询的阈值（单位：秒）。执行时间超过此值的SQL将被记录
long_query_time = 2

# (可选)记录未使用索引的查询
log_queries_not_using_indexes = 1

# (可选)记录管理语句，如ALTER TABLE, ANALYZE TABLE等
log_slow_admin_statements = 1

# (可选)记录执行较慢的从库复制线程的查询
log_slow_slave_statements = 1

修改后需重启 MySQL 服务。

方式二：动态设置（无需重启）

-- 全局设置，对之后的新会话生效
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/lib/mysql/mysql-slow.log';

-- 注意：long_query_time 变量在当前会话中可能还是旧值，需要重新连接或设置会话级别
SET SESSION long_query_time = 2;

查看命令

可以直接查看日志文件，也可以查询相关状态。

-- 查看慢查询相关配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 查看慢查询日志记录的数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';

分析工具

方式一：mysqldumpslow (MySQL 官方自带)

MySQL 自带的 Perl 脚本，用于汇总慢查询日志，生成一个可读的报告。

基本用法：

# 列出使用时间最长的前10条查询
mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log

# 列出锁定时间最长的前10条查询
mysqldumpslow -s l -t 10 /var/lib/mysql/mysql-slow.log

# 列出执行次数最多的前10条查询
mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log

# 更详细的输出，并尝试解析IP和用户名
mysqldumpslow -a -g "SELECT" /var/lib/mysql/mysql-slow.log

方式二：pt-query-digest (Percona Toolkit 中的神器)

这是最强大、最常用的慢查询分析工具，属于 Percona Toolkit 的一部分。它提供了极其详细的分析报告。
基本用法

# 直接分析慢查询日志
pt-query-digest /var/lib/mysql/mysql-slow.log

# 分析最近12小时的慢查询（从日志中过滤）
pt-query-digest --since '12h' /var/lib/mysql/mysql-slow.log

# 将分析报告保存到文件
pt-query-digest /var/lib/mysql/mysql-slow.log > slow_report.txt

# 直接分析来自当前MySQL实例的慢查询（需要权限）
pt-query-digest --processlist h=localhost,u=root,p=password --interval 0.01

达梦数据库慢查询

开启方式

达梦通过修改参数文件和动态设置来开启。

方式一：修改参数文件 dm.ini

# 开启SQL日志追踪
SVR_LOG = 1

# 设置SQL日志文件路径（通常保持默认即可）
SVR_LOG_FILE_PATH = /dm8/data/DAMENG/slog

# 定义慢查询阈值（单位：毫秒）。执行时间超过此值的SQL将被记录
SQL_TRACE_MASK = 2
SLOW_SQL_THRESHOLD = 2000  # 例如2000毫秒，即2秒

# 设置SQL日志文件大小和个数，避免磁盘爆满
SVR_LOG_FILE_NUM = 10
SVR_LOG_FILE_SIZE = 100   # 单位：MB

修改 dm.ini 后，需要重启达梦数据库服务。

方式二：动态设置（部分参数）

-- 开启SQL日志（立即生效，但重启后失效，需修改ini文件永久化）
SP_SET_PARA_VALUE(1, 'SVR_LOG', 1);

-- 设置慢SQL阈值（单位：毫秒）
SP_SET_PARA_VALUE(2, 'SLOW_SQL_THRESHOLD', 2000);

查看命令

达梦的慢查询日志默认以 .log 文件形式存放在 SVR_LOG_FILE_PATH 目录下。同时，也提供了动态性能视图。

-- 查看慢查询相关的动态性能视图
SELECT * FROM V$SQL_HISTORY WHERE EXEC_TIME > 2000; -- 查询执行时间超过2秒的历史SQL

SELECT * FROM V$LONG_EXEC_SQLS; -- 查看长时间执行的SQL

-- 直接查看SQL日志文件（在数据库服务器上）
-- 文件路径通常在 /dm8/data/DAMENG/slog/*.log

分析工具

方式一：达梦性能监视工具 (DEM)

达梦企业管理器是一个图形化工具，内置了丰富的性能监控和慢SQL分析功能。

路径：浏览器访问 http://服务器IP:端口 (默认端口号是 8080)。

使用：在 DEM 的“监控”或“诊断”模块中，可以直观地查看慢SQL排行榜、执行计划、等待事件等。

方式二：命令行分析 (使用 dmLogParse)

达梦数据库提供了一个日志解析工具 dmLogParse，可以解析包括慢查询在内的日志文件

基本用法：

# 进入达梦安装目录的bin下
cd /dm8/bin

# 解析SQL日志，并输出到文件
./dmLogParse -f SLOG -I /dm8/data/DAMENG/slog/DAMENG01.log -O /tmp/slow_analysis.txt

# 然后查看 /tmp/slow_analysis.txt 文件

分析工具的使用场景举例说明

场景 1：发现全局性性能瓶颈

动作：压力测试运行一小时后，使用 pt-query-digest（MySQL）或查看 DEM 的慢SQL排行榜（达梦）。

发现：报告头部显示，一个 UPDATE product_stock SET stock = stock - ? WHERE product_id = ? 的SQL占据了总响应时间的 60%，且平均执行时间很长。

分析：这立刻告诉你，库存扣减是系统的核心瓶颈。你需要重点关注这个SQL：它的锁竞争是否激烈？product_id 是否有索引？

场景 2：定位特定慢查询的根因

动作：在 pt-query-digest 的详细报告中，你看到一条 SELECT * FROM orders WHERE user_name = ? 的SQL很慢。

下一步：复制这条完整的SQL，到数据库客户端中，在其前面加上 EXPLAIN（MySQL）或 EXPLAIN FOR（达梦）。

发现：执行计划显示为 ALL（全表扫描）。

根因：user_name 字段上没有索引。

解决方案：为 user_name 字段添加索引。

场景 3：优化“最频繁”的查询

动作：使用 mysqldumpslow -s c 或达梦的 GROUP BY SQL_TEXT 查询，找到执行次数最多的慢查询。

发现：一个简单的 SELECT id, name FROM products WHERE category_id = ? 查询被调用了数百万次，且平均响应时间不理想。

分析：即使它单次执行只慢 50ms，但乘以百万次，总耗时也非常可观。优化它的收益会很大。

解决方案：确保 category_id 有索引；或者考虑引入缓存（如 Redis），将热门分类的商品缓存起来，根本不给数据库压力。

场景 4：对比不同时间段的性能

动作：在代码发布前后，分别收集一段时间的慢查询日志。

使用工具：用 pt-query-digest 分别生成两个报告。

分析：对比报告，查看是否有新的慢查询出现，或者原有慢查询的执行时间/次数是否有显著变化。这可以帮你快速定位因代码变更引入的性能回归问题。

SQL 分析工具EXPLAIN的使用

使用方式

Mysql

MySQL 的 EXPLAIN 使用非常简单，直接在 SQL 语句前加上 EXPLAIN 关键字即可。

基本语法：

EXPLAIN SELECT * FROM your_table WHERE your_condition;

扩展语法（获取更详细的信息）：

-- MySQL 8.0.18+ 支持，可以实际执行查询并显示更详细的执行计划信息
EXPLAIN ANALYZE SELECT * FROM your_table WHERE your_condition;

EXPLAIN ANALYZE 会实际执行该查询，并返回详细的执行时间信息和实际的执行计划，对于分析性能瓶颈非常有用。

达梦数据库的 EXPLAIN

达梦数据库的 EXPLAIN 有两种主要使用方式。

方式一：EXPLAIN FOR（针对当前会话）

-- 先设置执行计划开关
SET EXPLAIN ON;

-- 然后执行你的SQL语句
SELECT * FROM your_table WHERE your_condition;

-- 执行计划会输出在屏幕上，查询结束后记得关闭
SET EXPLAIN OFF;

方式二：通过系统包（不实际执行SQL）

-- 调用系统包，直接生成执行计划，不会真正执行SQL
CALL DBMS_SQLTUNE.EXPLAIN_SQL('SELECT * FROM your_table WHERE your_condition');
-- 然后查询计划表
SELECT * FROM EXPLAIN_TABLE;

返回结果分析

两者返回的信息虽然列名和细节有所不同，但核心思想是一致的：展示一个查询需要经过哪些步骤（操作），以及这些操作的代价。

MySQL EXPLAIN 核心列分析

我们以一个典型的 EXPLAIN 输出为例：
在这里插入图片描述
关键列解读：

id: 执行顺序。id 越大越先执行，id 相同则从上到下执行。

select_type: 查询类型。

SIMPLE: 简单查询（无子查询或 UNION）。

PRIMARY: 最外层的查询。

SUBQUERY: 子查询。

table: 访问的表。

type (非常重要): 访问类型，性能从优到劣大致为：

system > const > eq_ref > ref > range > index > ALL

ref/eq_ref: 使用索引查找，很好。

range: 使用索引范围扫描，良好。

index: 全索引扫描（比全表扫描好一点）。

ALL: 全表扫描，需要优化。

possible_keys: 可能用到的索引。

key: 实际用到的索引。如果为 NULL，说明未使用索引。

key_len: 使用的索引长度。

rows: 估算的需要扫描的行数。这个值越小越好。

Extra: 额外信息，非常重要。

Using where: 在存储引擎检索行后，在服务器层进行了过滤。
Using index: 覆盖索引，所有数据均可从索引中获取，性能极佳。
Using temporary: 使用了临时表，常见于排序和分组，需要优化。
Using filesort: 使用了文件排序，而不是索引排序，需要优化。

达梦 EXPLAIN 核心列分析

达梦的执行计划输出是一个步骤列表，每一步代表一个操作。

一个典型的输出片段：
在这里插入图片描述

关键操作符解读：

NSET: 结果集收集。通常是执行计划的根节点。

PRJT: 投影操作，用于选择要输出的列。

SLCT: 选择操作，对应 WHERE 子句的过滤。

CSCN: 全表扫描（Clustered Index Scan）。类似于 MySQL 的 ALL，需要优化。

SSEK / SSCN: 二级索引查找/扫描（Secondary Index Seek/Scan）。通过非聚集索引访问数据，性能较好。

BLKUP: 回表操作。通过二级索引找到主键后，需要回到主键索引（聚集索引）中查找其他列的数据。这是一个常见的性能瓶颈点。

HASH JOIN / NEST LOOP INDEX JOIN: 表连接方式。

SORT: 排序操作。

操作符后的数组 [1, 1000, 4] 含义：

第一个值: 操作代价。
第二个值: 处理的行数。
第三个值: 结果集的行数。

乐奇 Rokid 开放社区

更多推荐

极刻觅镜 | AI眼镜周报｜Rokid四款硬件发布与Android XR工具转稳定版

乐奇 Rokid 开放社区

极刻觅镜 | AI眼镜日报｜北京发布七项消费举措、韩国高考排查AI眼镜

乐奇 Rokid 开放社区

极刻觅镜 | AI眼镜日报｜京东智能眼镜西安站活动与千问AI眼镜OTA升级

乐奇 Rokid 开放社区

所有评论(0)

查看更多评论

思想是一切事物的源头

@weixin_37689012

已为社区贡献2条内容