第1章 课程介绍

本章对课程内容、知识点、授课过程进行引导性说明。

1. 问卷调查

  • 你真的搞懂MySQL底层原理了吗?
  • 你知道如何改善慢查询性能吗?
  • 你知道如何架设高性能集群吗?
  • 你知道未来数据库的发展趋势吗?

这些问题很重要! 因为….

  • 工作中:MySQL高性能集群越来越流行
  • 面试中:深入考察MySQL原理、性能调优
  • 职业规划:需要根据技术发展方向选择职业路径

2. 精通MySQL 应该怎么学?

从理论到实战

  • 从数据表逻辑结构，到优化数据表的性能
  • 从数据查询原理，到改善慢SQL性能

从单点到集群

  • 从学习单点原理，到掌握集群原理
  • 从高性能单点，到高性能集群
  • 从单点快速部署，到集群快速部署

从现在到未来

  • 从5.x版本，到8.0版本
  • 从原生单体数据库，到原生分布式数据库
  • 从学习技术原理，到理解技术趋势

以上三点就是本课程的主线

  • 以技术原理作为坚实基础
  • 以真实场景作为学习环境
  • 以架构思维作为学习目标

3. 什么是“三高”

  • 高并发:同时处理的事务数高
  • 高性能:事务/SQL的执行速度高
  • 高可用:系统可用时间高

为什么不直接讲“三高”

“三高”只是目的，并不是手段，手段有:

  • 复制
  • 扩展
  • 切换
复制
  • 目的:数据冗余
  • 手段: binlog传送
  • 收获:并发量提升、可用性提升
  • 问题:占用更多硬件资源
扩展
  • 目的:扩展数据库容量
  • 手段:数据分片分库、分表
  • 收获:性能、并发量的提升
  • 问题:可能降低可用性
切换
  • 目的:提高可用性
  • 手段:主从身份切换
  • 收获:并发量的提升
  • 问题:丢失切换时期数据

“三高”的实现

  • 高并发:通过复制和拓展，将数据分散至多节点
  • 高性能:复制提升速度，拓展提升容量
  • 高可用:节点间身份切换保证随时可用

“三高”章节

  • 复制:《怎样实现数据冗余?》

  • 扩展:《数据库容量不够怎么办?》

  • 切换:《数据库经常宕机怎么办?》

4. 如何提升单点性能

三高的集群也是以单点的高性能作为保障的

  • 建表: 表结 构合理，索引高效
  • 查询: 优化SQL语句，选择正确索引
  • 更新: 正确使用锁，合理优化事务

建表:表结构合理，索引高效

B+树的数据结构与InnoDB的存储结构
lnnoDB行记录格式的历史与原理
索引、数据约束、视图的注意事项

查询: 优化SQL语句，选择正确索引

  • 覆盖索引、索引下推、松散索引的原理与实战
  • 排序、随机选取、COUNT的优化方法
  • 索引失效时的排查方向

更新: 正确使用锁，合理优化事务

  • MySQL和InnoDB日志体系
  • 全局锁、表锁、元数据锁、行锁、间隙锁
  • 死锁的原理与优化方法
  • 事务与MVCC的原理与性能优化

5. “高性能单点”章节

  • 建表:《如何建表更符合业务?》
  • 查询:《怎么查询速度更快?》
  • 更新:《如何处理数据更新?》

6. 未来数据库发展趋势

  • MySQL8.0新特性
  • NewSQL数据库
  • 新一代分布式数据库CockroachDB

第2章 环境搭建

本章主要进行学习环境的搭建：CentOS7操作系统搭建、MySQL5.7安装与MySQL5.7配置与启动。

使用Mac或者不想用虚拟机的同学请参考：

Mac安装：https://juejin.cn/post/6844903831298375693

Windows直接安装（不使用linux虚拟机）：https://www.runoob.com/w3cnote/windows10-mysql-installer.html

Linux安装Mysql

  • 安装 wget
1
yum install wget
  • 下载MySQL5.7安装包
1
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-1.el7.x86_64.rpm-bundle.tar
  • 解压
1
tar -xvf mysql-5.7.33-1.el7.x86_64.rpm-bundle.tar
  • 卸载Centos自带的MariaDB
1
rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64
  • 安装perl
1
yum install perl
  • 安装net-tools
1
yum install net-tools
  • 安装mysql-community-common
1
rpm -ivh mysql-community-common-5.7.33-1.el7.x86_64.rpm
  • 安装mysql-community-libs
1
rpm -ivh --force --nodeps mysql-community-libs-5.7.33-1.el7.x86_64.rpm
  • 安装mysql-community-client
1
rpm -ivh mysql-community-client-5.7.33-1.el7.x86_64.rpm
  • 安装mysql-community-server
1
rpm -ivh --force --nodeps mysql-community-server-5.7.33-1.el7.x86_64.rpm
  • 查看mysql客户端是否安装成功
1
which mysql
  • 安装vim
1
yum install vim
  • 编辑mysql配置文件，增加skip-grant-tables配置
1
vim /etc/my.cnf

  • 在[mysqld]段增加一行：skip-grant-tables

  • 启动mysql服务

1
systemctl start mysqld.service
  • 打开mysql客户端
1
mysql
  • 设置root密码
1
update mysql.user set authentication_string=password('123456') where user='root';
  • 立即生效
1
flush privileges;
  • 退出mysql并停止mysql服务
1
systemctl stop mysqld.service

  • 编辑my.cnf配置文件将：skip-grant-tables这一行注释掉

  • 启动mysql服务

1
systemctl start mysqld.service
  • 再次登录mysql
1
mysql -uroot -p123456
  • 设置密码的验证强度等级
1
set global validate_password_policy=LOW;
  • 设置密码长度
1
set global validate_password_length=4;
  • 设置密码
1
set password=password('123456');
  • 开启远程登录
1
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
  • 退出mysql客户端
1
exit
  • 开放端口
1
firewall-cmd --zone=public --add-port=3306/tcp --permanent
  • 重启防火墙
1
firewall-cmd --reload

第3章 一个SQL语句如何执行

本章介绍MySQL的各个组成部分与架构设计思路，演示MySQL的网络连接方法及图解一次更新SQL的执行过程等，建立MySQL清晰的架构体系，深入理解MySQL Server层+存储引擎层的架构体系，并理解存储引擎插件化的好处。

3.1 为什么需要数据库

  • 有了电子表格，为什么还要数据库?

image-20220327105014957

数据量-数据库无上限

  • Excel2003之前最大行数是65536行
  • Excel2007之后最大行数是1048576行
  • 数据库理论上没有行数上限
  • 单机数据库若行数太多遇到性能问题，可以通过分库分表等技术解决

结构化查询-数据库有SQL

  • 数据库有完备的结构化查询语言(SQL)
  • Excel也有结构化查询的功能（Microsoft Query)
  • Excel结构化查询功能不如数据库强大

服务化-数据库面向软件

  • Excel是文件级别的，直接面向终端用户
  • 数据库面向软件，提供软件接口

事务-数据库千人干面

  • Excel没有事务，无法做到不同人的工作同时进行（在线文档底层还是数据库）
  • 数据库事务是关系型数据库的核心优势

分布式-数据库的潜力巨大

  • Excel天生是单体的，面向文件的
  • 数据库具有主备复制、高可用、分布式等形态。

总结

  • 电子表格面向个人，以文件为基础，拓展性差
  • 数据库面向软件，支持事务、拓展性好

3.2 软件的典型架构是怎样的

分层架构

  • MVC controller—->service—->dao
  • 微服务 前端–>网关—->服务层—->数据库
  • k8s kubelete–>Docker—->linux宿主机
  • 生活 董事会—->高管—>程序员

image-20220327110050983

事件驱动架构

  • MQ
  • openstack

image-20220327110651178

管道-过滤器架构

  • 一个订单经过一系列操作数据

image-20220327110751870

微核架构

  • VScode 本身很小，依赖很多插件实现其强大功能
  • IDEA 社区版也是很小 旗舷版本=安装Springboot等很多插件
  • Linux内核
  • 鸿蒙内核

image-20220327110939386

3.3 MySQL软件架构是怎样的

MySQL软件架构

image-20220327111504555

总结

  • 软件工程当中有很多经典的架构设计
  • 大型软件往往不是使用单一的架构设计，而是多种混合
  • 研究问题要有清晰的视野，分清楚微观和宏观
  • 理论要灵活实践，不要死读书

3.4 客户端怎样连接MySQL数据库

客户端与MySQL的连接方式

  • TCP/IP连接
  • 命名管道
  • 共享内存
  • UNIX域套接字

TCP/IP连接

  • TCP/IP连接是MySQL在任何平台上都提供的连接方法
  • TCP/IP是目前互联网最主流的网络连接方式
MySQL TCP通讯协议
  • 三次握手建立TCP连接
  • 认证连接
  • 认证通过之后，客户端开始与服务端之间交互
  • 断开MySQL连接
  • 四次握手断开TCP连接

认证连接

  • 服务端–->客户端:发送握手初始化包
  • 客户端–>服务端:发送验证
  • 服务端->客户端:认证结果消息

命令执行

  • 客户端->服务端:发送命令包(Command Packet)
  • 服务端->客户端:发送回应包

断开连接

  • 客户端->服务器:发送退出命令包
MySQL TCP报文格式

image-20220327112135152

  • 消息头:3字节报文长度、1字节序号
  • 消息体:1字节指令、其余为参数
  • 指令举例:切换数据库(Ox02)、查询命令(Ox03)

image-20220327113012528

命名管道

  • 命名管道:同一台服务器通讯(Windows)
  • 命名管道开启方式: –enable-named-pipe

共享内存

  • 服务端:配置—shared-memory

  • 客户端:配置—protocol=memory

UNIX域套接字

  • 服务端:配置 socket=/tmp/mysql.sock
  • 客户端:配置 S =/tmp/mysql.sock

总结

  • TCP/IP连接是MySQL最常用的连接方式
  • TCP/IP连接报文可以作为其他C/S架构的参考
  • 其他连接方式均限于本机连接，使用范围有限

3.5 一个SQL语句是怎样执行的

image-20220327113858006

查询缓存

  • 之前执行过的语句会KV的形式缓存在内存中
  • 查询之前先查找之前执行过的相同语句
  • 不推荐使用缓存:数据表修改后，会删除所有相关缓存
  • MySQL 8.0以后缓存功能已经去掉

分析器

  • 分析器的作用是知道你要“干什么”
  • 先做词法分析，识别SQL语句中的关键字
  • 再做句法分析，判断SQL语句是否符合语法

优化器

  • 优化器的作用是要知道“怎么做”
  • 优化器的主要工作是决定如何使用索引

执行器

  • 执行器的主要工作是校验权限、调用存储引擎
  • 执行器首先校验此用户对目标数据有无权限
  • 执行器会以行为粒度，调用存储引擎，执行SQL
  • 在没有索引的情况下，执行器会循环查询所有行

存储引擎

  • 存储引擎的任务是将执行器的指令落实在数据文件上
  • 不同存储引擎的原理和执行方法有很大不同

总结

  • SQL语句执行的过程涉及到了MySQL几乎所有的模块
  • 一个SQL语句是按照分析-优化-执行-落盘的步骤执行的
  • MySQL 8.0之后已经停用了缓存功能
  • 不同存储引擎的执行方法不同

3.6 MySQL有哪些存储引擎

  • lnnoDB
  • MylSAM
  • Memory
  • Archive

MylSAM

  • MySQL 5.5.5之前的默认存储引擎
  • 插入数据快（不支持事务 功能少速度快，这是一对不可调横的天平）
  • 空间利用率高（没有事务 不需要redolog undolog）
  • 不支持事务

lnnoDB

  • MySQL 5.5.5之后的默认存储引擎
  • 支持事务、外键
  • 支持崩溃修复能力和并发控制

Memory

  • 所有的数据都在内存中，速度快
  • 数据安全性差

Archive

  • 数据压缩、空间利用率高
  • 插入速度快
  • 不支持索引，查询性能差

总结

  • lnnoDB是目前最主流的存储引擎，适合各种互联网业务
  • 查询效率要求非常高的可以考虑MylSAM
  • 日志信息归档可以考虑Archive

3.7 本章小结

为什么需要数据库

  • 数据库面向软件
  • 数据库支持事务
  • 数据库有SQL语言
  • 数据库可扩展

数据库软件的典型架构

  • 分层架构

连接器—-> Server层—> 存储引擎层

  • 管道-过滤器

分析器 优化器 执行器久类似于管道过滤

  • 微核架构

lnnoDB MylSAM等存储引擎类似于插件插入进来的

image-20220327114947500

MySQL有哪些存储引擎

  • lnnoDB-互联网业务最常用，支持事务
  • MylSAM一查询快
  • Memory 一 l临时内存表
  • Archive -归档

第4章 如何建表更符合业务

本章详细学习InnoDB的数据表底层原理，详细阐述B+树与逻辑存储结构，掌握如何建立高效索引、如何优化数据表结构。

4.1 什么叫索引组织表

索引组织表(Index Organized Table)

  • 索引组织表不是一种“组织表”
  • 索引组织表是由索引“组织起来的”表
  • InnoDB中，表都是根据主键顺序组织存放的

索引(Index)

  • 索引是数据库中对某一列或多个列的值进行预排序的数据结构
  • 索引可以理解为数据的“目录”
  • InnoDB中，主键是一个特殊索引字段

主键(Primary Key)

InnoDB存储引擎表中，每张表都有一个主键:

  • 若表中有一个非空唯一索引 (Unique NOT NULL)，即为主键
  • 若有多个非空唯一索引，选择第一个定义的索引
  • 若无，InnoDB自动创建一个6字节的指针，作为主键
1
2
3
4
5
6
7
8
9
CREATE TABLE Z (
    a INT NOT NULL,
    b INT NULL,
    c INT NOT NULL,
    d INT NOT NULL,
    UNIQUE KEY (b),
    UNIQUE KEY (d),
    UNIQUE KEY (c)
);

image-20220327120529998

总结

lnnoDB数据表均为索引组织表

4.2 B+树的B是什么意思

主流索引查找算法

  • 线性查找Linear Search

  • 二分查找Binary Search

  • 二叉查找树Binary Search Tree

  • 平衡二叉树AVL Tree

  • B树B Tree

  • B+树B+ Tree

  • 时间复杂度O(N)
  • 从第一个数据开始，逐个匹配
  • 时间复杂度O(logN)
  • 拿出有序数列中点位置作为比较对象
  • 根据中点数据大小，选取一半数据作为新的数列
  • 每次可以将数据量减小一半
