Mysql总结

raja 收录于类别 mysql

2023-09-24 2023-09-24 约 14573 字预计阅读 30 分钟

注意

本文最后更新于 2023-09-24，文中内容可能已过时。

Mysql总结

三大范式

第一范式：原子性

每一列不可再分。

第二范式：完全依赖

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

第三范式：直接依赖

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

存储引擎对比

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-809-boxcnsVmGCz82ioc0lTxXkU6dfb-20230913234912-owqc346.png

语句执行流程

MySQL 执行流程是怎样的？

连接器：建立连接，管理连接、校验用户身份；
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
执行 SQL：执行 SQL 共有三个阶段：
- 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
- 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划；
- 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

分层结构

MySQL 的架构共分为两层：Server 层和存储引擎层，

Server 层负责建立连接、分析和执行 SQL 主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
存储引擎层负责数据的存储和提取。

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnsHfsMt2PaN6pBvSITmPggc-20230913234912-wk7ek5n.png

第一步：连接器

与客户端进行 TCP 三次握手建立连接；
校验客户端的用户名和密码；
读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限；

如何查看 MySQL 服务被多少个客户端连接了？

show processlist

空闲连接会一直占用着吗？

自动断开：MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880 秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。
手动断开：kill connection + id

MySQL 的连接数有限制吗？

最大连接数由 max_connections 参数控制：show variables like 'max_connections';

怎么解决长连接占用内存的问题？

MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。如果长连接累积很多，将导致 MySQL 服务占用内存太大

定期断开长连接
客户端主动重置连接：mysql_reset_connection 重置连接

第二步：查询缓存

客户端向 mysql 发送 SQL 语句

MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。

如果是查询语句，mysql 会优先查询缓存中有没有记录 k-v。

对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空

第三步：解析 SQL

MySQL 会先对 SQL 语句做解析，这个工作交由由解析器来完成

解析器。

词法分析：构建 SQL 语法树，便于后续获取信息。
语法分析：判断是否符合 SQL 语法规则。

第四步：执行 SQL

prepare 阶段，也就是预处理阶段；（预处理器）
- 检查 SQL 查询语句中的表或者字段是否存在；
- 将 select * 中的 * 符号，扩展为表上的所有列；
optimize 阶段，也就是优化阶段；（优化器）表中有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。
execute 阶段，也就是执行阶段；（执行器）
- 主键索引查询存储引擎负责定位符合条件的第一条记录并返回给执行器，执行器判断符合查询条件就返回给客户端。
- 全表扫描 Server 层每从存储引擎读到一条记录就会发送给客户端，最后再显示。接着 Server 向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器（Server 层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；
- 索引下推索引下推能够减少二级索引在查询时的回表操作，提高查询的效率 select * from t_user where age > 20 and reward = 100000; age 和 reward 存在联合索引
  1. 不使用索引下推： age 的范围查询导致索引失效，存储引擎查询到一条数据就需要回表查询所有数据返回给 Server 来判断 reward 是否满足条件
  2. 使用索引下推：存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward）的条件（reward 是否等于 100000）是否成立。 如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。

执行 `update` 语句，发生了什么？

1

UPDATE t_user SET name = 'xiaolin' WHERE id = 1;

查询语句的那一套流程，更新语句也是同样会走一遍：

客户端先通过连接器建立连接，连接器自会判断用户身份；
因为这是一条 update 语句，所以不需要经过查询缓存，但是表上有更新语句，是会把整个表的查询缓存清空的，所以说查询缓存很鸡肋，在 MySQL 8.0 就被移除这个功能了；
解析器会通过词法分析识别出关键字 update，表名等等，构建出语法树，接着还会做语法分析，判断输入的语句是否符合 MySQL 语法；
预处理器会判断表和字段是否存在；
优化器确定执行计划，因为 where 条件中的 id 是主键索引，所以决定要使用 id 这个索引；
执行器负责具体执行，找到这一行，然后更新。当优化器分析出成本最小的执行计划后，执行器就按照执行计划开始进行更新操作。具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下：

执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：
- 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
- 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
- 如果一样的话就不进行后续更新流程；
- 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
开启事务， InnoDB 在更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。
InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘 I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。
至此，一条记录更新完了。
在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
事务提交

