MySQL性能调优架构设计

1 引言

查询语句：

select * from user_info where id = 1;

返回结果为：o

xxxxxxxxxx
+----+----------+----------+--------+------+---------------------+---------------------+
| id | username | password | openid | role | create_time         | update_time         |
+----+----------+----------+--------+------+---------------------+---------------------+
| 1  | 子慕     | 123      | 1      |    1 | 2022-01-01 00:29:08 | 2022-01-01 00:29:08 |
+----+----------+----------+--------+------+---------------------+---------------------+

问题：

• 思考：一条SQL查询语句是如何执行的？

2 MySQL架构设计

2.1 Server层

MySQL 架构可以分为 Server层 和 Engine层两部分：

MySQL 的逻辑架构图

2.1.1 连接器（Connector）

Mysql作为服务器，一个客户端的Sql连接过来就需要分配一个线程进行处理，这个线程会专门负责监听请求并读取数据。这部分的线程和连接管理都是有一个连接器，专门负责跟客户端建立连接、权限认证、维持和管理连接。

思考：（1）一个客户端只会和MySQL服务器建立一个连接吗？

（2）只能有一个客户端和MySQL服务器建立连接吗？

答：

多个系统都可以和MySQL服务器建立连接，每个系统建立的连接肯定不止一个。

所以，为了解决TCP无限创建与TCP频繁创建销毁带来的资源耗尽、性能下降问题。

MySQL服务器里有专门的TCP连接池限制接数，采用长连接模式复用TCP连接，来解决上述问题。

TCP连接收到请求后，必须要分配给一个线程去执行，所以还会有个线程池，去走后面的流程。

连接器负责跟客户端建立连接、获取权限、维持和管理连接。

连接命令一般是这么写的：

xxxxxxxxxx
mysql -h$ip -P$port -u$user -p

在完成 经典TCP 握手后，连接器会基于用户名和密码来验证身份。

• 验证不通过："Access denied for user"错误
• 验证通过：连接器会到权限表里面查出拥有的权限，之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限

xxxxxxxxxx
show processlist  -- 查看连接状态

图中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。

2.1.2 查询缓存 （Query Cache）

经过了连接管理，现在MySQL服务器已经获取到SQL字符串。

执行逻辑就会来到第二步：查询缓存

查询语句，MySQL服务器会使用select SQL字符串作为key，去缓存中获取：

• 缓存命中，直接返回结果
• 缓存未命中：执行后面的阶段，执行完成后，执行结果会被存入查询缓存中

缓存中数据：key：(查询的语句） value：(查询的结果)

注意：但是大多数情况下建议不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利

• 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空
• 5.x版本可以按需使用”的方式。可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定例：

xxxxxxxxxx
mysql> select SQL_CACHE * from T where ID=10；

• MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了

2.1.3 分析器（Analyzer）

缓存如果未命中，就要开始真正执行语句了

首先，MySQL 需要知道要做什么，因此需要对 SQL 语句做解析

• 词法分析

首先，会进行词法分析。 将一个完整的SQL语句，拆分成语句类型（select? insert? update? …）、表名、列名等等。

• 语法分析

其次，会进行语法分析。 根据语法规则，判断输入的这个 SQL 语句是否满足 MySQL 语法。 如果错误，会报出下面的错误：

x

mysql> elect * from t where ID=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

这时，我们只要修正 use near 后面的语句即可。

2.1.4 优化器（optimizer）

通过了分析器，说明SQL字符串符合语法规范，现在MySQL服务器要执行SQL语句了。

MySQL服务器要怎么执行呢？

那么就需要产出执行计划，交给MySQL服务器执行，所以来到了优化器阶段。

优化器不仅仅只是生成执行计划这么简单，这个过程它会帮你优化SQL语句。

如外连接转换为内连接、表达式简化、子查询转为连接、连接顺序、索引选择等一堆东西，优化的结果就是执行计划。

例：执行下面这样的语句，这个语句是执行两个表的 join：

xxxxxxxxxx
mysql> 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。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

截止到现在，还没有真正去读写真实的表，仅仅只是产出了一个执行计划。

2.1.5 执行器（Actuator）

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示

xxxxxxxxxx
mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'zimu'@'localhost' for table 'T'

如果有权限，就会根据表的 Engine 选择来调用对应的引擎接口。

例：

user_info 表的存储引擎是 InnoDB。

xxxxxxxxxx
select * from user_info where name = "zimu";

• 如果 name 列没有声明任何索引，执行步骤如下：

1. 调用 innoDB 引擎接口获取表的第一行，判断 name 是否等于 zimu。如果不是，跳过。如果是，将结果保存。
2. 调用 innoDB 引擎接口获取表的下一行，重复相同逻辑，一直到表的最后一行。

1. 将所有满足条件的结果集返回给客户端。

• 如果 name 列有索引，执行步骤如下：

2. 调用 innoDB 引擎接口获取索引树（B+树），基于索引树快速查到 name 等于 zimu 的所有主键id。

1. 将所有满足条件的组件 id，回主表查详细信息。（这个操作称为“回表”）
2. 将所有满足条件的结果集返回给客户端。

2.2 Engine层

2.2.1 什么是存储引擎?

引擎（Engine），我们都知道是机器发动机的核心所在，数据库存储引擎便是数据库的底层软件组织。

数据库使用数据存储引擎实现存储、处理和保护数据的核心服务

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySql的核心就是插件式存储引擎。

2.2.2 mysql支持哪些存储引擎？

我们可以使用MySQL命令行查看：

xxxxxxxxxx
SHOW ENGINES ;

可以发现，MySQL目前支持多种数据库存储引擎，默认引擎为InnoDB，且是唯一支持事务的存储引擎。

2.2.3 常见的存储引擎对比

INnoDB引擎

概述：InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。

主要特性：

为MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在 SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合

InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上

• InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键

使用 InnoDB存储引擎 MySQL将在数据目录下创建一个名为 ibdata1的10MB大小的自动扩展数据文件，以及两个名为 ib_logfile0和 ib_logfile1的5MB大小的日志文件。

MyISAM存储引擎

概述：MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。

主要特性：

• 被大文件系统和操作系统支持

• 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，若下一个块被删除，就扩展到下一块自动完成
• 每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
• 最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
• BLOB和TEXT列可以被索引
• NULL被允许在索引的列中，这个值占每个键的0~1个字节
• 所有数字键值以高字节优先被存储以允许一个更高的索引压缩
• 每个MyISAM类型的表都有一个AUTOINCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTOINCREMENT列将被刷新。所以说，MyISAM类型表的AUTOINCREMENT列更新比InnoDB类型的AUTOINCREMENT更快
• 可以把数据文件和索引文件放在不同目录
• 每个字符列可以有不同的字符集

• 有VARCHAR的表可以固定或动态记录长度
• VARCHAR和CHAR列可以多达64KB

使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）。

MEMORY存储引擎

概述：MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

主要特性：

• MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度

• MEMORY存储引擎执行HASH和BTREE缩影
• 可以在一个MEMORY表中有非唯一键值
• MEMORY表使用一个固定的记录长度格式
• MEMORY不支持BLOB或TEXT列

• MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
• MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
• MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享

• 当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行 DELETE FROM或 TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

2.2.4 存储引擎的选择

不同的数据处理选择适合的存储引擎是使用MySQL的一大优势。

• InnoDB： **支持事务处理，支持外键，支持崩溃修复能力和并发控制。**如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。
• MyISAM： **插入数据快，空间和内存使用比较低。**如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。
• MEMORY： **所有的数据都在内存中，数据的处理速度快，但是安全性不高。**如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

注意：同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。