mysql核心原理

---

1.mysq5种日期类型

1.year 1 0000
2.date 4 yyyy-,mm-dd
3 time 3 00:00:00
4.datetime yyyy-mm-dd 00:00:00
5.timestamp 4 123456464898

2.mysql写入数据原理：

1.client发送sql语句
2.mysql server会通过连接器连接客户端
3.server的分析器会对sql此法语法分析
4.server的优化器会对sql语句进行优化
4.存储引擎innodb进行sql执行
5.innodb将修改操作的旧数据写入undolog（回滚日志）
6.innodb将修改语句放入bufferpool，并将修改的语句放入内存的redolog缓存中
（redolog刷盘策略3种）
7.通过刷盘策略将redolog缓存中的数据写到redolog文件，此时写入binlog日志
8.写入binlog成功后，引擎会将redolog中事务日志进行commit标记

注释：relaylog中继日志，用于mysql主从同步时用到的日志文件

3.mysql主从同步原理：

1.master将数据的变更写入binlog
2.slave开启io线程拉去数据，保存在本机的relaylog中
3.slave开启sql线程重复的去relaylog中读取并写入

4.主从同步的延迟问题

4.1 MTS 多线程salve复制

在哪里使用多线程？开启多个sql thread线程

mysql主从同步：
延时问题：由于sqlthread单线程写效率跟不上

解决：MTS的协调器
协调规则：
1.同一行的多个事务，必须分发到同一个worker中执行
2.同一个事务必须放到分发到一个worker中执行

worker个数=cpu核心数（一般情况）

分发粒度：
库为单位：worker标识为db
表为单位：worker标识db.table
行为单位：worker标识db.table+唯一标识

缺点：前面分发粒度方案：实现比较复杂
后面GTID：全局事务id = 服务器唯一标识+递增的事务ID

4.2 基于MTS的GTID：

GTID概念：
1.GTID与事务一一对应，在master与slave之间全局唯一
2.一个服务器上只能执行一次同一个GTID
GITD作用：用来强化主备数据的一致性，故障恢复及容错能力。取代传统的主从复制（基于binlog文件的position异步复制）

binlog与GTID的关系：
binlog文件中记录了：1.binlog的版本信息，元数据信息，2.之前binlog文件存储的GTID集合set；3.多个GTID事件。

GTID的复制原理：
1.从服务器向主服务器发送已经执行的过的GTID = 5
2.主服务器将所有GTID大于5的GTID发送给从服务器
3.同样的GTID不能被执行两次，如果有同样的GTID，会被自动skip掉

5.binlog的日志格式：

statement：sql执行前后的变化
row：每行数据的变化
Mixed: 在无法用statement记录的时候，会使用row的方式记录

6.为什么需要两阶段提交？

保证服务挂机后重启依然能够恢复数据；
1.当redolog中有数据，binlog中没有数据，那么该数据恢复时无效
2.当redolog中有数据，binlog中有数据，redolog中未commit时，恢复时认为数据有效

什么事mysql组提交?
大致原理：多组事务累计后进行刷盘提交；
作用于：解决redolog和binlog刷盘性能瓶颈

所有日志数据写磁盘时，会有如下几个阶段：
1.当前进程的内存空间->2.操作系统的内存空间->3.fsync异步刷盘（追求一次写更多的数据，效率高，但如发生数据库挂了，丢数据多。这个就是组提交）

7.mysql双1刷盘策略：

redolog刷盘策略：innodb_flush_log_at_trx_commit
三个值：
0：每秒将buffer写入到redolog file，同时刷盘；
1：每次事务将buffer写到redolog file，并且刷盘
2：每次事务将buffer写到redolog，mysql默认1秒刷盘

binlog刷盘策略：sync_binlog
三个值：
0：事务提交后只写到binlog buffer，等待os系统刷盘
1：每次事务提交都刷盘
n: 每次事务都写到binlog buffer，n此事务后刷盘

组提交：可以理解为对双1操作的优化
处理日志的一种方式，主要是为了解决频繁刷磁盘的问题，提高写日志效率
设置：时间：延迟多少微妙 刷一次盘
数量：事务达到多少数量，刷一次盘

事务特性：ACID
原子性:undolog实现
持久性:redolog实现
隔离性:加锁和mvcc实现

隔离级别：读已提交和可重复读都是基于MVCC实现

8.MVCC原理: 不加锁的方式提高数据库的读写性能

每行数据的隐藏行=事务id，回滚指针（用于回滚到上一个版本）

参考：https://www.bilibili.com/video/BV1t5411u7Fg/?spm_id_from=333.337.search-card.all.click&vd_source=2bd125b0100126fe4ea30c5e93123e55

MVCC主要数处理读请求：
读：快照读，普通的select操作

当前读：
1.select lock in share mode(共享锁）
2.selct for update (排他锁）
3.update/delete/insert （排它锁）
4.串行化的事务隔离级别

MVCC三个概念：
1.版本链：
版本链=undolog+回滚指针链接起来
2.undo log：
回滚日志
3.readview（快照）：当前快照读对应版本链中的那一个版本记录

readview的数据结构：
1.m_ids: 在生成readview时，当前表/系统中未commit的事务id列表
2.min_trx_id: 最小的事务id
3.max_trx_id: 生成readview时系统应该分配给下一个事务的id
4.creator_trx_id: 生成readview的事务id

readview可见性原则？
1.行中的trx_id == creator_trx_id：可以访问这个版本（即这个就是当前trx_id创建的view）
2.行中的trx_id < min_trx_id：可以访问这个版本（当前的事务已经commit了）
3.行中的trx_id > max_trx_id：不可用（下一个事务的id还未生成，生成的readview不可用）
4.行中的min_trx_id <= trx_id <= max_trx_id： 如果trx_id在m_ids中有，则不可访问这个版本（因为m_ids都是未提交的事务）；反正则可以（在里面没有，说明中间部分事务已经提交）

RR与Rc生成readview时机不同：
rc：每次select的时候直接生成，以每次select查询为单位
rr：以一个事务为单位，每个事务select得到的readview是一样的

rr中如何同readview解决幻读的问题？
使用间隙锁，锁住一段范围；新增或修改删除的数据，如果在这个范围中，那么读取不到数据的变化；
mysql 默认rr隔离级别，默认开启了间隙锁

9.mysql锁的分类：

加锁机制： 乐观锁和悲观锁
兼容性机：共享锁和排他锁
锁的粒度：全局锁、表锁、行锁、页锁
全局锁：对整个数据库加锁
表锁：对整个表加锁
行锁：对行数据加锁
页锁： BDB 存储引中。
锁的模式：记录锁、间隙锁、临界锁、意向锁（意向共享锁，意向排他锁）、插入意向锁、自增锁；
临界锁：记录锁和间隙锁的组合
意向锁：表级锁
插入意向锁：间隙锁锁范围，间隙锁锁范围中的某一个

mysql常用存储引擎：myisam，innodb，memory，csv等
索引类型：

数据结构角度：
btree
hash
fulltext
rtree

物理角度：
聚集索引
非聚集索引

逻辑角度：
普通索引
唯一索引
主键索引
组合索引
全文索引