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; 却不能使用索引

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)mysql通配符使用，那么以下的操作是能触发索引的:

//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，来具体地查看我们的语句有没有正确地使用到索引，然后再一步步优化。

另外，如果你有兴趣，或者是有问题想要与我探讨，欢迎来访问我的博客：https:mu-mu.cn/blog

（编辑：广州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!