MySQL 拓展笔记

三范式理解

第一范式：所有列原子不可分
第二范式：一行数据要有一个唯一标识该行的字段（比如主键）
第三范式：要求表中每列都和主键列直接相关

常用函数

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关联时都可使用

字符集与排序规则

image-20230823114227991
  1. 字符集理解

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

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

就是将字符与二进制数据进行映射
  1. 排序规则

排序规则 的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中。所以建表时要注意列排序，同时也要注意是否区分大小写问题，比如加了唯一约束后，如果是utf8_general_ci大小写不敏感，认为大写小写是同一个。

案例

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

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

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

  2. mysql处理字符集

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

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

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

compact 行格式 记录格式如下

image-20230824215344290

事务基础理解

隔离性：

隔离性是为了解决多个不同的事务并发读写时由于交叉执行而导致数据的不一致问题

同一个事务内前面对表数据的修改（增删改）对当前事务后边任何操作都是可见的

原因：在一个事务中，执行普通的查询会生成ReadView，其包含m_ids、min_trx_id、max_trx_id、creator_trx_id，
前面三个不管用，看第四个creator_trx_id是生成ReadView时的所在事务的事务id。

当在这个事务中对数据进行修改时，会将当前正在操作的事务id赋值给该记录的trx_id

执行普通的查询时，会通过可见性条件判断当前版本记录是否可见，具体的第一步就是判断当前记录的隐藏列trx_id是否等于creator_trx_id，如果相同表示当前事务正在访问已修改的记录信息，是能被当前事务访问到。

修改数据的过程

-- 1.开启事务
BEGIN;

-- 2.加锁 lock
-- 3.复制数据到日志中 copy到undo log中
-- 4.修改数据（同时将操作的事务id复制给trx_id）
UPDATE `t_stu` SET age = 4 WHERE `name` = 'zs1';

-- 5.提交事务
COMMIT;
-- 6.释放锁 unlock，唤醒其他阻塞的事务

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

主要来自两方面的努力

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

持久性：事务提交后，如果MySql崩溃了，重启后可以保证该事务对数据的永久性修改

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

索引(innodb)

对所有数据排好序的目录

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

页分裂概念

为了保证记录按索引值有序排放，可能还要让一个页裂开分成两个，也可能会将记录会移动到其他页，这些操作叫做页分裂

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

举例

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

图解MySQL页分裂

页

  1. 页是 MySQL 中磁盘和内存交互的基本单位，也是 MySQL 是管理存储空间的基本单位。内存每次读取的是以16KB的页为单位，写入的也是以16KB的页为单位。

  2. 一个页可以理解为图书馆里的一个个书架，行记录理解为书架的一本书籍

  3. 页与页之间存在双向链表，页内记录是单向链表关联

  4. 读取一个页从磁盘到内存可以当做一次磁盘I/O操作，Innodb 引擎对于千万级别的数据只需要3-4次磁盘I/O操作

  5. 通过索引查找数据的过程主要通过二分法、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. 叶子节点存储完整的用户记录（包含隐藏列、记录额外信息）

二级索引：
1. 使用索引列对记录和页进行升序排列
2. 叶子节点存储索引列和相应的主键值

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

聚簇索引

特点

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

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

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

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

组成

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

非聚簇索引

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

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

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

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

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

建立索引index_c2_c3（c2,c3）

image-20230304101826886

image-20230202210013108

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

索引总结

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

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

  2. 二级索引与联合索引

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

  3. 每建立一个索引都会创建一个相应的b+树

  4. 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索引列，用不到birthday列的，因为查到的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：通过二级索引等值查询查询

eq_ref：如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的

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

index：扫描索引树，其一般在内存中执行，相对于all来讲执行更快

all：全表扫描

key：实际用到的索引

rows：预计扫描的索引记录行数

extra信息:

1. Using filesort：在内存中或者磁盘上进行排序的方式统称为文件排序
2. Using temporary：借助临时表来完成一些功能，比如去重、排序之类的

如果查询的sql命中了索引还是慢，就要看看rows预计扫描的行数多少。也就是说慢查询和索引没有必然联系，一个SQL语句的执行效率最终要看的是扫描行数

MySQL中只写group by 分组字段默认情况会添加order by 分组字段（8.0.13版本之前）的，如下

SELECT
	key1,
	COUNT(*) AS amount 
FROM
	single_table 
GROUP BY
	key1;

-- 等价于
SELECT
	key1,
	COUNT(*) AS amount 
FROM
	single_table 
GROUP BY
	key1
ORDER BY key1;

最左匹配原则

查询条件中包含联合索引只最左列

