Mysql云服务简介

目录：

  • 体系架构
  • InnoDB
  • 索引结构
  • 事务与锁
  • Mysql备份恢复及安全防护
  • Mysql复制及常见高可用架构

概述

Mysql发展

  • 2013年 5.6版本
  • 2015年 5.7版本
  • 2018年 8.0版本

推荐二进制版本安装

服务启停

  • 启动 systemctl（更方便）/mysqld_safe（安全停止）
  • 停止 systemctl/mysqladmin … shutdown
    • kill -15
    • kill -9 会跳过-15的正常关闭

体系架构

  • 单进程多线程结构
  • 数据库实例才是真正操作数据库文件的
  • 一个数据库对应操作系统是一个文件夹

数据库 vs 实例：

  • 一个数据库对应一个实例 - oracle
  • 一个实例对应“多个”数据库 - mysql - database和schema同义
  • 一个数据库对应多个实例 - oralce rac

物理文件：

  • 数据文件 包括redo等文件
  • 配置文件
  • pid文件 进程文件
  • socket文件 套接字

配置关键参数：

  • innodb_buffer_pool_size 建议内存50%-70%
  • expire_logs_days 自动清理binlog 一般7天
  • max_connections
  • transaction_isolation 隔离级别，默认RR（repeatable read），建议初始设置为RC（read commited），避免RR下由于间隙锁导致可能的死锁 等等

常见的事务隔离级别包括：

  1. 读未提交（Read Uncommitted, RU）: 事务可以读取其他未提交事务的更改，可能导致脏读。
  2. 读已提交（Read Committed, RC）: 事务只能读取其他已提交事务的更改，防止脏读，但可能导致不可重复读。
  3. 可重复读（Repeatable Read, RR）: 事务在执行期间看到的数据是一致的，即使其他事务在此期间提交了新更改，防止脏读和不可重复读，但可能导致幻读。
  4. 可串行化（Serializable, S）: 事务之间完全隔离，保证数据的一致性，但性能最低，防止脏读、不可重复读和幻读。

为什么推荐使用 RC 而不是 RR

  • 避免间隙锁导致的死锁：在 Repeatable Read (RR) 隔离级别下，为了防止幻读，数据库引擎会使用间隙锁（Gap Lock），这可能导致死锁，特别是在高并发的环境中。
  • 性能考虑：Read Committed (RC) 隔离级别相对于 Repeatable Read (RR) 更加高效，因为它不会使用间隙锁，仅防止脏读。这对于大多数应用场景来说，RC 是一种性能与数据一致性之间的良好平衡。

InnoDB

特点：

  • 支持事务
  • 行级锁
  • 故障恢复：undo+redo
  • 缓存buffer
  • 索引组织表：按主键顺序排序

主要线程：

  • master thread 主线程，负责调度，作用将缓冲池中数据异步刷新到磁盘
  • purge thread 回收undo页

表空间：

  • 系统表空间 所有innodb数据
  • 独立表空间 每个表都有 页是管理的基本单位

redo log 专属于innodb 环状结构（write_position, check_point） 相关参数：innodb_flush_log_at_trx_commit 推荐设置为1

binlog 记录方式：

  • sql语句，更高效
  • 记录行的前后变化
  • 混合模式，一般生产不会用

binlog的刷新：

  • binlog_cache
  • binlog_file
  • disk

两阶段提交

  • 先写redolog - prepare
  • 再写binlog
  • redolog commit
  • fsync

多版本并发控制 MVCC

  • 只有写写会相互阻塞了
  • 快照（readview）

索引结构

  • 行记录 - 单向链表
  • 页目录
  • 数据页 - 双向链表
  • B+树（聚集索引、二级索引）

事务与锁

事务 transaction

特性：

  • 原子性 commit rollback redo undo
  • 一致性 双写
  • 隔离性 锁机制
  • 持久性 innodb_flush_log_at_trx_commit, sync_binlog

提交方式：

  • 自动提交 一般是
  • 手动提交
  • 隐式提交 如ddl，修改数据库架构， 管理类语句

并发执行带来的问题 ⬇️ 事务隔离级别

查看加锁情况

1
2
3

select * from information_schema.innodb_trx\G
show engine innodb status\G
show processlist;

数据备份和安全

工具、方式、窗口等

冷备、热备 物理、逻辑

逻辑备份-mysqldump 物理备份-xtrabackup/innobackupex

安全连接插件 connection_control

加密存储 - 一般不用

推荐定期清理mysql_history，否则可能有安全风险

