MYSQL数据库------索引底层结构

MySql的存储结构

MySQL作为一个关系型数据库，最核心也是最基本的一个功能就是存储数据，而数据的存储最终都是存储在磁盘文件上的，不过MySQL并不会像Java中那样把一个对象序列化之后直接存储到磁盘上去，因为这样的话后续就不太方便来查找数据了，所以MySQL在一行数据的存储上做了一定的设计。

我们平时是以记录为单位来向表中插入数据的，我们在Navicat等客户端看到是一个表格，里面有一行一行的记录，比如下面这样：

但这些记录最终在磁盘上的存放方式是有规则的，一个记录在磁盘上的存放方式也被称为行格式，行格式的简单的示意图如下：

重点关注一下上图红色部分的记录头信息，它里面会有个next_record指针，用于指向下一条记录。像下图这样：

所以最终我们插入到表的记录其实就是这样一行一行的存储到了磁盘的数据文件中，然后用这个next_record指针彼此关联了起来。

不过大部分情况下一个表中可能有成千上万的记录，所以MySQL为了更方便管理这些记录，规定用数据页为基本单位来存放记录。一个数据页的容量大小是固定的，默认大小16KB，所以一个数据页中能存放多少条记录是由这些记录的大小来决定的，记录本身占用的空间小就能多存一些记录。假设一条记录大小是30个字节的话，那么一个数据页大概能存储16*1024/30 ≈ 500多条记录，在我们这个文章的示例中，为了讲解方便，我们就约定一个数据页就只能存放10条记录好了。

当一个数据页存满了记录之后，MySQL就开辟一个新的页继续存储，这个过程也就是就是MySQL中的页分裂，新开辟出来的数据页会跟原来的数据页之间有个双向链表的互相关联着，像下图这样：

好了，现在我们知道了记录是存在数据页中的，数据页内的记录之间有个单向链表指向下一条记录，然后数据页之间有个双向链表互相关联，那么这个时候我就可以来思考一下MySQL中按主键ID来查找数据是怎么找的了。

比如我们要查找ID=12这条记录，MySQL首先去到数据页1中从第一条记录开始查找，如果第一条记录不匹配，就沿着单向链表查找下一条记录，以此类推会一次把数据页1中的所有记录都查找出来对比一下。如果数据页1中没有找到目标记录，那么就沿着数据页1与数据页2之间的双向链表从数据页2的第一条记录继续查找，如果找到了目标数据就返回给客户端，没找到的话就沿着单向链表把数据页2中的记录都遍历一遍。如果数据页2中的记录也都遍历完了，会看下数据页2还有没有关联下一个数据页3，有的话继续查找，没有就返回空给客户端。

看着上面的操作过程，不知道大家有没有感觉出来，这个过程其实就是数据库中的全表扫描。利用数据页内部记录之间的单向链表跟数据页之间的双向链表依次把表中每条记录都遍历一下，这个过程就是全表扫描。
全表扫描的效率很低 所以就有了索引

MySql的索引结构原理

MySQL中的InnoDB引擎使用B+Tree结构来存储索引，可以尽量减少数据查询时磁盘IO次数，同时树的高度直接影响了查询的性能，一般树的高度维持在 3~4 层。
B+Tree由三部分组成：根root、枝branch以及Leaf叶子，其中root和branch不存储数据，只存储指针地址，数据全部存储在Leaf Node，同时Leaf Node之间用双向链表链接

从下面可以看到，每个Leaf Node是三部分组成的，即前驱指针p_prev，数据data以及后继指针p_next，同时数据data是有序的，默认是升序ASC，分布在B+tree右边的键值总是大于左边的，同时从root到每个Leaf的距离是相等的，也就是访问任何一个Leaf Node需要的IO是一样的，即索引树的高度Level + 1次IO操作。
我们可以将MySQL中的索引可以看成一张小表，占用磁盘空间，创建索引的过程其实就是按照索引列排序的过程，先在sort_buffer_size进行排序，如果排序的数据量大，sort_buffer_size容量不下，就需要通过临时文件来排序，最重要的是通过索引可以避免排序操作（distinct，group by，order by）。

