MySQL优化原理 (一)


摘要:提到MySQL的性能优化,我想你一定知道:合理创建索引、不能使用SELECT *、不使用NULL字段、为字段选择合适的数据类型…..但是你是否真的理解这些优化技巧?是否理解其背后的工作原理?在实际场景下性能真有提升吗?我想未必。正所谓,知其然,知其所以然。学习本身就是一个不断专研和探究的过程,本篇文章主要是我个人对MySQL逻辑架构、MySQL架构总览、MySQL查询过程的基本原理上的学习和总结,因为我深知只有真正明白原理,才能在实际的应用场景下得心应手。在之后也还会有更多系列的文章去进行不断的完善和补充。

MySQL逻辑架构

首先,如果你能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图,我想这将有助于你深入理解MySQL服务器。

下图展示了MySQL的逻辑架构图。你必须深深的刻在你的脑海中。

MySQL逻辑架构整体分为三层:

最上层为客户端层

该客户端层并非MySQL所独有,诸如:连接处理、授权认证、安全等功能均在这一层处理。

中间层为核心服务层

服务层包括查询解析、分析、优化、缓存、内置函数(比如:时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现:存储过程、触发器、视图等。

最下层为存储引擎

其主要负责MySQL中的数据存储和提取。和Linux下的文件系统类似,每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信,这些API接口屏蔽了不同存储引擎间的差异。

MySQL架构总览

下图展示了MySQL相关的模块和组件。

各模块和组件之间负责的主要功能:

连接管理器:

负责监听在某个端口套接字上,并接受用户请求。

线程管理器:

负责给用户的请求生成新的线程,在请求完成后负责销毁进程或完成进程的重用。

用户模块:

验证用户是否有权限连接服务器,验证用户的身份。

命令分发模块:

根据查询的不同类型,和缓存模块交互,和日志模块交互。若缓存中没有则交给解析器处理,需要记录日志的交给日志模块。

解析器:

解析查询的法和词法,再次查询缓存,没有则生成解析树。若是SELECT则交给查询优化器。若是UPDATE、DELETE、INSERT、CREATE等则交给表定义模块,REPARE交给表维护模块。

缓存管理器:

负责管理缓存。

日志管理器:

记录日志。

查询优化器:

对解析树进行优化,优化为在服务器上执行开销最小,速度最高的执行模式。

表定义模块(表修改模块):

检查用户跟表操作相关数据的完整性和权限。负责表的创建、删除、重命名、移除、更新、插入等操作。

表维护模块:

用来修复表的逻辑错误等。检查用户权限。负责表的检查、修改、备份、恢复、优化(碎片整理)、解析。

状态报告模块:

更新操作执行状态报告信息。

复制模块:

负责数据同步等。

访问控制模块:

检查用户是否有完成对应操作的权限。

表管理器:

负责和存储引擎接口交互,根据表定义模块的操作,将表定义转换为文件。负责创建、修改读取表定义文件,表定义文件用来储存表结构。。

管理表锁:

一个用户修改表内容时其他用户不能操作。和表定义模块和表维护模块协同操作。

存储引擎:

插件式的,负责表示层和物理层的双向转换和磁盘上的数据进行交互。

下面从连接、处理和结果三个方面介绍一下MySQL执行过程:

1)连接

1.1 客户端发起一条SQL请求,监听客户端的“连接管理模块”接收请求

1.2 将请求转发到“连接进/线程模块”

1.3 调用“用户模块”来进行授权检查

1.4 通过检查后,“连接进/线程模块”从线程连接池中取出空闲的被缓存的连接线程和客户端请求对接,如果失败则创建一个新的连接请求

2)处理

2.1 先查询缓存,检查SQL语句是否完全匹配,接着再检查是否具有权限,都成功则直接取数据返回

2.2 上一步有失败则转交给“命令解析器”,经过语法分析,语法分析后生成解析树

2.3 接下来是预处理阶段,处理解析器无法解决的语义,检查权限等,生成新的解析树

2.4 再转交给对应的模块处理

2.5 如果是SELECT查询还会经由“查询优化器”做大量的优化,生成执行计划

