小菜鸟

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

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

慢查询日志的基本概念

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

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

慢查询日志的配置

查看当前配置

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

-- 查看是否开启慢查询日志
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 的执行逻辑是 “先子查询，后外层查询”，适合外层表大、子查询结果小的场景。

语法与等价逻辑：

-- 原查询
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）。

索引的操作语法

创建索引

MySQL 执行计划详解：读懂 Explain，优化 SQL 不求人

执行计划（Explain）是 MySQL 优化的 “导航图”，它能模拟优化器执行 SQL 的过程，展示 MySQL 如何解析和执行你的查询。掌握执行计划的分析方法，是写出高效 SQL 的前提。本文将系统拆解执行计划的每个字段，教你如何通过 Explain 定位性能瓶颈。

执行计划的基本使用

通过EXPLAIN关键字可以生成执行计划，语法简单：

-- 基础用法
EXPLAIN SELECT * FROM user WHERE age > 18;

-- 结合索引未使用的原因（配合show warnings）
EXPLAIN SELECT * FROM user WHERE age > 18;
SHOW WARNINGS; -- 显示优化器对SQL的改写和分析

执行后会生成一张包含 12 个字段的表格，每个字段都隐藏着查询的关键信息：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

执行计划字段详解

1. id：查询执行顺序标识

表示 SELECT 子句或操作表的序号，决定执行顺序。

• id 相同：执行顺序由上至下（按 table 字段顺序）。
  例：多表连接查询中，id 相同表示按表的出现顺序依次访问。
• id 不同：id 越大优先级越高，先执行（子查询的 id 通常大于外层查询）。
  例：子查询的 id 为 2，外层查询 id 为 1，则先执行子查询。
• id 混合（有相同也有不同）：先执行 id 大的，id 相同的按顺序执行。

2. select_type：查询类型

区分普通查询、子查询、联合查询等，核心类型如下：