MariaDB 慢查询

慢查询是 MariaDB 数据库性能问题的主要原因之一，它会导致数据库响应延迟、CPU 使用率过高、连接数增加等问题，严重影响业务系统的稳定性和用户体验。本文将详细介绍 MariaDB 慢查询的诊断、分析和优化方法，帮助 DBA 快速定位和解决慢查询问题。

慢查询概述

慢查询定义

慢查询是指执行时间超过指定阈值的 SQL 查询。在 MariaDB 中，默认的慢查询阈值是 10 秒，可以通过 long_query_time 参数进行调整。

慢查询的影响

响应延迟：慢查询会导致应用程序响应时间增加
资源消耗：占用大量 CPU、内存和 I/O 资源
连接阻塞：长时间占用数据库连接，导致其他查询等待
锁竞争：增加锁等待和死锁的风险
复制延迟：主库上的慢查询会导致从库复制延迟

版本差异

不同 MariaDB 版本在慢查询处理方面存在一些差异：

MariaDB 5.5+：支持基本的慢查询日志功能
MariaDB 10.0+：增加了慢查询日志的 JSON 格式和更多的监控指标
MariaDB 10.1+：引入了 Performance Schema，提供更详细的查询性能数据
MariaDB 10.2+：支持慢查询日志的压缩和自动轮转
MariaDB 10.5+：优化了 EXPLAIN 命令，增加了更多的执行计划信息
MariaDB 10.6+：引入了 Query Profiler，提供更详细的查询执行步骤分析

慢查询诊断

开启慢查询日志

临时开启

sql

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 2;

-- 设置慢查询日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mariadb/mariadb-slow.log';

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 记录管理语句
SET GLOBAL log_slow_admin_statements = ON;

-- 记录慢查询的最小行数
SET GLOBAL min_examined_row_limit = 100;

永久开启

在配置文件中添加以下配置：

ini

[mysqld]
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /var/log/mariadb/mariadb-slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
min_examined_row_limit = 100

分析慢查询日志

使用 mysqldumpslow 工具

mysqldumpslow 是 MariaDB 自带的慢查询日志分析工具，可以按不同维度汇总慢查询：

bash

# 按查询次数排序
mysqldumpslow -s c -t 10 /var/log/mariadb/mariadb-slow.log

# 按执行时间排序
mysqldumpslow -s t -t 10 /var/log/mariadb/mariadb-slow.log

# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/log/mariadb/mariadb-slow.log

# 按扫描行数排序
mysqldumpslow -s r -t 10 /var/log/mariadb/mariadb-slow.log

使用 pt-query-digest 工具

pt-query-digest 是 Percona Toolkit 中的工具，提供更详细的慢查询分析：

bash

pt-query-digest /var/log/mariadb/mariadb-slow.log > slow_query_analysis.txt

使用 Performance Schema

从 MariaDB 10.1 开始，可以使用 Performance Schema 分析慢查询：

sql

-- 启用 Performance Schema
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%statement%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%statement%';

-- 查看慢查询
SELECT * FROM performance_schema.events_statements_summary_by_digest 
WHERE SUM_TIMER_WAIT > 2000000000000 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

使用 EXPLAIN 分析查询

EXPLAIN 命令是分析查询执行计划的重要工具：

sql

EXPLAIN SELECT * FROM users WHERE username = 'test' AND status = 1;

-- 查看详细的执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE username = 'test' AND status = 1;

-- 查看查询的实际执行情况
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'test' AND status = 1;

常见慢查询场景

缺少索引

症状：

EXPLAIN 显示 type = ALL（全表扫描）
rows 列显示扫描了大量行
慢查询日志中 Rows_examined 远大于 Rows_sent

处理方法：

为查询条件添加合适的索引
考虑联合索引，覆盖多个查询条件

示例：

sql

-- 原查询（全表扫描）
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2023-01-01';

-- 添加索引后
CREATE INDEX idx_user_created ON orders(user_id, created_at);

索引失效

症状：

查询条件包含索引列，但 EXPLAIN 显示未使用索引
慢查询日志中 key 列显示 NULL

常见原因：

使用 != 或 <> 操作符
使用 OR 连接条件，其中一个条件没有索引
使用 LIKE 并以通配符开头（如 LIKE '%test'）
对索引列进行函数操作（如 DATE(created_at) = '2023-01-01'）
索引列类型不匹配（如字符串列与数字比较）

处理方法：

避免对索引列进行函数操作
使用 UNION 代替 OR
重新设计查询，使用前缀索引或全文索引
确保索引列类型匹配

