MySQL索引

索引

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样

索引常见的模型

1. 哈希表，这种结构适用于只有等值查询的场景
2. 有序数组，等值查询和范围查询场景中的性能就都非常优秀，但更新比较慢。有序数组索引只适用于静态存储引擎
3. 树。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。你可以想象一下一棵100万节点的平衡二叉树，树高20。一次查询可能需要访问20个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说，对于一个100万行的表，如果使用二叉树来存储，单独访问一个行可能需要20个10 ms的时间，这个查询可真够慢的。为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N叉”树。这里，“N叉”树中的“N”取决于数据块的大小。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。你可以想象一下一棵100万节点的平衡二叉树，树高20。一次查询可能需要访问20个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说，对于一个100万行的表，如果使用二叉树来存储，单独访问一个行可能需要20个10 ms的时间，这个查询可真够慢的。为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N叉”树。这里，“N叉”树中的“N”取决于数据块的大小。

N叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

Innodb 的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。

每一个索引在InnoDB里面对应一棵B+树。

索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引维护

• 由于B+树为了维护索引的有序性，在插入新值的时候需要做必要的维护。会涉及到数据的挪动。而且如果当前数据页已经满的话，会新建一个数据页，把部分数据挪过去。这个过程称为页分裂。会对性能造成影响
• 当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。

基于上面的案例，一般要求我们在建表的时候要有自增主键.也就是说,每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型（bigint）则是8个字节。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求是这样的：

1. 只有一个索引；
2. 该索引必须是唯一索引。

你一定看出来了，这就是典型的KV场景。

由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

这时候我们就要优先考虑“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

数据页的合并

当从InnoDB表删除数据时，相应的数据是先打上删除标签（deleted mark），而后再由purge线程执行清理工作。

清理工作结束后，如果两个相邻的数据页存储填充率低于一定程度，就会尝试合并页，以降低碎片率，提高存储效率。

或者经过多次长度变小的UPDATE操作后（将varchar列长度更新变短），数据页填充率低于一定程度也会尝试合并。

合并完毕之后，空出来的页就会被标记为空闲页，等待再分配。这个工作是InnoDB后台线程自动完成的，无需人为干预、控制。

• 合并数据页，是通过参数 MERGE_THRESHOLD的值确定的，当InnoDB数据页填充率低于该阈值时，就会尝试进行合并页操作。该参数的默认值是50，最小值是1，在5.6版本之后允许自行指定设置，在5.6之前的版本中则是被硬编码的，无法修改.

• 默认的是当发现两个相邻页的填充率都低于50%时，就会尝试进行合并。

• 除了表可以设置外，单个索引也可以设置合并阙值.这个只能在创建索引时一次性指定，不能中途修改

  ALTER TABLE t_sk ADD INDEX k1(c1) COMMENT 'MERGE_THRESHOLD=20';
  

• 页合并的统计情况，可以通过查询 INNODB_METRICS 表获取到。

• alter table T engine=InnoDB #重建索引
  

覆盖索引

正常的根据索引进行查询，会有回表的过程。即查询两次。

而覆盖索引可以减少树的搜索次数，显著提提升查询性能，所以使用覆盖所以是一个常用的性能优化。

select * from T where k between 3 and 5 #会回表查询
select ID from T where k between 3 and 5 # 普通索引记录了主键的值，直接返回结果

联合索引

联合索引也属于覆盖索引的一种

比如我们在市民信息表中，建立一个（身份证、姓名）的联合索引。当用户根据身份证来查询他的姓名时，这个索引就起到了覆盖索引的效果。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

当然这种索引的维护是有代价的，它是以空间来换时间。

最左前缀原则

通过最左匹配原则，可以减少索引建立的次数.比如（a,b,c） 实际建立了(a,b,c),(a),(a,b)三个索引

注意事项:

• 遇到范围查询(>,<,between,like)会停止
• where中and条件的先后顺序对如何选择索引是无关的。因为优化器会去分析判断选用哪个索引。
• 部分值匹配时，只要条件中有最左索引项就会用到索引
• 条件中没有最左索引，不会用到索引，全表扫描
• like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
• or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
• 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
• 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引。
• 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描
• 对索引字段进行计算操作、字段上使用函数
• 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

索引下推

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

这个语句，只会使用到name 这个索引

在MySQL 5.6之前，只能开始一个个回表。到主键索引上找出数据行，再对比字段值。

而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。