DAY4 SQL完结

binLog二进制归档日志

保存所有执行过的修改操作语句，如果mysql服务意外宕机，可通过二进制日志文件排查用户操作或表结构操作进行数据恢复启用binlog会影响服务器性能，但如果需要数据恢复或者主从复制，开启的好处大于对服务器影响在配置文件[mysqld]新增如下配置

查看binlog参数
- show variables like ‘%log_bin%’;
- show binary logs;
bin log格式
- 使用bin_format设置bin_log日志记录格式
- STATEMENT 基于SQL语句复制，每一条修改数据的SQL都会记录到master机器的bin_log中，这种方式日志量小，节约IO开销，提升性能，但是对于语句中有一些只有执行过程才能确定结果的函数（如UUID()\SYSDATE()等）同步到slave中去，会导致与master机器执行结果不一致
- ROW 基于行的复制，日志中会记录每一行数据被修改的形式，然后在slave端对相同的数据进行修改，虽然可以解决函数、存储过程等在slave机器的复制问题，但是这种方式日志量大，性能更差。
- MIXED 混合模式，以上两种的结合，mysql根据执行的具体SQL来区分判断使用哪种方式记录日志。
binlog 磁盘写入机制
- 由sync_binlog 参数控制
  - 设置为0 每次提交只写入到page cache，依赖操作系统的同步机制写到磁盘，性能最好，但是机器宕机时会丢失数据
  - 设置为1 每次提交事物之前写入到磁盘，数据最安全，保证事物操作不丢失，但是会损失性能
    
    当innodb_flush_log_at_trx_commit=1且bin_log开始时，sync_binlog也应当设置为1
  - 设置为N(n >1) 每次提交事物都写入到page cache，收集到N个事物再写入磁盘，发生宕机时会丢失这N个事物
- 当发生以下事件时，bin_log会重新生成
  - 服务器启动或重启
  - 服务器日志刷新 flush logs
  - 日志文件大小达到max_binlog_size，默认为1GB
binlog文件删除
- 删除当前bin_log文件 reset master;
- 删除指定日志文件之前的所有文件 purge master logs to ‘mysql-binlog.000006’;(删除6之前的所有文件)
- 删除指定日期前的日志索引中bin_log文件 purge master logs before ‘2025-01-11 12:00:00’;
查看binlog文件
- mysqlbinlog --no-defaults -v --base64-output=decode-rows +binlog绝对路径
- mysqlbinlog --no‐defaults ‐v ‐‐base64‐output=decode‐rows D:/dev/mysql‐5.7.25-winx64/data/mysql‐binlog.000007 start‐datetime="2023‐01‐21 00:00:00" stop‐datetime="2023‐02‐01 00:00:00" start‐position="5000" stop‐position="20000"
数据恢复
- 从binlog文件中找到数据丢失的起始和结束位置
  
  起始位置一般找最先丢失数据的那个事物BEGIN之前的一个位置标识结束位置一般找最后丢失数据的那个事物COMMIT之后的第一个位置标识
- 执行恢复命令
  - mysqlbinlog --no-defaults --start-position=219 --stop-position=701 --database=dbName D:/dev/mysql‐5.7.25-winx64/data/mysql‐binlog.000007 | mysql -u root -p password -v dbName (根据位置恢复)
  - mysqlbinlog --no-defaults --start-datetime=”yyyy-mm-dd hh:MM:ss(将binlog文件中的时间戳做转换)” —stop-timw=”yyyy-mm-dd hh:MM:ss” --database=dbName D:/dev/mysql‐5.7.25-winx64/data/mysql‐binlog.000007 | mysql -u root -p password -v dbName (根据位置恢复)
由于bin log占用空间比较大，一般不会长时间保存，万一出现删库或者大量数据丢失的情况，只靠bin log无法恢复正常来说需要数据库做备份，同时使用bin log做归档，采用两者结合的方式进行恢复
- 数据库备份与恢复
  - mysqldump -u root dbName > backFileName; 备份整个库
  - mysqldump -u root dbName tableName > backFileName; 备份整个表
  - mysqldump - u root dbName < backFileName; 从备份文件恢复库
