MySQL: 查询全流程深度解析与性能优化实践指南

原创于 2025-11-20 19:45:00 发布 · 1.2k 阅读

20 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #性能优化 #数据库

DataBase 专栏收录该内容

105 篇文章

订阅专栏

SQL解析预处理及执行计划生成机制

MySQL 处理查询请求的核心流程包括以下步骤：

客户端请求：通过接口向服务器发送 SQL 请求（性能影响可忽略）。
查询缓存检查：通过大小写敏感的哈希查找匹配缓存。若命中则直接返回结果（需满足条件：SQL 完全一致且涉及的表未更新），否则进入下一阶段。
解析与预处理：
- 语法解析：基于关键字生成解析树，验证 SQL 语法正确性。
- 语义检查：验证表/列是否存在、别名歧义等合法性。
优化器生成执行计划：
- 对比索引统计信息，选择成本最低的执行路径。
- 影响因素包括：
  - 统计信息偏差（如 InnoDB 的抽样估算）。
  - 成本估算模型缺陷（忽略内存/磁盘访问差异）。
  - 固定规则优先级（如强制使用全文索引）。
执行与数据获取：调用存储引擎 API 读取数据，返回结果前可能进行缓存过滤

MySQL服务器处理SQL请求的核心流程如下：

1 ) 查询缓存机制深度剖析

工作逻辑：通过大小写敏感的哈希查找匹配SQL语句（字节级完全一致）。若缓存命中则跳过解析/优化阶段直接返回结果。

关键参数：

-- 查询缓存配置参数示例 
SET GLOBAL query_cache_type = ON;  -- 可选ON/OFF/DEMAND 
SET GLOBAL query_cache_size = 1048576;  -- 单位字节（需1024整数倍）
SET GLOBAL query_cache_limit = 1048576;  -- 单条结果最大值

性能陷阱：
- 表数据更新即导致关联缓存失效
- 高并发下缓存锁引发性能衰减

查询缓存局限性：

频繁读写场景中，缓存锁竞争会降低性能
建议配置参数

在读写密集型系统中关闭查询缓存

SET GLOBAL query_cache_size = 0;     -- 清空缓存内存
SET GLOBAL query_cache_type = OFF;   -- 关闭缓存

优化器的改写能力：

关联顺序重排（基于统计信息调整 JOIN 顺序）
外连接转内连接（当 WHERE 条件过滤关联表时）
表达式简化（如 5=5 AND a>5 → a>5）
聚合函数优化（利用 B+树索引直接获取 MIN/MAX）

2 ) SQL解析与优化器工作机制

处理阶段：
1. 语法解析：构建解析树，验证关键字顺序
2. 预处理：检查表/列存在性，消除歧义
3. 优化器：基于成本模型生成执行计划

优化器局限因素：

影响因素	具体表现
统计信息偏差	InnoDB的抽样统计导致行数估算错误
成本估算偏差	未考虑内存页缓存状态（随机IO vs 顺序IO）
规则优先于成本	强制使用全文索引（即使其他索引更快）
忽略外部成本	不计算存储过程/UDF执行开销

3 ) 优化器的智能改写能力

关联顺序重排：根据统计信息调整JOIN顺序

外连接转内连接：当WHERE条件过滤NULL值时自动转换

/* 改写示例：LEFT JOIN → INNER JOIN */
SELECT * FROM A 
LEFT JOIN B ON A.id = B.a_id 
WHERE B.col IS NOT NULL;

表达式简化：
```
WHERE 5=5 AND a>5  →  WHERE a>5 
```

聚合函数优化：利用B+树特性快速获取MIN/MAX

EXPLAIN SELECT MAX(id) FROM table; 
-- 执行计划显示：Select tables optimized away

IN列表优化：二分查找替代多重OR条件

SELECT * FROM table WHERE id IN (1,3,5,7...10000);

精准度量SQL各阶段耗时的方法论

1 ) Profiling工具（已弃用）

SHOW PROFILE

SET profiling = 1;  -- 开启会话级监控
SELECT * FROM film;         -- 执行目标查询 
SHOW PROFILES;  -- 查看总耗时
SHOW PROFILE CPU FOR QUERY 1;  -- 查看细节

输出示例：

Status	Duration
starting	0.0001s
Sending data	0.0050s
freeing items	0.0002s

