持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第15天，点击查看活动详情

前言

索引都知道，但平时工作中写SQL真的会考虑到这条SQL如何能用上索引的又有几人？索引并不是万能的，索引设计的不合理可能会阻碍数据库和业务的处理性能，那么什么情况下不需要创建索引？创建了索引不一定代表一定能用得上，我们又该如何避免这情况？本文通过如何创建索引和使用索引来让查询效率最大化，希望对大家有所帮助，如有错误或未考虑完全的地方，望不吝赐教。

创建索引的技巧

创建索引有一定的规律，我们可以通过创建索引提升查询效率，下面我们来看看什么情况下可以创建索引：

维度高的列创建索引

维度是数据列中不重复的值出现的个数，这个数量越高维度就越高。如数据表中存在8行数据，a、b、c、d、e、a、b、c、d、e，则这个表的维度为4，性别这样的低纬度列不适合创建索引。

字段的数值有唯一性的限制，比如用户名

在我们的数据表中，如果某个字段时唯一性的，就可以直接创建唯一索引，或者主键索引。

频繁作为WHERE查询条件的字段

在数据量大的情况下，某个字段在sql查询的where条件中经常被使用到，那么就需要给这个字段创建索引了。

需要经常GROUP BY和ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用GROPY BY对数据进行分组查询，或者使用ORDER BY对数据进行排序的时候，就需要对分组或者排序的字段进行索引。

比如我们按照class对学生进行分组，如果我们不对字段class添加索引执行sql如下：

SELECT class, COUNT(*) from user GROUP BY `class`

当我们对字段class添加索引后再执行，运行时间不到原来的一半，效率提升很明显。

UPDATE、DELETE的WHERE条件列，一般也需要创建索引

是因为当我们对数据按照某个条件进行Update或delete的操作时，需要先根据Where条件列检索出来这条记录，然后再对它进行更新或删除。

DISTINCT字段需要创建索引

因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

比如我们对字段class进行去重查询，如果我们不对字段class添加索引执行sql如下：

SELECT DISTINCT class from user;

当我们对字段class添加索引后再执行，运行时间是原来的1/5，效率提升很明显。

做多表JOIN连接操作的时候，创建索引需要注意

对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

使用组合索引

使用组合索引，可以减少文件索引大小，在使用时速度要优于多个单列索引。

我们要尽可能的扩展索引，而不是新建索引，因为索引数量过多，除了增加额外的磁盘空间外，会导致写效率变低，因为其每增删改一次就得从新建立索引。

频繁更新的字段不一定要创建索引

频繁更新的字段不一定要创建索引，因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

索引使用技巧

尽量全值匹配

建立了联合索引列后，如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，比方说下边这个查找语句：

最左前缀原则

最左原则就是需要从左到右的使用索引中的字段，一条SQL语句可以只使用联合索引的一部分，但是需要从最左侧开始，否则就会失效。

不在索引列上做任何操作

不要在索引列上做任何操作，包括计算，函数，自动或者手动类型转换，会导致索引失效而转向全表扫描。

举例：

范围条件放最后

左边索引的范围查找会打乱右边索引的顺序，所有记录都是按照索引列的值从小到大的顺序排好序的，而联合索引则是按创建索引时的顺序进行分组排序。

尽量使用覆盖索引

如果我们查询的数据在联合索引中没有出现，那么我们就需要进行回表操作，到主键索引的B+树中再次定位数据进行查询，这个磁盘IO消耗的时间是非常严重的。

因此尽量使用覆盖索引(只访问索引的查询(索引列包含所有的查询列))，不是必要的情况下减少 select *，除非是需要将表中的全部列检索后，在程序中进行缓存。

不等于要慎用

在使用不等于（!= 或者<>），会导致索引失效，如果定要需要使用不等于,请用覆盖索引。

NULL和NOT NULL 要慎用

在字段为NOT NULL的情况下，如果使用 is null 或者 is not null，会导致索引失效；在字段为可以为NULL的情况下，使用IS NULL，索引正常；使用 IS NOT NULL，则索引失效。

LIKE查询要当心

like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

字符类型要加引号

少用or

少用or，在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效，少用不是不用；

总结

对于索引的优化，我们要从两个方面入手，如何创建索引和查询如何使用索引，随着查询的需求增多，创建的索引也会越来越多，这时我们要注意，我们要尽量的扩展索引，而不是新建索引，因为索引数量过多需要维护的成本也会变大，导致写效率变低。

对于查询时，我们可以记住如下口诀：

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；

以上就是mysql索引使用情况的总结了，欢迎大家补充！

一文搞定如何通过索引让SQL查询效率最大化?

前言