1、Linux下安装mysql（8.0版本） （144地址）

软件下载

可能用到的软件如下：

不知道怎么下的可以直接去官网下载相应的安装包。

CentOS7下检查MySQL依赖

检查/tmp临时目录权限（必不可少）

由于mysql安装过程中，会通过mysql用户在Linux的/tmp目录下新建tmp_db文件，所以需要我们给/tmp较大的权限。
具体在Linux中执行如下指令 即可：

chmod -R 777 /tmp

安装前，检查依赖

 rpm -qa|grep libaio

• 如果存在libaio包如下：

  rpm -qa|grep net-tools

  

• 如果存在net-tools包如下：

如果不存在需要到centos安装盘里进行rpm安装。安装linux如果带图形化界面，这些都是安装好 的。

CentOS7下MySQL安装过程

将安装文件拷贝到要安装到的路径下
然后依次执行如下命令 （必须按照顺序执行）

rpm -ivh mysql-community-common-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm

在执行第三个指令的时候报了一个错误

解决方法是执行如下指令，进行安装

yum remove mysql-libs

替换完成之后再次执行刚才报错的指令

rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm

然后继续依次执行如下指令

rpm -ivh mysql-community-client-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.25-1.el7.x86_64.rpm

查看MySQL版本

执行完上面几个指令之后，mysql就安装完毕了。接下来 执行如下命令，如果成功表示安装mysql成功。类似java -version如果打出版本等信息 。

mysql --version
#或
mysqladmin --version

然后执行如下命令，查看是否安装成功。需要增加 -i 不用去区分大小写，否则搜索不到

rpm -qa|grep -i mysql

服务的初始化

为了保证数据库目录与文件的所有者为 mysql 登录的用户，如果你是以 root 身份运行 mysql 服务，需要执 行下面的命令初始化：

mysqld --initialize --user=mysql

说明： —initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将 该密码标记为过期 ，登录后你需要设置一个新的密码。生成的临时密码会往日志中记录一份。
我们可以在日志中查看密码：

cat /var/log/mysqld.log

root@localhost: 后面就是初始化的密码

启动MySQL，查看状态

#加不加.service后缀都可以
查看状态：systemctl status mysqld.service
启动：systemctl start mysqld.service
关闭：systemctl stop mysqld.service
重启：systemctl restart mysqld.service

注意， mysqld 这个可执行文件就代表着 MySQL 服务器程序，运行这个可执行文件就可以直接启动一个 服务器进程。

没启动，所以我们先来启动一下；

systemctl start mysqld.service

每次都要手动启动mysql服务还挺麻烦，所以可以去配置一下mysql的自启动；

查看MySQL服务是否自启动

 systemctl list-unit-files|grep mysqld.service

enabled说明当前mysql并不是自启动的，需要我们自己去配置一下让mysql在Linux开机的时候跟着启动。

#如不是enabled可以运行如下命令设置自启动
systemctl enable mysqld.service
#如果希望不进行自启动，运行如下命令设置
systemctl disable mysqld.service

MySQL登录

首次登录

通过 mysql -hlocalhost -P3306 -uroot -p 进行登录，在Enter password：录入初始化密码 （就是刚才查看的初始密码）

 mysql -hlocalhost -P3306 -uroot -p

修改密码

因为登陆之后初始化密码默认是过期的，所以查看数据库会报错 ，需要我们去修改密码：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

5.7版本之后（不含5.7），mysql加入了全新的密码安全机制。设置新密码太简单会报错。

改为更复杂的密码规则之后，设置成功，可以正常使用数据库了

这里我的密码是root

修改MySQL5.7字符集

在MySQL 8.0版本之前，默认字符集为 latin1 ，utf8字符集指向的是 utf8mb3网站开发人员在数据库 设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题，也就是书 ，在我们创建数据库表的时候若是没有指明表的字符集为utf-8的话，我们插入中文字符就会报错。
从MySQL 8.0开始，数据库的默认编码将改为 utf8mb4 ，从而避免上述乱码的问题。

如果你的MySQL版本是MySQL5.7之前的话，你可以按照如下的步骤来修改默认的字符集。

操作1：查看默认使用的字符集

show variables like 'character%';
# 或者
show variables like '%char%';

MySQL 5.7 默认的客户端和服务器都用了 latin1 ，不支持中文，保存中文会报错。MySQL5.7截图如 下：

在MySQL5.7中添加中文数据时，报错：

因为默认情况下，创建表使用的是 latin1 。如下：

操作2：修改字符集

vim /etc/my.cnf

在MySQL5.7或之前的版本中，在文件最后加上中文字符集配置：

操作3：重新启动MySQL服务

systemctl restart mysqld

但是原库、原表的设定不会发生变化，参数修改只对新建的数据库生效。

已有库&表字符集的变更

修改已创建数据库的字符集

alter database dbtest1 character set 'utf8';

修改已创建数据表的字符集

alter table t_emp convert to character set 'utf8';

注意：但是原有的数据如果是用非’utf8’编码的话，数据本身编码不会发生改变。已有数据需要导 出或删除，然后重新插入。

各级别的字符集原理浅析

MySQL有4个级别的字符集和比较规则，分别是： 服务器级别 、数据库级别 、表级别 、列级别 。
执行如下SQL语句，就可以查看这些级别