二叉查找树Binary Search Tree
  • 时间复杂度O(logN)
  • 使用经典的二叉树数据结构
  • 由根节点开始查找
  • 可能退化为线性查找
平衡二叉树AVL Tree
  • 查找时，与二叉搜索树相同
  • 增删改时，通过旋转操作，维护树的平衡
  • AVL树可以保证不会退化成线性查找
B树B Tree
  • B树是线性数据结构和树的结合
  • B树通过多数据节点大大降低了树的高度
  • B树不需要旋转就可以保证树的平衡
B+树B+ Tree
  • B+树是由B树发展而来的一种数据结构
  • B+树的所有数据均在叶子节点
  • B+树的所有数据形成了一个线性表

总结

  • B+树是目前最主流的数据库索引算法
  • B+树由线性表、二叉树、B树发展而来
  • B＋树集成了线性表、平衡二叉树的优势

4.3 为什么说InnoDB索引即数据

B+树索引

  • lnnoDB使用B+树作为索引的数据结构
  • B+树的高度一般为2-4层，查找速度非常快
  • InnoDB索引分为聚簇索引(主索引)和辅助索引

聚簇索引Clustered Index

  • 根据表的主键构造一个B+树
  • 叶子节点直接存放行数据，而不是指针
  • 索引组织表中，数据也是B+树的一部分

辅助索引Secondary Index

  • 每张表可以有多个辅助索引
  • 叶子节点并不包含行数据
  • 叶子节点记录了行数据的主键，用来指示数据位置

总结

  • InnoDB索引分为聚簇索引(主索引)和辅助索引
  • 在同层B+树节点之间，为双向链表
  • 在B+树节点之内，数据条目之间为单向链表

4.4 lnnoDB数据表是如何存储的

lnnoDB逻辑存储结构

image-20220327121649734

表空间(tablespace)
  • 表空间指的是数据表在硬盘上的存储空间
  • 默认，所有表的数据都存在共享表空间
  • 每个表的数据也可以放在独占表空间(ibd文件)
    心
段(segment)
  • 数据段:B+树的叶子节点
  • 索引段:B+树的非叶子节点
  • lnnoDB中，段由存储引擎自动管理
区(extent)
  • 区是由连续页组成的空间，大小为1MB
  • 一次从磁盘申请4~5个区
  • 一般来讲含有64个页(Page)
页(Page)
  • 页是lnnoDB中磁盘读写的最小逻辑单位，默认16KB
  • 一个数据页就是一个B+树的节点(B+Tree Node)
  • 页的大小充分考虑了机械硬盘和SSD的最小单元(512B和4KB）

总结

  • InnoDB的逻辑存储结构为表空间、段、区、页、行
  • lnnoDB的逻辑存储结构充分考虑了以基于B+树的表结构
  • InnoDB中的页是lnnoDB自身的逻辑概念，与硬件的页无关

思考

  • 为什么页不能太大，比如16MB?

页是innodb读写磁盘的最小单位，16M可能每次对磁盘 的压力过大，每次浪费特别大，很有可能只需要一条数据 就读16M数据

16M的页是一个链表，拉到内存读遍历要找的一条数据需要线性查找，效率慢

  • 为什么页不能太小，比如16B?

机械硬盘和SSD的最小单元(512B和4KB）如果太小，要么照成数据浪费，要么就还需要和别的数据拼一块单元，逻辑更复杂

4.5 lnnoDB数据行长什么样

lnnoDB中的变长列

  • 长度不固定的数据类型: VARCHAR、VARBINARY、BLOB、TEXT
  • 占用空间大于768Byte的不变长类型:CHAR（大于768byte自动转varchar）
  • 变长编码下的CHAR（定长char在UTF-8编码下占据空间也是不固定的）

行溢出数据

  • 由于lnnoDB每个数据页容量有限，导致数据字段也是有限的
  • 当数据字段过大时，InnoDB会使用行溢出机制
  • 行溢出机制会把超长字段放入单独开辟的数据页

image-20220327132624668

行溢出机制

有一个区域是blob页，专门存大数据字段

image-20220327132656558

InnoDB行记录格式Row Format

InnoDB行记录格式主要分为两个时代:

  • Redundant / Compact (Antelope文件格式)
  • Dynamic / Compressed (Barracuda文件格式)
Redundant

image-20220327134106007

  • 字段偏移列表

用来记录每个字段的相对位置

按照列的顺序逆序放置

  • Header

列数量、字段偏移表的单位、下一行记录的指针等信息

  • RowID

没有可用主键时，使用Row ID作为隐藏主键

  • TxID

Transaction lD:事务ID

  • Roll Pointer

Roll Pointer:回滚指针

  • Col 1，Col 2，…，Col n，

数据（未溢出)

数据 前768Byte数据BLOB页指针（溢出)

Compact

image-20220327134440953

  • 变长字段长度表

每个变长字段的长度

按照列的顺序逆序放置

  • NULL标志位

指示行记录中的NULL值

每个bit代表一个字段

  • Header

列数量、字段偏移表的单位、下一行记录的指针等信息

  • RowID

没有可用主键时，使用Row ID作为隐藏主键

  • TxID

Transaction lD:事务ID

  • Roll Pointer

Roll Pointer:回滚指针

  • Col 1，Col 2，…，Col n，

数据（未溢出)

数据 前768Byte数据BLOB页指针（溢出)

Dynamic

image-20220327134907172

  • Col 1，Col 2，…，Col n，

数据长度大于40Byte发生溢出

数据（未溢出)

数据BLOB页指针（溢出)

Compressed

物理结构上与Dynamic类似

image-20220327134907172

  • 对表的数据行使用zlib算法进行了压缩存储
  • 可以节约40%左右空间但对CPU的压力较大

总结

  • InnoDB行记录格式经历了两个时代、四种类型
  • 行记录格式进化的核心需求是节约行记录空间
  • 节约行记录空间从而增加每个页的数据行数，提高查询效率

4.6 索引有哪些左侧用法

联合索引

  • 使用两个或以上字段生成的索引
  • 联合索引也可以加速“最左前缀”的查询
  • 联合索引可以代替最左侧字段的单独索引
  • “带头大哥不能死，中间兄弟不能丢。”

字符串的前缀索引

  • 如果字符串过长，可以考虑使用前缀索引节约空间
  • 如果前缀区分度太小，可以考虑两种变通方法:
    • 倒序存储(譬如身份证号一个县城前几位都是一样的)
    • 新建Hash字段、
    • alter table user add index index2(email(6))

字符串like

  • (like %关键字%) (like %关键字)会使索引失效
  • (like关键字%)左模糊才可以使用索引
总结
  • MySQL有很多“左侧用法”
  • 联合索引可以代替最左侧字段的单独索引
  • 字符串的前缀索引可以节约硬盘空间
  • 字符串左模糊可以有效利用索引（如果全模糊左模糊需要搜索引擎）

4.7 如何约束数据

Primary Key / Unique Key

  • 通过将数据字段设置为索引，约束数据内容
  • Primary Key:唯一，不为NULL
  • Unique Key:唯一
  • 唯一约束插入时的性能开销较大

Foreign Key

  • 外键可以对数据的正确性实现约束
  • 外键会影响性能
  • 外键手动修复的时候比较麻烦，设计每个表直接数据约束

Default / NOT NULL

  • Default : 数据默认值
  • NOT NULL:数据不为空

触发器

  • 插入、修改数据时，使用触发器校验数据
  • 容易干扰业务，使用很少

总结

  • InnoDB有多重约束数据方法
  • 将数据设置为索引字段可以约束数据的唯一性，但开销较大
  • 外键可以对数据有效性进行校验
  • NOT NULL的行为可能受到sql_mode参数的影响(Mysql是否开启了严格模式，如果没开启，设置NOT NULL是没用的)

SHOW VARIABLES LIKE ‘innodb_strict_mode’ ;

image-20220327140330909

4.8 如何使用不存在的数据表

视图View

  • 使用视图可以创建不存在的虚拟表
  • 视图的原理是预设一个SELECT语句
  • SELECT语句的查询结果作为虚拟表的数据

视图算法的选择

  • MERGE，将视图SQL合并到主查询SQL中
  • TEMPTABLE，将视图作临时表（中间结果)来处理
  • 一般来讲，MERGE的性能优于TEMPTABLE

无法使用MERGE的SQL

  • 聚集函数
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION,UNION ALL
  • 子查询

总结

  • 视图可以在不改变原有数据的情况下，创建虚拟表
  • 尽量使用MERGE算法，并避免无法使用MERGE的SQL

4.9 本章小结

中心思想

本章从以下两个方向探索了InnoDB数据表

  • 抽象数据结构（B+树)
  • 物理存储结构（空间、段、区、页)

lnnoDB行记录格式

  • 目前默认使用Dynamic，由Compact发展而来

image-20220327134907172

  • 行溢出数据:把超长字段放入单独开辟的数据页

索引的左侧用法

  • 联合索引可以代替最左侧字段的单独索引
  • 字符串的前缀索引可以节约硬盘空间
  • 字符串左模糊可以有效利用索引

数据约束

  • 索引约束
  • 外键约束
  • 默认值约束

视图View

  • 视图可以在不改变原有数据的情况下，创建虚拟表
  • 尽量使用MERGE算法，并避免无法使用MERGE的SQ1

理论-实践

  • 增加每页数据量:
    • 尽量做到冷热数据分离，减小表的宽度
    • 优先选择符合存储需要的最小的数据类型
  • 避免行溢出:
    • 把 BLOB或是TEXT列分离到单独的扩展表中
    • 禁止在数据库中存储图片，文件等大的二进制数据
  • 控制B+树高度:
    • 尽量控制单表数据量的大小，建议控制在500w以内

作业

你还能想到什么数据库结构设计原则?

第5章 怎么查询速度更快

本章讲解MySQL排序、随机、联合索引等原理。在丰富的实战场景下学习分析慢SQL语句的思路，并学习如何多角度优化慢SQL性能。

5.1 示例数据库的安装

source /root/sakila-db/sakila-schema.sql

source /root/sakila-db/sakila-data.sql

5.2 WHERE查询太慢，怎么办

覆盖索引

  • 覆盖索引指的是:查询语句从执行到返回结果均使用同一个索引
  • 覆盖索引可以有效减少回表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
show create table inventory;

