Mysql 系列 | 索引

索引就相当于一本书的目录，有了这个目录能更快查到自己想要的内容。大大提高数据查询效率。

Mysql 中索引在存储引擎层实现。

数据库中的索引

索引模型

哈希表

  • 以键值对（key-value）存储数据

  • 多个 key 经过哈希函数可能会计算出同样的值，相同 key 对应一个链表循环取值。

  • key 值不递增，插入数据比较快，直接放在最后面。

  • key 值不递增，查询区间值时需要扫描所有数据，比较慢。

  • 适用于等值查询

有序数组

  • 因为是有序的，查到一个往后遍历即可，查找数据比较快。

  • 插入新数据需要挪动后面的所有数据，成本较高

  • 适用于静态存储引擎，不经常修改的数据。

二叉搜索树

  • 每个节点左儿子小于父节点；父节点小于右节点。

  • 平衡二叉树搜索效率最高，但搜索引擎中常用 N 叉树。

  • 引入一段丁奇对 N 叉树的描述

你可以想象一下一棵100万节点的平衡二叉树，树高20。一次查询可能需要访问20个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说，对于一个100万行的表，如果使用二叉树来存储，单独访问一个行可能需要20个10 ms的时间，这个查询可真够慢的。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N叉”树。这里，“N叉”树中的“N”取决于数据块的大小。

以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了。

考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

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

索引

InnoDB 的索引模型

  • InnoDB 中表都是根据主键顺序，以索引的形式存放，称为索引组织表。

  • 所有数据存储在 B+ 树中，很好的配合磁盘的读写特性，减少单次查询对磁盘的读写次数。

  • 主键索引 & 非主键索引
    image
    （丁奇原图）

  • 主键索引 & 非主键索引

    • 主键索引叶子节点存放整行数据，上图左边。按照主键查询只需要去 ID 树查找。

    • 非主键索引叶子节点存放主键，上图右边，按照普通字段查询则需要先去 k 树查到对应的 ID，再去 ID 树找到对应的行数据。

  • 自增主键 & 普通主键

    • 自增主键，可以保证每次插入数据直接放在后面，不会存在从中间插入，要挪动后面数据的情况。

    • 比起字符串字段做主键，自增数字主键占用内存更小

在我工作的项目中，除了自增主键，其他的一概不用。。。

posted @ 2022-08-01 16:38  菜乌  阅读(56)  评论(0)  编辑  收藏  举报