show variables like 'character%';

服务器级别

• character_set_server：服务器级别的字符集

之前我们在配置文件中修改了5.7版本服务器级别的字符集，它直接就决定了数据库级别的字符集跟服务级别的字符集一致。
我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET 语句修改这两个变量 的值。比如我们可以在配置文件中这样写：

[server]
character_set_server=gbk # 默认字符集
collation_server=gbk_chinese_ci #对应的默认的比较规则

当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

数据库级别

• character_set_database：当前数据库的字符集

当我们去创建一个数据库的时候若是没有显示指明字符集的话，默认就会使用character_set_database指定的字符集。而且在这种情况下的数据库中创建一个没有指明字符集的数据表的话，默认也是使用数据库级别的字符集。

我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下：

##创建，可以显示指明字符集，也可以不指定，直接使用默认的
CREATE DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];

##修改
ALTER DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];

表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

CREATE TABLE 表名 (列的信息)
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]]

ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]

如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作 为该表的字符集和比较规则。

列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列 定义的时候可以指定该列的字符集和比较规则，语法如下：

CREATE TABLE 表名(
列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
其他列...
);
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集 和比较规则作为该列的字符集和比较规则。

注意， 在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生 错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的 话就会出错，因为ascii字符集并不能表示汉字字符。

小结

我们介绍的这4个级别字符集和比较规则的联系如下：
1、如果 创建或修改列 时没有显式的指定字符集和比较规则，则该列 默认用表的 字符集和比较规则
2、如果 创建表时 没有显式的指定字符集和比较规则，则该表 默认用数据库的 字符集和比较规则
3、如果 创建数据库时 没有显式的指定字符集和比较规则，则该数据库 默认用服务器的 字符集和比较规 则

但是！在实际开发中若果我们使用的是8.0版本的mysql，我们在创建数据库以及数据表的时候无需担心字符集的指定，因为8.0版本默认的都是可以支持中文的字符集，直接使用即可。

字符集与比较规则(了解)

1、utf8 与 utf8mb4 utf8

utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示 了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计 MySQL的设计者偷偷的定义了两个概念：

• utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。
• utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

在MySQL中utf8是utf8mb3的别名，所以之后在MysQL中提到utf8就意味着使用1~3个字节来表示一个字符。如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情，那请使用utf8mb4。
此外，通过如下指令可以查看MySQL支持的字符集:

SHOW CHARSET;
#或者
SHOW CHARACTER SET;

最后一列 Maxlen ，它代表该种字符集表示一个字符最多需要几个字节。

2、比较规则

比较规则是指我们的数据在进行比较时需要遵守的规则。
我们查看字符集时就知道，MySQL版本一共支持41种字符集，其中的 Default collation 列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则 比较， utf8_spanish_ci 是以西班牙语的规则比较， utf8_general_ci 是一种通用的比较规则。
比较规则的后缀表示该比较规则是否区分语言中的重音、大小写。具体如下：

常用操作1：

#查看GBK字符集的比较规则
SHOW COLLATION LIKE 'gbk%';
#查看UTF-8字符集的比较规则
SHOW COLLATION LIKE 'utf8%';

常用操作2：

#查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server';
#查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database';
#查看具体数据库的字符集
SHOW CREATE DATABASE dbtest1;
#修改具体数据库的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

常用操作3：

#查看表的字符集
show create table employees;
#查看表的比较规则
show table status from atguigudb like 'employees';
#修改表的字符集和比较规则
ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

请求到响应过程中字符集的变化

涉及到的几个系统变量

这几个系统变量在我的计算机上的默认值如下（不同操作系统的默认值可能不同）：

为了体现出字符集在请求处理过程中的变化，我们这里特意修改一个系统变量的值：

现在假设我们客户端发送的请求是下边这个字符串：

SELECT * FROM t WHERE s = '我';

为了方便大家理解这个过程，我们只分析字符 ‘我’ 在请求从发送到结果返回过程中 字符集的转换。

1、客户端发送请求所使用的字符集
一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一 样，如下：

• 类 Unix 系统使用的是 utf8
• Windows 使用的是 gbk

当客户端使用的是 utf8 字符集，字符 ‘我’ 在发送给服务器的请求中的字节形式就是： 0xE68891

如果你使用的是可视化工具，比如navicat、sqlyog之类的，这些工具可能会使用自定义的字符集比如utf8
来编码发送到服务器的字符串，而不采用操作系统默认的字符集（所以在学习的时候还是尽量用
命令行窗口）

2、 服务器接收到客户端发送来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是 character_set_client指定的字符集 ，然后把这串字节转换为 character_set_connection 字符集编码的字符。
由于我的计算机上 character_set_client 的值是 utf8 ，首先会按照 utf8 字符集对字节串 0xE68891 进行解码，得到的字符串就是 ‘我’ （若果客户端使用的字符集跟服务器使用的字符集不一样，就会乱码），然后按照 character_set_connection 代表的 字符集，也就是 我们刚才修改为的gbk 进行编码，得到的结果就是字节串 0xCED2 。
3、因为表 t 的列 col 采用的也是 gbk 字符集，与 character_set_connection 一致，所以直接到列中找字节值为 0xCED2 的记录，最后找到了一条记录。

