MySQL索引失效和索引是不是越多越好


> 给我详细介绍mysql索引和索引失效情况及处理举措，最后介绍索引是不是越多越好



一、MySQL 索引是什么？

1. 定义

索引（Index）是数据库中一种特殊的数据结构，用于快速定位和访问表中的数据行，类似于书籍的目录。它通过建立“列值 ↔ 行位置”的映射关系，避免全表扫描，显著提高查询效率。

2. 索引的本质

  • 有序的数据结构：MySQL 默认使用 B+树（B-Tree 的变种）作为索引结构。
  • 特点：
  • 支持范围查询（如 >, <, BETWEEN）
  • 叶子节点形成链表，支持顺序遍历
  • 高扇出，树高度低，通常3~4层即可支撑上亿数据


二、MySQL 常见索引类型

类型说明使用场景
主键索引（PRIMARY KEY）唯一且非空，一张表只能有一个主键字段自动创建
唯一索引（UNIQUE）列值唯一，允许一个 NULL用户名、邮箱等唯一约束
普通索引（INDEX）最基本的索引，无唯一性要求普通查询字段
复合索引（Composite Index）多个列组合的索引，遵循最左前缀原则多条件查询如 (user_id, status)
全文索引（FULLTEXT）支持文本内容的关键词搜索文章标题、内容搜索（MyISAM/InnoDB 支持）
空间索引（SPATIAL）用于地理数据类型GIS 应用
⚠️ InnoDB 引擎的主键索引是聚簇索引（Clustered Index），数据行按主键顺序存储；非主键索引是二级索引（Secondary Index），叶子节点存储主键值，需回表查询完整数据。


三、索引失效的常见场景及原因

即使建立了索引，如果 SQL 写法不当，优化器也可能放弃使用索引，导致全表扫描。以下是典型的索引失效场景：

✅ 场景1：违反最左前缀原则（复合索引）

sql深色版本-- 假设索引为 (a, b, c)
SELECT * FROM t WHERE b = 2 AND c = 3; -- ❌ 不走索引（缺少 a）
SELECT * FROM t WHERE a = 1 AND c = 3; -- ⚠️ 只能用到 a，c 无法利用索引排序
原因：B+树是按 (a,b,c) 排序的，缺少最左列 a，无法定位起始点。

✅ 场景2：对索引列使用函数或表达式

SELECT * FROM users WHERE YEAR(create_time) = 2024; -- ❌
SELECT * FROM users WHERE id + 1 = 100;             -- ❌
原因：索引存储的是原始值，函数改变了列值，无法直接匹配。

✅ 正确写法：

SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

✅ 场景3：隐式类型转换

-- name 是 VARCHAR 类型
SELECT * FROM users WHERE name = 123; -- ❌
原因：MySQL 会将 name 转为数字比较，相当于对列使用函数，破坏索引。

✅ 场景4：使用前导通配符的 LIKE

sql深色版本SELECT * FROM users WHERE name LIKE '%abc'; -- ❌ 全表扫描
SELECT * FROM users WHERE name LIKE 'abc%'; -- ✅ 可以走索引
原因：%abc 无法利用索引的有序性。

✅ 场景5：OR 条件中部分无索引

sql深色版本-- 只有 a 有索引，b 无索引
SELECT * FROM t WHERE a = 1 OR b = 2; -- ❌ 可能全表扫描
原因：优化器认为 OR 中无索引部分会导致大量回表，不如直接全表扫描。

✅ 替代方案：使用 UNION

sql深色版本SELECT * FROM t WHERE a = 1
UNION
SELECT * FROM t WHERE b = 2;

✅ 场景6：范围查询后跟其他条件

sql深色版本-- 索引 (a, b, c)
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3; -- c 无法用索引排序
原因：b > 2 是范围查询，之后的 c 无法再利用索引的有序性。

✅ 建议：调整索引顺序，如 (a, c, b)，让等值查询在前。

✅ 场景7：查询所有数据或低区分度字段

sql深色版本SELECT * FROM users WHERE status = 1; -- status 只有两个值（0/1），区分度低
原因：如果满足条件的数据超过全表 10%~20%，优化器可能认为全表扫描更快。


四、如何处理索引失效问题？

1. 使用 EXPLAIN 分析执行计划

  • 确认是否走了索引（key 字段）、扫描行数（rows）、是否回表（Using index vs Using where）。

2. 重构 SQL 或调整索引

  • 避免函数操作，改写为范围查询。
  • 调整复合索引顺序，优先等值、高区分度字段。
  • 使用覆盖索引减少回表。

3. 添加合适的索引

  • 根据高频查询模式设计复合索引。
  • 对大表的外键、状态字段等考虑加索引。

4. 定期维护统计信息

sql深色版本ANALYZE TABLE table_name;
  • 更新表的统计信息，帮助优化器做出更优的执行计划选择。

5. 避免过度索引

  • 删除长期未使用的索引，减少维护成本。


五、索引是不是越多越好？—— 答案：❌ 不是！

虽然索引能加速查询，但索引不是越多越好，过多索引会带来以下问题：

⚠️ 1. 写性能下降

  • 每次 INSERT、UPDATE、DELETE 都需要同时更新对应索引。
  • 索引越多，写操作越慢，尤其在高并发写入场景下明显。

⚠️ 2. 存储空间增加

  • 每个索引都是一棵独立的 B+ 树，占用磁盘和内存。
  • 大表的索引可能比数据本身还大。

⚠️ 3. 优化器选择困难

  • 索引过多可能导致优化器在多个执行计划间犹豫，甚至选错（如本该走索引却全表扫描）。

⚠️ 4. 维护成本高

  • 索引需要定期重建、分析、监控。
  • 项目迭代中容易产生“僵尸索引”（不再使用的索引）。


✅ 正确的索引设计原则（黄金法则）

  1. 按需创建：只为高频查询、过滤条件、JOIN、排序分组字段建索引。
  2. 优先复合索引：避免多个单列索引，合理设计复合索引，遵循最左前缀。
  3. 区分度优先：优先为区分度高的列建索引（如用户ID > 状态字段）。
  4. 覆盖索引优化：尽量让查询字段都在索引中，避免回表。
  5. 定期清理：通过 performance_schema.table_io_waits_summary_by_index 查看索引使用情况，删除无用索引。


总结：索引使用口诀

“索引如刀，利刃双面； 查询快了，写入变慢； 最左前缀要记牢，函数通配全失效； 覆盖索引最高效，回表代价要避免； 不是越多就越好，按需设计才可靠。”

掌握这些知识，你就能在开发和运维中科学地使用 MySQL 索引，既提升性能，又避免陷阱。如果有具体 SQL 或表结构，欢迎提供，我可以帮你做针对性分析。