CREATE TABLE `inventory` (
  `inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint(5) unsigned NOT NULL,
  `store_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `idx_fk_film_id` (`film_id`),
  KEY `idx_store_id_film_id` (`store_id`,`film_id`),
  CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8mb4


CREATE TABLE `inventory1` (
  `inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint(5) unsigned NOT NULL,
  `store_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `idx_fk_film_id` (`film_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8mb4

INSERT INTO inventory1 SELECT * from inventory

explain SELECT store_id, film_id FROM sakila.inventory where store_id = 1;

explain SELECT store_id, film_id FROM sakila.inventory1 where store_id = 1;

总结

  • 覆盖索引通过取消回表操作，提升查询效率
  • 若数据的查询不只使用了一个索引，则不是覆盖索引
  • 可以通过优化SQL语句或优化联合索引，来使用覆盖索引

5.3 有更合适的索引不走，怎么办

如何确定用哪条索引

  • MySQL在选取索引时，会参考索引的基数(Cardinality)
  • 基数是MySQL估算的，反映这个字段有多少种取值
  • 选取几个页算出取值的乎均值，再乘以页数，即为基数

基数的应用

  • 使用以下SQL建立测试表
1
2
3
4
5
6
7
CREATE TABLE sakila.city_1(city VARCHAR(50) NOT NULL);
INSERT INTO sakila.city_1 SELECT city FROM sakila.city;
INSERT INTO sakila.city_1 SELECT city FROM sakila.city;
INSERT INTO sakila.city_1 SELECT city FROM sakila.city;
INSERT INTO sakila.city_1 SELECT city FROM sakila.city;
INSERT INTO sakila.city_1 SELECT city FROM sakila.city;
UPDATE sakila.city_1 set city = (SELECT city from sakila.city ORDER BY RAND() LIMIT 1);
  • 增加以下前缀索引
1
2
3
4
5
6
7
8
ALTER TABLE sakila.city_1 ADD key (city(1));
ALTER TABLE sakila.city_1 ADD key (city(2));
ALTER TABLE sakila.city_1 ADD key (city(3));
ALTER TABLE sakila.city_1 ADD key (city(4));
ALTER TABLE sakila.city_1 ADD key (city(5));
ALTER TABLE sakila.city_1 ADD key (city(6));
ALTER TABLE sakila.city_1 ADD key (city(7));
ALTER TABLE sakila.city_1 ADD key (city(8));

查看各个索引的基数

1
show index from city_1;

image-20220327155414265

Cardinality 为索引区分度，第一个索引 为首字母区分 肯定小于等于26了

强制使用索引

使用force index可以强制使用索引

优化索引

analyze table可以重新统计索引信息

重新统计索引信息时，会重新计算索引的基数

总结

  • 根据索引基数，可以判断索引性能的好坏
  • 使用force index可以强制使用索引
  • analyze table可以重新统计索引信息，修复基数信息

5.4 COUNT这么慢，怎么办

MySQL count()函数

  • MySQL count()函数用来统计结果集中不为null的数据个数
  • 首先存储引擎查询出结果集
  • server层逐个结果判断是否为null，不为null则加1

show index from sakila.customer;

image-20220327160059289

count(非索引字段)

count(非索引字段)时，server层需要判断每个数据是否为nul

而且查询本身无法使用覆盖索引，理论上最慢

select count(first_name) from sakila.customer

count(索引字段)

count(索引字段)，可以覆盖，依然需要每次判断字段是否为null

select count(last_name) from sakila.customer

count(主键)，同理

select count(customer_id) from sakila.customer

count(1)

count(1)只有扫描索引树，没有解析数据行的过程，理论更快但Server层依然每次需要判断“1是否为null”

select count(1) from sakila.customer

count(*)

  • count(*)—般用来返回数据表行数
  • MylSAM的count(*)直接返回数据库中记录的数据表行数
  • 由于InnoDB支持事务，数据库中不记录数据表行数
  • MySQL专门优化了count(*)函数直接返回索引树中数据的个数

总结

  • count(非索引字段)︰无法使用覆盖索引，最慢
  • count(索引字段)∶可以使用覆盖索引但依然要取出数据判空
  • count(1):不需要取出数据，但需要判断“1是否为null”
  • count(*):经过专门优化，不需要判空，理论最快

5.5 ORDER BY这么慢，怎么办

ORDER BY步骤原理

SELECT * FROM film WHERE film_id > 80 ORDER BY title

  • 根据WHERE等条件查询
  • 将查询结果放入sort_buffer
  • 对中间结果集按照ORDER字段排序
  • 回表生成完整结果集（若需要)

1. 条件查询

给查询字段加索引,可以改善条件查询速度

SELECT * FROM film WHERE film_id > 80 ORDER BY title

2.中间结果集

  • 中间表比较小时，直接放在内存中
  • 中间表大于sort_buffer_size时，放在硬盘中
  • 若需要优化内存占用，减小sort_buffer_size
  • 若需要优化排序查询时间，增大sort_buffer_size

3. 回表生成完整结果集

  • 当行小于max_length_for_sort_data时，生成全字段中间表
  • 大于阈值时，只生成排序字段＋主键中间表，需要回表
  • 调大阈值并不一定改善效率，因为太大的结果集排序效率低(太大的结果集可能会放到硬盘里，放到硬盘排序速度还不如回表)

例如：

SELECT * FROM film WHERE film_id > 80 ORDER BY title

中间结果集为

SELECT * FROM film WHERE film_id > 80

然后用title排序就是最终结果集，但是如果中间结果集太大会将其放到硬盘里不会在内存排序

大于阈值时，只生成排序字段＋主键中间表，需要回表，虽然查询的是 * 但是执行的中间结果集

SELECT title,file_id FROM film WHERE film_id > 80

然后进行回表查询

最高效-索引覆盖

生成的中间表是不会走索引的，所以尽量避免中间表

  • 索引覆盖可以跳过生成中间结果集，直接输出查询结果
  • ORDER字段需要有索引(或在联合索引左侧)
  • 其他相关字段（条件、输出）均在上述的索引中

SELECT film_id, title FROMfilmORDER BY title

总结

  • MySQL排序一般需要生成中间结果集、排序、回表的过程
  • 索引覆盖是最高效的处理排序的方式

5.6 随机选取这么慢，怎么办

ORDER BY RAND()原理

SELECT title, description FROMfilm ORDER BY RAND()LIMIT 1;

  • 1.创建一个临时表，临时表的字段为rand . title、descriptio
  • 2.从表中取出一行，调用RAND()，将结果和数据放入临时表，以此类推
rand title description
0.25484 ACADEMY DINOSAUR A Epic Drama.. .
0.67487 ACE GOLDFINGER A Astounding Epistle…
0.19871 ADAPTATION HOLES A Astounding Reflection…
… … …
  • 3.针对临时表，将rand字段+行位置（主键)放入sort_buffer

image-20220327193740166

  • 4.对sort buffer排序，取出第一个的行位置（主键)，查询临时表

image-20220327193728327

为什么会慢?

  • SQL执行过程中出现了两次中间结果，都是全长度的
  • 仅需要一个随机结果，缺经历了不必要的排序（虽然优化过)
  • 调用了很多次RAND()

解决方法-临时方案

  • 选取数据中最大的ID和最小的ID
  • 使用RAND()随机选取一个ID
  • 直接查询此ID的数据条目

select max(film_id),min(film_id) into @M,@N from film ;

set @X= floor((@M-@N+1)*rand() + @N);

select title, description from film where film_id >= @X limit 1;

解决方法-业务方案

  • 查询数据表总数total
  • total范围内，随机选取一个数字
  • 执行以下SQL:

select title, description from film limit r, 1

总结

  • ORDER BY RAND() LIMIT 1是效率很低的随机查询方式
  • 原因主要是，上述方法有两次中间结果，还有排序过程
  • 可以通过修改SQL，选取随机主键方式，提升性能
  • 最好还是在业务中处理，尽量减少复杂SQL

5.7 带头大哥丢了，怎么办

索引下推

  • 用下列方式建表:
1
2
3
4
5
6
7
8
9
CREATE TABLE `inventory_3` (
  `inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint unsigned NOT NULL,
  `store_id` tinyint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `idx_store_id_film_id` (`store_id`, `film_id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
INSERT into inventory_3 SELECT * from inventory

SELECT * FROMinventory_3 WHERE store_id in (1,2) and film_id = 3;

store_id film_id inventory_id
1 2 3
1 5 2
2 1 9
2 3 5
3 4 4

image-20220327195107276

为什么扫描行数非常少?

  • MySQL5.6之前，需要先使用索引查询store_id in (1,2)再全部回表验证film_id = 3
  • MySQL5.6之后，如果索引中可以判断，直接使用索引过滤

松散索引扫描

SELECT film_id FROM inventory_3 WHERE film_id = 3

  • Mysql5.7

image-20220327195354192

  • Mysql8.0

image-20220327195521453

store_id film_id inventory_id
1 2 3
1 5 2
1 6 5
2 1 9
2 3 5
3 4 4
  • mysql索引按第一个字段排序，第一个字段相同按第二个字段排序，现在查找film_id=3的，譬如在上图中store_id=1时，film_id为2，5，6当判断5不满足时，没必要往后判断了。因为 store_id=1 film_id为5之后的film_id肯定大于5 不满足条件

总结

  • 联合索引可以加速一些特殊查询场景
  • 索引下推可以大大减少回表次数

5.8 明明有索引，就是不走，怎么办

尝试一下这个SQL

select * from film WHERE film id + 1 = 100

  • MySQL中，对索引字段做函数操作，优化器会放弃索引

时间函数

SELECT * FROM’rental` WHERE month(rental_date)= 5

  • 使用month()函数后，无法使用索引
  • 需要去掉month()函数，换成between

SELECT * FROM rental WHERE rental_date BETWEEN ‘2005-5-1’AND ‘2005-6-1’ OR rental_date BETWEEN ‘2006-5-1’AND ‘2006-6-1’

字符串与数字比较

  • MySQL中若出现字符串与数字比较，会将字符串转换为数字

select * from t1 where f1=6;

  • 如果t1表中f1字段为varchar类型，则此SQL相当于:

select * from t1 where CAST(f1 AS signed int)=6;

  • MySQL中若出现字符串与数字比较，会将字符串转换为数字

  • 如果字段为int类型，6和‘6’分别会是什么情况?

1
2
3
4
5
6
7
CREATE TABLE`t1` ( 
`f1 `varchar(32) NOT NULL,
`f2` int NOT NULL,
KEY `idx_f1` (`f1`),
KEY `idx_f2` (`f2`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

隐式字符编码转换

新建t2表，并执行以下语句:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE`t2`(
`f1`varchar(32) NOT NULL,
`f2`int NOT NULL,
KEY `idx_f1` (`f1`),
KEY `idx_f2` (`f2`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


select t2.* from t1, t2 where t1.f1 = t2.f1 and t1.f2=6;

MySQL中,utf8与utf8mb4字段比较时，会把utf8转为utf8mb4

  • 上一句的SQL相当于:

select t2.* from t1, t2 where t1.f1=CONVERT(t2.f1 USING utf8mb4) and t1.f2=6;

  • 解决方法:将查询条件转换为索引字段的编码

select t2.* from t1, t2 where CONVERT(t1.f1 USING utf8) = t2.f1 and t1.f2=6;

总结

  • MySQL中，对索引字段做函数操作，优化器会放弃索引
  • 这种情况可能包括:时间函数，字符串转数字，字符编码转换
  • 解决方案:时间函数转区间、数字强转字符串、高级编码转低级

5.9 分页查询这么慢，怎么办

SELECT film_id, title, description FROM film ORDER BY title LIMIT 900,10;

先执行ORDER BY，再执行LIMIT 需要先全排序

show index from film

image-20220327201537712

有一个 title索引可以用到

优化思路

  • 先想办法走索引覆盖
  • 得到所需数据的ID
  • 根据所需数据的ID，得到最终结果集

得到所需数据的主键

SELECT film_id FROM filmORDER BY title LIMIT 900,10;

原表与上面的结果连表，获取最终结果

SELECT f.film_id, f.title, fdescription FROMfilmf
INNER JOIN ( SELECT film_id FROM film ORDER BY title LIMIT 900,10 ) m ON f.film_id = m.film_id;

总结

  • 排序偏移量大时，会丢弃大量无用数据，导致效率低下
  • 可以采取先索引覆盖，再用最终ID回表的方法，优化效率

5.10 本章小结

慢查询的怀疑方向

  • 索引设计有问题
  • SQL语句有问题
  • 数据库选错索引

覆盖索引

  • 覆盖索引指的是:查询语句从执行到返回结果均使用同一个
  • 覆盖索引通过取消回表操作，提升查询效率
  • 可以通过优化SQL语句或优化联合索引，来使用覆盖索引

索引基数

  • 根据索引基数，可以判断索引性能的好坏
  • 使用force index可以强制使用索引
  • analyze table可以重新统计索引信息，修复基数信息

count()函数

  • count(非索引字段)︰无法使用覆盖索引，最慢
  • count(索引字段)∶可以使用覆盖索引但依然要取出数据判空
  • count(1):不需要取出数据，但需要判断“1是否为null”
  • count(*):经过专门优化，不需要判空，理论最快

ORDER BY

  • MySQL排序一般需要生成中间结果集、排序、回表的过程
  • 索引覆盖是最高效的处理排序的方式

随机选取

  • 临时优化:使用SQL语句选出随机主键
  • 业务优化:使用业务选出随机偏移量，再用分页查询语法

索引下推

  • MySQL5.6之后，如果索引中可以判断，直接使用索引过滤

松散索引扫描

松散索引扫描可以打破“左侧原则”，解决带头大哥去天的口题

字段做函数操作

  • MySQL中，对索引字段做函数操作，优化器会放弃索引
  • 这种情况可能包括:时间函数，字符串转数字，字符编码转换
  • 解决方案:时间函数转区间、数字强转字符串、高级编码转低级

分页查询

  • 排序偏移量大时，会丢弃大量无用数据，导致效率低下
  • 可以采取先索引覆盖，再用最终ID回表的方法，优化效率

第6章 如何处理数据更新

本章讲解InnoDB日志、锁和事务的原理，并讲透MVCC原理、间隙锁原理。并学习如何解决事务场景下的慢事务、死锁等问题。

6.1 数据库动起来之后，会发生什么

产生日志数据

  • 数据库在更新时，会产生binlog、redo log、undo log
  • binlog: server层产生的逻辑日志
  • redo log : lnnoDB产生的物理日志，保证持久化
  • undo log: InnoDB产生的逻辑日志，保证隔离性、原子性

客户端之间因为锁而互相影响

  • 客户端执行SQL时，会产生各种行锁、表锁、元数据锁
  • 一个客户端产生的锁，会干扰其他客户端SQL的执行
  • 两个客户端之间可能产生死锁

事务造成查询到的数据与磁盘上不一致

  • 客户端可能暂时看不到已经更新的数据
  • 事务可能产生隐式锁，造成性能问题

总结

  • 数据库“动起来”之后，会产生一系列性能问题
  • 需要理解日志、锁、事务的底层原理，才能应对问题

6.2 什么日志不是给人看的

MySQL日志体系

  • MySQL为了满足主从复制、事务等，有复杂的日志体系
  • Server层产生binlog，用来进行数据复制
  • InnoDB产生undo log、redo log，用来实现事务ACID
  • MySQL的日志体系不是主要不是用来看的，而是运行必要的

binlog归档日志

  • Binlog是server层产生的逻辑日志
  • 用来进行数据复制和数据传送
  • Binlog完整记录了数据库每次的数据操作，可作为数据闪回手段

undo log回滚日志

  • InnoDB自身产生的逻辑日志，用于事务回滚和展示旧版本
  • 对任何数据（缓存)的更新，都先写undo log
  • undo log位于表空间的undo segment中

SQL: 原来为 a 修改为b

UPDATE name = ‘b’→ undo: UPDATE name = ‘a’

redo log重做日志

  • InnoDB自身产生的物理日志，记录数据页的变化

  • InnoDB “日志优先于数据”，记录redo log视为数据已经更新

  • 内存中的数据更新后写redo log，数据被写入硬盘后删除

  • redolog储存在4个1GB文件中，并且循环写入

image-20220327222157542

  • write pos是当前日志写入点
  • check point是擦除点，数据被更新到硬盘时擦除
  • 当write pos追上check point时，事务无法提交，需要等待check point推进
  • 只要redo log不丢，数据就不会丢失

6.3 —条数据是如何更新的、

数据更新流程

image-20220327222628372

redo log刷盘

  • innodb_flush_log_at_trx_commit参数控制redo log刷盘
    • 0∶异步每秒刷盘
    • 1:每1个事务刷盘
    • N:每N个事务刷盘
  • 建议设置为1，保证数据安全

binlog刷盘

  • sync_binlog参数控制binlog刷盘

    • 0:自动控制刷盘
    • 1:每1个事务刷盘
    • N:每N个事务刷盘

  • 建议设置为1，保证数据安全

持久化分析

  • redo log刷盘前系统崩溃:

    • 数据丢失

  • redo log刷盘后系统崩溃:

    • 重启时会对redo log进行重放、重写内存中数据页、重写binlog

为什么redo log在binlog之前

  • redo log是系统关键节点，想到于“决断点”
  • binlog一旦写入无法撤回，因为可能已经被传送至备库

总结

  • MySQL日志主要有binlog、undo log、redo log
  • MySQL实行日志优先的策略，日志刷盘，数据就不会丢失

6.4 锁:怎样平衡功能与性能

MySQL锁的种类

  • 按照粒度分，MySQL锁可以分为全局锁、表级锁、行锁
  • 全局锁会锁住左右表，整个库无法修改
  • 表级锁分为表锁（数据锁）和元数据锁
  • 行锁会锁住数据行，分为共享锁和独占锁

全局锁

  • FTWRL (Flush tables with read lock)
  • 此命令使整个库处于只读状态
  • 主要用途是保证备份的一致性
  • 不要随意使用，杀伤性极大，要在备库使用

表锁（数据锁)

  • 命令: lock tables XXX read/write
  • 表锁是非常重的锁，在lnnoDB中使用很少

元数据锁(matadata lock)

  • 元数据指的是表的结构、字段、数据类型、索引等

  • 事务访问数据时，会自动给表加MDL读锁

  • 事务修改元数据时，会自动给表加MDL写锁

行锁

行锁也有两种类型，有很多种叫法:

  • 读锁/写锁
  • 共享锁/排他锁
  • 共享锁/独占锁
  • S锁/X锁

S锁不是不让读，而是自己要读，不让别人写

X锁不只是不让写，而是自己要写，不让别人读写

只有S锁和S锁之间可以兼容，其他均不兼容

数据更新流程

image-20220327223955734

总结

  • 锁是MySQL高效执行事务的必备基础
  • 锁会引发很多的性能问题，可能造成等待和死锁
  • 锁还会引发很多的功能问题，如脏读和不可重复读等

6.5 事务:lnnoDB的杀手锏(ACID)

事务的特性

  • 原子性(Atomicity)
  • —致性(Consistency)
  • 隔离性( lsolation)
  • 持久性(Durability)

原子性(Atomicity)

  • 事务中的操作要么全部成功，要么全部失败
  • MySQL的两阶段提交保证了事务的原子性
  • undo log用来回滚事务的更改

一致性(Consistency)

  • 事务必须使数据库从一个一致性状态变换到另外一个一致性状态
  • 锁和两阶段提交保证了一致性

隔离性(lsolation)

  • 事务不能被其他事务的操作数据所干扰
  • 多个并发事务之间要相互隔离
  • 锁和undo log实现了MySQL事务的隔离性

持久性(Durability)

  • 一个事务一旦被提交，它对数据库中数据的改变就是永久性的
  • redo log实现了MySQL事务的持久性

知识图谱

image-20220327224841641

6.6 事务: InnoDB的杀手锏(隔离级别)

隔离级别

  • 读未提交(READ UNCOMMITTED)
  • 读提交(READ COMMITTED)
  • 可重复读(REPEATABLE READ)
  • 串行化(SERIALIZABLE)

读未提交(READ UNCOMMITTED)

  • 读、写都不加锁，不隔离
  • 每次查询都查询到数据的最新版本
  • 性能最好，但是等于没有事务，很少采用

读提交(READ COMMITTED)

  • 一般读取时，读取此时已经提交的数据
  • 写数据时，加X锁，提交时释放
  • Oracle数据库的默认隔离级别

可重复读(REPEATABLE READ)

  • 一般读取时，读取本事务开始时的数据状态
  • 写数据时，加X锁，提交时释放
  • MySQL数据库的默认隔离级别

串行化(SERIALIZABLE)

  • 读加S锁、写加X锁，提交时释放
  • 对于一条数据，同时只能有一个事务进行写操作
  • 事务隔离性最高，性能太差，很少采用

总结

  • 事务是lnnoDB最核心的功能点
  • 事务也是引发性能问题最多的点
  • 读提交和可重复读是如何查询到历史版本的?需要研究

6.7 MVCC是怎么做到千人千面的

行记录的版本控制

  • 由于redo log的存在，可以从最新版本推算之前版本

image-20220327231936405

快照读（一致性非锁定读)

  • 不锁定数据的情况下，读取数据的特定历史版本
  • 版本由事务的具体需求确定:
    • 读已提交:根据每次SELECT时，其他事务的提交情况
    • 可持续读:根据事务开始时，其他事务的提交情况

当前读（一致性锁定读)

  • 读取数据的当前版本，并加锁
  • 若当前版本已经被加锁且不兼容，则阻塞等待
    • X锁: UPDATE、DELETE、SELECT 5OR UPDATE
    • S锁: SELECT IN SHARE MODE

隔离问题

  • 脏读:读到了其他事务未提交的数据
  • 不可重复读:同样的查询读到的数据内容不一样
  • 幻读:同样的查询读到了更多的数据

MySQL不同隔离级别的问题

隔离级别 脏读 不可重复读 幻读
读未提交 × × ×
读提交 √ × ×
可重复读 √ √ 部分√
串行化 √ √ √

如何解决幻读问题

  • MySQL在可重复读级别时，通过Next-Key锁解决了幻读问题
  • Next-Key锁是行锁＋间隙锁
  • 间隙锁的功能与行锁相同，只是针对间隙加锁
  • 间隙锁不分读写，也可以认为是读锁，不允许在间隙插入
  • 可重复读加锁时，将同时锁住数据及其左右间隙
id name age
1 a 10
2 b 30

image-20220327233625806

总结

  • InnoDB使用undo log实现了行记录历史查询
  • 快照读不需加行锁，属于乐观锁的一种思路
  • 当前读需加行锁，为了并发控制
  • Next-Key锁解决了可重复读下的幻读问题

6.8 间隙锁把全表都锁住了，怎么办

Next-Key Lock的加锁逻辑

  • 1.加锁时以Next-Key为基本单位
  • 2.查找过程中扫描过的范围才加锁
  • 3.唯一索引等值查询，没有间隙锁，只加行锁
  • 4.索引等值查询最右一个扫描到的不满足条件值不加行锁
  • 5.索引覆盖且只加S锁时，不锁主键索引

示例数据表

  • 表结构
1
2
3
4
5
6
7
CREATE TABLE `t3` (
`id` INT ( 11 ) NOT NULL,
`c` INT ( 11 ) DEFAULT NULL,
`d` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `c` ( `c` ) 
) ENGINE = INNODB;
  • 数据
1
2
3
4
5
6
7
8
INSERT INTO t3
VALUES
	( 0, 0, 0 ),
	( 10, 10, 10 ),	
	( 20, 20, 20 ),	
	( 30, 30, 30 ),	
	( 40, 40, 40 ),	
	( 50, 50, 50 );

image-20220327234609302

等值查询间隙锁

TxA
1
2
begin;
select * from t3 where id = 11 for update;
TxB
1
2
3
insert into t3 value(12,12,12);

select * from t3 where id = 20 for update;

首先锁(10,20]，又因为等值查询最后一个扫描20，不加行锁

主键索引

image-20220328133804684

非唯一索引等值锁

TxA
1
2
3
4
begin; 
# 走覆盖索引 lock inshare mode 读锁
select id from t3 where c = 10 lock inshare mode;

TxB
1
2
3
4
# 锁不上
select * from t3 where id = 20 for update;
# 锁上
insert into t3 value(12,12,12);

只涉及辅助索引: 首先锁(0,10]，向右扫描时锁上（10,20]

又因为等值查询最后一个扫描20，20锁解除

辅助索引c

image-20220328215155956

主键索引范围锁

TxA
1
2
3
begin;
select * from t3 where id between 20and 30 for update;

TxB
1
2
3
4
#阻塞
select * from t3 where id = 20 for update;
#不会阻塞
insert into t3 value(12,12,12);

首先等值查询20，只锁行锁

向右扫描至30，锁(20,30]

主键索引

image-20220328215347802

非唯一索引范围锁

TxA
1
2
3
begin;
select * from t3 where c between 20 and 30 for update;

首先等值查询20，不是唯一索引，锁(10,20]

向右扫描至30，锁(20,30]

向右扫描，锁(30,40)

辅助索引C

image-20220328215732064

非索引字段查询

TxA
1
2
3
begin;
select * from t3 where d=20 for update;

因为字段没有索引，走主键索引扫描，锁全表

没有索引就不知道怎么排序的，需要遍历所有数据才行

主键索引

image-20220328220035101

6.9 MySQL也有垃圾回收吗

MySQL的“垃圾回收”

  • MySQL并没有所谓的垃圾回收
  • 但是会发现数据库卡几秒，磁盘IO很高
  • 此时MySQL正在“刷脏页”

MySQL脏页的产生

  • 更新数据时，只更新了内存中的数据页，没有更新磁盘

  • 内存中数据页与磁盘中数据页不一致，称为脏页

    image-20220328220357572

什么是刷脏

  • 将内存中数据页保存至磁盘

  • 同时，删除与此页相关的redo log，推进check point

为什么要刷脏

  • 1.内存中的脏页太多，内存不足
  • 2.redo log文件写满，需要推进check point
  • 3.系统空闲，提前刷脏，预防上述情况
  • 4.MySQL关闭前，保存数据
  • 前两种会产生性能问题，导致MySQL卡住

总结

  • 目的:在可重复读的隔离级别下部分预防幻读
  • 手段:Next-Key Lock(行锁+间隙锁)
  • 特点:规则复杂，个人认为并不优雅
  • 注意:当前读时，不要查询没有索引的项目

6.10 如何改善刷脏性能

如何避免被迫刷脏

  • 1.正确告知InnoDB，服务器的硬盘性能
  • 2.配置合理的脏页比例上限
  • 3.控制“顺便刷脏”策略

服务器lO配置

  • 配置项:innodb_io_capacity
  • 用来告知服务器的硬盘性能
  • 常见IOPS:
    • 7200 rpm SATA，70 IOPS
    • 10000 rpm FC，125 IOPS
    • SSD SATA,3000～40000 IOPS

配置合理的脏页比例上限

  • 配置项:innodb_max_dirty_pages_pct
  • 当脏页比例接近此值，会加速刷脏页
  • 建议保持默认值75

控制“顺便刷脏”策略

  • 配置项:innodb_flush_neighbors
  • 传统的磁盘连续写性能最好，尽量刷连续的页
  • SSD建议设为0(8.0已经默认0)

总结

  • 刷脏可能会导致MySQL卡住，造成性能问题
  • 通过告知服务器IO性能，可以控制合理刷脏IO
  • 合理的脏页比例上限，建议保持默认

6.11 如何解决死锁问题

长事务有哪些危害?

主要危害:锁无法释放

  • 行级锁长时间无法释放，导致其他事务等待
  • 容易产生死锁
  • MDL锁hold住大量事务，造成MySQL奔溃

行级锁长时间无法释放

  • 当前读会对数据行加锁，事务提交前无法释放
  • 其他事务更新相同数据时会等待锁，造成更新性能差
解决方法
  • 调整innodb_lock_wait_timeout参数
  • 默认为50，即等待50秒还未获取锁，当前语句报错
  • 如果等待时间过长，可以适当缩短此参数

容易产生死锁

  • 长事务的锁长时间不释放，容易与其他事务产生死锁
  • 死锁指的是两个事务都依赖对方的锁释放
TxA TxB
begin; begin;
select * from t3 where id = 10 for update;
select * from t3 where id = 20 for update;
select * from t3 where id = 20 for update;
select * from t3 where id = 10 for update;
解决方法
  • 主动死锁检测:innodb_deadlock_detect(如果并发来了，事务比较多的话deadlock_detect频率特别高，影响性能，可以关闭该参数，使用锁超时等待 )
  • 发现死锁时回滚代价较小的事物
  • 默认开启

6.12 元数据锁是如何导致数据库崩溃的

MDL锁

  • 事务访问数据时，会自动给表加MDL读锁
  • 事务修改元数据时，会自动给表加MDL写锁
  • 遇到锁不兼容时，申请MDL锁的事务形成一个队列

image-20220328223657332

解决方法
  • alter table之前，查看是否有长事务还未提交
  • 查看长事务:information_schema库innodb_trx表

如何查看影响性能的锁(8.0)

  • 查看锁: performance_schema库data_locks表
  • 查看锁等待: performance_schema库data_lock_waits表
  • 查看MDL锁: performance_schema库metadata_locks表

业务上的建议

  • 控制长事务，没有必要的情况下不开启事务
  • 数据修改（当前读）尽量放在事务后部，降低锁时间

总结

  • 长事务可能会造成行锁（死锁)、MDL锁等待
  • 可以通过参数调整，降低锁影响
  • 可以通过系统表识别长事务和锁
  • 业务上尽量将加锁的操作后移，降低锁时间

6.13 本章小结

动起来的数据库有哪些问题?

  • 产生日志数据
  • 事务之间因为锁而互相影响
  • 事务之间查询到的数据不一致

MySQL日志体系

  • MySQL日志主要有binlog、 undo log、redo log
  • MySQL实行日志优先的策略，日志刷盘，数据就不会丢失

MySQL的锁

  • 按照粒度分，MySQL锁可以分为全局锁、表级锁、行锁
  • 全局锁会锁住所有表，整个库无法修改
  • 表级锁分为表锁（数据锁）和元数据锁
  • 行锁会锁住数据行，分为共享锁和独占锁

事务与MVCC

  • lnnoDB使用undo log实现了行记录历史查询
  • 快照读不需加行锁，属于乐观锁的一种思路
  • 当前读需加行锁，为了并发控制
  • Next-Key锁解决了可重复读下的幻读问题

MySQL的刷脏策略

  • 刷脏可能会导致MySQL卡住，造成性能问题
  • 通过告知服务器IO性能，可以控制合理刷脏IO
  • 合理的脏页比例上限，建议保持默认
  • “顺便刷脏”功能需在SSD下关闭

长事务有哪些危害

  • 长事务可能会造成行锁（死锁)、MDL锁等待
  • 可以通过参数调整，降低锁影响
  • 可以通过系统表识别长事务和锁
  • 业务上尽量将加锁的操作后移，降低锁时间

第7章 ORM框架原理

本章介绍ORM框架原理，了解ORM的一般架构和中间层设计思路以及ORM框架使用过程中如何规避SQL缺陷问题。

7.1 什么是ORM框架

Object Relational Mapping

  • 对象(Object)
  • 关系(Relational)
  • 映射(Mapping)
  • 对象与关系型数据库的映射关系

著名的ORM框架

  • Java: MyBatis,Hibernate
  • Python: Django,SQLALchemy
  • Go: GORM

ORM框架的意义

  • 将数据库操作与程序编码解耦，提高开发效率
  • 自动拼装生成SQL语句，避免SQL注入风险
  • 自动管理数据库连接、自动重试、自动回滚等操作
  • 自动管理事务

数据库操作与程序编码解耦

image-20220328224527263

避免SQL注入风险

image-20220328224612775

自动管理数据库连接

image-20220328224736245

自动管理事务

image-20220328224754142

总结

  • ORM框架的作用是程序对象与关系型数据库之间的映射
  • ORM框架可以大大降低程序员的代码开发工作量

7.2 ORM框架是如何设计的

复习:经典软件架构

  • 分层架构
  • 事件驱动架构
  • 管道-过滤器架构
  • 微核架构

ORM框架-架构层次

  • 接口层:向上支持程序调用
  • 处理层:参数映射→SQL生成→SQL执行→结果处理
  • 支撑层:事务管理、连接池管理
  • 连接层:数据库连接驱动

ORM框架-架构设计

image-20220328225010395

总结

  • ORM框架体现了软件架构设计的经典模式
  • ORM框架一般由接口层、处理层、支撑层、连接层组成
  • ORM框架中有很多优秀的代码，值得阅读

7.3 ORM框架有哪些常见问题

ORM与DAO是什么关系?

  • DAO这个概念在Java中比较常见，一般是ORM生成的
  • DAO(Data Access Object)是ORM框架的接口层
  • DAO并不等于ORM框架，只是框架的一部分

开发同学和DBA的误会在哪里?

  • 在ORM框架的加持下，SQL不是开发同学直接写的
  • 开发同学往往更注重业务逻辑，忽视了SQL本身的性能
  • 开发同学和DBA都没有写SQL，出了问题都不背锅

如何定位出问题的SQL?

  • 业务应用:根据代码推断、查看相关日志
  • 数据库:查看慢日志
  • 网络:监听数据库端口，解析TCP报文

总结

  • ORM框架执行的SQL往往不是工程师写的
  • 遇到SQL性能问题时可以从应用、网络、数据库定位
  • 性能差的SQL一般可以通过ORM的自定义SQL解决

7.4 本章小结

ORM框架

  • 对象关系映射
  • 降低程序员的代码开发工作量
  • 代码更安全、更易维护更容易

ORM框架的问题

  • ORM框架执行的SQL往往不是工程师写的
  • 遇到SQL性能问题时可以从应用、网络、数据库定位
  • 性能差的SQL一般可以通过ORM的自定义SQL解决

第8章 怎么给数据上保险

本章学习不同的备份种类，mysqldump和ibbackup的备份逻辑与使用方法等，使同学对MySQL备份有全局认识，可以通过所学知识为公司的数据库设计备份恢复方案了。

8.1 数据库有哪些种类的备份

备份的分类维度

  • 备份时数据库的状态
  • 备份文件的格式
  • 备份的内容

备份时数据库的状态

  • Hot Backup (热备):正常运行中直接备份
  • Cold Backup (冷备)∶完全停止后备份
  • Warm Backup(温备):数据库只读

备份文件的格式

  • 逻辑备份:输出文本或SQL语句
  • 物理备份（裸文件)∶备份数据库底层文件

