乐趣区

学习数据库你不得不知道的关于索引的小知识

最近在看阿里的数据库开发规范,正好看到说 select * 对查询效率的影响,正好想起来“多年”以前没有整理完成的数据库优化架构图,所以,一时激动,我就继续完成我的宏图伟业,不说别的,先上图,以证清白

有了图,接下来,就是对于我今天看的内容觉得比较好的分享,文末有福利


1、select * 对效率的影响

在我们平时的代码编写或面试题中,很多人都会疑惑:select * 到底合理吗?

如果说不合理,为什么?如果说合理,原因又是什么?

1)、阿里规范

在阿里 java 规范中是强制不允许使用 select * 这种操作的。

下面分析一下为什么不允许这种操作。

1、增加查询分析器解析成本。

  这里主要是考虑了多余字段带来的更多成本消耗。

  假设一张表有 10 个字段,你只需要其中的三个字段做分析。若使用 select *,分析器就需要解析这十个字段,且将这是个字段查询出来也会增加多余的网络消耗,且若数据库和应用程序不在同一台服务器上,这种网络消耗更加明显。

2、增减字段容易与 resultMap 配置不一致。

  采用 select * 这种方式时,若表结构发生了改变,很容易导致操作失败。

2)、其 缺点

1、会查询出一些不需要的字段。这些字段很可能没有索引,这样就杜绝了覆盖索引的可能性,而索引覆盖又是速度极快,效率极高,业界极为推荐的查询方式。

2、多余字段会增大数据传输时间。

3、大字段,例如很长的 varchar,blob,text。准确来说,长度超过 728 字节的时候,会把超出的数据放到另外一个地方,因此读取这条记录会增加一次 io 操作。

科普:覆盖索引

解读 1:就是 select 的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。

解读 2:索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫 做覆盖索引。

2、索引的简单介绍

1)、索引简介

  索引用于快速找出在某个列中有一特定值的行。不使用索引,必须从第 1 条记录开始然后读完整个表直到找出相关的行。表越大,花费的时间越多。如果表中查询的列有一个索引,就能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据。

2)、索引分类

索引分单列索引和组合索引。

①、单列索引(主键索引, 唯一索引, 普通索引)

  单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。

  普通索引, 这个是最基本的索引,

  唯一索引, 与普通索引类似, 但是不同的是唯一索引要求所有的类的值是唯一的, 这一点和主键索引一样. 但是他允许有空值

  主键索引, 不允许有空值,(在 B +TREE 中的 InnoDB 引擎中, 主键索引起到了至关重要的地位)

②、组合索引

组合索引,即一个索引包含多个列。

如果你建立了 组合索引(a_b_c) 那么他实际包含的是 3 个索引 (a) (a,b)(a,b,c)

在使用查询的时候遵循组合索引的 ” 最左前缀 ”, 下面我们来分析一下 什么是最左前缀: 及索引 where 时的条件要按照建立索引的时候字段的排序方式

1、不按索引最左列开始查询.

(多列索引)如 index(‘a’,‘b’,‘b’)

查询时使用 where‘b’=‘b1’, 此时不使用索引,

查询时使用 where’b‘=‘b1’and‘c’=‘c1’, 此时也不能使用索引

2、查询中某个列有范围查询,则其右边的所有列都无法使用索引查询(多列查询)

where a=‘a1’and b like =‘b%’and c=’c’

查询时只会使用索引中的前两列, 因为 like 是范围查询

3、不能跳过某个字段来进行查询, 这样利用不到索引, 比如我的 sql 是

select * from table where a >‘a1’and b =‘b1’and c = c1;

那么这时候他使用不到组合索引.

因为我的索引是 (a, b, c), 如果第一个字段出现了范围符号的查找, 那么将不会用到索引, 如果我是第二个或者第三个字段使用范围符号的查找, 那么他会利用索引, 利用的索引是(a).

因为上面说了建立组合索引(a, b, c), 会出现三个索引

③、聚集索引、非聚集索引

聚集(clustered)索引,也叫聚簇索引。

定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。

一个表就像是我们以前用的新华字典,聚集索引就像是拼音目录,而每个字存放的页码就是我们的数据物理地址,而拼音目录对应的 A - Z 的字顺序,和新华字典实际存储的字的顺序 A - Z 也是一样的

非聚集(unclustered)索引。

定义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。

其实按照定义,除了聚集索引以外的索引都是非聚集索引,只是人们想细分一下非聚集索引,分成普通索引,唯一索引,全文索引。如果非要把非聚集索引类比成现实生活中的东西,那么非聚集索引就像新华字典的偏旁字典,它结构顺序与实际存放顺序不一定一致。

3)、索引建立规范

索引建立的规范:

  1: 经常被搜索。2: 经常排序。3: 经常作为 where 条件。4: 无大量重复值。5: 不经常更新。6: 经常作为 order 条件。

3、索引是不是越多越好

由于经常被问到这个问题,所以这里单独拉出来分析一下。

结论: 不是

1、数据量小的表不需要建立索引, 因为小的表即使建立索引也不会有大的用处, 还会增加额外的索引开销。

2、不经常引用的列不要建立索引, 因为不常用, 即使建立了索引也没有多大意义。

3、经常频繁更新的列不要建立索引, 数据变更需要维护索引,越多的索引代表更多的维护成本。

4、更多的索引也需要更多的存储空间。

5、重复值多的列不适合建立索引,例如 sex 字段,值只有男、女。这时建立索引就没有意义。


其中除了自己的见解之外,还有就是从书中看到的,毕竟老祖宗就说过,书中自有黄金屋,书中自有颜如玉,所以平时除了工作的总结之外,更多的就是看书,尤其是像 阿里的开发手册 这样的书籍,虽然可能用不太到,防患于未然,不要当那天用到了,让自己很尴尬

需要这 3 份资料的,关注下方公众号之后,添加小助手获取即可

除了文档之外,还有各类思维导图

需要 xmind 图(包括上面我整理的索引优化的),关注公众号:Java 架构师联盟,后台回复 mysql 即可

退出移动版