MySQL高级1【安装-目录-用户和权限-架构-存储引擎】尚硅谷康师傅

标签：康师傅 缓存 SQL MySQL 字符集 mysql 硅谷 权限

【第01章 Linux下MySQL的安装与使用】

1. 安装前说明

• 安装并启动好两台虚拟机：CentOS 7

  • 掌握克隆虚拟机的操作
    • mac地址
    • 主机名
    • ip地址
    • UUID

• 安装有Xshell 和Xftp 等访问CentOS系统的工具

• CentOS6和CentOS7在MySQL的使用中的区别

  1. 防火墙：6是iptables，7是firewalld
  2. 启动服务的命令：6是service，7是systemctl

1.1 查看是否安装过MySQL

• 如果你是用rpm安装, 检查一下RPM PACKAGE：

rpm -qa | grep -i mysql # -i 忽略大小写

• 检查mysql service：

systemctl status mysqld.service

• 如果存在mysql-libs的旧版本包，显示如下：

  

1.2 MySQL的卸载

1. 关闭 mysql 服务

systemctl stop mysqld.service

2. 查看当前 mysql 安装状况

rpm -qa | grep -i mysql
# 或
yum list installed | grep mysql

3. 卸载上述命令查询出的已安装程序

yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx

务必卸载干净，反复执行rpm -qa | grep -i mysql确认是否有卸载残留

4. 删除 mysql 相关文件

• 查找相关文件

find / -name mysql

• 删除上述命令查找出的相关文件

rm -rf xxx

5.删除 my.cnf

rm -rf /etc/my.cnf

2. MySQL的Linux版安装

MySQL的4大版本

• MySQL Community Server 社区版本，开源免费，自由下载，但不提供官方技术支持，适用于大多数普通用户。
• MySQL Enterprise Edition 企业版本，需付费，不能在线下载，可以试用30天。提供了更多的功能和更完备的技术支持，更适合于对数据库的功能和可靠性要求较高的企业客户。
• MySQL Cluster 集群版，开源免费。用于架设集群服务器，可将几个MySQL Server封装成一个 Server。需要在社区版或企业版的基础上使用。
• MySQL Cluster CGE 高级集群版，需付费。

截止目前，官方最新版本为8.0.27 。此前，8.0.0 在 2016.9.12日就发布了。
本课程中主要使用8.0.25版本。同时为了更好的说明MySQL8.0新特性，还会安装MySQL5.7 版 本，作为对比。

此外，官方还提供了MySQL Workbench （GUITOOL）一款专为MySQL设计的ER/数据库建模工具。它是 著名的数据库设计工具DBDesigner4的继任者。MySQLWorkbench又分为两个版本，分别是社区版 （MySQL Workbench OSS）、商用版（MySQL WorkbenchSE）。

下载MySQL指定版本

1. 下载地址
   官网：https://www.mysql.com
2. 打开官网，点击DOWNLOADS
   然后，点击 MySQL Community(GPL) Downloads

3. 点击 MySQL Community Server

4. 在General Availability(GA) Releases中选择适合的版本

• 如果安装Windows 系统下MySQL ，推荐下载 MSI安装程序 ；点击 Go to Download Page 进行下载
  即可

• Windows下的MySQL安装有两种安装程序
  • mysql-installer-web-community-8.0.25.0.msi 下载程序大小：2.4M；安装时需要联网安装组件。
  • mysql-installer-community-8.0.25.0.msi 下载程序大小：435.7M；安装时离线安装即可。推荐。

5. Linux系统下安装MySQL的几种方式
   5.1 Linux系统下安装软件的常用三种方式：

• 方式1：rpm命令
  使用rpm命令安装扩展名为".rpm"的软件包。

  • rpm包的一般格式：

  

• 方式2：yum命令

  ​ 需联网，从 互联网获取 的yum源，直接使用yum命令安装。

• 方式3：编译安装源码包

​ 针对 tar.gz 这样的压缩格式，要用tar命令来解压；如果是其它压缩格式，就使用其它命令。

5.2 Linux系统下安装MySQL，官方给出多种安装方式

• 这里不能直接选择CentOS 7系统的版本，所以选择与之对应的 Red Hat Enterprise Linux
• https://downloads.mysql.com/archives/community/ 直接点Download下载RPM Bundle全量包。包括了所有下面的组件。不需要一个一个下载了。

6. 下载的tar包，用压缩工具打开

   解压后rpm安装包 （红框为抽取出来的安装包）

2.1 CentOS7下检查MySQL依赖

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

由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限。执行 ：

chmod -R 777 /tmp

2. 安装前，检查依赖

rpm -qa|grep libaio
rpm -qa|grep net-tools

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

2.2 CentOS7下MySQL安装过程

1. 将安装程序拷贝到/opt目录下

在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 
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-community-server会报错
• rpm是Redhat Package Manage缩写，通过RPM的管理，用户可以把源代码包装成以rpm为扩展名的文件形式，易于安装。
• -i, --install 安装软件包
• -v, --verbose 提供更多的详细信息输出
• -h, --hash 软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条
• 

若存在mariadb-libs问题，则执行yum remove mysql-libs即可

2.3 查看MySQL版本

mysql --version 
#或
mysqladmin --version

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

rpm -qa|grep -i mysql

2.4 服务的初始化

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

mysqld --initialize --user=mysql

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

查看密码：

cat /var/log/mysqld.log

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

2.5 启动MySQL，查看状态

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

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

查看进程：

ps -ef | grep -i mysql

2.6 查看MySQL服务是否自启动

systemctl list-unit-files|grep mysqld.service

• 如不是enabled可以运行如下命令设置自启动

systemctl enable mysqld.service

• 如果希望不进行自启动，运行如下命令设置

systemctl disable mysqld.service

3. MySQL登录

3.1 首次登录

通过mysql -hlocalhost -P3306 -uroot -p进行登录，在Enter password：录入初始化密码

3.2 修改密码

因为初始化密码默认是过期的，所以查看数据库会报错

修改密码：

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

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

3.3 设置远程登录

