MySQL索引原理理解

本文将渐进式的帮助理解mysql中innoDB的索引原理，InnoDB存储引擎是以页为单位进行存储的，一页的大小是16kb

1、InnoDB的行记录：

本次行记录的叙述主要以 COMPACT为主，其他的行记录都是相同的原理：

1.1 行记录的分类：

• COMPACT：紧凑型行
• REDUNDANT ：字段长度偏移行
• COMPRESSED ：压缩行
• DYNAMIC：动态行

查看当前的记录行格式：

show table status like 'table_name'

1.2 COMPACT的行格式：

2、InnoDB的页：

页是InnoDB存储引擎管理数据库的最小磁盘单位

InnoDB页结构如下图：

需要了解在文件头中存在两个属性分别是：FIL_PAGE_PREV（上页），FIL_PAGE_NEXT（下页）

具体的信息，此处略。

3、数据之间的联系：

在数据行中，有一个next_recod指向下一条元素的位置，所以在行数据之间就是类似于单向链表的结构，在页中行记录就是以链表的结构来存储的。

大概是下面这个样子：

新建一张表：

mysql> CREATE TABLE test(
    ->     c1 INT,
    ->     c2 INT,
    ->     c3 VARCHAR(10000),
    ->     PRIMARY KEY (c1)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.03 sec)
mysql>

插入几条数据：

mysql> INSERT INTO test VALUES(1, 100, 'aaaa'), (2, 200, 'bbbb'), (3, 300, 'cccc'), (4, 400, 'dddd');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql>

数据存储如下所示：

改图是简化了上面的图，其中的infimum代表页中的第一个元素，也就是最小的元素，sumpremum代表页中最后一个元素们，是页中最大的元素。

在查找时，由于主键时按照由小及大的方式存储的，所以查找时可以通过链表的形式，例如：

查找主键为3的元素：
根据元素找到主键为1的元素，依次往下找，因为链表的查询速率很高，所以很快就能找到。

一个页中只能存在16kb的数据，当数据量变大时，一个页也就不够存储相应的数据，假设一页只能存储两条数据，由第二部分得知，文件头存贮了上下页的位置信息，所以页之间的关系也就是相当于一个双向链表的形式：

这时候就会出现一个问题，查找的时候怎么才能知道我要去找那一页的数据呢

当然有相应的解决方案了，如下图所示：

这样就解决了查找的问题，例如：

查找主键是3的数据，从上面的数据得到了3所在页的地址，然后取那一页里面查找。

这时候，仔细一看，这不是树形结构吗，这就是B+树的结构。

4、索引：

上图就是一颗以主键为索引的B+树，在这颗树中，存储了所有列的信息，这类索引也叫做聚簇索引。

来创建一个主键以外的索引：

ALTER TABLE test ADD INDEX index_c2(c2);

这是以c2 为索引，在InnoDB中，每建一个索引都会新增一个B+树，所以以c2为索引的B+树如下所示：

这时候会出现一个问题，因为在这个表里面没有c3的数据，这时候查询需要c3的数据，需要执行一个叫回表的操作。例如：

查找c2=200的数据，首先找到200数据所在的页，然后根据链表找到200数据，根据这条数据的主键，去之前的B+树里面查找整条数据。

这种索引叫做二次索引，添加union关键字的也被称作唯一索引。

关于索引的基础原理就了解这么多，关于其他的索引是否命中，以后有空可以了解了解。