2021-07-15

**

高性能MySQL学习笔记

**

学习的高性能msyql这书主要网上的文章里面讲解的不太一样，自己看了也不好消化，所以自己尝试能从这书入手。ps加粗的从书上复制的。

我们先看一下mysql服务器的架构图方便我们在以后的学习，理清逻辑。

Mysql 服务器逻辑架构图[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BvE8I5dJ-1626340279159)(/Users/jiang/Documents/截屏2021-07-15 14.39.00.png)]

主要是分为三层：

第一层: 连接服务，比如我们常用navicat,小🐬这些连接的工具；

**第二层：mysql 的核心的服务在这一层，包括查询解析，分析，优化，缓存，以及所有的内置函数，（比如日期，时间，数学，和加密），所有的跨存存引擎的功能都在这一层：存储过程，触发器，视图；**现在我们可以把这简单的连接就是我们平常写sql，java的api，先不用具体的，以后不对的我们在修改；

第三层包含了存储引擎，存储引擎负责 My SQL 中数据的存储和提取。和 GNU Linu

下的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过 PI 与存储引擎

进行通信。这些接口屏蔽了不同存储引擎之 的差异，使得这些差异对上层 查询过程

透明。存储引擎 AP 包含几十个底层函数，用于执行诸如“开始 个事务”或者“根据

主键提取一行记录”等操作。但存储引擎不会去解析 SQL 酌，不同存储引擎之间 不会

相互通信，而只是简单地响应上层服务器的请求。简单的理解为存储数据的地方。

连接管理与安全新：

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询 只会在这个单独

线程中执行，该线程只能轮流在某个 CPU 核心或者 CPU 中运行。服务器会负责缓存钱

程，因此不需要为每 个新建的连接创建或者销毁线程 2 。

如何的理解这句话：

首先说一下如何的查看mysql的服务器的最大的连接数；

show variables like '%max_connections%';

如何查看已经使用的最大的连接数

show global status like 'Max_used_connections';

举一个例msyql 安装服务A 上，我们用自己的电脑去远程的理解服务器A的mysql数据库，

这时mysql的mysql连接mysql数量加一,相对于可以连接的数量就减一。

ps 注意这个账号没有关系比如我们开几个终端连接mysql 服务器Max_used_connections就会加几。[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Agq6HxAO-1626340279161)(/Users/jiang/Desktop/截屏2021-07-15 15.19.24.png)]

左边第一个终端是我第一次连接我本地的mysql 服务器，右边是开得第二个，

我们可以看见Max_used_connections的值加一。

其实这个最大连接数不是越大越好，我公司就出现这个一个问题客户用navicat连接mysql服务器，每一个都不关，造成连接数很多，相当于开好多线程连接，查询的时候需要竞争cpu,导致查询巨慢。

优化与执行

MySQ 会解析查询，井创建内部数据结构（解析树），然后对其进行各种优化，包括重

写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示

Hint）优 器，影响它的决策过程。 可以请求优化器解释（ explin ）优化过程的各个

因素，使用户可以知道服务器是如何进行优 决策的，并提供一个参考基准，便于用户

重构查询和 sc hema修改相关配置，使应用尽可能高效运行。第 章我们将 论更多优

化器的细节。

简单的说我写的sq可能和系统的执行的有一定的差别，mysql的优化器可能重写我的sql，

我们卡伊通过explain 具体执行的计划；

举一个例子：explain select * from user;[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WoPtrCcE-1626340279161)(/Users/jiang/Library/Application Support/typora-user-images/截屏2021-07-15 16.31.07.png)]

变量解释：

table:显示这一行的数据是关于那张表的

type:显示使用何种的类型，const,eq_reg,ref,range,index,和All

Const:常数查找,一般是主键，唯一的索引；

eq_reg:是一种范围的查找，一般是主键，唯一的索引的范围查找。

Ref:一般出现在连接的出现中；

Range:一般是索引的范围的查找；

index:一般是索引的扫描；

all:一般是表扫描；

Possible_keys:显示可能应用在这张表的索引，如果为空，没有可能的索引。

key:实际使用索引，如果为null，没有使用索引。

key_len:使用索引的长度。在不损失精确性的情况下，长度越短越好。

Ref:显示索引的那一列被使用，如果可能的话，是一个常数。

Rows:mysql认为必须检查的用来返回的请求的行数。

优化器并不关心表使用的是什么存储引擎，但存储引 擎对于优化 询是有影响的。优化

器会请求存储引擎提供容量或某个具体操作 开销信息，以及表数据的统计信息等。例

如，某些存储引擎的某种索引，可能对一些特定的查询有优化。

可以理解为不懂存储引擎事对查询事有不同的影响，现在我们使用的一般是InnoDB存储引擎。

需要具体了解可以参考下面这个链接。

https://blog.csdn.net/zhangyuan19880606/article/details/51217952

对于 SELECT 语句，在解析查询之前，服务器会先检查查 缓存（ Query Cache ），如果能

够在其中找到对应的 查询，服务器就不必再执行查询解析、优化和执行的整个过程，

是直接返回查询缓存中的结果集。

可以理解为缓存中有直接从缓存中获取，没有查询；

并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生井发控制的问题。本

章的目的是讨论 MySQL 在两个层面的井发控制 服务器 层与存储引擎层 并发控制是

个内容庞大的话题，有大 的理论文献对其进行过详细的论述。本章只简要地讨论

MySQL 如何控制并发读写，因此读者需要有相关的知识来理解本章接下来的内容。

Unix 系统的 email box 为例，典型的 mbox 文件格式是非常简单的。 mbox lliB

中的所有 件都串行在 起，彼此首尾相连。这种格式对于读取和分析邮件信息非常友

好，同时投递邮件 很容易，只要在文件末尾附加新的邮件内容即可。

但如果两个进程在同一时刻对同 个邮箱投递邮件，会发生什么情况？显然，邮箱的数 ＜王

据会被破坏，两封邮件的内容会交叉地附加在邮箱文件的末尾。设计良好的邮箱投递系

统会通过锁（ lock ）来防止数据损坏。如果客户试图投递邮件，而IHB 箱已经被其他客户锁住，

那就必须等待，直到锁释放才能进行投递。

可以理解为两个线程同时对一份数据性进行ddl，为了保证数据的在同一时刻，自能被一个线程修改，可以给这个数据加锁，InnoDB索引默认是行锁，当ddl 涉及到大部分的数据，可能由行锁升级到表锁；