一篇文章彻底搞懂MySQL的索引原理

1. 申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个节点只需一次I/O。
2. B树中一次检索最多需要h-1次I/O（根节点常驻内存）。一般实际应用中，出度d（树的分叉数）是非常大的数字，通常超过100；h非常小，通常不超过3。

综上所述，用B树作为索引结构效率是非常高的。

红黑树或者平衡二叉树的其他树结构，

1. h明显要深的多，执行效率低。
2. 逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，
3. 每个节点存储的数据量太小了，对磁盘空间造成浪费，带来频繁的IO操作。

所以其他树结构的效率明显比B树差很多。

相对B树，B+树做索引的优势

1. B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
2. B+树的查询效率更加稳定：由于所有数据都存于叶子节点。所有关键字查询的路径长度相同，每一个数据的查询效率相当。
3. B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。

笔者认为第三条原因才是MySQL使用B+树而不是B树做索引的主要原因，毕竟MongoDB的索引是B树，所以两种数据结构并没有绝对的好坏，要看实际的业务需求。

MyISAM

磁盘存储

MyISAM在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。

• .frm：用于存储表的定义。
• .MYD：用于存放数据。
• .MYI：用于存放表索引。

索引

主键索引

MyISAM引擎使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。

MyISAM索引文件和数据文件是分离的，索引文件仅保存记录所在页的指针（物理位置），通过这些地址来读取页，进而读取被索引的行。

树中叶子保存的是对应行的物理位置。通过该值，存储引擎能顺利地进行回表查询，得到一行完整记录。同时，每个叶子页也保存了指向下一个叶子页的指针。从而方便叶子节点的范围遍历。

辅助索引

在MyISAM中，主键索引和辅助索引在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。

Innodb

MySQL5.5开始支持InnoDB引擎，并将其作为默认数据库引擎。

磁盘存储

Innodb有两种存储方式，共享表空间存储和多表空间存储。

Innodb只有表结构文件和数据文件。

表结构文件和MyISAM一样，以表名开头，扩展名是.frm。

数据文件与存储方式有关：

• 如果使用共享表空间，那么所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件，通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字，一般共享表空间的名字叫ibdata1-n。
• 如果使用多表空间，那么每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。

索引

主键索引

Innodb主键索引中，既存储了主键值，又存储了行数据。

辅助索引

对于辅助索引，InnoDB采用的方式是在叶子页中保存主键值，通过这个主键值来回表（上图）查询到一条完整记录，因此按辅助索引检索实际上进行了二次查询，效率肯定是没有按照主键检索高的。

Innodb与MyISAM的区别

1. 存储结构

MyISAM存储表分为三个文件frm（表结构）、MYD（表数据）、MYI（表索引），而Innodb如上文所说，根据存储方式不同，存储结构不同。

2. 事务支持

MyISAM不支持事务，而Innodb支持事务，具有事务、回滚和恢复的事务安全。

3. 外键和主键

MyISAM不支持外键，而Innodb支持外键。MyISAM允许没有主键，但是Innodb必须有主键，若未指定主键，会自动生成长度为6字节的主键。

4. 锁

MyISAM只支持表级锁，而Innodb支持行级锁，具有比较好的并发性能，但是行级锁只有在where子句是对主键筛选才生效，非主键where会锁全表

5. 索引

MyISAM使用B+树作为索引结构，叶节点保存的是存储数据的地址，主键索引key值唯一，辅助索引key可以重复，二者在结构上相同。Innodb也是用B+树作为索引结构，数据表本身就是按照b+树组织，叶节点key值为数据记录的主键，data域为完整的数据记录，辅助索引data域保存的是数据记录的主键。

FAQ

MongoDB的索引为什么选择B树，而Mysql的索引是B+树

MongoDB不是传统的关系性数据库，而是以Json格式作为存储的nosql，目的就是高性能，高可用，易扩展。首先它摆脱了关系模型，所以范围查询和遍历查询的需求就没那么强烈了，其次Mysql由于使用B+树，数据都在叶节点上，每次查询都需要访问到叶节点，而MongoDB使用B-树，所有节点都有Data域，只要找到指定索引就可以进行访问。

总体来说，Mysql选用B+树和MongoDB选用B-树还是以自己的需求来选择的。

索引有关的名词解释

普通索引

用表中的普通列构建的索引，没有任何限制

唯一索引

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引

根据主键建立索引，不允许重复，不允许空值；

全文索引

仅可用于MyISAM表，针对较大的数据，生成全文索引非常的消耗时间和空间（在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引）。

组合索引

又叫联合索引。用多个列组合构建的索引，这多个列中的值不允许有空值。可以在创建表的时候指定，也可以修改表结构。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。示例的组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。

最左前缀规则

假设联合索引由列(a,b,c)组成，则一下顺序满足最左前缀规则：a、ab、abc；selece、where、order by 、group by都可以匹配最左前缀。其它情况都不满足最左前缀规则就不会用到联合索引。

聚集索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

如果定义了主键，Innodb会选择主键作为聚集索引；如果没有定义主键，Innodb会选择不包含NULL值的唯一索引作为聚集索引；如果也没有这样的唯一索引列，Innodb会选择内置6字节长的rowID作为隐含的聚集索引，这里的RowId会随着记录的写入而主键自增，但是它是不可引用和查看的，是数据库引擎内部的使用。

如果我们使用自增主键，那么每次插入的新纪录都在原先记录的尾部按照顺序，添加到当前节点的索引后面，当一页快写满的时候，就会开辟一个新的页。数据记录本身就存与主索引的叶子节点上，B+tree的树。这就要求每一个叶子节点内的各条数据记录按主键顺序存放，因此每当有一条新的记录插入的时候，MYSQL会根据其主键将其插入到合适的节点和位置上，如果页面达到装载因子（INNODB默认为15/16），则开辟新的页面（节点）

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

非聚集索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

除了InnoDB的主键索引，在mysql中的其他索引形式都是非聚集索引。

覆盖索引

指从辅助索引中就能获取到需要的记录，而不需要查找主键索引中的记录。使用覆盖索引的一个好处是因为辅助索引不包括一条记录的整行信息，所以数据量较聚集索引要少，可以减少大量io操作。

覆盖查询失效

1. select选择的字段中含有不在索引中的字段 ，即索引没有覆盖全部的列。
2. where条件中不能含有对索引进行like的操作。