2021-08-09 MySQL索引原理

,索引概念

数据库索引，是数据库管理系统中的一个排序的数据结构，用于协助快速查询、修改数据。

索引分类

正常索引、唯一索引、联合索引、主键索引（not Null）、全文索引；

索引存储模型推演

二分查找 -- 有序数组（不适合插入，删除）、单链表（不适合查找）--> 二叉查找树（极端情况退化成链表） -- > 平衡二叉查找树（AVL 树）[存储I/O次数较多的问题] --多路平衡树（B 树）[无法解决区间值的问题] --> B+树

• AVL树通过左右旋的方式来保证左右子树的深度差的绝对值 < 1.[图片上传失败...(image-8725e3-1628519986464)]
  AVL 树.png
  B 树.png
  B+ 树.png

小点：

• B+ 树和B 树 通过分裂、合并保证树的平衡

• 红黑树一般用于内存

• 用户无法自行创建Hash索引，hash索引是InnoDB 为热点数据(5/8buffer pool)自行创建的内存索引。

B+树的落地形式

B+ 树的优势.png

MyISAM

MyISAM-主键索引.png

InnoDB

InnoDB-主键索引.png
InnoDB-二级索引.png

问题：为什么二级索引不存储数据的访问地址？

当添加 、删除数据会引起地址变化，索引二级索引存储的是主键值（不变的数据）

小点：

聚集索引：主键索引、不包含空值的唯一索引、没有索引情况下系统会将rowID(非空唯一列的别名)作为聚集索引。

聚集索引的键值顺序 与 数据行的物理存储顺序一致，这也是为什么建议使用有顺列作为聚集索引的原因。

索引使用原则

1. 只在必要的列上面建立索引。

2. 在离散度高的列建立索引；

3. 联合索引的最左匹配原则

列的离散度

列的离散度 = count(distinct(column_name)) / count(*) ;

联合索引最左匹配

联合索引按照从左到右的顺序创建搜索树，创建联合索引的时候通常我们将最常用的列放到最左边

联合索引最左匹配.png

覆盖索引

在二级索引（单例或者联合索引）里面，如果select 列都包含在二级索引中，则不需要回表（回到聚集索引查询表数据），这时候的索引叫做覆盖索引

覆盖索引.png

索引条件下推

索引条件下推.png

索引的创建和使用

索引失效的条件

索引失效.png
隐式转换.png

索引创建

创建索引.png
前缀索引.png