系统优化 - 索引该怎么创建？

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

前言：

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

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

• 索引的相关知识（包括索引数据结构等）；
• 索引创建的准则/依据；
• 学会查看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主键值 ，存储在叶子节点中的真实数据是具体的 行记录 ； 结构图如下：

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、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

测试数据 如图：

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

上面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 操作，会导致索引失效；

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

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

个人原创Java技术文公众号，欢迎大家关注；关注后如果 不香 ，来捶我啊！嘿嘿。。。。。。