mysql innodb知识总结
2020-09-07 13:44:24 5 举报AI智能生成
登录查看完整内容
mysql
作者其他创作
大纲/内容
范式
1.属性不可分割
2.非主键属性 完全依赖主键
3 非主键属性无传递依赖
引擎
innodb
聚簇索引，数据文件和主键索引在一起
行级锁、表级锁
支持事务
查count需要全表扫描
适用OLTP场景
myisam
非聚簇索引，数据文件和索引文件分开
表级锁
不支持事务
查询count查一个字段就行
不支持全文索引
适用OLAP场景
索引
为什么要索引
提高查询效率
提高排序效率
排序字段是索引的时候，直接借助索引的有序性进行排序
索引优缺点
创建索引和维护索引要耗费时间，每次增删改数据都需要维护索引
索引需要占用一定空间
结构类型
B+树索引
类型
聚集索引(主键索引)
每张表只能有一个聚集索引
索引值不允许为空
逻辑上顺序存储，叶子节点(数据节点)通过双向链表连接
根据主键索引查询不需要回表
覆盖索引
索引上已经包含所有要查询的字段，不需要回表
唯一索引
所有的更新操作都要先判断这个操作是否违反唯一性约束
更新比较多的时候考虑直接使用普通索引
普通索引
前缀索引
相对于全字段索引，节省了空间
使用多长前缀？
缺点就是无法利用覆盖索引了
如果前几位区分度低，后几位区分度高，如身份证，可以将身份证反转后建索引
索引下推
select * from tuser where name like '张%' and age=10 and ismale=1
当查询记录后，先不直接回表，直接通过索引上的age判断是否满足，这就是索引下推
索引重建
为什么要重建
索引因为删除或者页分裂等导致索引页有空洞，重建索引会创建一个新的索引，把数据按顺序插入，提供页面利用率和查询效率
B+树本身不能找到具体的一行记录，只能找到记录所在的页，然后将页加载到缓冲区，再进行二分查找
索引结构
通B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
自适应Hash索引
mysql会自动将访问频率高的热点页建立哈希索引
Hash索引底层就是Hash表，进行查找时，调用一次Hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据
查询效率高
不支持模块查询和范围查询
每次查询到需要回表查询数据
不支持索引完成排序
全文索引
分析
explain
type
system: 表中只有一条数据
const：主键或唯一二级索引列与常量进行等值匹配，匹配一条数据
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描
ref_or_null：普通二级索引与常量进行等值匹配，该索引可能是 null
explain select * from film where name = \"film1\" or name is null
index_merge：查询中同时使用两个(或更多)索引，然后对索引结果进行合并(merge)，再读取表数据。这种连接类型意味着使用了Index Merge优化方法
index: 全索引扫描 。连接类型跟ALL一样，不同的是它只扫描索引树。它通常会比ALL快点，因为索引文件通常比数据文件小
explain select id from tb
all：全表扫描
explain select * from tb
rows
如果使用全表扫描，那么 rows 就代表需要扫描的行数如果使用索引，那么 rows 就代表预计扫描的行数
Extraspan style=\
Using where
mySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤
Using index
表示所需要的数据从索引中就能够全部获取到，而不需要再次从表中查询数据，这意味着mysql将使用覆盖索引
Using index condition
使用索引下推避免了回表
Using index for group-by
类似Using index，它表示mysql可仅通过索引中的数据完成group by或distinct操作
Using filesort
借助slow log收集报警
基于sql查询时间
基于是否使用到了索引
优化
使用自增id作为主键
优点
主键作为聚簇索引，主键自增，有序，B+树插入效率高
自增主键相对uuid占用空间小，索引页上可以存储更多的索引，减少加载次数
主键不关联业务，降低修改概率
如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引，如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的递增ROWID作为隐含的聚集索引
缺点
根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况
id为什么不连续
百万级别或以上的数据如何删除
想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
然后删除其中无用数据（此过程需要不到两分钟）
删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右
分批次单线程循环删除
去除删除数据需要不停操作索引的时间
索引原则
b+索引特性
联合索引+最左前缀匹配原则
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配
=和in可以乱序
使用非空字段，使用0或者一个特殊的值或者一个空串代替空值
索引列不能参与计算
不要使用NOT等负向查询条件
节省空间
索引字段越小越好
使用前缀索引，对长字符串列进行索引时指定一个前缀长度，节省索引空间
对于定义为text、image和bit的数据类型的列不要建立索引
减少索引个数
不要过度索引，索引需要额外的磁盘空间，并降低写操作的性能
较频繁作为查询条件的字段才去创建索引
考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
区分度低的字段，索引效果较差，没有必要在此列建立索引，如性别
更新频繁字段不适合创建索引
尽量的扩展索引，不要新建索引，使用联合索引的特性
锁
锁级别
粒度大
不会死锁
冲突大，并发低
页级锁
粒度适中
冲突一般，并发一般
行级锁
粒度小
容易发生死锁
冲突小，并发大
在InnoDB事务中，行锁是在查询到数据后才加锁的，但是要等到事务结束时才释放
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
锁算法
record lock
gap lock
next-key lock
一致性非锁定读
通过MVCC控制读取当前时间的数据
读已提交隔离级别下，总是读已提交的最新的数据
可重复读情况下，总是读当前事务开始前的已提交的数据
一致性锁定读
select for update
select lock in share mode
死锁
一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数来设置
innodb_lock_wait_timeout 默认50s
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。
innodb_deadlock_detect=on
可重复度下加锁原则
原则 1: 加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。原则 2: 查找过程中访问到的对象才会加锁。 (limit语句)优化 1: 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。优化 2: 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。优化 3: 范围查询：无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止；
事务
事务提交过程
事务日志的提交时两阶段
如果binlog已经写入，redolog是prepare，然后发生crash，恢复后会认可该事务，进行提交
如果binlog没有写入，redolog是prepare，然后发生crash，恢复后不认可该事务，进行回滚
binlog和redlog使用一个xid关联
binlog通过binlog-checksum参数来验证binlog是否完整
注意
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction with consistent snapshot 这个命令。
事务特性
原子性
一致性
事务结束前后，数据库完整性约束没有被破坏
隔离性
并发控制
可串行化
持久性
事务隔离级别
读未提交（read uncommitted）
读提交（read committed）
在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。查询只承认在语句启动前就已经提交完成的数据
可重复读（repeatable read）
在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；查询只承认在事务启动前就已经提交完成的数据
串行化（serializable ）
事务问题
脏读
不可重复读
幻读
undo log
实现 MVCC
在实现MVCC时，每一行的数据中会额外保存几个隐藏的列，比如当前行创建时的版本号和删除时间和指向undo log的回滚指针
每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id
数据表中的一行记录，存在多个版本(row)，每个版本有自己的row trx_id
历史版本的数据行是通过版本号和undo log计算出来的
InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没提交
一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的之前的版本”
更新数据update内部都是先读后写的，而这个读，只能读该行记录已提交的最新的值，当前读
select 加了锁(共享锁或者排它锁)也是当前读
可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。
事务回滚
unlod中存储了相反的sql语句
问题
如何在进行DDL时避免全局锁
在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
select count(?)
myisam会在磁盘上记录总数，直接返回
innodb由于要支持MVCC，每次查询时有多少行是不一定的
count(字段)
遍历整张表，把每行字段取出来判断不为空后，计数器+1，性能最差
count(主键)
遍历整张表，把每一行id取出来判断不为空后，计数器+1
count(1)
遍历整张表，但不取值，每取到一行，计数器+1，性能高于count(主键)
count(*)
mysql做过优化，不会取值也不判断空，直接+1，性能最优
高可用
主从复制
同步
master节点接收用户的写请求，并写入到binlog中，Slave上执行sart slave命令开启主从复制开关
slave通过一个I/O线程与Master建立连接，发送binlog dump指令
Master服务器接收到来自Slave服务器的IO线程的请求后，其上负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程
当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容
slave通过另一个线程SQL thread应用本地的relay log，将数据同步到slave库中
常见处理
数据丢失
如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了
5.6开启半同步复制(semi-sync复制)
主库写入binlog日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的relay log之后，接着会返回一个ack给主库，主库接收到至少一个从库的ack之后才会认为写操作完成了
可以设置超时时间，等待一定时间后从库未返回结果，降级为异步同步
主从数据同步延迟问题
问题原因
大事务，导致同步慢
化大事务为小事务
控制每个事务操作的量，如删除很多数据时，分成多次删除
sql语句操作的数据量大
垂直拆表
大表DDL
借住第三方工具进行ddl操作 pt-online-schema-change
建立一张新表，进行表结构修改操作
操作完毕后将原始表中的数据同步到新表，分批同步
同步完毕之后修改名字并将原始表删除
主多线程写，从库单线程重放
5.7之后使用从库的并行复制
从库开启多个线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志
网络传输延迟
master节点挂载过多的slave节点
一般不要超过5个
主从服务器配置不一样
从服务器承担过多的读操作
使用多级主从结构
show status 查看主从同步延时
Seconds_Behind_Master
对于一些强一致性的业务场景，要求插入后必须能读取到，强制读请求走主库
考虑是否需要拆表，将大表拆分为小表，提高同步效率
修改代码逻辑，不要插了就查，直接update就行
sql上加上特殊标识，代理层解析
/*master*/select * from table_xx
开发专门访问主库的查询api
sleep方案
事务中的所有sql统一都走主库，由于只涉及到一个库，本地事务就可以搞定
双M复制
节点A和B之间总是互为主备关系。这样在切换的时候就不用再修改主备关系
规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系
一个备库接到binlog并在重放的过程中，生成与原binlog的server id相同的新的binlog
每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。
双M可以设置各自的id自增步长不一样，让一个库的自增id都是奇数，另一个库的自增id都是偶数，避免两个库生成的主键发生冲突
auto_increment_increment
MGR复制
在原来的基于日志的复制中，从服务器连接到主服务器并告诉主服务器要从哪个二进制日志的偏移量开始执行增量同步，这时我们如果指定的日志偏移量不对，这与可能造成主从数据的不一致，而基于GTID的复制会避免
什么是GTID，也就是全局事务ID，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID
一个GITD由两部分组成的，分别是source_id 和transaction_id，GTID=source_id:transaction_id，其中source_id就是执行事务的主库的server-uuid值，server-uuid值是在mysql服务首次启动生成的，保存在数据库的数据目录中，在数据目录中有一个auto.conf文件，这个文件保存了server-uuid值（唯一的）。而事务ID则是从1开始自增的序列，表示这个事务是在主库上执行的第几个事务，Mysql会保证这个事务和GTID是一比一的关系
在基于GTID的复制中，首先从服务器会告诉主服务器已经在从服务器执行完了哪些事务的GTID值，然后主库会有把所有没有在从库上执行的事务，发送到从库上进行执行，并且使用GTID的复制可以保证同一个事务只在指定的从库上执行一次，这样可以避免由于偏移量的问题造成数据不一致。
高可用架构
原理
对主从复制集群中的Master节点进行监控
master节点不可用后，修改vip指向新的master节点，客户端连接vip
配置集群中的其他slave节点同步新的master节点
MHA
架构
一个master节点
2到n个从节点
操作
主服务器宕机后，选举具有最新更新的Slave从节点
尝试从宕机的master中保存binlog
应用差异的中继relay_log到其他Slave从节点
将从master保存的binlog应用到这个slave节点中
提升这个slave节点作为新的master节点，将vip指向这个master节点
配置其他slave节点去新的master节点同步数据
优缺点
支持GTID主从复制
可以从多个slave节点中选择最合适的节点作为新的master
尽可能减少丢失事务
会尝试从宕机的master中尽可能多的保存未同步的日志
需要自行开发vip迁移脚本
只监控master节点的监控，没有对slave节点进行监控和高可用
MMM(3M)
两个master节点
一个做为主
业务上同一时刻只允许对一个主进行写入，另一台备选主上提供部分读服务
一个做为主备
双向同步
一个监控节点 monitor
监控两个master的状态
一般是把其中一个slave做monitor
n个slave节点，同步主节点
2个vip
主和主备之间vip
多个从节点的读vip
主服务器宕机后，主备服务器设置read_only=off
主备服务器迁移写VIP到自己
从服务器切换指向新的主服务器
提供了读写VIP的配置，使得读写请求都可以做到高可用
工具包相对完善，不需要额外开发脚本。
故障转移过程中可能会丢失数据
主备的复制偏移量不一定比从库的大
只支持基于日志点的主从复制，不支持GTID复制
MMM无法完全的保证数据一致性，所以MMM适用于对数据的一致性要求不是很高，但是又想最大程度的保证业务可用性的场景
分库分表
什么时候拆分
能不切分尽量不要切分
数据量过大，正常运维影响业务访问
随着业务发展，需要对某些字段垂直拆分
数据量快速增长
安全性和可用性
拆分方向
垂直拆分
不经常用或字段长度较大的字段拆分出去到扩展表中
垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表
避免一条记录占用空间过大会导致跨页，造成额外的性能开销
数据库以行为单位将数据加载到内存中，表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。
解决业务系统层面的耦合，业务清晰
对不同业务的数据进行分级管理、维护、监控、扩展
高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈
部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
分布式事务处理复杂
依然存在单表数据量过大的问题（需要水平切分）
水平拆分
当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了
不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
应用端改造较小，不需要拆分业务模块
跨分片的事务一致性难以保证
跨库的join关联查询性能较差
数据多次扩展难度和维护量极大
按数值范围range来分
每个库一段连续的数据
容易产生热点问题
扩容迁移的时候方便
数值hash取模
扩容麻烦
拆分模式
代理模式
扩展性强
能够处理非常复杂的需求，不受数据库访问层原来实现的限制
无侵入
直接将proxy当做mysql客户端连，对于应用服务器透明且没有增加任何额外负载
架构复杂
应用需经过代理来连接数据库，网络上多了一跳，性能有损失且有额外风险。
运维成本高
需部署和运维独立的代理中间件
mycat
性能降低30%
不适合链表查询
不适合大数据导入导出
业务侵入小
代理本身需要做高可用
atlas
sharing-sphere
客户端直连模式
扩展性一般
限于只能在数据库访问层上做文章，对于比较复杂的系统可能会力不从心
业务侵入性高
应用直连数据库，降低外围系统依赖所带来的宕机风险
集成成本低，无需额外运维的组件
将分片逻辑的压力放在应用服务器上，造成额外风险
sharding-jdbc
分库分表切换方案
双写
同时写新库和老库
数据做同步
可能产生脏数据
切换追加
从库摘除
主库binlog记录
从库导入新库
数据校验
binlog追加
canal工具
开启写新库
基本路由问题
库路由
表路由
全局主键避重问题
uuid
不建议
全局数据库表维护一个主键递增
存在单点问题，强依赖DB
不同表使用不同的步长
扩展分片表的时候会有问题
区间段分配
每次申请一个区间段的id，避免每次申请，当区间段快不够用时，提前去申请
使用全局的id生成器
12位日期+10位IP+6位序列ID+4位数据库扩展位
12位日期：格式为yyMMddHHmmss，意味着本方案的id生成策略可以使用到2099年，把时间部分前置，从而保证趋势递增。
10位ip：利用ip to decimal算法将12位的ip转为10进制数字。通过ip地址，来保证全局唯一。如果ip地址被回收重复利用了，也不用担心id的唯一性，因为日期部分还在变化。
6位序列id：意味着每秒最多支持生成100百万个id(0~999999)。不足6位前置补0，如000123。
4位数据库扩展位：为了实现不迁移数据的情况下，实现动态扩容，其中2位表示DB，2位表示TB，最多可扩容到10000张表。假设每张表存储1000万数据，则总共可以支持存储1000亿条数据。
跨库join
字段冗余
反范式，需要跨库join查询的表字段，冗余到本表中，如username之类的
全局表
每个库里面都存储一份
业务层数据组装
多次查询在业务层组装数据
ER分片
将存在join关系的表尽量分片到同一个库中
非partition key的查询问题
依据查询条件进行分片
映射表，建立非partition key到patition key的映射表
基因法
例如partion key的后4位用于分片，可以分16片
生成非partition key的时候将partition key的后4位添加到非partition key的后4位中
使用消息队列将数据存储到分布式存储中间件中，查询这里
tidb es等
分页排序及count、sum等函数失效
分布式事务
数据迁移、扩容问题
升级从库法
双写迁移法
修改应用配置和代码，加上双写，部署
将老库中的老数据复制到新库中
以老库为准校对新库中的老数据
修改应用配置和代码，去掉双写，部署
mysql(innodb)
执行过程
连接器
管理连接
长连接
建立连接后，一直使用会使用同一个连接
可能会导致内存占用太大
定期断开长连接
短连接
每次执行完几次查询之后就断开，下次查询再重新建一个
权限验证
命令
show processlist
配置
wait_timeout
连接长时间不用，会自动断开，默认8小时
查询缓存
一般都是关掉
query_cache_type=DEMAND
弊端
更新频繁，导致缓存频繁被清空
命中概率低，需要sql和查询条件全一样，hash之后才能找到
解析器
词法分析
语法分析
优化器
决定使用哪个索引
扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少
一个索引上不同的值越多，这个索引的区分度就越好
mysql通过采样得到区分度，选择N个数据页，统计这些页上面的值，得到一个平均值
使用 force index 强行选择一个索引
执行器
多线程模型
Master Thread
异步将缓冲池数据(脏页)刷新到磁盘
double write
刷新日志缓冲区
总是把redolog刷新到磁盘，即使事务还未提交
合并插入缓冲
IO Thread
负责异步IO的回调处理
Purge Thread
回收提交事务后的undolog页
清除page里面带有delete_bit表示的数据行
mysql删除是假删除，delete操作并不真正删除数据行，只是在记录上标识Delete_bit，最后由purge线程去完成
内存
缓冲池
数据页(脏页)
缓冲池LRU中被修改的页，跟磁盘上页数据不一致
刷新策略
redo log写满了，此时MySQL会停止所有更新操作，把脏页刷到磁盘
系统空闲时，MySQL定期将脏页刷到磁盘
关闭数据库时全量刷新脏页
使用数据的时候首先将磁盘上的数据页加载到缓冲池中
是按页加载的
两次写
解决的问题
partial page write
nnoDB的Page Size一般是16KB，其数据校验也是针对这16KB来计算的，将数据写入到磁盘是以Page为单位进行操作的。由于文件系统对一次大数据页的写操作不是原子操作，如果服务器宕机了，可能只做了部分写入。会导致partial page write问题。
方案
doublewrite由两部分组成
内存中的doublewrite buffer，其大小为2MB
磁盘上共享表空间中连续的128个页，即2个区(extent)，大小也是2M
在写数据页之前，先把这个数据页写到一块独立的物理文件位置（ibdata），然后再写到数据页。这样在宕机重启时，如果出现数据页损坏，在应用redo log之前，需要通过该页的副本来还原该页，然后再进行redo log重做，这就是double write。
步骤
脏页进行刷新到data file的时候，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的double write buffer
通过double write buffer再分两次、每次1MB顺序写入共享表空间的物理磁盘上
马上调用fsync函数，同步脏页进磁盘上
double write页的存储时连续的，因此写入磁盘为顺序写，性能很高
完成double write后，再将doublewrite buffer中的脏页数据写入实际的各个表空间文件(离散写)；(脏页数据固化后，即进行标记对应doublewrite数据可覆盖)
doublewrite的崩溃恢复 与 Redo log 恢复
如果操作系统在将页写入磁盘的过程中发生崩溃，在恢复过程中，innodb存储引擎可以从共享表空间的doublewrite中找到该页的一个最近的副本，将其复制到表空间文件，再应用redo log，就完成了恢复过程。因为有副本所以也不担心表空间中数据页是否损坏
参数
innodb_max_dirty_pages_pct
脏页比例上限，默认75%
索引页
存储的是索引
undo页
存储的是undolog段
插入缓冲
针对的是非唯一索引，提高非唯一索引的插入效率
它是一种应用在非唯一普通索引页不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更(buffer changes)，等未来数据被读取时，再将数据合并(merge)恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘IO，提升数据库性能
innodb_change_buffer_max_size 插入缓存大小，默认值25%
自适应哈希索引
锁信息
数据字典信息
数据结构
LRU列表
是什么
为了避免频繁的去磁盘读取数据页，将加载后的数据也缓存到内存中
内存是有限的，当内存不足的时候需要将不需要的数据页淘汰并刷新到磁盘
LRU就是缓冲池的淘汰策略
一次查询的数据页有可能只被用一下，并不是热数据，导致真正的热数据被淘汰到磁盘
解决方案
将LRU分为两段，新生代连接老年代
新生代
存储真正的热数据
老年代
每次新加载的数据页先放到老年代头部，内部不够再淘汰老年代尾部
老年代的数据只有筛选后是真正查询的数据并且在老年代待够一定时间窗口后才会被放到新生代头部
innodb_buffer_pool_size
调整缓冲区大小
innodb_old_blocks_pct
设置插入位置，默认37
innodb_old_blocks_time
设置页读取到mid位置需等待多久才会被加入到LRU的热端，默认1000ms
redolog缓冲
redolog先写入缓冲，异步写到日志文件
文件
error log
记录mysql 启动、运行、关闭情况的日志，用于定位问题
show variables like 'log_error'\\G 查看错误日志位置
慢查询日志
mysql将慢查询语句收集到表 mysql.slow_log
log_slow_queries
是否收集慢sql开关
long_query_time 默认10s
运行时间大于该配置的sql会被收集到slow_log中
log_queries_not_using_indexes
如果sql没有走索引也会被收集到slow_log中
一般查询日志
用于跟踪记录查询日志
中继日志relay log
用于主从同步
表空间文件 .ibd
按表空间存储数据库数据
innodb_file_per_table
配置每个表数据放到单独的一个表空间下
binlog
记录updata或者insert语句，不会记录select和show语句
追加写入binlog日志文件，当量大的时候会进行拆分
仅在事务提交前，一次写入，记录binlog
当一个线程开启一个事务后，会自动分配一个大小为binlog_cache_size的缓存，默认32k
用途
数据恢复
作为数据库的备份文件，一旦出问题可以通过binlog基于时间点来进行恢复
注意，可能会定时清理binlog，只保留最近一段时间内的binlog
从节点使用主节点的binlog进行同步复制
审计
根据二进制日志判断是否受到攻击
格式
statement
默认模式
记录的是客户端执行的sql
binlog日志量小，节省磁盘IO，提高同步效率
raw
记录的不再是单纯的sql，而是每行更改情况，更新前的行数据和更新后的行数据
阿里的canal要求binlog格式必须是raw
单条记录的更新sql更加可靠和稳定
导致binlog体积变大，主从复制网络开销大
同步一般适用raw模式
mixed
默认采用statement格式，特殊情况下会使用raw
因为有些statement格式的binlog可能会导致主备不一致，所以要使用row格式
但row格式的缺点是，很占空间。比如你用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间
mixed格式的意思是，MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。
max_binlog_size 单个二进制文件最大值 1G
binlog_cache_size 缓冲区大小
默认大小 32k，当一个线程开启一个事务后，mysql会自动为该事务分配一个大小为binlog_cache_sieze的缓冲，如果事务记录大于缓冲，mysql会将缓冲中的内容写到一个文件中，性能会下降
sync_binlog
0
1
事务提交后，将二进制文件写入磁盘并立即执行刷新fsync()操作
2(N)
表示每次提交事务都write，但累积N个事务后才fsync
innodb_support_xa
expire_logs_days
指定保留binlog的天数，0代表全保留不清理
redo log
用于宕机后恢复缓冲区脏页的数据，实现事务的原子性和持久性
redolog是物理日志，记录的是关于每个页更改的物理情况
顺序IO写入，性能高，将事务提交时对磁盘的随机写转换为顺序写
事务执行时先写入缓冲池脏页，再写入redolog缓冲， 然后提交事务
在写redolog文件前先写入redo log缓冲，实现批量写
redo log缓冲写入redo log文件规则
事务提交时
根据innodb_flush_log_at_trx_commit配置决定
Master Thread每秒将日志缓冲刷新到文件中，无论事务是否提交
当redo log buffer中一半内存已被使用时
log checkpoint时
在磁盘上至少有两个文件 ib_logfile0 ib_logfile1
redo log采用了循环写机制，ib_logfile0写满了就写ib_logfile1，ib_logfile1满了就重写ib_logfile0。
基于循环写，一个文件写满之后，会写另一个文件，然后覆盖，覆盖前会将缓冲池脏页数据刷新到磁盘
checkpoint
当前要覆盖的位置，覆盖前要确保缓存中脏页刷盘
write pos
当前写日志的位置，一边写一边后移
checkpoint机制
InnoDB缓冲区不能无限大
redo log文件也不能无限追加
解决数据库恢复慢的问题
让redo log对应的脏页少点
分类
Sharp Checkpoint
数据库关闭时将所有的脏页刷新到磁盘中去
Fuzzy Checkpoint
根据条件刷新脏页
Checkpoint机制其实就是什么时间将InnoDB缓存中的脏页刷新到磁盘中去，其隐含的语义是：保持redo log中的日志信息与磁盘数据一致
当缓冲区中的脏页太多(75%)的时候就触发Checkpoint，将其中的脏页刷新到磁盘中去
write pos追上checkpoit时触发刷新脏页，然后checkpoint向前走
数据库恢复的时候只需要重做chekpoint之后的日志，checkpoint表示已经完整刷到磁盘上data page上的LSN，因此恢复时对比数据页的LSN和redolog的LSN，仅需要恢复从checkpoint开始的日志部分，缩短恢复时间
redolog缓冲和磁盘都是以块(512字节)存储，往磁盘写入的时候是一个扇区大小512字节整个写入，可以保证写入的成功，不需要doublewrite
redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗
事务日志具有幂等性，所以多次操作得到同一结果的行为在日志中只记录一次。二进制日志不具有幂等性，多次操作会全部记录下来，在恢复的时候会多次执行二进制日志中的记录，速度就慢得多
innodb_log_file_size
配置重做日志文件大小，默认5m
innodb_log_buffer_size
log buffer的大小，默认8M
innodb_log_files_in_group
配置重做日志组中重做日志文件个数
innodb_flush_log_at_trx_commit
事务提交时不会将log buffer中日志写入到os buffer，依赖主线程定时每秒写入os buffer并调用fsync()写入到log file on disk中
事务提交时将redolog缓冲同步到磁盘，并执行fsync写入磁盘文件
2
每次提交都仅写入到os buffer，依赖操作系统调用fsync()将os buffer中的日志写入到log file on disk
结构
MySQL只会有一个行记录，行记录中有事务id trx_id和回滚指针 roll_ptr
undo log通过回滚指针连接在一起
提供回滚和多个行版本控制(MVCC)。
用于保证事务的原子性、一致性，用于事务回滚
事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性
当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。
undo log是逻辑日志，根据每行数据进行记录
insert undo log：记录对应insert语句的delete语句
事务提交后可以直接删除purge
update undo log：记录update语句或delete语句的逆向upate语句
需要提供MVCC支持，放入undo log链表，等待purge线程最后删除
如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。如果是主键列，update分两部执行：先删除该行，再插入一行目标行。
随机写入
日志存储在undo 段中，undo log存放在共享表空间中
事务提交后并不能立即删除undo log，因为其他事务可能通过undo log得到之前的版本，需要通过多个版本计算
但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。
undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。
当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。
表
逻辑结构
Tablespace
表空间，若设置innodb_file_per_table则一张表一个tableSpace
segment(段)
数据段
索引段 (innodb索引段就是数据段)
回滚段
extent(区)
默认大小为1M，为64个连续的页
page(页/块)
默认大小16k
通过innodb_page_size修改页大小
磁盘加载的最小单位
行
设置缓冲池大小
设置redelog 缓冲大小
服务器优化
0 条评论
回复 删除
下一页
