一、问题解析 1、 Mysql 的事务隔离级别 Mysql 有四种事务隔离级别，这四种隔离级别代表当存在多个事务并发冲突时，可能出现的脏读、不可重复读、幻读的问题。其中 InnoDB 在 RR 的隔离级别下，解决了幻读的问题。  2、 什么是幻读？ 那么， 什么是幻读呢？ 幻读是指在同一个事务中，前后两次查询相同的范围时，得到的结果不一致 （我们来看这个图） 第一个事务里面我们执行了一个范围查询，这个时候满足条件的数据只有一条 第二个事务里面，它插入了一行数据，并且提交了 接着第一个事务再去查询的时候，得到的结果比第一查询的结果多出来了一条数据。  所以，幻读会带来数据一致性问题。 3、 InnoDB 如何解决幻读的问题 InnoDB 引入了间隙锁和 next-key Lock 机制来解决幻读问题，为了更清晰的说明这两种锁，我举一个例子： 假设现在存在这样（图片）这样一个 B+ Tree 的索引结构，这个结构中有四个索引元素分别是：1、4、7、10。  当我们通过主键索引查询一条记录，并且对这条记录通过 for update 加锁（请看这个图片）  这个时候，会产生一个记录锁，也就是行锁，锁定 id=1 这个索引（请看这个图片）。 被锁定的记录在锁释放之前，其他事务无法对这条记录做任何操作。 前面我说过对幻读的定义： 幻读是指在同一个事务中，前后两次查询相同的范围时，得到的结果不一致！注意，这里强调的是范围查询，也就是说，InnoDB 引擎要解决幻读问题，必须要保证一个点，就是如果一个事务通过这样一条语句（如图）进行锁定时。  另外一个事务再执行这样一条（显示图片）insert 语句，需要被阻塞，直到前面获得锁的事务释放。  所以，在 InnoDB 中设计了一种间隙锁，它的主要功能是锁定一段范围内的索引记录（如图） 当对查询范围 id>4 and id <7 加锁的时候，会针对 B+树中（4，7）这个开区间范围的索引加间隙锁。意味着在这种情况下，其他事务对这个区间的数据进行插入、更新、删除都会被锁住。  但是，还有另外一种情况，比如像这样（图片）  这条查询语句是针对 id>4 这个条件加锁，那么它需要锁定多个索引区间，所以在这种情况下 InnoDB 引入了 next-key Lock 机制。next-key Lock 相当于间隙锁和记录锁的合集，记录锁锁定存在的记录行，间隙锁锁住记录行之间的间隙，而 next-key Lock 锁住的是两者之和。（如图所示）  每个数据行上的非唯一索引列上都会存在一把 next-key lock ，当某个事务持有该数据行的 next-key lock 时，会锁住一段 左开右闭区间 的数据。因此，当通过 id>4 这样一种范围查询加锁时，会加 next-key Lock，锁定的区间范围是：(4, 7] , (7,10],(10,+∞]  间隙锁和 next-key Lock 的区别在于加锁的范围，间隙锁只锁定两个索引之间的引用间隙，而 next-key Lock 会锁定多个索引区间，它包含记录锁和间隙锁。当我们使用了范围查询，不仅仅命中了 Record 记录，还包含了 Gap 间隙，在这种情况下我们使用的就是临键锁，它是 MySQL 里面默认的行锁算法。 二、问题总结 虽然 InnoDB 中通过间隙锁的方式解决了幻读问题，但是加锁之后一定会影响到并发性能，因此，如果对性能要求较高的业务场景中，可以把隔离级别设置成 RC，这个级别中不存在间隙锁。好了，今天的分享就到这里，如果你在面试中遇到了比较奇葩的问题，欢迎在评论区留言。 ————————————————                   原文链接：https://blog.csdn.net/sinat_53467514/article/details/135273385 

MySQL 是如何解决幻读的一、什么是幻读在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做 幻行二、为什么要解决幻读在高并发数据库系统中，需要保证事务与事务之间的隔离性，还有事务本身的一致性。三、MySQL 是如何解决幻读的如果你看到了这篇文章，那么我会默认你了解了 脏读 、不可重复读与可重复读。多版本并发控制（MVCC）（快照读）多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的。以 InnoDB 为例，每一行中都冗余了两个字断。一个是行的创建版本，一个是行的删除（过期）版本。版本号随着每次事务的开启自增。事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。普通的 select 就是快照读。select * from T where number = 1;原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见的。next-key 锁 （当前读）next-key 锁包含两部分记录锁（行锁）间隙锁记录锁是加在索引上的锁，间隙锁是加在索引之间的。（思考：如果列上没有索引会发生什么？）select * from T where number = 1 for update;select * from T where number = 1 lock in share mode;insertupdatedelete原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的。其他：MySQL InnoDB 引擎 RR 隔离级别是否解决了幻读引用一个 github 上面的评论 地址：Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row就算幻读。a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作），a事务再select出来的结果在MVCC下还和第一次select一样，接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的），a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了，实测在RR级别下确实如此。如果这样理解的话，Mysql的RR级别确实防不住幻读有道友回复 地址：在快照读读情况下，mysql通过mvcc来避免幻读。在当前读读情况下，mysql通过next-key来避免幻读。select * from t where a=1;属于快照读select * from t where a=1 lock in share mode;属于当前读不能把快照读和当前读得到的结果不一样这种情况认为是幻读，这是两种不同的使用。所以我认为mysql的rr级别是解决了幻读的。先说结论，MySQL 存储引擎 InnoDB 隔离级别 RR 解决了幻读问题。如引用一问题所说，T1 select 之后 update，会将 T2 中 insert 的数据一起更新，那么认为多出来一行，所以防不住幻读。看着说法无懈可击，但是其实是错误的，InnoDB 中设置了 快照读 和 当前读 两种模式，如果只有快照读，那么自然没有幻读问题，但是如果将语句提升到当前读，那么 T1 在 select 的时候需要用如下语法： select * from t for update (lock in share mode) 进入当前读，那么自然没有 T2 可以插入数据这一回事儿了。注意next-key 固然很好的解决了幻读问题，但是还是遵循一般的定律，隔离级别越高，并发越低原文链接：https://blog.csdn.net/weixin_34380781/article/details/89560645

并发场景下事务会存在那些数据问题？ 并发场景下mysql会出现脏读、幻读、不可重复读问题； 1. 脏读 dirty read（读到未提交的数据）： A事务正在修改数据但未提交，此时B事务去读取此条数据，B事务读取的是未提交的数据，A事务回滚。 解决办法： 方法1：事务隔离级别设置为：read committed。 方法2：读取时加共享锁（update …lock in share mode)，事务提交才会释放锁，修改时加排他锁（select…for update）。加排它锁后，不能对该条数据再加锁，其它事务即不能查询也不能更改数据。mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁，共享锁下其它用户可以并发读取，查询数据。但不能修改，增加，删除数据。资源共享。  2.不可重复读 Non-Repeatable read（前后多次读取，数据内容不一致）：     A事务中两次查询同一数据的内容不同，B事务间在A事务两次读取之间更改了此条数据。 解决办法： 方法1：事务隔离级别设置为Repeatable read。 方法2：读取数据时加共享锁，写数据时加排他锁，都是事务提交才释放锁。读取时候不允许其他事物修改该数据，不管数据在事务过程中读取多少次，数据都是一致的，避免了不可重复读问题。  3. 幻读 repeatable read（前后多次读取，数据总量不一致）： 在同一事务中两次相同查询数据的条数不一致，例如第一次查询查到5条数据，第二次查到8条数据，这是因为在两次查询的间隙，另一个事务插入了3条数据。 解决办法： 方法1：事务隔离级别设置为serializable ，那么数据库就变成了单线程访问的数据库，导致性能降低很多。 Isolation 属性一共支持五种事务设置，具体介绍如下： DEFAULT： 使用数据库设置的隔离级别 ( 默认 ) ，由 DBA 默认的设置来决定隔离级别 . READ_UNCOMMITTED： 会读到未提交的数据， 出现脏读、不可重复读、幻读 ( 隔离级别最低，并发性能高 )。 READ_COMMITTED： 不会读到未提交的数据，会出现不可重复读、幻读问题（锁定正在读取的行） REPEATABLE_READ ：会出幻读（锁定所读取的所有行） SERIALIZABLE ：保证所有的情况不会发生（锁表） ————————————————                   原文链接：https://blog.csdn.net/qq_42817320/article/details/119905192 

