十六、mysql详解系列（高级部分）

一、Linux下MySQL的安装与使用

1. 安装前说明

⑴ Linux系统及工具的准备

⑵ 克隆虚拟机的操作介绍

vmware中的完整克隆是基于指定的虚拟机克隆出相同的一份出来，不必再安装。
但是我们要保证几个地方不能一样，一个是主机名称（hostname），一个是虚拟网卡设备mac地址，还有就是是ip地址和UUID。
所以我们在克隆后要对这四个地方进行修改。

这里以centos为例：

1) 首先进行完全克隆，注意（要克隆的虚拟机在克隆前是需要处于关闭状态）

选择要克隆的虚拟机右键，选择管理，然后选择克隆

然后直到这一步选择完整克隆，注意：链接克隆是指在一些资源上两个虚拟机会共用，这里不做详细介绍，完整克隆是完全独立出来的一个新虚拟机。

然后下一步，虚拟机名称输入你想要的名称就完成了

注意，克隆完之后所有信息与原虚拟机一样，所以下面我们进行一些信息的修改

2) 开机前修改mac地址（注意：如果是动态生成IP地址，请在启动前先启动被克隆的虚拟机，以保证原来虚拟机的ip不会变，否则原来虚拟机ip会变，克隆后的虚拟机ip是原来的虚拟机ip）

点击编辑虚拟机设置,选择网络适配器，点击右下角高级然后下边就是mac地址，这个mac地址和被克隆的是一样的，我们点击生成，重新生成一个新的：

然后mac地址就修改完了

3) 开机后修改主机名称(这步不用改也可以)

修改主机名可能不同linux版本不同，修改方法也不同。centos7就是vim /etc/hostname 命令来编辑主机名(如果不好使，可以用下面的命令)

hostnamectl set-hostname 主机名

需要重启。（reboot）

4) 修改 IP地址 和 UUID

此处需要注意的是：如果虚拟机使用的是动态ip分配，那么不需要更改ip，如果想改为静态ip，请修改：

vim /etc/sysconfig/network-scripts/ifcfg-ens33

修改完成后请重启网络

systemctl restart network

⑶ 查看是否安装过MySQL

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

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

• 检查mysql service：

  systemctl status mysqld.service
  

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

• 如果不存在mysql-lib的版本，显示如下：
  

⑷ 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指定版本

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安装有两种安装程序
  

5) Linux系统下安装MySQL的几种方式

① Linux系统下安装软件的常用三种方式：

○ 方式1：rpm命令

使用rpm命令安装扩展名为".rpm"的软件包。

○ 方式2：yum命令

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

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

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

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

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

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

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

⑶ CentOS7下检查MySQL依赖

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

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

chmod -R 777 /tmp

2) 安装前，检查依赖

rpm -qa|grep libaio

• 如果存在libaio包如下：
  

rpm -qa|grep net-tools

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

rpm -qa|grep net-tools

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

⑷ 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 一起使用效果更好)，展示进度条

2) 安装过程截图

安装过程中可能的报错信息：

如果报如下错误，解决： 安装perl

[root@localhost opt]# yum install -y perl-Module-Install.noarch
[root@localhost opt]# yum install -y perl

3) 查看MySQL版本

执行如下命令，如果成功表示安装mysql成功。类似java -version如果打出版本等信息

mysql --version 
#或
mysqladmin --version

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

rpm -qa|grep -i mysql

4) 服务的初始化

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

mysqld --initialize --user=mysql

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

查看密码：

cat /var/log/mysqld.log

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

5) 启动MySQL，查看状态

#加不加.service后缀都可以 

启动：systemctl start mysqld.service 

关闭：systemctl stop mysqld.service 

重启：systemctl restart mysqld.service 

查看状态：systemctl status mysqld.service

查看进程：

ps -ef | grep -i mysql

6) 查看MySQL服务是否自启动

systemctl list-unit-files|grep mysqld.service

默认是enabled。

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

  systemctl enable mysqld.service
  

  

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

  systemctl disable mysqld.service
  

  

7) linux下安装mysql5.7补充

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

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

chmod -R 777 /tmp

② 安装前，检查依赖

rpm -qa|grep libaio

rpm -qa|grep net-tools

③ CentOS下MySQL5.7安装过程

在mysql的安装文件目录下执行：（必须按照顺序执行）

rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm 

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

rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

• 注意: 如在检查工作时，没有检查mysql依赖环境在安装mysql-community-server会报错
• rpm 是Redhat Package Manage缩写，通过RPM的管理，用户可以把源代码包装成以rpm为扩展名的文件形式，易于安装。
• -i , --install 安装软件包
• -v , --verbose 提供更多的详细信息输出
• -h , --hash 软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条

④ 查看MySQL版本

执行如下命令，如果成功表示安装mysql成功。类似java -version如果打出版本等信息

mysql --version 
#或
mysqladmin --version

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

rpm -qa|grep -i mysql

后面的操作按mysql8.0来就可以了

3. MySQL登录

⑴ 首次登录

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

⑵ 修改密码

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

• 修改密码：

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

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

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

⑶ 设置远程登录

1) 当前问题

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

2) 确认网络

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

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

   telnet ip地址 端口号
   

   拓展： telnet命令开启 :

   
   

3) 关闭防火墙或开放端口

方式一：关闭防火墙

• 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下修改配置

在Linux系统MySQL下测试：

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都有连接权限。

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

update user set host = '%' where user ='root';

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

Host修改完成后记得执行flush privileges使配置立即生效：

flush privileges;

5) 测试

• 如果是 MySQL5.7 版本，接下来就可以使用SQLyog或者Navicat成功连接至MySQL了。

• 如果是 MySQL8 版本，连接时还会出现如下问题：

  
  配置新连接报错：错误号码 2058，分析是 mysql 密码加密方法变了。
  解决方法：Linux下 mysql -u root -p 登录你的 mysql 数据库，然后 执行这条SQL：

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

  然后在重新配置SQLyog的连接，则可连接成功了，OK。

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

⑴ 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
  

⑵ 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

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

⑶ MySQL8的安全策略

1) validate_password说明

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

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

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

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位以上密码

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

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. 字符集的相关操作

⑴ 修改MySQL5.7字符集

1) 修改步骤

在MySQL 8.0版本之前，默认字符集为latin1，utf8字符集指向的是 utf8mb3 。网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL 8.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';

⑵ 各级别的字符集

执行如下SQL语句：

show variables like 'character%';

1) 服务器级别

2) 数据库级别

3) 表级别

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

4) 列级别

5) 小结

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

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

1) utf8 与 utf8mb4

2) 比较规则

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

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

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

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

6. SQL大小写规范

⑴ Windows和Linux平台区别

SHOW VARIABLES LIKE '%lower_case_table_names%'

• Windows系统下：
  
• Linux系统下：
  
• lower_case_table_names参数值的设置：
  ○ 默认为0，大小写敏感 。
  ○ 设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和数据库进行查找。
  ○ 设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。
• 两个平台上SQL大小写的区别具体来说：
  

⑵ 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编写建议

7. sql_mode的合理设置

⑴ 宽松模式 vs 严格模式

⑵ 宽松模式再举例

⑶ 模式查看和设置

二、MySQL的数据目录

1. MySQL8的主要目录结构

[root@localhost ~]# find / -name mysql

安装好MySQL 8之后，我们查看如下的目录结构：

⑴ 数据库文件的存放路径

MySQL数据库文件的存放路径：/var/lib/mysql/

mysql>  show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.02 sec)

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

⑵ 相关命令目录

相关命令目录：/usr/bin（mysqladmin、mysqlbinlog、mysqldump等命令）和/usr/sbin。

⑶ 配置文件目录

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

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

⑴ 查看默认数据库

查看一下在我的计算机上当前有哪些数据库：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

可以看到有4个数据库是属于MySQL自带的系统数据库。

• mysql
  MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

• information_schema
  MySQL 系统自带的数据库，这个数据库保存着MySQL服务器 维护的所有其他数据库的信息 ，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为 元数据 。在系统数据库information_schema中提供了一些以 innodb_sys 开头的表，用于表示内部系统表。

  

• performance_schema
  MySQL 系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来 监控 MySQL 服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。

• sys
  MySQL 系统自带的数据库，这个数据库主要是通过 视图 的形式把 information_schema 和 performance_schema结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

⑵ 数据库在文件系统中的表示

看一下我的计算机上的数据目录下的内容：

[root@localhost ~]# cd /var/lib/mysql
[root@localhost mysql]# ll
total 188868
-rw-r-----. 1 mysql mysql       56 Jun 25 00:22 auto.cnf
-rw-r-----. 1 mysql mysql     1456 Jun 27 05:13 binlog.000001
-rw-r-----. 1 mysql mysql      156 Jun 29 01:59 binlog.000002
-rw-r-----. 1 mysql mysql       32 Jun 29 01:59 binlog.index
-rw-------. 1 mysql mysql     1676 Jun 25 00:22 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 25 00:22 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 25 00:22 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jun 25 00:22 client-key.pem
-rw-r-----. 1 mysql mysql   196608 Jun 29 02:01 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql  8585216 Jun 25 00:22 #ib_16384_1.dblwr
-rw-r-----. 1 mysql mysql     3663 Jun 27 05:13 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jun 29 01:59 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jun 29 02:01 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jun 25 00:22 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jun 29 01:59 ibtmp1
drwxr-x---. 2 mysql mysql      187 Jun 29 01:59 #innodb_temp
drwxr-x---. 2 mysql mysql      143 Jun 25 00:22 mysql
-rw-r-----. 1 mysql mysql 25165824 Jun 29 01:59 mysql.ibd
srwxrwxrwx. 1 mysql mysql        0 Jun 29 01:59 mysql.sock
-rw-------. 1 mysql mysql        4 Jun 29 01:59 mysql.sock.lock
drwxr-x---. 2 mysql mysql     8192 Jun 25 00:22 performance_schema
-rw-------. 1 mysql mysql     1680 Jun 25 00:22 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jun 25 00:22 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 25 00:22 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jun 25 00:22 server-key.pem
drwxr-x---. 2 mysql mysql       28 Jun 25 00:22 sys
-rw-r-----. 1 mysql mysql 16777216 Jun 29 02:01 undo_001
-rw-r-----. 1 mysql mysql 16777216 Jun 29 02:01 undo_002

这个数据目录下的文件和子目录比较多，除了information_schema 这个系统数据库外，其他的数据库在 数据目录 下都有对应的子目录。

以我的temp数据库为例，在MySQL5.7 中打开：

[root@localhost mysql]# cd ./temp 
[root@localhost temp]# ll 
总用量 1144
-rw-r-----. 1 mysql mysql 8658 8月 18 11:32 countries.frm
-rw-r-----. 1 mysql mysql 114688 8月 18 11:32 countries.ibd
-rw-r-----. 1 mysql mysql 61 8月 18 11:32 db.opt
-rw-r-----. 1 mysql mysql 8716 8月 18 11:32 departments.frm
-rw-r-----. 1 mysql mysql 147456 8月 18 11:32 departments.ibd
-rw-r-----. 1 mysql mysql 3017 8月 18 11:32 emp_details_view.frm
-rw-r-----. 1 mysql mysql 8982 8月 18 11:32 employees.frm
-rw-r-----. 1 mysql mysql 180224 8月 18 11:32 employees.ibd
-rw-r-----. 1 mysql mysql 8660 8月 18 11:32 job_grades.frm
-rw-r-----. 1 mysql mysql 98304 8月 18 11:32 job_grades.ibd
-rw-r-----. 1 mysql mysql 8736 8月 18 11:32 job_history.frm
-rw-r-----. 1 mysql mysql 147456 8月 18 11:32 job_history.ibd
-rw-r-----. 1 mysql mysql 8688 8月 18 11:32 jobs.frm
-rw-r-----. 1 mysql mysql 114688 8月 18 11:32 jobs.ibd
-rw-r-----. 1 mysql mysql 8790 8月 18 11:32 locations.frm
-rw-r-----. 1 mysql mysql 131072 8月 18 11:32 locations.ibd
-rw-r-----. 1 mysql mysql 8614 8月 18 11:32 regions.frm
-rw-r-----. 1 mysql mysql 114688 8月 18 11:32 regions.ibd

在MySQL8.0中打开：

[root@localhost mysql]# cd ./temp 
[root@localhost temp]# ll 
总用量 1080
-rw-r-----. 1 mysql mysql 131072 7月 29 23:10 countries.ibd
-rw-r-----. 1 mysql mysql 163840 7月 29 23:10 departments.ibd
-rw-r-----. 1 mysql mysql 196608 7月 29 23:10 employees.ibd
-rw-r-----. 1 mysql mysql 114688 7月 29 23:10 job_grades.ibd
-rw-r-----. 1 mysql mysql 163840 7月 29 23:10 job_history.ibd
-rw-r-----. 1 mysql mysql 131072 7月 29 23:10 jobs.ibd
-rw-r-----. 1 mysql mysql 147456 7月 29 23:10 locations.ibd
-rw-r-----. 1 mysql mysql 131072 7月 29 23:10 regions.ibd

⑶ 表在文件系统中的表示

1) InnoDB存储引擎模式

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

   表名.frm
   

   比方说我们在 kejizhentan 数据库下创建一个名为test的表：

   mysql> USE atguigu; 
   Database changed 
   mysql> CREATE TABLE test (
   -> c1 INT
   -> ); 
   Query OK, 0 rows affected (0.03 sec)
   

   那在数据库 kejizhentan 对应的子目录下就会创建一个名为 test.frm 的用于描述表结构的文件。.frm文件的格式在不同的平台上都是相同的。这个后缀名为.frm是以 二进制格式 存储的，我们直接打开是乱码的。

