MySQL 索引

资源参考：mysql 实战 45 讲

Mysql 索引

• 索引的概念
• 索引的数据模型
• InnoDB 中的索引

索引的概念

索引，是为了提高数据库查询效率的产物。它可以由多种 数据结构 实现

索引常见的数据模型

哈希表

哈希表是一种以 key - value 键值对存储的数据结构，通过输入 key 找到其对应的 value 值。

• 优点：
  • 因为 数据无序 ，新增的时候不需要维护有序性，所以直接在 尾部追加 数据即可。新增 数据的 时间复杂度是 O(1)
  • 一个 key 对应一个 value ，在 hash 值不重复的情况下，根据 key 查询 对应 value 的 时间复杂度为 O(1)
• 缺点：
  • 因为数据是无序的，如果做 区间查询 ，key => [x, y] 的查询，只能全部扫描一遍。时间复杂度为 O(n)

所以哈希表常常适用于 等值查询 的场景，类似 memcached, redis

有序数组

有序数组，顾名思义，用数组存储一组数据，数据是按照某个值顺序排列的（递增或递减），是有序的。

• 优点：
  • 有序数组非常适合查询。在等值查询的场景下，因为数据的有序性，简单的，用 二分查找 即可找到需要的数据，时间复杂度为 O(logn) ；在 区间查询 的场景下，先用二分法找到区间中较小的数据，再遍历向右查询，直到找到第一个大于右区间的值为止。平均时间复杂度为 O(nlogn)
• 缺点：
  • 为了维持数据的有序性，在插入数据之后，可能会对数据进行 搬移操作 。平均情况时间复杂度为 O(n)
  • 如果在 指定位置插入 数据，那么其后的节点全部需要搬移。平均情况时间复杂度为 O(n)

所以有序数组，常常用来存储一些 静态数据 存储。上月销售量，销售额等。

搜索树

二叉搜索树

每个父节点只有两个子节点的书结构被成为二叉数，其 左子树小于父节点，父节点小于右子树

• 优点：
  • 二叉搜索树 搜索 节点的 时间复杂度为 O(logn)
  • 为了保证搜索的效率，所以需要保证是一颗 平衡二叉树 ，更新 时候的时间复杂度也为 O(logn)
• 缺点：对于数据存储系统而言，数据是写到磁盘上的，在查询的时候，需要尽量避免对磁盘的读写次数，这样才能达到效率最优化。
  由于 二叉树 叶子 节点个数的限制 ，如果作为数据库索引的数据结构，那么 100 节点的二叉树的树高就会是 20，也就是说，一次搜索，可能 需要访问到 20 个磁盘的数据快，这样的效率是很低的

N 叉搜索树

N 叉树是基于二叉树的基础上衍生出来的针对数据库系统高效的查询数据结构。N 的值取决于数据快的大小。在 InnoDB 中，一个整型索引的 N 差不多是 1200，也就是说，一个父节点可以存放 1200 个子节点。一个树高为 4 的 N 叉数，差不多能存储 17 亿行整型索引，极大地减少了对于磁盘的随机读取，进而提升了查询性能。

InnoDB 索引模型

InnoDB 的索引是基于 N 叉树设计的一种数据结构，叫 B+ Tree。在 InnoDB 中，表数据存储的逻辑结构，都是根据 主键顺序 以 索引 的形式存放，被成为 索引组织表。

故每一个索引在 InnoDB 中，都对应一颗 B+ Tree。

主键索引和非主键索引

根据 B+ Tree 叶子节点的内容不同，索引类型被分为了 主键索引 和 非主键索引。

一般地，InnoDB 引擎中的数据表，在指定主键的情况下，Mysql 会根据指定的列创建对应的索引树，而节点中的内容，是存放的整行数据。在 InnoDB 中，主键索引也被称为 聚簇索引。

非主键字段上建立的索引，叶子节点中存放的是 主键 的值。在 InnoDB 中，非主键索引也被称为二级索引。

主键索引和非主键索引查询的差别：

• 基于主键索引的查询，搜索主键所在的 B+ Tree 即可直接拿到所需要的行数据
• 基于非主键索引的查询，搜索全部行数据需要拿到主键 ID 后 回表 主键索引树，拿到对应的数据

主键索引查询会比非主键索引查询少一次 回表 的操作，所以性能上更优，尽量在使用中使用主键查询。