简单来说就是通过mvcc + next-key locks 防止幻读 幻读是什么？ 当前事务读取了一个范围的记录，另一个事务在该范围内插入了新记录，当前事务再次读取该范围内的记录就会发现新插入的记录，这就是幻读 以下MySQL的隔离界别都是可重复读（RR） mvcc与next-key分别在什么情况下起作用？  在快照读的情况下，会通过mvcc来避免幻读 在当前读的情况下，会通过next-key来避免幻读 快照读与当前读 快照读：所有普通的select语句都算快照读，它并不会给表中任何记录做加锁操作，其他事务可以对表中记录做任何改动 当前读：加锁的操作都叫当前读，分为s锁，x锁 共享锁：S锁。在事务要读取一条记录时，需要先获取该记录的S锁  select … lock in share mode 独享锁（排他锁）：X锁。事务要改动一条记录时，需要先获取X锁 select … for update、insert、update、delete S锁与S锁是兼容的；S锁与X锁是不兼容；X锁与X锁也是不兼容。 简单了解下跟防止幻读有关的行级锁 record locks：把当前记录上锁  gap locks：如果当前列具有唯一索引，那么就仅仅是把当前行加锁；只有当前列没有索引或者具有非唯一索引，才会锁定前面的间隙 什么意思呢？ 如下例：  CREATE TABLE `user` (  `id` int NOT NULL,  `score` int DEFAULT NULL,  PRIMARY KEY (`id`) ) ENGINE=InnoDB; insert into user values(1,79),(3,91),(6,59); 事务1    事务2 1    begin; select * from user where score=91 for update;     2        begin; insert into user values(2,98); // 因为gap锁的原因，插入失败 commit; 3    commit；     也就是在 id (1, 3) 之间加x锁 而如果把事务1的查询语句改成 select * from user where id=3 for update; 则前面的间隙不会上锁，事务2会成功插入！ next-key locks：就是record locks跟gap locks的组合，既能保护该条记录，又能防止其他事务插入该记录前面的间隙中。 如上述加x锁的区间就变成了 (1, 3] 简单了解下mvcc 具有三个隐藏字段： DB_TRX_ID：记录最后进行插入、更新操作的事务 DB_ROLL_PTR：滚动指针，指向修改前的记录 DB_ROW_ID：如果没有聚簇索引，该字段会构建聚簇索引（相当于隐藏的自增主键） readview：会记录当前活跃事务的id范围，根据事务id来判断哪个版本是对当前事务可见的 如下例（还是上面表，默认三条数据）：  事务1    事务2 1    begin; update user set score=50 where id=1; update user set score=60 where id=1;     2        begin; select * from user where id=1; //score=79 3    commit;     4        select * from user where id=1; //score=79 commit; ​ 为什么？  ​ 假设事务1的事务id是100  **事务1未提交：**事务2在执行select之前会生成一个readview，活跃的只有事务1，该readview的事务范围就是100，在该范围内都不符合要求。根据滚动指针（DB_ROLL_PTR）找之前的版本，直到事务id小于100，也就是找到事务1开启之前的版本，那时的score就是79 DB_TRX_ID（事务id）    id    score    DB_ROLL_PTR（滚动指针） 1    100    1    60    2 2    100    1    50    3 3    80（肯定小于100）    1    79     事务1提交： 上述的例子是在MySQL默认隔离级别（RR）下，在该隔离级别下，只在第一次select前生成一个readview。在事务1未提交之前已经生成过了，所以搜索到的score还是79。 如果隔离级别是RC，那么第二次select前会再次生成一个readview，那么score就是60 上面内容过一遍后，在回过头来考虑幻读问题，这不就已经解决了嘛！  快照读的情况下，通过mvcc来避免幻读 当前读的情况下，通过next-key来避免幻读 ————————————————              原文链接：https://blog.csdn.net/qq_51535737/article/details/123937193 

 在当今的数据库管理和优化领域，MySQL作为一种广泛使用的关系型数据库管理系统（RDBMS），其性能和稳定性一直是开发者关注的焦点。特别是对于高级开发人员和技术精湛的团队来说，理解并有效管理MySQL中的幻读问题尤为重要。本文旨在深入分析MySQL如何避免幻读，为高级开发者提供一份《面试宝典：MySQL-深度分析如何避免幻读》。  什么是幻读？  幻读是指在一个事务内，同一SELECT语句在不同时间执行，得到不同的结果集时发生的现象[7]。这种现象通常发生在可重复读隔离级别下，因为在这个隔离级别下，事务看到的是一个快照视图，不会看到其他事务插入的数据[1]。  MySQL如何解决幻读？  MySQL通过多种机制来尽量减少或避免幻读的发生。其中，MVCC（多版本并发控制）机制是MySQL避免幻读的核心技术之一。MVCC允许每个事务在其开始时看到的是数据库的一个快照，这样即使在事务执行期间有新的数据插入，也不会影响到当前事务的视图[4]。此外，MySQL还引入了Next-Key锁机制，在当前读情况下使用，进一步提高了避免幻读的能力[9]。  解决幻读的策略  间隙锁：这是一种常用的解决幻读的方法。通过锁定数据之间的间隙，可以防止其他事务在这个间隙内插入新的行，从而避免出现幻读[2]。 一致性非锁定读：这种方法不涉及锁定任何数据，而是通过确保事务的隔离级别足够高来避免幻读。这要求数据库系统能够在保持可重复读的同时，允许事务看到最新的数据变化[2]。 手动加行X锁：在可重复读隔离级别下，通过对SELECT操作手动加行X锁（例如使用SELECT ... FOR UPDATE），可以避免幻读的发生。这是因为行X锁会阻止其他事务对这些行进行插入、更新或删除操作，从而保证了事务的一致性和隔离性[6]。 结论  虽然MySQL在可重复读隔离级别下并不能完全避免幻读的发生，但通过上述策略和技术的应用，可以大大减少幻读的可能性，提高数据库的性能和稳定性。对于高级开发人员和技术精湛的团队而言，深入理解和应用这些机制是提升数据库管理和优化能力的关键。希望本文能为读者提供有价值的参考和指导。  MySQL MVCC机制的工作原理是什么？  MySQL的MVCC（多版本并发控制）机制是一种优化数据库事务处理的技术，它允许在不加锁的情况下进行读操作。在MVCC机制下，每个事务都会看到一个当前版本的数据库状态，这个状态是该事务开始时所有更改都已提交的状态。这样，不同的事务可以同时访问数据库的不同版本，从而避免了锁的竞争，提高了数据库的并发性能。  具体来说，当一个事务开始执行时，它会看到一个快照，这个快照包含了事务开始时刻数据库的状态。即使在这个事务执行期间其他事务对数据进行了修改，这些修改也不会影响到当前事务所看到的快照。因此，当前事务中的读操作不会因为其他事务的写操作而阻塞或出错。这种机制特别适用于可重复读隔离级别的场景，在这种隔离级别下，事务执行过程中看到的数据始终保持与事务启动时一致，有效解决了幻读问题[12]。  简而言之，MySQL的MVCC机制通过为每个事务提供一个独立的视图来实现高并发下的数据一致性，使得多个事务可以在不同的时间点上看到数据库的不同版本，从而避免了锁的竞争，提高了数据库的性能和可用性。  如何在MySQL中实现间隙锁以避免幻读？  在MySQL中实现间隙锁以避免幻读的方法是通过使用next-key lock机制。具体操作步骤如下：  当前读事务开启时，首先给涉及到的行加写锁（行锁），这样做是为了防止其他写操作对这些行进行修改[13]。 然后，给涉及到的行两端加间隙锁（Gap Lock），这样做的目的是为了防止在当前读事务执行期间有新的行被插入到这些行之间，从而避免了幻读的发生[13]。 通过这种方式，可以有效地利用间隙锁来避免幻读的问题，确保读取的数据集是一致性和连续性的。  MySQL中Next-Key锁机制的具体应用和效果如何？  MySQL中的Next-Key锁机制主要用于InnoDB存储引擎，在当前读读（CC）情况下，通过避免幻读来提高读取的准确性。具体来说，当使用当前读读（CC）级别进行事务操作时，如果涉及到对行的读取，InnoDB会使用Next-Key锁机制来确保数据的一致性和准确性。这种机制通过锁定数据页和索引页以及插入点（即行的开始部分），从而在一定程度上避免了幻读的发生[14]。  幻读是指在一个事务中，虽然没有直接访问某个行，但是由于其他事务对该行进行了修改或删除，导致当前事务在后续扫描时发现这些变更，从而产生了本不应该出现在快照中的行。Next-Key锁机制通过锁定数据页和索引页，以及插入点，可以有效地防止这种情况的发生，因为任何对这些被锁定区域的写操作都会被阻塞，直到当前事务完成。这样，即使有新的行被插入到表中，只要这些行不在被锁定的区域内，就不会影响到当前事务的执行，从而减少了幻读的可能性。  总的来说，Next-Key锁机制在MySQL中主要用于提高读取操作的准确性和一致性，尤其是在当前读读（CC）级别下，通过避免幻读来确保事务的一致性。这种机制通过锁定关键的数据结构（数据页、索引页和插入点），有效地控制了对数据的访问和修改，从而提高了数据库的整体性能和可靠性 ————————————————         原文链接：https://blog.csdn.net/weixin_39801169/article/details/136931645 

