MySQL 8.0慢查询治理实战：从Performance Schema到索引调优全链路方案

小编 • • 数据库

慢查询采集与Performance Schema分析

MySQL 8.0的Performance Schema比慢查询日志提供更细粒度的分析能力。启用事件语句采集：

-- 启用语句事件采集
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_statements%';

查询Top 10耗时SQL：

SELECT
    DIGEST_TEXT AS query_pattern,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_time_ms,
    SUM_ROWS_EXAMINED / COUNT_STAR AS avg_rows_examined,
    SUM_ROWS_SENT / COUNT_STAR AS avg_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'production_db'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

关注avg_rows_examined / avg_rows_sent比值，超过100说明存在大量无效扫描，是索引缺失或失效的典型信号。

EXPLAIN执行计划深度解读

MySQL 8.0的EXPLAIN ANALYZE提供实际执行耗时，比传统EXPLAIN更可靠：

EXPLAIN ANALYZE
SELECT o.order_id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2026-07-01'
  AND o.status = 'PAID'
ORDER BY o.amount DESC
LIMIT 50;

输出中关注三个指标：actual time显示各阶段真实耗时，rows显示实际处理行数，loops显示执行次数。常见问题模式：

1. type=ALL：全表扫描，缺少索引或索引失效。

2. type=ref但rows远大于实际返回：索引选择性差，需要优化索引列顺序。

3. Using filesort：排序未走索引，需要创建覆盖排序的复合索引。

复合索引设计与最左前缀规则

慢查询治理的核心手段是建立高效索引。复合索引的设计遵循等值条件在前、范围条件在后的原则：

-- 查询模式：WHERE status = 'PAID' AND created_at >= '2026-07-01' ORDER BY amount DESC
ALTER TABLE orders ADD INDEX idx_status_created_amount (status, created_at, amount);

最左前缀规则决定索引能否被使用：status作为等值条件放在最左，created_at范围条件居中，amount用于排序避免filesort。三个列组合让这条查询完全走索引覆盖，无需回表。

索引失效的常见陷阱：

-- 陷阱1：索引列使用函数
WHERE DATE(created_at) = '2026-08-04'  -- 索引失效
-- 修正：
WHERE created_at >= '2026-08-04' AND created_at < '2026-08-05'

-- 陷阱2：隐式类型转换
WHERE varchar_col = 123  -- 索引失效，应写 '123'

-- 陷阱3：OR条件导致索引合并失效
WHERE col_a = 1 OR col_b = 2
-- 修正：UNION ALL拆分
SELECT * FROM t WHERE col_a = 1
UNION ALL
SELECT * FROM t WHERE col_b = 2 AND col_a != 1

InnoDB缓冲池调优与监控

慢查询的另一个根源是缓冲池命中率不足。监控关键指标：

SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_reads'
);

-- 命中率 = 1 - reads / read_requests
-- 命中率低于95%需要增大缓冲池

调整缓冲池大小：

-- 在线调整（MySQL 8.0支持）
SET GLOBAL innodb_buffer_pool_size = 8589934592;  -- 8GB

