MySQL-学习笔记

基础篇

sql字段

1. date 类型

date类型用于存储日期值，格式为’YYYY-MM-DD’，显示范围是从’1000-01-01’到’9999-12-31’。它只包含日期部分，不包含时间部分。

2.datetime 类型

datetime类型用于存储日期和时间值，格式为’YYYY-MM-DD HH:MM:SS’，显示范围是从’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。它同时包含日期和时间部分

在java 开发中, 无论是String类型的对象, 或者是 java.util.Date() 类型的对象都是可以作为 日期类型的数据直接传入的.

mysql这边都可以很好的兼容~

可能还存在一些其他类型的 对象也可以被 mysql的日期类型所兼容 。

SQL 语法

SQL执行顺序

分组

查询的字段一般为 聚合函数 和 分组字段 , 其他字段无任何意义.

内连接

外连接

自连接

联合查询

子查询

进阶篇

1.关于DQL 的执行顺序

DDL : 数据定义语言

DML : 数据更改语言

执行顺序如下：
1. From
2. where
3. GROUP BY
4. HAVING 
5. SELECT 
6. ORDER BY 
7. LIMIT

日期类 的函数

【注意】这里的DAY() 得到的日期只是这个 单纯的日， 并不包含其他的数字部分。

DATEDIFF (date1,date2) ,这里的话是用 date1 - date2 得到的是一个正值。

2.事务简介

事物是一组操作的集合, 它是一个不可分割的工作单位, 事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求, 即这些操作要么同时成功,要么同时失败。

MySQL 的事务是默认自动提交的。

1，set @@autocommit = 0; 设置事务的提交方式为手动提交 ， 1是自动提交

2，也可以设置这个 set autocommit = off 关闭自动提交

3，start transaction

事务的隔离级别查询：

1.查看当前会话隔离级别 select @@tx_isolation; 或者select @@Transaction_isolation; 2.查看系统当前隔离级别 select @@global.tx_isolation; 3.设置当前会话隔离级别 set session transaction isolatin level repeatable read; 4.设置系统当前隔离级别 set global transaction isolation level repeatable read; 5.命令行，开始事务时 set autocommit=off 或者 start transactionbegin; 也可以开启事务

• 脏读： 对于脏读，我们只有在数据的隔离级别为这个 未提交读 的 隔离级别下才会有这种情况的发生。
• 不可重复读：已提交读的事务隔离级别 下， 在一个事务A中 两次读取同一个数据之间， 有别的事物更改了这个数据， 那么此时就会发生两次读取到的数据不一致的问题， 也就是这个不可重复读的情况。

【注意】 在出现幻读的前提是： 已经解决了这个不可重复读的问题， 隔离级别到了这个可重复读。 
【可重复读】： 就是在一次事务A中， 两次读取同一个数据， 即使中间有其他事务B去对我们的这个数据进行了修改，并且提交，
但是， 我们事务A的两次读取数据都是一致的。这里看到的这个资料的意思是， 事务A 可以看到在事务A 开始之前的提交后的数据，但是在事务开始之后的对数据的修改本事务是看不到的 

• 幻读：在这个 可重复读的隔离级别 下，数据会发生幻读的情况。 比如事务A 的刚开始先去查询id为1的数据，此时没有查到。然后事物B进行了一次数据的插入, 插入了一条id = 1 的数据, 然后事物A再执行这个插入一条id为1的数据，但是会插入失败，但是如果再查询一次的话，仍然是没有。 因为是已经是可重复读的隔离级别下~

事务的隔离级别：

从上图中我们可以明显的看到这个： 在默认的隔离级别下， 还是会有这个幻读的发生的可能。 存在这个问题
串行化的隔离级别是最好的， 并发性能是最低的。我们的每一个sql都是需要经历开启事务和提交的阶段的. 
增删改查当隔离级别是串行化的时候, 当有一个事务开启的时候, 别的事务的提交都不会执行成功。 只有等最先开始的事物提交成功commit 了，
才会让别的事物commit。 

set session transaction isolation level read uncommitted;  -- 设置事务的隔离级别为 读已提交  /  是当前会话的隔离级别

在可重复读事物隔离级别下:

有2个概念:

