何时创建索引？适合在频繁查询的where条件列、join连接列、排序或分组字段上创建索引；2. 如何判断索引是否有效？通过explain命令查看执行计划、分析慢查询日志、验证查询与索引匹配度；3. 哪些情况会导致索引失效？如使用函数、like前导通配符、类型转换、or部分无索引、大量数据返回时；4. 优化建议包括使用覆盖索引、避免冗余索引、使用前缀索引、合理决策索引添加、平衡读写性能。
在MySQL数据库优化中，索引的使用是提升查询效率最直接、最有效的方式之一。但很多人对索引的理解还停留在“加了就快”的层面，实际上，不同场景下索引的创建和使用方式差异很大。这篇文章主要从实际操作角度出发，分析几种常见的MySQL索引优化场景，并给出对应的建议。
什么时候应该创建索引？
索引不是越多越好，也不是所有字段都适合建立索引。通常来说，以下几种情况更适合创建索引：
- 频繁出现在WHERE条件中的列：比如用户ID、订单状态等经常用于筛选数据的字段。
- 用于JOIN连接的列：如果两张表经常通过某个字段进行关联，那么这个字段最好有索引。
- 排序或分组的字段：比如ORDER BY或GROUP BY常用的字段，添加索引可以避免额外的排序操作。
举个例子，假设你有一个用户表users，其中有个字段是email，如果你的应用系统里经常需要根据邮箱查找用户，那这个字段就很适合建索引。
注意：不要给低选择性的字段（如性别、是否启用）建普通B+树索引，因为这类字段区分度太低，索引几乎起不到加速作用。
如何判断现有索引是否有效？
很多开发人员建完索引后就不去验证效果了，这是非常常见的误区。判断索引是否有效，可以通过以下几个方法：
- 使用
EXPLAIN命令查看SQL执行计划，观察是否命中索引； - 查看慢查询日志，找出哪些查询没有走索引；
- 分析表结构和查询语句之间的匹配程度。
例如，执行以下语句查看执行计划：
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
如果结果中的key列显示为NULL，说明没有用到索引；如果有值，说明索引被正确使用了。
另外，还要注意复合索引的最左前缀原则。比如你建立了(user_id, status)的联合索引，那么查询中必须包含user_id才能使用该索引。只查status的话，是无法命中这个联合索引的。
哪些情况下索引会失效？
即使你建立了合适的索引，也有可能因为写法不当而导致索引失效。以下是一些常见原因：
- 使用函数或表达式处理索引列，比如
WHERE YEAR(create_time) = 2024； - 使用
LIKE以通配符开头，比如LIKE '%abc'； - 对索引列进行类型转换，比如字符串字段传入数字；
- 查询中使用
OR且部分条件无索引； - 查询返回大量数据，优化器认为全表扫描更快。
这些问题往往容易被忽略，特别是在动态拼接SQL的情况下，更要注意生成的语句是否规范。
索引优化的一些实用建议
结合上述内容，这里再总结几个比较实用的优化建议：
- 尽量使用覆盖索引（即查询字段都在索引中），减少回表操作；
- 避免冗余索引，定期清理无效索引；
- 对于大表，考虑使用前缀索引，尤其适用于长文本字段；
- 不要盲目给每个WHERE条件加索引，而是根据查询频率和数据分布来决策；
- 考虑读写平衡，频繁更新的字段不适合建立过多索引。
基本上就这些。索引优化看似简单，但真正做好的人并不多。关键在于理解原理、结合实际业务场景去调整策略。