示例：

sql

-- 索引失效（对索引列进行函数操作）
SELECT * FROM orders WHERE DATE(created_at) = '2023-01-01';

-- 优化后（避免函数操作）
SELECT * FROM orders WHERE created_at BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

复杂查询

症状：

查询包含多个 JOIN 操作
使用了子查询或临时表
执行计划显示 Using temporary 或 Using filesort

处理方法：

拆分复杂查询为多个简单查询
优化 JOIN 顺序
考虑使用临时表或视图
增加 join_buffer_size 等配置参数

示例：

sql

-- 复杂查询
SELECT u.username, o.order_id, o.amount, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.status = 1 AND o.created_at > '2023-01-01';

-- 拆分后
CREATE TEMPORARY TABLE temp_orders AS
SELECT o.order_id, o.user_id, o.amount
FROM orders o
WHERE o.created_at > '2023-01-01';

SELECT u.username, t.order_id, t.amount, p.product_name
FROM users u
JOIN temp_orders t ON u.id = t.user_id
JOIN order_items oi ON t.order_id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.status = 1;

大量数据扫描

症状：

查询返回大量数据
Rows_examined 或 Rows_sent 数值很大
执行时间随数据量增长而线性增加

处理方法：

限制返回的行数（使用 LIMIT）
只查询必要的列（避免 SELECT *）
考虑分页查询
增加 read_buffer_size 和 read_rnd_buffer_size

示例：

sql

-- 优化前（返回所有列和所有行）
SELECT * FROM users;

-- 优化后（只查询必要列，限制行数）
SELECT id, username, email FROM users WHERE status = 1 LIMIT 100;

锁等待

症状：

查询执行时间很长，但 CPU 使用率不高
SHOW PROCESSLIST 显示查询状态为 Waiting for table lock 或 Waiting for row lock
慢查询日志中 Lock_time 数值较大

处理方法：

优化事务，减少事务持有锁的时间
避免长事务
使用更细粒度的锁（如行锁代替表锁）
考虑使用乐观锁

慢查询优化

索引优化

索引设计原则

选择合适的列：为查询条件、排序和分组列添加索引
联合索引顺序：将选择性高的列放在前面
覆盖索引：包含查询所需的所有列，避免回表查询
前缀索引：对于长字符串，使用合适的前缀长度
避免过多索引：每个索引都会增加写操作的开销

索引维护

sql

-- 查看索引使用情况
SHOW INDEX FROM users;

-- 分析表，更新索引统计信息
ANALYZE TABLE users;

-- 重建索引
ALTER TABLE users ENGINE=InnoDB;

-- 删除无用索引
DROP INDEX idx_old ON users;

查询改写

优化子查询

sql

-- 优化前
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 优化后（使用 JOIN）
SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 1000;

优化 JOIN 查询

sql

-- 优化前（笛卡尔积）
SELECT * FROM users, orders WHERE users.id = orders.user_id AND users.status = 1;

-- 优化后（显式 JOIN）
SELECT * FROM users JOIN orders ON users.id = orders.user_id WHERE users.status = 1;

优化 GROUP BY 和 ORDER BY

sql

-- 优化前（需要排序）
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id ORDER BY COUNT(*) DESC;

-- 优化后（使用索引覆盖）
CREATE INDEX idx_user_created ON orders(user_id, created_at);
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id ORDER BY COUNT(*) DESC;

表结构优化

规范化与反规范化

规范化：减少数据冗余，提高数据一致性
反规范化：增加冗余数据，提高查询性能

示例：

sql

-- 规范化设计（订单表和订单明细表分离）
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10,2),
    created_at DATETIME
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2)
);

-- 反规范化设计（订单表包含汇总信息）
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    item_count INT,
    total_amount DECIMAL(10,2),
    created_at DATETIME
);

选择合适的数据类型

使用更小的数据类型（如 TINYINT 代替 INT）
避免使用 TEXT 和 BLOB 存储频繁查询的数据
使用 DATETIME 或 TIMESTAMP 存储时间，避免使用字符串

配置优化

查询优化器配置

ini

[mysqld]
# 启用查询缓存（注意：MariaDB 10.1.7+ 默认禁用）
query_cache_type = 0

# 优化器开关
optimizer_switch = "index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on"

# 连接缓存
join_buffer_size = 2M

# 排序缓存
sort_buffer_size = 2M
max_length_for_sort_data = 1024

# 临时表
max_heap_table_size = 64M
tmp_table_size = 64M

InnoDB 配置

ini