• 可重复读: 是对于同一条数据, 是不会读取到其他事物未提交的数据的, 当然这个是已提交读的事物隔离级别就已经实现的功能。

​ 但是相比于已提交读隔离级别的增加的特性是: 对于在一次事物中, 2次读取到的同一条数据是一致的, 即使有其他事物对 其更改了, 这个是已提交读的隔离级别做不到的.

​

​ 但是存在的问题是: 但是对于别的事物的 insert 语句是防止不住的, 如果是一个条件查询, 在一个事物A中2次查询其间, 有另一个事物B, 插入了一些满足条件的数据, 就会导致出现幻读现象。 2次读取到的数据不一致。

幻读，目前我了解的有两种说法：

说法一：事务 A 根据条件查询得到了 N 条数据，但此时事务 B 删除或者增加了 M 条符合事务 A 查询条件的数据，这样当事务 A 再次进行查询的时候真实的数据集已经发生了变化，但是A却查询不出来这种变化，因此产生了幻读。

这一种说法强调幻读在于某一个范围内的数据行变多或者是变少了，侧重说明的是数据集不一样导致了产生了幻读。

说法二：幻读并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：A事务select 某记录是否存在，结果为不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。产生这样的原因是因为有另一个事务往表中插入了数据。

理论上 幻读更应该是第二种说法:

因为在RR的隔离级别下, 本身就是一个快照读的方式, 只能读取到开启事务的时候的快照数据, 并不能读取到实时变化的数据, 所以第一种说法不认同.

第二种说法 认同, 正是因为快照读, 但是其他事物插入数据的时候是可以的, 但是当前事务又读取不到已经插入的数据, 但是去尝试插入数据就会有问题.

要想解决这个事务的 幻读 问题， 只能把数据库的事务的隔离级别调整到这个 可串行化，serializable。 （规避所有的并发问题，但是性能最低）

2. 存储引擎

通过查看建表语句, 可以看到会有一个默认的存储的引擎. innodb

2.1 InnoDB 存储引擎

也就是我们的数据是存储在 页 逻辑单元中的. 

Trx: 最后一次操作事务的id
Roll pointer: 
col1: 一个一个的字段的值

在innodb存储引擎中, 一个页是磁盘操作的最小单元,

一个Extent 的大小是固定的，是 1M ； 一个页的大小是16K

2.2 MyISAM存储引擎

sdi 是一个文本文件是可以直接打开的

2.3 Memory存储引擎

3种存储引擎的区别：

存储引擎的选择

3. 索引

3.1 索引简介

前言：
要知道系统中所有的动作都是放到内存中由CPU来执行处理的。 
所以我们的查询的操作也是如此，需要先把数据一部分一部分的加载到内存中然后由CPU执行比对操作，最后查询到我们所有需要的数据。

没有索引的时候，查询数据是进行一个全表的扫描。 当建立了索引之后，根据对应的字段建立索引。 如果是一个二叉树的索引结构， 那么比对查询的次数就是树高，已经会高效了很多。 基本已经不是一个数量级的了。

比如一个100万的数据，如果没有索引的话，我们需要比对100万次数据。

如果有这个索引的话，我们以建立二叉树的索引为例，那么建立100万的数据，需要的树高是：

设树高为 n ： 100万 = 2^(n) -1 算出来大约 n= 20次, 也就是如果有建立一个索引, 那么查询的速度 只是比对20次左右.

3.2 索引结构

如果索引结构选择的是2叉树， 那么在插入数据的时候，如果数据是一个顺序的，那么就会形成一个链式结构。

也就是缺少一个自平衡的算法。

所以可以选择红黑树， --> 右侧

3.2.1 B Tree （B树: 多路平衡查找树）

B树，当每次插入进来第5个元素后， 都会发生中间的元素向上分裂。

阶数指的是：指针的个数，或者说是分支的最大个数。

3.2.2 B+ 树

可以看到不同的是：
1. 数据是存放到了叶子节点中
2. 叶子节点之间还形成了一个单向链表
3. 所有的节点都会出现在叶子节点， 非叶子节点只是起到了索引的作用

但是在mysql的B+树， 在B+树的基础上进行了扩展，将底部叶子节点的单向链表扩展为了双向链表。提高了区间访问的性能。

每一块数据都是存放到页中。

3.2.3 哈希

