中国联通网站建设与维护,百度上做网站,网站优化体验报告,建设银行河北省分行官方网站引言
关于数据库设计与优化的前几篇文章中#xff0c;我们提到了数据库设计优化应该遵守的指导原则、数据库底层的索引组织结构、数据库的核心功能组件以及SQL的解析、编译等。这些其实都是在为SQL的优化、执行的理解打基础。 今天这篇文章#xff0c;我们以MySQL中InnoDB存…
引言
关于数据库设计与优化的前几篇文章中我们提到了数据库设计优化应该遵守的指导原则、数据库底层的索引组织结构、数据库的核心功能组件以及SQL的解析、编译等。这些其实都是在为SQL的优化、执行的理解打基础。 今天这篇文章我们以MySQL中InnoDB存储引擎中的数据索引组织及一条SQL的物理执行过程来更直观的理解数据库中我们提交一条SQL后数据库默默帮我们做的事情。
准备工作
我们依然以前一篇文章中的t_customer表为例建表语句如下
create table t_customer(id int not null auto_increment comment 会员id,name varchar(32) comment 会员姓名,gender tinyint not null default 0 comment 会员性别0未知1男2女,city varchar(32) comment 会员所在城市,primary key(id),key idx_city (city)
) comment 会员信息表;然后我们编写一个Python脚本利用Faker框架来生成测试数据
import random
from faker import Faker
from faker.providers import BaseProvider
import pymysql
import db_config as db_cfgprint(db_cfg.host)conn pymysql.connect(hostdb_cfg.host, portdb_cfg.port, userdb_cfg.user, passworddb_cfg.password,databasedb_cfg.database)
cursor conn.cursor()
sql insert into t_customer(name, gender, city) values({}, {}, {})class GenderProvider(BaseProvider):def gender(self):return random.sample([1, 2, 0], counts[100, 100, 1], k1)[0]# 指定语言环境为中文环境创建Faker生成器
fk Faker(zh_CN)
fk.add_provider(GenderProvider)
for i in range(10000):cursor.execute(sql.format(fk.name(), fk.gender(), fk.city()))
conn.commit()
cursor.close()
conn.close()
测试数据大概如下 其实这里我们只是从数据组织结构上展开SQL的执行没有测试数据也没啥影响。不过还是强烈建议感兴趣的了解下Python很好用很好玩。这里不再展开需要理解的可以看下笔者关于Python的相关系列文章。
B树的索引组织结构
简单说下B树索引
B树索引就是传统意义上的索引也是目前关系型数据库系统中查找最为常用和最有效的索引。 需要注意的是从使用的角度来看B树索引的构造类似于二叉树根据键值(key value)能够快速找到相应的数据。但是有几个细节需要提一下
B树中的B不是表示二叉(binary)而是代表平衡(balance)因为B树是从最早的平衡二叉树演化而来的但是B树不是一个二叉树树结构的索引只有是平衡树才能降低树的高度从而降低基于索引检索的磁盘IO的次数B树索引实际上并不能通过一个给定的键值查到具体的某一行数据而是只能找到被查找符合键值的数据所在的页这些数据按照键值顺序进行组织存储。然后数据库通过把页读入内存然后在内存中执行进一步的查找操作最终得到要查找的数据。后续我们简化一下操作假设每个页都只存储一条数据以便更好地进行表述、理解关于数据以页为单位进行读取前面的文章中已经提到可以更好地利用程序的局部性原理从而提高检索的效率
t_customer的索引结构
引言中已经提到我们这里以MySQL的InnoDB存储引擎为例进行介绍其他数据库中的底层原理也基本类似。 从前面的建表语句中可以看出t_customer有两个索引
主键索引 id是聚簇索引(Clustered Index)idx_city是辅助索引(Secondary Index)
索引的示意图大概如下 前面已经提到我们简化一下一个页只存储一条数据。 辅助索引的叶子结点存储的都是该索引的键值及对应的主键的值 聚簇索引的叶子节点存储的都是一行行完整的数据。
SQL执行过程
接下来我们将要执行的是这样一条SQL语句
select id,name
from t_customer
where city 合肥 and gender 1假设数据库的优化器最终决定要走idx_city这个索引进行SQL的执行主要的执行过程大概如下
从索引idx_city中找到第一个满足city 合肥的主键id到主键id索引中取出整行将id, name, gender取出如果gender 1 则将id, name的值放入内存缓冲区重复前两个步骤直到在idx_city索引中找到的city值不满足查询条件为止将内存缓冲区的数据返回给用户
上面的这条SQL首先从idx_city索引中找到主键id然后再到聚簇索引中找到整行记录然后还要判断是否符合条件再决定是否返回改行数据。这种查询场景叫做”回表“。 回表的操作会增加磁盘IO的次数如果辅助索引结构中已经包含了用户需要的所有字段则可以避免回表的操作这时候的索引叫做”覆盖索引“。
下面我们对这条SQL稍微修改一下
select id,name
from t_customer
where city 合肥 and gender 1
order byname
limit 100
现在这条要执行的SQL中添加了排序及limit操作执行的过程会发生相应的调整假设优化器还是选择了要走idx_city这个索引
从索引idx_city中找到第一个满足city 合肥的主键id到主键id索引中取出整行将id, name, gender取出如果gender 1 则将id, name的值放入排序缓冲区sor_buffer中重复前两个步骤直到在idx_city索引中找到的city值不满足查询条件为止对sort_buffer中的数据按照字段name进行快速排序按照排序结果的数据取出前100条返回给用户
其实涉及到排序的话问题会突然变得复杂起来这里简单描述下可能的情况 1、符合条件的行数很多sort_buffer中放不下这时候就不能直接基于内存的排序算法进行了就需要我们前面文章提到的TPMMS的算法了进行基于磁盘的多路归并排序 2、加入最终返回的字段比较多执行引擎在执行的过程中可能决定不将所有字段都放入sort_buffer可能只放主键id和参与排序的字段然后排序完成之后需要再按序进行一次回表的操作获取用户需要的所有字段然后再返回给用户。基于是否将所有字段放入sort_buffer中排序的操作符可以简单分为全字段排序和rowid排序。
实际上SQL的执行要考虑的真实场景比较复杂本文为了便于描述与理解做了相应的简化感兴趣的可以自行研究。
