MySQL 索引、事务与慢 SQL 优化
数据库优化的目标不是让每条 SQL 都“走索引”,而是用更少的 IO、锁和计算完成正确查询。索引、事务和执行计划需要放在同一个成本模型中理解。
为什么常见索引使用 B+ 树
B+ 树适合磁盘和页式存储:
- 分支多,树高较低;
- 非叶子节点主要保存导航键,可以容纳更多分支;
- 叶子节点有序,适合范围扫描;
- 查找和范围遍历都能以较少页访问完成。
哈希结构擅长等值查询,但不天然支持有序范围、前缀排序和最左匹配,因此不能替代通用有序索引。
聚簇索引与二级索引
在 InnoDB 中,主键索引的叶子节点保存整行记录。二级索引叶子节点通常保存索引列和主键值。
使用二级索引查询其他列时,可能先找到主键,再回到聚簇索引读取整行,这就是常说的回表。
如果查询所需字段都能从二级索引得到,就形成覆盖索引,减少额外页访问。但为了覆盖所有查询而创建超宽索引,会增加写放大和缓存压力。
联合索引与最左前缀
联合索引按列顺序排序。例如 (tenant_id, status, created_at) 可以高效支持以 tenant_id 开头的条件组合。
设计顺序时要结合:
- 等值条件;
- 范围条件;
- 排序和分组;
- 区分度;
- 真实查询频率。
一旦进入范围扫描,后续列能否继续用于定位取决于具体条件和优化器策略,不能只背“范围后全部失效”的绝对口诀。
常见索引利用问题
- 对索引列做无法改写的函数计算;
- 隐式类型转换;
- 联合索引没有可用前缀;
- 前导通配符模糊匹配;
- 返回数据占比过高,优化器认为全表扫描更便宜;
- 排序、分组与索引顺序不匹配;
- 统计信息不足或分布倾斜。
“没有使用索引”不一定是错误。小表或需要返回大部分行时,全表扫描可能更合理。
事务隔离解决什么
事务提供原子性、一致性、隔离性和持久性。隔离级别在并发能力和可见性之间取舍。
常见现象包括:
- 脏读:读到尚未提交的数据;
- 不可重复读:同一事务两次读取同一行得到不同值;
- 幻读:同一条件查询出现新的行集合。
具体行为还受到数据库实现、快照读、当前读和锁策略影响,不能只用一张理论表解释所有 SQL。
MVCC 的基本思路
MVCC 通过版本信息和一致性视图,让读取在很多情况下不必阻塞写入。事务根据自己的视图判断哪个版本可见。
MVCC 不意味着所有查询都不加锁:修改语句、显式锁定读和部分约束检查仍需要锁。长事务还会阻碍旧版本清理,增加存储和维护成本。
用执行计划而不是猜测
分析慢 SQL 时,执行计划重点观察:
- 访问方式;
- 实际选择的索引;
- 预计与实际扫描行数;
- 过滤比例;
- 排序、临时表和回表;
- 连接顺序;
- 执行时间分布。
如果环境支持实际执行分析,可以比较优化器估计与真实行数。估计严重偏差时,应检查统计信息和数据分布,而不是立刻强制索引。
慢 SQL 的排查流程
- 从慢日志或追踪定位具体 SQL 和参数模式;
- 确认慢是稳定发生还是偶发锁等待;
- 查看执行计划和扫描行数;
- 检查索引、统计信息和返回数据量;
- 分析事务与锁;
- 在接近生产分布的数据上验证修改;
- 观察优化后 CPU、IO、延迟和写入成本。
只在开发环境用少量数据执行很快,不能证明线上计划合理。
索引的代价
每个索引都会带来:
- 写入时维护成本;
- 额外存储;
- 缓冲池占用;
- 优化器选择空间;
- DDL 和数据迁移成本。
应删除长期不使用且与其他索引重复覆盖的索引,但要先观察真实工作负载。
总结
索引优化本质上是减少不必要的数据访问,事务设计则控制并发可见性和不变量。执行计划、锁等待和真实数据分布是证据;索引口诀只能作为排查起点。