继续浅谈索引

未分类

本篇我们接着来谈索引

关于排序

先来谈一下MySQL的排序，MySQL有两种方式来产生有序的结果：
1. 通过排序操作
2. 按索引顺序扫描

我们知道索引结构本身就是有序的，那如何使用索引进行排序呢。这就需要我们的 query 的 order by 条件和 query 所利用的索引键一致并且索引访问方式为 rang、 ref 或者 index ，这样利用索引直接取得已经排好序的数据。在关联多张表的情况下，那就只有当 order by 子句引用的字段全部在第一个表，这是才能使用索引做排序，当然， order by 子句仍需要满足索引的最左前缀原则。那么如果不能使用索引做排序的情况下，MySQL就会使用排序算法来产生有序结果。

MySQL 是可以满足使用同一个作引同时进行查找行以及排序操作的。在设计索引时，尽量同时满足这两种任务。

思考一个问题，按索引顺序读取数据与顺序的全表扫描数据哪一种方式快？
通常情况下是顺序的全表扫描比较快。有的人可能会有疑问，我们在进行查询时不都是尽量避免扫描表，更多的利用索引来提高效率么，为什么全表扫描反倒比按索引读取要快了呢？其实扫描索引本身是很快的，因为查询时只需要从一条索引记录移动到紧邻的下一条。但是当我们的索引结构不能形成覆盖索引时，那么就会每扫描一条索引记录就回表查询一次对应的行，而这基本上都是随机I/O 。因此通常情况下顺序全表扫描要比按索引顺序读取数据要更快。

关于冗余

说到冗余，我们知道MySQL是允许在相同的列上创建多个索引，基于此有时我们在创建使用索引的过程中会创建出重复索引。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。由于MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也会去考虑全部的索引，这也会影响性能。

另外一种情况是冗余索引，之前提到过InnoDB的索引遵循最左前缀原则，也就是说假如我有索引 (a,b) ，那么我就同时有了 (a) 索引，有时我们会再次单独创建索引 (a) ，这就是冗余的索引。大多数情况下冗余索引都是不需要的，应当尽量扩展已有的索引而不是创建新的索引。当然场景万千，有时出于各方面考虑也会特意使用冗余索引来提升效率。

比如我们现在在一个整数列上有一个索引，现在需要额外增加一个很长的varchar类型列来扩展该索引，那性能可能会受到很大影响。具体场景应有具体的分析及解决方法，要权衡各方面利弊。

最后再提一种情况，对于InnoDB来说主键列是已经包含在二级索引中的了，那么我们现有一个索引 (a) ，其实在 (a) 上的索引就相当于在 (a, id) 上的索引。我们现在有这样一个场景，如果我们的 query 是 ‘where a = 5 order by id’ 那么这个索引将会被非常有效的利用，这是如果我们把索引 (a) ，扩展为 (a,b) 那么实际上就相当于是 (a,b,id) 这样的索引了，这时如果进行上面的 query ，很显然的将只能使用到索引a，而排序操作搜索引擎将不得不进行文件排序，这点要十分注意。