Sianao

title: mysql_pre
date: 2024-07-08 23:10:56
tags: mysql

cover: https://static.sianao.site/static/16c11f929645cf8ec5ab68e2338cd215.svg

ACID

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

隔离级别

脏读：读取到了其他事务未提交的数据。
不可重复读：读取到了其他事务提交的数据。
幻读：读取到了其他的事务的插入的结果。

执行

查询

连接器：首先由客户端与连接器建立连接，
查询缓存（8.0）已经不存在了。
分析器（分析语法是否存在错误）。

词法分析，提取关键字，语法分析，分析是否是正确的SQL语句

优化器（诸如使用哪一个索引）。选取最优的方案。
执行器，执行器通过操作数据库存储引擎返回数据。首先会查看是否存在权限。再调用存储API

更新

更新涉及到日志问题，在日志模块默认的是binlog 归档日志和rredolog 重做日志模块。

调用存储api写入数据，InnoDB把数据保存在内存中，同时记录redolog,此时redolog进入prepare阶段，然后告诉执行器执行完了，执行器收到消息后记录binlog,调用引擎接口提交redolog为提交状态,总之是采用了一种WAL也就是先写日志再写磁盘。有了redolog就可以实现crash-safe binlog主要用于恢复数据是一种增量的追加写的过程。redolog是循环写。

索引

索引提高了查询效率，索引有哈希表，但是哈希表只适用于等值查询的条件。
有序数组在等值查询和范围查询中都很优秀。但是只适用于静态数据存储
InnoDB使用了B+树索引模型。

数据都是存在B+树中的叶子节点，B+树可以很好的配合磁盘的读写特性，减少单次查询的磁盘访问次数。

根据索引类型，可以分为主键索引和非主键索引。

主键索引又叫做聚簇索引。
非主键索引又叫做二级索引。

利用到了非主键索引查询的时候并且非主键索引没有需要的元素的时候（没有覆盖索引）还需要进行回表操作。

覆盖索引

可以减少回表次数但是会增加磁盘使用空间，造成额外的空间使用。

重建索引可以提高页的利用率，使得空间更加紧凑。

索引下推

在数据过滤过程中，通过判断索引中的条件减少回表的次数。

函数覆盖，对于一个索引直接使用的话，可以用到索引，但是要是使用了函数，就不会进行索引操作而是使用了全表扫描。同时要是使用的时候发生了类型转换，也不会用到索引。同时，要是做联合表查询的话，字符集不一样的话，也会用不上索引。

锁

全局锁

对整个数据库加锁，做全库备份逻辑，阻塞大部分操作。

但是大部分备份还是使用mysqldump –single-transaction 拿到一个一致性视图。

表锁

1
2
3

lock table table_name read;
lock table table_name write;
unlock tables

另一类锁是MDL 也就是元素锁，在访问一个表的时候自动加上锁。

读锁不互斥
写锁与其他是互斥的。
Online DDL 在表上执行DDL操作不阻塞并发的数据库的shen操作和查询。
两阶段锁协议

行锁是在需要的时候加上的，并不是不需要了立即释放，而是等到事务结束时commit rollback时才释放。

死锁

死锁解决方案
- 直接进入等待，直到超时，可以通过控制innodb_lock_wait_timeout
- 发起死锁检测，发现死锁后主动回滚死锁链条的某一个事务。innodb_deadlock_detect开启
事务开启的时候：第一条SQL语句执行的时候。或者显式开启

间隙锁（Gap Lock)

对于间隙锁就是锁住两个值之间的空隙，如图这里六个记录的话，就锁住了七个间隙

间隙锁和间隙锁是兼容的，间隙锁和插入操作是冲突的。

临界锁（next-key lock)

临界锁是间隙锁+记录锁汇合起来的，临界锁是前开后闭区间，间隙锁的引入可能会导致锁住更大的范围，对并发受到影响。

插入意向锁