2.6 模块收到请求后,通过“访问控制模块”检查所连接的用户是否有访问目标表和目标字段的权限

2.7 有则调用“表管理模块”,先是查看table cache中是否存在,有则直接对应的表和获取锁,否则重新打开表文件

2.8 根据表的meta数据,获取表的存储引擎类型等信息,通过接口调用对应的存储引擎处理

2.9 上述过程中产生数据变化的时候,若打开日志功能,则会记录到相应二进制日志文件中

3)结果

3.1 SQL请求完成后,将结果集返回给“连接进/线程模块”

3.2 返回的也可以是相应的状态标识,如成功或失败等

3.3 “连接进/线程模块”进行后续的清理工作,并继续等待请求或断开与客户端的连接

MySQL查询过程

当向MySQL发送一个请求的时候,MySQL到底做了些什么呢?

MySQL整个查询执行过程,总的来说分为6个步骤:

  1. 客户端向MySQL服务器发送一条查询请求

  2. 服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段

  3. 服务器通过解析器进行SQL语法解析并生成相应的解析树

  4. 解析树经过预处理器生成新的解析树,再经过查询优化器生成对应的执行计划

  5. MySQL根据执行计划,调用存储引擎的API来执行查询

  6. 将结果返回给客户端,同时缓存查询结果

客户端/服务端通信协议

先简单介绍下通信传输方式和通信协议,相关通信协议原理的讲解我会在之后的博客中进行学习。

根据数据信息在传输线上的传送方向,数据通信方式分为单工通信、半双工通信和全双工通信3种。

1) 单工通信

数据信息在通信线上始终向一个方向传输,数据信息永远从发送端传输到接收端。

例如:广播、电视就是单工传输方式。收音机、电视机只能分别接受来自电台、电视台的信号,不能进行相反方向的信息传输。

2) 半双工通信

数据信息可以双向传输,但必须交替进行,同一时刻一个信道只允许单向传送。半双工通信要求A、B端都有发送装置和接收装置,若想改变信息的传输方向,有开关K1、K2进行切换,再任意时刻保证A端发送装置与B端接收装置A端接收装置与B端发送装置介入信道。

例如:对讲机通信就是典型的半双工通信方式。在一方讲话的时候另一方不能讲话,但通过开切换可以切换可以改变通话方式。

3) 全双工通信

全双工数据通信允许数据同时在两个方向上传输,因此,全双工通信是两个单工通信方式的结合,它要求发送设备和接收设备都有独立的接收和发送能力。

例如:普通电话就是一种典型的全双工通信。全双工的通信效率较高,比较容易控制。

TCP/IP是网络中使用的基本的通信协议。

虽然从名字上看TCP/IP包括两个协议,传输控制协议(TCP)和网际协议(IP),但TCP/IP实际上是一组协议,它包括上百个各种功能的协议,

如:远程登录、文件传输和电子邮件等,而TCP协议和IP协议是保证数据完整传输的两个基本的重要协议。

通常说TCP/IP是Internet协议族,而不单单是TCP和IP。

MySQL客户端/服务端通信协议是“半双工”的:

在任一时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据,这两个动作不能同时发生。

一旦一端开始发送消息,另一端要接收完整个消息才能响应它,所以我们无法也无须将一个消息切成小块独立发送,也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器,所以当查询语句很长的时候,需要设置max_allowed_packet参数。

但是需要注意的是,如果查询实在是太大,服务端会拒绝接收更多数据并抛出异常。

与之相反的是,服务器响应给用户的数据通常会很多,由多个数据包组成。

但是当服务器响应客户端请求时,客户端必须完整的接收整个返回结果,而不能简单的只取前面几条结果,然后让服务器停止发送。

因而在实际开发中,尽量保持查询简单且只返回必需的数据,减小通信间数据包的大小和数量是一个非常好的习惯。

这也是查询中尽量避免使用SELECT * 以及加上LIMIT限制的原因之一。

查询缓存

在解析一个查询语句前,如果查询缓存是打开的,那么MySQL会检查这个查询语句是否命中查询缓存中的数据。

