MySQL 之索引详解（B树 & B+树）, B+树数据量计算

1、简介

        索引在MySQL中常用于提高检索效率，降低数据库的IO成本，通过索引对数据进行排序，降低排序成本，减少CPU 的消耗。索引虽然能够提高查询性能，但是对于插入、更新、删除等操作会更新索引文件造成额外消耗，也占用一定的空间。接下来我详细介绍MySQL中两种存储引擎的索引数据结构。

2、MyISAM 存储引擎使用B树

        在上篇博文中介绍MyISAM存储引擎使用B树进行存储索引和数据，索引文件和数据文件分开存储，在进行查找的时候，先从索引文件中找到数据的磁盘位置，再到数据文件中找到索引对应的数据内容。

3、 InnoDB 存储引擎使用B+树

        B+树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。InnoDB 中页的默认大小是 16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数 （节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数会减少；对于数据库来说，所有的数据必然都是存储在磁盘上的，而磁盘IO的效率实际上是很低的，因此减少IO次数能够使数据查询的效率更快，具体如下图。

        B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么 B+树使得 范围查找，排序查找，分组查找以及去重查找变得异常简单。 

3.1、聚簇索引

        InnoDB 存储引擎表是索引组织表，表中的数据按照主键的顺序存放，而聚集索引是每张表按照主键构造出来的一棵B+树，同时叶子结点存储整张表的数据，叶子结点也称为数据页，每个数据页都使用双向链表连接，如上图所示。一张表只能有几个聚簇索引。

3.2、辅助索引（非聚簇索引）

        对于辅助索引，叶子结点并不包含行记录的全部数据，叶子节点除了包含键值，还包含一个聚簇索引键，通过聚簇索引键查找聚簇索引获得行数据；每张表可以有多个辅助索引。辅助索引具体查询过程如下图：

3.3、覆盖索引

        覆盖索引(covering index)，MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后再去查询数据，所以那是相当的快；但是同时也要求所查询的字段必须被索引所覆盖到，在Explain的时候，输出的Extra信息中如果有“Using Index”，就表示这条查询使用了覆盖索引。

个人理解：查询字段在索引中，并且使用了该索引，就不会回表查询，这就是覆盖索引。

4、B树和B+树比较

1）、B树所有节点都保存索引指针（叶子结点索引指针为空）和数据地址，键值分布整棵树，不会重复；

2）、B+树所有非叶子节点只保存键值，不保存数据，叶子结点保存键值和行数据，并且页数据之间使用双向链表连接；

3）、在MySQL中页数据大小为16Kb，由于B树节点既保存键值也包含数据，因此，每页保存键值的数量B+树要比B树要多，因此需要IO的次数更少，效率更高。

5、B+树存储数据量计算

        在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是4k，而对于我们的InnoDB存储引擎也有自己的最小储存单元是页（Page），一个页的大小是16KB。

        以3层B+树为例，如果主键采用 bigint 类型，在MySQL中使用8个字节存储，指针大小在InnoDB引擎中使用6个字节，这样一共14个字节。一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。所以可以算出一个高度为3的B+树可以存放：1170 * 1170 *  16 = 21902400 条这样的记录。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

6、MySQL索引操作

# 1、创建索引
alter table tb_name add index index_name(字段1，[字段2, ...]);
create index index_name on tb_name(字段1，[字段2, ...]);
# 2、删除索引
alter table tb_name drop index index_name;
drop index index_name on tb_name;
# 3、查询索引
show index from tb_name;
# 4、建表时建索引
index index_name(字段1，[字段2, ...])

7、总结

        本文详细介绍MySQL中两种存储引擎使用的两种索引数据结构，从原理上剖析，帮助我们进一步掌握MySQL中索引使用，后续将会更新更多MySQL更底层内容。

        本人是一个从小白自学计算机技术，对运维、后端、各种中间件技术、大数据等有一定的学习心得，想获取自学总结资料（pdf版本）或者希望共同学习，关注微信公众号：it自学社团。后台回复相应技术名称/技术点即可获得。（本人学习宗旨：学会了就要免费分享）