3.2.4 为什么InnoDB 选择B+树作为索引结构

1.聚集索引： 叶子结点存放的是该行的数据    
2.非聚集索引： 叶节点存放的是id值    

3.3 索引分类

首先这里要清楚，对于一张表，我们是可以有多个索引的。每一个索引会有2种选择，要么是聚集索引，要么是非聚集索引。
首先主键索引一定是一个聚集索引，且一张表中只有一个。 当然, 前提是这个表有主键,无论是单个字段的主键,还是多个字段的联合主键。其他的二级索引那就可以有很多个了。二级索引的叶子节点上的数据域存储的是主键的值，（一般是主键id的值）
那我们查询的时候是根据这个二级索引查找到对应的id，再在这个聚集索引中根据id找到对应的数据

优化sql ：

针对于上面的两种两种sql 语句，很明显这个第一种的效率更高一些， 因为这个直接根据主键去查询的话，速度更快。
而根据name 的话需要进行一次回表的操作， 会慢一点。

在B+ 树的结构中， 每个结点时存放在这个页中的，而页的大小时固定的，为16K， 每个结点 存储的信息为 key ， 以及指针。

这样的话，相比于B树，就可以存放更多的key， 这样可以提升IO 效率， 减少树的层级高度，提升搜索的效率。

相比于Hash索引，B+ 树 支持 范围匹配 以及 排序操作。

对于这个唯一索引： 我们在创建这个唯一约束的时候， 会自动给我们创建一个唯一索引。 
主键也是， 默认会给创建一个主键索引。

3.4 索引语法

• 创建索引

  CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(index_col_name,... 列名) -- 列名不需要加这个""
  -- 如果在索引前不指定的话,那么创建的索引就是一个常规索引
  

• 查看索引

  SHOW INDEX FROM table_name;
  

• 删除索引

  DROP INDEX index_name ON table_name;
  

1： 这个索引名字一般是： idx_表名_字段名 

查询数据库的增删改查分别执行了多少次的语句
show global status like 'Com_______' -- Com 后面是跟了7个下划线。

4. sql 性能分析

4.0 查询sql执行的频次

SHOW global status like 'Com_______';
-- 其中后面一个_ ,代表一个字符.

4.1 慢查询日志

因为我们不知道哪些sql的执行时间较长, 那么就可以通过慢查询日志来帮助我们定位到哪些sql的执行时间较长。

-- 开启慢日志查询 
set global slow_query_log=1; 
-- 修改慢日志记录SQL的最低阈值时间 
set global long_query_time=4;

对于慢查询sql 只会记录这个比设置的时间大的sql语句，

但是这个 其他的sql 的耗时， 我们可以通过这个 profile 进行查看， 需要开启 profiling， 然后就可以查看我们执行的每一条sql的耗时。

慢查询日志文件

通过慢查询日志找到我们的执行慢的sql

4.2 profile 详情

这个命令在mysql5.7以后就不推荐了

打开可以看到这个sql执行的耗时情况

4.3 explain 执行计划

explain: 的字段如下：
id: 查询语句的id
select_type：select 的类型
table： 表名
partitions：
type：连接类型
possible_keys：可能用到的索引
key: 实际用到的索引
key_len： 索引长度
ref：
rows：
filtered,
extra：在前面的select 后面如果不是查询 所有字段， 那么

【注意】 ： explain 只能解释这个select 语句 ！！！！

4.3.1 id

如果一个联合多表查询, 那么其中就会有多个执行的语句。 id相同，执行顺序从上到下， id值不同，值越大，越先执行。

4.3.2 select_type

查询类型：

SIMPLE： 简单表，即不使用表连接或者子查询

PRIMARY： 子查询中最外层查询, 查询中若包含任何复杂的子部分, 最外层的select被标记为primary

UNION：UNION 中的第二个或者后面的查询语句

SUBQUIRY：SELECT　/ WHERE 之后包含了子查询

4.3.3 type(非常重要的字段)

其中type字段：连接类型（null 是不扫描表）

最为常见的扫描方式有： (性能从上至下递减)

NULL: 当你不访问任何表的时候, type 是null , 所以一般业务系统中不太可能优化为null

• system：系统表，少量数据，往往不需要进行磁盘IO；