2. 表中数据和索引
   ① 系统表空间（system tablespace）
   默认情况下，InnoDB会在数据目录下创建一个名为 ibdata1 、大小为 12M 的文件，这个文件就是对应的 系统表空间 在文件系统上的表示。怎么才12M？注意这个文件是 自扩展文件 ，当不够用的时候它会自己增加文件大小。
   当然，如果你想让系统表空间对应文件系统上多个实际文件，或者仅仅觉得原来的ibdata1这个文件名难听，那可以在MySQL启动时配置对应的文件路径以及它们的大小，比如我们这样修改一下my.cnf 配置文件：

   [server]
   innodb_data_file_path=data1:512M;data2:512M:autoextend
   

   ② 独立表空间(file-per-table tablespace)
   在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为 每 一个表建立一个独立表空间 ，也就是说我们创建了多少个表，就有多少个独立表空间。使用 独立表空间 来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个 .ibd 的扩展名而已，所以完整的文件名称长这样：

   表名.ibd
   

   比如：我们使用了 独立表空间 去存储 kejizhentan 数据库下的 test 表的话，那么在该表所在数据库对应的 kejizhentan 目录下会为 test 表创建这两个文件：

   test.frm 
   test.ibd
   

   其中 test.ibd 文件就用来存储 test 表中的数据和索引。
   ③ 系统表空间与独立表空间的设置
   我们可以自己指定使用 系统表空间 还是 独立表空间 来存储数据，这个功能由启动参数innodb_file_per_table 控制，比如说我们想刻意将表数据都存储到 系统表空间 时，可以在启动MySQL服务器的时候这样配置：

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

   默认情况：

   mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+
   | Variable_name | Value | 
   +-----------------------+-------+
   | innodb_file_per_table | ON | 
   +-----------------------+-------+
   1 row in set (0.01 sec)
   

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

2) MyISAM存储引擎模式

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

   表名.frm
   

2. 表中数据和索引
   在MyISAM中的索引全部都是 二级索引 ，该存储引擎的 数据和索引是分开存放 的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下。假如 test表使用MyISAM存储引擎的话，那么在它所在数据库对应的 kejizheantan目录下会为 test 表创建这三个文件：

   test.frm 存储表结构 
   test.MYD 存储数据 (MYData) 
   test.MYI 存储索引 (MYIndex)
   

   举例：创建一个 MyISAM 表，使用 ENGINE 选项显式指定引擎。因为 InnoDB 是默认引擎。

   CREATE TABLE `student_myisam` ( 
   	`id` bigint NOT NULL AUTO_INCREMENT, 
   	`name` varchar(64) DEFAULT NULL, 
   	`age` int DEFAULT NULL, 
   	`sex` varchar(2) DEFAULT NULL, 
   	PRIMARY KEY (`id`) 
   )ENGINE=MYISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb3;
   

⑷ 小结

三、用户与权限管理

1. 用户管理

⑴ 登录MySQL服务器

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

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

举例：

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

⑵ 创建用户

CREATE USER语句的基本语法形式如下：

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

举例：

CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是 %

CREATE USER 'kejizhentan'@'localhost' IDENTIFIED BY '123456';

⑶ 修改用户

修改用户名：

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

FLUSH PRIVILEGES;

⑷ 删除用户

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

使用DROP USER语句来删除用户时，必须用于DROP USER权限。DROP USER语句的基本语法形式如下：

DROP USER user[,user]…;

举例：

DROP USER li4 ; # 默认删除host为%的用户

DROP USER 'kangshifu'@'localhost';

2) 方式2：使用DELETE方式删除

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

执行完DELETE命令后要使用FLUSH命令来使用户生效，命令如下：

FLUSH PRIVILEGES;

举例：

DELETE FROM mysql.user WHERE Host='localhost' AND User='Emily';

FLUSH PRIVILEGES;

⑸ 设置当前用户密码

1) 旧的写法如下 ：

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

2) 这里介绍 推荐的写法 ：

① 使用ALTER USER命令来修改当前用户密码 用户可以使用ALTER命令来修改自身密码，如下语句代表修改当前登录用户的密码。基本语法如下：

ALTER USER USER() IDENTIFIED BY 'new_password';

② 使用SET语句来修改当前用户密码 使用root用户登录MySQL后，可以使用SET语句来修改密码，具体SQL语句如下：

SET PASSWORD='new_password';

该语句会自动将密码加密后再赋给当前用户。

⑹ 修改其它用户密码

① 使用ALTER语句来修改普通用户的密码 可以使用ALTER USER语句来修改普通用户的密码。基本语法形式如下：

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

② 使用SET命令来修改普通用户的密码 使用root用户登录到MySQL服务器后，可以使用SET语句来修改普通用户的密码。SET语句的代码如下：

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

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

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

⑺ MySQL8密码管理(了解)

1) 密码过期策略

ALTER USER user PASSWORD EXPIRE;

练习：

ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE;

方式①：使用SQL语句更改该变量的值并持久化

SET PERSIST default_password_lifetime = 180; # 建立全局策略，设置密码每隔180天过期

方式②：配置文件my.cnf中进行维护

[mysqld]
default_password_lifetime=180 #建立全局策略，设置密码每隔180天过期

手动设置指定时间过期方式2：单独设置
每个账号既可延用全局密码过期策略，也可单独设置策略。在 CREATE USER 和 ALTER USER 语句上加入 PASSWORD EXPIRE 选项可实现单独设置策略。下面是一些语句示例。

#设置kangshifu账号密码每90天过期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

#设置密码永不过期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;

#延用全局密码过期策略：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;

2) 密码重用策略

① 手动设置密码重用方式1：全局

• 方式①：使用SQL

  SET PERSIST password_history = 6; #设置不能选择最近使用过的6个密码
  
  SET PERSIST password_reuse_interval = 365; #设置不能选择最近一年内的密码
  

  

• 方式②：my.cnf配置文件

  [mysqld]
  password_history=6
  password_reuse_interval=365
  

② 手动设置密码重用方式2：单独设置

#不能使用最近5个密码：
CREATE USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;
ALTER USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;

#不能使用最近365天内的密码：
CREATE USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;

#既不能使用最近5个密码，也不能使用365天内的密码
CREATE USER 'kangshifu'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;

ALTER USER 'kangshifu'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;

2. 权限管理

⑴ 权限列表

MySQL到底都有哪些权限呢？

mysql> show privileges;

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

⑵ 授予权限的原则

权限控制主要是出于安全因素，因此需要遵循以下几个 经验原则 ：
1、只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
2、创建用户的时候 限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。
3、为每个用户 设置满足密码复杂度的密码 。
4、 定期清理不需要的用户 ，回收权限或者删除用户。

⑶ 授予权限

给用户授权的方式有 2 种，分别是通过把 角色赋予用户给用户授权 和 直接给用户授权 。用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全隐患。
授权命令：

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';
  

⑷ 查看权限

1) 查看当前用户权限

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

2) 查看某用户的全局权限

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

⑸ 收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用 REVOKE语句 取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句）。

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

1) 收回权限命令

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

举例

#收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';

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

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

3. 权限表

⑴ user表

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

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

⑵ db表

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

DESCRIBE mysql.db;

⑶ tables_priv表和columns_priv表

⑷ procs_priv表

4. 访问控制(了解)

⑴ 连接核实阶段

当用户试图连接MySQL服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确定接受或者拒绝连接。即客户端用户会在连接请求中提供用户名、主机地址、用户密码，MySQL服务器接收到用户请求后，会使用user表中的host、user和authentication_string这3个字段匹配客户端提供信息。
服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的密码时才接受连接。如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待用户请求。

⑵ 请求核实阶段

一旦建立了连接，服务器就进入了访问控制的阶段2，也就是请求核实阶段。对此连接上进来的每个请求，服务器检查该请求要执行什么操作、是否有足够的权限来执行它，这正是需要授权表中的权限列发挥作用的地方。这些权限可以来自user、db、table_priv和column_priv表。

确认权限时，MySQL首先 检查user表 ，如果指定的权限没有在user表中被授予，那么MySQL就会继续 检查db表 ，db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据；如果在该层级没有找到限定的权限，则MySQL继续 检查tables_priv表 以及 columns_priv表 ，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将 返回错误信息 ，用户请求的操作不能执行，操作失败。

5. 角色管理

⑴ 角色的理解

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

⑵ 创建角色

创建角色使用 CREATE ROLE 语句，语法如下：

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

角色名称的命名规则和用户名类似。如果 host_name省略，默认为% ， role_name不可省略 ，不可为
空。
练习：我们现在需要创建一个经理的角色，就可以用下面的代码：

CREATE ROLE 'manager'@'localhost';

⑶ 给角色赋予权限

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

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

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

SHOW PRIVILEGES\G;

练习1：我们现在想给经理角色授予商品信息表、盘点表和应付账款表的只读权限，就可以用下面的代码来实现：

GRANT SELECT ON demo.settlement TO 'manager'; 
GRANT SELECT ON demo.goodsmaster TO 'manager'; 
GRANT SELECT ON demo.invcount TO 'manager';
grant all privileges on *.*  to 'manager'@'%'；#赋予manager全部权限

⑷ 查看角色的权限

赋予角色权限之后，我们可以通过 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”赋予的权限，也就是对商品信息表、盘点表和应付账款表的只读权限。
结果显示，库管角色拥有商品信息表的只读权限和盘点表的增删改查权限。

⑸ 回收角色的权限

角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句。
修改了角色的权限，会影响拥有该角色的账户的权限。
撤销角色权限的SQL语法如下：

REVOKE privileges ON tablename FROM 'rolename';

注意：

如果通过root用户撤销权限的时候出现如下错误：
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

执行如下操作即可：
grant system_user on *.* to 'root';

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

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

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

SHOW GRANTS FOR 'school_write';

⑹ 删除角色

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

DROP ROLE role [,role2]...

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

练习：执行如下SQL删除角色school_read。

DROP ROLE 'school_read';

⑺ 给用户赋予角色

角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用。给用户添加角色可使用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();

⑻ 激活角色

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

举例：

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

例如：

set default  role  'boss'@'%' to 'kejizhentan'@'%';

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

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

2) 方式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 语句的意思是，对 所有角色永久激活 。运行这条语句之后，用户才真正拥有了赋予角色的所有权限。

⑼ 撤销用户的角色

撤销用户角色的SQL语法如下：

REVOKE role FROM user;

练习：撤销kangshifu用户的school_read角色。
（1）撤销的SQL语句如下

REVOKE 'school_read' FROM 'kangshifu'@'localhost';

（2）撤销后，执行如下查询语句，查看kangshifu用户的角色信息

SHOW GRANTS FOR 'kangshifu'@'localhost';

执行发现，用户kangshifu之前的school_read角色已被撤销。

⑽ 设置强制角色(mandatory role)

1) 方式1：服务启动前设置

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

2) 方式2：运行时设置

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

四、mysql逻辑架构

1. 逻辑架构剖析

⑴ 服务器处理客户端请求

那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？这里以查询请求为例展示：

下面具体展开看一下：

⑵ 第1层：连接层 Connectors

⑶ 第2层：服务层

⑷ 第3层：引擎层

⑸ 存储层

⑹ 小结

2. SQL执行流程

⑴ MySQL 中的 SQL执行流程

⑵ MySQL8中SQL执行原理

⑶ MySQL5.7中SQL执行原理

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

⑷ SQL语法顺序

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

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

⑴ 缓冲池 vs 查询缓存

⑵ 缓冲池如何读取数据

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

⑷ 多个Buffer Pool实例

⑸ 引申问题

五、存储引擎

1. 查看存储引擎

查看mysql提供什么存储引擎：

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. 设置表的存储引擎

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

⑴ 创建表时指定存储引擎

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

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

⑵ 修改表的存储引擎

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

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

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

