什么样的字段适合加索引？索引失效了解吗？

题目

推荐解析

适合加索引的场景

1）主键字段：用于唯一标识每个记录的字段，通常是数据库表中的一个自增或唯一标识字段。

2）外键字段：用于连接两个表之间关系的字段，通常是一个表中的字段与另一个表中的主键字段关联。

3）经常用于查询的字段：如果某个字段经常被用于查询条件，例如用户的用户名、邮箱或订单的编号，那么为该字段添加索引可以提高查询性能。

4）经常用于排序的字段：如果某个字段经常用于排序操作，例如文章的发布时间或销售订单的日期，为该字段添加索引可以加快排序操作的速度。

5）经常用于连接操作的字段：如果某个字段用于连接多个表进行关联查询，例如用户表和订单表中的用户 ID 字段，为该字段添加索引可以提高连接操作的性能。

总结：Select、Where、Join、Order By 后面跟着的字段建议加索引。

不适合加索引的场景

同样，我们需要知道什么样的数据不推荐加索引，索引提升的能力有限，甚至是倒退。

1）低选择性的列：低选择性的列指的是具有很少不同值的列。如果一列只有很少几个不同的值，那么为它添加索引可能不会提供显著的性能改进，而且可能浪费存储空间。例如，性别列通常只有两个不同的值（男和女），对其添加索引通常没有太大意义。

2）频繁更新的列：如果一个列经常被更新，特别是大规模的批量更新，那么索引会增加更新操作的开销。每次更新索引列都需要维护索引结构，这可能会导致性能下降。在这种情况下，需要仔细权衡查询性能和更新性能。

3）小表：对于非常小的表，查询通常非常快，即使没有索引。在这种情况下，添加索引可能只会增加存储开销，而不会明显提高性能。

总结：低选择性、频繁更新、数据流较小的表不推荐加索引。

索引失效的场景

1）左或左右模糊匹配，因为 MySQL 采用最左匹配原则。

SELECT * FROM users WHERE name LIKE 'abc%';

2）查询条件中队索引列使用函数。

SELECT * FROM users WHERE LOWER(name) = 'abc';

3）查询条件对索引列使用表达式计算。

SELECT * FROM users WHERE age + 10 = 30;

4）如果索引列是字符串，并且条件语句中输入参数是数字，那么索引列会产生隐式类型转换，CAST 函数实现，因为等同于堆索引列使函数，导致索引失效，反之索引列是数字，输出参数是字符串，那么不会失效。

SELECT * FROM users WHERE str = 1234 ;

5）在 Where 子句中，如果在 OR 前的条件列是索引列，但 OR 后面的条件列不是索引列。

SELECT * FROM users WHERE name = 'abc' OR age = 30;

6）使用 Select * 语句，大概率不会走索引，因为不是每一列都加索引。

SELECT * FROM users;

7）使用 Not Exists 关键字，索引也会失效，本质上是 Where 查询范围过大。

SELECT * FROM users WHERE NOT EXISTS (SELECT * FROM orders WHERE orders.user_id = users.id);

8）使用 Order by 注意最左匹配，并且要加 limit 或者 Where 关键字，否则索引会失效。

SELECT * FROM users ORDER BY name LIMIT 10;

我们需要了解适合加索引的场景和不适合加索引的场景，另外你需要知道索引失效的场景，这次你可能需要利用 Explain 语句分析索引使用情况，我推荐大家去实战自己试一试能够加深印象，面试的时候也能根据你的回忆进行阐述。

其他补充

鱼聪明 AI 的回答：

鱼聪明 AI 地址：https://www.yucongming.com/

适合加索引的字段包括：

1）经常用于查询的字段：如果某个字段经常被用于查询条件，例如用户的用户名、邮箱或订单的编号，那么为该字段添加索引可以提高查询性能。

2）经常用于排序的字段：如果某个字段经常用于排序操作，例如文章的发布时间或销售订单的日期，为该字段添加索引可以加快排序操作的速度。

3）经常用于连接操作的字段：如果某个字段用于连接多个表进行关联查询，例如用户表和订单表中的用户 ID 字段，为该字段添加索引可以提高连接操作的性能。

索引失效是指在某些情况下，数据库查询不会使用已经创建的索引，导致索引无法发挥作用，从而降低查询性能。常见的导致索引失效的情况包括：

1）模糊匹配：如果在查询条件中使用了模糊匹配，例如使用 LIKE ‘%xxx%’，那么索引可能会失效。

2）函数或表达式的使用：如果查询条件中对索引列使用了函数或表达式计算，例如使用 LOWER( name ) = ‘abc’，那么索引可能会失效。

3）类型转换：如果索引列是字符串，但查询条件中输入参数是数字，或者索引列是数字，但查询条件中输入参数是字符串，那么索引可能会失效。

4）OR 操作符：如果在 WHERE 子句中使用了 OR 操作符，并且 OR 前的条件列是索引列，但 OR 后的条件列不是索引列，那么索引可能会失效。

5）使用 NOT EXISTS：如果使用 NOT EXISTS 关键字，索引可能会失效。

6）使用 ORDER BY：如果使用 ORDER BY 进行排序，但没有加上 LIMIT 或者 WHERE 关键字，那么索引可能会失效。

了解这些索引失效的情况可以帮助我们更好地设计和优化数据库查询，以提高查询性能。

博客园某同学：

适合加索引的场景：

1）表的主键、外键必须有索引；

2）数据量超过 300 的表应该有索引；

3）经常与其他表进行连接的表，在连接字段上应该建立索引；

4）经常出现在 Where 子句中的字段，特别是大表的字段，应该建立索引；

5）索引应该建在选择性高的字段上；

CSDN 某同学：

适合加索引的场景

1）字段的数值有唯一性的限制
2）频繁作为 where 查询条件的字段
3）经常 group by 和 order by 的列
4）update、delete 的 where 条件列
5）distinct 字段需要创建索引
6）使用最频繁的字段放在联合索引的左侧

欢迎交流

在阅读本文后，大家应该了解了索引应该放在什么场景，以及索引在哪些情况下会失效，这些还是需要花时间去记忆的，应该很多隐式规则，到后来复盘可能会很难发现，接下来我给出三个问题，大家可以在评论区发表自己的见解，互相交流学习。

1）什么是最左匹配原则？

2）索引如何影响插入、删除和更新记录的开销？

3）索引是越多越好吗？

什么样的字段适合加索引？索引失效了解吗？

题目

推荐解析

适合加索引的场景

不适合加索引的场景

索引失效的场景

其他补充

推荐阅读

欢迎交流