索引

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnTVm3DCsseD5ktf6dCmd1ib-20230913234912-vyxo9s1.png

索引的分类

按「数据结构」分类：B+tree 索引、~~Hash~~ 索引、Full-text 索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

按数据结构分类

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnrlVLNVfVR098Dg3WYia7Th-20230913234912-p82x4mn.png

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为聚簇索引的索引键：（优先级从高到低）。

主键；
第一个不包含 NULL 值的唯一列；
自动生成一个隐式自增 id 列作为聚簇索引的索引键；

其它索引都属于二级索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

按物理存储分类

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

1
2


CREATE TABLE table_name  (....PRIMARY KEY (index_column_1) USING BTREE
);

唯一索引

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

1
2


CREATE TABLE table_name  (....UNIQUE KEY(index_column_1,index_column_2,...) 
);

建表后，如果要创建唯一索引，可以使用这面这条命令：

1
2


CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

1
2


CREATE TABLE table_name  (....INDEX(index_column_1,index_column_2,...) 
);

1
2


CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

1
2
3


CREATE TABLE table_name(
    column_list,INDEX(column_name(length))
);

建表后，如果要创建前缀索引，可以使用这面这条命令：

1
2


CREATE INDEX index_name
ON table_name(column_name(length));

按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

建立在单列上的索引称为单列索引，比如主键索引；
建立在多列上的索引称为联合索引；

联合索引

通过将多个字段组合成一个索引，该索引就被称为联合索引。

因此，使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。

最左前缀因为：前者相同下局部有序，但是全局无序

联合索引范围查询

范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

Q1: select * from t_table where a > 1 and b = 2，联合索引（a， b）哪一个字段用到了联合索引的 B+Tree？

由于联合索引（二级索引）是先按照 a 字段的值排序的，所以符合 a > 1 条件的二级索引记录肯定是相邻，但是在符合 a > 1 条件的二级索引记录的范围里，b 字段的值是无序的。

因此：Q1 这条查询语句只有 a 字段用到了联合索引进行索引查询，而 b 字段并没有使用到联合索引。

Q2: select * from t_table where a >= 1 and b = 2，联合索引（a， b）哪一个字段用到了联合索引的 B+Tree？

虽然在符合 a>=1 条件的二级索引记录的范围里，b 字段的值是「无序」的，但是对于符合 a = 1 的二级索引记录的范围里，b 字段的值是「有序」的

在确定需要扫描的二级索引的范围时，当二级索引记录的 a 字段值为 1 时，可以通过 b = 2 条件减少需要扫描的二级索引记录范围，也就是说，从符合 a = 1 and b = 2 条件的第一条记录开始扫描，而不需要从第一个 a 字段值为 1 的记录开始扫描。

Q2 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

Q3: SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2，联合索引（a， b）哪一个字段用到了联合索引的 B+Tree？

在 MySQL 中，BETWEEN 包含了 value1 和 value2 边界值，类似于 >= and =<。

因此 Q3 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

Q4: SELECT * FROM t_user WHERE name like 'j%' and age = 22，联合索引（name， age）哪一个字段用到了联合索引的 B+Tree？

由于联合索引（二级索引）是先按照 name 字段的值排序的，所以前缀为 ‘j’ 的 name 字段的二级索引记录都是相邻的，于是在进行索引扫描的时候，可以定位到符合前缀为 ‘j’ 的 name 字段的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录的 name 前缀不为 ‘j’ 为止。

于是，在确定需要扫描的二级索引的范围时，当二级索引记录的 name 字段值为 ‘j’ 时，可以通过 age = 22 条件减少需要扫描的二级索引记录范围（age 字段可以利用联合索引进行索引查询的意思）。也就是说，从符合 name = 'j' and age = 22 条件的第一条记录时开始扫描。

所以，Q4 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询

索引下推

而 MySQL 5.6 引入的索引下推优化，可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。 当你的查询语句的执行计划里，出现了 Extra 为 Using index condition，那么说明使用了索引下推的优化。

联合索引实践

