Mysql 索引实现-MyISAM & InnoDB

讲索引之前先讲讲B树，B树就是二叉搜索树，而B-，B+，B*是多路搜索树。

B-树

B+树

B+基本与B-树同，以下是与B-树的区别:

• 非叶子结点的指针与关键字个数相等，而B-树的关键字=指针个数-1;
• 指针P[i]，指向关键字值属于[K[i], K[i+ 1])范围的子树，左闭右开，而在B-树是开区间。
• 所有叶子结点增加一个指针，指向下一个的兄弟叶子节点。
• 所有关键字都出现在叶子结点的链表中(稠密索引)， 且链表中的关键字恰好是有序的，搜
  索只会在叶子节点结束，叶子节点存储所有关键字的值。
• 不可能在非叶子结点命中;非叶子结点相当于是叶子结点的索引(稀疏索引)，叶子结点相
  当于是存储。

B树与B+树的区别:

• 所有的叶子节点包含了全部关键子信息，及指向含有这些关键字记录的指针，且叶子结点本
  身依关键字的大小自小而大的顺序链接。(而B树的叶子节点并没有包括全部需要查找的信
  息)。
• 所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大(或最小)关键
  字。(而B树的非终节点也包含需要查找的有效信息)。

这些链指针在链表中是有序存储的，在搜索中能省大量的时间。那这些链指针可不可以加在所有的节
点中呢，答案是可以的，除了根节点，所有的节点都可以加上链指针。这就是B*树索引。

B*树

特点:

• B*树定义了非叶子结点关键字个数至少为(2/3)M, 即块的最低使用率为2/3 (代替B+树的1
  /2)
• 在非根和非叶子结点增加指向兄弟的指针。

B+树与B树的区别:
(1) B+树的分裂:当-一个结点满时，分配- -个新的结点，并将原结点中1/2的数据复制到新结点，最
后在父结点中增加新结点的指针; B+树的分裂只影响原结点和父结点，而不会影响兄弟结点，所以它
不需要指向兄弟的指针。
(2) B树的分裂:当一个结点满时，如果它的下一个兄弟结点未满，那么将一部分数据移 到兄弟结
点中，再在原结点插入关键字，最后修改父结点中兄弟结点的关键字(因为兄弟结点的关键字范围改
变了) ;如果兄弟也满了，则在原结点与兄弟结点之间增加新结点，并各复制1/3的数据到新结点，最
后在父结点增加新结点的指针。
结论:
所以，B*树分配新结点的概率比B+树要低，空间使用率更高。

关于回表查询:
比如select name from table where id=?,如果name没有索引，那在查询的时候先得得到的是id对应这条数据所在的行数。拿着这个行数，再去表中查询这条数据，得到name字段。而拿着这个行数去得到name字段的动作，就是回表查询。
我们如何避免回表查询呢，首先就是不要用”*“查询，因为这时候会默认查询的字段没有索
引，必定进行回表查询。

Mysql 索引实现-MyISAM & InnoDB： important

聚簇索引： 索引 和 数据文件为同一个文件。非聚簇索引： 索引 和 数据文件分开的索引。

MyISAM & InnoDB 都使用B+Tree索引结构。但是底层索引存储不同，MyISAM 采用非聚簇索引，而InnoDB采用聚簇索引。

MyISAM索引原理：采用非聚簇索引-MyISAM myi索引文件和myd数据文件分离，索引文件仅保存数据记录的指针地址。叶子节点data域存储指向数据记录的指针地址。(底层存储结构： frm -表定义、 myi -myisam索引、 myd-myisam数据)

MyISAM索引按照B+Tree搜索，如果指定的Key存在，则取出其data域的值，然后以data域值-数据指针地址去读取相应数据记录。辅助索引和主索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。MyISAM索引树如下：

InnoDB优势：高扩展性，充分发挥硬件性能、 Crash Safe、 支持事务、 可以在线热备份

InnoDB特性：