如果当前查询恰好命中查询缓存,在检查一次用户权限后直接返回缓存中的结果。

这种情况下,查询不会被解析,也不会生成执行计划,更不会执行。

MySQL将缓存存放在一个引用表,不要理解成table,可以认为是类似于HashMap的数据结构。

通过一个哈希值索引,这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。

所以两个查询在任何字符上的不同(例如:空格、注释),都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表,其查询结果都不会被缓存。

比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间,返回不同的查询结果。

再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果,将这样的查询结果缓存起来没有任何的意义。

既然是缓存,就会失效,那查询缓存何时失效呢?

MySQL的查询缓存系统会跟踪查询中涉及的每个表,如果这些表(数据或结构)发生变化,那么和这张表相关的所有缓存数据都将失效。

正因为如此,在任何的写操作时,MySQL必须将对应表的所有缓存都设置为失效。

如果查询缓存非常大或者碎片很多,这个操作就可能带来很大的系统消耗,甚至导致系统僵死一会儿。

而且查询缓存对系统的额外消耗也不仅仅在写操作,读操作也不例外:

1) 任何的查询语句在开始之前都必须经过检查,即使这条SQL语句永远不会命中缓存

2) 如果查询结果可以被缓存,那么执行完成后,会将结果存入缓存,也会带来额外的系统消耗

基于此,我们要知道并不是什么情况下查询缓存都会提高系统性能。

缓存和失效都会带来额外消耗,只有当缓存带来的资源节约大于其本身消耗的资源时,才会给系统带来性能提升。

但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情,具体还得看实际的业务场景。

如果系统确实存在一些性能问题,可以尝试打开查询缓存,并在数据库设计上做一些优化,比如:

1)用多个小表代替一个大表,注意不要过度设计

2)批量插入代替循环单条插入

3)合理控制缓存空间大小,一般来说其大小设置为几十兆比较合适

4)可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

语法解析

MySQL通过关键字将SQL语句进行解析,并生成一颗对应的解析树。

这个过程解析器主要通过语法规则来验证和解析。

比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。

预处理

预处理会根据MySQL规则进一步检查解析树是否合法。

比如检查要查询的数据表和数据列是否存在,处理解析器无法解决的语义,检查权限等,然后再生成新的解析树

查询优化器

经过前面的步骤生成的语法树被认为是合法的了,并且由优化器将其转化成查询计划。

多数情况下,一条查询可以有很多种执行方式,最后都返回相应的结果。

优化器的作用就是找到这其中最好的执行计划。

什么叫执行计划?

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的。

比如一条SQL语句如果用来从一个10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,

如果该表进行了归档,当前只剩下5000条记录了,那查询优化器就会改变方案,采用 “全表扫描”方式。

查询执行引擎

在完成解析和优化阶段以后,MySQL会生成对应的执行计划,查询执行引擎根据执行计划给出的指令逐步执行得出结果。

整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成,这些接口被称为handler API。

查询过程中的每一张表由一个handler实例表示。

实际上,MySQL在查询优化阶段就为每一张表创建了一个handler实例,优化器可以根据这些实例的接口来获取表的相关信息,包括表的所有列名、索引统计信息等。

存储引擎接口提供了非常丰富的功能,但其底层仅有几十个接口,这些接口像搭积木一样完成了一次查询的大部分操作。

返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。

即使查询不到数据,MySQL仍然会返回这个查询的相关信息,比如改查询影响到的行数以及执行时间等等。

如果查询缓存被打开且这个查询可以被缓存,MySQL也会将结果存放到缓存中。

结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时,就开始向客户端逐步返回结果集了。

这样服务端就无须存储太多结果而消耗过多内存,也可以让客户端第一时间获得返回结果。

需要注意的是,结果集中的每一行都会以一个满足通信协议的数据包发送,再通过TCP协议进行传输,在传输过程中,可能对MySQL的数据包进行缓存然后批量发送。

参考文章

深入理解mysql的查询缓存

MySQL的查询缓存机制基本学习教程

MySQL步步深入