备份的内容

  • 完全备份:备份完整数据
  • 增量备份:备份数据差异
  • 日志备份:备份Binlog

工具举例

  • mysqldump:逻辑、热、全量备份
  • xtrabackup:物理、热、全量+增量备份

总结

  • 备份方式的分类维度有:状态、格式、内容
  • 备份方式并没有绝对的好坏，只是用途不同

8.2 如何使用OUTFILE命令进行备份

OUTFILE

  • MySQL原生的SQL指令
  • 最原始的逻辑备份方式
  • 备份的功能和效果取决于如何写SQL语句

OUTFILE的使用方式

  • 首先查询MySQL的导出路径
  • show variables like ‘%secure%’;

默认 空格分隔

select * into outfile ‘/var/lib/mysql-files/t1-out’ from t1;

select * into outfile ‘ /var/lib/mysql-files/t2-out1’ fields terminated by ‘,’ from t2;

OUTFILE的注意事项

  • 在lnnoDB事务下，可以做到一致性视图
  • 修改分隔符:fields terminated by
  • 修改换行符:lines terminated by

OUTFILE的缺陷

  • 输出的文本比较简略
  • 很难进行还原，现在往往用来简单导出数据

总结

  • OUTFILE是最简单且原生的数据备份工具
  • 使用简单、灵活性高
  • 不适合数据还原场景，基本用来导出数据

