慢查询分析锁与版本

在MySQL数据库运维中，慢查询分析、锁问题诊断和版本管理是保障系统稳定性和性能的关键技能。本文将深入探讨这三个方面的核心知识和实践方法。

1. 慢查询分析基础

1.1 慢查询的概念

慢查询是指执行时间超过指定阈值的SQL语句。MySQL提供了慢查询日志功能来记录这些查询：

-- 慢查询相关配置
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 超过2秒的查询记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

1.2 慢查询日志格式

-- 慢查询日志示例
# Time: 2024-01-01T10:30:45.123456Z
# User@Host: user[user] @ host [ip]
# Query_time: 5.123456  Lock_time: 0.000123 Rows_sent: 1000  Rows_examined: 500000
SET timestamp=1704065445;
SELECT * FROM large_table WHERE status = 'active' ORDER BY created_date DESC;

1.3 慢查询诊断工具

-- 使用pt-query-digest分析慢查询日志
-- 安装percona-toolkit后使用：
pt-query-digest /var/log/mysql/slow.log

-- 分析结果示例：
-- # Query 1: 100% 5.12s total, 5.12s mean, 1000 rows
-- # Query 2: 50% 2.56s total, 2.56s mean, 500 rows

2. 锁问题诊断

2.1 锁等待分析

-- 查看当前锁等待情况
SHOW ENGINE INNODB STATUS\G

-- 分析结果示例：
-- TRANSACTIONS
-- -----------
-- Trx id counter 1000000
-- Purge done for trx's n:o < 1000000
-- History list length 1000000
-- -----------
-- TRX HAS BEEN WAITING 5.12 SEC FOR THIS LOCK TO BE GRANTED:
-- -----------
-- WAITING TRANSACTIONS:
-- # Query 1: 100% 5.12s total, 5.12s mean, 1000 rows
-- # Query 2: 50% 2.56s total, 2.56s mean, 500 rows

2.2 死锁分析

-- 查看当前死锁情况
SHOW ENGINE INNODB STATUS\G

-- 分析结果示例：
-- TRANSACTIONS
-- -----------
-- Trx id counter 1000000
-- Purge done for trx's n:o < 1000000
-- History list length 1000000
-- -----------
-- DEADLOCK FOUND!
-- 1 deadlock detected
-- -----------
-- # Query 1: 100% 5.12s total, 5.12s mean, 1000 rows
-- # Query 2: 50% 2.56s total, 2.56s mean, 500 rows

3. 版本管理

3.1 数据库版本查询

-- 查询当前数据库版本
SELECT VERSION();

-- 示例结果：
-- 8.0.33-0ubuntu0.22.04.1

3.2 插件版本查询

-- 查询当前安装的插件版本
SHOW PLUGINS;

-- 示例结果：
-- Plugin | Version | Status | Type | Path | Source | License
-- innodb | 8.0.33 | ACTIVE | STORAGE ENGINE | NULL | NULL | NULL

4. 总结

-- # Query 1: 100% 5.12s total, 5.12s mean,