固定链接 基于滴滴云 MySQL 验证索引优化简单查询

基于滴滴云 MySQL 验证索引优化简单查询

基于滴滴云 MySQL 验证索引优化简单查询

1. MySQL 的索引

索引用于快速查找具有某一特定列值的行,如果不使用索引,MySQL 必须从表中的第一行开始读取整个表来找到相关的行,表越大,开销越大。如果表中有待查询列的索引,MySQL 可以在数据文件中快速定位所要查找的内容,而不用查询所有数据,速度大大优于顺序读取每一行数据。

本文将重点介绍基于滴滴云 MySQL 验证默认引擎 InnoDB 下索引对于简单查询的优化效果。

2. B+ 树与 InnoDB 引擎的索引

InnoDB 存储引擎支持 B+ 树索引、哈希索引、全文索引和空间索引,本文主要介绍 B+ 树索引。

B+ 树是由二叉树演变而来,关于二叉树和二分法在这里不再赘述,我们直接来看 B+ 树的结构。

在上图中,最下层被称为叶子节点,顺序保存了完整的数据记录,各叶子节点通过双向链表连接,上层节点储存着子节点的 key。B+ 树的高度通常为 2~3 层,因此一次查询一般只需要 2~3 次搜索,而由于叶子节点间的连接是通过双向链表,因此顺序遍历所有数据的效率大大提高。

InnoDB 的索引分为聚集索引(Clustered Index)和辅助索引(Secondary Indexes)。通常 InnoDB 表中的主键(primary key)即为聚集索引,而除了聚集所以之外所创建的其他索引均称作辅助索引。辅助索引所包含记录中必然会包含主键所在列,以这种方式将辅助索引与聚集索引关联起来。

接下来我们使用滴滴云的云主机与 MySQL 来验证索引对于搜索效率的提升。

3. 创建 MySQL 数据库

在滴滴云官网控制台创建 MySQL(主要步骤):

本文选择的数据库规格为 2G 内存,20G 存储容量:

创建完成后可以看到数据库的链接信息:

后续详细使用步骤可点击以下链接参考滴滴云官网教程:https://help.didiyun.com/hc/kb/article/1143410/

通过滴滴云虚拟机 DC2 连接 MySQL,DC2 的创建可以参考以下连接:https://help.didiyun.com/hc/kb/article/1145869

本文操作系统以 Centos7.3 为例。

进入 DC2,安装 MariaDB 并启动:

连接 MySQL:

创建 test 数据库并创建 test_user 表:

创建一个存储过程,插入1000万条数据:

调用存储过程:

由于数据量较大,时间较长,完成后查看数据:

出现以上结果,证明 1000 万条数据已插入成功。

4. 索引查询

接下来使用 primary key 也就是 ID 来查询符合条件的某一条记录,前面我们提到过表中的 primary key 就是 InnoDB 默认的聚集索引。

我们可以看到 rows 字段显示 1,说明只读取了 1 行记录,而真正获取结果只需 0.02 秒。

接下来我们尝试用一个非索引字段 username 查询一条记录:

我们可以看到执行这条数据需要检查 9722473 行,而得到结果需要 5.5 秒。

现在我们将 username 字段作为辅助索引,看是否能够提高查询效率:

我们可以看到在创建了 username 索引后,以 username 作为查询条件查询值为 7777777 的用户记录只需读取一条记录,返回结果只需不足 0.001 秒,大幅提升了查询效率。

本文作者:贺子一

您的留言将激励我们越做越好