如果尝试远程连接会出现如下问题：
在用SQLyog或Navicat中配置远程连接Mysql数据库时遇到如下报错信息，这是由于Mysql配置了不支持远 程连接引起的。

1. 确认网络

1.在远程机器上使用ping ip地址保证网络畅通

2.在远程机器上使用telnet命令保证端口号开放访问

telnet ip地址 端口号

拓展： telnet命令开启 :

2. 关闭防火墙或开放端口

方式一：关闭防火墙

• CentOS6 ：

service iptables stop

• CentOS7：

#开启防火墙
systemctl start firewalld.service
#查看防火墙状态
systemctl status firewalld.service
#关闭防火墙
systemctl stop firewalld.service
#设置开机启用防火墙 
systemctl enable firewalld.service 
#设置开机禁用防火墙 
systemctl disable firewalld.service

方式二：开放端口

• 查看开放的端口号

firewall-cmd --list-all

• 设置开放的端口号

firewall-cmd --add-service=http --permanent
firewall-cmd --add-port=3306/tcp --permanent

• 重启防火墙

firewall-cmd --reload

4. Linux下修改配置

• 修改允许远程登陆

use mysql;
select Host,User from user;

可以看到root用户的当前主机配置信息为localhost。

• 改Host为通配符%

Host列指定了允许用户登录所使用的IP，比如user=root Host=192.168.1.1。这里的意思就是说root用户只能通过192.168.1.1的客户端去访问。 user=root Host=localhost，表示只能通过本机客户端去访问。而% 是个通配符，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连 接。如果Host=% ，表示所有IP都有连接权限。

update user set host = '%' where user ='root';
flush privileges;  -- Host修改完成后记得执行flush, privileges使配置立即生效：

Host设置了“%”后便可以允许远程访问。

%是个 通配符 ，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果Host=%，表示所有IP都有连接权限。

注意：在生产环境下不能为了省事将host设置为%，这样做会存在安全问题，具体的设置可以根据生产环境的IP进行设置。

配置新连接报错：错误号码 2058，分析是 mysql 密码加密方法变了。

解决方法一：升级远程连接工具版本

解决方法二：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';

4. MySQL8的密码强度评估（了解）

4.1 MySQL不同版本设置密码(可能出现)

• MySQL5.7中：成功

mysql> alter user 'root' identified by 'abcd1234';
Query OK, 0 rows affected (0.00 sec)

• MySQL8.0中：失败

mysql> alter user 'root' identified by 'abcd1234'; # HelloWorld_123
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

4.2 MySQL8之前的安全策略

在MySQL 8.0之前，MySQL使用的是validate_password插件检测、验证账号密码强度，保障账号的安全
性。

安装/启用插件方式1：在参数文件my.cnf中添加参数:

[mysqld]

plugin-load-add=validate_password.so

#ON/OFF/FORCE/FORCE_PLUS_PERMANENT: 是否使用该插件(及强制/永久强制使用)

validate-password=FORCE_PLUS_PERMANENT

---

说明1： plugin library中的validate_password文件名的后缀名根据平台不同有所差异。 对于Unix和
Unix-like系统而言，它的文件后缀名是.so，对于Windows系统而言，它的文件后缀名是.dll。
说明2： 修改参数后必须重启MySQL服务才能生效。
说明3： 参数FORCE_PLUS_PERMANENT是为了防止插件在MySQL运行时的时候被卸载。当你卸载插
件时就会报错。如下所示。

mysql> SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION
-> FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE PLUGIN_NAME = 'validate_password';
+-------------------+----------------------+---------------+----------------------+
| PLUGIN_NAME | PLUGIN_LIBRARY | PLUGIN_STATUS | LOAD_OPTION |
+-------------------+----------------------+---------------+----------------------+
| validate_password | validate_password.so | ACTIVE | FORCE_PLUS_PERMANENT |
+-------------------+----------------------+---------------+----------------------+
1 row in set (0.00 sec)
mysql> UNINSTALL PLUGIN validate_password;
ERROR 1702 (HY000): Plugin 'validate_password' is force_plus_permanent and can not be
unloaded
mysql>

安装/启用插件方式2：运行时命令安装（推荐）

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected, 1 warning (0.11 sec)

此方法也会注册到元数据，也就是mysql.plugin表中，所以不用担心MySQL重启后插件会失效。

4.3 MySQL8的安全策略

1. validate_password说明

MySQL 8.0，引入了服务器组件（Components）这个特性，validate_password插件已用服务器组件重新实
现。8.0.25版本的数据库中，默认自动安装validate_password组件。

未安装插件前，执行如下两个指令 ，执行效果：

mysql> show variables like 'validate_password%';
Empty set (0.04 sec)
mysql> SELECT * FROM mysql.component;
ERROR 1146 (42S02): Table 'mysql.component' doesn't exist

安装插件后，执行如下两个指令 ，执行效果：

mysql> SELECT * FROM mysql.component;
+--------------+--------------------+------------------------------------+
| component_id | component_group_id | component_urn |
+--------------+--------------------+------------------------------------+
| 1 | 1 | file://component_validate_password |
+--------------+--------------------+------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

关于 validate_password 组件对应的系统变量说明：

提示：
组件和插件的默认值可能有所不同。例如，MySQL 5.7. validate_password_check_user_name的默认值为OFF。

2. 修改安全策略
   修改密码验证安全强度

SET GLOBAL validate_password_policy=LOW;
SET GLOBAL validate_password_policy=MEDIUM;
SET GLOBAL validate_password_policy=STRONG;
SET GLOBAL validate_password_policy=0; # For LOW
SET GLOBAL validate_password_policy=1; # For MEDIUM
SET GLOBAL validate_password_policy=2; # For HIGH
#注意，如果是插件的话,SQL为set global validate_password_policy=LOW

此外，还可以修改密码中字符的长度

set global validate_password_length=1;

