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

偏执的太偏执、 2022-12-22 11:12 134阅读 0赞

先来看个语句，当前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索引树保存的是对应的索引值，来看下面的图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

**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来说事，看下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]

我们如果去查询人名为“王五”的人员，也可以利用这个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，直接进行回表。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 3]

### 1.4练习问题： ###

1.  覆盖索引的概念及意义？联合索引？
2.  最左前缀原则是怎样的？
3.  联合索引中字段顺序如何去考虑？
4.  索引下推在哪个MySQL中出现，实现效果是怎样的？

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/6de6ae64f0be4aa485a71f549d9a63f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/fb1c52ef8bd84ac99fabede3de95dee3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/2786a1098b0144b592777b615c711f9e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221120/7ff1a14b9a444a4caf5cedaa942456e6.png