MySQL知识笔记——中级进阶之索引（实施工程师和DBA工作笔记）

在上一章中我们已经讲完了学习和实施工作中需要掌握的MySQL基础知识，但是在实际应用中这些基础只能让我们简单了解流程，以后的工作不只是简单的安装部署系统，我们还要将客户的数据导入数据库中才能完善系统的完整性和可使用性，接下来将详细讲解MySQL的索引、锁、事务等等进阶又重要的知识。

一、MySQL的索引

常见的索引算法有顺序查找、二分查找（折半查找）、二叉树查找、平衡二叉树、B树、B+树等。
B树是一种自平衡的搜索树，它允许在对数时间内进行插入、删除和查找操作。B树具有以下特点：

• B树是一个多路搜索树，每个节点可以包含多个子节点。
• 所有叶子节点位于相同的层级上，且为空节点（NIL）。
• 节点中的键值按照升序排列，并且每个键值都与一个指向子树的引用相关联。
• 每个节点有一个上限和下限，通常称为阶（order），表示节点可以容纳的最小和最大键值数量。
  

理解B树的关键是要明白它的自平衡特性。插入和删除操作可能导致树的不平衡，但B树会通过一系列的旋转和重组操作来保持平衡。这确保了在树中进行查找操作时，每个节点的子树高度保持相对平衡，从而提供了快速的搜索性能。
B+树也是一种自平衡的搜索树，它在B树的基础上进行了一些优化。B+树与B树相比有以下不同之处：

• 所有键值都保存在叶子节点中，内部节点只包含键值的引用。
• 所有叶子节点通过指针连接形成一个有序链表，使得范围查询更加高效。
• 叶子节点之间的指针提供了顺序访问数据的能力，适用于范围查询和顺序遍历。
  

由于B+树的叶子节点形成了一个有序链表，范围查询操作的性能非常高。而B树虽然也可以执行范围查询，但需要在内部节点上进行递归操作，性能相对较低。

总结：

• B树适用于需要频繁进行随机读写操作的场景，它对于范围查询的性能稍逊于B+树。
• B+树适用于需要进行范围查询和顺序遍历的场景，它在范围查询方面具有更好的性能。

讲了这么多，接下来将引出B+树索引。B+树索引是基于B+树发展而来的，通常在InnoDB上对某个字段添加索引，就是对这个字段构建一棵B+树。当查询条件是该字段时，查询速度非常快，对比逐行扫描，效率明显高很多。
MySQL的索引按具体作用划分，可分为聚集索引、辅助索引、唯一索引和联合索引。

1.1、聚集索引

InnoDB通过主键聚集数据，如果没有定义主键，那么InnoDB会隐式定义一个ROW ID代替。聚集索引占用的空间最大，因为它保存了全部数据。下面将通过例子理解过程：

use test;
#创建表
create table 't1' (
'id' int not null auto_increment,
'a' int not null,
'b' char(2) not null,
primary key ('id'),
key 'idx_a' ('a')
) engine=innodb default charset=utf8mb4;
# 插入数据
insert into t1(a,b) values(1,'a'),(2,'b'),(3,'c'),(4,'c'),(5,'e'),(6,'f'),(7,'g'),(8,'i');
#查看数据
select * from t1;
1
2
3
4
5
6
7
8
9
10
11
12
13

表t1的聚集索引建立在主键ID上，B+树结构图如下：

1.2、辅助索引

辅助索引我们也常叫为二级索引，一张表可以有多个，与聚集索引不同的是，辅助索引的叶子节点只存放对应索引字段的键值和主键ID。
有时需要统计表的总行数，此时优化器可能会选择辅助索引作为统计目标索引，因为它占用的空间最小。
在使用二级索引时，因为它只存储了索引字段的值和主键，所以如果需要查询其他列的数据，就需要先通过二级索引中的值找到对应的主键，在通过主键找到聚簇索引中的其他列的数据，这个过程称为回表。
为了减少回表次数，可以将语句中经常使用到的所有列以合适的顺序建立一个二级联合索引，这样所有需要的列都被这个二级索引覆盖，就不需要回表了。
当通过辅助索引来检索数据时，InnoDB先遍历辅助索引查找对应记录的主键，然后通过主键索引找到对应的行数据。下图将采用上面的数据表t1举例：