MySQL 5.7+ 推荐改用 Performance Schema

2 ) Performance Schema方案（推荐）

-- 启用性能监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%statement/%';
 
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements_%';

-- 获取各阶段耗时分析 
SELECT 
  event_name AS Stage,
  SUM(timer_wait)/1e9 AS Duration_ms 
FROM performance_schema.events_stages_history_long
WHERE thread_id = (SELECT THREAD_ID 
                   FROM performance_schema.threads 
                   WHERE PROCESSLIST_ID = CONNECTION_ID())
GROUP BY event_name;

耗时热点解析：

Sending data：数据检索阶段（常为瓶颈）
- 阶段高耗时：通常因全表扫描或索引失效导致，需检查执行计划
Creating sort index：排序操作开销
- 高耗时：内存排序溢出到磁盘，需优化ORDER BY或增大sort_buffer_size
statistics：统计信息收集时间

输出示例：

EVENT_ID	Duration_sec	SQL_TEXT	Stage
101	0.005	SELECT * FROM film	Sending data

特定场景SQL优化实战方案

1 ) 大表数据变更操作

/* 分批更新存储过程示例 */
DELIMITER $$
CREATE PROCEDURE batch_update()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE start_id INT DEFAULT 0;
  WHILE NOT done DO 
    UPDATE large_table 
    SET col = new_value 
    WHERE id BETWEEN start_id AND start_id + 5000;
    SET start_id = start_id + 5000;
    -- 主从同步等待 
    DO SLEEP(1); 
    IF start_id > max_id THEN SET done = TRUE;
    END IF;
  END WHILE;
END$$
DELIMITER ;

或

存储过程示例（分批删除数据）：

DELIMITER $$
CREATE PROCEDURE batch_delete(
  IN table_name VARCHAR(64), 
  IN condition VARCHAR(255), 
  IN batch_size INT
)
BEGIN 
  DECLARE rows_affected INT DEFAULT 1;
  WHILE rows_affected > 0 DO
    SET @sql = CONCAT(
      'DELETE FROM ', table_name, 
      ' WHERE ', condition, 
      ' LIMIT ', batch_size
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    SET rows_affected = ROW_COUNT();
    DEALLOCATE PREPARE stmt;
    DO SLEEP(2);  -- 缓解主从复制压力
  END WHILE;
END$$
DELIMITER ;

或

-- 存储过程分批次删除数据（避免长事务）
DELIMITER $$
CREATE PROCEDURE batch_delete()
BEGIN
  DECLARE rows INT DEFAULT 1;
  WHILE rows > 0 DO
    DELETE FROM large_table 
    WHERE create_time < '2020-01-01' 
    LIMIT 5000; -- 单批处理量 
    SET rows = ROW_COUNT();
    DO SLEEP(2); -- 主从同步缓冲 
  END WHILE;
END$$
DELIMITER ;

2 ) 亿级量表结构变更方案

工具：pt-online-schema-change（Percona Toolkit）

使用Percona Toolkit在线DDL
pt-online-schema-change \
  --alter "MODIFY COLUMN c VARCHAR(150) NOT NULL" \
  --user=root --password= \
  D=sakila,t=ssb_test4 \
  --charset=utf8 --execute

工具原理：

创建影子表（新结构）
原表建触发器同步增量数据
分批拷贝历史数据
原子切换表（RENAME TABLE）
删除旧表

执行流程图：

3 ) NOT IN子查询优化

/* 低效写法 */
SELECT * FROM customer 
WHERE customer_id NOT IN (
  SELECT customer_id FROM payment 
);
 
/* 优化方案：LEFT JOIN */
SELECT c.* 
FROM customer c
LEFT JOIN payment p ON c.customer_id = p.customer_id 
WHERE p.customer_id IS NULL;  -- 无关联记录即未支付

4 ) 高并发统计查询优化

/* 原始实时统计 */
SELECT COUNT(*) FROM comments WHERE item_id = 999; 
 
/* 汇总表方案 */
-- 建表 
CREATE TABLE comment_summary (
  item_id INT PRIMARY KEY,
  comment_count INT,
  last_update DATE 
);
 
-- 凌晨批量更新
REPLACE INTO comment_summary 
SELECT item_id, COUNT(*), CURDATE() 
FROM comments GROUP BY item_id;
 