• const：常量连接；（主键或者唯一索引访问）
  

• eq_ref：主键索引(primary key)或者 非空唯一索引 (unique not null)等值扫描；

• ref：非主键 非唯一索引 等值扫描； （非唯一索引）
  

• range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引

• index：Full Index Scan，index 与 ALL 区别为index类型只遍历索引树。这通常为ALL块，因为 索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）

• ALL：全表扫描(full table scan)；

如果查询的是主键或者唯一索引，那么速度是要优于普通索引的

4.3.4 possible_key

显示可能应用在这张表上的索引， 一个或多个。

4.3.5 key_len

表示索引中使用的字节数， 该值为索引字段最大可能长度， 并非实际使用长度， 在不损失精确性的前提下， 长度越短越好。

此列显示MySQL在索引里使用的字节数，通过此列可以算出具体使用了索引中的那些列。索引最大长度为768字节，当长度过大时，MySQL会做一个类似最左前缀处理，将前半部分字符提取出做索引。当字段可以为null时，还需要1个字节去记录。

​ key_len计算规则：

​ 字符串：

​ char(n)：n个数字或者字母占n个字节，汉字占3n个字节

​ varchar(n)： n个数字或者字母占n个字节，汉字占3n+2个字节。+2字节用来存储字符串长度。

​ 数字类型：

​ tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节

​ 时间类型

​ date：3字节 timestamp：4字节 datetime：8字节

4.3.6 rows

MYsql认为必须要执行的查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

4.3.7 filtered

表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

4.4 联合索引

如果给一个表添加多个单列索引， 和给多个字段添加这个联合索引

单列索引的，查询时， 只会走其中一个索引。 在数据量比较大的时候，联合索引更加有优势。

联合索引失效的情况：

最左前缀法则：

就是在建立了联合索引后，我们查询时的where条件中的 必须要有最左侧的字段的条件，但是与我们where的条件的顺序是没有关系的。

字符串类型的数据查询时, 需要加上单引号, 或者双引号.

• 数据分布影响

  如果MySQL评估使用索引比全表更慢, 则不使用索引。
  
  

所以走不走索引也会取决于表中数据的分布。

1：没有遵循最左前缀法则
2：中间有跳跃，那么跳跃后的部分索引失效
3：在索引字段上出现范围查询，范围查询右侧（右侧是指建立的联合索引的顺序）的列索引失效。  
（如果是使用这个 > , 右侧会失效；但是使用>= , 则不会失效，很迷）
4：在索引列上进行运算操作， 索引会失效
5：字符串类型字段使用时，不加引号，索引将失效
6：模糊查询时，头部模糊查询时，索引会失效 （ like %key% , like %key ) 索引会失效，（like key% ) 索引不会失效
7：查询条件中，使用 or 关键字，如果有一侧的条件字段中没有索引，那么会使索引失效

4.4.1 SQL 提示

如果对应于某一个字段既有单列索引，又有联合索引的情况，我们可以人为的指定要走什么索引 ， 整个提示是我们给mysql提示
use index（索引名） ， 这个是建议， mysql 自己会再做一个评估
force index（索引名） ， 这个是强制的 必须走这个索引

4.4.2 覆盖索引

【回表】

如果我们查询的字段刚好 , 这个字段有索引的话， 那么就不会回表，提高效率

比如下图： 表的 id ， name， 这两个字段我们都有索引，那么根据name 去查询的时候， 如果 select id，name 那么会直接返回结果，不用回表。

但是如果其中存在这个没有索引的字段，那么就需要一次回表的操作

覆盖索引（Covering Index）：也叫索引覆盖。就是select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
注意：
a、如需使用覆盖索引，select列表中的字段只取出需要的列，不要使用select *
b、如果将所有字段都建索引会导致索引文件过大，反而降低crud性能

4.4.3 前缀索引

目的： 有些字段比较长的字符串，我们可以给它建立部分的索引， 从而降低索引的体积，提高查询的效率

了解一下即可：

前缀的长度可以根据索引的选择性来决定

【查询过程】

4.4.4 索引选择

推荐尽量使用联合索引， 避免使用单列索引

使用覆盖索引，还可以避免回表查询。 提升效率

4.4.5 索引设计原则

4.4.6 索引失效

