MySQL索引底层数据结构

目录

一、索引的本质

二、MySQL的索引结构

1、二分查找树（Binary Search Tree）

2、红黑树存储（平衡二叉树）

3、B树（多路平衡二叉树）

4、B+树（MySQL的索引结构）

5、B树和B+树的区别

 三、MySQL索引的实现（存储引擎区分）

1、MyISAM索引实现（非聚集）

2、InnoDB索引实现（聚集）

3、索引节点的容量

四、联合索引与覆盖索引

1、联合索引

2、覆盖索引

五、索引列的选择

---

一、索引的本质

下面这个网址可以演示数据结构的存储和转变过程：Data Structure Visualization

索引是帮助MySQL高效获取数据的排好序的数据结构

索引存储在文件里

索引结构

• 二叉树（红黑树）
• Hash
• B tree

重点：

树结构中，每个节点是以 KEY-VALUE 存储的

key：是当前节点的索引值

value：是索引对应这一行的地址的指针

二、MySQL的索引结构

预先说明：MySQL 的索引结构是 B+ 树结构的

下面我们来分析一下为什么用B+树来做索引结构的

分析前我们先了解常用的树结构我们依次推导 

Binary Search Tree --》Red Black Tree --》B Tree --》B+ Tree

下面这个网址可以演示数据结构的存储和转变过程：

Data Structure Visualization

1、二分查找树（Binary Search Tree）

特点：左子树的节点值比父亲节点小，而右子树的节点值比父亲节点大 

基于二叉查找树的这种特点，我们在查找某个节点的时候，可以采取类似于二分查找的思想，快速找到某个节点。n 个节点的二叉查找树，正常的情况下，查找的时间复杂度为 O（logn）。 

二叉树可以解决部分线性搜索遍历的问题，但是在顺序递增的情况下也存在问题 

总结：

二叉树插入col1的数据会是单边插入，这种情况也是满足二叉查找树的条件。然而，此时的二叉查找树已经近似退化为一条链表，也是逐条遍历过去，并没有提高效率。

2、红黑树存储（平衡二叉树）

红黑树的特点：

1、平衡属性：任意节点左右子树的深度相差不大于1

2、根结点是黑色，叶子节点是不存储数据的黑色空节点

3、任何两个相邻的父子节点不能同时为红色

4、任意节点到其可到达的叶节点间包含相同数量的黑色节点

优缺点：

优点：平衡二叉树非绝对平衡树。节点有自动旋转的属性，从而调整树的深度，左右子树的深度差始终保持<=1

缺点：会随着数据量不断增加，虽然会自动进行调整，但查找效率仍会不断降低

总结：

如果数据量过大，那么红黑 树的深度会太深

树的高度不可控，随着数据里量的增大，深度会一直加深

3、B树（多路平衡二叉树）

特点：一个节点里面存储多个横向元素

• 度（degree）-节点的数据存储个数
• 叶节点具有相同的深度
• 叶节点的指针为空
• 节点中的数据 key 从左到右递增排列 

下面是转换演示:

由于我选择了Max. Degree = 3，所以每个节点存到3后就会给变结构（大家理解就好）

4、B+树（MySQL的索引结构）

特点：在B树的变种，非叶子节点把DATA去掉了，只存储key

B+树是B树的变种

• 非叶子节点不存储 data，只存储 key，可以增大存储量（只保存关键字和子节点的引用）
• 叶子节点保存相关数据（data）
• 叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系。顺序访问指针，提高区间访问的性能
• 采用左闭合区间（节点左边的数进行冗余）

问：B+树为什么要有指针？

答：为了提高范围查询的效率

比如我们要查询大于20的所有数据

B树的做法，我们查到20，然后又要返回上个分支，在向后查找，再进入，如下图

B+树的做法，找到20，然后通过指针直接向后访问，不需要回溯到根元素再从头查找

5、B树和B+树的区别

回到早先的问题

B 树：

1、每个节点都存储数据

2、B树 没有冗余

B+树：

1、只有最后的叶子节点才存有数据