这里出一个题目，针对针对下面这条 SQL，你怎么通过索引来提高查询效率呢？

1

select * from order where status = 1 order by create_time asc

给 status 和 create_time 列建立一个联合索引，因为这样可以避免 MySQL 数据库发生文件排序。

因为在查询时，如果只用到 status 的索引，但是这条语句还要对 create_time 排序，这时就要用文件排序 filesort，也就是在 SQL 执行计划中，Extra 列会出现 Using filesort。

所以，要利用索引的有序性，在 status 和 create_time 列建立联合索引，这样根据 status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率。

什么时候需要 / 不需要创建索引？

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

需要占用物理空间，数量越大，占用空间越大；
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

什么时候适用索引？

字段有唯一性限制的，比如商品编码；
经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
表数据太少的时候，不需要创建索引；
经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree 的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

有什么优化索引的方法？

这里说一下几种常见优化索引的方法：

前缀索引优化；
覆盖索引优化；
主键索引最好是自增的；
防止索引失效；

前缀索引优化

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

局限性，例如：

order by 就无法使用前缀索引；
无法把前缀索引用作覆盖索引；

覆盖索引优化

在 B+ 索引的叶子节点中可以找到查询的全部字段，避免回表，减少了大量的 I/O 操作。

主键索引最好是自增的

追加，不移动数据：如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
移动数据，页分裂：如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
主键字段不宜太大，使得二级索引叶子节点小。

索引最好设置为 NOT NULL

优化器选择难：NULL 字段计算比较复杂，count 会省略值为 NULL 的行。
具有存储空间

防止索引失效

左模糊 或者 左右模糊 匹配注意 %x 在查询索引字段时，如果不用回表，可以扫描二级索引树。
查询是对索引列做了计算，函数，类型转换等操作
联合索引遵循最左前缀
where 子句中如果 or 前是索引列但 or 后不是，则失效

从页角度看待 B+ 树索引

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnkLnyC2Bo5xlalUUIvKadCh-20230913234912-3t1p3hr.png

InnoDB 的数据是按「数据页」为单位来读写的，默认数据页大小为 16 KB。每个数据页之间通过双向链表的形式组织起来，物理上不连续，但是逻辑上连续。

数据页内包含用户记录，每个记录之间用单向链表的方式组织起来，为了加快在数据页内高效查询记录，设计了一个页目录，页目录存储各个槽（分组），且主键值是有序的，于是可以通过二分查找法的方式进行检索从而提高效率。

聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点；
二级索引的叶子节点存放的是主键值，而不是实际数据

在使用二级索引进行查找数据时，如果查询的数据能在二级索引找到，那么就是「索引覆盖」操作，如果查询的数据不在二级索引里，就需要先在二级索引找到主键值，需要去聚簇索引中获得数据行，这个过程就叫作「回表」。

为什么采用 B+ 树作为索引？

尽可能少的磁盘的 I/O 操作；
要能高效地查询某一个记录，也要能高效地执行范围查找；

二分查找

通过数组存储，每次查询范围减半。

缺点：插入删除元素性能差

二分查找树

二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点。

优点：插入删除效率高

缺点：

容易退化成单链表形式
树层数太高，IO 操作太多

平衡二叉树

条件约束：每个节点的左子树和右子树的高度差不能超过 1。

优点：可以自平衡。

缺点：树层数太高，IO 操作太多（应该使用 N 叉树）

B 树

当树的节点越多的时候，并且树的分叉数 M 越大的时候，M 叉树的高度会远小于二叉树的高度。

B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，所以 B 树就是一个多叉树。

优点：较为矮平，减少磁盘 IO。

缺点：

B 树的每个节点都包含数据，容易多读很多无用数据。
范围查询需要使用中序遍历，IO 操作多

B+ 树

B+ 树就是对 B 树做了一个升级

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnl6GwT4r1WSkTMfGZdD8a5g-20230913234912-n7mqtmw.png

B+ 树与 B 树差异的点，主要是以下这几点：

叶子节点放数据（包括索引），非叶子节点放索引
所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；
非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。
非叶子节点中有多少个子节点，就有多少个索引；

