共计 4995 个字符，预计需要花费 13 分钟才能阅读完成。
最艰难的事件就是意识本人！
集体网站，欢送拜访！
前言：
想要彻底明确索引该怎么创立，以及怎么创立出最正当的索引，首先须要对一些常识要有所理解；
本文将从以下几方面来进行论述：
- 索引的相干常识（包含索引数据结构等）；
- 索引创立的准则 / 根据；
- 学会查看 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、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) 改为 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 操作，会导致索引生效；
❤不要遗记留下你学习的脚印 [点赞 + 珍藏 + 评论]嘿嘿ヾ
所有看文章不点赞都是“耍流氓”，嘿嘿ヾ (◍°∇°◍)ﾉﾞ！开个玩笑，动一动你的小手，点赞就完事了，你每个人出一份力量(点赞 + 评论) 就会让更多的学习者退出进来！非常感谢！~ω~=
