数据库的表就如同一本厚厚的字典.字典里面有我们想要查询的数据. 但是呢,一页一页地翻,然后去找到相应的内容.真是太漫长了.
而索引呢,就如同字典的中的目录.通过索引我们可以快速地定位想要查询的内容. 然后呢,当然就去找咯!
索引的数据结构有三种.
hash表有序数组B+树hash表大家都很熟悉了.根据某一个条件,然后把数据尽可能地均匀地分布.然后在不同的桶中. 一般就是一个数组,然后每个数组中的元素是一个链表.
但这样数据量少,还不错.但是数据量一旦上去了.每次查询就可能遍历链表中的很多数据. 会有很多时间上的不足.
有序数组,顾名思义.数组中的元素是有顺序地.那么这样查询起来就会很快. 用二分查找 O(log n) 就可以了.
但是,我们知道数组的查询快,但是插入和删除都很糟糕.如果要是在数据前面插入或者删除第一个. 整个索引就相当于都重新排列了一下!
不同的应用场景,需要选择不同的数据库索引.
如果说,这个数据库索引类型,可以被替代,或者有什么更好的解决方案.那么就不会存在在现有的数据库中了.
在静态数据中,使用有序数据是非常有用的. 例如在去年的工资统计中,数据不会进行修改或者新加,那么有序数据就是一个很好的选择.
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呢.那么其它字段上的索引又是咋样的呢? 这就有涉及到了联合索引
联合索引,比较常见.
我们可以看到这样的索引
一个索引中有两个字段.
nameage两个字段,那么就有得分出个优先级来吧 总得排个先后顺序吧
最左原则最左原则就出现了:左边的优先.满足左边的采取满足右边的
这里又涉及到索引复用 上述的索引中,已经有了以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,达到减少回表的效果.
