MySQ 个人探索笔录

常用函数

IFNULL(exp1,exp2)、IF(exp1，exp2，exp3)、ISNULL（exp）

SELECT
	employee_id,
-- 	  1.判断是否为null，如果为null则展示0，否则展示原来值
	IFNULL( commission_pct, 0 ) ,
--    2.if、else，判断是否为null，如果为null取第一个值，否则取第二个值
	IF(commission_pct IS NULL,0,100),
--    3.判断是否为null，如果为null，则返回1，否则返回0
	ISNULL(commission_pct),
-- 	  4.case when
	CASE commission_pct
	WHEN 0 THEN
		'值为0'
	ELSE
		'值不为0'
END AS commission_pct
FROM
	`employees`;

between and：等价 a>=b and b<=c，前闭后闭

union（合并查询结果集，连接条件相当于or，查询到的列字段要相同）

union：将查询到的数据去重后列出来
union all ：将所有结果查询出来

SELECT job_id FROM `jobs` WHERE min_salary > 4500 OR max_salary < 10000;


SELECT job_id FROM `jobs` WHERE min_salary > 4500
UNION
SELECT job_id FROM `jobs` WHERE max_salary < 10000;

函数使用位置

函数使用可以在sql中select查询结果集、where条件、以及join关联时都可使用

字符集与比较规则

字符集理解

字符（Character）是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。字符集（Character set）是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同，常见字符集名称：ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等。计算机要准确的处理各种字符集文字，就需要进行字符编码，以便计算机能够识别和存储各种文字。

字符编码_百度百科 (baidu.com)

是把字符集中的字符编码为指定集合中某一对象（例如：比特模式、自然数序列、8位组或者电脉冲），以便文本在计算机中存储和通过通信网络的传递

案例

编码和解码使用的字符集不一致的后果

说到底，字符串在计算机上的体现就是一个字节串，如果你使用不同字符集去解码这个字节串，最后得到的结果可能让你挠头。我们知道字符 '我' 在 utf8 字符集编码下的字节串长这样： 0xE68891 ，如果一个程序把这个字节串发送到另一个程序里，另一个程序用不同的字符集去解码这个字节串，假设使用的是 gbk 字符集来解释这串字节，解码过程就是这样的：

首先看第一个字节 0xE6 ，它的值大于 0x7F （十进制：127），说明是两字节编码，继续读一字节后是 0xE688 ，然后从 gbk 编码表中查找字节为 0xE688 对应的字符，发现是字符 '鎴' 2. 继续读一个字节 0x91 ，它的值也大于 0x7F ，再往后读一个字节发现木有了，所以这是半个字符。 3. 所以 0xE68891 被 gbk 字符集解释成一个字符 '鎴' 和半个字符。
mysql处理字符集

如果 创建或修改列 时没有显式的指定字符集和比较规则，则该列 默认用表的 字符集和比较规则
如果 创建表时 没有显式的指定字符集和比较规则，则该表 默认用数据库的 字符集和比较规则
如果 创建数据库时 没有显式的指定字符集和比较规则，则该数据库 默认用服务器的 字符集和比较规则

![image-20221224224943886](mysql 拓展笔记.assets/image-20221224224943886.png)

比较规则：排序、比较大小使用的规则

事务基础理解

隔离性：可以防止多个事务并发读写时由于交叉执行而导致数据的不一致

一致性：数据库中的数据符合现实生活中的约束

主要来自两方面的努力

1.数据库本身的约束
2.业务代码保障的约束

持久性：事务提交后，如果MySql发生了错误，系统重启后可以保证该事务对数据的永久性修改

持久性是通过 redo log （重做日志）来保证的；
原子性是通过 undo log（回滚日志） 来保证的；【undo log 也用来MVCC】
隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

注意Java中的锁与MySQL中锁的对象异同点

MySQL锁针对的对象是事务
Java程序中的锁针对的对象是线程

索引(innodb)

对所有数据排好序的目录

为某个列（多个列）建立索引的实质含义：使用某个列（多个列）对数据进行排序

页分裂概念

为了保证记录按索引值有序排放，要让一个页裂开分成两个，同时记录会移动到其他页的过程叫做页分裂

在对页中的记录进行增删改操作的过程中，我们必须通过一些`诸如记录移动的操作`来始终保证这个状态一直成立：
`下一个数据页中行记录的主键值必须大于上一个页中行记录的主键值。这个过程我们也可以称为 页分裂`

