MySQL学习笔记(四):正确使用索引(二)

上一篇学习到了MySQL中的索引是什么,以及一些关于索引的简单的知识,这篇我打算详细地谈一谈如何正确地使用索引。索引的使用看起来很简单,但是在真正去学习它的时候才发现,原来是暗藏玄机。使用索引时有很多需要特别注意的点,稍有不慎,我们建立的索引就不能发挥其作用了。

1、不是所有操作都能够使用到索引的
比如在我们常用的查询操作中,<,<=,=,>,>=,BETWEEN,IN 是能够使用索引的,而 <>,NOT IN ,!= 则不行。

所以当我们希望在 NOT IN 和 != 操作中使用到索引,得把NOT IN 换成 NOT EXISTS,把 != 换成 id >5 OR id < 5。

以及在like中,用通配符%开头是不能使用索引的。
like ‘%mumu’ 和 like ‘%mumu%’不能使用索引,而like ‘mumu%’ 和 like ‘mumu’ 能够使用。
所以,如果我们需要对大的文本进行搜索,一般有两种操作:
第一是使用全文索引 FULLTEXT,但是会占用大量空间。
第二是使用前缀索引即限制索引的大小,仅对字段的前N个字符设置索引,也能在很大程度上提升查询速度。

2、不是所有字段都适合用索引
像状态、性别这种值重复率特别高的字段是不适合建立索引的,不仅会占用空间,还会造成不必要的性能损失。
另外,含有 NULL 的值不适合作为索引字段,索引不会包含有 NULL 值的列,尤其是在复合索引中,只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。(NULL这个家伙非常特殊,以后还得专门去学习一下)

3、索引的字段类型和查询的数据类型需要一致
比如说,你的字段 id 是 int 类型的,在建立了索引之后
select * from table where id=1; 是能够使用索引的,但是
select * from table where id=”11″; 却不能使用索引

4、不能在添加了索引的字段上使用函数以及表达式(聚合函数除外)
聚合函数即 COUNT、SUM、AVG、MIN、MAX。
比如以下操作都是不能使用索引的。
select * from table where YEAR(time)=2020;
select * from table where id+1=5;

另外,在使用索引时,我们需要考虑索引的选择性,即不重复的索引值(基数)和数据表中的记总数比值。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MYSQL在查找时过滤掉更多的行。唯一索引和主键索引的选择性是1,这是最高的索引选择性,性能也是最好的。

为了更高的性能,还能将多个索引组合起来使用,即组合索引。组合索引会占用更多的空间,但是能够提高更高的效率。不过在使用组合索引的时候需要注意最左原则(之前说过的)。

仅仅是掌握以上的四种原则还远远不够,还有很多特殊的情况需要我们考虑。

1、在一个查询中,只能使用一个索引。其实与其说是只能使用一个索引,倒不如说同时使用两个单列索引的效率不如单单使用一个索引。数据库会根据 SQL 语句自己判断分析执行计划,选择最优的执行计划执行,即智能地选择是否使用索引,以及如何使用索引。

所以,在多个查询条件下,如果没有组合索引,数据库会自动选择一个最优的单列索引执行。
select * from table where id=1 and name=’mumu’ and age=20;
但是这是针对 AND 情况的,对 OR 条件并不适用。OR 会导致索引失效,即

//并不会使用到联合索引(id,name)
select * from table where id=1 or name='mumu';

所以如果我们希望 OR 也能够使用索引,就需要把 OR 换成相应的 IN、BETWEEN 和 UNION。

//比如把OR换成IN
select * from table where name in ('mm','mu','mumu');
//把OR换成BETWEEN
select * from table where age between (18,20);
//把OR换成UNION
select * from table where id=1;
union
select * from table where name='mumu';

2、ORDER BY 对索引的影响。在排序操作中,索引想要生效可不是那么容易。比如说,我建立了联合索引字段(name,age),那么以下的操作是能触发索引的:

//1 只查询索引字段
select name,age from table order by age;
//2 查询这两个索引字段加上别的已经建立了索引的字段
select id,name from table order by name,age;
//3 where和order的条件为同一个字段
select * from table where age=18 order by age;
//4 where和order使用了联合索引
select * from table where name='mumu' order by age;
//5 联合索引字段被where包含
select * from table where name='mumu' and age=18 order by age;

总结概括一下就是:ORDER BY 语句只有使用了跟 WHERE 语句中相同的索引字段(或组合索引),ORDER BY 中的索引才会生效。并且还需注意,当索引的顺序与ORDER BY中的列顺序不同,或者所有的列不是同一方向(全部升序或者全部降序)时,索引也不会生效。

覆盖索引与回表查询
覆盖索引,即所有需要查询的字段都被包含在了联合索引当中,在查询时只需要通过索引就能返回所需要的数据,而不再需要回表查询(如果我们需要的数据过多,并不是所有的字段都是索引,那么就需要去表中返回所需的数据),通过覆盖索引,能够极大极大地提升查询的效率,但是同时也会占用很多空间,使用时要谨慎。

关于 MySQL 的索引,真的还有很多东西要学,真正要学得通透还得深入到其中的数据结构和算法。不过更重要的还是学会去使用它。要想会用索引,可不是百度几下规则记一记就可以了,很多情况都是超出我们的预期的,我们应该学会使用 EXPLAIN,来具体地查看我们的语句有没有正确地使用到索引,然后再一步步优化。

发表评论

邮箱地址不会被公开。 必填项已用*标注