关于数据库设计与优化的前几篇文章中我们提到了数据库设计优化应该遵守的指导原则、数据库底层的索引组织结构、数据库的核心功能组件以及SQL的解析、编译等。

这些其实都是在为SQL的优化、执行的理解打基础。

今天这篇文章我们以MySQL中InnoDB存储引擎中的数据索引组织及一条SQL的物理执行过程来更直观的理解数据库中我们提交一条SQL后数据库默默帮我们做的事情。

准备工作

我们依然以前一篇文章中的t_customer表为例建表语句如下

create

会员信息表;然后我们编写一个Python脚本利用Faker框架来生成测试数据

import

pymysql.connect(hostdb_cfg.host,

portdb_cfg.port,

passworddb_cfg.password,databasedb_cfg.database)

cursor

GenderProvider(BaseProvider):def

gender(self):return

fk.add_provider(GenderProvider)

for

range(10000):cursor.execute(sql.format(fk.name(),

fk.gender(),

其实这里我们只是从数据组织结构上展开SQL的执行没有测试数据也没啥影响。

不过还是强烈建议感兴趣的了解下Python很好用很好玩。

这里不再展开需要理解的可以看下笔者关于Python的相关系列文章。

简单说下B树索引

B树索引就是传统意义上的索引也是目前关系型数据库系统中查找最为常用和最有效的索引。

需要注意的是从使用的角度来看B树索引的构造类似于二叉树根据键值(key

B树中的B不是表示二叉(binary)而是代表平衡(balance)因为B树是从最早的平衡二叉树演化而来的但是B树不是一个二叉树树结构的索引只有是平衡树才能降低树的高度从而降低基于索引检索的磁盘IO的次数B树索引实际上并不能通过一个给定的键值查到具体的某一行数据而是只能找到被查找符合键值的数据所在的页这些数据按照键值顺序进行组织存储。

然后数据库通过把页读入内存然后在内存中执行进一步的查找操作最终得到要查找的数据。

后续我们简化一下操作假设每个页都只存储一条数据以便更好地进行表述、理解关于数据以页为单位进行读取前面的文章中已经提到可以更好地利用程序的局部性原理从而提高检索的效率

引言中已经提到我们这里以MySQL的InnoDB存储引擎为例进行介绍其他数据库中的底层原理也基本类似。

主键索引

1假设数据库的优化器最终决定要走idx_city这个索引进行SQL的执行主要的执行过程大概如下

name,

name的值放入内存缓冲区重复前两个步骤直到在idx_city索引中找到的city值不满足查询条件为止将内存缓冲区的数据返回给用户

上面的这条SQL首先从idx_city索引中找到主键id然后再到聚簇索引中找到整行记录然后还要判断是否符合条件再决定是否返回改行数据。

这种查询场景叫做”回表“。

回表的操作会增加磁盘IO的次数如果辅助索引结构中已经包含了用户需要的所有字段则可以避免回表的操作这时候的索引叫做”覆盖索引“。

下面我们对这条SQL稍微修改一下

现在这条要执行的SQL中添加了排序及limit操作执行的过程会发生相应的调整假设优化器还是选择了要走idx_city这个索引

name,

name的值放入排序缓冲区sor_buffer中重复前两个步骤直到在idx_city索引中找到的city值不满足查询条件为止对sort_buffer中的数据按照字段name进行快速排序按照排序结果的数据取出前100条返回给用户

其实涉及到排序的话问题会突然变得复杂起来这里简单描述下可能的情况

1、符合条件的行数很多sort_buffer中放不下这时候就不能直接基于内存的排序算法进行了就需要我们前面文章提到的TPMMS的算法了进行基于磁盘的多路归并排序

2、加入最终返回的字段比较多执行引擎在执行的过程中可能决定不将所有字段都放入sort_buffer可能只放主键id和参与排序的字段然后排序完成之后需要再按序进行一次回表的操作获取用户需要的所有字段然后再返回给用户。

基于是否将所有字段放入sort_buffer中排序的操作符可以简单分为全字段排序和rowid排序。

实际上SQL的执行要考虑的真实场景比较复杂本文为了便于描述与理解做了相应的简化感兴趣的可以自行研究。