MySQL笔记

0. MySQL性能优化

性能优化调整的参数：

最大连接数

max_connections 1600——>5000——>10000———>15000

max_used_connections / max_connections * 100% （理想值≈ 85%）

该参数在服务器资源够用的情况下应该尽量设置大，以满足多个客户端同时连接的需求

设置事务等待锁的超时时间

当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒

慢查询阈值

set global long_query_time=1; //慢查询阈值

只要你的SQL实际执行时间超过了这个阈值，就会被记录到慢查询日志里面。这个阈值默认是10s，线上业务一般建议把long_query_time设置为1s，如果某个业务的MySQL要求比较高的QPS，可设置慢查询为0.1s。发现慢查询及时优化或者提醒开发改写。一般测试环境建议long_query_time设置的阀值比生产环境的小，比如生产环境是1s，则测试环境建议配置成0.5s。便于在测试环境及时发现一些效率低的SQL

慢查询优化

跨库 join、跨库聚合查询

创建 - 循环定时执行事件

event_scheduler = on // 默认为off

定时时间提前创建表，分库分表

分库分表

分库目的是减轻单台MySQL实例存储压力及可扩展性，而分表是解决单张表数据过大以后查询的瓶颈问题

水平分表：拆分表数据（如设备库：水平分表，按月 或 日）

垂直分表：拆分表字段

加入缓存

sql开发优化

索引

使用到or的改写为in

1. 索引

1.1 联合索引的最左匹配

https://blog.csdn.net/u014453898/article/details/113748259

顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where 子句 中使用最频繁的一列放在最左边。

最左前缀匹配原则，非常重要的原则，MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d 是用不到索引的，如果建立 (a,b,d,c)的索引则都可以用到，a,b,d 的顺序可以任意调整。 =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任 意顺序，MySQL 的查询优化器会帮你优化成索引可以识别的形式

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

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

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

• where a=1；
• where a=1 and b=2 and c=3；
• where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

• where b=2；
• where c=3；
• where b=2 and c=3；

上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的

1.2 索引失效 慢查询优化

user_delete_status有索引

select count(*) as number from user_region where openid='%s' and is_valid=1 and %s and state in(%d,%d,%d,%d) and (user_delete_status=0 or user_delete_status=2)     //索引失效

虽然命中了索引，但是or会全表扫描，为了减少扫描表的行数

1.2.1 范围查询索引是否失效

create_time有创建索引 index_create_time

explain select * from device_order_202305 where create_time > ‘2023-05-10 12:00:00’

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	device_order_202305		range	index_create_time	index_create_time	5		1361570	100	Using index condition

但是 explain select * from device_order_202305 where create_time > ‘2023-05-10 00:00:00’ 不会走索引

结论是：查询数据条数约占总条数一定比例(一般是30%)以下时才能够使用到索引

1.2.2 索引失效的场景

https://segmentfault.com/a/1190000021464570

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
• 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

EXPALIN 看到type列的值为ALL，意味着MYSQL使用全表扫描解析查询。MYSQL 使用全表扫描去解析查询通常有如下几种条件:

• 该表非常小，全表扫描比索引查找要快。对于少于10行和短行长度的表来说，这很常见。
• ON 或 where条件句中不存在可用的索引约束。简单理解为条件句中根本不包含索引列或者包含索引列但是由于条件限制导致索引失效。
• 将索引列与常量值做比较，并且MYSQL 已经基于索引树计算出这个常量值在表数据中存在的概率很大以至于全表扫描更快

1.2.3 explain查看执行计划

对于执行计划，参数有：

• possible_keys 字段表示可能用到的索引；
• key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
• key_len 表示索引的长度；
• rows 表示扫描的数据行数。
• type 表示数据扫描类型，我们需要重点看这个。

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

• All（全表扫描）；
• index（全索引扫描）；
• range（索引范围扫描）；
• ref（非唯一索引扫描）；
• eq_ref（唯一索引扫描）；
• const（结果只有一条的主键或唯一索引扫描）。

range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式

eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中

除了关注 type，我们也要关注 extra 显示的结果。

这里说几个重要的参考指标：

• Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候， 这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
• Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现
• Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错
• Using index condition，说明使用了索引下推，如下（age和reward是联合索引）：

select * from t_user  where age > 20 and reward = 100000;

1.2.4 慢查询日志

启动和设置慢查询日志

