当前位置： 首页>编程语言>正文

mysql有类似dual mysql的dual

编程语言2024-04-21 11:42:07


文章目录

  • 一.MySQL架构
  • 二.I/O和存储
  • 三.缓冲池 buffer pool
  • 缓冲池 buffer pool结构
  • 四. MySQL临时表
  • 五.MySQL事务
  • 六. 索引
  • 七.锁机制
  • 八.日志系统
  • 1.binlog
  • 2.其他日志
  • 3.redolog
  • 4.undolog
  • 九.隔离级别和MVCC


一.MySQL架构

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据,第1张

查询的大概操作流程：

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_mysql_02,第2张

MySQL8.0为什么取消了查询缓存（不同的一个DML,DQL 语句包括大小写都会让高并发下的查询缓存增添负担）

（1）数据库备份
传统的NA~就不赘述了

-- 备份一个表
mysqldump -u root -p ydlclass ydl_user  > ~/文件名
-- 备份一个数据库
mysqldump -u root -p ydlclass  > ~/文件名
-- 备份所有数据库
mysqldump -u root -p --all-databases >文件名

备份完成之后使用

mysql -u root -p ydl < ~/文件名

（2）全局变量与当前会话变量
加global指定全局变量：

show global variables like '%wait_timeout%';
set global wait_timeout=10000;

加session指定当前会话变量：

set session wait_timeout=10000;
 show session variables like 'wait_timeout';

二.I/O和存储

先了解InnoDB的逻辑存储结构：

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据库_03,第3张

段->区->表->页（16k）->行【compact行记录】是在MySQL 5.0时被引入的，其设计目标是能高效存放数据。Compact行记录以如下方式进行存储：

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据库_04,第4张

  • 第一个部分是一个非NULL【变长字段长度列表】我们的数据类型除了定长的char、int还有不定长的如varchar、text等，变长列的真实长度就保存在这个部分，他是按照列的顺序【逆序放置】的。最多放两个字节，所以vachar的最大长度为2^16
  • 第二个部分是【NULL标志位】，他指示了当前行数据中哪些为null值，用一个bitmap表示,如果not null 则没有
    第三部分为记录头信息

    • mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据库_05,第5张

  • 第四部分就是实际存储的每个列的数据了，需要特别注意的是，NULL不占该部分任何数据，即NULL除了占有NULL标志位，实际存储不占有任何空间。
    注意： 另外有一点需要注意的是，每行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列，分别为6个字节和7个字节的大小。若InnoDB表没有定义Primary Key，每行还会增加一个【RowID列】。

io 简单来说就是刷盘机制：MyIsam和InnoDB存储引擎都是将数据存储在磁盘上，我们在使用DQL语句时会从磁盘读取到内存，CPU读取记录以及检测记录是否满足对应的搜索条件、对结果集进行排序。

三.缓冲池 buffer pool

为了减少io操作，设计缓冲池

缓冲池 buffer pool结构

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_mysql_06,第6张

数据库会在启动的时候，按照配置中的Buffer Pool大小，去向操作系统申请一块内存，作为Buffer Pool的内存区域，检查缓冲池的大小show VARIABLES LIKE 'innodb_buffer_pool_size' 随着时间的推移，原本干净的缓冲池，近期会用到的与近期用不到的都在缓冲池里面，所以我们需要管理：

控制块是用来描述缓冲页的信息