mysql> SHOW CREATE TABLE engine_demo_table\G 
*************************** 1. row *************************** 
Table: engine_demo_table 
Create Table: CREATE TABLE `engine_demo_table` ( 
	`i` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

4. 引擎介绍

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

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

⑶ Archive 引擎：用于数据存档

下表展示了ARCHIVE 存储引擎功能

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

⑸ 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打开如图所示

⑹ Memory 引擎：置于内存的表

⑺ Federated 引擎：访问远程表

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

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

⑽ 引擎对比

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

5. MyISAM和InnoDB

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

课外补充：

六、索引的数据结构

1. 为什么使用索引

2. 索引及其优缺点

⑴ 索引概述

⑵ 优点

⑶ 缺点

3. InnoDB中索引的推演

⑴ 索引之前的查找

先来看一个精确匹配的例子：

SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

1） 在一个页中的查找

2） 在很多页中查找

⑵ 设计索引

建一个表：

mysql> CREATE TABLE index_demo(
	-> c1 INT,
	-> c2 INT,
	-> c3 CHAR(1),
	-> PRIMARY KEY(c1)
	-> ) ROW_FORMAT = Compact;

1） 一个简单的索引设计方案

2） InnoDB中的索引方案

① 迭代1次：目录项纪录的页

② 迭代2次：多个目录项纪录的页

③ 迭代3次：目录项记录页的目录页

④ B+Tree

⑶ 常见索引概念

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

1） 聚簇索引

2） 二级索引（辅助索引、非聚簇索引）

3） 联合索引

⑷ InnoDB的B+树索引的注意事项

• 根页面位置万年不动
• 内节点中目录项记录的唯一性
• 一个页面最少存储2条记录

4. MyISAM中的索引方案

⑴ MyISAM索引的原理

下图是MyISAM索引的原理图。

⑵ MyISAM 与 InnoDB对比

5. 索引的代价

6. MySQL数据结构选择的合理性

⑴ 全表遍历

这里都懒得说了。

⑵ Hash结构

⑶ 二叉搜索树

⑷ AVL树

⑸ B-Tree

B 树的结构如下图所示：

⑹ B+Tree

⑺ R树

附录：算法的时间复杂度

七、InnoDB数据存储结构

1. 数据库的存储结构：页

⑴ 磁盘与内存交互基本单位：页

⑵ 页结构概述

⑶ 页的大小

不同的数据库管理系统（简称DBMS ）的页大小不同。比如在 MySQL 的 InnoDB 存储引擎中，默认页的大小是 16KB ，我们可以通过下面的命令来进行查看：

mysql> show variables like '%innodb_page_size%';

⑷ 页的上层结构

2. 页的内部结构

3. InnoDB行格式（或记录格式）

mysql> SELECT @@innodb_default_row_format; 
+-----------------------------+
| @@innodb_default_row_format | 
+-----------------------------+
| dynamic | 
+-----------------------------+
1 row in set (0.00 sec)

也可以使用如下语法查看具体表使用的行格式：

SHOW TABLE STATUS like '表名'\G

4. 区、段与碎片区

⑴ 为什么要有区？

⑵ 为什么要有段？

⑶ 为什么要有碎片区？

⑷ 区的分类

5. 表空间

⑴ 独立表空间

⑵ 系统表空间

mysql> USE information_schema; 
Database changed 

mysql> SHOW TABLES LIKE 'innodb_sys%'; 
+--------------------------------------------+
| Tables_in_information_schema (innodb_sys%) | 
+--------------------------------------------+
| INNODB_SYS_DATAFILES | 
| INNODB_SYS_VIRTUAL | 
| INNODB_SYS_INDEXES | 
| INNODB_SYS_TABLES | 
| INNODB_SYS_FIELDS | 
| INNODB_SYS_TABLESPACES | 
| INNODB_SYS_FOREIGN_COLS | 
| INNODB_SYS_COLUMNS | 
| INNODB_SYS_FOREIGN | 
| INNODB_SYS_TABLESTATS | 
+--------------------------------------------+
10 rows in set (0.00 sec)

八、索引的创建与设计原则

1. 索引的声明与使用

⑴ 索引的分类

⑵ 创建索引

1） 创建表的时候创建索引

举例：

CREATE TABLE dept( 
dept_id INT PRIMARY KEY AUTO_INCREMENT, 
dept_name VARCHAR(20) 
);

CREATE TABLE emp( 
emp_id INT PRIMARY KEY AUTO_INCREMENT, 
emp_name VARCHAR(20) UNIQUE, 
dept_id INT, 
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id) 
);

但是，如果显式创建表时创建索引的话，基本语法格式如下：

CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

① 创建普通索引

在book表中的year_publication字段上建立普通索引，SQL语句如下：

CREATE TABLE book( 
	book_id INT , 
	book_name VARCHAR(100), 
	authors VARCHAR(100), 
	info VARCHAR(100) , 
	comment VARCHAR(100), 
	year_publication YEAR, 
	INDEX(year_publication) 
);

② 创建唯一索引

举例：

CREATE TABLE test1( 
	id INT NOT NULL, 
	name varchar(30) NOT NULL, 
	UNIQUE INDEX uk_idx_id(id) 
);

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：

SHOW INDEX FROM test1 \G

③ 主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法：

• 随表一起建索引：

  CREATE TABLE student ( 
  	id INT(10) UNSIGNED AUTO_INCREMENT , 
  	student_no VARCHAR(200), 
  	student_name VARCHAR(200), 
  	PRIMARY KEY(id) 
  );
  

• 删除主键索引：

  ALTER TABLE student drop PRIMARY KEY ;
  

• 修改主键索引：必须先删除掉(drop)原索引，再新建(add)索引

④ 创建单列索引

举例：

CREATE TABLE test2( 
	id INT NOT NULL, 
	name CHAR(50) NULL, 
	INDEX single_idx_name(name(20)) 
);

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：

SHOW INDEX FROM test2 \G

⑤ 创建组合索引

举例：创建表test3，在表中的id、name和age字段上建立组合索引，SQL语句如下：

CREATE TABLE test3( 
	id INT(11) NOT NULL, 
	name CHAR(30) NOT NULL, 
	age INT(11) NOT NULL, 
	info VARCHAR(255), 
	INDEX multi_idx(id,name,age) 
);

该语句执行完毕之后，使用SHOW INDEX 查看：

SHOW INDEX FROM test3 \G

⑥ 创建全文索引

举例1：创建表test4，在表中的info字段上建立全文索引，SQL语句如下：

CREATE TABLE test4( 
	id INT NOT NULL, 
	name CHAR(30) NOT NULL, 
	age INT NOT NULL, 
	info VARCHAR(255), 
	FULLTEXT INDEX futxt_idx_info(info) 
) ENGINE=MyISAM;

举例2：

CREATE TABLE articles ( 
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
	title VARCHAR (200), 
	body TEXT, 
	FULLTEXT index (title, body) 
) ENGINE = INNODB ;

创建了一个给title和body字段添加全文索引的表。
举例3：

CREATE TABLE `papers` ( 
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
	`title` varchar(200) DEFAULT NULL, 
	`content` text, PRIMARY KEY (`id`), 
	FULLTEXT KEY `title` (`title`,`content`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

不同于like方式的的查询：

SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;

全文索引用match+against方式查询：

SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

⑦ 创建空间索引

CREATE TABLE test5( 
	geo GEOMETRY NOT NULL, 
	SPATIAL INDEX spa_idx_geo(geo) 
) ENGINE=MyISAM;

2） 在已经存在的表上创建索引

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。

① 使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的基本语法如下：

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]

② 使用CREATE INDEX创建索引 CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中，CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]

⑶ 删除索引

1) 使用ALTER TABLE删除索引 ALTER TABLE删除索引的基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name;

2) 使用DROP INDEX语句删除索引 DROP INDEX删除索引的基本语法格式如下：

DROP INDEX index_name ON table_name;

2. MySQL8.0索引新特性

⑴ 支持降序索引

举例：分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表ts1，结果如下：

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

在MySQL 5.7版本中查看数据表ts1的结构，结果如下：

从结果可以看出，索引仍然是默认的升序。
在MySQL 8.0版本中查看数据表ts1的结构，结果如下：

从结果可以看出，索引已经是降序了。下面继续测试降序索引在执行计划中的表现。
分别在MySQL 5.7版本和MySQL 8.0版本的数据表ts1中插入800条随机数据，执行语句如下：

DELIMITER // 
CREATE PROCEDURE ts_insert() 
BEGIN
	DECLARE i INT DEFAULT 1; 
	WHILE i < 800 
	DO 
		insert into ts1 select rand()*80000,rand()*80000; 
		SET i = i + 1; 
	END WHILE; 
	commit; 
END // 
DELIMITER ; 

#调用
CALL ts_insert();

在MySQL 5.7版本中查看数据表ts1的执行计划，结果如下：

EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;

从结果可以看出，执行计划中扫描数为799，而且使用了Using filesort。

EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;

在MySQL 8.0版本中查看数据表ts1的执行计划。
从结果可以看出，修改后MySQL 5.7的执行计划要明显好于MySQL 8.0。

⑵ 隐藏索引

1) 创建表时直接创建 在MySQL中创建隐藏索引通过SQL语句INVISIBLE来实现，其语法形式如下：

CREATE TABLE tablename( 
	propname1 type1[CONSTRAINT1], 
	propname2 type2[CONSTRAINT2], 
	……
	propnamen typen, 
	INDEX [indexname](propname1 [(length)]) INVISIBLE 
);

上述语句比普通索引多了一个关键字INVISIBLE，用来标记索引为不可见索引。

2) 在已经存在的表上创建

可以为已经存在的表设置隐藏索引，其语法形式如下：

CREATE INDEX indexname 
ON tablename(propname[(length)]) INVISIBLE;

3)通过ALTER TABLE语句创建

语法形式如下：

ALTER TABLE tablename 
ADD INDEX indexname (propname [(length)]) INVISIBLE;

4)切换索引可见状态 已存在的索引可通过如下语句切换可见状态：

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
 ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

5) 使隐藏索引对查询优化器可见

3. 索引的设计原则

⑴ 数据准备

第1步：创建数据库、创建表

CREATE DATABASE atguigudb1; 

USE atguigudb1;
 
#1.创建学生表和课程表 
CREATE TABLE `student_info` ( 
	`id` INT(11) NOT NULL AUTO_INCREMENT, 
	`student_id` INT NOT NULL , 
	`name` VARCHAR(20) DEFAULT NULL, 
	`course_id` INT NOT NULL , 
	`class_id` INT(11) DEFAULT NULL, 
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	PRIMARY KEY (`id`) 
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 

CREATE TABLE `course` ( 
	`id` INT(11) NOT NULL AUTO_INCREMENT, 
	`course_id` INT NOT NULL , 
	`course_name` VARCHAR(40) DEFAULT NULL, 
	PRIMARY KEY (`id`) 
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

第2步：创建模拟数据必需的存储函数

#函数1：创建随机产生字符串函数 
DELIMITER // 
CREATE FUNCTION rand_string(n INT) 
	RETURNS VARCHAR(255) #该函数会返回一个字符串 
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT
 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 	DECLARE return_str VARCHAR(255) DEFAULT '';
 	DECLARE i INT DEFAULT 0; 
 	WHILE i < n DO 
 	SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); 
 	SET i = i + 1; 
 	END WHILE; 
	RETURN return_str; 
 END // 
 DELIMITER ;

#函数2：创建随机数函数 
DELIMITER // 
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11) 
BEGIN 
DECLARE i INT DEFAULT 0; 
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ; 
RETURN i; 
END // 
DELIMITER ;

第3步：创建插入模拟数据的存储过程

# 存储过程1：创建插入课程表存储过程 
DELIMITER // 
CREATE PROCEDURE insert_course( max_num INT ) 
BEGIN 
DECLARE i INT DEFAULT 0; 
	SET autocommit = 0; #设置手动提交事务 
	REPEAT #循环 
	SET i = i + 1; #赋值 
	INSERT INTO course (course_id, course_name ) VALUES (rand_num(10000,10100),rand_string(6)); 
	UNTIL i = max_num
	END REPEAT; 
	COMMIT; #提交事务 
END // 
DELIMITER ;

# 存储过程2：创建插入学生信息表存储过程 
DELIMITER // 
CREATE PROCEDURE insert_stu( max_num INT ) 
BEGIN 
DECLARE i INT DEFAULT 0; 
	SET autocommit = 0; #设置手动提交事务 
	REPEAT #循环 
	SET i = i + 1; #赋值 
	INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES (rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6)); UNTIL i = max_num 
	END REPEAT; 
	COMMIT; #提交事务 
END // 
DELIMITER ;

第4步：调用存储过程

CALL insert_course(100); 

CALL insert_stu(1000000);

⑵ 哪些情况适合创建索引

公式：

count(distinct left(列名, 索引长度))/count(*)

例如：

select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度 
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度 
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度 
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop;

⑶ 限制索引的数目

⑷ 哪些情况不适合创建索引

1) 在where中使用不到的字段，不要设置索引

2) 数据量小的表最好不要使用索引

举例：创建表1：

CREATE TABLE t_without_index( 
	a INT PRIMARY KEY AUTO_INCREMENT, 
	b INT 
);

提供存储过程1：

#创建存储过程 
DELIMITER // 
CREATE PROCEDURE t_wout_insert() 
BEGIN
	DECLARE i INT DEFAULT 1; 
	WHILE i <= 900 
	DO 
		INSERT INTO t_without_index(b) SELECT RAND()*10000; 
		SET i = i + 1; 
	END WHILE;
	COMMIT; 
END // 
DELIMITER ; 

#调用 
CALL t_wout_insert();

创建表2：

CREATE TABLE t_with_index( 
	a INT PRIMARY KEY AUTO_INCREMENT, 
	b INT, INDEX idx_b(b) 
);

创建存储过程2：

#创建存储过程 
DELIMITER // 
CREATE PROCEDURE t_with_insert() 
BEGIN
	DECLARE i INT DEFAULT 1; 
	WHILE i <= 900 
	DO 
		INSERT INTO t_with_index(b) SELECT RAND()*10000; 
		SET i = i + 1; 
	END WHILE; 
	COMMIT; 
END // 
DELIMITER ; 

#调用 
CALL t_with_insert();

查询对比：

mysql> select * from t_without_index where b = 9879; 
+------+------+
| a | b | 
+------+------+
| 1242 | 9879 | 
+------+------+
1 row in set (0.00 sec) 


mysql> select * from t_with_index where b = 9879; 
+-----+------+
| a | b | 
+-----+------+
| 112 | 9879 | 
+-----+------+
1 row in set (0.00 sec)

3) 有大量重复数据的列上不要建立索引

举例1：要在 100 万行数据中查找其中的 50 万行（比如性别为男的数据），一旦创建了索引，你需要先访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。
举例2：假设有一个学生表，学生总数为 100 万人，男性只有 10 个人，也就是占总人口的 10 万分之 1。学生表 student_gender 结构如下。其中数据表中的 student_gender 字段取值为 0 或 1，0 代表女性，1 代表男性。

CREATE TABLE student_gender( 
	student_id INT(11) NOT NULL, 
	student_name VARCHAR(50) NOT NULL, 
	student_gender TINYINT(1) NOT NULL, 
	PRIMARY KEY(student_id) 
)ENGINE = INNODB;

如果我们要筛选出这个学生表中的男性，可以使用：

SELECT * FROM student_gender WHERE student_gender = 1

运行结果（10 条数据，运行时间 0.696s ）：

4) 避免对经常更新的表创建过多的索引

5) 不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

6) 删除不再使用或者很少使用的索引

7) 不要定义冗余或重复的索引

① 冗余索引

举例：建表语句如下