为什么需要redo log和bin log两份日志？ 一个属于InnoDB引擎层，一个属于MySQL Server层，是MySQL分层架构与功能设计分离的体现两者职责不同 redo log 属于InnoDB运行的必需品，记录哪个属于页改了什么，是物理日志，循环复用磁盘空间，用于崩溃恢复 bin log则不是必须的（主从复制场景除外），记录执行了什么SQL，是逻辑日志，追加写入，可以配置保留时间，用于主从复制和数据归档/恢复如果只有redo log，物理页变更无法在不同实例间通用，实现不了主从复制。如果只有bin log，在InnoDB崩溃后，无法从bin log恢复未刷盘的数据页。

Undo Log回滚日志

InnoDB 对undo log采用段管理方式，也就是回滚段（rollback segment），每个回滚段记录1024个undo log segment,每个事物只使用一个

MySQL5.6+，InnoDB支持最大128个回滚段，支持最大同时在线的事物数为128 * 1024

undo log什么时候回收？

新增类型的在事物提交后进行回收，修改类型的在没有任何事物用到该版本信息的时候才进行回收

Doublewrite Buffer双写缓冲

解决部分页写入问题。InnoDB一页是16KB，但操作系统的IO通常是4KB，意味着写一个InnoBD页需要进行四次磁盘IO，假如写入的时候期间断电，那这个页就作废了。于是InnoDB引入双写缓冲区来解决这个问题。

InnoDB在每次将页数据写入数据文件前，先把数据写到双写缓冲区，只有当页面安全写入缓冲区内后，才会将最终的完整数据写入数据文件，这样即使刷盘断电，也能从缓冲区中找到有效的完整页面拿出来重新刷回去，保证数据不会损坏

补充参数
- innodb_thread_concurrency 并发线程数，默认值为0表示不限制，通常配置为与CPU核心数相同或两倍，超过配置线程数则排队等待，不宜配置太大，可能会导致锁竞争严重，影响性能
- innodb_buffer_pool_size 存储引擎buffer pool缓存池的大小，一般配置为物理内存的60%-70%.通常来说，InnoDB存储引擎的缓冲池命中率不应该小于99%
计算缓冲池命中比例 show global status like 'innodb%read%'\G; Innodb_buffer_pool_reads：表示从物理磁盘读取页的次数 Innodb_buffer_pool_read_ahead：预读的次数 Innodb_buffer_pool_read_ahead_evicted：预读的页，但是没有被读取就从缓冲池中被替换的页的数量，一般用来判断预读的效率 Innodb_buffer_pool_read_requests：从缓冲池中读取页的次数 Innodb_data_readsInnodb_rows_read：总共读入的字节数 Innodb_data_reads：发起读取请求的次数，每次读取可能需要读取多个页 innodb_lock_wait_timeout 行锁锁定时间

错误日志

记录数据库启动和停止，以及运行过程中发生任何严重错误的相关信息，当数据库出现故障导致无法正常使用时查看

通用查询日志

记录用户所有操作，包括启动和关闭MySQL服务、所有用户连接开始时间和截止时间，发给MySQL数据库服务器的所有指令等，不论语法正确还是错误，成功与否，都会记录下来。资源消耗大，一般不会启用。

为什么要做这种复杂设计，直接写磁盘不好吗？磁盘随机写性能相当差，高并发场景完全不能适用，这样设计虽然看起来复杂，但是可以保证每个更新请求都是更新内存buffer pool，然后顺序写日志文件，性能提高（更新内存快，顺序写也比随机写快）的同时还能保证数据的一致性。

MySQL8

新特性

