提起优化SQL,你可能会把它理解为优化索引。简单来说这也不算错,索引在SQL优化中占了很大的比重。索引用的好,可以将SQL查询的效率提升10倍甚至更多。但索引是万能的吗?既然索引可以提升效率,只要创建索引不久好了吗?实际上,在有些情况下,创建索引反而会降低效率。

索引是万能的吗?

首先我们需要了解什么是索引(Index)。数据库中的索引,就好比是一本书的目录,它可以帮我们快速进行特定值的定位于查找,从而加快查询的效率。

索引就是帮助数据库管理系统高效获取数据的数据结构。

如果我们不使用索引,就必须从第1条记录开始扫描,直到把所有的数据表都扫描完,才能找到想要的数据。既然如此,如果我们想要快速查找数据,就只需要创建更多的索引就好了呢?

其实索引不是万能的,在有些情况下使用索引反而会让效率变低。

索引的价值是帮我们从海量数据中找到想要的数据,如果数据量少,那么是否使用索引对结果影响并不大。

在数据表中的数据行数比较少的情况下,比如不到1000行,是不需要创建索引的。另外,当数据重复度大,比如高于10%的时候,也不需要对这个字段使用索引。我们之前讲过,如果是性别这个字段,就不需要对他创建索引。这时为什么呢?如果你想要在100万行数据中查找其中的50万行(比如性别为男的数据),一旦创建了索引,你需要先访问50万次索引,然后在访问50万次数据表,这样加起来的开销比不适用索引可能还要大。

索引地种类有哪些?

虽然使用索引的本质目的是帮我们快速定位想要查找的数据,但实际上,索引有很多种类。

从功能逻辑上说,索引只要有4中,分别是普通索引唯一索引主键索引、和全文索引

普通索引是基础的索引,没有任何约束,主要用于提高查询效率。唯一索引就是再普通索引的基础上增加了数据唯一性的约束,在一张数据表里可以有多个唯一索引。主键索引是在唯一索引的基础上增加了不为空的约束,也就是NOT NULL + UNIQUE,一张表里最多只有一个主键索引。全文索引用的不多,MySQL自带的全文索引只支持英文。我们通常可以采用专门的全文搜索引擎,比如 ES(ElasticSearch) 和 Solr。

其实前三种索引(普通索引、唯一索引和主键索引)都是一类索引,只不过对数据的约束性逐渐提升。在一张数据表中只能有一个主键索引,这时由主键索引的物理实现方式决定的,因为数据存储在文件中只能按照一种顺序进行存储。但可以有多个普通索引或者多个唯一索引。

按照物理实现方式,索引可以分为两种:聚集索引非聚集索引。我们也把非聚集索引称为二级索引或者辅助索引

聚集索引可以按照主键来排序存储数据,这样在查找行的时候非常有效。举个例子,如果是一本汉语字典,我们想要查找“数”这个字,直接在书中找汉语拼音的位置即可,也就是拼音 “shu”。这样找到了索引的位置,在他后面就是我们要找的数据行。

非聚集索引又是什么呢?

在数据库系统会有单独的存储空间存放非聚集索引,这些索引是按照顺序存储的,但索引项指向的内容是随机存储的。也就是所系统会进行两次查找,第一次找到索引,第二次找到索引对应的位置取出数据行。非聚集索引不会把索引指向内容向聚集索引一样直接放到索引的后面,而是维护单独的索引表(只维护索引,不维护索引指向的数据),为了数据检索方便我们还是以汉语字典为例,如果想要查找“数”字,那么按照首部查找的方式,先查找到“数”字的偏旁首部,然后这个目录会告诉我们“数”字存放到第多少也,我们在去只当的页码找到这个字。

聚集索引指表中数据行按索引的排序方式进行存储,对查找行很有效。只有当表包含聚集索引时,表内的数据行才会按照索引列的值在磁盘上进行物理排序和存储。每一个表只能有一个聚集索引,因为数据行本身只能按照一个顺序进行存储。

