MYSQL

逻辑架构

---title: MySQL 逻辑架构---flowchart TB    subgraph 客户端        direction TB        Client1 --> Connection[连接/线程处理]    end    subgraph " "        direction TB        Connection --> Cache[查询缓存]        Connection --> Parser[解析器]        Parser --> Optimizer[优化器]    end    Optimizer --> 存储引擎    subgraph 存储引擎        direction LR        DB1[( )] 				DB2[( )]				DB3[( )]				DB4[( )]				DB5[( )]    end

取数据和发数据的流程：

  1. 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k
  2. 重复获取行，直到 net_buffer 写满，调用网络接口发出去
  3. 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer
  4. 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送

日志机制

在数据更新写日志的时候，是先预提交写 redolog 和写 binlog，提交事务后再把redolog 改成提交状态。要两阶段提交写的原因就是为了避免极端情况下两个日志的数据不一致

redo log

作用是在数据库崩溃时，用来恢复事务提交之前的数据修改，是一个循环写的日志文件，不断地被填写，当写满时，重新从开头开始覆盖。write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘，除此之外，redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘，另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘

控制 redo log 的写入策略（innodb_flush_log_at_trx_commit 参数）：

组提交：

日志逻辑序列号（log sequence number，LSN），单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length

通过一次性写入多个事务的 redo log，来提升写入效率

undo log

作用是在事务回滚时，用来撤销已经提交的事务所做的数据修改。undo log是在内存中缓存的

bin log

事务执行时，binlog 是先写入到线程自己的 binlog cache 中，事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中（先 write 到操作系统的 page cache，后再 fsync 到磁盘上）

锁机制

对比

MySQL的锁释放是在COMMIT或者ROLLBACK时释放的。隐式锁定是存储引擎根据隔离级别自动进行，但也可以进行显式锁定：

SELECT ... LOCK IN SHARE MODE; -- 任何时候都不要手动加锁

全局锁

MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)，锁了之后整个数据库全局就是只读

mysqldump 在备份时使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图，但只有支持事务的引擎才能用这个参数

表级锁

为了防止拿不到元数据锁一直等待：一些 MySQL 的分支支持NOWAIT/WAIT n 这个语法，等待一段时间拿不到锁就终止 DDL 语句

MySQL 5.6 后支持了 online ddl 可以不阻塞读写：

  1. 拿MDL写锁
  2. 降级成MDL读锁
  3. 真正做DDL
  4. 升级成MDL写锁
  5. 释放MDL锁

行锁

InnoDB 事务中，行锁是在需要的时候才加上，要等到事务结束时才释放

为了解决死锁，有两种策略：

  1. 获取锁时直接进入等待，直到超时，使用参数innodb_lock_wait_timeout，但是这个时间很难控制
  2. 检测到死锁时，自动中断，每次获取一个行锁都要判断会不会因为自己的加入而导致死锁，如果有热点数据，每个线程都要去检测一下，这容易导致CPU飙高

所以死锁最好的解决方案应该是从业务上来解决，保证业务操作不会产生死锁，另外一个较次一点的方案是控制并发度，这样也不会导致CPU飙高

间隙锁

锁定一个范围，不包括记录本身 防止这个区间的数据插入 通过锁定一个范围来避免其他并发事务的修改，从而解决幻读问题

间隙锁是在可重复读隔离级别下才会生效的。间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的

Next-key lock

行锁 + 间隙锁 = 锁定一个范围，包含记录本身

Insert Intention Locks

插入意向锁 insert前执行，也是一种间隙锁

AUTO-INC Locks

一种特殊的表级锁，如果一个事务正在将值插入表中，则任何其他事务都必须等待才能向该表中执行自己的插入操作

Predicate Locks

在空间索引中被使用

锁相关统计信息

show status like '%innodb_row_lock%';
SHOW ENGINE INNODB STATUS; -- 关注结果中 TRANSACTIONS 段落

锁、事务相关的表

