Logo

菜单

Administrator
发布于 2026-01-15 / 1 阅读
0

面鸭突击30之Day4

  1. MySQL 中的索引数量是否越多越好？为什么？

  2. 如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

  3. MySQL 中如何进行 SQL 调优？

一、索引数量的取舍原则

核心结论：索引并非越多越好，需在查询效率与写入性能之间做平衡。

1.1 索引过多的弊端

（1）时间维度：大幅增加写入 / 更新 / 删除开销

  • 每一次数据变更（INSERT/UPDATE/DELETE），都需要同步更新所有相关索引的 B+ 树，涉及页分裂、页合并、索引节点调整等操作。

  • 索引数量越多，优化器选择索引的耗时越长，甚至可能出现选错索引的情况，导致查询性能下降。

（2）空间维度：占用额外内存与磁盘空间

  • 每一个普通索引对应一棵独立的 B+ 树，索引树的存储会占用磁盘空间；

  • 内存中 Buffer Pool 需缓存索引页，过多索引会挤占数据页的缓存空间，降低缓存命中率。

（3）DDL 操作效率极低

  • 对于必须重建表的 DDL 操作（如修改字段类型、添加主键、创建联合索引），索引数量越多，重建时间越长，大表操作可能导致业务长时间不可用。

  • 注意：InnoDB 5.6+ 支持 Online DDL，部分操作（如添加列、修改注释）无需重建索引，影响较小。

（4）非自增主键的额外问题

  • 非自增主键（如 UUID、随机字符串）插入时，易触发 B+ 树页分裂，产生大量索引碎片；

  • 删除数据时，页利用率过低会触发页合并，进一步增加 IO 开销，长期会导致索引查询效率下降。

1.2 合理建索引的原则

（1）优先为高频查询、高选择性字段建索引

  • 索引选择性计算公式：选择性 = 字段不重复值数量 / 总行数，越接近 1 选择性越高，索引效率越好。

  • 低选择性字段（如性别、状态）不建议建单字段索引，但可作为联合索引的前置等值字段（如 idx_status_age (status, age)）。

  • 写入频率远高于查询频率的表，应尽量减少索引数量。

（2）联合索引遵循「最左匹配原则」

  • 联合索引字段顺序：等值查询字段放前面，范围查询字段放后面（如 WHERE a=1 AND b>10，建 idx_a_b (a,b)）。

  • 否则，范围查询后的字段无法利用索引。

1.3 索引的生命周期管理

（1）定期清理无用索引

  • MySQL 5.7+ 可通过 sys.schema_unused_indexes 视图查询长期未使用的索引，需满足两个前提：

    1. 开启 Performance Schema；

    2. 统计周期覆盖完整业务周期（避免遗漏离线任务索引）。

  • 低版本 MySQL 可通过 SHOW PROFILE 或 Performance Schema 相关表分析索引使用情况。

（2）删除索引前的验证步骤

  1. 确认索引未被任何业务 SQL、定时任务、报表查询使用；

  2. 在测试环境删除索引，执行压测，对比删除前后的查询 / 写入性能；

  3. 生产环境建议低峰期操作，做好回滚预案。

二、EXPLAIN 语句查询分析实战

2.1 基本使用方法

EXPLAIN SELECT * FROM user_info WHERE id = 1;
-- MySQL 8.0.18+ 支持 EXPLAIN ANALYZE，执行实际查询并返回真实执行数据
EXPLAIN ANALYZE SELECT * FROM user_info WHERE id > 10;

2.2 核心字段详解

字段

作用与关键知识点

id

表示查询执行顺序：1. id 相同 → 从上到下顺序执行2. id 不同 → id 越大，优先级越高，越先执行

select_type

查询类型，常见值：1. SIMPLE：简单查询（无子查询 / UNION）2. PRIMARY：外层主查询3. SUBQUERY：WHERE 子句中的非关联子查询4. DERIVED：FROM 子句中的派生表5. UNION：UNION 中后续查询6. UNION RESULT：UNION 结果汇总

table

执行查询涉及的表名（派生表会显示为 derivedN）

type

访问类型，效率从高到低排序：system > const > eq_ref > ref_equal > ref > range > index > ALL优化目标：至少达到 range，最好是 ref/eq_ref

key

实际使用的索引名称，NULL 表示未走索引

key_len

使用的索引长度（字节），越短越好（可判断联合索引的使用情况）

rows

优化器预估的扫描行数，数值越小越好

Extra

额外执行信息，重点关注以下值

2.3 Extra 字段重点值解析

Extra 值

含义与优化建议

Using index

使用覆盖索引，无需回表，性能最优

Using where

过滤数据，分两种情况：1. 与 Using index 共存 → 存储引擎层过滤（高效）2. 单独出现 → 服务层过滤（低效，常伴随全表扫描）

Using filesort

未使用索引排序，触发文件排序优化方案：为排序字段建索引；调大 sort_buffer_size 避免磁盘排序

Using temporary

创建临时表存储中间结果，性能差触发场景：GROUP BY/ORDER BY 字段不一致、无索引的 DISTINCT、UNION 去重优化方案：1. 为分组 / 排序字段建索引2. 用 UNION ALL 替代 UNION3. 保证 GROUP BY 与 ORDER BY 字段一致

Using join buffer

关联查询未走索引，使用连接缓冲区，需优化关联字段索引

