MySQL 之 InnoDB

1.索引数据结构选择

索引是为了提高数据查询的效率，提高读写的数据结构有很多，常见的数据结构有：哈希表，有序数组和平衡二叉查找数。而MySQL的InnoDB为什么要选择B+树呢？

• 哈希表：插入单个数据时间复杂度为O(1),查找和删除时间复杂度跟链表的长度成正比，链表长为k时时间复杂度为O(k)。哈希表中数据是无序的不支持范围查找，适合于等值查找。
• 有序数组: 通过二分法查找数据时间复杂度为O(logN)，等值和范围查询效率很高,但是新增和删除每次都要移动大量的数据，时间复杂度就变为了O(n)，成本太高。有序数组适合于静态数据索引查找。
• 平衡二叉查找树： 平衡二叉查找树的查询复杂度为O(logN),插入和删除时间复杂度为O(logN)效率也很高，中序遍历后也支持范围查找。

1.1.InnoDB为什么选择B+树

既然平衡二叉树这么高效了为什么还要用B+树作为MySQL索引的存储数据结构了。当有数据有100W时，树高20，一次查询就需要访问20个数据块，从磁盘随机读一个数据块大概需要10ms左右，而内存中读取数据是纳秒级别，在磁盘上读取20个数据块所消耗的时间就不能接受了。能不能减少数据块次数的读取就成为关键了。

这时候N叉树就出现了，当N=100时1亿个数据树高就只有3了，查找一次数据最多只有3次磁盘操作，操作磁盘次数减少，查找效率也就提高了。B+树其实就是一个父节点有N个子节点既然N叉树的一种。

1.2.N的取值

在操作系统中读取磁盘中的数据是按页读取，如果读取的数据小大超过一页就会出发多次IO操作。在选择N大小时，尽量让每个节点的大小等于一个页的大小。影响N的大小是由页大小和索引数据大小所决定，如一个也大小为4KB,索引数据为整型时，整型数据大小为4个字节，则N大小为1024。

2.InnoDB常见索引

InnoDB中的表都是根据主键顺序按索引的形式存储，这种存储方式的表称为索引组织表，InnoDB是使用的B+树作为索引模型，其数据都存放在叶子节点，父节点存储着索引值。

• 主键索引
  主键索引是将正行数据都放在叶子节点，MySql中主键索引也称聚簇索引

• 非主键索引
  非主键索引的叶子节点存储的是主键的值，非主键索引也被称之为二级索引，其中常见的非主键索引包括普通索引，覆盖索引等

2.1.主键索引与非主键索引的区别

• 主键索引叶子节点存储整行数据，非主键索引叶子节点存储主键值
• 主键索引只搜索主键的B+索引树，只需要搜索一次。非主键索引先搜索自己的索引树得到主键之后再去回查主键索引，这个过程叫做回表，非主键索引需要进行两次搜索。

2.2.主键索引使用场景

B+树作为一个动态数据结构的树为了维护索引的有序性，在插入或删除节点时都要做一些必要的维护，当数据页写满时需要页分裂，当数据页利用率不足时需要页合并。

2.2.1.主键自增长

为什么主键要定义为自增长，当主键定义为自增长时每次插入一条数据，新增数据ID为系统中最大的ID+1，这样数据就能以递增的方式插入，每次新增都是增加操作，则不会造成叶子节点页分裂的场景。

当业务字段作为主键时，业务字段不能保证有序数据随机写入，从而会造成页分页。

主键数据长度越小，普通索引所占空间就越小，主键自增长主键的数据类型为整型只占4个字节

2.2.2.业务字段作为主键

业务字段保证数据唯一
只存在一个索引

典型的场景就是KV场景

2.3.非主键索引使用场景

2.3.1.覆盖索引

联合索引是多个字段组成的索引，当查询的字段都在索引列中时就可以直接返回，不需要再次回表查询，从而减少一次查询，这个索引称之为覆盖索引。

覆盖索引可以减少树的搜索次数提高性能，可以作为一种优化方式，选择场景为：

• 当要查询的字段都可以作为索引，则使用覆盖索引
• 如果通过调整索引字段顺序，可以少维护一个索引，则优先采用这个顺序作为联合索引的顺序，联合索引遵循最左匹配原则。

2.3.2.索引下推

当一个表数据为:

ID	name	age
id1	张三	10
id2	张四	20
id3	张五	10
id4	李六	30

name和age为联合索引。
查询语句为：

select * from user where name like '张%' and age=10;

MySQL在5.6之后引入索引下推，上面SQL执行流程为：

1. 联合索引根据name查询出id1,id2,id3三条记录
2. age=10条件直接在联合索引上录刷选处理只发送id1,id3两条记录。
3. 将id1,id3发送到主键索引查询

根据上述流程来看，索引下推在查询第一颗索引树时就做了过滤减少回表的数据量查询。也可以作为一种优化方式。

参考文献

• 极客时间《MySQL实战45讲》