举例

数据移动前

数据移动后

如果插入表中主键值是无序的（比如uuid），第一个页插入的主键值是1，4，第二个页插入的3，2。
为了保证有序，下一页中主键值要大于上一页的主键值，会将第二页中主键值为2的行记录移动到第一页里，第一页中主键值为4的行记录移动到第二页里。

图解MySQL页分裂

页

页是 MySQL 中磁盘和内存交互的基本单位，也是 MySQL 是管理存储空间的基本单位。内存每次读取的是以16KB的页为单位，写入的也是以16KB的页为单位。
一个页可以理解为图书馆里的一个个书架，行记录理解为书架的一本书籍
页与页之间存在双向链表，页内记录是单向链表关联
读取一个页从磁盘到内存可以当做一次磁盘I/O操作，Innodb 引擎对于千万级别的数据只需要3-4次磁盘I/O操作
通过索引查找数据的过程主要通过二分法、B+树的数据结构【二分法时间复杂度是 O(logn)】

根据聚簇索引查找数据时的过程

1. 先通过二分法确定目录页所在位置
2. 再目录页中先通过二分法定位到记录的真实数据页
    3. 最后通过二分法在定位记录所在的槽（每个槽有1-8条记录），然后遍历槽中的记录

![image-20230304103500708](mysql 拓展笔记.assets/image-20230304103500708.png)

根据非聚簇索引查找完整的用户记录时的步骤【需要查询两个B+树】

1. 先确定目录页所在位置
2. 再目录页中先通过二分法定位到记录的真实数据页
3. 然后通过二分法在数据页中找到主键值和索引列值
4. 最后通过主键值去聚簇索引中查找完整的用户记录

目录页、数据页

聚集索引

1. 目录页：指向下级各个页中数据的最小主键值、相应的页号
2. 数据页：聚集索引则是 页号、完整的记录

非聚集索引（联合索引）

1. 目录页：指向下级各个页中数据的最小索引列值、相应的页号、主键值
2. 数据页：索引值，以及对应的主键值

b+树特点：

1. 使用索引列对记录和页进行排序
2. 叶子节点存储完整的用户记录

![image-20221225144453126](mysql 拓展笔记.assets/image-20221225144453126.png)

聚簇索引

特点

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

    - 页内的记录是按照主键的大小顺序排成一个单向链表。 
    - 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。 
    - 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成 一个双向链表。 
    
2. B+ 树的叶子节点存储的是完整的用户记录。

聚簇索引与非聚簇索引的主要区别是：

叶子节点是否存储完整的用户记录，聚簇索引的叶子节点存储的是完整的用户记录，非聚簇索引的叶子节点存储的是索引值与主键值

组成

聚簇索引是通过主键 递增 构建出来的数据结构。
叶子节点记录了主键值以及行记录，非叶子节点记录了页号、下级页最小主键值

二级索引（非聚簇索引）

二级索引是通过二级索引值 递增 构建出来的数据结构。

非叶子节点记录了页码，下级页最小主键值，还有主键值（保证目录页的唯一性）
叶子节点记录了普通索引值、相应的主键值，

如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的记录

联合索引（非聚簇索引）

根据多个字段排序建立一个索引树。

排序规则：
先把所有记录和页按照左边第一个索引列进行排序，在记录的第一个索引列值相同的情况下，再对 第二个索引 列进行排序，依次这样排列

建立索引index_c2_c3（c2,c3）

![image-20230202230318794](mysql 拓展笔记.assets/image-20230202230318794.png)

索引总结

根据主键查询数据，只需要查询一个b+树即可拿到数据

通过普通索引查询数据，需要先通过`普通索引树`找到相应行数据的主键，然后再通过主键查找`聚簇索引树`找到该主键值对应的行记录。此过程需要一个回表操作

二级索引与联合索引

1. 一个二级索引只会创建一个b+树，多个二级索引会创建多个b+树
2. 联合索引只会创建一个b+树

每建立一个索引都会创建一个相应的b+树
B+ 树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录都先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。

主键设计原则：

核心业务表：uuid

1.安全性问题，防止猜到
2.只在当前数据库实例中唯一，而不是全局唯一，对于分布式系统来说，这简直就是噩梦。

非核心业务表：bigint(自增)

利用索引

范围查询

建立索引idx_name_birthday_phone_number

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