当前事务执行情况：

锁信息：

锁等待信息：

事务与锁情况分析

SELECT  waiting_trx_id,  waiting_pid,  waiting_query,  blocking_trx_id,  blocking_pid,  blocking_queryFROM sys.innodb_lock_waits;

insert 相关的锁

MVCC

用户及权限管理

create user 'user2'@'%' identified by '123';
grant all on *.* to 'user2'@'%';

grant 语句会同时修改数据表和内存，判断权限的时候使用的是内存数据，如果直接操作了系统权限表，此时数据表和内存的数据就会不一致，此时就需要 flush privileges

事务

MySQL使用一个变量控制是否采用自动提交：

SHOW VARIABLES LIKE 'AUTOCOMMIT';

但对于不支持事务的引擎，如MyISAM或者内存表，或者执行DDL等操作，默认也会自动提交。

MySQL设置隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

问题

Lock wait timeout exceeded

该问题是由于某个事务执行时间过长，而导致其他事务无法获取相对应的锁，其他事务在等待一定时间后，则会出现这个问题。

可通过调高 innodb_lock_wait_timeout 变量来增加超时时间。

但为了解决根本问题，还是要避免长事务的出现，可以考虑通过缩小事务的粒度以及减少事务的执行时间来解决，尽量避免在事务里执行耗时的操作，如大量调用远程接口。

一个远程接口调用过长导致的长事务超时案例：

预警在落库时，会经过一连串数据处理流程，这个流程就包括调用其他系统的资源对数据进行清洗完善，大部分接口返回响应很快，但唯独有一个接口等待响应的时长要几十秒，由于外部系统的不稳定，这就可能导致一下子出现大量事务超时的线程，为了能让流程正常地走下去，我们对外部接口做了发生超时时自动熔断并降级的功能，也就是这个接口不去调用了，当然是会影响数据的清洗，但相比实时的预警没法被民警处理，这种信息的缺失是可以接受的。同时后台也会开个异步的任务去刷这个数据。

Spring的事务与MySQL的事务

Spring所表达的含义就是根据规则来决定要不要事务，怎么创建事务。

1、spring里面，方法嵌套调用外层读取数据和内层读取数据效果与数据库隔离级别的关系。

高可用性

影响可用性的原因

实现高可用

复制

故障转移与恢复

健康检测

相关文件说明

文件名类型说明
performance_schema文件夹数据库， MySQL 的数据字典
mysql文件夹数据库，MySQL 的数据字典
sys文件夹数据库， SQL 数据字典
my.cnf文件参数文件，默认是从/etc/my.cnf中读取 也可自定义
auto.cnf文件MySQL 启动时如果没有UUID就会生成这个文件
binlog.00000x文件二进制日志，即binlog ，数据变化都会在里面记录。如果是在从库，还会有相应的relay log
binlog.index文件binlog的索引文件，里面记录相应的bin log名称
mysqld.pid文件MySQL服务的进程号
mysqld.log文件MySQL日志，记录数据库启动日志、服务端日志，有的公司会将其命名为error.log
Ibtmpx文件临时表的表空间，由innodb_temp_data_file_path变量控制
ibdata1文件系统表空间，由innodb_data_file_path变量控制
undo_00x文件undo表空间
mysql.ibd文件mysql库中系统表与数据字典的表空间
ib_logfilex文件InnoDB特有，redo文件
ib_buffer_pool文件关闭MySQL时，会把内存中的热数据保存在该文件中，从而提高使用率和性能
slow.log文件慢查询日志
xxx.pem文件SSL相关文件
mysql.sock文件本地服务器的套接字文件使用UNIX domain socket作为通讯协议的载体，比TCP更快 用于从客户端到本地服务器来进行交换数据。
ib_16384_x.dblwr文件doublewrite 文件，格式为#ib_page_size_file_number.dblwr

内存结构

MySQL内存结构

参数设置

general

character

connection

log

cache

innodb