CREATE TABLE person_info( 
	id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
	name VARCHAR(100) NOT NULL, 
	birthday DATE NOT NULL, 
	phone_number CHAR(11) NOT NULL, 
	country varchar(100) NOT NULL, 
	PRIMARY KEY (id), 
	KEY idx_name_birthday_phone_number (name(10), birthday, phone_number), 
	KEY idx_name (name(10)) 
);

② 重复索引

另一种情况，我们可能会对某个列 重复建立索引 ，比方说这样：

CREATE TABLE repeat_index_demo ( 
	col1 INT PRIMARY KEY, 
	col2 INT, 
	UNIQUE uk_idx_c1 (col1), 
	INDEX idx_c1 (col1) 
);

我们看到，col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

九、性能分析工具的使用

1. 数据库服务器的优化步骤

2. 查看系统性能参数

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

3. 统计SQL的查询成本：last_query_cost

我们依然使用第8章的 student_info 表为例：

CREATE TABLE `student_info` ( 
	`id` INT(11) NOT NULL AUTO_INCREMENT, 
	`student_id` INT NOT NULL , 
	`name` VARCHAR(20) DEFAULT NULL, 
	`course_id` INT NOT NULL , 
	`class_id` INT(11) DEFAULT NULL, 
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	PRIMARY KEY (`id`) 
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：

SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id = 900001;

运行结果（1 条记录，运行时间为 0.042s ）
然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：

mysql> SHOW STATUS LIKE 'last_query_cost'; 
+-----------------+----------+
| Variable_name | Value | 
+-----------------+----------+
| Last_query_cost | 1.000000 | 
+-----------------+----------+

如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？

SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id BETWEEN 900001 AND 900100;

运行结果（100 条记录，运行时间为 0.046s ）：
然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询。

mysql> SHOW STATUS LIKE 'last_query_cost'; 
+-----------------+-----------+
| Variable_name | Value | 
+-----------------+-----------+
| Last_query_cost | 21.134453 | 
+-----------------+-----------+

4. 定位执行慢的 SQL：慢查询日志

⑴ 开启慢查询日志参数

1） 开启slow_query_log

mysql > set global slow_query_log='ON';

你能看到这时慢查询分析已经开启，同时文件保存在/var/lib/mysql/atguigu02-slow.log 文件中。

2） 修改long_query_time阈值

接下来我们来看下慢查询的时间阈值设置，使用如下命令：

mysql > show variables like '%long_query_time%';

#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并 执行下述语句 
mysql > set global long_query_time = 1; 
mysql> show global variables like '%long_query_time%'; 

mysql> set long_query_time=1; 
mysql> show variables like '%long_query_time%';

⑵ 查看慢查询数目

查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

⑶ 案例演示

步骤1. 建表

CREATE TABLE `student` ( 
	`id` INT(11) NOT NULL AUTO_INCREMENT, 
	`stuno` INT NOT NULL , 
	`name` VARCHAR(20) DEFAULT NULL, 
	`age` INT(3) DEFAULT NULL, 
	`classId` INT(11) DEFAULT NULL, 
	PRIMARY KEY (`id`) 
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步骤2：设置参数 log_bin_trust_function_creators
创建函数，假如报错：

This function has none of DETERMINISTIC......

命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

步骤3：创建函数
随机产生字符串：（同上一章）

DELIMITER // 
CREATE FUNCTION rand_string(n INT) 
	RETURNS VARCHAR(255) #该函数会返回一个字符串 
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
	DECLARE return_str VARCHAR(255) DEFAULT ''; 
	DECLARE i INT DEFAULT 0; 
	WHILE i < n DO 
		SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); 
		SET i = i + 1; 
	END WHILE; 
	RETURN return_str; 
END // 
DELIMITER ; 

#测试 
SELECT rand_string(10);

产生随机数值：（同上一章）

DELIMITER // 
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11) 
BEGIN 
DECLARE i INT DEFAULT 0; 
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ; 
RETURN i; 
END // 
DELIMITER ; 

#测试： 
SELECT rand_num(10,100);

步骤4：创建存储过程

DELIMITER // 
CREATE PROCEDURE insert_stu1( START INT , max_num INT ) 
BEGIN 
DECLARE i INT DEFAULT 0; 
	SET autocommit = 0; #设置手动提交事务 
	REPEAT #循环 
	SET i = i + 1; #赋值 
	INSERT INTO student (stuno, NAME ,age ,classId ) VALUES ((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000)); UNTIL i = max_num 
	END REPEAT; 
	COMMIT; 
	#提交事务 
END // 
DELIMITER ;

步骤5：调用存储过程

#调用刚刚写好的函数, 4000000条记录,从100001号开始 
CALL insert_stu1(100001,4000000);

⑷ 测试及分析

1) 测试

mysql> SELECT * FROM student WHERE stuno = 3455655; 
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId | 
+---------+---------+--------+------+---------+
| 3523633 | 3455655 | oQmLUr | 19 | 39 | 
+---------+---------+--------+------+---------+
1 row in set (2.09 sec) 

mysql> SELECT * FROM student WHERE name = 'oQmLUr'; 
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId | 
+---------+---------+--------+------+---------+
| 1154002 | 1243200 | OQMlUR | 266 | 28 | 
| 1405708 | 1437740 | OQMlUR | 245 | 439 | 
| 1748070 | 1680092 | OQMlUR | 240 | 414 | 
| 2119892 | 2051914 | oQmLUr | 17 | 32 | 
| 2893154 | 2825176 | OQMlUR | 245 | 435 | 
| 3523633 | 3455655 | oQmLUr | 19 | 39 | 
+---------+---------+--------+------+---------+
6 rows in set (2.39 sec)

2) 分析

show status like 'slow_queries';

⑸ 慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow 。
查看mysqldumpslow的帮助信息

mysqldumpslow --help

mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log

工作常用参考：

#得到返回记录集最多的10个SQL 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 

#得到访问次数最多的10个SQL 
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log 

#得到按照时间排序的前10条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

⑹ 关闭慢查询日志

MySQL服务器停止慢查询日志功能有两种方法：

方式1：永久性方式

[mysqld] 
slow_query_log=OFF

或者，把slow_query_log一项注释掉 或 删除

[mysqld] 
#slow_query_log =OFF

重启MySQL服务，执行如下语句查询慢日志功能。

SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录 
SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长

方式2：临时性方式
使用SET语句来设置。
（1）停止MySQL慢查询日志功能，具体SQL语句如下。

SET GLOBAL slow_query_log=off;

（2）重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，具体SQL语句如下

SHOW VARIABLES LIKE '%slow%'; 
#以及 
SHOW VARIABLES LIKE '%long_query_time%';

⑺ 删除慢查询日志

5. 查看 SQL 执行成本：SHOW PROFILE

mysql > show variables like 'profiling';

通过设置 profiling='ON’ 来开启 show profile：

mysql > set profiling = 'ON';

然后执行相关的查询语句。接着看下当前会话都有哪些 profiles，使用下面这条命令：

mysql > show profiles;

你能看到当前会话一共有 2 个查询。如果我们想要查看最近一次查询的开销，可以使用：

mysql > show profile;

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

6. 分析查询语句：EXPLAIN

⑴ 概述

官网介绍
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

⑵ 基本语法

EXPLAIN 或 DESCRIBE语句的语法形式如下：

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样：

mysql> EXPLAIN SELECT 1;

EXPLAIN 语句输出的各个列的作用如下：

⑶ 数据准备

1) 建表

CREATE TABLE s1 (
	id INT AUTO_INCREMENT,
	key1 VARCHAR(100),
	key2 INT,
	key3 VARCHAR(100),
	key_part1 VARCHAR(100),
	key_part2 VARCHAR(100),
	key_part3 VARCHAR(100),
	common_field VARCHAR(100),
	PRIMARY KEY (id),
	INDEX idx_key1 (key1),
	UNIQUE INDEX idx_key2 (key2),
	INDEX idx_key3 (key3),
	INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE s2 (
	id INT AUTO_INCREMENT,
	key1 VARCHAR(100),
	key2 INT,
	key3 VARCHAR(100),
	key_part1 VARCHAR(100),
	key_part2 VARCHAR(100),
	key_part3 VARCHAR(100),
	common_field VARCHAR(100),
	PRIMARY KEY (id),
	INDEX idx_key1 (key1),
	UNIQUE INDEX idx_key2 (key2),
	INDEX idx_key3 (key3),
	INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

2) 设置参数 log_bin_trust_function_creators

创建函数，假如报错，需开启如下命令：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

3) 创建函数

DELIMITER //
CREATE FUNCTION rand_string1(n INT)
	RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT
	'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
	SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	SET i = i + 1;
	END WHILE;
	RETURN return_str;
END //
DELIMITER ;

4) 创建存储过程

创建往s1表中插入数据的存储过程：

DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
	SET i = i + 1;
	INSERT INTO s1 VALUES(
	(min_num + i),
	rand_string1(6),
	(min_num + 30 * i + 5),
	rand_string1(6),
	rand_string1(10),
	rand_string1(5),
	rand_string1(10),
	rand_string1(10));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER ;

创建往s2表中插入数据的存储过程：

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
	SET i = i + 1;
	INSERT INTO s2 VALUES(
		(min_num + i),
		rand_string1(6),
		(min_num + 30 * i + 5),
		rand_string1(6),
		rand_string1(10),
		rand_string1(5),
		rand_string1(10),
		rand_string1(10));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER ;

5) 调用存储过程

s1表数据的添加：加入1万条记录：

CALL insert_s1(10001,10000);

s2表数据的添加：加入1万条记录：

CALL insert_s2(10001,10000);

⑷ EXPLAIN各列作用

1) table

2) id

SELECT * FROM s1 WHERE key1 = 'a';

SELECT * FROM s1 INNER JOIN s2
ON s1.key1 = s2.key1
WHERE s1.common_field = 'a';

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');

mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

小结:

3) select_type

具体分析如下：

mysql> EXPLAIN SELECT * FROM s1;

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

• PRIMARY

  mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
  

  

• UNION

• UNION RESULT

• SUBQUERY

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
  

  

• DEPENDENT SUBQUERY

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
  

  

• DEPENDENT UNION

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');
  

  

• DERIVED

  mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;
  

  

• MATERIALIZED

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
  

  

• UNCACHEABLE SUBQUERY
  不常用，就不多说了。

• UNCACHEABLE UNION
  不常用，就不多说了。

4) partitions (可略)

• 如果想详细了解，可以如下方式测试。创建分区表：

  -- 创建分区表，
  -- 按照id分区，id<100 p0分区，其他p1分区 
  CREATE TABLE user_partitions (
  	id INT auto_increment, 
  	NAME VARCHAR(12),
  	PRIMARY KEY(id)) 
  	PARTITION BY RANGE(id)( 
  		PARTITION p0 VALUES less than(100), 
  		PARTITION p1 VALUES less than MAXVALUE 
  );
  

  

  DESC SELECT * FROM user_partitions WHERE id>200;
  

  查询id大于200（200>100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则
  

5) type ☆

• system

  mysql> CREATE TABLE t(i int) Engine=MyISAM; 
  Query OK, 0 rows affected (0.05 sec) 
  
  mysql> INSERT INTO t VALUES(1); 
  Query OK, 1 row affected (0.01 sec)
  

  然后我们看一下查询这个表的执行计划：

  mysql> EXPLAIN SELECT * FROM t;
  

  

• const

  mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;
  

  

• eq_ref

  mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
  

  

• ref

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
  

  

• fulltext
  全文索引

• ref_or_null

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
  

  

• index_merge

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
  

  

• unique_subquery

  mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (
  	SELECT id FROM s2 where s1.key1 = s2.key1
  ) OR key3 = 'a';
  

  

• index_subquery

  mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';
  

  

• range

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
  

  
  或者：

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
  

  

• index

  mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
  

  

• ALL

  mysql> EXPLAIN SELECT * FROM s1;
  

  

6) possible_keys和key

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

7) key_len ☆

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;

mysql> EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';

mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

8) ref

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

9) rows ☆

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

10) filtered

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

11) Extra ☆

mysql> EXPLAIN SELECT 1;

• Impossible WHERE

  mysql> EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
  

  

• Using where

  mysql> EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
  

  

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
  

  

• No matching min/max row

  mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
  

  

• Using index

  mysql> EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';
  

  

• Using index condition

  SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
  

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';
  

  

• Using join buffer (Block Nested Loop)

  mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
  

  

• Not exists

  mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
  

  

• Using intersect(...) 、 Using union(...) 和 Using sort_union(...)

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
  

  

• Zero limit

  mysql> EXPLAIN SELECT * FROM s1 LIMIT 0;
  

  

• Using filesort

  mysql> EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
  

  

  mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
  

  

• Using temporary

  mysql> EXPLAIN SELECT DISTINCT common_field FROM s1;
  

  

  mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
  

  

  mysql> EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
  

  

• 其它
  其它特殊情况这里省略。

12) 小结

7. EXPLAIN的进一步使用

⑴ EXPLAIN四种输出格式

1) 传统格式

传统格式简单明了，输出是一个表格形式，概要说明查询计划。

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

2) JSON格式

• JSON格式：在EXPLAIN单词和真正的查询语句中间加上 FORMAT=JSON 。

  EXPLAIN FORMAT=JSON SELECT ....
  

  

  "cost_info": {
  	"read_cost": "1840.84",
  	"eval_cost": "193.76",
  	"prefix_cost": "2034.60",
  	"data_read_per_join": "1M"
  }
  

"cost_info": {
	"read_cost": "968.80",
	"eval_cost": "193.76",
	"prefix_cost": "3197.16",
	"data_read_per_join": "1M"
}

968.80 + 193.76 + 2034.60 = 3197.16

3) TREE格式

mysql> EXPLAIN FORMAT=tree SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G 
*************************** 1. row *************************** 
EXPLAIN: -> Nested loop inner join (cost=1360.08 rows=990)
-> Filter: ((s1.common_field = 'a') and (s1.key1 is not null)) (cost=1013.75 rows=990)
-> Table scan on s1 (cost=1013.75 rows=9895)
-> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double)) (cost=0.25 rows=1) 
1 row in set, 1 warning (0.00 sec)