8.3 何使用mysqldump进行备份

OUTFILE如何改进?

  • 自动发SELECT语句，不需要手动发送（OUTFILE需要一个表发送一条sql）
  • 自动开始事务
  • 输出INSERT语句，可以直接用于还原

mysqldump

  • 非常常用的MySQL逻辑备份工具
  • MySQL Server自带
  • 输出的备份内容为SQL语句，平衡了阅读和还原
  • SQL语句占空间较小（不需要redo undo B+树等）

mysqldump原理

  • mysqldump使用以下语句对数据进行备份:

SELECT SQL_NO_CACHE FROMt;

  • SQL_NO_CACHE查询出的数据不会进入SQL缓存

mysqldump使用方法

  • mysqldump使用以下语句对数据进行备份:

mysqldump -uroot -p123456 –databases d1 –single-transaction > test.sql;

  • 直接执行导出的sql文件即可进行还原

source test.sql;

mysqldump注意事项

  • –single-transaction:在RR级别下进行(InnoDB)
  • –lock-all-tables:使用FTWRL锁所有表(MylSAM)
  • –lock-tables:使用READ LOCAL锁当前库的表（MylSAM)
  • –all-databases:备份所有库

mysqldump缺点

  • 导出逻辑数据，备份较慢
  • 还原需要执行sql。速度也比较慢(需要走mysql的解析，执行 等操作，不如物理备份)

总结

  • mysqldump使用简单、可以热备
  • SQL文件可以直接执行、占空间小、可以阅读

8.4 如何使用mysqldump+ binlog增量备份

mysqldump增量备份思路

  • binlog忠实记录了MySQL数据的变化
  • mysqldump全量备份之后，可以用binlog作为增量
  • mysqldump全量备份时，切换新的binlog文件(如果不这样做，不知道分割点是哪里)
  • 从零还原时，采用全量还原+binlog还原

步骤1.mysqldump全量备份

  • mysqldump使用以下语句对数据进行全量备份:

mysqldump -uroot -p123456–databases d1 –single-transaction –flush-logs –master-data=2 > test.sql;

  • –flush-logs:备份后切换binlog文件
  • –master-data=2:记录切换后的binlog文件名

步骤2.binlog增量备份

  • 需要增量备份时，切换binlog文件

mysqladmin -uroot -p123456 flush-logs

  • 将所有新增的binglog文件备份

步骤3.还原

  • 首先恢复旧的全量备份

source test.sql;

  • 然后将binlog增量还原至数据库

mysqlbinlog MysQL-bin.000002 … | mysql -u root -p123456

总结

  • mysqldump+binlog可以有效对数据进行全量+增量备份
  • 两个组件各司其职，是工程实践中的经典作法
  • 理论上来说，可以将数据库恢复至binlog的任意时刻
  • 缺点是操作起来较为复杂

8.5 如何使用XtraBackup物理备份

为什么需要物理备份

  • 直接备份InnoDB底层数据文件
  • 导出不需要转换，速度快
  • 工作时对数据库的压力较小
  • 更容易实现增量备份

直接拷贝裸文件可行吗?

  • 理论上可行，但有很多问题:

  • 要同时备份frm文件、ibd文件、binog文件、redo log文件等

  • 在不同版本的数据库和操作系统上还原可能有兼容问题

  • 必须冷备份，影响业务

    如何实现物理+热+全量备份?

  • 思路:利用redo log，备份ibd文件+备份期间的redo log

  • 1.启动redo log监听线程，开始收集redo log

  • 2拷贝ibd数据文件

  • 3.停止收集redo log

  • 4.加FTWRL锁拷贝元数据frm

如何实现物理+热+增量备份?

  • 思路:与全量级别相同
  • 如何确定增量:根据每个页的LSN号，确定变化的页

如何实现物理还原?

  • 思路: mysqld crash奔溃恢复流程相似
  • 还原ibd文件，重放redo log

ibbackup

  • 现名MySQL Enterprise Backup,InnoDB官方出品
  • 实现了上述的功能(全量备份,增量备份,物理还原)，性能优秀

image-20220329094744051

最大的缺点 Enterprise 企业版 商业版 收费的…….

XtraBackup

  • Percona公司开发的开源版本，实现ibbackup所有功能
  • XtraBackup 8.0→MySQL 8.0
  • XtraBackup 2.4→MySQL 5.1,5.5,5.6,5.7

XtraBackup安装方法

下载

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm

安装
1
rpm -ivh --nodeps --force percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm

XtraBackup全量使用方法

备份:

innobackupex –user=root –password=123456 bakdir/

数据还原（停掉mysqld)

innobackupex –copy-back bakdir/XXXX-XX-XX/

XtraBackup增量使用方法

增量备份:

innobackupex –user=root –password=123456 –incremental bakdir/ –incremental-basedir=’/bakdir/XXXX-XX-XX/‘

增量备份合并至全量备份

innobackupex –apply-log bakdir/XXXX-XX-XX/ –incremental-dir=bakdir/YYYY-YY-YY/

总结

  • 物理备份是一种高效的备份方式
  • XtraBackup采用了备份ibd+备份期间redo log方式
  • XtraBackup是最常用的MySQL物理备份工具
  • 物理备份的缺点是备份文件无法直接阅读

8.6 MySQL备份工具如何指导我们的创新

mylvmbackup

  • 物理、温备
  • 利用LVM(Logical Volume Manager)逻辑卷管理器
  • 直接备份磁盘数据(类似于直接整个快照 )

mydumper

  • 跟mysqldump类似的工具
  • 实现了多线程并发的备份还原
  • 速度更快
  • 对Mysql性能影响更大

Zmanda Recovery Manager

  • 功能强大的备份恢复管理工具
  • 集成了多种备份工具
  • 集成binlog分析功能

总结

  • 本节介绍的3种备份工具都是改良了原型
  • mylvmbackup:使用LVM备份磁盘
  • mydumper:多线程备份
  • ZRM:备份管理工具

8.7 如何防患于未然

  • 可以备份，但是希望永远不需要还原

权限隔离

  • 给业务应用分配的账号只给DML权限
  • 开发同学使用只读账号
  • DBA平时使用时使用只读账号，特殊操作时切换账号

SQL审计

  • DBA在开发环境审计即将上线的SQL语句
  • 开发同学修改在线数据，提交给DBA执行
  • Inception自动审核工具

伪删表

  • 删表之前将表改名，观察业务是否受影响
  • 不直接删表，给表明加特殊后缀，用脚本删除

完备流程

  • 上线之前备份数据
  • 准备生产环境事故预案

总结

  • 最小权限原则，收缩权限
  • 使用自动或手动的手段审计SQL语句
  • 删表前伪删除，再使用自动工具删表
  • 完善上线与运维流程

8.8 本章小结

数据库有哪些种类的备份

  • 备份方式的分类维度有:状态、格式、内容

OUTFILE命令

  • OUTFILE是最简单且原生的数据备份工具
  • 使用简单、灵活性高
  • 不适合数据还原场景，基本用来导出数据

mysqldump

  • mysqldump使用简单、可以热备、可以增量备份
  • SQL文件可以直接执行、占空间小、可以阅读
  • 备份、还原性能不如物理备份

XtraBackup

  • 物理备份是一种高效的备份方式
  • XtraBackup采用了备份ibd+备份期间redo log方式
  • XtraBackup是最常用的MySQL物理备份工具
  • 物理备份的缺点是备份文件无法直接阅读

其他备份工具

  • mylvmbackup:使用LVM备份磁盘
  • mydumper:多线程备份
  • ZRM:备份管理工具

防患于未然

  • 最小权限原则，收缩权限
  • 使用自动或手动的手段审计SQL语句
  • 删表前伪删除，再使用自动工具删表
  • 完善上线与运维流程

第9章 搭建“三高”架构的基础–如何实现数据冗余

本章讲解MySQL主备复制的原理、配置方法、binlog格式对主备复制的影响等。本章还研究了备库延迟的原因以及备库延迟的改善方法。最后讲解了MySQL最简单的高可用架构：主主架构。

9.1 三高架构之路

什么是“三高”

  • 高并发:同时处理的事务数高
  • 高性能:事务/SQL的执行速度高
  • 高可用:系统可用时间高

为什么不直接讲“三高”

  • “三高”只是目的，并不是手段，手段有:
    • 复制，扩展，切换

复制

  • 目的:数据冗余
  • 手段: binlog传送
  • 收获:并发量提升、可用性提升
  • 问题:占用更多硬件资源
扩展
  • 目的:扩展数据库容量
  • 手段:数据分片分库、分表
  • 收获:性能、并发量的提升
  • 问题:可能降低可用性
切换
  • 目的:提高可用性
  • 手段:主从身份切换
  • 收获:并发量的提升
  • 问题:丢失切换时期数据

“三高”的实现

  • 高并发:通过复制和拓展，将数据分散至多节点
  • 高性能:复制提升速度，拓展提升容量
  • 高可用:节点间身份切换保证随时可用

“三高”章节

  • 复制:《怎样实现数据冗余?》

  • 扩展:《数据库容量不够怎么办?》

  • 切换:《数据库经常宕机怎么办?》

9.2 复制有哪些类型

复制的基本原理

image-20220329201439544

复制的类型

  • 根据复制同步的类型，复制可以分为:
  • 异步复制(Asynchronous Replication)
  • 半同步复制(Semisynchronous Replication)
  • 组复制(Group Replication)

异步复制(Asynchronous Replication)

image-20220329201812789

  • 原理简单
  • 对网络延迟要求较小
  • 不能保证日志被传送到了备库，可能丢失数据