本文介绍了如何通过提高事务隔离级别、利用特定并发控制机制（如MVCC和Next-KeyLocks），以及在应用层进行控制来解决幻读问题。数据库系统特性、业务需求和性能一致性是选择策略的关键因素。 解决幻读问题通常涉及调整数据库事务的隔离级别或采用特定的并发控制机制。幻读是指在一个事务内，按照相同的查询条件多次执行查询时，原本不存在的行（phantom rows）在事务后续的查询中突然出现。以下是一些解决幻读问题的方法：  1. **提高事务隔离级别**：    - **可重复读（Repeatable Read, RR）**：许多数据库系统（如MySQL的InnoDB存储引擎）的默认隔离级别。在RR级别下，事务开始时会创建一个快照视图，事务内后续的读操作都基于这个视图，看到的是事务开始时已提交的数据版本。这可以防止其他事务的更新影响当前事务内数据的一致性，避免了不可重复读。然而，RR级别本身并不足以防止幻读。    - **序列化（Serializable）**：这是最高的隔离级别，提供了最严格的事务隔离。在序列化隔离级别下，事务的执行顺序被强制为串行或等效于串行，从而完全避免了幻读以及其他并发问题（如脏读、不可重复读）。但这种级别的代价是并发性能显著降低，因为需要更严格地锁定数据和/或采用其他复杂的并发控制机制。  2. **使用特定的并发控制机制**：    - **MVCC（多版本并发控制）与附加锁**：      在支持MVCC的数据库系统（如InnoDB）中，即使在可重复读隔离级别下，通常还需要额外的并发控制机制来防止幻读。例如，InnoDB在RR级别下使用Next-Key Locks，这是一种结合了行锁和间隙锁的机制。当事务进行范围查询时，不仅锁定查询范围内已有的行，还锁定这些行之间的间隙，防止其他事务在此间隙插入新行，从而解决了幻读问题。     - **间隙锁（Gap Locks）与Next-Key Locks**：      一些数据库系统在可重复读隔离级别下，会针对范围查询使用间隙锁或Next-Key Locks。间隙锁锁定的是两个相邻索引记录之间的“间隙”，防止其他事务在该间隙内插入新行。Next-Key Locks则是行锁与间隙锁的组合，既锁定索引记录本身，也锁定其前后的间隙。     - **逻辑序列化（Logical Serialization）**：      一些现代数据库系统（如PostgreSQL）使用逻辑序列化（如Serializable Snapshot Isolation,SSI）或乐观并发控制（Optimistic Concurrency Control, OCC）等技术，在保持较高并发性能的同时，通过事务冲突检测和回滚来达到序列化级别的效果，从而避免幻读。  3. **应用层控制**：    对于某些特定应用场景，如果业务逻辑允许，也可以在应用程序层面采取措施来规避幻读问题。例如，通过在事务开始时获取一个全局唯一的序列号或时间戳，作为后续查询的附加条件，确保只有在事务开始后插入的新行才会被看到，从而避免幻读。这种方法需要对业务有深入理解，并可能增加应用开发的复杂性。  综上所述，解决幻读问题主要有以下途径： - 提升事务隔离级别至序列化，但需权衡并发性能的下降。 - 保持在可重复读隔离级别，结合使用MVCC以及如Next-Key Locks或Gap Locks等附加的并发控制机制。 - 应用层干预，根据业务特点设计特定的查询条件或控制逻辑来规避幻读。  选择哪种方法取决于具体的数据库系统特性、业务需求以及对性能和数据一致性的权衡。在实际应用中，应仔细评估并发访问模式和性能要求，合理配置事务隔离级别和使用适当的并发控制手段。 ————————————————                原文链接：https://blog.csdn.net/weixin_43803780/article/details/138047140 