2、存在冗余

3、最后的叶子节点有指针

问：为什么要用B+树，为什么B+树非叶子节点不存储数据，而是不惜冗余的代价都要将数据存储在叶子节点？

答：因为我们需要在每一个非叶子节点存储更多的KEY从而使树的深度降低，不存储数据是减少内存的消耗。

 

非叶子节点不存储data之存储KEY：比如15是KEY，后面的方框是一个指针

 三、MySQL索引的实现（存储引擎区分）

MySQL包含两种存储引擎

1. MyISAM存储引擎
2. InnoDB存储引擎

MySQL的主键是默认自动添加索引的

1、MyISAM索引实现（非聚集）

MyISAM 索引文件 和 数据文件 是 “分离的”（索引文件.myd文件）

MyISAM 的 主键索引 和 非主键索引 “结构基本是一样的”

查找及底层操作逻辑

主键存储B+树结构

Col1为主键，存入B+树结构为 key

查找 Col1=49 的底层逻辑

1. 首先 MyISAM 索引文件和数据文件是分离的
2. 先去.myi文件查找，发现 是B+树结构
3. 会快速从B+树的根节点依次向下定位，每一个节点是一次磁盘IO查找，最终从B+树中定位到我们需要的 key 值
4. 发现这个 key 值对应的 data 是一行数据的地址指针，从而直接从磁盘上面找到该数据

比如：找 49 的数据步骤如下：

1. 将第一节点中所有检索数据加入内存中，第一节点中没有，但在 15 和 56 之间
2. 于是进入第二节点，第二节点找到49，但不是叶子节点，所以只存 key 不存储数据，进入下一节点
3. 进入第三节点，找到49，是叶子节点拿到data（data就是存储的数据所在磁盘上面的文件指针） 0x90 地址，然后通过地址找到该行所有数据

2、InnoDB索引实现（聚集）

innoDB 表数据 和 索引存储 在一个文件中（.ibd文件）

innoDB 的 主键索引 和 非主键索引 “结构不同”

查找及底层操作逻辑

主键存储B+树结构

Col1为主键，存入B+树结构为 key

查找 Col1=49 的底层逻辑

1. 首先 InnoDB 表数据 和 索引存储 在同一个文件中（.ibd文件）
2. 先去.ibd文件查找，是B+树结构
3. 会快速从B+树的根节点依次向下定位，每一个节点是一次磁盘IO查找，最终从B+树中定位到我们需要的 key 值
4. 发现这个 key 值对应的 data 直接就是需要的行数据
    

比如：找 49 的数据步骤如下：

1. 将第一节点中所有检索数据加入内存中，第一节点中没有，但在 15 和 56 之间
2. 于是进入第二节点，第二节点找到49，但不是叶子节点，所以只存 key 不存储数据，进入下一节点
3. 进入第三节点，找到49，是叶子节点拿到data（data 直接就是需要的行数据） 

innoDB 的 主键索引 和 非主键索引 “结构不同”

非主键索引的底层逻辑

1. 与主键索引的区别在于，从非主键查找后，找到的 data 并非所有数据 而是 该表的  “主键”
2. 拿到主键后再去主键索引里面进行查找

3、索引节点的容量

知识普及-扇区、块、页

• 扇区是磁盘的最小存储单元
• 块是文件系统的最小存储单元,比如你保存一个记事本，即使只输入一个字符，也要占用4KB的存储，这就是最小存储的意思。
• 页呢？是B+树的最小存储单元
  下面用表格总结一下

单元	谁的（归属）	最小大小
扇区	磁盘	512B
块	文件系统	4K
页	B+	16K

B+树的索引结构。上面已经讲解过，直接上图，直接盗取一张：

图上为id：1-12的12条数据，id为主键索引。白色区域就为一个页。上图画的其实存在一个问题，没有很好的展现B+树的特点，页之间存在一个双向链表
看图我们说一下B+树的特点：

