索引-mysql详解（三）

上篇文章说了，mysql可以指定行格式，compact，dynamic，他结构有变长字段长度列表，null值，头部和真实数据存储，compact真实数据会存一定量的页，后面指向页的页码，dynamic全部存的页码，char会根据字符集来变换存储，行溢出是65535个字节，其中null值占一个，数据长度占两个，所以实际65532个字节，也会根据不同字节来变换。Index页存储这标记是否删除，删除的数据会组成垃圾链表，也叫可重用链表，而页里的数据，会根据next_Records来组成链表方便查询，二分查找法查找不同组的槽点。

索引

首先我们明确下页分裂是什么呢，因为每页是16kb，这时候数据存满肯定会存到另一个页，每个新页并不是连续的，但下一页必须数据大于前一页，所以当后面的页数据小于前面页的时候，需要数据移动交换下，这个过程就是页分裂。

于是我们如何通过索引目录来查找呢，因为前面说了页分裂是从小打大排序好的，所以 每个目录key都是小的主键值，value则就是页码，通过key来查找对应的页码找到对应存储数据的页。

其实索引目录 就是跟之前数据页一样的存储格式，之前records_type 0 代表普通数据，2 和 3代表小记录和大记录，而剩下的1是什么呢，就是我们存储的目录项记录页（索引页）。

目录记录页 和 普通页除了records_type不同外，还有 普通页前面说了除了存储我们需要的真实数据外，还有头部信息等额外数据，以及重要的roll_point指针和transaction_id，而目录记录页只有小主键和对应的页码。

根节点：

当一个记录页不能存储所有的主键和页码的时候，这时候有多个记录页，于是就出现了上面的根节点，而第二层就是 非叶子节点或者叫内节点，而下面一层存数据的就是叫叶子节点，或者叶节点。

这样的组成之后就是b+树。

聚簇索引：当使用记录主键为值大小进行记录和页的排序，组成一个单向链表，各个存放数据的页是双向链表，b+树叶子节点存储的是完整的记录数（包括隐藏列）。这种聚簇索引并不需要我们显式的来创建，innoDB会自动创建，索引即数据，数据即索引。

二级索引：当不能以主键为查询条件来进行搜索的时候，这时候该怎么办呢，当我们以其他列来建立索引，以其他列来排序组成单向链表，存放数据的页组成双向链表，这种b+树的叶子节点存放的不是所有的数据，存放的时候二级索引和主键，非叶子节点的数据也不再是主键+页号，而是列+页号。这时候 查不到我们需要的值怎么办，用查询到的主键来回表查询。

联合索引：每个记录页都是列+页码组成，联合索引是两个列以上，先按前面的列进行排序，如果一致，则按后面的列进行排序，本质上也是一个二级索引，如果是联合索引则只会建立一颗b+树，如果联合索引的两个列分开创建索引，则是两颗树。

前面我们说了非叶子节点（内节点）存放的是列+页码，但这样是不准确的，如果列的数值都相同，那么他就没有性，于是内节点存储的还有主键值，通过三个值来确定性。

前面说的都是innodb，那么,MyISAM中的索引如何存储的呢？

我们前面说了innodb的聚簇索引是索引即数据，但myISAM是不同的，他是把索引和数据分开存储，myISAM有两个文件，数据文件 和 索引文件，数据文件即是我们存储的真实数据等，索引则会存放在索引文件里，先通过索引找到对应的行号，再通过行号去找对应的记录，意味着myISAM都是需要回表查询的。

索引的代价：

空间上的代价：显而易见，每次创建索引都需要创建一颗b+树，每个b+树每个节点都是数据页，组成起来就是很大的存储空间。

时间上的代价：我们前面说了索引在叶子节点和内节点上都是排序好的，如果每次新增修改删除数据，必定会导致b+树的修改，并且修改过后，页面的回收，页面的分配，记录的位移重新排序都需要大量的时间，所以时间消耗巨大。

联合索引查询的时候，有左原则，当我们查询的时候，只查询左边的数据也是会走索引的，而且如果查询多条数据，只要都包含在联合索引里，即使查询的时候顺序是乱的，查询优化器也会按照左原则来优化查询。

联合索引范围查找的时候，左边的列是可以使用索引查询的，但如果第二个索引需要使用到的话，左边的列则不可以使用范围查询，需要值，因为只有相同的值才可以之后再排序。

1排序的时候也必须建立索引来排序，如果没有建立索引的排序这时候就属于filesort，文件排序，这时候是非常缓慢的。

2联合索引列的排序必须一致，不能一个列asc一个列desc。

3并且排序的时候不能用不同索引的列。

所以因为排序是排好的，索引还可以用于分组。

回表查询的代价：

当我们查询二级索引的时候是顺序I/O会查询到多个不同的id，这些id会取查询聚簇索引，而且id是随机不是顺序的，于是查询聚簇索引的时候就是随机I/O,这时候性能就会下降很多，所以在有的时候，如果回表的查询记录数据太多，这时候mysql优化器会选择全表查询都不会走索引查。

当我们控制数量的时候，比如加个limit10，这时候就会更别走索引查询，同样的道理适用于排序，当后面加个limit，这时候会走索引。

如何完全不回表，当我们查询的列只有索引的时候，这时候就不需要再去查询聚簇索引里的其他值，这时候就属于覆盖索引，虽然查询的是二级索引的b+树，但是并不需要回表查询。

列的基数，当一个列都是重复的值，索引的排序没有效果，列的基数太小，则创建索引没有意义，所以创建索引选列基数大的。

自增主键的重要性:我们插入数据是按顺序在b+树排序好的，如果在中间插入一条数据，意味着重新排序和页分裂，意味着性能损耗，这时候自增主键保证新增的数据在后面。