今天分享 mysql中幻读出现的原因及解决方案： 一、首先明确什么是幻读：​  事务A按照一定条件进行数据读取，期间事务B插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取操作修改时，发现了事务B新插入的数据称之为幻读。 二、幻读出现的场景： 1、如果事务中都是用快照读，那么不会产生幻读的问题 2、快照读和当前读一起使用的时候就会产生幻读 三、实验验证  1、采用mysql 5.6之后的版本和 默认的隔离级别 RR ，启动A、B两个事务对比，阿拉伯数字递增代表事务执行的时间顺序，比如 1,2,3,4.......，模拟数据库执行(前提是数据库有两条数据)， 假设有如下业务场景：  | 时间 | 事务1                                                                                                        | 事务2                                               | | ---- | --------------------------------------------------                                              ---------- | ------------------------------------------- | |      | begin；                                                                                               |                                                                 | | T1   | select * from user where age = 20;2个结果                                       |                                                               | | T2   |                                                                                                            | insert into user values(25,'25',20);commit; | | T3   | select * from user where age =20;2个结果                                        |                                                                    | | T4   | update user set name='00' where age =20;此时看到影响的行数为3 |                                                                  | | T5   | select * from user where age =20;三个结果                                        |                                                                 |  2、执行流程如下： 1)、T1时刻读取年龄为20 的数据，事务1拿到了2条记录 2)、T2时刻另一个事务插入一条新的记录，年龄也是20  3)、T3时刻，事务1再次读取年龄为20的数据，发现还是2条记录，事务2插入的数据并没有影响到事务1的事务读取  4)、T4时刻，事务1修改年龄为20的数据，发现结果变成了三条，修改了三条数据  5)、T5时刻，事务1再次读取年龄为20的数据，发现结果有三条，第三条数据就是事务2插入的数据，此时就产生了幻读情况  此时大家需要思考一个问题，在当下场景里，为什么没有解决幻读问题？  其实通过前面的分析，大家应该知道了快照读和当前读，一般情况下select * from ....where ...是快照读，不会加锁，而 for update,lock in share mode,update,delete都属于当前读，**如果事务中都是用快照读，那么不会产生幻读的问题，但是快照读和当前读一起使用的时候就会产生幻读**。 3、模型结果如下图： 分析，select 执行的是快照读（某个版本的数据，Read View）,而update 执行的是当前读（最新的数据，即最新的Read View,因此更新了三条数据）。这就是幻读的场景，它是不可重复读的一个子类。 3、怎么解决幻读：加间隙锁（如果当前和快照读均存在的情况下）。采用mysql 5.6之后的版本和 默认的隔离级别 RR ，启动A、B两个事务对比，阿拉伯数字递增代表事务执行的时间顺序，比如 1,2,3,4.......，模拟数据库执行(前提是数据库有两条数据)  结果模型如下图： 此时，可以看到事务B被阻塞了，需要等待事务A提交事务之后才能完成，其实本质上来说采用的是间隙锁的机制解决幻读问题,因此可以发现，MVCC + 锁 共同实现隔离级别。 ————————————————                    原文链接：https://blog.csdn.net/nandao158/article/details/116007366 

1、事务隔离级别：         在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多或者少的那一行被叫做幻行，也就是说当一个事务在进行读取数据的时候，其他事务对该数据进行了改变。在高并发数据库系统中，需要保证事务与事务之间的隔离性，还有事务本身的一致性。          脏读：比如A事务读取到了B事务还没有提交的数据，因为什么原因B事务回滚了，那么A事务读取的数据和数据库中的数据不同，也就是读到了其他事务没有提交的数据。          读取已提交会产生不可重复读：比如A事务读取数据，开始是100，事务还没有提交，此时B事务对这个数据修改为80，然后提交了事务，此时A事务再次读取就是80，因为B已经提交了，但是两次的结果不一样，就产生了不可重复读的现象。         可重复读：在可重复读中，该sql第一次读取到数据后，就将这些数据加锁（悲观锁），其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据。          Mysql中读的操作是通过MVCC实现的，如果A事务读取数据，B事务修改了数据提交了，因为MVCC是根据事务粒度生成的ReadView所以不会读取到B事务修改的数据。          比如：A事务读取数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。 但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来实现。         Mysql的默认隔离级别是可重读，可产生幻读的：幻读仅专指  新插入的行，且在当前读的情况下。         比如：A事务中读取有一条数据是100，B事务中修改为了80，且插入了一条数据，变成了两条事务提交。此时A事务读取的依旧是100，一条数据，但是A事务往里边插入第二条数据的时候，id和B事务插入的数据一样，id是一样的，此时A事务插入不进去，主键冲突，但是查询的时候依旧差不到，这就是幻读的一个例子。         比如：A事务中读取大于5的数据有3条，B事务插入了一条，提交，A事务再次查询大于5的数据发现是4条，就产生了幻读。 2、MVCC以及RC、RR：         MVCC Multi-Version Concurrency Control 就是一个多版本并发控制，即多个不同版本的数据实现并发控制的技术，其基本思想是为每次事务生成一个新版本的数据，在读数据时选择不同版本的数据即可以实现对事务结果的完整性读取，从而不用竞争锁，提高性能。这种读是属于快照读，不是当前读，当前读需要加锁，悲观锁。         重要：MVCC 使每个连接到数据库的读者，在某个瞬间看到的是数据库的一个ReadView，不同的隔离级别生成快照的粒度不同，读取已提交生成ReadView的粒度是以每个select单位，所以A事务前后生成的ReadView不同。可重复读生成ReadView的粒度是以事务为粒度生成的，同一个事务只会生成一个ReadView所以避免了可重复读的问题。         当一个 MVCC 数据库需要更新一个一条数据记录的时候，它不会直接用新数据覆盖旧数据，而是将旧数据标记为过时（obsolete）并在别处增加新版本的数据。这样就会有存储多个版本的数据，但是只有一个是最新的。这种方式允许读者读取在他读之前已经存在的数据，即使这些在读的过程中半路被别人修改、删除了，也对先前正在读的用户没有影响。这种多版本的方式避免了填充删除操作在内存和磁盘存储结构造成的空洞的开销，但是需要系统周期性整理（sweep through）以真实删除老的、过时的数据。  3、Mysql在的当前读和快照读：         快照读：读取的是记录数据的可见版本（可能是过期的数据），不用加锁，select时为快照读。MVCC实现。不需要竞争锁。         当前读：读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。update、insert、delete 都是当前读。排它锁 4、Mysql的默认隔离级别是可重读，但是可重复会产生幻读，Mysql是如何实现避免幻读的呢？幻读只存与插入，且是当前读                 在Mysql的Innodb引擎中默认开起了间隙锁，幻读是通过间隙锁+行锁方式解决的。  5、Mysql的锁类型：         锁存在的意义就是为了保证事务的隔离性，防止并发产生问题，从而保证一致性。         基于属性分类：                 共享锁 S：共享锁又称之为读锁，简称S锁，当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，所以读锁和写锁是互斥的。直到所有的读锁全部释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现不可重读的问题。                  排它锁 X：排它锁又称之为写锁，简称X锁。当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁，排它锁的目的是在数据修改的时候，不允许其他事务同时修改，也不允许其他事务读取，避免了脏读数据问题。         基于锁的状态分类：                 意向共享锁：                 意向排它锁：         基于粒度分类：重点                 表级锁：表锁指的是对整个表进行加锁，当下一个事务访问该表的数据时，必须等前一个事务释放了锁才能进行对表进行访问。粒度大，并发小。                 行级锁：行锁指上锁的时候锁住的是某一行或多行，其他事务访问同一张表时，只有被锁住的记录不能访问，其他记录可以访问。粒度小，并发高。                 记录锁：加锁后只对表中的一行记录加上了锁，也就是精准查找，条件字段是唯一索引。                  间隙锁：间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻id之间出现空隙则会形成一个区间，遵循左开右闭原则。间隙锁之间不会冲突。间隙锁是在可重复读隔离级别下才会生效的。                 临建锁： 6、ACID的原理：【吊打面试官】大厂面试必问的MySQL事务ACID原理，终于有人讲清楚了！_哔哩哔哩_bilibili         mysql将数据存储到数据库之前都是先通过日志的方式来存储数据，因为日志的存储是顺序存储，可以通过偏移量来控制或者查找，而数据库的持久化存储，是见缝插针，这样可能最大化利用磁盘空间，存储完还需要记录数据的地址，所以相比日志存储比较慢。          原子性的实现：通过Redo log和Undo log，重做和回滚。如果事务提交了，那么就会执行Redo log写到数据库，如果没有提交就会执行undo log。         持久性：redo log实现：   7、Mysql的日志                        日志系统主要有redo log(重做日志)和binlog(归档日志)。redo log是InnoDB存储引擎层的日志，binlog是MySQL Server层记录的日志， 两者都是记录了某些操作的日志(不是所有)自然有些重复（但两者记录的格式不同）。          redo log和binlog区别  redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。 redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑 redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。 binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。 ————————————————                         原文链接：https://blog.csdn.net/weixin_43059299/article/details/118851604 

