MySQL索引(一)索引基础

索引是数据库系统里面最重要的概念之一。一句话简单来说,索引的出现其实是为了提高数据查询的效率,就像书的目录一样。

常见模型

索引的出现是为了提高查询效率,但是实现索引的方式却有很多种,这里就介绍三种常见、也比较简单的数据结构,它们分别是哈希表、有序数组和搜索树。

哈希表

哈希表是一种以key-value存储数据的结构。通过哈希函数把key换算成一个确定位置,然后把value放在这个数据的这个位置上。

但是当存储的数据越来越多,就有可能出现两个不同的key通过哈希函数得到了一样的值,这时候就出现冲突。而这时就引入链表来解决这种冲突了。

哈希表这种数据结构适用于只有等值查询的场景,时间复杂度为O(1),但是对于范围查找就必须全部遍历了,时间复杂度为O(n)。

有序数组

有序数组在等值查询和范围查询场景中的性能非常优秀。

但是需要往中间插入时就必须要挪动数据,时间复杂度很高。

搜索树

二叉搜索树在查询和插入、删除数据方面能够中和上面两种结构。查找时间复杂为O(logn)、插入删除的时间复杂度为O(logn)。

虽然二叉搜索树的搜索效率很高,但是在大多数的数据库并不使用二叉树。原因是索引要写在磁盘上。

磁盘上的随机读是很耗时间的,为了让一个查询尽量少地读磁盘,就必须在查询过程中访问尽量少的数据块。

那么就不应该使用二叉树,而是要使用“N”叉树,这里的“N”取决于数据块的大小。

B+树是为磁盘设计的一种平衡查找树。在B+树中,所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上,由各叶子节点指针进行连接。

image

索引类型

B+树索引

数据库中的B+树索引可以分为主键索引和普通索引两种,也有叫聚集索引(clustered index)和辅助索引(secondary index)

但不管是主键索引还是普通索引,都是使用B+树的,即高度平衡的,叶子节点存放着所有数据。

主键索引

InnoDB存储引擎表是索引组织表,即表中数据按主键顺序存放。

主键索引就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的是行数据。每张表只能拥有一个主键索引。

普通索引

普通索引与主键索引的区别在于,普通索引的叶子节点并不包含行数据,而是包含主键。

主键索引和普通索引的B+树

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

  • 如果语句是select * from T where ID=500,即主键索引查询方式,则只需要搜索ID这棵B+树;
  • 如果语句是select * from T where k=5,即普通索引查询,则需要先搜索k索引树,得到ID的值为500,再到ID索引树搜索一次。这个过程称为回表。

也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此我们应该尽量使用主键索引查询。

哈希索引

哈希索引基于哈希表实现,在MySQL中只有Memory引擎显示支持哈希索引,也是Memory引擎表的默认索引类型。

下面是创建Memory引擎表的语句:

CREATE TABLE `testhash` (
  `fname` varchar(50) DEFAULT NULL,
  `lname` varchar(50) DEFAULT NULL,
  KEY `fname` (`fname`) USING HASH
) ENGINE=MEMORY;
哈希索引限制
  1. 哈希索引只保存哈希码和指针,而不存储字段值,所以不能使用索引中的值来避免读取行。不过访问内存中的行速度非常快,所以对性能影响并不大。
  2. 哈希索引数据并不是按照索引值顺序存储的,所以无法无法用于排序
  3. 哈希索引不支持部分索引列查找,因为哈希索引始终是使用索引列的全部内容来计算哈希码。
  4. 哈希索引只支持等值比较查询,不支持范围查询。
  5. 哈希冲突会影响查询速度,此时需要遍历索引中的行指针,逐行进行比较。
  6. 如果哈希冲突很多,一些索引维护操作的代价会很高。
自定义哈希索引

在InnoDB中,某些索引值被使用的非常频繁的时候,它会在内存中基于B+树的基础上再创建一个哈希索引,使其不必要从根节点就查找。完全自动的内部行为,用户无法配置或更改。

posted @ 2020-12-06 22:01  大杂草  阅读(219)  评论(0编辑  收藏  举报