一、B+树索引
1、聚簇(cu)索引
1、聚集索引/聚簇索引;不是单独的索引类型,而是一种数据结构/一些索引类型的统称;
2、通过主键来构建一棵B+树;
3、整张表的行数据存放在B+树的叶子节点上;
4、如果定义表没设置主键,会不会创建索引?
-- 会;
-- MySQL会默认创建一个 隐藏的rowid 为主键;用这个rowid 创建索引;
5、查询时,条件用到了主键才会用这个索引;
2、非聚簇索引
1、辅助索引/二级索引/非聚簇索引;不是单独的索引类型,而是一种数据结构/一些索引类型的统称;
2、通过【非主键字段】构建一棵B+树;
3、每个索引都有一个它自己的B+树;
4、这个B+树的叶子节点存放的不是全部数据;
-- 存放该字段的值;
-- 存放该行的主键的值;
5、如果该字段的值不是唯一的如何处理?
-- 虽然该字段的值可能有重复的,但是每个叶子节点对应的主键id一定不同,所以认为节点不冲突;
3、回表
1、通过辅助索引获取主键 --> 使用主键拿到全部数据;
2、sql优化:少用回表;
4、联合索引
1、使用多个字段构建一个索引;也是一个二级索引
2、每一个联合索引都有一个自己的B+树;
-- 这个B+树是使用索引第一个字段创建的;
3、这个B+树的叶子节点存放的数据为:主键的值、每一个字段的值;
4、组合索引的B+树排序方式:以联合索引的第一个字段先排,后面的依次排;
-- 举例:select * from table order index1,index2;
5、最左匹配原则:
6、尽量创建联合索引代替单个索引,减少回表;
7、覆盖索引:
-- 可以创建一个覆盖【查询结果】和【查询条件】的联合索引,减少回表;
5、小结 - 聚簇索引和非聚簇索引
索引类型 | 聚簇索引 | 非聚簇索引 |
名称 | 聚簇索引、聚集索引 | 非聚簇索引、辅助索引、二级索引 |
构建方式 | 主键 --> B+树; 如果有主键,就以主键做聚簇索引; 如果有唯一索引(非空),就以其中一个做聚簇索引; 如果啥也没有,就创建一个rowid做聚簇索引; | 非主键 --> B+树; 手动创建的一般都是非聚簇索引; |
数据存放方式 | 叶子节点存放整行数据; | 叶子节点存放主键值 和 该字段的值; |
触发方式 | 条件用到这个字段才会触发; 非聚簇索引触发; | |
索引值唯一 | 主键值/唯一索引值一定唯一 | 该字段的值不一定唯一,但是携带的主键值一定唯一 |
包含哪些索引 | 主键索引 | 联合索引、前缀索引、唯一索引 |
数量 | 1; 每张表只能有一个聚集索引 | N; 每张表可以有多个非聚簇索引 |
二、Hash索引
1、自适应hash索引
1、是一个无法手动干预的,且MySQL内部创建的索引;
2、针对热点数据创建的;
三、高性能的索引创建策略
1、前提:
-- 创建索引的目的:提高查询速度;
-- 每次查询只能触发一个索引;
2、索引列的类型越小越好;
3、索引列的选择:
-- 值越离散越好;相同的值越少的列越好;
3、前缀索引:针对大类型的字段创建的索引
-- blob,text,varchar
-- MySQL不支持索引它们的全部长度;
-- 语法 column(字符长度);
-- 需要遵循离散度高的要求;
-- 缺点:无法排序和分组,因为数据不全;
4、后缀索引:
-- MySQL不支持后缀索引;
-- 可以单独出一列专门放后缀,用这个字段创建索引;
5、只给 搜索、排序、分组、表连接创建索引;
6、多列索引:
-- 要注意联合索引里面的字段的顺序,越重要越靠前;
-- 可以根据sql的不同创建不同的索引,允许使用的列相同,但是顺序要调整;
四、三星索引及实战
1、有三颗星(缩小范围、提前排序、减少回表)
2、对一个查询来说,三星索引是最好的索引;
3、索引能将要查询的数据放到一堆中;通过查询条件一下就可以确定叶子节点的范围;
4、索引的顺序和查询的顺序一致,就能获得第二星;
5、避免回表就能获得第三星;--> 索引列覆盖查询结果;(最重要,要尽量避免回表)