在 MySQL 中，索引失效的情况有多种，以下是一些常见的索引失效情况：

1. 不满足最左前缀原则：在使用联合索引时，查询条件需要按照索引中定义的字段顺序进行匹配，如果顺序不匹配，索引可能失效 。
2. 索引列上有计算：如在 WHERE 条件中对索引列进行加1操作，会导致索引失效，因为数据库需要先进行计算再进行索引查找 。
3. 索引列使用了函数：例如使用 SUBSTR() 函数处理索引列，会导致索引失效，因为索引系统无法应用于函数的结果 。
4. 字符类型没加引号：如果索引列是字符类型，但在查询时没有使用引号，会导致类型不匹配，索引失效 。
5. 使用了 LIKE 查询：当 LIKE 查询的条件以 % 开头，如 '%value'，索引将失效，因为这种模式无法有效使用索引进行查找 。
6. 使用 IS NULL 和 IS NOT NULL：如果字段不允许为空，使用 IS NULL 或 IS NOT NULL 作为查询条件时，索引可能失效 。
7. 使用了 OR 条件：如果 OR 两边的条件中只有一边有索引，索引可能失效 。
8. 使用了 SELECT *：使用 SELECT * 可能会让索引失效，因为可能需要回表查询非索引列，如果查询的列都是索引列，则可以使用覆盖索引
9. 索引列参与了类型转换：如在查询条件中将数值类型与字符类型进行比较而没有进行适当的类型转换，会导致索引失效 。
10. 使用了 NOT IN 或 NOT EXISTS：这些操作可能会导致索引失效，尤其是当 NOT IN 子句中的列表较大时 。
11. ORDER BY 索引字段顺序不当：如果 ORDER BY 子句中的字段顺序与索引定义的顺序不一致，或者 ORDER BY 涉及到多个索引，可能会导致索引失效 。
12. 列对比操作：在查询条件中对两个索引列进行比较，如 id = height，可能会导致索引失效 。
13. Mysql优化器的决策：在某些情况下，MySQL 优化器可能认为全表扫描比使用索引更快，因此会放弃使用索引 。

以上是一些常见的索引失效情况，但请注意，索引的使用和效果可能会受到 MySQL 版本和具体查询负载特性的影响。在实际应用中，应使用 EXPLAIN 命令来分析查询语句是否真的使用了索引，并根据具体情况进行优化。

5.SQL 优化

5.1插入数据优化

• 如果是插入多条数据，最好不要使用这种一次插入一条的方式

可以使用一次插入多条的方式

• 如果是一次性插入大量的数据，使用insert 语句性能比较低，此时可以使用MySQL数据库提供的load 指令进行插入。
  

一次导入几百万的数据 大概几十秒的时间。

1百万的数据 只花费了7.56秒, 非常快。

5.2 主键优化

主键乱序插入的话，会发生页分裂的情况，效率比较低。

在删除某一行数据时， 当删除到一定的数据时，可能发生页合并。

5.3 主键设计原则

5.4 limit优化

使用 覆盖索引 + 子查询的方式来查询，可以提高性能。

原来： select * from tb_sku limit 100000,10  -- 查询从100000 后的10条数据
采用覆盖索引： 
select * from tb_sku t,(select id from tb_sku) s where t.id = s.id; -- 但是这种方式， 这个id必须是要自增的吧， 不然查出来还是不一致。这里为啥要 order by 呢 ？   

5.5 update 优化

在mysql 的默认的隔离级别下：

事务A 对这个id = 1的数据进行 update， 事务B 在想进行update 操作的时候，就不可以了。 但是可以读数据。

在事务A读数据的时候，事务B 是可以进行一个写操作的。

事务A 对 这个没有加索引的字段作为条件进行更新，是会锁住整张表的。

此时事务B再想进行一个操作的时候，是会被阻塞住的。

针对于这个行锁的是 where 条件是这个 创建了索引的字段。

避免这个由行锁升级为表锁~

5.6　优化总结

6.　锁

按照锁的粒度分为一下三类：

1.全局锁: 锁定数据库中的所有表
2.表级锁: 每次操作锁住整张表
3.行级锁: 每次操作锁住对应的行数据