1. 事务支持（ACID）2. 扩展性优良 3. 读写不冲突 4. 缓存加速

2. 功能组件: redo/undo & 异步IO & MVCC & 行级别锁 & Page Cache（LRU）
   
   InnoDB物理存储文件结构说明：

   InnoDB以表空间Tablespace(idb文件)结构进行组织，每个Tablespace 包含多个Segment段，每个段(分为2种段：叶子节点Segment&非叶子节点Segment), 一个Segment段包含多个Extent，一个Extent占用1M空间包含64个Page（每个Page 16k），InnoDB B-Tree 一个逻辑节点就分配一个物理Page,一个节点一次IO操作。,一个Page里包含很多有序数据Row行数据，Row行数据中包含Filed属性数据等信息。

• 表空间(ibd文件)

• 段(一个索引2段：叶子节点Segment & 非叶子节点Segment）

• Extent（1MB）：一个Extent(1M) 包含64个 Page(16k),一个Page里包含很多有序行数据 , InnoDB B-Tree 一个逻辑节点就分配一个物理Page,一个节点一次IO操作。

• Page(16KB)

• Row

• Field

表插入数据扩展原理： 一次扩张一个Extent空间（1M），64个Page,按照顺序结构向每个page中插入顺序。

InnoDB逻辑组织结构：

每个索引一个B+树， 一个B+树节点 = 一个物理Page（16K）

• 数据按16KB切片为Page 并编号， 编号可映射到物理文件偏移(16K * N）， B+树叶子节点前后形成双向链表， 数据按主键索引聚簇， 二级索引叶节点存储主键值， 通过叶节点主键值回表查找数据。

InnoDB索引原理：
采用聚簇索引- InnoDB数据&索引文件为一个idb文件，表数据文件本身就是主索引，相邻的索引临近存储。 叶节点data域保存了完整的数据记录(数据[除主键id外其他列data]+主索引[索引key:表主键id])。 叶子节点直接存储数据记录，以主键id为key,叶子节点中直接存储数据记录。(底层存储结构: frm -表定义、 ibd: innoDB数据&索引文件)

注：由于InnoDB采用聚簇索引结构存储，索引InnoDB的数据文件需要按照主键聚集，因此InnoDB要求表必须有主键(MyISAM可以没有)。如果没有指定mysql会自动选择一个可以唯一表示数据记录的列作为主键，如果不存在这样的列，mysql自动为InnoDB表生成一个隐含字段(6个字节长整型)作为主键。 InnoDB的所有 辅助索引 都引用 数据记录的主键 作为data域。

聚簇索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得数据记录主键，然后用主键到主索引中检索获得数据记录。InnoDB聚簇索引结构：

索引查找流程：

1.索引精确查找

确定定位条件, 找到根节点Page No, 根节点读到内存, 逐层向下查找, 读取叶子节点Page,通过 二分查找找到记录或未命中。（select * from user_info where id = 23）

2.索引范围查找

读取根节点至内存, 确定索引定位条件id=18, 找到满足条件第一个叶节点, 顺序扫描所有结果, 直到终止条件满足id >=22 （select * from user_info where id >= 18 and id < 22）

3.全表扫描

直接读取叶节点头结点， 顺序扫描， 返回符合条件记录， 到最终节点结束

（select * from user_info where name = ‘abc’）

4.二级索引查找

二级索引查找过程
Create table table_x(int id primary key, varchar(64) name,key sec_index(name) )

• Select * from table_x where name = “d”;

通过二级索引查出对应主键，拿主键回表查主键索引得到数据， 二级索引可筛选掉大量无效记录，提高效率

Innodb对索引的优化 Insert Buffer todo

Innodb Buffer Pool： todo

Innodb 异步IO框架:

Innodb ACID如何保证:

• 原子性 redo + undo • 一致性 redo • 隔离性 锁 + MVCC • 持久性 redo

Innodb Redo日志:

Innodb 行级别锁:

参考资料：https://www.jianshu.com/p/486a514b0ded