对于插入语句的时候会在插入的间隙插入插入意向锁，插入意向锁和间隙锁是冲突的，只有等待其他事物释放间隙锁之后，才能获取到插入意向锁。

加锁规则

对于加锁是先生成锁结构，然后设置锁的状态，只有当锁状态为正常的时候才表明锁加锁成功了。

加锁的基本单位是next-key lock

唯一等值查询：

索引上的查询或者唯一索引加锁的时候，退化成记录锁
记录不存在的时候退化成间隙锁

非索引等值查询

查询记录存在时，会对查询到的二级索引加的是临界锁，在符合记录的主键索引上会加记录锁
查询记录不存在的时候，扫描到不符合条件的二级索引记录的时候会退化成间隙锁，不会对主键进行加锁，因为没有主键。

没有索引的加锁是锁住整张表

死锁

互斥、占有且等待、不可强占用、循环等待

快照

每一个事务都有一个唯一的事务ID 叫做 transaction id 是在事务开启的时候申请的是严格递增的

而每一行数据都有多版本，每次事务更新的时候会生成一个新的版本，并把事务id赋值给这个数据版本的事务id记录为row trx_id

读取：

事务启动的时候会构造一个当前活跃的事务的数组，也就是启动了还没提交的事务。

当一个事务进行查询的时候当前事务只能看见小于当前事务的并且不在活跃事务的数组里的。

更新

更新数据都是先读后写，而这个读是当前读不是快照读

同时要是select加上了锁也是当前读

在更新和读取的过程中也会执行二阶段锁协议。

Change Buffer：

innodb在更新的过程中，如果数据页在内存中就直接更新，要是还没在内存中的话会保存更新操作到change buffer中去，要是下次查询需要访问这个数据页的时候，将数据页读入内存中去，然后执行change buffer中有关该页的操作，同时会进行merge操作，归还到原始页中去。但是唯一索引不能用到change buffer,change buffer只适用于写多读少的情况，能够很大的程度提高io性能。

索引修改，可以通过暂存减少对索引节点的随即写操作
数据页修改，缓存对数据页的操作，延迟到后续的合适时间再操作
减少IO压力，通过合并多次写操作，减少对磁盘的随即访问。

强制索引

优化器可以通过索引统计数据来选择索引，但是有时候需要矫正analyze table

当自己清楚自己要干什么并且知道怎么做才能够提高查询效率的时候可以通过强制索引，避免优化器分析带来的问题

1	force index(column_name)

字符串索引

可以对字符串加索引，也可以加前缀索引，通过前缀索引，要是设立得当的话，可以很大程度上的节省空间和不用增加太多的查询成本。

同时可以采用倒序粗出，比如身份证号码通过倒叙存储，或者采用hash函数来进行操作。

脏页刷新

InnoDB脏页刷新，当内存和硬盘的数据不一致的时候，被称作为脏页，对于脏页，需要通过flush进行刷页操作。发生刷页操作可能有以下几种可能：

redo log 写满了
内存淘汰也就是buffer pool 满了
重启或者计划任务。

表空间

对于一个删除操作，一般情况下只是把原来的位置标记为可用并没有真正的删除掉，同时要是插入的话也会导致导致数据页的分裂
为了有时候收缩表空间的话，可能需要重建表来去除掉空洞

排序

对于使用了排序的SQL语句，会甚勤给一个sort_buffer用于排序，要是分配的内存空间不够的话，需要使用磁盘辅助排序。

排序一般分为全字段排序和rowid排序,对于数据量大的就是采用rowid排序也就是多了一次回表操作，先按照要排序的排好序，然后根据排好了的数据来获取原始数据。除了这样的方式，还可以使用创建一个联合索引，通过联合索引来构建数据，既是有序的，也不需要回表，只是额外占据了存储空间。

慢查询分析

索引设计问题可以通过online ddl进行索引修改
SQL语句没写好,通过explain 分析执行流程。
MYSQL选错了索引通过force idnex进行强制索引