2.4 关键补充

  1. ref_equal 是 ref 的特殊子集，仅适用于主键 / 唯一索引的等值关联查询，部分客户端会简化显示为 ref。

  2. index 类型是扫描整个索引树（仅扫索引，不扫数据），效率高于 ALL（全表扫描）。

  3. EXPLAIN 是估算结果，EXPLAIN ANALYZE 会实际执行查询，返回真实数据（MySQL 8.0.18+ 支持）。

三、SQL 调优完整流程与方案

3.1 第一步：定位慢 SQL

（1）开启慢查询日志

  • 临时开启（重启失效，需 SUPER 权限）：

    SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 阈值：执行时间超过1秒的SQL
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未走索引的查询

  • 永久开启（修改 my.cnf/my.ini，重启生效）：

    slow_query_log = ON
slow_query_log_file = /var/lib/mysql/mysql_slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

  • 注意：log_queries_not_using_indexes 生产环境不建议长期开启，避免日志膨胀。

（2）分析慢查询日志

  • 使用 mysqldumpslow 工具快速分析：

    # 查看执行时间最长的10条SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/mysql_slow.log

3.2 第二步：用 EXPLAIN 分析慢 SQL

核心关注：type 字段是否达到 range 及以上；Extra 字段是否出现 Using filesort/Using temporary。

3.3 第三步：常见性能问题与优化方案

（1）索引失效场景与解决办法

失效场景

示例

优化方案

模糊查询左 %/ 中间 %

WHERE name LIKE '%张三'

1. 改为右模糊 LIKE '张三%'2. 使用全文索引（FULLTEXT）

联合索引违反最左匹配

索引 idx_a_b (a,b)，查询 WHERE b=1

调整查询条件，或单独为 b 建索引

索引字段参与函数 / 运算

WHERE DATE(create_time) = '2024-01-01'

1. 改为范围查询 WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'2. MySQL 8.0+ 建函数索引

隐式类型转换

WHERE id = '123'（id 为 INT 类型）

保证查询条件与字段类型一致

关联字段字符集 / 排序规则不一致

t1.a JOIN t2.b，a 是 utf8，b 是 utf8mb4

统一关联字段的字符集和排序规则

NOT IN 子查询

WHERE id NOT IN (SELECT id FROM t2)

用 NOT EXISTS 替代：SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id)

（2）Using filesort 优化

  • 核心原因：排序字段无索引，或排序字段与索引字段不一致。

  • 优化方案：

    1. 为排序字段建立合适的索引（联合索引需包含排序字段）；

    2. 调大 sort_buffer_size（排序缓冲区），避免磁盘排序；

    3. 业务层面减少不必要的排序。

（3）Using temporary 优化

  • 核心优化手段：

    1. 为 GROUP BY/DISTINCT 字段建索引；

    2. 保证 GROUP BY 与 ORDER BY 字段一致，且符合索引左前缀；

    3. 用 UNION ALL 替代 UNION（无需去重）；

    4. 减少派生表使用，尽量合并子查询。

（4）深度分页问题优化

  • 问题：LIMIT 10000, 10 需要扫描前 10010 条记录，效率极低。

  • 优化方案：

    1. 自增主键 / 唯一索引分页（推荐）：

      -- 上一页最后一条 id 为 10000
SELECT * FROM t1 WHERE id > 10000 LIMIT 10;

    2. 联合索引书签分页（适用于复杂条件）：

      -- 索引 idx_age_id (age, id)，上一页最后一条 age=30, id=10000
SELECT * FROM t1 WHERE age > 25 AND (age > 30 OR (age=30 AND id>10000)) LIMIT 10;

    3. 业务层面限制分页页数，或采用下拉加载更多。

3.4 第四步：走了索引仍慢的进阶优化

（1）回表查询代价过高

  • 问题：非覆盖索引需要回表（通过二级索引查主键，再查聚簇索引），大结果集回表开销大。

  • 优化方案：建覆盖索引（索引包含查询所需的所有字段），避免回表。

（2）索引选择性过低

  • 问题：索引字段重复值过多，扫描行数依然很大。

  • 优化方案：

    1. 改为联合索引，增加前置高选择性字段；

    2. 业务层面增加过滤条件，缩小扫描范围。

（3）索引碎片过多

  • 问题：长期增删改导致索引碎片，扫描时需要读取更多页。

  • 优化方案：低峰期执行 OPTIMIZE TABLE t1; 重建索引（InnoDB 会重建表和索引，锁表需谨慎）。

（4）数据库负载过高

  • 排查方向：

    1. 服务器资源：CPU / 内存 / 磁盘 IO 是否达到瓶颈；

    2. 数据库连接数：是否超过 max_connections；

    3. 锁等待 / 死锁：执行 SHOW ENGINE INNODB STATUS 查看；

    4. 其他慢查询：是否有长事务阻塞。

3.5 第五步：架构层面优化

  1. 读写分离：主库写入，从库查询，分摊查询压力；

  2. 分库分表：大表按范围 / 哈希分表，降低单表数据量；

  3. 二级缓存：热点数据存入 Redis，减少数据库查询；

  4. 冷热数据分离：将历史数据迁移到历史表，保持业务表数据量适中。

四、总结

  1. 索引设计需平衡查询与写入性能，遵循「高频、高选择性、左前缀」原则；

  2. 熟练使用 EXPLAIN 分析执行计划，重点关注 type 和 Extra 字段；

  3. SQL 调优需从「索引优化 → SQL 逻辑优化 → 架构优化」逐步推进；

  4. 生产环境优化需谨慎，做好测试和回滚预案。

面鸭突击之Day5
面鸭突击30之Day3