单点查询 B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+ 树的非叶子节点可以存放更多的索引，**因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 ****I/O **次数会更少。
插入和删除效率
- B+ 树有大量的冗余节点，这样使得删除一个节点的时候，可以直接从叶子节点中删除，甚至可以不动非叶子节点，这样删除非常快，
- B 树则不同，B 树没有冗余节点，删除节点的时候非常复杂，比如删除根节点中的数据，可能涉及复杂的树的变形。
范围查询 B+ 树叶子节点数据之间通过链表按顺序连接，支持范围查询。而 B 树只能通过树的遍历来完成范围查询

因此，存在大量范围检索的场景，适合使用 B+ 树，比如数据库。而对于大量的单个索引查询的场景，可以考虑 B 树，比如 nosql 的 MongoDB。

count（*）和 count（1）有什么区别？哪个性能最好？

count（*）和 count（1）？

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnGhA9tA622UzqJo3f8g13Ae-20230913234912-5sbksxk.png

count(1) InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值。但是，如果表里有二级索引时，InnoDB 循环遍历的对象就二级索引了。
count(*) == count(0) == count(1)
count(主键)：走主键索引
count(字段)：全表扫描

事务

事务是由 MySQL 的引擎来实现的，我们常见的 InnoDB 引擎它是支持事务的。

MyISAM 引擎就不支持事务

事务隔离级别

事务特性

原子性：一个事务要么完成，要么全部不执行。（undo log 进行回滚）
一致性：事务前后操作满足数据的完整性约束，数据库保持一致。（其他保证）
隔离性：并发事务之间数据隔离。（MVCC + 锁）
持久性：事务结束后数据持久化，可以崩溃恢复。( redolog)

事务问题

脏读（读提交 - Read View - 每次都申请）

一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。

不可重复读（可重复读 - Read View - 只申请一次）

一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。

幻读（串行化 - 读写锁）

在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。

如何解决幻读？

快照读：MVCC，插入语句不会被看到。
当前读：next-key lock（记录锁+间隙锁），因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

ReadView 如何实现 MVCC

InnoDB 中主族索引中每个记录除了记录数据还有 修改者的事务 ID和 undolog 版本链。
undolog里面记录修改前数据和修改事务的 ID。
比如 可重复读会在事务开始时计算一个 ReadView，里面有未提交事务最小 ID，分配给下一个事务的 ID，以及未提交事务的 ID 列表。 最小未提交事务 ID之前的事务一定可见，分配给下一个事务的 ID之后一定不可见，中间部分有提交和未提交的事务，通过是否在 未提交事务 ID 列表判断是否可见
读重复 则在执行每一条语句开始时计算一遍 ReadView
如果该记录看不见则通过undolog进行计算之前的可见值。

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnU3vVeBUheh9q48QwFo1hGe-20230913234912-uiztfes.png

Read View 有四个重要的字段：

m_ids ：启动未提交的事务 IDs
min_trx_id ：未提交事务的最小 ID
max_trx_id ：下一个应该分配的事务 ID
creator_trx_id ：当前事务 ID

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnbu64EyckLj8HNI7o9E7Ugd-20230913234912-t87on25.jpg

幻读被解决了吗？

幻读：两次查询时数据量不同。（防止插入和删除）

MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。

快照读解决了幻读
当前读只能尽量避免幻读，无法彻底解决。

Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了间隙锁（间隙锁 + 记录锁）。

但是幻读并没有被完全解决！

A 事务查询不存在的一个主键 X 的数据
B 事务插入一个主键 X 的数据，然后提交
A 事务更新这个数据，然后再查询就可以查询出来

避免？

直接在第一次读的时候使用快照读，锁住之后的间隙。

锁

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcn2nc4OKtCjqoEEQrkQPzW7e-20230913234912-tnyqopw.png

全局锁

整个数据库只读：flush tables with read lock

解锁：unlock tables

应用场景：全库逻辑备份

优化：可重复读下开启事务再备份，利用 MVCC，可以备份同时进行更新也不影响。

表级锁

