小菜鸟

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。

排序过程（常规流程）：

MySQL 中 IN 和 EXISTS 的取舍：原理与最佳实践

在 SQL 查询中，IN 和 EXISTS 都用于实现子查询条件过滤，但两者的执行逻辑和性能表现存在显著差异。掌握它们的适用场景，能有效提升查询效率，核心原则是 “小表驱动大表”—— 让数据量少的表作为外层循环，减少总迭代次数。

IN 与 EXISTS 的执行原理

IN 子查询：先执行子查询，再匹配外层表

IN 的执行逻辑是 “先子查询，后外层查询”，适合外层表大、子查询结果小的场景。

语法与等价逻辑：

1
2
3
4
5
6

-- 原查询
SELECT * FROM A WHERE id IN (SELECT id FROM B);

-- 等价执行步骤：
1. 先执行子查询，获取 B 表的 id 集合（如结果为 [1,2,3]），并生成临时表存储。
2. 遍历 A 表，判断每条记录的 id 是否在临时表中，匹配则保留。

特点：

• 子查询只执行一次，结果存储在临时表中（内存或磁盘）。
• 外层表需全表扫描（或使用索引），逐条与子查询结果匹配。
• 子查询结果集越大，临时表占用空间越多，匹配效率越低。

EXISTS 子查询：先执行外层表，再用子查询验证

EXISTS 的执行逻辑是 “先外层查询，后子查询验证”，适合外层表小、子查询表大的场景。

语法与等价逻辑：

MySQL 索引详解

什么是 MySQL 索引

索引是帮助 MySQL 快速查找数据的数据结构，它通过某种方式指向数据，并基于该结构实现高效查找算法，从而加速数据检索。

MySQL 默认使用的是 B-Tree 索引（实际结构为 B + 树），这是最常用的索引类型。

索引的分类

1. 普通索引

最基础的索引，无任何约束，仅用于加速查询。

2. 唯一索引

与普通索引类似，但附加唯一性约束（允许空值，但空值只能出现一次），可避免数据重复。

3. 主键索引

特殊的唯一索引，不允许空值，每张表只能有一个主键索引。MySQL 会自动为主键创建主键索引。

4. 复合索引

将多个列组合创建的索引，可覆盖多列查询条件，查询时需遵循 “最左前缀法则”。

5. 外键索引

仅 InnoDB 引擎支持，用于维护表之间的关联关系，保证数据的一致性、完整性，并支持级联操作。

6. 全文索引

用于全文检索，MySQL 自带的全文索引仅支持 InnoDB（5.6+）和 MyISAM 引擎，且对中文支持有限（5.7 + 通过 ngram 插件支持）。实际场景中，更常用专业全文索引引擎（如 Elasticsearch、Solr）。

索引的操作语法

创建索引