(1）free(用于找新的可以缓存的地方，此时指针不再会指向有数据的地方)

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据_07,第7张

用来放缓存的地方有了，但是怎么知道哪些地方有缓存，哪些没有呢？

使用【表空间号+页号】就可以确定一个唯一的页，使用【表空间号+页】号当做key，使用【控制块地址】做value，每次查询的时候只需要通过key进行查找即可

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据_08,第8张

缓冲是有了，但是在我们对缓冲里面的数据修改以后，还没来的及刷入磁盘，那这些脏数据，我们怎么才能知道，然后让这些脏数据再刷入磁盘呢？

(2)flush(来指向脏数据，方便后期在缓存里找到，刷入磁盘)

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据库_09,第9张

刷盘时机:

后台会有专门的线程每隔一段时间就把flush链表中的脏页刷入磁盘中，刷新的速率取决与当前系统是否繁忙。在这样的机制下，万一系统奔溃，是会产生数据不一致的问题的，没有刷入磁盘的数据就会丢失，而mysql通过日志系统解决了这个问题, 在日志系统解决

还有一个问题就是缓存里的数据有的长久不用了，留着又浪费缓冲池的空间。

(3）lru(用来删除缓冲池中不常用的数据)

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据库_10,第10张

因为cpu会预读以提高性能，但是有时也拿过来了一些没意义的数据，所以有了冷热区的概念。对于预读的数据页，会在第一次访问时放入old区域。如果在sql执行的过程中访问相邻数据时，再次访问访问到该数据页，则把他加入如热数据区。

【大表的全表扫描】是个使用频率很低的操作（小表怎么操作都无所谓），但是如果按照上边的操作，首先全表数据会被放在【old区】，全表扫描必然会因为访问相邻数据而产生第二次、第三次、甚至数百次的访问，也就以为着这些页面会被全部放在young区。为了解决这个问题，INnodb提供了这样一个参数【innodb_old_blocks_time】，默认是1s

这样就保证了即使全表访问了，但是查询数据差小于1s,相邻数据还是被放在了冷区域。

四. MySQL临时表

一、临时表简介
MySQL临时表在很多场景中都会用到，比如用户自己创建的临时表用于【保存临时数据】，以及MySQL内部在执行【复杂SQL】时，需要借助临时表进行【分组、排序、去重】等操作，临时表具有一下几个特点：

1）临时表不能通过show tables查看，在服务器重启之后，所有的临时表将全部被销毁。

2）临时表是每个进程独享的，当前进程（客户端）创建的临时表，其他进程（客户端）是查不到临时表里面的数据的，所以不同客户端可以创建同名的临时表

五.MySQL事务

一、事务简介
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
二.事务分类
1、显式事务和隐式事务
（1）mysql的事务可以分为【显式事务】和【隐式事务】。默认的事务是隐式事务，由变量【autocommit】控制。隐式事务的环境下，我们每执行一条sql都会【自动开启和关闭】事务，变量如下：

SHOW VARIABLES LIKE 'autocommit';

（2）显式事务由我们【自己控制】事务的【开启，提交，回滚】等操作
2、只读事务和读写事务
我们可以使用read only开启只读事务，开启只读事务模式之后，事务执行期间任何【insert】或者【update】语句都是不允许的（锁中会介绍）
3、保存点
我们可以使用savepoint 关键字在事务执行中新建【保存点】语法：savepoint a;
三、事务四大特征（ACID）
1、原子性（Atomicity）
一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。如果事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样，这个很好理解。

2、一致性（Consistency）
在事务【开始之前和结束以后】，数据库的完整性没有被破坏，数据库状态应该与业务规则保持一致。举一个例子：A向B转账，不可能A扣了钱，B却没有收到，也不可能A和B的总金额，在事务前后发生变化，产生数据不一致。其他的三个特性都在为他服务。

3、隔离性（Isolation）
数据库【允许多个并发事务同时对其数据进行读取和修改】，隔离性可以防止多个事务在并发修改共享数据时产生【数据不一致】的现象

事务隔离级别分为不同等级，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）

4、持久性（Durability）

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

（原子性，隔离性，持久性）-------------->（一致性)

四、事务的隔离级别

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_mysql_11,第11张

六. 索引

提高查询性能

一、数据结构

一方面mysql的数据是存储在磁盘上的，另一方面还要满足对日常操作如【增删改查】的高效稳定的支持，我们当然可以采用更好的硬件来提升性能，但是选用合适的数据结构也很关键，innodb采用的是一种名为【b+树】的数据结构。

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据库_12,第12张

二、索引的分类和创建

1、聚簇索引和非聚簇索引

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据库_13,第13张

2、普通索引

-- 创建索引
create index idx_user_name on user(user_name); 
-- 删除索引
drop index idx_user_name on ydl_user;

创建email列的索引，索引可以截取length长度，只使用这一列的前几个字符

create index idx_email on user(email(5));

重点：

有的列【数据量比较大】，使用前几个字符就能【很快标识】出来一行数据，那我们就可以使用这种方式建立索引，比如我们的邮箱，邮箱很多后缀是相同的我们完全可以忽略。
---------其他创建索引方式，可以查到
3、唯一索引
对列的要求：索引列的值不能重复

-- 创建索引
create
 **unique** index idx_email on user(email);
  • 创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引

4、二级索引的组合使用

记住一点：mysql在执行查询语句的时候一般只会使用【一个索引】，除非是使【用or连接的两个索引列】会产生索引合并。（所以无论怎么创建索引，再第一次使用索引后，都会返回，使用聚簇索引，进行全表扫面）

这里只举合并的例子：

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据_14,第14张

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据库_15,第15张

我们要检索名称为阿玛尼（Armani）或价格大于8000的包包

**第一步：**通过【品牌索引】检索出所有阿玛尼的商品id，进行缓存。

**第二步：**通过【价格索引】检索出价格在5万到7万之间的商品id，这是一个连接条件带有【or的查询】，所以需要和上一步的结果进行【并集】，得到结果。

**结论：**这个过程叫【索引合并】当检索条件有or但是所有的条件都有索引时，索引不失效，可以走【两个索引】。

5、联合索引
当【查询语句】中包含【多个查询条件，且查询的顺序基本保持一致】时，我们推荐使用复合索引，索引的【组合使用】效率是低于【复合索引】的。

-- 创建索引
create index idx_user_nick_name on ydl_user(user_name,nick_name,email(7));

**重点：**最左前缀原则：

（1）最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立（a,b,c,d）顺序的联合索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

（2）=和in可以乱序，比如a = 1 and b < 2 and c = 3 ，咱们建立的索引就可以是（a,c,b）或者（c,a,b）。
查到对应的不用再回表扫，即使最后没有索引，但是已经拿到了对应的id，回表扫也很快。
不是等值查询，一类与一类的顺序是乱序的，但是查到的本类是顺序的。
6、hash索引
hash索引是Memory存储引擎的默认方式，而且只有memory引擎支持hash索引，memory的数据是放在内存中的，一旦服务关闭，表中的数据就会丢失。
三、explain的用法
explain关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈。explain的使用很简单，只需要在目标sql前加上这个关键字就可以了。
explain会产生的列：
1 id select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
2 select_type 查询类型
（1）SIMPLE
简单查询，不包含子查询或Union查询的sql语句。
（2）PRIMARY
查询中若包含任何复杂的子部分，最外层查询则被标记为主查询。
（3） SUBQUERY
在select或where中包含子查询。
（4）UNION
若第二个select出现在uion之后，则被标记为UNION。
（5）UNION RESULT
从UNION表获取结果的合并操作
3 table 正在访问哪个表
4 partitions 匹配的分区
5 type 访问的类型
最好到最差备注：掌握以下10种常见的即可NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL
6 possible_keys 显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
7 key 实际使用到的索引，如果为NULL，则没有使用索引
8 key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
9 ref 显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
10 rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数 filtered //查询的表行占表的百分比
11 filtered 查询的表行占表的百分比
12 Extra 包含不适合在其它列中显示但十分重要的额外信息
四、使用索引的问题
设计好MySql的索引可以让你的数据库飞起来。但是，不合理的创建索引同样会产生很多问题？我们在设计MySql索引的时候有一下几点注意：

1、哪些情况下适合建索引

  • 频繁作为where条件语句查询的字段
  • 关联字段需要建立索引
  • 分组，排序字段可以建立索引
  • 统计字段可以建立索引，例如count()，max()等

2、哪些情况下不适合建索引

  • 频繁更新的字段不适合建立索引
  • where条件中用不到的字段不适合建立索引
  • 表数据可以确定比较少的不需要建索引
  • 数据重复且发布比较均匀的的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值

七.锁机制

一、InnoDB的锁类型
InnoDB的锁类型主要有读锁(共享锁)、写锁(排他锁)
1.读锁保证其他人可读不可写

begin;
select * from ydl_student where id = 1 lock in share mode;

2.写锁保证其他人不可读不可写 update自动加锁

begin;
update ydl_student set score = '90' where id = 1;

3、记录锁（Record Lock）
where不加索引：修改同行，锁行。修改不同行，锁表
where加索引：修改同行，锁行。修改不同行，锁行
**证明：**行锁是加在索引上的，这是标准的行级锁。

4、间隙锁（GAP Lock）
间隙锁帮我们解决了mysql在rr级别下的一部分幻读问题。间隙锁锁定的是记录范围，不包含记录本身，也就是不允许在某个范围内插入数据。
间隙锁生成的条件：
（1）、A事务使用where进行范围检索时未提交事务，此时B事务向A满足检索条件的范围内插入数据。
（2）、where条件必须有索引。

5、死锁问题(循环保持抢占互斥)
发生死锁的必要条件有4个，分别为互斥条件、不可剥夺条件、请求与保持条件和循环等待条件：

  • 互斥条件，在一段时间内，计算机中的某个资源只能被一个进程占用。此时，如果其他进程请求该资源，则只能等待。
  • 不可剥夺条件，某个进程获得的资源在使用完毕之前，不能被其他进程强行夺走，只能由获得资源的进程主动释放。
  • 请求与保持条件，进程已经获得了至少一个资源，又要请求其他资源，但请求的资源已经被其他进程占有，此时请求的进程就会被阻塞，并且不会释放自己已获得的资源。
  • 循环等待条件，系统中的进程之间相互等待，同时各自占用的资源又会被下一个进程所请求。例如有进程A、进程B和进程C三个进程，进程A请求的资源被进程B占用，进程B请求的资源被进程C占用，进程C请求的资源被进程A占用，于是形成了循环等待条件，
    6、表锁
    第一种情况是：事务需要更新【大部分或全部数据】，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。

第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
主动上锁

lock tables teacher write,student read;
select * from teacher;
commit;
unlock tables;

7、乐观锁（加字段，每更新+1）
乐观锁大多是基于数据【版本记录机制】实现，一般是给数据库表增加一个"version"字段。

读取数据时，将此版本号一同读出，

更新时，对此版本号加一。此时将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据

select * from ydl_student where id = 1;
update ydl_student set score = 99,version = version + 1 where id = 1 and version = 1;
commit;

8、悲观锁（就是类似于x锁）

八.日志系统

mysql给我们提供了很多有用的日志，这是mysql服务层给我们提供的：

日志类型

写入日志的信息

二进制日志

记录了对MySQL数据库执行更改的所有操作

慢查询日志

记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询

错误日志

记录在启动，运行或停止mysqld时遇到的问题

通用查询日志

记录建立的客户端连接和执行的语句

中继日志

从复制主服务器接收的数据更改

1.binlog

（sqlserver提供，存放类sql语句， 流水账）
（1）概述
二进制日志（binnary log）以【事件形式】记录了对MySQL数据库执行更改的所有操作。
binlog记录了所有数据库【表结构】变更（例如CREATE、ALTER TABLE…）以及【表数据】修改（INSERT、UPDATE、DELETE…）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。

binlog 文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别于 redo log，redo log 是循环写入的，即后面写入的可能会覆盖前面写入的。

binlog有两个常用的使用场景：

数据库单点部署的问题：

  • 服务器宕机，会导致业务停顿，影响客户体验。
  • 服务器损坏，数据丢失，不能及时备份，造成巨大损失。
  • 读写操作都在同一台服务器，在并发量大的情况下性能存在瓶颈。
  • 那么我们就可以使用mysql的binlog搭建一个一主多从的mysql集群服务。这样的服务可以帮助我们异地备份数据、进行读写分离，提高系统的可用性

主从复制：我们搭建一个主从同步的两台mysql服务。

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_mysql_16,第16张

Ⅰ：
环境准备
安装两个mysql，使用vmvare安装两个linux系统就可以：

mysql1(master): 42.192.181.133:3306
mysql2(slave):  124.220.197.17:3306

Ⅱ：
mysql 配置文件配
mysql1（master）: 配置文件设置，开启bin_log（已经开启的可以忽略）且需要配置一个server-id

#mysql master1 config 
[mysqld]
server-id = 1            # 节点ID，确保唯一

# log config
log-bin = master-bin     #开启mysql的binlog日志功能

mysql2（slave）: 需要开启中继日志

[mysqld]
server-id=2
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=sys.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%

重启两个mysql，让配置生效。

service mysqld restart

service mysql restart (5.5.7版本命令)

Ⅲ：
在master数据库创建复制用户并授权
1.进入master的数据库，为master创建复制用户

CREATE USER 'repl'@'124.220.197.17' IDENTIFIED BY 'Root12345_';

2.赋予该用户复制的权利

grant replication slave on *.* to 'repl'@'124.220.197.17' 
FLUSH PRIVILEGES;

3.查看master的状态

show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005      120|              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4,配置从库

CHANGE MASTER TO 
MASTER_HOST = '42.192.181.133',  
MASTER_USER = 'repl', 
MASTER_PASSWORD = 'Root12345_',
MASTER_PORT = 3306,
MASTER_LOG_FILE='mysql-bin.000020',
MASTER_LOG_POS=2735,
MASTER_HEARTBEAT_PERIOD = 10000; 

# MASTER_LOG_FILE与主库File 保持一致
# MASTER_LOG_POS=120 , #与主库Position 保持一致

解释：MASTER_HEARTBEAT_PERIOD表示心跳的周期。当MASTER_HEARTBEAT_PERIOD时间之内，master没有binlog event发送给slave的时候，就会发送心跳数据给slave。

Ⅳ：
启动从库slave进程：

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

Ⅴ：
查看是否配置成功

show slave status ;
  • Slave_IO_Running：从库的IO线程，用来接收master发送的binlog，并将其写入到中继日志relag log
  • Slave_SQL_Running：从库的SQL线程，用来从relay log中读取并执行binlog。
    ~~~~~ Slave_IO_Running、Slave_SQL_Running：这两个进程的状态需全部为 YES，只要有一个为 NO，则复制就会停止。

Ⅵ：如果使用克隆会出现的问题解决：
错误：
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
原因：
如果你使用了两台虚拟机，一主一从，从库的mysql是直接克隆的。在mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，但是查看到直接克隆data文件夹后server_uuid是相同的。

解决：
找到data文件夹下的auto.cnf文件，修改里面的server_uuid值，保证各个db的server_uuid不一样，重启db即可。

cd /www/server/data

使用

select uuid();

生成一个uuid即可。

修改server_uuid的值

vim  auto.cnf

数据恢复：通过mysqlbinlog工具来恢复数据。
mysql8中的binLog默认是开启的，5.7默认是关闭的，可以通过参数log_bin控制：
数据恢复
Ⅰ：确认binlog开启，log_bin变量的值为ON代表binlog是开启状态：

show variables like '%log_bin%';

Ⅱ：为了防止干扰，我们flush刷新log日志，自此刻会产生一个新编号的binlog日志文件：

flush logs;

Ⅲ：查看所有binlog日志列表：

show master logs;

自己创建完数据以后进行删除
Ⅳ：查看binlog日志：
我们因为刷新了日志，所以本次操作都会在最新的日志文件上：

因为 binlog 的日志文件是二进制文件，不能用文本编辑器直接打开，需要用特定的工具来打开，MySQL 提供了 mysqlbinlog 来帮助我们查看日志文件内容：

/www/server/mysql/bin/mysqlbinlog -v            mysql-bin.000008（日志名）
# 指定位置范围
/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-position=0 --stop-position=986
# 指定时间范围
/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-datetime="2022-06-01 11:18:00" --stop-datetime="2022-06-01 12:18:00"

Ⅵ：开始恢复：

/www/server/mysql/bin/mysqlbinlog -v mysql-bin.000008 --stop-position=3228 -v | mysql -uroot -p

2.其他日志

(1)通用查询日志，默认关闭
MySQL通用查询日志，它是记录建立的客户端连接和执行的所有DDL和DML语句(不管是成功语句还是执行有错误的语句)，默认情况下，它是不开启的。请注意，它也是一个文本文件。

show VARIABLES LIKE '%general_log%'

(2)慢查询日志
当前版本慢查询日志默认是开启的，有的版本是关闭的，使用如下命令查看慢查询日志的状态：

show VARIABLES LIKE '%long_query_time%'

(3)错误日志
错误日志（Error Log）主要记录 MySQL 服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等。一旦发生mysql服务无法启动、程序崩溃一定要记得去查询错误日志:

show VARIABLES LIKE '%log_error%'

3.redolog

（innodb提供，数据的变化 ，物理日志 已经提交的脏页进行回复）
把当前事务中修改的数据内容在日志中记录下来，日志记录是顺序写，性能很高

1、MTR

在innodb执行任务时，有很多操作，必须具有原子性，我们把这一类操作称之为MIni Transaction

（1）MTR的按组写入

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_mysql_17,第17张

（2）单条redolog的标识方法

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据库_18,第18张

（3）事务、sql、MTR、redolog的关系如下

一个事务包含一条或多条sql
一条sql包含一个或多个MTR
一个MTR包含一个或多个redo log

2、log buffer(给redolog做缓冲进行刷盘，这里与前面的刷脏页时的flush对应)
log buffer会在合适的时机进行刷盘：

  • log buffer空间不足。logbuffer的容量由innodb_log_buffer_size指定，当写入log buffer的日志大于容量的50%，就会进行刷盘。
  • 提交事务时，如果需要实现崩溃恢复，保证数据的持久性，提交事务时必须提交redo log，当然你也可以为了效率不去提交，可以通过修改配置文件设置该项目。
  • 后台有独立线程大约每隔一秒会刷新盘一次。
  • 正常关闭服务器。
  • 做checkpoint时（redolog容量不够）
  • 3、checkpoint（redolog存数据变化的日志，log buffe做缓冲，万一缓冲慢了，不能及时把脏页刷到，redolog容量会不够）
    redolog日志文件容量是有限的，需要循环使用，redo log的作用仅仅是为了在崩溃时恢复脏页数据使用的，如果脏页已经刷到磁盘上，其对应的redo log也就没用了，他也就可以被重复利用了。checkpoint的作用就是用来标记哪些旧的redo log可以被覆盖，空间就大了。

4.undolog

（innodb提供，对未提交事务的脏页进行恢复,用来解决脏读，不可重复读，幻读）

1、概述

undo log（也叫撤销日志或者回滚日志），他的主要作用是为了实现回滚操作。同时，他是MVCC多版本控制的核心模块。

2、事务id（在compact行记录中说过）

事务id是一个自增的全局变量，如果一个【事务】对任意表做了【增删改】的操作，那么innodb就会给他分配一个独一无二的事务id。

3、roll_pointer

undo log在记录日志时是这样记录的，每次修改数据，都会将修改的数据标记一个【新的版本】，同时，这个版本的数据的地址会保存在修改之前的数据的roll_pointer列中，如下：

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据_19,第19张

4、分类（因为会生成版本链，所以在后面读视图的时候选择版本会给隔离级别的不同带来好处）

所以roll_pointer是核心

当我们对数据库的数据进行一个操作时必须记录之前的信息，将来才能【悔棋】，如下：

innodb将undo log分为两类：一类日志只记录插入类型的操作（insert）对应幻读

一类日志只记录修改类型的操作（delete，update）对应不可重复读

生成不同的版本链：

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_mysql有类似dual_20,第20张

九.隔离级别和MVCC

终于到了不同隔离级别解决的源
【MVCC】，全称Multi-Version Concurrency Control，即【多版本并发控制】。
一、Read View（读视图）

当前读：像select lock in share mode（锁）、 select for update、 update、insert、delete（排他锁）这些操作都是【当前读】，他读取的是记录的【最新版本】，读取时还要保证其他【并发事务】不能修改当前记录，会对读取的记录进行加锁。

快照读：像不加锁的select操作就是快照读，即不加锁的【非阻塞读】；快照读的前提是【隔离级别不是串行级别】，串行级别下的快照读会【退化成当前读】，顾名思义，快照读读取的是【快照】，他是通过readView实现的。

1、实现原理
Read View就是事务进行【快照读】的时候生产的【读视图】(Read View)，在该事务【执行快照读】的那一刻，会生成数据库系统当前的一个快照。

注意：【快照】不是说将数据库复制一份，【Read View】的主要作用是做【可见性判断】， 快照的实现逻辑是通过undo log的【版本链】，配合一些【参数】，比如事务id，来确定当前事务可以读取的版本。

2.readView的结构
readView[记录活跃事务的id]

m_ids：记录活跃事务的id
min_trx_id：是m_ids的最小值
max_trx_id：当前系统中计划分配给下一个事务的id
creator_trx_id：生成这个readView的事务id

二、快照读原理解析
在一个事务读取数据时，会根据当前数据形成一个readview，读取时会按照以下逻辑进行读取：

如果【被访问数据的事务trx_id】和readView中的【creator_trx_id值】相同，意味着自己在访问自己修改过的记录，当然可以被访问。

如果【被访问数据的事务trx_id】小于readView中的【min_trx_id】值，说明生成这个版本数据的事务，在生成readview前已经提交，这样的数据也可以访问。

**通俗一点：**这个数据之前被其他的事务修改过，但是事务已经提交，所以这个版本的数据是可以使用的，这样不会产生脏读。

如果【被访问数据的事务trx_id】大于等于readView中的max_trx_id值，说明生成这个版本数据的事务，是在生成readview后开启，这样的数据不应该被访问。

**通俗一点：**你读取数据之后，有人修改了当前数据，那人家后边修改的数据，你也不能读。

如果【被访问数据的事务trx_id】如果在min_trx_id和max_trx_id范围内，则需要判断是不是在【m_ids】中（目的是判断这个数据是不是已经提交）。如果在，说明生成这个版本的事务还是活跃的，没有提交的事务产生的数据当然不能读，如果不在，说明事务已经提交，该数据可以被访问。

**通俗一点：**这个数据被现在活跃的其他事务正在修改中，读取时要看此时这个事务是不是已经提交，目的也是为了不要读取别人未提交的事务

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据库_21,第21张

三、解决脏读和不可重复读

RC和RR两个隔离级别解决不可重复读是通过生成readview时间不同

  1. 而rc隔离级别解决ru隔离级别的脏读直接就是不能读取比它下一个事务id大的。
    解决了脏读，但是存在不可重复读的问题(生成不同的readview)

mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_mysql_22,第22张

  2. 而rr隔离级别解决rc隔离级别的不可重复读直接是在此版本链中找到对应的，就算提交了，但是还是一个readview (生成一个readview)

    3. mysql有类似dual mysql的dual,mysql有类似dual mysql的dual_数据库_23,第23张

  3. 解决幻读
    他是通过间隙锁实现的，一旦锁定某一个范围的数据，就会对这个范围的数据加锁，间隙锁保证我们不能在这个范围内插入新的数据



https://www.xamrdz.com/lan/5eu1924858.html

相关文章：