【MySql学习笔记】MySql索引

索引是什么

    索引：对数据库表中的一列或者多列数据进行排序的一种结构。使用索引可以快速查询数据库表中的特定信息。

存储引擎都用了哪些索引

      InnoDB:B+树索引，叶子结点保存的是完整的数据记录。

      Memory:hash索引。

      MyISAM:B+树索引，叶子结点保存的是数据记录的地址。

为什么InnoDB存储引擎底层数据结构使用B+树，而不是B树？

• 数据结构的角度

• 哈希表，HashMap：会出现哈希冲突的问题，如果使用这种结构，那么就需要设计很好的避免哈希冲突的算法，保证数据尽可能的更加的散列；使用这种数据结构要一次性申请很多的内存，比较浪费内存空间；在查找数据的时候，需要进行挨个的匹配，不适合进行范围查询。

• 二叉树/二叉搜索树：在数据全部是升序或者降序的时候，二叉树就退化成了链表，查询数据比较慢。

• 二叉平衡树：通过左旋、右旋和双旋的操作，使得树尽量保持平衡；这样一来，查询的性能就能够提升，但是如果需要频繁的插入操作，不断的通过旋转来调整树结构，那么插入的效率就会降低。

• 使用各种类型的二叉树存储数据的时候，每个结点只能保存一个数据，如果需要插入更多的数据的话，那么只能通过增加树的深度来保存数据，但是加深数据的深度在操作数据库的时候IO次数就会增多。

• 为什么树越深，IO次数越多？在读取数据的时候，需要磁盘预读，使用树结构的时候，每次读取数据都是4k，但是每一个结点存储的都是一条数据，这样就造成了空间的浪费。

• 读取数据时的两个概念B树：我们考虑每一个结点多保存一些数据，二叉树变成多茶树的结构，这样一来就可以降低树的深度；每一个结点可以多存放几个结点，结点中的值保存key和value。B树存储数据多，可能树高会高一些，IO效率低。B+树IO次数少。

  • 局部性原理：数据和程序都有聚集成群的倾向，之前被访问过的数据很可能再次被访问到
  • 磁盘预读：内存与磁盘进行交互的时候有一个最小的逻辑单元：页，一般是由操作系统决定他都多大（4k/8k）,数据交互的时候可以取页的整数倍进行读写。InnoDB每次读取16K。

• B+树：在创建索引的时候，尽量给整型的数据创建索引，因为整型占用的字节数小；B+树的非叶子结点只是存储了key值，value由叶子结点保存，叶子结点保存了这些key和对应的value；千万级别的数据可能树能够达到3-4层，尽量不要多于3层，多一层就会增加更多的内存占用，多出来一点内存占用其实影响很大，多个人并发操作的时候，相当于每个人在请求的时候都多出来这么多内存；主键最好是自增的，在插入数据的时候，树的结构不容易发生很大的变化，如果不是自增的数据可能造成树的结构发生改变，增加索引维护的成本。

• 数据库的角度

• 存储引擎：不同的存储引擎表示了不同的文件在磁盘上面的表现形式（不同的存储引擎数据的存储形式不同）。

• InnoDB B+树索引：非叶子结点放入的key值，叶子结点放入的key值和整行的行记录。其数据文件和索引文件是放在一起的。在存储数据的时候必然会有一个key作为聚簇索引，可能是主键，唯一键，或者rowid（rowid占6个字节）

• MyISAM B+树索引：非叶子结点放入的key值，叶子结点放入的是存储文件的一个地址。其数据文件和存储文件是分开存放的。MyISAM中没有聚簇索引。

• B+树是B树的变种，B+树的非叶子结点只用来保存索引，不存储数据，所有的数据都保存在叶子节点上。而B树的非叶子结点也会保存数据。B+树的查询效率更加的稳定，都是从根节点到叶子结点。

• B+树的内部节点没有指向关键字具体信息的指针，其内部节点相对于B树来说更小，同样的空间可以读入更多的节点，B+树磁盘读写代价更低。

• 聚簇索引与非聚簇索引　　

  • 聚簇索引：索引树的叶子结点存储的是整行数据，一个表只能有一个聚簇索引；数据和索引是一起存放的，聚簇索引的key值可以是主键、唯一键或者rowid。

  • 非聚簇索引：普通索引，叶子结点存储的是主键的值；在InnoDB中，非聚簇索引也叫做二级索引；如果创建了一个普通索引，那么叶子结点中存放的值是聚簇索引的key值。

索引分类

　 数据库：哈希索引，全文索引，空间索引，B+树索引

    InnoDB存储引擎：单列索引，主键索引（聚簇索引），唯一索引，联合索引

    经常进行查询的数据，并且很少进行修改的数据适合创建索引。在插入数据的时候会重新创建索引树，对性能会造成一定的影响。

相关名词

回表

    主键查询方式：搜索主键索引树，其叶子结点保存了整行数据记录，直接获取即可。

    普通查询方式：首先搜索普通索引树，得到对应的主键值，然后根据主键值去主键索引树中在搜索数据，即可获得相应的数据，这个过程就是回表，普通索引树上没有相应的数据，需要回表到主键索引树中在查询一次。回表操作增加了IO次数。

索引覆盖

    查询结果中的字段是索引字段的子集，这个就是覆盖索引。如果顺序不一样的时候，底层优化器会进行优化。

    如果在普通索引树上的查询已经直接提供了结果，不需要进行回表的操作，这样的普通索引就叫做覆盖索引。覆盖索引可以有效的提高查询的效率，是常见的MySql性能优化的手段。

最左匹配

    在联合索引的情况下，不需要定义全部的索引，只要索引满足最左前缀，索引就可以生效，可以利用索引提高查询效率。

索引下推

      MySql5.6之后引入了索引下推优化，在索引遍历的过程中，对索引中包含的字段优先做判断，直接过滤掉不满足条件的记录，减少回表的次数，提高查询的效率。

select * from table where name=? and age=?

    如果没有索引下推：数据存储在存储引擎中，server层负责数据的筛选，先根据name的值从存储引擎中把符合条件的过滤出来，然后在server层对age进行筛选。

    有索引下推：可以直接根据name和age的值从存储引擎中进行筛选数据，不需要在server层中做任何的处理。减少了server层和存储引擎之间的IO次数，回表的次数也减少了。

MySql逻辑架构

• server层

  • 连接器：验证客户端权限，建立和断开MySql连接
  • 分析器：进行Sql语句的语法分析
  • 优化器：选择索引，生成具体的Sql语句执行计划
  • 执行器：操作存储引擎，执行Sql，返回执行结果

• 存储引擎层：各个不同的存储引擎都提供了一套读写接口来操作数据库

Sql执行计划

    explain分析执行计划

    show profile分析Sql

索引优化

• explain分析执行计划

• 慢查询日志

  • 数据库层面可以看日志
  • MyBatis-Plus插件

• 避免索引失效

  • 全值匹配：对索引中的具体的列都指定具体的值；进行查询的时候联合索引中的每一个索引都有值；
  • 最左索引法则
  • 尽量使用覆盖索引，避免使用select *
  • 回表查询
  • 通过覆盖索引解决like模糊查询索引失效
  • in走索引，not in索引失效

• 索引失效

  • 违背最左索引法则
  • 范围查询
  • 索引列有运算操作
  • 字符串不加单引号
  • 使用or分割条件，or前面有索引列，后面没有
  • %开头的模糊查询
  • is null，is not null可能会失效，全表扫描块全表扫描，否则就走索引