一、幻读的定义 幻读是什么 同一事务中，对同一条件进行多次查询，由于在多次查询的过程中 其他事务对数据进行插入或则删除操作，导致获取的数据不一致。就像出现了 幻觉一样。 举一个例子： 事务T1的查询： SELECT * FROM products WHERE price > 100; 返回一组商品记录，这时事务T1还未提交。 事务T2的插入： 在T1查询之后，另一个事务T2插入了一些新的商品，它们的价格也大于100。 事务T1的再次查询： T1再次执行 SELECT * FROM products WHERE price > 100;，此时它返回的结果集比第一次查询更大，因为T2插入的新商品也满足条件。 幻读的产生原因 原因： 1.事务的隔离级别太低了导致 2.对表进行了行的插入和行的删除操作  二、 解决幻读 方案一 提高隔离级别 将事务隔离级别提升到SERIALIZABLE，但是这种方案正如名字一样 串行化，就是让多个事务穿行的执行。这种方式虽然可以解决幻读问 题，但是效率太低了，一般不推荐使用。 方案二 MVCC和next-key lock MVCC的定义：  MVCC 是一种并发控制机制，用于在多个并发事务同时读写数据库时 保持数据的一致性和隔离性。它是通过在每个数据行上维护多个版本 的数据来实现的。当一个事务要对数据库中的数据进行修改时， MVCC 会为该事务创建一个数据快照，而不是直接修改实际的数据 行。 通俗一点说，对数据进行修改时，数据库并不会删除以前的数据，而是将数据保存起来，以实现对旧数访问。数据的保存，其实是基于undo log并不是保存真正的数据。至于为什么这么做，第一，通过对数据的undo操作，我们也能拿到数据。第二，这种方式可以为我们节约大量的内存，也不必为了维护以前的数据额外花费其他资源，因为数据库原本就保存了undo log，只需要通过一个字段保存该行undo log的地址。  下图就是MVCC中，数据库维护行MVCC中行数据的多版本维护。每一条数据都有一个回滚指针(DB_ROLL_PTR)用来记录回归日志的地址。  InnoDB实现MVCC： 离不开read view， undo log，隐藏字段这三个属性  名称    作用    结构 read view    开启事务时生成，用来记录当前事务的事务id，判断事务是否对当前事务可见    m_low_limit_id：大于等于这个 ID 的事务均不可见 m_up_limit_id: 小于这个 ID 的事务均可见 m_creator_trx_id:创建该 Read View 的事务ID m_low_limit_no：事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge m_ids： 创建 Read View 时的活跃事务列表，不包括当前事务 如下图1-3 undo log    当事务不可见时，通过readview和隐藏字段找到可见事务的事务id，并通过undo log生成可见事务的行数据     隐藏字段    InnoDB存储引擎为每行添加的，用来维护行的多个版本数据    DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。 DB_ROLL_PTR（7字节） 回滚指针 ，指向该行的 undo log 。如果该行未被更新，则为空 DB_ROW_ID（6字节) 如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引  图1-3  数据可见性算法： 在讲算法之前，我先来梳理一下readview各个字段是如何创建的. 数据库系统首先会找到当前处于活跃事务，并将他们的id记录在m_ids中，然后根据m_ids中的id来生成m_low_limit_id（大于m_ids中最大值+1）和m_up_limit_id（小于m_ids中最小值）。至于m_creator_trx_id字段，在开启事务时，系统就为该事务生成了一个事务id。  如果记录 DB_TRX_ID < m_up_limit_id，那么表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之前就提交了，所以该记录行的值对当前事务是可见的。 如果 DB_TRX_ID >= m_low_limit_id，那么表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之后才修改该行，所以该记录行的值对当前事务不可见。跳到步骤 5 m_ids 为空，则表明在当前事务创建快照之前，修改该行的事务就已经提交了，所以该记录行的值对当前事务是可见的 如果 m_up_limit_id <= DB_TRX_ID < m_low_limit_id，表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照的时候可能处于“活动状态”或者“已提交状态”；所以就要对活跃事务列表 m_ids 进行查找（源码中是用的二分查找，因为是有序的） 如果在活跃事务列表 m_ids 中能找到 DB_TRX_ID，表明：① 在当前事务创建快照前，该记录行的值被事务 ID 为 DB_TRX_ID 的事务修改了，但没有提交；或者 ② 在当前事务创建快照后，该记录行的值被事务 ID 为 DB_TRX_ID 的事务修改了。这些情况下，这个记录行的值对当前事务都是不可见的。跳到步骤 5 在活跃事务列表中找不到，则表明“id 为 trx_id 的事务”在修改“该记录行的值”后，在“当前事务”创建快照前就已经提交了，所以记录行对当前事务可见 在该记录行的 DB_ROLL_PTR 指针所指向的 undo log 取出快照记录，用快照记录的 DB_TRX_ID 跳到步骤 1 重新开始判断，直到找到满足的快照版本或返回空 举个例子：  如上图， 1.假设当前处于T4时刻，103事务会生成一个read view，当前的事务id为事务103，此时该事务的m_ids为[101，102]，因此m_low_limit_id = 104 ，则 m_up_limit_id = 101，m_creator_trx_id =103 2.根据上面的表格，T4时刻时，103事务查询id=1的所有行（一般幻读是根据相同的条件查出了不一样的结果）。但是此时数据最新的修改者101，因此DB_TRX_ID 为 101。因为m_up_limit_id <= 101 < m_low_limit_id，所以要在 m_ids 列表中查找，发现 DB_TRX_ID 存在列表中，那么这个记录不可见。  3.根据 DB_ROLL_PTR 找到 undo log 中的上一版本记录，上一条记录的 DB_TRX_ID 还是 101，不可见 4.直到找到可见的版本，如果此时行的DB_TRX_ID小于m_up_limit_id，那么就找到了可读的行数据。  如何解决幻读： 回归正题，MVCC分为两种模式，一种是读当前（读取最新的数据），例如： select…for update/lock in share mode、insert、update、delete。另一种是非锁定(不用读取最新的数据)，例如普通的select。  对于第二种，读取的并非最新数据，我们通过在事务开始生成一个快照，后面一直使用这个快照，就能解决幻读，不需要额外的操作  对于第一种，由于每次都是读当前，会导致一直生成新的快照。当有行数据插入或则删除时并且在查询范围之内，就会造成幻读的现象。解决办法：行锁+间隙锁。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读。 ————————————————               原文链接：https://blog.csdn.net/qq_44644452/article/details/134744829 