4) 可视化输出

⑵ SHOW WARNINGS的使用

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

mysql> SHOW WARNINGS\G 
*************************** 1. row *************************** 
Level: Note 
Code: 1003 
Message: /* select#1 */ select `atguigu`.`s1`.`key1` AS `key1`,`atguigu`.`s2`.`key1` AS `key1` from `atguigu`.`s1` join `atguigu`.`s2` where ((`atguigu`.`s1`.`key1` = `atguigu`.`s2`.`key1`) and (`atguigu`.`s2`.`common_field` is not null)) 
1 row in set (0.00 sec)

8. 分析优化器执行计划：trace

SET optimizer_trace="enabled=on",end_markers_in_json=on; 

set optimizer_trace_max_mem_size=1000000;

开启后，可分析如下语句：

测试：执行如下SQL语句

select * from student where id < 10;

最后， 查询 information_schema.optimizer_trace 就可以知道MySQL是如何执行SQL的 ：

select * from information_schema.optimizer_trace\G

9. MySQL监控分析视图-sys schema

⑴ Sys schema视图摘要

⑵ Sys schema视图使用场景

索引情况

#1. 查询冗余索引 
select * from sys.schema_redundant_indexes; 
#2. 查询未使用过的索引 
select * from sys.schema_unused_indexes; 
#3. 查询索引的使用情况 
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname' ;

表相关

# 1. 查询表的访问量 
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc; 
# 2. 查询占用bufferpool较多的表 
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10; 
# 3. 查看表的全表扫描情况 
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

#1. 监控SQL执行的频率 
select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
#2. 监控使用了排序的SQL 
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1; 
#3. 监控使用了临时表或者磁盘临时表的SQL 
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

IO相关

#1. 查看消耗磁盘IO的文件 
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb 相关

#1. 行锁阻塞情况 
select * from sys.innodb_lock_waits;

十、索引优化与查询优化

1. 数据准备

学员表 插 50万 条， 班级表 插 1万 条。

步骤1：建表

CREATE TABLE `class` ( 
	`id` INT(11) NOT NULL AUTO_INCREMENT, 
	`className` VARCHAR(30) DEFAULT NULL, 
	`address` VARCHAR(40) DEFAULT NULL, 
	`monitor` INT NULL , 
	PRIMARY KEY (`id`) 
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `student` ( 
	`id` INT(11) NOT NULL AUTO_INCREMENT, 
	`stuno` INT NOT NULL , 
	`name` VARCHAR(20) DEFAULT NULL, 
	`age` INT(3) DEFAULT NULL, 
	`classId` INT(11) DEFAULT NULL, 
	PRIMARY KEY (`id`) 
	#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`) 
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步骤2：设置参数

• 命令开启：允许创建函数设置：

  set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。
  

步骤3：创建函数
保证每条数据都不同。

#随机产生字符串 
DELIMITER // 
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) 
BEGIN 
DECLARE chars_str VARCHAR(100) DEFAULT 
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
DECLARE return_str VARCHAR(255) DEFAULT ''; 
DECLARE i INT DEFAULT 0; 
WHILE i < n DO 
SET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); 
SET i = i + 1; 
END WHILE;
RETURN return_str; 
END // 
DELIMITER ; 
#假如要删除 
#drop function rand_string;

随机产生班级编号

#用于随机产生多少到多少的编号 
DELIMITER // 
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11) 
BEGIN 
DECLARE i INT DEFAULT 0; 
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ; 
RETURN i; 
END // 
DELIMITER ; 

#假如要删除 
#drop function rand_num;

步骤4：创建存储过程

#创建往stu表中插入数据的存储过程 
DELIMITER // 
CREATE PROCEDURE insert_stu( START INT , max_num INT ) 
BEGIN 
DECLARE i INT DEFAULT 0; 
	SET autocommit = 0; #设置手动提交事务 
	REPEAT #循环 
	SET i = i + 1; #赋值 
	INSERT INTO student (stuno, name ,age ,classId ) VALUES ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT; #提交事务 
END // 
DELIMITER ; 

#假如要删除 
#drop PROCEDURE insert_stu;

创建往class表中插入数据的存储过程

#执行存储过程，往class表添加随机数据 
DELIMITER // 
CREATE PROCEDURE `insert_class`( max_num INT ) 
BEGIN 
DECLARE i INT DEFAULT 0; 
	SET autocommit = 0; 
	REPEAT 
	SET i = i + 1; INSERT INTO class ( classname,address,monitor ) VALUES (rand_string(8),rand_string(10),rand_num(1,100000)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT;
END // 
DELIMITER ; 

#假如要删除 
#drop PROCEDURE insert_class;

步骤5：调用存储过程
class

#执行存储过程，往class表添加1万条数据 
CALL insert_class(10000);

stu

#执行存储过程，往stu表添加50万条数据 
CALL insert_stu(100000,500000);

步骤6：删除某表上的索引
创建存储过程

DELIMITER // 
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200)) 
BEGIN 
	DECLARE done INT DEFAULT 0; 
	DECLARE ct INT DEFAULT 0; 
	DECLARE _index VARCHAR(200) DEFAULT ''; 
	DECLARE _cur CURSOR FOR SELECT index_name FROM 
	information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY' ; 
	#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束 
	DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ; 
	#若没有数据返回,程序继续,并将变量done设为2 
	OPEN _cur; 
	FETCH _cur INTO _index; 
	WHILE _index<>'' DO 
		SET @str = CONCAT("drop index " , _index , " on " , tablename ); 
		PREPARE sql_str FROM @str ; 
		EXECUTE sql_str; 
		DEALLOCATE PREPARE sql_str; 
		SET _index=''; 
		FETCH _cur INTO _index; 
	END WHILE; 
	CLOSE _cur; 
END // 
DELIMITER ;

执行存储过程

CALL proc_drop_index("dbname","tablename");

2. 索引失效案例

⑴ 全值匹配我最爱

⑵ 最佳左前缀法则

⑶ 主键插入顺序

CREATE TABLE person_info( 
	id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
	name VARCHAR(100) NOT NULL, 
	birthday DATE NOT NULL, 
	phone_number CHAR(11) NOT NULL, 
	country varchar(100) NOT NULL, 
	PRIMARY KEY (id), 
	KEY idx_name_birthday_phone_number (name(10), birthday, phone_number) 
);

⑷ 计算、函数、类型转换(自动或手动)导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

创建索引

CREATE INDEX idx_name ON student(NAME);

第一种：索引优化生效

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

mysql> SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%'; 
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId | 
+---------+---------+--------+------+---------+
| 5301379 | 1233401 | AbCHEa | 164 | 259 | 
| 7170042 | 3102064 | ABcHeB | 199 | 161 | 
| 1901614 | 1833636 | ABcHeC | 226 | 275 | 
| 5195021 | 1127043 | abchEC | 486 | 72 | 
| 4047089 | 3810031 | AbCHFd | 268 | 210 | 
| 4917074 | 849096 | ABcHfD | 264 | 442 | 
| 1540859 | 141979 | abchFF | 119 | 140 | 
| 5121801 | 1053823 | AbCHFg | 412 | 327 | 
| 2441254 | 2373276 | abchFJ | 170 | 362 | 
| 7039146 | 2971168 | ABcHgI | 502 | 465 | 
| 1636826 | 1580286 | ABcHgK | 71 | 262 | 
| 374344 | 474345 | abchHL | 367 | 212 | 
| 1596534 | 169191 | AbCHHl | 102 | 146 |
 ... 
| 5266837 | 1198859 | abclXe | 292 | 298 | 
| 8126968 | 4058990 | aBClxE | 316 | 150 | 
| 4298305 | 399962 | AbCLXF | 72 | 423 | 
| 5813628 | 1745650 | aBClxF | 356 | 323 | 
| 6980448 | 2912470 | AbCLXF | 107 | 78 | 
| 7881979 | 3814001 | AbCLXF | 89 | 497 | 
| 4955576 | 887598 | ABcLxg | 121 | 385 | 
| 3653460 | 3585482 | AbCLXJ | 130 | 174 | 
| 1231990 | 1283439 | AbCLYH | 189 | 429 | 
| 6110615 | 2042637 | ABcLyh | 157 | 40 | 
+---------+---------+--------+------+---------+
401 rows in set, 1 warning (0.01 sec)

第二种：索引优化失效

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

mysql> SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; 
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 5301379 | 1233401 | AbCHEa | 164 | 259 | 
| 7170042 | 3102064 | ABcHeB | 199 | 161 | 
| 1901614 | 1833636 | ABcHeC | 226 | 275 | 
| 5195021 | 1127043 | abchEC | 486 | 72 | 
| 4047089 | 3810031 | AbCHFd | 268 | 210 | 
| 4917074 | 849096 | ABcHfD | 264 | 442 | 
| 1540859 | 141979 | abchFF | 119 | 140 | 
| 5121801 | 1053823 | AbCHFg | 412 | 327 | 
| 2441254 | 2373276 | abchFJ | 170 | 362 | 
| 7039146 | 2971168 | ABcHgI | 502 | 465 | 
| 1636826 | 1580286 | ABcHgK | 71 | 262 | 
| 374344 | 474345 | abchHL | 367 | 212 | 
| 1596534 | 169191 | AbCHHl | 102 | 146 |
	 ... 
| 5266837 | 1198859 | abclXe | 292 | 298 | 
| 8126968 | 4058990 | aBClxE | 316 | 150 | 
| 4298305 | 399962 | AbCLXF | 72 | 423 | 
| 5813628 | 1745650 | aBClxF | 356 | 323 | 
| 6980448 | 2912470 | AbCLXF | 107 | 78 | 
| 7881979 | 3814001 | AbCLXF | 89 | 497 | 
| 4955576 | 887598 | ABcLxg | 121 | 385 | 
| 3653460 | 3585482 | AbCLXJ | 130 | 174 | 
| 1231990 | 1283439 | AbCLYH | 189 | 429 | 
| 6110615 | 2042637 | ABcLyh | 157 | 40 | 
+---------+---------+--------+------+---------+
401 rows in set, 1 warning (3.62 sec)

type为“ALL”，表示没有使用到索引，查询时间为 3.62 秒，查询效率较之前低很多。

再举例：

• student表的字段stuno上设置有索引

  CREATE INDEX idx_sno ON student(stuno);
  

  EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
  

  运行结果：
  

• 索引优化生效：

  EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;
  

再举例：

• student表的字段name上设置有索引

  CREATE INDEX idx_name ON student(NAME);
  

  EXPLAIN SELECT id, stuno, name FROM student WHERE SUBSTRING(name, 1,3)='abc';
  

  

  EXPLAIN SELECT id, stuno, NAME FROM student WHERE NAME LIKE 'abc%';
  

  

⑸ 类型转换导致索引失效

下列哪个sql语句可以用到索引。（假设name字段上设置有索引）

# 未使用到索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

# 使用到索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

• name=123发生类型转换，索引失效。

⑹ 范围条件右边的列索引失效

ALTER TABLE student DROP INDEX idx_name; 
ALTER TABLE student DROP INDEX idx_age; 
ALTER TABLE student DROP INDEX idx_age_classid; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND 
student.classId>20 AND student.name = 'abc' ;

create index idx_age_name_classid on student(age,name,classid);

• 将范围查询条件放置语句最后：

  EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20 ;
  

  

⑺ 不等于(!= 或者<>)索引失效

⑻ is null可以使用索引，is not null无法使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

⑼ like以通配符%开头索引失效

⑽ OR 前后存在非索引的列，索引失效

# 未使用到索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

#使用到索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';

⑾ 数据库和表的字符集统一使用utf8mb4

3. 关联查询优化

⑴ 数据准备

⑵ 采用左外连接

下面开始 EXPLAIN 分析

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

添加索引优化

ALTER TABLE book ADD INDEX Y ( card); #【被驱动表】，可以避免全表扫描 

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

可以看到第二行的 type 变为了 ref，rows 也变成了优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以 右边是我们的关键点,一定需要建立索引 。

ALTER TABLE `type` ADD INDEX X (card); #【驱动表】，无法避免全表扫描 

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

接着：

DROP INDEX Y ON book; 

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

⑶ 采用内连接

drop index X on type; 
drop index Y on book;（如果已经删除了可以不用再执行该操作）

换成 inner join（MySQL自动选择驱动表）

EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

添加索引优化

ALTER TABLE book ADD INDEX Y ( card); 

EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

ALTER TABLE type ADD INDEX X (card); 

EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

接着：

DROP INDEX X ON `type`; 

EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

接着：

ALTER TABLE `type` ADD INDEX X (card); 

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;

⑷ join语句原理

• Index Nested-Loop Join
  我们来看一下这个语句：

  EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.a);
  

  
  执行流程图也就变成这样：
  

⑸ 小结

4. 子查询优化

5. 排序优化

⑴ 排序优化

INDEX a_b_c(a,b,c) 

order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC 

如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c 

不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

⑵ 案例实战

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。
执行案例前先清除student上的索引，只留主键：

DROP INDEX idx_age ON student; 
DROP INDEX idx_age_classid_stuno ON student; 
DROP INDEX idx_age_classid_name ON student; 

#或者 
call proc_drop_index('atguigudb2','student');

场景:查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

