JAVA入门基础_MYSQL高级

目录

• 第1章 Linux下MySQL的安装与使用
  • Linux下MYSQL的卸载
  • 安装MYSQL之前的准备步骤
  • 正式安装
    • 检查/tmp临时目录权限
    • 安装前检查依赖并卸载mariadb
    • 按照顺序依次安装
    • MYSQL服务的初始化配置
      • 服务初始化并启动
      • 查看系统生成的默认密码
      • 登录到MYSQL设置root用户可以远程登录
  • 字符集的相关操作
    • 字符集的级别及默认原则
    • 查看默认的字符集及比较规则
    • 请求到响应过程中字符集的变化
• MySQL的数据目录（Linux中的MYSQL）
  • MySQL8的主要目录结构查询
    • 数据库文件的存放路径
    • 相关命令目录
    • 配置文件目录
  • 数据库和文件系统的关系
    • 默认数据库的作用
    • 根据不同的存储引擎，数据表文件的表现形式
• 用户和权限管理
  • 登录到MYSQL服务器
  • 查询用户、创建用户、设置密码、删除用户
  • 查询角色、创建角色、删除角色
  • 为用户或角色授予权限、回收权限
  • 将角色赋予给用户，并激活角色
  • 权限表的层级关系
    • 如下四张权限表中，均有增删改查的权限选项
    • 用户登录时的权限判断
• MYSQL的逻辑架构
  • 逻辑架构图（这是5.7的，如果8.0需要去掉查询缓存）
    • Connectors
    • 连接层（第一层）
    • 服务层（第二层）
      • SQL Interface的作用
      • Parser解析器的作用
      • Optimizer优化器
      • Caches & Buffers查询缓存（MYSQL8已弃用）
        • 弃用原因
    • 引擎层（第三层）
    • 存储层
  • 缓冲池（Buffer Pool）
  • 监控MYSQL指令的查询指令（生产环境不建议开启）
    • 查询profiling监控是否开启（用于监控查询的sql语句）
    • 开启监控功能
    • 查看所有的profiles;
    • 查看具体的执行情况
• 存储引擎
  • 什么是存储引擎
  • 查看和设置MYSQL中的存储引擎
  • InnoDB 引擎：具备外键支持功能的事务存储引擎
  • MyISAM 引擎：主要的非事务处理存储引擎
  • InnoDB引擎与MyISAM引擎的区别
• 索引的数据结构
  • 索引的概述
    • 什么是索引
    • 索引的优点
    • 索引的缺点
  • 常见的索引概念
    • 聚簇(聚集)索引
      • 特点
      • 优点
      • 缺点
    • 非聚簇(聚集)索引 （二级索引 、辅助索引）
      • 联合索引
      • 特点
    • InnoDB的数据结构与MyISAM的数据结构图
      • MyISAM 与 InnoDB对比
      • B+树与B树的区别
• 索引的创建与设计原则
  • 索引的分类
    • 根据逻辑功能划分
    • 根据物理实现方式
    • 根据作用字段个数
  • 索引的创建、删除、查看表的索引
    • 创建索引
      • 创建表的时候创建索引
      • 创建表之后添加索引
    • 删除索引
    • 查看索引
  • JDK8对于索引的新特性
    • 降序索引
    • 隐藏索引
      • 使隐藏索引对查询优化器可见（了解）
  • 索引的设计原则
    • 哪些情况适合创建索引
    • 哪些情况不适合创建索引
• 性能分析工具的使用
  • 数据库服务器的优化步骤
  • 查看系统性能参数
  • 比较查询开销last_query_cost
  • 定位执行慢的SQL：慢日志查询
    • 开启和查看
      • 查看慢查询数量
    • 修改及查看慢查询阈值
    • 慢查询分析工具 mysqldumpslow，注意不是在mysql当中使用
      • mysqldumpslow的常用参数
      • 常用的工作参考
    • 关闭慢查询日志
    • 删除、重建慢查询日志
  • 查看SQL执行成本（资源消耗信息） SHOW PROFILE
    • 开启该功能
    • 查询SQL的执行成本
    • PROFILE常见参数
  • 分析查询语句EXPLAIN（常用）
  • 基本语法及各个列的作用
    • table列
    • id列，
    • select_type列
    • partitions (可略)
    • type（重要）
    • possible_key 和 key
    • ken_len（重要）
    • ref
    • rows（重要）、filtered
    • Extra（重要）
    • EXPLAIN四种输出格式
      • EXPLAIN小结
    • SHOW WARNINGS（查看优化器优化后的SQL语句）
    • 分析优化器执行计划：trace
    • MySQL监控分析视图-sys schema
      • 视图摘要
      • 索引情况
      • 表相关
      • 语句相关
      • IO相关
      • Innodb 相关
• 10、索引优化及查询优化
  • 索引可能失效的原因
  • 关联查询优化
    • JOIN的分类及原理
      • 分类
      • 原理（Hash略过）
      • 外连接与内连接，驱动表与被驱动表由谁决定？
      • 参数配置
  • 子查询优化和排序优化
    • 子查询优化
    • 排序优化
      • 排序的算法以及参数配置
  • Group by优化
  • Limit分页查询
  • 覆盖索引及索引下推ICP
    • 覆盖索引
    • 索引下推
  • 其他查询优化策略
    • Exists与IN的使用
    • COUNT(*)与COUNT(1)、COUNT(字段)的区别
    • 主键应该如何设计
    • 经常做COMMIT操作
• MYSQL中的事务
  • 事务的ACID特性与事务的状态
    • ACID特性
    • 事物的状态
  • 显式事务与隐式事务
    • 显式事务
    • 隐式事务
    • 保存点的使用
  • 事务的4种隔离级别
  • 隔离级别的查看与设置
  • Redo重做日志与binlog日志
    • Mini-Transaction，简称mtr
    • write pos 和 checkpoint
    • Redo重做日志的相关配置
  • Updo回滚/撤销日志
    • Updo的存储结构
      • 回滚段中的数据分类
    • updo日志对于增删改的记录规则
• 锁的概述
  • 锁的分类
    • 按照数据的操作类型划分
    • 锁颗粒度划分
    • 对待锁的态度划分
    • 加锁方式
    • 其他
  • 锁的内存结构
  • 锁监控常用指令
    • 查看行锁的争夺情况
    • 查询正在被锁阻塞的sql语句
    • 查询锁阻塞情况
    • 查询锁的情况
  • MVCC多版本并发控制（乐观锁）
    • 组成的核心
    • 适用的隔离级别
    • 快照读与当前读
    • 行格式额外字段复习
    • ReadView
• 其他数据库日志（除了binlog日志，其他都是文本文件）
  • 通用查询日志
  • 错误日志
  • 二进制日志
    • 查看二进制日志系统变量信息show variables like '%log_bin%';
    • 建立一个新的binlog日志flush logs
    • binlog日志文件的常用配置
      • binlog_format的3个参数
    • binlog的写入过程
      • sync_binlog参数配置
      • binlog 与 redo log的对比
      • 二阶段提交
      • 数据查看与数据恢复
        • 查看bin日志的2种方式
        • 使用binlog恢复数据的2种方式
      • 删除 binlog日志文件
  • 中继日志
  • 数据定义语句日志（元数据日志）
• 主从复制
  • 主从复制的原理
  • 准备工作
    • 准备2台Centos虚拟机
      • 可以选择关闭防火墙或者开放端口
    • 主机配置文件
    • 从机配置文件
    • 主机：使用主机建立账户并授权
      • 主机：查看主机的状态并记录下File和status的值
    • 从机：配置需要复制的主机
      • 常用命令
• 使用mysqldump备份、恢复数据库(逻辑备份)
  • 备份数据库
    • 备份单个数据库
    • 备份所有数据库
    • 备份部分数据库
    • 备份部分表
    • 备份单表的部分数据
    • 备份时排除某些表
    • 只备份结构或只备份数据
    • 备份中包含存储过程、函数、事件
    • mysqldump常用选项
  • MYSQL命令恢复数据
    • 单库备份中恢复单库
    • 全量备份恢复
    • 从全量备份中恢复单库
    • 从单库备份中恢复单表
  • 表的导入与导出
    • 使用SELECT…INTO OUTFILE导出文本文件（MYSQL内）
    • 使用mysqldump命令导出文本文件
    • 使用mysqldump导出表，要求字段之间使用逗号“，”间隔，所有字符类型字段值用双引号括起来
    • 使用mysql命令导出文本文件
    • 使用LOAD DATA INFILE方式导入文本文件
      • 不同格式的文本数据
      • 字段之间有分隔符，所有字段有双引号括起来
    • 使用mysqlimport方式导入文本文件

