大家好!我是黄啊码,MySQL的入门篇已经讲到第11个课程了,今天我们继续讲讲大白篇系列——索引
目录
2. 频繁作为 WHERE 查询条件的字段,尤其在数据表大的情况下
3. 需要经常 GROUP BY 和 ORDER BY 的列
4.UPDATE、DELETE 的 WHERE 条件列,一般也需要创建索引
6. 做多表 JOIN 连接操作时,创建索引需要注意以下的原则
3. 在 WHERE 子句中,如果在 OR 前的条件列进行了索引,而在 OR 后的条件列没有进行索引,那么索引会失效。
4. 当我们使用 LIKE 进行模糊查询的时候,后面不能是 %
5. 索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效。
索引本身可以起到约束的作用,比如唯一索引、主键索引都是可以起到唯一性约束的,因此在我们的数据表中,如果某个字段是唯一性的,就可以直接创建唯一性索引,或者主键索引。
在数据量大的情况下,某个字段在 SQL 查询的 WHERE 条件中经常被使用到,那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。
比如数据表中一共有 100 万条数据,假设我们想要查询 user_id=785110 的用户对商品的评论。
SELECT comment_id, product_id, comment_text, comment_time, user_id FROM product_comment WHERE user_id = 785110
运行时间为 0.699s,你能看到查询效率还是比较低的。当我们对 user_id 字段创建索引之后,运行时间为 0.047s,不到原来查询时间的 1/10,效率提升还是明显的。
索引就是让数据按照某种顺序进行存储或检索,因此当我们使用 GROUP BY 对数据进行分组查询,或者使用 ORDER BY 对数据进行排序的时候,就需要对分组或者排序的字段进行索引。
对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作,如果对 WHERE 字段创建了索引,就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录,然后再对它进行更新或删除。如果进行更新的时候,更新的字段是非索引字段,提升的效率会更明显,这是因为非索引字段更新不需要对索引进行维护。
不过在实际工作中,我们也需要注意平衡,如果索引太多了,在更新数据的时候,如果涉及到索引更新,就会造成负担。
有时候我们需要对某个字段进行去重,使用 DISTINCT,那么对这个字段创建索引,也会提升查询效率。
首先,连接表的数量尽量不要超过 3 张,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增长会非常快,严重影响查询的效率。
其次,对 WHERE 条件创建索引,因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下,没有 WHERE 条件过滤是非常可怕的。
我们创建了索引,还要避免索引失效,你可以先思考下都有哪些情况会造成索引失效呢?下面是一些常见的索引失效的例子:
1. 如果索引进行了表达式计算,则会失效
2. 如果对索引使用函数,也会造成失效
3. 在 WHERE 子句中,如果在 OR 前的条件列进行了索引,而在 OR 后的条件列没有进行索引,那么索引会失效。
4. 当我们使用 LIKE 进行模糊查询的时候,后面不能是 %
5. 索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效。
6. 我们在使用联合索引的时候要注意最左原则
实际工作中,查询的需求多种多样,创建的索引也会越来越多。这时还需要注意,我们要尽可能扩展索引,而不是新建索引,因为索引数量过多需要维护的成本也会变大,导致写效率变低。同时,我们还需要定期查询使用率低的索引,对于从未使用过的索引可以进行删除,这样才能让索引在 SQL 查询中发挥最大价值。
好了,今天的课程学到这里,有问题的留个言,别忘了一键三连,下次我们还会再见!
我是黄啊码,码字的码,退。。。退。。。退。。。朝!