查询结果如下：

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ; 
+---------+--------+--------+------+---------+
| id | stuno | name | age | classId | 
+---------+--------+--------+------+---------+
| 922 | 100923 | elTLXD | 30 | 249 | 
| 3723263 | 100412 | hKcjLb | 30 | 59 | 
| 3724152 | 100827 | iHLJmh | 30 | 387 | 
| 3724030 | 100776 | LgxWoD | 30 | 253 | 
| 30 | 100031 | LZMOIa | 30 | 97 | 
| 3722887 | 100237 | QzbJdx | 30 | 440 | 
| 609 | 100610 | vbRimN | 30 | 481 | 
| 139 | 100140 | ZqFbuR | 30 | 351 | 
+---------+--------+--------+------+---------+
8 rows in set, 1 warning (3.16 sec)

优化思路：

方案一: 为了去掉filesort我们可以把索引建成

#创建新索引 
CREATE INDEX idx_age_name ON student(age,NAME);

方案二: 尽量让where的过滤条件和排序使用上索引
建一个三个字段的组合索引：

DROP INDEX idx_age_name ON student; 

CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME); 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

mysql> SELECT SQL_NO_CACHE * FROM student
-> WHERE age = 30 AND stuno <101000 ORDER BY NAME ; 
+-----+--------+--------+------+---------+
| id | stuno | name | age | classId | 
+-----+--------+--------+------+---------+
| 167 | 100168 | AClxEF | 30 | 319 | 
| 323 | 100324 | bwbTpQ | 30 | 654 | 
| 651 | 100652 | DRwIac | 30 | 997 | 
| 517 | 100518 | HNSYqJ | 30 | 256 | 
| 344 | 100345 | JuepiX | 30 | 329 | 
| 905 | 100906 | JuWALd | 30 | 892 | 
| 574 | 100575 | kbyqjX | 30 | 260 | 
| 703 | 100704 | KJbprS | 30 | 594 | 
| 723 | 100724 | OTdJkY | 30 | 236 | 
| 656 | 100657 | Pfgqmj | 30 | 600 | 
| 982 | 100983 | qywLqw | 30 | 837 | 
| 468 | 100469 | sLEKQW | 30 | 346 | 
| 988 | 100989 | UBYqJl | 30 | 457 | 
| 173 | 100174 | UltkTN | 30 | 830 | 
| 332 | 100333 | YjWiZw | 30 | 824 | 
+-----+--------+--------+------+---------+
15 rows in set, 1 warning (0.00 sec)

思考：这里我们使用如下索引，是否可行？

DROP INDEX idx_age_stuno_name ON student; 

CREATE INDEX idx_age_stuno ON student(age,stuno);

⑶ filesort算法：双路排序和单路排序

6. GROUP BY优化

7. 优化分页查询

优化思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;

优化思路二

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

8. 优先考虑覆盖索引

⑴ 什么是覆盖索引？

⑵ 覆盖索引的利弊

9. 如何给字符串添加索引

有一张教师表，表定义如下：

create table teacher( 
ID bigint unsigned primary key, 
email varchar(64),
 ... 
)engine=innodb;

讲师要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：

mysql> select col1, col2 from teacher where email='xxx';

如果email这个字段上没有索引，那么这个语句就只能做 全表扫描 。

⑴ 前缀索引

MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

mysql> alter table teacher add index index1(email); 
#或
mysql> alter table teacher add index index2(email(6));

这两种不同的定义在数据结构和存储上有什么区别呢？下图就是这两个索引的示意图。

⑵ 前缀索引对覆盖索引的影响

10. 索引下推

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

⑴ 使用前后的扫描过程

使用ICP扫描的过程：

• storage层：
  首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。
• server 层：
  对返回的数据，使用table filter条件做最后的过滤。
  

⑵ ICP的使用条件

⑶ ICP使用案例

11. 普通索引 vs 唯一索引

mysql> create table test( 
id int primary key, 
k int not null, 
name varchar(16), 
index (k) 
)engine=InnoDB;

表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)。

⑴ 查询过程

⑵ 更新过程

⑶ change buffer的使用场景

12. 其它查询优化策略

⑴ EXISTS 和 IN 的区分

⑵ COUNT(*)与COUNT(具体字段)效率

⑶ 关于SELECT(*)

⑷ LIMIT 1 对优化的影响

⑸ 多使用COMMIT

13. 淘宝数据库，主键如何设计的？

⑴ 自增ID的问题

⑵ 业务字段做主键

mysql> CREATE TABLE demo.membermaster
-> (
-> cardno CHAR(8) PRIMARY KEY, -- 会员卡号为主键
-> membername TEXT,
-> memberphone TEXT,
-> memberpid TEXT,
-> memberaddress TEXT,
-> sex TEXT,
-> birthday DATETIME
-> ); Query OK, 0 rows affected (0.06 sec)

mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
-> FROM demo.trans AS a
-> JOIN demo.membermaster AS b
-> JOIN demo.goodsmaster AS c
-> ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber); +------------+-----------+----------+------------+---------------------+
| membername | goodsname | quantity | salesvalue | transdate | +------------+-----------+----------+------------+---------------------+
| 张三 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 | +------------+-----------+----------+------------+---------------------+
1 row in set (0.00 sec)

如果会员卡“10000001”又发给了王五，我们会更改会员信息表。导致查询时：

mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
-> FROM demo.trans AS a
-> JOIN demo.membermaster AS b
-> JOIN demo.goodsmaster AS c
-> ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber); 
+------------+-----------+----------+------------+---------------------+
| membername | goodsname | quantity | salesvalue | transdate | 
+------------+-----------+----------+------------+---------------------+
| 王五 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 | 
+------------+-----------+----------+------------+---------------------+
1 row in set (0.01 sec)

这次得到的结果是：王五在 2020 年 12 月 01 日，买了一本书，消费 89 元。显然是错误的！结论：千万不能把会员卡号当做主键。

⑶ 淘宝的主键设计

⑷ 推荐的主键设计

SET @uuid = UUID(); 

SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

通过函数uuid_to_bin(@uuid,true)将UUID转化为有序UUID了。全局唯一 + 单调递增，这不就是我们想要的主键！

有序UUID性能测试

十一、数据库的设计规范

1. 为什么需要数据库设计

2. 范 式

⑴ 范式简介

⑵ 范式都包括哪些

⑶ 键和相关属性的概念

⑷ 第一范式(1st NF)

⑸ 第二范式(2nd NF)

⑹ 第三范式(3rd NF)

⑺ 小结

3. 反范式化

⑴ 概述

⑵ 应用举例

⑶ 反范式的新问题

⑷ 反范式的适用场景

1) 增加冗余字段的建议

2) 历史快照、历史数据的需要

4. BCNF(巴斯范式)

5. 第四范式

6. 第五范式、域键范式

8. ER模型

⑴ 包含的要素

⑵ 关系的类型

⑶ 建模分析

⑷ ER 模型的细化

⑸ ER 模型图转换成数据表

9. 数据表的设计原则

10. 数据库对象编写建议

⑴ 关于库

⑵ 关于表、列

CREATE TABLE user_info ( 
	`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键', 
	`user_id` bigint(11) NOT NULL COMMENT '用户id', 
	`username` varchar(45) NOT NULL COMMENT '真实姓名', 
	`email` varchar(30) NOT NULL COMMENT '用户邮箱', 
	`nickname` varchar(45) NOT NULL COMMENT '昵称', 
	`birthday` date NOT NULL COMMENT '生日', 
	`sex` tinyint(4) DEFAULT '0' COMMENT '性别', 
	`short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
	`user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址', 
	`user_register_ip` int NOT NULL COMMENT '用户注册时的源ip', 
	`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', 
	`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', 
	`user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未 通过，4为还未提交审核',
	PRIMARY KEY (`id`), 
	UNIQUE KEY `uniq_user_id` (`user_id`), 
	KEY `idx_username`(`username`), 
	KEY `idx_create_time_status`(`create_time`,`user_review_status`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息'

⑶ 关于索引

⑷ SQL编写

11. PowerDesigner的使用

⑴ 开始界面

⑵ 概念数据模型

⑶ 物理数据模型

⑷ 概念模型转为物理模型

⑸ 物理模型转为概念模型

上面介绍了概念模型转物理模型，下面介绍一下物理模型转概念模型（如下图点击操作即可）

⑹ 物理模型导出SQL语句

十二. 数据库其它调优策略

1. 数据库调优的措施

⑴ 调优的目标

⑵ 如何定位调优问题

⑶ 调优的维度和步骤

2. 优化MySQL服务器

⑴ 优化服务器硬件

⑵ 优化MySQL的参数

3. 优化数据库结构

⑴ 拆分表：冷热数据分离

CREATE TABLE members ( 
	id int(11) NOT NULL AUTO_INCREMENT, 
	username varchar(50) DEFAULT NULL, 
	password varchar(50) DEFAULT NULL, 
	last_login_time datetime DEFAULT NULL, 
	last_login_ip varchar(100) DEFAULT NULL, 
	PRIMARY KEY(Id) 
);

CREATE TABLE members_detail ( 
	Member_id int(11) NOT NULL DEFAULT 0, 
	address varchar(255) DEFAULT NULL, 
	telephone varchar(255) DEFAULT NULL, 
	description text 
);

如果需要查询会员的基本信息或详细信息，那么可以用会员的id来查询。如果需要将会员的基本信息和
详细信息同时显示，那么可以将members表和members_detail表进行联合查询，查询语句如下：

SELECT * FROM members LEFT JOIN members_detail on members.id = members_detail.member_id;

通过这种分解可以提高表的查询效率。对于字段很多且有些字段使用不频繁的表，可以通过这种分解的方式来优化数据库的性能。

⑵ 增加中间表

CREATE TABLE `class` ( 
	`id` INT(11) NOT NULL AUTO_INCREMENT, 
	`className` VARCHAR(30) DEFAULT NULL, 
	`address` VARCHAR(40) DEFAULT NULL, 
	`monitor` INT NULL , PRIMARY KEY (`id`) 
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 

CREATE TABLE `student` ( 
	`id` INT(11) NOT NULL AUTO_INCREMENT, 
	`stuno` INT NOT NULL ,
	`name` VARCHAR(20) DEFAULT NULL, 
	`age` INT(3) DEFAULT NULL, 
	`classId` INT(11) DEFAULT NULL, 
	PRIMARY KEY (`id`) 
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `temp_student` ( 
	`id` INT(11) NOT NULL AUTO_INCREMENT, 
	`stu_name` INT NOT NULL , 
	`className` VARCHAR(20) DEFAULT NULL, 
	`monitor` INT(3) DEFAULT NULL, 
	PRIMARY KEY (`id`) 
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

接下来，从学生信息表和班级表中查询相关信息存储到临时表中：

insert into temp_student(stu_name,className,monitor) 
	select s.name,c.className,c.monitor
	from student as s,class as c 
	where s.classId = c.id

以后，可以直接从temp_student表中查询学生名称、班级名称和班级班长，而不用每次都进行联合查询。这样可以提高数据库的查询速度。

⑶ 增加冗余字段

⑷ 优化数据类型

⑸ 优化插入记录的速度

insert into student values(1,'zhangsan',18,1); 
insert into student values(2,'lisi',17,1); 
insert into student values(3,'wangwu',17,1); 
insert into student values(4,'zhaoliu',19,1);

使用一条INSERT语句插入多条记录的情形如下：

insert into student values 
(1,'zhangsan',18,1), 
(2,'lisi',17,1), 
(3,'wangwu',17,1), 
(4,'zhaoliu',19,1);

第2种情形的插入速度要比第1种情形快。

⑹ 使用非空约束

⑺ 分析表、检查表与优化表

① 分析表

MySQL中提供了ANALYZE TABLE语句分析表，ANALYZE TABLE语句的基本语法如下：

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…

② 检查表

CHECK TABLE tbl_name [, tbl_name] ... [option] ... 
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

③ 优化表

⑻ 小结

4. 大表优化

⑴ 限定查询的范围

⑵ 读/写分离

⑶ 垂直拆分

⑷ 水平拆分

5. 其它调优策略

⑴ 服务器语句超时处理

SET GLOBAL MAX_EXECUTION_TIME=2000;

SET SESSION MAX_EXECUTION_TIME=2000; #指定该会话中SELECT语句的超时时间

⑵ MySQL 8.0新特性：隐藏索引对调优的帮助

十三、事务基础知识

1. 数据库事务概述

⑴ 存储引擎支持情况

⑵ 基本概念

⑶ 事务的ACID特性

⑷ 事务的状态

2. 如何使用事务

⑴ 显式事务

⑵ 隐式事务

⑶ 隐式提交数据的情况

⑷ 使用举例1：提交与回滚

我们看下在 MySQL 的默认状态下，下面这个事务最后的处理结果是什么。
情况1：

CREATE TABLE user(name varchar(20), PRIMARY KEY (name)) ENGINE=InnoDB; 

BEGIN; 
INSERT INTO user SELECT '张三'; 
COMMIT; 

BEGIN; 
INSERT INTO user SELECT '李四'; 
INSERT INTO user SELECT '李四'; 
ROLLBACK; 

SELECT * FROM user;

运行结果（1 行数据）：

mysql> commit; 
Query OK, 0 rows affected (0.00 秒) 

mysql> BEGIN; 
Query OK, 0 rows affected (0.00 秒) 

mysql> INSERT INTO user SELECT '李四'; 
Query OK, 1 rows affected (0.00 秒) 

mysql> INSERT INTO user SELECT '李四'; 
Duplicate entry '李四' for key 'user.PRIMARY' 
mysql> ROLLBACK; 
Query OK, 0 rows affected (0.01 秒) 

mysql> select * from user; 
+--------+
| name | 
+--------+
| 张三 | 
+--------+
1 行于数据集 (0.01 秒)

情况2：

CREATE TABLE user (name varchar(20), PRIMARY KEY (name)) ENGINE=InnoDB; 

BEGIN; 
INSERT INTO user SELECT '张三'; 
COMMIT; 

INSERT INTO user SELECT '李四'; 
INSERT INTO user SELECT '李四'; 
ROLLBACK;

运行结果（2 行数据）：

mysql> SELECT * FROM user; 
+--------+
| name | 
+--------+
| 张三 | | 李四 | 
+--------+
2 行于数据集 (0.01 秒)

情况3：

CREATE TABLE user(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB; 

SET @@completion_type = 1; 
BEGIN; 
INSERT INTO user SELECT '张三'; 
COMMIT;
 
INSERT INTO user SELECT '李四'; 
INSERT INTO user SELECT '李四'; 
ROLLBACK; 

SELECT * FROM user;

运行结果（1 行数据）：

mysql> SELECT * FROM user; 
+--------+
| name | 
+--------+
| 张三 | 
+--------+
1 行于数据集 (0.01 秒)

3. 事务隔离级别

⑴ 数据准备

我们需要创建一个表：

CREATE TABLE student ( 
	studentno INT, 
	name VARCHAR(20), 
	class varchar(20), 
	PRIMARY KEY (studentno) 
) Engine=InnoDB CHARSET=utf8;

然后向这个表里插入一条数据：

INSERT INTO student VALUES(1, '小谷', '1班');

现在表里的数据就是这样的：

mysql> select * from student; 
+-----------+--------+-------+
| studentno | name | class | 
+-----------+--------+-------+
| 1 | 小谷 | 1班 | 
+-----------+--------+-------+
1 row in set (0.00 sec)

⑵ 数据并发问题

⑶ SQL中的四种隔离级别

⑷ MySQL支持的四种隔离级别

MySQL的默认隔离级别为REPEATABLE READ，我们可以手动修改一下事务的隔离级别。

# 查看隔离级别，MySQL 5.7.20的版本之前： 
mysql> SHOW VARIABLES LIKE 'tx_isolation'; 
+---------------+-----------------+
| Variable_name | Value | 
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ | 
+---------------+-----------------+
1 row in set (0.00 sec) 

# MySQL 5.7.20版本之后，引入transaction_isolation来替换tx_isolation 

# 查看隔离级别，MySQL 5.7.20的版本及之后： 
mysql> SHOW VARIABLES LIKE 'transaction_isolation'; 
+-----------------------+-----------------+
| Variable_name | Value | 
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ | 
+-----------------------+-----------------+
1 row in set (0.02 sec) 
#或者不同MySQL版本中都可以使用的： 
SELECT @@transaction_isolation;

⑸ 如何设置事务的隔离级别

通过下面的语句修改事务的隔离级别：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别; 
#其中，隔离级别格式： 
> READ UNCOMMITTED 
>  READ COMMITTED
> REPEATABLE READ
> SERIALIZABLE

或者：

SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别' 

#其中，隔离级别格式： 
> READ-UNCOMMITTED 
> READ-COMMITTED 
> REPEATABLE-READ 
> SERIALIZABLE

⑹ 不同隔离级别举例

4. 事务的常见分类

十四、 MySQL事务日志

1. redo日志

⑴ 为什么需要REDO日志

⑵ REDO日志的好处、特点

1) 好处

2) 特点

⑶ redo的组成

⑷ redo的整体流程

以一个更新事务为例，redo log 流转过程，如下图所示：

⑸ redo log的刷盘策略

⑹ 不同刷盘策略演示

1) 流程图

⑺ 写入redo log buffer 过程

1) 补充概念：Mini-Transaction

2) redo 日志写入log buffer

每个mtr都会产生一组redo日志，用示意图来描述一下这些mtr产生的日志情况：

3) redo log block的结构图

