【Mysql索引的数据结构】

1、为什么需要索引以及什么是索引
数据的存储最终是落在磁盘上，在磁道上分配了空间存放，且存放的内存空间不一定连续。如果没有索引，在查询数据时，可能每一条数据都会有一次I/O。当数据量比较大的时候，会严重影响性能。因此就需要一个有序的数据结构建立一个列值与原表行记录一一对应的表，也就是索引。换句话说，索引就是帮助mysql高效获取数据的排好序的数据结构。

2、索引的选择
有序的数据结构可以是二叉树、红黑树、hash、B-tree以及B+tree，这些数据结构如果用作索引，会有什么结果呢？
二叉树：对于常规的列，二叉树可以作为索引

对于上面的列，二叉树可以减少查找次数，但对于特殊的列

会出现上面的结构，无法达到优化的效果。
优化上面的数据结构，得到了红黑树。红黑树也叫二叉平衡树

红黑树虽然不会出现二叉树那种情况，但是在数据量特别大的情况下，树的高度会非常高，同样会产生很多次的I/O。
如果限制红黑树的高度，每一行申请一个连续的空间，存放多个元素，每一个子节点同样申请连续空间，存放多个元素，那么就可以限制树的高度，从而达到优化的效果，这种数据结构也就是B-tree。

但是对于这种数据结构，每个子节点都存放了数据，需要消耗比较多的内存，而且对于单个索引块来说存储的数据就会变少。另一个问题就是对于范围类的查找，B-tree并不能很好的支持。
因此，需要对B-tree进行进一步的优化，即B+tree。

对比B-tree的优点：
a、这种结构非叶子节点不存储data，只存储索引(冗余)，可以存放更多的节点
b、叶子节点包含所有的索引字段
c、叶子节点用指针连接，提高了区间访问的性能。

对于一次查询，以上图为例，假设查询数据为60，查询的过程如下：
首先将头节点加载到RAM中，在头节点使用二分查找法定位到指向磁盘块3的指针，再将磁盘块3的数据加载到RAM中，再根据二分查找法定位到磁盘块6，再将磁盘块6的数据加载到RAM中，二分比对即可找到60所在的位置，并拿到查询的数据。仅通过三次与磁盘的交互就拿到了查询的数据，大大提高了查询效率。
在数据量非常大的情况下，B+tree还能保证树的高度吗？
show global variables like ‘%page%’; 通过这一条sql可以查询到mysql默认的一个磁盘块（数据页）的大小，查询结果是| innodb_page_size | 16384 ，即16kb大小。
这个16kb是可以修改的，但是这个大小即查询时，每一次加载到RAM的内存大小，不推荐修改。
假设这个索引字段大小为8个字节，一个内存地址大小6个字节，叶子节点假设是存放的是一行数据约1kb，那么当B+tree树的高度为3时，这颗B+tree可以存放的索引数量约为16kb/14b * 16KB/14b * 16 约等于2200万条。也就是说，一个2000万数据的表，在通过索引查询时，仅需要三次磁盘I/O就能查到结果。
因此，B+tree可以完美的作为Mysql的索引数据结构。
3、myisam与innodb的区别
1）如果某张表的存储引擎是Myisam，那么对应的会生成三个文件：
.frm文件：存放表结构相关的数据。
.myd文件：存放的是表数据。
.myi文件存放索引相关文件。
Myisam的索引文件和数据文件是分离的，所以对于Myisam的主键索引，是一个非聚集索引，在B+tree的叶子节点存放的是主键对应的内存地址。
如果某张表的存储引擎是Innodb，那么对应的会生成两个文件：
.frm文件：存放表结构相关数据。
.ibd文件：存放表数据与索引数据。
Innodb的索引文件与数据文件存放在同一个文件中，所以对于Innodb的主键索引，是一个聚集索引，在B+tree的叶子节点存放的是一整条数据。
2）MyISAM 可以不设立主键，InnoDB 推荐使用int类型自增主键。
Myisam如果没有建立索引，Mysql会从表中选出一列所有数据都不相等的列作为主键索引来组织B+tree，如果没有，会建立一个隐藏列，维护一个唯一的id，用这个隐藏列来组织B+tree。对于Innodb，因为ibd文件必须要用b+tree来组织，如果有主键，主键自带主键索引，那就可以用主键来组织整张表的数据。同样的，如果没有建立索引，他也会建立一个隐藏列，但这样会比较损耗Mysql性能。同时，建立索引时推荐使用int类型自增主键，这样在索引比较时和数据插入时树的分裂情况，性能都会比较好。
3）MyISAM 不支持事务，InnoDB 支持事务。
Myisam默认事务自动提交且不能修改。Innodb可以通过set autocommit = 0;
1或者on 表示开启自动提交，0表示禁用。
所以一般不推荐同一个数据库中设置两种不同的存储引擎，当这两张表事务不一样的时候一起操作，可能会导致无法预料的事务问题。
4）MyISAM必须依靠操作系统来管理读取与写入的缓存，而InnoDB则是有自己的读写缓存管理机制。
5）InnoDB 支持外键，而 MyISAM 不支持
补充说明：
Mysql数据库同样可以选择Hash作为索引的数据结构

这种数据结构，对于等值的查找仅需一次Hash就能找到所查的元素，效率高于B+tree。但仅适用于“=” 或者“in”的场景，且有hash冲突的问题。