默认情况下，慢查询日志功能是关闭的。
通过 my.cnf 或者 my.ini 文件的 log-slow-queries 选项可以开启慢查询日志。通过 long_query_time 选项来设置时间值，时 间以秒为单位。如果查询时间超过了这个时间值，这个查询诧句将被记录刡慢查询日志。将 log-slow-queries 选项和 long_query_time 选项加入刡 my.cnf 或者 my.ini 文件的[mysqld]组中，形式如下:
# my.cnf(Linux 操作系统下)或者 my.ini(Windows 操作系统下)

 [mysqld]
 log-slow-queries [=DIR \ [filename] ]
 long_query_time=n

1.2.5 如何定位并优化慢查询SQL？

一般有3个思考方向
1.根据慢日志定位慢查询sql
2.使用explain等工具分析sql执行计划
3.修改sql或者尽量让sql走索引

1.2.6 between and

idx_name between and是可以用到索引的

select * from device_order_202305 where id between 1 and 100000

在 SQL 中，使用 BETWEEN 和 进行查询时，如果使用了索引，索引的效率可能会受到影响。这是因为 BETWEEN 运算符会在查询时生成一个范围，而索引可能不能很好地支持范围查询。

为了提高查询效率，可以使用其他查询方法，如使用 >= 和 <= 运算符，或在数据表上创建多列索引

注意：between本身是包含等于的，不会索引失效！！！

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

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

1.4 B+树

B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次

1.4.1 B+树和B树比较

1. B+ 树所有叶子节点间还有一个链表进行连接，这种设计对范围查找非常有帮助
2. B+ 树的插入和删除效率更高
3. B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下(每个节点大小是固定的 16KB)，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少

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

1.5 优化索引的方法

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

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

1.5.1 覆盖索引优化

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作

1.5.2 主键索引最好是自增的

我们在建表的时候，都会默认将主键索引设置为自增的，具体为什么要这样做呢？又什么好处？

InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率

1.5.3 索引最好设置为 NOT NULL

为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：

• 第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。

• 第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式 (opens new window)中**至少会用 1 字节空间存储 NULL 值列表

1.6 索引类型

1.6.1 聚簇索引

一句话：聚簇索引是一种数据存储方式，B+树的叶子节点中存放的就是整张表的行记录数据；与聚簇索引相对的是二级索引，其叶子节点存放的是主键值，而不是实际数据。

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键；
• 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作「回表」，也就是说要查两个 B+ 树才能查到数据。不过，当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」，也就是只需要查一个 B+ 树就能找到数据

InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分；

我们日常工作中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需找主键索引的二级索引，现在找到主键索引再通过主键索引找数据

MySQL数据库中innodb存储引擎，B+树索引可以分为聚簇索引（也称聚集索引，clustered index）和辅助索引（有时也称非聚簇索引或二级索引，secondary index，non-clustered index）。这两种索引内部都是B+树，聚集索引的叶子节点存放着一整行的数据。

Innobd中的主键索引是一种聚簇索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。

Innodb使用的是聚簇索引，MyISam使用的是非聚簇索引

1.6.2 覆盖索引

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引(主键)查询获得，可以避免回表的操作

比如（id是主键）：

select id from product where product_no = '0002';