要使用后边的索引列的前提是前面索引列是相同的。因为联合索引构建时是对所有的数据从左到右先按第一个索引列排，第一个列相同的情况下再按第二个列排序

此时只能用到name索引列

因为通过name列对应的不同记录中对应的birthday并不一定是有序的。
但如果想利用到birthday索引列，前提是通过name字段过滤出来的name值都是相同的，因为想要利用到联合索引时，查到的结果集时先按name列对数据进行升序排，如果发现name列相同，再使用birthday升序排。

如下数据，按照上边查询的结果，得到数据id为2,3,4，但是相应的birthday并不是有序的，所以不能使用birthday索引列

id   name	 birthday

1    Asa  	 1980-09-01
2    Asb  	 1980-08-01
3    Asc  	 1980-12-01
4    Asd  	 1980-06-01
5    Barlow  1980-07-01

访问方法

type

const：针对主键或唯一二级索引的等值查询

ref：通过二级索引等值查询查询

range：利用索引进行范围查询的方式（此处的索引可以是聚簇索引，也可以是二级索引）

index：扫描索引树

all：全表扫描

key：

实际用到的索引

最左匹配原则

针对联合索引中，查询条件中要包含索引最左边的条件

索引覆盖与索引条件下推（Index Condition Pushdown【ICP】）

索引覆盖（目的是不用回表）

当查询的列或者返回结果集都在同一个索引树上，那么这个查询就可以使用到索引覆盖，原因是可以通过遍历二级索引树找到匹配的数据。

注：（二级索引只包含了主键值和二级索引列值，相比与聚簇索引的叶子节点包含所有列的数据，同样的遍历所花费的代价更低）

案例：

