文章目录
- 前言
- 什么情况下可以创建索引？
- 1. 字段的数值有唯一性的限制，比如用户名
- 2. 频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下
- 3. 需要经常 GROUP BY 和 ORDER BY 的列
- 4.UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引
- 5.DISTINCT 字段需要创建索引
- 6. 做多表 JOIN 连接操作时，创建索引需要注意以下的原则
- 什么时候不需要创建索引
- 什么情况下索引失效
前言
既然我们的目标是提升 SQL 的查询效率，那么该如何通过索引让效率最大化？
本篇解决以下问题：
- 什么情况下使用索引？ 当我们进行数据表查询的时候，都有哪些特征需要我们创建索引？
- 索引不是万能的，索引设计的不合理可能会阻碍数据库和业务处理的性能。那么什么情况下不需要创建索引？
- 创建了索引不一定代表一定用得上，甚至在有些情况下索引会失效。哪些情况下，索引会失效呢？又该如何避免这一情况？
什么情况下可以创建索引？
创建索引有一定的规律。当这些规律出现的时候，我们就可以通过创建索引提升查询效率，下面我们来看看什么情况下可以创建索引：
1. 字段的数值有唯一性的限制，比如用户名
索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。
2. 频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下
在数据量大的情况下，某个字段在 SQL 查询的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。
3. 需要经常 GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。
多个单列索引在多条件查询时只会生效一个索引（MySQL 会选择其中一个限制最严格的作为索引），所以在多条件联合查询的时候最好创建联合索引。
4.UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引
假设对一个100万的数据表更新一条数据：
UPDATE product_comment SET product_id = 10002 WHERE comment_text = '462eed7ac6e791292a79'执行时间为 1.173s，如果对 comment_text 添加索引，执行时间仅为 0.1110s。效率提升了10倍。
DELETE也是如此。
5.DISTINCT 字段需要创建索引
去重时使用索引，不仅效率提升很多，而且还是按照递增的顺序展示。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。
6. 做多表 JOIN 连接操作时，创建索引需要注意以下的原则
首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 user_id 在 product_comment 表和 user 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。
什么时候不需要创建索引
- WHERE 条件（包括 GROUP BY、ORDER BY）里用不到的字段不需要创建索引。
- 如果表记录太少，比如少于 1000 个，那么是不需要创建索引。
- 字段中如果有大量重复数据，也不用创建索引。
- 频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。
什么情况下索引失效
- 如果索引进行了表达式计算，则会失效
SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id + 1 = 900001- 如果对索引使用函数，也会造成失效
SELECT comment_id, user_id, comment_text FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='abc'- 在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。
- 当我们使用 LIKE 进行模糊查询的时候，不能以 % 开头。
- 索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效。
- 在使用联合索引的时候要注意最左原则
