MySQL高级篇-康师傅-包含索引-调优-范式-事务和锁-MVCC-非常详细笔记

最新推荐文章于 2024-07-18 11:24:27 发布

Gogoinggoing

最新推荐文章于 2024-07-18 11:24:27 发布

阅读量458

点赞数 2

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/Gogoinggoing/article/details/130068762

版权

博客链接🔗 – 学习笔记

基础

#方式1：也是执行顺序
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

执行步骤

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。
当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段。
首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2 。
当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段，得到虚拟表 vt6 。
最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果，对应的是虚拟表 vt7 。
当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

基础select

别名：空行 或 as
DISTINCT 去重
空值：null 
着重号：`order`
显示表结构：desc employees; 

比较运算符：
	= ：
	1 = '1'  字符串会被转为整数
	都是字符串会比较ANSI编码
	!=
    ISNULL
    IS Not NULL
    BETWEEN x AND y
    IN
    NOT IN
    LIKE  % _
	RLIKE 正则表达式
逻辑运算符
	NOT 或 !
	AND 或 &&
	OR 或 ||
	XOR
	
排序
	ORDER BY x ASC(DESC), y ASC  多列排序在x相同才比y
分页
	LIMIT [位置偏移量,] 行数     LIMIT 20,10; 
UNION
	连接两次查询

连接
	SELECT emp.employee_id, dep.department_name
    FROM employee emp, department dep
    WHERE emp.`department_id` = dep.`department_id`;

	JOIN / INNER JOIN   
    LEFT JOIN
    RIGHT JOIN
    FULL JOIN
    最后加上
    	ON 条件
    	USING 名称要对应并且值相等
    	
    	
    还有连接方式是直接选出来后where =条件连接，inner join（inner join即join）和=等号结果一样，但实现原理完全不同，join是基于hashtable连接比较，而=直接就是取笛卡尔集再过滤，所以后者效率低，是O(N^2)，前者是O(LogN)

聚合函数
	AVG()
	SUM()
	MAX()
	MIN()
	count(*) count(1) count(列名)
		MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。 
		Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。
	
GROUP BY 分组
	SELECT的字段必须声明在GROUP BY或者聚合函数
	HAVING 过滤分组
	WITH ROLLUP 对统计出来的结果再求和，多一行

高级

~文件目录

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9k7qC73n-1681129045819)(null)]

Mysql5.7：ibd默认96KB，6个页
Mysql8.0：ibd默认112KB，结合了frm

~逻辑架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RxgmS1Bj-1681129039279)(null)]

连接层TCP握手建立连接，确认账号密码。通过线程池分配线程
服务层生成解析树，优化器生成执行计划完成优化选取-投影-连接
引擎层插件式存储引擎层，负责数据的提取和存储 show engines；

缓存

缓存必须一模一样，命中率低；更新后还可能失效

show global variables like "%query_cache_type%"; show status like '%Qcache%';
解析器

词法分析识别关键字

语法分析查看是否满足mysql语法，然后生成语法树

优化器

找到这其中最好的执行计划。

比如：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联 (join) 的时候，决定各个表的连接顺序，还有表达式简化、子查询转为连接、外连接转为内连接等。

select * from test1 join test2 using(ID)
where test1.name='zhangwei' and test2.name='mysql高级课程';

方案1：可以先从表 test1 里面取出 name='zhangwei'的记录的 ID 值，再根据 ID 值关联到表 test2，再判
断 test2 里面 name的值是否等于 'mysql高级课程'。

方案2：可以先从表 test2 里面取出 name='mysql高级课程' 的记录的 ID 值，再根据 ID 值关联到 test1，
再判断 test1 里面 name的值是否等于 zhangwei。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化
器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。
如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等。后面讲到索引我们再谈。

执行器

~存储引擎

①引擎

show engines;
show variables like '%storage_engine%' 或 SELECT @@default_storage_engine;
SET DEFAULT_STORAGE_ENGINE=MyISAM;   修改 my.cnf 文件：default-storage-engine=MyISAM

不同的表可以设置不同的存储引擎

创建
CREATE TABLE 表名(
建表语句;
) ENGINE = 存储引擎名称;
修改
ALTER TABLE 表名 ENGINE = 存储引擎名称;

②引擎介绍

5.5之后，默认InnoDB

1 InnoDB 引擎：具备外键支持功能的事务存储引擎

事务型引擎。确保事务的完整提交(Commit)和回滚(Rollback)。
行锁，更新一条数据只锁定一行
更新和删除效率高
外键
处理效率差一些，对内存要求(索引即数据，索引和数据存储在一起)

2 MyISAM 引擎：主要的非事务处理存储引擎

读速度快，针对select insert
count(*)等数据有额外存储

3 Archive 引擎：用于数据存档

只有插入和查询，适合存储大量独立历史记录
数据压缩，比MyISAM小75&

4 Blackhole 引擎：丢弃写操作，读操作会返回空内容

5 CSV 引擎：存储数据时，以逗号分隔各个数据项

6 Memory 引擎：置于内存的表

索引数据结构(B+) InnoDB数据结构设计原则性能分析工具优化数据库设计

~索引的数据结构

① 索引介绍

查找一个数据：遍历索引二叉树(保存value, 地址) 减少磁盘IO

定义：为了高效获取数据的数据结构

在存储引擎中实现，不同引擎可以不一样结构

优点：

类似大学图书馆建书目索引，提高数据检索的效率，降低 数据库的IO成本 ，这也是创建索引最主要的原因。
通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性 。
在实现数据的参考完整性方面，可以 加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时间 ，降低了CPU的消耗。‘

缺点：

创建和维护需要时间
索引需要磁盘空间
提高查询速度但降低更新速度

频繁更新，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

② InnoDB中的索引

普通查找

SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

数据以页的形式存储，一页默认为16KB，查找时按照页逐步加载到内存

数据在一页中（记录物理不连续，单链表，通过页目录实现二分）
- 以主键搜索条件：主键通常为递增的 -> 在页目录二分定位对应槽，然后对槽内遍历
- 其他列：每一条记录为单链表连接(逻辑上连续)，依次遍历
很多页（页间双向链表连接）
1. 首先遍历页。没有索引无法快速定位
2. 再查找相应记录

索引雏形

以ROW_FORMAT = Compact 行格式创建表，表的每一条记录处理基本信息还有是否最大下一条地址其他等，以下一条地址串联记录。

为了针对主键设计索引，需要实现主键递增（主键不是自增的有个隐藏主键）

页内是递增的，单向链表
页之间主键也是递增的，双向链表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0Kh4An2P-1681129045204)(null)]

如果具体查找某一个主键还是得遍历全部页，然后页内二分，因此加一层。
加一个目录（目录可以连续存放）保存最小值，两次查找

迭代1

将目录转为页，**实现快速定位数据页。**目录也是页，用record_type=1.通过页目录实现二分

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9tGt5MtW-1681129039729)(null)]

迭代2

一个目录页放不下，多个目录页。具体哪一个目录页要遍历，下面优化

迭代3

再往上迭代套娃，实现快速定位是哪个目录页。一个页为一次IO。总共3次IO

B+TreeB+Tree

上面的结构就是3层的B+树，可以迭代任意层，但最多不超过4层（最多4次IO）

假设存放记录的页可以100条，目录项的页为1000（页大小16KB，一条记录包含指针8KB主键8KB）条：

如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。
如果B+树有2层，最多能存放 1000×100=10,0000 条记录。
如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。
如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。相当多的记录！
一般2-4层，根节点常驻内存，所以1-3次磁盘操作

页内数据通过链表连接，使用页目录实现二分法定位

同行页间通过双向链表连接

常见索引

1. 聚簇索引

基本的数据存储方式（叶子节点存放全部记录）。 索引即数据，数据即索引 .ibd文件