[mysqld]
# InnoDB 缓冲池大小（建议为物理内存的 50%-70%）
innodb_buffer_pool_size = 4G

# 缓冲池实例数（建议与 CPU 核心数相同）
innodb_buffer_pool_instances = 8

# 日志缓冲区大小
innodb_log_buffer_size = 16M

# 事务日志文件大小
innodb_log_file_size = 1G

# 事务隔离级别
transaction_isolation = READ-COMMITTED

慢查询监控与告警

监控指标

Slow_queries：慢查询总数
Long_query_time：慢查询阈值
Queries：总查询数
Slow_queries_rate：慢查询率（Slow_queries / Queries）
Innodb_row_lock_time_avg：平均行锁等待时间
Innodb_row_lock_waits：行锁等待次数

告警设置

基于慢查询数量：当慢查询数超过一定阈值时触发告警
基于慢查询率：当慢查询率超过 1% 时触发告警
基于单个查询执行时间：当单个查询执行时间超过 30 秒时触发告警
基于锁等待时间：当锁等待时间超过一定阈值时触发告警

自动化处理

使用 pt-kill 工具自动杀死长时间运行的查询
设置定期任务，自动分析慢查询日志并生成报告
集成监控系统，如 Prometheus + Grafana，实时监控慢查询情况

慢查询最佳实践

慢查询日志管理

定期轮转：避免慢查询日志过大，影响性能
压缩存储：对旧的慢查询日志进行压缩，节省磁盘空间
集中管理：将多个实例的慢查询日志集中存储，便于分析
定期清理：删除过期的慢查询日志，保持磁盘空间充足

开发规范

避免复杂查询：将复杂查询拆分为多个简单查询
使用绑定变量：避免 SQL 注入，提高查询缓存命中率
限制结果集大小：使用 LIMIT 限制返回的行数
避免长事务：尽量缩短事务的执行时间
测试查询性能：在开发环境测试查询性能，避免将慢查询引入生产环境

定期分析

每日分析：查看当天的慢查询，及时发现问题
每周总结：汇总每周的慢查询，分析趋势
每月优化：根据慢查询分析结果，进行系统性优化
版本升级前分析：在升级 MariaDB 版本前，分析慢查询，确保升级后性能不会下降

慢查询案例分析

案例一：缺少联合索引

问题描述：

sql

SELECT * FROM orders WHERE user_id = 123 AND created_at > '2023-01-01' ORDER BY id DESC;

诊断过程：

使用 EXPLAIN 分析，发现 type = ref，但只使用了 user_id 索引
Extra 列显示 Using where; Using filesort，说明需要额外排序
慢查询日志显示 Rows_examined = 10000，Rows_sent = 100

处理方法：

sql

-- 添加联合索引，包含查询条件和排序列
CREATE INDEX idx_user_created_id ON orders(user_id, created_at, id);

优化结果：

EXPLAIN 显示 type = range，使用了新创建的联合索引
Extra 列显示 Using index condition，不需要额外排序
查询执行时间从 5 秒减少到 0.1 秒

案例二：索引失效

问题描述：

sql

SELECT * FROM users WHERE DATE(created_at) = '2023-01-01';

诊断过程：

EXPLAIN 显示 type = ALL，全表扫描
表中有 created_at 索引，但未被使用
原因是对索引列进行了函数操作

处理方法：

sql

-- 改写查询，避免对索引列进行函数操作
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

优化结果：

EXPLAIN 显示 type = range，使用了 created_at 索引
查询执行时间从 10 秒减少到 0.2 秒

FAQ

Q1: 慢查询阈值设置为多少合适？

A1: 慢查询阈值应根据业务需求和系统性能来设置，一般建议：

在线交易系统：1-2 秒
数据分析系统：5-10 秒
可以根据不同的查询类型设置不同的阈值

Q2: 慢查询日志会影响性能吗？

A2: 启用慢查询日志会对性能产生一定影响，但影响较小。可以通过以下方式减少影响：

设置合理的慢查询阈值
定期轮转慢查询日志
考虑使用 Performance Schema 代替慢查询日志

Q3: 如何区分真慢查询和假慢查询？

A3: 假慢查询通常是由以下原因引起的：

系统负载过高
锁等待
资源竞争
临时的网络问题

可以通过查看系统负载、锁等待情况和网络状态来区分真慢查询和假慢查询。

Q4: 为什么优化后的查询在某些情况下仍然很慢？

A4: 可能的原因包括：

数据分布不均匀
索引统计信息过时
查询优化器选择了错误的执行计划
系统资源不足