6.1 全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于这个只读状态， 后续的DML的写语句，DDL语句， 都会处于一个阻塞的状态。其典型的使用场景就是做全库的逻辑备份， 对所有的表进行锁定，保证这个数据的一致性以及数据的完整性。（一致性：  比如有两张表，订单表，库存表） 可能会得到备份的数据不一致的问题。   就是这个订单表里面有这个数据， 但是备份的库存没有扣减。  也就是这个数据不一致的问题。 

一般在执行这个数据备份的时候，会先给这个数据库加全局锁。

flush talbes with read lock； 加锁

unlock tables；解锁

【注意】mysqldump 这个语句是直接在Windows 命令行中执行的, 并不是sql 语句.

在innoDB 引擎中,我们可以在备份时加上参数-- single-transaction 参数来完成不加锁的一致性数据备份 。

mysqldump -h:IP+端口 --single-transaction -uroot -p1234 数据库名 >  存放的路径

6.2 表级锁

分为以下3类:

1.表锁

2.元数据锁

3.意向锁

6.2.1 表锁（要自己去加锁)

DDL: 对 表结构 进行更改的操作

DML: 对表中数据进行更改的操作

DQL: 对表中数据进行查询的操作

需要显示地写这个 加解锁的语句。

查询表的锁的语句:

select * from performance_schema.metadata_locks;

1; 对于 【读锁】; 
本客户端和其他客户端是可以读, 但都不可以写.
lock tables 表名 read;
unlock tables;2; 对于 【写锁】;
本客户端可以读写, 其他客户端不可以读和写.
lock tables 表名 write;
unlock tables;

6.2.2 元数据锁 （不用自己去加）

DDL 是对这个表结构的更改， DML是对这个数据的更改。

**目的：**为了维护数据的一致性，当这个表上面有这个事务的时候，是不可以对表中的数据进行写入操作的 。 避免DML 和 DDL 发生冲突，从而导致这个数据不一致的问题。

6.2.3 意向锁🔒 (不需要手动地去添加)

在没有引入这个意向锁之前，在每次进行添加表锁的时候, 需要检查一遍整张表, 查看是否已经被上了这个行锁, 如果没有上锁，那么可以直接加锁； 如果上锁了，需要判断行锁🔒的类型，来确定是不是可以成功加锁，效率比较低。 一行一行的来检查的。

为了提高效率， 引入了意向锁。

同样，比如线程A 给表中的某一条数据加了这个行锁，紧接着会给整张表加一个意向锁🔒，那么此时线程B要对这个表来执行一个加表锁🔒的操作，它会首先来检查这个意向锁的情况， 然后来判断当前的意向锁是否与这个表锁兼容。

引入意向锁之后，在加这个表锁的时候，就不用去逐行检查这个行锁，直接根据意向锁，以及意向锁的类型来判断当前的表锁是否可以加锁成功。

0：如果直接执行这个 select * from user where id =1； 是不会加任何锁的。 1; 意向共享锁(IS): 由语句 select ... lock in share mode 添加   -- 这个加意向共享锁的同时，也是一个加这个共享读锁的过                                                           程。 也就是说加这个行级锁的读锁，同时会加一把意向共享锁与表锁共享锁(read) 兼容,与表锁排它(write)锁互斥.在执行这个 update ,insert,delete 语句的时候，mysql 会自动为这个语句加上一个行锁（for update）（排他锁，同时也是一个意向排他锁） 
2; 意向排他锁(IX): 由 insert ,update,delete,select ...  for update 添加与表锁共享锁(read)及排它锁(write) 都互斥.   意向锁之间是不会互斥的

6.3 行级锁.

锁的粒度： 从小到大： 分别是： 行锁 --> 间隙锁 --> 临键锁 （最大）

行锁🔒也叫记录锁

也就是说: 间隙锁, 如果你在 select 语句中 添加了 锁: 排它锁 / 共享锁

比如有一张source 表:

事务A:
begin;
select * from source where id > 8 lock in share mode;

我在查询的时候, 加了锁, 并且不是一个等值查询, 是一个范围查询, 那么会向id > 8 的所有数据添加一个 间隙锁🔒

事务A查询结果:

此时执行事务B: 看下结果

事务B:
begin;
insert source VALUES(10,'天南海北','10',10,'哒哒哒',null,null);

