索引概述
介绍
- 索引（index）是帮助MySQL高效获取数据的数据结构（且 有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
优缺点
索引结构
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：
B-Tree（多路平衡查找树）
- 以一颗最大度数（max-degree）为5（5阶）的b-tree为例（每个节点最多存储4个key，5个指针）
往下看，我来告诉你，啥叫5阶，什么是key，指针又是什么。
下图可见：红色方框中已经有5个数值了，但这并不是最终形态，因为我们的最大度数是5，而度数指的是指针。
看下图最终形态，8是中间元素，那么此时 中间元素就会向上裂变。
为什么会向上裂变呢，因为此时我们的最大度数是5阶，也就是5个指针，4个key，key指的就是方框中的数值，当数值大于4个，或指针大于5个时，就不符合5阶B数的规则了，那么就得分裂。
指针：也叫最大度数，也叫5阶。一个指针对应这一个字节点
节点：
子节点：
key：关键字
最后来一张动态图演示一下B树的新增过程吧。
B+Tree
- 设计的目的是为了优化磁盘读写操作。
- 以一颗最大度数（max-degree）为5（5阶）的b+tree为例
且叶子节点形成一个单向链表、
MySQL版 B+Tree
- MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
为什么InnoDB会选择B+Tree？
- 相对于二叉树，层级更少，搜索效率高。
- 对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少【一页的大小是16kb】，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
- 相对Hash索引，B+Tree支持范围匹配及排序操作。
索引分类
在InnoDB存储引擎中，根据索引的存储形式，可以分为以下两种
聚集索引的选取规则：
- 如果存在主键，主键索引就是聚集索引。
- 如不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个
GEN_CLUST_INDEX的6字节长整型字段，作为隐藏的聚集索引。
注意事项：
- 一个表只能有一个聚集索引，可以有多个二级索引。
- 更新聚集索引列可能导致大量的页分裂或合并操作，从而影响性能。
- 除了聚集索引之外的所有索引都是二级索引，它们并不决定数据行的实际存储顺序。
- 如果查询仅需访问二级索引就能完成，则无需回查聚集索引，这种情况被称为"覆盖索引"。
- 与直接使用聚集索引相比，使用二级索引可能效率较低，尤其是在大量随机读取的情况下。
聚集索引的查询过程
回表查询的过程
覆盖索引
什么是覆盖索引：一条select 语句在查询的过程中使用到了索引，并且需要返回的列，在该索引中已经全部能够找到。
- 当查询仅需访问二级索引就能完成时，则无需回表查询
二级索引：idx_name_age 包含了 name 与 age两个字段
查询语句：select name,age from table where name = 'xxx'
像上述这种情况，所需要查询的字段正是二级索引中所包含的字段，二级索引可以直接满足查询需求，所以这种情况下就无需再回表查询了。
- 需要回表查询的情况
二级索引：idx_name_age 包含了 name 与 age两个字段
查询语句：select id,name,age from table where name = 'xxx'
加了一个id，二级索引里面是不包含的，所以这里就必须查完二级索引后，在查聚集索引。
索引语法
查询索引
- show index from table_name
删除索引
- drop index index_name on table_name
创建索引
- create [ unique | fulltext ] index index_name on table_name ( index_ col_name,... )
- unique ：代表创建的是一个唯一索引，该字段不能出现重复数据。
- fulltext：代表创建的是一个全文索引。
- index_col_name：代表的是要创建索引的字段，如果是一个字段，代表的是单列索引；如果是多个字段，代表的是联合索引。
- 如果不加上面这两个可选参数（unique | fulltext），代表创建的是一个常规索引。
测试数据表
案例
- name字段为姓名字段，该字段的值可能重复，为该字段创建索引
- phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引
- 为profession，age，status创建联合索引
- 为email创建一个索引来提升查询效率
索引-使用规则-验证索引效率
上两图：
- 该表有1千万条数据
- name字段是没有索引的
- 可以看见，这条sql扫全表了，速度相当慢，一条数据就需要6秒的时间
好，接下来，我们给name字段加上普通索引试试看。
上三图
- 加上索引后，速度的提升是指数级的
- 可以看见，这条查询语句已经使用上索引了，row也为1了
最左前缀法则
- 如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始（也就是最左边的那个索引字段必须存在），并且不跳过索引中的列。
- 如果跳跃某一列，索引将部分失效（后面的字段索引失效）
我有一张1千万数据的表，并且创建了如下图中的联合索引
看如下案例：
- 案例一：
索引的三个字段都用上了。
- 案例二：
将job字段的查询去除了，索引依然生效了，且索引key_len的长度也减少了。
- 案例三：
将age字段也去除了，索引依然生效，且索引key_len的长度也减少了。
- 案例四：
将name字段的查询去除了，发现没有走索引，扫全表了，这是因为没有遵循最左原则，name字段是该索引的最左侧字段，但查询时缺不存在。
- 案例五：
和案例四结果一样。
- 案例六：
我将查询的顺序给打乱了，name在SQL中并非是最左侧的字段，但为何还是触发了索引呢？
原因：回看上面的最左前缀法则，其中说明是：最左侧的那个索引字段必须存在，而并没有顺序要求，所以在查询时，name只需要存在即可，位置想放哪放哪。
- 案例七：
这个案例中，我将中间的索引字段age取消了，那么此时触发了索引字段跳过，从ref中可以看到，只有一个const，只触发了name一个字段的索引，job没有触发，因为失效了。
范围查询
- 联合索引中，出现范围查询（>或<）,范围查询右侧的列索引失效。
- 案例：
如下图，当使用了范围查询时，范围查询右侧的列索引就失效了，可以看见只生效了name与age的索引字段，job索引字段没有生效
所以在我们实际开发中，如果业务允许，就使用 >= 或 <= ，这样就规避了这个失效规则。
索引失效的多种情况
如下图，这是我当前表中所有的索引，下面索引失效情况下都会使用。
-
索引列运算：不要在索引列上进行运算操作，索引将失效。
图一：触发了age的单列索引
图二：age字段进行了 “加法”，导致索引失效了
-
字符串类型字段使用时，不加引号，索引将失效。
图一：下面是加了引号的情况
图二：下面是没加引号的情况
-
模糊查询
-
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
图一：尾部模糊匹配，索引生效
图二：头部模糊匹配，索引失效
-
or连接的条件
-
用or分割开的条件，如果or前的条件中的所有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
图一：两个字段都有索引，所以两个索引都用上了，索引生效
图二：age有索引，但managerid没有索引，所以age的索引也失效了
-
数据分布影响
-
如果MySQL评估使用索引比全表更慢，则不使用索引。
图一：得知，年龄最大的为1000
图二：查询年龄大于990的数据时，用上了索引
图三：查询年龄小于1000的数据时，没有使用索引，全表查询了
SQL提示（指定索引进行查询）
- SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
如下所有案例都是用name字段举例，name字段有两个索引，一个单列索引，一个联合索引。
- use index：建议MySQL使用某个索引
- explain select * from emp use index(idx_emp_name) where name = 'xxx';
图一：可以看见，可能会使用到的索引有两个，使用上的索引是联合索引。
图二：这里建议MySQL使用单列索引，MySQL采纳了
- ignore index：不建议MySQL使用某个索引
- explain select * from emp ignore index(idx_emp_name) where name = 'xxx';
可以看见下图，MySQL也采纳了我的建议，不使用单列索引，所以使用了联合索引
- force index：强制使用某个索引
- explain select * from emp force index(idx_emp_name) where name ='xxx';
前缀索引
- 当字段类型为字符串(varchar , text 等)时，有时候需要索引很长的字符串，这会让索引变的很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
- 语法：
- create index idx_xxxx on table_name(column(n));
- n ：代表该字段前几个字符。例如：n = 5 ，那么就是前5个字符
前缀长度
- 可以根据索引的选择性来决定。
- 什么是索引的选择性？选择性指的是 不重复的索引值（基数）和 数据表的记录总数的比值。
- 索引选择性越高 则查询效率越高，唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。
索引选择性如何得到，如下两个语句可以计算出索引选择性：
select count (distinct email) / count(*) from tb_user;
select count(distinct substring(email,1,5)) / count(*) from tb_user;
如下图，我们来看看前缀索引他是怎样获取数据的。
上图中，1至7就不细说了，
- 先拿email的前5个字符在二级索引中进行查询，最终得到了主键ID为1。
- 然后拿到1 去聚集索引中查询，最后得到 一个完整的row，也就是一行完整的数据。
- 那么一行完整的数据之后，拿到这行完整数据里面的email字段的数据与where中提供的email查询数据相比较。如果完全相同，那么获取。
- 可以看见二级索引的叶子节点，它们是链表的形式，所以，mysql会继续查询下一个叶子节点，看看是否前缀数据相同，如果还相同，那么重复1-3操作。直至不相同。
索引设计原则
- 针对于数据量较大，且查询比较频繁的表建立索引。
- 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
- 尽量选择区分度高【区分度：可以理解为重复率，例如身份证号是唯一的，那么区分度就是最高的】的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响赠删改的效率。
- 如果索引列不能存储NULL值，请在创建表时使用NOT BULL约束它，当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。