掌握了索引基本原理和常见失效场景后，就可以系统性地应用一些优化技巧，在查询速度和空间占用之间找到平衡。下面这些技巧都不是花架子，而是生产环境里实打实能提升性能的做法。
12.4.1 覆盖索引：用索引直接回答查询
原理
InnoDB 的二级索引叶子节点存放的是“索引键 + 主键值”。当查询所需的所有列都包含在某个索引中时，InnoDB 可以直接从索引中获取全部数据，不需要再回表到聚簇索引中读取完整行记录。这就是覆盖索引（Covering Index）。
回表操作是随机磁盘 I/O 的重要来源之一，尤其是当查询返回大量行时，每次回表可能访问不同的数据页。覆盖索引彻底消除了这一步，查询效率通常会提升数倍甚至数十倍。
具体做法
把查询中涉及的所有列都加入到索引中，但不必把所有列都建成单列索引——合理利用联合索引的“左前缀”特性即可。
示例
假设有订单表 orders，主键是 id，经常执行以下查询：
SELECT order_no, user_id, amount
FROM orders
WHERE user_id = 88215
AND status = 'paid';如果只给 user_id 和 status 建联合索引 idx_user_status (user_id, status)，查询过程是：先在 idx_user_status 中定位到符合条件的记录，得到对应的主键 id，再根据 id 回表取出 order_no 和 amount 列。
如果把索引修改为 idx_user_status_cover (user_id, status, order_no, amount)，将 order_no 和 amount 也拼在索引的后面，那么这个索引就“覆盖”了查询所需的全部列。执行计划中 Extra 列会显示 Using index，表示没有发生回表。
注意事项
- 不要为了一个查询把表中十几个列全加到索引里，索引过大会占用更多磁盘和内存，还会拖慢写入速度。
- 覆盖索引最适合高频、返回列少的查询。返回列越多，索引覆盖面越难实现。
- 如果经常用
SELECT，覆盖索引将无从谈起，严格禁止在业务代码中使用SELECT** 不仅是为了可维护性，也直接关系到索引优化的效果。
12.4.2 索引下推（ICP）：让存储引擎更聪明地过滤
原理
索引条件下推（Index Condition Pushdown，ICP）是 MySQL 5.6 引入的一项优化。在没有 ICP 时，查询执行过程是：存储引擎根据索引条件找到所有符合索引范围的行，将完整的行记录返回给 MySQL 服务层，由服务层再根据 WHERE 中的其他过滤条件进行判断。
开启 ICP 后，服务层会把那些能在索引中直接判断的 WHERE 条件下推给存储引擎。存储引擎在遍历索引时，如果发现某条记录不满足这些下推条件，就直接跳过，不再回表。这样既减少了回表次数，也减少了返回给服务层的数据量。
如何识别
执行计划中 Extra 列会显示 Using index condition。
示例
仍然是 orders 表，建有联合索引 idx_user_status (user_id, status)，执行查询：
SELECT * FROM orders
WHERE user_id > 10000
AND status = 'paid';- 无 ICP（5.6 前行为）：存储引擎使用
idx_user_status扫描所有user_id > 10000的索引记录，每条都回表取完整行交给服务层，服务层再判断status = 'paid'。如果user_id > 10000的条数很多但大部分状态不是paid，回表量将极其巨大。 - 有 ICP：服务层把
status = 'paid'条件下推给存储引擎。存储引擎在扫描idx_user_status时，判断索引键中的status是否为paid，如果不满足，直接跳过，不会回表。而status恰好是这个联合索引的第二列，完全可以在索引内部判断。
关键点
- ICP 只能下推与当前使用索引相关的条件。若条件中引用了索引不包含的列，无法下推。
- 对覆盖索引场景，ICP 意义不大，因为本来就不会回表。
- ICP 默认开启（
optimizer_switch中index_condition_pushdown=on），一般情况下不要关闭。
12.4.3 前缀索引：用小代价索引长字符串
场景
当需要对 VARCHAR、TEXT、BLOB 等较长字符串列建索引时，全列索引会占用非常大的空间。例如，一个存储文章 URL 的列 url VARCHAR(2000)，如果直接建 INDEX(url)，索引可能会比表本身还大，并且一个数据页能存放的键值数量极少，增加索引树的层高和 I/O。
方法
只对列的前面若干个字符建立索引，这种索引就是前缀索引。
ALTER TABLE articles ADD INDEX idx_url_pref(url(50));这里的 50 是对 url 列前 50 个字符建立的索引长度。
如何选择前缀长度
核心指标是索引选择性（Index Selectivity）：不重复的索引值与总记录数的比值。选择性越高，索引的区分度越好。
可以通过以下 SQL 计算不同前缀长度的选择性：
SELECT COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) AS sel_20,
COUNT(DISTINCT LEFT(url, 30)) / COUNT(*) AS sel_30,
COUNT(DISTINCT LEFT(url, 50)) / COUNT(*) AS sel_50,
COUNT(DISTINCT LEFT(url, 100)) / COUNT(*) AS sel_100
FROM articles;一般来说，选择性接近 1 是理想状态，但考虑到空间成本，只需要选择一个可以让选择性足够大的最小长度。对于 URL 这类数据，通常取 30~100 字节就足够。
注意事项
- 前缀索引无法用于
ORDER BY和GROUP BY，因为索引中只保存了不完整的前缀，无法以这个前缀确定排序顺序。 - 前缀索引也不能作为覆盖索引，因为索引键中缺少列后缀的完整信息，必须回表。
- 前缀索引对写入速度也有少量影响，但相比全列索引，空间节省的效果往往远大于这点开销。
替代方案
如果前缀长度难以平衡，或者需要利用索引进行排序和覆盖查询，可以考虑额外创建一个计算列，存储字符串的哈希值（如 crc32），并对哈希列建普通索引。查询时同时使用哈希列和原列来精确匹配，但这会带来额外的存储和哈希冲突处理成本，需慎重评估。
12.4.4 弥合索引缺口的其他实用技巧
掌握了覆盖索引、ICP 和前缀索引这三个核心技巧，你已经能解决大部分索引性能问题。还有一些容易忽视的优化点，值得在日常开发中留意：
1. 利用联合索引的“扩展”作用
与其建多个单列索引，不如将高频查询条件设计成一个联合索引，其他查询可以利用该索引的左前缀。例如 INDEX(a,b,c) 已经相当于有了 (a)、(a,b) 和 (a,b,c) 三个索引。这样就可以避免维护多个冗余索引。
2. 定期清理无用索引
使用 sys.schema_unused_indexes 视图（MySQL 8.0）或 performance_schema.table_io_waits_summary_by_index_usage 表来识别长期未被使用的索引。无用索引不仅占空间，还拖慢写入性能。确认无用后，及时删除。
3. 用不可见索引安全地废弃索引
MySQL 8.0 支持 ALTER TABLE ... ALTER INDEX idx_name INVISIBLE。将索引设为不可见后，优化器不会使用它，但索引数据仍然维护。观察一段时间，确认没有查询变慢后再真正删除，避免白天删索引晚上报警的惨剧。
4. 避免在索引列上使用函数或运算
这个属于“失效场景”，但值得反复强调。WHERE DATE(create_time) = '2025-03-20' 会导致索引失效。应当改写为范围查询：
WHERE create_time >= '2025-03-20 00:00:00'
AND create_time < '2025-03-21 00:00:00';这样就能完美利用 create_time 上的索引。
5. 使用 force index 要谨慎
有时优化器选错索引，你可能忍不住 force index(idx_name)。这可以临时应急，但长期维护容易出问题（比如数据分布变化后强制索引反而变慢）。更好的做法是：分析为什么优化器选错（统计信息是否过时、索引选择性是否变了），通过 ANALYZE TABLE 或修正 SQL 来解决。
索引优化不是一项独立工作，它跟 SQL 写法、表结构设计、业务场景紧密耦合。好的索引是设计出来的，不是“加”出来的。 在创建索引那一刻，就要明确它要服务的查询模式，才能让前述的覆盖索引、ICP、前缀索引等技巧真正发挥作用。