koyomi315的头像
koyomi315
北京邮电大学硕士,关注后端开发、AI Agent 与 Agent Evaluation。
1185 字
6 分钟

MySQL 索引、事务与慢 SQL 优化

数据库优化的目标不是让每条 SQL 都“走索引”,而是用更少的 IO、锁和计算完成正确查询。索引、事务和执行计划需要放在同一个成本模型中理解。

为什么常见索引使用 B+ 树#

B+ 树适合磁盘和页式存储:

  • 分支多,树高较低;
  • 非叶子节点主要保存导航键,可以容纳更多分支;
  • 叶子节点有序,适合范围扫描;
  • 查找和范围遍历都能以较少页访问完成。

哈希结构擅长等值查询,但不天然支持有序范围、前缀排序和最左匹配,因此不能替代通用有序索引。

聚簇索引与二级索引#

在 InnoDB 中,主键索引的叶子节点保存整行记录。二级索引叶子节点通常保存索引列和主键值。

使用二级索引查询其他列时,可能先找到主键,再回到聚簇索引读取整行,这就是常说的回表。

如果查询所需字段都能从二级索引得到,就形成覆盖索引,减少额外页访问。但为了覆盖所有查询而创建超宽索引,会增加写放大和缓存压力。

联合索引与最左前缀#

联合索引按列顺序排序。例如 (tenant_id, status, created_at) 可以高效支持以 tenant_id 开头的条件组合。

设计顺序时要结合:

  • 等值条件;
  • 范围条件;
  • 排序和分组;
  • 区分度;
  • 真实查询频率。

一旦进入范围扫描,后续列能否继续用于定位取决于具体条件和优化器策略,不能只背“范围后全部失效”的绝对口诀。

常见索引利用问题#

  • 对索引列做无法改写的函数计算;
  • 隐式类型转换;
  • 联合索引没有可用前缀;
  • 前导通配符模糊匹配;
  • 返回数据占比过高,优化器认为全表扫描更便宜;
  • 排序、分组与索引顺序不匹配;
  • 统计信息不足或分布倾斜。

“没有使用索引”不一定是错误。小表或需要返回大部分行时,全表扫描可能更合理。

事务隔离解决什么#

事务提供原子性、一致性、隔离性和持久性。隔离级别在并发能力和可见性之间取舍。

常见现象包括:

  • 脏读:读到尚未提交的数据;
  • 不可重复读:同一事务两次读取同一行得到不同值;
  • 幻读:同一条件查询出现新的行集合。

具体行为还受到数据库实现、快照读、当前读和锁策略影响,不能只用一张理论表解释所有 SQL。

MVCC 的基本思路#

MVCC 通过版本信息和一致性视图,让读取在很多情况下不必阻塞写入。事务根据自己的视图判断哪个版本可见。

MVCC 不意味着所有查询都不加锁:修改语句、显式锁定读和部分约束检查仍需要锁。长事务还会阻碍旧版本清理,增加存储和维护成本。

用执行计划而不是猜测#

分析慢 SQL 时,执行计划重点观察:

  • 访问方式;
  • 实际选择的索引;
  • 预计与实际扫描行数;
  • 过滤比例;
  • 排序、临时表和回表;
  • 连接顺序;
  • 执行时间分布。

如果环境支持实际执行分析,可以比较优化器估计与真实行数。估计严重偏差时,应检查统计信息和数据分布,而不是立刻强制索引。

慢 SQL 的排查流程#

  1. 从慢日志或追踪定位具体 SQL 和参数模式;
  2. 确认慢是稳定发生还是偶发锁等待;
  3. 查看执行计划和扫描行数;
  4. 检查索引、统计信息和返回数据量;
  5. 分析事务与锁;
  6. 在接近生产分布的数据上验证修改;
  7. 观察优化后 CPU、IO、延迟和写入成本。

只在开发环境用少量数据执行很快,不能证明线上计划合理。

索引的代价#

每个索引都会带来:

  • 写入时维护成本;
  • 额外存储;
  • 缓冲池占用;
  • 优化器选择空间;
  • DDL 和数据迁移成本。

应删除长期不使用且与其他索引重复覆盖的索引,但要先观察真实工作负载。

总结#

索引优化本质上是减少不必要的数据访问,事务设计则控制并发可见性和不变量。执行计划、锁等待和真实数据分布是证据;索引口诀只能作为排查起点。

MySQL 索引、事务与慢 SQL 优化
https://example.com/posts/mysql-index-and-transaction/
作者
koyomi315
发布于
2026-03-03
许可协议
CC BY-NC-SA 4.0

一只小鲸鱼正在游过来…

ⓘ
DeepSeek 同人鲸鱼娘

角色原作:上善无形
二次设计:ZipZipPipe
Live2D:氵六青

素材未修改 · CC BY-NC-SA 4.0

模型来源 ↗