1.2.1、建表时创建索引

create table 't1' (
'id' int not null auto_increment,
'a' int not null,
'b' char(2) not null,
primary key ('id'),
key 'idx_a' ('a')
) engine=innodb default charset=utf8mb4;
1
2
3
4
5
6
7

1.2.2、使用create index创建索引

#创建索引
create index idx_b on t1(b);
#查看索引
show index from t1\G
#删除索引
drop index idx_b on t1;
1
2
3
4
5
6

1.2.3、使用alter table语句创建索引

#为b字段添加索引
alter table t1 add index idx_b(b);
#删除索引
alter table t1 drop index idx_b;
1
2
3
4

1.3、唯一索引

唯一索引由唯一约束和二级索引两部分组成，为字段添加唯一索引后，写入该字段的值必须是不同的，否则会报如下错误:

ERROR 1062 (23000): Duplicate entry 'xxx' for key 'xxx'
1

若在其中指定了前缀，那么前缀的长度必须唯一。

1.4、联合索引

联合索引适用于where条件中的多列组合，并且在某些场景中可以避免回表。

use test;

create table 't2' (
'id' int not null auto_increment,
'a' int not null,
'b' char(2) not null,
'c' datetime not null default current_timestamp,
primary key ('id'),
key 'idx_a_b' ('a','b') #选择两个字段做索引
) engine=innodb default charset=utf8mb4;
1
2
3
4
5
6
7
8
9
10

二、MySQL索引的优化

2.1、ICP

索引条件下推（Index Condition Pushdown）是针对MySQL用于在执行查询时将过滤条件推送到存储引擎层进行处理，减少不必要的数据读取和传输，从而提高查询性能。
ICP是MySQL的一个自动优化功能，一般情况下无需手动配置或启用。当满足以下条件时，MySQL会自动使用ICP进行优化：

• 查询中包含等值查询、范围查询或部分LIKE查询。
• 查询中的条件列存在适当的索引。
• 查询中的其他条件可以在存储引擎层进行检查。

在使用ICP时，需要注意以下几点：

• 确保表中的查询条件列有适当的索引。索引可以帮助存储引擎更快地定位和过滤数据。
• 避免在查询中使用不支持ICP的操作，如函数、计算、隐式类型转换等。这些操作会导致ICP无法使用，影响性能。
• 监控和分析查询执行计划、性能指标和日志，以确定ICP是否起到了优化作用。可以使用MySQL的性能分析工具和查询优化器来帮助评估和调整查询性能。
• 当需要访问全表时，ICP适用于range、ref、eq_ref和ref_or_null访问方法
• 可用于（分区）InnoDB表和MyISAM表
• 在虚拟列上创建的二级索引不支持ICP
• 引用子查询的条件不能使用ICP
• 引用存储过程、触发器的条件不能使用ICP

#关闭ICP
set optimizer_switch = 'index_condition_pushdown=off';
#开启ICP
set optimizer_switch = 'index_condition_pushdown=on';
#查看执行计划
explain select c from t3 where a=1 and b like '%j'\G
1
2
3
4
5
6

2.2、MRR

MRR（Multi-Range Read）是MySQL数据库中的一个优化技术。在传统的查询执行中，对于范围查询，MySQL需要逐条读取满足范围条件的记录，这可能导致大量的磁盘I/O操作。而MRR通过批量读取索引和数据页的方式，将范围查询的数据一次性读入内存，减少了磁盘I/O操作的次数，从而提高了查询性能。
MRR是MySQL的一个自动优化功能，一般情况下无需手动配置或启用。当满足以下条件时，MySQL会自动使用MRR进行优化：

• 查询中包含范围查询（如BETWEEN、IN、>、<等）。
• 查询的表中的索引可以支持MRR操作。
• 查询的表的存储引擎支持MRR。

在使用MRR时，需要注意以下几点：

• 确保表中的索引可以支持MRR操作MRR主要使用索引来批量读取数据，因此需要合适的索引来支持范围查询。
• 避免在查询中使用不支持MRR的操作，如函数、计算、隐式类型转换等。这些操作可能导致MRR无法使用，影响性能。
• 监控和分析查询执行计划、性能指标和日志，以确定MRR是否起到了优化作用。可以使用MySQL的性能分析工具和查询优化器来帮助评估和调整查询性能。