一、理解索引的底层逻辑：为什么B+树是MySQL的选择？
要优化索引，首先得明白索引是怎么工作的。你可以把数据库表想象成一本厚书，索引就像这本书的目录——没有目录时，找内容只能逐页翻（全表扫描）；有了目录，就能直接定位到具体章节（索引扫描）。
MySQL最常用的存储引擎InnoDB，默认使用B+树作为索引结构。为什么是B+树？这得从它的特性说起：
- 层级结构：B+树的非叶子节点只存储索引键和指向子节点的指针，不保存数据；所有数据都存放在叶子节点，且叶子节点通过双向链表连接。这种设计让单次查询的IO次数稳定（由树的高度决定），范围查询时还能通过链表快速遍历。
- 高扇出性：每个节点可以存储多个索引键（具体数量取决于索引列大小和页大小），这使得树的高度非常低。比如，假设每个节点存1000个键，3层B+树就能存储10亿级数据（1000×1000×1000），查询时最多只需3次IO。
举个具体例子：一张用户表（id INT, name VARCHAR(20), age INT），假设我们为(name, age)建立联合索引。B+树的非叶子节点会按name排序存储键值，每个键指向子节点；叶子节点则存储完整的(name, age)组合，以及对应的行记录指针（InnoDB中是主键值）。当执行SELECT id FROM user WHERE name='张三' AND age=25时，索引会先按name快速定位到"张三"的区间，再在该区间内按age筛选，最终通过主键回表获取id。
二、基础优化思路：从查询需求倒推索引设计
很多人设计索引时容易陷入一个误区：“先给所有可能查询的列都加上索引”。但索引不是越多越好——每个索引都会增加写操作的开销（插入/更新/删除时需要维护索引结构），还可能因为冗余索引浪费存储空间。正确的思路应该是**“基于实际查询需求，精准设计索引”**。
2.1 优先覆盖高频查询条件
假设你的业务中，用户最常执行的查询是：
SELECT order_id, amount FROM orders WHERE user_id=12345 AND status='paid' ORDER BY create_time DESC LIMIT 10;
这时候，应该围绕user_id、status、create_time设计索引。观察查询条件：user_id是等值查询，status是等值过滤，create_time用于排序和分页。最优的索引应该是(user_id, status, create_time)，原因有三：
- 最左匹配原则：联合索引会从左到右依次匹配条件。
user_id作为第一列，能快速定位到该用户的所有订单；第二列status进一步缩小范围；第三列create_time直接满足排序需求（避免文件排序）。 - 覆盖索引：如果索引包含查询所需的所有列（这里索引包含
user_id、status、create_time，而查询需要order_id、amount，但InnoDB的二级索引叶子节点存储的是主键值，所以需要回表获取amount。如果想完全覆盖，可以考虑将amount加入索引，即(user_id, status, create_time, amount)，这样查询时无需回表。 - 避免冗余排序：当索引的顺序与ORDER BY的顺序一致时，MySQL可以直接利用索引的有序性返回结果，无需额外的排序操作。
2.2 区分度高的列优先
索引的效率很大程度上取决于列的区分度（即列中不同值的数量与总行数的比值）。比如，性别列（只有男/女）的区分度低，而用户ID（唯一）的区分度高。如果必须在联合索引中选择列顺序，应该让区分度高的列排在前面。
举个反例：一张日志表有log_type（区分度低，只有5种类型）和log_time（区分度高，精确到秒），如果查询是WHERE log_type='error' AND log_time>'2024-01-01'，把log_time放在前面的索引(log_time, log_type)会比(log_type, log_time)更高效吗？
答案是否定的。因为log_type是等值查询，而log_time是范围查询。根据最左匹配原则，(log_type, log_time)可以先通过log_type快速定位到error类型的日志，再在该范围内按时间筛选；而(log_time, log_type)虽然区分度高，但范围查询后的列无法使用索引（B+树在范围查询后会停止匹配后续列）。这说明区分度优先级需结合查询条件的类型（等值/范围）综合判断。
2.3 避免冗余索引
冗余索引是指功能被其他索引完全覆盖的索引。例如：
- 已有索引
(a, b)，再创建(a)就是冗余的（因为(a, b)的最左前缀已经包含a）； - 已有索引
(a, b, c)，再创建(a, c)也是冗余的（(a, b, c)的前两列是a, b，但(a, c)无法被(a, b, c)覆盖，这里需要注意：只有当索引的前缀完全包含另一个索引时才是冗余的，比如(a, b)和(a)是冗余，而(a, c)和(a, b, c)不是）。
如何检测冗余索引？可以通过sys库的schema_redundant_indexes视图（需要MySQL 5.7+且安装了sys库），它会列出被其他索引完全覆盖的冗余索引，帮助我们清理不必要的索引。
三、进阶优化：避开常见的索引失效场景
即使索引设计合理，实际使用中也可能因为错误的写法导致索引失效。以下是最常见的几种场景及解决方案：
3.1 类型不匹配导致的隐式转换
假设user_id是INT类型，但查询时写成WHERE user_id='12345'（字符串形式），MySQL会尝试将user_id列转换为字符串进行比较，这会导致索引失效。
解决方案：严格保持查询条件与列的数据类型一致，避免隐式转换。如果业务中确实需要传入字符串（比如接口参数），可以在应用层做类型转换，或者在数据库层使用CAST(user_id AS CHAR)（但这会导致索引失效，所以更推荐应用层处理）。
3.2 对索引列使用函数或表达式
例如SELECT * FROM orders WHERE YEAR(create_time)=2024，这里对create_time使用了YEAR()函数，MySQL无法使用create_time的索引。
解决方案：改写为范围查询WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'，这样可以利用索引的范围扫描。如果需要频繁按年查询，也可以考虑新增一个year列（通过触发器或应用层写入时维护），并为该列建立索引。
3.3 前导通配符的LIKE查询
SELECT * FROM product WHERE name LIKE '%手机%'这种以通配符开头的模糊查询，无法使用name列的索引（因为B+树是按前缀排序的，无法定位到中间部分）。
解决方案：
- 如果业务允许，尽量使用
LIKE '手机%'（后缀通配符），这样可以利用索引； - 对于必须前后都有通配符的场景，可以考虑使用全文索引（MySQL 5.6+支持InnoDB的全文索引），或者引入Elasticsearch等外部搜索引擎。
3.4 范围查询后的索引列失效
在联合索引(a, b, c)中，如果查询条件是a=1 AND b>2，那么c列的索引会失效（因为B+树在范围查询后无法继续使用后续列的有序性）。
解决方案：调整索引顺序，将范围查询的列放在最后。例如，如果查询是a=1 AND c=3且b>2，可以将索引改为(a, c, b)，这样a和c作为等值查询，b作为范围查询，就能充分利用索引。
四、动态监控
索引优化不是一次性的工作，随着数据量增长和业务变更，索引的使用情况会动态变化。以下是几个关键的维护手段：
4.1 用EXPLAIN分析执行计划
EXPLAIN是诊断索引使用情况的核心工具。执行EXPLAIN SELECT ...后，重点关注以下字段：
type：表示访问类型，最优的是const（单条记录），其次是ref（等值匹配），range（范围扫描），最差的是ALL（全表扫描）；key：实际使用的索引；rows：MySQL估计需要扫描的行数，数值越小越好；Extra：如果出现Using filesort（需要文件排序）或Using temporary（使用临时表），通常意味着索引设计有优化空间。
例如，当Extra显示Using index，说明使用了覆盖索引，无需回表，这是理想状态；如果显示Using where; Using index condition，则表示使用了索引下推（ICP，Index Condition Pushdown），这是MySQL 5.6+的优化特性，能在索引扫描时过滤部分条件，减少回表次数。
4.2 定期分析索引使用情况
MySQL的performance_schema库中记录了索引的使用信息。通过查询table_io_waits_summary_by_index_usage视图，可以找到从未被使用过的索引（count_star=0），这些索引可以安全删除。
另外，对于频繁更新的表（如订单表），索引的碎片会逐渐增加（B+树的叶子节点可能出现空洞），导致索引扫描效率下降。可以通过OPTIMIZE TABLE命令重建索引（InnoDB会自动优化，但高并发场景下建议在低峰期执行），或者使用ALTER TABLE ... FORCE重建表（效果类似OPTIMIZE TABLE）。
4.3 结合业务场景动态调整
比如，某电商大促期间，订单表的查询从“按用户ID查询”变为“按商品ID查询”，这时候需要评估现有索引是否还能满足需求，可能需要新增(product_id, create_time)索引。再比如，当某张表的写操作（INSERT/UPDATE/DELETE）明显多于读操作时，应尽量减少索引数量（因为写操作需要维护所有索引）。