第1章 Linux下MySQL的安装与使用

Linux下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 -e --nodeps my mysql-xxx mysql-xxx mysqk-xxxx

• （3） 删除 mysql 相关文件

find / -name mysql  # 查找相关文件
rm -rf xxx          # 删除上述命令查找出的相关文件

如上的2个命令也可以替换成下面这个
使用如下命令需要先确认一下需要删除的文件和目录是否正确，避免误删除： find / -name mysql -print
find / -name mysql -exec rm -rf {} \; # 删除掉find查询到的文件及目录

rm -rf /etc/my.cnf  # 删除 my.cnf

安装MYSQL之前的准备步骤

• 到MYSQL官网中下载如下安装包下载地址
  

• 注意：MYSQL5.7和MYSQL8所需要的安装包是不一样的，区别如下（版本号不必在意）：

# MYSQL8 需要如下安装包
mysql-communiity-client-8.0.25-1.el7.x86_64.rpm
mysql-communiity-client-plugins-8.0.25-1.el7.x86_64.rpm
mysql-communiity-common-8.0.25-1.el7.x86_64.rpm
mysql-communiity-libs-8.0.25-1.el7.x86_64.rpm
mysql-communiity-server-8.0.25-1.el7.x86_64.rpm

# MYSQL5.7 需要如下安装包
# mysql-community-common-5.7.26-1.el7.x86_64.rpm
# mysql-community-libs-5.7.26-1.el7.x86_64.rpm
# mysql-community-client-5.7.26-1.el7.x86_64.rpm
# mysql-community-server-5.7.26-1.el7.x86_64.rpm

正式安装

检查/tmp临时目录权限

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

安装前检查依赖并卸载mariadb

rpm -qa|grep libaio # 检查依赖
rpm -qa|grep net-tools # 检查依赖

#  查询是否有相关的依赖，有则删除
rpm -qa|grep mari
rpm -e --nodeps mariadb-libs # 例如这样一个个卸载
yum remove mysql-libs # 也可以这样卸载

按照顺序依次安装

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 登录用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化。
初始化：mysqld --initialize --user=mysql
启动：systemctl start mysqld.service

查看系统生成的默认密码

cat /var/log/mysqld.log

登录到MYSQL设置root用户可以远程登录

• 登录到MYSQL并修改密码

mysql -uroot -p输入默认密码  # 登录到MYSQL

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; # 修改ROOT的密码

• 设置ROOT用户可以远程登录

use mysql; # 使用mysql这个数据库

update mysql set host = '%' where user='root' and host='localhost'; # % 代表任意ip都可以访问

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123'; # MYSQL5不用做该步骤。这里的密码自己定义。

flush privilege; # 刷新权限

• 开放Centos7的对应端口

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

• 修改MYSQL5.7的字符集（/etc/my.cnf）

# 在[mysqld]后面加上
character_set_server=utf8

字符集的相关操作

字符集的级别及默认原则

• 服务器级别

• 数据库级别

• 表级别

• 列级别

  提示：当创建数据库时未指定字符集和比较规则时，会默认与服务器级别的字符集、比较规则一致。表级别与数据库级别，列级别再与表级别，以此类推。

查看默认的字符集及比较规则

show variables like 'character%';
show variables like 'collation%';

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

• character_set_client 服务器解码请求时使用的字符集（需要与客户端一致）

• character_set_connection服务器处理请求时会把请求字符串从character_set_client 转为 character_set_connection

• character_set_results 服务器向客户端返回数据时使用的字符集（需要与客户端一致）
  

MySQL的数据目录（Linux中的MYSQL）

MySQL8的主要目录结构查询

find / -name mysql

数据库文件的存放路径

/var/lib/mysql/

相关命令目录

/usr/bin（mysqladmin、mysqlbinlog、mysqldump等命令）和/usr/sbin。

配置文件目录

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

数据库和文件系统的关系

默认数据库的作用

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

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

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

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

根据不同的存储引擎，数据表文件的表现形式

举例： 数据库a ， 表b 。

• 1、如果表b采用 InnoDB ，data\a中会产生1个或者2个文件：
  • b.frm ：描述表结构文件，字段长度等
  • 如果采用 系统表空间 模式的，数据信息和索引信息都存储在 ibdata1 中
  • 如果采用 独立表空间 存储模式，data\a中还会产生 b.ibd 文件（存储数据信息和索引信息）
    此外：
    ① MySQL5.7 中会在data/a的目录下生成 db.opt 文件用于保存数据库的相关配置。比如：字符集、比较规则。而MySQL8.0不再提供db.opt文件。
    ② MySQL8.0中不再单独提供b.frm，而是合并在b.ibd文件中。

MYSQL5.7：b.frm、b.idb、db.opt 三个文件
MYSQL8.0：b.idb一个文件

---

• 2、如果表b采用 MyISAM ，data\a中会产生3个文件：

  • MySQL5.7 中： b.frm ：描述表结构文件，字段长度等。

  • MySQL8.0 中 b.xxx.sdi ：描述表结构文件，字段长度等

  • b.MYD (MYData)：数据信息文件，存储数据信息(如果采用独立表存储模式)

  • b.MYI (MYIndex)：存放索引信息文件

MYSQL5.7：b.frm、b.MYD、b.MYI 三个文件
MYSQL8.0：b.sdi、b.MYD、b.MYI 三个文件

用户和权限管理

登录到MYSQL服务器

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

• h参数 后面接主机名或者主机IP，hostname为主机，hostIP为主机IP。

• P参数 后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口，port为连接的端口号。

• u参数 后面接用户名，username为用户名。

• p参数 会提示输入密码。DatabaseName参数 指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库。

• e参数 后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器。

查询用户、创建用户、设置密码、删除用户

# 查询用户
SELECT HOST,USER FROM mysql.user;

# 创建用户，这里的意思是只有192.168.22开头的ip可以连接上，密码为123
CREATE USER 'zhangsan'@'192.168.22.*' IDENTIFIED BY '123';
# 创建一个用户，名字为zhagnsan,任意ip都可以登录，密码123
CREATE USER 'zhangsan'@'%' IDENTIFIED BY '123';

# 设置密码（修改密码）
ALTER USER 'zhangsan'@'192.168.22.*' IDENTIFIED BY '321';

# 删除用户
DROP USER 'zhangsan'@'192.168.22.*';

查询角色、创建角色、删除角色

# 查询角色
SELECT HOST,USER FROM mysql.user; # 注意角色跟用户都在该表当中

# 创建角色
CREATE role 'manager'@'%'; # 创建一个经理角色
CREATE role 'boos'@'%'; # 创建一个boos角色

# 删除角色
DROP role 'manager'@'%';

为用户或角色授予权限、回收权限

# 格式为：GRANT 权限... ON 数据库.数据表 TO  角色;

# 为角色授予权限
GRANT ALL PRIVILEGES ON *.* TO 'boos'@'%';

# 为用户授予权限
GRANT SELECT,UPDATE ON mydb.* TO 'zhangsan'@'%'; # 给予mydb数据库下的所有表的查询和更新权限

# 回收用户的角色。（回收角色时，与其操作一致）
REVOKE SELECT,UPDATE ON mydb.* FROM 'zhangsan'@'%';

将角色赋予给用户，并激活角色

# 将角色授予给用户
GRANT 'boos'@'%' TO 'zhangsan'@'%';

# 激活角色的2种方式
SET DEFAULT ROLE ALL TO 'zhangsan'@'%'; # 为'zhangsan'@'%'用户激活角色
SET GLOBAL activate_all_roles_on_login=ON; # 设置全局激活

# 将角色收回
REVOKE 'boos'@'%' FROM 'zhangsan'@'%';

权限表的层级关系

