MySQL索引原理

一、索引类型

索引能显著提升查询速率，一般影响到where查询，order by排序等查询场景。当然降低插入，修改索引列速率。

• 从存储结构上划分：B Tree索引（B + Tree 索引）、Hash索引、FULLTEXT全文索引、R Tree索引
• 从应用层次划分：普通索引、唯一索引、主键索引、复合索引
• 从索引键值划分：主键索引、辅助索引
• 从逻辑关系划分：聚簇索引，非聚簇索引

二、索引原理

通俗的讲：索引是存储引擎用于快速查找记录的一种数据结构，需要额外的开辟空间和数据维护工作。

• 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页存储。
• 索引虽可一定程度上加快检索速度，但同时也会降低增删改操作数据（索引重排），且索引维护需要一定的系统资源。

索引执行所涉及的算法与结构：二分查找，Hash，B Tree（B + Tree）

二分查找

这里的二分查找与平时的二分法基本没有区别，用于B+ tree上同级索引值查询等，它在等值查询、范围查询性能较好，但在修改数据时效率较差且维护成本较高。

Hash结构

Hash底层实现是由Hash表实现的，是根据键值<key,value>存储的结构（结构类似Java HashMap数据结构），十分适合等值查询。

Hash索引可以方便的提供等值查询，但在范围查询效率较低，有时还需要全表扫描。
Hash缩影在MySQL中Hash结构主要应用在Memory原生的Hash索引、InnoDb自适应哈希索引。
这里主要说下InnoDb自适应哈希索引：
InnoDB存储引擎会监控表中各个索引页查询，当某些索引值被频繁访问时，会在内存中基于B+ Tree索引再创建一个哈希索引，使得内存中的B+ Tree 索引具备哈希索引的功能，即可使用等值查询快速访问索引页。

B+ Tree 结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上进行了优化

• B-Tree结构

  • 索引值和data数据分布在整个数结构中
  • 每个节点可以存放多个索引值以及对应的data数据
  • 树节点中多个索引值从左到右升序排序
    搜索：从根节点开始，对节点内索引值采用二分法查找，如果命中结束。没有则会进入子节点重复查找，直到所有节点指针为空，或已经是叶子节点查询结束。

• B+Tree

  • 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
  • 叶子节点包含了所有的索引值和data数据
  • 叶子节点用指针连接，提高区间的访问性能
    

这里我们需要知道：
1.InnoDB存储引擎最小存储单元为页，页可以用于存放数据与键值+指针，在B+数中叶子节点存放数据，非叶子节点存放键值+指针
2.InnoDB默认一个页大小为16K（可手动更改）
3.B+tree组成的索引表通过非叶子节点的二分法查找到数据指针确定数据在哪个页中，进而去查找需要的数据。
4.因为每个页大小固定所以在使用B树时因其每个节点存储了数据与指针会导致索引表页数过多（指针较少情况下只能不断增加树的高度，导致查询时IO操作增多），性能下降。而B+Tree基本不会出现这样的问题。
5.页里存的不是一条数据，在B+ Tree中 非叶子节点页中存储的是键值+指针（比如上图的15,56,77）,而叶子节点存放数据（比如上图的15/data）

联合索引在B+树上是如何保存的？

地址

聚簇索引与辅助索引

聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行
记录分开存放就属于非聚簇索引。

主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值
就属于辅助索引（二级索引）。

在innoDB引擎中，主键索引采用的就是聚簇索引的结构

• 聚簇索引
  聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。
  需要注意innoDB必须要有聚簇索引，选取方式如下：
  • 如果表定义了主键，则主键索引就是聚簇索引
  • 如果表没有定义主键，则第一个非空unique列作为聚簇索引
  • 否则InnoDB会从建一个隐藏的row-id作为聚簇索引
• 辅助索引
  InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列数据和主键的信息。二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。（平时我们所创建的大部分索引都是辅助索引）

三、 索引问题

回表查询

上文介绍了如果创建的是辅助索引那么叶子节点只保存索引列与主键主键值。那么如果我们的查询需要取辅助索引列之外的数据时，就需要先查询辅助索引的值与主键值，再通过主键值去聚簇索引中找到列的全部记录。这样相当于走了两次索引，造成回表查询。降低效率。
推荐一篇博客，写的很详细：
地址

覆盖索引

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。
实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

最左前缀原则

复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。（只要条件中包含最左列即可，位置交换时，mysql查询优化器会自动优化）

like 查询

MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。
select * from user where name like ‘%o%’; //不起作用
select * from user where name like ‘o%’; //起作用
select * from user where name like ‘%o’; //不起作用

NULL查询

虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以设置系统当前时间或某个固定的特殊值，例如’1970-01-01 00:00:00’。

部分索引失效

1. 不满足最左前缀原则
2. 范围索引列没有放到最后 （这里指的是联合索引中的范围列放在最后，不是指where条件中的范围列放最后。如果联合索引中的范围列放在最后了，即使where条件中的范围列没放最后也能正常走到索引。）
3. 使用了select *语句（部分情况下select * 会走全表，如数据量小，且select * 大概率会回表查询）
4. 索引列上有计算 （select * from test1 where height+1 =7）
5. 索引列上使用了函数（select * from test1 where SUBSTR(height,1,1)=8）
6. 字符类型没加引号
7. is null和is not null
   7.1. 如果字段不允许为空，则is null 和 is not null这两种情况索引都会失效。
   7.2. 如果字段允许为空，则is null走 ref 类型的索引，而is not null走 range 类型的索引。
8. like查询左边有%

联合索引abc, a=1 and b > 2 and c = 3 走那些索引？
会走ac索引