聚集索引与非聚集索引的原理不同,在使用上也有一些区别:

  1. 聚集索引的叶子结点存储的就是我们的数据记录,非聚集索引的叶子结点存储的时数据的位置。非聚集索引不会影响数据表的物理存储顺序。
  2. 一个表只能有一个聚集索引,因为只能有一种排序存储的方式,但可以有多个非聚集索引,也就是多个索引目录提供数据检索。
  3. 使用聚集索引的时候,数据的查询效率高,但如果对数据进行插入,删除,更新等操作,效率会比非聚集索引低。

实验:使用聚集索引和非聚集索引的查询效率

在 user_gender 表中,我设置了 user_id 为主键,也就是聚集索引的字段是 user_id。这里我们查询下 user_id=90001 的用户信息:

1
SELECT user_id,user_name,user_gender FROM user_gender WHERE user_id = 900001

运行结果(1条数据,运行时间0.043s):

img

我们在直接对user_name字段进行条件查询,此时user_name字段没有创建索引:

1
SELECT user_id, user_name, user_gender FROM user_gender WHERE user_name = 'student_890001'

运行结果(1条数据,运行时间0.961s):

你能看出对没有建立索引的字段进行条件查询,查询效率明显降低了。

然后我们对user_name字段创建普通索引,进行SQL查询:

1
SELECT user_id, user_name, user_gender FROM user_gender WHERE user_name = 'student_890001'

运行结果(1条记录,运行时间0.050s):

通过这3次SQL查询结果的对比,我们可以总结出以下两点内容:

  1. 对WHERE字句的字段建立索引,可以大幅提升查询效率。
  2. 采用聚集索引进行数据查询,比使用非聚集索引的查询效率略高。如果查询次数比较多,还是尽量使用主键索引进行数据查询。

除了业务逻辑和物理实现方式,索引还可以按照字段个数进行划分,分成单一索引和联合索引。

索引列为一列时为单一索引;多个列组合在一起创建的索引叫做联合索引。

创建联合索引时,我们需要注意创建时的顺序,因为联合索引(x,y,z)

和(z,y,x)在使用的时候效率可能会存在差别。

这里需要说明的时联合索引存在最左匹配原则,也就是按照最左优先的方式进行索引的匹配。

比如刚才举例的(x,y,z),如果查询条件时 WHERE x = 1 AND y = 2 AND z=3 就可以匹配上联合索引;如果查询条件时WHERE y = 2,就无法匹配上联合索引。

实验:联合索引的最左原则

还是针对user_gender数据表,我们把user_id 和 user_name 字段设置为联合主键,然后看下SQL查询效率有什么区别。

1
SELECT user_id, user_name, user_gender FROM user_gender WHERE user_id = 900001 AND user_name = 'student_890001'

运行结果(1条数据,运行时间0.046s):

1
SELECT user_id, user_name, user_gender FROM user_gender WHERE user_id = 900001

我们再来看下普通的条件查询是什么样子:

1
SELECT user_id, user_name, user_gender FROM user_gender WHERE user_name = 'student_890001'

运行结果(1 条数据,运行时间 0.943s):

你能看到当我们使用了联合索引(user_id,user_name)的时候,在WHERE字句中对联合索引中的字段user_id和user_name进行条件查询,或者支队user_id进行查询,效率基本上一样的。当我们对user_name进行条件查询时,效率机会降低很多,这是因为根据联合索引的最左原则,user_id在user_name的左侧,如果没有使用user_id,而是直接使用user_name进行条件查询,联合索引就会失效。

总结

使用索引可以帮助我们从海量的数据中快速定位想要查找的数据,不过索引也存在一些不足,比如占用存储空间、降低数据库写操作的性能等,如果有多个索引还会增加索引选择的时间。当我们使用索引时,需要平衡索引的利(提升查询效率)和弊(维护索引所需的代价)。

在实际工作中,我们还需要基于需求和数据本身的分布情况来确定是否使用索引,尽管索引不是万能的,但数据量大的时候不使用索引是不可想象的,毕竟索引的本质,是帮助我们提升数据检索的效率。