表锁：限制之后对表的读写操作
- 读锁：lock tables t_student read;
- 写锁：unlock tables 注意：表锁同样会限制自身事务的读写操作
元数据锁（MDL）：防止 CRUD 时操作表结构
- 对表进行 CRUD 时加MDL 读锁
- 对表结构变更时加MDL 写锁
意向锁：为了告知表锁此表内事务读写状态，不然得遍历获知。
- 给记录加读锁时会给表加读意向锁
- 给记录加写锁时会给表加写意向锁（CUD 操作）
AUTO-INC 锁：在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

1
2
3


//对读取的记录加共享锁
select ... lock in share mode;//对读取的记录加独占锁
select ... for update;

Record Lock：记录锁，也就是仅仅把一条记录锁上；分为读锁和写锁。
Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身； 只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。间隙锁之间是兼容的。
Next-Key Lock: Record Lock +Gap Lock . 锁定一个范围，并且锁定记录本身。
插入意向锁：事务插入记录时如果存在间隙锁，则会发生阻塞，在此期间会生成一个插入意向锁。

如何加锁

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnapP3JPCJEY809E9MVKDWze-20230913234912-zup9z1x.png

唯一索引等值查询

select id from t where id = 10 for update

查询记录存在，临键锁退化为记录锁。[10]
**不存在退化为间隙锁。**锁上一条和下一条的区间。(8,16)

唯一索引范围查询

select * from t_test where id>=8 and id<9 for update;

有 = 则先进行唯一索引等值查询：存在加记录锁，不存在加间隙锁[8]
然后进行范围查询，会给遍历到的满足条件的加记录锁，找到第一个不满足条件的退回加间隙锁。 (8,16)

加锁：[8,16)

非唯一索引等值查询

select id from t where b = 8 for update

存在加临键锁和间隙锁。 临键锁：(4,8] 继续向下遍历到不满足条件的加间隙锁：(8,16)
不存在加间隙锁。 select * from t where b = 10 for update 临键锁退化为间隙锁 ：(8,16)

非唯一索引范围查询

非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于普通索引范围查询，next-key lock 不会退化为间隙锁和记录锁。

select * from t where b>=8 and b<9 for update

先进行非唯一索引等值查询，加临建锁：(4,8]
然后范围查询找到第一个不满足条件的记录加临键锁：(8,16]

非索引

会走全表扫描，锁全表

日志

undo log（回滚日志）：Innodb 主要用于事务回滚和 MVCC。
redo log（重做日志）：Innodb 实现了事务中的持久性，主要用于掉电等故障恢复；
binlog （归档日志）：Server 层主要用于数据备份和主从复制；

undo log：记录更新前的值，实现原子性和 MVCC

实现事务回滚，保障事务的原子性
实现 MVCC（多版本并发控制）关键因素之一

记录更新前的数据到 undolog 日志，commit 前出错时通过 undolog 回滚

字段

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务 id：

通过 trx_id 可以知道该记录是被哪个事务修改的；
通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；

持久化

undolog 会记录到 buffer pool，然后写入 redo log

Buffer Pool

缓存从磁盘读入的数据。（以 page 为单位的连续内存空间）

读数据，如果缓存有就直接读取。
写数据，优先写入缓存，标记为脏，定期刷盘，同时记录 redolog。

InnoDB 会把存储的数据划分为若干个 page，以 page 作为磁盘和内存交互的基本单位，一个 page 的默认大小为 16KB**。**

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnYef9TDOWRsCXhhyha3B6if-20230913234912-wogsf37.png

undolog****会记录进入缓存页。
查询一个记录时会读入一整个页的数据，然后通过页目录定位。

如何管理 Buffer Pool

InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括

**「缓存页的表空间、页号、缓存页地址、链表节点」**等等。

管理空闲页

为了能够快速找到空闲的缓存页，可以使用链表结构，将空闲缓存页的控制块作为链表的节点**，这个链表称为 Free 链表（空闲链表）**。

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcn8H54LzSEH6syv4xGhVSXRf-20230913234912-ekds20a.png

有了 Free 链表后，每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free 链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从 Free 链表中移除。

如何管理脏页

为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页。

提高缓存命中率

容量有限，采用最近最少访问来淘汰。当空间不够了，就淘汰最久没被使用的**（LRU）**节点。