这么说可能比较抽象，我们把存放了1000条学生信息的聚族索引结构画出来看一下（聚簇索引）：

上面的图，我们是约定一个普通数据页最多放10条记录，一个索引页最多放50条记录。由于总共有1000条学生记录，那么普通数据页总共是100个，然后第二层的索引页每页可以放50条记录，就有2个索引页，最顶层的索引里面只有2条记录分别关联着第二层的两个索引页。

对着上面的图，我们再来看下如果要查找ID=1000的这个学生记录，要怎么来查找呢？首先在最顶层的索引页201中判断，1000大于501，说明目标记录在ID=501关联的下级索引页中，也就是索引页102，接着到索引102中来判断，1000大于901，说明目标记录在ID=991关联的下级数据页中，也就是数据页100，最后到数据页100中去查找，利用前面说的数据页内部记录之间的单向链表与页目录，就可以快速的找到目标记录了，像下图这样：

可以明显的看到，有了索引之后，我们查找到ID=1000的记录，就只需要在2个索引页中判断，最后在一个数据页中进行快速的比对就可以了，完全不用从第一个数据页的第一条记录开始依次遍历下来进行查找，其实这个就是利用索引B+树结构来提高查询速度的原理，比全表扫描的性能高太多了。

聚簇索引

MySQL中的表是IOT（Index Organization Table，索引组织表)，数据按照主键id顺序存储（逻辑上是连续，物理上不连续），而且主键id是聚集索引（clustered index），存储着整行数据，如果没有显示的指定主键，MySQL会将所有的列组合起来构造一个row_id作为primary key，例如表users(id, user_id, user_name, phone, primary key(id))，id是聚集索引，存储了id, user_id, user_name, phone整行的数据。

非聚簇索引

辅助索引也称为二级索引，索引中除了存储索引列外，还存储了主键id，对于user_name的索引idx_user_name(user_name)而言，其实等价于idx_user_name(user_name, id)，MySQL会自动在辅助索引的最后添加上主键id

索引回表

上面证明了辅助索引包含主键id，如果通过辅助索引列去过滤数据有可能需要回表，举个例子：业务需要通过用户名user_name去查询用户表users的信息，业务接口对应的SQL：

select  user_id, user_name, phone from users where user_name = 'Laaa';

我们知道，==对于索引idx_user_name而言，其实就是一个小表idx_user_name(user_name, id)，==如果只查询索引中的列，只需要扫描索引就能获取到所需数据，是不需要回表的，如下SQL语句：

SQL 1:  select id, user_name from users where user_name = 'Laaa';

SQL 2:  select id from users where user_name = 'Laaa';

SQL 1和SQL 2的执行计划中的Extra=Using index 表示使用覆盖索引扫描，不需要回表，再来看上面的业务SQL：

select user_id, user_name, phone from users where user_name = 'Laaa';

可以看到select后面的user_id，phone列不在索引idx_user_name中，就需要通过主键id进行回表查找，MySQL内部分如下两个阶段处理：

Section 1：select **id** from users where user_name = 'Laaa'  //id = 100101
Section 2:   select user_id, user_name, phone from users where id = 100101;

将Section 2的操作称为回表，即通过辅助索引中的主键id去原表中查找数据。

复合索引

mysql> create index idx_status_create_time on trade_info(status, create_time);

复合索引设计原则

• 将范围查询的列放在复合索引的最后面，例如idx_status_create_time。
• 列过滤的频繁越高，选择性越好，应该作为复合索引的前导列，适用于等值查找，例如idx_user_id_status。
  冷知识：如果where多个条件 但是开发者没有创建一个复合索引，而是创建多个单独的索引，当多条件查询的时候mysql会把这几个单独索引复合成一个索引