目录
B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树（如果不知道平衡查找树，请自行google），在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶节点指针进行连接。下图是在网上找的一张B+树示意图。
InnoDB数据页结构
页介绍
页是InnoDB存储引擎管理数据库的最小磁盘单位。页类型为B-Tree node的页，存放的即是表中行的实际数据了。
InnoDB中的页大小为16KB，且不可以更改。
InnoDB可以将一条记录中的某些数据存储在真正的数据页面之外，即作为行溢出数据。MySQL的varchar数据类型可以存放65535个字节，但实际只能存储65532个。同时InnoDB是B+树结构的，因此每个页中至少应该有两个行记录，否则失去了B+树的意义，变成了链表，所以一行记录最大长度的阈值是8098，如果大于这个值就会将其存到溢出行中。
InnoDB数据页组成部分
File Header
| 名称 | 占用空间大小 | 描述 |
|---|---|---|
FIL_PAGE_SPACE_OR_CHKSUM | 4字节 | 页的校验和（checksum值） |
FIL_PAGE_OFFSET | 4字节 | 页号 |
FIL_PAGE_PREV | 4字节 | 上一个页的页号 |
FIL_PAGE_NEXT | 4字节 | 下一个页的页号 |
FIL_PAGE_LSN | 8字节 | 页面被最后修改时对应的日志序列位置（英文名是：Log Sequence Number） |
FIL_PAGE_TYPE | 2字节 | 该页的类型 |
FIL_PAGE_FILE_FLUSH_LSN | 8字节 | 仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值 |
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID | 4字节 | 页属于哪个表空间 |
这个属性里面的值就说三个:
FIL_PAGE_OFFSET
- 每个表空间的页号唯一，四个字节足够用了
FIL_PAGE_PREV和FIL_PAGE_NEXT
- 上一页和下一页，所有的页加起来组成了一个双向链表
FIL_PAGE_TYPE
| 类型名称 | 十六进制 | 描述 |
|---|---|---|
FIL_PAGE_TYPE_ALLOCATED | 0x0000 | 最新分配，还没使用 |
FIL_PAGE_UNDO_LOG | 0x0002 | Undo日志页 |
FIL_PAGE_INODE | 0x0003 | 段信息节点 |
FIL_PAGE_IBUF_FREE_LIST | 0x0004 | Insert Buffer空闲列表 |
FIL_PAGE_IBUF_BITMAP | 0x0005 | Insert Buffer位图 |
FIL_PAGE_TYPE_SYS | 0x0006 | 系统页 |
FIL_PAGE_TYPE_TRX_SYS | 0x0007 | 事务系统数据 |
FIL_PAGE_TYPE_FSP_HDR | 0x0008 | 表空间头部信息 |
FIL_PAGE_TYPE_XDES | 0x0009 | 扩展描述页 |
FIL_PAGE_TYPE_BLOB | 0x000A | BLOB页 |
FIL_PAGE_INDEX | 0x45BF | 索引页，也就是我们所说的数据页 |
现在只需要看这最后一个索引页，又称数据页（跟InnoDB的特性有关）
属性介绍了一堆，还是不知道怎么查，举个栗子
- 假设一条查询语句定位到了这个页面
- 先将条件中的列(加深为
col)与值(假设为x)拎出来，二分法定位在哪个组。
- 假设10个槽，(0 + 9)/2 = 4, 将第四个槽的条件列的值拿出来跟
x比较，如果x大，那就从5~9的槽中继续二分找；如果x小, 那就从0~4的槽中继续二分; - 直到前后两个槽相差为1，那记录就在那个大的槽里面，又每个槽其实存的是组内最大记录，那前一个槽对应记录的下一个节点就是目标槽对应组中的最小值，顺着单链表往后一个一个比就完事了，每组最多八条记录，这速度还是很快的。
Page Header
| 名称 | 占用空间大小 | 描述 |
|---|---|---|
PAGE_N_DIR_SLOTS | 2字节 | 在页目录中的槽数量 |
PAGE_HEAP_TOP | 2字节 | 还未使用的空间最小地址，也就是说从该地址之后就是Free Space |
PAGE_N_HEAP | 2字节 | 本页中的记录的数量（包括最小和最大记录以及标记为删除的记录） |
PAGE_FREE | 2字节 | 第一个已经标记为删除的记录地址（各个已删除的记录通过next_record也会组成一个单链表，这个单链表中的记录可以被重新利用） |
PAGE_GARBAGE | 2字节 | 已删除记录占用的字节数 |
PAGE_LAST_INSERT | 2字节 | 最后插入记录的位置 |
PAGE_DIRECTION | 2字节 | 记录插入的方向 |
PAGE_N_DIRECTION | 2字节 | 一个方向连续插入的记录数量 |
PAGE_N_RECS | 2字节 | 该页中记录的数量（不包括最小和最大记录以及被标记为删除的记录） |
PAGE_MAX_TRX_ID | 8字节 | 修改当前页的最大事务ID，该值仅在二级索引中定义 |
PAGE_LEVEL | 2字节 | 当前页在B+树中所处的层级 |
PAGE_INDEX_ID | 8字节 | 索引ID，表示当前页属于哪个索引 |
PAGE_BTR_SEG_LEAF | 10字节 | B+树叶子段的头部信息，仅在B+树的Root页定义 |
PAGE_BTR_SEG_TOP | 10字节 | B+树非叶子段的头部信息，仅在B+树的Root页定义 |
1.PAGE_GARBAGE
- 在页面空间不够时，会尝试将所需空间与这个值比较，如果够，那么将记录移到临时表中，再重新插回来（记录逐条新增是不会产生碎片空间的），但很明显，这很耗性能
PAGE_BTR_SEG_LEAF和PAGE_BTR_SEG_TOP
- 后面会说到段的概念，一个索引会有两个段，一个叶子节点段，一个非叶子节点段，这两个属性分别代表一个
Segment Header，代表了哪个表空间哪个页面的哪个INODE Entry(表空间号 + 页面号 + 偏移量值)
Infimum + Supremum
- 这个值位于整个页面的第三部分
- 分别是最小记录和最大记录，属于MySQL为每个页添加的
虚拟记录 - 由五个字节的
记录头和 八个字节的 值(分别是单词Infimum和Supremum)组成 - 最小记录的记录头中
heap_no为0 - 最大记录的记录头中
heap_no为1 - 也就是说正式记录中的
heap_no属性从2开始 - 最小记录的record_type 是2
- 最大记录的record_type 是3
- 最小记录是页中单链表的头结点
- 最大记录是页中单链表的尾结点
在InnoDB存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum记录是比该页中任何主键值都要小的值，Supremum指比任何可能大的值还要大的值。这两个值在页创建时被建立，并且在任何情况下不会被删除。
由上图可以看出，行记录是记录在页中的，同时是在页内行记录之间也是双向链表链接的（在网上有看到说是单链表的）
User Records 和 Free Space
- 完全空闲的页是没有
User Records部分的 - 插入数据时，从
Free Space分配空间给User Records，直到Free Space没有空间或空间不够分配新的记录，这时需要申请新的页
Page Directory
- 这个页目录里面存的是一个一个的槽(
slot) - 每个槽指向组内最大记录的地址偏移量(我理解为页内偏移量)
- 这个数据结构是数组，按主键值从小到大排列
- 这种结构注定了查询最快的方式是二分法，时间复杂度O(lgN)
页分裂(前面说过) 时会增加一个槽
页目录中存放了记录的相对位置，有些时候这些记录指针称为Slots（槽）或者目录槽，与其他数据库不同的是，InnoDB并不是每个记录拥有一个槽，InnoDB中的槽是一个稀疏目录，即一个槽中可能属于多个记录，最少属于4个目录，最多属于8个目录。槽中记录按照键顺序存放，这样可以利用二叉查找迅速找到记录的指针。但是由于InnoDB中的Slots是稀疏目录，二叉查找的结果只是一个粗略的结果，所以InnoDB必须通过recorder header中的next_record来继续查找相关记录。同时slots很好的解释了recorder header中的n_owned值的含义，即还有多少记录需要查找，因为这些记录并不包括在slots中。
索引的实现原理
索引的优点：
- 减少查询需要扫描的数据量(加快了查询速度)
- 减少服务器的排序操作和创建临时表的操作(加快了groupby和orderby等操作)
- 将服务器的随机IO变为顺序IO(加快查询速度).
索引的缺点：
首先索引也是数据,也需要存储,因此会带来额外的存储空间占用.其次,在插入,更新和删除操作的同时,需要维护索引,因此会带来额外的时间开销.
- 索引占用磁盘或者内存空间
- 减慢了插入更新操作的速度
索引结构
MySQL主要有以下几种索引:
- B-树索引/B+树索引
- 哈希索引
- 空间数据索引
- 全文索引
B-树索引/B+树索引
1 搜索二叉树：每个节点有两个子节点，数据量的增大必然导致高度的快速增加，显然这个不适合作为大量数据存储的基础结构。
2 B树：一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足：┌m/2┐ - 1 <= j <= m - 1；一个节点的子节点数量会比关键字个数多1，这样关键字就变成了子节点的分割标志。一般会在图示中把关键字画到子节点中间，非常形象，也容易和后面的B+树区分。由于数据同时存在于叶子节点和非叶子结点中，无法简单完成按顺序遍历B树中的关键字，必须用中序遍历的方法。
3 B+树：一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足：┌m/2┐ - 1 <= j <= m；子树的个数最多可以与关键字一样多。非叶节点存储的是子树里最小的关键字。同时数据节点只存在于叶子节点中，且叶子节点间增加了横向的指针，这样顺序遍历所有数据将变得非常容易。
聚簇索引与非聚簇索引
B+树索引可以分为主键索引和辅助索引，他们不同点是，主键索引的行数据和主键B+树存储在一起，辅助索引只存储辅助键和主键。
1.主键索引
聚集索引是按每张表的主键构造的一颗B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的节点成为数据页，这个特性决定了索引组织表中数据也是索引的一部分。由于实际的数据页只能按照一颗B+树进行排序，所以每张表只能拥有一个聚集索引。查询优化器非常倾向于采用聚集索引，因为其直接存储行数据，所以主键的排序查询和范围查找速度非常快。
不是物理上的连续，而是逻辑上的，不过在刚开始时数据是顺序插入的所以是物理上的连续，随着数据增删，物理上不再连续。
2.辅助索引
辅助索引页级别不包含行的全部数据。叶节点除了包含键值以外，每个叶级别中的索引行中还包含了一个书签，该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。其中存的就是聚集索引的键。
辅助索引的存在并不影响数据在聚集索引的结构组织。InnoDB会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后通过主键索引找到一个完整的行记录。当然如果只是需要辅助索引的值和主键索引的值，那么只需要查找辅助索引就可以查询出索要的数据，就不用再去查主键索引了。
MyISAM——非聚簇索引
- MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。
- 非聚簇索引的数据表和索引表是分开存储的。
- 非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。
- 只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)
*最开始我一直不懂既然非聚簇索引的主索引和辅助索引指向相同的内容，为什么还要辅助索引这个东西呢，后来才明白索引不就是用来查询的吗，用在那些地方呢，不就是WHERE和ORDER BY 语句后面吗，那么如果查询的条件不是主键怎么办呢，这个时候就需要辅助索引了。
InnoDB——聚簇索引
- 聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
- 聚簇索引的数据和主键索引存储在一起。
- 聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。
- 在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。
可以说数据库必须有索引，没有索引则检索过程变成了顺序查找，O(n)的时间复杂度几乎是不能忍受的。我们非常容易想象出一个只有单关键字组成的表如何使用B+树进行索引，只要将关键字存储到树的节点即可。当数据库一条记录里包含多个字段时，一棵B+树就只能存储主键，如果检索的是非主键字段，则主键索引失去作用，又变成顺序查找了。这时应该在第二个要检索的列上建立第二套索引。 这个索引由独立的B+树来组织。有两种常见的方法可以解决多个B+树访问同一套表数据的问题，一种叫做聚簇索引（clustered index ），一种叫做非聚簇索引（secondary index）。这两个名字虽然都叫做索引，但这并不是一种单独的索引类型，而是一种数据存储方式。对于聚簇索引存储来说，行数据和主键B+树存储在一起，辅助键B+树只存储辅助键和主键，主键和非主键B+树几乎是两种类型的树。对于非聚簇索引存储来说，主键B+树在叶子节点存储指向真正数据行的指针，而非主键。
InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。
MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。
我们重点关注聚簇索引，看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？
1 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位，后面会涉及）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。
*使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。
*因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。但是由于主索引存储的是数据本身，因此聚簇索引会占用更多的空间。
*聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。
此外MyISAM和innoDB的区别总结如下：
索引的管理
索引在创建或者删除时，MySQL会先创建一个新的临时表，然后把数据导入临时表，删除原表，再把临时表更名为原表名称。
但是在InnoDB Plugin版本开始，支持快速创建索引。其原理是先在InnoDB上加一个s锁，在创建过程中不需要建表，所以速度会很快。创建过程中由于加了s锁，所以只能进行读操作，不能写操作。
什么时候要使用索引？
- 主键自动建立唯一索引；
- 经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
- 作为排序的列要建立索引；
- 查询中与其他表关联的字段，外键关系建立索引
- 高并发条件下倾向组合索引；
- 用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引
什么时候不要使用索引？
- 经常增删改的列不要建立索引；
- 有大量重复的列不建立索引；
- 表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。
索引失效的情况：
- 在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
- 在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
- LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
- 在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
- 在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。（经erwkjrfhjwkdb同学提醒，不等于，包括<符号、>符号和！，如果占总记录的比例很小的话，也不会失效）
- 在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
- 字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致失败，应该改为WHERE email='99999'。
- 在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
- 如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。
- 尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；
索引的优化
1、最左前缀
索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3中情况可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。
根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推。
2、带索引的模糊查询优化
在上面已经提到，使用LIKE进行模糊查询的时候，'%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化（上文有讲到）。
3、为检索的条件构建全文索引，然后使用
SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);
4、使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
Hash索引
哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。对于hash相同的，采用链表的方式解决冲突。
hash索引的限制：
- 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
- 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
- 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
- 哈希索引只支持等值比较查询，包括=、IN()、<>（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如WHERE price>100。
- 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
- 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。
575
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
