索引该怎么创建?

最困难的事情就是认识自己!
个人网站 ,欢迎访问!

前言:

想要彻底明白索引该怎么创建,以及怎样创建出最合理的索引,首先需要对一些知识要有所了解;

本文将从以下几方面来进行阐述:

  • 索引的相关知识(包括索引数据结构等);
  • 索引创建的准则/依据;
  • 学会查看sql执行计划,以及哪些sql执行时会导致索引失效;

索引基本知识:

1、索引的数据结构:

索引的数据机构是 B+Tree,B+Tree 是一个多路平衡查找树。
1.1、至于为什么索引使用此数据结构呢?

最主要一个原因就是:使用B+Tree这种数据结构的索引,在进行sql查询时只需要较少的几次磁盘IO 即可,所以会大大提升查询效率;具体原因可参考:【深入学习MySQL】MySQL的索引结构为什么使用B+树?

1.2、索引 B+Tree 结构的特性:

①、B+Tree 只有叶子节点会存储真实的数据,非叶子节点只会存储索引字段值;

②、B+Tree的叶子节点之间使用 双向链表 链接,所以更加适合范围查询和排序;

2、索引的类型:

在平时创建的索引中,可以将索引大体分为两类:

①、聚簇索引(主键索引) ②、非聚簇索引(二级索引)

二级索引根据索引中的字段个数可以分为:

①、单字段索引 ②、联合索引 / 复合索引(多个字段组成的索引)

3、不同类型索引在磁盘中的B+Tree的存储结构:

3.1、聚簇索引:(主键索引)
聚簇索引:当表中创建了主键,默认就会生成主键索引;

聚簇索引的B+Tree索引结构中,非叶子节点中存储的是 ID主键值 ,存储在叶子节点中的真实数据是具体的 行记录 ; 结构图如下:

索引该怎么创建?_第1张图片

3.2、单字段索引:(二级索引)
单字段索引:手动创建的索引,由 一个字段 组成的索引;

单字段索引的B+Tree索引结构中,非叶子节点中存储的是 索引字段值,存储在叶子节点中的数据部分是 主键值 。结构图如下:

索引该怎么创建?_第2张图片

3.3、联合索引:(二级索引)
联合索引:手动创建的索引,由 多个字段 组成的索引;

联合索引的B+Tree索引结构中,非叶子节点中存储的是 多个索引字段值,存储在叶子节点中的数据部分是 主键值 。结构图如下:

索引该怎么创建?_第3张图片

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、sex 这两个字段的数据没有得到,所以只能再根据查询到主键值去主键索引树中查询;

②、拿着 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 ) 改为 index( name, age, address, sex ) 后,在执行上面的sql 就 不会发生回表了,并且提高了查询效率, 这就是 覆盖索引。

但是,索引的创建及维护也是需要耗费代价的,并且这种代价也是随着索引中索引字段的个数增加而增加的,所以覆盖索引需要根据实际情况使用。

6、最左前缀原则:

说到索引的最左前缀原则,就必须说下 索引长度 这个概念了;

索引长度指的是 索引字段列的 前缀长度索引;

例如:name字段的长度设置为100,但是在以name设置索引时设置的索引长度为20;意思就是创建的索引中存储的索引key键值就是name字段值的前20长度的内容。

6.1、索引的最左前缀原则的两种情况:

①、索引字段的最左前缀原则;

②、联合索引的最左前缀原则;

6.2、索引字段的最左前缀原则:

根据 索引长度 应该就可以明白了 索引字段的最左前缀原则了;但还是举例说明下:

经常说的导致索引失效的情况之一: 模糊查询时 将 % 放在了索引字段的前面;导致失效的最终原因就是 不满足索引的最左前缀原则;

数据表:t_user

字 段:id(主键)、name、age、address、sex

索 引:index( name )

Sql语句:select name, age from t_user where name like ’ %黎明 ’

上面这个 sql 语句执行时,并不会走 index(name) 索引查询,因为什么呢?不满足索引的最左前缀原则。

6.2、联合索引的最左前缀原则:
根据联合索引的B+Tree 存储结构可以知道其最左前缀原则是什么。

不太清楚也别着急,下面将会举例说明:

数据表:t_user

字 段:id(主键)、name、age、address、sex

索 引:index( name, age )

Sql语句:select id from t_user where name=’黎明’ and age=26

测试数据 如图:

索引该怎么创建?_第4张图片

根据上面的测试数据及联合索引可以得到 B+Tree存储结构图:

索引该怎么创建?_第5张图片

上面sql执行时索引树的搜索步骤:

①、首先是加载索引树的根节点(磁盘块1),然后匹配根节点中的第一个索引字段的值,查询name值是“黎明”的数据,得到指向其子节点地址的指针p1;

②、然后根据指针p1 找到子节点所在的磁盘块,然后将磁盘块加载到内存中,然后接着查询name值是“黎明”的数据,得到指向其子节点(叶子节点)地址的指针p3;

③、然后根据p3指针找到name值是“黎明”的 所在叶子节点的磁盘块,然后将磁盘块中数据加载到内存中,然后再比较 age 为26的数据,最终找到name值是“黎明”并且age为26 的 id主键值,由于select 投影列 只有 id 主键值,所以不需要回表,直接返回结果集了。

注意:在联合索引中,当索引中前一个索引字段值相同时,后面紧挨着的索引字段的值是有序的,所以索引天然适合进行排序,无需自己再进行排序了,提升了查询效率。