Redo log：记录更新后的值，崩溃恢复

为了防止断电后，脏数据未落盘。

文件格式

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了 AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

流程

更新记录时，先更新到缓存，标记为脏页，同时记录对应页的修改到 redolog
Innodb 定期刷新脏缓存页（WAL）
事务提交时，先将 redo log 持久化到磁盘即可。

写入方式

redolog 顺序写入磁盘，磁盘写入则是随机写

实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能

刷盘方式

redolog 也存在自己的缓存，生成一条 redolog 就写入 redolog buffer(16MB)

之后三种方式刷盘：

Mysql 正常关闭
redo log 缓存空间超过一半
每隔一秒刷新一次。
事务提交时进行持久化，innodb_flush_log_at_trx_commit 参数控制。
1. 0：不主动刷盘
2. 1：提交时进行刷盘（默认）
3. 2：写入 OS 的页缓存（不断电就行）

`redo log` 满了：刷盘循环写

redo log 存在一个重做日志组：两个 redo log 循环写日志文件组成。

写完一圈回到开头写。
满了会阻塞 Mysql，刷入脏页后继续写入。

Binlog：主从备份恢复

文件格式

STATEMENT：逻辑日志修改数据的 SQL 会被记录下来，主从备份时进行恢复。但是一些时间类函数会导致数据不一致问题。
ROW：最终值日志记录行数据最终修改结果，容易记录太多更新的行数据。
MIXED：自动切换前两者模式

写入 + 刷盘方式

事务执行时，先把日志写入到 binlog cache（每个线程都有），事务提交时再把缓存写入 OS，然后清空缓存。

sync_binlog= 0，只写入 OS 缓存。
sync_binlog= 1 的时候，表示每次提交事务都刷盘；
sync_binlog = N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

虽然每个线程有自己 binlog cache，但是最终都写到同一个 binlog 文件

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnkyH18VnnxAmNpVcjBQp0nd-20230913234912-8bgdyg1.png

主从复制

通过二进制 binlog 进行异步主从复制。

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnRa0HHaiJ8q2atKMugr66wb-20230913234912-eerb90y.png

MySQL 集群的主从复制过程梳理成 3 个阶段：

写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
回放 Binlog：回放 binlog，并更新存储引擎中的数据。

主库写，从库读

从库太多会怎样？

binlog 备份异步资源开销大

主从模型：

同步复制：等待所有从库响应。
异步复制（默认模型）：不等待响应就返回。
半同步复制：至少有一个从库响应即可。

`redo log` 和 `binlog` 区别

	`binlog`	`redo log`
实现层	`server` 层	`innodb` 引擎
文件格式	`STATEMENT`, `ROW`, `MIXED`	物理日志，记录的是在某个数据页做了什么修改。
写入方式	追加写，全量日志	追加写，写满循环写入
用途	备份恢复，主从复制	掉电恢复

两阶段提交：先刷 `redolog` 再刷 `binlog`

为了保证 redo log 和 binlog 日志的数据一致性。

事务的提交阶段分为两个阶段：

准备（Prepare）将事务 ID 写入 redo log，设置事务状态为 prepare，redo log 刷盘
提交（Commit）将事务 ID 写入 binlog，设置 redo log 状态为 commit，binlog 刷盘

崩溃恢复：使用 redo log 发现有标记的事务 ID 则查看 binlog

binlog 中有事务 ID，则提交事务
binlog 中没有事务 ID，则回滚事务

优化

SQL 优化

Select 指明字段
1. SELECT* 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；
2. 增加使用覆盖索引的可能性；
3. 当表结构发生改变时，返回数据也需要更新。所以要求直接在 select 后面接上字段名。
当只需要一条数据的时候，使用 limit 1
对 order by 或 group by（group by NULL避免排序）字段采用索引
区分 in 和 exists、not in 和 not exists：in 适合 外表 大，exists 适合 内表 大

select * from 表A where id in (select id from 表B)

==

select * from 表A where exists(select * from 表B where 表B.id=表A.id)

