Mysql索引底层B+树的实现原理以及Innodb和Myisam引擎存储的区别

1.如何创建索引

通过SQL语句或者Navicat上直接创建

创建索引对象：
		create index 索引名称 on 表名(字段名);
删除索引对象：
		drop index 索引名称 on 表名;

首先要明白，主键和带有unique字段的系统默认添加索引。
但是对于普通字段，select 的时候需要全盘查找，这对于数据量大的情况下是不可接受的
对于我的emp 表（一共15条数据）如下，如果我要查找sal（工资） =5000的情况。
借助explain 看SQL底层的执行情况。

如果不添加索引，看到Type=all ,查找了15次

添加索引后，结果如下：

只需查找一次。

2.关于磁盘I/O读写问题

如果主键id 自增，采用树的结构存放主键，那么树的高度越低I/O读写次数将越小，能更进一步提高性能。至于为什么要采用B+树存放，请看下一篇文章分析了普通二叉树、红黑树、和B树的劣势。

采取树的结构存储数据的时候，从根节点向下比对数据的时候，是在内存中进行的，所以速度非常快。

3.MyISAM索引的实现

MyISAM索引文件和数据文件是分离的，首先查看用Myisam引擎生成的表，在windows下其实是由3个文件构成的。（非聚集索引）


如果采用B+树的结构存储主键id的话：

一、 B+树叶子节点中存放了全部的节点，并且每一个叶子节点，附带一个data值，里面存放着主键id一整行数据的真实磁盘内存地址。 假设在.MYI索引文件中，找到id=20的内存地址为0x6A.
二、拿到0x6A地址后再去.MYD文件读取磁盘，则可以拿到id=20一整行字段的值。
三、同时，每个叶子节点下的data域也保存了指向下一个顺序叶子的指针。从而方便叶子节点的范围遍历。
(比如查询where id between 10 and 20 里面的数 )

结论：可以看出MyISAM的索引文件仅仅保存数据记录的地址。

4.Innodb索引的实现

MyISAM相同的一点是，InnoDB 也采用 B+Tree这种数据结构来实现索引。最大的不同是，其将索引和数据存放到了同一个文件当中。（聚集索引）
用InnoDB 引擎生成的表，在windows下其实是由2个文件构成的。


对于 innodb来说,
1: 主键索引 既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

问题1: 为什么innodb必须要有主键？
设计如此，如果不设定主键，表也会创建成功，但是系统为查找 是否存在数据不重复的一列；如果没有，系统自动再添加一列，帮你生成一列主键。

问题2：为什么不推荐主键设为varchar类型
每一次大小比较都是一次运算，如果用int比较 只需要一次。
如果采用字符串，则需要比较每一个字符对应的ASCII码值，比如abbc,abbd。需要比对第四次，才能发现d的值比c大，非常耗费时间。

5.为何索引能支持千万级数据的快速查找

首先明确一个概念，mysql官方对非叶子节点的大小是有限制的。16KB.
因为一层肯定不能存在过多的节点。因为将来一次扫描的时候需要放到内存，数据过大是不利于I/O读取的。再加上内存资源非常珍贵。

SHOW GLOBAL STATUS like 'InnoDB_page_size';

如果采用bigint存储主键id,那么64位应该占用8b内存，每一个数据后还要跟一个下一个节点的指针，是6B。那么存储一个数据 就需要8+6=14B.
最下一层的叶子节点，如果采用Innodb引擎，里面存放的都是数据，保守估计最多也就1kb。
如下如：

所以按照16KB来算，
1.根节点可以存放16KB/14B 约等于1170个数据。
2.指针指向下一块每一块大小都是16K也就是1170个节点
3.叶子节点由于放了主键对应所有 字段的值，按照1KB算，可以放16个、

那么一共可以最多放1170×1170×16 = 21,902,400。也即是两千万个值。
但是树高只有3层。也就是I/O操作仅仅用了3次 就查到了这么大的数据量。