如果某个列使用的字符集和character_set_connection代表的字符集不一致的话，还需要进行
一次字符集转换。

4、上一步骤找到的记录中的 col 列其实是一个字节串 0xCED2 ， col 列是采用 gbk 进行编码的，所 以首先会将这个字节串使用 gbk 进行解码，得到字符串 ‘我’ ，然后再把这个字符串使用 character_set_results 代表的字符集，也就是 utf8 进行编码，得到了新的字节串： 0xE68891 ，然后发送给客户端。
5、 由于客户端是用的字符集是 utf8 ，所以可以顺利的将 0xE68891 解释成字符 我 ，从而显示到我 们的显示器上，所以我们人类也读懂了返回的结果。

从这个分析中我们可以得出这么几点需要注意的地方:

• 服务器认为客户端发送过来的请求是用character_set_client编码的。

假设你的客户端采用的字符集和character_set_client不一样的话，这就会出现识别不准确的情况。比如我的客户端使用的是utf8字符集，如果把系统变量character_set_client的值设置为ascii的话，服务器可能无法理解我们发送的请求，更别谈处理这个请求了。

• 服务器将把得到的结果集使用character_set_results编码后发送给客户端。

假设你的客户端采用的字符集和 character_set_results 不一样的话，这就可能会出现客户端无法解码结果集的情况，结果就是在你的屏幕上出现乱码。比如我的客户端使用的是utf8字符集，如果把系统变量character_set_results的值设置为ascii的话，可能会产生乱码。

• character_set_connection只是服务器在将请求的字节串从character_set_client转换为chracter set_comnection时使用，一定要注意，该字符集包含的字符范围一个要涵盖请求中的字符要不然会导致有的字符无法使用character_set_connection代表的字符集进行编码。

所以我们可以总结出：
开发中通常把character_set_client 、character_set_connection、character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。为了方便我们设置，MySQL提供了一条非常简便的语句:

SET NAMES 字符集名;

这一条语句产生的效果和我们执行这3条的效果是一样的:

SET character_set_client =字符集名;
SET character_set_connection =字符集名;
SET character_set_results =字符集名;

比方说我的客户端使用的是utf8字符集，所以需要把这几个系统变量的值都设置为utf8 :

mysql>SET NAMES utf8;

另外，如果你想在启动客户端的时候就把character_set_client、 character_set_connection character_set_results这三个系统变量的值设置成一样的，那我们可以在启动客户端的时候指定一个叫default-character-set 的启动选项，比如在配置文件里可以这么写:

[client]
default-character-set=utf8

它起到的效果和执行一遍SET NAMES utf8是一样一样的，都会将那三个系统变量的值设置成utf8。

SQL大小写规范问题

Windows和Linux平台区别

在 SQL 中，关键字和函数名是不用区分字母大小写的，比如 SELECT、WHERE、ORDER、GROUP BY 等关 键字，以及 ABS、MOD、ROUND、MAX 等函数名。
不过在 SQL 中，你还是要确定大小写的规范，因为在 Linux 和 Windows 环境下，你可能会遇到不同的大 小写问题。 windows系统默认大小写不敏感 ，但是 linux系统是大小写敏感的 。

是否敏感可以通过如下命令查看：

SHOW VARIABLES LIKE '%lower_case_table_names%';

lower_case_table_names参数值的设置：

• 默认为0，大小写敏感 。
• 设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转 换为小写对表和数据库进行查找。
• 设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。

两个平台上SQL大小写的区别具体来说：

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
1、数据库名、表名、表的别名、变量名是严格区分大小写的；
2、关键字、函数名称在 SQL 中不区分大小写；
3、列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；

MySQL在Windows的环境下全部不区分大小写

Linux下大小写规则设置

当想设置为大小写不敏感时，要在 my.cnf 这个配置文件 [mysqld] 中加入 lower_case_table_names=1 ，然后重启服务器。
但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名。
此参数适用于MySQL5.7。在MySQL 8下禁止在重新启动 MySQL 服务时将 lower_case_table_names 设置成不同于初始化 MySQL 服务时设置的 lower_case_table_names 值。
如果非要将MySQL8设置为大小写不敏感，具体步骤为：

1、停止MySQL服务
2、删除数据目录，即删除 /var/lib/mysql 目录
3、在MySQL配置文件（ /etc/my.cnf ）中添加 lower_case_table_names=1
4、启动MySQL服务

SQL编写建议

如果你的变量名命名规范没有统一，就可能产生错误。这里有一个有关命名规范的建议：

1. 关键字和函数名称全部大写；
2. 数据库名、表名、表别名、字段名、字段别名等全部小写；
3. SQL 语句必须以分号结尾。

数据库名、表名和字段名在 Linux MySQL 环境下是区分大小写的，因此建议你统一这些字段的命名规 则，比如全部采用小写的方式。 虽然关键字和函数名称在 SQL 中不区分大小写，也就是如果小写的话同样可以执行。但是同时将关键词 和函数名称全部大写，以便于区分数据库名、表名、字段名。

2、MySQL的数据目录（了解）

MySQL8的主要目录结构

在Linux中安装好MySQL 8之后，我们可以执行命令查看Linux下的mysql目录结构：

同样我们可以在mysql中执行如下命令进行查看

从结果中可以看出，在我的计算机上MySQL的数据目录就是 /var/lib/mysql/ 。