-- 持久化到配置文件
[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8

SQL查询优化工具与自动化巡检

查找冗余索引：

SELECT s.table_name, s.index_name,
    GROUP_CONCAT(s.column_name ORDER BY s.seq_in_index) AS index_columns
FROM information_schema.statistics s
WHERE s.table_schema = 'production_db'
GROUP BY s.table_name, s.index_name
HAVING COUNT(*) > 1
AND EXISTS (
    SELECT 1 FROM information_schema.statistics s2
    WHERE s2.table_schema = s.table_schema
    AND s2.table_name = s.table_name
    AND s2.index_name != s.index_name
    AND s2.column_name = s.column_name
);

Prometheus + mysqld_exporter搭建持续监控，核心告警指标：慢查询数量增长率、缓冲池命中率下降、连接数逼近上限。慢查询治理不是一次性工作，需要纳入日常运维SOP，每周Review一次Top SQL变化，确保治理成果长期有效。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql80-man-cha-xun-zhi-li-shi-zhan-cong-performanceschema/

赞 (0)
小编小编
0
上一篇 12小时前
下一篇 12小时前

相关推荐

MySQL 8.0慢查询治理实战：从Performance Schema到索引调优全链路方案

小编 • • 数据库

慢查询采集与Performance Schema分析

MySQL 8.0的Performance Schema比慢查询日志提供更细粒度的分析能力。启用事件语句采集：

-- 启用语句事件采集
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_statements%';

查询Top 10耗时SQL：

SELECT
    DIGEST_TEXT AS query_pattern,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_time_ms,
    SUM_ROWS_EXAMINED / COUNT_STAR AS avg_rows_examined,
    SUM_ROWS_SENT / COUNT_STAR AS avg_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'production_db'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

关注avg_rows_examined / avg_rows_sent比值，超过100说明存在大量无效扫描，是索引缺失或失效的典型信号。

EXPLAIN执行计划深度解读

MySQL 8.0的EXPLAIN ANALYZE提供实际执行耗时，比传统EXPLAIN更可靠：

EXPLAIN ANALYZE
SELECT o.order_id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2026-07-01'
  AND o.status = 'PAID'
ORDER BY o.amount DESC
LIMIT 50;

输出中关注三个指标：actual time显示各阶段真实耗时，rows显示实际处理行数，loops显示执行次数。常见问题模式：

1. type=ALL：全表扫描，缺少索引或索引失效。2. type=ref但rows远大于实际返回：索引选择性差，需要优化索引列顺序。3. Using filesort：排序未走索引，需要创建覆盖排序的复合索引。

复合索引设计与最左前缀规则

慢查询治理的核心手段是建立高效索引。复合索引的设计遵循等值条件在前、范围条件在后的原则：

-- 查询模式：WHERE status = 'PAID' AND created_at >= '2026-07-01' ORDER BY amount DESC
-- 索引设计
ALTER TABLE orders ADD INDEX idx_status_created_amount (status, created_at, amount);

最左前缀规则决定索引能否被使用：status作为等值条件放在最左，created_at范围条件居中，amount用于排序避免filesort。三个列组合让这条查询完全走索引覆盖，无需回表。

索引失效的常见陷阱：

-- 陷阱1：索引列使用函数
WHERE DATE(created_at) = '2026-08-04'  -- 索引失效
-- 修正：
WHERE created_at >= '2026-08-04' AND created_at < '2026-08-05'

-- 陷阱2：隐式类型转换
WHERE varchar_col = 123  -- 索引失效，应写 '123'

-- 陷阱3：OR条件导致索引合并失效
WHERE col_a = 1 OR col_b = 2  -- 走不了(a)和(b)两个索引
-- 修正：UNION ALL拆分
SELECT * FROM t WHERE col_a = 1
UNION ALL
SELECT * FROM t WHERE col_b = 2 AND col_a != 1

InnoDB缓冲池调优与监控

慢查询的另一个根源是缓冲池命中率不足。监控关键指标：

SELECT
    variable_name,
    variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_reads'
);

-- 命中率计算
-- hit_rate = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
-- 命中率低于95%需要增大缓冲池

调整缓冲池大小：

-- 在线调整（MySQL 8.0支持）
SET GLOBAL innodb_buffer_pool_size = 8589934592;  -- 8GB

-- 持久化到配置文件
[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8

SQL查询优化工具与自动化巡检

MySQL Shell的util.checkTableIndexes()可批量检测冗余索引：

\sql
-- 查找冗余索引
SELECT
    table_name,
    index_name,
    GROUP_CONCAT(column_name ORDER BY seq_in_index) AS index_columns,
    CASE WHEN 
        EXISTS (
            SELECT 1 FROM information_schema.statistics s2
            WHERE s2.table_schema = s.table_schema
            AND s2.table_name = s.table_name
            AND s2.index_name != s.index_name
            AND s2.column_name = s.column_name
            AND s2.seq_in_index >= s.seq_in_index
        ) THEN 'REDUNDANT' ELSE 'OK'
    END AS status
FROM information_schema.statistics s
WHERE s.table_schema = 'production_db'
GROUP BY table_name, index_name, seq_in_index;

Prometheus + mysqld_exporter搭建持续监控，核心告警指标：慢查询数量增长率、缓冲池命中率下降、连接数逼近上限。慢查询治理不是一次性工作，需要纳入日常运维SOP，每周Review一次Top SQL变化，确保治理成果长期有效。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql80-man-cha-xun-zhi-li-shi-zhan-cong-performanceschema/

赞 (0)
小编小编
0
上一篇 13小时前
下一篇 13小时前

相关推荐