本篇聚焦 MySQL 索引优化技巧与实战案例，覆盖从慢查询定位到生产环境的性能提升要点。通过对原理的回顾、诊断流程的梳理、索引设计的要点，以及真实案例的落地演示，帮助读者在实际系统中实现稳定的性能提升。
1. MySQL 索引原理与设计要点
1.1 B树、聚簇索引与唯一性
B树是 MySQL 索引的底层结构，它决定了数据库在最短路径内定位数据的能力。需要注意的是，在 InnoDB 存储引擎中，聚簇索引将表数据物理排序在主键键值上，导致主键不仅是数据定位的入口，也决定了记录在磁盘上的存储顺序。为常用查询提供快速定位的同时，二级索引包含主键指针，若查询只需覆盖列且能通过覆盖索引返回结果，可以避免回表提升性能。理解这一点对于设计高效查询至关重要。
设计要点包括尽量让高频查询的条件落在一个前导列上，避免在列上对比函数、进行隐式转换，确保索引能够被使用。唯一性约束还能帮助优化器更好地判断统计信息，提升执行计划的稳定性。
1.2 复合索引与前导列
当查询条件涉及多列时，复合索引的列序必须与查询的筛选条件顺序一致，否则可能无法充分利用索引。前导列的选择直接影响覆盖能力，若前导列满足大部分查询的过滤条件，后续列的条件就能更高效地使用索引。此处的关键在于分析典型查询路径并将最常用的筛选条件放在前面。
在设计时应结合查询日志与慢查询日志，确保 复合索引的顺序与实际业务查询模式对齐，避免出现“只用到前几列”的低效情况。同时，合理使用前缀索引以节省存储但仍保持查询需求的覆盖性，例如对长字符列的前缀索引在某些场景下可显著减少索引大小。
2. 从慢查询到优化的实战流程
2.1 使用 EXPLAIN 诊断慢查询
当遇到慢查询时，使用 EXPLAIN（FORMAT=JSON）查看执行计划是第一步。通过分析type、key、rows、 Extra等字段，可以判断是否走了全表扫描、是否使用了正确的索引、以及是否需要回表。type 的取值范围（如 const, range, ref, index, all）直接反映了查询的成本梯度。
在分析阶段，优先关注以下要点：是否使用了覆盖索引、是否发生回表、以及索引选择性是否符合预期。如果发现没有使用到索引，且查询条件符合索引字段，则需要考虑重写查询、调整索引顺序或添加合适的复合索引。
EXPLAIN FORMAT=JSON
SELECT o.id, o.total
FROM orders o
WHERE o.status = 'PAID' AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 100;通过 JSON 结果可以看到使用的索引、是否有隐式排序、以及过滤条件的应用情况，从而明确改动方向。
2.2 构建高效的复合索引与覆盖索引
在确认慢查询的瓶颈后，创建合适的复合索引并尽量做到覆盖查询，可以将多次访问的数据行合并在一个索引中返回，避免回表带来的额外 IO。设计要点包括：
将高基数的列放在复合索引的前导列，并确保查询条件的顺序与索引列的顺序一致；在可能的情况下，让查询能返回所需的列，同时落在同一个索引结构中。以下示例展示了创建一个覆盖查询的复合索引的做法。
CREATE INDEX idx_orders_status_created ON orders (status, created_at, id);SELECT id, created_at, status
FROM orders
WHERE status = 'PAID' AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 100;覆盖索引的本质是：查询所需的列均包含在索引中，从而避免对实际数据行的随机读写。若查询中的列都在索引中，则 ORDER BY、LIMIT 的数据行也可能来自索引本身，进一步提升性能。
在实际落地时，亦可通过 USE INDEX/FORCE INDEX 提示优化器选取更合适的索引路径，结合监控观察改动后的执行计划是否对业务路径有实质提升。
3. 生产环境的稳定提升方案
3.1 避免查询中的函数和隐式转换
生产环境中，对列使用函数、隐式类型转换或函数式索引表达式往往会使优化器放弃对现有索引的使用，导致全表扫描。应尽量将筛选条件改写为对列直接比较的形式，并确保列的类型与查询参数类型一致。
示例中，避免这样写：
SELECT * FROM events WHERE DATE(event_time) = '2024-08-01'应改写为：让 where 子句直接针对列的范围筛选，例如指定时间区间，这样就能利用时间字段的索引。
3.2 针对热点数据的分区与分表策略
对于数据量极大且被频繁访问的表，可以考虑采用分区（PARTITION）或分表策略，以减少单次查询需要扫描的行数。分区的好处在于查询可以只扫描相关分区，减少了 I/O 与缓冲区命中成本。合适的分区策略包括基于时间戳的 RANGE 分区、基于哈希的 HASH 分区等。
CREATE TABLE logs (id BIGINT PRIMARY KEY,ts DATETIME,level VARCHAR(16),message TEXT
) PARTITION BY RANGE (TO_DAYS(ts)) (PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01'))-- 继续按月分区
);分区也需注意维护成本，如全表跨分区查询的代价、跨分区的排序和聚合等，应结合实际业务查询模式综合评估。
3.3 在线结构变更与监控
在生产环境中，在线结构变更和零停机部署是提升可用性与稳定性的关键。使用工具如 pt-online-schema-change、gh-ost 等，可以在不锁表的情况下完成新增列、修改列类型、添加索引等操作。
pt-online-schema-change --alter "ADD COLUMN archived TINYINT(1) NOT NULL" D=shop,t=logs --execute同时，持续的监控也是必要的：如监控慢查询日志、执行计划的稳定性、以及系统资源的瓶颈点。常用指标包括 Queries per second、Threads_running、Buffer_pool_read_requests 等，通过对比变更前后的趋势，判断优化是否落地。
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW ENGINE INNODB STATUS;通过这些监控与变更记录，可以确保在生产环境中的优化不会带来新的问题，并能快速定位回滚路径以保障系统稳定。
