MySQL要点记录

一、为什么索引能加快查询速度

索引实际上就是把数据组织成某个数据结构（例如B+树），索引查询的时候就可以利用数据结构的特性加快查询速度。

二、索引本质

索引是帮助MySQL高效获取数据的排好序的数据结构，索引数据结构主要有：二叉树、红黑树、Hash表、B-Tree等

索引二叉树结构

注：早期的MySQL可能会有用到二叉树，现在MySQL的版本不用二叉树，主要用B+Tree(B-Tree的变种)

三、B-Tree结构

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列

B-Tree结构

四、B+Tree结构

• 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能

B+Tree结构

五、Hash结构

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+树索引更高效
• 仅能满足“=”，“IN”,不支持范围查询 （适合ID主键）
• hash冲突问题 （数组+链表的形式解决）

结论：Hash索引正常情况下会比B+树查询性能更高，但工作中，99%的情况下，都不会用Hash索引，主要是因为Hash索引不支持范围查找。

Hash结构

六、MyISAM存储引擎索引实现

MyISAM索引文件和数据文件是分离的（非聚集）

非聚集：数据和索引是分开存储的

Primary Key

七、InnoDB存储引擎索引实现

7.1 InnoDB索引实现（聚集）

• 聚集：即数据和索引是 存储在一起的
• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引：叶节点包含了完整的数据记录

Primary Key Secondary Key

7.2 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键，不推荐使用UUID？

为什么建议InnoDB表必须建主键：
InnoDB表需要用主键作为聚集索引来组织B+Tree，如果我们不建主键，MySQL会从所有列中找到一项唯一的列来作为聚集索引组织B+Tree，如果所有列中都没有合适的列来作为聚集索引，则MySQL会默认建一列隐藏的列（类似于rowid）作为聚集索引来组织B+Tree。如果我们自己建立了主键，则就能节省以上一系列环节，因此建议InnoDB表必须建主键。

推荐使用整型的自增主键，不推荐使用UUID：
1、UUID既不是整型，也不是自增。
2、在B+Tree中进行查找比较大小时，整型比较大小要比字符串比较大小效率更高。
3、另外，整型要比UUID占用空间更小。
4、当ID是自增时，新的数据插入B+树一般是按顺序在后面的节点进行追加，如果节点能存储的数据已经满了，只需要新建新的节点进行存储，不会影响之前已经存储好的节点和数据。但如果是非自增的，当插入数据时，就需要先定位到需要插入的位置，如果所插入的节点数据已经满了，就需要将节点进行分裂，移动被分裂节点里的数据。

综上原因，因此推荐使用整型且自增主键而不是UUID。

7.3为什么非主键索引结构叶子节点存储的是主键值？

一致性和节省存储空间

八、索引最左前缀原理

联合索引的底层存储结构

联合索引的底层存储结构

最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

key 'idx_name_age_position' ('name','age','position') using btree 

//会走索引
explain select * from employees where name = 'Bill' and age = 31;
//不会走索引
explain select * from employees where age = 30 and position = 'dev';
//不会走索引
explain select * from employees where position = 'manager';