在创建时自动构建出来–B+树。

术语"聚簇"表示当前数据行和相邻的键值聚簇的存储在一起，只有一种存储方式所以聚簇索引只有一个

数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的 排序查找 和 范围查找 速度非常快

插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式(~~UUID MD5 HASH~~)，否则将会出现页分裂
更新主键的代价很高 ，一般定义主键为不可更新
二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据

2. 二级索引（辅助索引、非聚簇索引）

想以别的列作为搜索条件，需要以别的列构建B+树。

叶子节点存储索引列和主键。目录项存储索引列和页号（实际上是索引列和主键联合，后面会说为保证唯一）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4q124o0V-1681129044881)(null)]

查询要查两次（回表）：先根据筛选条件在普通索引上定位id，再拿id再聚集索引上定位行

3.联合索引

以c2和c3同时作为排列规则，优先c2，然后c3。别的和二级索引等价

InnoDB注意事项

1.根节点位置不变
一开始根节点存放数据，满了后复制一份数据存储到别的地址，然后根节点升一级。此外，根节点常驻内存

2.目录项记录的唯一性
二级索引中索引列 + 页号搭配，索引列不唯一插入走哪条路就不知道了。所以默认会将索引列+主键+页号搭配，索引列和主键类似构建了一个联合索引。

3.一个页面至少两条数据

③ MyISAM中索引

索引与数据分离 .myd存储数据（无序）， .myi存储索引

B树索引使用存储引擎如表所示：

索引 / 存储引擎	MyISAM	InnoDB	Memory
B-Tree索引	支持	支持	支持

即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。Innodb和MyISAM默认的索引是Btree索引；而Memory默认的索引是Hash索引。

MyISAM引擎使用 B+Tree 作为索引结构，叶子节点的data域存放的是地址。相当于二级索引，但第二次是直接去磁盘

这里是针对主键构建的，还可以根据c2，c3都构建。

对比：

InnoDB对聚簇索引只需要查找一次，二级索引需要两次。MYISAM都是两次，但第二次很快
MYISAM索引文件分离
MYISAM可以没有主键

④ 索引的代价

空间：每一个索引都是一颗b+树
时间：增删改需要额外维护树结构

⑤ 数据结构选择

Hash

hashmap O(1)

Hash只能进行=和in的查询，不能进行范围查询(退化到O n)
无序不能order by
联合索引时，hash值是一起构建的，无法拆开利用
重复值过多，冲突很多

索引 / 存储引擎	MyISAM	InnoDB	Memory
HASH索引	不支持	不支持	支持

但Innodb也可以结合Hash，例如当某个条件WHERE a = XXX经常访问时，将该条件对应地址直接存在hash中

mysql> show variables like '%adaptive_hash_index';

二叉搜索树

一个节点只能有两个子节点
左子节点 < 本节点; 右子节点 >= 本节点
不平衡时退化

AVL树

平衡二叉树且高度差小于1

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xgEB6NFv-1681129039387)(null)]

还可以变成多叉树来降低高度

B-Tree

每一个节点最多M个子节点，M称为树的阶。非叶子节点也存放数据

一个 M 阶的 B 树（M>2）有以下的特性：

根节点的儿子数的范围是 [2,M]。
每个中间节点包含 k-1 个关键字和 k 个孩子，孩子的数量 = 关键字的数量 +1，k 的取值范围为 [ceil(M/2), M]。
叶子节点包括 k-1 个关键字（叶子节点没有孩子），k 的取值范围为 [ceil(M/2), M]。
假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即 Key[i]<Key[i+1]。此时 k-1 个关键字相当于划分了 k 个范围，也就是对应着 k 个指针，即为：P[1], P[2], …, P[k]，其中 P[1] 指向关键字小于 Key[1] 的子树，P[i] 指向关键字属于 (Key[i-1], Key[i]) 的子树，P[k] 指向关键字大于 Key[k-1] 的子树。
所有叶子节点位于同一层。

数据是一块的实现相比于平衡二叉树来说磁盘 I/O 操作要少

B+树

B+ 树和 B 树的差异在于以下几点：

有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数，而 B 树中，孩子数量 = 关键字数 +1。
非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。
非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中（小从小到大顺序链接）。而 B 树中，非叶子节点既保存索引，也保存数据记录。

不在中间节点放数据，好处

数据都在叶子，查询效率稳定
非叶子无数据，可以存储更多数据，更矮胖
范围查找：叶子节点上直接进行范围查找效率高

B树层数与节点数关系见：B+Tree，整课树能达到GB，不会全部加载到内存，只有根节点是常驻内存的

~InnoDB数据存储结构

索引是在存储引擎中实现的，存储引擎负责数据的读取和写入

InnoDB将数据划分为页（16KB），页是磁盘和内存交换的基本单位，

show variables like '%innodb_page_size%';

SQL Server 中页的大小为 8KB，而在 Oracle 中我们用术语 “块” （Block）来表示 “页”，Oracle 支持的快大小为2KB, 4KB, 8KB, 16KB, 32KB 和 64KB。

页和页间双向链表
页内数据按主键值单向链表

① 上层结构

区：包含64个==连续==的页，16*16KB = 1MB

范围查询时需要连续访问多个页，通过数据连续减少磁盘读取时间 10ms -> 0.4ms
- 随机读取 单页10ms(6ms寻道3ms等待1ms传输)
- 顺序读取 40MB/s吞吐量，40/16kb可以读取2560页，单页0.4ms
段：多个区，数据库分配的基本单位：创建表、索引时分配。逻辑概念

叶子节点放在一个区里，非叶子节点页在一个区里，区的集合叫做段
**碎片区：**每次创建索引都会申请两整块区，太浪费。为了节约空间碎片区内的页可以服务于不同的段，当达到32个碎片区页面后，就申请完整的区。
表空间：最高层逻辑容器，一个数据库由多个表空间：系统表空间、用户表空间等
- 独立表空间: 即每张表有一个独立的表空间 .ibd show variables like ‘innodb_file_per_table’
- 系统表空间 : 系统中所有表的索引外键路径等信息，也用B+树整合为表：数据字典
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Zo7IbmhJ-1681129044749)(null)]

② 页内结构

数据页的 16KB 大小的存储空间被划分为七个部分：

第一部分：File Header (文件头部) 和 File Trailer (文件尾部)

File Header：页编号、页类型（Undo日志数据页等）、上一页下一页、校验和（hash 文件->checksum）、日志位置

File Trailer：校验和（头尾一样代表完整）、日志位置

第二部分：User Records (用户记录)、最大最小记录、Free Space (空闲空间)

数据记录： 空闲空间->用户记录

最大最小记录：为了能构建B+树，同时是整个链表的首尾

第三部分：Page Directory (页目录) 和 Page Header (页面头部)

页目录：记录分组（直接二分空间太大，跳表），每组4-8个(一个槽)，每个组拿出一个最大值排列出来，然后二分。

最小记录单成组，最大记录4-8个
满9就拆分，所以很多都是4个

页目录就负责记录下每组最大记录的（地址偏移、最大值）

页面头部

③ 行格式

链表记录的组合方式，为了链表有效组合，需要提供一定的额外信息，变长字段长度和NULL在InnoDB行格式讲

记录头信息(5bit)

delete_mask 是否删除。逻辑删除，同时链表跳过它，并且修改组的数量最值。如果有新数据进来就覆盖一下
n_owned 每一组最后一条记录负责记下组内的记录个数
heap_no 页内位置编号 01是最小和最大记录
record_type 是否为叶子节点
next_record 链表next，记录偏移量

④ InnoDB行格式

create table emp3(id int)  row_format=compact;
alter table emp3 row_format=compact;