如下四张权限表中，均有增删改查的权限选项

• mysql.user表（第一层）

• mysql.db表（第二层）

• mysql.tables_priv表（第三层）

• mysql.columns_priv表（第四层）

用户登录时的权限判断

• 用户登录认证成功时候，若有角色时，将会被赋予角色

• 会根据用户在mysql.user表中查询权限，若拥有该表中增删改查的任一权限，则可以使用该权限访问如下三层。

• 接下来会在第二层查询权限，以此类推。

MYSQL的逻辑架构

逻辑架构图（这是5.7的，如果8.0需要去掉查询缓存）

Connectors

连接MYSQL服务的一些客户端程序

连接层（第一层）

• 客户端对MYSQL服务器发送请求时，TCP连接池会分出一个连接与客户端进行TCP连接

• TCP连接收到请求后，将会从线程池当中分配一个线程来进行该请求的处理。

• 接下来连接层会进行用户信息的校验

  • 用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行

  • 用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限。

服务层（第二层）

SQL Interface的作用

1. 接收客户端发送的SQL命令

2. 返回SQL处理的结果给客户端

Parser解析器的作用

1. 解析器用于对SQL进行解析

2. 检查语法、语义，如果检查出错误，直接让SQL Interface返回报错结果

3. 检查语法、语义，如果检查没有错误，则生成SQL的语法树，交给Optimizer优化器

Optimizer优化器

1. 对SQL语法树进行逻辑优化（比如说同一个功能换个写法）

2. 对SQL语法树进行物理优化（更换代码位置等）

Caches & Buffers查询缓存（MYSQL8已弃用）

1. 查询缓存的存储规则为：key:value，其中的key是SQL语句，value是查询的结果。

2. 在服务层的SQL Interface接收到了客户端的SQL时,就会进入缓存中查一查,查到就直接返回结果了

弃用原因

• 如果对表中数据进行了删除，但是查询时又命中了缓存，可能导致读取到脏数据

• 命中率低

引擎层（第三层）

均为可插拔式引擎，真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作。服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同。

存储层

  所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在 文件系统 上，以 文件 的方式存在的，并完成与存储引擎的交互。

缓冲池（Buffer Pool）

• 在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存
  

• 如果你使用的是 InnoDB 存储引擎，可以通过查看 innodb_buffer_pool_size 变量来查看缓冲池的大
  小

• 修改缓冲池大小的方法

# 设置全局变量
set global innodb_buffer_pool_size = 268435456;

# 修改配置文件
[server]
innodb_buffer_pool_size = 268435456
innodb_buffer_pool_instances = 2 # 缓冲池数量

• 如果缓冲池的大小不超过1G，那么没必要拆分多个示例

• 缓冲池按照一定的规则，定期将数据写入磁盘文件当中

监控MYSQL指令的查询指令（生产环境不建议开启）

查询profiling监控是否开启（用于监控查询的sql语句）

select @@profiling;

show variables like 'profiling';

开启监控功能

set profiling=1;

set global profiling=1;

查看所有的profiles;

show profiles;

查看具体的执行情况

show profile;
show profile  for query 1~n;
show profile cpu,block io  for query 1~n;

存储引擎

什么是存储引擎

存储引擎在以前被称之为表处理器，实质上就是存储表的类型。

查看和设置MYSQL中的存储引擎

• 查看MYSQL提供什么存储引擎
  show engines;

• 查看默认的存储引擎

  • show variables like '%storage_engine%';

  • select @@default_storage_engine;

• 修改默认的存储引擎

SET DEFAULT_STORAGE_ENGINE=MyISAM;

也可以修改默认文件

default-storage-engine=MyISAM
# 重启服务
systemctl restart mysqld.service

• 解析一下show engines;命令的结果
  

Engine：引擎名称

Support：是否支持使用该存储引擎

Transcations：是否支持事物

XA：是否支持分布式事物
Savepoints：是否支持保存点

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

• MySQL从3.23.34a开始就包含InnoDB存储引擎。 大于等于5.5之后，默认采用InnoDB引擎 。

• InnoDB是MySQL的 默认事务型引擎 ，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。

• 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。增删改查。

• 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

• InnoDB是 为处理巨大数据量的最大性能设计 。

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

• MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。（不适合高并发的操作）

• ** 5.5之前默认的存储引擎**。

• 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用

• 针对数据统计有额外的常数存储。故而** count(*) 的查询效率很高**

• 应用场景：只读应用或者以读为主的业务

InnoDB引擎与MyISAM引擎的区别

• 对比MyISAM的存储引擎， InnoDB写的处理效率差一些 ，并且会占用更多的磁盘空间以保存数据和索引。

• MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较高 ，而且内存大小对性能有决定性的影响。

索引的数据结构

索引的概述

什么是索引

• 索引（Index）是帮助MySQL高效获取数据的数据结构。

• 索引的本质：索引是数据结构。你可以简单理解为 “排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现 高级查找算法 。

索引的优点

• （1）数据库的IO成本

• （2）通过创建唯一索引，可以保证数据库表中每一行** 数据的唯一性**

• （3）在实现数据的参考完整性方面，可以 加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。

• （4）在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时间 ，降低了CPU的消耗。

索引的缺点

• （1）创建索引和维护索引要 耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加。

• （2）索引需要占 磁盘空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。

• （3）虽然索引大大提高了查询速度，同时却会 降低更新表的速度 。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

常见的索引概念

聚簇(聚集)索引

特点

• （1）使用记录主键值的大小进行记录页和数据页的排序，这包括三个方面的含义：

  • 页内 的记录是按照主键的大小顺序排成一个 单向链表 。

  • 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。

  • 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表 。

• （2）B+树的叶子节点存储的是完整的用户记录

  • 所谓的完整记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点

• （1）数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中（不用进行回表操作），因此从聚簇索引中获取数据比非聚簇索引更快

• （2）聚簇索引对于主键的 排序查找 和 范围查找 速度非常快

• （3）按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以 节省了大量的io操作 。

缺点

• （1）插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键

• （2）更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

• （3）二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据。

非聚簇(聚集)索引 （二级索引 、辅助索引）

首先，如上三个称呼，表明的意思都是一样的。

联合索引

由多个非主键列同时组成。数据页存储联合索引列的数据和主键的值。

特点

• （1）二级索引的插入速度比聚簇索引快

• （2）二级索引各个数据页当中会存储索引列数据和主键的值

InnoDB的数据结构与MyISAM的数据结构图

MyISAM 与 InnoDB对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别：

• （1）在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。

• （2） InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数据记录的地址。

• （3）InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址 。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。

• （4） MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

• （5） InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

B+树与B树的区别

• （1）有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数，而 B 树中，孩子数量 = 关键字数+1。

• （2）非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。

• （3）非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而 B 树中， **非叶子节点既保存索引，也保存数据记录 **。

• （4）所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

索引的创建与设计原则

索引的分类

根据逻辑功能划分

• 普通索引
  • 这类索引可以创建在任何数据类型上，其是否唯一和非空由本身的完整性约束决定。
• 唯一索引
  • 当标识一个字段为UNIQUE时，就会自动创建一个唯一索引。当为一个字段创建唯一索引时，也会将该字段设置为QNIQUE。
• 主键索引
  • 只能存在一个，在InnoDB存储引擎当中是一个B+树的存储结构，同时也是一个聚簇索引。
• 全局索引
  • 可以参考一下solr、Elasticsearch等，一般很少用MYSQL中的全局索引。

根据物理实现方式

• 聚簇索引

  • 使用记录主键值的大小进行记录和页的排序
  • 仅用叶子节点存储完整的用户记录

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

  • 需要回表的索引一般称之为二级索引

  • 二级索引中的各个节点当中存储的都为 二级索引字段的值以及主键的值。所以需要回表。（因为没有存储完整用户记录）

根据作用字段个数

• 单列索引

• 联合索引

  • 联合索引不仅仅只能联合2个字段，甚至可以更多

索引的创建、删除、查看表的索引

创建索引

创建表的时候创建索引

# 创建普通索引
CREATE TABLE t_1(
  id INT,
  sname VARCHAR(32),
  sno INT,
  age INT,
  intor VARCHAR(64),
  INDEX t_1_sno(sno)
)