数据库文件的存放路径

3、用户与权限管理

4、逻辑架构-mysql

1、服务器处理客户端请求

首先MySQL是典型的c/s架构，即 Client/Server 架构，服务器端程序使用的mysqld 。不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是∶客户端进程向服务器进程发送段文本(SQL语句)，服务器进程处理后再向客户端进程发送一段文本(处理结果)。
那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？下图是查询请求时的大致流程。

在具体的分析流程之前，我们先来学习一下MySQL的体系结构。 MySQL Server[架构]从上到下依次为网络连接层(Connectors)、服务层（MySQL Server）、存储引擎层(Plugable Storage Engines)、系统文件层(File System)。

网络连接层（Connectors）

主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对这个连接请求进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。

值得一提的是这一层并不是MySQL所特有的技术，它是MySQL服务器之外的客户端程序

• 客户端连接器：处理客户端与mysql服务端的连接、授权认证、安全等，需要编写api、客户端驱动程序实现与服务端的连接。
• 支持的编程语言：java、C、Python、.Net等等，基本支持所有主流的编程语言

系统（客户端）访问MySQL服务器前，做的第一件事就是建立TCP连接。
经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。

• 用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行
• 用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依
  赖于此时读到的权限

为什么要设计成线程池？
在服务器内部收到TCP连接请求后，由于每个client都要有自己的线程，必须要分配给一个线程专门与这个客户端的交互，每个连接的查询都在一个单独的线程中执行。可是现实场景中数据库访问连接实在是太多了，如果每次连接都要创建一个线程，同时还要负责该线程的销毁。对于系统来说是多么大的消耗。由于线程是操作系统宝贵的资源。这时候线程池的出现就显得自然了，服务器缓存了线程，因此不需要为每个Client连接创建和销毁线程。

服务层（MySQL Server）

这一层是MySQL的核心组件，主要包括系统管理和控制工具(Management Services&Utilities)、连接池（Connection Pool）、SQL接口（SQL Interface）、解析器（Parser）、查询解析器（Optimizer）、缓存（Cache&Buffer）等。

• 连接池（Connection Pool）
  负责存储和管理客户端与数据库的连接，一个线程管理一个连接
• 系统管理和控制工具(Management Services&Utilities)
  系统管理和控制工具负责备份恢复、安全管理、集群管理等
• SQL Interface: SQL接口
  • 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQL
    Interface接口
  • MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定
    义函数等多种SQL语言接口
• Parser: 解析器
  • 在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构
    传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错
    误，那么就说明这个SQL语句是不合理的。
  • 在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树，并根据数据字
    典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还
    会对SQl查询进行语法上的优化，进行查询重写。

• Optimizer: 查询优化器

  • SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个执行计划。
  • 这个执行计划表明应该使用哪些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。
  • 它使用“选取-投影-连接”策略进行查询。例如：

    SELECT id,name FROM student WHERE gender = '女';
    

    这个SELECT查询先根据WHERE语句进行选取，而不是将表全部查询出来以后再进行gender过滤。 这个SELECT查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。

• Caches & Buffers： 查询缓存组件。

  • MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。
  • 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。这个查询缓存可以在不同客户端之间共享。查询语句一般先查缓存，缓存查不到才会进行处理，缓存的目的是避免硬解析，提高系统性能
  • 从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除。

MySQL的核心服务层都在这一层，查询解析，SQL执行计划分析，SQL执行计划优化，查询缓存。以及跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。

红色部分为服务层的执行过程。这里简单分析SQL语句在服务层中具体的流程：
查询缓存 在解析查询之前，服务器会检查查询缓存，如果能找到对应的查询，服务器不必进行查询解析、优化和执行的过程，直接返回缓存中的结果集。
解析器与预处理器 MySQL会解析查询，并创建了一个内部数据结构（解析树）。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理会根据MySQL的规则进一步检查解析树是否合法。比如要查询的数据表和数据列是否存在等。
查询优化器 优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。优化器并不关心使用的什么存储引擎，但是存储引擎对优化查询是有影响的。优化器要求存储引擎提供容量或某个具体操作的开销信息来评估执行时间。
查询引擎 在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令调用存储引擎的接口得出结果。

存储引擎层（Plugable Storage Engines）

插件式存储引擎层（ Storage Engines）， 真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作（与底层的文件系统进行交互） ，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
服务器中的查询执行引擎通过API与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。MySQL采用插件式的存储引擎。MySQL为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码来得到自己想要达到的性能。例如阿里巴巴的X-Engine，为了满足企业的需求facebook与google都对InnoDB存储引擎进行了扩充。

特点：
存储引擎是针对于表的而不是针对库的（一个库中不同表可以使用不同的存储引擎），服务器通过API与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异。

Mysql的事务回滚等处理是给存储引擎负责的，下面介绍一下几种常见的MySQL存储引擎：

• 
  InnoDB存储引擎是mysql默认的存储引擎，也是使用最广泛的存储引擎，前面也说了，InnoDB是一种可以支持事务的存储引擎。InnoDB是mysql 5.1才开始出现的，之前名称是称为InnoDB plugin，直到被oracle收购mysql后，才正式命名为InnoDB。InnoDB采用MVCC来支持事务一致性和并发，并且实现了4个基本的隔离级别，默认级别是PREATABLE READ(可重复读)，并且通过间隙锁(next-key loking)策略来防止幻读的出现，

  InnoDB存储引擎✔

