MySQL索引

MySQL 索引详解

什么是 MySQL 索引

索引是帮助 MySQL 快速查找数据的数据结构，它通过某种方式指向数据，并基于该结构实现高效查找算法，从而加速数据检索。

MySQL 默认使用的是 B-Tree 索引（实际结构为 B + 树），这是最常用的索引类型。

索引的分类

1. 普通索引

最基础的索引，无任何约束，仅用于加速查询。

2. 唯一索引

与普通索引类似，但附加唯一性约束（允许空值，但空值只能出现一次），可避免数据重复。

3. 主键索引

特殊的唯一索引，不允许空值，每张表只能有一个主键索引。MySQL 会自动为主键创建主键索引。

4. 复合索引

将多个列组合创建的索引，可覆盖多列查询条件，查询时需遵循 “最左前缀法则”。

5. 外键索引

仅 InnoDB 引擎支持，用于维护表之间的关联关系，保证数据的一致性、完整性，并支持级联操作。

6. 全文索引

用于全文检索，MySQL 自带的全文索引仅支持 InnoDB（5.6+）和 MyISAM 引擎，且对中文支持有限（5.7 + 通过 ngram 插件支持）。实际场景中，更常用专业全文索引引擎（如 Elasticsearch、Solr）。

索引的操作语法

创建索引

-- 方式1：CREATE语句
CREATE [UNIQUE] INDEX 索引名 ON 表名(列名);

-- 方式2：ALTER语句
ALTER 表名 ADD [UNIQUE] INDEX 索引名 ON (列名);

• UNIQUE为可选参数，用于创建唯一索引。

删除索引

DROP INDEX 索引名 ON 表名;

查看索引

SHOW INDEX FROM 表名;

该命令会返回索引的详细信息，包括索引名、列名、Cardinality 等。

Cardinality 的含义

SHOW INDEX FROM 表名;的结果中，Cardinality列表示索引中不重复记录数量的预估值，用于判断索引的有效性：

• 理想情况下，Cardinality / 表行数应接近 1（值越大，重复值越少）。
• 若值过小（如远小于 1），说明字段重复值多，不适合作为索引。

适合与不适合创建索引的场景

适合创建索引的场景

1. 主键字段（MySQL 自动创建主键索引）。
2. 频繁作为查询条件的字段（如WHERE子句中的字段）。
3. 外键字段（维护关联关系，加速关联查询）。
4. 用于排序的字段（索引本身有序，可避免Using filesort，提升排序效率）。
5. 用于统计或分组的字段（如GROUP BY、COUNT()中的字段）。

不适合创建索引的场景

1. 频繁更新的字段（索引会随数据更新而重建，增加开销）。
2. 从不作为查询条件的字段（索引无法发挥作用，浪费空间）。
3. 表记录极少时（全表扫描效率已足够高）。
4. 字段值重复率高且分布平均（如性别字段，索引过滤效果差）。

索引失效的常见情况

索引失效会导致查询无法使用索引，转而进行全表扫描，大幅降低效率。以下是常见的失效场景：

1. 复合索引违反 “最左前缀法则”

复合索引

复合索引（如(a, b, c)）的查询条件需按索引创建顺序使用，否则失效：

• 例：索引(class_id, name, age)，若查询条件不含class_id，或跳过name直接用age，索引失效。
• 规则：从左到右依次使用，不能跳过前面的字段。

2. 索引列上使用计算、函数或类型转换

• 例：WHERE SUBSTR(name, 1, 3) = 'abc'（函数操作）、WHERE age + 1 = 20（计算）会导致索引失效。

• 字符串不加单引号（隐式类型转换）：WHERE name = 123（name为字符串类型）会失效。

  

  类型转化

3. 范围查询后的字段索引失效

范围查找

范围条件（>, <, >=, <=, BETWEEN）右侧的字段索引会失效：

• 例：索引(class_id, name, age)，查询WHERE class_id > 10 AND name = '张三'，name的索引失效。

4. 使用特殊运算符

• != 或 <>：导致索引失效。

  

  不等于

• IS NULL 或 IS NOT NULL：通常失效（视版本和数据分布而定）。

• NOT IN 或 NOT EXISTS：大概率失效。

notin

5. LIKE 查询以%开头

like查询

• LIKE '%abc' 或 LIKE '%abc%' 会导致索引失效（无法利用前缀匹配）。

• LIKE 'abc%' 可正常使用索引（后缀匹配，符合最左前缀）。

  

  like右模糊

  此时age索引列也生效了

6. OR 连接的条件中存在非索引字段

• 例：WHERE id = 1 OR name = '张三'，若name无索引，则id的索引也会失效（需全表扫描匹配name）。（如果or连接的条件都包含索引则索引会生效）

7. 其他特殊情况

• IN条件值过多（超过range_optimizer_max_mem_size默认 8M）或值接近表记录数，可能触发全表扫描。
• 未使用覆盖索引（查询字段超出索引包含的列，需回表取数据，效率降低）。

索引的优缺点

优点

1. 减少服务器扫描的数据量，降低 IO 成本。
2. 提高检索效率，将随机 IO 转为顺序 IO（B + 树特性）。
3. 降低排序成本（索引本身有序，避免Using filesort）。

缺点

1. 占用磁盘空间（索引需单独存储，尤其是复合索引）。
2. 降低更新速度（增删改时需同步更新索引，增加开销）。

索引使用口诀总结

全值匹配我最爱，最左前缀要遵守  
带头大哥不能死，中间兄弟不能断  
索引列上不计算，范围之后全失效  
LIKE符号写最右，覆盖索引不写*  
不等空值还有or，索引失效要少用  
字符单引不能丢，SQL高级也不难  
分组之前必排序，一定要上索引啊

补充：索引下推（Index Condition Pushdown, ICP）

MySQL 5.6 + 引入的优化机制，用于减少回表次数：

• 原理：在索引遍历过程中，先过滤掉不符合条件的记录（利用索引包含的字段），再回表取数据。
• 例：索引(class_id, age)，查询WHERE class_id = 1 AND age > 10，ICP 会在索引中先过滤age > 10，再回表，减少无效回表。