可以通过 ANALYZE TABLE 更新统计信息，或使用 USE INDEX 强制使用特定索引。

Q5: 如何处理大量的慢查询？

A5: 可以采取以下步骤：

对慢查询进行分类，优先处理影响大的查询
建立慢查询优化的优先级队列
制定长期的优化计划
考虑引入自动化工具，如 pt-query-digest
优化开发流程，避免新的慢查询产生

Q6: MariaDB 和 MySQL 的慢查询处理有什么区别？

A6: MariaDB 和 MySQL 的慢查询处理基本相似，但 MariaDB 提供了更多的优化功能：

MariaDB 10.5+ 提供了更详细的 EXPLAIN 输出
MariaDB 10.6+ 引入了 Query Profiler
MariaDB 支持更多的慢查询日志格式
MariaDB 对慢查询的监控指标更丰富

Q7: 如何使用 Performance Schema 代替慢查询日志？

A7: 可以通过以下步骤配置：

sql

-- 启用 Performance Schema
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%statement%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%statement%';

-- 设置慢查询阈值
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';

-- 查看慢查询
SELECT * FROM performance_schema.events_statements_summary_by_digest 
WHERE SUM_TIMER_WAIT > 2000000000000 
ORDER BY SUM_TIMER_WAIT DESC;

Performance Schema 提供了更详细的查询性能数据，但会消耗更多的系统资源，建议根据实际情况选择使用。

MariaDB 慢查询 ​

慢查询概述 ​

慢查询定义 ​

慢查询的影响 ​

版本差异 ​

慢查询诊断 ​

开启慢查询日志 ​

临时开启 ​

永久开启 ​

分析慢查询日志 ​

使用 mysqldumpslow 工具 ​

使用 pt-query-digest 工具 ​

使用 Performance Schema ​

使用 EXPLAIN 分析查询 ​

常见慢查询场景 ​

缺少索引 ​

索引失效 ​

复杂查询 ​

大量数据扫描 ​

锁等待 ​

慢查询优化 ​

索引优化 ​

索引设计原则 ​

索引维护 ​

查询改写 ​

优化子查询 ​

优化 JOIN 查询 ​

优化 GROUP BY 和 ORDER BY ​

表结构优化 ​

规范化与反规范化 ​

选择合适的数据类型 ​

配置优化 ​

查询优化器配置 ​

InnoDB 配置 ​

慢查询监控与告警 ​

监控指标 ​

告警设置 ​

自动化处理 ​

慢查询最佳实践 ​

慢查询日志管理 ​

开发规范 ​

定期分析 ​

慢查询案例分析 ​

案例一：缺少联合索引 ​

案例二：索引失效 ​

FAQ ​

Q1: 慢查询阈值设置为多少合适？ ​

Q2: 慢查询日志会影响性能吗？ ​

Q3: 如何区分真慢查询和假慢查询？ ​

Q4: 为什么优化后的查询在某些情况下仍然很慢？ ​

Q5: 如何处理大量的慢查询？ ​

Q6: MariaDB 和 MySQL 的慢查询处理有什么区别？ ​

Q7: 如何使用 Performance Schema 代替慢查询日志？ ​

MariaDB 慢查询

慢查询概述

慢查询定义

慢查询的影响

版本差异

慢查询诊断

开启慢查询日志

临时开启

永久开启

分析慢查询日志

使用 mysqldumpslow 工具

使用 pt-query-digest 工具

使用 Performance Schema

使用 EXPLAIN 分析查询

常见慢查询场景

缺少索引

索引失效

复杂查询

大量数据扫描

锁等待

慢查询优化

索引优化

索引设计原则

索引维护

查询改写

优化子查询

优化 JOIN 查询

优化 GROUP BY 和 ORDER BY

表结构优化

规范化与反规范化

选择合适的数据类型

配置优化

查询优化器配置

InnoDB 配置

慢查询监控与告警

监控指标

告警设置

自动化处理

慢查询最佳实践

慢查询日志管理

开发规范

定期分析

慢查询案例分析

案例一：缺少联合索引

案例二：索引失效

FAQ

Q1: 慢查询阈值设置为多少合适？

Q2: 慢查询日志会影响性能吗？

Q3: 如何区分真慢查询和假慢查询？

Q4: 为什么优化后的查询在某些情况下仍然很慢？

Q5: 如何处理大量的慢查询？

Q6: MariaDB 和 MySQL 的慢查询处理有什么区别？

Q7: 如何使用 Performance Schema 代替慢查询日志？