-- 当日增量统计（利用覆盖索引）
SELECT cs.comment_count + IFNULL(tmp.cnt,0) AS total 
FROM comment_summary cs 
LEFT JOIN (
  SELECT item_id, COUNT(*) cnt 
  FROM comments 
  WHERE item_id=999 AND create_time > CURDATE()
) tmp ON cs.item_id = tmp.item_id
WHERE cs.item_id=999;

关键技术点：

覆盖索引优化：确保comments(create_time, item_id)索引存在
异步计算：定时任务更新历史聚合数据
增量合并：实时查询仅计算当日变化

Nestjs 工程实例

1 ) 方案1

每日定时更新

// 汇总表更新服务 (NestJS)
import { Cron, CronExpression } from '@nestjs/schedule';
import { EntityManager } from 'typeorm';
 
@Injectable()
export class SummaryService {
  constructor(private readonly manager: EntityManager) {}
 
  @Cron(CronExpression.EVERY_DAY_AT_4AM)
  async updateCommentSummary() {
    await this.manager.query(`
      INSERT INTO comment_summary (product_id, comment_count)
      SELECT product_id, COUNT(*) 
      FROM comments
      GROUP BY product_id
      ON DUPLICATE KEY UPDATE 
        comment_count = VALUES(comment_count),
        last_update = NOW()
    `);
  }
}

实时查询优化：

SELECT 
  cs.comment_count AS historical,
  COUNT(c.id) AS today,
  cs.comment_count + COUNT(c.id) AS total 
FROM comment_summary cs 
LEFT JOIN comments c 
  ON cs.product_id = c.product_id
  AND c.created_at >= CURDATE()  -- 当日数据 
WHERE cs.product_id = 999;

2 ) 方案2

高并发统计查询优化

/* 原始实时统计（性能低下） */
SELECT COUNT(*) FROM comments WHERE product_id = 999;
 
/* 汇总表方案 */
-- 步骤1：创建汇总表
CREATE TABLE comment_summary (
  product_id INT PRIMARY KEY,
  comment_count INT,
  last_update TIMESTAMP
);

步骤2：NestJS定时任务（每日增量更新）

import { Cron } from '@nestjs/schedule';
@Injectable()
export class SummaryService {
  @Cron('0 3 * * *') // 每天3AM执行
  async updateSummary() {
    await this.entityManager.query(`
      INSERT INTO comment_summary (product_id, comment_count, last_update)
      SELECT product_id, COUNT(*), NOW() 
      FROM comments 
      WHERE create_time > CURDATE() - INTERVAL 1 DAY 
      GROUP BY product_id 
      ON DUPLICATE KEY UPDATE 
        comment_count = VALUES(comment_count),
        last_update = NOW()
    `);
  }
}

步骤3：查询优化

SELECT 
  COALESCE(s.comment_count, 0) + 
    (SELECT COUNT(*) 
     FROM comments 
     WHERE product_id = 999 
       AND create_time >= CURDATE()) AS total 
FROM comment_summary s 
WHERE product_id = 999;

关键优化总结

场景	优化方案	核心收益
高频小数据查询	查询缓存（读写低频场景）	避免重复解析与执行
大表 DML 操作	分批处理 + 睡眠间隔	减少锁竞争与主从延迟
大表 DDL 操作	`pt-online-schema-change`	无锁在线修改表结构
NOT IN 子查询	转换为 LEFT JOIN + IS NULL	避免多次子查询扫描
实时聚合统计	汇总表 + 增量更新	将全表扫描转为单行查询

关键优化原则总结

索引策略

优先使用覆盖索引（EXPLAIN的Extra: Using index）
避免索引失效场景（函数转换、类型隐式转换）

执行计划分析

EXPLAIN FORMAT=JSON 
SELECT * FROM film WHERE title LIKE 'A%';

关注possible_keys vs key差异
警惕Using filesort/Using temporary

架构级优化

读写分离：将统计查询路由到只读副本
分布式方案：分库分表（如ShardingSphere）

重要结论：查询缓存在高并发场景下建议关闭（query_cache_type = OFF），因其维护成本高于收益。优化重点应聚焦于执行计划生成质量与存储引擎交互效率

通过精确度量各阶段耗时（Performance Schema）和执行计划分析，可针对性优化关键瓶颈点，避免盲目调优