Mysql 索引介绍及原理（二）索引的原理和存储结构

目标：

• 掌握索引、分类、优劣势 

• 使用命令创建、查看、删除索引 

• 理解索引的原理和存储结构

---

索引存储结构

 

• 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引

• MyISAM和InnoDB存储引擎：只支持B+ TREE索引， 也就是说默认使用BTREE，不能够更换

• MEMORY/HEAP存储引擎：支持HASH和BTREE索引

B树和B+树

     数据结构示例网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B树图示

• B树是为了磁盘或其它存储设备而设计的一种多叉（下面你会看到，相对于二叉，B树每个内结点有多个分支，即多叉）平衡查找树。 多叉平衡树
• B树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数。
• 如果是三层树结构---支撑的数据可以达到20G，如果是四层树结构---支撑的数据可以达到几十T

B+树图示

B和B+树的区别

• B树和B+树的最大区别在于非叶子节点是否存储数据的问题。
• B树是非叶子节点和叶子节点都会存储数据。
• B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的。

聚集索引和非聚集索引区别

• 聚集还是非聚集指的是 B+Tree 叶节点存的是指针还是数据记录
• MyISAM 索引和数据分离，使用的是非聚集索引
• InnoDB 数据文件就是索引文件，主键索引就是聚集索引

对比如下：

聚集索引

非聚集索引

辅助索引

辅助索引存储的是主键索引的主键值，不是地址值。

 结论：

1、MySQL非主键查询，则需要搜索两次索引树（一次是辅助索引树，一次是主键索引树），最终取出数据。如下示例

select * from t where id=15
select * from t where name='Alice

从辅助索引树上找到主键后，再在主键索引树下找到数据 我们称为回表。

select name from t where name='Alice' 给name做了索引
select id,name from t where name='Alice' 覆盖索引
select * from t where name='Alice' ？

形成索引树：利用组合索引 完成覆盖索引（利用组合索引完成在辅助索引树的遍历，不回表）
 

2、 MySQL表设计需要创建主键ID，如果未创建建主键，则MySQL会找唯一字段 当主键，没有唯一字段则MySQL自动生成伪列 当主键。

3、创建主键方案：使用连续自增主键，不要用大字符串比如 uuid作为主键，推荐雪花算法 snowflakes。