• 
  在MySQL5.1之前版本，MyISAM是默认的存储引擎，MyISAM提供了全文索引、压缩、空间函数等，但是MyISAM是不支持事务和行级锁的，MyISAM会将表存储在两个文件中，数据文件和索引文件，拓展名分别是.MYD和.MYI，MyISAM虽然没有行级锁，不过是可以支持表锁的，所以在一些业务系统还是可以做到并发控制的 。

  MyISAM存储引擎✔

  

系统文件层（File System）

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在文件系统上，以文件的方式存
在的，并完成与存储引擎的交互。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等 。
当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用DAS、NAS、SAN等各种存储系统。

小结

下面为了熟悉SQL执行流程方便，我们可以简化如下:
1.连接层:客户端和服务器端建立连接，客户端发送sQL至服务器端;
2. SQL层(服务层)∶对sQL语句进行查询处理;与数据库文件的存储方式无关;
3．存储引擎层:与数据库文件打交道，负责数据的存储和读取。

2、 SQL执行流程

2. 1 MySQL 中的 SQL执行流程

MySQL的查询流程图（背）：

1. 查询缓存流程
客户端发起的请求进入到数据库服务器Server 之后首先会去查询缓存，Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。
需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。大多数情况查询缓存就是个鸡肋，为什么呢？
查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在MySQL 中的查询缓存，不是缓存查询计划，而是缓存查询对应的结果。这就意味着查询匹配的名中率性大大降
低，只有相同的查询操作才会命中查询缓存。两个查询请求语句在任何字符上的不同（例如：空格、注释、
大小写），都会导致缓存不会命中。因此 MySQL 的查询缓存命中率不高。
同时，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、
information_schema、 performance_schema 数据库中的表，那这个请求也不会被缓存。以某些系统函数
举例，可能同样的函数的两次调用会产生不一样的结果，比如函数NOW，每次调用都会产生最新的当前
时间，如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次
查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询
的结果就是错误的！
此外，既然是缓存，那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的
结构或者数据被修改，如对该表使用了INSERT、 UPDATE、DELETE、TRUNCATE TABLE、ALTER
TABLE、DROP TABLE或 DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高
速缓存中删除！对于更新压力大的数据库来说，查询缓存的命中率会非常低。
总之，因为查询缓存往往弊大于利，查询缓存的失效非常频繁。基于以上原因mysql8.0抛弃了查询缓存这个功能。
一般建议大家在静态表里使用查询缓存，什么叫静态表呢?[就是一般我们极少更新的表。比如，一个系统配置表、字典表，这张表上的查询才适合使用查询缓存。
好在MySQL5.7也提供了这种“按需使用”的方式。你可以将my.cnf参数query_cache_type 设置成DEMAND，代表当sql语句中有SQL_CACHE关键词时才缓存。比如:

#query_cache_type有3个值0代表关闭查询缓存OFF，1代表开启ON，2(DEMAND)
query_cache_type=2

可以使用该语句查看该变量的状态(5.7版本才有，8.0删除抛弃了)

show variables like 'query_cache_type';

这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，像下面这个语句一样:

2. 解析器流程
在没有命中缓存的情况下就会来到解析器流程，真正开始执行SQL语句，首先在解析器中对 SQL 语句进行语法分析、语义分析，决定该SQL语句到底要干什么。

分析器先做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面
的字符串分别是什么，代表什么意思。
MySQL 从你输入的”select”这个关键字识别出来，这是一个查询语句。它也要把字符串比如“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
接着，要做“语法分析”。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输
入的这个 SQL 语句是否满足 MySQL 语法。
如果你的语句不对，就会收到”“You have an error in your SQL syntax”的错误提醒，比如下面这个语句from写成了”rom”。

如果SQL语句正确，则会生成一个这样的语法树：

下面是Sql词法分析的过程步骤:

词法分析结束后解析器的工作就完成了，经过解析器之后就知道了SQL语句要做什么之后来到优化器流程。

3.优化器 ：在优化器中会确定SQL语句的执行路径，比如是根据全表检索，还是根据索引检索等。

解析器流程结束后，来到查询优化器流程，优化器中进行SQL的优化，之后生成一个执行计划，执行计划去调用执行引擎，调用相应的API接口去查询底层的文件系统，若是查询到结果就将其放到查询缓存中并且返回给客户端。
经过优化器之后就算一条查询语句有多种执行方式，最后都会返回相同的结果，优化器的作用就是找到其中最好的执行计划，通俗来说就是找到执行SQL语句最好的方式。

比如:优化器在表里面有多个索引的时候，会决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序，还有表达式简化、子查询转为连接、外连接转为内连接等。
举例：如下语句是执行两个表的join：

方案 1 ：可以先从表 test 1 里面取出 name='zhangwei'的记录的 ID 值，再根据 ID 值关联到表
test 2 ，再判断 test 2 里面 name的值是否等于 'mysql高级课程'。
方案 2 ：可以先从表 test 2 里面取出 name='mysql高级课程' 的记录的 ID 值，再根据 ID 值关联到
test 1 ，再判断 test 1 里面 name的值是否等于 zhangwei。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用
哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等。后面讲到索引我们再谈。
在查询优化器中，又可以分为逻辑查询优化阶段和物理查询`优化阶段。
逻辑查询优化就是通过改变SQL语句的内容来使得SQL查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对SQL语句进行等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。
物理查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。在这个阶段里，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率。
优化器执行完之后，会生成一个执行计划，还没有真正去执行，需要来到执行器流程才会真正去执行SQL语句。

4. 执行器 ：

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

在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就执行 SQL查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

select * from test where id= 1 ;

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，调用存储引擎API对表进行的读写。这里的存储引擎API 只是抽象接口，下面还有个存储引擎层，具体实现还是要看当初创建表时选择的存储引擎是什么。

比如：表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：

调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 1 ，如果不是则跳过，如果是则将
这行存在结果集中；调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多。

SQL 语句在 MySQL 中的流程是：SQL语句→查询缓存→解析器→优化器→执行器。

2.2 MySQL 8.0 中SQL执行原理

前面的结构图很复杂，我们需要抓取最核心的部分: SQL的执行原理。不同的DBMS的SQL的执行原理是相通的，只是在不同的软件中，各有各的实现路径。
既然一条sQL语句会经历不同的模块，那我们就来看下，在不同的模块中，SQL执行所使用的资源（时间）是怎样的以及如何在MySQL中对一条sQL语句的执行时间进行分析。

1. 确认profiling 是否开启

了解查询语句底层执行的过程: select @@profiling;或者show variables like '%profiling%'查看是否开启计划。
profiling=0 代表关闭，我们需要把 profiling 打开，即设置为 1 。
开启它可以让MySQL收集在SQL执行时所使用的资源情况，命令如下:

mysql> slect @@profiling;

mysql> show variables like 'profiling ';

可以当前该变量的值是0，需要我们手动设置为1；

2. 多次执行相同SQL查询

我们执行一个 SQL 查询（你可以执行任何一个 SQL 查询）：
比如如下指令： select * from employees;

3. 查看profiles

查看当前会话所产生的所有 profiles：

mysql> show profiles;  # 显示最近的几次查询

4. 查看profile

显示具体语句的执行计划，查看程序的执行步骤：

mysql> show profile;  # 显示最近的一次查询

当然你也可以查询指定的 Query ID，比如：

查询 SQL 的执行时间结果和上面是一样的。

此外，还可以查询更丰富的内容：

mysql> show profile cpu,block io for query 7 ;

2. 3 MySQL 5. 7 中SQL执行原理

将上述操作在MySQL5.7中测试，发现前后两次相同的sql语句，执行的查询过程仍然是相同的。不是会使用
缓存吗？这里我们需要显式开启查询缓存模式。在MySQL5.7中如下设置：

1. 配置文件中开启查询缓存

在 /etc/my.cnf 中新增一行：

query_cache_type= 1

2. 重启mysql服务

systemctl restart mysqld

3. 开启查询执行计划

由于重启过服务，需要重新执行如下指令，开启profiling。

mysql> set profiling= 1 ;

4. 执行语句两次：

mysql> select * from locations;

5. 查看profiles

6. 查看profile

mysql> show profile for query 1 ;

显示执行计划，查看程序的执行步骤：

mysql> show profile for query 2 ;

结论不言而喻。执行编号 2 时，比执行编号 1 时少了很多信息，从截图中可以看出查询语句直接从缓存中获取数据。

2.4 SQL语法顺序

随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同
而动态调整执行顺序。

需求：查询每个部门年龄高于 20 岁的人数且高于 20 岁人数不能少于 2 人，显示人数最多的第一名部门信息

下面是经常出现的查询顺序：

2.5 Oracle中的SQL执行流程(了解)

Oracle 中采用了共享池来判断 SQL 语句是否存在缓存和执行计划，通过这一步骤我们可以知道应该采用
硬解析还是软解析。

我们先来看下 SQL 在 Oracle 中的执行过程：

从上面这张图中可以看出，SQL 语句在 Oracle 中经历了以下的几个步骤。

1 .语法检查： 检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。

2 .语义检查： 检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统
就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。

3 .权限检查： 看用户是否具备访问该数据的权限。

4.共享池检查： 共享池（Shared Pool）是一块内存池， 最主要的作用是缓存 SQL 语句和该语句的执行计
划。 Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。那软解析
和硬解析又该怎么理解呢？
在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash 值在库缓存（Library Cache）中
查找，如果存在 SQL 语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。
如果没有找到 SQL 语句和执行计划，Oracle 就需要创建解析树进行解析，生成执行计划，进入“优化器”
这个步骤，这就是硬解析。
5. 优化器：优化器中就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。
6. 执行器：当有了解析树和执行计划之后，就知道了 SQL 该怎么被执行，这样就可以在执行器中执
行语句了。

共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲区等。我们上面已经讲到了库缓存区，它主要
缓存 SQL 语句和执行计划。而数据字典缓冲区存储的是 Oracle 中的对象定义，比如表、视图、索引等对
象。当对 SQL 语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

库缓存这一个步骤，决定了 SQL 语句是否需要进行硬解析。为了提升 SQL 的执行效率，我们应该尽量
避免硬解析，因为在 SQL 的执行过程中，创建解析树，生成执行计划是很消耗资源的。

你可能会问，如何避免硬解析，尽量使用软解析呢？在 Oracle 中，绑定变量是它的一大特色。绑定变量
就是在 SQL 语句中使用变量，通过不同的变量取值来改变 SQL 的执行结果。这样做的好处是能提升软解
析的可能性，不足之处在于可能会导致生成的执行计划不够优化，因此是否需要绑定变量还需要视情况
而定。

举个例子，我们可以使用下面的查询语句：

这两个查询语句的效率在 Oracle 中是完全不同的。如果你在查询 player_id = 10001 之后，还会查询
10002 、 10003 之类的数据，那么每一次查询都会创建一个新的查询解析。而第二种方式使用了绑定变
量，那么在第一次查询之后，在共享池中就会存在这类查询的执行计划，也就是软解析。

因此， 我们可以通过使用绑定变量来减少硬解析，减少 Oracle 的解析工作量。 但是这种方式也有缺点，
使用动态 SQL 的方式，因为参数不同，会导致 SQL 的执行效率不同，同时 SQL 优化也会比较困难。

小结：

Oracle 和 MySQL 在进行 SQL 的查询上面有软件实现层面的差异。Oracle 提出了共享池的概念，通过共享
池来判断是进行软解析，还是硬解析。

3. 数据库缓冲池(buffer pool)

InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页
面（包括读页面、写页面、创建新页面等操作）。而磁盘 I/O 需要消耗的时间很多，如果在内存中进行操
作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为
数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访
问。
这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间。要知道，这种策略对提
升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。

3. 1 缓冲池 vs 查询缓存

缓冲池和查询缓存是一个东西吗？不是。

1. 缓冲池（Buffer Pool）

首先我们需要了解在 InnoDB 存储引擎中，缓冲池都包括了哪些。
在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种
数据的缓存，如下图所示：

从图中，你能看到 InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典
信息等。
缓存池的重要性：
对于使用InnoDB作为存储引擎的表来说，表中的内容不管是用于存储用户数据的索引(包括聚簇索引和二级索引)，还是各种系统数据，都是以页的形式存放在表空间中的，而所谓的表空间只不过是InnoDB对文件系统上一个或几个实际文件的抽象，也就是说我们的数据说到底还是存储在磁盘上的。
但是各位也都知道，磁盘的速度慢的跟乌龟一样，怎么能配得上“快如风，疾如电”的CPU呢? 这里，缓冲池可以帮助我们消除CPU和磁盘之间的鸿沟。所以InnoDB存储引擎在处理客户端的请求时，当需要访问某个页中的某个的数据时，就会把完整的页的数据全部加载到内存中,也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘TO的开销了。
缓存原则：
使用“位置 频次”这个原则，可以帮我们对 I/O 访问效率进行优化。
首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。
其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200 G，但是内存只有 16 G，缓冲
池大小只有 1 G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会优先对使用频次高
的热数据进行加载。
*缓冲池的预读特性——“局部性原理”
了解了缓冲池的作用之后，我—们还需要了解缓冲池的另一个特性:预读。
缓冲池的作用就是提升I/o效率，而我们进行读取数据的时候存在一个“局部性原理”，也就是说我们使用了一些数据，大概率还会使用它周围的一些数据，因此采用“预读”的机制提前加载查到的数据的前后部分的数据，可以减少未来可能的磁盘I/О操作。

2. 查询缓存

那么什么是查询缓存呢？
查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在
MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。因为命中条件苛刻，而且只要数据表
发生变化，查询缓存就会失效，因此命中率低。

3. 2 缓冲池如何读取数据

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。
缓存在数据库中的结构和作用如下图所示：

如果我们执行 SQL 语句的时候更新了缓存池中的数据，那么这些数据会马上同步到磁盘上吗？
实际上，当我们对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录信息，然后数据库会以一定的频率刷新到磁盘上。注意并不是每次发生更新操作，都会立刻进行磁盘回写。缓冲池会采用一种叫做checkpoint的机制将数据回写到磁盘上，这样做的好处就是提升了数据库的整体性能。
比如，当缓冲池不够用时，需要释放掉一些不常用的页，此时就可以强行采用checkpoint的方式，将不常用的脏页回写到磁盘上，然后再从缓冲池中将这些页释放掉。这里脏页(dirty page)指的是缓冲池中被修改过的页，与磁盘上的数据页不一致。

3. 3 查看/设置缓冲池的大小

如果你使用的是MySQL My]SAM存储引擎，它只缓存索引，不缓存数据，对应的键缓存参数为key_buffer_size，你可以用它进行查看。
如果你使用的是 InnoDB 存储引擎，可以通过查看 innodb_buffer_pool_size 变量来查看缓冲池的大
小。命令如下：

show variables like 'innodb_buffer_pool_size';

你能看到此时 InnoDB 的缓冲池大小只有 134217728 / 1024 / 1024 = 128 MB。我们可以修改缓冲池大小，比如
改为 256 MB，方法如下：

set global innodb_buffer_pool_size = 268435456 ;

然后再来看下修改后的缓冲池大小，此时已成功修改成了 256 MB：

3.4 多个Buffer Pool实例

Buffer Pool本质是InnoDB向操作系统申请的一块连续的内存空间，在多线程环境下，访问Buffer Pool中的数据都需要加锁处理。在Buffer Pool特别大而且多线程并发访问特别高的情况下，单一的Buffer Pool可能会影响请求的处理速度。所以在Buffer Pool特别大的时候，我们可以把它们拆分成若干个小的Buffer Pool，每个Buffer Pool都称为一个实例，它们都是独立的，独立的去申请内存空间，独立的管理各种链表。所以在多线程并发访问时并不会相互影响，从而提高并发处理能力。
我们可以在服务器启动的时候通过设置innodb_buffer_pool_instances的值来修改Buffer Pool实例的个数比方说这样: 修改mysql配置文件的server标签的如下内容

[server]
innodb_buffer_pool_instances = 2

这样就表明我们要创建 2 个Buffer Pool实例。
查看缓冲池的个数，使用命令：

show variables like 'innodb_buffer_pool_instances';

那每个Buffer Pool实例实际占多少内存空间呢？其实使用这个公式算出来的：

innodb_buffer_pool_size/innodb_buffer_pool_instances

也就是总共的大小除以实例的个数，结果就是每个Buffer Pool实例占用的大小。
不过也不是说Buffer Pool实例创建的越多越好，分别管理各个Buffer Pool也是需要性能开销的，InnoDB规定:当innodb_buffer_pool_size的值小于1G的时候设置多个实例是无效的,InnoDB会默认把innodb_buffer_pool_instances的值修改为1。而我们鼓励在Buffer Pool大于或等于1G的时候设置多个Buffer Pool实例。

3.5 引申问题

Buffer Pool是MySQL内存结构中十分核心的一个组成，你可以先把它想象成一个黑盒子。
黑盒下的更新数据流程
当我们查询数据的时候，会先去Buffer Pool中查询。如果Buffer Pool中不存在，存储引擎就先将数据从磁盘加载到Buffer Pool中，然后将数据返回给客户端; 同理，当我们更新某个数据的时候，如果这个数据不存在于BufferPool，同样会先数据加载进来，然后修改内存的数据。被修改过的数据会在之后统一刷入磁盘。
这里注意，并不是以修改缓冲池中的数据就会立马将其更新到磁盘，这是没有必要的。因为一个数据页的大小是16KB，而我们有可能只是去修改了某个表中的某条数据，因为这一条数据的变动 就去更新16KB的数据页，显然沾点….所以我们都是定期进行一次缓冲池到磁盘的更新，而不是有变化就更新。

这个过程看似没啥问题，实则是有问题的。假设我们修改Buffer Pool中的数据成功，但是还没来得及将数据刷入磁盘MySQL就挂了怎么办?按照上图的逻辑，此时更新之后的数据只存在于Buffer Pool中，如果此时MysQL宕机了，这部分数据将会永久地丢失;
再者，我更新到一半突然发生错误了，想要回滚到更新之前的版本，该怎么办?如果连数据持久化的保证、事务回滚都做不到还谈什么崩溃恢复?
答案： 利用Redo Log & Undo Log两个日志文件数据的恢复。这个后面有详细解释。

5、存储引擎 -mysql

引擎概述

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

简而言之，存储引擎就是指表的类型。其实存储引擎以前叫做表处理器，后来改名为存储引擎，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

查看存储引擎

• 查看mysql默认支持存储引擎的指令：

show engines;

Support表示是否支持该类型的引擎，其中DEFAULT表示默认的存储引擎。Transactions表示该引擎是否支持事务，XA表示是否支持分布式事务

设置系统默认的存储引擎

• 查看默认的存储引擎： ```sql show variables like ‘%storage_engine%’;

  或

  SELECT @@default_storage_engine;

![image.png](https://cdn.nlark.com/yuque/0/2022/png/28814483/1653290785792-e80d5f97-1ba3-4b1d-a415-6969ecf7db02.png#clientId=uddf02d1b-15ea-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=154&id=u25e45a4b&name=image.png&originHeight=193&originWidth=573&originalType=binary&ratio=1&rotation=0&showTitle=false&size=20627&status=done&style=none&taskId=u0e835544-2958-442f-b8d6-9213a695b85&title=&width=458.4)

- **修改默认的存储引擎**<br />如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用 InnoDB 作为表的存储引擎。<br />如果我们想改变表的默认存储引擎的话，可以这样写启动服务器的命令行：

`SET DEFAULT_STORAGE_ENGINE=MyISAM;`<br />或者修改my.cnf 文件：
```sql
default-storage-engine=MyISAM

然后重启服务
systemctl restart mysqld.service

设置表的存储引擎

存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为 不同的表设置不同的存储引擎 ，也就是
说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

1、创建表时指定存储引擎

我们之前创建表的语句都没有指定表的存储引擎，那就会使用默认的存储引擎InnoDB 。如果我们想显式的指定一下表的存储引擎，那可以这么写：

CREATE TABLE 表名(
 建表语句;
) ENGINE = 存储引擎名称;

2、修改表的存储引擎

如果表已经建好了，我们也可以使用下边这个语句来修改表的存储引擎：

ALTER TABLE 表名 ENGINE = 存储引擎名称;

示例：

这时我们再查看一下engine_demo_table的表结构：

或者

各类存储引擎的介绍

https://achang.blog.csdn.net/article/details/122581631