# 创建唯一索引
CREATE TABLE t_2(
  id INT,
  sname VARCHAR(32),
  sno INT,
  age INT,
  intor VARCHAR(64),
  UNIQUE INDEX t_1_sname(sname)
)

# 创建主键索引（其实就是指定一个主键）
CREATE TABLE t_3(
  id INT,
  sname VARCHAR(32),
  sno INT,
  age INT,
  intor VARCHAR(64),
  PRIMARY KEY(id)
)

创建表之后添加索引

# 第一种方式
ALTER TABLE t_1 
ADD INDEX t_1_sname(sname);

# 第二种方式
CREATE INDEX t_1_age ON t_1(age);

删除索引

# 第一种方式
ALTER TABLE t_1
DROP INDEX t_1_age;

# 第二种方式
DROP INDEX t_1_sno ON t_1;

查看索引

SHOW INDEX FROM 表名;

JDK8对于索引的新特性

降序索引

• 使用方法：在创建索引时，在索引字段后添加 DESC

# 例如：
CREATE INDEX idx_t_1_age_desc ON t_1(age DESC);

• 注意：MYSQL8之前不支持。

隐藏索引

• 使用场景：想要废弃掉一个索引，但是又无法确认该索引会造成的后果时，可以将该索引设置为隐藏，这样如果该索引没了会导致效率降低或不符合预期，就可以再将索引设置为可见。

  • 隐藏后的特点：当更新表中的数据时，仍然会更新索引

  • 不会使用到该索引（查询优化器不可见所以就无法使用）

• 使用方法（在创建或修改索引时添加invisable）

# 创建一个索引并设置为隐藏
CREATE INDEX idx_t_1_sname ON t_1(sname) invisible;

# 将一个已有的索引设置为隐藏
ALTER TABLE t_1
ALTER INDEX idx_t_1_sname visible;

使隐藏索引对查询优化器可见（了解）

• 通过修改变量的方式

select @@optimizer_switch;
set session optimizer_switch="use_invisible_indexes=on";

• 通过配置文件修改的方式

use_invisible_indexes=off

索引的设计原则

哪些情况适合创建索引

• （1）字段的数值有唯一性约束（业务上具有唯一特性的字段，即使是组合字段，也必须建立为唯一索引），虽然影响了Insert的速度，但是这个速度是可以忽略不计，对查询速度点提升是明显的。

• （2）频繁作为WHERE查询条件的字段

• （3）经常GROUP BY 和 ORDER BY的字段

  • 小提示：MYSQL8可以考虑降序索引（如果ORDER BY 经常对某个字段降序时）

• （4）UPDATE、INSERT时的WHERE条件列

  • 如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

• （5）DISTINCT字段需要建立索引

• （6）多表JOIN操作时，创建索引的注意事项

  • 连接表尽量不要超过3张

  • 对WHERE条件创建索引

  • 最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。

• （7）使用列类型小的字段创建索引

  • 因为小嘛，那么在一个个数据页当中就可以存储更多的记录

• （8）字符串创建索引时，需要创建前缀索引alter table t_1 add index(intor(12));

• （9）区分度高(散列性高)的列适合作为索引

• （10） 使用最频繁的列放到联合索引的左侧

  • 因为最左前缀原则，使用联合索引时通常会先选择左侧的索引

• （11）在多个字段都要创建索引的情况下，联合索引优于单值索引

  • 首先联合索引的查询效率跟单值/列索引的查询效率差不多，那么如果建立更多的单值索引，相当于备份了多份叶子节点，会更加浪费磁盘空间。

• （12）一般一个表中，索引不能超过6个

哪些情况不适合创建索引

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

• （2）数据量小的表最好不要使用索引，一般低于1000条数据，就没必要建立索引

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

  • 复杂度太高导致时间复杂度都变成O(n)了，属实没必要
  • 当数据重复度大，比如 高于 10% 的时候，也不需要对这个字段使用索引。

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

• （5）不建议用无序的值作为索引

  • 可能会导致页分裂，维护索引将会非常耗费资源。

• （6）删除不再使用或者很少使用的索引

• （7）不要定义冗余或重复的索引

  • 冗余索引（例如联合索引中已经创建了某个字段的索引，还单独创建了一个该字段的单列索引或联合索引）

  • 重复索引（一个索引又是唯一性索引、又是主键索引，也重复了）

性能分析工具的使用

数据库服务器的优化步骤

整体思路： 先观察、再行动

查看系统性能参数

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

一些常用的性能参数如下：

• • Connections：连接MySQL服务器的次数。

• • Uptime：MySQL服务器的上线时间。

• • Slow_queries：慢查询的次数。

• • Innodb_rows_read：Select查询返回的行数

• • Innodb_rows_inserted：执行INSERT操作插入的行数

• • Innodb_rows_updated：执行UPDATE操作更新的行数

• • Innodb_rows_deleted：执行DELETE操作删除的行数

• • Com_select：查询操作的次数。

• • Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。

• • Com_update：更新操作的次数。

• • Com_delete：删除操作的次数。

比较查询开销last_query_cost

• 当执行了一条SQL语句后，可以通过如下命令获取到执行该条SQL所获取的数据页

• SHOW STATUS LIKE '%last_query_cost%';

• 在进行查询时，采用顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然 页数量（last_query_cost）增加了不少 ，但是通过缓冲池的机制，并 不会增加多少查询时间 。

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

开启和查看

• 查询慢日志是否开启SHOW VARIABLES LIKE '%slow_query_log%';

• 开启慢日志SET GLOBAL slow_query_log = 1

• 再次查看慢日志可以发现多了一个日志文件SHOW VARIABLES LIKE '%slow_query_log%';
  这个名字是以主机名-show.log命名的
  

查看慢查询数量

SHOW STATUS LIKE '%Slow_queries%'

修改及查看慢查询阈值

• 查看慢查询阈值SHOW VARIABLES LIKE '%long_query_time%';，可以看到默认是10秒

• 修改慢查询阈值SET [global|session] long_query_time = 1，

慢查询分析工具 mysqldumpslow，注意不是在mysql当中使用

mysqldumpslow的常用参数

• -a: 不将数字抽象成N，字符串抽象成S

• -s: 是表示按照何种方式排序：

  • c: 访问次数
  • l: 锁定时间
  • r: 返回记录
  • t: 查询时间
  • al:平均锁定时间
  • ar:平均返回记录数
  • at:平均查询时间 （默认方式）
  • ac:平均查询次数

• -t: 即为返回前面多少条的数据

• -g: 后边搭配一个正则匹配模式，大小写不敏感的

常用的工作参考

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 慢查询日志文件路径

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 慢查询日志文件路径

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" 慢查询日志文件路径

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 慢查询日志文件路径 | more

关闭慢查询日志

• 永久性关闭

[mysqld]
slow_query_log=OFF

或者

[mysqld]
#slow_query_log =OFF

• 临时性方式

SET GLOBAL slow_query_log=off;

删除、重建慢查询日志

• 删除

  • 关闭慢查询后，直接删除其log文件即可。

• 重建
  mysqladmin -uroot -p flush-logs slow
  提示：相当于重建了慢查询日志，如果要使用之前的，记得先备份。

查看SQL执行成本（资源消耗信息） SHOW PROFILE

介绍：该功能开启后，可以监控每一条SQL指令，查看SQL的执行成本。

开启该功能

# 查看功能是否开启
SHOW VARIABLES LIKE '%profiling%';

# 开启该功能（这里仅开启session当前会话）
SET profiling = 1;

查询SQL的执行成本

# 查询监控到的SQL，该命令可以看到一个字段叫做Query_ID
SHOW PROFILES;

# 通过Query_ID查询指定SQL的执行成本，例如Query_ID = 37
SHOW PROFILE FOR QUERY 37;

PROFILE常见参数

• （1） ALL：显示所有的开销信息。

• （2） BLOCK IO：显示块IO开销。

• （3） CONTEXT SWITCHES：上下文切换开销。

• （4） CPU：显示CPU开销信息。

• （5） IPC：显示发送和接收开销信息。

• （6）MEMORY：显示内存开销信
  息。

• （7） PAGE FAULTS：显示页面错误开销信息。

• （8） SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。