• 非叶子节点不存储数据
• 每个页可以容纳更多的节点、直接减小树的深度，减小访问IO（和B-TREE作比较）
• 叶子节点存储数据（称这种索引为聚集或者聚簇索引）
  上图我们看到是主键索引，而非主键索引叶子节点也不存储数据，而是存储的的主键。
• 叶子节点的页之间存在双向链表用于范围或者比较查找

关于IO

那上图查找6需要几次IO呢?2次。先从根节点查找，将页数据去到内存一次，然后查找到第2层的节点又是一次IO，所以两次。
基于B+TREE的特点，一般3层的树就可以存储约2000万条数据？那是如何计算的呢？

计算3层索引树能容纳的数据量

首先两个假设：

1. 主键id，我们采用bigint，8字节
2. 一条数据大小1KB

• 第一层
  一个页16K，每一个索引键的大小8字节（bigint）+6字节（指针大小），因此第一层可存储16*1024/14=1170个索引键。
• 第二层
  第二层只存储索引键，能存储多少个索引键呢？1170（这么多个页，有第一层延伸的指针）1170(每页的索引键个数，跟第一步计算一致)=1368900
  如果第二层存储数据呢？1170（这么多个页，有第一层延伸的指针）16(16KB的页大小/1KB的数据大小)=18720，也就是能存储一万多条数。
• 第三层
  直接看三层能存储多少数据？1170*1170*16=21902400，是不是很强大，此处应该有掌声和鲜花，3次IO就可以查询到2千多万左右的数据，也就是这么大的数据量如果通过主键索引来查找是很快，这就是explain一个sql时，type=const为什么性能是最优的。

回表

简单说一下回表，就是根据非主键索引查找到根节点，拿到根节点后，再去主键索引中查找相应数据。

覆盖索引

覆盖索引也就是直接从索引上就可以获取到相应的数据，不需要回表。
比如两个SQL语句
select name from xxxtable where name='张三';
select * from xxxtable where name='张三';
xxxtable中主键索引为id、非主键索引为name
第一个SQL直接从索引树上获取到数据，
第二个SQL需要跟回到主键索引中获取所需的数据。
因此在遇到性能调优时可以考虑这一点优化。

四、联合索引与覆盖索引

1、联合索引

联合索引是指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处仅在于有多个索引列

查询时使用联合索引中的一个字段，如果这个字段在联合索引中所有字段的第一个，那就会用到索引，否则就无法使用到索引

将选择性最高的列放到索引的最前列虽然是一条重要的参考准则，但通常不如避免随机IO和排序那么重要。所以在设计索引时，还要考虑到WHERE子句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响。

如下

mysql> create table t(
    -> a int,
    -> b int,
    -> primary key(a),
    -> key idx_a_b(a,b)
    -> );
Query OK, 0 rows affected (0.11 sec)

那么何时需要使用联合索引呢？在讨论这个问题之前，先来看一下联合索引内部的结果。从本质上来说，联合索引就是一棵B+树，不同的是联合索引的键值得数量不是1，而是>=2。接着来讨论两个整型列组成的联合索引，假定两个键值得名称分别为a、b如图

可以看到这与我们之前看到的单个键的B+树并没有什么不同，键值都是排序的，通过叶子结点可以逻辑上顺序地读出所有数据，就上面的例子来说，即（1,1），（1,2），（2,1），（2,4），（3,1），（3,2），数据按（a,b）的顺序进行了存放。

结论：

1、对于查询 select * from table where a=xxx and b=xxx, 显然是可以使用(a,b) 这个联合索引的，对于单个列a的查询select * from table where a=xxx,也是可以使用（a,b）这个索引的。

2、但对于b列的查询 select * from table where b=xxx,则不可以使用（a,b） 索引，其实你不难发现原因，叶子节点上b的值为1、2、1、4、1、2显然不是排序的，因此对于b列的查询使用不到(a,b) 索引

3、使用顺序：说的使用顺序的保证是，你不能跳过 第一字段 用 第二字段 比如: where b=? 或者 where b= ? order by a

可以explan看看就知道了，

