MySQL性能调优架构设计

末蓝、 2024-03-17 15:39 57阅读 0赞

#### 目录 ####

*   *  1 引言
     *  2 MySQL架构设计
     *   *  2.1 Server层
         *   *  2.1.1 连接器（Connector）
             *  2.1.2 查询缓存 （Query Cache）
             *  2.1.3 分析器（Analyzer）
             *  2.1.4 优化器（optimizer）
             *  2.1.5 执行器（Actuator）
         *  2.2 Engine层
         *   *  2.2.1 什么是存储引擎?
             *  2.2.2 mysql支持哪些存储引擎？
             *  2.2.3 常见的存储引擎对比
             *  2.2.4 存储引擎的选择

--------------------

### 1 引言 ###

![在这里插入图片描述][84d04aa93c824972bdeb9b688b865a7f.png]

查询语句：

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层**两部分：

![在这里插入图片描述][5dd5bc37a92848b9b18aa51786cb7a24.png]  
MySQL 的逻辑架构图

##### 2.1.1 连接器（Connector） #####

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

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

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

![在这里插入图片描述][6c3e9e1d9bac4b99bb0f675d9e30b114.png]

答：

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

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

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

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

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

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

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

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

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

xxxxxxxxxx
    show processlist  -- 查看连接状态

![在这里插入图片描述][e34c9f67e8794611bd61afd865e8fc4c.png]

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

##### 2.1.2 查询缓存 （Query Cache） #####

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

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

![在这里插入图片描述][869de926ce3f4040b46c9da95dd5d61d.png]

查询语句，`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 语句做解析

![在这里插入图片描述][205f1ac578f84166bc13a60ce499f38f.png]

*  **词法分析**

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

*  **语法分析**

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

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`服务器执行，所以来到了**优化器**阶段。

![在这里插入图片描述][b0b4508ae43343209fc33d6088820215.png]

优化器不仅仅只是生成执行计划这么简单，这个过程它会帮你优化`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 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

![在这里插入图片描述][f120838782dc4be3957e0fee72ee39e0.png]

开始执行的时候，要先判断一下你对这个表 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 引擎接口获取表的下一行，重复相同逻辑，一直到表的最后一行。
3.  将所有满足条件的结果集返回给客户端。

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

1.  调用 innoDB 引擎接口获取索引树（B+树），基于索引树快速查到 `name` 等于 `zimu` 的所有主键id。
2.  将所有满足条件的组件 id，回主表查详细信息。（这个操作称为“回表”）
3.  将所有满足条件的结果集返回给客户端。

#### 2.2 Engine层 ####

##### 2.2.1 什么是存储引擎? #####

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

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

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

##### 2.2.2 mysql支持哪些存储引擎？ #####

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

xxxxxxxxxx
    SHOW ENGINES ;

![在这里插入图片描述][f2394192e9d44554a064423264cc9924.png]

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

##### 2.2.3 常见的存储引擎对比 #####

![在这里插入图片描述][e0cd4ef4164146c099a4f9e09fd11260.png]

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的一大优势。

![在这里插入图片描述][463bb10b88874504bc9fabf2d59b4bf0.png]

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

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

[84d04aa93c824972bdeb9b688b865a7f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/cf5acbe88dca455486e294963d02cc3f.png
[5dd5bc37a92848b9b18aa51786cb7a24.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/15f71854b9624777aae7de08dd699003.png
[6c3e9e1d9bac4b99bb0f675d9e30b114.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/db96e2f4c3484bb6b2581da1f5d27c38.png
[e34c9f67e8794611bd61afd865e8fc4c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/f00f66b95ad44793b817778343e3d207.png
[869de926ce3f4040b46c9da95dd5d61d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/bfe2ce6e53574bc3b2acbf232aa09e98.png
[205f1ac578f84166bc13a60ce499f38f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/f19054116b8048eb9cb94866758f0bb4.png
[b0b4508ae43343209fc33d6088820215.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/5703f3d8462445fc94026eeadaaf459d.png
[f120838782dc4be3957e0fee72ee39e0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/31a621a6df6a47bd8741403216286de5.png
[f2394192e9d44554a064423264cc9924.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/47351379c5f947979159bbdbf8ae7417.png
[e0cd4ef4164146c099a4f9e09fd11260.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/befc9a7743e8444591dca0fb1f07fada.png
[463bb10b88874504bc9fabf2d59b4bf0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/cf2816f88519494bb17d78134733e1a9.png