• （9） SWAPS：显示交换次数开销信息。

分析查询语句EXPLAIN（常用）

版本情况（了解）

• MySQL 5.6.3以前只能 EXPLAIN SELECT ；MYSQL 5.6.3以后就可以 EXPLAIN SELECT，UPDATE，DELETE

• 在5.7以前的版本中，想要显示 partitions 需要使用 explain partitions 命令；想要显示filtered 需要使用 explain extended 命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。

基本语法及各个列的作用

EXPLAIN 后接SQL语句即可

table列

  不论我们的查询语句有多复杂，里边儿 包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

id列，

• （1）一个SELECT对应一个唯一id，被优化器进行SQL优化后可能会有变化.

• （2）id如果相同，可以认为是一组，从上往下顺序执行

• （3）在所有组中，id值越大，优先级越高，越先执行

• （4）关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

select_type列

查询类型。

partitions (可略)

type（重要）

完整的访问方法如下：按照常理来说越往后效率越低。

• （1）system 属于const类型的特例，表只有一条记录行，几乎不会出现

• （2）const 常数级别，根据主键或者唯一性二级索引列与常数进行匹配时，对单表的访问类型为const，查询时表中只有一条记录与之对应。

• （3）eq_ref 在连接查询时，如果被驱动表是通过主键或者唯一性二级索引列等值匹配的方式进行访问的。（如果是联合索引的话，所有索引列都必须进行等值比较）则对该被驱动表的访问办法就是'rq_ref'。

• （4）ref** 根据二级索引列与常数进行匹配时，对单表的访问类型为ref，查询时表中只有一条记录与之对应，在多表连接时，如果被驱动表与驱动表的等值匹配字段为二级索引时，访问类型也为ref。

• （5）fulltext

• （6）ref_or_null ，在第四点的基础上，再添加一个 and 二级索引 is null.

• （7）index_merge ，当多个单列索引共同出现在查询条件中时，优化器可能会使用该种访问方式

• （8）unique_subquery

• （9）index_subquery

• （10）range 使用索引列进行范围查询时

• （11）index 覆盖索引时出现

• （12）AL L全表扫描

possible_key 和 key

• possibale_key ： 可能会使用到的索引

• key：实际使用到的索引

ken_len（重要）

• 一般主要对于联合索引比较有参考价值

• 可以看到联合索引中使用到的索引长度

• 注意：行格式中null值的标志位为1个字节，可变长度记录列为2个字节

ref

• 一般显示表中进行条件过滤时所对比的数据类型

rows（重要）、filtered

• 这2个属性通常一起使用

• rows表示可能查询出的记录数

• filtered表示可能会用上行数的百分比

• 例如rows为100，而filtered为10，那么可能实际需要的数据量为100 * 10%= 10行

Extra（重要）

• Impossible WHERE（不可能的查询条件，例如1=2）

• Using where（没有使用上索引时）

• No matching min/max row 没有聚合查询的匹配结果

• Using index 使用上了索引

• Using index condition 索引下推时

• Using join buffer (Block Nested Loop) 连接时使用上了块缓冲池

• Not exists 不存在

• Using filesort 使用了文件排序

• Zero limit

• Using temporary 使用上了临时表，例如DISTINCT时

EXPLAIN四种输出格式

# 传统格式
 EXPLAIN
 
# JSON格式
EXPLAIN FORMAT=JSON 

# Tree格式
EXPLAIN FORMAT=tree

# 可视化格式
可以通过MySQL Workbench可视化查看MySQL的执行计划

EXPLAIN小结

• EXPLAIN不考虑各种Cache

• EXPLAIN不能显示MySQL在执行查询时所作的优化工作

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

• 部分统计信息是估算的，并非精确值

SHOW WARNINGS（查看优化器优化后的SQL语句）

• 当使用了EXPLAIN之后，可以使用SHOW WARNINGS;命令查看优化器优化后的SQL语句

分析优化器执行计划：trace

• 开启分析优化器

# 设置trace开启，并指定格式为JSON
SET optimizer_trace="enabled=on",end_markers_in_json=on;

# 设置trace最大能够使用的内存大小，避免因内存过小不能够完整展示
set optimizer_trace_max_mem_size=1000000;

• 如何使用
  1. 先执行一个SQL语句
  2. 查询执行计划select * from information_schema.optimizer_trace;

MySQL监控分析视图-sys schema

视图摘要

1. 主机相关：以host_summary开头，主要汇总了IO延迟的信息。

2. Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。

3. I/o相关：以io开头，汇总了等待I/O、I/O使用量情况。

4. 内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况

5. 连接与会话信息：processlist和session相关视图，总结了会话相关信息。

6. 表相关：以schema_table开头的视图，展示了表的统计信息。

7. 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。

8. 语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。

9. 用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。

10. 等待事件相关信息：以wait开头，展示等待事件的延迟情况。

索引情况

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

10、索引优化及查询优化

索引可能失效的原因

• （1）最佳左前缀法则会出现的情况：若是跳过左边的索引，直接想要使用右边的，那不行

• （2）当索引列出现函数、计算、类型转换（自动或手动）时会导致该索引失效

• （3）范围条件右边的列索引失效

• （5）不等于、is not null、等会使索引失效

• （6）like以通配符%开头

• （7）or前后存在非索引列，索引失效

• （8）MYSQL8数据库、表的字符集统一使用utf8mb4，字符集的转换也会导致索引失效。

关联查询优化

1. 为被驱动表的等值比较列添加索引

2. 为where条件中的字段添加索引

JOIN的分类及原理

分类

• Index Nested-Loop Join 索引嵌套循环连接

• Hash Nested-Loop Join 哈希嵌套循环连接

• Block Nested-Loop Join 块嵌套循环连接

• Simple Nested-Loop Join 简单嵌套循环连接

原理（Hash略过）

• 简单嵌套循环连接

  1. 通过IO获取驱动表中的一条数据
  2. 通过IO获取被驱动表中的数据
  3. 使用该一条数据与被驱动表中的所有数据比较
  4. 在通过IO获取驱动表中的一条数据，以此类推
  5. 结论就是需要加载很多次的被驱动表，占用大量资源

• 块嵌套循环连接

  • 1. 通过IO获取驱动表中的数据，放在一个Buffer缓存池当中。
  • 2. 在一次性加载被驱动表的数据与之进行比较
  • 3. 如果缓存池够大，可以一次性将驱动表加载进来，那么被驱动表也就只用加载一次。

外连接与内连接，驱动表与被驱动表由谁决定？

由优化器进行确认。

• 一般如果驱动表与被驱动表进行连接的字段均没有索引、或者均有索引的情况下。 优化器会按照小表驱动大表的原则分配驱动表与被驱动表。（注意大小指的是结果集的行数 * 每行的大小得出）

• 而一般如果其中任意一张表的连接字段有索引，那么很可能会被优化器当成被驱动表使用

参数配置

# 查看block_nested_loop是否开启
show variables like '%optimizer_switch%'

# 驱动表能不能被一次性加载完，要看join buffer能不能存储下所有驱动表的数据。默认大小为256k，Windows以及32位的操作系统最多申请4G，64位的Linux可以申请4G以上
show variables like '%join_buffer%;'

子查询优化和排序优化

子查询优化

• 注意：子查询结果集作为一张表出现时，无法使用到索引

• 建议：能不用子查询就尽量不用，可以换成多表连接的方式。多表连接起码还有个join缓存池。

排序优化

1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。

3. 无法使用 Index 时，需要对 FileSort 方式进行调优。

4. 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

排序的算法以及参数配置

• 双路排序（慢）
  需要进行2次磁盘扫描，最终得到数据。先从磁盘中取出所有需要排序的字段，在buffer进行排序后再次扫描磁盘获取到其他的字段。

• 单路排序
  一次就将需要排序与不需要排序的字段加载进buffer中进行排序，避免了二次访问磁盘。

• sort缓存的大小设置
  sort_buffer_size

• sort如果Query的字段大小总和小于该值，则会使用单路排序，否则使用双路排序。默认1024字节，可以在1027-8192字节之间调整
  show variables like '%max_length_for_sort_data%';

Group by优化

