# Mysql学习笔记
**Repository Path**: HaiXiuDeDXianSheng/msql-learning-notes
## Basic Information
- **Project Name**: Mysql学习笔记
- **Description**: 本仓库不记录mysql基础，记录mysql更深层次的内容。
- **Primary Language**: SQL
- **License**: GPL-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No
## Statistics
- **Stars**: 35
- **Forks**: 10
- **Created**: 2022-01-01
- **Last Updated**: 2025-02-12
## Categories & Tags
**Categories**: Uncategorized
**Tags**: MySQL, 高级
## README
- [1. 致谢](#1-致谢)
- [2. Mysql架构](#2-mysql架构)
- [2.1. Mysql 系统架构](#21-mysql-系统架构)
- [2.1.1. 数据库和数据库实例](#211-数据库和数据库实例)
- [2.1.2. Mysql 架构](#212-mysql-架构)
- [2.1.3. mysql目录结构](#213-mysql目录结构)
- [2.1.3.1. bin目录工具](#2131-bin目录工具)
- [2.1.3.1.1. mysql服务端工具](#21311-mysql服务端工具)
- [2.1.3.1.2. Mysql 客户端工具](#21312-mysql-客户端工具)
- [2.1.3.1.2.1. sokect配置](#213121-sokect配置)
- [2.1.3.1.2.2. mysqldump 和数据备份](#213122-mysqldump-和数据备份)
- [2.1.4. 字符集和排序规则](#214-字符集和排序规则)
- [2.1.4.1. 比较规则](#2141-比较规则)
- [2.1.4.2. 字符集](#2142-字符集)
- [2.1.5. mysql修改配置](#215-mysql修改配置)
- [2.1.5.1. 全局变量](#2151-全局变量)
- [2.1.5.2. 会话变量](#2152-会话变量)
- [2.1.6. mysql内置数据库](#216-mysql内置数据库)
- [3. I/O和存储](#3-io和存储)
- [3.1. IO物理存储](#31-io物理存储)
- [3.2. 数据存储](#32-数据存储)
- [3.2.1. innodb 架构](#321-innodb-架构)
- [3.2.2. innodb数据存储](#322-innodb数据存储)
- [3.2.2.1. 数据页](#3221-数据页)
- [3.2.2.2. 表空间](#3222-表空间)
- [3.2.2.2.1. 系统表空间(The System Tablespace)](#32221-系统表空间the-system-tablespace)
- [3.2.2.2.2. 独立表空间 (File-Per-Table Tablespaces)](#32222-独立表空间-file-per-table-tablespaces)
- [3.2.2.2.3. 表空间组织结构](#32223-表空间组织结构)
- [3.2.2.3. Row Format(行记录格式)](#3223-row-format行记录格式)
- [3.2.2.3.1. Compact格式](#32231-compact格式)
- [3.2.2.3.1.1. 案例](#322311-案例)
- [3.2.2.3.2. Redundant格式](#32232-redundant格式)
- [3.2.2.3.3. Dynamic格式](#32233-dynamic格式)
- [3.2.2.3.4. compressed格式](#32234-compressed格式)
- [4. 缓冲池(buffer pool)](#4-缓冲池buffer-pool)
- [4.1. 功能介绍](#41-功能介绍)
- [4.2. 内部结构](#42-内部结构)
- [4.3. Free链表](#43-free链表)
- [4.3.1. 如何知道数据页已经被缓存过](#431-如何知道数据页已经被缓存过)
- [4.4. Flush链表](#44-flush链表)
- [4.4.1. 脏页](#441-脏页)
- [4.4.1.1. 链表结构](#4411-链表结构)
- [4.4.1.2. 刷盘时机](#4412-刷盘时机)
- [4.5. LRU (Least Recently Used)链表](#45-lru-least-recently-used链表)
- [4.5.1. 概述](#451-概述)
- [4.5.2. 优化](#452-优化)
- [4.5.3. 优化思路](#453-优化思路)
- [5. Mysql临时表](#5-mysql临时表)
- [5.1. 临时表分类](#51-临时表分类)
- [5.1.1. 外部临时表](#511-外部临时表)
- [5.1.2. 内部临时表](#512-内部临时表)
- [5.1.2.1. group by 执行流程](#5121-group-by-执行流程)
- [5.1.2.2. 内部表创建的几种常见情况](#5122-内部表创建的几种常见情况)
- [6. Mysql事务(重点)](#6-mysql事务重点)
- [6.1. 事务分类](#61-事务分类)
- [6.1.1. 显式事务 \& 隐式事务](#611-显式事务--隐式事务)
- [6.1.2. 只读事务 \& 读写事务](#612-只读事务--读写事务)
- [6.1.2.1. 只读事务](#6121-只读事务)
- [6.1.2.2. 读写操作](#6122-读写操作)
- [6.1.3. 保存点](#613-保存点)
- [6.2. 事务的特性 (ACID)](#62-事务的特性-acid)
- [6.3. 事务隔离级别 (重点)](#63-事务隔离级别-重点)
- [6.3.1. 读未提交 (RU)](#631-读未提交-ru)
- [6.3.1.1. 案例测试](#6311-案例测试)
- [6.3.2. 读已提交 (RC)](#632-读已提交-rc)
- [6.3.2.1. 案例测试](#6321-案例测试)
- [6.3.3. 可重复读 (RR)](#633-可重复读-rr)
- [6.3.3.1. 案例测试](#6331-案例测试)
- [6.3.3.1.1. 幻读案例](#63311-幻读案例)
- [6.3.4. 串行化](#634-串行化)
- [7. 索引](#7-索引)
- [7.1. 详细参考资料](#71-详细参考资料)
- [7.2. B 树与 B+ 树](#72-b-树与-b-树)
- [7.3. 主键索引(聚簇索引)](#73-主键索引聚簇索引)
- [7.4. 非聚簇索引(二级索引)](#74-非聚簇索引二级索引)
- [7.4.1. 普通索引](#741-普通索引)
- [7.4.1.1. 短索引](#7411-短索引)
- [7.4.2. 唯一索引](#742-唯一索引)
- [7.4.2.1. 唯一索引 VS 主键索引](#7421-唯一索引-vs-主键索引)
- [7.4.2.2. 唯一索引 VS 唯一约束](#7422-唯一索引-vs-唯一约束)
- [7.4.2.3. 唯一索引的作用](#7423-唯一索引的作用)
- [7.4.3. 多个二级索引的组合使用](#743-多个二级索引的组合使用)
- [7.4.3.1. 索引组合使用的几种情况](#7431-索引组合使用的几种情况)
- [7.4.3.2. 索引失效条件](#7432-索引失效条件)
- [7.4.4. 复合索引](#744-复合索引)
- [7.5. 大字段建表注意事项](#75-大字段建表注意事项)
- [7.6. Explain 语句](#76-explain-语句)
- [7.6.1. id 字段](#761-id-字段)
- [7.6.1.1. 若 id 相同](#7611-若-id-相同)
- [7.6.1.2. 若 id 不同](#7612-若-id-不同)
- [7.6.1.3. 若 id 部分相同部分不同](#7613-若-id-部分相同部分不同)
- [7.6.2. select\_type 字段](#762-select_type-字段)
- [7.6.3. type字段](#763-type字段)
- [7.6.3.1. NULL](#7631-null)
- [7.6.3.2. system](#7632-system)
- [7.6.3.3. const](#7633-const)
- [7.6.3.4. eq\_ref](#7634-eq_ref)
- [7.6.3.5. ref](#7635-ref)
- [7.6.3.6. ref\_or\_null](#7636-ref_or_null)
- [7.6.3.7. index\_marge](#7637-index_marge)
- [7.6.3.8. range](#7638-range)
- [7.6.3.9. index](#7639-index)
- [7.6.3.10. ALL](#76310-all)
- [7.6.4. possible\_keys 字段](#764-possible_keys-字段)
- [7.6.5. key 字段](#765-key-字段)
- [7.6.6. filtered字段](#766-filtered字段)
- [7.6.7. Extra字段](#767-extra字段)
- [7.7. 使用索引的问题](#77-使用索引的问题)
- [7.7.1. 哪些情况下适合创建索引](#771-哪些情况下适合创建索引)
- [7.7.2. 哪些情况不适合建立索引](#772-哪些情况不适合建立索引)
- [7.7.3. 复合索引](#773-复合索引)
- [7.7.4. 使用短索引](#774-使用短索引)
- [7.7.5. 排序的索引问题](#775-排序的索引问题)
- [7.7.6. 索引失效的情况](#776-索引失效的情况)
- [8. Mysql 锁机制 (重点)](#8-mysql-锁机制-重点)
- [8.1. InnoDB锁类型](#81-innodb锁类型)
- [8.1.1. s 锁 (shared lock)](#811-s-锁-shared-lock)
- [8.1.1.1. 案例测试](#8111-案例测试)
- [8.1.2. X 锁 (exclusive lock)](#812-x-锁-exclusive-lock)
- [8.1.2.1. 案例测试](#8121-案例测试)
- [8.1.3. 记录锁/行锁 (Record Lock)](#813-记录锁行锁-record-lock)
- [8.1.3.1. 表锁与行锁案例测试](#8131-表锁与行锁案例测试)
- [8.1.3.1.1. where 条件列不加索引 (表锁)](#81311-where-条件列不加索引-表锁)
- [8.1.3.1.2. where 条件列加索引 (行锁)](#81312-where-条件列加索引-行锁)
- [8.1.4. 间隙锁 (gap lock)](#814-间隙锁-gap-lock)
- [8.1.4.1. 间隙锁的生成条件](#8141-间隙锁的生成条件)
- [8.1.4.2. 记录锁和间隙锁的组合 (next-key lock)](#8142-记录锁和间隙锁的组合-next-key-lock)
- [8.1.5. MDL(Meta data lock) 锁](#815-mdlmeta-data-lock-锁)
- [8.2. 死锁问题](#82-死锁问题)
- [8.2.1. 避免死锁](#821-避免死锁)
- [8.3. 表锁](#83-表锁)
- [8.4. 锁的分类 (从另外的角度分)](#84-锁的分类-从另外的角度分)
- [8.4.1. 乐观锁](#841-乐观锁)
- [8.4.1.1. 案例](#8411-案例)
- [8.4.2. 悲观锁](#842-悲观锁)
- [9. 日志系统](#9-日志系统)
- [9.1. bin log 日志 (Mysql Server 提供)](#91-bin-log-日志-mysql-server-提供)
- [9.1.1. 概述](#911-概述)
- [9.1.2. 数据恢复](#912-数据恢复)
- [9.1.2.1. 开启 binlog](#9121-开启-binlog)
- [9.1.2.2. 测试数据恢复](#9122-测试数据恢复)
- [9.1.2.3. 总结](#9123-总结)
- [9.1.3. binlog\_format (不重要)](#913-binlog_format-不重要)
- [9.1.4. 日志格式](#914-日志格式)
- [9.1.5. binlog 刷盘](#915-binlog-刷盘)
- [9.1.6. binlog 实现主从同步](#916-binlog-实现主从同步)
- [9.1.6.1. 主从复制工作原理](#9161-主从复制工作原理)
- [9.1.6.2. 配置主从同步](#9162-配置主从同步)
- [9.1.7. 分布式事务与两阶段提交](#917-分布式事务与两阶段提交)
- [9.1.7.1. 分布式事务](#9171-分布式事务)
- [9.1.7.2. XA 事务](#9172-xa-事务)
- [9.1.7.3. 两阶段提交](#9173-两阶段提交)
- [9.1.7.4. 两阶段为什么要写日志](#9174-两阶段为什么要写日志)
- [Mysql XA 语法](#mysql-xa-语法)
- [9.2. 其他日志](#92-其他日志)
- [9.2.1. 通用日志查询](#921-通用日志查询)
- [9.2.2. 慢查询日志](#922-慢查询日志)
- [9.2.3. 错误日志](#923-错误日志)
- [9.3. redo log 日志](#93-redo-log-日志)
- [9.3.1. redo log VS binlog](#931-redo-log-vs-binlog)
- [9.3.2. 系统崩溃数据丢失](#932-系统崩溃数据丢失)
- [9.3.3. redo log 格式](#933-redo-log-格式)
- [9.3.4. MTR (Mini-Transaction)](#934-mtr-mini-transaction)
- [9.3.4.1. MTR 按组写入](#9341-mtr-按组写入)
- [9.3.4.2. 单条 redo log 的标识方法](#9342-单条-redo-log-的标识方法)
- [9.3.4.3. 事务、sql、MTR、redolog之间的关系](#9343-事务sqlmtrredolog之间的关系)
- [9.3.5. log buffer (理解即可)](#935-log-buffer-理解即可)
- [9.3.6. checkpoint](#936-checkpoint)
- [9.3.6.1. lsn](#9361-lsn)
- [9.3.6.2. flush\_to\_disk\_lsn](#9362-flush_to_disk_lsn)
- [9.3.6.3. flush 链中的 lsn](#9363-flush-链中的-lsn)
- [9.3.6.4. checkpoint 过程](#9364-checkpoint-过程)
- [9.3.7. 一个事务执行的流程](#937-一个事务执行的流程)
- [9.3.8. 系统崩溃的影响](#938-系统崩溃的影响)
- [10. undo log](#10-undo-log)
- [10.1. 概述](#101-概述)
- [10.2. 逻辑存储形式](#102-逻辑存储形式)
- [10.2.1. 事务 id (trx\_id)](#1021-事务-id-trx_id)
- [10.2.2. roll\_pointer](#1022-roll_pointer)
- [10.3. 物理存储形式](#103-物理存储形式)
- [10.3.1. undo log 分类(了解即可)](#1031-undo-log-分类了解即可)
- [10.3.2. 物理存储结构](#1032-物理存储结构)
- [10.3.3. undo log 记录流程](#1033-undo-log-记录流程)
- [10.3.4. 回滚过程](#1034-回滚过程)
- [11. 隔离级别与MVCC（重要）](#11-隔离级别与mvcc重要)
- [11.1. Read View (读视图)](#111-read-view-读视图)
- [11.1.1. 当前读 VS 快照读](#1111-当前读-vs-快照读)
- [11.1.2. 实现原理](#1112-实现原理)
- [11.1.2.1. readView 的结构](#11121-readview-的结构)
- [11.2. 快照读原理解析](#112-快照读原理解析)
- [11.2.1. 脏读和不可重复读](#1121-脏读和不可重复读)
- [11.2.1.1. 解决脏读](#11211-解决脏读)
- [11.2.1.2. 解决不可重复读取](#11212-解决不可重复读取)
- [11.2.1.2.1. RC 隔离级别](#112121-rc-隔离级别)
- [11.2.1.2.2. RR 隔离级别](#112122-rr-隔离级别)
- [11.2.1.3. 解决幻读](#11213-解决幻读)
- [12. 其他知识](#12-其他知识)
- [12.1. 触发器](#121-触发器)
- [12.1.1. 特性](#1211-特性)
- [12.1.2. 触发器创建](#1212-触发器创建)
- [12.1.3. 删除触发器](#1213-删除触发器)
- [12.2. 存储过程](#122-存储过程)
- [12.2.1. 变量](#1221-变量)
- [12.2.1.1. 系统变量](#12211-系统变量)
- [12.2.1.2. 用户变量](#12212-用户变量)
- [12.2.2. 存储过程的创建](#1222-存储过程的创建)
- [12.2.2.1. 参数列表](#12221-参数列表)
- [12.2.2.2. 存储过程案例](#12222-存储过程案例)
- [12.2.2.3. 删除和修改](#12223-删除和修改)
- [12.2.3. 流程控制语句](#1223-流程控制语句)
- [12.2.3.1. 选择结构](#12231-选择结构)
- [12.2.3.2. 循环结构](#12232-循环结构)
- [12.3. 存储函数](#123-存储函数)
- [12.4. 定时任务](#124-定时任务)
- [12.4.1. 创建定时任务](#1241-创建定时任务)
- [12.4.2. 定时任务操作](#1242-定时任务操作)
- [12.4.3. 定时规则](#1243-定时规则)
# 1. 致谢
- 本文主要参考资料
- https://ydlclass.com/doc21xnv/database/mysqladvance/mysqlAdvance1.html
- https://www.bilibili.com/video/BV1eU4y117tx?spm_id_from=333.999.0.0
- 致谢
- 感谢B站 up 主 IT 楠老师的教学视频
- 推荐去听up主的教学视频
# 2. Mysql架构
## 2.1. Mysql 系统架构
### 2.1.1. 数据库和数据库实例
- 数据库：
- 按照数据结构来组织、存储和管理数据的仓库，通常由数据库管理系统进行管理
- 是宏观概念
- 数据库管理软件（RDBMS）
- 就是我们说的数据库管理系统软件，他强调软件
- 例如 Mysql、SQL Server这种
- 数据库实例
- 启动数据库软件，在内存中运行一个独立进程，用来操作数据
- 这个正在运行的进程就是一个数据库实例，理论上可以在一台电脑上启动多个数据库实例
- 但是要监听在不同的端口
- 同一个数据库的不同实例可以操作共同数据也可以操作不同数据
### 2.1.2. Mysql 架构
- SQL查询流程
- **Mysql 8.0 取消了查询缓存**
- MySQL缓存机制（hashMap）
- key-value 形式存储，缓存sql文本及查询结果
- 如果运行完全相同的SQL，服务器直接从缓存中取到结果，而不需要再去解析和执行SQL
- 如果表中任何数据或是结构发生改变，包括INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等，那么使用这个表的所有缓存查询将不再有效，查询缓存中相关条目被清空
- **取消原因**
- MySQL会对每条接收到的SELECT类型的查询进行hash计算，然后查找这个查询的缓存结果是否存在
- hash计算和查找的效率已经足够高了，一条查询语句所带来的开销可以忽略
- 但一旦涉及到高并发，有成千上万条查询语句时，hash计算和查找所带来的开销就必须重视了
- 查询语句的字符大小写、空格或者注释的不同，Query Cache都会认为是不同的查询
- 因为他们的hash值会不同
- 当向某个表写入数据的时候，必须将和这个表相关的所有缓存设置为失效
- 如果缓存内容很多，则消耗也会很大，可能使系统僵死
- 因为这个操作是靠全局锁操作来保护的
### 2.1.3. mysql目录结构
- linux中的文件目录
- 在linux中各项目录可以安装时自由指定
- 我们可以通过配置文件查看mysql的一些基本信息
- linux中配置文件一般都放在 /etc/ 目录下
>```bash
>zhonghuadong@LAPTOP-PQIEGIQ8:/etc/mysql$ ll
>-----------------------------------------------
>total 8
>drwxr-xr-x 1 root root 4096 Jul 13 16:31 ./
>drwxr-xr-x 1 root root 4096 Jul 14 16:40 ../
>drwxr-xr-x 1 root root 4096 Jul 13 16:31 conf.d/
>-rwxr-xr-x 1 root root 120 May 4 20:36 debian-start*
>-rw------- 1 root root 317 Jul 13 16:31 debian.cnf
>lrwxrwxrwx 1 root root 24 Jul 13 16:31 my.cnf -> /etc/alternatives/my.cnf //做了一个软链接
>-rw-r--r-- 1 root root 839 Aug 3 2016 my.cnf.fallback
>-rw-r--r-- 1 root root 682 Mar 11 04:45 mysql.cnf
>drwxr-xr-x 1 root root 4096 Jul 13 16:31 mysql.conf.d/
>```
>```bash
>zhonghuadong@LAPTOP-PQIEGIQ8:/etc/mysql/mysql.conf.d$ cat mysqld.cnf
>-----------------------------------------------------
>[mysqld]
>#
># * Basic Settings
>#
>user = mysql
># pid-file = /var/run/mysqld/mysqld.pid
># socket = /var/run/mysqld/mysqld.sock
># port = 3306
># datadir = /var/lib/mysql
>```
- 备注
- 建议 data 和 socket 文件都不要放在 /var 或者 /tmp 下
- 因为这些是临时文件夹，每次系统启动都可能删除这些文件，出错概率极大
- 所以最好是固定放在一个地方
#### 2.1.3.1. bin目录工具
##### 2.1.3.1.1. mysql服务端工具
- **Mysqld : Mysql服务器进程 (SQL后台保护程序)**
- 该程序必须运行之后，客户端才能连接服务端程序访问和操作数据库
- 重启和关闭 mysql 服务都是由 Mysqld 程序来实现
>```bash
>systemctl mysqld restart
>```
- Mysqld_safe : Mysql服务脚本
- 增加了一些安全特性：当出现错误重启服务器时，向错误文件日志写入运行时间信息
- **mysql.server : Mysql服务启动脚本**
- 调用mysqld_safe来启动MySQL服务
- mysql_multi : 服务器启动脚本
- 可以启动和停止系统上安装的多个服务
- myiasmchk
- 用来描述、检查、优化和维护MyISAM表的实用工具
- mysqlbu : MySQL缺陷报告脚本
- 它可以用来向MySQL邮件系统发送缺陷报告
- mysql_install_db : 用于默认权限创建MySQ授权表
- 通常只是在系统上首次安装MySQL时执行一次
##### 2.1.3.1.2. Mysql 客户端工具
- mysql :交互式输入SQL语句或从文件以批处理模式执行SQL语句来操作数据库管理系统，就是我们的客户端。
- mysqldump : 将MySQL数据库转储到一个文件，可以用来备份数据库
- 可以用来做数据库的迁移
- mysqladmin : 用来检索版本、进程、以及服务器的状态信息
- mysqlbinlog : 用于从二进制日志读取语句
- 在二进制日志文件中包含执行的语句，可用来帮助系统从崩溃中恢复
- mysqlcheck : 检查、修复、分析以及优化表的表维护
- mysqlhotcopy : 当服务器在运行时，快速备份MyISAM或ISAM表的工具。
- mysql import : 使用load data infile将文本文件导入相关表的客户程序。
- perror : 显示系统或MySQL错误代码含义的工具
- myisampack : 压缩MyISAP表，产生更小的只读表
- mysaqlaccess : 检查访问主机名、用户名和数据库组合的权限
通常我们会用如下命令使用 mysql 客户端
>```bash
>mysql -h 127.0.0.1 -P 3306 -uroot -p
>```
###### 2.1.3.1.2.1. sokect配置
- 我们看到在配置文件中有一个socket的配置，socket 即 Unix 域套接字文件
- 在类 unix 平台，客户端连接 MySQL 服务端的方式有两种
- 分别是 TCP/IP 方式
- socket 套接字文件方式
- Unix 套接字文件连接的速度比 TCP/IP 快，**但是只能连接到同一台计算机上的服务器使用。**
- 通过设置 socket 变量可配置套接字文件路径及名称，默认值为 /tmp/mysql.sock (与linux版本有关, 在 Ubuntu 中默认安装是 [/var/run/mysqld/mysqld.sock](#213-mysql目录结构))
- 本地客户端的连接默认会使用到该文件
如果出现如下错误
>```bash
>ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
> # 说明 /etc/my.cnf 中路径配置不对，建议重新初始化 mysql
>```
则是因为对应目录下的 mysql.sock 找不到引起的 (这个目录下根本没有 mysql.sock这个文件)。
- 如果存在这个 sock 文件，则可以通过指定 sock 文件的形式启动 mysql
- 例如
>```bash
>mysql -uroot -p -S /tmp/mysql.sock.bak // 备份文件
>```
- 如果mysql.sock文件误删的话，就需要重启mysql服务
###### 2.1.3.1.2.2. mysqldump 和数据备份
- mysqldump 是将数据表导成 SQL 脚本文件
- 用于数据库快速迁移
- 一般在数据量比较小的时候 (数据量为几个G) 使用
- 大数据量不建议用 mysqldump , 可以使用 LOAD DATA INFILE
>```bash
>导出到文件中（select into outfile）
>SELECT fields INTO OUTFILE 'file_name'
> [{FIELDS | COLUMNS} 字段
> [TERMINATED BY 'string'] 字段之间分隔符号
> [[OPTIONALLY] ENCLOSED BY 'char'] 字段被包含在char中间
> [ESCAPED BY 'char'] 忽略字段里出现的char
> ]
> [LINES
> [STARTING BY 'string'] 忽略开头是string的行
> [TERMINATED BY 'string'] 行分隔符
> ]
>FROM test_table;
>
>导入文件中的数据到mysql表
>LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
> [REPLACE | IGNORE] 遇到重复的时候处理方法，替换或者是忽略
> INTO TABLE tbl_name 导入数据的目的表名
> [PARTITION (partition_name,...)] 分区选择
> [CHARACTER SET charset_name] 字符集
> [{FIELDS | COLUMNS} 字段
> [TERMINATED BY 'string'] 字段之间分隔符号
> [[OPTIONALLY] ENCLOSED BY 'char'] 字段被包含在char中间
> [ESCAPED BY 'char'] 忽略字段里出现的char
> ]
> [LINES
> [STARTING BY 'string'] 忽略开头是string的行
> [TERMINATED BY 'string'] 行分隔符
> ]
> [IGNORE number {LINES | ROWS}] 忽略行/列
> [(col_name_or_user_var,...)] 目的表的表字段名或者用户变量名
> [SET col_name = expr,...] 设置表字段值
>```
- 备份数据命令
>```bash
>-- 备份一个表
>mysqldump -u root -p [db_name] [table_name] > [导出目录]
>-- 例如
>mysqldump -u root -p ydlclass ydl_user > ~/dump.txt
>-------------------------------------------------------
>-- 备份一个数据库
>mysqldump -u root -p [db_name] > [导出目录]
>-- 例如
>mysqldump -u root -p ydlclass > ~/dump.txt
>---------------------------------------------------------
>-- 备份所有数据库
>mysqldump -u root -p --all-databases > [导出目录]
>-- 例如
>mysqldump -u root -p --all-databases > dump.txt
>```
- 从备份数据回复
- 使用的是 mysql 命令 (注意: 不是 mysqldump)
>```bash
>mysql -u root -p [数据库名] < 备份文件
> -- 例如
>mysql -u root -p ydl < ~/dump.txt
>```
### 2.1.4. 字符集和排序规则
>```bash
>MariaDB [(none)]> show collation like '%utf8%';
>+------------------------------+---------+------+---------+----------+---------+
>| Collation | Charset | Id | Default | Compiled | Sortlen |
>+------------------------------+---------+------+---------+----------+---------+
>| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
>| utf8_bin | utf8 | 83 | | Yes | 1 |
>| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
>| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
>| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
>| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
>...
>| utf8mb4_nopad_bin | utf8mb4 | 1070 | | Yes | 1 |
>| utf8mb4_unicode_nopad_ci | utf8mb4 | 1248 | | Yes | 8 |
>| utf8mb4_unicode_520_nopad_ci | utf8mb4 | 1270 | | Yes | 8 |
>
>---------------------------------------------------------------------------------
>```
- mysql-8 的默认字符集是 utf8mb4
- utf8mb4: 用 1-4 个字节表示一个字符
#### 2.1.4.1. 比较规则
utf8_bin : 以二进制形式进行比较
#### 2.1.4.2. 字符集
>```bash
>MariaDB [(none)]> show variables like 'character_set_server';
>+----------------------+--------+
>| Variable_name | Value |
>+----------------------+--------+
>| character_set_server | latin1 |
>+----------------------+--------+
>1 row in set (0.001 sec)
>```
### 2.1.5. mysql修改配置
mysql在启动时，会将 my.cnf 加载到内存中，mysql 的变量存储在内存中。也就是说，有两种方式修改 mysql 变量
- 修改内存变量
- 重启后会失效
- 修改配置文件
- 修改后不直接生效，需要重启才生效
#### 2.1.5.1. 全局变量
- 查看全局变量
- 方式1: show
- 方式2：select
>```bash
>MariaDB [(none)]> show global variables like '%wait_timeout%';
>+--------------------------+-------+
>| Variable_name | Value |
>+--------------------------+-------+
>| innodb_lock_wait_timeout | 50 |
>| lock_wait_timeout | 86400 |
>| wait_timeout | 28800 |
>+--------------------------+-------+
>3 rows in set (0.001 sec)
>```
>```bash
>MariaDB [(none)]> select @@global.wait_timeout;
>+-----------------------+
>| @@global.wait_timeout |
>+-----------------------+
>| 28800 |
>+-----------------------+
>1 row in set (0.001 sec)
>```
- 修改变量
>```sql
>set global wait_timeout = 12000;
>set @@global.wait_timeout = 12000;
>```
#### 2.1.5.2. 会话变量
mysql 连接时优先读取 会话变量
- 修改会话变量
>```sql
>set wait_timeout = 12000; // 记住这个就行
>set session wait_timeout = 12000;
>set local wait_timeout = 12000;
>--------------------------------------
>set @@wait_timeout = 12000;
>set @@session.wait_timeout = 12000;
>set @@local.wait_timeout = 12000;
>```
- 查看会话变量
>```bash
>MariaDB [(none)]> set global wait_timeout = 12000; // 设置全局变量并不会影响会话变量
>Query OK, 0 rows affected (0.001 sec)
>
>MariaDB [(none)]> show variables like '%wait_timeout%'; // 查看会话变量
>+--------------------------+-------+
>| Variable_name | Value |
>+--------------------------+-------+
>| innodb_lock_wait_timeout | 50 |
>| lock_wait_timeout | 86400 |
>| wait_timeout | 28800 |
>+--------------------------+-------+
>3 rows in set (0.001 sec)
>
>MariaDB [(none)]> show global variables like '%wait_timeout%';
>+--------------------------+-------+
>| Variable_name | Value |
>+--------------------------+-------+
>| innodb_lock_wait_timeout | 50 |
>| lock_wait_timeout | 86400 |
>| wait_timeout | 12000 |
>+--------------------------+-------+
>3 rows in set (0.001 sec)
>```
### 2.1.6. mysql内置数据库
- mysql
- 这个库很重要，他是mysql的核心数据库，负责存储数据库的用户、权限设置、关键字等mysql自己需要使用，控制和管理的信息。
- information_schema
- 这个数据库维护了数据库其他表的一些描述性信息，也称为元数据。比如，当前有哪些表，哪些视图，哪些触发器，哪些列等。
- performation_schema
- 这个数据库用来存储mysql服务器运行过程中的一些状态信息，是做性能监控的。比如最近执行了什么sql语句，内存使用情况等
- sys
- 结合information_schema和performation_schema的数据，能更方便的了解mysql服务器的性能信息。
# 3. I/O和存储
- 谈及 mysql 性能，一般涉及两个方面（也就是I/O密集型还是计算密集型）
- I/O成本
- MyIsam和InnoDB存储的数据都在磁盘上，当查询表中的记录时，需要先将数据加载到内存中，然后进行操作
- 这个从磁盘到内存的加载过程损耗的时间成为I/O成本。
- cpu成本
- 读取记录以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作所消耗的时间称之为CPU成本
## 3.1. IO物理存储
- 详见 [这里](https://ydlclass.com/doc21xnv/database/mysqladvance/mysqlAdvance1.html#%E4%B8%80%E3%80%81io%E6%88%90%E6%9C%AC)
- 扇区、块/簇、page的关系：
- 扇区： 硬盘的最小读写单元
- 块/簇： 是操作系统针对硬盘读写的最小单元
- page： 是内存与操作系统之间操作的最小单元
扇区 <= 块/簇 <= page
- 局部性原理与预读
- 【尽量减少磁盘I/O】。也是因为这个原因，磁盘往往不是严格的【按需读取】，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存
- 在 JAVA 多线程中，它从内存读数据，也是一个缓存行一个缓存行读取，所以才有 volatile 关键字的问题
- 预读的长度一般为【页（page）】的整倍数（在许多操作系统中，页得大小通常为4k）
## 3.2. 数据存储
本文档以 innodb 为主进行讲解
### 3.2.1. innodb 架构
### 3.2.2. innodb数据存储
- 对于innodb而言，数据存储在表空间中
- 表空间是innodb抽象出来的概念，它对应着硬盘上的一个或多个文件
- 表空间对用的具体的文件
- 表空间操作数据的基本单位是【页】(page)
- 这个 page 是innodb的概念，和前面将的磁盘的页不是一个东西
- 一个表空间就是一本书，每本书里是一页页的数据
- innodb是以页为单位进行数据存储的
#### 3.2.2.1. 数据页
- 数据页的组成
- file header
- 记录文件的一些元数据，比如当前页的校验和、页号、上页号、下页号、所属表空间等。
- 表空间中的每一个页，都有一个页号（File_PAGE_OFFSET, 通过偏移量来定位）
- 这个页号由4个字节组成，也就是32位，所有最多能存放2的32次方页，如果按照一页16k计算，一个表空间最大支持【64TB】的数据
- 整体的排列中页是连续的，但是页有上下指针，不连续的页也能组成链表。
- file trailer
- 主要的工作是检验页是否完整
#### 3.2.2.2. 表空间
##### 3.2.2.2.1. 系统表空间(The System Tablespace)
- 系统表空间
- 存储了很多【公共数据】
- 比如InnoDB的数据字典，回滚信息、系统事物信息、二次写缓冲等
- 系统表空间是一个共享的表空间
- 因为它是被多个表共享的。
- 该空间的数据文件通过参数【innodb_data_file_path】控制
- 默认值是ibdata1:12M:autoextend
- 文件名为ibdata1、12MB、自动扩展。
>```bash
>MariaDB [(none)]> show variables like "innodb_data_file_path";
>+-----------------------+------------------------+
>| Variable_name | Value |
>+-----------------------+------------------------+
>| innodb_data_file_path | ibdata1:12M:autoextend |
>+-----------------------+------------------------+
>1 row in set (0.001 sec)
>```
##### 3.2.2.2.2. 独立表空间 (File-Per-Table Tablespaces)
- 独立表空间默认开启
- mysql5.6 版本之后会为每个表建立一个独立表空间
- innodb 独立表空间为 【.ibd】文件
>```bash
>MariaDB [(none)]> show variables like'innodb_file_per_table';
>+-----------------------+-------+
>| Variable_name | Value |
>+-----------------------+-------+
>| innodb_file_per_table | ON |
>+-----------------------+-------+
>1 row in set (0.001 sec)
>```
- 如果启用了【innodb_file_per_table】参数
- 需要注意的是每张表的表空间内存放的只是【数据】、【索引】和【插入缓冲Bitmap页】
- 其他数据如：**回滚信息、系统事物信息、二次写缓冲（Double write buffer）等还是放在原来的系统表空间内。**
- 因此启用了【innodb_file_per_table】参数，系统表空间还是会不断的增加其大小的。
##### 3.2.2.2.3. 表空间组织结构
- 表空间
- 区 (extent)
- 每个表空间保存了大量的 page , 为了更好管理这些 page , innodb 提出了【区】的概念
- 1 extent = (64 pages)*(16k/page) ≈ 1Mb
- 一个区由64个页组成
- 每 256 个组划分为一组
- 段 (Segment)
- 分为 索引段、数据段、回滚段等
- 段是为了区分不同的数据类型
- 相同的段保存的数据类型是一致的
- 一个段包含 256 个区，大约 256M
#### 3.2.2.3. Row Format(行记录格式)
- Row Format
- Compact
- Redundant
- 一个表的行记录格式，决定了行的物理存储模式
- 也决定了DQL和DML的操作性能
- 越多的行被匹配进独立的磁盘页，sql的性能会更好一些
- 因为需要的缓存及io操作就越少
- innodb 从磁盘加载数据到内存的最小单位是页(16K)
- 这个页，不是磁盘的页(4K)
- 一条完整的信息记录包括
- 【记录的额外信息】
- 描述记录的信息，可以认为是元数据，[与hive中的SequenceFile和ORC类似](https://gitee.com/HaiXiuDeDXianSheng/big-data-learning-notes/tree/master/HIVE%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/hive%E6%96%87%E4%BB%B6%E6%A0%BC%E5%BC%8F#32-sequencefile)
- 【记录的真实数据】
- **可以用 SHOW TABLE STATUS LIKE 'table_name' 来查看当前表的行格式**
- 现在默认是 Dynamic 格式
>```bash
>MariaDB [mysql]> show table status like 'user'\G;
>*************************** 1. row ***************************
> Name: user
> Engine: MyISAM
> Version: 10
> Row_format: Dynamic // Compact格式的升级版本，默认行格式
> Rows: 4
> Avg_row_length: 118
> Data_length: 472
> Max_data_length: 281474976710655
> Index_length: 4096
> Data_free: 0
> Auto_increment: NULL
> Create_time: 2022-07-15 02:53:02
> Update_time: 2022-07-15 03:07:36
> Check_time: 2022-07-18 04:49:41
> Collation: utf8_bin
> Checksum: NULL
> Create_options:
> Comment: Users and global privileges
>Max_index_length: 288230376151710720
> Temporary: N
>1 row in set (0.000 sec)
>
>ERROR: No query specified
>```
- 帮助命令查看
>```
>MariaDB [mysql]> help show table status;
>Name: 'SHOW TABLE STATUS'
>Description:
>Syntax:
>SHOW TABLE STATUS [{FROM | IN} db_name]
> [LIKE 'pattern' | WHERE expr]
>
>SHOW TABLE STATUS works likes SHOW TABLES, but provides a lot of
>information about each non-TEMPORARY table. You can also get this list
>using the mysqlshow --status db_name command. The LIKE clause, if
>present, indicates which table names to match. The WHERE clause can be
>given to select rows using more general conditions, as discussed in
>https://mariadb.com/kb/en/extended-show/.
>
>URL: https://mariadb.com/kb/en/show-table-status/
>
>```
- 指定行格式
>```sql
>-- 创建数据表时,显示指定行格式
>CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
>-- 创建数据表时,修改行格式
>ALTER TABLE 表名 ROW_FORMAT=行格式名称;
>
>-- 具体如下：
>CREATE TABLE `ydl_user` (
> `user_id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
> `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户账号',
> ....
> PRIMARY KEY (`user_id`) USING BTREE
>) ROW_FORMAT = DYNAMIC;
>```
##### 3.2.2.3.1. Compact格式
- **1. 变长字段的长度列表**(非null)
- 存储 varchar、text等这种可变长度数据类型的实际长度
- 对于定长字段，每个字段占多少字节，它是固定的
- 按照列的顺序【**逆序放置**】
- 先记录最后一列，然后记录倒数第二列...最后记录第一列
- 该部分用来计算整个数据的长度
- 不然varchar这种类型的长度无法确定
- 对于变长字段
- 如果列长度<255字节，例如 varchar(50)，则用 1 字节表示该变长字段
- 如果列长度>255字节，例如 varchar(600), 则用 2 字节表示。
- 变长字段最多用 2 字节表示
- 2 byte 为 16 位二进制 , 2^16 = 25536
- 所以 **varchar 能存储的最长长度是 25536**
- **2. NULL 标志位**
- 指示了当前行数据中哪些为null值
- 用一个 [bitmap](https://blog.csdn.net/qq_29119581/article/details/115270409) 表示
- 假如该标志位为06(二进制：00000110)则表示第二三列（可以为空的列）的数据为NULL。
- **逆序存放**
- 占用空间按照字节数**高位补零** : 一个字节为 8 位
- 如有九个字段可以为空（00000001 01010101）。
- 一个字节为 8 位，字段数为 9 ，所以需要 2 字节存储，而 9<16 ,不足两个字节，因此在高位补零
- 需要注意的是，**NULL值标志位仅仅针对可以为【NULL的字段】**
- 如果某个字段被定义为not null，那么这个字段就不会进入NULL值标志位的BitMap中
- **3. 记录头信息 (Record Header)**
- 固定占用5个字节（40位）,每一位的含义如下
- **4. 组成每个列的数据**
- NULL不占该部分任何数据
- 即NULL除了占有NULL标志位，实际存储不占有任何空间
- **对于变长列 (例如 VARCHAR、VARBINARY、BLOB、TEXT)**
- 字段长度超过 768 字节
- 只存储前768字节
- 随后跟着存储一个 20 个字节的页地址(指针)
- 将剩余的内容存储在这些地址所指向的其他页中
- 溢出页如果还是存不下，则继续往下溢出，直到存完为止
- 不超过 768 字节
- 直接在行格式中存储
- **对于固定长度列**
- 长度若超过 768 字节
- 则视为变长列
- 只存储前 768 字节
- 随后紧跟 20 字节指针
- 存储列的溢出页的地址
- 不超过 768 字节
- 则直接存储在行格式中
- 注意
- 每行数据除了用户自定义的字段外，还有两个隐藏列
- **事务ID列** : 6 字节
- **回滚指针列** : 7 字节
- 若InnoDB表没有定义Primary Key，每行还会增加一个【6字节的**RowID列**】。
###### 3.2.2.3.1.1. 案例
- 创建表结构如下
>```sql
>create table row_test (
> t1 varchar(10),
> t2 varchar(10),
> t3 char(10),
> t4 varchar(10)
>) engine=innodb row_format=compact;
>```
- 插入数据
>```bash
>MariaDB [zhdtest]> insert into row_test values('a','bb','bb','ccc');
>Query OK, 1 row affected (0.017 sec)
>
>MariaDB [zhdtest]> insert into row_test values('d','ee','ee','fff');
>Query OK, 1 row affected (0.001 sec)
>
>MariaDB [zhdtest]> insert into row_test values('d',NULL,NULL,'fff');
>Query OK, 1 row affected (0.014 sec)
>
>MariaDB [zhdtest]> select * from row_test;
>+----------+----------+----------+----------+
>| t1 | t2 | t3 | t4 |
>+----------+----------+----------+----------+
>| a | bb | bb | ccc |
>| d | ee | ee | fff |
>| d | NULL | NULL | fff |
>+----------+----------+----------+----------+
>3 rows in set (0.013 sec)
>```
- 节选对应的【真实的表空间】中的的二进制结构表示：
>```python
> 03 02 01 00 00 00 10 00
>2c 00 00 00 00 2b 68 00 00 00 00 06 05 80 00 00
>00 32 01 10 61 62 62 62 62 20 20 20 20 20 20 20
>20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 00
>00 02 01 00 00 00 00 0f 62 c9 00 00 01 b2 01 10
>64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66
>03 01 06 00 00 20 ff 98 00 00 00 00 02 02 00 00
>00 00 0f 67 cc 00 00 01 b6 01 10 64 66 66 66
>```
- 第一行整理如下，需要注意，我们有三个变长列varchar：
- 上述编码说明：上述编码是 16 进制编码。每位数代表一个 8 位(即一个字节)
- 因此 00 代表两个字节
>```
>03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1
>00 // NULL标志位，第一行没有NULL值
>00 00 10 00 2c // 记录头信息，固定5字节长度
>00 00 00 00 2b 68 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
>00 00 00 00 06 05 // 事务ID，固定6个字节
>80 00 00 00 32 01 10 // 回滚指针，固定7个字节
>61 // t1数据'a'，'a'的ascii码十六进制就是61
>62 62 // t2'bb'
>62 62 20 20 20 20 20 20 20 20 // t3数据'bb' Ox20十进制是32对应ascii码是空字符
>63 63 63 // t4数据'ccc'
>```
- 第二行数据整理如下
>```
>03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1
>00 // NULL标志位，第二行没有NULL值
>00 00 18 00 2b // 记录头信息，固定5字节长度
>00 00 00 00 02 01 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
>00 00 00 00 0f 62 // 事务ID，固定6个字节
>c9 00 00 01 b2 01 10 // 回滚指针，固定7个字节
>64 // t1数据'd'
>65 65 // t2数据'ee'
>65 65 20 20 20 20 20 20 20 20 // t3数据'ee'
>66 66 66 // t4数据'fff'
>```
- 第三行数据如下
>```
>03 01 // 变长字段长度列表，逆序，t4列长度为3，t1列长度为1
>06 // 00000110 NULL标志位，逆序排列，t2和t3列为空。bitmap数据格式
>00 00 20 ff 98 // 记录头信息，固定5字节长度
>00 00 00 00 02 02 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
>00 00 00 00 0f 67 // 事务ID，固定6个字节
>cc 00 00 01 b6 01 10 // 回滚指针，固定7个字节
>64 // t1数据'd'
>66 66 66 // t4数据'fff'
>```
##### 3.2.2.3.2. Redundant格式
- mysql8.0基本已经弃用了，它是 mysql 5.0 版本之前的格式
- [参考资料](https://www.ydlclass.com/doc21xnv/database/mysqladvance/mysqlAdvance1.html#%E4%BA%8C%E3%80%81%E6%95%B0%E6%8D%AE%E5%AD%98%E5%82%A8)
##### 3.2.2.3.3. Dynamic格式
- 背景
- InnoDB Plugin引入了两种新的文件格式（file format，可以理解为新的页格式）
- 对于以前支持的Compact和Redundant格式将其称为Antelope文件格式
- 新的文件格式称为Barracuda，Barracuda文件格式下拥有两种新的行记录格式
- Compressed格式
- Dynamic格式
- 新的两种格式对于存放BLOB的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在BLOB Page中，而之前的Compact和Redundant两种格式会存放768个前缀字节。**mysql8.0默认此格式。**
##### 3.2.2.3.4. compressed格式
- COMPRESSED 基于dynamic格式
- 支持表和索引数据压缩
- compressed行格式采用dynamic相同的页外存储细节，同时，存储在其中的行数据会以zlib的算法进行压缩
- 因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能进行非常有效的存储。
# 4. 缓冲池(buffer pool)
- 这个缓冲池(buffer pool) 和前面 [mysql 架构](#212-mysql-架构) 中的"查询缓存"是不一样的
- mysql 架构中的查询缓存
- 是属于 mysql 级别，它直接对接的是 sql 语句。在 mysql8.0 已经取消了该模块
- 对于热点数据，缓存在查询缓存中，以便后续有相同查询时，直接返回结果，不再需要进行搜索查询
- 但事实上有 redis 更适合做这个事情
- buffer pool
- 是 innodb 级别的
- buffer pool 缓存的是【[数据页](#3211-数据页)】数据，**缓冲池是在内存中的**
## 4.1. 功能介绍
- 现有如下 sql 语句
>```sql
>select * from user where id between 10 and 1000;
>```
- **如果没有 buffer pool，则需要在全表扫描时频繁进行 I/O 操作**
- 访问id为1的数据，需要访问当前表空间的第一行数据，一次I/O
- 访问id为2的数据，需要访问当前表空间的第二行数据，两次I/O
- 访问id为3的数据，需要访问当前表空间的第三行数据，三次I/O
- ......
- 访问id为1000的数据，需要访问当前表空间的第一千行数据，一千次I/O
- 这显然不合理
- 我们发现id为1，2，3...的数据都在同一个【数据页】
- 这会导致一个严重的问题，一次简单的查询，会访问【同一个页很多次】，可能产生很几百次I/O操作
- 所以为了解决快如闪电的【cpu】，和慢如蜗牛的【磁盘】之间的矛盾，innodb设计了buffer pool
- **有了 buffer pool 后，执行过程变成如下**
- 访问id为1的数据，需要访问当前表空间的第一行数据，缓存当前页，一次I/O
- 访问id为2的数据，需要访问当前表空间的第二行数据，从缓存获取，无需I/O
- 访问id为3的数据，需要访问当前表空间的第三行数据，从缓存获取，无需I/O......
- Innodb引擎会在mysql启动的时候，向操作系统申请一块连续的空间当做 buffer pool
- 空间的大小由变量innodb_buffer_pool_size
>```bash
>MariaDB [mysql]> show variables like 'innodb_buffer_pool_size';
>+-------------------------+-----------+
>| Variable_name | Value |
>+-------------------------+-----------+
>| innodb_buffer_pool_size | 134217728 |
>+-------------------------+-----------+
>1 row in set (0.001 sec)
>
>MariaDB [mysql]> select 134217728/1024/1024;
>+---------------------+
>| 134217728/1024/1024 |
>+---------------------+
>| 128.00000000 |
>+---------------------+
>1 row in set (0.001 sec)
>```
## 4.2. 内部结构
- 结构组成
- 缓冲页
- buffer pool 中存储的【数据页】
- 和磁盘上的【数据页】一一对应，都是 16 KB
- 它是从磁盘上加载到 buffer pool 中的一个**完整页**
- 默认 16 KB
- 控制块
- 缓冲页的【描述信息】
- 保存数据页所属表空间号、数据页编号、数据页地址、链表相关的节点信息
- 每一个控制块负责管理一个缓冲页
- 每个控制块大小是缓存页的5%左右，大约是800个字节
- buffer pool
- 前一部分存储的是【控制块】
- 所有的控制块都存储在这里
- 后一部分是【缓冲页】
- 中间有一部分未被利用的空间，称为【内存碎片】
- buffer pool的初始化
- 数据库会在启动的时候，按照配置中的Buffer Pool大小，去向操作系统申请一块内存
- 按照默认的缓存页的的大小【16KB】
- 对应的【控制块】大小大约【800个字节左右】
## 4.3. Free链表
- Free链的作用
- **标记哪些缓冲页可以被使用**
- buffer pool刚被初始化时，内存中都是【空白的缓冲页】
- 随时间推移，程序在执行过程中会不断的有新的页被缓存起来
- 需要判断哪些缓冲页处于【空闲状态】
- 则需要用到【控制块】来进行管理
- 【空闲的缓冲页】对应的【控制块】的节点形成的链表，称为 Free 链，即空闲链表
- **维护的永远是空闲的数据页**
- 是关于存储控制块信息的
- 可以看到 free 链表是一个双向链表
- 当有新的缓冲页被使用时，控制块基节点的 start 指针往后移动一位，count - 1
### 4.3.1. 如何知道数据页已经被缓存过
- 当新的一个数据页请求进来时，如何判断这个数据页的数据已经被缓存过了？
- 缓存过的数据就不需要再进行缓存了
- 没有缓存过，则去对应表空间中找到对应数据返回给请求，然后在 buffer pool 中缓存该数据
- 循环遍历 buffer pool?
- 显然不合理。万一 buffer pool 设置有好多个G，那不是等到天荒地老？
- 事实上，【表空间号 + 页号】可以确定唯一的页
- **设计一个 hash 表，key 为 【表空间号 + 页号】，value 为 【控制块地址】**
- hash的时间复杂度是O(1)，可以迅速定位缓存的页
## 4.4. Flush链表
### 4.4.1. 脏页
- sql执行过程中，增删改查都是优先在 buffer pool 中进行的
- 可以极大保证执行效率
- 但是存在问题
- 对缓存页的修改，会导致与磁盘中的数据页【数据不一致】，此时与磁盘不一致的【数据页】叫做【脏页】
- 类似于 java 内存模型的[可见性问题](https://gitee.com/HaiXiuDeDXianSheng/java-se-learning-notes#%E7%BA%BF%E7%A8%8B%E5%AE%89%E5%85%A8%E7%9A%84%E8%AE%A8%E8%AE%BA)
- 【脏页】是迟早需要刷到磁盘上的
- **这就是 Flush 链表的功能**
#### 4.4.1.1. 链表结构
- flush链表结构与free链表完全一致
- 不同在于 **链表结点维护的是被【修改过的缓存页】(即要刷新的脏页)的控制块。**
- 新增脏页，只需要在链表尾部指针指向新增节点即可
- flush 链表原理详见章节 [flush 链中的 lsn](#9363-flush-链中的-lsn)
#### 4.4.1.2. 刷盘时机
- 后台会有专门的线程每隔一段时间就把flush链表中的脏页刷入磁盘中
- 刷新的速率取决与当前系统是否繁忙。
- 在这样的机制下，万一系统奔溃，是会产生数据不一致的问题的，没有刷入磁盘的数据就会丢失
- 而 **mysql通过[日志系统](#redo-log-日志)解决了这个问题，**
## 4.5. LRU (Least Recently Used)链表
### 4.5.1. 概述
- LRU的功能是淘汰掉旧的数据页
- 内存是有限的，buffer pool在内存中，更是有限的
- 当 buffer pool 满了之后，还是需要加载缓存页，怎么办？
- 淘汰掉一些旧的数据页，释放空间
- **这就是 LRU 的功能**
- 淘汰策略是**淘汰最近最少使用的缓冲页**
- 将使用频率比较高的缓冲页放在链表一端，频率比较低的放在另一端。
- 在 java 中，可以使用 [LinkedHashMap 来实现 LRU 缓存算法](https://gitee.com/HaiXiuDeDXianSheng/java-se-learning-notes#lru%E7%AE%97%E6%B3%95%E7%9A%84%E7%BC%93%E5%AD%98)
- 在 innodb 中，使用【LRU链表】实现此功能
- 结构与 Free 链表一致，但其维护的是访问的缓冲页的频率
### 4.5.2. 优化
- 在实际应用场景中，**存在一些问题**，需要对原有 LRU 链表进行优化
- 数据页预读
- 当mysql读取当前需要的页时，如果觉得后续操作会使用【附近的页】，就会将他们一起缓存到buffer pool，提高效率。
- 数据页预读可能会导致一个问题，预读多读进来的数据页，可能压根就没有用
- 会把这些数据误认为是【热点数据】，空间浪费
- 全表扫描
- 一旦对大表进行【select *】，则会将其全部的数据进行缓存，这就会导致需要淘汰很多其他热点数据
### 4.5.3. 优化思路
- 思路
- 将 LRU 链表分成【热点数据】(young数据区域)和【冷数据】(old数据区域)两块区域。保证 young 数据区域，永远是有用的数据
- **预读的数据页**，第一次访问时会放入 old 区域，如果 sql 执行过程中访问相邻数据时，再次访问到该数据页，则把它放入 young 区域。
- 例如，一个 select 语句访问 id = 12、13、14 的数据，则由于预读，innodb 会把 id = 12、13、14、15、16、17、18 的数据都加载到【冷数据区域】
- 当再次访问 id = 12，则会将 id = 13 所在的数据页(相邻的数据页)加载到【热点数据区域】，id = 14、15、16、17、18 的数据页仍然在冷数据区域
- 对于多读进来的数据依然在冷数据区域，以后会在适当的时候释放这部分空间
- **对于大表扫描问题**
- 大表扫描其实是一个频率很低的操作
- 但根据上述设计
- 首先会把全表数据存放在【old区】
- 全表扫描必然会因为访问相邻数据，而产生第二次、第三次，甚至第100次的访问。
- 也就意味着这些页面都会被放在【young区】
- 这不是我们希望的
- **解决方案**
- 提供了一个 【innodb_old_blocks_time】 参数，默认是 1 s
- 不急着把数据放入【热点数据区】
- 盖一个时间戳
- 以后访问和首次访问的时间相比，如果时间间隔大于 1s ,则把数据放入 young 区
- 一个 sql 扫描数据页所花费的时间，再慢也不会大于 1s
- 一个数据页间隔 1s 后再来访问
- 那认为就是正常的业务请求
- 而不是全表扫描引起的
>```bash
>MariaDB [mysql]> show variables like "%blocks_time%";
>+------------------------+-------+
>| Variable_name | Value |
>+------------------------+-------+
>| innodb_old_blocks_time | 1000 | // 1000 ms
>+------------------------+-------+
>1 row in set (0.001 sec)
>```
>```bash
>MariaDB [mysql]> show engine innodb status;
>----------------------
>BUFFER POOL AND MEMORY
>----------------------
>Total large memory allocated 170590208
>Dictionary memory allocated 34272
>Buffer pool size 8192
>Free buffers 7866
>Database pages 326
>Old database pages 0 // 老数据页
>Modified db pages 0
>Percent of dirty pages(LRU & free pages): 0.000
>Max dirty pages percent: 75.000
>Pending reads 0
>Pending writes: LRU 0, flush list 0, single page 0
>Pages made young 0, not young 0
>0.00 youngs/s, 0.00 non-youngs/s // young数据区
>Pages read 195, created 131, written 131
>0.00 reads/s, 0.00 creates/s, 0.00 writes/s
>No buffer pool page gets since the last printout
>Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
>LRU len: 326, unzip_LRU len: 0
>I/O sum[0]:cur[0], unzip sum[0]:cur[0]
>--------------
>```
# 5. Mysql临时表
- 临时表使用场景
- 用户自行创建临时表
- 用于保存临时数据
- 系统创建
- Mysql在执行【复杂SQL】时，需要借助临时表进行【分组、排序、去重】等操作
- 临时表不能通过 show tables 查看，服务器重启后，临时表会被销毁
- **只要当前 mysql 连接会话一断开，临时表就删除了**
- 临时表是每个进程独享的
- 进程(客户端)创建的临时表，是相互独立的，其他进程访问不到。因此不同进程可以创建相同名字的临时表。
## 5.1. 临时表分类
### 5.1.1. 外部临时表
- 创建语句
- create temporary table
>```sql
>create temporary table temp_table(
> id int,
> name varchar(10)
>) ENGINE = InnoDB;
>insert into temp_table values (1,'1');
>
>select * from temp_table ;
>
>-- 删除临时表
>DROP TEMPORARY TABLE table_name;
>```
- **在 mysql 中一般不用外部临时表**，而在hive中用的很多
### 5.1.2. 内部临时表
- 内部临时表
- 用来存储某些操作的【中间结果】
- 这些操作包括【优化阶段】或者【执行阶段】
- 执行复杂 SQL，会自动生成临时表
- group by
- distinct
- union
- 可以使用 explain 来查看执行计划
- 内部临时表对用户是不可见的
>```bash
>MariaDB [mysql]> select * from innodb_table_stats ;
>+---------------+----------------+---------------------+--------+----------------------+--------------------------+
>| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
>+---------------+----------------+---------------------+--------+----------------------+--------------------------+
>| mysql | gtid_slave_pos | 2022-07-15 02:53:02 | 0 | 1 | 0 |
>| zhdtest | row_test | 2022-08-16 04:57:59 | 2 | 1 | 0 |
>+---------------+----------------+---------------------+--------+----------------------+--------------------------+
>2 rows in set (0.000 sec)
>
>MariaDB [mysql]> explain select table_name from innodb_table_stats group by table_name order by table_name;
>+------+-------------+--------------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
>+------+-------------+--------------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
>| 1 | SIMPLE | innodb_table_stats | index | NULL | PRIMARY | 793 | NULL | 2 | Using index; Using temporary; Using filesort |
>+------+-------------+--------------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
>1 row in set (0.014 sec)
>```
- 发现有个 extra 字段，其中有个值是 Using temporary
#### 5.1.2.1. group by 执行流程
>```sql
>select age,count(*) from student group by age order by age
>```
- 执行流程
- 1. 创建内部临时表，一列是 age，一列是 count(*)
- 2. 全表扫描【原始表】
- 对扫描的每一条数据进行一次判断
- 当表中没有对应数据时，将 age 写入，count写 1
- 当表中已存在当前 age 数据，则 count+1 写入
- 可以看出，这和 [hive 的 mapreduce 过程](https://gitee.com/HaiXiuDeDXianSheng/big-data-learning-notes/tree/master/HIVE%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/HIVE%E5%9F%BA%E7%A1%80#125-%E4%BC%98%E5%8C%96group-by%E6%95%B0%E6%8D%AE%E5%80%BE%E6%96%9C)有很大区别
#### 5.1.2.2. 内部表创建的几种常见情况
- 使用以下几种关键字，会创建内部临时表
- group by
- 且 group by 分组字段没有索引时
- distinct
- 因为需要去重，所以要临时表做中间存储，判断当前数据是否已经有了
- union
- 注意 union all 不会产生临时表
- 其实临时表还可以分为
- 【内存临时表】
- 内存临时表使用memery引擎（Memory引擎不支持BOLB和TEXT类型）
- 【磁盘临时表】
- 磁盘临时表默认使用innodb引擎
- 磁盘临时表的情况（防止加载到内存的数据过多）
- 数据表中包含BLOB/TEXT列
- 在 GROUP BY 或者 DSTINCT 的列中有超过 512字符的字符类型列
- 在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列
- （对于字符串类型是512个字符，对于二进制类型则是512字节）
# 6. Mysql事务(重点)
前四章内容，只需要了解有宏观概念即可，事务这章需要完全理解和掌握。
- 对于一个具体业务操作，我们可能需要操作多个 sql 语句，但是这个业务是不可分割的，必须实现要么这些sql全部成功，要么全部失败。
- Mysql 只有 innodb 引擎才支持事务
## 6.1. 事务分类
### 6.1.1. 显式事务 & 隐式事务
- mysql 的事务可以分为
- 隐式事务
- 默认的事务是隐式事务
- 由变量【autocommit】控制
>```bash
>MariaDB [mysql]> show variables like'%autocommit%';
>+------------------------+-------+
>| Variable_name | Value |
>+------------------------+-------+
>| autocommit | ON |
>| wsrep_retry_autocommit | 1 |
>+------------------------+-------+
>2 rows in set (0.001 sec)
>```
- 显式事务
- 由用户自己控制事务的【开启、提交、回滚】
- 开启新的事务
- START TRANSACTION 或 BEGIN 开始新的事务
- 提交当前事务
- COMMIT
- 回滚当前事务
- ROLLBACK
- 准备数据
>```bash
>MariaDB [zhdtest]> create table user(
> -> id int primary key auto_increment,
> -> name VARCHAR(20),
> -> balance DECIMAL(10,2) UNSIGNED
> -> );
>
>MariaDB [zhdtest]> insert into user VALUES (1,'zhd',200);
>Query OK, 1 row affected (0.001 sec)
>
>MariaDB [zhdtest]> insert into user VALUES (2,'zzz',5000);
>Query OK, 1 row affected (0.001 sec)
>
>```
- 执行事务
>```sql
>-- 开启事务;
>start transaction;
>UPDATE user set balance = balance - 200 where id = 1;
>UPDATE user set balance = balance + 200 where id = 2;
>-- 提交事务
>commit;
>```
- 查看执行结果
>```bash
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 0.00 |
>| 2 | zzz | 5400.00 |
>+----+------+---------+
>2 rows in set (0.000 sec)
>```
- 回滚
>```sql
>-- 都失败
>start transaction;
>UPDATE user set balance = balance - 200 where id = 1;
>UPDATE user set balance = balance + 200 where id = 2;
>-- 回滚事务
>rollback;
>```
- 查看执行结果
>```bash
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 0.00 |
>| 2 | zzz | 5400.00 |
>+----+------+---------+
>2 rows in set (0.000 sec)
>```
### 6.1.2. 只读事务 & 读写事务
#### 6.1.2.1. 只读事务
- 开启只读事务
- 使用 **read only** 开启只读事务
- 在只读事务模式下，只能执行 select 语句，不能执行任何 insert 或者 update 语句
- 只读事务基本语法
>```sql
>start transaction read only
>select * from ....
>select * from ....
>commit;
>```
- 测试
>```bash
>MariaDB [zhdtest]> start transaction read only;
> -> delete from user where id = 1;
>Query OK, 0 rows affected (0.000 sec)
>
>ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction
>```
- 只读事务有什么用呢？
- 在涉及【隔离级别】后就有用了
#### 6.1.2.2. 读写操作
读写事务就是正常的事务，采用 start transaction 或者 begin 进行开启
### 6.1.3. 保存点
我们可以使用 **savepoint** 关键字在事务执行中新建【保存点】
- 保存点有什么用？
- 我们可以 rollback 回滚到任意保存点
- 使用 **rollback to 【保存点名称】** 语句
- 比如事务执行失败了，我们不想回滚到初始位置，而是想回滚到事务执行一半的时候的位置
>```bash
>MariaDB [zhdtest]> start transaction; -- 开启事务
>Query OK, 0 rows affected (0.001 sec)
>
>MariaDB [zhdtest]> UPDATE user set balance = balance + 200 where id = 1;
>Query OK, 1 row affected (0.000 sec)
>Rows matched: 1 Changed: 1 Warnings: 0
>
>MariaDB [zhdtest]> savepoint a; -- 设置保存点
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> UPDATE user set balance = balance + 200 where id = 2;
>Query OK, 1 row affected (0.000 sec)
>Rows matched: 1 Changed: 1 Warnings: 0
>
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 5800.00 |
>+----+------+---------+
>2 rows in set (0.000 sec)
>
>MariaDB [zhdtest]> rollback to a; -- 回滚保存点
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 5600.00 |
>+----+------+---------+
>2 rows in set (0.000 sec)
>
>MariaDB [zhdtest]> commit; -- 关闭事务
>Query OK, 0 rows affected (0.001 sec)
>
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 5600.00 |
>+----+------+---------+
>2 rows in set (0.000 sec)
>
>```
## 6.2. 事务的特性 (ACID)
- 原子性 (Atomicity)
- 要么全部完成，要么全部失败
- 一致性 (Consistency)
- 事务开始之前和结束之后，数据库的完整性没有被破坏
- 举例：A 向 B 转账，不可能 A 扣钱了，B 没收到钱
- 隔离性 (Isolation)
- 数据库【允许多个并发事务同时对其数据进行读取和修改】
- 隔离性，可以防止多个事务在**并发修改共享数据**时产生【数据不一致】现象
- 参考 [javaSE 的多线程](https://gitee.com/HaiXiuDeDXianSheng/java-se-learning-notes#%E5%A4%9A%E7%BA%BF%E7%A8%8B).
- **事务隔离级别**
- 读未提交 (Read uncommited)
- 读提交 (Read committed)
- 可重复读 (Repeatable read)
- 串行化 (Serializable)
- 类似于 java 的 synchronized
- 性能太差，一般数据库不选择它
- 持久性 (Durablity)
- 事务处理结束后，对数据的修改时永久的，即使系统故障也不会丢失
## 6.3. 事务隔离级别 (重点)
- 在【并发操作】相同表数据时
- 为了多个事务都要得到正确的结果，不会因为互相的交叉操作产生干扰
- 同时还要保证一定的执行效率
|隔离级别 |脏读 |不可重复读| 幻读 |解决方案|
|:-:|:-:|:-:|:-:|:-:|
|Read uncommitted（读未提交）| √| √| √||
|Read committed（读已提交）| ×| √| √| undo log|
|Repeatable read（可重复读）| × |×| √| MVCC版本控制+间隙锁（mysql的rr不存在幻读）|
|Serializable（串行化）| × |×| ×||
- mysql 中查看事务隔离级别
>```bash
>MariaDB [mysql]> show variables like '%isolation%';
>+---------------+-----------------+
>| Variable_name | Value |
>+---------------+-----------------+
>| tx_isolation | REPEATABLE-READ | // 由于是 MariaDB 因此
>+---------------+-----------------+
>1 row in set (0.001 sec)
>```
- 设置隔离级别变量的优先级
>```sql
>--5.7 tx_isolation
>--8.0 transaction_isolation
>
>-- 设置下一个事务的隔离级别，只对下一个事务有效，下下个事务的隔离级别还是依赖于 session 的隔离级别
>SET transaction isolation level read uncommitted;
>SET transaction isolation level read committed;
>set transaction isolation level repeatable read;
>SET transaction isolation level serializable;
>-- 设置当前会话的隔离级别，优先于全局变量生效
>SET session transaction isolation level read uncommitted;
>SET session transaction isolation level read committed;
>set session transaction isolation level repeatable read;
>SET session transaction isolation level serializable;
>-- 设置全局事务的隔离级别
>SET GLOBAL transaction isolation level read uncommitted;
>SET GLOBAL transaction isolation level read committed;
>set GLOBAL transaction isolation level repeatable read;
>SET GLOBAL transaction isolation level serializable;
>```
### 6.3.1. 读未提交 (RU)
- 一个事务可以读取其他【未提交的事务】修改的数据
- 一般不用这种隔离级别
- **脏读**
- 一个事务读到了其他事务未提交的数据
- **未提交，意味着这些数据可能会回滚**，因此读到的数据不一定准确
#### 6.3.1.1. 案例测试
- 开启两个窗口 A、B，连接同一个 mysql 库 (MariaDB)
- 对两个窗口分别设置隔离级别为 read-ucommitted
>```bash
>MariaDB [zhdtest]> set tx_isolation = 'read-uncommitted';
>Query OK, 0 rows affected (0.000 sec)
>```
- 在窗口 A 查看原有数据
>```bash
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 5600.00 |
>+----+------+---------+
>2 rows in set (0.000 sec)
>```
- 窗口 A 中开启事务，但不提交
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> update user set balance = balance - 2000 where id = 2;
>Query OK, 1 row affected (0.001 sec)
>Rows matched: 1 Changed: 1 Warnings: 0
>
>MariaDB [zhdtest]> update user set balance = balance + 2000 where id = 1;
>Query OK, 1 row affected (0.000 sec)
>Rows matched: 1 Changed: 1 Warnings: 0
>```
- 在窗口 B 中查看 user 数据
- 可以查询到未提交的数据
>```bash
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 2200.00 |
>| 2 | zzz | 3600.00 |
>+----+------+---------+
>2 rows in set (0.000 sec)
>```
- 我们再在窗口 A 中对事务进行回滚
>```bash
>MariaDB [zhdtest]> rollback;
>Query OK, 0 rows affected (0.014 sec)
>
>MariaDB [zhdtest]> commit;
>Query OK, 0 rows affected (0.000 sec)
>```
- 然后我们再从窗口 B 进行查看
>```bash
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 5600.00 |
>+----+------+---------+
>2 rows in set (0.000 sec)
>```
- 存在的问题
- 当事务 B 查询 user 数据时，查询到事务 A 未提交的数据。当事务 B 认为没有问题，关闭连接后，事务 A 回滚了事务
- 导致事务 B 查询到的数据是假数据。（给我转账，看到好像到账了，实际钱没到账）
### 6.3.2. 读已提交 (RC)
- 当前事务只能读到别的事物已经提交的数据
- 该隔离级别可能会产生不可重复读和幻读。
- **不可重复读**
- 【一个事务】（A事务）修改了【另一个未提交事务】（B事务）读取过的数据
- 那么B事务【再次读取】，会发现两次读取的数据不一致。
- 也就是说**在一个原子性的操作中一个事务两次读取相同的数据，却不一致**，一行数据不能重复被读取。(同一个事务中，读取相同的数据，存在数据不一致问题)
- 事务 B 读取了 user 表的数据，未提交
- 事务 A 修改了 user 表的数据，提交了
- 事务 B 再次读取 user 表，仍不提交，但发现数据和第一次它读取的时候不一致
- 主要是【update】语句，会导致不可重复读。
#### 6.3.2.1. 案例测试
- 拿工资卡消费为例
- 我们打开两个窗口 A、B，分别设置两个窗口的隔离级别为 read-committed
>```bash
>MariaDB [zhdtest]> set tx_isolation = 'read-committed';
>Query OK, 0 rows affected (0.000 sec)
>```
- 窗口 A、B 分别开启事务，窗口 A 读取 user 数据
- 不提交事务
>```
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 5600.00 |
>+----+------+---------+
>2 rows in set (0.000 sec)
>```
- 窗口 B 开启事务
- 更改数据，未提交
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> update user set balance = balance -5000 where id = 2;
>Query OK, 1 row affected (0.000 sec)
>Rows matched: 1 Changed: 1 Warnings: 0
>```
- 此时再查询窗口 A 的数据
- 发现脏读的问题解决了
>```bash
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 5600.00 |
>+----+------+---------+
>2 rows in set (0.000 sec)
>```
- 窗口 B 提交事务
>```bash
>MariaDB [zhdtest]> commit;
>Query OK, 0 rows affected (0.001 sec)
>```
- 再从窗口 A 查询数据
- 发现数据改变了
- 这就有问题了
- 事务具有原子性，事务 A 在两次查询过程中读到的数据不一致，违背了原子性的原则。(事务 A 还没提交呢，数据就改变了)
>```bash
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 600.00 |
>+----+------+---------+
>2 rows in set (0.000 sec)
>```
- 但如果在该事务发生过程中，balance 的数据真的少了怎么办？
- 应该在该事务提交后进行异常提醒，而不是在该事务操作中直接修改数据
### 6.3.3. 可重复读 (RR)
- 在【可重复读】隔离级别下
- 同一个事务中发出同一个 select 语句两次或多次，其产生的结果数据总是相同的
- 但是可能存在幻读问题
- **幻读**
- 一个事务按照某些条件查询
- 事务提交前，有另一个事务插入了或删除了满足条件的其他数据
- 再次使用相同条件查询，却发现多了或少了数据
- 由【delete】和【insert】语句引起
- **【不可重复读】与【幻读】的区别**
- 不可重复读：查询结果的数据量没变，值变了
- 幻读：查询结果原有的值没变，但是数据量变多或者变少了
#### 6.3.3.1. 案例测试
- 开启两个窗口 A 和 B，分别设置隔离级别为 rpeatable-read
- 窗口 A 开启事务，查询数据
- 不提交事务
>```bash
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 600.00 |
>+----+------+---------+
>2 rows in set (0.004 sec)
>```
- 窗口 B 开启事务，并提交
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> insert user values (3,'zhd',1000);
>Query OK, 1 row affected (0.019 sec)
>
>MariaDB [zhdtest]> commit;
>Query OK, 0 rows affected (0.014 sec)
>```
- 窗口 A 中查询数据
- 发现并没有出现幻读的情况
>```bash
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 600.00 |
>+----+------+---------+
>2 rows in set (0.013 sec)
>```
- 窗口 A 提交事务后，再查询数据
>```bash
>MariaDB [zhdtest]> commit;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 600.00 |
>| 3 | zhd | 1000.00 |
>+----+------+---------+
>3 rows in set (0.000 sec)
>```
- 这里并没有发生幻读的情况
- 这是因为**在 mysql 的 RR 隔离级别中，innodb 使用了 MVCC+锁 帮我们解决了绝大部分的幻读情况**
##### 6.3.3.1.1. 幻读案例
我们改一下上述案例，就会出现幻读的情况。
- 窗口 A、B 分别设置隔离级别为 RR
- 窗口 A 开启事务，查询数据
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> select * from user;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 600.00 |
>| 3 | zhd | 1000.00 |
>+----+------+---------+
>3 rows in set (0.000 sec)
>```
- 窗口 B 开启事务，删除一条数据，并提交
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> insert user values (4,'zhd',4000);
>Query OK, 1 row affected (0.013 sec)
>
>MariaDB [zhdtest]> commit;
>Query OK, 0 rows affected (0.001 sec)
>```
- 窗口 A 查询数据
>```bash
>MariaDB [zhdtest]> select * from user; -- 一开始没有发生幻读
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 200.00 |
>| 2 | zzz | 600.00 |
>| 3 | zhd | 1000.00 |
>+----+------+---------+
>3 rows in set (0.000 sec)
>
>MariaDB [zhdtest]> update user set balance = balance + 200; -- 使用 update 更新数据后，出现了幻读
>Query OK, 4 rows affected (0.000 sec)
>Rows matched: 4 Changed: 4 Warnings: 0
>
>MariaDB [zhdtest]> select * from user; -- 出现幻读
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 400.00 |
>| 2 | zzz | 800.00 |
>| 3 | zhd | 1200.00 |
>| 4 | zhd | 4200.00 |
>+----+------+---------+
>4 rows in set (0.000 sec)
>```
- 为什么加了 update 后会出现幻读？
- 会在后续章节进行解释
### 6.3.4. 串行化
- 一旦一个事务使用了某个表，这个表就不能再被其他事务修改 (但是可以读)，其他事务需要等待，类似于 java 的 synchronized
- 这种隔离级别一般不用，它无法并发
# 7. 索引
## 7.1. 详细参考资料
关于 Mysql 的索引，在介绍有关 Hive 文件格式的时候有详细说明，详见 [这里](https://gitee.com/HaiXiuDeDXianSheng/big-data-learning-notes/tree/master/HIVE%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/hive%E6%96%87%E4%BB%B6%E6%A0%BC%E5%BC%8F#52-mysql%E7%B4%A2%E5%BC%95)
## 7.2. B 树与 B+ 树
- **为什么在 B 树或 B+ 树中选择？**
- mysql数据模型更适合用这类数据结构，一条数据中通常包含【id】+【其他列数据】，我们可以很轻松的根据id组织一颗B+树。
- 我们知道innodb使用【页】（这是inndb管理数据的最小单位）保存数据，一页（16k），b+树中的每个节点都是一页数据。
- **为什么选择 B+ 树？**
- 相同的空间，不存放【整行数据】就能存【更多的id】，b+树能使每个节点能检索的【范围更大、更精确，极大的减少了I/O操作，保证b+树的层高较低，通常3到4层的层高就能支持百万级别的访问】。
- Mysql是一种关系型数据库，【区间访问】是很常见的一种情况，**B+树叶节点增加的双向指针，加强了区间访问性，可使用在范围区间查询的情况**。
## 7.3. 主键索引(聚簇索引)
- 主键为什么建议使用自增id？
- **主键最好不要使用uuid，因为uuid的值太过离散**
- 不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，出现页分裂，导致索引树调整复杂度变大，消耗更多的时间和资源。
- 聚簇索引的数据的物理存放顺序与索引顺序是一致的
- 只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的
- 如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。
- 但如果是自增的id，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高
- 什么是uuid？
>```bash
>MariaDB [zhdtest]> select uuid();
>+--------------------------------------+
>| uuid() |
>+--------------------------------------+
>| 0558a506-2a9c-11ed-8402-000c29935689 |
>+--------------------------------------+
>1 row in set (0.001 sec)
>```
## 7.4. 非聚簇索引(二级索引)
### 7.4.1. 普通索引
- 建表时指定索引
>```sql
>CREATE TABLE `dept`(
> `id` bigint(20) NOT NULL AUTO_INCREMENT,
> `code` varchar(32) NOT NULL ,
> `name` varchar(30) NOT NULL,
> `address` varchar(50) DEFAULT NU;LL,
> PRIMARY KEY(`id`),
> KEY `idx_code` (`code`) USING BTREE -- 创建索引
>)ENGINE = InnoDB
>DEFAULT CHARSET = utf8mb4
>COMMENT '部门表'
>```
- create 语句创建索引
>```sql
>create index idx_user_name on user(user_name);
>```
- alter 语句创建索引
>```sql
>alter table user add index idx_email (email);
>```
- 删除索引
>```sql
>drop index idx_user_name on ydl_user;
>```
#### 7.4.1.1. 短索引
在创建索引时，有些列数据特别长，例如 email，其前几位就能唯一标识一行数据，我们可以截取前几位作为索引
>```sql
>create index idx_email on user(email(5));
>```
### 7.4.2. 唯一索引
#### 7.4.2.1. 唯一索引 VS 主键索引
- 唯一索引列允许有空值，主键列不能有空值
- 主键列在创建时，已经默认 **非空值 + 唯一索引**
- 主键可以被其他表引用为外键，而唯一索引不能
- 一个表最多只能创建一个主键，而唯一索引可以有多个
- **主键更适合不容易修改的唯一标识**
- 如身份证、自动递增列等
- 唯一索引无所谓修不修改，只需要修改前后值都唯一即可
#### 7.4.2.2. 唯一索引 VS 唯一约束
- 唯一约束和唯一约束，都可以实现列数据的唯一，列值可以为null
- **创建唯一约束，会自动创建一个同名的唯一索引**，该索引不能单独删除，删除约束会自动删除索引。**唯一约束是通过唯一索引来实现数据唯一**
- 创建一个唯一索引，这个索引就是独立的索引，可以单独删除
- 如果一个列上想有约束和索引，且两者可以单独的删除。可以先建唯一索引，再建同名的唯一约束。
#### 7.4.2.3. 唯一索引的作用
- 作用
- 保证加索引的这列值唯一
- **如果不加唯一索引，要判断新插入的数据是不是唯一，必须要进行全表扫描，效率太低**
- 加唯一索引，就可以加速判断，避免全表扫描
- 应用场景
- 注册用户名时，新注册的用户名不能是已存在的用户名
- 则可以使用唯一索引 (避免全表扫描)
### 7.4.3. 多个二级索引的组合使用
- mysql 在查询语句时，**一般只会使用【一个索引】**
- 除非是使用【or 连接的两个索引列】，会产生索引合并 (索引合并，但它还是使用索引的)
#### 7.4.3.1. 索引组合使用的几种情况
- 现有表结构如下
>```sql
>CREATE TABLE items(
> id INT NOT NULL AUTO_INCREMENT,
> brand VARCHAR(20), -- 品牌
> price INT, -- 价格
> sales INT, --销量
> PRIMARY KEY (id),
> KEY `idx_brand` (`brand`),
> KEY `idx_price` (`price`),
> KEY `idx_sales` (`sales`)
>)
>```
- **1. 检索品牌为“阿玛尼”的包包**
- 第一步: 通过【品牌索引】检索出所有阿玛尼的商品 id , 根据主键索引回表查询，得到结果
- 结论 : 只使用一个索引 (不考虑主键索引)
- **2. 检索品牌为“阿玛尼”，价格在1万到3万之间的包包**
- 第一步: 通过【品牌索引】检索出所有阿玛尼的商品id
- 第二步: 回表扫描，找到对应数据行，再进行价格判断
- 遍历检索的数据，逐个进行价格判断
- mysql索引树管理的数据量比较巨大，通过索引筛选到的数据，只是全部数据的很小的一部分
- 再次走一遍【价格索引】的性能，还不如全部遍历来的快
- 结论: 只会走一个索引
- **3. 检索名称为阿玛尼（Armani），价格为26800，且销量在50以上的包包**
- 第一步:通过【品牌索引】检索出所有阿玛尼的商品id，进行缓存
- 通过优化，会使得第一次索引检索到的数据越少越好。使用的索引未必是【品牌索引】
- 第二步: 直接回表扫描，根据剩余条件检索结果
- **4. 要检索名称为阿玛尼（Armani）或名称为LV的包包**
- 第一步: 通过【品牌索引】检索出所有阿玛尼的商品id，得到结果
- 结论: 我们经常听说，有or索引会失效，但是像这样的【brand =‘Armani’ or brand = ‘LV’】并不会，他相当于一个in关键字，会使用一个索引。
- **同一个索引，使用 or 连接不会使得索引失效**
- **5. 检索名称为阿玛尼（Armani）或价格大于8000的包包**
- 第一步: 通过【品牌索引】检索出所有阿玛尼的商品id，进行缓存。
- 第二步: 通过【价格索引】检索出价格在5万到7万之间的商品id，这是一个连接条件带有【or的查询】，所以需要和上一步的结果进行【并集】，得到结果。
- 结论：这个过程叫 **索引合并**
- **当检索条件有or但是所有的条件都有索引时，索引不失效**，可以走【两个索引】
- **6. 检索名称为阿玛尼（Armani），且价格大于8000，且【产地（该列无索引）】在北京的包包**
- 第一步: 通过【品牌索引】检索出所有阿玛尼的商品id。
- 第二步: 直接回表扫描，根据剩余条件检索结果
- 结论: 只会使用第一个索引。
- **7. 检索名称为阿玛尼（Armani）或价格大于8000，或【产地（该列无索引）】在北京的包包**
- 第一步: 优化器发现【产地列】无索引，同时连接的逻辑是【or】没有办法利用【索引】优化，只能全表扫描，索引失效。
- 结论: 发生全表扫描，索引失效，条件中有没建立索引的列，同时关联条件是or
#### 7.4.3.2. 索引失效条件
综上所述，索引失效的条件为：**where 条件中对不加索引的列使用 or 条件关联**
### 7.4.4. 复合索引
- [复合索引](https://gitee.com/HaiXiuDeDXianSheng/big-data-learning-notes/tree/master/HIVE%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/hive%E6%96%87%E4%BB%B6%E6%A0%BC%E5%BC%8F#52321-%E8%A6%86%E7%9B%96%E7%B4%A2%E5%BC%95)
- 适合多条件查询
- 索引的【组合使用】效率低于【复合索引】
>```sql
>alert table test add idx_a1_a2_a3 table (a1,a2,a3);
>create index idx_user_nick_name on ydl_user(user_name,nick_name,email(7));
>```
转化为下边的表格,复合索引是按如下进行排序的
|品牌 |价格| 销量| id|
|:-:|:-:|:-:|:-:|
|Armani |16800| 35| |13,24,76|
|Armani |26800 |35 |12,14,16|
|Armani |26800 |100 |34,56,17|
|Armani |68888 |15 |1,4,5,6,7|
|GUCCI |8999 |135 |78,92|
|LV |9999 |326 |55,63 |
|LV |12888 |99 |57,99 |
|LV |42888 |69 |11,22 |
|PRADA |9588| 125 |111,202 |
- 索引是有排序关系的
- 复合索引建立的过程，是按照建立索引的字段顺序进行排序
- **最左匹配前缀原则**
- 最左前缀匹配原则，非常重要的原则，**mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配** ，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立（a,b,c,d）顺序的联合索引，**d是用不到索引的**，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
- 只要遇到范围查询，那后面的数据就都是无序的，需要全表扫描
- = 和 in 可以乱序，比如a = 1 and b < 2 and c = 3
- 我们建立的索引就可以是（a,c,b）或者（c,a,b）
- mysql 在优化的时候会把所有的等值条件往前放
- **为什么复合索引效率比索引的组合效率高？**
- 复合索引包含了多个索引，可以适应多条件查询的情况
- 比索引的组合更少机会出现索引失效问题。
## 7.5. 大字段建表注意事项
在 innodb 中不要尽可能不要建立 text、blob 等大字段，否则会极大影响查询性能。
## 7.6. Explain 语句
explain 关键字可以模拟 Mysql 优化器
>```bash
>MariaDB [zhdtest]> explain select * from user where id = 4;
>+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
>+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
>| 1 | SIMPLE | user | const | PRIMARY | PRIMARY | 4 | const | 1 | |
>+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
>1 row in set (0.007 sec)
>```
- 对于 explain 的查询结果
|列号 |列 |说明|
|:-:|:-|:-|
|1 |id |select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序|
|2 |select_type |查询类型|
|3 |table |正在访问哪个表|
|4 |partitions |匹配的分区|
|5 |type |访问的类型|
|6 |possible_keys |显示可能应用在这张表中的索引，一个或多个，**但不一定实际使用到**|
|7 |key |实际使用到的索引，如果为NULL，则没有使用索引|
|8 |key_len |表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度|
|9 |ref |显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值|
|10| rows| 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数 filtered //查询的表行占表的百分比|
|11| filtered| 查询的表行占表的百分比|
|12| Extra| 包含不适合在其它列中显示但十分重要的额外信息|
- 注意对比 mysql 的 Explain 和 [Hive 的 Explain](https://gitee.com/HaiXiuDeDXianSheng/big-data-learning-notes/tree/master/HIVE%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/HIVE%E5%9F%BA%E7%A1%80#121-%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92-explain) 的不同
### 7.6.1. id 字段
- id
- select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
#### 7.6.1.1. 若 id 相同
- id如果相同，可以认为是一组，执行顺序从上至下
>```bash
>MariaDB [zhdtest]> explain select * from user a,user b where a.id=b.id ;
>+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
>+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
>| 1 | SIMPLE | a | ALL | PRIMARY | NULL | NULL | NULL | 3 | |
>| 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer (flat, BNL join) |
>+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
>2 rows in set (0.005 sec)
>```
#### 7.6.1.2. 若 id 不同
- 如果是子查询，则 id 号会自增，**id 的值越大优先级越高，越先被执行**
>```
>MariaDB [zhdtest]> explain select * from user where balance> (select balance from user where id = 1);
>+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
>+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
>| 1 | PRIMARY | user | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
>| 2 | SUBQUERY | user | const | PRIMARY | PRIMARY | 4 | const | 1 | |
>+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
>2 rows in set (0.015 sec)
>```
#### 7.6.1.3. 若 id 部分相同部分不同
- **id如果相同，可以认为是一组**，从上往下顺序执行
- 在所有组中，id值越大，优先级越高，越先执行
>```bash
>MariaDB [zhdtest]> explain select * from user a,user b where a.id=b.id union select * from user a,user b where a.id=b.id ;
>+------+--------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
>+------+--------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
>| 1 | PRIMARY | a | ALL | PRIMARY | NULL | NULL | NULL | 3 | |
>| 1 | PRIMARY | b | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer (flat, BNL join) |
>| 2 | UNION | a | ALL | PRIMARY | NULL | NULL | NULL | 3 | |
>| 2 | UNION | b | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer (flat, BNL join) |
>| NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | |
>+------+--------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
>```
### 7.6.2. select_type 字段
|字段值|解释说明|
|:-:|:-|
|SIMPLE|简单查询，不包含子查询或Union查询的sql语句。|
|PRIMARY|查询中若包含任何复杂的子部分，最外层查询则被标记为主查询。|
| SUBQUERY|在select或where中包含子查询。|
|UNION|若第二个select**出现在uion之后，则被标记为UNION。**|
|UNION RESULT|从UNION表获取结果的合并操作。(临时表)|
### 7.6.3. type字段
- type字段表示查询的性能
- 从最好到最差备注：掌握以下10种常见的即可
- NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL
#### 7.6.3.1. NULL
type 为 NULL 表示，MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引
>```bash
>MariaDB [zhdtest]> explain select min(id) from user;
>+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
>+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
>| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
>+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
>1 row in set (0.001 sec)
>```
#### 7.6.3.2. system
表只有一行记录（等于系统表），平时不太会出现，可以忽略
#### 7.6.3.3. const
- 表示通过索引一次就找到了
- const 用于比较 primary key 或者 unique 索引
- 因为只匹配了一行数据
- 主键置于where列表中，MySQL就能将该查询转换为一个常量
>```bash
>MariaDB [zhdtest]> explain select * from user where id=1;
>+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
>+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
>| 1 | SIMPLE | user | const | PRIMARY | PRIMARY | 4 | const | 1 | |
>+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
>1 row in set (0.000 sec)
>```
#### 7.6.3.4. eq_ref
- 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，
- **常见于主键或唯一索引扫描。**
- 经常出现在关联查询中
- 多次等值查询
>```bash
>MariaDB [zhdtest]> explain select * from user a left join user b on a.id=b.id;
>+------+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+
>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
>+------+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+
>| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 3 | |
>| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | zhdtest.a.id | 1 | |
>+------+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+
>2 rows in set (0.002 sec)
>```
#### 7.6.3.5. ref
- 非唯一性索引扫描 (**用于普通索引**，不是唯一的，需要回表查询)，返回匹配某个单独值的所有行
- 可能会找到多个符合条件的行，属于查找和扫描的混合体
>```bash
>MariaDB [zhdtest]> show create table items\G;
>*************************** 1. row ***************************
> Table: items
>Create Table: CREATE TABLE `items` (
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `brand` varchar(20) DEFAULT NULL,
> `price` int(11) DEFAULT NULL,
> `sales` int(11) DEFAULT NULL,
> PRIMARY KEY (`id`),
> KEY `idx_brand` (`brand`),
> KEY `idx_price` (`price`),
> KEY `idx_sales` (`sales`)
>) ENGINE=InnoDB DEFAULT CHARSET=latin1
>1 row in set (0.000 sec)
>
>
>MariaDB [zhdtest]> explain select * from items where brand = 'a';
>+------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
>+------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
>| 1 | SIMPLE | items | ref | idx_brand | idx_brand | 23 | const | 1 | Using index condition |
>+------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
>1 row in set (0.001 sec)
>```
#### 7.6.3.6. ref_or_null
类似于 ref，但可以搜索值为 NULL 的行
>```sql
>explain select * from student s where name = '白杰' or name is null
>```
#### 7.6.3.7. index_marge
- index_marge : 索引合并
>```sql
>explain select * from student where id = 1 or name ='李兴';
>```
#### 7.6.3.8. range
- 范围查询
- in、between、>、<
#### 7.6.3.9. index
- index: 表示全索引扫描
- Index与All区别
- index只遍历索引树，通常比All快因为索引文件通常比数据文件小
- 也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的
#### 7.6.3.10. ALL
全表扫描
### 7.6.4. possible_keys 字段
显示可能应用在这张表中的索引，一个或多个查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用
### 7.6.5. key 字段
实际使用到的索引，如果为NULL，则没有使用索引查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表
### 7.6.6. filtered字段
它指返回结果的行占需要读到的行(rows列的值)的百分比
### 7.6.7. Extra字段
- 该列包含不适合在其它列中显示，但十分重要的额外信息，可能会展示如下信息
- Using filesort
- Using temporary
- 使用了临时表保存中间结果
- Using where
- impossible where
- where子句的值总是false，不能用来获取任何数据：
- Select tables optimized away
- SELECT操作已经优化到不能再优化了
- no matching row in const table
## 7.7. 使用索引的问题
### 7.7.1. 哪些情况下适合创建索引
- 频繁作为 where 条件语句查询条件的字段
- 关联字段需要建立索引
- 根据 [eq_ref](#7634-eq_ref)，被关联表会根据关联表的关联字段去做等值查询。如果没有索引，则会全表扫描好多次。
- 分组、排序字段可以建立索引 (索引本身就是有序的)
- 统计字段可以建立索引，例如 count()、max() 等
在学习 [临时表](#5-mysql临时表) 时分析过 [group by 执行的流程](#5121-group-by-执行流程) , 而在创建索引后，执行过程如下
直接使用索引信息，统计每个组的人数，直接返回。
### 7.7.2. 哪些情况不适合建立索引
- 频繁更新的字段
- 不然既要频繁更新主键索引，又要频繁更新该普通索引
- where 条件中用不到的字段
- 表数据可以确定比较少的情况
- 数据重复且分布比较均匀的字段
- 唯一性太差的字段不适合建立索引，例如性别、真假值
- 参与列计算的列不适合建立索引，索引会失效
### 7.7.3. 复合索引
- 能使用复合索引的要使用复合索引
- **null 值也是可以建立索引的**，它会被处理成最小值放在 B+ 树的最左侧
>```bash
>MariaDB [zhdtest]> show create table items\G;
>*************************** 1. row ***************************
> Table: items
>Create Table: CREATE TABLE `items` (
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `brand` varchar(20) DEFAULT NULL,
> `price` int(11) DEFAULT NULL,
> `sales` int(11) DEFAULT NULL,
> PRIMARY KEY (`id`),
> KEY `idx_brand` (`brand`),
> KEY `idx_price` (`price`),
> KEY `idx_sales` (`sales`)
>) ENGINE=InnoDB DEFAULT CHARSET=latin1
>1 row in set (0.000 sec)
>
>ERROR: No query specified
>
>MariaDB [zhdtest]> explain select * from items where brand is null; # 对于 null 值得判断，可以走索引
>+------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
>+------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
>| 1 | SIMPLE | items | ref | idx_brand | idx_brand | 23 | const | 1 | Using index condition |
>+------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
>1 row in set (0.018 sec)
>```
### 7.7.4. 使用短索引
- 对字符串的列创建索引，如果可能，应该指定一个前缀长度。
- 例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。
- 短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
### 7.7.5. 排序的索引问题
- 一般情况下 mysql 查询只会使用一个索引
- 因此如果 where 子句中已经使用了索引，则 order by 中得列时不会使用索引的
- 因此数据库默认排序可以符合要求的情况下尽可能不要使用排序
- 尽量不要包含多个列的排序，如果需要，最好给这些列创建 **复合索引**
### 7.7.6. 索引失效的情况
- 使用 or 的条件下，其中有个字段没有索引
- 复合索引不满足最左原则，**就不能使用索引的全部内容**
- 索引不一定失效，可能只使用了一部分索引
- like查询以 % 开头
- **存在列计算**
- 加减乘除都算列计算
>```bash
>MariaDB [zhdtest]> explain select * from items where concat(brand,'-') = 'a-';
>+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
>+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
>| 1 | SIMPLE | items | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
>+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
>1 row in set (0.019 sec)
>
>MariaDB [zhdtest]> explain select * from items where brand = 'a-';
>+------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
>+------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
>| 1 | SIMPLE | items | ref | idx_brand | idx_brand | 23 | const | 1 | Using index condition |
>+------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
>1 row in set (0.014 sec)
>```
- 存在隐式类型转化
- 但这个未必会失效，可能失效也可能不失效
# 8. Mysql 锁机制 (重点)
- 锁是为了保证数据库中数据的一致性，使得各种【共享资源】在被访问时变得【有序】而设计的一种规则
- InnoDB 支持【行锁】和【表锁】
- 【表锁】，开销小，加锁快，不会出现死锁，锁粒度大，并发度相对低
- 【行锁】，开销大，加锁慢，会出现死锁，并发度高
## 8.1. InnoDB锁类型
- InnoDB锁类型主要有
- 读锁 (共享锁), s 锁 (shared lock)
- 写锁 (排他锁)
- 意向锁
- MDL锁
### 8.1.1. s 锁 (shared lock)
- 一个事务获取了一个数据的读锁，其他事务也可获得该行对应的读锁，但是不能获得写锁
- 即，**一行数据可能被多个事务进行读取，一旦有人获取了这行数据的读锁，这行数据就被锁住了，其他事务不能对它进行修改**
- 读锁是共享锁，多个事务可以同时持有，当有一个或多个事务持有共享锁时，被锁的数据就不能修改。
- 就是**可以多个事务读，但只能一个事务写**
- **获取读锁的语法**
>```sql
>select.... lock in share mode
>```
#### 8.1.1.1. 案例测试
- 窗口 A 开启一个事务 A
- 获取读锁
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.001 sec)
>
>MariaDB [zhdtest]> select * from user where id = 1 lock in share mode;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 400.00 |
>+----+------+---------+
>1 row in set (0.013 sec)
>```
- 窗口 B 开启一个事务 B
- 其仍可以获取读锁
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> select * from user where id = 1 ;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 400.00 |
>+----+------+---------+
>1 row in set (0.001 sec)
>```
- 窗口 B 尝试获取写锁
- 发现一直处于阻塞状态
>```bash
>MariaDB [zhdtest]> update user set balance = 600 where id = 1; // 一直处于阻塞状态，直到超时
>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
>```
- 当窗口 A 提交事务后，窗口 B 可以成功提交 update 语句
>```bash
>MariaDB [zhdtest]> update user set balance = 600 where id = 1;
>Query OK, 1 row affected (0.005 sec)
>Rows matched: 1 Changed: 1 Warnings: 0
>```
### 8.1.2. X 锁 (exclusive lock)
- 写锁
- 也叫排他锁，X锁 (exclusive)
- 一个事务获得了一个数据行的写锁，既可以读取该行的记录，也可以修改该行的记录
- 但是 **其他事务不能再获得任何其他锁**，包括s锁，直到事务将锁释放
- **写锁，是独占锁，只有一个事务可以持有**
- 一些 DML 语句操作都会对行记录加写锁
- update 语句可以自动加锁
#### 8.1.2.1. 案例测试
- 原有数据
>```bash
>MariaDB [zhdtest]> select * from user where id = 1;
>+----+------+---------+
>| id | name | balance |
>+----+------+---------+
>| 1 | zhd | 600.00 |
>+----+------+---------+
>1 row in set (0.000 sec)
>```
- 窗口 A 开启事务 A，但不提交
- update 语句会自动加锁
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> update user set balance = 1000 where id = 1;
>Query OK, 1 row affected (0.000 sec)
>Rows matched: 1 Changed: 1 Warnings: 0
>```
- 窗口 B 开启事务 B
- 使用 update 语句更新
- 发现程序阻塞，这是因为 update 语句触发了写锁
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> update user set balance = 200 where id = 1;
>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
>```
- 待窗口 A commit 事务后，窗口 B 的 update 语句可以提交
- 锁的执行顺序是按照加锁的顺序执行的。
### 8.1.3. 记录锁/行锁 (Record Lock)
- 记录锁，即我们所说的 行锁，只有 innodb 才支持
#### 8.1.3.1. 表锁与行锁案例测试
- 现有数据
>```bash
>MariaDB [zhdtest]> select * from user;
>+----+--------+---------+
>| id | name | balance |
>+----+--------+---------+
>| 1 | zhd | 2000.00 |
>| 2 | zzz | 800.00 |
>| 3 | lisi | 1200.00 |
>| 4 | wangwu | 4200.00 |
>+----+--------+---------+
>4 rows in set (0.000 sec)
>
>MariaDB [zhdtest]> show create table user\G;
>*************************** 1. row ***************************
> Table: user
>Create Table: CREATE TABLE `user` (
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
> `balance` decimal(10,2) unsigned DEFAULT NULL,
> PRIMARY KEY (`id`)
>) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
>1 row in set (0.000 sec)
>```
##### 8.1.3.1.1. where 条件列不加索引 (表锁)
- 窗口 A 开启事务 A
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> update user set balance = 3000 where name = 'wangwu';
>Query OK, 1 row affected (0.001 sec)
>Rows matched: 1 Changed: 1 Warnings: 0
>```
- 窗口 B 开启事务 B
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> update user set balance = 2000 where name = 'lisi';
>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction # 提交超时
>```
- 由于窗口 A 和窗口 B 分别针对同一个表的不同行数据进行修改，但是在事务 A 提交前事务 B 无法修改
- 因此触发的是 **表锁**
##### 8.1.3.1.2. where 条件列加索引 (行锁)
- 窗口 A 开启事务，不提交
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.002 sec)
>
>MariaDB [zhdtest]> update user set balance = 0 where id =1;
>Query OK, 1 row affected (0.017 sec)
>Rows matched: 1 Changed: 1 Warnings: 0
>```
- 窗口 B 开启事务
>```bash
>MariaDB [zhdtest]> begin ;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> update user set balance = 100 where id = 1;
>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
>
>MariaDB [zhdtest]> update user set balance = 100 where id = 2; // 事务 B 可以更新索引不同的行
>Query OK, 1 row affected (0.000 sec)
>Rows matched: 1 Changed: 1 Warnings: 0
>```
- **因此对于 where 条件中加索引的列，Innodb 加的是行锁，而非表锁**
- 这也说明，行锁是加在索引上的
### 8.1.4. 间隙锁 (gap lock)
- 间隙锁帮我们解决了 mysql 在 [RR隔离级别](#633-可重复读-rr) 下的一部分 [幻读问题](#63311-幻读案例)
- **间隙锁，锁定的是记录范围，不包含记录本身**
- 即，不允许在某个范围内插入数据
#### 8.1.4.1. 间隙锁的生成条件
- 现有数据如下
>```bash
>MariaDB [zhdtest]> show create table user\G;
>*************************** 1. row ***************************
> Table: user
>Create Table: CREATE TABLE `user` (
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
> `balance` decimal(10,2) unsigned DEFAULT NULL,
> PRIMARY KEY (`id`)
>) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
>1 row in set (0.000 sec)
>
>MariaDB [zhdtest]> select * from user;
>+----+--------+---------+
>| id | name | balance |
>+----+--------+---------+
>| 1 | zhd | 2000.00 |
>| 2 | zzz | 800.00 |
>| 3 | lisi | 1200.00 |
>| 4 | wangwu | 3000.00 |
>| 7 | zd | 200.00 |
>| 10 | zs | 300.00 |
>| 11 | sd | 1000.00 |
>+----+--------+---------+
>7 rows in set (0.000 sec)
>```
- 窗口 A 开启事务，但不提交
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> select * from user where id<=10 and id>=4 lock in share mode; # 锁定 id 在 4 到 10 之间的范围
>+----+--------+---------+
>| id | name | balance |
>+----+--------+---------+
>| 4 | wangwu | 3000.00 |
>| 7 | zd | 200.00 |
>| 10 | zs | 300.00 |
>+----+--------+---------+
>3 rows in set (0.005 sec)
>```
- 窗口 B 开启事务
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> insert into user values (5,'wd',1000); # 在记录 id 为 4 到 10 之间的数据无法进行插入，该范围被锁定
>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
>MariaDB [zhdtest]> insert into user values (12,'wd',1000); # 锁定范围以外的记录可以进行插入
>Query OK, 1 row affected (0.000 sec)
>```
- **间隙锁生成条件**
- 事务 A 使用 where 条件进行范围检索，有未提交事务
- 事务 B 向 A 中满足检索条件的范围中插入数据
- **where 条件必须有索引**
- 否则就是表锁
#### 8.1.4.2. 记录锁和间隙锁的组合 (next-key lock)
- next-key lock
- 即临键锁，是记录锁与间隙锁的组合
- 既封锁索引记录，又封锁索引区间。它指的是加在某条记录以及这条记录前面间隙上的锁，会锁住一段左开右闭区间的数据
- InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。
- 详见 https://www.jianshu.com/p/478bc84a7721
- 临键锁的主要目的，也是为了避免幻读(Phantom Read)。
- 如果把事务的隔离级别降级为RC，临键锁则也会失效
- 触发临键锁的案例
>```sql
>begin;
>-- 或根据非唯一索引列 锁住某条记录
>SELECT * FROM table WHERE age = 24 FOR UPDATE;
>```
### 8.1.5. MDL(Meta data lock) 锁
- MDL锁
- 即元数据锁，用于保证表中的[元数据信息](#216-mysql内置数据库)
- 在会话 A 中，表开启事务后，会自动获得一个 MDL 锁
- 会话 B 就不可以对其执行任何 DDL 语句，不能对其修改表结构
- 元数据就是描述数据的数据，也就是你的表结构
## 8.2. 死锁问题
**当多个事务互相持有对方所需要的锁，且想要夺取对方所持有的锁，产生循环等待，无法正常结束，即会产生死锁问题**。详细可以参考 [javaSE 中的死锁](https://gitee.com/HaiXiuDeDXianSheng/java-se-learning-notes#%E7%BA%BF%E7%A8%8B%E6%AD%BB%E9%94%81%E9%97%AE%E9%A2%98)
- 死锁产生的条件（正儿八经版）
- 互斥条件
- 一段时间内，计算机中的某个资源智能被一个进程占用。此时，其他进程请求该资源，则只能等待
- 不可剥夺条件
- 某个进程获得的资源，在使用完毕之前，不能被其他进程强行夺取，只能由获得资源的进程主动释放
- 请求保持条件
- 进程已经获得了至少一个资源，又要请求其他资源，但请求的资源已经被其他进程占有，此时请求的进程就会被阻塞，并且不会释放自己已获得的资源。
- 循环等待条件
- 系统中的进程之间相互等待，同时各自占用的资源又会被下一个进程所请求。例如有进程A、进程B和进程C三个进程，进程A请求的资源被进程B占用，进程B请求的资源被进程C占用，进程C请求的资源被进程A占用，于是形成了循环等待条件
- 当产生死锁时，mysql 会报异常，并将事务进行回滚
### 8.2.1. 避免死锁
- Innodb 会采用一种叫 **等待图(wait-for graph)** 的方法自动检测死锁
- 如果发现死锁，则会**自动回滚一个事务 (回滚后，锁也就释放了)**
- **避免死锁的几种方式**
- 尽量让数据检索通过索引来完成
- [避免无效索引导致行锁升级为表锁](#81311-where-条件列不加索引-表锁)
- 合理设计索引，尽量减小锁的范围
- 尽量减少查询条件的范围，尽量避免间隙锁或者缩小[间隙锁](#814-间隙锁-gap-lock)的范围
- 控制事务的大小，减小一次事务锁定的资源数量，缩短锁定资源的时间
- 如果一条 SQL 语句涉及事务加锁操作，则尽可能将其放在整个事务的最后执行
## 8.3. 表锁
- 对于 InnoDB 表，绝大部分情况下应该使用【行级锁】(不然干嘛选 InnoDB)，但在个别特殊事务中，可以考虑使用【表锁】
- 情况1 : 事务需要更新【**大部分或者全部数据**】，表又很大
- 如果使用行锁，则效率低下，还会造成其他事务长时间【锁等待】和【锁冲突】
- 此时选用【表锁】提高执行事务的速度
- 情况2 : 事务涉及多个表，比较复杂，很可能引起死锁，引起大量事务回滚
- 考虑使用表锁，减少因事务回滚带来的开销
- 因为表锁也会有死锁
- InnoDB 主动加表锁的方式
>```sql
>-- lock tables 会自动开启一个事务
>lock tables teacher write,student read; -- 给 teacher 表加写锁，给 student 表加读锁
>select * from teacher;
>commit;
>unlock tables;
>```
- 使用表锁的注意点如下
- 使用 LOCK TABLES 可以给 InnoDB 加表锁
- 但是表锁不是 InnoDB 存储引擎负责的
- 表锁是其上一层 Mysql Server 负责的
- 仅当 autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB 层才能感知 MYSQL Server 加的表锁，MYSQL Server 才能感知 InnoDB 加的行锁
- **这种情况下 InnoDB 才能自动识别涉及表锁的死锁**
- 否则 InnoDB 无法自动检测并处理表级别的死锁
- 在用LOCAK TABLES对InnoDB加锁时要注意
- **事务结束前，不要用UNLOCAK TABLES释放表锁**
- 因为 UNLOCK TABLES 会隐含地提交事务
- OMMIT 或 ROLLBACK 不能释放用 LOCAK TABLES 加的表级锁，**必须用 UNLOCK TABLES 手动释放表锁**
- 表锁力度很大，一旦加锁，别的事务不能访问该表的任何数据，所以要慎用
## 8.4. 锁的分类 (从另外的角度分)
### 8.4.1. 乐观锁
- 乐观锁大多是基于数据【**版本记录机制**】实现的
- 一般是给数据库表增加一个版本号 (version 字段)，有点像 [HBase 的数据存储机制](https://gitee.com/HaiXiuDeDXianSheng/big-data-learning-notes/tree/master/HBase%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0#23-hbase-%E7%89%A9%E7%90%86%E5%AD%98%E5%82%A8%E7%BB%93%E6%9E%84) (HBase 通过 Timestamp 控制版本，来实现数据的修改)
- 读数据时，将版本号一同读出
- 更新数据时，对此版本号加一
- 此时将提交数据的版本号与数据库表中对应记录的当前版本号对比，如果提交数据的版本号大于数据库中表的当前版本号，则予以更新，否则认为说过期数据
- 乐观锁，通过不加锁的方式，实现了加锁的功能
- 先尝试去改，如果修改发生问题，则再去解决
#### 8.4.1.1. 案例
- 现有数据如下
>```
>MariaDB [zhdtest]> show create table user_version\G;
>*************************** 1. row ***************************
> Table: user_version
>Create Table: CREATE TABLE `user_version` (
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `name` varchar(20) DEFAULT NULL,
> `balance` float(10,2) DEFAULT NULL,
> `version` int(11) DEFAULT NULL,
> PRIMARY KEY (`id`)
>) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
>1 row in set (0.000 sec)
>
>MariaDB [zhdtest]> select * from user_version;
>+----+--------+---------+---------+
>| id | name | balance | version |
>+----+--------+---------+---------+
>| 1 | 张三 | 500.00 | 1 |
>| 2 | 李四 | 1500.00 | 1 |
>| 3 | 王五 | 2500.00 | 1 |
>+----+--------+---------+---------+
>3 rows in set (0.000 sec)
>```
- 窗口 A 开启事务，不提交
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> select * from user_version where id = 1;
>+----+--------+---------+---------+
>| id | name | balance | version |
>+----+--------+---------+---------+
>| 1 | 张三 | 500.00 | 1 |
>+----+--------+---------+---------+
>1 row in set (0.001 sec)
>
>MariaDB [zhdtest]> update user_version set balance = balance+200, version=version+1 where id =1 and version =1 ;
>Query OK, 1 row affected (0.001 sec)
>Rows matched: 1 Changed: 1 Warnings: 0
>```
- 窗口 B 开启事务, 不提交
>```bash
>MariaDB [zhdtest]> begin;
>Query OK, 0 rows affected (0.000 sec)
>
>MariaDB [zhdtest]> select * from user_version;
>+----+--------+---------+---------+
>| id | name | balance | version |
>+----+--------+---------+---------+
>| 1 | 张三 | 500.00 | 1 |
>| 2 | 李四 | 1500.00 | 1 |
>| 3 | 王五 | 2500.00 | 1 |
>+----+--------+---------+---------+
>3 rows in set (0.000 sec)
>```
- 此时窗口 A 进行事务提交
>```bash
>MariaDB [zhdtest]> commit;
>Query OK, 0 rows affected (0.001 sec)
>```
- 窗口 B 根据其之前查询到的信息执行语句
>```bash
>MariaDB [zhdtest]> update user_version set balance = balance+200, version=version+1 where id =1 and version =1;
>Query OK, 0 rows affected (14.252 sec)
>Rows matched: 0 Changed: 0 Warnings: 0 # 匹配不到数据，因为事务 A 提交后，id = 1 的 version 已经变成 2 了，表中已经匹配不到对应得数据了
>```
- 这种使用了不加锁的方式，实现了一个事务修改期间，禁止其他事务修改的能力
### 8.4.2. 悲观锁
- 我们前面所有手动加的锁都是悲观锁，悲观锁，不管三七二十一，先上锁。
- MySQL中的共享锁和排它锁都是悲观锁。
- 数据库的增删改操作默认都会加排他锁，
- 而查询不会加任何锁
# 9. 日志系统
- mysql 提供了很多日志系统，一些是 Mysql Server提供的
|日志类型 |写入日志的信息|
|:-:|:-|
|binlog 日志| 记录了对MySQL数据库执行更改的所有操作|
|慢查询日志| 记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询|
|错误日志| 记录在启动，运行或停止mysqld时遇到的问题|
|通用查询日志| 记录建立的客户端连接和执行的语句|
|中继日志(relay log)| 从复制主服务器接收的数据更改|
- 也有一些是存储引擎层提供的日志
- redo Log 日志
- undo Log 日志
## 9.1. bin log 日志 (Mysql Server 提供)
### 9.1.1. 概述
- 二进制日志 (binnary log), 也叫 bin log 日志
- 以【事件形式】记录了对 Mysql 数据库执行更改的所有操作
- **只要 sql 语句对数据库做了修改**，无论是 DDL 还是 DML (除了 SELECT )。都会以日志形式进行记录
- binlog 由 Mysql Server 进行维护，和采用何种存储引擎没有关系，记录的是所有的更新操作的日志记录
- binlog 是在事务最终 commit 前写入的
- SELECT、SHOW 等不涉及数据库更新的语句不会记入 binlog
- 对支持事务的引擎如innodb而言，**必须要提交了事务才会记录binlog**
- binlog 的使用场景
- 主从复制
- 数据恢复
- 通过 mysqlbinlog 工具恢复数据
- Mysql 8 的 binlog 默认是开启的，5.7是关闭的
### 9.1.2. 数据恢复
- 1. 确认开启 binlog , log_bin 变量为 ON 代表开启
>```bash
>MariaDB [zhdtest]> show variables like '%log_bin%';
>+---------------------------------+-------+
>| Variable_name | Value |
>+---------------------------------+-------+
>| log_bin | OFF | # 没有开启，需要修改 mariadb 配置文件进行开启
>| log_bin_basename | |
>| log_bin_compress | OFF |
>| log_bin_compress_min_len | 256 |
>| log_bin_index | |
>| log_bin_trust_function_creators | OFF |
>| sql_log_bin | ON |
>+---------------------------------+-------+
>7 rows in set (0.003 sec)
>```
#### 9.1.2.1. 开启 binlog
- 参考资料：https://www.cmdschool.org/archives/15272
- 这里以安装的 mariadb 为例，进行 binlog 开启设置
>```bash
>[zhd@localhost ~]$ cd /etc/my.cnf.d
>[zhd@localhost my.cnf.d]$ chmod 777 mariadb-server.cnf
>[zhd@localhost my.cnf.d]$ vim mariadb-server.cnf # 如果是 mysql 则伟为 vim mysql-server.cnf
>```
- 修改配置文件如下
>```conf
>[mysqld]
>datadir=/var/lib/mysql
>socket=/var/lib/mysql/mysql.sock
>log-error=/var/log/mariadb/mariadb.log
>pid-file=/run/mariadb/mariadb.pid
># 新增 binlog 配置内容如下
>log_bin=binlog # 为日志路径和文件格式，可以修改为 log_bin = /var/lib/mysql/mysql-bin.log , 则数据会存储在/var/lib/mysql/目录下，文件会以mysql-bin.log开头
>binlog_format=ROW # 默认是 MIXED 格式，但是推荐是 ROW 格式（row格式问题在于数据量太大）
>expire_logs_days=7 # 设置 7 天自动过期
>```
- 注意：修改完后，必须将 mariadb-server.cnf 恢复到只读模式
>```bash
>[zhd@localhost my.cnf.d]$ chmod 644 mariadb-server.cnf
>```
- 有关数字表示linux文件权限的相关内容，详见 [linux基础笔记](https://gitee.com/HaiXiuDeDXianSheng/big-data-learning-notes/tree/master/linux%E5%9F%BA%E7%A1%80#chmod-%E4%BB%A5%E6%95%B0%E5%AD%97%E5%BD%A2%E5%BC%8F%E6%94%B9%E5%8F%98%E6%96%87%E4%BB%B6)
- 重新启动，查看 log_bin 变量
>```bash
>MariaDB [(none)]> show variables like '%log_bin%';
>+---------------------------------+-----------------------------+
>| Variable_name | Value |
>+---------------------------------+-----------------------------+
>| log_bin | ON | # 发现已经开启 binlog
>| log_bin_basename | /var/lib/mysql/binlog |
>| log_bin_compress | OFF |
>| log_bin_compress_min_len | 256 |
>| log_bin_index | /var/lib/mysql/binlog.index |
>| log_bin_trust_function_creators | OFF |
>| sql_log_bin | ON |
>+---------------------------------+-----------------------------+
>7 rows in set (0.001 sec)
>```
- 查看 binlog 格式
>```bash
>MariaDB [(none)]> show variables like '%binlog%';
>+-----------------------------------------+----------------------+
>| Variable_name | Value |
>+-----------------------------------------+----------------------+
>| binlog_annotate_row_events | ON |
>| binlog_cache_size | 32768 |
>| binlog_checksum | CRC32 |
>| binlog_commit_wait_count | 0 |
>| binlog_commit_wait_usec | 100000 |
>| binlog_direct_non_transactional_updates | OFF |
>| binlog_file_cache_size | 16384 |
>| binlog_format | ROW |
>| binlog_optimize_thread_scheduling | ON |
>| binlog_row_image | FULL |
>| binlog_stmt_cache_size | 32768 |
>| encrypt_binlog | OFF |
>| gtid_binlog_pos | |
>| gtid_binlog_state | |
>| innodb_locks_unsafe_for_binlog | OFF |
>| max_binlog_cache_size | 18446744073709547520 |
>| max_binlog_size | 1073741824 |
>| max_binlog_stmt_cache_size | 18446744073709547520 |
>| read_binlog_speed_limit | 0 |
>| sync_binlog | 0 |
>| wsrep_forced_binlog_format | NONE |
>+-----------------------------------------+----------------------+
>```
#### 9.1.2.2. 测试数据恢复
- 为了防止干扰，我们flush刷新log日志，自此刻会结束上一个日志记录，产生一个新编号的binlog日志文件
>```bash
>flush logs;
>```
- 根据配置文件，数据存储在 /var/lib/mysql 目录下
>```bash
>[zhd@localhost ~]$ cd /var/lib/mysql
>[zhd@localhost mysql]$ ls | grep log
>aria_log.00000001
>aria_log_control
>binlog.000001
>binlog.000002 # 产生 binlog 日志文件
>binlog.index
>ib_logfile0
>ib_logfile1
>```
- 在 mysql 中查看所有 binlog 日志列表
>```bash
>MariaDB [zhdtest]> show master logs;
>+---------------+-----------+
>| Log_name | File_size |
>+---------------+-----------+
>| binlog.000001 | 369 |
>| binlog.000002 | 365 |
>+---------------+-----------+
>2 rows in set (0.000 sec)
>```
- 执行 sql 建表语句
>```sql
>DROP TABLE IF EXISTS student;
>CREATE TABLE `student` (
> `id` int(11) NOT NULL,
> `name` varchar(255) DEFAULT NULL,
> `score` int(255) DEFAULT NULL,
> `grade` varchar(255) DEFAULT NULL,
> PRIMARY KEY (`id`)
>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
>INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (1, 'lucy', 80, 'a');
>INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (2, 'lily', 90, 'a');
>INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (3, 'jack', 60, 'c');
>INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (4, 'hellen', 40, 'd');
>INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (5, 'tom', 60, 'c');
>INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (6, 'jerry', 10, 'd');
>INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (7, 'sily', 20, 'd');
>```
- 此时，假装有人删库跑路
>```sql
>truncate table student;
>```
- 查看 binlog 日志
- 因为我们刷新了日志，因此本次操作都会在最新的日志文件上
- binlog 是二进制文件，不能通过文本编辑器进行查看
- 需要通过 mysql 提供的 mysqlbinlog 来查看
>```bash
>[zhd@localhost mysql]$ sudo /usr/bin/mysqlbinlog -v binlog.000002
>[sudo] password for zhd:
>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
>/*!40019 SET @@session.max_insert_delayed_threads=0*/;
>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
>DELIMITER /*!*/;
>
>#220915 22:37:24 server id 1 end_log_pos 903 CRC32 0x6e2c5cc9 GTID 0-1-3 ddl
>/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
># at 903
>#220915 22:37:24 server id 1 end_log_pos 1029 CRC32 0xf3a82cc7 Query thread_id=9 exec_time=0 error_code=0
>SET TIMESTAMP=1663295844/*!*/;
>DROP TABLE IF EXISTS `student` /* generated by server */
>/*!*/;
># at 1029
>#220915 22:37:24 server id 1 end_log_pos 1071 CRC32 0x24050c2d GTID 0-1-4 ddl
>/*!100001 SET @@session.gtid_seq_no=4*//*!*/;
># at 1071
>#220915 22:37:24 server id 1 end_log_pos 1357 CRC32 0xce99a09e Query thread_id=9 exec_time=0 error_code=0
>SET TIMESTAMP=1663295844/*!*/;
>CREATE TABLE `student` (
> `id` int(11) NOT NULL,
> `name` varchar(255) DEFAULT NULL,
> `score` int(255) DEFAULT NULL,
> `grade` varchar(255) DEFAULT NULL,
> PRIMARY KEY (`id`)
>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
>/*!*/;
># at 1357
>#220915 22:37:24 server id 1 end_log_pos 1399 CRC32 0x2076d049 GTID 0-1-5 trans
>/*!100001 SET @@session.gtid_seq_no=5*//*!*/;
>START TRANSACTION
>/*!*/;
># at 1399
># at 1503
>#220915 22:37:24 server id 1 end_log_pos 1503 CRC32 0xcf5154a8 Annotate_rows:
>#Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (1, 'lucy', 80, 'a')
>#220915 22:37:24 server id 1 end_log_pos 1563 CRC32 0x56a9f7ee Table_map: `zhdtest`.`student` mapped to number 33
># at 1563
>#220915 22:37:24 server id 1 end_log_pos 1614 CRC32 0x7df5615a Write_rows: table id 33 flags: STMT_END_F
>
>BINLOG '
>ZOEjYxMBAAAAPAAAABsGAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw7u
>96lW
>ZOEjYxcBAAAAMwAAAE4GAAAAACEAAAAAAAEABP/wAQAAAAQAbHVjeVAAAAABAGFaYfV9
>'/*!*/;
>### INSERT INTO `zhdtest`.`student`
>### SET
>### @1=1
>### @2='lucy'
>### @3=80
>### @4='a'
># Number of rows: 1
># at 1614
>#220915 22:37:24 server id 1 end_log_pos 1645 CRC32 0xeae43f0d Xid = 27
>COMMIT/*!*/;
># at 1645
>#220915 22:37:24 server id 1 end_log_pos 1687 CRC32 0x7d171b5e GTID 0-1-6 trans
>/*!100001 SET @@session.gtid_seq_no=6*//*!*/;
>START TRANSACTION
>/*!*/;
># at 1687
># at 1791
>#220915 22:37:24 server id 1 end_log_pos 1791 CRC32 0xdabd3dc9 Annotate_rows:
>#Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (2, 'lily', 90, 'a')
>#220915 22:37:24 server id 1 end_log_pos 1851 CRC32 0x78d3812d Table_map: `zhdtest`.`student` mapped to number 33
># at 1851
>#220915 22:37:24 server id 1 end_log_pos 1902 CRC32 0x15aa6892 Write_rows: table id 33 flags: STMT_END_F
>
>BINLOG '
>ZOEjYxMBAAAAPAAAADsHAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw4t
>gdN4
>ZOEjYxcBAAAAMwAAAG4HAAAAACEAAAAAAAEABP/wAgAAAAQAbGlseVoAAAABAGGSaKoV
>'/*!*/;
>### INSERT INTO `zhdtest`.`student`
>### SET
>### @1=2
>### @2='lily'
>### @3=90
>### @4='a'
># Number of rows: 1
># at 1902
>#220915 22:37:24 server id 1 end_log_pos 1933 CRC32 0x32198f5a Xid = 28
>COMMIT/*!*/;
># at 1933
>#220915 22:37:24 server id 1 end_log_pos 1975 CRC32 0x9bfeb0fd GTID 0-1-7 trans
>/*!100001 SET @@session.gtid_seq_no=7*//*!*/;
>START TRANSACTION
>/*!*/;
># at 1975
># at 2079
>#220915 22:37:24 server id 1 end_log_pos 2079 CRC32 0x051361bb Annotate_rows:
>#Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (3, 'jack', 60, 'c')
>#220915 22:37:24 server id 1 end_log_pos 2139 CRC32 0xeab337a4 Table_map: `zhdtest`.`student` mapped to number 33
># at 2139
>#220915 22:37:24 server id 1 end_log_pos 2190 CRC32 0x496c2581 Write_rows: table id 33 flags: STMT_END_F
>
>BINLOG '
>ZOEjYxMBAAAAPAAAAFsIAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw6k
>N7Pq
>ZOEjYxcBAAAAMwAAAI4IAAAAACEAAAAAAAEABP/wAwAAAAQAamFjazwAAAABAGOBJWxJ
>'/*!*/;
>### INSERT INTO `zhdtest`.`student`
>### SET
>### @1=3
>### @2='jack'
>### @3=60
>### @4='c'
># Number of rows: 1
># at 2190
>#220915 22:37:24 server id 1 end_log_pos 2221 CRC32 0x14a72229 Xid = 29
>COMMIT/*!*/;
># at 2221
>#220915 22:37:24 server id 1 end_log_pos 2263 CRC32 0x3077f8df GTID 0-1-8 trans
>/*!100001 SET @@session.gtid_seq_no=8*//*!*/;
>START TRANSACTION
>/*!*/;
># at 2263
># at 2369
>#220915 22:37:24 server id 1 end_log_pos 2369 CRC32 0xbbd3ba13 Annotate_rows:
>#Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (4, 'hellen', 40, 'd')
>#220915 22:37:24 server id 1 end_log_pos 2429 CRC32 0x8dd0713c Table_map: `zhdtest`.`student` mapped to number 33
># at 2429
>#220915 22:37:24 server id 1 end_log_pos 2482 CRC32 0x2908e3ab Write_rows: table id 33 flags: STMT_END_F
>
>BINLOG '
>ZOEjYxMBAAAAPAAAAH0JAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw48
>cdCN
>ZOEjYxcBAAAANQAAALIJAAAAACEAAAAAAAEABP/wBAAAAAYAaGVsbGVuKAAAAAEAZKvjCCk=
>'/*!*/;
>### INSERT INTO `zhdtest`.`student`
>### SET
>### @1=4
>### @2='hellen'
>### @3=40
>### @4='d'
># Number of rows: 1
># at 2482
>#220915 22:37:24 server id 1 end_log_pos 2513 CRC32 0x8724de27 Xid = 30
>COMMIT/*!*/;
># at 2513
>#220915 22:37:24 server id 1 end_log_pos 2555 CRC32 0x01288a30 GTID 0-1-9 trans
>/*!100001 SET @@session.gtid_seq_no=9*//*!*/;
>START TRANSACTION
>/*!*/;
># at 2555
># at 2658
>#220915 22:37:24 server id 1 end_log_pos 2658 CRC32 0xf9740bc3 Annotate_rows:
>#Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (5, 'tom', 60, 'c')
>#220915 22:37:24 server id 1 end_log_pos 2718 CRC32 0x66f63303 Table_map: `zhdtest`.`student` mapped to number 33
># at 2718
>#220915 22:37:24 server id 1 end_log_pos 2768 CRC32 0xfa7cc749 Write_rows: table id 33 flags: STMT_END_F
>
>BINLOG '
>ZOEjYxMBAAAAPAAAAJ4KAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw4D
>M/Zm
>ZOEjYxcBAAAAMgAAANAKAAAAACEAAAAAAAEABP/wBQAAAAMAdG9tPAAAAAEAY0nHfPo=
>'/*!*/;
>### INSERT INTO `zhdtest`.`student`
>### SET
>### @1=5
>### @2='tom'
>### @3=60
>### @4='c'
># Number of rows: 1
># at 2768
>#220915 22:37:24 server id 1 end_log_pos 2799 CRC32 0x9609a343 Xid = 31
>COMMIT/*!*/;
># at 2799
>#220915 22:37:24 server id 1 end_log_pos 2841 CRC32 0x4d0a025d GTID 0-1-10 trans
>/*!100001 SET @@session.gtid_seq_no=10*//*!*/;
>START TRANSACTION
>/*!*/;
># at 2841
># at 2946
>#220915 22:37:24 server id 1 end_log_pos 2946 CRC32 0xd0c46d77 Annotate_rows:
>#Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (6, 'jerry', 10, 'd')
>#220915 22:37:24 server id 1 end_log_pos 3006 CRC32 0x488c45c0 Table_map: `zhdtest`.`student` mapped to number 33
># at 3006
>#220915 22:37:24 server id 1 end_log_pos 3058 CRC32 0x18aa3012 Write_rows: table id 33 flags: STMT_END_F
>
>BINLOG '
>ZOEjYxMBAAAAPAAAAL4LAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw7A
>RYxI
>ZOEjYxcBAAAANAAAAPILAAAAACEAAAAAAAEABP/wBgAAAAUAamVycnkKAAAAAQBkEjCqGA==
>'/*!*/;
>### INSERT INTO `zhdtest`.`student`
>### SET
>### @1=6
>### @2='jerry'
>### @3=10
>### @4='d'
># Number of rows: 1
># at 3058
>#220915 22:37:24 server id 1 end_log_pos 3089 CRC32 0xc8863468 Xid = 32
>COMMIT/*!*/;
># at 3089
>#220915 22:37:25 server id 1 end_log_pos 3131 CRC32 0x06302011 GTID 0-1-11 trans
>/*!100001 SET @@session.gtid_seq_no=11*//*!*/;
>START TRANSACTION
>/*!*/;
># at 3131
># at 3235
>#220915 22:37:25 server id 1 end_log_pos 3235 CRC32 0x9bcc74c2 Annotate_rows:
>#Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (7, 'sily', 20, 'd')
>#220915 22:37:25 server id 1 end_log_pos 3295 CRC32 0x89f122d2 Table_map: `zhdtest`.`student` mapped to number 33
># at 3295
>#220915 22:37:25 server id 1 end_log_pos 3346 CRC32 0x454562a2 Write_rows: table id 33 flags: STMT_END_F
>
>BINLOG '
>ZeEjYxMBAAAAPAAAAN8MAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw7S
>IvGJ
>ZeEjYxcBAAAAMwAAABINAAAAACEAAAAAAAEABP/wBwAAAAQAc2lseRQAAAABAGSiYkVF
>'/*!*/;
>### INSERT INTO `zhdtest`.`student`
>### SET
>### @1=7
>### @2='sily'
>### @3=20
>### @4='d'
># Number of rows: 1
># at 3346
>#220915 22:37:25 server id 1 end_log_pos 3377 CRC32 0x7a528eea Xid = 33
>COMMIT/*!*/;
>
>-------------------------------------------------------------------------------------------------------
># at 3377
>#220915 22:41:44 server id 1 end_log_pos 3419 CRC32 0x6095c83c GTID 0-1-12 ddl
>/*!100001 SET @@session.gtid_seq_no=12*//*!*/;
># at 3419
>#220915 22:41:44 server id 1 end_log_pos 3511 CRC32 0xe5b478ae Query thread_id=9 exec_time=0 error_code=0
>SET TIMESTAMP=1663296104/*!*/;
>truncate table student
>/*!*/;
>DELIMITER ;
># End of log file
>ROLLBACK /* added by mysqlbinlog */;
>/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
>```
- 可以发现上面日志内容非常多
- 我们想恢复到 truncate 之前 ，即恢复到 3377 之前的位置
>```bash
>[zhd@localhost mysql]$ sudo /usr/bin/mysqlbinlog -v binlog.000002 --start-position=0 --stop-position=3419 -v | mysql -uroot -p
>Enter password: Warning: option 'start-position': unsigned value 0 adjusted to 4
>```
- 查看数据恢复结果
>```bash
>MariaDB [zhdtest]> select * from student;
>+----+--------+-------+-------+
>| id | name | score | grade |
>+----+--------+-------+-------+
>| 1 | lucy | 80 | a |
>| 2 | lily | 90 | a |
>| 3 | jack | 60 | c |
>| 4 | hellen | 40 | d |
>| 5 | tom | 60 | c |
>| 6 | jerry | 10 | d |
>| 7 | sily | 20 | d |
>+----+--------+-------+-------+
>7 rows in set (0.000 sec)
>```
- 针对 binlog 日志查看，有一种更好的方法，就是在 mysql 客户端中进行查看
>```bash
>MariaDB [zhdtest]> show binlog events in 'binlog.000002';
>+---------------+------+-------------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
>+---------------+------+-------------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>| binlog.000002 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.28-MariaDB-log, Binlog ver: 4 |
>| binlog.000002 | 256 | Gtid_list | 1 | 285 | [] |
>| binlog.000002 | 285 | Binlog_checkpoint | 1 | 325 | binlog.000001 |
>| binlog.000002 | 325 | Binlog_checkpoint | 1 | 365 | binlog.000002 |
>| binlog.000002 | 365 | Gtid | 1 | 407 | GTID 0-1-1 |
>| binlog.000002 | 407 | Query | 1 | 533 | use `zhdtest`; DROP TABLE IF EXISTS `student` /* generated by server */ |
>| binlog.000002 | 533 | Gtid | 1 | 575 | GTID 0-1-2 |
>| binlog.000002 | 575 | Query | 1 | 861 | use `zhdtest`; CREATE TABLE `student` (
> `id` int(11) NOT NULL,
> `name` varchar(255) DEFAULT NULL,
> `score` int(255) DEFAULT NULL,
> `grade` varchar(255) DEFAULT NULL,
> PRIMARY KEY (`id`)
>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
>| binlog.000002 | 861 | Gtid | 1 | 903 | GTID 0-1-3 |
>| binlog.000002 | 903 | Query | 1 | 1029 | use `zhdtest`; DROP TABLE IF EXISTS `student` /* generated by server */ |
>| binlog.000002 | 1029 | Gtid | 1 | 1071 | GTID 0-1-4 |
>| binlog.000002 | 1071 | Query | 1 | 1357 | use `zhdtest`; CREATE TABLE `student` (
> `id` int(11) NOT NULL,
> `name` varchar(255) DEFAULT NULL,
> `score` int(255) DEFAULT NULL,
> `grade` varchar(255) DEFAULT NULL,
> PRIMARY KEY (`id`)
>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
>| binlog.000002 | 1357 | Gtid | 1 | 1399 | BEGIN GTID 0-1-5 |
>| binlog.000002 | 1399 | Annotate_rows | 1 | 1503 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (1, 'lucy', 80, 'a') |
>| binlog.000002 | 1503 | Table_map | 1 | 1563 | table_id: 33 (zhdtest.student) |
>| binlog.000002 | 1563 | Write_rows_v1 | 1 | 1614 | table_id: 33 flags: STMT_END_F |
>| binlog.000002 | 1614 | Xid | 1 | 1645 | COMMIT /* xid=27 */ |
>| binlog.000002 | 1645 | Gtid | 1 | 1687 | BEGIN GTID 0-1-6 |
>| binlog.000002 | 1687 | Annotate_rows | 1 | 1791 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (2, 'lily', 90, 'a') |
>| binlog.000002 | 1791 | Table_map | 1 | 1851 | table_id: 33 (zhdtest.student) |
>| binlog.000002 | 1851 | Write_rows_v1 | 1 | 1902 | table_id: 33 flags: STMT_END_F |
>| binlog.000002 | 1902 | Xid | 1 | 1933 | COMMIT /* xid=28 */ |
>| binlog.000002 | 1933 | Gtid | 1 | 1975 | BEGIN GTID 0-1-7 |
>| binlog.000002 | 1975 | Annotate_rows | 1 | 2079 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (3, 'jack', 60, 'c') |
>| binlog.000002 | 2079 | Table_map | 1 | 2139 | table_id: 33 (zhdtest.student) |
>| binlog.000002 | 2139 | Write_rows_v1 | 1 | 2190 | table_id: 33 flags: STMT_END_F |
>| binlog.000002 | 2190 | Xid | 1 | 2221 | COMMIT /* xid=29 */ |
>| binlog.000002 | 2221 | Gtid | 1 | 2263 | BEGIN GTID 0-1-8 |
>| binlog.000002 | 2263 | Annotate_rows | 1 | 2369 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (4, 'hellen', 40, 'd') |
>| binlog.000002 | 2369 | Table_map | 1 | 2429 | table_id: 33 (zhdtest.student) |
>| binlog.000002 | 2429 | Write_rows_v1 | 1 | 2482 | table_id: 33 flags: STMT_END_F |
>| binlog.000002 | 2482 | Xid | 1 | 2513 | COMMIT /* xid=30 */ |
>| binlog.000002 | 2513 | Gtid | 1 | 2555 | BEGIN GTID 0-1-9 |
>| binlog.000002 | 2555 | Annotate_rows | 1 | 2658 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (5, 'tom', 60, 'c') |
>| binlog.000002 | 2658 | Table_map | 1 | 2718 | table_id: 33 (zhdtest.student) |
>| binlog.000002 | 2718 | Write_rows_v1 | 1 | 2768 | table_id: 33 flags: STMT_END_F |
>| binlog.000002 | 2768 | Xid | 1 | 2799 | COMMIT /* xid=31 */ |
>| binlog.000002 | 2799 | Gtid | 1 | 2841 | BEGIN GTID 0-1-10 |
>| binlog.000002 | 2841 | Annotate_rows | 1 | 2946 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (6, 'jerry', 10, 'd') |
>| binlog.000002 | 2946 | Table_map | 1 | 3006 | table_id: 33 (zhdtest.student) |
>| binlog.000002 | 3006 | Write_rows_v1 | 1 | 3058 | table_id: 33 flags: STMT_END_F |
>| binlog.000002 | 3058 | Xid | 1 | 3089 | COMMIT /* xid=32 */ |
>| binlog.000002 | 3089 | Gtid | 1 | 3131 | BEGIN GTID 0-1-11 |
>| binlog.000002 | 3131 | Annotate_rows | 1 | 3235 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (7, 'sily', 20, 'd') |
>| binlog.000002 | 3235 | Table_map | 1 | 3295 | table_id: 33 (zhdtest.student) |
>| binlog.000002 | 3295 | Write_rows_v1 | 1 | 3346 | table_id: 33 flags: STMT_END_F |
>| binlog.000002 | 3346 | Xid | 1 | 3377 | COMMIT /* xid=33 */ |
>| binlog.000002 | 3377 | Gtid | 1 | 3419 | GTID 0-1-12 |
>| binlog.000002 | 3419 | Query | 1 | 3511 | use `zhdtest`; truncate table student |
>+---------------+------+-------------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>49 rows in set (0.000 sec)
>```
- 除了针对位置进行恢复外，还可以指定时间进行恢复
>```bash
># 指定时间范围
>/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-datetime="2022-06-01 11:18:00" --stop-datetime="2022-06-01 12:18:00" -v | mysql -uroot -p
>```
#### 9.1.2.3. 总结
- 数据恢复
- 指定位置范围
>```bash
> # 指定位置范围
>[zhd@localhost mysql]$ sudo /usr/bin/mysqlbinlog -v binlog.000002 --start-position=0 --stop-position=3419 -v | mysql -uroot -p
>```
- 指定时间范围
>```bash
># 指定时间范围
>[zhd@localhost mysql]$ sudo /usr/bin/mysqlbinlog -v binlog.000002 --start-datetime="2022-06-01 11:18:00" --stop-datetime="2022-06-01 12:18:00" -v | mysql -uroot -p
>```
- 日志查看
>```bash
>MariaDB [zhdtest]> show binlog events in 'binlog.000002'; -- 指定日志
>```
- binlog 的数据恢复本质
- 就是将之前执行过的sql，从开始到指定位置全部执行一遍
- 如果报错【当前表已经存在】
- 就将数据库的表手动删除，重新恢复
### 9.1.3. binlog_format (不重要)
- Statement (Statement-Based Replication, SBR)
- 每一条会修改数据的 SQL 都会记录在 binlog 中
- Row(Row-Based Replication,RBR)
- 不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
- Mixed(Mixed-Based Replication,MBR)
- Statement 和 Row 的混合体，Mysql 8 当前默认的选项，5.7中默认row。
- Statement 与 Row
- Statement 模式只记录执行的 SQL ，不需要记录每一行数据的变化。因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。
- 由于 Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。
- 所以使用 Statement 格式会出现一些数据一致性问题。
- Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要**记录某一条记录被修改成什么样子了。**
- 不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。
### 9.1.4. 日志格式
- 日志文件格式
- binlog 文件以一个值为 0Xfe62696e 开头的 magic number，这个 magic number 对应 0xfebin
- binlog 由一系列的 binlog event 构成，每个 binlog event 包含 header 和 data 两部分
- header 部分提供的是 event 的公共的类型信息，
- 包括 event 的创建时间、服务器等
- data 部分提供的是针对 event 的具体信息，如具体数据的修改
- 常见的事件类型
- FORMAT_DESCRIPTION_EVENT : 该部分位于整个文件的头部，每个 binlog 文件必定有唯一一个该 event
- WRITE_ROW_EVENT : 插入操作
- DELETE_ROW_EVENT : 删除操作
- UPDATE_ROW_EVENT : 更新操作，记载的是一条记录的完成变化情况，即从前量变为后量的过程
- ROTATE_EVENT : Binlog 结束时的事件，用于说明下一个 binlog 文件
>```bash
>MariaDB [zhdtest]> show binlog events in 'binlog.000004';
>+---------------+-----+-------------------+-----------+-------------+------------------------------------------------+
>| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
>+---------------+-----+-------------------+-----------+-------------+------------------------------------------------+
>| binlog.000004 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.28-MariaDB-log, Binlog ver: 4 |
>| binlog.000004 | 256 | Gtid_list | 1 | 299 | [0-1-11] |
>| binlog.000004 | 299 | Binlog_checkpoint | 1 | 339 | binlog.000004 |
>| binlog.000004 | 339 | Rotate | 1 | 383 | binlog.000005;pos=4 |
>+---------------+-----+-------------------+-----------+-------------+------------------------------------------------+
>4 rows in set (0.000 sec)
>```
- 一个 event 的结构如下
- binlog.index 为一个文本文件，是一个索引文件，其中记录了当前 binlog 文件列表
>```bash
>[zhd@localhost mysql]$ sudo cat binlog.index
>[sudo] password for zhd:
>./binlog.000001
>./binlog.000002
>./binlog.000003
>./binlog.000004
>./binlog.000005
>```
### 9.1.5. binlog 刷盘
- binlog 文件并不是每次写的时候都同步到磁盘 (内存中先更新)
- 因此在数据库所在操作系统宕机时，总有一部分数据没有写入 binlog 文件。这给恢复和复制带来问题
- 参数 sync_binlog=[N] 表示每写多少次就同步到磁盘
- 如果将 N 设为 1，即 sync_binlog=1 表示采用同步写磁盘的方式来写二进制日志
- 这时写操作不使用操作系统的缓冲来写二进制日志
- I/O 较大
- 该值默认为 0，采用操作系统机制进行缓冲数据同步
- 内存中有一个缓冲区，缓冲区存储数据到一定大小，会自动刷到磁盘上
### 9.1.6. binlog 实现主从同步
- 数据库单节点部署的问题
- 服务器宕机：造成业务停顿，影响客户体验
- 服务器损坏：造成数据丢失，不能及时备份，造成损失
- 读写操作都在一台服务器，在并发量大的情况下性能存在瓶颈
- 可以使用 mysql binlog 搭建一个 **一主多从** 的 mysql 集群服务
- 这样的服务可以帮助我们异地备份数据
- 进行读写分离，提高系统的可用性
- 可以**所有的写操作，由主节点服务器完成**
- **所有的读操作，由从节点服务器完成**
#### 9.1.6.1. 主从复制工作原理
- Master 数据库只要发生变化，立马记录到 binlog 日志文件中
- 因此 Master 需要开启 binlog 日志
- Slave 数据库启动一个 I/O 线程连接 Master 数据库，请求 Master 变化的 binlog 文件
- Slave I/O 获取到的 binlog 日志，保存到自己的 Relay Log(中继日志) 日志文件中
- Slave 有一个 SQL thread 定时检查 Relay log 是否变化，如果变化则更新数据
#### 9.1.6.2. 配置主从同步
- 实现主从同步，Mysql 的版本最好一致
- 本节采用另一台电脑上的真正的 mysql 8 作为演示
- node01 作为主服务器，node02 作为从服务器
>```bash
>[hadoop@node02 mysql]$ mysql --version
>mysql Ver 8.0.29 for Linux on x86_64 (MySQL Community Server - GPL)
>```
1. mysql 配置文件
- node01 (Master) 配置
- 配置文件设置，开启 bin_log（已经开启的可以忽略）且需要配置一个server-id
>```conf
>[mysqld]
>port=3306
>server-id=1
>user=hadoop
> socket=/zhd/data/mysqlDatas/socket/mysql.sock
># 设置mysql的安装目录
>basedir=/zhd/install/mysql-8.0.29 #你自己的安装路径
># 设置mysql数据库的数据的存放目录
>datadir=/zhd/data/mysqlDatas/mysql/ #你自己创建的数据库文件存放路径
>log-bin=/zhd/data/mysqlDatas/mysql/master-bin # mater-bin 文件
>```
- node02 (slave) 配置
- 需要开启中继日志，即 relay-log
>```conf
>[mysqld]
>server-id=2
>relay-log=mysql-relay-bin
> # 以下设置的意思是，在复制 binlog 日志时，忽略那些表的 binlog
> # 系统自带的数据库不需要进行同步
>replicate-wild-ignore-table=mysql.%
>replicate-wild-ignore-table=sys.%
>replicate-wild-ignore-table=information_schema.%
>replicate-wild-ignore-table=performance_schema.%
>```
2. 重启两个 mysql 服务
3. Master 数据库中创建用户并授权
- 进入 mster 数据库，为 master 创建用户
>```sql
>CREATE USER 'hadoop'@'node02.zhd.org' IDENTIFIED BY 'Zh******2';
>```
- 赋予该用户数据库复制权限
>```sql
>grant replication slave on *.* to 'hadoop'@'node02.zhd.org'; -- 这里的 *.* 表示任意的数据库中的任意表，都允许复制
>FLUSH PRIVILEGES;
>```
4. 查看 master 服务器的状态
- 这里的 position 表示从 binlog 的哪个位置开始同步，之前的就不同步了
>```bash
>mysql> show master status;
>+-------------------+----------+--------------+------------------+-------------------+
>| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
>+-------------------+----------+--------------+------------------+-------------------+
>| master-bin.000001 | 1071 | | | |
>+-------------------+----------+--------------+------------------+-------------------+
>1 row in set (0.00 sec)
>```
5. 配置从库
>```sql
>CHANGE MASTER TO
>MASTER_HOST = 'node01.zhd.org',
>MASTER_USER = 'hadoop',
>MASTER_PASSWORD = 'Zh******2',
>MASTER_PORT = 3306,
>MASTER_LOG_FILE='master-bin.000001',
>MASTER_LOG_POS=1071,
>MASTER_HEARTBEAT_PERIOD = 10000;
>
>-- MASTER_LOG_FILE与主库File 保持一致, 即上面查询的结果
>-- MASTER_LOG_POS=120 , #与主库Position 保持一致
>```
6. 启动从库 slave 进程
>```bash
>mysql> start slave;
>Query OK, 0 rows affected, 1 warning (0.02 sec)
>```
7. 查看从库状态
>```bash
>mysql> show slave status\G;
>*************************** 1. row ***************************
> Slave_IO_State: Connecting to source
> Master_Host: node01.zhd.org
> Master_User: hadoop
> Master_Port: 3306
> Connect_Retry: 60
> Master_Log_File: master-bin.000001
> Read_Master_Log_Pos: 1999
> Relay_Log_File: mysql-relay-bin.000001
> Relay_Log_Pos: 4
> Relay_Master_Log_File: master-bin.000001
> Slave_IO_Running: Connecting
> Slave_SQL_Running: Yes
> Replicate_Do_DB:
> Replicate_Ignore_DB:
> Replicate_Do_Table:
> Replicate_Ignore_Table:
> Replicate_Wild_Do_Table:
> Replicate_Wild_Ignore_Table: mysql.%,sys.%,information_schema.%,performance_schema.%
> Last_Errno: 0
> Last_Error:
> Skip_Counter: 0
> Exec_Master_Log_Pos: 1999
> Relay_Log_Space: 157
> Until_Condition: None
> Until_Log_File:
> Until_Log_Pos: 0
> Master_SSL_Allowed: No
> Master_SSL_CA_File:
> Master_SSL_CA_Path:
> Master_SSL_Cert:
> Master_SSL_Cipher:
> Master_SSL_Key:
> Seconds_Behind_Master: NULL
>Master_SSL_Verify_Server_Cert: No
> Last_IO_Errno: 2061
> Last_IO_Error: error connecting to master 'hadoop@node01.zhd.org:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
> Last_SQL_Errno: 0
> Last_SQL_Error:
> Replicate_Ignore_Server_Ids:
> Master_Server_Id: 0
> Master_UUID:
> Master_Info_File: mysql.slave_master_info
> SQL_Delay: 0
> SQL_Remaining_Delay: NULL
> Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
> Master_Retry_Count: 86400
> Master_Bind:
> Last_IO_Error_Timestamp: 220918 10:35:39
> Last_SQL_Error_Timestamp:
> Master_SSL_Crl:
> Master_SSL_Crlpath:
> Retrieved_Gtid_Set:
> Executed_Gtid_Set:
> Auto_Position: 0
> Replicate_Rewrite_DB:
> Channel_Name:
> Master_TLS_Version:
> Master_public_key_path:
> Get_master_public_key: 0
> Network_Namespace:
>1 row in set, 1 warning (0.00 sec)
>```
- 存在的问题
- Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
- 原因分析
- 主库的密码插件 plugin是caching_sha2_password 导致连接不上，修改为mysql_native_password即可解决。
- 问题解决
>```bash
># 在主库进行修改
>mysql> ALTER USER 'hadoop'@'node02.zhd.org' IDENTIFIED WITH mysql_native_password BY 'Zhonghuadong2';
>Query OK, 0 rows affected (0.00 sec)
>```
- 重启从库的 slave 服务，又出现新的问题
- The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
- 原因分析
- master 和 slave 的 server uuid 完全一样
- 使用了两台虚拟机，一主一从，从库的mysql是直接克隆的。
- 在mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，但是查看到直接克隆data文件夹后server_uuid是相同的。
- 问题解决
- 找到data文件夹下的auto.cnf文件，修改里面的server_uuid值，保证各个db的server_uuid不一样，重启db即可。
- 查看 slave 的 uuid 值
>```bash
>[hadoop@node02 etc]$ cd /zhd/data/mysqlDatas
>[hadoop@node02 mysqlDatas]$ cd mysql
>[hadoop@node02 mysql]$ cat auto.cnf
>[auto]
>server-uuid=f2140bea-0458-11ed-83e3-000c293f8c8e
>```
- 查看 master 的 uuid 值
>```bash
>[hadoop@node01 mysql]$ cat auto.cnf
>[auto]
>server-uuid=f2140bea-0458-11ed-83e3-000c293f8c8e
>```
- 修改 slave 库的 uuid
>```bash
>mysql> select uuid();
>+--------------------------------------+
>| uuid() |
>+--------------------------------------+
>| 6815e635-3762-11ed-984c-000c293f8c8e |
>+--------------------------------------+
>1 row in set (0.00 sec)
>```
- 把新生成的 uuid 写入 auto.cnf
>```bash
>vim auto.cnf
>```
- slave 重启数据库服务
>```bash
>[hadoop@node02 mysql]$ mysql.server restart
>Shutting down MySQL.. SUCCESS!
>Starting MySQL. SUCCESS!
>```
- 再次连接发现成功
>```bash
>mysql> start slave;
>Query OK, 0 rows affected, 2 warnings (0.00 sec)
>
>mysql> show slave status\G;
>*************************** 1. row ***************************
> Slave_IO_State: Waiting for source to send event
> Master_Host: node01.zhd.org
> Master_User: hadoop
> Master_Port: 3306
> Connect_Retry: 60
> Master_Log_File: master-bin.000001
> Read_Master_Log_Pos: 2301
> Relay_Log_File: mysql-relay-bin.000004
> Relay_Log_Pos: 629
> Relay_Master_Log_File: master-bin.000001
> Slave_IO_Running: Yes
> Slave_SQL_Running: Yes
> Replicate_Do_DB:
> Replicate_Ignore_DB:
> Replicate_Do_Table:
> Replicate_Ignore_Table:
> Replicate_Wild_Do_Table:
> Replicate_Wild_Ignore_Table: mysql.%,sys.%,information_schema.%,performance_schema.%
> Last_Errno: 0
> Last_Error:
> Skip_Counter: 0
> Exec_Master_Log_Pos: 2301
> Relay_Log_Space: 1199
> Until_Condition: None
> Until_Log_File:
> Until_Log_Pos: 0
> Master_SSL_Allowed: No
> Master_SSL_CA_File:
> Master_SSL_CA_Path:
> Master_SSL_Cert:
> Master_SSL_Cipher:
> Master_SSL_Key:
> Seconds_Behind_Master: 0
>Master_SSL_Verify_Server_Cert: No
> Last_IO_Errno: 0
> Last_IO_Error:
> Last_SQL_Errno: 0
> Last_SQL_Error:
> Replicate_Ignore_Server_Ids:
> Master_Server_Id: 1
> Master_UUID: f2140bea-0458-11ed-83e3-000c293f8c8e
> Master_Info_File: mysql.slave_master_info
> SQL_Delay: 0
> SQL_Remaining_Delay: NULL
> Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
> Master_Retry_Count: 86400
> Master_Bind:
> Last_IO_Error_Timestamp:
> Last_SQL_Error_Timestamp:
> Master_SSL_Crl:
> Master_SSL_Crlpath:
> Retrieved_Gtid_Set:
> Executed_Gtid_Set:
> Auto_Position: 0
> Replicate_Rewrite_DB:
> Channel_Name:
> Master_TLS_Version:
> Master_public_key_path:
> Get_master_public_key: 0
> Network_Namespace:
>1 row in set, 1 warning (0.00 sec)
>```
- 测试
- 在主库执行以下语句
>```bash
>mysql> drop database if exists master_test;
>Query OK, 0 rows affected (0.00 sec)
>
>mysql> create database if not exists master_test;
>Query OK, 1 row affected (0.01 sec)
>```
- 从库查询数据库
>```bash
>mysql> start slave;
>Query OK, 0 rows affected, 1 warning (0.01 sec)
>
>mysql> show databases;
>+--------------------+
>| Database |
>+--------------------+
>| information_schema |
>| master_test |
>| mysql |
>| performance_schema |
>| sys |
>+--------------------+
>5 rows in set (0.00 sec)
>```
- 主从不一致的相关资料参考 https://blog.csdn.net/yanzer/article/details/125859359
### 9.1.7. 分布式事务与两阶段提交
#### 9.1.7.1. 分布式事务
- 分布式事务
- 指的是允许多个独立的事务资源参与到一个全局的事务中。(**分布式事务通常会跨数据库**)
- 事务资源通常是关系型数据库系统，但也可以是其他类型的资源。
- 全局事务要求在其中的**所有参与的事务**要么都提交，要么都回滚，
- 这对于事务原有的ACID要求又有了提高。
- 分布式事务案例
- 当你发了工资之后，把你的当月工资¥10000从银行卡 (**银行系统**) 转到了支付宝 (**阿里系统**) 中。如果在银行卡账户扣除 ¥10000之后，支付宝系统挂掉了，支付宝的账户并没有增加 ¥10000，这时候就出现了数据不一致的情况。（当然这种情况肯定不会出现了，只是一个假设）
- 在很多交易系统中（比如我们熟知的电商）都能找到上述情况的影子：
- （1）在下单的时候，需要在订单表中插入一条数据，然后把库存减去一。
- （2）在搜索的时候如果点击了广告，需要先记录该点击事件，然后通知商家系统扣除广告费。
- 在这种情况下，一定需要使用分布式事务来保证数据的安全。如果发生的操作不能全部提交或回滚，那么任何一个节点出现问题都会导致严重的结果。
可见与本地事务不同的是，分布式事务需要多一次的PREPARE操作，待收到所有节点的同意信息后，再进行COMMIT或是ROLLBACK操作。
#### 9.1.7.2. XA 事务
- XA（eXtended Architecture）是指由 X/Open 组织提出的分布式交易处理的规范
- XA 是一个分布式事务协议
- 分布式事务也称为 XA 事务。
- XA 协议主要定义了 **事务管理器 TM（Transaction Manager，协调者）和资源管理器 RM（Resource Manager，参与者）** 之间的接口
- **事务管理器作为全局的调度者，负责各个本地资源的提交和回滚**
- **XA 事务允许不同数据库的分布式事务，只要参与在全局事务中的每个结点都支持 XA 事务**
- **XA 事务的缺点是性能不好，且无法满足高并发场景**
- 尽量避免XA 事务，如可以将数据写入本地，用高性能的消息系统分发数据，或使用数据库复制等技术。
- **只有在其他办法都无法实现业务需求，且性能不是瓶颈时才使用XA。**
#### 9.1.7.3. 两阶段提交
- XA 事务是基于两阶段提交 (Two-phaseCommit) 实现的
- 一条普通语句的执行过程
- 连接器
- 用于和客户端建立连接，管理连接。检查连接中的用户名密码是否正确吗，以及是否对表有操作权限。
- 分析器
- 只要是进行词法、语法分析，区分sql关键词与非关键词，生成一颗语法树。如果生成语法树失败，则证明你的sql有语法错误。之后对语法树进行一些剪枝操作，去除一些无用的条件等
- 优化器
- 生成sql的执行计划，你可以使用explain来查看执行计划。会基于某些规则来选择走的索引项，在取样的时候可能会存在误差，可是使用force index来强制走某条索引。
- 执行器
- 依据执行计划，调用存储引擎的接口，来实现对数据的读写操作。
- 两阶段提交过程
- InnoDB在写redo log时，并不是一次性写完的，而有两个阶段，**Prepare与Commit阶段**，这就是"两阶段提交"的含义
#### 9.1.7.4. 两阶段为什么要写日志
- prepare 阶段
- 写 redo-log 并将其标记为prepare状态。紧接着写binlog
- commit阶段
- 写 redo-log 并将其标记为commit状态
- **为什么要写 binlog 和 redolog？**
- binlog (Mysql server 级别) 主要是为了主从同步，防止由于单节点 Mysql 宕机而无法提供服务
- 也用于数据备份，但可能丢失部分数据。(因为[binlog并不是每次都刷盘](#915-binlog-刷盘))
- **redo log : 在于崩溃恢复**
- MySQL为了提升性能，引入了 BufferPool 缓冲池。查询数据时，先从 BufferPool 中查询，查询不到则从磁盘加载在BufferPool。每次对数据的更新，也不总是实时刷新到磁盘，而是先同步到 BufferPool 中，涉及到的数据页就会变成脏页。同时会启动后台线程，异步地将脏页刷新到磁盘中，来完成 BufferPool 与磁盘的数据同步。
- 如果在某个时间，MySQL突然崩溃，则内存中的 BufferPool 就会丢失，剩余未同步的数据就会直接消失。虽然在更新BufferPool 后，也写入了 binlog 中，但 binlog 并不具备 crash-safe 的能力。因为崩溃可能发生在写 binlog 后，刷脏前。在主从同步的情况下，**从节点**会拿到多出来的一条binlog,导致主从数据不一致。
- 所以 server 层的 binlog 是不支持崩溃恢复的，只是支持误删数据恢复。**InnoDB考虑到这一点，自己实现了redo log**
- **为什么要写两次 redo log，写一次不行吗？**
- redo log与binlog都写一次的话，也就是存在以下两种情况
- **先写 binlog，再写 redo log**
- 当前事务提交后，写入 binlog 成功，之后主节点崩溃。在**主节点重启**后，由于没有写入redo log，因此不会恢复该条数据。而**从节点**依据 binlog 在本地回放后，会相对于主节点多出来一条数据，从而产生主从不一致
- **先写 redo log，再写 binlog**
- 当前事务提交后，写入 redo log 成功，之后主节点崩溃。在主节点重启后，主节点利用redo log进行恢复，就会相对于从节点多出来一条数据，造成主从数据不一致。
- 因此，只写一次 redo log 与 binlog，无法保证这两种日志在事务提交后的一致性。也就是无法保证主节点崩溃恢复与从节点本地回放数据的一致性
- **在两阶段提交的情况下，是怎么实现崩溃恢复的呢？**
- 在写入redo log时，**会顺便记录XID，即当前事务id**。在写入binlog时，也会写入XID。
- 如果在写入redo log之前崩溃，那么此时redo log与binlog中都没有，是一致的情况，崩溃也无所谓
- 如果在写入redo log prepare 阶段后立马崩溃
- 之后会在崩恢复时，由于 redo log 没有被标记为 commit。
- 于是拿着 redo log 中的 XID 去 binlog 中查找，此时肯定是找不到的，那么执行回滚操作
- 如果在写入 binlog 后立马崩溃
- 在恢复时，由 redo log 中的 XID 可以找到对应的 binlog，这个时候直接提交即可。
- 总的来说，在崩溃恢复后，只要 redo log 不是处于commit阶段，那么就拿着redo log中的XID去binlog中寻找，找得到就提交，否则就回滚。在这样的机制下，两阶段提交能在崩溃恢复时，能够对提交中断的事务进行补偿，**来确保 redo log 与 binlog 的数据一致性**。
#### Mysql XA 语法
- xa事务的语法（六个命令 按顺序来）
- xa start, xa end，开始和结束，两个中包裹一些分布式原子化操作，就像本质事务transaction一样
- xa prepare
- xa commit | xa rollback
- xa recover
- 注意: mysql XA 和本地事务是互斥的，当你开启了 XA 则不能执行本地事务
## 9.2. 其他日志
### 9.2.1. 通用日志查询
- Mysql 通用查询日志
- 记录建立客户端连接和执行的所有 DDL 和 DML 语句
- 不管是成功语句还是执行所有错误的语句
- **默认情况下是不开启的**
- 它是一个文本文件
>```bash
>MariaDB [zhdtest]> show variables like '%general_log%';
>+------------------+---------------+
>| Variable_name | Value |
>+------------------+---------------+
>| general_log | OFF |
>| general_log_file | localhost.log | # 通用查询日志文件名称为 localhost.log
>+------------------+---------------+
>2 rows in set (0.015 sec)
>```
- 开启通用查询
>```sql
>-- 在全局模式下，开启通用查询日志，1表示开启，0表示关闭
>SET global general_log=1;
>```
- 开启后，发现在 mysql 数据存储文件夹下多了一个 localhost.log 文件 (文件名和系统别名有关系)
>```bash
>[zhd@localhost mysql]$ ls
>aria_log.00000001 binlog.000003 binlog.index ib_logfile1 multi-master.info performance_schema
>aria_log_control binlog.000004 ib_buffer_pool ibtmp1 mysql zhdtest
>binlog.000001 binlog.000005 ibdata1 localhost.localdomain.err mysql.sock
>binlog.000002 binlog.000006 ib_logfile0 localhost.pid mysql_upgrade_info
>
>[zhd@localhost mysql]$ ls
>aria_log.00000001 binlog.000003 binlog.index ib_logfile1 localhost.pid mysql_upgrade_info
>aria_log_control binlog.000004 ib_buffer_pool ibtmp1 multi-master.info performance_schema
>binlog.000001 binlog.000005 ibdata1 localhost.localdomain.err mysql zhdtest
>binlog.000002 binlog.000006 ib_logfile0 #localhost.log# mysql.sock
>```
- 查看日志文件内容
>```bash
>[zhd@localhost mysql]$ sudo cat localhost.log
>/usr/libexec/mysqld, Version: 10.3.28-MariaDB-log (MariaDB Server). started with:
>Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
>Time Id Command Argument
>220916 4:33:47 9 Query select * from student
>220916 4:33:50 9 Query select * from stdent
>220916 4:33:59 9 Query show status # 其记录了所有的 DDL 和 DML 操作
>```
### 9.2.2. 慢查询日志
- 慢查询日志 Mysql 8 默认是开启的, mariadb 默认不开启
- 也是文本文件
>```bash
>MariaDB [zhdtest]> show variables like '%slow_query_log%';
>+---------------------+--------------------+
>| Variable_name | Value |
>+---------------------+--------------------+
>| slow_query_log | OFF |
>| slow_query_log_file | localhost-slow.log |
>+---------------------+--------------------+
>2 rows in set (0.001 sec)
>```
- SQL 语句多慢算慢查询？
- 由变量 long_query_time 进行决定，如果查询时间超过 long_query_time，则认为是慢查询
>```bash
>MariaDB [zhdtest]> show variables like 'long_query_time';
>+-----------------+-----------+
>| Variable_name | Value |
>+-----------------+-----------+
>| long_query_time | 10.000000 |
>+-----------------+-----------+
>1 row in set (0.001 sec)
>```
### 9.2.3. 错误日志
- 错误日志（Error Log）
- 主要记录 MySQL 服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等。
- 一旦发生mysql服务无法启动、程序崩溃一定要记得去查询错误日志：
>```bash
>MariaDB [zhdtest]> show variables like '%log_error%';
>+---------------+------------------------------+
>| Variable_name | Value |
>+---------------+------------------------------+
>| log_error | /var/log/mariadb/mariadb.log | # 位置和配置文件中的参数log-error有关系
>+---------------+------------------------------+
>1 row in set (0.001 sec)
>```
- 具体日志
>```txt
>[zhd@localhost ~]$ sudo cat /var/log/mariadb/mariadb.log
>220919 02:59:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
>2022-09-19 2:59:11 0 [Note] /usr/libexec/mysqld (mysqld 10.3.28-MariaDB-log) starting as process 2681 ...
>...
>...
>2022-09-19 2:59:11 0 [Note] InnoDB: Buffer pool(s) load completed at 220919 2:59:11
>2022-09-19 2:59:12 0 [Note] Plugin 'FEEDBACK' is disabled.
>2022-09-19 2:59:12 0 [Warning] mysqld: GSSAPI plugin : default principal 'mariadb/localhost@' not found in keytab
>2022-09-19 2:59:12 0 [ERROR] mysqld: Server GSSAPI error (major 851968, minor 2529639093) : gss_acquire_cred failed -Unspecified GSS failure. Minor code may provide more information. Keytab FILE:/etc/krb5.keytab is nonexistent or empty.
>2022-09-19 2:59:12 0 [ERROR] Plugin 'gssapi' init function returned error.
>2022-09-19 2:59:12 0 [Note] Recovering after a crash using binlog
>2022-09-19 2:59:12 0 [Note] Starting crash recovery...
>...
>2022-09-19 2:59:12 0 [Note] Added new Master_info '' to hash table
>2022-09-19 2:59:12 0 [Note] /usr/libexec/mysqld: ready for connections.
>Version: '10.3.28-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
>```
## 9.3. redo log 日志
前面讲述的 binlog日志、通用日志、错误日志、慢查询日志，都是 mysql server 级别的，接下来的 redo log 和 undo log 都是 InnoDB 级别的日志
### 9.3.1. redo log VS binlog
- redo log
- 主要是为了防止因系统崩溃而导致数据丢失。功能和 binlog 部分重叠。(毕竟不是一个团队设计的)
- binglog 只是记录流水账无法体现事务
- redo log 和 undo log 可以记录事务
- binlog 更像是逻辑日志，其记录的是 **类 SQL 语句**
- 而 **redo log 是物理日志**，记录的是哪个表空间的哪个数据页的数据发生了改变。
### 9.3.2. 系统崩溃数据丢失
- **为什么发生系统崩溃时，mysql 会发生数据丢失？**
- mysql 数据发生变化，首先是发生在 [buffer pool](#4-缓冲池buffer-pool) 中的
- 这是一个 InnoDB 级别的架构，详见 [InnoDB 架构图](#321-innodb-架构)，增删改查首先在 buffer pool 中进行
- 当数据被修改，会在 buffer pool 中形成 [脏页](#441-脏页), 存储在 [flush 链表](#44-flush链表) 中
- 如果脏页没有被刷新到磁盘上，则这个 flush 链表中的脏页的数据将会全部丢失
- **解决系统数据丢失的思路**
- 1. 每次提交事务之前，必须将所有和当前事务相关的【buffer pool 的脏页】刷入磁盘
- 问题在于这样做效率太低，可能影响主线程效率，产生用户等待
- 因为刷盘是 I/O 操作，事务的读写操作也不是顺序读写 (update 是随机 I/O，可能会修改一棵 B+ 树中好多页的好多个数据，修改 B+ 树是这棵树可能会被锁住)
- 2. redo log 的做法
- 不直接修改数据，而是**记录当前事务中数据的变化**，写成日志文件。
- 日志记录是顺序写，性能比较高
- 执行事务中，每执行一条语句，就可能有若干redo日志，并按产生的顺序写入磁盘
- redo日志占用的空间非常小，当redo log空间满了之后又会从头开始以循环的方式进行覆盖式的写入
- 后台线程读取 redo log 将数据刷入磁盘
- 当 app 访问 mysql server 时，需要尽可能快地获得数据返回
- 因此直接在 buffer pool 中修改数据后，不等数据刷入磁盘，即可返回给 app
- 数据从 buffer pool 刷入磁盘由后台独立线程进行处理
- 方案一
- 采用线程不停找 flush 链表中的脏页，将脏页刷新到磁盘上
- 解决了 app 的性能问题
- 但是系统万一崩溃，则会导致数据丢失
- 方案二
- 每次有脏页产生，记录一条 redo log 到 log buffer 中
- 后台线程把 redo log 从 log buffer 刷到磁盘中的 redo log 文件中
- 然后将 redo log 文件中的数据刷新到磁盘表空间中
- 万一系统发生崩溃，只需要将 redo log 中的数据重新刷到磁盘上既可以恢复数据
- app 数据读取也是直接从 buffer pool 的内存中读取，因此也不存在和磁盘中数据不一致的问题。
- 因为如果磁盘的数据需要加载到缓冲页，此时 redo log 早就把数据刷入过磁盘了
### 9.3.3. redo log 格式
- redo log 包含以下几部分内容
- type: 该日志的类型。
- 在5.7版本中，大概有53种不同类型的redo log，占用一个字节
- space id: 表空间 id
- 因为 redo log 是物理日志，直接记录的是哪个表空间的数据页的数据发生了什么变化
- page number: 页号
- data: 日志数据
### 9.3.4. MTR (Mini-Transaction)
- 当我们向 B+ 树中插入一条记录时
- 需要定位这条数据将要插入的位置，即将要插入的【数据页】
- 因为插入的位置不太，需要处理的情况也不同
- **1. 当待插入的页，拥有【充足的剩余空间】**
- 此时足以容纳这条数据，直接插入即可
- 此时记录的是一条【MLOG_COMP_REC_INSERT类型】的 redolog
- 该种情况称为 **乐观插入**
- **2. 当待插入的页【剩余空间不足】**
- 待插入页不足以容纳该条数据，则需要进行【页分裂】
- 必须新建一个页面，将原始页面的数据拷贝一部分到新页面，然后再插入数据
- 这其中对应了好几个操作，必须记录多条 redo log
- 申请新的数据页
- 修改段、区的信息
- 修改各种链表的信息等
- 可能需要记录二三十条 redolog
- 但是本次操作必须是一个【原子性操作】
- 因此在记录的过程中，要么全部记录，要么全部失败
- 这种情况称之为一个 **Mini-Transaction (最小事务)**
- 这二三十条 relog 组成了 Mini-Transaction
在innodb执行任务时，有很多操作，必须具有原子性，我们把这一类操作称之为 Mini Transaction
#### 9.3.4.1. MTR 按组写入
- 对于一个【MTR】操作，必须是原子性的
- innodb 采用组的形式来记录 redo log (为了保证原子性)
- 在恢复时，要么一组的日志全部恢复，要么全部不恢复
- innodb 采用一条类型为 **MLO_MULTI_REC_END** 类型的 redolog 作为组的结尾标志
- 在系统崩溃恢复时只有解析到该项日志，才认为解析到了一组完成的 redo log
- **否则直接放弃前面解析的日志**
#### 9.3.4.2. 单条 redo log 的标识方法
- 有些操作只会产生一条 redo log
- innodb 是通过【类型标识】的**第一个字符**判断，这个日志是单一日志还是组日志。具体如下图所示
#### 9.3.4.3. 事务、sql、MTR、redolog之间的关系
- 一个事务包含一条或多条 sql
- 一条 sql 包含一个或者多个 MTR
- 一个 MTR 包含一个或多个 redolog
### 9.3.5. log buffer (理解即可)
- 任何可能产生批量 I/O 的操作，一般情况下都会设计【缓冲层】
- innodb 使用一个变量 buf_free 来标记吓一跳 redo log 的插入位置 (标记偏移量)
- log buffer 会在合适的时机进行刷盘（以下两个只要一个满足即可）
- log buffer 空间不足
- logbuffer的容量由 innodb_log_buffer_size 指定，当写入 log buffer 的日志大于容量的50%，就会进行刷盘。
- 提交事务时
- 如果需要实现崩溃恢复，保证数据的持久性，提交事务时必须提交redo log (**因为 redo log 刷盘比原始数据刷盘要更加轻量级**)
- 当然你也可以为了效率不去提交，可以通过修改配置文件设置该项目。
- 后台有独立线程大约每隔一秒会刷新盘一次。
- 正常关闭服务器。
- 做checkpoint时
- 做这么多工作，其实就是为了将重量级的操作，用轻量级操作进行替代
### 9.3.6. checkpoint
- redo log 不像 binlog
- binlog 如果一个文件写满了，则会开启一个新的文件
- 而 redolog，innodb 只给它分配了一个固定的空间，是**需要循环使用的，容量有限**
- redo log 的作用
- 仅仅是为了在崩溃时恢复脏页使用
- 如果脏页已经刷到了磁盘上，则其对应的 redolog 也就没有用了
- 可以被重复利用了
- checkpoint 的作用
- **标记哪些旧的 redo log 可以被覆盖**
- 判断 redo log 占用的磁盘是否可以重新利用的标志就是看对应的脏页有没有被刷新到磁盘
- 需要知道以下概念
- lsn
- flush_to_disk_lsn
#### 9.3.6.1. lsn
- lsn (log sequence number)
- 是一个全局变量
- innodb 使用 lsn 来记录当前总计写入 logbuffer 的日志量
- lsn 初始值不是 0 ，而是 8704 (原因未知)
- 系统在记录lsn时是按照【偏移量】不断累加的。
- 每一组redo log都有一个**唯一**的lsn值和他对应
- lsn的值越小说明redo log产生的越早。
- 可以理解为lsn是redo log的年龄。
#### 9.3.6.2. flush_to_disk_lsn
- flush_to_disk_lsn
- 全局变量，**表示已经刷入磁盘的 redo log 的量**
- 它小于等于 lsn
- 例如
- 1、将redo log写入log buffer，lsn增加，假如：8704+1024 = 9728，此时flush_to_disk_lsn不变。
- 2、刷如512字节到磁盘，此时flush_to_disk_lsn=8704+512=9256。
- 通过两者对比，就能知道还有多少 redo log 未刷到磁盘上
- 如果两者数据相同，说明已经全部刷盘。
#### 9.3.6.3. flush 链中的 lsn
- 要保证数据不丢失，需要将 buffer pool 中的脏页不断进行刷盘
- 刷盘工作比较损耗性能，需要独立现成在后台静默操作
- [flush 链表](#44-flush链表)
- 当第一次修改某个已经加载到 buffer pool 中的页面时，他会变成【脏页】，会把它放置在 flush 链表的头部
- flush 链表是按照第一次修改的时间排序
- 再一次修改缓冲页时，会在【缓冲页对应的控制块】中记录两个属性
- oldest_modification
- **第一次** 修改缓冲页时，就将【修改该页面的第一组 redo log 的 lsn 值】记录在对应的控制块
- 因为一个 MTR 包含了一组 Redo log
- newest_modeification
- **每一次** 修改缓冲页时，就将【修改该页面的最后组 redo log 的 lsn 值】记录在对应的控制块
- 既然 flush 链表是按照修改日期排序的，也就是说，oldest_modification的值也是有序的。（增大的）
#### 9.3.6.4. checkpoint 过程
- 执行 chekpoint 的过程
- **第一步**：计算当前 redo log 文件中**可以被覆盖**的 redo log 日志对应的 lsn 值是多少。
- 1. flush 链是按照第一次修改的时间进行排序的
- 因此控制块内的【oldest_modification】记录的lsn值也是有序的。
- 2. 我们只要找到 flush 链表头节点上的【oldest_modification】所记录的lsn值，也就**找到了一个当前可以刷盘的最大值的 lsn 值**
- 小于这个值的脏页，肯定已经刷入磁盘了
- 3. 所有小于这个 lsn 值得 redolog 都可以被覆盖重用
- 4. 将这个 lsn 值赋值给一个全局变量 checkpoint_lsn ，他代表可以被覆盖的量
- **第二步**：将 checkpoint_lsn 与对应的 redo log 文件组偏移量，以及此次 checkpoint 的编号（checkpoint_no 也是一个变量，记录 checkponit 的次数）全部记录在日志文件的管理信息内
### 9.3.7. 一个事务执行的流程
- 主线程
- 客户端访问 mysql 服务，在 buffer pool 中进行数据操作，会形成脏页
- 如果目标页不在缓冲区，需要将其载入缓冲区
- 记录 redolog 缓冲在 log buffer 中
- 可能会产生很多组 redo log，会在提交事务时进行刷盘
- 刷盘时根据 checkpoint 的结果，选择可以使用的日志空间进行记录
- 事务提交后客户端就返回了，此时数据还未真正落盘
- 此时只是记录了 redo log ，真正的数据并未刷盘
- 这个过程很多操作只在内存进行，只需要记录redo log（顺序写），所以速度很快
- 后台线程1
- 不断对 flush 链表的脏页进行刷盘
- 对响应没有过高的要求
- 后台线程2
- 不断进行 checkpoint 操作，保证 redol log 可以及时写入
### 9.3.8. 系统崩溃的影响
- **log buffer 中日志丢失**
- log buffer 中日志会在每次事务开始前进行刷盘，如果在事务进行过程中崩溃，则事务需要回滚
- 事务本来就需要回滚
- **buffer pool 中的脏页丢失**
- 崩溃后可以通过 redo log 进行恢复
- 通过 checkpoint 操作我们可以确保内存中的脏页对应的记录都会在 redo log 中存在
- 存在的问题
- redo log 保证了崩溃后数据不丢失(redo log 只是记录了物理层的数据的变化)
- 但是一个事务进行中，如果**一部分 redo log 已经刷盘** (redo log 刷盘不一定是事务结束刷盘，也有[专门线程在进行](#935-log-buffer-理解即可))
- 那么系统会将本应该回滚的数据同样恢复
- 为了解决这个问题，innodb 提出了 undo log
# 10. undo log
- 参考资料：https://developer.aliyun.com/article/987243
## 10.1. 概述
- undo log
- 也叫撤销日志或者回滚日志
- 主要作用
- 实现回滚操作
- 也是 MVCC 多版本控制的核心模块
- undo log 保存在共享表空间【ibdata1文件】中
>```bash
>[zhd@localhost mysql]$ pwd
>/var/lib/mysql
>[zhd@localhost mysql]$ ll |grep ib
>-rw-rw----. 1 mysql mysql 1306 Sep 16 02:54 ib_buffer_pool
>-rw-rw----. 1 mysql mysql 12582912 Sep 16 03:08 ibdata1 # undo log 存放在这里面
>-rw-rw----. 1 mysql mysql 50331648 Oct 2 22:38 ib_logfile0
>-rw-rw----. 1 mysql mysql 50331648 Jul 15 02:53 ib_logfile1
>-rw-rw----. 1 mysql mysql 12582912 Oct 2 22:38 ibtmp1
>```
- 在讲 undo log 前，先要了解一下行数据中的两个隐藏列
- 之前在讲[行记录Compact 格式](#32231-compact格式)的时候，有讲到“每行数据除了用户自定义的字段外，还有两个隐藏列”
- 事务 ID 列: 6 字节
- 回滚指针列: 7字节
- 事实上，当我们没有建立主键时,也没有定义【非空唯一】列，还会产生 row_id
- 是为了生成聚簇索引而使用的
## 10.2. 逻辑存储形式
### 10.2.1. 事务 id (trx_id)
- innodb 的行数据中，会自动添加两个隐藏列
- trx_id:事务 id
- roll_pointer
- 事务 id (trx_id) 是一个自增的全局变量
- 如果一个【事务】对任意表做了【增删改】的操作，那么innodb 就会给他分配一个独一无二的事务id
- begin 开启一个事务, 没有事务 id
- 执行 select ，没有事务 id
- **只有当数据库数据发生改变时，才有事务 id**
- 事务 id 保存在一个全局变量 【MAX_TRX_ID】上
- 每次事务需要分配 id，就会从这个全局变量中获取，然后自增 1
- 每次变量自增到 **256(二进制为 100000000，倍数只要前面加1即可) 的倍数**就会进行落盘（保存在表空间页号为 5 的页面中）
- 发生系统服务停止或者系统崩溃后，再次启动，会读取这个数字，然后再增加 256
- 这样既保证了不会有太多 I/O 操作，还能保证 id 的有序增长
- 比如：读到 256 进行落盘，后来增长到 302，突然崩溃，下次启动后，第一个事务的 id 就是 256+256 = 512，保证新的事务id一定大
### 10.2.2. roll_pointer
- undo log 在记录日志时是这样记录的
- 每次修改数据，都会将改变的数据标记一个【新的版本】
- 老的数据就会被标记成【历史版本】
- 版本和版本之间就用 roll_pointer 指针进行连接，形成版本链
- 最新修改的数据会在链的最前面，最老的数据会在链条最后面
## 10.3. 物理存储形式
### 10.3.1. undo log 分类(了解即可)
- 当我们对数据库的数据进行一个操作时必须记录之前的信息，将来才能【悔棋】
- 插入一条数据时，至少要把这条数据的主键记录下来，以后不想要了直接根据主键删除。
- 插入一条数据，如果要回滚，则需要把主键记录下来，然后执行一个delete操作
- 删除一条数据时，至少要把这个数据所有的内容全部记录下来，以后才能全量恢复。但事实上不需要，每行数据都有一个delete_flag，事务中将其置1，记录id，如需要回滚根据id复原即可，提交事务后又purge线程处理垃圾。
- 执行一个delete操作，如果要回滚，则必须进行执行一个insert操作
- 修改一条数据时，至少要将修改前后的数据都保存下来。
- 在回滚时进行反向操作即可
- undo log 分为两类
- 一类日志只记录插入类型的操作 (insert)
- 插入类型的记录，不需要记录版本，事务提交以后这一片空间就可以重复利用了
- 一类日志只记录修改类型的操作 (delete、update)
- 修改类型的记录，必须每次修改作为一个版本记录下来
- 即使当前事务已经提交，也不一定能回收空间
- 因为其他事务可能在用
### 10.3.2. 物理存储结构
- undo log 也是以页的形式进行存储的
- 多个页是使用链表的形式进行管理的
- 针对【普通表和临时表】、【插入型和修改型】的数据，一个事务可能产生以下四种链表
### 10.3.3. undo log 记录流程
- 1. 开始事务，执行【增删改】时获得【事务id】
- 2. 在系统表空间中第 5 号页中，分配一个回滚[段](#32223-表空间组织结构)，回滚段是轮动分配的
- 比如，当前事务使用第 5 个回滚段，下一个事务就是使用第 6 个
- 【回滚段】是一个【数据页】，里面划分了 1024 个 undo slot，用来存储日志链表的头节点地址
- 3. 在当前回滚段的 cached 链表 (回收可复用的)和空闲 slot 中，找到一个可用的 slot，找不到就报错
- 4. 创建或复用一个 undo log 页，作为 first undo page，并把它的地址写入 undo slot
- 关于段的补充
- 在 [表空间组织结构](#32223-表空间组织结构) 中，段分为三类
- 叶子节点段
- 存放 B+ 树的叶子节点，存放具体的数据
- 每个叶子节点的数据量比较大，存放叶子节点数量比较小
- 非叶子节点段
- 存放 B+ 树非叶子节点的数据，主要用来进行查询
- 每个叶子节点数据量较小，存放的叶子节点数量大
- 回滚段
### 10.3.4. 回滚过程
- 1. 事务再次启动时，通过表空间 5 号页面定位到 128 个回滚段的位置
- 2. 遍历所有的 slot，找到所有状态不为空闲的 slot，并且通过 undo log 得标记位找到现在活跃 (未提交) 的所有事务 id
- 3. 根据 undo log 的记录，将数据全部回滚
# 11. 隔离级别与MVCC（重要）
- MVCC: Multi-Version Concurrency Control
- 多版本并发控制
- MVCC 在 Mysql InnoDB 中的实现主要是为了提高数据库的并发性能
- 用更好的方式去处理【读-写冲突】（加锁也能解决读写冲突，但是效率太低，其他事务需要等待）
- 做到即使有【读-写冲突】时，也能做到不加锁，非阻塞并发读
## 11.1. Read View (读视图)
### 11.1.1. 当前读 VS 快照读
- 当前读
- 像 select..lock in share mode (锁)、select for update、update、insert、delete (排他锁)，这些操作都是【当前读】，它读取的是记录的【**最新版本**】
- 读取时还要保证其他【并发事务】不能修改当前记录，会对读取的记录进行加锁
- 快照读
- 像不加锁的 select 操作就是快照读，即不加锁的【非阻塞读】
- 快照读的前提是【隔离级别不是穿行级别】
- 串行级别下的快照读会退化为当前读
- 顾名思义，快照读，读取的是【快照】，是通过 readView 实现的
- 要理解快照读，首先要理解 readView
### 11.1.2. 实现原理
- readView 是事务进行【快照读】的时候产生的【读视图】(Read View)
- 在该事务【执行快照读】的那一刻，会生成数据库系统当前的一个快照
- 注意：【快照】不是说将数据库复制一份
- 【Read View】的主要作用是做【**可见性判断**】
- 快照的实现逻辑通过 undo log 的【版本链】，配合一些【参数】，比如事务id，来确定当前事务可以读取的版本
#### 11.1.2.1. readView 的结构
- 例子
- 当前有事务id 为 12、13、14、16、20 五个事务，他们在同事修改一条数据
- 此时，事务 13 发生读取行为，在【事务13】读取之前，【事务14】已经提交，当前场景下产生一个 readView 如下：
- 一个 readView 就是一个【结构体】，可以理解为 java 中的实例 (readView)属性，包含属性如下：
- **m_ids** : 生成该 readView 时，当前系统中【活跃的事务】id列表
- 对于此前案例，因为 14 已经提交，就不活跃了，所以该变量的值为 [12,13,16,20]
- **min_trx_id** : 当前系统【活跃事务】中最小的【事务id】，也就是 m_ids 的最小值，当前案例为 12
- **max_trx_id** : 当前系统中计划分配给下一个事务的id，它可能是 m_ids 的最大值+1，也可能比它大。当前案例值假设为 22
- **creator_trx_id** : 生成这个 readView 的事务id，当前案例的值为 12
- 以上 readView 配合 undo log 就可以形成一个【快照】
- 那它是如何读取快照的呢？
## 11.2. 快照读原理解析
- 当一个事务读取数据时，会根据当前数据形成一个 readView，读取时会按照以下逻辑进行读取
- 如果【被访问数据的事务trx_id】和 readView 中的【creator_trx_id】相同时
- 说明是自己在访问自己修改过的记录，当然可以被访问
- 如果【被访问数据的事务trx_id】小于 readVew 中的【min_trx_id】
- 说明生成这个版本数据的事务，在生成 readView 前已经提交，这样的数据可以访问
- 也就是说，这个数据之前被其他事务修改过，但**事务已经提交**，所以这个版本的数据是可用的，不会产生脏读
- 如果【被访问数据的事务trx_id】大于等于 readView 中的 max_trx_id 值
- 说明生成这个版本数据的事务，是在生成 readView 后开启，这样的数据不应该被访问
- 也就是说，读取数据之后，有人修改了当前数据，那人家后面修改的数据，不能读取。即不要读取人家的最新版本的数据了。还是读取老版本数据。
- 如果【被访问数据的事务trx_id】在 min_trx_id 和 max_trx_id 范围内，则需要判断是不是在【m_ids】中（目的是判断这个数据是否已经提交）
- 如果在 m_ids 中，则说明这个版本的事务还是活跃的，没有提交的事务产生的数据当然不能读
- 如果不在 m_ids 中，说明事务已经提交，该数据可以被读取
- 也就是说，这个数据被现在活跃的其他事务正在修改，读取时要看此时这个事务是不是已经提交，目的是为了不要读取别人未提交的事务
- **总结**
- **自己修改的数据可以访问**，不管事务是否提交都可访问
- **已经提交的事务的数据可以访问**（因此需要根据情况进行判断当前数据是否是已提交的数据）
- 使用如下案例来查看这个过程
- 有一条原始数据，当前事务 trx_id = 100
- 有三个事务，分别是 trx_id = 201、trx_id = 202、trx_id = 203 要并发修改这条数据
- 首先，事务 trx_id = 201，将数据修改为 age = 24, 并记录当前数据的 trx_id = 201，rollpointer 指向历史版本数据
- 但事务 trx_id = 201 不提交
- 事务 trx_id = 202，又将数据修改为 name = Jerry, 此时先前的两条数据变成了历史版本
- 但事务 trx_id = 202 不提交
- 事务 trx_id = 203，又将数据改成 name = Alex，记录trx_id = 203，之前数据就变成了历史版本
- 但事务 trx_id = 203 不提交
- 此时，事务 trx_id = 202 发生提交
- 事务 trx_id =201 开始读取数据，生成一个 readView，readView 中记录内容如下
- m_ids:[201,203]
- 为什么没有 202？因为202已经提交了，因此它不活跃了，不需要记录
- min_trx_id:201
- max_trx_id:204
- 当前系统中计划分配给下一个事务的id，m_ids 中最大值+1
- creator_trx_id:201
- 表示创建这个读视图的事务 trx_id = 201
- 当前数据的事务id=203, 在 min_trx_id 和 max_trx_id 之间，同时它在 m_ids 中，说明事务未提交，该版本数据不可用。
- 于是往前寻找历史版本。
- 历史版本3的事务id=202, 在 min_trx_id 和 max_trx_id 之间，但它不在 m_ids 中，说明事务已提交，该版本可用。
- 找到合适版本，停止搜索
### 11.2.1. 脏读和不可重复读
- 对于 RU 隔离级别的事务来说
- 由于可以读取到未提交的事务，所有直接读取【最新的记录】（当前读）就可以
- 对于 serializable 的事务，必须使用加锁的放肆来访问
- 这两种隔离级别和 MVCC 没有什么关系
#### 11.2.1.1. 解决脏读
- 脏读 : 指的是在当前事务中读取到了其他事务未提交的数据
- 解决方案
- (1) 没有 undo + MVCC，加锁解决
- 一个事务读取了数据之后，立马给这个数据加写锁，加锁解决脏读
- (2) 使用 undo + MVCC
- 所有事务对数据的修改，记录成版本链，使用 readView 进行版本选择，每个事务只能读取满足条件的数据。这个过程不需要加锁
- 使用 MVCC 很好的解决率【读写操作】的并发执行，而且使用无锁机制（上面案例已经说明了）
#### 11.2.1.2. 解决不可重复读取
- RC 和 RR 两个隔离级别解决不可重复读是通过生成 readView 时间不同解决的
##### 11.2.1.2.1. RC 隔离级别
- RC 隔离级别
- 同一个事务中【每次读取数据同时都生成一个新的 readView】
- 两次读取时，如果中间没有其他事务进行提交，**可能会生成两个不同的readView**，导致当前事务中，两次读取的数据不一致（读的是两个不同的历史版本）
- 这就是不可重复读
##### 11.2.1.2.2. RR 隔离级别
- RR 隔离级别
- 同一个事务中【只在第一次读取数据时生成一个 ReadView】，**以后这个事务一直使用这个 readView**
- 那么同一个事务中能保证多次读取的数据是一致的。具体执行流程如下：
#### 11.2.1.3. 解决幻读
通过[间隙锁](#814-间隙锁-gap-lock)实现，一旦锁定某一个范围的数据，就会对这个范围的数据进行加锁，间隙锁保证我们 **不能在这个范围内插入新的数据**
# 12. 其他知识
## 12.1. 触发器
- 什么叫触发器？
- 与表有关的数据对象，在满足某种条件的时候，被动执行的 SQL 语句
- 有点像 Flink 的触发器
### 12.1.1. 特性
- 有 begin、end 的结构体（多条sql语句）
- 需要指定触发条件：INSERT、UPDATE、DELETE
- 有指定的触发事件：BEFORE、AFTER
### 12.1.2. 触发器创建
- 单条业务逻辑的触发器创建
>```sql
>/*
>CREATE TRIGGER 触发器名称 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名
>FOR EACH ROW 业务逻辑
>*/
>-- 当b_user表中插入数据后，b_log表中也插入一条数据
>CREATE TRIGGER trigger_insert AFTER INSERT ON b_user
>FOR EACH ROW INSERT INTO b_log(字段) VALUES('插入数据')
>```
- 多条业务逻辑的触发器
- 使用 begin、end 对多条 sql 进行包围
>```sql
>/*
>DELIMITER $
>CREATE TRIGGER 触发器名称 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名
>FOR EACH ROW
>BIGIN
>INSERT...;
>UPDATE...;
>END;$
>*/
>-- 在b_user表中插入数据前，b_log表中插入2条数据
>DELIMITER $
>CREATE TRIGGER trigger_ insert_before BEFORE INSERT ON b_user
>FOR EACH ROW
>BEGIN
>INSERT INTO b_log(comments,name) values('insert1' ，NEW.name);
>INSERT INTO b_log(comments,name) values('insert2' , NEW.name) ;
>END;$
>```
### 12.1.3. 删除触发器
>```sql
>DROP TRIGGER 触发器名称
>```
## 12.2. 存储过程
### 12.2.1. 变量
- 参考资料：https://zhuanlan.zhihu.com/p/420749309
#### 12.2.1.1. 系统变量
- 系统变量
- 由 mysql 数据库滚利系统提供的、变量名称固定，可以修改和查看值。分为全局变量和会话变量
- [全局变量](#2151-全局变量)
- 当前mysql服务没有重启时，我们可以查看和修改的变量
- 全局变量在修改后，在不同的会话中都会立即生效，但是在重新启动mysql服务后，全局变量会恢复为默认值，如果想让全局变量依旧有效，需要去修改.ini文件（MySQL配置文件）
- [会话变量](#2152-会话变量)
- 和mysql连接形成的会话，生命周期在整个会话过程中
- 会话变量在修改后只对当前会话有效。一般在开发过程中修改会话变量。如：字符编码格式等可以在ini文件中进行设置
- 查看系统变量
>```sql
>SHOW GLOBAL variables; -- 查看全局变量
>SHOW SESSION variables; -- 查看会话变量
>SHOW variables; -- 查看会话变量
>SHOW GLOBAL variables like '%dir%'; -- 模糊查询环境变量
>SELECT @@datadir; -- 查看全局系统变量
>SELECT @@session_track_transaction_info;
>```
- 修改系统变量
>```sql
>SHOW GLOBAL variables like 'autocommit'; -- 全局系统变量中为自动提交事务
>SET GLOBAL autocommit=0; -- 将全局的自动提交的事务改为手动提交
>SHOW SESSION variables like 'autocommit'; -- 查看会话变量中自动提交事务
>SET SESSION autocommit=0; -- 将会话变量中自动提交的事务改为手动提交
>SET @@session.autocommit=1;
>SET @@global.autocommit=1;
>```
#### 12.2.1.2. 用户变量
- Mysql 允许用户自定义变量：用户变量和局部变量
- 系统变量用 @@ 开头，用户变量用 @ 开头
- 用户变量：当前会话有效
>```
>#设置方式1，先去声明并初始化用户变量，赋值操作既可以使用=进行赋值，也可以使用:=进行赋值
>SET @变量名=值;
>SET @变量名:=值; // 对于未事先声明的变量，需要用 @ 前缀，对于已经进行 DECLARE 变量，不需要用 @
>SELECT @变量名:=值;
>#设置方式2
>SELECT 字段 into @变量名 FROM 表名;
>```
- 局部变量
- 在 begin、end 的结构体中有效，生命必须时begin end 结构体的第一句
>```
>#声明方式，必须在begin后面从第一行开始
>DECLARE 变量名 类型;
>DECLARE 变量名 类型 DEFAULT 值;
>
>#局部变量的赋值
>SET 变量名:=值;
>SELECT @变量名:=值;
>SELECT 字段 into 变量名 FROM 表名;
>```
### 12.2.2. 存储过程的创建
- 存储过程
- 概念
- 存储过程是一组已经预先编译好的 sql 语句的集合，理解为批处理语句（增加流程控制语句），一般在复杂逻辑中才会使用存储过程
- 通俗讲，**存储过程有点像 C 语言函数**
- 就是定义一个模板流程，供其他代码调用
- 优点
- 提高了代码的可用性
- 简化了数据库操作，将业务逻辑的细节隐藏在存储过程中
- 减少了编译次数，减少了网络 I/O 的次数，从而提高操作效率
- **创建存储过程**
- 如果存储过程中只有一条SQL语句可以省略BEGIN END
>```
>/*
>DELIMITER $
>CREATE PROCEDURE 存储过程的名称(参数列表)
>BEGIN
>局部变量的定义
>多条sql语句
>流程控制语句
>END;$
>*/
>```
- 有关 delimiter 的作用
- https://blog.csdn.net/xkdlzy/article/details/123918971
- delimiter表示分隔符，delimiter $ 表示将分隔符换成 $
#### 12.2.2.1. 参数列表
|参数模式 |形参名称 |参数类型|
|:-:|:-:|:-:|
|IN |username |mysql数据库中的数据类型（数值型，字符型，日期型）|
|OUT |pwd |mysql数据库中的数据类型（数值型，字符型，日期型）|
|INOUT |xxx |mysql数据库中的数据类型（数值型，字符型，日期型）|
- **IN** : 声明该参数是一个输入姓参数（类似于java中的形参）
- **OUT** : 声明该参数为一个输出型参数（类似于java中的返回值），在一个存储过程中可以定义多个out类型的参数
- **INOUT** : 声明该参数可以为输入型参数，也可以为输出型参数
- 存储过程的调用
>```
>CALL 存储过程的名称(实参列表)
>-- 实参列表中包含由输出类型的参数
>```
#### 12.2.2.2. 存储过程案例
- 无参的存储过程
>```sql
>-- 用于向b_user表中插入2条数据
>DELIMITER $
>CREATE PROCEDURE pro_insert()
>BEGIN
>INSERT INTO b_user(name,sex) VALUES('1','1');
>INSERT INTO b_user(name,sex) VALUES('2','2');
>END; $
>
>CALL pro_insert();
>```
- 带有 IN 模式参数的存储过程
>```sql
>-- 用于向b_user插入2条数据，性别由客户输入
>DELIMITER $
>CREATE PROCEDURE pro_insert2(IN sex CHAR(1))
>BEGIN
>INSERT INTO b_user(name,sex) VALUES('1',sex);
>INSERT INTO b_user(name,sex) VALUES('2',sex);
>END;$
>-- 调用存储过程
>CALl pro_insert2('男');
>```
- 带有多个 IN 参数的存储过程
>```sql
>-- 用于向b_user插入2条数据，用户名和密码由客户输入
>DELIMITER $
>CREATE PROCEDURE pro_insert3(IN name VARCHAR(10),IN sex VARCHAR(20))
>BEGIN
>INSERT INTO b_user(name,sex) VALUES(name,sex);
>INSERT INTO b_user(name,sex) VALUES(name,sex);
>END; $
>
>CALL pro_insert2('uname','男');
>```
- 带有IN、OUT参数的存储过程
>```sql
>-- 判断用户登录，如果用户名和密码输入正确登录成功，否则登录失败
>-- 根据输入的用户名和密码作为条件去b_user表中查询，如果查询总行数==1，则认为登录成功，让result返回登录成功，否则登录失败
>DELIMITER $
>CREATE PROCEDURE pro_login(IN name VARCHAR(20),IN pwd VARCHAR(20),OUT result VARCHAR(20))
>BEGIN
>DECLARE total INT DEFAULT 0;-- 用于存放查询总行数，创建局部变量
>select count(*) from b_user u where u.name=name and u.pwd=pwd;-- 将查询结果赋值给total局部变量
>SET result:=IF(total=1,'登录成功','登录失败');
>END; $
>-- 存储过程如何执行
>-- 解决判断，使用自定义变量
>SET @result:='';
>CAll pro_login('李四','123',@result);
>select @result;
>```
- 存储过程的综合案例可以看 [hive笔记](https://gitee.com/HaiXiuDeDXianSheng/big-data-learning-notes/tree/master/HIVE%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/HIVE%E5%9F%BA%E7%A1%80#10432-%E4%BD%BF%E7%94%A8-mysql-%E5%AE%9E%E7%8E%B0-posexplode-%E5%87%BD%E6%95%B0%E5%8A%9F%E8%83%BD)，介绍了使用 mysql 代替 hive posexplode() 函数的过程
#### 12.2.2.3. 删除和修改
- 删除存储过程
>```
>DROP PROCEDURE 存储过程名称
>```
- 查看存储过程
>```
>SHOW CREATE PROCEDURE 存储过程名称;
>```
- 修改存储过程
>```sql
>DROP
>CREATE
>```
### 12.2.3. 流程控制语句
#### 12.2.3.1. 选择结构
- IF函数
- 功能：三目运算
- 语法：IF(逻辑表达式，表达式1，表达式2)
- IF结构
- 功能：实现多路选择
- 注意：只能用在BEGIN END结构体中
>```
>/*
>IF 逻辑表达式 THEN 语句1;
>ELSEIF 逻辑表达式2 THEN 语句2;
>...
>ELSE 语句n;
>END IF;
>*/
>```
- case 结构
- 不等值选择
>```
>CASE
>WHEN 逻辑表达式 THEN 语句1
>...
>ELSE 语句n
>END
>```
- 等值选择
>```
>CASE 字段|变量|表达式
>WHEN 值 THEN 值|语句
>WHEN 值 THEN 值
>...
>ELSE 值
>END
>```
#### 12.2.3.2. 循环结构
- WHILE
>```sql
>/*
>WHILE 逻辑表达式 DO
>循环体
>END WHILE;
>*/
>-- 需求：创建存储过程，输入一个值，返回1到该值的和
>-- 分析：一个输入参数，一个返回值，在结构体中，从1循环到输入的值，求和
>DELIMITER // -- 将分隔符换成 //
>CREATE PROCEDURE pro_sum(IN input INT,OUT total INT)
>BEGIN
>DECLARE i INT DEFAULT 1;
>DECLARE sum_ INT DEFAULT 0;
>WHILE i<=input do
>SET sum_=sum_+i;
>SET i=i+1;
>END WHILE;
>SET totle:=sum_;
>END;//
>
>SET @result=0;
>CALL por_sun(10,@result);
>SELECT @result;
>```
- LOOP
- 基本语法
>```bash
>#Loopname是定义的循环名称，为了跳出循环时指定跳出的循环
>loopname:LOOP;
> IF 逻辑表达式 THEN
> LEAVE loopname; -- 跳出当前指定的循环，类似于java中的break
> END IF;
>END LOOP;
>```
- 案例
>```sql
>DElIMITER //
>CREATE PROCEDURE pro_sum_loop(IN input INT,OUT total INT)
>BEGIN
>DECLARE i INT DEFAULT 1;
>DECLARE sum_ INT DEFAULT 0;
>a:LOOP; -- a为loopname
>SET sum_:=sum_+i;
>SET i:=i+1;
>IF i>input THEN
>LEAVE a;
>END IF;
>END LOOP;
>SET total:=sum_;
>END;//
>-- 调用存储过程
>SET @result=0;
>CALL por_sum_loop(10,@result);
>SELECT @result;
>```
- REPEAT: 其实就是 do...until..循环
- 基本语法
>```
>REPEAT
>循环体
>UNTIL 逻辑表达式 -- 当满足逻辑表达式，跳出循环
>END REPEAT;
>```
- 案例
>```sql
>DELIMITER //
>CREATE PROCEDURE pro_sum_loop(IN input INT,OUT total INT)
>BEGIN
>DECLARE i INT DEFAULT 1; -- 声明局部变量
>DECLARE sum_ INT DEFAULT 0;
>REPEAT
>SET sum_:=sum_+i;
>SET i:=i+1
>UNTIL i>input
>END REPEAT;
>SET total:=sum_;
>END; //
>
>SET @result=0;
>CALL por_sum_loop(10,@result);
>SELECT @result;
>```
## 12.3. 存储函数
- 函数也是一组预先编译好的sql的集合，基本和存储过程相似
- 函数 VS 存储过程
- **存储过程可以有 0 个、1个或者多个返回值**
- 适用于 insert、update、delete 操作
- **函数只能有一个返回值**，适用于处理数据以后，返回一个已知的结果
- 创建函数基本语法
>```
>CREATE FUNCTION 函数名称(参数列表) RETURNS 返回类型 BINLOG参数
>BEGIN
>函数体
>END
>```
- 名词解释
- 参数列表
- 需要填的是 参数名称 参数类型
- BINLOG 参数
- NO SQL : 函数体中没有sql语句，也不会存在参数
- READS SQL DATE : 函数体中存在 sql 语句，但是整个数据是只读的，不会修改数据
- MODIFIES SQL DATE : 函数体中存在 sql 语句，并且会修改数据
- CONTAINS SQL : 函数体中包含有 sql 语句
- 函数体
- 在函数体汇总必须包含 return 语句，将 return 放在函数体最后一行执行
- 案例
>```sql
>-- 写一个函数，用于求两数之和
>DELIMITER //
>CREATE FUNCTION sum_(input1 INT,input2 INT) RETURNS INT NO SQL
>BEGIN
>return input1+input2;
>END;//
>```
- 使用函数
>```
>select 函数名(参数列表);
>```
- 查看函数
>```
>show create function 函数名 ;
>```
- 删除函数
>```
>DROP FUNCTION 函数名
>```
## 12.4. 定时任务
- 定时任务使用场景
- 定时任务可以用于 mysql 生产系统定期向大数据平台同步数据
- 定时清空mysql表中的过期数据
- 查看定时策略是否开启
>```bash
>MariaDB [zhdtest]> show variables like '%event_sche%' ;
>+-----------------+-------+
>| Variable_name | Value |
>+-----------------+-------+
>| event_scheduler | OFF |
>+-----------------+-------+
>1 row in set (0.001 sec)
>```
- 开启定时策略
>```sql
>set global event_scheduler=1;
>```
### 12.4.1. 创建定时任务
>```sql
>create event run_event
>on schedule every 1 minute
>on completion preserve disable
>do call test_procedure (); -- 可以调用存储过程或函数
>```
- create event day_event：是创建名为run_event的事件
- 创建周期定时的规则，意思是每分钟执行一次
- on completion preserve disable 是表示创建后并不开始生效。
- do call test_procedure() 是该 event (事件)的操作内容
### 12.4.2. 定时任务操作
- 查看定时任务
>```bash
>MariaDB [zhdtest]> SELECT event_name,event_definition,interval_value,interval_field,status
> -> FROM information_schema.EVENTS;
>Empty set (0.003 sec)
>```
- 开启或关闭定时任务
>```sql
>alter event run_event on completion preserve enable; -- 开启定时任务
>alter event run_event on completion preserve disable;-- 关闭定时任务
>```
### 12.4.3. 定时规则
- 周期执行，关键字 EVERY
- 单位有：second、minute、hour、day、week、quarter(季度)、month、year
>```sql
>on schedule every 1 week --每周执行1次
>```
- 在具体某个时间执行：关键字 AT
>```sql
>on schedule at current_timestamp()+interval 5 day -- 5天后执行
>on schedule at '2019-01-01 00:00:00' -- 在2019年1月1日，0点整执行
>```
- 在某个时间段执行：关键字 STARTS...ENDS...
>```sql
>on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month -- 5天后开始每天都执行执行到下个月这天
>on schedule every 1 day ends current_timestamp()+interval 5 day -- 从现在起每天执行，执行5天
>```
