【MySQL】索引

学习于极客时间

哈希索引

哈希索引做区间查询是很慢的，哈希表适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。

有序数组索引

有序数组在等值查询和范围查询场景中的性能就都很好。

如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

有序数组索引只适用于静态存储引擎，比如你要保存的是省市这类几乎不会再修改的数据。

二叉树索引

二叉树为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。

为什么数据库存储使用b+树 而不是二叉树？因为二叉树树高过高，每次查询都需要访问过多节点，即访问数据块过多。索引不止存在内存中，还要写到磁盘上，而从磁盘随机读取数据块过于耗时。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，就不应该使用二叉树，而是要使用N 叉树。

N叉树索引

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

redis中使用的是跳表的数据结构。

InnoDB索引模型

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

InnoDB 的索引组织结构

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引： key:主键的值，value:整行数据。主键索引也被称为聚簇索引。

非主键索引： key：索引列的值， value:主键的值。非主键索引也被称为二级索引。

基于主键索引和普通索引的查询有什么区别？

主键索引 是唯一且有序的，非主键索引，是需要找到结果ID，回表，获取最后的结果的。基于非主键索引的查询需要多扫描一棵索引树，因此，我们应尽量使用主键查询。索引不是越多越好的。

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。

当然有分裂就有合并，当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

自增ID

自增主键是指自增列上定义的主键。插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。也就是说，自增主键的插入数据模式，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

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

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

那么，什么场景适合用业务字段直接做主键的呢？

——只有一个索引，且该索引必须是唯一索引。

重建索引

重建普通索引

alter table T drop index k;
alter table T add index(k);

重建主键索引

alter table T drop primary key;
alter table T add primary key(id);

修改主键索引会影响普通索引。所以怎么修改普通索引和主键索引，主要要考虑的问题是减少修改主键索引对普通索引的影响！

覆盖索引

创建一个索引，该索引包含查询中用到的所有字段，称为“覆盖索引”。

使用覆盖索引，MySQL 只需要通过索引就可以查找和返回查询所需要的数据，而不必在使用索引处理数据之后再进行回表操作。

覆盖索引可以一次性完成查询工作，有效减少IO，提高查询效率。当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

也就是说，查询的列和索引的列一致，可以使用覆盖索引，查询中应该尽量使用覆盖索引，减少使用 select * from。

示例

查询语句：

SELECT col2, col3 FROM test_table WHERE col1 = xxx ORDER BY col2;

创建一个单列索引：

ALTER TABLE `test_table` ADD INDEX `idx_col1`(`col1`) USING BTREE;

分析查询：

EXPLAIN SELECT SQL_NO_CACHE col2, col3 FROM test_table WHERE col1 = xxx ORDER BY col2;

结果：对 col1 建立索引后，type 为 ref，使用到了 idx_col1 索引。

---

修改索引，根据查询语句，建立一个联合索引：

ALTER TABLE `test_table` DROP INDEX `idx_col1`;
ALTER TABLE `test_table` ADD INDEX `idx_col1_col2_col3`(`col1`，`col2`，`col3`) USING BTREE;

分析查询：

EXPLAIN SELECT SQL_NO_CACHE col2, col3 FROM test_table WHERE col1 = xxx ORDER BY col2;

结果：建立联合索引后，type 为 ref，使用了 idx_col1_col2_col3 索引，Extra 为 Using index，说明使用了覆盖索引。

前缀索引

最左前缀原则

MYSQL做词法分析语法分析的时候是通过建立最左子树来建立语法树的，解析的过程也是从左到右所以遵循最左前缀的原则。

只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

那么，在建立联合索引的时候，如何安排索引内的字段顺序呢？

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。

这时候，我们要考虑的原则就是空间了。比如上面这个市民表的情况，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

索引下推

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

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

对于二级索引C，会默认和主键做联合索引。所以索引c的排序为cab，索引cb的排序顺序为cba。所以，结论是 ca 可以去掉，cb 需要保留。