• GROUP BY使用索引的原则几乎与ORDER BY一致，即便没有过滤条件使用了索引，GROUP BY 也可以直接使用索引

• 遵循最佳左前缀法则

• 当无法使用索引列，也可以通过增大sort中的2个参数进行优化

• 减少使用order by，像distinct、order by、group by这些语句比较耗费cpu

• 如果包含了order by、group by、distinct这些查询语句，那么where条件过滤出来的结果集应该保持在1000条以内，否则SQL会很慢

Limit分页查询

• 在索引上完成排序分页操作，最后根据主键关联返回原表所需要查询的内容

覆盖索引及索引下推ICP

覆盖索引

• 当select字段中仅包含当前使用上的索引时，将不会进行回表操作，这种情况下称之为覆盖索引，表访问类型为index

• 可能会打破一些上述中描述的可能无法使用索引的情况

• 结论：不用回表

索引下推

• 索引下推 index condition pushdown，当有多个筛选条件时，优先在B+Tree中将条件都筛选完，再进行回表操作。

• 可能会打破一些上述中描述的可能无法使用索引的情况

• 结论：减少回表次数，仅支持二级索引

其他查询优化策略

Exists与IN的使用

前提： A表大于B表

• Exists相当于是一个相关子查询，一般右边为被驱动表(大表)会比较合适

• 而In相当于是先内层查询再到外层查询，所以外层（左边）为驱动表比较合适

COUNT(*)与COUNT(1)、COUNT(字段)的区别

• 首先COUNT(*)与COUNT(1)的效率是一致的

• 在都保证查询出的数据无误的情况下，介绍如下情况

  • 在MYISAM中，有一个字段row_count存储着当前表的记录数，所以使用count(*)效率高，复杂度为O(1)
  • 在InnoDB中，复杂度为O(n)

主键应该如何设计

• （1）保证全局且唯一

• （2）保证有序，避免影响B+树索引的创建

• （3）UUID其实在经过更新迭代后，将高位的时间与低位进行替换，再使用16进制显示，占用16个字节的同时也可以保证单调递增

• （4）雪花算法挺牛的

经常做COMMIT操作

• （1）会释放回滚段上用于恢复数据的信息

• （2）会释放被程序语句获得的锁

• （3）会释放redo/undo/buffer中的空间
  释放资源以提高服务器的性能。
  
  

MYSQL中的事务

事务是一组逻辑操作单元，使数据从一种状态变换到另一种状态。

事务的ACID特性与事务的状态

ACID特性

• 原子性（atomicity）：表示事务是不可分割的工作单位，要么一起执行成功，要么都不执行。

• 一致性（consistency）：将数据从一个合法性状态转换到另一个合法性状态，这个合法性状态指的不是语法上的，而是语义上的，满足预定的约束就称为满足了一致性。

• 隔离性（isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对 并发 的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

• 持久性（durability）：是指事物一旦被提交，那么对数据库中数据的改变就是永久性的。

事物的状态

• 活动的：事务正在执行的状态

• 部分提交的：事务已经在内存中完成了操作，但是还没有COMMIT

• 提交的：执行了COMMIT，数据刷新到磁盘后

• 失败的：事务执行过程中出现了错误导致程序执行失败。

• 中止的：将失败的事务进行回滚之后。

显式事务与隐式事务

显式事务

# 格式1
begin

commit/rollback

# 格式2，中括号中3个选项分别为只读，读写、启动一致性读
start transaction [READ ONLY |  READ WRITE |  WITH CONSISTENT SNAPSHOT]


commit/rollback

隐式事务

# 将自动提交关闭
SET autocommit = OFF;

commit/rollback;

保存点的使用

# 设置保存点，任意取个名
SAVEPOINT aaa;

# 回滚到保存点处，注意：这个时候事务并没有关闭。
ROLLBACK TO aaa;

事务的4种隔离级别

• READ UNCOMMITTED ：读未提交：会出现脏读、可重复读、幻读的现象

• READ COMMITTED：读已提交：会出现 不可重复读、幻读的现象

• REPEATABLE READ ：可重复读：会出现幻读

• SERIALIZABLE：都能解决，但效率非常低

隔离级别的查看与设置

• 查看当前隔离级别
  transaction_isolation

• 设置当前隔离级别

# 例如设置为读已提交，注意，该变量也可以设置为global全局
set transaction_isolation = 'READ-COMMITTED';

# 如果需要永久生效，需要修改配置文件
[mysqld]
transaction-isolation = READ-COMMITTED

Redo重做日志与binlog日志

• Redo日志在事务执行的过程中就会不断的将数据存储在Redo日志当中（保证数据的持久性）。binlog日志只有在COMMIT时才会将内存中的数据写入到binlog日志中

• 如果内存中的数据还没有刷到磁盘上，那么MYSQL在重启时，会读取Redo日志来恢复日志保证持久性。

1. redo日志的好处

   • redo日志降低了刷盘频率
   • redo日志占用的空间非常小

2. redo日志的特点

   • redo日志是顺序写入磁盘的
   • 事务执行过程中，redo log不断记录
     

Mini-Transaction，简称mtr

• 一个事物可以包含若干个语句，而一个语句可以包含若干个mtr，底层的原子操作

  • mtr会将数据写入redo log buffer当中
  • mtr写的时候，一个mtr写入redo log buffer中的日志块时，会写入一片连续的空间，一个日志块大小不够了，就写入下一个日志块。

• redo log buffer当中有着一个个的redo日志块，每个日志块有512字节，刚好有一个扇区的大小，避免因为扇区损坏出现无法保证原子性和一致性问题。

write pos 和 checkpoint

• write pos指针记录着已经写到了磁盘文件的哪个位置

• checkpoint指针记录这已经清理了的磁盘位置。

• 当wirte pos 追上了 checkpoint，表示日志文件组满了，这时候不能再写入新的 redo log记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。
  

Redo重做日志的相关配置

• innodb_flush_log_at_trx_commit：刷新策略

  • 0，仅仅将事物记录到redo log buffer当中，其余啥都不干
  • 1，每当事物执行commit时，将redo log buffer中的数据写入到Page Cache中，并立刻进行刷盘操作
  • 2，每当事物执行commit时，将redo log buffer中的数据写入到Page Cache中，由OS操作系统来完成文件的持久化。

• innodb_log_buffer_size：日志redo log buffer缓存的默认大小为16M

• innodb_log_files_in_group：redo日志在磁盘中有几个文件，默认为2

• innodb_log_file_size：日志文件大小，注意：是单个日志文件的大小总和，默认为48M，注意所有redo 日志文件的大小不能超过512G

• innodb_flush_log_at_timeout：每隔多少秒，将redo log buffer中的数据刷盘到磁盘文件当中

• 重点提示：Redo日志在事务执行的过程中就会不断的将数据存储在Redo日志当中

Updo回滚/撤销日志

undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子。已经开辟的updo 页并不会删除，仅仅是从逻辑上删除了而已。

Updo的存储结构

• Updo的日志信息存储在回滚段（rollback segment） 当中，一个回滚段中可以存放1024页的updo log segment，1.1版本以前只有一个回滚段，也就是仅支持1024个事务，1.1之后的版本有128个回滚段，也就是支持128 * 1024个事务。

回滚段中的数据分类

1. 未提交的回滚数据(uncommitted undo information)

2. 已经提交但未过期的回滚数据(committed undo information)

   • 考虑为一个updo页，可能不仅仅是一个事务在使用，因此不能马上清理

3. 事务已经提交并过期的数据(expired undo information)

updo日志对于增删改的记录规则

• 在一个事务当中，一旦出现了增删改的操作，那么就会将修改之前的数据进行记录
  • 如果是insert，则记录insert的id，生成一条delete语句
  • 如果是update，则记录一条与之相反的update语句
  • 如果是delete，则记录当前删除的记录，生成一条insert语句

锁的概述

锁的分类

按照数据的操作类型划分

• 读锁/共享锁/S锁

• 写锁/排他锁/X锁

锁颗粒度划分

• 表级锁

  • 表级别的S锁、X锁
  • 意向锁（自动添加）
  • 自增锁（自动添加）
  • MDL锁（元数据锁，自动添加）