3. 密码强度测试

   如果你创建密码是遇到“Your password does not satisfy the current policy requirements”，可以通过函数组件去检测密码是否满足条件： 0-100。当评估在100时就是说明使用上了最基本的规则：大写+小写+特殊
   字符+数字组成的8位以上密码

   mysql> SELECT VALIDATE_PASSWORD_STRENGTH('medium');
   +--------------------------------------+
   | VALIDATE_PASSWORD_STRENGTH('medium') |
   +--------------------------------------+
   | 25 |
   +--------------------------------------+
   1 row in set (0.00 sec)
   

   mysql> SELECT VALIDATE_PASSWORD_STRENGTH('K354*45jKd5');
   +-------------------------------------------+
   | VALIDATE_PASSWORD_STRENGTH('K354*45jKd5') |
   +-------------------------------------------+
   | 100 |
   +-------------------------------------------+
   1 row in set (0.00 sec)
   

注意：如果没有安装validate_password组件或插件的话，那么这个函数永远都返回0。 关于密码复杂度对
应的密码复杂度策略。如下表格所示：

4.4 卸载插件、组件(了解)

卸载插件

mysql> UNINSTALL PLUGIN validate_password;
Query OK, 0 rows affected, 1 warning (0.01 sec)

卸载组件

mysql> UNINSTALL COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (0.02 sec)

5. 字符集的相关操作

5.1 修改步骤

在MySQL 8.0版本之前，默认字符集为latin1 ，utf8字符集指向的是utf8mb3 。网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，往数据库中插入的数据就会出现乱码的问题。从MySQL8.0开始，数据库的默认编码将改为utf8mb4 ，从而避免上述乱码的问题。

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

show variables like 'character%'; 

# 或者

show variables like '%char%';

• MySQL8.0中执行：

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

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

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

操作2：修改字符集

vim /etc/my.cnf

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

character_set_server=utf8

操作3：重新启动MySQL服务

systemctl restart mysqld

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

2. 已有库&表字符集的变更
   MySQL5.7版本中，以前创建的库，创建的表字符集还是latin1。

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

alter database dbtest1 character set 'utf8';

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

alter table t_emp convert to character set 'utf8';

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

5.2 各级别的字符集

MySQL有4个级别的字符集和比较规则，分别是：

• 服务器级别

• 数据库级别

• 表级别

• 列级别

  执行如下SQL语句:

show variables like 'character%';

• character_set_server：服务器级别的字符集
• character_set_database：当前数据库的字符集
• character_set_client：服务器解码请求时使用的字符集
• character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
• character_set_results：服务器向客户端返回数据时使用的字符集

1. 服务器级别

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

我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET 语句修改这两个变量
的值。比如我们可以在配置文件中这样写：

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

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

2. 数据库级别

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

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

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

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

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

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

4. 列级别

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

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

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

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

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

小结

• 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
• 如果创建表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
• 如果创建数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

知道了这些规则之后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据
这个列的类型来确定存储数据时每个列的实际数据占用的存储空间大小了。比方说我们向表 t 中插入一
条记录：