1.7 索引总结

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YGD8vtwu-1687315667630)(https://cdn.xiaolincoding.com/gh/xiaolincoder/mysql/%E7%B4%A2%E5%BC%95/%E7%B4%A2%E5%BC%95%E6%80%BB%E7%BB%93.drawio.png)]

2. 数据库事务

https://xiaolincoding.com/mysql/transaction/mvcc.html#%E4%BA%8B%E5%8A%A1%E7%9A%84%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E6%9C%89%E5%93%AA%E4%BA%9B

2.1 事物的特性ACID

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

不过并不是所有的引擎都能支持事务，比如 MySQL 原生的 MyISAM 引擎就不支持事务，也正是这样，所以大多数 MySQL 的引擎都是用 InnoDB。

事务看起来感觉简单，但是要实现事务必须要遵守 4 个特性 ACID：

• 原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样，就好比买一件商品，购买成功时，则给商家付了钱，商品到手；购买失败时，则商品在商家手中，消费者的钱也没花出去。
• 一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。比如，用户 A 和用户 B 在银行分别有 800 元和 600 元，总共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后，最后的结果是用户 A 还有 600 元，用户 B 有 800 元，总共 1400 元，而不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况（该情况，用户 A 和 B 均为 600 元，总共 1200 元）。
• 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。也就是说，消费者购买商品这个事务，是不影响其他消费者购买的。
• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

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

这次将重点介绍事务的隔离性，这也是面试时最常问的知识的点。

为什么事务要有隔离性，我们就要知道并发事务时会引发什么问题

2.2 并行事务会引发什么问题？

MySQL 服务端是允许多个客户端连接的，这意味着 MySQL 会出现同时处理多个事务的情况。

那么在同时处理多个事务的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。

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

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

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

2.3 事务的隔离级别

SQL 标准提出了四种隔离级别来规避这些现象(脏读，不可重复读， 幻读 )，隔离级别越高，性能效率就越低，这四个隔离级别如下：

• 读未提交（*read uncommitted*），指一个事务还没提交时，它做的变更就能被其他事务看到；
• 读提交（*read committed*），指一个事务提交之后，它做的变更才能被其他事务看到；
• 可重复读（*repeatable read*），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；(可重复读：只能看见启动事务时的数据)
• 串行化（*serializable* ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

这四种隔离级别具体是如何实现的呢？

• 对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
• 对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
• 对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，*大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View
• 「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
• 「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

2.3.1 读提交

读已提交/读未提交 级别下，如果事务A的update语句要修改的记录已经被其他事务加了X锁，事务A就会读取该记录版本链的最新已提交版本，并判断该版本是否与update语句中的搜索条件相匹配，如果不匹配则不对其加锁（不对其修改，跳到下一条记录），匹配则对其加锁（然后陷入阻塞，待其他事务解锁后对该记录进行修改），这就是半一致性读。

半一致性读可以避免update读到where不匹配的记录时被阻塞的情况，从而提高写写之间的效率。

例子9：有两个读已提交的事务T1、T2

T1执行了当前读，未提交

SELECT * FROM hero where number = 8 FOR UPDATE;

此时聚簇索引的记录8被加了X记录锁。

T2执行update语句

UPDATE hero SET name = 'cao曹操' where number >= 8 AND number < 20 AND country != '魏';

扫描区间在[8, 20)，T2不会先对记录8加锁，而是先查记录8的最新已提交版本到server层，该版本的country是’魏’，不满足T2的update条件，因此server层会放弃让事务T2对记录8上锁也不会修改记录8。如此一来，T2就避免被阻塞从而提高了并发效率。

换做是 可重复读和串行化 的情况下，无论如何T2都会尝试对曹操记录加锁因而被阻塞。

半一致性读让写写在某些特殊场景下可以并发进行，虽然没有产生脏写，但相当于打了擦边球

2.4 Mysql是怎么解决幻读的

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了，详见这篇文章 (opens new window)），解决的方案有两种：

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题

2.5 为什么互联网公司选择使用 RC

https://www.zbpblog.com/blog-392.html

提升并发

• 不会有Gap Lock和 Next-Key Lock ，只对要修改的记录添加行级锁
• RC 还支持"半一致读"，可以大大的减少了更新语句时行锁的冲突；对于不满足更新条件的记录，可以提前释放锁，提升并发度

减少死锁

• RR这种事务隔离级别会增加Gap Lock和 Next-Key Lock，这就使得锁的粒度变大，那么就会使得死锁的概率增大。

  死锁：一个事务锁住了表A，然后又访问表B；另一个事务锁住了表B，然后企图访问表A；这时就会互相等待对方释放锁，就导致了死锁

当然，这样做也不是完全没有问题，（1）首先使用 RC 之后，就需要自己解决幻读的问题，这个其实还好，很多时候幻读问题其实是可以忽略的，或者可以用其他手段解决。

（2）使用 RC 的时候，不能使用statement格式的 binlog，这种影响其实可以忽略不计了，因为MySQL是在5.1.5版本开始支持row的、在5.1.8版本中开始支持mixed，后面这两种可以代替 statement格式

采用RC的隔离级别，不会有涉及Gap Lock和 Next-Key Lock的死锁问题？

3. 锁

3.1 update没加索引会锁全表吗

当我们要执行 update 语句的时候，确保 where 条件中带上了索引列，并且在测试机确认该语句是否走的是索引扫描，防止因为扫描全表，而对表中的所有记录加上锁(注意不是表锁)。

我们可以打开 MySQL sql_safe_updates 参数，这样可以预防 update 操作时 where 条件没有带上索引列。

如果发现即使在 where 条件中带上了列索引列，优化器走的还是全表扫描，这时我们就要使用 force index([index_name]) 可以告诉优化器使用哪个索引

3.2 Mysql死锁

3.2.1 死锁案例1

Order_no是二级索引，事务 A 在二级索引（INDEX_NAME : index_order）上加的是 X 型的 next-key 锁，锁范围是(1006, +∞]

死锁原因：事务 A 和事务 B 在执行完后 select ... for update 语句后都持有范围为(1006,+∞]的next-key 锁，为了获取插入意向锁，都在等待对方事务的间隙锁释放（因为其它事务释放间隙锁之后，才能获取到插入意向锁）

注意：next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的

但是这个死锁例子中，对于这种范围为 (1006, +∞] 的 next-key lock，两个事务是可以同时持有的，不会冲突。因为 +∞ 并不是一个真实的记录，自然就不需要考虑 X 型与 S 型关系

3.2.2 死锁案例2

事物2 （trx id 717594503）持有 user_profile.user_imei_record的主键上的记录锁，然后请求该表idx_open_op_time索引上的记录锁

事物1 持有idx_open_op_time索引上的记录锁，请求该表主键索引上的记录锁

两者相互死锁

3.2.3 死锁案例3

https://cloud.tencent.com/developer/article/1730535

pk-主键。 id1、id2 两个二级索引

//事物1
UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6247476) AND (id2 = 74354)

