原文《MySQL实战45讲》
在日常工作中经常接触到数据库索引,但到底什么是索引,索引又是如何工作的呢? 索引的出现其实就是为了提高数据查询的效率,就像书的目录一样。一本500页的书,如果你想快速找到其中的某一个知识点,在不借助目录的情况下,那只能一页一页的翻找。同样,对于数据库的表而言,索引就是它的”目录“。
实现索引的方式有很多种,所以这里引入了索引模型的概念。可以用于提高读写效率的数据结构有很多。这里先介绍三种比较常见且比较简单的数据结构,分别是哈希表、有序数组、搜索树。
哈希表是一种以键-值(key-value)存储数据的结构。哈希的思路很简单,把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后把value放在数组的这个位置。不可避免,可能会出现多个不同的key经过哈希函数的换算后得到了同样的value的情况。处理这种情况的一种方法是,拉出一个链表。假设,现在维护着一个身份证信息和姓名的表,需要根据身份证号查找对应的名字,这时对于的哈希索引的示意图如下: 需注意的是,由于该模型新增数据的速度很快,因为需要往后追加。但缺点是,因为不是有序的,所以哈希索引做区间查询的速度很慢,需要全表扫描。所以哈希表这种结构适用于只有等值查询的场景。
而有序数组在等职查询和范围查询场景中的性能就都非常优秀。还是用上面身份证号查询名字的例子,如果使用有序数组实现的话,示意图如下: 由于数组是按照身份证号递增的顺序保存的,所以使用二分法可以快速的根据身份证号查询名字,时间复杂度为 O(log(N))。有序数组的查询效率非常高,但是,在更新数据的时候就麻烦了,往中间插入一个记录,就必须得挪动后面所有的记录。所以,有序数组只使用于静态存储引擎,比如你要保存的是2017年某个程释的所有人口信息,这类不会再修改的数据。
第三种就是二叉搜索树了,还是上面的例子,示意图如下: 二叉搜索树的特点是:每个节点的左儿子小于父节点,父节点又小于右儿子。这种结构根据身份证号查询名字的时间复杂度是O(log(N))。更新的时候,需要保持这棵树是平衡二叉树,所以更新的时间复杂度也是O(log(N))。为了维持O(log(N))的查询复杂度,做更新操作时就需要保证这棵树是平衡二叉树。为了做这个保证,更新的时间复杂度也是O(log(N))。二叉树的搜索效率是最高的,但是实际上大多数的数据库存储却不使用二叉树。其原因是,索引不只存在内存中,还要写到磁盘上。由于每个节点最多只有两个子节点,数据多的时候,树高会比较高。如100万节点的平衡二叉树,树高20(计算公式为:节点数 = 2 ^ 树高 - 1)。也就是说,一次查询可能需要访问20个数据块。 为了让一个查询尽量少的读磁盘,就必须让查询过程访问尽量少的数据库。所以,数据库索引存储使用的是 ”N叉树“,”N“取决于数据块的大小。 InnoDB的一个整数字段索引为例,这个N差不多是1200。这个树高为4的时候,就可以存1200的3次方个值,这已经17亿了。考虑到树根的数据块总是存在内存中,一个10亿行的表上一个整数字段的索引,查询一个值最多只要访问3次磁盘。同时,树的第二层页由很大概率存在内存中,那么访问磁盘的平均次数就更少了。
由于MySQL的索引是存储引擎层实现的,所以不同的存储引擎的索引工作方式并不一样。此处以InnoDB为例,分析InnoDB的索引模型。 在InnoDB的中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为缩影组织表。又因为前面我们提到的,InnoDB使用了B+树索引模型,所以数据都是存储在B+树中的。每一个 索引在InnoDB中对应一棵B+树。 索引类型分为主键索引和非主键索引。主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引。非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引。
假设,表结构如下,表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(400,4)、(500,5)、(600,6);
mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k)
)engine=InnoDB;
B+树为了维护索引有序性,在插入新值的时候需要做必要的维护。如果插入新的行ID值为700,则只需要在R5的记录上插入一个 新的记录。如果新插入的ID值为400,就需要逻辑上挪动后面的数据,腾出位置。假如R5坐在的数据页已经满了,根据B+树的算法,这个时候需要申请一个新的 数据页,然后挪动部分数据过去,这个过程称为页分裂。在这种情况下,性能自然会受影响,除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约50%。当然,有分裂,就有合并,当相邻的两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。
alter table T engine=InnoDB;
我们一起来看一下这个SQL查询语句的执行流程: select * from T where k between 3 and 5;
在这个过程中,回到主键索引搜索的过程,我们称为回表。这个查询过程,读了k索引树的3条记录(步骤1、3、5),回表了两次(步骤2、4)。
就上面的例子,如果SQL改为 : select ID from T where k between 3 and 5,这时只需要查ID的值,而ID的值已经在k索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引k已经覆盖了我们的查询需求,我们称为覆盖索引。 由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录。不只是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。 在建立联合索引的时候,如何安排索引内的字段顺序:
上一段我们说到满足最左前缀原则的时候,最左前缀可以用于在索引中定位记录。这时,你可能要问,那些不符合最左前缀的部分,会怎么样呢? 以市民表的联合索引(name, age)为例,如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是 10 岁的所有男孩”。那么,SQL 语句是这么写的:
mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。