mysql索引基础_MySQL索引（一）索引基础

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

常见模型

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

哈希表

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

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

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

有序数组

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

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

搜索树

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

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

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

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

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

索引类型

B&＃43;树索引

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

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

主键索引

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

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

普通索引

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

基于主键索引和普通索引的查询有什么区别&＃xff1f;

如果语句是select * from T where ID&＃61;500&＃xff0c;即主键索引查询方式&＃xff0c;则只需要搜索ID这棵B&＃43;树&＃xff1b;

如果语句是select * from T where k&＃61;5&＃xff0c;即普通索引查询&＃xff0c;则需要先搜索k索引树&＃xff0c;得到ID的值为500&＃xff0c;再到ID索引树搜索一次。这个过程称为回表。

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

哈希索引

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

下面是创建Memory引擎表的语句&＃xff1a;

CREATE TABLE &＃96;testhash&＃96; (

&＃96;fname&＃96; varchar(50) DEFAULT NULL,

&＃96;lname&＃96; varchar(50) DEFAULT NULL,

KEY &＃96;fname&＃96; (&＃96;fname&＃96;) USING HASH

) ENGINE&＃61;MEMORY;

哈希索引限制

哈希索引只保存哈希码和指针&＃xff0c;而不存储字段值&＃xff0c;所以不能使用索引中的值来避免读取行。不过访问内存中的行速度非常快&＃xff0c;所以对性能影响并不大。

哈希索引数据并不是按照索引值顺序存储的&＃xff0c;所以无法无法用于排序

哈希索引不支持部分索引列查找&＃xff0c;因为哈希索引始终是使用索引列的全部内容来计算哈希码。

哈希索引只支持等值比较查询&＃xff0c;不支持范围查询。

哈希冲突会影响查询速度&＃xff0c;此时需要遍历索引中的行指针&＃xff0c;逐行进行比较。

如果哈希冲突很多&＃xff0c;一些索引维护操作的代价会很高。

自定义哈希索引

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

内容来源于网络如有侵权请私信删除