数据库笔记6---什么是索引、分类、B+Tree特点、面试题

---

一、什么是索引

  索引是存储引擎用于快速找到记录的一种数据结构，不同的索引类型底层采用不同的数据结构。索引优化应该是查询性能优化中最有效的手段。索引可以轻易将查询性能提高几个数量级，“最优”的索引有时比“好的”索引性能要好两个数量级。索引的优点可以简单归纳如下：

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序、分组和临时表。
3. 索引可以将随机I/O变为顺序I/O 。

只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效；对于中到大型的表，索引才会变得更高效。

二、innodb索引的类型

  innodb是MySQL数据库的默认存储引擎，也是目前企业生产环境中使用最多的存储引擎，所以这里就直接总结innodb支持的索引类型。

1.聚簇索引

  聚簇索引实际上在同一个结构中保存了B+Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。因为无法同时把数据行放在两个不同的地方，所以一个表只能有一个聚簇索引。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。如果你已经设置了主键为聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

2.非聚簇索引(辅助索引、二级索引)

  innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

---

三、B+Tree的特点

  innodb底层采用B+Tree结构，这意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同；因为B+Tree按照顺序存储数据，所以MySQL可以用来做order by和group by。因为索引中存储了实际的列值，所有某些查询只使用索引就能够完成全部的查询，即覆盖索引。

1. 聚簇索引插入的速度严重依赖于插入顺序。

待补充。

四、面试题

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

  简单版本回答是：因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），非叶子节点保存的指针少的情况下，又要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低。

2. InnoDB一棵B+树可以存放多少行数据？

  问题的简单回答是：约2千万行。 InnoDB存储引擎的最小存储单元是页，InnoDB页的大小默认是16k，数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。如果采用B+树的方式组织这些数据。如图所示：

  页可以用于存放数据也可以用于存放键值+指针，在B+树中叶子节点存放数据，非叶子节点存放键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据；

  这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点。那么现在我们需要计算出非叶子节点能存放多少指针，其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。

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

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

  根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170117016=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

---

五、参考资料

1. InnoDB一棵B+树可以存放多少行数据？
2. 《高性能MySQL》