根据 上面的索引树搜索过程,知道一开始是以索引中的第一个索引字段进行搜索的,最后在叶子节点中依次进行的索引字段值的匹配,这就是联合索引的最左前缀原则;

所以如果sql语句中的where 条件中没有使用到联合索引的第一个索引字段,则整个索引就失效了。

索引创建的准则:

1、考虑创建索引的表的读写情况:

对于 写操作比较多 的表,创建索引时,应该尽量保证 联合索引 尽可能的 窄(窄: 索引字段个数尽可能少), 如果索引字段比较多的话,写数据时的索引维护比较麻烦。

所以说,能创建单字段索引就不创建联合索引。

2、避免冗余索引:

合理创建索引,避免冗余索引;

联合索引index(a, b, c)相当于 index(a)、index(a,b)、index(a,b,c)这三个索引,所以如果有 index(a, b, c)索引了,那么 index(a)、index(a,b)这两个索引就不用存在了,属于重复索引了。

3、对多表关联查询使用的 连接键 字段创建索引:

针对 多表关联 查询时,需要将 连接键 字段创建索引 ; 例如:user 、role 两张表;

(一)、 select * from user a, role b where a.name = b.name 【普通连接查询】

需要在 role 表中创建 name 连接键的索引,user 不必创建 name 字段索引;

(二)、select * from user a left join role b on a.name = b.name 【左外连接查询】

需要在 role 表中创建 name 连接键的索引,user 不必创建 name 字段索引;

4、明确字段的区分度:

提前估算出表中每个字段存储的值的区分度,尽量在 区分度高的字段创建索引 ,区分度低的字段创建索引用处不大,并且还会多出索引维护的消耗。

区分度:字段值的不同的比例;例如性别字段只会有两种值,男或女,属于区分度低的字段。

5、保证索引的唯一性:

创建索引时,尽可能保证索引的唯一性,唯一性指的什么呢?

在查看SQL执行计划时,执行计划中 possible_keys 中展示的就是优化器采样估算后可能会使用到的索引,只有这里展示一个索引,并且与实际使用的索引一致时,这才是唯一性;如果此处展示了多个索引名称的话,说明没有保证索引的唯一性。

为什么要保证唯一性呢?

因为如果在执行一个SQL时,优化器是通过采样分析判断发现有两个索引都可以时,那么就会面临抉择,到底最终使用哪个索引呢;如果只有一个索引符合的话,优化器直接选择即可。所以如果可以使用的索引存在多个的话,那么优化器还需要通过 额外的运算 得到最终使用的索引的,那么这就会降低查询效率的。

6、创建联合索引时,索引字段怎么排序:

在联合索引中,怎么对索引字段进行排序? 大白话就是 在联合索引中,哪些索引字段 放在最前面?

①、where 条件中长使用的字段 放到前面。

②、区分度高的字段放在前面,因为这样可以减少扫描 B+tree 中的叶块。

《高性能MySQL》 一书中提到的一个经验法则:将选择性最高( 区分度高 )的列放到索引最前列。

7、SQL中,有等值查询和范围查询时,联合索引怎么创建?

当然是将 等值查询的字段 放在联合索引的前面, 范围查询 的放到联合索引的后面;

为什么这么设计呢?

因为依据索引的查询规则,在联合索引中,首先都是根据第一个索引字段查询符合要求的数据,然后再从符合要求的数据中筛选第二个索引字段符合的数据;如果第一个索引字段值不符合,则直接结束查询了,不会再去进行范围查询了;但是如果将范围查询字段放在联合索引的第一位的话,那么很大可能会查询到符合范围的数据,然后再从数据中筛选等值查询的字段值,如果等值查询的值不存在,那么前面范围查询所做的工作就白白浪费了;并且降低了查询效率。

8、单表索引数量建议:

建议:单张表中索引的数量不超过5个;单个索引中的索引字段个数不超过5个;

导致索引失效的SQL:

明明已经创建好了索引,但是SQL执行时通过执行计划发现没有走索引查询,为什么?

1、查看SQL的执行计划:

创建好索引后,一般会查看下SQL的执行计划,看看SQL执行时是否走了索引查询;

Mysql 数据库查看执行计划 参考:使用explain分析SQL执行计划

Oracle 数据库查看执行计划 参考:Oracle通过执行计划查看查询语句是否使用索引

2、导致SQL执行时没走索引的原因:

2.1、优化器采样估算后不走索引:
可能数据库的优化器在进行采样估算时,发现走全表扫描更加合适;例如:表中数据量很小时,全表扫描和索引查询可能一样快,所以此时可能会直接进行全部扫描查询了。
2.2、SQL语句问题导致索引失效:(简单写几个常用到的)
  • 不满足索引的最左前缀原则;例如:like模糊查询时,%放在前面;
  • 使用了反向查询,例如:not in 、!= 、<> 等,导致索引失效;
  • 在索引字段上进行了 函数运算 或者 算数运算;
  • 隐式转换导致索引失效:隐式类型转换、隐式字符集转换;其实底层都是使用函数进行的隐式转换,所以导致的索引失效;
  • 对索引字段做 判 null 操作,会导致索引失效;

❤不要忘记留下你学习的足迹 [点赞 + 收藏 + 评论]嘿嘿ヾ

一切看文章不点赞都是“耍流氓”,嘿嘿ヾ(◍°∇°◍)ノ゙!开个玩笑,动一动你的小手,点赞就完事了,你每个人出一份力量(点赞 + 评论)就会让更多的学习者加入进来!非常感谢! ̄ω ̄=

你可能感兴趣的