mysql> INSERT INTO t(col) VALUES('我们');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t;
+--------+
| s |
+--------+
| 我们 |
+--------+
1 row in set (0.00 sec

首先列 col 使用的字符集是 gbk ，一个字符 '我' 在 gbk 中的编码为 0xCED2 ，占用两个字节，两个字
符的实际数据就占用4个字节。如果把该列的字符集修改为 utf8 的话，这两个字符就实际占用6个字节

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

1. utf8 与 utf8mb4

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

• utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。(MySQL5.7中说的utf8全称就是utf8mb3 )
• utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符。如果大家有使用4字节编码来表示一个字符的情况，比如存储一些emoji表情，那请使用utf8mb4。

2. 比较规则

上表中，MySQL版本一共支持41种字符集，其中的 Default collation 列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则比较， utf8_spanish_ci 是以西班牙语的规则比较， utf8_general_ci 是一种通用的比较规则。

后缀表示该比较规则是否区分语言中的重音、大小写。具体如下：

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

常用操作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'

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

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

mysql> set character_set_connection = gbk;
Query OK, 0 rows affected (0.00 sec)

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

SELECT * FROM t WHERE s = '我';

为了方便大家理解这个过程，我们只分析字符 '我' 在这个过程中字符集的转换。

现在看一下在请求从发送到结果返回过程中字符集的变化：

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

6. SQL大小写规范

graph TB A(客户端) --> |"使用操作系统的字符集编码请求字符串"| B(从character_set_client转换为character_set_connection) B --> C(从character_set_connection转换为具体的列使用的字符集) C --> D(将查询结果从具体的列上使用的字符集转换为character_set_results) D --> |"使用操作系统的字符集解码响应的字符串"| A

一一一一一一一一一一

【第02章 MySQL的数据目录】

1. MySQL8的主要目录结构

find / -name mysql

1.1 数据库文件的存放路径

show variables like 'datadir'; # /var/lib/mysql/

1.2 相关命令目录

相关命令目录：/usr/bin 和/usr/sbin。

1.3 配置文件目录

配置文件目录：/usr/share/mysql-8.0（命令及配置文件），/etc/mysql（如my.cnf）

2. 数据库和文件系统的关系

2.1 表在文件系统中的表示

2.3.1 InnoDB存储引擎模式

1. 表结构

为了保存表结构，InnoDB在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件

表名.frm

2. 表中数据和索引

① 系统表空间（system tablespace）

默认情况下，InnoDB会在数据目录下创建一个名为ibdata1、大小为12M的自拓展文件，这个文件就是对应的系统表空间在文件系统上的表示。

② 独立表空间(file-per-table tablespace)

在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同。

表名.ibd

MySQL8.0中不再单独提供表名.frm，而是合并在表名.ibd文件中。

③ 系统表空间与独立表空间的设置

我们可以自己指定使用系统表空间还是独立表空间来存储数据，这个功能由启动参数innodb_file_per_table控制

[server] 
innodb_file_per_table=0 # 0：代表使用系统表空间； 1：代表使用独立表空间

④ 其他类型的表空间

随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间（general tablespace）、临时表空间（temporary tablespace）等。

2.3.2 MyISAM存储引擎模式

1. 表结构

在存储表结构方面， MyISAM 和 InnoDB 一样，也是在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件

表名.frm

2. 表中数据和索引

在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下。

test.frm 存储表结构 #MySQL8.0 改为了 b.xxx.sdi
test.MYD 存储数据 (MYData) 
test.MYI 存储索引 (MYIndex

一一一一一一一一一一一

【第03章 用户与权限管理【

1. 用户管理

1.1 登录MySQL服务器

启动MySQL服务后，可以通过mysql命令来登录MySQL服务器，命令如下：

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"

• -h参数后面接主机名或者主机IP，hostname为主机，hostIP为主机IP。
• -P参数后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口，port为连接的端口号。
• -u参数后面接用户名，username为用户名。
• -p参数会提示输入密码。
• DatabaseName参数指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库。
• -e参数后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器。

mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"

1.2 创建用户

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];

举例：

CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是 %
CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';

1.3 修改用户

UPDATE mysql.user SET USER='li4' WHERE USER='wang5'; 
FLUSH PRIVILEGES;

1.4 删除用户

方式1：使用DROP方式删除（推荐）

DROP USER user[,user]…;

举例：

DROP USER li4 ; # 默认删除host为%的用户
DROP USER 'kangshifu'@'localhost';

方式2：使用DELETE方式删除（不推荐，有残留信息）

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
FLUSH PRIVILEGES;

注意：不推荐通过 DELETE FROM USER u WHERE USER='li4' 进行删除，系统会有残留信息保
留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表
的相应记录都消失了。

1.5 设置当前用户密码

旧的写法如下 ：(不推荐)

# 修改当前用户的密码：（MySQL5.7测试有效）
SET PASSWORD = PASSWORD('123456');

这里介绍 推荐的写法 ：

1. 使用ALTER USER命令来修改当前用户密码

ALTER USER USER() IDENTIFIED BY 'new_password';

2. 使用SET语句来修改当前用户密码

SET PASSWORD='new_password';

1.6 修改其它用户密码

1. 使用ALTER语句来修改普通用户的密码

ALTER USER user [IDENTIFIED BY '新密码'] 
[,user[IDENTIFIED BY '新密码']]…;

2. 使用SET命令来修改普通用户的密码

SET PASSWORD FOR 'username'@'hostname'='new_password';

3. 使用UPDATE语句修改普通用户的密码（不推荐）

   UPDATE MySQL.user SET authentication_string=PASSWORD("123456")
   WHERE User = "username" AND Host = "hostname";
   

2. 权限管理

2.1 权限列表

show privileges;

• CREATE和DROP权限，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。
• SELECT、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表上实施操作。
• SELECT权限只有在它们真正从一个表中检索行时才被用到。
• INDEX权限允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。
• ALTER权限可以使用ALTER TABLE来更改表的结构和重新命名表。
• CREATE ROUTINE权限用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序。
• GRANT权限允许授权给其他用户，可用于数据库、表和保存的程序。
• FILE权限使用户可以使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。

2.2 授予权限的原则

权限控制主要是出于安全因素，因此需要遵循以下几个经验原则：

1、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。

2、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。

3、为每个用户设置满足密码复杂度的密码。

4、定期清理不需要的用户，回收权限或者删除用户。

2.3 授予权限

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

• 该权限如果发现没有该用户，则会直接新建一个用户。
• 给li4用户用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。

GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost;

• 授予通过网络方式登录的joe用户 ，对所有库所有表的全部权限，密码设为123。注意这里唯独不包括grant的权限

GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';

2.4 查看权限

• 查看当前用户权限

SHOW GRANTS; 
# 或 
SHOW GRANTS FOR CURRENT_USER; 
# 或 
SHOW GRANTS FOR CURRENT_USER();

• 查看某用户的全局权限

SHOW GRANTS FOR 'user'@'主机地址';

2.5 收回权限

注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。

• 收回权限命令

REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;

• 举例

#收回全库全表的所有权限 
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%'; 
#收回mysql库下的所有表的插删改查权限 
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

• 注意：须用户重新登录后才能生效

3. 权限表

3.1 user表

user表是MySQL中最重要的一个权限表， 记录用户账号和权限信息 ，有49个字段。如下图：

这些字段可以分成4类，分别是范围列（或用户列）、权限列、安全列和资源控制列。

1.范围列（或用户列）

• host ： 表示连接类型
  • % 表示所有远程通过 TCP方式的连接
  • IP 地址 如 (192.168.1.2、127.0.0.1) 通过制定ip地址进行的TCP方式的连接
  • 机器名 通过制定网络中的机器名进行的TCP方式的连接
  • ::1 IPv6的本地ip地址，等同于IPv4的 127.0.0.1
  • localhost 本地方式通过命令行方式的连接 ，比如mysql -u xxx -p xxx 方式的连接。
• user ： 表示用户名，同一用户通过不同方式链接的权限是不一样的。
• password ： 密码
  • 所有密码串通过 password(明文字符串) 生成的密文字符串。MySQL 8.0 在用户管理方面增加了
    角色管理，默认的密码加密方式也做了调整，由之前的 SHA1 改为了 SHA2 ，不可逆 。同时
    加上 MySQL 5.7 的禁用用户和用户过期的功能，MySQL 在用户管理方面的功能和安全性都较之
    前版本大大的增强了。
  • mysql 5.7 及之后版本的密码保存到 authentication_string 字段中不再使用password 字段。

2. 权限列

• Grant_priv字段

  ​ 表示是否拥有GRANT权限

• Shutdown_priv字段

  ​ 表示是否拥有停止MySQL服务的权限

• Super_priv字段

  ​ 表示是否拥有超级权限

• Execute_priv字段

  ​ 表示是否拥有EXECUTE权限。拥有EXECUTE权限，可以执行存储过程和函数。

• Select_priv , Insert_priv等

  ​ 为该用户所拥有的权限。

3. 安全列

安全列只有6个字段，其中两个是ssl相关的（ssl_type、ssl_cipher），用于 加密 ；两个是x509相关的（x509_issuer、x509_subject），用于 标识用户 ；另外两个Plugin字段用于 验证用户身份 的插件，该字段不能为空。如果该字段为空，服务器就使用内建授权验证机制验证用户身份。

4. 资源控制列

资源控制列的字段用来 限制用户使用的资源 ，包含4个字段，分别为：

①max_questions，用户每小时允许执行的查询操作次数；

②max_updates，用户每小时允许执行的更新操作次数；

③max_connections，用户每小时允许执行的连接操作次数；

④max_user_connections，用户允许同时建立的连接次数。

查看字段：

DESC mysql.user;

查看用户, 以列的方式显示数据：

SELECT * FROM mysql.user \G;

查询特定字段：

SELECT host,user,authentication_string,select_priv,insert_priv,drop_priv
FROM mysql.user

3.2 db表

使用DESCRIBE查看db表的基本结构：

DESCRIBE mysql.db;

1. 用户列

​ db表用户列有3个字段，分别是Host、User、Db。这3个字段分别表示主机名、用户名和数据库名。表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。

2. 权限列

​ Create_routine_priv和Alter_routine_priv这两个字段决定用户是否具有创建和修改存储过程的权限。

3.3 tables_priv表和columns_priv表

tables_priv表用来 对表设置操作权限 ，columns_priv表用来对表的 某一列设置权限 。tables_priv表和columns_priv表的结构分别如图：

desc mysql.tables_priv;

tables_priv表有8个字段，分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和
Column_priv，各个字段说明如下：

• Host 、 Db 、 User 和 Table_name 四个字段分别表示主机名、数据库名、用户名和表名。
• Grantor表示修改该记录的用户。
• Timestamp表示修改该记录的时间。
• Table_priv 表示对象的操作权限。包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index和Alter。
• Column_priv字段表示对表中的列的操作权限，包括Select、Insert、Update和References。

3.4 procs_priv表

procs_priv表可以对 存储过程和存储函数设置操作权限 ，表结构如图：

desc mysql.procs_priv;

4. 访问控制(了解)

4.1 连接核实阶段

5. 角色管理

5.0 角色的理解

引入角色的目的是 方便管理拥有相同权限的用户 。恰当的权限设定，可以确保数据的安全性，这是至关
重要的

5.1 创建角色

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

角色名称的命名规则和用户名类似。如果host_name省略，默认为%，role_name不可省略，不可为空。

5.2 给角色赋予权限

创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。给角色授权的语法结构是：

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

上述语句中privileges代表权限的名称，多个权限以逗号隔开。可使用SHOW语句查询权限名称，图11-43
列出了部分权限列表。

SHOW PRIVILEGES\G

GRANT SELECT ON demo.settlement TO 'manager';
GRANT SELECT ON demo.goodsmaster TO 'manager';
GRANT SELECT ON demo.invcount TO 'manager';

5.3 查看角色的权限

赋予角色权限之后，我们可以通过 SHOW GRANTS 语句，来查看权限是否创建成功了：

mysql> SHOW GRANTS FOR 'manager';
+-------------------------------------------------------+
| Grants for manager@% |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%` |
| GRANT SELECT ON `demo`.`goodsmaster` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`invcount` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`settlement` TO `manager`@`%` |
+-------------------------------------------------------+

只要你创建了一个角色，系统就会自动给你一个“ USAGE ”权限，意思是 连接登录数据库的权限 。代码的最后三行代表了我们给角色“manager”赋予的权限，也就是对商品信息表、盘点表和应付账款表的只读权限。

结果显示，库管角色拥有商品信息表的只读权限和盘点表的增删改查权限。

5.4 回收角色的权限

角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句。

修改了角色的权限，会影响拥有该角色的账户的权限。

撤销角色权限的SQL语法如下：

REVOKE privileges ON tablename FROM 'rolename';

练习1：撤销school_write角色的权限。 （1）使用如下语句撤销school_write角色的权限。

REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write';

（2）撤销后使用SHOW语句查看school_write对应的权限，语句如下。

SHOW GRANTS FOR 'school_write';

5.5 删除角色

当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角
色。删除角色的操作很简单，你只要掌握语法结构就行了。

DROP ROLE role [,role2]...

注意，如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限。

3.6 给用户赋予角色

角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。给用户添加角色可使用GRANT语句，语
法形式如下：

GRANT role [,role2,...] TO user [,user2,...];

在上述语句中，role代表角色，user代表用户。可将多个角色同时赋予多个用户，用逗号隔开即可。
练习：给kangshifu用户添加角色school_read权限。

（1）使用GRANT语句给kangshifu添加school_read权限，SQL语句如下。

GRANT 'school_read' TO 'kangshifu'@'localhost';

（2）添加完成后使用SHOW语句查看是否添加成功，SQL语句如下。

SHOW GRANTS FOR 'kangshifu'@'localhost';

（3）使用kangshifu用户登录，然后查询当前角色，如果角色未激活，结果将显示NONE。SQL语句如
下。

查询当前已激活的角色

SELECT CURRENT_ROLE();

3.7 激活角色

方式1：使用set default role 命令激活角色

SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';

举例：使用 SET DEFAULT ROLE 为下面4个用户默认激活所有已拥有的角色如下：

SET DEFAULT ROLE ALL TO
'dev1'@'localhost',
'read_user1'@'localhost',
'read_user2'@'localhost',
'rw_user1'@'localhost';

方式2：将activate_all_roles_on_login设置为ON

• 默认情况：

  mysql> show variables like 'activate_all_roles_on_login';
  +-----------------------------+-------+
  | Variable_name | Value |
  +-----------------------------+-------+
  | activate_all_roles_on_login | OFF |
  +-----------------------------+-------+
  1 row in set (0.00 sec)
  

SET GLOBAL activate_all_roles_on_login=ON;

这条 SQL 语句的意思是，对所有角色永久激活。运行这条语句之后，用户才真正拥有了赋予角色的所有
权限。

5.8 撤销用户的角色

REVOKE role FROM user;

5.9 设置强制角色(mandatory role)

方式1：服务启动前设置

[mysqld] 
mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'

方式2：运行时设置

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后失效

6.配置文件的使用

6.1 配置文件格式

7. 系统变量（复习）

第04章 逻辑架构

1. 逻辑架构剖析

上图是大概的，下面具体展开看一下：

1.1 第1层：连接层

系统（客户端）访问MySQL服务器前，做的第一件事就是建立TCP连接。

经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。

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

TCP连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

1.2 第2层：服务层

• 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中删除。

1.3 第3层：引擎层

插件式存储引擎层（ Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务层通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样 我们可以根据自己的实际需要进行选取。
MySQL 8.0.25默认支持的存储引擎如下：

1.4 存储层

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

1.5 小结

MySQL架构图本节开篇所示。下面为了熟悉SQL执行流程方便，我们可以简化如下：

简化为三层结构：

1. 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；

2. SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；

3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。

2. SQL执行流程

2.1 MySQL 中的 SQL执行流程

MySQL的查询流程：

1. 查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。

查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。这就意味着查询匹配的鲁棒性大大降低，只有相同的查询操作才会命中查询缓存。两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。因此 MySQL 的 查询缓存命中率不高 。

同时，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql、 information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。

此外，既然是缓存，那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！对于更新压力大的数据库来说，查询缓存的命中率会非常低。

查询缓存的命中率：

show status like '%Qcache%';

2. 解析器：在解析器中对 SQL 语句进行语法分析、语义分析。

分析器先做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。 MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

接着，要做“语法分析”。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果SQL语句正确，则会生成一个语法树。

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

3. 优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引检索等。在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。

举例：如下语句是执行两个表的 join：

select * from test1 join test2 using(ID)
where test1.name='zhangwei' and test2.name='mysql高级课程';

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

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

如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等。后面讲到索引我们再谈。

在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。

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

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

select * from test where id=1;

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

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

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

至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多。
SQL 语句在 MySQL 中的流程是：SQL语句→查询缓存→解析器→优化器→执行器。

2.2 MySQL-8中的SQL执行原理

1、确认profiling 是否开启

mysql> select @@profiling;
mysql> show variables like 'profiling';

上边两种方式都行，只不过显示的值类型不一样，一个是boolean一个是0/1；如下图：

profiling=0 代表关闭，我们需要把 profiling 打开，即设置为 1：

mysql> set profiling=1;

这样设置之后我们再去执行sql的时候，就会记录sql的执行过程

2. 多次执行相同SQL查询
   然后我们执行一个 SQL 查询（你可以执行任何一个 SQL 查询）：

mysql> select * from employees;

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

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

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

mysql> show profile;

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

mysql> show profile for query 7;

查询 SQL 的执行时间结果和上面是一样的。
此外，还可以查询更丰富的内容：

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

继续：

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

2.3 MySQL5.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;
   mysql> select * from locations;
   

5. 查看profiles

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

查看cpu、io阻塞、、、等开销

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 的执行结果。这样做的好处是能 提升软解
析的可能性 ，不足之处在于可能会导致生成的执行计划不够优化，因此是否需要绑定变量还需要视情况
而定。
举个例子，我们可以使用下面的查询语句：

SQL> select * from player where player_id = 10001;

你也可以使用绑定变量，如：

SQL> select * from player where player_id = :player_id

这两个查询语句的效率在 Oracle 中是完全不同的。如果你在查询 player_id = 10001 之后，还会查询
10002、10003 之类的数据，那么每一次查询都会创建一个新的查询解析。而第二种方式使用了绑定变
量，那么在第一次查询之后，在共享池中就会存在这类查询的执行计划，也就是软解析。
因此，我们可以通过使用绑定变量来减少硬解析，减少 Oracle 的解析工作量。但是这种方式也有缺点，
使用动态 SQL 的方式，因为参数不同，会导致 SQL 的执行效率不同，同时 SQL 优化也会比较困难。

Oracle的架构图：

简图

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

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

InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。而磁盘 I/O 需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。

这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间。要知道，这种策略对提升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。

3.1 缓冲池 vs 查询缓存

1. 缓冲池（Buffer Pool）

从图中，你能看到 InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典信息等。

缓存原则：

“位置 * 频次”这个原则，可以帮我们对 I/O 访问效率进行优化。

首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。

其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会优先对使用频次高的热数据进行加载。

2. 查询缓存

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

3.2 缓冲池如何读取数据

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

3.3 查看/设置缓冲池的大小

查看缓冲池的大小

show variables like 'innodb_buffer_pool_size';

默认是128MB

设置缓冲池的大小

set global innodb_buffer_pool_size = 268435456;

或者

[server] 
innodb_buffer_pool_size = 268435456

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实例的个数，比方说这样:

[server] 
innodb_buffer_pool_instances = 2
# 注意 ：这是将上面总的128MB的缓存池拆分为两个64MB的缓存池

这样就表明我们要创建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内存结构中十分核心的一个组成，你可以先把它想象成一个黑盒子。
黑盒下的更新数据流程

我更新到一半突然发生错误了，想要回滚到更新之前的版本，该怎么办？连数据持久化的保证、事务回 滚都做不到还谈什么崩溃恢复？
答案：Redo Log & Undo Log

第05章 存储引擎

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

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

1. 查看存储引擎

show engines;
# 或
show engines\G

2. 设置系统默认的存储引擎

• 查看默认的存储引擎：

show variables like '%storage_engine%'; 
#或
SELECT @@default_storage_engine;

• 修改默认的存储引擎

如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用InnoDB作为表的存储引擎。

如果我们想改变表的默认存储引擎的话，可以这样写启动服务器的命令行：

SET DEFAULT_STORAGE_ENGINE=MyISAM;

或者修改my.cnf文件：

default-storage-engine=MyISAM 

# 重启服务 
systemctl restart mysqld.service

3. 设置表的存储引擎

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

3.1 创建表时指定存储引擎

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

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

3.2 修改表的存储引擎

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

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

比如我们修改一下engine_demo_table 表的存储引擎：

mysql> ALTER TABLE engine_demo_table ENGINE = InnoDB; Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

4. 引擎介绍

4.1 InnoDB 引擎：具备外键支持功能的事务存储引擎

• MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后，默认采用InnoDB引擎。
• InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
• 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。
• 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
• 数据文件结构：（在《第02章_MySQL数据目录》章节已讲）
  • 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）
  • 表名.ibd 存储数据和索引
• InnoDB是为处理巨大数据量的最大性能设计。
  • 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比如：.frm，.par，.trn，.isl，.db.opt等都在MySQL8.0中不存在了。
• 对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。
• MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。

4.2 MyISAM 引擎：主要的非事务处理存储引擎

• MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
• 5.5之前默认的存储引擎
• 优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用
• 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
• 数据文件结构：（在《第02章_MySQL数据目录》章节已讲）
  • 表名.frm 存储表结构
  • 表名.MYD 存储数据 (MYData)
  • 表名.MYI 存储索引 (MYIndex)
• 应用场景：只读应用或者以读为主的业务

4.3 Archive 引擎：用于数据存档

特征	支持
B树索引	不支持
备份/时间点恢复 （在服务器中实现，而不是在存储引擎中）	支持
集群数据库支持	不支持
聚集索引	不支持
压缩数据	支持
数据缓存	不支持
加密数据（加密功能在服务器中实现）	支持
外键支持	不支持
全文检索索引	不支持
地理空间数据类型支持	支持
地理空间索引支持	不支持
哈希索引	不支持
索引缓存	不支持
锁粒度	行锁
MVCC	不支持
存储限制	没有任何限制
交易	不支持
更新数据字典的统计信息	支持

4.4 Blackhole 引擎：丢弃写操作，读操作会返回空内容

4.5 CSV 引擎：存储数据时，以逗号分隔各个数据项

使用案例如下:

mysql> CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM test;
+---+------------+
| i | c |
+---+------------+
| 1 | record one |
| 2 | record two |
+---+------------+
2 rows in set (0.00 sec)

创建CSV表还会创建相应的 元文件 ，用于 存储表的状态 和 表中存在的行数 。此文件的名称与表的名称相
同，后缀为 CSM 。如图所示

如果检查 test.CSV 通过执行上述语句创建的数据库目录中的文件，其内容使用Notepad++打开如下：

"1","record one"
"2","record two"

这种格式可以被 Microsoft Excel 等电子表格应用程序读取，甚至写入。使用Microsoft Excel打开如图所示

4.6 Memory 引擎：置于内存的表

概述：
Memory采用的逻辑介质是内存，响应速度很快，但是当mysqld守护进程崩溃的时候数据会丢失。另 外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。

主要特征：

• Memory同时支持哈希（HASH）索引和B+树索引。
• Memory表至少比MyISAM表要快一个数量级。
• MEMORY 表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows 和
  max_heap_table_size 。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默 认为16MB，可以按需要进行扩大。
• 数据文件与索引文件分开存储。
• 缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。

使用Memory存储引擎的场景：

1. 目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢
   出。可以通过参数max_heap_table_size 控制Memory表的大小，限制Memory表的最大的大 小。
2. 如果数据是临时的，而且必须立即可用得到，那么就可以放在内存中。
3. 存储在Memory表中的数据如果突然间 丢失的话也没有太大的关系 。

4.7 Federated 引擎：访问远程表

Federated引擎是访问其他MySQL服务器的一个 代理 ，尽管该引擎看起来提供了一种很好的 跨服务
器的灵活性 ，但也经常带来问题，因此 默认是禁用的 。

4.8 Merge引擎：管理多个MyISAM表构成的表集合

4.9 NDB引擎：MySQL集群专用存储引擎

也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群 环境，类似于 Oracle 的 RAC 集
群。

4.10 引擎对比

MySQL中同一个数据库，不同的表可以选择不同的存储引擎。如下表对常用存储引擎做出了对比。

特 点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
事务安全		支持
锁机制	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持		支持
索引缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外键		支持

其实这些东西大家没必要立即就给记住，列出来的目的就是想让大家明白不同的存储引擎支持不同的功能。

其实我们最常用的就是 InnoDB 和 MyISAM ，有时会提一下 Memory 。其中 InnoDB 是 MySQL 默认的存储引擎。

5. MyISAM和InnoDB

很多人对 InnoDB 和 MyISAM 的取舍存在疑问，到底选择哪个比较好呢？
MySQL5.5之前的默认存储引擎是MyISAM，5.5之后改为了InnoDB。

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
自带系统表使用	Y	N
关注点	性能：节省资源、消耗少、简单业务	事务：并发写、事务、更大资源
默认安装	Y	Y
默认使用	N	Y

6. 阿里巴巴、淘宝用哪个

• Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有很显著的提升。
• 该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外 有更多的参数和命令来控制服务器行为。
• 该公司新建了一款存储引擎叫Xtradb 完全可以替代Innodb ，并且在性能和并发上做得更好
• 阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。

课外补充：

1、InnoDB表的优势

InnoDB存储引擎在实际应用中拥有诸多优势，比如操作便利、提高了数据库的性能、维护成本低等。如果由于硬件或软件的原因导致服务器崩溃，那么在重启服务器之后不需要进行额外的操作。InnoDB崩溃恢复功能自动将之前提交的内容定型，然后撤销没有提交的进程，重启之后继续从崩溃点开始执行。InnoDB存储引擎在主内存中维护缓冲池，高频率使用的数据将在内存中直接被处理。这种缓存方式应用于多种信息，加速了处理进程。

在专用服务器上，物理内存中高达80%的部分被应用于缓冲池。如果需要将数据插入不同的表中，可以设置外键加强数据的完整性。更新或者删除数据，关联数据将会被自动更新或删除。如果试图将数据插入从表，但在主表中没有对应的数据，插入的数据将被自动移除。如果磁盘或内存中的数据出现崩溃，在使用脏数据之前，校验和机制会发出警告。当每个表的主键都设置合理时，与这些列有关的操作会被自动优化。插入、更新和删除操作通过做改变缓冲自动机制进行优化。 InnoDB不仅支持当前读写，也会缓冲改变的数据到数据流磁盘 。

InnoDB的性能优势不只存在于长时运行查询的大型表。在同一列多次被查询时，自适应哈希索引会提高查询的速度。使用InnoDB可以压缩表和相关的索引，可以 在不影响性能和可用性的情况下创建或删除索引 。对于大型文本和BLOB数据，使用动态行形式，这种存储布局更高效。通过查询INFORMATION_SCHEMA库中的表可以监控存储引擎的内部工作。在同一个语句中，InnoDB表可以与其他存储引擎表混用。即使有些操作系统限制文件大小为2GB，InnoDB仍然可以处理。 当处理大数据量时，InnoDB兼顾CPU，以达到最大性能 。

2、InnoDB和ACID模型

ACID模型是一系列数据库设计规则，这些规则着重强调可靠性，而可靠性对于商业数据和任务关键型应用非常重要。MySQL包含类似InnoDB存储引擎的组件，与ACID模型紧密相连，这样出现意外时，数据不会崩溃，结果不会失真。如果依赖ACID模型，可以不使用一致性检查和崩溃恢复机制。如果拥有额外的软件保护，极可靠的硬件或者应用可以容忍一小部分的数据丢失和不一致，可以将MySQL设置调整为只依赖部分ACID特性，以达到更高的性能。下面讲解InnoDB存储引擎与ACID模型相同作用的四个方面。

1. 原子方面 ACID的原子方面主要涉及InnoDB事务，与MySQL相关的特性主要包括：

• 自动提交设置。
• COMMIT语句。
• ROLLBACK语句。
• 操作INFORMATION_SCHEMA库中的表数据。

2. 一致性方面 ACID模型的一致性主要涉及保护数据不崩溃的内部InnoDB处理过程，与MySQL相关的特性主要包括：

• InnoDB双写缓存。
• InnoDB崩溃恢复。

3. 隔离方面 隔离是应用于事务的级别，与MySQL相关的特性主要包括：

• 自动提交设置。
• SET ISOLATION LEVEL语句。
• InnoDB锁的低级别信息。

4. 耐久性方面 ACID模型的耐久性主要涉及与硬件配置相互影响的MySQL软件特性。由于硬件复杂多样化，耐久性方面没有具体的规则可循。与MySQL相关的特性有：

• InnoDB双写缓存，通过innodb_doublewrite配置项配置。
• 配置项innodb_flush_log_at_trx_commit。
• 配置项sync_binlog。
• 配置项innodb_file_per_table。
• 存储设备的写入缓存。
• 存储设备的备用电池缓存。
• 运行MySQL的操作系统。
• 持续的电力供应。
• 备份策略。
• 对分布式或托管的应用，最主要的在于硬件设备的地点以及网络情况

3、InnoDB架构

1. 缓冲池 缓冲池是主内存中的一部分空间，用来缓存已使用的表和索引数据。缓冲池使得经常被使用的
   数据能够直接在内存中获得，从而提高速度。
2. 更改缓存 更改缓存是一个特殊的数据结构，当受影响的索引页不在缓存中时，更改缓存会缓存辅助索
   引页的更改。索引页被其他读取操作时会加载到缓存池，缓存的更改内容就会被合并。不同于集群索
   引，辅助索引并非独一无二的。当系统大部分闲置时，清除操作会定期运行，将更新的索引页刷入磁
   盘。更新缓存合并期间，可能会大大降低查询的性能。在内存中，更新缓存占用一部分InnoDB缓冲池。
   在磁盘中，更新缓存是系统表空间的一部分。更新缓存的数据类型由innodb_change_buffering配置项管
   理。
3. 自适应哈希索引 自适应哈希索引将负载和足够的内存结合起来，使得InnoDB像内存数据库一样运行，
   不需要降低事务上的性能或可靠性。这个特性通过innodb_adaptive_hash_index选项配置，或者通过--
   skip-innodb_adaptive_hash_index命令行在服务启动时关闭。
4. 重做日志缓存 重做日志缓存存放要放入重做日志的数据。重做日志缓存大小通过
   innodb_log_buffer_size配置项配置。重做日志缓存会定期地将日志文件刷入磁盘。大型的重做日志缓存
   使得大型事务能够正常运行而不需要写入磁盘。
5. 系统表空间 系统表空间包括InnoDB数据字典、双写缓存、更新缓存和撤销日志，同时也包括表和索引
   数据。多表共享，系统表空间被视为共享表空间。
6. 双写缓存 双写缓存位于系统表空间中，用于写入从缓存池刷新的数据页。只有在刷新并写入双写缓存
   后，InnoDB才会将数据页写入合适的位置。
7. 撤销日志 撤销日志是一系列与事务相关的撤销记录的集合，包含如何撤销事务最近的更改。如果其他
   事务要查询原始数据，可以从撤销日志记录中追溯未更改的数据。撤销日志存在于撤销日志片段中，这
   些片段包含于回滚片段中。
8. 每个表一个文件的表空间 每个表一个文件的表空间是指每个单独的表空间创建在自身的数据文件中，
   而不是系统表空间中。这个功能通过innodb_file_per_table配置项开启。每个表空间由一个单独的.ibd数
   据文件代表，该文件默认被创建在数据库目录中。
9. 通用表空间 使用CREATE TABLESPACE语法创建共享的InnoDB表空间。通用表空间可以创建在MySQL数
   据目录之外能够管理多个表并支持所有行格式的表。
10. 撤销表空间 撤销表空间由一个或多个包含撤销日志的文件组成。撤销表空间的数量由
    innodb_undo_tablespaces配置项配置。
11. 临时表空间 用户创建的临时表空间和基于磁盘的内部临时表都创建于临时表空间。
    innodb_temp_data_file_path配置项定义了相关的路径、名称、大小和属性。如果该值为空，默认会在
    innodb_data_home_dir变量指定的目录下创建一个自动扩展的数据文件。
12. 重做日志 重做日志是基于磁盘的数据结构，在崩溃恢复期间使用，用来纠正数据。正常操作期间，
    重做日志会将请求数据进行编码，这些请求会改变InnoDB表数据。遇到意外崩溃后，未完成的更改会自
    动在初始化期间重新进行

标签：康师傅,缓存,SQL,MySQL,字符集,mysql,硅谷,权限
From： https://www.cnblogs.com/qipaoxian/p/16860254.html