半同步复制(Semisynchronous Replication

image-20220329202034942

  • 原理简单
  • 对网络延迟有一定要求，最好在同—机房
  • 可以保证日志被传送到了备库，不易丢失数据
  • rpl_semi_sync_master_timeout参数可以调整脱扣时间（时间超过默认10s还没响应，退化为异步复制）

组复制(Group Replication)

image-20220329202349768

组复制(Group Replication)

  • 原理比较复杂
  • 需要依赖共识算法（Paxos）
  • 实际应用较少
  • 是数据库走向原生分布式的示范

总结

  • 按照同步类型，可以分为异步、半同步、组复制
  • 半同步是兼顾数据安全与效率的较好选择

9.3 主从复制配置实战

Mysql主从原理图

1. 创建两个Mysql实例

Docker安装模拟MySQL集群（和本地安装没啥区别，我为了方便直接Docerk了）

1、下载mysql镜像

1
2
docker pull mysql:5.7

2、创建Master实例并启动

1
2
3
4
5
6
7
8
9
10
11
12
13
docker run -p 3306:3306 --name mysql-master \
-v /mydata/mysql/master/log:/var/log/mysql \
-v /mydata/mysql/master/data:/var/lib/mysql \
-v /mydata/mysql/master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7 
参数说明
	-p 3306:3306：将容器的3306端口映射到主机的3306端口
	-v /mydata/mysql/master/conf:/etc/mysql：将配置文件夹挂在到主机
	-v /mydata/mysql/master/log:/var/log/mysql：将日志文件夹挂载到主机
	-v /mydata/mysql/master/data:/var/lib/mysql/：将配置文件夹挂载到主机
	-e MYSQL_ROOT_PASSWORD=root：初始化root用户的密码

修改master基本配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

vim /mydata/mysql/master/conf/my.cnf

[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

注意：skip-name-resolve一定要加，不然连接mysql会超级慢,加上会跳过域名解析

skip-character-set-client-handshake 为跳过字符编码客户端与服务端的握手过程

修改后重启Mysql服务： **docker restart mysql-master **

3、创建Slave实例并启动

1
2
3
4
5
6
7
docker run -p 3316:3306 --name mysql-slaver-01 \
-v /mydata/mysql/slaver/log:/var/log/mysql \
-v /mydata/mysql/slaver/data:/var/lib/mysql \
-v /mydata/mysql/slaver/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7

修改Slave基本配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
修改slave基本配置
vim /mydata/mysql/slaver/conf/my.cnf

[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

修改后重启Mysql服务： **docker restart mysql-slaver-01 **

2. 设置好主从的配置文件

修改master基本配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

vim /mydata/mysql/master/conf/my.cnf(还是之前master配置文件)

server_id=1
log-bin=mysql-bin
read-only=0

#需要同步的数据库！！！改成自己的
binlog-do-db=gmall_ums
binlog-do-db=gmall_pms
binlog-do-db=gmall_oms
binlog-do-db=gmall_sms
binlog-do-db=gmall_cms

#需要忽略的数据库（Mysql自带的）
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

重启master

修改slaver基本配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
vim /mydata/mysql/slaver/conf/my.cnf(还是之前slaver配置文件)



server_id=2
log-bin=mysql-bin
read-only=1

binlog-do-db=gmall_ums
binlog-do-db=gmall_pms
binlog-do-db=gmall_oms
binlog-do-db=gmall_sms
binlog-do-db=gmall_cms


replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

重启slaver

3. 主从环境搭建完成

3.1 为master授权用户来他的同步数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1、进入master容器
docker exec -it mysql /bin/bash

2、进入mysql内部 （mysql –uroot -p）

   1）、授权root可以远程访问（ 与主从无关，为了方便我们远程连接mysql  @后面应该为ip地址，现在是%表示所有ip都可以用root用户访问）
grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;

flush privileges;

   2）、添加用来同步的用户
       GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';
	   
3、查看master状态
   show master status\G;

3.2 配置slaver同步master数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
1、进入slaver容器
docker exec -it mysql-slaver-01 /bin/bash

2、进入mysql内部（mysql –uroot -p）

   1）、授权root可以远程访问（ 主从无关，为了方便我们远程连接mysql）
grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;

flush privileges;


# master_log_file 为上图主库的bin-log日志 mysql-bin.000001，master_log_pos应该填上图中 889 因为主库现在位置只有889，所以填0，也可以自动确定889
   2）、设置主库连接
change master to master_host='192.168.159.128',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=0,master_port=3306;

  3）、启动从库同步
     start slave;
  4）、查看从库状态(下图)
     show slave status\G;

至此主从配置完成；

总结：
1）、主从数据库在自己配置文件中声明需要同步哪个数据库，忽略哪个数据库等信息。并且server-id不能一样
2）、主库授权某个账号密码来同步自己的数据
3）、从库使用这个账号密码连接主库来同步数据

4. 主从环境测试完成

在主数据库中建库、导入测试表、 以及测试数据

1
2
3
4
5
6
CREATE DATABASE /*!32312 IF NOT EXISTS*/`gmall_cms` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`gmall_pms` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`gmall_oms` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`gmall_ums` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`gmall_sms` /*!40100 DEFAULT CHARACTER SET utf8 */;

从库跟着同步

5. 使用Sharding-jdbc完成读写分离配置

1
2
3
4
5
6
https://mvnrepository.com/artifact/io.shardingjdbc/sharding-jdbc-core -->
    <dependency>
        <groupId>io.shardingjdbc</groupId>
        <artifactId>sharding-jdbc-core</artifactId>
        <version>2.0.3</version>
    </dependency>

构建Sharding-jdbc.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
dataSources:
  db_master: !!com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://39.97.251.40:3306/gmall_pms?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: root
  db_slave: !!com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://39.97.251.40:3316/gmall_pms?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: root
masterSlaveRule:
  name: db_ms
  masterDataSourceName: db_master
  slaveDataSourceNames: [db_slave]

  ###注意，这份文档直接从官网复制的。!!后面是DataSource的实现类全类名不能省略。
  ###多个slave加多份db_slaveXX配置即可，在slaveDataSourceNames列举这些slaver


配置类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import io.shardingjdbc.core.api.MasterSlaveDataSourceFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.util.ResourceUtils;

import javax.sql.DataSource;
import java.io.File;

import java.io.IOException;
import java.sql.SQLException;

/**
 * 这是一个配置类
 *
 * SpringBoot引入某个场景，这个场景的组件就会自动配置好。
 * 1）、
 */
@Configuration
public class PmsDataSourceConfig {

  @Bean
    public DataSource dataSource() throws IOException, SQLException {

        File file = ResourceUtils.getFile("classpath:sharding-jdbc.yml");
        DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(file);
      return dataSource;
    }
}

测试

因为代码涉及到controller Service Mapper 所以都不写了，就写一点伪代码

1
2
3
4
 //测试增删改在主库，查在从库
Brand brand = new Brand();
 brand.setName("哈哈哈");
 brandService.save(brand);

数据库中主从库都有数据

手动修改数据库从库53号数据

1
2
Brand byId=brandService.getById(53);
 System.out.println(byId);

查询出来从库数据

9.4 怎样使得复制的配置更方便

思路

  • LOG_FILE+LOG_POS不方便
  • 根本原因是: 备库不知道应该从哪个log开始复制
  • 思路: 给每个事务配一个唯一ID

GTID(Global Transaction ldentifier)

  • MySQL 5.6版本引入了GTID
  • GTID = server_uuid:gno
  • server_uuid:节点的UUID
  • gno事务流水号（回滚会收回)

如何启用GTID

  • gtid_mode=on
  • enforce_gtid_consistency = on

使用GTID进行主从复制

  • master_auto_position 自动根据 GTID 选取从哪个位置开始复制
  • 主从数据库密码一样，所以不写也行
1
CHANGE MASTER TO MASTER_HOST='192.168.57.144',MASTER_USER='root',master_auto_position=1;

总结

  • GTID可以给事务分配全局唯一ID
  • GTID方便了主从复制的配置，推荐打开
  • GTID对主从切换、故障恢复也有很大意义

9.5 为什么binlog格式会影响复制

statement格式的binlog

  • 5.0之前的MySQL默认使用了statement格式的binlog
  • binlog记录的是SQL语句原文
  • 由于主备库对于SQL的执行不一致，可能有数据安全风险

show variables like ‘ binlog_format’;

set binlog_format=statement;

ROW格式的binlog

  • 不记录SQL语句原文
  • 记录数据行的变化
  • 不是物理日志，还是逻辑日志
  • 占空间较大

mixed格式的binlog

  • 两种格式混合使用
  • 有数据风险的语句使用ROW
  • 无风险的使用statement

基于语句或行的复制

  • 基于语句的复制=statement
  • 基于行的复制=row

总结

  • statement格式记录了实际执行的SQL语句
  • ROW格式按照整行记录了数据的变化
  • 推荐直接使用ROW格式

9.6 备库延迟太大，怎么办

备库延迟的原因

  • log传送开销较小，主要是消费relay log耗时
  • 备库性能不如主库
  • 备库承担了很多分析SQL
  • 主库的长事务未提交(主库commit了 备库一直收不到)

处理方法

  • 主备使用相同配置的机器
  • 备库关闭log实时落盘
  • 增加从库数量，应对分析SQL
  • binlog传送至大数据系统，供分析
  • 大事务一分多

依然存在的问题

  • 备库对硬件资源利用天然不如主库
  • 备库单线程执行（备库日志重放是单线程）
  • 主库多线程执行（主库是并发执行SQL语句，很多线程写binlog）

image-20220329201439544

总结

  • 备库延迟的主要原因是备库执行慢
  • 提升性能:升级备库硬件、关闭log实时落盘
  • 减少负担:增加数量、使用其他组件
  • 依然存在备库单线程的问题

9.7 并行复制

并行复制的思路

  • 分配relaylog是难点，因为如果前一条是插入，后一条是修改，这两条语句明显不能并行执行

image-20220329220534105

  • 思路：可以按行分发，按表分发

image-20220329220749638

MySQL 5.6并行复制

  • MySQL 5.6使用按库并行的策略
  • 优点:分发选择快、支持各种log格式
  • 缺点:库粒度太大，很难负载平衡
  • slave-parallel-type = DATABASE

MySQL 5.7使用按事务组并行的策略

  • binlog刷盘其实是两步动作
  • 先把 binlog 从 binlog cache 中写到内存的binlog文件
  • 调用fsync持久化至磁盘

image-20220329222409628

image-20220329222446306

  • binlog_group_commit_sync_delay: 延迟多少微秒后才调用fsync
  • binlog _group_commit_sync_no_delay_count: 累积多少次以后才调用fsync
  • 两个条件是或的关系
  • MySQL 5.7使用按事务组并行的策略
  • 同时处于prepare状态的事务，在备库执行时是可以并行的
  • slave-parallel-type = LOGICAL_CLOCK

MySQL 5.7.22并行复制

  • binlog-transaction-dependency-tracking参数:
    • COMMIT_ORDER: 按事务组并行(5.7)
    • WRITESET: 没有修改相同行的事务可以并行
    • WRITESET_SESSION: 同一个线程先后执行的两个事务不能并行

总结

  • 备库延迟的根本原因是主库多线程，备库单线程
  • 并行复制思路:按表复制、按行复制
  • 5.7借鉴了事务组的思想，将同一个事务组的relay log 并行

9.8 如何在备库读到最新数据

如何判断备库已经追上

  • 强制延时（保证备库同步完成再给响应）
  • seconds_behind_master=0（主从延时为0时）
  • 对比binlog执行位点（主库写到binlog哪个位置，从库复制到哪个位置，一致了即可）
  • 对比GTID执行情况（查看主库的GTID在从库上执行好了没）

备库延迟理论上无法消灭

  • binglog传送、中继日志重放需要时间
  • 理论上，备库延迟只能减小，无法消灭
  • 在备库读取数据时永远面临数据延迟问题

判断具体事务是否已经重放

  • 等待binlog位点

select master_pos_wait(file,pos [, timeout])

  • 等待GTID (5.7.6之后可以返回每次的GTID)

select wait_for_executed_gtid_set(gtid_set,1);

总结

  • 可以通过对比执行位点的方法判断从库延时
  • 从库延时可能无法消灭
  • 可以通过binlog位点和GTID等待方法等待具体事务

9.9 怎样实现最简单的高可用架构

主-主复制架构

  • 两个节点均为Master
  • 两个节点互为Slave
  • 一个节点出现故障时，无需切换

image-20220329224416035

数据冲突问题
  • 两边插入相同ID时，可能出现冲突
  • 两边约定好插入不同ID
  • 只写一个主，另一个只读
  • 有切换过快的数据丢失问题
客户端切换
  • 应用自己切换比较麻烦
  • 使用keepalived等手段可以完成自动切换
循环复制
  • 理论有此问题(A库复制数据到B库，B库又复制到A，然后A又到B)
  • 未开GTID:使用serverlD过滤
  • GTID:天然避免

总结

  • 主主架构是最简单的高可用架构
  • 存在数据冲突、客户端切换不便等问题
  • 切换时的问题解决方法，后面章节详细介绍

9.10 本章小结

复制有哪些类型

  • 按照同步类型，可以分为异步、半同步、组复制
  • 半同步是兼顾数据安全与效率的较好选择

GTID(Global Transaction ldentifier)

  • GTID可以给事务分配全局唯一ID
  • GTID方便了主从复制的配置，推荐打开
  • GTID对主从切换、故障恢复也有很大意义

binlog格式

  • statement格式记录了实际执行的SQL语句
  • ROW格式按照整行记录了数据的变化
  • 推荐直接使用ROW格式

备库延迟

  • 提升性能:设计备库硬件、关闭log实时落盘
  • 减少负担:增加数量、使用其他组件

并行复制

  • 备库延迟的根本原因是主库多线程，备库单线程
  • 并行复制思路:按表复制、按行复制
  • 5.7借鉴了事务组的思想，将同一个事务组的relay log并行
  • 5.7.22引入了没有修改相同行的事务并行的方法

判断主备同步情况

  • 可以通过对比执行位点的方法判断从库延时
  • 从库延时可能无法消灭
  • 可以通过binlog位点和GTID等待方法等待具体事务

主-主复制架构

  • 主主架构是最简单的高可用架构
  • 存在数据冲突、客户端切换不便等问题

第10章 数据库高并发、高性能的基本保证–如何解决数据库超大容量不够问题

本章学习MySQL的可拓展性（容量和性能的拓展），首先介绍分区表的原理、特点和注意事项。然后学习分库分表的原理，重点学习MyCat的升级版：dble的安装与使用。让大家对MySQL分库分表的分类、目的、手段等有清晰的认识。

10.1 怎样最简单地扩展容量

什么是分区表

  • 将InnoDB的一个表分为多个表
  • Server层依然看做一个表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `t` ( `ftime` datetime NOT NULL, `c` INT ( 11 ) DEFAULT NULL, KEY ( `ftime` ) ) ENGINE = INNODB DEFAULT CHARSET = latin1 PARTITION BY RANGE (
	YEAR ( ftime ))(
	PARTITION p_2030
	VALUES
		LESS THAN ( 2030 ) ENGINE = INNODB,
		PARTITION p_2031
	VALUES
		LESS THAN ( 2031 ) ENGINE = INNODB,
		PARTITION p_2032
	VALUES
		LESS THAN ( 2032 ) ENGINE = INNODB,
		PARTITION p_others
	VALUES
	LESS THAN MAXVALUE ENGINE = INNODB 
	);

image-20220329230001350

指定分区位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE `t` ( `ftime` datetime NOT NULL, `c` INT ( 11 ) DEFAULT NULL, KEY ( `ftime` ) ) ENGINE = INNODB DEFAULT CHARSET = latin1 PARTITION BY RANGE (
	YEAR ( ftime ))(
	PARTITION p_2030
	VALUES
		LESS THAN ( 2030 ) ENGINE = INNODB,
        INDEX DIRECTORY = '/data1'
        DATA DIRECTORY = '/data2',
		PARTITION p_2031
	VALUES
		LESS THAN ( 2031 ) ENGINE = INNODB,
		PARTITION p_2032
	VALUES
		LESS THAN ( 2032 ) ENGINE = INNODB,
		PARTITION p_others
	VALUES
	LESS THAN MAXVALUE ENGINE = INNODB 
	);

分区方式

  • 范围分区
  • Hash分区
  • List分区

分区表的优势

  • 降低B+树的层级，搜索加速
  • 将一个数据表物理上分为多个文件，方便处理

分区表的缺陷

  • 第一次需要访问所有分区
  • 共用MDL锁
  • 分区之后，所有的分区依然位于同一节点(还是同一个server，如果server层是瓶颈，则并不能做出什么贡献)

总结

  • 分区表可以优化单节点容量、增强分区之间隔离
  • 第一次访问需要打开所有ibd，可能达到上限
  • 可以通过存储在不同的磁盘上提高容量

10.2 为什么要分库分表

分表

  • 垂直:按照字段分表，一般分为冷热
  • 水平:按照行分表，常用范围、Hash切分
  • 水平分表类似于分区表，但Server层也分了
垂直分表

image-20220329231110089

水平分表

image-20220329231119979

分库

  • 垂直:将数据表分散在多个数据库或者多个节点中
  • 水平:将所有表水平拆分，每个数据库结构相同
垂直分库

image-20220329231445118

水平分库

image-20220329231513164

分库分表的优点

  • 增加隔离性
  • 提升容量与并发性能
  • 水平分表类似于分区表，但Server层也分了
分库分表的缺点
  • 部分失效可能性成倍增加(如果垂直分了表，一块坏了，整个表用不了了)
  • 无法使用单点事务（垂直分表之后无法锁住一行记录，还需要XA分布式事务 ）
  • 垂直切分后无法JOIN
  • 范围查询困难

分库分表后的使用方式

  • 业务特殊处理（dao层自己拼SQL，银行柜台只支持查询3年内的交易流水）
  • 业务应用使用中间层（利用java的mybatis等实现）
  • 使用分库分表中间件

总结

  • 分库分表可以提升数据库性能
  • 分库分表使得数据的使用方法更加复杂、数据丢失的可能性增加

10.3 dble比MyCat强在哪

分库分表中间件的原理

  • 分析SQL语句
  • 根据SQL语义，将SQL拆分成多个，发送至数据节点
  • 将多个数据节点的结果聚集，返回客户端

dble

  • 高性能、高可用的MySQL分库分表中间件
  • 上海爱可生公司开发，国货之光
  • 完全开源
  • 基于开源项目MyCat

dble架构设计

image-20220330063425766

dble对MyCat做的增强

  • 缺陷修复
  • 实现改进
  • 功能增强
  • 功能裁减（异构数据库的支持）

dble的基础概念

  • schema:虚拟数据库（不同于传统的schema)
  • shardingTable :虚拟表（被拆分的表)
  • shardingNode :虚拟节点
  • dbGroup:实际的MySQL集群
  • database :实际的database