COMPACT
- 变长字段长度列表：对于变长字段不知道实际的长度VARCHAR(8)，需要有一个位子记录下实际上的长度逆序
- NULL值列：对于可能为NULL的列，NULL在存储中如果用特殊字符表示会浪费空间，直接用bit位来标识是不是NULL
- 记录头信息：同上
- 隐藏列：row_id(行ID，无主键和Unique键时生成) transaction_id(事务ID) roll_pointer(回滚指针)
.ibd文件讲解

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6GTdPn3S-1681129039151)(null)]

行溢出：

对于varchar，最长共65535字节: 65532 + 2(长度值) + 1(null值)

16KB=16384字节，存不下varchar，所以compact存一部分然后存在别的地方，保存下地址
Dynamic(MySQL5.7)和Compressed

Dynamic行溢出只存地址不存数据，Compressed还会用zlib压缩数据
Redundant

字段偏移列表：所有列的偏移地址，等价于变长+NULL，但纯在冗余（对于非varchar）

~索引创建

创建完成，.ibd文件会增大

① 索引基本操作

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引（UNIQUE）、主键索引(UNIQUE+NOT NULL 唯一)、全文索引（提高大数据量的检索速度，被solr、ElasticSearch代替）。
按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。
按照作用字段个数 进行划分，分成单列索引和联合索引。

创建索引

隐式：主键、Unique、外键 会自动添加索引

CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT, # 
dept_name VARCHAR(20)
);

CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT, # 主键
emp_name VARCHAR(20) UNIQUE, # unique
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id) # 外键
)

显式：

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |
DESC]

UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；
index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
ASC 或 DESC 指定升序或者降序的索引值存储。

INDEX (book_name)
INDEX multi_idx(book_id, book_name)
UNIQUE INDEX book_n(book_name)

SHOW INDEX FROM test3 \G # 查看全部索引

EXPLAIN select * from book where book_name = "mysql" # 查看是否用上索引

添加删除索引

添加

ALTER TABLE table_na ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]
	ALTER TABLE book5 ADD INDEX book_n (book_name) ;

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

删除

ALTER TABLE table_na DROP INDEX index_name;

DROP INDEX index_name ON table_na;

# 唯一索引不能删除 起约束作用

② 8.0索引新特性

降序索引

MySQL 8.x开始支持降序 DESC，之前版本的会被忽略。多个列排序

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

查询时order by a,b desc，没有降序的化效果很差，需要文件排序

隐藏索引

索引设置为隐藏索引，查询优化器忽略这个索引（更新时索引树还是会更新）。可以用来验证某个索引性能

ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE; # 添加隐藏索引

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

③ 索引的设计原则

数据准备 100W条

适合创建索引!

唯一字段

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。（来源：Alibaba）

频繁where字段

277ms->20ms

经常 GROUP BY 和 ORDER BY 的列

DISTINCT 字段

多表JOIN时

连接表的数量尽量不要超过 3 张
对 WHERE 条件创建索引
对用于连接的字段创建索引

使用前缀创建索引

不使用整个字符串构建。虽然不能精确查询，但查个大概然后回表查询完整的字符串。排序时用不上

太长存储空间太大
太长比较时需要占用更多时间

# 截取长度的选择：根据区分度    一般20 就可以达到90%以上
select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度
from shop

区分度高(散列性高)

select count(distinct a) / count(*) from t1，超过33%就不错的索引了

相对应的，像性别字段就不要添加索引

最频繁的列放到联合索引的左侧

对应最左前缀原则。

多个字段都要创建时，联合索引更优

减少开销：一个联合索引(c1,c2,c3)，相当于(c1),(c1,c2),(c1,c2,c3)三个索引
覆盖索引： select col1,col2,col3 from test where col1=1 and col2=2，无需回表
效率高：索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w * (10% *10% *10%)=1w，效率提升可想而知！

限制索引数量

单表不超过六个：

索引占用空间
更新时间
查询时，优化器需要对可能用到的索引进行比较，太多会降低性能

~性能分析工具

① 优化步骤

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HRuB4HC2-1681129045305)(null)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sW8S04N5-1681129040791)(null)]

② 查看系统性能 status

SHOW STATUS ：查询一些MySQL数据库服务器的性能参数、执行频率。 %是通配符任意匹配

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

Connections：连接MySQL服务器的次数。
Uptime：MySQL服务器的上线时间。
Slow_queries：慢查询的次数。
Innodb_rows_read：Select查询返回的行数
Innodb_rows_inserted：执行INSERT操作插入的行数
Innodb_rows_updated：执行UPDATE操作更新的行数
Innodb_rows_deleted：执行DELETE操作删除的行数
Com_select：查询操作的次数。
Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
Com_update：更新操作的次数。
Com_delete：删除操作的次数。
last_query_cost：查看最后一次操作查询了多少张数据页。
- 建立了索引，可以有效减少访问的页数。回表的化页数还要增加
- 页数多页不用担心，1.页可以在缓冲池或内存中、2.批量顺序读取速度也很快

③ 定位执行慢的 SQL：慢查询日志

超过long_query_time的SQL会记录下，默认10s

show variables like '%slow_query_log%'; # 是否打开 以及 保存地址
set global slow_query_log='ON'; # 打开

 show variables like '%long_query_time%'; # 时间阈值
 
 # 设置时间
 #测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并
mysql > set global long_query_time = 1;
mysql> show global variables like '%long_query_time%';
mysql> set long_query_time=1;
mysql> show variables like '%long_query_time%';

show status like 'slow_queries' 显示有多少sql超时了

mysqldumpslow ：日志分析工具，给日志文件就可以定位sql。

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

④ PROFILE

show variables like 'profiling';
set profiling = 'ON';
show profiles; # 近几个查询
show profile; # 最近一个的细节   各个阶段耗时
show profile cpu,block io for query 2

⑤ EXPLAIN

不考虑各种Cache、不能显示优化工作、不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

数据准备

CREATE TABLE s1 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE s2 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。
# Function
DELIMITER //
CREATE FUNCTION rand_string1(n INT)
	RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
        SET i = i + 1;
    END WHILE;
    RETURN return_str;
END //
DELIMITER ;

# 存储过程
DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO s1 VALUES(
        (min_num + i),
        rand_string1(6),
        (min_num + 30 * i + 5),
        rand_string1(6),
        rand_string1(10),
        rand_string1(5),
        rand_string1(10),
        rand_string1(10));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO s2 VALUES(
        (min_num + i),
        rand_string1(6),
        (min_num + 30 * i + 5),
        rand_string1(6),
        rand_string1(10),
        rand_string1(5),
        rand_string1(10),
        rand_string1(10));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;

# 调用
CALL insert_s1(10001,10000);
CALL insert_s2(10001,10000);

各列作用☆

一条语句可多个select，一个select可from多个表，一个表一行记录

table

对应表，每行记录都对应一个表。可能有临时表

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; Union会构建临时表去重，一共3个
id

对应select个数，如子查询就有多个select

按照id从小到大执行，同一id前面的是驱动表
select_type 小查询在整个大查询扮演的角色
1. simple：简单以及JOIN EXPLAIN SELECT * FROM s1; EXPLAIN SELECT * FROM s1 INNER JOIN s2;
2. PRIMARY ：UNION最左边，子查询最外面的 EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; s1
3. UNION：UNION剩下的 s2
4. UNION RESULT：UNION产生的临时表
5. SUBQUERY ：子查询里面EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
6. 等等p137
partition(分区相关)
type ☆

执行查询时的访问方法：system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。
- system 一条记录且数据统计精准（MyISAM、Memory）
- const 主键或唯一二级索引与常数等值匹配
- eq_ref 连接时，被驱动表是通过主键或者唯一二级索引列
- ref 普通的二级索引列与常量进行等值匹配
- index_merge 条件为or时，同时用上多个索引
- …
- range 索引的范围查询 c1 IN ('a', 'b') c1 > 'a' c1 LIKE 'abc%';
- index 索引覆盖，但需要扫描全部记录 EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
  
  原因：扫描二级索引的代价比直接全表扫描，也就是扫描聚簇索引的代价更低一些。
