MySql索引、引擎讲解及相关面试题

索引是帮助MySQL高效获取数据的排好序的数据结构。

针对以下类型一一分析：

二叉树

红黑树

HASH

B-Tree

B+Tree（默认）

以此SQL为例：select * from user where id = 0005;默认id列有索引。

假如使用二叉树（左边节点小于右边节点的值），如果加索引的字段是递增的，由于二叉树是左边节点小于右边节点的值，根据索引查找数据时，等价于全表扫描，速度太慢。因此，二叉树不适合做索引类型。
假如使用红黑树（一种自平衡二叉树），此时查id = 0005的数据只需要3次即可。但是，红黑树在数据量大时，树的高度会增加，查询（磁盘IO费时间）次数变多，速度也会变慢。
B-Tree（多叉平衡树：扩展横向节点，降低树的高度），减少磁盘IO次数，一次性将一个节点读入内存，在内存中根据索引计算数据的位置。
可存索引数量：高度为3的B+Tree，可以存2000W（1170*1170*16）。[16*1024/14=1170]行索引数据。
分析：默认节点大小是16K，bigint = 8B，指针在MySQL源码占6B，看下图说明：
HASH类型索引（对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码）适合等值查询，不适合范围查询。
如果是select * from user where id = 5可以用HASH索引，因为可以根据5的HASH值，精准查询索引。如果是select * from user where id > 5则无法用HASH索引。

常见的

MyISAM

InnoDB（常用）

MySQL引擎类型可以在Navicat中看到

MyISAM引擎的索引文件（表名.myi结尾）和数据文件（表名.myd）是分开的，InnoDB引擎的索引和数据存储在同一个文件（表名.ibd）中。
聚集/聚簇索引：InnoDB引擎的主键索引就是聚集索引（叶子节点包含完整的数据记录）参考下图理解一下。
在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
为了建立索引，如果没有主键，MySQL也会默认创建一个列来当索引；
整型的主键一是存储空间小，二是为了比较方便，不用再排序；
如果是采用UUID做主键，不仅占用的存储空间大，而且难于做顺序比较；
自增的主键是为了新增的索引数据只需创建新的存储空间存储即可，不用插入已经建好的结点中，避免结点数据重新排列。
为什么非主键索引结构叶子节点存储的是主键值？
B+ Tree索引和Hash索引区别？
HASH索引适合等值查询，但是无法进行范围查询
HASH索引没办法利用索引完成排序
HASH索引不支持多列联合索引的最左匹配规则
如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题