• 行级锁

  • Record Locks 记录锁
  • Gap Locks 间隙锁
  • Next-Key Locks 临键锁
  • 插入意向锁

• 页级锁
  小提示：当行锁的压力过大时，MYSQL会自动升级为表锁。

对待锁的态度划分

• 悲观锁

• 乐观锁：由程序实现，不依赖于MYSQL的锁

加锁方式

• 隐式锁

  • 打个比方：事务1修改了一条记录，事务2也想要修改该记录时候，就会隐式的为事务1加锁。

• 显示锁

其他

• 全局锁：全库逻辑备份时使用
  Flush tables with read lock

• 死锁

锁的内存结构

锁监控常用指令

查看行锁的争夺情况

show status like 'innodb_row_lock%';

• （1）Innodb_row_lock_current_waits：当前正在等待锁定的数量；

• （2）Innodb_row_lock_time ：从系统启动到现在锁定总时间长度；（等待总时长）

• （3）Innodb_row_lock_time_avg ：每次等待所花平均时间；（等待平均时长）

• （4）Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

• （5）Innodb_row_lock_waits ：系统启动后到现在总共等待的次数；（等待总次数）

查询正在被锁阻塞的sql语句

SELECT * FROM information_schema.INNODB_TRX\G;

查询锁阻塞情况

SELECT * FROM data_lock_waits\G;

查询锁的情况

SELECT * from performance_schema.data_locks\G;

MVCC多版本并发控制（乐观锁）

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

组成的核心

• MV = undo log

• CC = ReadView

适用的隔离级别

仅在读已提交和可重复读这两个隔离级别上有效。

快照读与当前读

• 快照读： 读历史记录undo log

• 当前读： 读最新的记录

行格式额外字段复习

• row_id ，当表中没有主键和唯一键时又InnoDB设置

• trx_id ，对该数据进行操作的事务ID

• roll_pointer：回滚指针，指向undo日志中上一个与主键一致的记录

ReadView

• 读可提交：每个SELECT都会创建一个新的ReadView

• 可重复读：一个SELECT只会创建一个ReadView
  
  

• 注意行格式中的删除标记，可以被ReadView利用
  

其他数据库日志（除了binlog日志，其他都是文本文件）

通用查询日志

• 作用：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。

• 查看通用查询日志是否开启以及文件地址show variables like '%general_log%';（默认关闭）
  

• 开启该日志set global general_log = 1;

• 随便执行几条SQL，查看一下日志文件
  

• 关闭该日志set global general_log = 0;

错误日志

• 作用：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。（默认开启且无法被关闭）

• 查看错误日志的配置信息show variables like 'log_error%';
  

• 查看一下错误日志vim /var/log/mysqld.log;

二进制日志

• 作用：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复（逻辑备份），可用于数据恢复，一般用于MYSQL集群时的主从复制。

查看二进制日志系统变量信息show variables like '%log_bin%';

建立一个新的binlog日志flush logs

binlog日志文件的常用配置

# 启用二进制日志,指名路径。比如：自己本地的路径/log/mysql-bin
log-bin=/log/mysql-bin

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

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

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

# 设置需要复制的数据库,默认全部记录。多个数据库的话，该配置可以写多次比如：binlog-do-db=codestarts_master_slave
binlog-do-db=需要复制的主数据库名字

# 设置binlog格式
binlog_format=STATEMENT

binlog_format的3个参数

• Statement
    不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能，如果记录的行出现了函数，可能会导致主从机数据不一致，例如主机插入数据时使用了NOW()函数。binlog记录时只是记录了NOW()函数，并不是插入的行记录。

• Row
    row level 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下
  的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

• Mixed（如上2种的结合）

binlog的写入过程

• binlog首先会将数据写入到binlog cache缓存当中，当事物提交的时候再将binlog cache缓存写入操作系统的Page Cache当中，由操作系统进行刷盘操作

sync_binlog参数配置

• 0：当执行提交时，只会将binlog cache中的数据写入到操作系统的Page Cache当中（如果操作系统宕机可能出现数据的丢失）

• 1：每当进行提交时，都会将数据写入Page Cache并进行刷盘操作（与redo 日志的刷新规则参数有些相似）

• n：这个n指的是1 + n，当第n次提交时候，才会主动执行刷盘操作，其他时候都只会将数据写入Page Cache

binlog 与 redo log的对比

• binlog是逻辑日志，记录内容是语句的原始逻辑。

• redo log是物理日志，记录的是一条条的执行结果，属于InnoDB存储层产生的。

二阶段提交

• 思考：执行commit时，redo日志是在事物的过程中就不断写入，binlog是在commit时才会写入。（注意这里说的都是写入缓存），redo日志完成了刷盘操作，而此时binlog 还没有完成刷盘操作，此时服务器宕机了，就会导致binlog中的数据不完整，那么在进行主从复制时，从机的数据就与主机不一致了。怎么解决这个问题？

• 二阶段提交

  • （1）当事物提交时，redo日志进行刷盘操作时会记录一个prepare准备阶段
  • （2）当binlog日志完成刷盘后，将该redo日志的阶段改成commit阶段，那么这次就保证了主机与从机数据的一致性
  • （3）一旦服务器重启，检查redo日志阶段为prepare阶段，就会将该事物的操作进行回滚。

数据查看与数据恢复

查看bin日志的2种方式

• mysqlbinlog（mysql外使用）

# -v 以伪sql的方式展示出来
mysqlbinlog -v  binlog日志文件路径

• show binlog events（MYSQL内使用）

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

# IN 'log_name' ：指定要查询的binlog文件名（不指定就是第一个binlog文件）
# FROM pos ：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
# LIMIT [offset] ：偏移量(不指定就是0)
# row_count :查询总条数（不指定就是所有行）

例如：show binlog events in 'codestarts-bin.000003';

使用binlog恢复数据的2种方式

• （1）使用show binlog events in '日志文件';来定位需要恢复的数据位置

• （2）使用如下命令完成数据恢复

# 注意开始的定位、结束的定位、数据库名、binlog文件名、用户名密码、以及-v后面的数据库名
/usr/bin/mysqlbinlog --start-position=? --stop-position=? --database=? /var/lib/mysql/binlog/codestarts-bin.000003 | /usr/bin/mysql -uroot -p? -v 数据库名

/usr/bin/mysqlbinlog --start-position= --stop-position=? --database=? /var/lib/mysql/binlog/codestarts-bin.000003 | /usr/bin/mysql -uroot -p? -v 数据库名

• （先为一张表添加3条数据，再删掉）

insert into table1 values(10,'lisi');
insert into table1 values(11,'lisi');
insert into table1 values(12,'lisi');

delete from table1 where id > 9;

• 查看binlog日志文件
  show binlog events in 'codestarts-bin.000003';
  

• 恢复数据（非常好用）

/usr/bin/mysqlbinlog --start-position=219 --stop-position=1014 --database=mydb /var/lib/mysql/codestarts-bin.000003 | /usr/bin/mysql -uroot -pabc123 -v mydb

删除 binlog日志文件

• 删除某个binlog日志文件之前的（不包括当前）
  purge master logs to 'codestarts-bin.000003';

• 全部删除（注意后果）
  purge master;

提示：恢复文件时，如果需要恢复文件1和文件2，而文件2是在文件1之后生成的，那么则需要先恢复文件1

中继日志

• 作用：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。

• 中继日志只在主从服务器架构的从服务器上存在。

• 搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。文件名的格式是： 从服务器名 -relay-bin.序号 。中继日志还有一个索引文件： 从服务器名 -relaybin.index ，用来定位当前正在使用的中继日志。

数据定义语句日志（元数据日志）

• 作用： 记录数据定义语句执行的元数据操作。

主从复制

主从复制的原理

• 主机负责写入数据，同时写入binlog日志文件，再调用io线程将数据传递给从机

• 从机负责读取数据，当主机写入binlog日志后通过io线程获取主机传送过来的binlog日志文件，将其写入中继日志，再将中继日志中的数据写入到从机的数据库。

准备工作

准备2台Centos虚拟机

• 采用克隆的方式完成新一台虚拟机的配置

• 新的一台虚拟机需要修改：MAC地址、IP地址、UUID、主机名[可选]、MYSQL的服务的UUID
  
  
  
  vim /etc/hostname