- ALL 全表扫描
  
  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
  
  SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）
possible_keys和key

可能用到的索引 和 实际用的 ：EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
key_len ☆

用上的索引字节长度，检查是否充分利用索引，主要针对联合索引。加上了变长和null

长度为 2个索引列 * （数据长度3 utf-8 + 2变长字段 + 1null） 606 = 2 * （1003+2+1）

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b'; 606
ref

和索引列比较的结构是什么：const、s1.id、func等

WHERE s2 = 'a'; s1 INNER JOIN s2 ON s1.id = s2.id; INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
rows☆

预估的需要读取的记录条数，值越小越好。
filtered

加入条件时，剩下数据的比例。在驱动表中剩下越少，循环下一张表次数也就越少
Extra☆
- No tables used
- Impossible WHERE where 永远false
- Using where 普通列
- No matching min/max row 有聚合函数但没有数据
- Using index 索引覆盖
- Using index condition 索引完后，先过滤key1 LIKE '%a’再去主表查询，索引条件下推
  
  SELECT * FROM s1 WHERE key1 > ‘z’ AND key1 LIKE ‘%a’;
- Using join buffer (Block Nested Loop) 被驱动表无索引，使用内存加速 基于块的嵌套循环算法
- Using filesort 对普通列order by，需在内存或磁盘排序。慢！
- Using temporary 使用临时表：DISTINCT、 GROUP BY、UNION。代价很大，最好使用索引来替代临时表

输出格式

传统格式
JSON格式 EXPLAIN FORMAT=JSON
TREE格式 EXPLAIN FORMAT=tree
Workbench可视化快速查看

⑥ SHOW WARNINGS

查看完整SQL语句，涉及到内查询等优化器可能会帮我们优化掉

EXPLAIN SELECT * from s1 where key1 in (select key2 from s2 where common_field = 'a');
show warnings; # 内查询优化成了连接

⑦ trace：分析优化器执行

# 开启，结果保存到information_schema.optimizer_trace中
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

select * from student where id < 10;

select * from information_schema.optimizer_trace\G

⑧ sys schema ：MySQL监控分析视图

查询需要消耗大量资源。查询结果来自全部的数据库的全部的表

索引情况

#1. 查询冗余索引
select * from sys.schema_redundant_indexes;
#2. 查询未使用过的索引
select * from sys.schema_unused_indexes;
#3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname';

表相关

# 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查询占用bufferpool较多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

#1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;
#2. 监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;
#3. 监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;

IO相关

#1. 查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb 相关

#1. 行锁阻塞情况
select * from sys.innodb_lock_waits;

~索引优化与查询优化

需要调优的角度：

索引失效、没有充分利用到索引——建立索引 物理查询优化
关联查询太多JOIN（设计缺陷或不得已的需求）——SQL优化 逻辑查询优化
服务器调优及各个参数设置（缓冲、线程数等）——调整my.cnf
数据过多——分库分表

① 数据准备

学员表 插 50万 条， 班级表 插 1万 条。

② 索引失效案例☆

用B+树推理出来即可！

2.1 全值匹配我最爱

# 没有索引时全部遍历
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;

2.2 最佳左前缀法则

最左边能匹配就能用

CREATE INDEX idx_age_classid_name ON student(age,classId,name);

# 能用
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId=4;

# 不能用
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.age=30 AND student.name = 'abcd';

2.3 主键插入顺序

主键AUTO_INCREMENT ，防止页面分裂：把本页中的一些记录移动到新创建的这个页中

2.4 计算、函数、类型转换(自动或手动)导致索引失效

CREATE INDEX idx_name ON student(NAME);
# 可以用上
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

# 函数千奇百怪，不知道返回什么，所以用不上
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

2.5 类型转换导致索引失效

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

2.6 范围条件右边的列索引失效

联合索引，范围查询的后面列无法用上索引。但c2 like 'c%'后面的可以用上

CAll proc_drop_index('atguigudb2', 'student');
CREATE INDEX idx_age_classid_name ON student(age,name,classId);

# 用不上 key_len=68
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 3 and name > 'c' and classId = 20;
# 用得上 key_len=73
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 3 and name like 'c%' and classId = 20;

应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。创建的联合索引中，务必把范围涉及到的字段写在最后

2.7 不等于(!= 或者<>)索引失效

不等于不能用

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';

2.8 is null可以使用索引，is not null无法使用索引

道理同上

结论：最好在设计数据库的时候就将字段设置为 NOT NULL 约束，比如你可以将 INT 类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串(‘’)。

扩展：同理，在查询中使用not like也无法使用索引，导致全表扫描。

2.9 like以通配符%开头索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab';

拓展：Alibaba《Java开发手册》

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

2.10 OR 前后存在非索引的列，索引失效

or纯在非索引导致失效，and不会

因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此所以的条件列也会失效。

# 加入只有age有索引
# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

2.11 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

③ 关联查询优化

数据准备

LEFT JOIN

不是绝对的左边为驱动表，如果左边有索引可能会变成被驱动表

驱动表Using joion buffer 使用缓存；添加索引被驱表可以走索引。

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

INNER JOIN

优化器选择驱动表，选择有索引的或者大表作为被驱动表

EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

JOIN原理

MySQL5.5版本之前，只有嵌套循环。后来引入BNLJ算法优化嵌套查询。8.0.18引入Hash Join

1.Simple Nested-Loop Join

简单嵌套循环连接：相当于两层循环。比较了A * B次，读取了A+AB次记录。

2. Index Nested-Loop Join

索引嵌套循环连接：被驱表走索引然后回表。比较A*height，读取了A+B(match)

3.Block Nested-Loop Join

块嵌套循环连接：引入块join buffer，把驱动表（分成一个个块）放在缓存中。被驱动一次比较一块驱动表记录，相当于外层循环变小了

通过show variables like '%optimizer_switch%' 查看 block_nested_loop状态。默认是开启的。

这里缓存的不只是关联表的列，select后面的列也会缓存起来。

比较了A * B，读取了 A + 块数*B，访问磁盘次数少了

join_buffer_size的最大值在32位操作系统可以申请4G，而在64位操作系统下可以申请大于4G的Join Buffer空间（64位Windows除外，其大值会被截断为4GB并发出警告）。

4. Hash Join

等值连接。对BNLJ进一步优化，针对小表直接建立Hash表，大表每一条比较时间降低到O1

如果能完全放下小表，时间为A+B。如果不能放下需分块，相当于大表还是一次比较一整块记录，但时间优化到O1

④ 子查询

**在MySQL中，可以使用连接（JOIN）查询来替代子查询。**连接查询 不需要建立临时表 ，其 速度比子查询 要快，如果查询中使用索引的话，性能就会更好。

EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
)
# 优化
EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` is NOT NULL;

⑤ 排序

FileSort （内存中，占CPU）或 Index 排序。

还可以使用联合索引，where用一部分，order后一部分， WHERE a = const AND b = const ORDER BY c

是否用所以还需要看排序代价，不大可以直接排序

双路和单路排序

双路：只拿排序列数据，排完序后再全部遍历取出需要列。

单路：直接所有需要列区排序，但需要更多sort_buffer_size，默认1MB。如果数据量大于max_length_for_sort_data（1024~8192B）转用单路。

所以select * 在这是大忌。

where效率高于having，能写在where限定的条件就不要写在having中了
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内

⑥ 优化分页查询

select * from sutdent limit 1000000,10这种头疼问题

本来需要回表>1000000，现在只需要回表10；使用连接优化子查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hiStgWnZ-1681129045716)(null)]

⑦ 索引覆盖

索引列+主键 包含 SELECT 到 FROM之间查询的列

索引覆盖可以提高速度，回表的IO很可能是随机IO（数据不连续）。但有维护代价，业务DBA（业务数据架构师）的工作。

此外，就算使用！=导致索引失效，如果可以索引覆盖还是会去使用索引，因为二级索引更小遍历代价更低。

⑧ 索引条件下推

先过滤再回表

通常针对联合索引，(c1, c2, c3) c1能使用但c2 c3用不了。c1用完索引后先c2 c3遍历过滤后再回表。

ICP的开启和关闭

set optimizer_switch = 'index_condition_pushdown=on'  或者off

执行语句时关闭
select /*+ no_icp (table_name) */ * from table_name  where ...

⑨ 其他优化策略

exists 和 in

exists用外表驱动内表，拿一条条外表数据去内表查（相关子查询）适合外小内大
in反过来，先查询内表，再拿数据去外表走索引

COUNT(*)

SELECT COUNT(*) 、 SELECT COUNT(1)基本等价，效率相等，自动选取小空间的二级索引。MyISAM维护了变量

SELECT COUNT(具体字段)尽量建立二级索引。

SELECT(*)

需要先查询数据字典将*转换为列名
无法使用覆盖索引
空间更大

LIMIT 1

在确定数据唯一，并且无唯一索引时，可以提前结束扫描，加速。

多使用COMMIT

COMMIT 所释放的资源：

回滚段上用于恢复数据的信息
被程序语句获得的锁
redo / undo log buffer 中的空间
管理上述 3 种资源中的内部花费

⑩ 淘宝数据库，主键如何设计的？

自增ID的问题

自增ID做主键，简单易懂，几乎所有数据库都支持自增类型，只是实现上各自有所不同而已。自增ID除了简单，其他都是缺点，总体来看存在以下几方面的问题：

可靠性不高

存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。
**安全性不高 **

对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/这样的接口，可以非常容易猜测用户ID的值为多少，总用户数量有多少，也可以非常容易地通过接口进行数据的爬取。
性能差

自增ID的性能较差，需要在数据库服务器端生成。
交互多

业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。
**局部唯一性 **

最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。

业务字段做主键

会员卡中的卡号：但如果用户注销了，购买记录还会给新的人。

身份证号：隐私问题等

经验：刚开始使用 MySQL 时，很多人都很容易犯的错误是喜欢用业务字段做主键，想当然地认为了解业务需求，但实际情况往往出乎意料，而更改主键设置的成本非常高。

淘宝的主键设计

淘宝的订单号

1550672064762308113
1481195847180308113
1431156171142308113
1431146631521308113

猜测：订单ID = 时间 + 去重字段 + 用户ID后6位尾号

~数据库设计规则

什么数据什么表、crud时的约束检查、减低数据冗余度、方便数据库维护使用

表结构的调整代价很大

① 范数

关系型数据库的基本原则。但有时为了提高性能还会破坏规则，反规范化（降低冗余度，但业务可能复杂了）

六种：

第一范式：列不能再分
第二范式：非主属性消除部分依赖
第三范式：非主属性消除传递依赖（平衡）
巴斯-科德范式：候选键只有一个，或者每个候选键都是单属性。主属性消除部分依赖
第四范式：不存在多组多值依赖（一对多）：职工表（职工id，孩子姓名，职工课程），需要拆开
第五范式：理论研究

找出所有码 -> 主属性、非主属性 -> 检查部分函数依赖 -> 检查传递函数依赖

键和相关概念

超键：能唯一标识元组的属性集
候选键：超键且不包括多余属性，可以多个；码
主键：从候选键中选一个
外键：R1中的属性不是R1的主键，而是R2的
主属性：所有候选键中的所有属性
非主属性

第一范式

每个字段都不能拆分了，如不能把电话、姓名、地址都放到一个字段中。

first name，lastname是否拆分要看具体的业务需求

第二范式

所有非主键完全依赖于候选键，如果存在部分依赖，则抽出来新建表

成绩表：要想知道成绩，完全依赖于（学号、课程号）

在学生的成绩表中，不再放学生的地址等信息

第三范式

非主属性C直接相关于主属性A，而不能依赖于其他非主属性B：主属性A->非主属性B->非属性C

员工表中只放部门编号，不放部门其他信息，这些信息可以通过join查询获得

商品表中只放商品类别id，不放类别名称

② 反范式

业务优先，增加冗余字段来提高读性能。空间换时间。冗余字段修改需要同步

员工表的查询经常要部门名称，加进来；商品表同理。违反了第三范式

学生表和课程评论表，查询课程的评论时经常要用学生名称

学生表100w，评论表100w

查询某课程的前10000条评论的姓名和评论；反范式化后，把学生姓名加入评论表

0.1 -> 0.036

条件：冗余字段不常修改且查询时必要

~数据库其他调优

从更大的层面来优化，主要就是下面这张表。垂直分库分表、读写分离

目标：吞吐量更大、响应速度更快

问题发现：用户反馈、日志

① 调优步骤

1.合适的DBMS

事务以及安全性要求高，选择SQL Server、Oracle。单表可以存储上亿条数据。

2.优化表设计

尽量遵循第三范式并且适当运用反范式

数据类型选择合适的

3.优化逻辑查询

SQL的重写：子查询变join

4.优化物理查询

索引

5.使用Redis缓存

redis支持持久化，除了内存还可以将数据放到硬盘上

6.库级优化

站在数据库维度进行优化。

多台机器读写分离、分库分表切割数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j1ZleExz-1681129044401)(null)]

② 优化MySQL服务器

硬件层面：大内存、高速磁盘系统(SCSI -> SSD) ，合理分布磁盘I/O，配置多处理器

MySQL参数：innodb_buffer_pool_size (表、索引)、key_buffer_size 、table_cache 、query_cache_size(查询缓存、8.0没有)、sort_buffer_size(排序)、join_buffer_size(join缓存)、max_connections(默认151)

③ 表结构优化

冷热数据分离

拆分：会员表 存储会员登录认证信息，该表中有很多字段，如id、姓名、密码、地址、电话、个人描述字段。其中地址、电话、个人描述等字段并不常用，可以将这些不常用的字段分解出另一个表。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Pn97czLI-1681129039608)(null)]

增加中间表

经常需要联合查询的数据，直接多开一个中间表

增加冗余字段

反范数

数据类型

整数：通常int，unsigned要用就用

TIMESTAMP：TIMESTAMP存储的时间范围1970-01-01 00:00:01 ~ 2038-01_19-03:14:07。TIMESTAMP使用4字节，DATETIME使用8个字节，同时TIMESTAMP具有自动赋值以及自动更新的特性。

DECIMAL代替FLOAT和DOUBLE存储精确浮点数：精准的浮点数、4B存储9位，且可以存储比bigint大的整型数据

④ 大表优化

1 限定查询的范围

禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

2 读/写分离

经典的数据库拆分方案，主库负责写，从库负责读。

3.垂直拆分

冷热数据分离，也可以是经常使用的放一起

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YgNz3nmM-1681129045097)(null)]

4.水平拆分

数据量尽量控制在1000w以内

按某个属性如年份划分到不同的表

但表数据还是同一个机器上，并发量还是上不去，还是分库比较好。

拆分会带来逻辑、部署、运维的复杂度

~事务

概述

**事务：**一组逻辑操作单元，使数据从一种状态变换到另一种状态。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HYtyWXM6-1681129039497)(null)]

MySQL中，只有InnoDB支持事务。

ACID

原子性（atomicity）： 不可分割

一致性（consistency）： 数据从一个 合法性状态 变换到另外一个 合法性状态 。

隔离型（isolation）：不能被其他事务干扰，锁来实现。下小节为对应隔离级别

持久性（durability）： 提交后永久改变。通过 事务日志（重做日志 和 回滚日志）来实现

事务状态

提交或者中止是一个事务生命周期的结束

显式事务

# 显式事务会关闭自动提交
BEGIN;
#或者
START TRANSACTION (READ ONLY \ READ WRITE);

SAVEPOINT savepoint_name
# 将事务回滚到某个保存点。 事务还没结束
ROLLBACK TO [SAVEPOINT]

## 结束事务
# 提交事务。当提交事务后，对数据库的修改是永久性的。
COMMIT;
# 回滚事务。即撤销正在进行的所有没有提交的修改
ROLLBACK;

隐式事务

SHOW VARIABLES LIKE 'autocommit'; 
ON # 每个语句自动提交事务
SET autocommit = on\1 ;  OFF\0

事务的隔离级别

在并发场景下（若干个客户端连接），如何处理隔离

并发问题

脏写（ Dirty Write ）：B在更新时，A先更新且commit了，但B rollback了，吞掉了A的更新。（实际上A需要排队）
脏读（ Dirty Read ）：读取到了还没commit的数据
不可重复读（ Non-Repeatable Read ）：一个事务两次读之间，值被别的人commit改了，导致每次读取不一样
幻读（ Phantom ）：两次读之间，插入了一些新行（幻影记录）。

隔离级别

解决上面的问题

1.读未提交（Read uncommitted）：内存有直接读内存

这种事务隔离级别下，select语句不加锁。

此时，可能读取到不一致的数据，即“读脏 ”。这是并发最高，一致性最差的隔离级别。

2.读已提交（Read committed）：读磁盘 Oracle

可避免脏读的发生。

在互联网大数据量，高并发量的场景下，几乎不会使用上述两种隔离级别。

3.可重复读（Repeatable read）：MySql SHOW VARIABLES LIKE 'tx_isolation'; transaction_isolation

事务开始前尽可能创建快照（内存）。

查询时先在快照区中查找数据的快照，如果快照区中没有该数据的快照，MySQL 就会再 undo 日志中查找该数据最新的修改操作，并将其还原到快照区中，然后再将数据返回给事务

mysql其实也可以解决幻读，在锁里讲

4.串行化（Serializable ）：

可避免幻读

加了行X锁，比如s1查询了id=3的数据，s2的插入就会阻塞

# mysql，想永久修改需要修改配置文件
SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别'
#其中，隔离级别格式：
> READ-UNCOMMITTED
> READ-COMMITTED
> REPEATABLE-READ
> SERIALIZABLE

隔离级别越高，数据一致性就越好，但并发性越弱。

事务日志

而事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。存储引擎层 (innodb) 生成

REDO LOG 称为 重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。

"物理级别"上的页修改操作，比如页号xxx，偏移量yyy写入了’zzz’数据。
UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

逻辑操作 日志，INSERT则会记录一条对应DELETE。用于 事务的回滚和 一致性非锁定读

COMMIT用REDO实现，ROLLBACK用UNDO实现。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1hf7aDT3-1681129044279)(null)]

redo日志

保证事务的持久性。

首先需要读取磁盘 -> 内存中的Buffer Pool
发生修改更先更新Buffer Pool，再更新磁盘；更新了但没写入磁盘的叫脏页。
内存是正确的，但刷盘是比较慢的且可能宕机，为了保证磁盘数据是正确的：
1. 疯狂一直刷盘（代价大），并且事务修改不相邻页面时，IO是随机IO
2. 引入redo日志

WAL技术 (Write-Ahead Logging)：先写日志（写入了就算事务成功），再写磁盘。

实现更低频率更高速度的刷盘：

低频：redo日志占用的空间小（存储表空间ID、页号、偏移量以及需要更新的值）
高速：顺序IO

redo特点

redo日志是顺序写入磁盘的

一个事务多条语句,一条语句多条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序ID，效率比随机IO快。
事务执行过程中，redo log不断记录

redo log跟bin log的区别，redo log是存储引擎层产生的，而bin log是数据库层产生的。假设一个事务，对表做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而bin log不会记录，直到这个事务提交，才会一次写入到bin log文件中。

redo日志组成

重做日志的缓冲 (redo log buffer)：内存 innodb_log_buffer_size 16MB
重做日志文件 (redo log file) ：mysql/data/ib_logfile0 ib_logfile1

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xBY3KvMH-1681129040573)(null)]

先持久化日志，再持久化数据(在commit后才会持久化)

至此，关键的刷盘变成了步骤3，由参数innodb_flush_log_at_trx_commit控制

设置为0 ：表示每次事务提交时不进行刷盘操作。
设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache（OS对写入磁盘的优化，由OS决定，有小概率宕机），不进行同步。由os自己决定什么时候同步到磁盘文件。此时MYSQL挂了不影响
设置为1 ：表示每次事务提交时都将进行同步（写入page cache并同步磁盘）（默认值，持久性的保证）

此外：系统默认master thread每隔1s进行一次重做日志的同步：redo log buffer到page cache，然后刷盘

或者写满innodb_log_buffer_size后刷盘。

redo log buffer

一个原子操作为一个Mini-Transaction，如一次插入。但一次插入可能移动多条数据，对应多个redo

一个MTR里的多个redo需要放在一起（不可分割），但一个事务内的多个MTR可以分割存放

t1、t2为两个事务，mtr_t1_1由多条redo组成
buffer由block组成，一个block512字节 innodb_log_block_size，对应一个扇区的大小

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZaxAi8nQ-1681129045485)(null)]

头包含指针、已使用多少等信息

redo log file

innodb_log_group_home_dir 文件路径
innodb_log_files_in_group 默认2组
innodb_log_file_size 单个 redo log 文件设置大小，默认值为 48M

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Of0uAmGD-1681129040110)(null)]

write pos指针（终点）：当前插入点

checkpoint指针（起点）：checkpoint 和 write pos 之间为在内存中但还没写入磁盘，故障后需要恢复这部分数据
innodb_flush_log_at_trx_commit 刷盘策略

施放时间

redo log 在事务的执行过程中不断记录。通常在事务提交时buffer->file。
理论上redo log file中的文件在数据写入磁盘（图4）后可以释放

Undo日志

更新数据前先更新undo log。在系统表空间，也可以使用独立表空间

**原子性：**要么都做，要么都不做。可能发生意外，如何逻辑恢复到之前状态

服务器错误、断电
手动ROLLBACK

增删改都要记录反向操作

undo日志为了实现持久化，需要同样会产生redo日志。

在内存数据更新前先更新undo

作用

作用1：回滚数据
作用2：MVCC（多版本并发控制）。当读取一条被占用的数据时，用undo读取之前的信息

回滚段

innodb_undo_logs个回滚段。一个回滚段多个页，一个页（可服务于多个事务，重用防止浪费）存放undo记录，

数据分类

未提交的回滚数据(uncommitted undo information)：该数据所关联的事务并未提交，用于实现读一致性，所以该数据不能被其他事务的数据覆盖。（避免脏读）
已经提交但未过期的回滚数据(committed undo information)：该数据关联的事务已经提交，但是仍受到undo retention参数的保持时间的影响。（避免不可重复读）
事务已经提交并过期的数据(expired undo information)：事务已经提交，而且数据保存时间已经超过 undo retention参数指定的时间，可覆盖

生命周期

正常提交就是提交，redo log file写入了就持久化了。在正常情况下rollback，根据undo日志回滚
如果没commit且没redo log file但宕机了，数据只内存中，不用改磁盘，事务结束
如果没commit但有redo log file宕机了，可以选择rollback，或者继续事务

行格式中，每行数据都会有一个事务id，以及回滚指针（指向undo的指针）

回滚

undo log在操作过程中串联起来，记录下数据差异，回滚时逆向进行

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DYCmJW3z-1681129044491)(null)]

通过undo no=3的日志把id=2的数据删除
通过undo no=2的日志把id=1的数据的deletemark还原成0
通过undo no=1的日志把id=1的数据的name还原成Tom
通过undo no=0的日志把id=1的数据删除

锁

实现事务的隔离性

并发访问时的安全性

并发事务情况

读-读 ：无影响
写-写：脏写 必须加锁。先来的加锁，后来的排队。先来的完成后把后来的激活
读-写：脏读、不可重复读、幻读 重点关注

读写解决

读（MVCC），写（加锁）。下章
读写都加锁。但插入的数据无法加，无法解决幻读；读-写操作彼此需要 排队执行 ，影响性能。

一般情况下我们当然愿意采用 MVCC 来解决 读-写 操作并发执行的问题，但是业务在某些特殊情况下，要求必须采用 加锁 的方式执行。下面就讲解下MySQL中不同类别的锁。

锁的类别

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9YfkxHpc-1681129040465)(null)]

读写锁

InnoDB可以加表上、也可以加行上

共享锁S：读也可以加X锁，这里是手动加锁。(MVCC中的当前读)

SELECT   LOCK IN SHARE MODE
SELECT   FOR UPDATE (nowait 锁了就直接返回、不等待)

排他锁X：写锁，会阻断X和S。有S时也不能加X。一直等到超时
- DELETE：X锁
- INSERT：隐式锁，因为一开始没有空间
- UPDATE：
  - 修改了主键：先del再insert
  - 空间没有变：X锁
  - 空间变化了：相当于修改主键，先del再insert

表行页锁

粒度越小，开销越大，并发性越高。InnoDB提供到行锁

表锁

不依赖于存储引擎，策略一样

表锁

表锁：InnoDB一般在崩溃恢复时才加，MyISAM默认

`LOCK TABLES t READ`   `S锁`
`LOCK TABLES t WRITE`  `X锁` 

# 查看
SHOW OPEN TABLES; # 主要关注In_use字段的值
或者
SHOW OPEN TABLES where In_use > 0;

UNLOCK TABLES; # 使用此命令解锁当前加锁的表

MyISAM默认会在执行CRUD时对整个表加锁，只能读-读，导致查询性能下降的原因。InnoDB不加

注意加锁后，不能操作别的表了

意向锁

InnoDB原本想加表锁，需要遍历全部数据查看有没有行锁

A加了行X锁后，自动加一个表的X意向锁（代表A有意向去操作整张表）其他人B就加不了表锁。存储引擎 维护

也就是B加锁前，除了看有没有锁，还要看有没有同级别的意向锁的存在

协调多粒度锁共存，方便在加表锁时，查看有没有行锁

自增锁

AUTO_INCREMENT

多并发下如何实现自增

直接加锁等、

元数据锁

DDL操作加锁：别人在读数据时，你不能改表结构

行锁

**优点：**锁定力度小，发生锁冲突概率低，可以实现的并发度高。
**缺点：**对于锁的开销比较大，加锁会比较慢，容易出现死锁情况。

① 记录锁（Record Locks）

LOCK_REC_NOT_GAP，就是正常的X、S锁。只有S-S共享

例：一个事务在更新（自动加X），另一个不可以select .. lock in share mode，但可以直接读（MVCC）

② 间隙锁（Gap Locks）

在RR级别下解决幻读

（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据

SELECT * FROM t WHERE id BETWEEN 100 AND 200；
查询后，100~200之间加上间隙锁，禁止再插入数据

③ 临键锁（Next-Key Locks）

一个记录锁和一个gap锁的合体

begin;
select * from student where id <=8 and id > 3 for update;

页锁

页锁就是在 页的粒度 ，粒度在表和行之间

乐观锁悲观锁

**悲观锁：**认为别人会影响自己，总是加锁阻塞其他线程。数据库实现 synchronized ReentrantLock

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-01pOpj9p-1681129045597)(null)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ou0xs44X-1681129040237)(null)]

加锁后等待时间较长，所有扫描到的数据都会锁定，因此必须要索引

乐观锁

不上锁。

在表中设计一个 版本字段 version ，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行 UPDATE ... SET version=version+1 WHERE version=version 。version不一样说明别人修改过了

上面可能会导致失败太多，再更新。减库存前先判断

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TnwC2GoI-1681129045911)(null)]

乐观锁 适合 读操作多 ，悲观锁相反

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Us2wi8eH-1681129039982)(null)]

全局锁

整个数据库只读

Flush tables with read lock

死锁

互斥条件：资源不能被共享，即每个资源一次只能被一个进程占用。
请求与保持条件：一个进程因请求资源而阻塞时，已获得的资源保持不放。
不剥夺条件：进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完后自己释放。
循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

处理死锁

**方式1：**等待，直到超时（innodb_lock_wait_timeout=50s)。不太好

**方式2：**使用死锁检测处理死锁程序， wait-for graph算法

需要存储全部事务构建的锁信息，构建等待图，存在环就存在死锁

一旦检测到回路、有死锁，这时候InnoDB存储引擎会选择回滚undo量最小的事务，让其他事务继续执行（innodb_deadlock_detect=on表示开启这个逻辑）。

复杂度还是比较高

锁结构

InnoDB使用锁信息来跟踪行级锁。锁信息（lock_t）存储在锁管理器（lock manager），去锁管理器中查询该行是否被锁定。

InnoDB 存储引擎中的 锁结构 如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ky5PuvKl-1681129044989)(null)]

结构解析：

1. 锁所在的事务信息 ：

不论是 表锁 还是 行锁 ，都是在事务执行过程中生成的，哪个事务生成了这个锁结构，这里就记录这个事务的指针

2. 索引信息 ：

对于 行锁 来说，需要记录一下加锁的记录是属于哪个索引的。这里也是一个指针。

3. 表锁／行锁信息 ：

表锁结构 和 行锁结构 在这个位置的内容是不同的：

表锁：

记载着是对哪个表加的锁，还有其他的一些信息。
行锁：

记载了三个重要的信息：
- Space ID ：记录所在表空间。
- Page Number ：记录所在页号。
- n_bits ：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个n_bis属性代表使用了多少比特位。
n_bits的值一般都比页面中记录条数多一些。主要是为了之后在页面中插入了新记录后也不至于重新分配锁结构

4. type_mode ：

这是一个32位的数，被分成了 lock_mode 、 lock_type 和 rec_lock_type 三个部分，如图所示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FqIL9HZt-1681129040351)(null)]

锁的模式（ lock_mode ），占用低4位，可选的值如下：
- LOCK_IS （十进制的 0 ）：表示共享意向锁，也就是 IS锁 。
- LOCK_IX （十进制的 1 ）：表示独占意向锁，也就是 IX锁 。
- LOCK_S （十进制的 2 ）：表示共享锁，也就是 S锁 。
- LOCK_X （十进制的 3 ）：表示独占锁，也就是 X锁 。
- LOCK_AUTO_INC （十进制的 4 ）：表示 AUTO-INC锁 。

在InnoDB存储引擎中，LOCK_IS，LOCK_IX，LOCK_AUTO_INC都算是表级锁的模式，LOCK_S和 LOCK_X既可以算是表级锁的模式，也可以是行级锁的模式。

锁的类型（ lock_type ），占用第5～8位，不过现阶段只有第5位和第6位被使用：
- LOCK_TABLE （十进制的 16 ），也就是当第5个比特位置为1时，表示表级锁。
- LOCK_REC （十进制的 32 ），也就是当第6个比特位置为1时，表示行级锁。
行锁的具体类型（ rec_lock_type ），使用其余的位来表示。只有在 lock_type 的值为 LOCK_REC 时，也就是只有在该锁为行级锁时，才会被细分为更多的类型：
- LOCK_ORDINARY （十进制的 0 ）：表示 next-key锁 。
- LOCK_GAP （十进制的 512 ）：也就是当第10个比特位置为1时，表示 gap锁 。
- LOCK_REC_NOT_GAP （十进制的 1024 ）：也就是当第11个比特位置为1时，表示正经 记录锁 。
- LOCK_INSERT_INTENTION （十进制的 2048 ）：也就是当第12个比特位置为1时，表示插入意向锁。其他的类型：还有一些不常用的类型我们就不多说了。
is_waiting 属性呢？基于内存空间的节省，所以把 is_waiting 属性放到了 type_mode 这个32 位的数字中：
- LOCK_WAIT （十进制的 256 ）：当第9个比特位置为 1 时，表示 is_waiting 为 true ，也就是当前事务尚未获取到锁，处在等待状态；当这个比特位为 0 时，表示 is_waiting 为 false ，也就是当前事务获取锁成功。

5. 其他信息 ：

为了更好的管理系统运行过程中生成的各种锁结构而设计了各种哈希表和链表。

6. 一堆比特位 ：

如果是 行锁结构 的话，在该结构末尾还放置了一堆比特位，比特位的数量是由上边提到的 n_bits 属性表示的。InnoDB数据页中的每条记录在 记录头信息 中都包含一个 heap_no 属性，伪记录 Infimum 的 heap_no 值为 0 ， Supremum 的 heap_no 值为 1 ，之后每插入一条记录， heap_no 值就增1。锁结构最后的一堆比特位就对应着一个页面中的记录，一个比特位映射一个 heap_no ，即一个比特位映射到页内的一条记录。

锁监控

# 查询正在被锁阻塞的sql语句。
SELECT * FROM information_schema.INNODB_TRX\G;
# 查询锁等待情况
SELECT * FROM data_lock_waits\G;
# 查询锁的情况
SELECT * from performance_schema.data_locks\G;

MVCC

MVCC （Multiversion Concurrency Control），隔离级别我们可以设置，MVCC是如何实现隔离级别(RM和RR)

解决读-写冲突，读不加锁。读为快照读，写为当前读

组成：隐藏字段、undo日志、ReadView

快照读与当前读

快照读

基本的select。隔离级别不是串行级别

当前读

为读到最新数据，加锁

SELECT * FROM student LOCK IN SHARE MODE; # 共享锁，锁上后别人不能修改
SELECT * FROM student FOR UPDATE; # 排他锁
INSERT INTO student values ... # 排他锁
DELETE FROM student WHERE ... # 排他锁
UPDATE student SET ... # 排他锁

复习

隔离级别

隐藏字段、Undo Log版本链

隐藏字段包含：
- trx_id ：最后一次修改的事务id
- roll_pointer: 操作的记录信息
Undo Log版本链：包含历史信息

举例：

如事务8插入了一条数据

此时两个事务分别进行2次修改，注意他们不能同时更新，只能一个commit后再一个更新：写锁

在完成后，第一条为记录，历史版本串成了undo日志

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xzXNZLlE-1681129044637)(null)]

ReadView

基本

MVCC 的实现依赖于：隐藏字段、Undo Log、Read View。

ReadView: 事务在进行快照读时产生的 读视图（一对一）。有一个数组记录活跃事务ID

READ UNCOMMITTED ：读未提交，就是最新数据。不使用
READ COMMITTED：每次读之前都会新生成一个视图
REPEATABLE READ：第一次读时生成
SERIALIZABLE：从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。并发度急剧下降，不建议使用。

ReadView组成

这个ReadView中主要包含4个比较重要的内容，分别如下：

creator_trx_id ，创建这个 Read View 的事务 ID。

说明：只有在CUD才会为事务分配事务id，否则在R事务中的事务id值都默认为0。
trx_ids ，表示在生成ReadView时当前系统中活跃的读写事务的 事务id列表 。
up_limit_id ，活跃的事务中最小的事务 ID。
low_limit_id ，表示生成ReadView时系统中应该分配给下一个事务的 id 值。low_limit_id 是系统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。

为什么需要: 如果读到了在trx_ids中的，或者大于low_limit_id，什么数据被修改了，那就要回滚undo读历史版本

up_limit_id 作用就是方便比较，小于up_limit_id 的就可以直接读

规则

trx_ids ： [up_limit_id … ] low_limit_id

规则：

快照读：如果改事务读到的行记录的trx_id>=low_limit_id || trx_id in trx_ids , 说明这个行版本在事务创建后被修改过，就需要沿着Undo一直倒退到事务创建时的版本：trx_id<low_limit_id || trx_id not in trx_ids 。
当前读：必须读取最新版本，也就是直接读到的数据

步骤判断：

如果被访问版本的trx_id属性值与ReadView中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值小于ReadView中的 up_limit_id 值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于或等于ReadView中的 low_limit_id 值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在ReadView的 up_limit_id 和 low_limit_id 之间，那就需要判断一下trx_id属性值是不是在 trx_ids 列表中。
- 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。
- 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

再根据生成规则，READ COMMITTED(每次读生成) REPEATABLE READ(事务生成时生成)，这两种隔离级别都实现了。

幻读解决

**快照读：**插入的数据一定是活跃的事务或未来的事务：trx_id>=low_limit_id || trx_id in trx_ids ，也不能倒退版本，根据规则就被MVCC直接忽略了

**当前读：**MVCC无法解决。A查询id>3,B插入id=6，A更新id=6(当前读)，至此之后A都会读到幻影记录6.

解决办法：在A查询id>3时，自动添加间隙锁(RR并且有索引)。

MVCC 幻读问题

间隙锁原理

事务总结

原子性： undo日志实现回滚，保证操作原子性

**隔离型：**读-读、读-写（锁或者 MVCC+锁）、写-写(锁)

持久性： redu日志实现，数据持久化前，先把操作持久化

目标：一致性： 数据从一个 合法性状态 变换到另外一个 合法性状态 。

其他

字节数据库优化慢 SQL 分析与优化

InnoDB

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Plvt8zWk-1681129040684)(null)]

回表查询：先根据筛选条件在普通索引上定位id，再拿id再聚集索引上定位行

EXPLAIN SELECT id, name FROM user WHERE name='shenjian';
EXPLAIN SELECT id, name, sex FROM user WHERE name='shenjian'; 需要回表

limit: 先查询全部数据，再进行过滤优化

（全部sn='XD12345678'的数据要回表）
SELECT o1.* FROM orders WHERE sn='XD12345678' LIMIT 10000,10 

（只回表10条数据）
SELECT o1.* FROM orders o1
INNER JOIN (SELECT id FROM orders WHERE sn='XD12345678' LIMIT 10000,10) o2
ON o1.id = o2.id;

不使用子查询：先查外表，外表大很慢

IN代替or：IN会将数组先排序，然后用二分去查找

少用select *

where groupby having 先where过滤

exists和in区别，要用小表驱动大表，因为是以驱动表建立for循环　被驱动表走索引

exists用外表驱动内表，拿一条条外表数据去内标查       适合外小内大
in反过来，先查询内表，再拿数据去外表走索引
子查询in exists:https://blog.csdn.net/wpc2018/article/details/122948692

join的驱动

1）当连接查询没有where条件时
left join 前面的表是驱动表，后面的表是被驱动表
right join 后面的表是驱动表，前面的表是被驱动表
inner join / join 会自动选择表数据比较少的作为驱动表
straight_join(≈join) 直接选择左边的表作为驱动表（语义上与join类似，但去除了join自动选择小表作为驱动表的特性）

2）当连接查询有where条件时，带where条件的表是驱动表，否则是被驱动表