MySQL-05:数据库索引(二)

先来看个语句,当前sql语句 select * from T where k between 3 and 5 会执行几次树的搜索操作,会扫描多少行?

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

insert into T values(100,1),(200,2),(300,3),(500,5),(600,6),(700,7);

我们想一想就会知道,会出现主键ID索引树和非主键K索引树,ID索引树保存的是整行数据,K索引树保存的是对应的索引值,来看下面的图:
在这里插入图片描述

SQL 查询语句的执行流程如下:

  1. 会先去 k 索引树上找到 k=3 的记录,取得 ID = 300;
  2. 进行回表,到 ID 索引树查到 ID=300 对应的 R3;
  3. k 索引树会往下遍历取下一个值 k=5,取得 ID=500;
  4. 再回到 ID 索引树查到 ID=500 对应的 R4;
  5. k 索引树依然会取下一个值 k=6,不满足条件,循环结束,K索引树进行索引搜索的时候,会对数据挨个进行搜索,直到搜索到不符合要求的数据,也就是进行边界判断。

统计一下,我们得到回表两次,K索引树查询3次,ID索引树查询2次。

由于查询的数据主要在ID索引树才会有,不得已进行回表采用,那有没有方法避免回表呢?来,看,例如我们再来执行sql语句select ID from T where k between 3 and 5 ,这样就不需要回表啦,这也叫索引覆盖。

1.1 覆盖索引

覆盖索引: 能从K索引树上查出我们所需要的数据(例如ID),避免回表的操作。覆盖索引可以大大地减少树的搜索次数,显著提升搜索性能,是常用的性能优化手段。

基于覆盖索引的概念,我们来说一说联合索引,有如下一张表:

create table user(
	id int(11) not null,
    id_card varchar(32) default null,
    name varchar(32) default null,
    age int(11) default null,
    primary key (id),
    key name_age(name,age)
)engine = InnoDB

从上面的那个表来看,我们会看到两个索引树:主键ID索引树,name_age联合索引树。

name_age索引树建立的目的在于满足根据人员名字来查询年龄的请求。

1.2最左前缀原则

B+树可以利用索引的最左前缀来定位数据,我们用上面的name_age来说事,看下图:
在这里插入图片描述

我们如果去查询人名为“王五”的人员,也可以利用这个name_age索引树来进程查询,name_age索引树可以进行复用,给满足查询条件前缀的sql语句利用,前缀可以是几个字段,也可以是几个字符。

例如:如下语句都可以使用

select id from user where name like '张%'
select id from user where name like '张%' and age = 10
select age from user where name like '张%' and age = 20

那在建立联合索引的时候就要考虑字段的前后顺序了,因为这涉及到索引树复用的问题。

  1. 考虑维护索引的数量,这个顺序往往就是需要优先考虑采用的。
  2. 考虑维护索引的空间,例如name往往比age来得大的多。

1.3索引下推

例有sql语句如下:

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

MySQL5.6推出索引下推优化,在进行索引遍历的时候,会对索引中包含的字段进行提前判断,刷掉不符合提交的记录,减少回表的次数。
从下图能很好地看出,MySQL5.6之前不会去管索引中的其他字段,会记录下name满足条件的id,直接进行回表。

在这里插入图片描述
在这里插入图片描述

1.4练习问题:

  1. 覆盖索引的概念及意义?联合索引?
  2. 最左前缀原则是怎样的?
  3. 联合索引中字段顺序如何去考虑?
  4. 索引下推在哪个MySQL中出现,实现效果是怎样的?
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页