4、如果是使用 select * from table where b=xxx and a=xxx，SQL执行优化器会优化该语句，实际执行的时候是把语句改成最左匹配为select * from table where a=xxx and b=xxx，但sql优化是有开销的

从而适应myqsl索引最左匹配原则，而执行优化器的这些操作，每一个MYSQL版本优化的力度都是不同的，也许你换一个旧一点的版本他就不帮你优化了呢，这会你要往哪哭去？所以，在了解原理的基础上，坚持良好习惯，让自己的代码健壮一些

联合索引的第二个好处是在第一个键相同的情况下，已经对第二个键进行了排序处理，例如在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以帮我们避免多一次的排序操作，因为索引本身在叶子节点已经排序了，如下

#===========准备表==============
create table buy_log(
    userid int unsigned not null,
    buy_date date
);

insert into buy_log values
(1,'2009-01-01'),
(2,'2009-01-01'),
(3,'2009-01-01'),
(1,'2009-02-01'),
(3,'2009-02-01'),
(1,'2009-03-01'),
(1,'2009-04-01');

alter table buy_log add key(userid);
alter table buy_log add key(userid,buy_date);

#===========验证==============
mysql> show create table buy_log;
| buy_log | CREATE TABLE `buy_log` (
  `userid` int(10) unsigned NOT NULL,
  `buy_date` date DEFAULT NULL,
  KEY `userid` (`userid`),
  KEY `userid_2` (`userid`,`buy_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

#可以看到possible_keys在这里有两个索引可以用，分别是单个索引userid与联合索引userid_2,但是优化器最终选择了使用的key是userid因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多
mysql> explain select * from buy_log where userid=2;
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys   | key    | key_len | ref   | rows | Extra |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | buy_log | ref  | userid,userid_2 | userid | 4       | const |    1 |       |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
row in set (0.00 sec)

#接着假定要取出userid为1的最近3次的购买记录，用的就是联合索引userid_2了，因为在这个索引中，在userid=1的情况下，buy_date都已经排序好了
mysql> explain select * from buy_log where userid=1 order by buy_date desc limit 3;
+--+-----------+-------+----+---------------+--------+-------+-----+----+------------------------+
|id|select_type|table  |type|possible_keys  | key    |key_len|ref  |rows| Extra                  |
+--+-----------+-------+----+---------------+--------+-------+-----+----+------------------------+
| 1|SIMPLE     |buy_log|ref |userid,userid_2|userid_2| 4     |const|  4 |Using where; Using index|
+--+-----------+-------+----+---------------+--------+-------+-----+----+------------------------+
row in set (0.00 sec)

#ps：如果extra的排序显示是Using filesort，则意味着在查出数据后需要二次排序(如下查询语句，没有先用where userid=3先定位范围，于是即便命中索引也没用，需要二次排序)
mysql> explain select * from buy_log order by buy_date desc limit 3;
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+
|id|select_type| table |type |possible_keys|key     |key_len|ref |rows|Extra                      |
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+
| 1|SIMPLE     |buy_log|index| NULL        |userid_2| 8     |NULL|  7 |Using index; Using filesort|
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+


#对于联合索引（a,b）,下述语句可以直接使用该索引，无需二次排序
select ... from table where a=xxx order by b;

#然后对于联合索引(a,b,c)来首，下列语句同样可以直接通过索引得到结果
select ... from table where a=xxx order by b;
select ... from table where a=xxx and b=xxx order by c;

#但是对于联合索引(a,b,c)，下列语句不能通过索引直接得到结果，还需要自己执行一次filesort操作，因为索引（a，c)并未排序
select ... from table where a=xxx order by c;

2、覆盖索引

InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。

使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作

注意：覆盖索引技术最早是在InnoDB Plugin中完成并实现，这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0以下的，InnoDB存储引擎不支持覆盖索引特性

对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primary key1，priamey key2，...,key1，key2，...）。

五、索引列的选择

1、官方建议主键最好是有序递增，这样对加索引的顺序检查更为高效 

2、选择离散性高的列添加索引，离散性越高选择性越好