可以看到的是, 插入数据是无法插入数据的. 因为在查询的时候给加了 临键锁🔒

innodb 默认是走行级锁， 如果所要查询的条件字段没有加索引的话， 那么会上升为表级锁。

  解释一下: 1. 共享锁: 一个事物A中, 对于select语句 加了共享锁 , 那么另一个事物如果是共享锁那么是可以读取到加了锁的数据的, 但是对于是加了排它锁的语句, update, insert, delete 语句是会直接不允许获取这些数据的, 一直处于一个等状态的.  2. 排它锁: 锁的力度更大. 只要有一个事物对于这块儿的数据,加了锁, 那么别的锁都会互斥, 无论是排它锁还是共享锁. 【注意】 虽然在排他锁是会对其他锁是有互斥的， 但是不开事物的select 语句， 是可以直接查询到数据的。

6.3.1 行锁🔒

所以意思是： 我们执行这个增删改的时候， 会自动加上这个 排他锁，

但是如果我们执行这个普通的 select 语句的时候， 默认是没有加任何🔒的， 如果要加一个共享锁， 我们需要自己来加一个

 select 语句 + lock in share  mode ；   -- 共享锁 

那么可以达到在一个线程对这个数据进行读的时候， 其他线程不可以对该数据进行更改~

6.3.2 间隙锁🔒 + 临键锁🔒

情况1： 索引上的等值查询，唯一索引，且查询的记录不存在的数据， 那么这个临键锁 会优化为这个 间隙锁。

那这个应该是 where id = 5 ； 唯一索引， 和这个 是 select 语句，还是这个 insert，update，delete 语句没有关系吧 ~

总之， 是 唯一索引， 且 不存在这个值， 那么就会给添加这个间隙🔒。

情况2：索引上的等值查询（普通索引），向右遍历到最后一个值不满足查询需求时， 临键锁会退化为这个间隙锁。

比如我现在一个用户表中，有一个age 年龄字段， 我建立的是一个普通索引。 那么我现在搜索这个年龄 = 18 岁的人

情况3：索引上的范围查询， （唯一索引）会访问到不满足条件的第一个值为止

比如查询 id>= 9;

那么会把这个id=9 这行数据以及之后的这些数据都锁起来。

7. 逻辑存储结构

8.1 内存架构

Buffer Pool

Change Buffer

自适应哈希索引

Log Buffer

8.2 磁盘结构

8.4 后台线程

9.事务原理

8.1 redolog 重做日志

这个脏页就是这个修改后的数据 。

发生数据的变更的时候， 会首先把数据的变化提交到这个Redolog Buffer 中。 然后当事务提交的时候，它会把这个Redolog Buffer 中的数据变化直接刷新到磁盘中，持久化的保存到磁盘文件中。 
然后，在后面执行这个脏页刷新的时候，如果出错了，那么会使用这个 redolog file  文件进行一个数据的恢复~  
在每次提交事务的时候，会把这个数据的变更提交到 Redolog Buffer 中，并且刷新到磁盘中， 属于日志追加文件redolog  是日志追加的方式， 是顺序磁盘IO，性能要高于 直接每次把这个事务提交的数据改变直接刷新到这个磁盘中的随机IO如果脏页顺利地刷新到了磁盘中，那么这个redolog file 文件也就不需要了。 每隔一段时间就回去清理redolog 日志文件~  总之：  redolog 的作用就是在将脏页数据刷新到磁盘中出错的时候，进行一个数据的恢复，从而保证一个数据的持久化~   

8.2 undolog 回滚日志

当要进行一个数据的回滚的时候， 只要执行undolog 日志文件的反向操作的日志，便可以使数据恢复到之前的版本。

9. MVCC 基本原理

多版本并发控制 ~

所谓隐藏字段： 就是我们在创建一张表的时候会自动给我们添加上的字段，隐式地添加上 2-3个字段。
前2个字段是默认都会添加的，但是最后一个字段是如果表结构没有指定主键的话，那么会为该表生成一个主键字段。

【注意】

对于这个当前读中， 根据这个不同的隔离级别， 快照读也是不一样的。

在RC 隔离级别下： 每一次 的select 语句都会 生成一个 快照读。 就像是对当前的数据的版本进行了一个保存~

在RR 隔离级别下：是开启事务后的第一个 select 语句 是 开始快照读的地方。