⑻ redo log file

1) 相关参数设置

2) 日志文件组

3) checkpoint

⑼ redo log 总结

2. Undo日志

⑴ 如何理解Undo日志

⑵ Undo日志的作用

• 作用1：回滚数据
  

• 作用2：MVCC
  

⑶ undo的存储结构

1) 回滚段与undo页

mysql> show variables like 'innodb_undo_logs'; 
+------------------+-------+
| Variable_name | Value | 
+------------------+-------+
| innodb_undo_logs | 128 | 
+------------------+-------+

2) 回滚段与事务

3) 回滚段中的数据分类

⑷ undo的类型

⑸ undo log的生命周期

1) 简要生成过程

2) 详细生成过程

3) undo log是如何回滚的

4) undo log的删除

⑹ 小结

十五、锁

1. 概述

2. MySQL并发事务访问相同记录

并发事务访问相同记录的情况大致可以划分为3种：

⑴ 读-读情况

读-读 情况，即并发事务相继 读取相同的记录 。读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生。

⑵ 写-写情况

写-写 情况，即并发事务相继对相同的记录做出改动。
在这种情况下会发生脏写的问题，任何一种隔离级别都不允许这种问题的发生。所以在多个未提交事务相继对一条记录做改动时，需要让它们 排队执行 ，这个排队的过程其实是通过 锁 来实现的。这个所谓的锁其实是一个 内存中的结构 ，在事务执行前本来是没有锁的，也就是说一开始是没有 锁结构 和记录进行关联的，如图所示：

当一个事务想对这条记录做改动时，首先会看看内存中有没有与这条记录关联的 锁结构 ，当没有的时候就会在内存中生成一个 锁结构 与之关联。比如，事务 T1 要对这条记录做改动，就需要生成一个 锁结构与之关联：

⑶ 读-写或写-读情况

⑷ 并发问题的解决方案

3. 锁的不同角度分类

锁的分类图，如下：

⑴ 从数据操作的类型划分：读锁、写锁

⑵ 从数据操作的粒度划分：表级锁、页级锁、行锁

1) 表锁（Table Lock）

① 表级别的S锁、X锁

② 意向锁 （intention lock）

③ 自增锁（AUTO-INC锁）

在使用MySQL过程中，我们可以为表的某个列添加AUTO_INCREMENT属性。举例：

CREATE TABLE `teacher` ( 
	`id` int NOT NULL AUTO_INCREMENT, 
	`name` varchar(255) NOT NULL, 
	PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

由于这个表的id字段声明了AUTO_INCREMENT，意味着在书写插入语句时不需要为其赋值，SQL语句修改如下所示。

INSERT INTO `teacher` (name) VALUES ('zhangsan'), ('lisi');

上边的插入语句并没有为id列显式赋值，所以系统会自动为它赋上递增的值，结果如下所示。

mysql> select * from teacher; 
+----+----------+
| id | name | 
+----+----------+
| 1 | zhangsan | 
| 2 | lisi | 
+----+----------+
2 rows in set (0.00 sec)

④ 元数据锁（MDL锁）

2) InnoDB中的行锁

① 记录锁（Record Locks）

② 间隙锁（Gap Locks）

③ 临键锁（Next-Key Locks）

④ 插入意向锁（Insert Intention Locks）

3) 页锁

⑶ 从对待锁的态度划分:乐观锁、悲观锁

从对待锁的态度来看锁的话，可以将锁分成乐观锁和悲观锁，从名字中也可以看出这两种锁是两种看待数据并发的思维方式 。需要注意的是，乐观锁和悲观锁并不是锁，而是锁的 设计思想 。

1) 悲观锁（Pessimistic Locking）

2) 乐观锁（Optimistic Locking）

3) 两种锁的适用场景

⑷ 按加锁的方式划分：显式锁、隐式锁

1) 隐式锁

2) 显式锁

通过特定的语句进行加锁，我们一般称之为显示加锁，例如：
显示加共享锁：

select .... lock in share mode

显示加排它锁：

select .... for update

⑸ 其它锁之：全局锁

Flush tables with read lock

⑹ 其它锁之：死锁

4. 锁的内存结构

InnoDB 存储引擎中的 锁结构 如下：

5. 锁监控

6. 附录

十六、多版本并发控制

1. 什么是MVCC

MVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的多个版本管理来实现数据库的并发控制。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

2. 快照读与当前读

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理 读-写冲突 ，做到即使有读写冲突时，也能做到不加锁 ， 非阻塞并发读，而这个读指的就是 快照读 , 而非 当前读 。当前读实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。

⑴ 快照读

⑵ 当前读

当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的 SELECT，或者对数据进行增删改都会进行当前读。比如：

SELECT * FROM student LOCK IN SHARE MODE; # 共享锁

SELECT * FROM student FOR UPDATE; # 排他锁

INSERT INTO student values ... # 排他锁

DELETE FROM student WHERE ... # 排他锁

UPDATE student SET ... # 排他锁

3. 复习

⑴ 再谈隔离级别

我们知道事务有 4 个隔离级别，可能存在三种并发问题：

⑵ 隐藏字段、Undo Log版本链

4. MVCC实现原理之ReadView

MVCC 的实现依赖于：隐藏字段、Undo Log、Read View。

⑴ 什么是ReadView

⑵ 设计思路

⑶ ReadView的规则

⑷ MVCC整体操作流程

5. 举例说明

⑴ READ COMMITTED隔离级别下

⑵ REPEATABLE READ隔离级别下

使用 REPEATABLE READ 隔离级别的事务来说，只会在第一次执行查询语句时生成一个 ReadView ，之后的查询就不会重复生成了。
比如，系统里有两个事务id分别为 10 、 20 的事务在执行：

⑶ 如何解决幻读

6. 总结

十七、其他数据库日志

1. MySQL支持的日志

⑴ 日志类型

⑵ 日志的弊端

2. 慢查询日志(slow query log)

前面章节《第09章_性能分析工具的使用》已经详细讲述。

3. 通用查询日志(general query log)

⑴ 问题场景

⑵ 查看当前状态

⑶ 启动日志

⑷ 查看日志

⑸ 停止日志

⑹ 删除\刷新日志

4. 错误日志(error log)

⑴ 启动日志

⑵ 查看日志

⑶ 删除\刷新日志

5. 二进制日志(bin log)

⑴ 查看默认情况

查看记录二进制日志是否开启：在MySQL8中默认情况下，二进制文件是开启的。

⑵ 日志参数设置

⑶ 查看日志

⑷ 使用日志恢复数据

⑸ 删除二进制日志

⑹ 其它场景

6. 再谈二进制日志(binlog)

⑴ 写入机制

⑵ binlog与redolog对比

⑶ 两阶段提交

7. 中继日志(relay log)

⑴ 介绍

⑵ 查看中继日志

⑶ 恢复的典型错误

十八、主从复制

1. 主从复制概述

⑴ 如何提升数据库并发能力

⑵ 主从复制的作用

主从同步设计不仅可以提高数据库的吞吐量，还有以下 3 个方面的作用。

2. 主从复制的原理

Slave 会从 Master 读取 binlog 来进行数据同步。

⑴ 原理剖析

⑵ 复制的基本原则

3. 一主一从架构搭建

⑴ 准备工作

vim /var/lib/mysql/auto.cnf 

systemctl restart mysqld

⑵ 主机配置文件

建议mysql版本一致且后台以服务运行，主从所有配置项都配置在 [mysqld] 节点下，且都是小写字母。
文件目录：

具体参数配置如下：

• 必选

#[必须]主服务器唯一ID 
server-id=1 

#[必须]启用二进制日志,指名路径。比如：自己本地的路径/log/mysqlbin 
log-bin=atguigu-bin

• 可选

#[可选] 0（默认）表示读写（主机），1表示只读（从机） 
read-only=0 

#设置日志文件保留的时长，单位是秒 
binlog_expire_logs_seconds=6000 

#控制单个二进制日志大小。此参数的最大和默认值是1GB 
max_binlog_size=200M 

#[可选]设置不要复制的数据库 
binlog-ignore-db=test 

#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave 
binlog-do-db=需要复制的主数据库名字 

#[可选]设置binlog格式 
binlog_format=STATEMENT

⑶ 从机配置文件

要求主从所有配置项都配置在 my.cnf 的 [mysqld] 栏位下，且都是小写字母。

• 必选

#[必须]从服务器唯一ID 
server-id=2

• 可选

#[可选]启用中继日志 
relay-log=mysql-relay

⑷ 主机：建立账户并授权

#在主机MySQL里执行授权主从复制的命令 
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'从机器数据库IP' IDENTIFIED BY 'abc123'; 
#5.5,5.7

注意：如果使用的是MySQL8，需要如下的方式建立账户，并授权slave：

CREATE USER 'slave1'@'%' IDENTIFIED BY '123456'; 

GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%'; 

#此语句必须执行。否则见下面。 
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

flush privileges;

查询Master的状态，并记录下File和Position的值。

show master status;

⑸ 从机：配置需要复制的主机

步骤1：从机上复制主机的命令

CHANGE MASTER TO 
MASTER_HOST='主机的IP地址', 
MASTER_USER='主机用户名', 
MASTER_PASSWORD='主机用户名的密码', 
MASTER_LOG_FILE='mysql-bin.具体数字', 
MASTER_LOG_POS=具体值;

举例：

CHANGE MASTER TO 
MASTER_HOST='192.168.1.150',
MASTER_USER='slave1',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='atguigu-bin.000007',
MASTER_LOG_POS=154;

步骤2：

#启动slave同步 
START SLAVE;

mysql> reset slave; #删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件

接着，查看同步状态：

SHOW SLAVE STATUS\G;

⑹ 测试

主机新建库、新建表、insert记录，从机复制：

CREATE DATABASE atguigu_master_slave; 

CREATE TABLE mytbl(id INT,NAME VARCHAR(16));
 
INSERT INTO mytbl VALUES(1, 'zhang3'); 

INSERT INTO mytbl VALUES(2,@@hostname);

⑺ 停止主从同步

• 停止主从同步命令：

stop slave;

• 如何重新配置主从
  

  stop slave; 
  reset master; #删除Master中所有的binglog文件，并将日志索引文件清空，重新开始所有新的日志文件(慎用)
  

⑻ 后续

搭建主从复制：双主双从

4. 同步数据一致性问题

⑴ 理解主从延迟问题

⑵ 主从延迟问题原因

⑶ 如何减少主从延迟

⑷ 如何解决一致性问题

5. 知识延伸