索引覆盖与索引条件下推（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`;

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

  1. 可能用不到覆盖索引
  2. 无用数据量多，网络传输消耗资源
  • 索引条件下推（目的是减少回表操作，5.6版本及其之后才支持的）

如果查询条件包含了索引列，则优先使用索引列对数据进行过滤，然后将符合条件的结果再返给数据库server层。这样能有效的减少回表次数。

使用条件下推的条件：

1. 针对联合索引
2. 条件查询中要有联合索引最左边第一个索引列（满足最左匹配原则）

比如建立联合索引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）进行条件判断，过滤出符合的主键值，然后再回表再通过聚簇索引树拿数据
image-20230305233848830

案例

image-20230824181915787

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

测试

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';		-- 索引条件下推

SELECT * FROM `table` WHERE b= '1' AND c ='2';		-- 不符合最左匹配原则，不会走索引

函数索引

对指定的列使用函数对其排序生成一个索引树，被成为函数索引。MySQL 8.x才支持的

比如用户表中的创建日期是年月日时分秒的格式，但是有需求要查询某日期下的用户数据，又想使用索引，这时候就可以为create_time创建一个函数索引

ALTER TABLE sys_user ADD INDEX idx_log_time ( ( date( update_time ) ) )

就可以使用date()函数执行查询

SELECT * FROM sys_user WHERE DATE(update_time) = '2029-12-10';

MyISAM存储引擎

特点：

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

    索引也是个数据结构，只不过索引文件中叶子节点存储的是行号、索引列。
数据文件存储的是行号、完整记录

    查找过程

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

缺点：

  1. 通过索引查询都需要回表操作

  2. 只支持表级锁

使用索引

  1. order by

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

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

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

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

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

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

  3. 避免回表操作

    最好在查询字段里只包含索引列

    当然，如果业务需要查询出索引以外的列，那还是以保证业务需求为重。但是我们很不鼓励用 * 号作为查询列 表，最好把我们需要查询的列依次标明。

  4. 索引重复的数据越多，则不建议建立索引。重复的数据越多则排序对其排序没有效果。

    比如逻辑删除字段，性别等

  5. 索引列前缀：如果字符串中字符比较多，可以对字符串前几个字符建立索引，而不是对整个字符串建立索引。

  6. 使用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 连接起来后是无法使用该索引的

  7. 联合索引使用>=失效情况

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

  8. 对列使用函数时如果想要命中索引，可以使用索引函数建立索引

    alter table t1 add key idx_index ((DATE(create_time)));

  9. 左模糊全模糊并不一定会使得索引失效，要看查询的结果是否只有主键值、索引列，如果只有这两项，那么就可以利用到索引覆盖

  10. 强制sql走某一条索引

    FORCE INDEX('索引名称')

    SELECT * FROM `user` FORCE INDEX(`idx_code`)  WHERE CODE = '周星驰';

索引数据结构以及优缺点

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

  • hash表
1. 存在hash冲突
2. 范围查询性能差
  • 数组
查询快，但插入元素时需要移动后面所有的数据，性能差

  • 二叉查找树

    特点：左节点比父节点小，右节点比父节点大

天然保持着二分查找，不用移动后面的数据，时间复杂度是O(log N)，但极端情况下会退化成链表，时间复杂度变成了O(N)，如果是主键递增很容易退化成链表
  • 平衡二叉树
不会退化成链表。
但一个父节点只能分叉出两个儿子节点，随着数据增多，树的高度会越来越高，磁盘IO次数也就越来越多
  • 红黑树
添加和删除节点很快。但其不追求绝对的平衡，只是大致的平衡，所以也会可能导致出现一边过于倾斜现象，出现磁盘IO次数会过多
  • B树（可以理解为多叉树）
优点：
一个节点包含多个子节点

缺点：
1. 但存储的内容是记录的完整数据
2. 元素之间没有指针，范围查询效率低
  • 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

索引失效

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

比如关联字段字符集不同
  • 联合索引非最左匹配
比如table（id,a,b,c,d）表创建了一个 index_a_b_c(a, b, c) 联合索引
select * from table where b=1 and c=3；

锁

lock锁与闩锁区别

image-20230905221624316

- MySQL锁互斥性针对的是事务，可以理解为事务锁。也就是多个事务对同一条数据进行操作时，会让这个事务串行执行；锁的对象是索引；在RR级别下加的锁是临键锁，在一定条件下会退化成间隙锁或记录锁，所以RR级别下一定对于更新语句条件要走索引

- Java程序中的互斥锁针对的对象是线程

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

共享锁

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锁，既锁住当前记录不被修改，又不允许其他事务往这条记录间隙插入新记录

但RR下不能完全避免幻读问题

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

记录锁（Record Lock） ：属于单个行记录上的锁。
间隙锁（Gap Lock） ：锁定了两个值的范围，不包括记录本身。
比如 SELECT * FROM t_stu WHERE age > 4 FOR UPDATE;	--是锁住age > 4范围的数据

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

GAP锁（间隙锁）

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

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

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

image-20230912222401799

意向锁

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

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

以下只针对Innodb引擎：

  • 在RR级别：

如果查询条件走了唯一、主键索引，只会锁行。普通索引会加间隙锁，无索引对表中所有的数据加临键锁

  • 在RC级别：

无论命不命中索引都只会锁行

RC 隔离级别不走索引也会锁行，是因为在这方面做了改进，在扫描所有的记录时如果发现不满足条件，会采取解锁方式

解决写与写的问题

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

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

解决写与读的问题

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

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

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

image-20230313234244995

MVCC

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

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

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

undo log（旧数据版本链）。

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

注：
InnoDB 只有读写/写（增删改）事务分配 trx->id，而单纯的读事务 trx->id = 0

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

1. 如果正在读取的数据正在执行update或delete操作，这时读操作不会等待锁的释放，相反它会去undo日志中读取最新的版本记录

2. 事务中快照读的结果是非常依赖该事务首次出现快照读的地方，即某个事务中首次出现快照读的地方非常关键，它有决定该事务后续快照读结果的能力
3. 事务在执行的过程中，只有在第一次执行增删改时才会被分配事务id，而且这个事务id是递增的
image-20230905210409217

可见性算法

image-20230305225337230 image-20230825000737824

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

RC出现不可重复读的原因

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

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

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

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

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

MVCC➕Next-key-Lock 防止幻读

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

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

https://juejin.cn/post/7187206201363398717

日志

redo log

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

undo log

作用：

一是可以用来事务回滚使用的，保证原子性；二是是参与MVCC

对数据增删改的过程

  • 对页中的数据先进行加锁
  • 把当前数据拷贝到undo_log中，使用回滚指针指向拷贝的数据
  • 然后对数据页中的数据进行修改，将当前操作的事务id赋值给当前记录，提交事务并释放锁
首先每个记录中会有两个隐藏字段trx_id（事务id），roll_pointer（回滚指针）。
1. 事务执行前，会把修改之前的旧数据保存到undo 日志里，同时会把当前事务id赋值给页内trx_id，roll_pointer，roll_pointer指向 undo 日志里当前事务修改之前的数据。
2. 当事务执行过程中发生回滚或者失败，就可以使用 undo 日志版本链的旧数据恢复事务修改之前的数据状态。
image-20230316104209076 image-20230315171953957

binlog

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

redo log 和 undo log 区别在哪？

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

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

SQL优化

  1. 避免使用select *

    1. 不会走覆盖索引
2. 可能会出现大量的回表操作
3. 多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间
4. 在使用基于`块`的嵌套循环查询会将驱动表中的数据放在join buffer，字段越少，join buffer 所缓存的数据就越多，外层表的循环次数就越少

  2. 优化大量数据分页

    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

  3. 使用union all或者union，分两条sql查询替换or查询

  4. 使用 > 值 and < 值替换 !=

  5. 生产环境使用RC

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

    缘由二：在RR隔离级别下，条件列未命中索引就会触发全表扫描，会对所有的记录进行加锁，也就是锁住表了！而在RC隔离级别下，只锁行。一旦锁表会导致其他更新这个表的数据会被阻塞，导致业务滞懈

    参考

  6. 避免大量数据一次性执行操作，应该采用分片处理

  7. 分页查询再没有要求指定字段排序，默认要使用主键进行排序

  8. 深分页问题使用延迟关联查询，同时也可从业务角度加限制：如查询必须至少有一个条件，避免无任何条件查询

注意事项

注意：

  1. 一行能存储多少数据
一个行中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节！

  1. 唯一二级索引列并不限制NULL值的数量，所以要保证某列数据是唯一性，不仅仅要加 唯一约束，还要保证是 not null

  2. join关联查询要点

    1. 驱动表只会访问一次，被驱动表会被访问n次，n次是来源于对驱动表查询到的记录数（基于嵌套循环查询时的逻辑）
2. 关联查询本质上还是对单个表一个个查询的
3. 驱动表的查询条件只是where部分，不包含 on 条件，被驱动表查询条件涉及where部分 和 on 条件部分
4. 基于嵌套循环查询逻辑是：每次取驱动表中的一条数据时，就要加载被驱动表每一条数据到内存中进行匹配，当再次取一条数据时就会再次加载被驱动表的每一条数据进行匹配
5. 基于块的嵌套循环查询逻辑是：当加载被驱动表数据到内存中时，在执行查询前将要查询的数据放在内存中（join buffer），一次性和多条驱动表的记录进行匹配，有效减少被驱动表加载时的I/0次数
6. 优先使用基于索引的嵌套循环查询，其次是基于块的嵌套循环查询，最后是基于一般的嵌套循环查询

    写出好的Join语句，前提你得懂这些

    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;		-- sql01
-- 对被驱动表使用on连接条件与where进行查询（访问多次，次数取决于上一次sql01查询的结果集）
SELECT * FROM tb_role_resource_relation WHERE role_id = 1;		-- sql02
-- 对被驱动表使用on连接条件与where进行查询（访问多次，次数取决于上一次sql02查询的结果集）
SELECT * FROM tb_resource_info WHERE parent_id = 1 AND data_status = 1;		-- sql03

buffer pool

buffer pool 是一块内存区域，为了提高数据库的性能，当数据库操作数据的时候，把硬盘上的数据加载到 buffer pool，不直接和硬盘打交道，操作的是 buffer pool 里面的数据，数据库的增删改查都是在 buffer pool 上进行

预读机制:
Buffer Pool 有一项特技叫预读，存储引擎的接口在被 Server 层调用时，会在响应的同时进行预判，将下次可能用到的数据和索引加载到 Buffer Pool
posted @ 2024-02-22 22:35  永无八哥  阅读(17)  评论(0)  编辑  收藏  举报