分段查询
必要时可以使用 force index 来强制查询走某个索引
避免索引失效：
1. 模糊 或者 左右模糊 匹配注意 %x 在查询索引字段时，如果不用回表，可以扫描二级索引树。
2. 查询是对索引列做了计算，函数，类型转换等操作
3. 联合索引遵循最左前缀
4. where 子句中如果 or 前是索引列但 or 后不是，则失效
优化 insert
- 主键顺序插入
- 关闭唯一性校验
- 手动提交事务
- 一次性插入多条数据
优化嵌套查询：使用连接查询替换嵌套子查询
优化分页查询：select * from t limit 200000,10
- 在索引上查询出 id，然后再关联回原表查询其他内容 select * from t,(select id from t limit 200000,10) t1 where t.id = t1.id
- 将 limit 查询转换为某个位置的查询**（必须主键自增）** select * from t where id > 2000000 limit 10

排查死锁

结束死锁线程

查看死锁线程 show processlist 中 sleep 状态然后 kill 了
查看正在进行中的事务 SELECT * FROM information_schema.INNODB_TRX
查看正在锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

SQL 优化步骤

开启慢查询日志，设置超过几秒为慢 SQL，抓取慢 SQL
通过 explain 对慢 SQL 分析（重点）
show profile 查询 SQL 在 Mysql 服务器里的执行细节和生命周期情况（重点）
对数据库服务器的参数调优

定位低效率执行 SQL

慢查询日志

开启日志分析

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


（1）设置开启：SET GLOBAL slow_query_log = 1;　　　#默认未开启，开启会影响性能，mysql重启会失效
（2）查看是否开启：SHOW VARIABLES LIKE '%slow_query_log%';
（3）设置阈值：SET GLOBAL long_query_time=3;
（4）查看阈值：SHOW 【GLOBAL】 VARIABLES LIKE 'long_query_time%';　　#重连或新开一个会话才能看到修改值
（5）通过修改配置文件my.cnf永久生效，在[mysqld]下配置：
　　[mysqld]
　　slow_query_log = 1;　　#开启
　　slow_query_log_file=/var/lib/mysql/atguigu-slow.log　　　#慢日志地址，缺省文件名host_name-slow.log
　　long_query_time=3;　　  #运行时间超过该值的SQL会被记录，默认值>10
　　log_output=FILE

获取慢 SQL 信息

查看慢查询日志记录数：SHOW GLOBAL STATUS LIKE '%Slow_queries%';
查看日志 ：cat atguigu-slow.log

查看当前线程状态

show processlist

可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

https://raw.githubusercontent.com/0RAJA/img/main/20230924134659-810-boxcnPov0yMcwrqKXZcLsTcIvvf-20230913234912-znedkmp.png

id 列，用户登录 mysql 时，系统分配的"connection_id"，可以使用函数 connection_id（）查看
user 列，显示当前用户。如果不是 root，这个命令就只显示用户权限范围的 sql 语句
host 列，显示这个语句是从哪个 ip 的哪个端口上发的，可以用来跟踪出现问题语句的用户
db 列，显示这个进程目前连接的是哪个数据库
command 列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
time 列，显示这个状态持续的时间，单位是秒
state 列，显示使用当前连接的 sql 语句的状态，很重要的列。state 描述的是语句执行中的某一个状态。一个 sql 语句，以查询为例，可能需要经过 copying to tmp table、sorting result、sending data 等状态才可以完成
info 列，显示这个 sql 语句，是判断问题语句的一个重要依据

`explain` 分析执行计划

https://segmentfault.com/a/1190000008131735

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

explain

对于执行计划，参数有：

select_type 查询类型
- SIMPLE，表示此查询不包含 UNION 查询或子查询
- PRIMARY，表示此查询是最外层的查询
- UNION，表示此查询是 UNION 的第二或随后的查询
- DEPENDENT UNION， UNION 中的第二个或后面的查询语句，取决于外面的查询
- UNION RESULT， UNION 的结果
- SUBQUERY，子查询中的第一个 SELECT
- DEPENDENT SUBQUERY: 子查询中的第一个 SELECT，取决于外面的查询。即子查询依赖于外层查询的结果。
table 表示查询涉及的表或衍生表
possible_keys 字段表示可能用到的索引注意，即使有些索引在 possible_keys 中出现，但是并不表示此索引会真正地被 MySQL 使用到。 MySQL 在查询时具体使用了哪些索引，由 key 字段决定。
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度表示查询优化器使用了索引的字节数。这个字段可以评估组合索引是否完全被使用，或只有最左部分字段被使用到。 key_len 的计算规则如下：
- 字符串
  - char（n）: n 字节长度
  - varchar（n）: 如果是 utf8 编码，则是 3 n + 2 字节；如果是 utf8mb4 编码，则是 4 n + 2 字节。