事务特性（ACID） 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability） 概念 脏读 读取了其他事务未提交的数据。未提交意味着这些数据有可能回滚，不插入数据库，也就是不存在的数据。读取数据库不存在的数据，就是脏读。 可重复读 在一个事务内，事务开始和事务结束前不同时刻读取的同一批是一致的，通常针对数据更新操作。  不可重复读 在同一事务内读取的同一批数据可能不一致，受其他事务影响，比如其他事务修改了这批数据并且提交了，通常针对更新操作。 幻读 幻读是针对数据插入操作来说的。比如A事务修改了数据但是还未提交，但是B事务插入了A事务修改前相同的数据，且B事务在A事务之前提交了，这样A事务再去读这批数据，感觉没有更新成功，实际上是B事务插入的数据,感觉出现幻觉。 隔离级别 1.读取未提交（READ UNCOMMITTED） 2.读取已提交（READ COMMITTED） 3.可重复读（REPEATABLE READ） 4.可串行化（SERIALIZABLE） 从上往下，隔离强度逐渐增强，性能逐渐变差  对于MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重复读） 设置隔离级别 -- 查看隔离级别  版本5.7.20 之后 show variables like '%iso%' SELECT @@GLOBAL.tx_isolation; SELECT @@SESSION.tx_isolation;   -- 设置隔离级别 set [作用域] transaction isolation level [事务隔离级别]  -- 其中作用域可以是 SESSION 或者 GLOBAL，GLOBAL 是全局的，而 SESSION 只针对当前回话窗口 -- 隔离级别是 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE set global transaction isolation level read committed; -- 设置完成后，只对之后新起的 session 才起作用，对已经启动 session 无效。如果用 shell 客户端那就要重新连接 MySQL，如果用 Navicat 那就要创建新的查询窗口 隔离级别解释  1.读取未提交（READ UNCOMMITTED） 其实就是可以读到其他事务未提交的数据。  启动两个事务，分别为事务A和事务B，在事务A中使用 update 语句，修改 age 的值为10，初始是 1 ，在执行完 update 语句之后，在事务B中查询 user 表，会看到 age 的值已经是 10 了，这时候 事 务A还没有提交，而此时事务B有可能拿着已经修改过的 age=10 去进行其他操作了。在事务B进 行操作的过程中，很有可能事务A由于某些原因，进行了事务回滚操作，那其实事务B得到的就是脏 数据了，拿着脏数据去进行其他的计算，那结果肯定也是有问题的 2.读提交 在事务A中使用 update 语句将 id=1 的记录行 age 字段改为 10。此时，在事务B中使用 select 语句进行查询，我们发现在事务A提交之前，事务B中查询到的记录 age 一直是1，直到事务A提交，此时在事务B中 select 查询，发现 age 的值已经是 10 了。  3.可重复读 B事务读取数据，A事务更新了数据也提交了，A事务再去读取数据还是跟之前数据一样。 这只是针对已有行的更改操作有效，但是对于新插入的行记录，就会出现幻读。  可重复读隔离级别出现幻读 A事务更新数据，在更新完数据之后B事务插入一条与A事务修改之前相同的数据，B事务提交之后，事务A查询数据发现多出一行，而且是A事务自己更新之前的数据。 4.串行化 串行化是4种事务隔离级别中隔离效果最好的，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束  MySQL 中是如何实现事务隔离的 首先说读未提交，它是性能最好，也可以说它是最野蛮的方式，因为它压根儿就不加锁，所以根本谈不上什么隔离效果，可以理解为没有隔离。 再来说串行化。读的时候加共享锁，也就是其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读。 最后说读提交和可重复读。这两种隔离级别是比较复杂的，既要允许一定的并发，又想要兼顾的解决问题。  实现可重复读  MySQL 采用了 MVVC (多版本并发控制) 的方式。 可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读提交则是每次执行语句的时候都重新生成一次快照 并发写问题 A执行 update 操作， update 的时候要对所修改的行加行锁，这个行锁会在提交之后才释放。而在事务A提交之前，事务B也想 update 这行数据，于是申请行锁，但是由于已经被事务A占有，事务B是申请不到的，此时，事务B就会一直处于等待状态，直到事务A提交，事务B才能继续执行，如果事务A的时间太长，那么事务B很有可能出现超时异常  加锁的过程要分有索引和无索引两种情况： 有索引的情况，那么 MySQL 直接就在索引数中找到了这行数据，加上行锁。 无索引的情况，MySQL 无法直接定位到这行数据，会为这张表中所有行加行锁，MySQL 会进行一遍过滤，发现不满足的行就释放锁，最终只留下符合条件的行。虽然最终只为符合条件的行加了锁，但是这一锁一释放的过程对性能也是影响极大的。所以，如果是大表的话，建议合理设计索引，如果真的出现这种情况，那很难保证并发度。  解决幻读 解决幻读用的也是锁，叫做间隙锁，MySQL 把行锁和间隙锁合并在一起，解决了并发写和幻读的问题，这个锁叫做 Next-Key锁。 假设现在表中有两条记录，并且 age 字段已经添加了索引，两条记录 age 的值分别为 10 和 30。 此时，在数据库中会为索引维护一套B+树，用来快速定位行记录。B+索引树是有序的，所以会把这张表的索引分割成几个区间。 分成了3 个区间，(负无穷,10]、(10,30]、(30,正无穷]，在这3个区间是可以加间隙锁的。 之后，我用下面的两个事务演示一下加锁过程。 在事务A提交之前，事务B的插入操作只能等待，这就是间隙锁起得作用。当事务A执行update user set name='风筝2号’ where age = 10; 的时候，由于条件 where age = 10 ，数据库不仅在 age =10 的行上添加了行锁，而且在这条记录的两边，也就是(负无穷,10]、(10,30]这两个区间加了间隙锁，从而导致事务B插入操作无法完成，只能等待事务A提交。不仅插入 age = 10 的记录需要等待事务A提交，age<10、10<age<30 的记录页无法完成，而大于等于30的记录则不受影响，这足以解决幻读问题了。  这是有索引的情况，如果 age 不是索引列，那么数据库会为整个表加上间隙锁。所以，如果是没有索引的话，不管 age 是否大于等于30，都要等待事务A提交才可以成功插入。 ————————————————   原文链接：https://blog.csdn.net/qq_38803590/article/details/129137108 

mysql版本是8.0的。opengauss版本是6.0.0的，使用的工具是portal