新增降序索引联合索引可以指定索引值按倒序
group by不再隐式排序，需手动order by
增加隐藏索引通过设置参数invisible=No，将索引隐藏对用户不可见，但数据库后台会维护索引，用户在使用隐藏索引时，即使使用force index，优化器也不会走该索引。
新增函数索引设置索引时可以加到函数上
上悲观锁新增nowait（立即报错返回）、 skip locked（跳过锁定行）语法
新增innodb_dedicated_server自适应参数，根据服务器内存大小自动配置buffer pool的大小（如果服务器只用于搭建Mysql服务的话可以这么做）
死锁检查控制 innodb_deadlock_detect 用于执行死锁检查，默认打开，比较消耗性能
undo log不再使用系统表空间
bin log日志过期时间精确到秒（以前是按天设置）
新增窗口函数 over(partition by field order by field);

序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()

分布函数：PERCENT_RANK()、CUME_DIST()

前后函数：LAG()、LEAD()

头尾函数：FIRST_VALUE()、LAST_VALUE()

其它函数：NTH_VALUE()、NTILE()
默认字符集由latin1变为utf8mb4
MyISAM系统表全部换成InnoDB表
元数据存储变动表结构文件.frm全部存入了mysql.ibd里
自增变量持久化

8.0之前自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，8.0之后会保持关机之前的值，当自增键发生更新时才会更新
参数修改持久化

set global 设置的变量参数在mysql重启后会失效。

主从复制

什么是复制
- MySQL Replication是官方提供的主从同步方案，也是用的最广的同步方案。Replication(复制)使来自一个 MySQL数据库服务器（称为源（Source））的数据能够复制到一个或多个 MySQL 服务器（称为副本（Replica））。默认情况下，复制是异步的；副本不需要永久连接即可从源接收更新。
- 复制的优势
  - 高可用 通过一定的机制，实现跨主机复制数据，从而获得一定的高可用能力；
  - 性能扩展 由于复制机制提供了多个数据备份，可以通过配置一个或多个副本，将读写请求分散至各个节点，从而获得性能提升
  - 异地灾备 将副本节点部署到异地机房，就可以获得一定的异地灾备能力
- 复制的缺点
  - 没有故障自动转移，容易造成单点故障
  - 主从库之间有复制延迟问题，容易导致数据不一致
  - 从库过多对主库的负载以及网络带宽都会带来很大负担
复制方式
- 基于源的二进制日志bin log复制
  - 副本从源中读取二进制归档日志，并在副本的本地数据库上执行二进制日志中记录的事件
- 基于全局事物标识符(GTID)的方式
  - 完全基于事物复制，很容易确定源和副本是否一致，只要在源上提交的所有事物也在副本上提交，能够保证主从数据一致性
复制数据同步方式
- 异步复制(默认方式)
  - 提交事务和复制这两个流程在不同的线程中执行，互相不会等待，这是异步复制。异步复制的劣势是，可能存在主从延迟，如果主节点宕机，可能会丢数据。
- 半同步复制(5.7新增)
  
  虽然提高了可用性，但是因为有主从节点的网络交互，以及从节点的刷盘消耗，性能会差一些
  - 主节点在收到客户端请求后，必须在完成本节点日志写入的同时，还需要至少等待一个从节点完成数据同步的响应之后才会响应请求
  - 从节点只有在写入relay log并完成刷盘后才会向主节点响应
  - 当从节点响应超时时，主节点会将同步机制退化为异步复制，在至少一个从节点恢复，并且完成数据追赶后，主节点会将同步机制恢复为半同步复制
设计理念：复制状态机
- 任何一个存储系统，无论它存储的是什么数据，用什么样的数据结构，都可以抽象成一个状态机。存储系统中的数据称为状态（也就是 MySQL 中的数据），状态的全量备份称为快照（Snapshot），就像给数据拍个照片一样。我们按照顺序记录更新存储系统的每条操作命令，就是操作日志（Commit Log，也就是 MySQL 中的 Binlog）。