一致性

binlog : 先把日志写入bin cache 事务提交的时候再把缓存写进binlog之中去对于这个操作，是通过调用write 写到缓存中去再通过fsync 持久化

redolog : 事务在执行的过程中，先写进redo log buffer 中，再写进去文件系统的page cache 里在持久化到磁盘中去，除此之外，还有一些其他的机制

redo log buffer 占用空间即将达到innodb_log_buffer_size一半的时候，
事务提交的时候顺道写到磁盘中

由于redolog和binlog两个都需要刷盘操作，一次事务可能会导致两次写盘。这样会发生多次io操作。所以这个就需要用到组提交。

两阶段提交顺序。

高可用

主备

主要是通过分享主库的binlog来实现的

binlog三种格式

statement

真实的记录sql语句，通过重放sql语句来实现语句。

没有原句，而是对于event 但是很占用空间。以行的形式记录被更改的行数据。此格式记录的是行级别的变更，包括每一行被更改的具体内容。

mixed

两者混合的结果，根据判断的结果来做处理。

对于多M或者多S可以通过server id来排除死循环的机制。

并行复制策略

由于主备的原因，我们需要开启多个线程的复制，来提升速度，为了防止数据不一致的问题

按表分发策略：同的表可以并行，跨表的事务需要放到一起
按行分发策略：两个事务没有更新相同的行，可以并行执行，binlog格式必须是row格式为了考虑唯一id,可以使用哈希函数来操作。同时可能会存在一些性能的损耗和空间的占用。
按库分发策略：以数据库作为分发单位。不需要row格式

并行复制的要求：

binlog必须是row
表必须有主键
不能有外键，表上如果有外键，级联更新的行不会包含在binlog里，冲突检测不准确。

按行分发的策略的缺点：

耗费内存
耗费cpu

不同版本实现的不同的提交策略：

mariadb 组提交（长事务拖慢进度）
mysql可以在通过锁冲突检测也就是在写redolog的时候就可以同步

一主多从

当主节点挂掉后，需要进行切换操作，同时切换操作可能带来一些问题，比如插入时唯一键冲突，删除时找不到行，可以忽略这些策略

mysql 5.6引入了GTID的主备复制，太多了不看我又不是DBA

读写分离

读写分离可能发生过期读的现象，对于处理过期读的方案

强制走主库
sleep方案
判断主备无延迟
配合semi-sync方案
等主库位点方案
等GTID方案

日志

undo log

保障事务的acid原子性，是通过记录操作内容并反向操作实现的。

主要结构

roll_pointer:回滚指针，组成链表
txr_id 事务id 记录是哪一个事务修改的记录

通过undo log实现了mvcc

Buffer Pool

内存缓冲池，提高数据的读取写效率，同时要是内容被修改了，内存缓冲池的内容被认为是脏页，需要进行刷盘操作。

索引叶
缓存页
undo页

对于Buffer Pool来说，主要有空闲页和脏页，空闲页是通过Free链表连接起来的，脏页也是通过链表连接起来的，

对于提高缓存命中率，会有一个预读的操作，同时存在一个yong 和old lru缓存

redo log

物理日志，记录对表空间的数据页的偏移量的地方做了什么更新，这是一种顺序写。同时redolog是一个循环写的过程，用一个双指针，来记录操作的信息。

对于更新操作，先是更新内存，对该次操作，以redolog的方式记录下来，再通过WAL技术，写入binlog 实现crase-safe

binlog

binlog是mysql实现的日志，对于所有存储引擎都适用，用于掉电恢复，主从复制，redolog+binlog实现的两阶段提交实现crase safe先写进redo log buffer 中，再写进去文件系统的page cache 里在持久化到磁盘中去，除此之外，还有一些其他的机制

redo log buffer 占用空间即将达到innodb_log_buffer_size一半的时候，
事务提交的时候顺道写到磁盘中