1.前言一条sql语句到底在执行时经历了什么？探究这个问题是学习mysql的重要步骤，面试时常被问到，也使得学习mysql时也有了知识框架的支撑，明白我们背的知识点到底用在哪里，笔者觉得这一点还是很重要的。注：对一个知识点的总结不仅包含知识点本身，还包含对该知识点的联想，这个联想是在面试时可能被追问的，也可以自己主动说出来（我还知道。。。）加分的。2.知识点MySQL 执行流程是怎样的？首先要知道的是，我们可以把mysql分成两层，server层和数据库引擎层，前者主要是对我们的查询进行处理（主要包括 {连接器}，{查询缓存}、{解析器}、{预处理器、优化器、执行器} 等），后者是数据真正存储的地方（从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎）。一条查询的执行流程如下：第一步：通过连接器连接 MySQL 服务1mysql -h$ip -u$user -p[连接器联想1]: 连接经过TCP 三次握手，断开经过四次挥手[连接器联想2]: 如果用户密码都没有问题，连接器就会获取该用户的权限，然后保存起来，后续该用户在此连接里的任何操作，都会基于连接开始时读到的权限进行权限逻辑的判断，意思是管理员修改已登录用户的权限需要等他重新登录才生效[连接器联想3]: 如何查看 MySQL 服务被多少个客户端连接了？show processlist[连接器联想4]: 空闲连接会一直占用着吗？MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。[连接器联想5]: MySQL 的连接数有限制吗？最大连接数由 max_connections 参数控制，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。[连接器联想6]: 怎么解决长连接占用内存的问题？MySQL 的连接也跟 HTTP 一样，有短连接和长连接的概念，长连接的好处就是可以减少建立连接和断开连接的过程，但是，使用长连接后可能会占用内存增多，因为 MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。有两种解决方式。第一种，定期断开长连接。第二种，客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。[连接器联想7]: 连接器的工作？与客户端进行 TCP 三次握手建立连接；校验客户端的用户名和密码，如果用户名或密码不对，则会报错；如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限；第二步：查询缓存连接器得工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据。但是其实查询缓存挺鸡肋的。对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。所以，MySQL 8.0 版本直接将server层查询缓存删掉了。第三步：解析SQL在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由「解析器」来完成。解析器会做两件事情:词法分析、 语法分析。[解释器联想1]: 词法分析：MySQL 会根据你输入的字符串识别出关键字出来，例如，SQL语句 select username from userinfo，在分析之后，会得到4个Token，其中有2个Keyword，分别为select和from.[解释器联想2]: 语法分析：根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法，如果没问题就会构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。[解释器联想3]: 解如果我们输入的 SQL 语句语法不对，就会在解析器这个阶段报错。（释器的主要作用）[解释器联想4]: 解释器只负责检查语法和构建语法树，但是不会去查表或者字段存不存在。第四步：执行 SQL解析SQL无误后，执行SQL需要经过三个步骤：预处理器、优化器、执行器。预处理器检查 SQL 查询语句中的表或者字段是否存在；将 select * 中的 * 符号，扩展为表上的所有列；优化器优化器主要负责将 SQL 查询语句的执行计划确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。[优化器联想1]: 要想知道优化器选择了哪个索引，我们可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行计划。explain select * from product where id = 1[优化器联想2]: 一般来讲普通索引查询效率高于主键索引，当索引覆盖时会先考虑普通索引的B+树上查询，这就是执行计划，是优化器决定的。执行器确定了执行计划，接下来 MySQL 就真正开始执行语句了，在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。主键索引查询 select * from product where id = 1; 让InnoDB引擎通过主键索引B+树搜索id=1的记录。全表扫描 select * from product where name = 'iphone'; 查询条件没有用到索引，触发全表扫描，查询每一条记录判断是否满足条件。索引下推 （MySQL 5.6 推出的查询优化策略）[索引下推联想1]: 索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。select * from t_user where age > 20 and reward = 100000;不使用索引下推（MySQL 5.6 之前的版本）时，定位到 age > 20 的一条记录，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层，Server 层再判断该记录的 reward 是否等于 100000。而使用索引下推后，判断记录的 reward 是否等于 100000 的工作交给了存储引擎层：定位到 age > 20 的第一条记录，存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。MySQL 执行流程是怎样的？总结：（总结只是简单总结，也就是被问到时该说的，上面的知识点，是可能被追问时涉及的，或者自己说出来的加分项。）连接器：建立连接，管理连接、校验用户身份；查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；执行 SQL：执行 SQL 共有三个阶段：预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

背景： 我们对数据库操作时常常有这种需求：如果不存在该记录则新增，存在则更新！ 传统的思路：先select判断是否存在，再选择insert或者update，这样的话步骤较多。 为了解决这种需求，mysql提供了两种常用的关键字方法：replace into 与 insert into … on duplicate key update，现在我们测试下这两种方法吧！  一、replace into 测试分析 介绍： replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。 要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。  准备测试表：  CREATE TABLE `customer` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(20) DEFAULT NULL,   `phone` varchar(20) DEFAULT NULL,   `data` varchar(100) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 注：id字段为自增主键 插入基础数据：  INSERT INTO customer(NAME,phone,DATA) VALUES("小一","17610111111","1") INSERT INTO customer(NAME,phone,DATA) VALUES("小二","17610111112","2") 测试1： replace into 一条数据（不含主键） REPLACE INTO customer(NAME,phone,DATA) VALUES("小三","17610111113","3") 结论：由于插入数据不包含主键或唯一索引，则判断该条数据不存在，此时效果等同于insert into 测试2： replace into 一条数据（含主键）  REPLACE INTO customer(id,NAME,phone,DATA) VALUES(2,"小四","17610111114","4") 注：共2行收到影响，即先删除再新增！  结论： 插入数据中存在已存在主键：id=2，则判断该条数据已存在，故先删除再新增（即更新) 测试3： replace into 的数据中减少一个字段：data  REPLACE INTO customer(id,NAME,phone) VALUES(2,"小五","17610111115") 结论： replace into 的数据中如果比原数据少字段，则该字段更新时恢复为默认值（此处默认为NULL）。故可以确定replace into 删除已存在记录时，很彻底，没有做备份，之后直接新增replace into 后面确定的数据，没有值的字段设为默认值。 注：这点就和 insert into … on duplicate key update 不同了！  测试4： insert into 2条数据，看主键id是否有变化 INSERT INTO customer(NAME,phone,DATA) VALUES("小六","17610111116","6") INSERT INTO customer(NAME,phone,DATA) VALUES("小七","17610111117","7") 结论： 虽然第二条记录做了2次 replace into 操作，但后面新增数据时主键id 并没有+1，那问题来了，网上说的主键+1是什么时候发生的呢？  测试5： 我们将name字段设置 unique索引，再replace into 这次语句中没有涉及主键id，为了判断记录存在性，所以我们为name设置了unique索引（此时根据name判断记录是否存在）  REPLACE INTO customer(NAME,phone,DATA) VALUES("小七","17610111118","8") 结论： 上一个测试主键没有变化的原因是我们在replace into 的数据中设置了确切的主键（相当于固定住了），而现在我们没有设置主键，故主键自增+1，此处需要注意！  测试6： 为name + phone字段设置 unique联合索引（后面测试都这样设置） 此时是根据 name + phone判断记录存在性！  REPLACE INTO customer(NAME,phone,DATA) VALUES("小七","17610111119","9") REPLACE INTO customer(NAME,phone,DATA) VALUES("小七","17610111119","10") 结论： 此时存在，故更新data（9 -> 10），同时主键id也+1 测试7： 在name+phone为联合索引情况下去除phone字段再replace into  REPLACE INTO customer(NAME,DATA) VALUES("小七","11") REPLACE INTO customer(NAME,DATA) VALUES("小七","11") 结论： 由于没加phone字段，数据库无法判断存在性，故默认不存在，即新增2条记录，phone默认为NULL。 此处特别说明：在MYSQL中UNIQUE索引将会对null字段失效，故这两条记录能同时存在不报错！  二、insert into … on duplicate key update 测试分析 注：接着上面的测试继续  测试8： INSERT INTO customer(NAME,phone,DATA) VALUES("小八","17610111118","8") ON DUPLICATE KEY UPDATE DATA = "88" 结论：此时根据 name + phone 判断出数据库不存在该记录，故新增，等同于直接 insert into，ON DUPLICATE KEY UPDATE DATA = "88" 无效！  测试9： INSERT INTO customer(NAME,phone,DATA) VALUES("小八","17610111118","9") ON DUPLICATE KEY UPDATE DATA = "99" 结论： 此时判断存在，更新data数据为99，这个没啥问题。但和replace into 不同的是，主键id竟然没有+1，依旧是11… 测试10： 简单insert into，接着上面测试主键+1问题 INSERT INTO customer(NAME,phone,DATA) VALUES("小九","17610111119","9")  结论： 发现这里新增时，主键id才额外+1，这一点确实和replace into 不一样，可以和之前的测试对比看下！  测试11： 继续测试主键+1问题 INSERT INTO customer(id,NAME,phone,DATA) VALUES("13","小九","17610111119","10") ON DUPLICATE KEY UPDATE DATA = "100" INSERT INTO customer(NAME,phone,DATA) VALUES("小十","17610111110","10") 结论： 如果新增数据中设置了确切的主键，则再insert into 时主键并没有额外+1  三、结论 区别 1：（主要） insert .. on deplicate udpate保留了所有字段的旧值，再覆盖然后一起insert进去，而replace into没有保留旧值，直接删除再insert新值。 从底层执行效率上来讲，replace into要比insert .. on deplicate update效率要高，但是在写replace的时候，字段要写全，防止老的字段数据被删除。  区别 2： 两个主键自增的场景有稍许不同，详情见以上测试 ————————————————                     原文链接：https://blog.csdn.net/Abysscarry/article/details/80518278 

