MySQL系列：索引（B+Tree树、构建过程、回表、基本操作、执行计划、应用）_mysql b+tree

介绍

https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html

作用

优化查询

算法

索引的算法包括

• BTree
• Hash
• RTree
• FullText
• GIS

B+Tree结构

BTree查找算法图

B+Tree查找算法图（在叶子节点上加上双向指针）

B*Treee查找算法图（在枝节点上也使用双向指针）

官方图

分类

构建过程

聚簇索引构建过程

作用：

有了聚簇索引，将来插入的数据行，在同一个区内，都会按照ID值得顺序，有序在磁盘存储数据。

MySQL InnoDB 表 通过聚簇索引组织存储数据表。

前提：

• 1.建表时指定了主键列，MySQL InnoDB会将主键自动作为聚簇索引列，比如Id not null primary key

• 2.没有指定主键，自动选择唯一键的列作为聚簇索引列

• 3.以上都没有，生成隐藏聚簇索引

辅助索引构建过程

作用：

优化非聚簇索引列

回表

介绍

MySQL用来存储数据行的逻辑结构，表的数据行最终存储到了很多个Page上

InnoDB存储引擎，会按照聚簇索引，有序的组织存储表数据到各个区的连续的页中上

这些连续的数据页，成为了聚簇索引的叶子节点，可以认为聚簇所以就是原表数据

所以回表就是回聚簇索引

辅助索引：将辅助索引列值+ID主键值，构建辅助索引BTree结构

用户使用辅助索引作为条件查询时，首先扫描辅助索引的B树

• 1.如果辅助索引能够完全覆盖到查询结构时，就不需要回表

• 2.如果不能完全覆盖到，只能通过得到的ID主键值，回到聚簇索引（回表）扫描，最终得到想要的结果

影响

• 1.IO量级变大

• 2.IOPS会增大

• 3.随机IO会增大

减少回表

• 1.将查询尽可能用ID主键查询

• 2.设计合理的联合索引

• 3.更精确的查询条件+联合索引

• 4.基于优化器对索引的算法（MRR）

基本操作

建议将索引建立在，经常where ,group by ,order by ,join on ...的条件

查询索引

desc test;
key :
PRI 聚簇索引(主键索引) 
MUL 辅助索引 
UNI 唯一索引
1
2
3
4
5

(root@localhost) [test]> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   | MUL | NULL    |                |
| sex   | varchar(2)  | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| phone | varchar(11) | YES  | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

查询索引详细信息

show index from test;
1

(root@localhost) [test]> show index from test;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY         |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          0 | ix_unique_phone |            1 | phone       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | ix_name         |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | ix_prifix_phone |            1 | phone       | A         |           0 |        3 | NULL   | YES  | BTREE      |         |               |
| test  |          1 | ix_name_sex     |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | ix_name_sex     |            2 | sex         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

创建索引

创建表时创建索引

create table test(
id int primary key auto_increment , # 主键索引
name varchar(20) not null, 
sex varchar(2) ,
age int ,
phone varchar(11),
unique index ix_unique_phone(phone asc), # 唯一索引
index ix_name(name), # 单列索引
index ix_prefix_phone(phone(3)), # 前缀索引
index ix_name_sex(name,sex) # 联合索引
);
1
2
3
4
5
6
7
8
9
10
11

修改表时创建索引

alter table test add primary key(id); # 添加主键索引
alter table test modify id integer auto_increment; # 自增
alter table test add unique ix_unique_phone(phone asc); # 唯一索引 # 
alter table test add index ix_name(name); # 单列索引
alter table test add index ix_prifix_phone(phone(3));  # 前缀索引
alter table test add index ix_name_sex(name,sex); # 联合索引
1
2
3
4
5
6

删除索引

alter table test modify id int; # 取消自增
alter table test drop primary key; # 删除主键索引
# 删除其它索引
alter table test drop index ix_unique_phone;
alter table test drop index ix_name;
alter table test drop index ix_prefix_phone;
alter table test drop index ix_name_sex;
1
2
3
4
5
6
7

执行计划

查看

explain select * from test;
desc select * from test;
1
2

(root@localhost) [test]> insert into test(name,sex,age,phone) values('张三','男',18,'13744551221');
Query OK, 1 row affected (0.00 sec)

(root@localhost) [test]> insert into test(name,sex,age,phone) values('李四','男',18,'13744531221');
Query OK, 1 row affected (0.01 sec)

(root@localhost) [test]> insert into test(name,sex,age,phone) values('王五','女',19,'13744531421');
Query OK, 1 row affected (0.01 sec)

(root@localhost) [test]> explain select * from test where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

(root@localhost) [test]> desc  select * from test where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

分析

应用规范

必须建表一定要有主键，一般是个无关列

选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录

限制索引的数目

Percona-toolkit中有个工具:专门分析索引是否有用

尽量少在经常更新值得列建索引

Innodb_index-stats

Innodb_table-stats

Optimize table city;

函数，隐式转换不走索引

<,>,in等也可能不走索引，和结果集有关 超过15%~30%就不走索引

优化器针对索引的算法

思考

更新数据时，会对索引有影响吗？索引是否实时更新？

比如insert，delete,update一行数据

对于聚簇索引会立即更新

对于辅助索引，不是实时更新的

在InnoDB内存结构中，加入了insert buffer(会话)，现在版本叫change buffer

change buffer功能是临时缓冲辅助索引需要的数据更新

当我们需要查询新的insert数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的

B+Tree索引树高度影响因素?

1.索引字段较长： 前缀索引

2.数据行过多：分区表，归档表（pt-archive），分布式架构

3.数据类型：选择合适的数据类型*

为什么不能乱建索引?

如果冗余索引过多，表的数据变化的时候，很有可能会导致索引频繁更新。会阻塞很多业务更新的请求

索引过多，会导致优化器选择出现偏差