//事物2
UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6249219) AND (id2 = 74354)

死锁日志分析后，死锁部分的对应请求数据如下:

// 事物1等待的数据
pk = 7479109 and id2 = 74354 

// 事物2等待的数据
where pk = 7480931 and id1 = 6247476 and id2 = 74354

死锁原因：事物1持有主键锁7480931，等待二级索引id2=74354的锁

​ 事物2持有二级索引id2=74354的锁，等待主键锁7480931

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LDbDCoee-1687315667631)(/Users/kyrieqin/Documents/学习资料/md链接/image-20230529211508532.png)]

3.2.3 如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

• 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

  当发生超时后，就出现下面这个提示：

• 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

  当检测到死锁后，就会出现下面这个提示：

总结：

如果两个事务分别向对方持有的间隙锁范围内插入一条记录，而插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁

1. 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。
2. 调整业务逻辑 SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面。
3. 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。
4. 以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2; 事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。
5. 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。
6. 尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。
7. 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解为多个简单的 SQL。

3.3 record lock

3.4 Gap Lock

非常重要的一点需要大家牢记：Gap Lock 只在 REPEATABLE READ 隔离级别下有效

给一条记录加 Gap Lock，是锁住了这条记录前面的空隙，例如给 id 为 1 的记录加 Gap Lock，锁住的范围是 (-∞,1)

3.5 Next-Key Lock

记录锁、间隙锁与 Next-Key Lock

如果我们既想锁定一行，又想锁定行之间的记录，那么就是 Next-Key Lock 了

Next-Key Lock 的加锁规则：

1. 锁的范围是左开右闭。
2. 如果是唯一非空索引的等值查询，查询结果存在时，Next-Key Lock 会退化成 Record Lock
3. 普通索引上的等值查询，向后遍历时，最后一个不满足等值条件的时候，Next-Key Lock 会退化成 Gap Lock。

3.6 mysql如何加行级锁的

有什么命令可以分析加了什么锁？

select * from performance_schema.data_locks\G

3.6.1 唯一索引等值查询

当我们用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

• 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
• 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

为什么唯一索引等值查询并且查询记录「不存在」的场景下，在索引树找到第一条大于该查询记录的记录后，要将该记录的索引中的 next-key lock 会退化成「间隙锁」？

原因就是在唯一索引等值查询并且查询记录不存在的场景下，仅靠间隙锁就能避免幻读的问题。

• 为什么 id = 5 记录上的主键索引的锁不可以是 next-key lock？如果是 next-key lock，就意味着其他事务无法删除 id = 5 这条记录，但是这次的案例是查询 id = 2 的记录，只要保证前后两次查询 id = 2 的结果集相同，就能避免幻读的问题了，所以即使 id =5 被删除，也不会有什么影响，那就没必须加 next-key lock，因此只需要在 id = 5 加间隙锁，避免其他事务插入 id = 2 的新记录就行了。
• 为什么不可以针对不存在的记录加记录锁？锁是加在索引上的，而这个场景下查询的记录是不存在的，自然就没办法锁住这条不存在的记录