在串行化的隔离级别下： 就相当于只有这个当前读了。

【注意2】

对于这个当前读： 在读取该数据的时候会对数据进行一个加锁的操作~ 保证并发事务不可以修改当前记录， 那么这个相当于也是读取到的这个一个旧的版本的数据了？

至于这个 RR隔离级别下： 就是读取还是读取的是这个旧版本的数据， 但是这个其他事务是可以对当前的记录进行一个修改的操作的。

刚开始插入数据：

DB_TRX_ID : 这个字段最开始插入一条数据，是初始值为1， 自增的

DB_ROLL_PTR ：null ， 没有回滚的指针

查看表空间具体信息字段：

ibd2sdi + 表空间文件名 ~

存在有未提交的事务在使用该记录， 所以undolog 不会被删除 ~

9.1 undolog版本链：

9.2 readview

那总结下来： 就是这个当前事务的 i d 只要是 <= max_trx_id , 就可以访问到该版本的数据。 而且是已经提交过的数据才可以访问到的

当前这条事务记录的id ， 就是这个 DB_TRX_ID , 这个是只有对数据进行了修改的事务，才会有事务id。

【分析】

我们来分析一下事务5的查询的记录到底是啥？

① 首先当前记录的 事务 id 是 4， creator_trx_id = 5， max_trx_id = 5+1=6, min_trx_id = 3 ,

根据上面的 4 个条件进行一一判断，发现无法查询到当前记录的版本， 因为其实我们可以看到在此时， 只有这个事务2对该事务进行了提交， 所以按照这个已提交读的隔离级别或者是 RR 级别下， 我们可以访问到的数据只能是这个 事务2 提交后的数据， 应该是这个

age= 3， name = A30 。

②我们接着按照这个规则进行判断

该记录无法查询到， 那么根据 undolog 版本链规则， 我们可以继续往下走， DB_ROLL_PTR —> OX0003 ,可以看到这个

当前数据记录的事务的id 为 3 ， 发现不符合规则 4 。 同样， 因为事务3 并没有提交， 该条记录是 事务3 提交后的数据。

③ 那么接下再根据 DB_ROLL_PTR —> OX0002 ， 找到对应的版本数据， 可以发现这个 当前记录中事务的id 是 2 ， 是符合这个要求的， 也就是在事务5中的 第一个select 语句 可以查询到的数据。

那么， 同理， 对于在这个事务5中的第2个select 语句 就是这个 在该语句之前提交的 语句了。 对应的因该是事务3提交的数据。

每次 select 语句都会有一个快照读的版本。 这个对应的应该是这个RC 隔离级别下。

**活动事务： **是指在当前的时间结点上， 还有这个对该事务进行读或者写的事务。

那么因为 RR 隔离级别下： 我们要保证的是这个在一次事务中， 两次select 语句要查询到的语句都是一样的 ， 所以这个是 以 本事务中的第一个 select 为 开始时间结点的判断。 当前读是读取到的可能是这个旧数据。 也就是在此期间， 其他事务是可以修改数据的， 但是我只能读取到这个事务开始之前的最新的版本的数据。 但是这个事务开启之后的版本的数据事务读取到的。

如果是这个当前读： 通过加锁的方式， 我们对数据进行了加锁，不让其他事务对当前事务进行修改， 那么其实也可以说， 我们读取到的是这个最新的数据。

来分析一下事务的隔离级别的实现：

【快照读】

① 未提交读：

运维篇

数据库设计三范式

第一范式：

第二范式：

因为仔细看一下， 上面的这张表 其实应该设计为一个多对多的关系的表~   不应该挤到一张表中。 
那么应该设计如下：  应该设计为3张表~ 

多对多怎么设计？

【多对多，三张表，关系表两个外键！！！！！！ 】

第三范式：

在上面的表中， 很明显 ， 班级名称发生了冗余。  一个班级 对应 多个学生。  是一个 一对多 的关系。
应该将其拆分为2张表。 我们需要在多的一方添加一个外键~  口诀：一对多，2张表，多的表加外键！ 

此处有疑问： 外键 和外键约束的关系？

一对一： 外键唯一， 也是分为2张表， 外键设置为唯一

总结：

冲 ！！！！！！