2021-11-29 数据库索引

关于索引的误区

第一个误区是，考虑到索引的维护代价、空间占用和查询时回表的代价，不能认为索引越多越好。索引一定是按需创建的，并且要尽可能确保足够轻量。一旦创建了多字段的联合索引，我们要考虑尽可能利用索引本身完成数据查询，减少回表的成本。第二个误区是，不能认为建了索引就一定有效，对于后缀的匹配查询、查询中不包含联合索引的第一列、查询条件涉及函数计算等情况无法使用索引。

此外，即使 SQL 本身符合索引的使用条件，MySQL 也会通过评估各种查询方式的代价，来决定是否走索引，以及走哪个索引。

因此，在尝试通过索引进行 SQL 性能优化的时候，务必通过执行计划或实际的效果来确认索引是否能有效改善性能问题，否则增加了索引不但没解决性能问题，还增加了数据库增删改的负担。如果对 EXPLAIN 给出的执行计划有疑问的话，你还可以利用 optimizer_trace 查看详细的执行计划做进一步分析。

索引的常见模型

实现索引的数据结构有很多，最常见的也是比较简单的数据结构有哈希表，有序数组和搜索树。

哈希表

哈希表是一种以键-值（key-value）形式存储数据的结构，我们只需要输入查找的键key，就可以得到对应的值value。哈希的思路是，把值放在数组里，用一个哈希函数把key换成一个确定的位置，然后把value放在数组的这个位置。

但是会有一种情况，就是多个不同的key有可能通过哈希函数的换算得到相同的位置，解决这种情况就是在这个位置拉出一个链表。

假如我们有一张用户表，用户昵称（nickname）字段使用的是哈希索引，我们需要根据昵称查询用户信息，这时哈希索引的示意图如下所示：

示意图中，user3和user4根据nickname字段算出来的位置都是4，所以在4位置用了一个链表表示，当我们在查询的时候，比如我们根据nickname4查询，查询步骤就是：先使用哈希函数计算nickname3得到4，然后遍历链表直到找到user4。

优点：因为哈希索引是根据索引字段计算位置，所以它的插入和根据key的查找会很快。

缺点：因为哈希索引是计算位置，而这个位置不一定是递增的，所以使用哈希索引做范围查询速度会很慢。如果要根据范围查找数据，就必须全部扫描一遍索引才能找到。

适合场景：哈希表适用于等值查询的场景，比如Redis或者其他的NoSQL数据库。

有序数组

还是上面的例子，如果是使用有序数组索引的话，示意图如下：

这个数组是根据nickname递增顺序保存的，如果我们要查nickname2对应的用户信息，用二分查找就可以很快找到对应的结果，时间复杂度为O(log(N))。

当然这个数据结构也是支持范围查询的，如果我们想要查到[nicknameX,nicknameY]这个区间的用户信息，我们只需要根据二分查找找到第一个nicknameX，然后向右遍历数组，找到找到最后一个nicknameY的用户即可。

优点：有序数组因为存入的数据已经是排好序的，所以根据等值查到和范围查到都比较快。

缺点：如果我们需要往数组中间插入一个值或者删除中间的某个值，那就需要挪动这个值所在位置后面的所有元素，成本比较高。

适合场景：有序数组适用于静态存储引擎，存储不会再修改的数据。

二叉搜索树

还是上面的例子，如果是二叉搜索树的话，示意图如下：

二叉树特点：每个节点的左儿子小于父节点，父节点小于右儿子。如果我们要查user2的话，跟着上图我们的查询路径就是：userA -> userB -> userD -> user2。时间复杂度为O(log(N))。

优点：查询效率高

缺点：因为索引不止存在于内存中，也要写到磁盘里。如果一个二叉树高度为20，我们查询某个用户信息就要访问20次磁盘，这个效率是非常低的。

适用场景：二叉树适用于表数据比较少的引擎。

为了减少树的高度，也就是减少对磁盘的访问，数据库索引就不能用二叉树。那么既然有二叉数，那就有N叉树，这里的N取决于数据块的大小。

在MySQL中，索引是在存储引擎层实现的，不同存储引擎的索引使用的数据结构可能都不一样。InnoDB的索引使用的数据结构为B+树。

InnoDB的索引模型

在InnoDB中，表都是根据主键顺序以索引的i形式存放的，这种存储方式的表称为索引组织表。每一个索引在InndDB中都对应一棵B+树。

假如我们有下面一张表：

create table T(

    id int primary key,

    k int not null,

    index (k)

)engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下：

从图中可以看出，根据叶子节点的数据，索引类型分为主键索引和非主键索引。

主键索引和非主键索引的区别：

主键索引的叶子节点存的是整行数据，在InnoDB里，主键索引也叫做聚簇索引；非主键索引的叶子节点存的内容是主键的值，在InnoDB里，非主键索引也叫做二级索引。

如果根据主键查询，则只需要查找id索引树即可；如果根据非主键索引查找（查找的数据不只有主键），则需要查找k索引树找到对应的主键，然后根据主键到id索引在查找一次。这个过程叫回表。