索引定义
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。（可以理解为索引本身也是一种数据结构）
索引的数据结构
1、Hash表（散列表）
哈希表是一种以键值对（key-value）方式存储数据的结构，它使用存储在内存中的内容来创建表，将数据全部放在内存当中。
缺点：
（1）Hash存储将所有的数据文件添加到内存当中，会浪费内存空间。
（2）如果查询的是“=”（等值查询）速度会比较快，但是一旦是范围查询就需要全量扫描，即使是在内存中，速度也不容乐观也就失去了建立索引的意义。
（3）哈希冲突问题
适用场景：
等值查询场景，就只有KV键值对的情况，例如redis。
引申 --> 如何解决哈希冲突
1、开放寻址法（再散列法）
2、再哈希法
3、链地址法（拉链法）
4、建立公共溢出区
注：除Hash表外，其他的存储结构都是存储再磁盘中。我们每次读取磁盘，也就是进行io操作。磁盘读取依靠的是机械运动，分为寻道时间、旋转时间、传输时间三部分，这三部分耗时相加就是一次磁盘的io时间，大概是9ms左右。这个成本大概是访问内存的10万倍左右。
当索很大的时候，我们利用索引进行查询时，不能把索引全部加载到内存当中，只能逐一的加载每个磁盘页，磁盘页对应的就是索引树的节点。我们每次读取一页也就意味着进行了一次io操作。
2、二叉树/红黑树
二叉树和红黑树是有序的，是支持范围查询的，理想状态下的时间复杂度为O(log(n))。
缺点：
树的遍历时间复杂度和树的高度有关，二叉树/红黑树都可能因为树深度过深而造成io次数过多，影响查询效率。最坏情况可能会退化为链表结构，此时时间复杂度为O(n)。
3、B树
知道了二叉树/红黑树的弊端，为减少io操作次数我们就必须压缩树的高度。由此B树就诞生了。
大致了解一下B树定义：（假设m阶B树）
1、每个节点最多拥有m个子树
2、分支节点至少拥有m/2个子树（向上取整）（除根节点和叶子节点外都是分支节点）
3、根节点至少拥有2个子树
4、所有叶子节点都在同一层、每个节点最多可以有m-1个key,并且升序排序
意味着同一节点中可以存放多个key，而再同一个节点中不同key值比较是在内存中操作，这样相同数量的key在B树中生成的节点要远远少于二叉树中的节点。而相差的节点数量实际上就是磁盘io的操作次数。这样当达到一定数量之后，性能差异就会显现出来。
4、B+树
定义：
1、有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
2、所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3、所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
既然B树已经很好的压缩了树高，为什么还有引入B+树？
1、B+树改进了B树，让其内部节点去掉了指向data record的指针，使得每个节点可以存放更多的key,这样就能够使得树的层高进一步被压缩。
2、Ｂ树的查找只需找到匹配元素即可，最好情况下查找到根节点，最坏情况下查找到叶子结点，所说性能很不稳定，而Ｂ＋树每次必须查找到叶子结点，性能稳定。
3、叶子结点是链表（双向链表）形式, 因此也可以实现更方便的顺序遍历。B树的范围查找需要不断依赖中序遍历。首先二分查找到范围下限，在不断通过中序遍历，知道查找到范围的上限即可。整个过程比较耗时。而B+树的范围查找则简单了许多。首先通过二分查找，找到范围下限，然后同过叶子结点的链表顺序遍历，直至找到上限即可，整个过程简单许多，效率也比较高。
扩充：B+树是多叉树结构，每个结点都是一个16k的数据页，能存放较多索引信息，所以扇出很高。三层左右就可以存储2kw左右的数据，也就是说查询一次数据，如果这些数据页都在磁盘里，那么最多需要查询三次磁盘IO
索引分类
1、主键索引（PRIMARY）
数据列不允许重复，不允许为NULL，一个表只能有一个主键。
（二级索引，又称辅助索引）
2、唯一索引（UNIQUE）
数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
3、普通索引（INDEX）
可以通过 ALTER TABLE table_name ADD INDEX index_name (column); 创建普通索引
可以通过 ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3); 创建组合索引
4、全局索引（FULLTEXT）
可以通过 ALTER TABLE table_name ADD FULLTEXT (column); 创建全文索引
索引并非是越多越好，创建索引也需要耗费资源，一是增加了数据库的存储空间，二是在插入和删除时要花费较多的时间维护索引
5、前缀索引(Prefix)
前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
聚集索引和非聚集索引
聚集索引指索引结构和数据一起存放的索引，在MySQL中主键索引属于聚集索引。因为一个表的数据行的物理顺序唯一，所以一个数据表只能有一个聚集索引。
在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
聚集索引的优点：因为主键唯一，然后B+树的叶子节点又是有序的存储着索引结构和数据，所以定位到该索引的节点就相当于定位到了数据，查询速度非常快。
缺点：
（1）依赖于数据的有序性，所以在插入时会进行计算和排序。如果时整型自增主键还好，但是如果时类似于UUID类型的主键，在计算和排序时会浪费大量时间，这种效率时比较低的。同时自增主键在插入的时候因为已经是有序的，也可以减少节点分裂。
若没有自增主键，则会优先找一例所有值不同的列来构建B+树，如果也找不到则会维护一例rowId来作为隐藏键，保证唯一。
（2）更新代价很大，如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。
非聚集索引即索引结构和数据分开存放的索引。
二级索引属于非聚集索引，非聚集索引的叶子节点不一定存放数据的指针，而是存放主键，然后根据主键回表查数据。
非聚集索引的优点：更新代价比聚集索引小，因为叶子节点不存放数据。
缺点：
（1）依赖于数据的有序性。
（2）可能会二次查询（回表）：当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。
MySQL数据库的两种搜索引擎在磁盘中存储表的方式不同：
InnoDB（聚集索引）：.ibd文件包含索引和数据（单文件）
MYISAM（非聚集索引）：.MYD文件包含表的数据；.MYI文件包含表的索引
注意： 非聚集索引不一定需要回表查询，比如，查询的数据正好建立了索引且被查的刚好是key
MYISAM的主键索引的叶子结点存放的是指针，所以它确实需要回表，但是如果查的就是主键，那查到返回就行了，这种情况称之为覆盖索引。
覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。覆盖索引是一种数据查询方式,不是索引类型。
如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。
再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。
联合索引（组合索引、复合索引）
使用表中多个字段创建的索引称为联合索引。
在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如>、<、between和以%开头的like查询等条件，才会停止匹配。
所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。
假如建立联合索引(a,b,c)
1、全职匹配查询
select * from table_name where a = '1' and b = '2' and c = '3'
select * from table_name where b = '2' and a = '1' and c = '3'
select * from table_name where c = '3' and b = '2' and a = '1'
2、匹配左边的列时
select * from table_name where a = '1'
select * from table_name where a = '1' and b = '2'
select * from table_name where a = '1' and b = '2' and c = '3'
都是从最左边开始连续匹配，用到了索引，没有从左边开始的，最后查询没有用到索引，而是全表扫描。如果不连续（查询a和c），就只用到了a列的索引，b列和c列的没有用到。
3、匹配列前缀
如果列是字符型的话它的比较规则是先比较字符串的第一个字符，第一个字符小的哪个字符串就比较小，如果两个字符串第一个字符相通，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小，依次类推，比较字符串。
select * from table_name where a like 'As%'; //前缀都是排好序的，走索引查询
select * from table_name where a like '%As' //全表查询
select * from table_name where a like '%As%'//全表查询 4、匹配范围值
select * from table_name where a > 1 and a < 3
可以对最左边的列进行范围查询，但是多个列同时进行范围查询时，只有对索引最左边的那个列进行范围查找才用到B+树索引，也就是只有a用到索引。
5、精准匹配某一列并范围匹配另外一列
select * from table_name where a = 1 and b > 3;
如果左边的列是精确查找的，右边的列可以进行范围查找
6、排序
一般情况下，我们只能把记录加载到内存中，再用一些排序算法，比如快速排序，归并排序等在内存中对这些记录进行排序，有时候查询的结果集太大不能在内存中进行排序的话，还可能暂时借助磁盘空间存放中间结果，排序操作完成后再把排好序的结果返回客户端。
Mysql中把这种再内存中或磁盘上进行排序的方式统称为文件排序。文件排序非常慢，但如果order子句用到了索引列，就有可能省去文件排序的步骤
select * from table_name order by a,b,c limit 10;
这是因为B+树索引本身就是按照上述规则排序的，所以可以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了
order by的子句后面的顺序也必须按照索引列的顺序给出，如果顺序颠倒则没有用到索引。没有颠倒但不全则为用到部分索引。
假如联合索引左边列为常量，后边的列排序可以用到索引
select * from table_name where a =1 order by b,c limit 10;
索引下推
索引下推是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
创建索引的注意事项
1.选择合适的字段创建索引：
1.1、不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
1.2、被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
1.3、被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
1.4、频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
1.5、被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
2.被频繁更新的字段应该慎重建立索引。
虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。
3.尽可能的考虑建立联合索引而不是单列索引。
因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
4.注意避免冗余索引 。
冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。
5.考虑在字符串类型的字段上使用前缀索引代替普通索引。
前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。
使用索引的一些建议
1、对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
2、避免 where 子句中对字段施加函数，这会造成无法命中索引。
3、在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
4、删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用
5、在使用 limit offset 查询缓慢时，可以借助索引来提高性能
索引失效的场景
1、没有查询条件，或者在查询条件上没有建立索引或者使用引导列
2、索引字段中使用了运算或者函数，或者使用is null、is not null、!=、<>
3、or语句中前后字段没有同时都为索引
4、数据类型出现隐式转化， 例如字符串比较没有使用单引号
5、查询结果大于全表的30%（会走全表扫描），也就是全表查询本身就很快的时候
6、复合索引中没有遵循最左前缀原则
7、使用非后置Like通配符，如Like ”%02“不会生效，%只有写在最后面才会索引生效