dble表拆分图示

image-20220330064045733

dble表的类型

  • 全局表:每个节点上有保存表的完整数据
  • 拆分表:被拆分的表，存入不同节点
  • 非拆分表:不拆分的表，存在单一节点

总结

  • dble是一个高性能、易用的分库分表中间件
  • dble基于MyCat，并做了改进
  • dble在功能上以水平分表为主

10.4 如何安装dble

image-20220330064404556

  • dble 用Java写的，需要有Java环境

下载地址：

https://github.com/actiontech/dble/releases

10.5 如何配置dble

  • 下载之后解压，目录结构如下：

image-20220330064912218

  • 主要使用的是conf文件下

image-20220330065056350

  • cluster_template.cnf, dble集群的配置

  • sharding_template.xml 分片的配置规则

  • user_template.xml dble的用户胚子

  • db_template.xml 数据库配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?xml version="1.0"?>

<dble:db xmlns:dble="http://dble.cloud/" version="4.0">
    <dbGroup name="dbGroup1" rwSplitMode="2" delayThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <dbInstance name="instanceM1" url="192.168.0.89:3306" user="root" password="123456" maxCon="1000" minCon="10"
                    primary="true" readWeight="1" id="xx1">
            <property name="testOnCreate">true</property>
        </dbInstance>
    </dbGroup>

    <dbGroup name="dbGroup2" rwSplitMode="0" delayThreshold="100" disableHA="true">
        <heartbeat errorRetryCount="1" timeout="10">show slave status</heartbeat>
        <dbInstance name="instanceM2" url="192.168.0.90:3306" user="root" password="123456" maxCon="1000" minCon="10"
                    primary="true">
            <property name="testOnCreate">true</property>
        </dbInstance>
    </dbGroup>
</dble:db>

10.6 如何使用dble

bin/dble start

mysql -h ‘ 127.0.0.1’ -uman1 -P9066 -p654321

10.7 怎样提高分库分表架构的可靠性

复制与分库分表架构的结合

image-20220330070717153

使用dble进行读写分离

原理
  • dble分析SQL语义
  • 将写语句发送给主节点
  • 将读语句发送给从节点

配置

  • rwSplitMode=0:直接分发到主实例
  • rwSplitMode=1:读操作必须在所有从实例中均衡
  • rwSplitMode=2:读操作在所有实例中均衡
  • rwSplitMode=3:读操作尽量在所有从实例中均衡

总结

  • 复制可以提高分库分表架构下的数据可靠性
  • dble可以基于SQL语义进行分库分表转发

10.8 分库分表之后性能反而下降，怎么办

查询语句中尽可能带有拆分字段

  • dble根据拆分字段，判断数据在哪个节点
  • 若无法判断数据节点，只能遍历全部节点数据

插入语句必须带有拆分字段

  • dble根据拆分字段，判断数据在哪个节点
  • 新数据若无拆分字段，无法插入

拆分字段尽量等值

  • 范围拆分字段会扫描过多节点
  • 若使用IN子句，缩减IN子句值的数量

减少表的搜索遍历

  • 不带拆分字段时
  • DISTINCT、GROUP BY、ORDER BY

减小结果集

  • 分布式系统中，节点间有大量的数据交互
  • 数据交互会影响查询性能
  • 过大的结果集会增大数据汇集的网络交互量

跨节点连表

  • 经常join的表使用相同的拆分规则
  • 使用拆分字段作为join条件
  • 尽量对驱动表添加更多的过滤条件
  • 尽量少使用跨节点排序、分页等功能
  • 复杂语句拆分成多条语句

总结

  • 数据的增删改查，尽量带有拆分字段
  • 尽量减少数据节点之间的数据交互，将SQL直接转发
  • 尽量将连接键作为拆分字段

10.9 本章小结

分区表

  • 分区表可以优化单节点性能、分区之间隔离
  • 第一次访问需要打开所有ibd，可能达到上限
  • 可以通过存储在不同的磁盘上提高容量

分库分表的意义

  • 分库分表可以提升数据库性能
  • 分库分表使得数据的使用方法更加复杂、数据丢失的可能性增加
  • 使用分库分表中间件可以
  • 最大程度方便客户端的使用

dble架构设计

image-20220330063425766

使用dble进行读写分离

image-20220330073045188

分库分表的查询性能优化

  • 数据的增删改查，尽量带有拆分字段
  • 尽量减少数据节点之间的数据交互，将SQL直接转发
  • 尽量将连接键作为拆分字段

第11章 数据库高可用和身份切换的关键–如何解决数据库经常宕机问题

本章学习MySQL实现高可用的意义、原理和实现方案。学习MySQL主备切换的隐患以及处理方法。讲解高并发、高性能、高可用MySQL集群：DRDS集群的原理和架构。

11.1 切换:保业务还是保数据

如何进行身份切换

  • 停止备库同步
  • 配置主库复制从库

可靠性优先策略

数据无丢失

有几秒的时间两个数据库均不可写

若一开始末检查seconds_behind_master,不可用时间无法控制

  • 检查B库seconds_behind_master，不能过大
  • A库只读readonly = true
  • 检查B库seconds_behind_master=0
  • B库关只读readonly = false
  • B库停止复制A库，A库开始复制B库

可用性优先策略

  • 系统没有不可写的时间
  • 若切换时还有未重放的relay log，可能造成数据不一致错误

总结

  • 普通业务执行时，尽量用可靠性优先策略
  • 日志、流水等不太需要数据可靠性的，用可用性优先策略

11.2 数据库切换了，业务怎么办

业务切换至新地址

  • 业务预留接口，通知新的数据库地址
  • 使用微服务框架通知业务

image-20220330075343666

使用内部DNS，通过域名连接

  • 自己搭建CoreDNS

image-20220330075709688

VIP漂移

  • 使用keepalived进行自动VIP漂移

image-20220330075757654

使用代理

  • 使用Haproxy等代理组件，将转发地址切换至新库

image-20220330080146391

使用dble时的主备切换

  • 使用dble等分库分表中间件，将数据分片地址切换至新库

image-20220330080239389

总结

  • 业务切换至新地址:不影响性能，业务可能不支持
  • 使用内部DNS:方便，需要多余的硬件资源
  • VIP漂移:自动漂移，需要多余的IP资源
  • 使用代理:自动更新，需要多余的资源
  • dble:客户端无感知，需要被动通知

11.3 如何实现自动主从切换

Keepalived

  • 常见的高可用组件
  • 可以检测节点状态
  • 自动执行切换脚本
  • 还有VIP漂移功能

使用keepalived进行身份切换+自动VIP漂移

宕机可以执行脚本自动切换

image-20220330075757654

MHA (Master High Availability)

  • 常用的MySQL高可用组件
  • 支持GTID方式
  • binlog来不及传送时会尝试登陆A库传送binlog
  • 不能自动漂移VIP

image-20220330081317620

  • 从宕机崩溃的master抢救未传送的binlog
  • 等待Slave执行中继日志，追赶Master
  • 在Slave执行从Master抢救出的binlog
  • 提升一个Slave为新Master
  • 使用其他的Slave连接新的Master进行复制

image-20220330081932908

自研高可用组件

  • 完全自主控制
  • 研发代价高

总结

  • Keepalived也可以自动切换身份，但是并不完善
  • MHA是较为完善的MySQL自动身份切换工具
  • 若有更高级的MySQL管理需求，可以二次开发MHA或者自己开发高可用中间件

11.4 MHA自动主从切换实战

主服务器的配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[root@localhost etc]# /vim/etc/my.cnf

[mysqld]
server_id=2
datadir=/mysql/data
log_bin=/mysql/logbin/log
innodb_file_per_table
binlog_format=row
skip_name_resolve=1
socket=/var/lib/mysql/mysql.sock

MariaDB [(none)]> show master status;
+------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| log.000003 |      245 |              |                  |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#建立主从同步的账号
MariaDB [(none)]> grant replication slave on *.* to repluser@'%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]>  grant all on *.* to mhauser@'%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)


配置从服务器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[mysqld]
server_id=24
datadir=/mysql/data
log_bin=/mysql/logbin/log
innodb_file_per_table
read_only=1
relay_log_purge=0         
skip_name_resolve=1 
socket=/var/lib/mysql/mysql.sock

MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='172.18.251.133',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='centos',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='log.000003',
    ->   MASTER_LOG_POS=245,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.24 sec)
MariaDB [(none)]> start slave;

mha服务器

  • mha服务器：server#这个顺序决定这从服务器变为主的次序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[root@localhost etc]# vim /etc/mha/app1.conf

[server default]
user=mhauser      #mha的账号
password=centos
manager_workdir=/data/mastermha/app1/  
manager_log=/data/mastermha/app1/manager.log  
master_binlog_dir=/mysql/logbin  设置master 保存binlog的位置，以便MHA可以找到master的日志        
remote_workdir=/data/mastermha/app1/  
ssh_user=root
repl_user=repluser  
repl_password=centos
ping_interval=1

[server1]  
hostname=192.168.153.17
candidate_master=1  #设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave
[server2]  
hostname=192.168.153.27
candidate_master=1  
[server3]  
hostname=192.168.153.37

mha服务器测试：

1
2
3
4
5
6
7
[root@localhost etc]# masterha_check_ssh --conf=/etc/mha/app1.conf
.......
Wed Oct 17 09:55:42 2018 - [info] All SSH connection tests passed successfully.
[root@localhost etc]# masterha_check_repl --conf=/etc/mha/app1.conf
......
MySQL Replication Health is OK.

  • 当主服务器损坏时，从服务器会自动变为主，read_only也会关闭，当主服务器再次修好时，会有主变为从其配置如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[root@localhost etc]# vim /etc/my.cnf
#增加以下两行
read_only=ON
relay_log_purge=0

[root@localhost etc]# systemctl restart mariadb.service

Master [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='172.15.254.228',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='centos',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='slave-log.000003',
    ->   MASTER_LOG_POS=245,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.05 sec)

