Mysql 索引

一、InnoDB的索引模型

InnoDB 使用了 B+ 树索引模型，能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数，所以数据都是存储在 B+ 树中 的。每一个索引在 InnoDB 里面对应一棵 B+ 树。什么是B+树
主键索引：

普通索引：

主键索引的叶子节点存的是整行数据（这就是为何不能重建或者删除主键索引，相当于重建表了），普通索引的叶子节点存储的是主键的值。在InnoDb中，主键索引也被成为聚合索引；普通索引被称为二级索引

主键索引与普通索引查询有什么区别：

从图中可以看出，主键查询直接通过Id直接可以查询到叶子节点的数据；而普通索引查询则先得到Id再通过主键索引树搜索一次，这个过程称为回表。

二、为何要使用自增主键

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的: NOT NULL PRIMARY KEY AUTO_INCREMENT。
1、性能方面（时间）：
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。需要逻辑上挪动后面的数据，空出位置。而更糟的情况是，需插入的所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新 的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中， 整体空间利用率降低大约 50%。
当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合
并的过程，可以认为是分裂过程的逆过程。
2、存储方面（空间）：
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。如果身份证坐主键，则普通索引的所占的空间比较大
什么情况下可以业务字段做索引，只有这个一个字段为主键索引。

三、索引查询

• 最左前缀原则
  B+树可以利用索引的“最左前缀”，来定位记录。最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符
  1、最左M个字符 如 like "a%"
  2、联合索引最左N个字段，尽可能的少维护索引。如有（a,b）则所以a，ab条件的查询都可以使用；如需b查询则需要考虑a索引和b索引的长度也就是使用空间
• 覆盖索引
  如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据。如建立身份证号和姓名的联合索引，则根据身份证号查询姓名时不需要回表查询
• 索引下推
  市民表为例（name,age）联合索引；
  select * from user where name like "a%" and age = 10;
  MySQL5.6版本之前，会对匹配的数据进行回表查询；
  5.6版本后，根据联合索引（覆盖索引）可以获取到age，直接过滤掉age!=10的记录，再进行回表查询，减少回表率，提升检索速度