mysql-Innodb存储引擎

在InnoDB存储引擎中，每张表都有个主键(Primary key)

如果在创建表时没有定义主键，则InnoDB存储引擎会选择表中符合条件的列去创建主键。

条件：

1.  首先判断表中是否有非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键。

2. 如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。

当表中存在多个非空的唯一索引的时候，InnoDB存储引擎会根据建表时所创建的第一个非空唯一索引作为主键。

InnoDB的表中的数据记录本身被存于主键索引（一颗B+Tree）的叶子节点上

索引的数据结构就是B+树。

而这棵树的所有叶子节点（每个叶子节点为一页，大小为16K，如果每行记录大小为1K，则每个叶子节点可以存储16个行记录），存储着一行一行的数据。如下图所示每个叶节点存储了10行记录。

索引树何时会建立

主键ID索引树会在建表的时候自动建立好，当我们为表里某个字段加索引是InnoDB会怎么建立索引树？比如我们要给user_name这个字段加索引，那么InnoDB就会建立user_name索引B+树，节点里存的是user_name的key，注意叶子节点存储的是主键key，拿到主键key后，InnoDB才会去主键索引树根据刚在user_name索引树找到的主键key查找对应的数据。

问题来了，为什么InnoDB只在主键索引树的叶子节点存储了具体数据，但是其他索引树却不存在具体数据，而要多此一举找到主键，在主键索引树找到对应的数据呢？

其实很简单，因为InnoDB需要节省存储空间，一个表里面可能有多个索引，InnoDB都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引树文件就变得非常巨大(数据极度冗余)，从节约磁盘空间的角度来说，真的没有必要每个字段索引树都存具体数据，通过这种看似多此一举的步骤，在牺牲较少的查询性能下节省了巨大的磁盘空间，这是非常值得的。

innodb数据文件类型如下

InnoDB存储引擎的最小储存单元——页（Page），一个页的默认大小是16K，可以通过以下参数进行设置。 

在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节。

innodb的所有数据文件（后缀为ibd的文件），他的大小始终都是16384（16k）的整数倍。 

frm文件：存储这张表的表结构
ibd文件：存储这张表的所有数据行和索引字段

1、InnoDB存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针，在B+树中叶子节点存放数据，非叶子节点存放键值+指针。

B+树数据查询过程

我们先将数据记录按主键进行排序，分别存放在不同的页中（为了便于理解我们这里一个页中只存放3条记录，实际情况可以存放很多）

除了存放数据的页以外，还有存放键值+指针的页，如图中page number=3的页，该页存放键值和指向数据页的指针，这样的页由N个键值+指针组成。

当然它也是排好序的。这样的数据组织形式，我们称为索引组织表。

现在来看下，要查找一条数据，怎么查？

如：select * from user where id=5;

这里id是主键,我们通过这棵B+树来查找，首先找到根页，你怎么知道user表的根页在哪呢？

其实每张表的根页位置在表空间文件中是固定的，即page number=3的页（这点我们下文还会进一步证明）

找到根页后通过二分查找法，定位到id=5的数据应该在指针P5指向的页中，那么进一步去page number=5的页中查找，同样通过二分查询法即可找到id=5的记录：

索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据；

怎么得到B+树的高度

B+树的高度通常是1-3，1指的是只有一个根节点（根页），2指的是除了根节点，还有一层叶子节点，3以此类推。

在InnoDB的表空间文件中，约定page number为3的代表主键索引的根页，而在根页偏移量为64的地方存放了该B+树的page level。

如果page level为1，树高为2，page level为2，则树高为3。即B+树的高度=page level+1；下面我们将从实际环境中尝试找到这个page level。

在实际操作之前，你可以通过InnoDB元数据表确认主键索引根页的page number为3，你也可以从《InnoDB存储引擎》这本书中得到确认。

 可以看出数据库dbt3下的customer表、lineitem表主键索引根页的page number均为3，而其他的二级索引page number为4。

因为主键索引B+树的根页在整个表空间文件中的第3个页开始，所以可以算出它在文件中的偏移量：16384*3=49152（16384为页大小）。

另外根据《InnoDB存储引擎》中描述在根页的64偏移量位置前2个字节，保存了page level的值

因此我们想要的page level的值在整个文件中的偏移量为：16384*3+64=49152+64=49216，前2个字节中。

接下来我们用hexdump工具，查看表空间文件指定偏移量上的数据：

linetem表的page level为2，B+树高度为page level+1=3；

region表的page level为0，B+树高度为page level+1=1；

customer表的page level为2，B+树高度为page level+1=3；

lineitem表的数据行数为600多万，B+树高度为3，customer表数据行数只有15万，B+树高度也为3。可以看出尽管数据量差异较大，这两个表树的高度都是3

换句话说这两个表通过索引查询效率并没有太大差异，因为都只需要做3次IO。那么如果有一张表行数是一千万，那么他的B+树高度依旧是3，查询效率仍然不会相差太大。

region表只有5行数据，当然他的B+树高度为1。

问题1-----myql主键设计原则

原则上主键是一个整型的自增量。

 如果选择一个非自增量的值作为索引，那么这个值的INSERT就很有可能导致索引的分裂，那么INSERT的效率势必会降低；而如果使用自增量，那么由于数据是增加的，整个数据也就写在了数据文件的后端，不会导致索引分裂。 

问题2-----为什么MySQL的索引要使用B+树而不是其它树形结构?比如B树？

因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出）

指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；

那么回到我们开始的问题，通常一棵B+树可以存放多少行数据？

这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。

上文我们已经说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）。

那么现在我们需要计算出非叶子节点能存放多少指针？

其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节

我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。

那么可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。

根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170*1170*16=21902400条这样的记录。

所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。

在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

mysql表中的数据是如何存储的

计费B+树的高度