vim /var/lib/mysql/auto.cnf
reboot重启一下

可以选择关闭防火墙或者开放端口

# 1.关闭防火墙
systemctl stop firewalld.service;

# 2. 开放端口
firewall-cmd --permanent --add-port=3306/tcp;
firewall-cmd --reload;

主机配置文件

[mysqld]
#启用二进制日志
# 为每个mysql服务器配置的id
server-id=1
# binlog日志的前缀
log-bin=codestarts-bin
#[可选] 0（默认）表示读写（主机），1表示只读（从机）
read-only=0
# 设置日志文件保留的时长，单位是秒，注意：MYSQL8才支持的
# binlog_expire_logs_seconds=6000
##控制单个二进制日志大小。此参数的最大和默认值是1GB
max_binlog_size=200M
##[可选]设置不要复制的数据库
binlog-ignore-db=test
##[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
binlog-do-db=codestarts_db
##[可选]设置binlog格式
binlog_format=STATEMENT

从机配置文件

[mysqld]
#[必须]从服务器唯一ID
server-id=2
#[可选]启用中继日志
relay-log=mysql-relay

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

主机：使用主机建立账户并授权

• MYSQL5.7

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

# 例如
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%' IDENTIFIED BY '123123';

• MYSQL8

CREATE USER 'slave1'@'%' IDENTIFIED BY '123123';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';

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

主机：查看主机的状态并记录下File和status的值

show master status;

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

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.22.22',MASTER_USER='slave1',MASTER_PASSWORD='123123',MASTER_LOG_FILE='codestarts-bin.000005',MASTER_LOG_POS=154;

常用命令

• 从机：启动SLAVE同步slave start;

• 主/从机：查看SLAVE状态show slave status;

• 从机：删除中继文件reset slave;
  reset slave; #删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件，这时候需要重新复制主机

• 主机：查看当前主机binlog信息show master slave;

使用mysqldump备份、恢复数据库(逻辑备份)

备份数据库

备份单个数据库

mysqldump -uroot -pabc123 db_dump_test>db_dump_test.sql

备份所有数据库

#  使用--all-databases或者-A参数
mysqldump -uroot -pabc123 --all-databases >all_databases.sql

备份部分数据库

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

备份部分表

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

# 例如备份多张表
mysqldump -uroot -p db_dump_test book studetn > multi_tables.sql

备份单表的部分数据

# 备份student表中id小于10的数据
mysqldump -uroot -p db_dump_test student --where="id < 10 " > student_part_id_low10.sql

备份时排除某些表

mysqldump -uroot -p db_dump_test --ignore-table=db_dump_test.student > no_stu_bak.sql

只备份结构或只备份数据

# 只备份结构：没有数据
mysqldump -uroot -p db_dump_test --no-data > db_no_data_bak.sql

# 只备份数据：没有结构
mysqldump -uroot -p db_dump_test --no-create-info > db_no_create_info_bak.sql

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

# 可以使用 --routines 或 -R 选项来备份存储过程及函数，使用 --events 或 -E 参数来备份事件。
mysqldump -uroot -p -R -E --databases db_dump_test > fun_db_bak.sql

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，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skipcomments与--comments=0的结果相同。默认值为1，即包括额外信息。
--compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skipdisable-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]：写调试日志。
运行帮助命令 mysqldump --help ，可以获得特定版本的完整选项列表。
提示 如果运行mysqldump没有--quick或--opt选项，mysqldump在转储结果前将整个结果集装入内
存。如果转储大数据库可能会出现问题，该选项默认启用，但可以用--skip-opt禁用。如果使用最
新版本的mysqldump程序备份数据，并用于恢复到比较旧版本的MySQL服务器中，则不要使用--opt
或-e选项。
--delete，-D：导入文本文件前清空表。
--default-character-set=charset：使用charsets默认字符集。如果没有指定，就使用utf8。
--delete--master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-masterdata。
--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输出。

MYSQL命令恢复数据

单库备份中恢复单库

# 如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称
mysql -uroot -p < mydb.sql

# 如果不包含，则需要自己创建数据库后
mysql -uroot -p mydb< mydb.sql

全量备份恢复

mysql –u root –p < all.sql

从全量备份中恢复单库

# 从全量备份中将单个数据库的信息抽取出来
sed -n '/^-- Current Database: `db_dump_test`/,/^-- Current Database: `/p' all_database.sql> db_dump_test.sql

# 这个时候就只需要执行恢复单库命令即可
mysql -uroot -p  < db_dump_test.sql

从单库备份中恢复单表

# 获取单表的结构，例如这里是从db_dump_test.sql这个数据库备份中获取student表的结构
cat db_dump_test.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `student`/!d;q' >student_structure.sql

# 获取单表的数据
cat db_dump_test.sql | grep --ignore-case 'insert into `student`' > student_data.sql

# 接下来只需要把如上2个生成的SQL都在MYSQL命令行执行（注意路径问题）
source student_structure.sql;
source student_data.sql;

表的导入与导出

使用SELECT…INTO OUTFILE导出文本文件（MYSQL内）

• mysql默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。

• 查询secure_file_priv值：
  

• 上面结果中显示，secure_file_priv变量的值为/var/lib/mysql-files/，导出目录设置为该目录，SQL语句如下。

• SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";

• 导出的结果如下：
  

使用mysqldump命令导出文本文件

• 将会在目录生成 account.sql(仅有表结构)和account.txt文件
  mysqldump -uroot -p -T "/var/lib/mysql-files/" db_dump account

使用mysqldump导出表，要求字段之间使用逗号“，”间隔，所有字符类型字段值用双引号括起来

mysqldump -uroot -p -T "/var/lib/mysql-files/" db_dump account --fields-terminatedby=',' --fields-optionally-enclosed-by='\"'

使用mysql命令导出文本文件

- 使用mysql语句导出db_dump数据中account表中的记录到文本文件
mysql -uroot -p --execute="SELECT * FROM account;" db_dump>"/var/lib/mysqlfiles/account.txt"

- 将db_dump数据库account表中的记录导出到文本文件，使用--veritcal参数将该条件记录分为多行显示
mysql -uroot -p --vertical --execute="SELECT * FROM account;" db_dump >"/var/lib/mysql-files/account_1.txt"

- 将db_dump数据库account表中的记录导出到xml文件，使用--xml参数
mysql -uroot -p --xml --execute="SELECT * FROM account;" db_dump>"/var/lib/mysqlfiles/account_3.xml"

- 如果要将表数据导出到html文件中，可以使用 --html 选项。然后可以使用浏览器打开。

使用LOAD DATA INFILE方式导入文本文件

不同格式的文本数据

# 先导出点数据
SELECT * FROM db_dump.account INTO OUTFILE '/var/lib/mysql-files/account_0.txt';

# 删除account表中的数据
DELETE FROM db_dump.account;

# 导入数据
LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE db_dump.account;

字段之间有分隔符，所有字段有双引号括起来

# 使用SELECT…INTO OUTFILE将db_dump数据库account表中的记录导出到文本文件，使用FIELDS选项和LINES选项，要求字段之间使用逗号"，"间隔，所有字段值用双引号括起来
SELECT * FROM db_dump.account INTO OUTFILE '/var/lib/mysql-files/account_1.txt' FIELDS
TERMINATED BY ',' ENCLOSED BY '\"';

# 删除account表中的数据
DELETE FROM db_dump.account;

# 从/var/lib/mysql-files/account.txt中导入数据到account表中
LOAD DATA INFILE '/var/lib/mysql-files/account_1.txt' INTO TABLE atguigu.account FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

使用mysqlimport方式导入文本文件

# 导出文件account.txt，字段之间使用逗号"，"间隔，字段值用双引号括起来
SELECT * FROM db_dump.account INTO OUTFILE '/var/lib/mysql-files/account.txt' FIELDS
TERMINATED BY ',' ENCLOSED BY '\"';

# 删除表中数据
DELETE FROM db_dump.account;

# 使用mysqlimport命令将account.txt文件内容导入到数据库db_dump的account表
mysqlimport -uroot -p db_dump '/var/lib/mysql-files/account.txt' --fields-terminated by=',' --fields-optionally-enclosed-by='\"