MYSQL索引原理（索引之最）

索引之概念

1. 索引是什么？

   是一种提高查询效率的数据结构，它好比一个字典目录，能够快速定位到对应的记录。

   索引一般是刷在磁盘上的文件

2. 索引有哪几种类型？

   从三种不同的维度：数据结构维度、物理存储维度和逻辑维度。

   数据结构维度：B+ 树索引、哈希索引、文本索引、R-Tree 索引

   物理存储维度：聚集索引、非聚集索引

   逻辑索引：主键索引（特殊的唯一索引）、唯一索引、组合索引、普通索引，空间索引

数据结构

1. 平衡二叉树（balance tree）：左子树节点小于当前节点，右子树节点要大于当前节点，左子树树高和右子树树高绝对值小于等于1

2. B树：B树中所有节点中孩子节点个数的最大值成为B树的阶，通常用 m表示，从查找效率上考虑一般要求m>=3。

   一颗m阶的树或者是一颗空树，或者满足以下条件的m叉树。

   1. 每一个节点最多有m个分支；而最少分支树要看是否为根节点，如果是根节点且不是叶子节点，则至少要有两个分支，如果非根非叶则至少需要ceil（m/2）分。

   2. 如果一个节点有n-1个关键字，那么该节点有那个分支。这个n-1关键字是递增顺序排序。

   3. 如果每一个节点为：

      n	k1	k2	....	kn
      p0	p1	p2	...	pn

      其中n为该节点的关键字个数；ki为该节点的关键字且满足ki<ki+1；pi为该节点的孩子节点指针且满足pi所指结点上的关键字大于ki且小于ki+1，p0所指结点上的关键字小于k1，pn所指节点上的关键字大于kn。

   4. 结点内各关键字互不相等且按从小到大排列。

   5. 叶子结点处于同一层；可以用空指针表示，是查找失败到达的位置。

3. B+ 树： B树的升级版，数据结构与平衡二叉树相同，比 BT 高度要低，比BT 要胖，所有叶子节点存储数据，非叶子节点存储键值，与B树 的区别在于，非叶子节点不存储数据，而B树 存储数据，优势在于 非叶子节点存储的键值更多，高度更低，IO 消耗更低。

4. 哈希结构：k-v 结构存储，更适合等值。

物理存储

1. 聚集索引：数据行的物理顺序和列值（主键的那一列）逻辑顺序相同，一个表中这能有一个聚集索引，聚集索引在叶子节点是表中的数据。

2. 非聚集索引：与聚集索引相反，在叶子节点存储的是主键和索引列。

逻辑索引

1. 主键索引：一种特殊的唯一索引，但是列值不能为空

2. 唯一索引： 索引列值必须是唯一的，但允许为空

3. 普通索引： MySQL基本索引，允许值空，重复。

4. 组合索引：多个字段创建的索引，遵循最左前缀原则。

5. 空间索引：MySQL 在5.7 支持了空间索引，在空间索引需要遵循openGIS 几何数据模型。

索引之数据结构B+树

数据库为何选择B+ 树作为索引数据结构，可以通过三个维度去分析：查询速度、查询效率和查找磁盘数 。

1. 哈希数据结构：我们都知道哈希是 k-v 结构，key 和value 是一一对应的，因此哈希数据结构更适合等值匹配

   效率更高，针对范围索引时，效率和性能很。

   1. 二叉树结构：二叉树时每个节点最多这又两个，分为左节点和右节点，左节点要小于当前节点，右节点需要大于当前节点，但是特殊的二叉树会导致全表扫描，例如：

      

      这样的二叉树会导致每一个节点都查找，性能和效率不太理想。

   2. 平衡二叉树：在二叉树基础上添加了必须满足的条件就是左树高和右树高之差的绝对值需要小于等于1，这样何以保证不会出现上图特殊的二叉树。看是可行，但是当数据很大时，势必导致树高很高，IO 数会增多性能很差。

   3. B树：B树相比平衡二叉树，高度更低，IO 树减少，而且有二叉树特征。

   4. B+树：是B树的升级版，和B最大区别是非叶子节点的子节点这存储键值，而B树存储键值还存储了数据。B+树所有叶子节点存储数据。

B+树索引过程

 

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
insert into user values(210,'小伦',43,'2021-01-20');
insert into user values(230,'俊杰',48,'2021-01-21');
insert into user values(130,'紫琪',36,'2020-01-21');
insert into user values(121,'立红',32,'2020-01-21');
insert into user values(200,'易迅',37,'2020-01-21');
insert into user values(300,'小军',49,'2021-01-21');
insert into user values(100,'小燕',28,'2021-01-21');
​
执行查询语句：select * from user where age = '32'
​
​

假如B+树索引idx_age 结构如下：

由于B+树每个叶节点保存数据，首先通过idx_age 二分查找 找见37 由于32<37, 搜索左路分支，找到磁盘块。继续遍历找到对应的数据主键ID 121

然后根据主键ID 找到对应数据，如下：

因此这条sql查询执行了两次树搜索，一次 idx_age，一次主键索引，这次查询中主键索引也叫回表查询

索引之失效

在日常开发中，常遇到的索引失效：

1. 查询条件中包含or，可能导致索引失效。

2. 如果字段类型是字符串，where时一定要引号括起来，否在不会走索引

3. like 通配符可能不走索引

4. 联合索引时，如果查询时的条件列不是索引列的第一列索引失效

5. 在索引列上用MySQL内置函数索引失效

6. 对索引列进行列运算（如：+、-、*、/） 索引失效

7. 索引列上使用（！=，<> 、not in ）可能会导致索引失效

8. 索引上使用 is null 或 is not null 可能会导致索引失效

最左前缀原则

上述表user添加上 name 和age 字段联合索引，联合索引示意图如下：

联合索引项首先要按照name 从小到大排序，汉字按照首字母排序，如果name 名字相同，按照age 排序

比如：

select * from user where name like '小%' order by age desc;

 

首先会通过 idx_name_age 联合索引最左前缀 “小” 找到小燕、小伦和小军，分别在在拿到对应ID 100、210、300

通过主键索引回表三次完成 小燕、小伦和小军 数据。

最左前缀原则在联合索引最左N个字段，比如 联合索引（a，b，c）相当于 创建了三个索引 （a），(a,b)，（a,b,c）

大大提高了索引的利用率。

索引之索引下推

还是基于联合索引，sql如下：

select * from user where name like '小%' and age=28;

在MYSQL 5.6版本前，idx_name_age 索引树回表如下：

 

在mysql 5.6版本前 需要回表3词首先通过最左前缀 “小”找见 三条数据后，回表再去筛选符合 age=28的数据，有人会问为什么不在 idx_name_age 索引后顺便比较age 后在回表 这样减少回表次数提高查询效率？是的在MYSQL 5.6 后，引入来了索引下推概念，在idx_name_age 比较了 age 在回表查询数据。

索引之大表添加索引

由于添加索引会锁表，新增数据会在阻塞状态，在生产环境一下，很有可能导致生产事故

那么如何操作更安全呢？

• 先创建临时表，表结构与原表相同

• 在临时表中添加索引

• 把原表中数据添加到临时表

• rename 原表，把临时表改为原表。