- 数值类型：
  - TINYINT: 1 字节
  - SMALLINT: 2 字节
  - MEDIUMINT: 3 字节
  - INT: 4 字节
  - BIGINT: 8 字节
- 时间类型
  - DATE: 3 字节
  - TIMESTAMP: 4 字节
  - DATETIME: 8 字节
- 字段属性： NULL 属性占用一个字节。如果一个字段是 NOT NULL 的，则没有此属性。
rows 表示扫描的数据行数。 rows 也是一个重要的字段。 MySQL 查询优化器根据统计信息，估算 SQL 要查找到结果集需要扫描读取的数据行数。这个值非常直观显示 SQL 的效率好坏，原则上 rows 越少越好。
type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：
- All（全表扫描）；
- index（全索引扫描）；和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引，而不扫描数据。 index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到，而不需要扫描数据。当是这种情况时， Extra 字段会显示 Using index。
- range（索引范围扫描）；这个连接类型使用索引返回一个范围中的行，这个类型通常出现在 **=， <>， >， >=， <， <=， IS NULL， <=>， BETWEEN， IN（）**操作中当 type 是 range 时，那么 EXPLAIN 输出的 ref 字段为 NULL，并且 key_len 字段是此次查询中使用到的索引的最长的那个。
- ref（非唯一索引扫描）； ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。 这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
- eq_ref（唯一索引扫描）； eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。 比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。
- const（结果只有一条的主键或唯一索引扫描）。 const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。
- system（系统表，即没有记录或仅有一行记录的表）
extra
- Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候，这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
- Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
- Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

‍

目录

Mysql总结

Mysql总结

Mysql总结

三大范式

第一范式：原子性

第二范式：完全依赖

第三范式：直接依赖

存储引擎对比

语句执行流程

MySQL 执行流程是怎样的？

分层结构

第一步：连接器

第二步：查询缓存

第三步：解析 SQL

第四步：执行 SQL

执行 update 语句，发生了什么？

索引

索引的分类

按数据结构分类

按物理存储分类

按字段特性分类

主键索引

唯一索引

普通索引

前缀索引

按字段个数分类

联合索引

联合索引范围查询

索引下推

联合索引实践

什么时候需要 / 不需要创建索引？

什么时候适用索引？

什么时候不需要创建索引？

有什么优化索引的方法？

前缀索引优化

覆盖索引优化

主键索引最好是自增的

索引最好设置为 NOT NULL

防止索引失效

从页角度看待 B+ 树索引

为什么采用 B+ 树作为索引？

二分查找

二分查找树

平衡二叉树

B 树

B+ 树

count（*） 和 count（1）？

事务

事务隔离级别

事务特性

事务问题

脏读（读提交 - Read View - 每次都申请）

不可重复读（可重复读 - Read View - 只申请一次）

幻读（串行化 - 读写锁）

如何解决幻读？

ReadView 如何实现 MVCC

幻读被解决了吗？

锁

全局锁

表级锁

行级锁

如何加锁

唯一索引等值查询

唯一索引范围查询

非唯一索引等值查询

非唯一索引范围查询

非索引

日志

undo log：记录更新前的值，实现原子性和 MVCC

字段

持久化

Buffer Pool

如何管理 Buffer Pool

管理空闲页

如何管理脏页

提高缓存命中率

Redo log：记录更新后的值，崩溃恢复

文件格式

流程

执行 `update` 语句，发生了什么？

count（*）和 count（1）？

`redo log` 满了：刷盘循环写

`redo log` 和 `binlog` 区别

两阶段提交：先刷 `redolog` 再刷 `binlog`

`explain` 分析执行计划