复制、高可用

看binlog工具：mysqlbinlog

mysql主从复制 异步复制、半同步复制（其中一个从库确认后主库才commit）、全同步复制（所有从库确认后）

半同步状态查看： show status like '%semi%';

查看复制状态 show slave status\G slave_sql_running: yes - 开启复制状态 参数要了解下

Mysql云平台服务介绍-略

Mysql体系架构及存储引擎解析

目录

  • 体系架构、Innodb索引结构
  • Online ddl
  • mysql开发规范

体系架构

连接器 长连接（应用连接池）、短连接（自己命令行查数据） 长连接：可能会内存不够->重连

查询缓存 query_cache 会先看是否缓存里有（key-value），有直接返回 不建议使用，因为可能不实时 mysql8.0 直接删掉了查询缓存的功能

分析器（解析器）

  • 词法分析 识别关键字
  • 语法分析 判断sql语法

优化器 生成执行计划

  • 多个索引，决定用哪个索引
  • 多表join，决定连接顺序（哪张是驱动表）

执行器 通过存储引擎提供的接口执行语句

慢日志里： 可以关注 rows_examined 扫描的行数（比较准确的） 执行计划里的rows 是估计值

写日志顺序：两阶段提交

  1. 更新到内存
  2. 写入redo log 处于prepare阶段 - innodb
  3. 写binlog - 执行器
  4. commit - innodb

物理存储结构 mysql是单进程，多线程

innodb内存结构

  1. 缓冲池 innodb_buffer_pool
  • 数据页
  • 索引页
  • 插入缓存
  • 锁信息
  • 数据字典
  • 哈希索引
  1. 重做日志缓存 redo log_buffer

表空间 ibd文件

MVCC 以前只有读读才能并发 版本链 - 回滚指针

B+树 上面的节点是索引页，叶子节点存数据的页位置

  • 页结构
    • 页有多行
    • 双向链表 范围查询友好
  • 行结构
    • 页目录

online ddl

在已有表加字段、加索引

  • 5.5 copy
  • 5.6/5.7 inplace rebuild/metadata
  • 8.0 instant 某些操作支持

怎么确定我的修改是以上哪种方式，可以在测试环境先试下，看下输出 query ok, n rows affected. 如果n是0的话，一般是后2种，比较快，在线

题外话：备份时 要保证所有表没有锁 没有要commit的 不然会阻塞。备份最好没有批处理的时间做

mysql开发规范

  • 避免隐式转换：索引问题、长的话还会有精度失效的问题
  • join优化：给驱动表的连接字段（on的字段）加索引
  • 大批量操作：分批操作，如1w行为单位来操作
  • 子查询改为join效率更高
  • 排序：先看执行计划，如果是using filesort就会很慢。索引一般可以where+orderby的字段作为复合索引。但是索引里有用到范围查找的字段（如where xxx > 10），则xxx字段无法利用索引。
  • limit分页查询：limit m,n mysql不是直接从第m行开始取，而是取m+n行然后放弃前m行。因为为保证不要取很多内容，考虑用join来优化。比如：
    • 反例：select * from table limit 10000,20;
    • 正例：select * from table a where join (select id from table limit 10000,20) b on a.id=b.id

推荐使用自增id或其他递增字段作为主键

主要原因：优化 B+ 树结构

InnoDB 存储引擎使用 B+ 树来组织主键索引（也称为聚簇索引）。B+ 树是一种平衡树结构，能够高效地进行数据查找、插入和删除操作。

  • 自增 ID（或递增字段）有助于保持 B+ 树的平衡：当使用递增字段作为主键时，新的数据总是插入到 B+ 树的末端。由于每次插入的记录都比前一条记录大，InnoDB 不需要在树的内部进行频繁的节点分裂或调整树的结构，这使得 B+ 树的维护成本较低，插入操作也更加高效。
  • 避免随机插入：如果主键是随机的（如 UUID），每次插入记录时，B+ 树可能需要在不同的节点之间插入新数据，这会导致更多的页分裂和树的重新平衡，进而影响插入性能，并增加存储的碎片化。

DDL规范：

  • alter变更时，不要有大的查询或更新，即不要有慢sql，否则可能阻塞alter，进而崩溃
  • alter变更时，不允许有未提交的锁 select * from information_schema.innodb_trx\G看下哪些事务未提交
  • 对大表（>1GB）alter时，需在业务低峰期执行
  • ddl禁止加sql_log_bin=0，因为这样不写binlog，主从复制就会失效