CREATE TABLE `table` (
  `id` int NOT NULL,
  `a` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `b` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `c` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

比如建立联合索引idx_a_b_c(a,b,c列)，执行下面查询是能够使用到覆盖索引的

SELECT a,b,c FROM `table` WHERE b = 1;
SELECT b,c FROM `table`;

不鼓励用 * 号作为查询列表，最好把我们需要查询的列依次标明

不能使用到覆盖索引

产生回表操作

无用数据量多，网络传输消耗资源

索引条件下推（针对有回表操作时的查询，目的是减少回表操作，5.6版本之后支持）

在使用联合索引时，优先在存储引擎层过滤出来符合条件的主键值，然后再回表查询。目的是减少回表查询的次数。

比如建立联合索引idx_a_b_c(a,b,c列)，执行下面查询是能够使用到覆盖条件下推的

SELECT * FROM `table` WHERE a= '1' AND c LIKE '%1%'

分析：

- 按照最左匹配原则，上边查询只能使用到a索引，c是无法使用到索引，通过a索引在idx_a_b_c索引树找到匹配的主键值，回表再通过聚簇索引树拿数据返回到server层，再通过server层过滤出满足c like '%1%'的记录。（mysql 5.6之前版本）

- 使用索引条件下推后：在存储引擎层就能通过索引中的字段（a，c）进行条件判断，过滤出符合的主键值，然后再回表再通过聚簇索引树拿数据

索引下推，原来这么简单！

测试

CREATE TABLE `table` (
  `id` int NOT NULL,
  `a` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `b` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `c` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `d` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- 建立索引  KEY `idx_a_b_c` (`a`,`b`,`c`)
-- 下面三个会走索引吗？
SELECT a,b,c FROM `table` WHERE  c ='2';		-- 索引覆盖

SELECT * FROM `table` WHERE a ='1' AND b ='2';		-- 走联合索引

SELECT * FROM `table` WHERE a= '1' AND c ='2';		-- 索引条件下推

MyISAM存储引擎

特点：

存储方式：数据文件与索引文件是分开存储的

索引文件中叶子节点存储的是行号、索引列。
数据文件存储的是行号、完整记录

查找过程

在查找记录时，也就是先在索引文件中通过索引列找到相应的行号，再通过行号去数据文件中找到记录

缺点：

通过索引查询都需要回表操作
只支持表级锁

使用索引

order by

用联合索引的各个排序列的排序顺序必须是一致的，否则使用不到索引

比如一个表有三个字段（name，birthday，phone_number）建立联合索引`idx_name_birthday_phone_number` (`name`,`birthday`,`phone_number`)

该联合索引是先按照记录的 name 列的值进行升序排列。
如果记录的 name 列的值相同，再按照 birthday 列的值进行升序排列。
如果记录的 birthday 列的值相同，再按照 phone_number 列的值进行升序排列。

使用二级索引+回表还是全表扫描？

那什么时候采用全表扫描的方式，什么时候使用采用 二级索引 + 回表 的方式去执行查询呢？

这个就是传说中的查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的
条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用 二级索引 + 回表 的方式。当然优化器做的分析工作不仅仅是这么简单，但是大致上是个这个过程。一般情况下，限制查询获取较少的记录数会让优化器更倾向于选择使用 二级索引 + 回表 的方式进行查询，因为回表的记录越少，性能提升就越高

避免回表操作
```
最好在查询字段里只包含索引列
```
当然，如果业务需要查询出索引以外的列，那还是以保证业务需求为重。但是我们很不鼓励用 * 号作为查询列表，最好把我们需要查询的列依次标明。
索引重复的数据越多，则不建议建立索引。重复的数据越多则排序对其排序没有效果。

比如逻辑删除字段，性别等
索引列前缀：如果字符串中字符比较多，可以对字符串前几个字符建立索引，而不是对整个字符串建立索引。

使用OR时，且OR前后有一个字段没有使用索引

为key2建立普通索引，common_field无索引
SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';

简化就相当于
SELECT * FROM single_table WHERE key2 > 100 OR True;

再简化就是
SELECT * FROM single_table WHERE True;

上边sql会进行全表扫描，然后再进行回表。此操作要比全表扫描性能更差，查询优化器就会采用全表扫描

也就是说一个使用到索引的搜索条件和没有使用该索引的搜索条件使用 OR 连接起来后是无法使用该索引的

联合索引>=失效情况

联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。
注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配

select * from t_table where a >= 1 and b = 2

这种利用到了索引是因为存在条件a = 1 and b = 2

SELECT * FROM t_user WHERE name like 'j%' and age = 22

这种利用到了索引是因为存在条件name = j and age = 22

对列使用函数时如果想要命中索引，可以使用索引函数建立索引
```
alter table t1 add key idx_index ((DATE(create_time)));
```
左模糊全模糊并不一定会使得索引失效，要看查询的结果是否只有主键值、索引列，如果只有这两项，那么就可以利用到索引覆盖

索引有哪些数据结构以及优缺点

追求目标：在尽可能少的磁盘I/O情况下读取更多的数据

hash表

1. 存在hash冲突	
2. 范围查询性能差

数组

插入元素时需要移动后面所有的数据

二叉查找树

天然保持着二分查找，不用移动后面的数据，时间复杂度是O(log N)，但极端情况下会退化成链表，时间复杂度变成了O(N)

平衡二叉树

不会退化成链表。
但一个父节点只能分叉出两个儿子节点，随着数据增多，树的高度会越来越高，磁盘IO次数也就越来越多

B-树（可以理解为多叉树）

一个节点能分叉出多个子节点。
但一个元素包含了一条记录的完整数据。元素之间没有指针，范围查询效率低

B+树（可以理解为多叉树）

叶子节点保存了一条记录的完整数据，非叶子节点只包含了页号和最小记录值，这样一个节点能存储更多的信息，相比B树，相同的磁盘IO操作能获取更多信息

叶子节点是一个单向链表，适合范围查询

女朋友问我：为什么 MySQL 喜欢 B+ 树？我笑着画了 20 张图 (qq.com)

数据库世界是现实世界的一个映射

表->实体
字段->实体属性
一行记录->真实实体

group by与order by

-- 分组后排序的数据与索引排序一样，就可以使用到索引
-- 如下：先对resource_name分组、然后使用resource_name进行排序，与建立索引idx_resource_name索引排序规则一样，就能用到索引
SELECT
	resource_name,
	COUNT(*) 
FROM
	`tb_resource_info` 
GROUP BY
	resource_name 
ORDER BY
	resource_name

关联查询原理

驱动表访问一次，被驱动表使用on条件与where条件进行单表查询

SELECT
	t3.resource_code,
	t3.resource_name,
	t3.resource_type,
	t3.parent_id,
	t3.resource_id 
FROM
	tb_user_role_relation t1
	LEFT JOIN tb_role_resource_relation t2 ON t1.role_id = t2.role_id
	LEFT JOIN tb_resource_info t3 ON t2.resource_id = t3.resource_id 
WHERE
	t1.user_id = 1
	AND t3.parent_id = 0 
	AND t3.data_status = 1;
	GROUP BY t3.resource_name

等价于如下sql

-- 只对驱动表访问一次
SELECT * FROM tb_user_role_relation WHERE user_id = 1;
-- 对被驱动表使用on连接条件与where进行查询（访问多次，次数取决于上一次查询的结果集）
SELECT * FROM tb_role_resource_relation WHERE role_id = 1;
-- 对被驱动表使用on连接条件与where进行查询（访问多次，次数取决于上一次查询的结果集）
SELECT * FROM tb_resource_info WHERE parent_id = 1 AND data_status = 1;

索引失效

隐式替换

比如字段age类型时varchar(256),使用where age = 1;会使得索引失效，但使用where age = '1';不会使得索引失效

联合索引非最左匹配（索引下推）

比如table表创建了一个 index_a_b_c(a, b, c) 联合索引
select * from table where a=1 and c=3；

根据最左匹配原则按理说是不会使用到c索引列，但是在MySQL 5.6之后有了索引下推，因为a, b, c三列在同一个索引树上，在索引树上就可以直接过滤出符合条件的数据

锁

共享锁

SELECT
* 
FROM
	sys_notice 
	WHERE notice_id = 1
LOCK IN SHARE MODE;

上述sql加了共享锁，其他事务可以继续读该数据，能获取独占锁，但不能对该数据进行更新，直到当前事务执行结束

加了共享锁之后，其他事务可以获共享锁的，但不能获取独占锁，直到当前事务执行结束

独占锁

SELECT
	* 
FROM
	sys_notice 
WHERE
	notice_id = 1 
FOR UPDATE;

上述sql加了独占锁，其他事务可以继续读该数据，不能获取独占锁，不能对该数据进行（修改，删除【也会加锁】），直到当前事务执行结束

加了独占锁之后，其他事务是无法获取独占锁或共享锁的，直到当前事务执行结束

Mysql是怎么在可重复读级别下解决可重复读呢？

通过mvcc，只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView

Mysql是怎么在可重复读级别下解决幻读呢？

快照读：使用MVCC
当前读：通过Next-key锁，既锁住当前记录不被修改，又不允许其他事务往这条记录间隙插入新记录

行锁：针对当前操作的行进行加锁

记录锁（Record Lock） ：属于单个行记录上的锁。
间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。
临键锁（Next-Key Lock） ：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

GAP锁（间隙锁）

不允许其他事务往当前记录间隙里插入新记录，直到当前事务提交

Next-Key锁（记录锁和GAP锁结合形成的）

即要锁住当前记录不被修改，又不允许其他事务往这条记录间隙插入新记录，解决了RR级别在写数据（当前读）时的幻读问题。

意向锁

快速判断是否可以对某个表使用表锁

行级锁什么时候锁表？锁行？

以下只针对Innodb引擎：

在RR级别：如果字段加了索引，只会锁行，没有加索引则会锁整个表

在RC级别：无论加不加索引都只行锁

【RC 隔离级别下MySQL做了优化，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁】

解决写与写的问题

InnoDB使用锁来使得事务进行串行执行【就类似于Java中使用加锁的方式对线程进行控制同步执行】。

也就是在第一个事务更新了某条记录后，就会给这条记录加锁，另一个事务对该数据进行更新时就需要等待第一个事务提交后，把锁释放掉才可以执行本次事务操作

解决写与读的问题

当前读：读的是数据页中的数据（最新数据）

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

快照读：读的是日志中的历史数据

MVCC

是一种多版本并发控制的方法，其实现依赖于：

隐藏字段（trx_id:事务id、roll_pointer:回滚指针）、

Read View（快照：包含了生成快照之前所有未提交事务id列表）、

undo log（旧数据版本链）。

理解：
trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里，即是最新事务对该数据的修改；
roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

注：每个事务开启时，都会被分配一个事务id，所以最新的事务对应的id一定是最新的

MVCC是 针对使用 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务，在事务中首次执行快照读时会生成一个ReadView（相当于一个快照，包含了生成快照之前所有未提交事务id列表），根据记录的trx_id通过这个ReadView查找版本链（undo log）中`符合可见性条件`的记录。要知道普通查询语句只能读取`生成 ReadView 之前已提交事务`对数据的6更改，在生成 ReadView 之前未提交的事务或者之后才开启的事务所做的更改是看不到的。

快照读 读的数据是 undo 日志中符合可见性条件的版本数据，当前读是数据页中的数据（最新记录），两者获取数据不是同一个地方拿的，所以并不会出现冲突问题

事务中快照读的结果是非常依赖该事务首次出现快照读的地方，即某个事务中首次出现快照读的地方非常关键，它有决定该事务后续快照读结果的能力

不同事务或者相同事务的对同一记录行的修改，会使该记录行的 undo log 成为一条链表，链首就是最新的记录，链尾就是最早的旧记录

RC出现不可重复读的原因

在 RC 隔离级别下，在一个事务中，每次查询开始时前都会生成并设置新的 Read View，如果中间有其他事务对记录进行修改了，导致数据不一致，出现不可重复读现象

RR下MVCC如何解决了不可重复读

在 RR 隔离级别下，在一个事务中，只会在事务开始后第一次快照读时生成一个 Read View，之后相同的SELECT操作都复用这个ReadView，自然在快照中查询最新版本记录也是一样的，也就避免了不可重复读

每个事务看到的历史版本可能是不一样的

在同一个事务里，普通查询语句只能读到在ReadView生成之前已提交事务做出的更改，在生成 ReadView 之前未提交的事务或者之后才开启的事务所做的更改是看不到的

MVCC➕Next-key-Lock 防止幻读

方案一：读操作利用多版本并发控制（ MVCC ），写操作进行加临键锁。读记录的历史版本和改动记录的最新版本本身并不冲突

方案二：读、写操作都采用 加锁 的方式。

针对方案一解读

1.在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询生成 Read View，之后的相同的读只会读取第一次生成的Read View，因为读的数据都是历史数据，既解决了不可重复读的问题，又解决了快照读下的幻读问题

2.在当前读情况下，使用临键锁（next-key lock）,既锁住当前记录，又防止在当前记录上一条记录之间添加数据

InnoDB存储引擎对MVCC的实现 | JavaGuide(Java面试+学习指南)

MySQL事务的隔离性是如何实现的？ - 腾讯云开发者社区-腾讯云 (tencent.com)

https://juejin.cn/post/7187206201363398717

日志

redo log

redo log日志会记录事务提交之后的数据信息，在`事务提交后`遇到了系统崩溃，重启MySQL之后只要把redo记录重新更新一下数据页，系统崩溃时对应的事务对数据的修改就可以永久生效，让事务有了`崩溃恢复`的能力，也就意味着满足 持久性 的要求

undo log

对数据增删改的过程

对页中的数据先进行加锁
把当前数据拷贝到undo_log中，使用回滚指针指向拷贝的数据
然后对数据页中的数据进行修改，将当前操作的事务id赋值给当前记录，提交事务并释放锁

首先每个记录中会有两个隐藏字段trx_id（事务id），roll_pointer（回滚指针）。
1. 事务执行前，会把修改之前的旧数据保存到undo 日志里，同时会把当前事务id赋值给页内trx_id，roll_pointer，roll_pointer指向 undo 日志里当前事务修改之前的数据。
2. 当事务执行过程中发生回滚或者失败，就可以使用 undo 日志版本链的旧数据恢复事务修改之前的数据状态。

binlog

主要记录了 MySQL 数据库中数据的所有变化(数据库执行的所有 DDL 和 DML 语句)

redo log 和 undo log 区别在哪？

redo log 记录了此次事务「提交后」的数据状态
undo log 记录了此次事务「提交前」的数据状态

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务

SQL优化

避免使用select *

1.select * 不会走覆盖索引
2.会出现大量的回表操作
3.多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间

优化大量数据分页

SELECT id FROM test1 LIMIT 9000000,2000;

原理：先通过获取offset+limit条数据，然后再抛弃掉offset条数据，取limit条

-- 使用延迟关联：先通过分页查询主键，然后通过主键
SELECT
	t1.id 
FROM
	test1 t1
	INNER JOIN ( SELECT id FROM test1 t1 LIMIT 9000000, 2000 ) t2 ON t1.id = t2.id;

SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

使用union all或者union，分两条sql查询替换or查询
使用 > 值 and < 值替换 !=

生产环境大多使用RC

缘由一：在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多！

缘由二：在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行!

https://topjavaer.cn/database/mysql.html#事务隔离级别有哪些

posted @ 2023-07-07 00:17 永无八哥阅读(10) 评论(0) 编辑收藏举报

刷新页面返回顶部

永无八哥

人生苦短，但愿世间再无bug