02-MySQL索引

索引

数据库的表就如同一本厚厚的字典.字典里面有我们想要查询的数据.
但是呢,一页一页地翻,然后去找到相应的内容.真是太漫长了.

而索引呢,就如同字典的中的目录.通过索引我们可以快速地定位想要查询的内容.
然后呢,当然就去找咯!

索引的数据结构类型

索引的数据结构有三种.

• hash表
• 有序数组
• B+树

hash表

hash表大家都很熟悉了.根据某一个条件,然后把数据尽可能地均匀地分布.然后在不同的桶中.
一般就是一个数组,然后每个数组中的元素是一个链表.

但这样数据量少,还不错.但是数据量一旦上去了.每次查询就可能遍历链表中的很多数据.
会有很多时间上的不足.

有序数组

有序数组,顾名思义.数组中的元素是有顺序地.那么这样查询起来就会很快.
用二分查找 O(log n) 就可以了.

但是,我们知道数组的查询快,但是插入和删除都很糟糕.如果要是在数据前面插入或者删除第一个.
整个索引就相当于都重新排列了一下!

不同的应用场景,需要选择不同的数据库索引.

如果说,这个数据库索引类型,可以被替代,或者有什么更好的解决方案.那么就不会存在在现有的数据库中了.

在静态数据中,使用有序数据是非常有用的.
例如在去年的工资统计中,数据不会进行修改或者新加,那么有序数据就是一个很好的选择.

B+树

B+树.这里我对B+树的了解不够.不敢妄言.

我只知道B+树,在叶子节点中存储数据.而且还是有序的数据.
查询和修改都是非常快速的.

MySQL中的B+树,是N叉树.因为索引也是在磁盘中的.

索引的类型

主键索引

主键索引,一般一个表都需要一个主键作为索引.
但为啥主键一般都是递增的呢?

因为无论那种数据结构的索引,在新加数据时,如果索引是有序增加的.
那么,索引就能很方便地加入进去.

• Hash索引,希望分布在每个桶的数据是差不多的.有序的话,几乎就是平衡的.
• 有序数组,就更不用说了.如果是新增直接加到后面就好了.
• B+树

记住,数据删除了.但是索引可不会.

二级索引

除了主键之外的索引是二级索引.

二级索引的创建,可以进一步加快sql的查询.

这时,我才突然明白了一个之前的sql优化.

SELECT id FROM user
WHERE name = 'hello'

如果在name上加入了索引.那么查询user表时,只查询id
会立刻返回结果,而不用回表.

这里就涉及到一个非常重要的概念 回表.

在数据库索引(B+树)中,每个叶子节点都按照顺序存放着数据.

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

现在,我们要执行

SELECT *
FROM t
WHERE k between 3 and 5

如果,我们要查询表中的所有字段,那么必须经过下面的过程

• 在k的索引树上,找到值为 3的数据得到对应行的id 300
• 根据id为300,来到对应的数据行row. 这个过程就是回表
• 接着再找到4,到5.会再次回表 2次
• 最后找到了6,不符合要求.结束

所以上述过程中,数据库会回表 3次

那么问题来了,如何减少回表次数.达到提高性能的效果呢?

这就是下一节要讲的覆盖索引了

覆盖索引

覆盖索引为什么可以减少回表的次数呢?

我们看之前的图片,细心的同学可能发现了.索引k上带有id
那么如果我们只查询id,那么自然就不需要回表了

SELECT id
FROM t
WHERE k between 3 and 5

减少了回表次数,自然查询就会变快了

这种应用场景,我认为还是非常常见的.
比如我们查询其实只需要它的id,对于其它值,我们是不关系的.

那么就最好就只查询id

至于为什么索引k上会带有id呢.那么其它字段上的索引又是咋样的呢?
这就有涉及到了联合索引

联合索引

联合索引,比较常见.

我们可以看到这样的索引

一个索引中有两个字段.

• name
• age

两个字段,那么就有得分出个优先级来吧
总得排个先后顺序吧

• 最左原则

最左原则就出现了:左边的优先.满足左边的采取满足右边的

这里又涉及到索引复用
上述的索引中,已经有了以name为最左的索引.那就不必要再单独为name创建索引了

这里如何取舍,还是得看具体的业务逻辑!

一般,一个数据库表的索引不要超过5个,否则数据库会消耗大量资源去维护它

回到上一节的问题,为什么说索引k上会带有id的值呢?

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

上边的数据库表结构.注意主键索引是 (a,b)

大家可以想一下,哪个索引是不必要的呢?

ca 是不必要的.因为索引的建立后,都会默认给你后面加上主键索引
有c其实就够了.因为c会转化为(c,a,b)

索引下推

索引下推,是MySQL5.6之后的优化.以前是没有的…

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

我们如果要查询姓张,age为10,且为男孩的用户
还是只有 (name,age)索引

没有索引下推之前,在查询时,就又会出现多余的回表.

如果同时满足 name和age条件.
但是呢,要查询ismale条件.如何是好呢?
还是要回表查询一次,看看符不符合吗?

这样是不是有点多余了啊?

确实是有些多余了.

所以呢.mysql也做了一些优化.它可以索引下推
如下图所示.它可以预先判断ismale是否合理.再去查询row,达到减少回表的效果.