MySQL 进阶 原理 优化
2025-06-24 15:27:00 11 举报AI智能生成
MySQL实战，MySQL知识进阶，包括CRUD的执行流程。事务隔离级别， B+Tree剖析。
作者其他创作
大纲/内容
MySQL基本架构
服务层
涵盖了mysql的大多核心服务功能, 所有的跨存储引擎的功能都在这层实现: 例如: 存储过程, 触发器, 视图等
组成部分
连接器
负责跟客户端建立连接, 获取用户权限, 维持和管理连接
过程
客户端发起连接请求, 先进行TCP握手, 连机器就开始认证身份, 若用户名密码不对则返回错误, 客户端执行结束
用户名密码验证通过, 连接器到权限表中获取拥有的权限, 之后连接里面的权限判断都依赖此时读取的权限
当连接完成之后, 当前连接处理空闲状态, 可以在show processlist 命令可以查看到它.
客户端长时间无请求, 连机器会主动断开, 通过wait_timeout控制, 默认8小时.
查询缓存
之前执行过的语句会以key-val的形式放在内存中. mysql得到一个查询请求之后, 会先在缓存中查找,是否之前被执行过
查询缓存的弊大于利, 当一个表更新之后, 表上素有的查询缓存都会被清空. 如果是相对更新频率比较低的表做缓存比较划算. mysql8.0之后直接废除了整个查询缓存的功能
分析器
词法分析
解析sql语句, 识别出语句的成分, 例如: 查询语句还是更新语句, 表名, 列名等等
语法分析
先判定语法上是否符合mysql的语法规则, 包括列是否存在, 表是否存等
优化器
生成执行计划, 决策使用的索引, 多表关联的连接顺序.
执行器
首先会在优化器之前先校验对表是否有对应的操作执行权限, 后续执行器打开表, 根据表的引擎定义, 使用对应的引擎接口, 最终把结果返回客户端
执行引擎层
存储和读取数据, 提供读写接口; 引擎内部的逻辑, 例如: 查询的优化, 使用索引定位磁盘数据, 事务支持等
客户端
负责跟服务端建立连接, 发送执行语句, 接受执行结果
图示
架构示意图
重要的日志模块
重做日志 [redo log]
由innoDB引擎实现
redo log是物理日志， 记录的是 在某数据页上做了什么修改。 当有一条记录行更新的时候, InnoDB先把记录对应的所在页的改动写到redo log 里面, 并更新内存数据, 此时就算更新完成. 同时InnoDB引擎会在适当的时候比如系统比较空闲时, 更新到磁盘中.
每个文件默认1G，一组4个文件，从文件头部开始写，到末尾再从头循环
write pos 是当前记录的位置, 一边写一边向后移动, 当写到3号文件末尾后就回到0号文件开头
checkpoint 是当前要擦除的位置, 也是往后推移并且循环. 擦除记录前更新到数据文件中
有了redo log , InnoDB就可以保证技术数据库发生异常重启, 之前提交的记录也不会丢失, 称之为 crash-safe
WAL(Write-Ahead Loggin)技术 先写日志, 再写磁盘
InnoDB把内存中变更的数据页flush到磁盘中
脏页
内存中的数据页跟磁盘中的内容不一致时, 称之为脏页
InnoDB刷新脏页到磁盘的策略
参数 innodb_io_capacity 限制磁盘的负载能力. 这个值可以设置为磁盘的IOPS
IOPS通过fio工具获取:
fio -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
fio -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
innodb_flush_neighbors默认为1，表示当前页刷新到磁盘时，如果旁边的数据页也是脏页，则把旁边的脏页也一起刷掉，这个逻辑有顺延效果
归档日志 [binlog]
Server层实现
binlog是逻辑日志, 记录的是这个语句的原始逻辑
binlog可以持续追加, 通过参数配置单个binlog文件的最大大小, 和保存的最大个数
可通过binlog恢复被误修改的数据， 同时也是作为主从复制的重要文件
机制
事务执行过程中，先把日志写入binlog cache, 事务提交的时候， 再把binlog cache写到 binlog文件中 并清空binlog cache
一个事务的binlog不能被拆开，不管这个事务多大，都会被一次性写入
参数binlog_cache_size 可以控制每一个线程内的binlog chache 的内存的大小，超过了这个规定， 就要暂存到磁盘
参数sync_binlog 控制刷盘时机
0， 每次事务提交只write, 不进行fsync
1， 默认值， 每次事务提交都执行fsync
N , > 1 每次事务提交都write， 累计到N个事务之后才fsync
常见的会设置为100~1000中的一个数值， 对应的风险是： 如果主机发生异常重启，会丢失最近N个事务的binlog日志
图示： write 过程是把日志写入到文件系统的page cache中； fsync 是数据持久化到磁盘的过程
三种格式
Row level: 保存每行被修改的细节，任何情况下都可以进行数据恢复，加快从库重放日志的效率， 保证从库的数据一致性
Statement level: 每条修改数据的sql会被记录下来，可能这条语句影响多行数据。对于Row模式下就是多行binlog
Mixed level : 混合模式，上面两种level的结合
作用
主从复制，master端开启binlog， master把二进制日志传递给salve并回放
数据恢复，通过mysqlbinlog 工具可以恢复数据
增量备份
回滚日志 [undo log]
由innoDB引擎实现, 是事务的回滚日志, 主要记录某数据被修改之前的日志, 包括数据页的变更和change buffer的变更. 每次数据被修改之前被记录到undo log中, 当需要回滚到某版本就是可以通过undo log完成
分类
insert undo log
事务在插入新的记录产生的undo log , 当事务提交之后可直接丢弃
update undo log
事务在进行update/delete时产生的undo log, 在快照读时还是需要的, 所以不能直接删除, 只有当系统没有比这个log更早的read-view了的时候才能删除
用途
保证事务进行roolback时的原子性和一致性, 当事务回滚时可以使用undo log的数据进行恢复
用户MVCC快照度的数据, 在MVCC多版本控制中, 通过读取undo log的历史版本数据可实现不同事务版本号都拥有自己的独立的快照数据版本
两阶段性提交
redo log 的写入被拆成了两个步骤, prepare 和 commit 两步; 当redo log经历了这两个步骤才算更新完成, 最终保证数据的一致性
分析两种crash场景
1. 在“写入redolog”后“写binlog”前 发生crash
此时binlog还没有写，redolog也还没有提交，所以在恢复的时候， 这个事物会回滚
2. 在“写binlog” 后， redlog 还没有commit 前发生crash
如果redolog事物是完整的，同时已经有了commit标识， 则直接提交； 如果redolog事务只有完整的prepare， binlog完整则提交事物，如果binlog不完整则回滚事务
binlog完整性判定规则
statement格式的binlog， 最后都有COMMIT
row格式的binlog， 最后会有一个XID event
MySQL5.6.2之后， 引入了binlog-checksum参数可以验证binlog的正确性
事务原理
ACID
原子性(Atomicity)
要执行的事务是一个独立的操作单元, 要么全部执行, 要么全部不执行
一致性(Consistency)
事务的一致性是指事务的执行不能破坏数控的一致性, 也成为完整性. 数据库中一个事务所包括的所有的数据从一个状态转为另一个一致性状态
隔离性(Isolcation)
多个事务并发执行时, 一个事务的执行不应影响其他事务的执行.
持久性(Durability)
所有提交的数据都要写入磁盘固化下来
隔离级别
概念
为了解决在多事务同时执行时, 可能会出现脏读, 不可重复读, 幻读 等问题, 提出了 "隔离级别" 的概念
分类
读未提交
一个事务还未提交, 他做的变更可以被其他事务看见
读提交 RC
一个事务提交之后, 他做的变更才会被其他事务看见; 每个sql执行的时候创建视图, 后续该行数据的使用都是基于这个视图进行的
可重复度 RR
一个事务执行过程中看到的数据, 总是跟这个事务启动的时候看到的数据是一致的. 事务启动时创建一个数据视图; mysql默认的隔离级别
串行(xing)化
对于同一行记录, 所有操作都进行加锁, 出现读写冲突时候, 后访问的事务需等到前一个事物执行完成才可继续执行
MVCC
多版本并发控制
通过一种可见性算法来实现数据库的并发控制, 实现在读取数据时不加锁. 避免因为写锁而造成读数据的并发阻塞问题
隐藏列
InnoDB中, 每一行都以隐藏列data_trx_id 和 data_roll_ptr
data_trx_id: 最近修改该行数据的事务ID
data_roll_ptr: 指向该行回滚段的指针, 该行上所有的旧版本, 在undo中通过链表的形式组织
roowId: 如果建表时没有指定主键, InnoDB会使用rowId创建一个聚簇索引
其中还有一个flag标记删除, 判断该行记录是否被删除, delete操作在引擎层面其实做的是逻辑删除
InnoDB中数据行的结构
整个MVCC的关键就是通过这两个隐藏列来实现的
事务链表
mysql中事务从开始到提交的前都会被保存到trx_sys的事务链表中, 这里报错的都是未提交的事务, 事务一旦被提交, 则会从事务链表中清除掉
事务链表的结构
两种读形式
快照读
读取的是当前事务的可见版本, 不必加锁. 简单的select操作就是快照读
当前读
读取的是当前版本, 比如特殊的操作, 更新/插入/删除操作
ReadView
读视图, 不同的隔离级别下会在不同的时机创建读视图; RC级别下, 每个sql执行前都会创建自己的数据读视图; RR级别下, 事务启动之后创建视图
其实就是一种数据结构
trx_ids: 当前系统活跃(未提交)的事务版本号集合
low_limit_id: 创建当前ReadView时, 当前系统活跃的事务的最大版本号+1
up_limit_id: 创建当前ReadView时, 当前系统活跃的事务的最小版本号
cerator_trx_id: 创建当前ReadView的事务的版本号
可见性规则
图示
总结: 一个数据版本对于一个事务视图来说, 自己的更新总是可见之外, 还有三种情况:
1. 版本未提交, 不可见
2. 版本已提交, 但是在视图创建之后提交的, 不可见
3. 版本已提交, 而且是在视图创建之前提交的, 可见
事务启动的时机
begin/start transaction 命令不是一个事务的起点, 在执行他们之后的第一个操作innoDB表的语句时, 事务才真正启动, 也正是此时会创建事务的一致性视图
start trannsaction with consistent snapshot 这个命令开始立即启动一个事务, 而此时也会创建对应事务的一致性视图
MySQL的锁
全局锁
对整个数据库实例加锁, 通过命令: Flush tables with read lock(FTWRL) . 加锁之后整个库处于只读状态, 其他的更新语句将会被阻塞
使用场景: 全库的逻辑备份. 官方使用自带的备份工具mysqldump, 使用参数--sign-transaction, 导出数据的时就会启动启动一个事务, 通过MVCC 来确保得到一个一致性视图, 这种方式适合事务引擎的库
如果引擎不支持事务, 那么备份就只能通过FTWRL方法进行备份了. 这样的代价就是 业务写数据需要暂停, 只能对库进行读操作
注意
set global readonly = true , 也可以保证全库只读, 但任然建议使用FTWRL
readonly 的值会被用来做其他逻辑, 例如判断主备库, global的影响范围更广
如果执行FTWRL命令之后客户端发生了异常断开, 那么mysql会自动释放全局锁, 整个库回到正常状态, 而通过readonly设置的, 则没有这种机制
表级锁
表锁
lock talbes ... read/write 可以使用unlock tables 主动释放锁, 也可以再客户端端口的时候自动释放. 还没有出现更细颗粒度的锁时, 表锁是常用的处理并发的方式
元数据锁 MDL(metadata lock)
不需要显示地使用, 在访问一个表的时候会被自动加上, 作用是保证读写的准确性.
对表的操作包括两种
对一张表进行 增删改查操作, 此时会加 MDL 读锁
对一张表的 结构进行变更操作(加字段/修改字段/删除字段/添加索引等)时, 此时会加 MDL 写锁
元数据锁之间的互斥关系
MDL 读锁之间不互斥, 因此可以有多个线程同时对一张表进行增删改查
读写锁, 写锁之间是互斥的, 用来保证表结构操作的安全性. 因此表持有写锁时, 该表的其他操作都被阻塞
注意
大表操作的时候, 需要格外小心, 以免导致整个库宕掉; 一定要避开当前mysql中正在提交事务的表进行DDL
在alter table 的时候, 可以设定等待时间, 在这个时间内能能到MDL写锁则进行执行DDL语句, 如果不能拿到就先放弃不要阻塞后面的业务语句.
alter等待时间设置语法: alter table t_test wait N add name varchar(32) not null default '';
行级锁
针对数据表中行记录的锁
例如事务A更新一行, 而这时事务B也要更新同一行, 则必须等事务A的操作完成才能进行更新
在InnoDB的事务中, 行锁再需要的时候才加上的, 但并不是不需要了就立即释放, 而是要等到事务结束时才释放, 这个就是两阶段所协议
子主题
如果事务中需要锁多行, 那应该把最可能造成锁冲突, 影响并发度的锁尽量往后放
死锁和死锁检测
当并发系统中出现循环资源依赖, 涉及的线程都在等待别的线程释放资源时,就会到导致这几个线程都进入无线等待状态, 称为死锁
死锁图示
出现死锁后有两种策略
进入死锁的线程进行等待, 直到超时自动退出. 超时参数可以通过innodb_lock_wait_timeout(默认50s)设置
发起死锁检测, 发现死锁后, 主动回滚掉其中一条事务, 让其他的事务得以执行, innodb_deadlock_deteck 设置为on, 这也是默认值, 表示开启这个逻辑
解决热点行的更新导致性能问题
业务上保证尽量少的出现死锁, 例如数据本行的更新顺序一致.
控制并发度
热点行数据分散到不同的表中, 不同的线程可以分散到这些表中
间隙所(Gap Lock)
所有的数据行上都加了行数，但是无法阻止新数据的插入，也就是会出现"幻读"， 此时InnoDB专门引入了间隙所解决该问题, 隔离级别在RR才有效果
跟 Gap Lock 存在冲突关系的是“往这个间隙插入记录”这个操作；而间隙锁之间不存在冲突
间隙锁和行数合称之为next-key lock, 每个next-key lock 是一个前开后闭区间
间隙锁的引入，可能会导致通用的语句锁住更大范围，进而影响了并发度
加锁规则
原则1. 加锁的基本单位是next-key lock, 是一个前开后闭区间；锁是加在索引上的
原则2. 查找过程中访问到的对象才会加锁
优化1. 索引上的等值查询，该唯一索引枷锁的时候， next-key lock 退化为行锁
优化2. 索引上的等值查询， 向右遍历时且最后一个值不满足等值条件的时候， next-key lock 退化为间隙锁
优化3. 唯一索引上的范围查找会访问到不满足条件的第一个值为止
MySQL索引解密
概念
是一种提高查询效率的数据结构, 就像书的目录一样
分类
根据数据结构划分
hash索引
以键值对的的方式存储数据, 其中key通过hash函数计算出哈希表的位置下标, 如果出现哈希冲突那么可以拉出一个链表保存数据
B+Tree索引
mysql索引的基本实现方式, 通过逻辑上构建一个B+Tree数据结构, 提高查询效率
根据索引字段个数划分
单值索引
索引创建时仅包含一个字段
复合索引
包含多个字段, 也成为多指索引
根据是否在是主键上的索引划分
主键索引
当创建表时,指定一个主键, 那么mysql会自动创建一个主键索引, 只能有一个, 不能为null同时保证唯一性
二级索引
非主键索引, 叶子节点上保存的是主键索引值, 通过二级索引查询数据时, 先查找主键索引, 然后在到主键索引上查找对应的数据, 这叫做回表
根据数据组织结构划分
聚簇索引
InnoDB的主键索引, 非叶子节点存储的是索引指针, 叶子节点存储的有索引也有数据, 就是典型的聚簇索引
非聚簇索引
二级索引, 叶子节点上保存的是 主键索引的指针, 无具体的数据. 是典型的非聚簇索引
其他分类
唯一索引
允许为null, 但是不允许具有索引值相同的行, 可以达到禁止重复的目的
全文索引
用来支持文本的搜索
B+Tree索引
学术意义上的B+Tree
概念
根节点至少一个元素
每个节点允许保存多个key
有两种节点: 非叶子节点, 只保存key; 叶子节点, 保存key和data
所有的叶子节点都位于同一层, 或者说更节点到所有的叶节点的长度相同
所有的非叶子节点key在叶子节点都有冗余
非叶子节点的可以都是按照大小顺序排列, 对于非叶子节点的一个key, left-key < key < right-key
叶子节点也是按照key的大小从左往右进行排列, 每个叶子节点都保存了右边叶子节点的指针
图示
学术上的B+Tree
mysql的B+Tree索引
补充
非叶子节点存放对应的索引值, 叶子节点存放 索引值和数据行; 叶子节点是双向链表,可以适用于任何形式的范围查找
本质
数据页: InnoDB 默认的数据页大小是16K, 引擎在提起一行记录时通过加载行所在页的数据到内存中, 以减少磁盘IO
B+Tree上的非叶子节点保存的是索引值, 保存了每个数据页对应的地址
叶子节点包含了分组的多行用户数据, 以及索引值, 叶子节点上下页的指针等内容
用户的数据区域会进行分组, 数据索引页中记录每组中最小数据行的主键
perv指针 指向上一页的地址; next指向下一页的地址
图示
回表
通过二级索引定位获取到了对应的主键id, 再回到主键索引树进行搜索数据行的过程, 称作回表
覆盖索引
如果sql查询的字段已经存在于被搜索的索引树上, 那么就不需要回表, 在这个查询中, 索引已经"覆盖了"查询需求, 称之为覆盖索引
由于覆盖索引可以减少索引树查询的次数, 可以显著提高查询性能, 所以使用覆盖索引是常用的性能优化手段
最左匹配原则
使用like
当使用类似 where name like '李四%' 语句的时候, 如果在name上建立的有索引, 那么这个查询也能通过索引加快检索的;
如果使用 where name like '%李四' 时候, 此时就无法使用索引了
多字段的联合索引
首要原则就是 如果通过调整顺序可以少维护一个索引, 那么这个顺序往往就是优先考虑采用的; 其次是考虑空间的使用.
索引下推
可以在索引遍历的过程中(一般地在联合索引中比较常见), 对索引中包含的字段先做判断, 直接过滤掉不满足条件的记录, 减少回表次数
字符串上的索引
前缀索引
可以定义字符串的一部分作为索引, 默认地不指定前缀长度, 那么索引就会包含整个字符串
alter table t_test add index index_t_test(email); 索引中会记录整个字符串
alter table t_test add index index_t_test(email(6)); 记录在索引中的数据只包含字符串的前6个字节, 好处在于节省空间
既可减少占用空间,也可达到相同的查询效率
倒序存储, 这种情况适合字符串前缀区分度低, 可以倒过来建立索引, 或者是倒序保存
hash字段, 可以在表中在建立一个整数字段, 保存身份证的校验码, 在这个字段上创建索引
mysql 如何选择索引
优化器的逻辑
选择索引的工作是有优化器进行的, 找到最优的执行方案, 以减少扫描的行数, 意味着减少了磁盘IO
索引的基数
一个索引上的不同的值越多, 这个索引的区分度就越好, 一个索引上不同的值的数量称之为"基数"
show index from t_test 可以查看表t_test的索引情况, 其中 cardinality表示一个索引的基数, 是采用采样统计法得来的. 不完全精确
mysql采样统计法
InnoDB默认会选择N个数据页, 统计这些页面上的不同值, 得到一个平均值, 然后乘以索引的总页面数, 得到索引的基数
数据行在不断的更新, 索引的统计信息也得发送变化; 当变更行数超过1/M时, 会自动触发重新做一个索引统计
innodb_stats_persistent 参数可以设置索引的统计方式
on : 统计信息会持久化存储, 默认的N=20, M =10
off: 统计信息值存储在内存中, 默认的N=8, M=16
如果统计信息跟实际情况差别太大, 可以进行修正, analyze talbe t_test, 重新统计索引信息
如何引导mysql使用正确的索引
1. 使用 force index(index_name) 强行选择一个索引
2. 修改sql语句, 引导mysql使用我们期望的索引
3. 根据业务场景, 建立更加合适的索引, 提供给优化器使用
案例分析
被索引的字段上添加函数，将不会使用该索引
例如：mysql> select count(*) from tradelog where month(t_modified)=7; 全表扫描
对于索引字段做函数操作，可能会破坏索引的有序性，因此优化器就决定放弃走索引的查找功能。直接进行了全表扫描
隐式类型转换，导致索引失效
例如：mysql> select * from tradelog where tradeid=110717; 字段tradeid类型是字符串
字符串和数字进行比较，会隐式的将字符串转化为数字，导致触发上一个规则
隐式字符编码转换
两张不同字符集的表进行关联，分别是utf8mb4 和 utf8 ， 当两个类型的字符串做比较的时候， 会先把utf8字符串转换为utf8mb4字符集
MySQL的分区表
示例语句
分区表的创建
本质
分区表的实现时是在引擎层， 这个表包含一个 .frm 文件和 4 个 . ibd文件，每个分区都对应一个 .ibd文件； 对于引擎层是4个表。 对于Server层来说是 1个表。
分区策略
MyISAMySQL使用的通用分区策略，每次访问都由server层控制， 性能问题比较严重。8.0已经丢弃了这种策略
MySQL5.7.9开始， InnoDB引擎引入了本地分区策略， 这个策略是在InnoDB内部自己管理打开分区的行为
应用场景
显而易见的优势是对于业务透明， 相对于用户分表来说， 使用分区表的业务代码更加简洁
一个业务数据跑的时间足够长，往往回产生大量的历史数据。此时就可以按照时间进行分区；如果每个用户生产的数据较大，也可以按照用户ID进行分区
分区方式
范围分区 range
hash分区
list分区
优化
1. 分区并不是越细越好， 单表控制在千万以下就可以
2. 分区也不要提前创建太多，例如按照月份的分区，每年创建好第二年的分区即可
字段定义中数字的意义
数值
可分为两类：整数，浮点数或小数
允许指定数值的正负(UNSIGNED)或者用零填补(ZEROFILL)
示例：int(6)
示例中的6 表示"宽度指示器",不会影响该字段的存储长度,只有超过int的存储范围才会被截取；此处的宽度指示器的作用在于 若有修饰符 zerofill , 则未满足6位的部分使用0来填充
mysql默认是有符号的，需要单独使用一位来保存符号，使用了修饰符设置了zerofill，那就自动变为无符号字段了 unisgned
浮点数定义是需要定义两个数字， 一个显示宽度指示器，一个是小数点指示器
字符串
char：定长字符串, 定义时指定的就是字符最大数量，修饰的范围在0-255；大于指定长度的值被截断或者报错，比指定长度小的值会用空格填补
varchar : 可变成字符串，定义时指定的就是最大的字符数， 按照实际的值需要的长度来存储，不会被填充其他字符
MySQL查询执行流程
示例语句
示例语句： select city,name,age from t where city='杭州' order by name limit 1000 ;
-- 其中 有 id 为主键， city是一个B+Tree索引
-- 其中 有 id 为主键， city是一个B+Tree索引
执行流程
1. 语句中有排序需求， 第一步就是初始化sort_buffer， 确定放入name, city, age 三个字段
2. 从索引city中找到第一个满足city='杭州' 条件的主键id
3. 到主键id索引取出整行， 取出name, city, age三个字段 存入sort_buffer中
4. 从索引city中取出下一条满足记录的主键id
5. 重复3-4 步骤直到不满足查询条件为止
6. 对sort_buffer 中数据按照字段name做快速排序
7. 按照排序加过取出前1000行返回给客户端
参数设置
其中按照name做排序可以在内存中完成， 如果sort_buffer_size设置的较小，则会使用磁盘临时文件进行辅助排序
开启优化器的执行跟踪
服务端处理查询流程
MySQL处理查询是“边读边发”，查询结果是分段发送给客户端的，流程如下：
1. 获取一行，写到net_buffer中，这块内存的大小是由参数net_buffer_length定义， 默认：16k
2. 重复获取行，直到net_buffer写满，调用网络接口发出去
3. 如果发送成功， 就清空net_buffer, 然后继续读取下一行， 并写入net_buffer
4. 如果发送函数返回EAGAIN或WSAEWOULDBLOCK, 即本地网络栈写满， 进入等待， 直到网络栈可重新写入，再继续发送
查询中的JOIN
join 优化器可能使用选择自然为比较合适的驱动表； straight_join则是让优化器按照我们的指定的方式去join
案例SQL: select * from t1 straight_join t2 on (t1.a=t2.a); t2.a上有条索引
上诉查询SQL流程
1. 从表t1中读取一行数据R
2. 从数据行R中， 取出a字段到表t2里去查找，会使用到t2表的a字段对应的索引
3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分
4. 重复执行步骤 1-3， 直到把表t1 的末尾循环结束
join_buffer作用
表关联查询的时候，执行计划中有时会看到 join buffer， 表示会把分段读取的驱动表数据放在join_buffer中，然后根据join条件关联下张被驱动表作为结果集的一部分返回。由参数join_buffer_size 设定，默认： 256k
结论
Index Nested Loop Join（NLJ）: 顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，使用的on条件在被驱动表选择走索引，匹配成功则跟驱动表组成一行作为结果集的一部分返回，重复上面的操作知道循环结束
Simple Nested-Loop Join：如果on条件在被驱动表中不是一条索引， 那么就会出现O(M*N)的时间复杂度, MySQL也没有使用这种方式
Block Nested-Loop Join(BNL): 先把驱动表的数据读取出来放在线程内存的join_buffer中，把被驱动表的每一行数据取出跟join_buffer中的数据做对比， 满足join条件的作为结果的一部分返回
1. 使用join语句，比直接强行拆成多个单标执行SQL语句的性能要好
2. 如果使用join语句的话， 需要让小表做驱动表，当然被驱动表需要能走索引
3. 如果使用Index Nested-Loop Join算法， 也就是可以用上被驱动表上的索引， 则join没有问题
4. 如果使用 Block Nested-Loop Join （BNL）算法， 扫描行数过多， 特别是大表上的join操作，这样可能要扫描的被驱动表多次，会占用大量的系统资源。主要关注 执行计划中是否有 “Block Nested Loop”字样
优化JOIN
MRR优化
大多数的数据都是按照主键递增的顺序插入得到的，所以可以认为，按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读， 能够提升读性能。
实例SQL: select * from t1 where a>=1 and a<=100;
优化后的流程
1. 根据索引a，定位到满足条件的行记录，把id的值放入read_rnd_buffer中
2. 将read_rnd_buffer 中的id进行递增排序
3. 排序后的id数组， 一次到主键id索引中查询记录， 并作为结果返回
4. 如果read_rnd_buffer放满了， 就会先执行完毕2-3， 然后清空read_rnd_buffer, 之后继续查找索引a的下一个值
BKA优化
对于Index Nested-Loop Join的一个优化， 在从驱动表获取a值再到t2使用a索引一条条查找数据中间， 添加了一层buffer， 即 驱动表查出去的a数据放在join_buffer中，便于后面t2的使用
图示
group by的优化
如果对结果没有排序的要求，可以在语句后面加上order by null 语句, 以省略最终的排序阶段
尽量让group by 过程用上索引， 确认explain中没有Using temporary 和 Using filesort
如果 group by 统计的数据量不大，尽量只使用内存表；也可以通过适当调大tmp_table_size参数，来避免使用磁盘临时表
如果数据量太大， 在使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by 的结果
案例分析
查询长时间不返回
示例语句： mysql> select * from t where id=1;
一般地这种情况大概率是表t被锁，可以使用show processlist 查看当前语句执行的情况
三种可能性
等待MDL锁
等待flush
等待行锁
查询慢
SQL没写好
示例： select * from t where id + 1 = 10000
错误的书写导致索引没有发挥作用
索引没有建好
例如一个联合索引，但是查询的语句中字段顺序出现跟索引不对应等
MySQL选择索引错误
MySQL索引统计存在偏差，导致选择了错误的索引
查询数据mvcc版本
示例语句： select * from t where id=1 lock in share mode
带有lock in share mode 的sql是当前读。如果有大量的写操作，此时直接返回的是MVCC中可见的最新值。而不加这个语句的sql是一致性读， 需要通过undo log 回滚到对应的位置，此时就会增加查询的负担
MySQL更新语句流程
update语句的执行流程
update T set C=C+1 where ID=2;
1. 执行器先引擎取出id=5这行数据, 若这行数据所在的数据页已经在内存中, 直接返回给执行器; 否则需要先从磁盘中读入内存, 然后在返回
2.执行器得到这行数据, 进行字段的更新操作, 得到新的数据行调用执行引擎接口写会这行数据
3. 执行引擎先更新内存中的数据, 同时数据行所在数据页的更改记录到redo log中, 此时redo log 处于prepare状态, 通知执行器执行完成, 随时可以提交事务
4. 执行器生成对应的binlog, 并把binlog写入磁盘
5. 执行器调用引擎的提交事务接口, 把redo log的状态修改 commit状态, 更新完毕
图示
更新语句中的索引的更新
change buffer
当需要更新一个数据页时, 如果数据页已经存在于内存中那么可以直接更新, 如果没有在内存中, 在不影响一致性的前提下, InnoDB会把更新操作缓存到change buffer 中, 暂时先不必读取更新数据页, 当下次有需求读取这个数据页的时候, 先把数据页读入内存中, 然后在数据页上执行merge change buffer 的操作. 同样能保证数据逻辑的准确性
change buffer 在存在中有拷贝, 也会被写入磁盘中. 除了提取数据页时会发生merge, 系统后台进程也会定期进行merge, 例如 数据库支持关闭时(shutdown)
change buffer 使用的是buffer pool里的内存, 可以通过innodb_change_buffer_max_size 设置百分比
如果更新记录操作可先记录在change buffer中, 减少磁盘的IO, 那么更新语句的执行速度也会得到明显的提升
针对某表的数据. 如果是写多读少的业务来说, 页面在写完之后立马被访问的概率比较小, 那么使用change buffer 就会起到比较好的效果
唯一索引字段的更新
首先需要读取数据页, 用来判定更新或者插入是否存在唯一冲突. 那么语句在更新的过程中就不会使用change buffer, 因为内存中已经有了对应的数据页
普通索引字段的更新
更新是需要判定内存中是否存在对应的数据页, 如果有则直接更新, 如果没有则走change buffer 的逻辑. 这样就提高了语句执行的效率
总结
在选择普通索引和唯一索引时, 这两种索引在查询性能上相差不大, 但是在维护性能上, 普通索引理论上会更占优势. 当时数据更新之后里面进行查询, 这种情况下两个索引的情况也就差不多了.
MySQL删除数据的奥秘
表的数据存储方式
innodb_file_per_table
OFF: 表的数据使用共享表空间， 跟数据字典放在一起
ON： 每个InnDB表数据存在一个以 .idb为后缀的文件中， Mysql5.6.6开始的默认值
数据“空洞”
当执行delete语句时，Inndb做标记删除；如果后续插入数据时，定位到这个数据页上有可能继续复用这个位置
同理，如果一个数据页上的数据都被删除了， 那么这个数据页也是可以被复用的
Inndb的标记删除，是不会主动回收表空间的，这些没有被复用的空间就造成了“空洞”
插入数据的时候，如果是随机插入，就有可能形成数据页分裂，就会造成数据页的末尾形成空洞
更新索引上的值可以理解为删除一个旧值， 插入一个新值。 同样会造成空洞
总结： 一个数据表经过大量的增删改后，都是有可能造成空洞的， 通过重建表可进行回收
重建表
MySQL5.6的Online DDL
1. 建立临时文件，扫描旧表中主键的所有的数据页
2. 用数据也中的旧表的记录生成B+Tree, 存储到临时文件中
3.生成临时文件的过程中， 把所有对旧表记录的操作记录在一个日志文件中（row log）
4. 临时文件生成后， 把日志文件的操作应用到临时文件，得到一个路基数据上与旧表相同的数据文件
5. 用临时文件替换旧表的数据文件
三种方式
MySQL5.6开始 alter table t engine = InnoDB
analyze table t ； 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据， 这个过程中添加了DML读锁
optimize table t ; 相当于上面两步的聚合
注意
重建表后，InnDB不会把整个表填满， 每个页都会留1/16的空间给后续的更新使用；当用户插入数据命中这个页时，会使用掉一部分预留空间， 当下次再重建表时就会继续按照1/16的规则进行
count(id) count(*) count(cel) 你真的懂吗
数据库设计法则
在保证逻辑正确都前提下， 尽量减少扫描的数据量， 减少磁盘IO
对于InnoDB来说，主键索引上保存的是数据， 而二级索引上保存的是主键值。 对于count(*)这种操作，优化器会找到最小的那棵树进行遍历， 因为遍历哪棵树得到的逻辑都结果一致
count()含义
count()是一个聚合函数， 对于返回的结果集，逐行进行判断，count中参数不是null， 则累加1， 否则不加。最后返回累加值
count(*), count(1), count(主键id) 都表示返回满足条件的结果集总行数； 而count(其他字段) 则表示返回满足提交的数据行里面 参数不为null的数量
性能上的差别
原则
1. server层要取哪个字段就返回哪个
2. InnoDB只给必要的值
3. 目前优化器只针对count(*)做了优化，定义为取行数
count(*)
这是例外，MySQL专门做了优化，不会把全部字段读取出来， 直接按照行累加
count(1)
InnoDB遍历整张表，但不取值。server层对于返回的每行添加一个“1”列， 判定不会为null，按照行累加即可
count(主键id)
InnoDB遍历整张表， 把每行的id取出，返回我server层，server层拿到id后，判定id不会为null，按照行累加即可
count(字段)
若字段定义为not null,则一行行取出读取字段判定不为null， 按照行累加
若字段定义允许null，取出字段判断不为null，进行累加
总结
按照效率排序：count(字段)<count(主键id)<count(1)≈count(*)
自增ID的使用
保存位置
MyISAM引擎的自增值保存在数据文件中
InnoDB引擎的自增值保存在内存中
<= 5.7， 自增值不会自动持久化，每次服务重启，第一次打开表的时候，会去找找表的max(id) ， 然后再这个基础上加1 作为表的自增值
8.0 开始， 把自增值的变更记录在了redo log 中， 重启的时候依靠 redo log 恢复重启之前的值
修改机制
1. 如果插数据时id字段指定了0, null 或未指定，那么就把这个表当前的AUTO_INCREMENT值填充到自增字段
2. 如果插入数据时id字段指定了具体的值， 就直接使用语句里指定的值
3. 指定的id值小于当前自增值时，那么这个表的自增值不变
4. 如果插入的id值， 就需要把当前自增值修改为新的自增值
注： 在插入一行新数据行前，首先对自增值进行修改，拿到最新的自增值给到新的数据行
那么在 插入出现异常（如：唯一键冲突，事务回滚）就会导致 自增值“空洞”
那么在 插入出现异常（如：唯一键冲突，事务回滚）就会导致 自增值“空洞”
自增值的算法
从auto_increment_offset 开始，以auto_increment_increment为步长， 持续叠加
自增值为何不能回退
避免导致事务回滚还需要回退自增值，从而增加设计复杂度以及系统性能降低。所以自增值在事务之外，所以自增值可能会不连续
自增id用完怎么办
int类型的id，无符号整形是4个字节，上限就是2的32次方-1
表定义的自增值达到上限后，再申请下一个ID时，得到的值不变。insert就会出现主键冲突
临时表的意义
内存表： 指的是使用Memory引擎的表(engine=memory)，数据保存在内存中，系统重启数据会被清空，表结构还存在
临时表： 语法(create temporary) 可以使用各种引擎类型，跟随session的生命周期. session内存在同名的临时表和普通表时，对表的show create , CURD都是临时表. 而 show tables 不显示临时表
应用: 大表被分库之后， 可以利用临时表进行汇总做逻辑操作，表最终会随着session的关闭而释放
临时表可以重名机制
临时表物理存放机制
create temporary 会创建一个frm文件保存表结构，这个frm文件存放在临时文件目录下， 名字是"#sql{进程id}_{线程id}_序列号"
临时表内存区别机制
每张表都对应一个table_def_key
普通表是由 "库名+表名" 得到的
临时表是由"库名+表名+server_id+thread_id"
两个不同的session创建的同名临时表，在物理上和逻辑内存中都是不同的，所以可以重名并存
临时表和主备
当前的binlog_format=row时，跟临时表相关的语句，就不会记录到binlog中，因为对表的操作都转化成了全量的数据模式
binlog_format=statment/mixed ， binlog中才会记录临时表相关的操作，创建临时表的语句会传到备库进行执行，并带上主库的thread_id， 在主库session关闭的时候，会自动写入一个 drop temporary table 传给备库执行
数据表的复制
insert ... select
insert select 是很常见的两个表之间复制数据的方法， 在RR的隔离级别下， 这个语句会给select的表里扫描到的的记录和间隙加读锁
若insert 和select的对象是同一个表， 则有可能造成循环写入， 此时可以使用临时表来优化
insert如果出现了唯一键的冲突，则会在唯一值上加共享的next-key lock (S 锁)，此时需要快速提交或者回滚事务，避免加锁时间过长
mysqldump 方法
-single-transaction: 导入数据的时候不要对表 db1.t加表锁
-add-locks= 0 表示输出的文件中，不需要增加“LOCK TABLES t WRITE”
-no-create-info 不需要导出表结构
-set-gtid-purged=off, 表示不输出跟GTID相关的信息
-where: 按照条件查询需要导出的数据
-skip-extended-insert: 生成的文件中一条INSERT语句只插入一行数据
在客户端执行命令导入
source 是个客户端命令，流程如下：
1、打开文件，默认以分号为结尾读取每条SQL语句
2. 把SQL语句发送到服务端进行执行
使用CSV文件
这个语句会把文件保存在服务端，原则上一个数据行对应文本中的一行，into outfile 需要指定一个不存在的文件名，不然会因文件同名而报错
导入CSV文件
流程
1. 打开CSV文件，按照格式读取数据
2. 启动一个事务
3. 判定每行字段数与新表是否相同，不相同则报错回滚事务； 相同则构造一行，调用InnoDB引擎接口， 写入到表中
4. 重复步骤3， 知道吧CSV文件写完。最终提交事物
物理复制法
MySQL5.6 开始引入了可传输表空间，可以通过导出+导入表空间的方式 实现物理表都复制
例如： 复制一个跟t相同的表r
1. 执行 create table r like t, 创建一个相同的表结构的空表
2. 执行alter table r discard tablespace, 这个时候r.ibd文件会被删除
3.执行flush table t for export, 在db1 目录下生成一个t.cfg文件
4. 在db1目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd ;
5. 执行unlokc tables, 这个时候t.cfg 文件自动被删除
6. 执行 alter table r import tablesspace ; 此时r.ibd 文件作为表r的新的表空间，复制成功
MySQL主备方案
同步过程
master接收到客户端的更新请求后， 执行内部事务的更新逻辑， 同时写binlog; slave跟master之间维持一个长连接。master内部有个线程专门用于服务这个长连接
流程图
过程
1. slave上通过change master 命令，指定同步的服务器端口 ， binlog文件的偏移量
2. slave上执行stater slave 命令， 此时slave就会启动两个线程 io_thread, sql_thread ; 其中io_thread是服务跟master建立连接的
3. master校验用户名密码，开始按照slave传过来的位置， 从本地读取binlog发送给slave
4. slave拿到binlog后，写到本地文件， 称之为 中继日志（relay log）
5. sql_thread 读取中继日志， 解析出日志中的明了，并执行
主备延迟
主备机器性能不对称， 从库要比主库性能差很多
备库负荷高，影响了同步的速度，造成延迟
大事务造成延迟
比如 一次性删除很多的数据，这就是典型的大事务场景
大表的DDL
备库的并行复制能力
复制分发策略
复制分发执行的原则
1. 不能造成更新覆盖， 要求更新同一行的两个事物， 分发到同一个worker中
2. 同一个事物不能被拆开，必须放到同一个woker中
MySQL5.5 并行复制策略
按表分发策略
如果两个事物更新不同的表， 他们就可以并行。
按行分发策略
如果两个事物没有更新相同的行， 他们在备库上可以并行执行。这个模式要求binlog格式是：row
相对的， 按行并发执行策略在决定的个线程分发的时候， 需要消耗更多的计算资源
约束条件
主库binlog格式必须是 row, 能够从binlog中解析出表名，主键值和唯一索引的值
表必须有主键
不能有外键，级联更新的行不会记录在binlog中，所以冲突检测就不准确
问题
操作很多行和大事务时，系统负载很高
实现这个策略时，需要设置一个行数阈值。达到这个阈值则退化为单线程模式
两种策略都没有被合并到官方
MySQL5.6并行复制策略
按照库并行处理
MySQL5.7 并行复制策略
利用组提交优化复制策略
特性
1. 能够在同一组里提交的事务， 一定不会修改同一行
2. 主库上可以并行执行的事务，备库上可以可以并行执行
3. 在备库上， 同时处于prepare状态的事务， 在备库执行时可以并行
4. 处于prepare状态的事务与处于commit状态的事务之间，在备库执行时也可以并行
实现
1. 在一组里面一起提交的事务， 有个一相关的commit_id, 下一组就是commit_id+1
2. commit_id 直接写到binlog中
3. 传到备库应用的时候， 相同的commit_id的事务可以被分发到多个worker中执行
4. 这一组全部执行完毕之后， coordinator再去取下一批
MySQL5.7.22 的并行复制策略
基于WRITESET并行复制
新增一个参数binlog-transaction-dependency-tracking, 用来控制是否启用这个策略
COMMIT_ORDER, 根据prepare和commit来判断是否可以并发的策略
WRITESET， 对于事务设计更新的每一行， 计算出这一行的hash值， 组成集合writeset， 如果两个事物没有操作相同的行， 就可以并行
WRITESET_SESSION, 是在WRITESET基础上， 添加了一个约束， 即在主库上同一个线程先后执行的两个事务， 在备库执行的时候， 要保证相同的先后顺序
主备切换
如何判定主库挂了
并发连接
使用show processlist 的结果中， 指得是并发连接， 当前正在执行的语句才是并发线程
并发线程， 参数: innodb_thread_concurrency
控制InnoDB并发线程的上限，默认为0 表示不限制。 一旦并发线程数达到这个值， InnoDB在接受到新请求时，就会进入等待状态直到线程退出。设置过大会导致线程上下文的切换。 一般建议设置为 64 - 128 之间
线程进入锁等待后，并发线程的计数会减一
查表判断
为了检测InnoDB并发线程过多导致系统不可用，一般地可以在系统库mysql中创建一个表，插入一条数据， 定期执行 select * from mysql.health_check;
更新判断
常见做法是放一个timestamp字段，表示最后一次执行的更新时间。 update mysql.health_check set t_modified = now();
内部统计
MySQL5.6添加了performance_schema库， 其中file_summary_by_event_name 表统计每次IO请求的时间
打开 redo log 的时间监控
update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%';
例： 检查单次IO请求时间查过200毫秒
select event_name,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;
数据统计完毕之后把统计信息清空
truncate table performance_schema.file_summary_by_event_name;
基于位点的主备切换
当我们把节点 B 设置成节点 A’的从库的时候可以通过命令进行
其中有个重要的点，在于确定MASTER_LOG_FILE 和 MASTER_LOGPOS，这个表示同步的主库文件的偏移量
按照 mysqlbinlog File --stop-datetime=T --start-datetime=T 大致确定一个相对较前的点，按照这个偏移量进行同步操作
同步过程中出现类似主键冲突，则停止同步，一般由两种做法
1.跳过一个事务，直到不会出现错误位置
set global sql_slave_skip_counter=1; start slave;
2. 通过设置参数slave_skip_errors 直接跳过错误
set slave_skip_errors = '1032,1062'
问题
基于位点进行主备切换， 两种方式的操作比较复杂， 而且容易出错。 MySQL5.6 引入了GTID
GTID
概念
GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是： GTID= server_uuid:gno
server_uuid: 每个MySQL实例的全局唯一标记
gno是一个整数， 初始值为1， 每次提交事务时， 分配这个事务并加1
开启
在启动一个 MySQL 实例的时候，加上参数 gtid_mode=on 和 enforce_gtid_consistency=on
生成方式
1. 如果 gtid_next = automatic, 表示 使用默认值，MySQL就会把server_uuid:gno分配给这个事务。 先set @@SESSION.GTID_NEXT= 'server_uuid:gno' 记录binlog， 然后把这个GTID加入本实例的GTID集合
2. 如果gtid_next 是一个指定GTID的值， 例如 set gitd_next = 'current_gtid'。 如果 current_gtid已经存在于本实例的GTID集合中， 则接下来执行这个事务会被直接忽略； 否则 就分配给当前事务
注意
一个current_gtid只能给一个事务使用， 这个事务提交之后，如果要执行下一个事务就要重新按照上诉两种生产方式重新set新的gtid
主备切换
在 GTID 模式下，备库 B 要设置为新主库 A’的从库；master_auto_position=1 就表示这个主备关系使用的是 GTID 协议
主备延迟解决方案
1. 强制走主库方案
对于刚提交的事务，需要立即获取最新的结果，则需要强制走主库
对于实时性要求不高的数据，即使从从库中读取，稍有延迟也不要紧。根据业务而定
2. Sleep方案
主库更新之后，读取从库先sleep一下。 类似先执行一条sleep(1) 命令。这个前提假设是在1s之内完成同步。 这个方案具有不确定性
3. 判定主备此刻是否存在延迟
1. show slave status; 结果中的seconds_behind_master 如果是0， 表示主从此时无延迟
2. 通过对比位点确保主备无延迟
Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点；
Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。
以上两组值完全相同，表示日志已经同步完成
3. 对比GTID集合确保主备无延迟
Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。
Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；
Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。
以上两个集合相同，则表示日志已经同步完成
4. 配合 semi-sync
半同步复制
1. 事务提交时，主库把binlog发给主库
2. 从库收到binlog之后，发回给主库ack，表示已经收到
3. 主库收到ack以后，才能返回给客户端“事务完成”的确认
5. 等待主库位点
从库执行： select master_pos_wait(file, pos[, timeout]);
参数file和 pos指定的是主库上的文件和位置
timeout可选， 设置为正整数， 表示这个函数最多等待的秒数
返回值 >=0 ，则可以在这个从库上进行查询操作
6. GTID
从库执行： select wait_for_executed_gtid_set(gtid_set, 1);
1. 等待，直到这个库执行事务中包含传入的gtid_set, 返回0
2. 超时返回 1
删库不跑路
1. delete误删除行
使用Flashback工具通过闪回恢复数据行
原理是通过binlog回放，前提是确保binlong_format_row 和binlog_row_image=FULL
2. truncat/drop 误删库表
这种情况下binlog只是记录了truncate/drop语句，此时需要最近全量备份+增量日志的方式恢复数据
MySQL5.6 版本引入延迟复制备库
是一种特殊的备库， 通过CHANGE MASTER TO MASTER_DELAY = N 命令，指定备库持续主库有N秒的延迟
设置一个合理的值， 在做了误删除之后， 立即在这个延迟备库上执行stop slave . 然后再恢复出需要的数据
3. rm删除物理文件
只要数据库的集群正常，仅仅是删除了其中一个节点的数据，MySQL集群就能正常工作
4. 防患于未然
账号分离
开发同学 只给DML权限，不给truncate/drop权限，需要DDL的话通过DBA操作
即是团队成员，日常操作使用只读账号，必要时才使用更新权限的账号
操作规范
删除之前先修改表名， 确保对业务无影响再删除这张表
改表名，要求给表加固定的后缀，删除表必须通过DBA执行，而且只能删除有这个后缀的表
充分的数据备份
异地延迟节点备份， 数据物理文件备份
0 条评论
下一页
