小菜鸟

MySQL 优化建议：从查询到存储引擎的全方位优化

数据库性能优化的核心是减少磁盘 IO 和 CPU 消耗，同时高效利用内存缓存。本文从查询优化、索引使用、存储引擎配置等维度，结合 MySQL 底层原理，提供可落地的优化建议。

JOIN 语句优化：减少循环与 IO 开销

JOIN 是多表关联查询的常用操作，但其性能易受表大小、索引、缓冲等因素影响，优化核心是减少嵌套循环次数和被驱动表的扫描代价。

1. 小表驱动大表（小结果集驱动大结果集）

JOIN 本质是 “嵌套循环”：外层表（驱动表）的每条记录都会触发内层表（被驱动表）的查询。驱动表越小，总循环次数越少。

反例：

1
2
3
4

-- 驱动表（admin）未过滤，数据量大
SELECT * FROM admin 
LEFT JOIN log ON log.admin_id = admin.id 
WHERE log.admin_id > 10;

优化：先过滤驱动表，缩小结果集

1
2
3
4
5

-- 子查询过滤驱动表，只保留必要数据
SELECT * FROM (
  SELECT * FROM admin WHERE id > 10  -- 驱动表结果集变小
) t1 
LEFT JOIN log ON log.admin_id = t1.id;

2. 优先优化内层循环

内层循环（被驱动表）的执行次数 = 驱动表的行数，因此被驱动表的查询效率对整体性能影响更大。

关键优化：确保被驱动表的 JOIN 条件有索引

SQL 优化全指南：从定位瓶颈到落地实践

SQL 优化是提升数据库性能的核心手段，但优化并非盲目调整，而是一套 “观察 - 分析 - 优化 - 验证” 的系统性流程。本文结合 MySQL 底层原理和实战工具，详细讲解 SQL 优化的步骤、原则及具体方法。

SQL 优化的核心步骤

优化的前提是 “找到问题”，而非盲目改写 SQL。完整的优化流程分为以下 6 步：

定位性能瓶颈：判断是 IO 还是 CPU 瓶颈

首先需明确 SQL 执行缓慢的根源 —— 是磁盘 IO 过多（如全表扫描），还是 CPU 计算密集（如复杂排序、多表连接）。

工具：performance_schema（替代废弃的 show profile）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 1. 开启事件收集（默认开启）
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements%';

-- 2. 执行目标 SQL 后，查询其执行细节
SELECT 
  event_name AS 阶段,
  TIMER_WAIT / 1000000000 AS 耗时_ms  -- 转换为毫秒
FROM performance_schema.events_stages_history_long 
WHERE NESTING_EVENT_ID = (
  SELECT EVENT_ID 
  FROM performance_schema.events_statements_history_long 
  WHERE SQL_TEXT LIKE '%目标SQL%' 
  ORDER BY EVENT_ID DESC LIMIT 1
)
ORDER BY TIMER_START;

瓶颈判断：

• IO 瓶颈：阶段包含 table scan（全表扫描）、reading data（大量磁盘读），且耗时占比高。
• CPU 瓶颈：阶段包含 sorting result（排序）、joining tables（连接），且耗时占比高。

明确优化目标：设定可量化指标

优化需有具体目标，例如：

MySQL show profile 调优：深入分析 SQL 执行性能

当 EXPLAIN 执行计划显示 SQL 已命中索引但仍执行缓慢时，show profile 可进一步分析 SQL 执行过程中的资源消耗（如 CPU、IO、内存等），定位具体性能瓶颈。本文详细介绍其配置、使用方法及优化实践。

show profile 简介

show profile 是 MySQL 提供的会话级性能分析工具，用于记录当前会话中 SQL 语句的执行阶段及各阶段的资源消耗，包括：

• 时间消耗（Duration）
• CPU 消耗（CPU_user、CPU_system）
• IO 操作（Block_ops_in、Block_ops_out）
• 内存、上下文切换、页面错误等

通过分析这些细节，可精准定位 SQL 执行中的低效环节（如临时表创建、磁盘 IO 频繁等）。

show profile 配置

查看当前配置

1

show variables like '%profiling%';

输出结果：

MySQL 慢查询日志：配置、分析与优化实践

慢查询日志是 MySQL 性能优化的重要工具，用于记录执行时间超过阈值的 SQL 语句。通过分析慢查询日志，可定位低效查询，针对性优化，提升数据库性能。本文详细介绍慢查询日志的配置、分析工具及使用技巧。

慢查询日志的基本概念

慢查询日志的核心作用是追踪执行耗时过长的 SQL 语句，帮助开发者发现性能瓶颈。其关键参数包括：

• long_query_time：慢查询阈值（单位：秒），默认值为 10 秒（即执行时间 ≥ 该值的 SQL 会被记录）。
• slow_query_log：是否开启慢查询日志（0 关闭，1 开启），默认关闭（因会产生一定性能开销）。

慢查询日志的配置

查看当前配置

通过以下命令查看慢查询相关参数：

1
2
3
4
5
6
7
8
9
10
11

-- 查看是否开启慢查询日志
show variables like '%slow_query_log%';

-- 查看慢查询阈值（long_query_time）
show variables like '%long_query_time%';

-- 查看慢查询日志文件路径
show variables like '%slow_query_log_file%';

-- 查看未使用索引的SQL是否被记录
show variables like '%log_queries_not_using_indexes%';

临时开启与配置（即时生效，重启失效）

MySQL 排序优化：从原理到实践

排序是数据库查询中常见的操作，ORDER BY 语句的性能直接影响查询效率。MySQL 的排序机制分为两种：利用索引排序（index） 和 文件排序（filesort）。前者效率极高，后者开销较大。优化排序的核心目标是：尽可能使用索引排序，避免或高效处理 filesort。

排序的两种方式：index vs filesort

利用索引排序（index）

索引本身是有序的（如 B+ 树索引的叶子节点按索引键排序），若 ORDER BY 语句能直接利用索引的有序性获取数据，则无需额外排序，这种方式称为 “索引排序”。

特点：

• 效率极高：无需排序计算，直接返回有序数据。
• 在执行计划中：Extra 字段无 Using filesort（可能显示 Using index 或空）。

适用条件：

要触发索引排序，需满足以下规则（核心是 “索引有序性与排序需求一致”）：

1. ORDER BY 需使用索引的最左前缀
   例：索引为 (a, b, c)，则 ORDER BY a、ORDER BY a, b、ORDER BY a, b, c 可利用索引排序；ORDER BY b、ORDER BY a, c 无法利用（跳过了中间字段 b）。
2. WHERE 与 ORDER BY 组合需满足最左前缀
   例：索引 (a, b, c)，WHERE a = 1 ORDER BY b 可利用索引；WHERE b = 1 ORDER BY a 无法利用（WHERE 未使用索引前缀 a）。
3. 多字段排序需方向一致
   索引的有序性是单向的（要么全升序，要么全降序），因此 ORDER BY 中多字段的排序方向（ASC/DESC）必须一致。
   例：ORDER BY a ASC, b ASC 有效；ORDER BY a ASC, b DESC 无效（方向冲突，无法利用索引有序性）。

文件排序（filesort）

当无法利用索引排序时，MySQL 会将数据取出后，在内存或磁盘中进行排序，这种方式称为 “filesort”。

特点：

• 效率较低：需额外的排序计算，可能涉及磁盘 I/O（临时文件）。
• 在执行计划中：Extra 字段显示 Using filesort。

排序过程（常规流程）：