在往表里面插入数据的时候，经常需要：a.先判断数据是否存在于库里面；b.不存在则插入；c.存在则更新一、replace into　　前提：数据库里面必须有主键或唯一索引，不然replace into 会直接插入新数据，导致数据表里面有重复数据　　执行时先尝试插入数据：　　　　a.当数据表里面存在(通过主键或唯一索引来判断)该数据，则先将表里的数据删除，再插入新的数据　　　　b.如果数据表里面不存在该数据，则直接插入数据　　replace into是insert into的增强版，语法跟insert iton差不多　　　　replace into table_name(columns)values(values1,values2);　　　　replace into table_name(columns) select columns from table_name2　　测试数据(该表建立了一个复合的唯一索引user_add)：　　　　CREATE TABLE `relace_on` (　　　　　　`id` int(11) unsigned NOT NULL AUTO_INCREMENT,　　　　　　`user_id` int(11) unsigned NOT NULL,　　　　　　`interal` tinyint(3) unsigned NOT NULL,　　　　　　`add_time` date NOT NULL,　　　　　　PRIMARY KEY (`id`),　　　　　　UNIQUE KEY `user_add` (`user_id`,`add_time`) USING BTREE　　　　) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;　　插入测试数据：　　　　　　　　INSERT INTO relace_on (user_id, interal, add_time)　　　　VALUES　　　　(1,20,'2016-05-06'),　　　　(2,20,'2016-05-06'),　　　　(3,20,'2016-05-06'),　　　　(1,20,'2016-05-07'),　　　　(2,20,'2016-05-07'),　　　　(3,20,'2016-05-07')　　现在数据库数据：　　接下来执行一下replace into语句（存在）：replace INTO relace_on(user_id, interal, add_time)values(1,40,'2016-05-06'),(2,60,'2016-05-06'),(3,80,'2016-05-06')　　此时sql执行成功，受影响行数为6行（删除三条，插入三条）　　对比一下你会发现user_id(1,2,3)的账户在2016-05-06这一天原先都是有数据的，并且id为（1，2，3）；现在执行了replace into后，id变成了（7,8,9），并且interal字段的值为执行语句的值，此时replace into语句根据数据表中的user_add这个复合的唯一索引发现在数据表中user_id为（1，2，3）的用户在2016-05-06这天各存在一条记录，这时就把原先的三条数据删除了，重新插入了三条，所以id从1，2，3变成了7，8，9；并且interal的值也变了　　接下来执行一下replace into语句（不存在）：replace INTO relace_on(user_id, interal, add_time)values(4,40,'2016-05-06'),(5,60,'2016-05-06'),(6,80,'2016-05-06')　　此时sql执行成功，受影响行数为3行（插入三条）　　对比上图，你会发现原先的数据没变，只是新增了三条数据，同样是2016-05-06这天的，但是user_id是（4，5，6）根据user_add这个复合的唯一索引，这三条数据不存在数据表中，所以直接插入即可　　　　二、on duplicate key update　　它也是可以用于更新数据的，跟replace into有点相似，但是on duplicate key update是数据表里面存在该数据就更新，不存在则插入，；而replace into则是存在就删除，再插入，不存在则插入　　依旧使用上面现有的数据来测试：　　先添加一个字段，用于等下更新多个字段之用：ALTER TABLE `relace_on` ADD COLUMN `copy_interal` tinyint(3) UNSIGNED NOT NULL AFTER `interal`;　　语法：　　　　更新单个字段：insert into table_name(columns)values(values1,values2) on duplicate key update column=values(column)或者column=value(1,'zgw')　　　　更新多个字段：insert into table_name(columns)values(values1,values2) on duplicate key update column1=values(column1),column2=values(column2)　　执行一条语句（存在）：insert into relace_on(user_id, interal,copy_interal, add_time)values(6,100,200,'2016-05-06') on duplicate KEY update interal=values(interal),copy_interal=values(copy_interal)　　如图，user_id=6，add_time='2016-05-06'这条数据存在，则更新interal和copy_interal两个字段的值（interal原先为80，copy_interal新增字段默认为0）　　再次执行一条语句（不存在）：insert into relace_on(user_id, interal,copy_interal, add_time)values(7,100,200,'2016-05-06') on duplicate KEY update interal=values(interal),copy_interal=values(copy_interal)原文链接：https://blog.csdn.net/txqd1989/article/details/83832352

ON DUPLICATE KEY UPDATE 子句和 REPLACE INTO 语句都是 MySQL 提供的用于处理数据插入时的冲突解决策略，但它们在处理方式和效果上有着本质的区别： ON DUPLICATE KEY UPDATE  操作流程：当尝试插入一条新记录时，如果该记录的主键或唯一索引与现有记录冲突，ON DUPLICATE KEY UPDATE 子句将触发，并执行更新操作，而不是插入新记录。只有冲突的行会被更新，不会影响到其他行。  数据处理：它允许你指定哪些列需要在冲突发生时进行更新，以及如何更新这些列的值。这意味着你可以只修改部分字段而保留其他字段的原有值。  资源消耗：由于它不涉及记录的删除，所以在处理冲突时相对节省资源。但如果更新涉及大量字段或并发操作，可能会影响性能。  适用场景：适合于需要在插入时检查记录是否存在，并根据情况更新某些字段的场景，如统计计数、用户信息更新等。  REPLACE INTO  操作流程：REPLACE INTO 在尝试插入新记录时，如果发现主键或唯一索引冲突，它会先删除原有的冲突行，然后插入新的记录。这实质上是“删除+插入”的操作。  数据处理：整个记录会被替换，不仅仅是冲突的字段，这意味着如果新记录没有指定某些字段的值，这些字段将使用默认值或NULL（如果没有默认值）。原有的行数据将完全被新数据覆盖。  资源消耗：相比 ON DUPLICATE KEY UPDATE，REPLACE INTO 可能会消耗更多的资源，因为它涉及到删除旧记录和插入新记录两个操作，特别是在处理大表时可能会影响性能。  适用场景：适用于需要确保每个唯一键对应的记录完全替换的场景，例如，当需要确保数据的绝对新鲜性，不关心被替换记录的其他字段值时。 总结： 数据保留性：ON DUPLICATE KEY UPDATE 保留了原有记录中未被更新字段的值，而 REPLACE INTO 则会替换整行数据。 资源和性能：ON DUPLICATE KEY UPDATE 在处理冲突时较为轻量，而 REPLACE INTO 可能会因涉及删除和插入而消耗更多资源。 应用场景：根据是否需要保留原有记录的非冲突字段值以及性能考量，选择合适的操作方式。 ————————————————          原文链接：https://blog.csdn.net/weixin_72610956/article/details/139604440