十九、数据库备份与恢复

1. 物理备份与逻辑备份

2. mysqldump实现逻辑备份

⑴ 备份一个数据库

基本语法：

mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名 称.sql

[root@localhost opt]# mysqldump -uroot -p kejizhentan>kejizhentan.sql #备份文件存储在当前目录下

[root@localhost opt]# mysqldump -uroot -p kejizhentan>/var/lib/mysql/student.sql

备份文件剖析：

-- MySQL dump 10.13  Distrib 8.0.25, for Linux (x86_64)
--
-- Host: localhost    Database: kejizhentan
-- ------------------------------------------------------
-- Server version	8.0.25

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student` (
  `student_id` int NOT NULL AUTO_INCREMENT,
  `class_id` int DEFAULT NULL,
  `student_name` varchar(15) NOT NULL,
  `student_age` int DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,1,'张三',11),(2,2,'李四',13);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-10-04  7:59:32

⑵ 备份全部数据库

若想用mysqldump备份整个实例，可以使用 --all-databases 或 -A 参数：

[root@localhost bakup]# mysqldump -uroot -p --all-databases > all_databases.sql


[root@localhost bakup]# mysqldump -uroot -p -A  > all_databases.sql

⑶ 备份部分数据库

使用 --databases 或 -B 参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定databases参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。语法如下：

mysqldump –u user –h host –p --databases [数据库的名称1 [数据库的名称2…]] > 备份文件名 称.sql

举例：

[root@localhost bakup]# mysqldump -uroot -p --databases kejizhentan kjzt >tow_database.sql

[root@localhost bakup]# mysqldump -uroot -p -B kejizhentan kjzt > tow_database.sql

⑷ 备份部分表

比如，在表变更前做个备份。语法如下：

mysqldump –u user –h host –p 数据库的名称 [表名1 [表名2…]] > 备份文件名称.sql

举例：备份kejizhentan数据库下的student表

[root@localhost bakup]# mysqldump -uroot -p kejizhentan student > part_table_student.sql;

tables_student.sql文件内容如下

-- MySQL dump 10.13  Distrib 8.0.25, for Linux (x86_64)
--
-- Host: localhost    Database: kejizhentan
-- ------------------------------------------------------
-- Server version	8.0.25

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student` (
  `student_id` int NOT NULL AUTO_INCREMENT,
  `class_id` int DEFAULT NULL,
  `student_name` varchar(15) NOT NULL,
  `student_age` int DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,1,'张三',11),(2,2,'李四',13);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-10-04  8:38:15

可以看到，book文件和备份的库文件类似。不同的是，book文件只包含book表的DROP、CREATE和 INSERT语句。

备份多张表使用下面的命令，比如备份student和teacher表：

#备份多张表 mysqldump -uroot -p kejizhentan student teacher > 2_tables_bak.sql

⑸ 备份单表的部分数据

有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用 --where 选项了。where后面附带需要满足的条件。
举例：备份student表中id小于3的数据：

[root@localhost bakup]# mysqldump -uroot -p kejizhentan student --where="student_id < 3" > student_part_id3_low_bak.sql

内容如下所示，insert语句只有id小于3的部分

-- MySQL dump 10.13  Distrib 8.0.25, for Linux (x86_64)
--
-- Host: localhost    Database: kejizhentan
-- ------------------------------------------------------
-- Server version	8.0.25

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student` (
  `student_id` int NOT NULL AUTO_INCREMENT,
  `class_id` int DEFAULT NULL,
  `student_name` varchar(15) NOT NULL,
  `student_age` int DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--
-- WHERE:  student_id < 3

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,1,'张三',11),(2,2,'李四',13);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-10-04  8:46:22

⑹ 排除某些表的备份

如果我们想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些表，同样的，选项 --ignore-table 可以完成这个功能。

[root@localhost bakup]# mysqldump -uroot -p kejizhentan --ignore-table=kejizhentan.student > no_stu_bak.sql;

通过如下指定判定文件中没有student表结构：

-- MySQL dump 10.13  Distrib 8.0.25, for Linux (x86_64)
--
-- Host: localhost    Database: kejizhentan
-- ------------------------------------------------------
-- Server version	8.0.25

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `teacher`
--

DROP TABLE IF EXISTS `teacher`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `teacher` (
  `teacher_id` int NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(15) NOT NULL,
  `teacher_age` int NOT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `teacher`
--

LOCK TABLES `teacher` WRITE;
/*!40000 ALTER TABLE `teacher` DISABLE KEYS */;
INSERT INTO `teacher` VALUES (1,'张老师',25),(2,'王老师',28);
/*!40000 ALTER TABLE `teacher` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-10-04  8:53:41

⑺ 只备份结构或只备份数据

只备份结构的话可以使用 --no-data 简写为 -d 选项；只备份数据可以使用--no-create-info 简写为 -t 选项。

• 只备份结构

mysqldump -uroot -p kejizhentan --no-data > kejizhentan_no_data_bak.sql 
#使用grep命令，没有找到insert相关语句，表示没有数据备份。 
[root@node1 ~]# grep "INSERT" kejizhentan_no_data_bak.sql 
[root@node1 ~]#

• 只备份数据

mysqldump -uroot -p kejizhentan --no-create-info > kejizhentan_no_create_info_bak.sql 
#使用grep命令，没有找到create相关语句，表示没有数据结构。 
[root@node1 ~]# grep "CREATE" kejizhentan_no_create_info_bak.sql 
[root@node1 ~]#

⑻ 备份中包含存储过程、函数、事件

mysqldump备份默认是不包含存储过程，自定义函数及事件的。可以使用 --routines 或 -R 选项来备份存储过程及函数，使用 --events 或-E参数来备份事件。
举例：备份整个kejizhentan库，包含存储过程及事件：

• 使用下面的SQL可以查看当前库有哪些存储过程或者函数

mysql> SELECT SPECIFIC_NAME,ROUTINE_TYPE ,ROUTINE_SCHEMA FROM information_schema.Routines WHERE ROUTINE_SCHEMA="kejizhentan"; 
+---------------+--------------+----------------+
| SPECIFIC_NAME | ROUTINE_TYPE | ROUTINE_SCHEMA | 
+---------------+--------------+----------------+
| rand_num | FUNCTION | kejizhentan| 
| rand_string | FUNCTION | kejizhentan| 
| BatchInsert | PROCEDURE | kejizhentan| 
| insert_class | PROCEDURE | kejizhentan| 
| insert_order | PROCEDURE | kejizhentan|
| insert_stu | PROCEDURE | kejizhentan| 
| insert_user | PROCEDURE | kejizhentan| 
| ts_insert | PROCEDURE | kejizhentan| 
+---------------+--------------+----------------+
9 rows in set (0.02 sec)

下面备份atguigu库的数据，函数以及存储过程。

mysqldump -uroot -p -R -E --databases kejizhentan > fun_kejizhentan_bak.sql

⑼ mysqldump常用选项

mysqldump其他常用选项如下：

--add-drop-database：在每个CREATE DATABASE语句前添加DROP DATABASE语句。

--add-drop-tables：在每个CREATE TABLE语句前添加DROP TABLE语句。

--add-locking：用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。

--all-database, -A：转储所有数据库中的所有表。与使用--database选项相同，在命令行中命名所有数据库。

--comment[=0|1]：如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skip- comments与--comments=0的结果相同。默认值为1，即包括额外信息。

--compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip- disable-keys和--skip-add-locking选项。

--compatible=name：产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、 MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options或者 no_field_options。

--complete_insert, -c：使用包括列名的完整的INSERT语句。

--debug[=debug_options], -#[debug_options]：写调试日志。

--delete，-D：导入文本文件前清空表。

--default-character-set=charset：使用charsets默认字符集。如果没有指定，就使用utf8。

--delete--master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-master- data。

--extended-insert，-e：使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可 以加速插入。

--flush-logs，-F：开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。

--force，-f：在表转储过程中，即使出现SQL错误也继续。

--lock-all-tables，-x：对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关 闭--single-transaction和--lock-tables。

--lock-tables，-l：开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表（例 如InnoDB和BDB），--single-transaction是一个更好的选项，因为它根本不需要锁定表。

--no-create-db，-n：该选项禁用CREATE DATABASE /*!32312 IF NOT EXIST*/db_name语句，如果给出- -database或--all-database选项，就包含到输出中。

--no-create-info，-t：只导出数据，而不添加CREATE TABLE语句。

--no-data，-d：不写表的任何行信息，只转储表的结构。

--opt：该选项是速记，它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启， 但可以用--skip-opt禁用。

--password[=password]，-p[password]：当连接服务器时使用的密码。

-port=port_num，-P port_num：用于连接的TCP/IP端口号。

--protocol={TCP|SOCKET|PIPE|MEMORY}：使用的连接协议。

--replace，-r –replace和--ignore：控制替换或复制唯一键值已有记录的输入记录的处理。如果指定--
replace，新行替换有相同的唯一键值的已有行；如果指定--ignore，复制已有的唯一键值的输入行被跳过。如果不 指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。

--silent，-s：沉默模式。只有出现错误时才输出。

--socket=path，-S path：当连接localhost时使用的套接字文件（为默认主机）。

--user=user_name，-u user_name：当连接服务器时MySQL使用的用户名。

--verbose，-v：冗长模式，打印出程序操作的详细信息。

--xml，-X：产生XML输出。

3. mysql命令恢复数据

基本语法：

mysql –u root –p [dbname] < backup.sql

⑴ 单库备份中恢复单库

使用root用户，将之前练习中备份的atguigu.sql文件中的备份导入数据库中，命令如下：
如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示

mysql -uroot -p < kejizhentan.sql

否则需要指定数据库名称，如下所示

mysql -uroot -p kejizhentan< kejizhentan.sql

⑵ 全量备份恢复

如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作：

mysql –u root –p < all.sql

mysql -uroot -pxxxxxx < all.sql

执行完后，MySQL数据库中就已经恢复了all.sql文件中的所有数据库。

⑶ 从全量备份中恢复单库

可能有这样的需求，比如说我们只想恢复某一个库，但是我们有的是整个实例的备份，这个时候我们可以从全量备份中分离出单个库的备份。
举例：

# 将kejizhentan库相关操作提取出来放到kejizhentan.sql文件中
sed -n '/^-- Current Database: `kejizhentan`/,/^-- Current Database: `/p' all_database.sql > kejizhentan.sql

#分离完成后我们再导入atguigu.sql即可恢复单个库

⑷ 从单库备份中恢复单表

这个需求还是比较常见的。比如说我们知道哪个表误操作了，那么就可以用单表恢复的方式来恢复。
举例：我们有kejizhentan整库的备份，但是由于class表误操作，需要单独恢复出这张表。

cat kejizhentan.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql 
cat kejizhentan.sql | grep --ignore-case 'insert into `class`' > class_data.sql 
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复
use kejizhentan; 
mysql> source class_structure.sql; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 

mysql> source class_data.sql; 
Query OK, 1 row affected (0.01 sec)

4. 物理备份：直接复制整个数据库

5. 物理恢复：直接复制到数据库目录

6. 表的导出与导入

⑴ 表的导出

① 使用SELECT…INTO OUTFILE导出文本文件

在MySQL中，可以使用SELECT…INTO OUTFILE语句将表的内容导出成一个文本文件。
举例：使用SELECT…INTO OUTFILE将kejizhentan数据库中account表中的记录导出到文本文件。
（1）选择数据库kejizhentan，并查询account表，执行结果如下所示。

use kejizhentan; 
select * from account; 
mysql> select * from account; 
+----+--------+---------+
| id | name | balance | 
+----+--------+---------+
| 1 | 张三 | 90 | 
| 2 | 李四 | 100 | 
| 3 | 王五 | 0 | 
+----+--------+---------+
3 rows in set (0.01 sec)

（2）mysql默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。
查询secure_file_priv值：

mysql> SHOW GLOBAL VARIABLES LIKE '%secure%'; 
+--------------------------+-----------------------+
| Variable_name | Value | 
+--------------------------+-----------------------+
| require_secure_transport | OFF | 
| secure_file_priv | /var/lib/mysql-files/ | 
+--------------------------+-----------------------+
2 rows in set (0.02 sec)

（3）上面结果中显示，secure_file_priv变量的值为/var/lib/mysql-files/，导出目录设置为该目录，SQL语句如下。

SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";

（4）查看 /var/lib/mysql-files/account.txt`文件。

② 使用mysqldump命令导出文本文件

举例1：使用mysqldump命令将将kejizhentan数据库中account表中的记录导出到文本文件：

mysqldump -uroot -p -T "/var/lib/mysql-files/" kejizhentan account

mysqldump命令执行完毕后，在指定的目录/var/lib/mysql-files/下生成了account.sql和account.txt文件。
打开account.sql文件，其内容包含创建account表的CREATE语句。

打开account.txt文件，其内容只包含account表中的数据。

举例2：使用mysqldump将kejizhentan数据库中的account表导出到文本文件，使用FIELDS选项，要求字段之间使用逗号“，”间隔，所有字符类型字段值用双引号括起来：

mysqldump -uroot -p -T "/var/lib/mysql-files/" kejizhentan account --fields-terminated- by=',' --fields-optionally-enclosed-by='\"'

语句mysqldump语句执行成功之后，指定目录下会出现两个文件account.sql和account.txt。
打开account.sql文件，其内容包含创建account表的CREATE语句。

打开account.txt文件，其内容包含创建account表的数据。从文件中可以看出，字段之间用逗号隔开，字符类型的值被双引号括起来。

③ 使用mysql命令导出文本文件

⑵ 表的导入

7. 数据库迁移

⑴ 概述

⑵ 迁移方案

⑶ 迁移注意点

⑷ 迁移小结

8. 删库了不敢跑，能干点啥？

9.附录：MySQL常用命令