索引怎么生成的 索引该怎么创建

1.2、索引 B+Tree 结构的特性:
①、B+Tree 只有叶子节点会存储真实的数据 , 非叶子节点只会存储索引字段值;
②、B+Tree的叶子节点之间使用 双向链表 链接,所以更加适合范围查询和排序;
2、索引的类型:
在平时创建的索引中,可以将索引大体分为两类:
①、聚簇索引(主键索引) ②、非聚簇索引(二级索引)
二级索引根据索引中的字段个数可以分为:
①、单字段索引 ②、联合索引 / 复合索引(多个字段组成的索引)
3、不同类型索引在磁盘中的B+Tree的存储结构:
3.1、聚簇索引:(主键索引)
聚簇索引:当表中创建了主键 , 默认就会生成主键索引;
聚簇索引的B+Tree索引结构中,非叶子节点中存储的是 ID主键值,存储在叶子节点中的真实数据是具体的 行记录 ; 结构图如下:

索引怎么生成的 索引该怎么创建

文章插图
3.2、单字段索引:(二级索引)
单字段索引:手动创建的索引 , 由 一个字段 组成的索引;
单字段索引的B+Tree索引结构中,非叶子节点中存储的是 索引字段值,存储在叶子节点中的数据部分是 主键值。结构图如下:
索引怎么生成的 索引该怎么创建

文章插图
3.3、联合索引:(二级索引)
联合索引:手动创建的索引,由 多个字段 组成的索引;
联合索引的B+Tree索引结构中 , 非叶子节点中存储的是 多个索引字段值,存储在叶子节点中的数据部分是 主键值。结构图如下:
索引怎么生成的 索引该怎么创建

文章插图
4、回表:
什么是回表?
回表是发生在 二级索引 中的;在使用二级索引查询数据时,如果 select 投影列 中拥有 索引字段和主键 之外的字段时 , 此时就需要 回表;使用在二级索引树中查询到的 主键值 去主键索引树中查询具体的行记录 , 然后在具体行记录中取得最终的 select 投影列 数据 。
4.1、举例说明:
数据表:t_user
字 段:id(主键)、name、age、address、sex
索 引:index( name, age ) 测试的这个索引是 联合索引,单字段索引也是一样的原理
Sql语句:select name, age, address, sex from t_user where name=’黎明’ and age=18
上面的sql语句执行时,就会发生回表;具体执行步骤如下:
①、执行时 , 首先会使用到 联合索引 index( name, age ),在此索引树中查询时,最终在叶子节点中查询到数据,发现此时最终只能得到 name、age、id 三个字段的数据 , 发现 address、age 这两个字段的数据没有得到,所以只能再根据查询到主键值去主键索引树中查询;
②、拿着 id主键值 去主键索引树中查询具体的行记录,然后在行记录中取出select投影列需要的字段的数据,最后返回 。
4.2、扩展:
根据上面举的例子可以知道什么时候会发生回表,但是发生回表就代表着 查询效率 会比较低下的,因为需要走两边索引树(二级索引树 + 主键索引树);所以一般情况下需要避免回表的发生;怎么避免发生呢?这又涉及到了 覆盖索引 这个知识点 。下面就来介绍下 覆盖索引的知识。
5、覆盖索引:
覆盖索引:大白话就是 将select 的投影列字段全部放入到 索引中;
5.1、举例说明:
数据表:t_user
字 段:id(主键)、name、age、address、sex
索 引:index( name, age )
Sql语句:select name, age, address, sex from t_user where name=’黎明’ and age=18
这个sql执行时,会发生回表;
发生回表的原因通过上面的解释应该也清楚了些 , 所以如果 将索引 index( name, age ) 改为 *


    推荐阅读