今天的课程主要包括下面几个部分:
- 什么情况下使用索引?当我们进行数据表查询的时候,都有哪些特征需要我们创建索引?
- 索引不是万能的,索引设计的不合理可能会阻碍数据库和业务处理的性能。那么什么情况下不需要创建索引?
- 创建了索引不一定代表一定用得上,甚至在有些情况下索引会失效。哪些情况下,索引会失效呢?又该如何避免这一情况?
创建索引有哪些规律?
创建索引有一定的规律。当这些规律出现的时候,我们就可以通过创建索引提升查询效率,下面我们来看看什么情况下可以创建索引:
1. 字段的数值有唯一性的限制,比如用户名
索引本身可以起到约束的作用,比如唯一索引、主键索引都是可以起到唯一性约束的,因此在我们的数据表中,如果某个字段是唯一性的,就可以直接创建唯一性索引,或者主键索引。
2. 频繁作为 WHERE 查询条件的字段,尤其在数据表大的情况下
在数据量大的情况下,某个字段在 SQL 查询的 WHERE 条件中经常被使用到,那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。
我之前列举了 product_comment 数据表,这张数据表中一共有 100 万条数据,假设我们想要查询 user_id=785110 的用户对商品的评论。
如果我们没有对 user_id 字段创建索引,进行如下查询:
|
复制代码
运行结果:
运行时间为 0.699s,你能看到查询效率还是比较低的。当我们对 user_id 字段创建索引之后,运行时间为 0.047s,不到原来查询时间的 1/10,效率提升还是明显的。
3. 需要经常 GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储或检索,因此当我们使用 GROUP BY 对数据进行分组查询,或者使用 ORDER BY 对数据进行排序的时候,就需要对分组或者排序的字段进行索引。
比如我们按照 user_id 对商品评论数据进行分组,显示不同的 user_id 和商品评论的数量,显示 100 个即可。
如果我们不对 user_id 创建索引,执行下面的 SQL 语句:
|
复制代码
运行结果(100 条记录,运行时间 1.666s):
如果我们对 user_id 创建索引,再执行 SQL 语句:
|
复制代码
运行结果(100 条记录,运行时间 0.042s):
你能看到当对 user_id 创建索引后,得到的结果中 user_id 字段的数值也是按照顺序展示的,运行时间却不到原来时间的 1/40,效率提升很明显。
同样,如果是 ORDER BY,也需要对字段创建索引。我们再来看下同时有 GROUP BY 和 ORDER BY 的情况。比如我们按照 user_id 进行评论分组,同时按照评论时间降序的方式进行排序,这时我们就需要同时进行 GROUP BY 和 ORDER BY,那么是不是需要单独创建 user_id 的索引和 comment_time 的索引呢?
当我们对 user_id 和 comment_time 分别创建索引,执行下面的 SQL 查询:
|
复制代码
运行结果(运行时间 >100s):
实际上多个单列索引在多条件查询时只会生效一个索引(MySQL 会选择其中一个限制最严格的作为索引),所以在多条件联合查询的时候最好创建联合索引。在这个例子中,我们创建联合索引 (user_id, comment_time),再来看下查询的时间,查询时间为 0.775s,效率提升了很多。如果我们创建联合索引的顺序为 (comment_time, user_id) 呢?运行时间为 1.990s,同样比两个单列索引要快,但是会比顺序为 (user_id, comment_time) 的索引要慢一些。这是因为在进行 SELECT 查询的时候,先进行 GROUP BY,再对数据进行 ORDER BY 的操作,所以按照这个联合索引的顺序效率是最高的。
4.UPDATE、DELETE 的 WHERE 条件列,一般也需要创建索引
我们刚才说的是数据检索的情况。那么当我们对某条数据进行 UPDATE 或者 DELETE 操作的时候,是否