Master [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

11.5 高可用了，集群为什么还会挂

image-20220330102211842

  • dble的单点问题可能造成数据库系统无法使用
  • dble变为多节点后，数据协调可以使用Zookeeper
  • 多节点dble可以使用Keepalived提供统一VIP，或者使用负载均衡

11.6 本章小结

切换策略

  • 普通业务执行时，尽量用可靠性优先策略
  • 日志、流水等不太需要数据可靠性的，用可用性优先策略

如何切换业务

  • 业务切换至新地址:不影响性能，业务可能不支持
  • 使用内部DNS:方便，需要多余的硬件资源
  • VIP漂移:自动漂移，需要多余的IP资源
  • 使用代理:自动更新，需要多余的资源
  • dble:客户端无感知，需要被动通知

如何自动切换数据库身份

  • Keepalived也可以自动切换身份，但是并不完善
  • MHA是较为完善的MySQL自动身份切换工具
  • 若有更高级的MySQL管理需求，可以二次开发MHA或者自己开发高可用中间件

最后一个单点问题

  • dble的单点问题可能造成数据库系统无法使用
  • dble变为多节点后，数据协调可以使用Zookeeper
  • 多节点dble可以使用Keepalived提供统一VIP，或者使用负载均衡
  • 至此，我们的三高分布式数据库系统已经初具规模

第12章 未来的数据库什么样

本章讲解未来新型数据库的发展趋势，主要讲解MySQL8.0新特性、阿里POLARDB、谷歌Spanner数据库、TiDB、阿里OceanBase等NewSQL数据库。

12.1 MySQL8.0有什么新特性

窗口函数(Window Functions)

  • 以某个列为分隔，分为多个“窗口”
  • 在窗口内执行特定函数
1
2
3
4
5
# 以每一个customer_id 为分区 按照 amount排序
SELECT
	* rank() over ( PARTITION BY customer_id ORDER BY amount DESC ) AS ranking 
FROM
	`payment`;

隐藏索引

  • 暂时隐藏某个索引

  • 可以通过隐藏和显示索引，来测试索引的作用

show index from payment ;

ALTER TABLE payment ALTER。INDEX fk_payment_rental INVISIBLE;

ALTER TABLE payment ALTER INDEX fk_payment_rental VISIBLE;

降序索引

  • 8.0之前只有升序索引
  • 对于很多本来能走索引覆盖的语句，升序索引无法覆盖
  • 降序索引解决了此问题

CREATE INDEX idx1 ON payment (payment_date desc);

通用表表达式(Common Table Expressions CTE)

  • 使用CTE表达式预先定义可以复杂语句中反复使用的中间结果
  • CTE可以认为是一个临时视图
1
2
3
4
5
6
7
8
SELECT
	b,
	d 
FROM
	( SELECT a，b FROM table1 )
	JOIN ( SELECT c, d FROM table2 ) 
WHERE
	cte1.a = cte2.c;
1
2
3
4
5
6
7
WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d
FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

UTF-8编码

  • utf8mb4作为MySQL的默认字符集

DDL事务

  • 8.0支持DDL事务，元数据操作可以回滚

12.2 lnnoDB Cluster和PXC有什么区别

InnoDB Cluster

InnoDB Cluster主要由MySQL Shell、MySQL Router和MySQL服务器集群组成，三者协同工作，共同为MySQL提供完整的高可用性解决方案。

Mysql InnoDB Cluster 主要由三个模块构成：

  1. 支持Group Replication 功能的Mysql Server(version >= 5.7.17)，模块主要功能在于实现了组内通信，故障转移（英语：failover, 即当活动的服务或应用意外终止时，快速启用冗余或备用的服务器、系统、硬件或者网络接替它们工作）、故障恢复（英语：failback，将系统，组件，服务恢复到故障之前的组态）
  2. Mysql-shell：实现快速部署，主要提供了一套AdminAPI，可以自动化配置Group Replication，让我们无须再手动配置cluster中group replication相关参数。
  3. Mysql-router：内置读写分离，负载均衡。自动根据Mysql InnoDB Cluster中的metadata自动调整。

Mysql InnoDB Cluster功能：故障转移、故障恢复、读写分离、负载均衡等。

image-20220330112755346

Percona XtraDB Cluster

Percona XtraDB Cluster提供的特性有：

  • 1.同步复制，事务要么在所有节点提交或不提交。
  • 2.多主复制，可以在任意节点进行写操作。
  • 3.在从服务器上并行应用事件，真正意义上的并行复制。
  • 4.节点自动配置。
  • 5.数据一致性，不再是异步复制。

Percona XtraDB Cluster完全兼容MySQL和Percona Server，表现在：

  • 1.数据的兼容性
  • 2.应用程序的兼容性：无需更改应用程序
  • 3.集群是有节点组成的，推荐配置至少3个节点，但是也可以运行在2个节点上。
  • 4.每个节点都是普通的mysql/percona服务器，可以将现有的数据库服务器组成集群，反之，也可以将集群拆分成单独的服务器。
  • 5.每个节点都包含完整的数据副本。
优点如下
  • 当执行一个查询时，在本地节点上执行。因为所有数据都在本地，无需远程访问。
  • 无需集中管理。可以在任何时间点失去任何节点，但是集群将照常工作。
  • 良好的读负载扩展，任意节点都可以查询。
缺点如下
  • 加入新节点，开销大。需要复制完整的数据。
  • 不能有效的解决写缩放问题，所有的写操作都将发生在所有节点上。
  • 有多少个节点就有多少重复的数据。

image-20220330112736065

12.3 数据库有哪些分类

按用途分类

  • OLTP(Online Transaction Processing)
  • OLAP(Online Analytical Processing)
  • HTAP(Hybrid Transaction/Analytical processing)

OLTP(Online Transaction Processing)

  • 在线事务(交易)处理系统
  • SQL语句不复杂，大都处于事务中
  • 并发量大，对可用性要求高
  • MySQL/PostgreSQL

OLAP(Online Analytical Processing)

  • 在线分析处理系统
  • SQL语句复杂、数据量大
  • 一般以单个事务为单位
  • Hive/SparkSQL/GreenPlum

HTAP(Hybrid Transaction/Analytical Processing)

  • 混合事务/分析处理
  • 混合了两种数据库的特点，实现一种架构多功能

按存储形式分类

image-20220330125114060

  • 行存

image-20220330125133917

  • 列存

image-20220330125147436

  • KV

按架构分类

  • Shared-Everything
  • Shared-Memory
  • Shared-Disk
  • Shared-Nothing

总结

  • 按用途分类
  • 按存储形式分类
  • 按架构分类

12.4 谁是单体数据库之王

PostgreSQL

  • 与MySQL类似的功能
  • 性能更好、更稳定
  • 代码质量更高
  • 有赶超MySQL的趋势（Mysql被Oracle控制之后，很多地方有加入付费功能的趋势）

Postgres-XL(OLTP)·

image-20220330130105368

  • GTM管理每个事务的执行
  • Coordinator解析SQL，制定执行计划，然后分发
  • DataNode返回执行结果到Coordinator

GreenPlum(OLAP)

image-20220330131952972

  • 高性能SQL优化器:GPORCA
  • Slice执行模式

总结

  • PostgreSQL是一个可以完全代替MySQL的高性能数据库
  • Postgres-XL是基于Postgres的分布式事务集群
  • GreenPlum是基于Postgres的分布式分析集群

12.5 MySQL能魔改成什么样

PolarDB

  • POLARDB 是阿里云自研的下一代关系型分布式数据库，100%兼容MySQL，之前使用MySQL的应用程序不需要修改一行代码，即可使用POLARDB。

  • POLARDB在运行形态上是一个多节点集群，集群中有一个Writer节点（主节点）和多个Reader节点，他们之间节点间通过分布式文件系统（PolarFileSystem）共享底层的同一份存储（PolarStore）。

  • POLARDB通过内部的代理层（Proxy）对外提供服务，也就是说所有的应用程序都先经过这层代理，然后才访问到具体的数据库节点。

    • Proxy可以做安全认证（Authorization）和保护（Protection），
    • 解析SQL，把写操作（比如事务、Update、Insert、Delete、DDL等）发送到Writer节点，
    • 解析SQL，把读操作（比如Select）均衡地分发到多个Reader节点，这个也叫读写分离。

  • POLARDB对外默认提供了两个数据库地址，一个是集群地址（Cluster），一个是主地址（Primary）

    • 推荐使用集群地址，因为它具备读写分离功能可以把所有节点的资源整合到一起对外提供服务。
    • 主地址是永远指向主节点，访问主地址的SQL都被发送到主节点，当发生主备切换（Failover）时，主地址也会在30秒内自动漂移到新的主节点上，确保应用程序永远连接的都是可写可读的主节点。

image-20220330195014231

日志传送VS共享存储

  • 特点

    • 除了可以像使用MySQL一样使用POLARDB，这里还有一些传统MySQL数据库不具备的优势。

      • 容量大

        • 最高100T，不再因为单机容量的天花板而去购买多个MySQL实例做Sharding，甚至也不需要考虑分库分表，简化应用开发，降低运维负担。

      • 高性价比

        • 多个节点只收取一份存储的钱，也就是说只读实例越多越划算。

      • 分钟级弹性

        • 存储与计算分离的架构，再加上共享存储，使得快速升级成为现实。

      • 读一致性

        • 集群的读写分离地址，利用LSN（Log Sequence Number）确保读取数据时的全局一致性，避免因为主备延迟引起的不一致问题。

      • 毫秒级延迟——物理复制

        • 利用基于Redo的物理复制代替基于Binlog的逻辑复制，提升主备复制的效率和稳定性。即使是加索引、加字段的大表DDL操作，也不会对数据库造成延迟。

      • 无锁备份

        • 利用存储层的快照，可以在60秒内完成2T数据量大小的数据库的备份。并且这个备份过程不需要对数据库加锁，对应用程序几乎无影响，全天24小时均可进行备份。

      • 复杂SQL查询加速

        • 内置并行查询引擎，对执行时长超过1分钟的复杂分析类SQL加速效果明显。该功能需要额外连接地址。

  • PolarFS

    • PolarFS设计中采用了如下技术以充分发挥I/O性能：

      • PolarFS采用了绑定CPU的单线程有限状态机的方式处理I/O，避免了多线程I/O pipeline方式的上下文切换开销。
      • PolarFS优化了内存的分配，采用MemoryPool减少内存对象构造和析构的开销，采用巨页来降低分页和TLB更新的开销。
      • PolarFS通过中心加局部自治的结构，所有元数据均缓存在系统各部件的内存中，基本完全避免了额外的元数据I/O。
      • PolarFS采用了全用户空间I/O栈，包括RDMA和SPDK，避免了内核网络栈和存储栈的开销。
      • 在相同硬件环境下的对比测试，PolarFS中数据块3副本写入性能接近于单副本本地SSD的延迟性能。从而在保障数据可靠性的同时，极大地提升POLARDB的单实例TPS性能。

  • PolarDB日志

    • 在数据库PolarDB中开创性地引入了物理日志（Redo Log）代替了传统的逻辑日志，不仅极大地提升了复制的效率和准确性，还节省了50%的 I/O 操作，对于有频繁写入或更新的数据库，性能可提升50%以上。

  • PolarProxy

    • PolarProxy存在的意义是可以把底层的多个计算节点的资源整合到一起，提供一个统一的入口，让应用程序访问，极大地降低了应用程序使用数据库的成本，也方便了从老系统到POLARDB的迁移和切换。
    • 本质上，PolarProxy是一个容量自适应的分布式无状态数据库代理集群，动态的横向扩展能力，可以将POLARDB快速增减读节点的优势发挥到极致，提升整个数据库集群的吞吐，访问它的ECS越多，并发越高，优势越明显。

image-20220330195122632

12.6 谁在支撑双十一

OceanBase

地域（Region）

Region 指一个地域或者城市（例如杭州、上海、深圳等），一个 Region 包含一个或者多个 Zone，不同 Region 通常距离较远。OceanBase 支持一份数据的多个副本跨 Region 部署。

可用区/区（Zone）

Zone 是 Availability Zone 的简称。一个 OceanBase 集群，由若干个可用区（Zone）组成。通常由一个机房内的若干服务器组成一个 Zone。为了数据安全性和高可用性，一般会把数据的多个副本分布在不同的 Zone 上，可以实现单个 Zone 故障不影响数据库服务。

OBServer

运行 OBServer 进程的物理机。一台物理机上可以部署一个或者多个 OBServer。在 OceanBase 内部，server 由其 IP 地址和服务端口唯一标识。

资源池（Resource Pool）

一个租户拥有若干个资源池，这些资源池的集合描述了这个租户所能使用的所有资源。一个资源池由具有相同资源规格（Unit Config）的若干个 UNIT（资源单元）组成。一个资源池只能属于一个租户。每个 UNIT 描述了位于一个 Server 上的一组计算和存储资源，可以视为一个轻量级虚拟机，包括若干 CPU 资源，内存资源，磁盘资源等。

一个租户在同一个 Server 上最多有一个 UNIT。实际上，从概念上讲，副本是存储在 UNIT 之中，UNIT 是副本的容器。

OBProxy

应用访问数据库使用兼容 MySQL/Oracle 的客户端来访问 OceanBase, OceanBase 以服务的形式提供给应用访问。OBProxy 就是满足此种需求，方便应用使用兼容 MySQL/Oracle 的客户端访问 OceanBase，它接收客户端的应用请求，并转发给 OBServer，然后 OBServer 将数据返回给 OBProxy, OBProxy 将数据转发给应用客户端。

RS（RootServer）

主控服务器。主要进行集群管理、数据分布和副本管理。

Multi-Paxos

一种执行多 Paxos 实例的优化协议，OceanBase 用 Multi-Paxos 协议实现 Commit Log 的多机持久化。

分布式执行（Distributed Execution）

执行计划在多台数据库服务器上执行，每台服务器完成其中的一部分工作。

ODC

OceanBase 开发者中心（OceanBase Developer Center，ODC）是为 OceanBase 数据库量身打造的企业级数据库开发平台。ODC 支持连接 OceanBase 中 MySQL 和 Oracle 模式下的数据库，同时为数据库开发者提供了数据库日常开发操作、WebSQL、SQL 诊断、会话管理和数据导入导出等功能。

OCP

OceanBase 云平台（OceanBase Cloud Platform，OCP）伴随 OceanBase 数据库而生，是一款以 OceanBase 为核心的企业级数据库管理平台。不仅提供对 OceanBase 集群和租户等组件的全生命周期管理服务，同时也对 OceanBase 相关的资源（主机、网络和软件包等）提供管理服务，让您能够更加高效地管理 OceanBase 集群，降低企业的 IT 运维成本。

OMS

OceanBase 迁移服务（OceanBase Migration Service，OMS）是 OceanBase 提供的一种支持同构或异构 RDBMS 与 OceanBase 之间进行数据交互的服务，它提供了数据的在线迁移和实时增量同步的数据复制能力。

image-20220330163512201

image-20220330163922048

12.7 谁是国产数据库最闪亮的星

TiDB简介

  • 一键水平扩容或者缩容
  • 金融级高可用
  • 实时HTAP
  • 云原生的分布式数据库
  • 兼容MySQL 5.7协议和MySQL生态

TiDB整体架构

image-20220330164718245

image-20220330165016082

image-20220330165831089

12.8 打不死的小强

CockroachDB

  • 2015年启动，由谷歌前员工发起
  • 完全开源
  • 分布式shared-nothing架构
  • 兼容Postgres协议

image-20220330165623822

  • 分层架构
  • 每个节点底层都为RocksDB
  • 使用Raft作为共识协议

12.9 本章小结

MySQL8.0新特性

  • UTF-8编码
  • DDL事务
  • lnnoDB Cluster

数据库分类

  • 按用途分类
  • 按存储形式分类
  • 按架构分类

PostgreSQL

  • 单机PostgreSQL
  • Postgres-XL(OLTP)
  • GreenPlum(OLAP)

PolarDB

  • PloarDB对寸MySQL进行了魔改
  • 采用了共享存储的架构
  • 将三种log简化为redo log一种

OceanBase

image-20220330163512201

TiDB

image-20220330164718245

CockroachDB

image-20220330165623822

第13章 课程总结

13.1 如何进一步提高?

MySQL方向

  • 《MySQL技术内幕InnoDB存储引擎第2版》
  • 《高性能MySQL(第3版）》
  • MySQL社区版、MariaDB源码

PostgreSQL方向

  • 《PostgreSQL数据库内核分析》
  • 《PostgreSQL指南》
  • PostgreSQL源码

数据库开发方向

  • 《数据库系统概念(第6版)》
  • 《数据库系统实现(第2版)》
  • CMU Databases Systems
  • CMU Advanced Database Systems