3.6.2 唯一索引范围查询

当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：

• 情况一：针对「大于等于」的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁。
• 情况二：针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：
  • 当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。
  • 当条件值的记录在表中，如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；如果「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁

update 语句的 where 条件使用了唯一索引，那么 next-key 锁会退化成记录锁，也就是只会给一行记录加锁

3.6.3 非唯一索引等值查询

非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，同时会对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁

针对非唯一索引等值查询时，查询的记录存不存在，加锁的规则也会不同：

• 当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
• 当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁**

针对插入锁边界值问题，比如(二级索引age)退化成间隙锁后的范围是 (22, 39)，是否能插入age=22、39的记录，需要分析其二级索引树上的插入位置，如果下一条记录没有间隙锁，则插入成功；否则阻塞

3.6.4 非唯一索引范围查询

非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况，也就是非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁

问题：在 age >= 22 的范围查询中，明明查询 age = 22 的记录存在并且属于等值查询，为什么不会像唯一索引那样，将 age = 22 记录的二级索引上的 next-key 锁退化为记录锁？

因为 age 字段是非唯一索引，不具有唯一性，所以如果只加记录锁（记录锁无法防止插入，只能防止删除或者修改），就会导致其他事务插入一条 age = 22 的记录，这样前后两次查询的结果集就不相同了，出现了幻读现象

3.6.5 没有加索引的查询

前面的案例，我们的查询语句都有使用索引查询，也就是查询记录的时候，是通过索引扫描的方式查询的，然后对扫描出来的记录进行加锁。

如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。

因此，在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

3.7 意向锁

意向锁是表级锁 IX

接着，说说意向锁。

• 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
• 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（*lock tables … read*）和独占表锁（*lock tables … write*）发生冲突。

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁

3.8 Mysql的一些参数

innodb_deadlock_detect = on    // 默认开启

innodb_lock_wait_timeout = 5

innodb_rollback_on_timeout = on

 sql_safe_updates = on     // 默认关闭，打开后可以预防 update 操作时 where 条件没有带上索引列

4.日志

4.1 undo log 回滚日志

undo log 是一种用于撤销回退的日志。在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚

undo log 还有一个作用，通过 ReadView + undo log 实现 MVCC（多版本并发控制）

4.2 redo log 重做日志

作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 mysql 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性

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

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了

4.3 binlog

• binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；

4.3 binlog和redo log的区别

• binlog 用于备份恢复、主从复制；

• redo log 用于掉电等故障恢复。

如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。

因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。

binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。

4.4 MySQL主从复制

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bE8UCqko-1687315667632)(https://cdn.xiaolincoding.com/gh/xiaolincoder/mysql/how_update/%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6%E8%BF%87%E7%A8%8B.drawio.png?image_process=watermark,text_5YWs5LyX5Y-377ya5bCP5p6XY29kaW5n,type_ZnpsdHpoaw,x_10,y_10,g_se,size_20,color_0000CD,t_70,fill_0)]

在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

为什么需要 Buffer Pool？

MySQL 的数据都是存在磁盘中的，那么我们要更新一条记录的时候，得先要从磁盘读取该记录，然后在内存中修改这条记录。那修改完这条记录是选择直接写回到磁盘，还是选择缓存起来呢？

当然是缓存起来好，这样下次有查询语句命中了这条记录，直接读取缓存中的记录，就不需要从磁盘获取数据了。

为此，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能

面试常问

1. mysql如何保证acid

2. redo log和undo log区别

3. redo log和undo log是如何生成的（这块细节忘了，只说了先写内存，然后再刷盘

   这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

   • redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
   • undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

4. mysql默认隔离级别

   3、如何实现可重复读

   4、如何解决幻读

   5、间隙锁和nextkey锁

   6、mysql锁是锁的什么（索引）

   7、mysql的索引结构，有什么优点

   8、怎么实现读写分离

   9、主从复制是怎么实现同步的，答传bin log文件，后续数据更新怎么同步，答mysq不了解，但我知道redis主从复制后续是通过一个复制缓存区来记录新增的命令，通过发送这些命令实现同步

   1. MySQL的主从是什么原理