一、索引是什么，为什么要使用索引。

索引是一种特殊的文件（InnoDB数据表上的索引时表空间的一个组成部分），他们包含着对数据表里所有记录的引用指针。
索引是一种数据结构，数据库索引，是数据库管理系统中一个排序的数据结构，协助快速查询、更新数据库表中数据。索引的实际通常使用B树或B+树。通俗的来说，索引就相当于字典目录，为了方便查找字典中的内容，通过对内容指定信息建立索引形成目录，且该目录（索引）是一个文件，需要占据一定的物理空间。
优点：加快查询速度，提高系统性能。
缺点： 1：空间方面，索引需要占据一定的磁盘空间。 2：时间方面，索引的创建以及维护都需要消耗时间，表现为：当表进行增删改时，索引也需要进行动态维护。

二、MySQL 索引类型及数据结构

1、普通索引、唯一索引、复合索引

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引（普通索引）。
唯一索引：索引列的值必须是唯一，允许有空值。
复合索引：多列值组成一个索引，适合于组合搜索，效率大于索引合并。

2、B Tree索引、B+ Tree索引、Hash索引

B Tree索引
- 实现：多叉树 B Tree能显著加快数据的访问速度，因为索引的存储引擎为多叉树，不再需要进行全表扫描来获取目标数据，数据存储在各个节点中。
- 索引结构：

B+ Tree索引
- 实现：多叉树+叶子节点指针 B+ Tree作为B Tree的改进版。所有的索引数据存储在树的叶子节点上，并且早叶子加点上增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比于B Tree来说，B+Tree在进行范围查找是只需要定位到起始和结束两个节点，接着进行一次遍历即可，而B Tree则需要获取范围内的所有节点。
- 索引结构：

- 索引性质： 1. n课子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。 2. 所有的叶子结点中包含了全部的关键字信息，以及指向包含这些关键字记录的指针，且叶子节点本身根据关键字的大小顺序链接。 3. 所有的非终端节点可以看做是索引的一部分，节点中仅含其子树中的最大（或最小）关键字 4. B+ tree中，数据对象的插入和删除仅在叶子节点上进行。 5. B+ tree中有两个头指针，一个是树的根节点，一个是最小关键字的叶子节点。

Hash索引
- 实现：基于Hash表实现，只有精确匹配索引所有列的值的查询才生效。对于每一行数据，存储引擎都会对所有的索引列计算一个hash code，并且Hash索引将所有的hash code存储在索引中，同时在索引表中保存指向没个数据行的指针。
- 索引结构：
- 优缺点： 1、基于Hash 表可快速定位被查值，但查询过程无序、IO复杂度高，且无法利用索引完成排序。 2、适用于等值查询，如=，in(),<=>，但不支持范围查询。 3、因为Hash 索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找。 4、如果有大量重复键存在的情况下，Hash索引的效率会因Hash碰撞而导致性能下降。

3、为什么默认B+ Tree数据结构

B+ Tree的磁盘IO代价更低：B+ Tree的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B Tree更小，如果把所有节点的关键字放在同一块硬盘中，那么该磁盘所能容纳的关键字数量越多，一次性读取内存关键字信息也越多，相对来说IO读写次数就越少。
由于B+ Tree的数据都存储在叶子节点中，且每个叶子节点均指向相邻的叶子节点，分支节点均为索引，方便扫库，只需要扫一遍叶子节点即可。但是B Tree因为其分支节点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历，所以B+ Tree更加适合在范围查询，所以通常B+ Tree用于数据库索引数据结构。

4、聚簇索引，非聚簇索引

聚簇索引：在B+ Tree中的叶子节点存储了整行数据，即数据存储与索引放在一起，找到索引也就找到了数据。
- 对于聚簇索引来说，通常只会查询一次。
非聚簇索引：在B+ Tree中的叶子节点中仅存储主键的值。（又称二级索引）
- 对于非聚簇索引来说，通常根据过滤条件找到非聚簇索引（顺序IO），接着需要根据主键索引再去聚簇索引中查找数据（随机IO），即回表，该过程可能存在多次。
- 当查询语句中的字段全部命中非聚簇索引时，那么就不必在进行回表查询。（即覆盖索引）。例如：我们在student表的score字段建立了索引，当执行select score from student where score > 90 的SQL查询时，因为索引在叶子节点上已经

注： InnoDB的主键索引时聚簇索引，二级索引时非聚簇索引，我们自己建的索引基本都是非聚簇索引。MyISAM中都是非聚簇索引。

三、正确使用索引

如何创建索引

索引创建原则

1. 非空字段：应指定非空（NOT NULL）字段的索引列，除非想要存储NULL。在mysql中，含有控制的列难以进行查询优化，因其使得索引的统计信息以及比较运算更加复杂。应该采用特殊字符或者空串代替NULL值。
1. 取离散大的字段：count((distinct col) / count( * )) （变量各取值之间的差异程度）的列放到联合索引的前面，可以通过count() 函数查看字段的差异，返回值越大说明字段的唯一值越多，字段的离散程度越高。
索引字段越小越好：从存储的角度来说，数据库的数据存储以页为单位，一页存储的数据越多，一次IO读取的数据量越大，效率越高。
1. 最左前缀匹配原则：创建多列索引（联合索引）时，要根据业务需求，where子句中使用最频繁的一列放在最左边。 MYSQL在查询时，会从左向右匹配，直到遇到范围查找（>、<、between、like）就停止匹配。例如：a = 1 and b = 2 and C > 3 and d =4，如果建立(a, b, c, d)顺序的索引，那么d是用不到索引的，如果建立(a, b, d, c)的索引，则都可以用到，a, b, d可以乱序。
1. =和 in 可以乱序: 例如：查询条件，a = 1 and b = 2 and d = 4 建立(a, b, d) 索引可以任意顺序。
1. 索引列不参与计算：索引列在参与计算时会导致索引失效，例如data(create_time) = '2022-04-06'就不会使用到索引，因为b+ 数中存的是数据表中的字段值。所以该语句应该写成：create_time=data('2022-04-06')
1. 尽量扩展索引，不要创建新的索引。比如表中已经有了(a)的索引，现在要加(a, b)的索引，那么直接修改原来的索引即可。

前缀索引

因为可能我们索引的字段非常长，这即占用内存空间，也不利于索引维护。所以我们考虑，将字段前面的公共部分作为一个索引，。但是，order by 不支持前缀索引。计算流程：先计算完整列的选择性：

select count(distinct col_1)/count(1) from table

在计算不同前缀长度的选择性：

select count(distinct left(col_1, 4))/count(1) from table

找到最优长度后，创建前缀索引：

create index idx_front on table (col_1(4))

联合索引

联合索引：使用多个字段同时建立一个索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

索引下推

MySQL5.6 引入了索引下推优化。
控制参数：set optimizer_swith='index_condition_pushdown=true'，默认开启。
1. 索引优化可以减少回表次数。
2. 在InnoDB中只针对二级索引有效。

索引失效

使用!=或者<>导致索引失效。
类型不一致导致索引失效。
函数导致索引失效。如：student表的create_time建索引列，但函数作用导致失效。

 select * from student data(create_time) = '2022-04-06'

基本运算符导致索引失效。（+、-、*、/、！操作后均不走索引）

 select * from student data(create_time) = '2022-04-06'

or 引起索引失效。(失效条件：or 连接非同一字段)

 select * from student name = "张三" or score = 90 ;

模糊搜索导致索引失效。（默认值在前走索引，默认值在后索引失效）

 select * from student where name like "%东";

not in 、 not exists导致索引失效。

MySQL索引