注:本人并非原创者,只是在自己学习的时候参考别人进行了整理,分享给大家,共同学习,共同进步。
前言
平时我们使用数据库,看到的通常都是一个整体。比如,你有个最简单的表,在执行下面这个查询语句时,我们看到的只是输入一条语句,返回一个结果,却不知道这条语句在 MySQL 内部的执行过程。所以今天把 MySQL 拆解一下,看看里面都有哪些“零件”,希望借由这个拆解过程,自己对 MySQL 有更深入的理解。这样当我们碰到 MySQL 的一些异常或者问题时,就能够直戳本质,更为快速地定位并解决问题。
大体来说,MySQL 可以分为 连接器、Server 层、存储引擎层三部分。Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。而存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成为了默认存储引擎。
下面我给出的是 MySQL 的基本架构示意图,从中你可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程。
也就是说,你执行 create table 建表的时候,如果不指定引擎类型,默认使用的就是 InnoDB。不过,你也可以通过指定存储引擎的类型来选择别的引擎,比如在 create table 语句中使用 engine=memory, 来指定使用内存引擎创建表。不同存储引擎的表数据存取方式不同,支持的功能也不同,在后面的文章中,我们会讨论到引擎的选择。从图中不难看出,不同的存储引擎共用一个 Server 层,也就是从连接器到执行器的部分。你可以先对每个组件的名字有个印象,接下来我会结合开头提到的那条 SQL 语句,带你走一遍整个执行流程,依次看下每个组件的作用。
连接器(处理连接)
客户端向服务器发送请求并最终收到响应,本质上是一个进程间通信的过程。
MySQL有专门用于处理连接的模块——连接器。
连接命令一般是这么写的:
mysql -h$ip -P$port -u$user -p
输完命令之后,你就需要在交互对话里面输入密码。虽然密码也可以直接跟在 -p 后面写在命令行中,但这样可能会导致你的密码泄露。如果你连的是生产服务器,强烈建议你不要这么做。连接命令中的 mysql 是客户端工具,用来跟服务端建立连接。
客户端和服务端的通信方式
TCP/IP协议
- TCP/IP协议是MySQL客户端和服务器最常用的通信方式。
- 我们平时所说的MySQL服务器默认监听的端口是3306,这句话的前提是客户端进程和服务器进程使用的是TCP/IP协议进行通信。
- 我们在使用mysql命令启动客户端程序时,只要在-h参数后跟随IP地址作为服务器进程所在的主机地址,那么通讯方式便是TCP/IP协议。
- 如果客户端进程和服务器进程位于同一台主机,且要使用TCP/IP协议进行通信,则IP地址需要指定为127.0.0.1,而不能使用localhost
权限验证
确认通信方式并且成功建立连接之后,连接器就要开始验证你的身份了,使用的信息就是你的用户名和密码。
- 如果用户名或密码不对,你就会收到一个"Access denied for user"的错误,然后客户端程序结束执行。
- 如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限。之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。
这就意味着,一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改,也不会影响已经存在连接的权限。修改完成后,只有再新建的连接才会使用新的权限设置。
连接完成后,如果你没有后续的动作,这个连接就处于空闲状态,你可以在 show processlist 命令中看到它。文本中这个图是 show processlist 的结果,其中的 Command 列显示为“Sleep”的这一行,就表示现在系统里面有一个空闲连接。
客户端如果太长时间没动静,连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的,默认值是 8 小时。
如果在连接被断开之后,客户端再次发送请求的话,就会收到一个错误提醒: Lost connection to MySQL server during query。这时候如果你要继续,就需要重连,然后再执行请求了。
数据库里面,长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。
建立连接的过程通常是比较复杂的,所以我建议你在使用中要尽量减少建立连接的动作,也就是尽量使用长连接。
但是全部使用长连接后,你可能会发现,有些时候 MySQL 占用内存涨得特别快,这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来,可能导致内存占用太大,被系统强行杀掉(OOM),从现象看就是 MySQL 异常重启了。
怎么解决这个问题呢?你可以考虑以下两种方案。
- 定期断开长连接。使用一段时间,或者程序里面判断执行过一个占用内存的大查询后,断开连接,之后要查询再重连。
- 如果你用的是 MySQL 5.7 或更新版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态。
解析与优化
服务器收到客户端传来的请求之后,还需要经过查询缓存、词法语法解析和预处理、查询优化的处理。
查询缓存
连接建立完成后,你就可以执行 select 语句了。执行逻辑就会来到第二步:查询缓存。
MySQL 拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式,被直接缓存在内存中。key 是查询的语句,value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key,那么这个 value 就会被直接返回给客户端。
如果语句不在查询缓存中,就会继续后面的执行阶段。执行完成后,执行结果会被存入查询缓存中。你可以看到,如果查询命中缓存,MySQL 不需要执行后面的复杂操作,就可以直接返回结果,这个效率会很高。
建议不要使用查询缓存,原因如下:
- MySQL自带的缓存系统应用场景非常有限,它要求SQL语句必须一模一样,多一个空格,变一个大小写都被认为是两条不同的SQL语句。
- 缓存失效非常频繁。只要一个表的数据有任何修改,针对该表的所有缓存都会失效。对于更新频繁的数据表而言,缓存命中率非常低。
所以缓存的功能还是交给专业的ORM框架(比如MyBatis默认开启一级缓存)或者独立的缓存服务Redis更加适合。
注:MySQL8.0已经彻底移除了缓存功能
解析器
如果没有命中查询缓存,就要开始真正执行语句了。首先,MySQL 需要知道你要做什么,因此需要对 SQL 语句做解析。
词法解析
以下面的SQL语句为例
SELECT id, username FROM `user` WHERE id = '8925ed73074b4a66a64d61c37c16411d'
分析器先会做“词法分析”,就是把一条完整的SQL语句打碎成一个个单词,比如一条简单的SQL语句,每个符号是什么类型,从哪里开始到哪里结束。
MySQL 从你输入的SELECT这个关键字识别出来,这是一个查询语句。它也要把字符串user识
别成“表名user”,把字符串id识别成“列 id”,把字符串username识别成“列 username”。
语法分析
做完词法解析,接下来需要做语法分析了。根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法,比如单引号是否闭合,关键词拼写是否正确等。解析器会根据SQL语句生成一个数据结构,这个数据结构成为解析树。
预处理器
词法解析和语法分析是无法知道数据库里有什么表,有哪些字段的。要知道这些信息还需要解析阶段的另一个工具——预处理器。
它会检查生成的解析树,解决解析器无法解析的语义。比如,它会检查表和列名是否存在,检查名字和别名,保证没有歧义。预处理之后得到一个新的解析树。
本质上,解析和预处理是一个编译过程,涉及到词法解析、语法和语义分析,更多细节我们不会探究,感兴趣的读者可以看一下编译原理方面的书籍。
查询优化器(Optimizer)与查询执行计划
这一步,MySQL终于知道我们想查询的表和列以及相应的搜索条件了,MySQL作者担心我们写的SQL太垃圾,所以有设计出一个叫做查询优化器的东东,辅助我们提高查询效率。优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。
比如你执行下面这样的语句,这个语句是执行两个表的 join:
select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
- 既可以先从表 t1 里面取出 c=10 的记录的 ID 值,再根据 ID 值关联到表 t2,再判断 t2 里面 d 的值是否等于 20。
- 也可以先从表 t2 里面取出 d=20 的记录的 ID 值,再根据 ID 值关联到 t1,再判断 t1 里面 c 的值是否等于 10。
这两种执行方法的逻辑结果是一样的,但是执行的效率会有不同,而优化器的作用就是决定选择使用哪一个方案。
优化器究竟做了什么?
举两个简单的例子∶
- 当我们对多张表进行关联查询的时候,以哪个表的数据作为基准表。
- 有多个索引可以使用的时候,选择哪个索引。
实际上,对于每一种数据库来说,优化器的模块都是必不可少的,他们通过复杂的算法实现尽可能优化查询效率。
往细节上说,查询优化器主要做了下面几方面的优化:
- 子查询优化
- 等价谓词重写
- 条件化简
- 外连接消除
- 嵌套连接消除
- 连接消除
- 语义优化
注:但是优化器也不是万能的,如果SQL语句写得实在太垃圾,再牛的优化器也救不了你了。因此大家在编写SQL语句的时候还是要有意识地进行优化。
执行器
MySQL 通过分析器知道了你要做什么,通过优化器知道了该怎么做,于是就进入了执行器阶段,开始执行语句。
开始执行的时候,要先判断一下你对这个表 T 有没有执行查询的权限,如果没有,就会返回没有权限的错误。
如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口。
存储引擎
什么是存储引擎
到底该把数据存储在什么位置,是内存还是磁盘?怎么从表里读取数据,以及怎么把数据写入具体的表中,这都是存储引擎负责的事情。
为什么需要存储引擎
因为存储的需求不同。试想一下:
- 如果一张表,需要很高的访问速度,而不需要考虑持久化的问题,是不是最好把数据放在内存呢?
- 如果一张表,是用来做历史数据存档的,不需要修改,也不需要索引,那是不是要支持数据的压缩?
- 如果一张表用在读写并发很多的业务中,是不是要支持读写互不干扰,而且要保证比较高的数据一致性呢?
为什么要支持这么多的存储引擎,因为一种存储引擎不能提供所有的特性。
存储引擎是计算机抽象的典型代表,它的功能就是接受上层指令,然后对表中数据进行读取和写入,而这些操作对上层完全是屏蔽的。你甚至可以查阅MySQL文档定义自己的存储引擎,只要对外实现同样的接口就可以了。存储引擎就是MySQL对数据进行读写的插件而已,可以根据不同目的随意更换(插拔)
存储引擎怎么用
创建表的时候指定存储引擎
在创建表的时候可以指定当前表的存储引擎,如果没有指定,默认的存储引擎为InnoDB,如果想显式指定存储引擎,可以这样
CREATE TABLE `user` (
`id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '用户ID',
`person_info_id` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '人员编号',
`nick_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '用户昵称',
`username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '用户名',
`password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '密码',
`avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '头像',
`is_system` tinyint(1) NULL DEFAULT NULL COMMENT '是否是系统内置(1是,0否)',
`status` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '帐号状态(枚举维护)',
`last_login_ip` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '上次登陆IP',
`last_login_time` datetime NULL DEFAULT NULL COMMENT '上次登陆时间',
`is_delete` tinyint(1) NOT NULL COMMENT '是否删除(0存在 1删除)',
`created_by` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建人',
`created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
`updated_by` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '更新人',
`updated_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '用户表' ROW_FORMAT = DYNAMIC;
not null :表示此列不能为空
primary key :表示主键(唯一且不为空)
engine =innodb :表示指定当前表的存储引擎
default charset utf8mb4 :设置表的默认编码集
修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称;
常见存储引擎比较
MylSAM
应用范围比较小,表级锁定限制了读/写的性能,因此在Web和数据仓库配置中,通常用于只读或以读为主的工作。
特点:
- 支持表级别的锁(插入和更新会锁表),不支持事务;
- 拥有较高的插入(insert)和查询(select)速度;
- 存储了表的行数(count速度更快)。
InnoDB
MySQL 5.7及更新版中的默认存储引擎。InnoDB是一个事务安全(与ACID兼容)的MySQL 存储引擎,它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁(不升级为更粗粒度的锁)和Oracle风格的一致非锁读提高了多用户并发性。InnoDB将用户数据存储在聚集索引中,以减少基于主键的常见查询的I/O。为了保持数据完整性,InnoDB还支持外键引用完整性约束。
特点:
- 支持事务,支持外键,因此数据的完整性、一致性更高;
- 支持行级别的锁和表级别的锁;
- 支持读写并发,写不阻塞读(MVCC);
- 特殊的索引存放方式,可以减少IO,提升査询效率;
番外:InnoDB本来是InnobaseOy公司开发的,它和MySQL AB公司合作开源了InnoDB的代码。
但是没想到MySQL的竞争对手Oracle把InnobaseOy收购了。后来08年Sun公司(开发Java语言的Sun)收购了MySQL AB,09年Sun公司又被Oracle收购了,所以MySQL和 InnoDB又是一家了。有人觉得MySQL越来越像Oracle,其实也是这个原因。
Memory
将所有数据存储在RAM中,以便快速访问。这个引擎以前被称为堆引擎。
特点:
- 把数据放在内存里面,读写的速度很快,但是数据库重启或者崩溃,数据会全部消失;
- 只适合做临时表。
CSV
它的表实际上是带有逗号分隔值的文本文件。csv表允许以CSV格式导入或转储数据, 以便与读写相同格式的脚本和应用程序交换数据。因为CSV表没有索引,所以通常在正常操作期间将数据保存在InnoDB表中,只在导入或导出阶段使用csv表。
特点:
- 不允许空行,不支持索引;
- 格式通用,可以直接编辑,适合在不同数据库之间导入导出。
Archive
专用与存档,空间经过压缩,用于存储和检索大量很少引用的信息。
特点:
- 不支持索引;
- 不支持update、delete;
如何选择存储引擎
- 如果对数据一致性要求比较高,需要事务支持,可以选择InnoDB。
- 如果数据查询多更新少,对查询性能要求比较高,可以选择MyISAM。
- 如果需要一个用于查询的临时表,可以选择Memory。