文章目录
一、简述索引发展过程
为什么数据库要有索引?
索引其实就是它的“目录”。
一本 500 页的书,如果你想快速找到其中的某一个知识点,在不借助目录的情况下,那我估计你可得找一会儿。就像你看到我这篇帖子一样,查找完全随缘。
一个数据库的索引到底应该怎么设计呢?
-
早期的索引底层设计其实是一个hash表结构,设计出一个hash函数,根据函数求出相应的值即为hash表对应的键值,但是根据数据结构查找章节的内容,我们知道,其实利用hash表来存储数据,对于hash函数的设计要求非常高,对于数据量比较大一不小心就会产生大量的冲突,再利用线性探测法、拉链法去解决冲突,耗时耗力,完全违背了我们简单查询的目的。
-
后来,我们想到可以用二叉树来解决啊!当有新结点的产生,我们就把他插入到树中去。但是二叉树又没有顺序,我们就选择设计一个二叉排序树去解决,数据左小右大,暂时解决!
-
但是后来我们又发现,当数据量过大,二叉排序树一直插入节点,会出现其中某些子树的深度变得特别的深,导致在搜索的时候出现查询最深节点的数据时,要经过整个树的高度。有点小烦!于是我们又引入了平衡二叉树,来降低树的高度,但是又引发了新问题,平衡二叉树节点只能插入一条数据,会不断的进行平衡旋转,还是有点烦!
-
之后,我们就引入B树来解决上面问题,虽然B树解决了节点存储,不断调整平衡的问题,但还是有点小瑕疵,比如范围查找,当我找到一个值时,不能最大效率的找到他前后的N个值。
-
最后引入了B+树来解决这种问题,让搜索的效率变得更高效。
二、索引分类
- 从功能逻辑上说,索引可以分为:普通索引、唯一索引、主键索引、全文索引。
- 按照物理实现方式,索引可以分为:聚簇索引和非聚簇索引。
- 按照作用字段个数进行划分,分成单列索引和联合索引。
三、索引介绍
1. 普通索引和主键索引
(1)简述
- 其中主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引。
- 非主键索引也即普通索引,它的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引。
- 普通索引和主键索引两个要放在一起进行介绍,因为他俩涉及到回表问题,下面会说到。
(2)创建过程
一般一张表设计出来的时候他的主键就已经是确定的,因此主键索引也是默认创建好的,如下:
create table iam_group
(
id varchar(255) not null
primary key,
name varchar(255) null,
parent_group_id varchar(255) null,
);
普通索引:
create index iam_group_index
on iam_group (parent_group_id);
其中iam_group_index为索引名,iam_group为表名,parent_group_id为要在表的这个列上加索引
2. 单列索引和联合索引
- 上面普通索引的举例就是单列索引,只在单列上加了索引。
- 联合索引是在多列上加了索引,举例:
create index IDX_AM_ACCESS_LOGIN_NAME_UNION
on iam_user (login_name, type, group_id);
表示对iam_user这张表的login_name,type,group_id加了一个联合索引
四、不得不提的索引高阶
首先我们先创建一张表,进行举例:
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');
说明:上面新建了一张表,主键是ID,列有k和s,k上加了普通索引,然后又插入了一批数据。
这个时候创建出来的索引是这样的结构
1.回表
首先和大家探讨下回表的问题,在表 T 中,如果我执行 select * from T where k between 3 and 5,需要执行几次树的搜索操作,会扫描多少行?
我们一起来看看这条 SQL 查询语句的执行流程:
- 在 k 索引树上找到 k=3 的记录,取得 ID = 300;
- 再到 ID 索引树查到 ID=300 对应的 R3;
- 在 k 索引树取下一个值 k=5,取得 ID=500;
- 再回到 ID 索引树查到 ID=500 对应的 R4;
- 在 k 索引树取下一个值k=6,不满足条件,循环结束。
在这个过程中,回到主键索引树搜索的过程,我们称为回表。可以看到,这个查询过程读了 k 索引树的 3 条记录(步骤 1、3 和 5),回表了两次(步骤 2 和 4)。
2. 覆盖索引
如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引。
3. 最左前缀原则
假如为每一种查询都设计一个索引,索引是不是太多了,因此就需要设计联合索引,但是在建立联合索引的时候,如何安排索引内的字段顺序呢?
这里我们的评估标准是,索引的复用能力。就出现了最左前缀原则,如果查询时使用了联合索引中的一部分列,那么这些列必须按照联合索引中的顺序出现,从左到右进行匹配。
- like aa%后模糊查询索引有效,like %aa模糊查询索引无效。
- mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。范围列可以用到索引,但是范围列后面的列无法用到索引。即,索引最多用于一个范围列,因此如果查询条件中有两个范围列则无法全用到索引
- InnoDB会把主键字段放到索引定义字段后面, 当然同时也会去重。 所以,当主键是(a,b)的时候, 定义为c的索引,实际上是(c,a,b); 定义为(c,a)的索引,实际上是(c,a,b) 你看着加是相同的 ps 定义为(c,b)的索引,实际上是(c,b,a)
4. 索引下推
MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
举例:
CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
说明:建立了tuser表,在表中建立了name和age的联合索引
执行下面这条查询语句时
mysql> select * from user where name like '张%' and age=10 and ismale=1;
无索引下推的情况:
有索引下推的情况:
可以看到,有索引下推的时候,过滤掉了两条age不等于10的回表情况,减少了两次回表,提高了效率!
最后想要对一个查询语句进行分析,可以使用explain关键字进行分析,该文就不一一举例了