数据库知识点(索引&数据库内数据间运算)

待我称王封你为后i 2022-05-20 00:08 254阅读 0赞

<table> 
 <tbody> 
 <tr> 
 <td>数据库、缓存相关的问题。 
 <ul> 
 <li>ACID</li> 
 <li>事务隔离级别</li> 
 <li>MVCC</li> 
 <li>数据库分表</li> 
 <li>索引</li> 
 <li>缓存一致性</li> 
 </ul>基本SQL语句参考文章&nbsp;<a href="https://blog.csdn.net/rico_rico/article/details/72867791" rel="nofollow">https://blog.csdn.net/rico_rico/article/details/72867791</a> <a href="https://www.cnblogs.com/java-jun-world2099/p/9290966.html" rel="nofollow">InnoDB事务日志（redo log 和 undo log）详解</a>&nbsp;&nbsp;<a href="https://www.cnblogs.com/java-jun-world2099/p/9290966.html" rel="nofollow">https://www.cnblogs.com/java-jun-world2099/p/9290966.html</a> </td> 
 </tr> 
 </tbody> 
</table>

### 数据库索引 B+树和B树  ###

**数据库中最常见的慢查询优化方式是什么？为什么加索引能优化慢查询？你知道哪些数据结构可以提高查询速度？**

**索引**其实就**是一种**优化查询的**数据结构**，比如Mysql中的索引是用**B+**树实现的。哈希表、完全平衡二叉树、B树、B+树等等。

哈希表: 注意字段值所对应的数组下标是哈希算法随机算出来的，所以可能出现**哈希冲突**。哈希表的特点就是**可以快速的精确查询，但是不支持范围查**找

一个表的数据行数越多，那么对应的索引文件其实也是会很大的，实际上**也是需要存储在磁盘中的，而不能全部都放在内存中**，所以我们在考虑选用哪种数据结构时，我们可以换一个角度思考，**哪个数据结构更适合从磁盘中读取数据**，或者**哪个数据结构能够提高磁盘的IO效率**。

B树与B+树的区别:

同样的元素，B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素。

同样的元素，B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。所有数据存储在叶子节点,非叶节点只放关键字和指针

为什么数据库选B-tree或B+tree而不是二叉树作为索引结构:

首先,最坏的情况下磁盘IO的次数由树的高度来决定。二叉树相比于顺序查找的确减少了查找次数，但是在最坏情况下，二叉树有可能退化为顺序查找。而且就二叉树本身来说，当数据库的数据量特别大时，其层数也将特别大。

**B+树中一个节点到底多大合适？**

答案是：**B+树中一个节点为一页或页的倍数最为合适**。这里说的“页”是Mysql自定义的单位（其实和操作系统类似），Mysql的Innodb引擎中一页的默认大小是**16k**（如果操作系统中一页大小是4k，那么Mysql中1页=操作系统中4页），可以使用命令SHOW GLOBAL STATUS like 'Innodb\_page\_size';(16384)查看。

16k的原因 : 假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)，,,,,所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU5Mzk4NQ_size_16_color_FFFFFF_t_70][]（叶子结点为数据的是innoDB）

\*主键为什么递增  参考 [https://blog.csdn.net/wukong\_666/article/details/54982712][https_blog.csdn.net_wukong_666_article_details_54982712]

B+树的数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的**各条数据记录按主键顺序存放**，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页 ; 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

综上总结，如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的，也就是下面这几种情况的存取效率最高：

1、使用**自增列(INT/BIGINT类型)做主键**，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；

2、该表不指定自增列做主键，同时也没有可以被选为主键的唯一索引(上面的条件)，这时候InnoDB会选择**内置的ROWID**作为主键，写入顺序和ROWID增长顺序一致；  
除此以外，如果一个InnoDB表又没有显示主键，又有可以被选择为主键的唯一索引，但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况)，该表的存取效率就会比较差。**应使用auto\_increment**

**主键一般都选择与用户数据不相关的自增序列ID,数据库一般会自动给主键添加索引,唯一键和主键的区别是主键不可以为null**

**\*MySQL行级锁、表级锁、页级锁  **[https://www.jb51.net/article/50047.htm][https_www.jb51.net_article_50047.htm]

页级:引擎 BDB。   
表级:引擎 MyISAM ， 理解为锁住整个表，可以同时读，写不行   
行级:引擎 INNODB ， 单独的一行记录加锁

表级，直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许   
行级,，仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。   
页级，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。  
上述三种锁的特性可大致归纳如下：  
1） 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。  
2） 行级锁：开销大，加锁慢；**会出现死锁**；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。  
3） 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

<table> 
 <tbody> 
 <tr> 
 <td> 表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如WEB应用；行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。 &nbsp;MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。 InnoDB有两种模式的行锁： 1）共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 &nbsp;&nbsp;&nbsp; ( Select * from table_name where ......lock in share mode) 2）排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和&nbsp; 排他写锁。(select * from table_name where.....for update) 为了允许行锁和表锁共存，实现多粒度锁机制；同时还有两种内部使用的意向锁（都是表锁），分别为意向共享锁和意向排他锁。 &nbsp;&nbsp;&nbsp; InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！ </td> 
 </tr> 
 </tbody> 
</table>

死锁的关键在于**：两个(****或以上)****的Session(会话)****加锁的顺序****不一致。**

session一个事务只能有一个会话产生....

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序

相同表记录行锁冲突-->     这种情况产生的死锁比较常见，之前遇到两个job在执行数据批量更新时，jobA处理的的id列表为\[1,2,3,4\]，而job处理的id列表为\[8,9,10,4,2\]，这样就造成了死锁。-->主要在于2,4 和4,2 的顺序

**间隙锁（Next-Key锁） :**  针对于辅助索引,也称**范围索引** **间隙锁只会出现在辅助索引上，唯一索引和主键索引是没有间隙锁。间隙锁（无论是S还是X）只会阻塞insert操作  **

间隙锁在InnoDB的唯一作用就是防止其它**事务的**插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用**行锁**方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁    要禁止间隙锁的话，可以把隔离级别降为读已提交，或者开启参数innodb\_locks\_unsafe\_for\_binlog。

当我们用**范围条件**而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；**对于键值在条件范围内但并不存在的记录(select)**，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。InnoDB除了通过范围条件加锁时使用间隙锁外，**如果使用相等条件请求给一个不存在的记录加锁**，InnoDB也会使用间隙锁！(insert)

### Mysql中MyISAM和innodb使用B+树 ###

**B树一个很重要的特征就是：高度小。**通常我们认为**B+树的非叶子节点不存储数据**，**只有叶子节点才存储数据**；**而B树的非叶子和叶子节点都会存储数据，会导致非叶子节点存储的索引值会更少，**树的高度相对会比B+树高，平均的I/O效率会比较低，所以使用B+树作为索引的数据结构，再加上B+树的叶子节点之间会有指针相连，也方便进行范围查找。

### MyISAM中的B+树 ###

MyISAM引擎使用B+Tree作为索引结构，**叶节点的data域存放的是数据记录的地址。其采用索引文件与数据文件，索引文件只存放索引，叶子节点存放数据的物理地址。**数据文件存放数据。其索引方式是非聚集的。主键索引和辅助索引没有太多区别。

### InnoDB中的B+树 ###

InnoDB也使用B+Tree作为索引结构。但是它的**主索引与数据都放在一个文件中。这种索引叫做聚集索引，**因为InnoDB的数据文件本身要按主键聚集，**所以InnoDB要求表必须有主键（MyISAM可以没有)，**如果一个表没有手动建立主键索引，Innodb会查看有没有唯一索引，如果有则选用唯一索引作为主键索引，如果连唯一索引也没有，则会默认建立一个隐藏的主键索引（用户不可见），这个字段长度为6个字节，类型为长整形。InnoDB中主键索引的叶子节点的数据区域存储的是数据记录，辅助索引存储的是主键值。另外，Innodb的主键索引要比MyISAM的主键索引查询效率要高（少一次磁盘IO），并且比辅助索引也要高很多。所以，我们在使用Innodb作为存储引擎时，我们最好：

1.  手动建立主键索引
2.  尽量利用主键索引查询

B+树中的一个节点存储的内容是：

*  非叶子节点：主键+指针
 *  叶子节点：数据

两个引擎间区别:

*  区别一：InnoDB的主索引与数据都放在一个文件中。而MYISAM是分开存放的。
 *  区别二：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
 *  区别三：InnoDB的主键索引是聚集索引，而MYISAM不是聚集索引。
 *  MyISAM是非事务安全(不支持事务)的，而InnoDB是事务安全的
 *  MyISAM支持全文类型索引(不支持行级锁)，而InnoDB支持行级锁和外键 : 因此可以支持写并发??
 *  MyISAM存储总行数，而InnoDB不存储总行数

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以，对应的聚集索引只能有一个。

[主键选择][Link 1]

*  在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

链接：[https://www.nowcoder.com/discuss/105302][Link 1]  
分表分库

*  水平拆分：库内分表(根据表内数据内在逻辑关系 同一表按不同条件分散到多个库或表中) 库内分表()
 *  垂直拆分：垂直分库(根据业务耦合性,不相关的分到不同数据库) 垂直分表(针对数据库的列进行,不常用的字段拆分到扩展表中)

## 最左前缀原则 ##

我们模拟数据建立一个联合索引 `select*,concat(right(emp_no,1),"-",right(title,1),"-",right(from_date,2))fromemployees.titles limit10;`

![20190314174309892.png][]

### [mysql联合索引][mysql] ###

命名规则：表名\_字段名  
1、需要加索引的字段，要在where条件中  
2、数据量少的字段不需要加索引  
3、如果where条件中是**OR**关系，加索引不起作用  
4、符合最**左**原则

alter table tablename_ add index indexname_(c1,c2,c3,c4); 通过命令行前加explain 看输出中的key 可知是否使用索引

存储时, 是按照第一个字段来分存在左子树还是右子树, 如上图 , 对于 **`select*from employees.titles where emp_no=1`**是能用到索引的，因为它能**利用**上面的**索引缩小查询范围**，首先和第一个节点“4-r-01”比较，1<4，所以可以直接确定结果在左子树，同理，依次按顺序进行比较，逐步可以缩小查询范围。对于 **`select*from employees.titles where title='1'`**是不能用到索引的，因为它不能用到上面的所以，和第一节点进行比较时，没有empno这个字段的值，不能确定到底该去左子树还是右子树继续进行查询。对于**`select*from employees.titles where title='1'and emp_no=1`**是能用到索引，按照我们的上面的分析，先用title='1'这个条件和第一个节点进行比较，是没有结果的，但是mysql会对这个sql进行优化，优化之后会将empno=1这个条件放到第一位，从而可以利用索引。

我们判断一个查询条件能不能用到索引，我们要分析这个查询条件能不能利用某个索引缩小查询范围

[最左原则的原理:][Link 2](参考)

索引本质是一棵B+Tree，联合索引(col1, col2,col3)也是。

其非叶子节点存储的是第一个关键字的索引，而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU5Mzk4NQ_size_16_color_FFFFFF_t_70 1][]

（图以innodb引擎为例，对应地址指的是数据记录的地址）

联合索引(年龄, 姓氏,名字)，叶节点上data域存储的是三个关键字的数据。且是按照年龄、姓氏、名字的顺序排列的。

**而最左原则的原理就是，因为联合索引的B+Tree是按照第一个关键字进行索引排列的。**

### [索引失效][Link 3] ###

失效的原因 很好的一篇 [https://blog.csdn.net/student\_\_software/article/details/82078786][https_blog.csdn.net_student_software_article_details_82078786]

<table> 
 <tbody> 
 <tr> 
 <td> 　　1用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。 　　要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引 　　2.对于多列索引，不是使用的第一部分，则不会使用索引 　　3.like查询以%开头 　　4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引 　　5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引 　　6对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)&nbsp; 　　7如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。因为用的是哈希索引。 　　8.使用NOT IN和&lt;&gt;操作 　　9、is null或者is not null 也会导致无法使用索引。 　　10、字符串不加单引号索引失效。 　　11 范围之后全失效 存储引擎不能使用索引中范围条件右边的列 &nbsp; 索引无法存储null值 　　a.单列索引无法储null值，复合索引无法储全为null的值。 　　b.查询时，采用is null条件时，不能利用到索引，只能全表扫描。 　　&nbsp;为什么索引列无法存储Null值？ 　　a.索引是有序的。NULL值进入索引时，无法确定其应该放在哪里。（将索引列值进行建树，其中必然涉及到诸多的比较操作，null&nbsp;值是不确定值无法　　 　　比较，无法确定null出现在索引树的叶子节点位置。）　 　　b.如果需要把空值存入索引，方法有二：其一，把NULL值转为一个特定的值，在WHERE中检索时，用该特定值查找。其二，建立一个复合索引。例如　 　　create index ind_a on table(col1,1);&nbsp;&nbsp;通过在复合索引中指定一个非空常量值，而使构成索引的列的组合中，不可能出现全空值。 &nbsp;</td> 
 </tr> 
 </tbody> 
</table>

### [mysql是如何解决脏读、不可重复读、幻读？][mysql 1]  ###

([https://blog.csdn.net/starlh35/article/details/76445267][https_blog.csdn.net_starlh35_article_details_76445267])

或设置事务隔离级别:

为了解决上述问题，数据库通过[锁机制][Link 4]解决并发访问的问题。根据锁定对象不同：分为行级锁和表级锁；根据并发事务锁定的关系上看：分为共享锁定和独占锁定，共享锁定会防止独占锁定但允许其他的共享锁定。而独占锁定既防止共享锁定也防止其他独占锁定。为了更改数据，数据库必须在进行更改的行上施加行独占锁定，insert、update、delete和selsct for update语句都会隐式采用必要的行锁定。

但是直接使用锁机制管理是很复杂的，基于锁机制，数据库给用户提供了不同的事务隔离级别，只要设置了事务隔离级别，数据库就会分析事务中的sql语句然后自动选择合适的锁。

**注意：事务的隔离级别和数据库并发性是成反比的，隔离级别越高，并发性越低。**

![2019032217285043.png][]

** 数据库事务处理中出现的数据不一致的情况 **

(带实例的参考 [据库事务隔离级别和锁实现机制][Link 5]  [https://comedsh.iteye.com/blog/698733][Link 5]   对应的还有回复问答)

**1、一级封锁协议 (对应 read uncommited) **　　  
     一级封锁协议是：事务 在对需要修改的数据上面（就是在发生修改的瞬间） 对其加共享锁（其他事务不能更改，但是可以读取-导致“脏读”），直到事务结束才释放。**事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。**  
     一级封锁协议不能避免 **丢失更新，脏读，不可重复读，幻读！**  
**2、二级封锁协议 （对应read commited)　**  
     二级封锁协议是：1）事务 在对**需要更新的数据 **上（就是发生更新的瞬间） 加 **排他锁 （直到事务结束） **， 防止其他事务读取未提交的数据，这样，也就避免了 “脏读” 的情况。2）事务 对当前被**读取的数据** 上面加**共享锁 **（当读到时加上共享锁），一旦读完该行，**立即 **释放该 该行的共享锁 - 从数据库的底层实现更深入的来理解，既是，数据库会对游标当前的数据上加**共享锁** ， 但是当游标离开当前行的时候，立即释放该行的共享锁。　  
     二级封锁协议除防止了“脏读”数据，但是不能避免 **丢失更新，不可重复读，幻读** 。

<table> 
 <tbody> 
 <tr> 
 <td>事务A</td> 
 <td>事务B</td> 
 </tr> 
 <tr> 
 <td>读取X=100（同时上共享锁）</td> 
 <td>读取X=100（同时上共享锁）</td> 
 </tr> 
 <tr> 
 <td>读取成功（释放共享锁）</td> 
 <td>读取成功（释放共享锁）</td> 
 </tr> 
 <tr> 
 <td>UPDATE&nbsp;X=X+100 （上排他锁）</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td>UPDATING A（等待事务A释放对X的排他锁）</td> 
 </tr> 
 <tr> 
 <td>事务成功（释放排他锁）X=200</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td>UPDATE X=X+200（成功上排他锁）</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td>事务成功（释放排他锁）X=300</td> 
 </tr> 
 </tbody> 
</table>

由此可以看到，事务A的提交被事务B覆盖了，所以不能防止 丢失更新。  
如果要避免 **丢失更新，我们需要额外的操作，** 对凡是读到的数据加 **共享锁** 和**排他锁** ，这个往往需要程序员自己编程实现，比如在Oracle 中，需要加 SELECT FOR UPDATE 语句，表明，凡是该事务读到的数据，额外的加上排他锁，防止其他数据同一时间获取相同数据，这样就防止了 **丢失更新** ！

**3、三级封锁协议 （对应reapetable read ）**  
      三级封锁协议是：二级封锁协议加上事务 在读取数据的瞬间 必须先对其加 **共享锁** ，**但是** 直到**事务结束才释放** ，这样保证了可重复读（既是其他的事务职能读取该数据，但是不能更新该数据）。　  
      三级封锁协议除防止了**“脏”数据** 和**不可重复读** 。但是这种情况不能避免 **幻读 和 丢失更新 **的情况，在事务 A 没有完成之前，事务 B 可以新增数据，那么 当事务 A 再次读取的时候，事务B 新增的数据会被读取到，这样，在该封锁协议下，**幻读** 就产生了。 如果事务A 和 事务B 同时读取了资源X=100，同样，如果事务A先对X进行 更新X=X+100，等待事务A执行完成X=200，那么事务B 获得X的排他锁，进行更新 X=X+200，然后提交 X=300，同样A的更新被B所覆盖！**(** 如果要避免 **丢失更新，我们需要额外的操作，**对凡是读到的数据加 **共享锁** 和**排他锁** ，这个往往需要程序员自己编程实现，比如在Oracle 中，需要加 SELECT FOR UPDATE 语句，表明，凡是读到的数据，我会加 排他锁，防止其他数据同一时间获取相同数据**)** ！   
      进阶：repeatable read 导致死锁的情况**（即便是 不同的资源在相同的顺序下获取）。** 比如 事务1 读取 A，同时 事务2 也读取 A，那么事务1和事务2 同时对 A 上了共享锁，然后事务1 要UPDATE A，而此时 事务2 也要 UPDATE A，这个时候 事务1 等待 事务2 释放其在 A 上的共享锁，然后 事务2 要等待 事务1 释放其在 A 上的共享锁，这样，事务1 和 事务2 相互等待，产生死锁！（SQL Server/DB2 里面有 UPDATE LOCK 可以解决这种情况，具体的思路是，在 repeatable read 的情况下，将读取的数据 上的 UPDATE 锁，介于 共享锁 和 排他锁之间的一种锁，该锁的作用是 当出现上面这种情况后，事务1 和 事务2 对 A 上的是 UPDATE 锁，那么谁先 要修改 A，那么该事务就会将 UPDATE 锁可以顺利升级为 排他锁对该数据进行修改！）

**4、最强封锁协议（对应Serialization)**

**      **四级封锁协议是对三级封锁协议的增强，其实现机制也最为简单，直接对 事务中 所 读取 或者 更改的数据所在的表加表锁，也就是说，其他事务不能 读写 该表中的任何数据。这样所有的 **脏读，不可重复读，幻读** ，都得以避免！

在一个事务中，我们应该尽量把 SELECT Queries 放到最前面，把所有的 Update 放到最后面，避免不必要的等待！

### MySQL 到底是怎么解决幻读的？ ###

参考 [https://mp.weixin.qq.com/s/U-mzFnooeQ5GboUu\_0yXNw][https_mp.weixin.qq.com_s_U-mzFnooeQ5GboUu_0yXNw]

**一、什么是幻读**

在一次事务里面，多次查询之后，结果集的**个数**不一致的情况叫做幻读。而多出来或者少的哪一行被叫做幻行。(insert)

**二、为什么要解决幻读**

在高并发数据库系统中，需要保证事务与事务之间的**隔离性(针对不同隔离级别)**，还有事务本身的**一致性(逻辑上的正确性)**。

**三、MySQL 是如何解决幻读的**

**1. 多版本并发控制（MVCC）（快照读/一致性读）**

多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的。以 InnoDB 为例，每一行中都冗余了两个字段。

一个是行的**创建版本**，一个是行的删除**（过期）版本**。具体的版本号（**trx\_id**）存在**information\_schema.INNODB\_TRX 表**中。版本号（trx\_id）**随着每次事务的开启自增**。

**事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。**

普通的 select 就是快照读。

`select * from T where number = 1;`

原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见的。

**2. next-key 锁 （当前读）**

next-key 锁包含两部分：

*  记录锁（行锁）
 *  间隙锁

**记录锁(行锁)是加在索引上的锁**，**间隙锁是加在索引之间的。**

`select * from T where number = 1 for update;`

`select * from T where number = 1 lock in share mode;`

`insert`

`update`

`delete`

原理：**将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的。**

**其他：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 解决了幻读问题**。[面试问烂的 MySQL 四种隔离级别，][MySQL]这篇文章建议大家看下。

如引用一问题所说，T1 select 之后 update，会将 T2 中 insert 的数据一起更新，那么认为多出来一行，所以防不住幻读。看着说法无懈可击，但是其实是错误的，InnoDB 中设置了快照读和当前读两种模式，如果只有快照读，那么自然没有幻读问题，但是如果将语句提升到当前读，那么 T1 在 select 的时候需要用如下语法： **select \* from t for update (lock in share mode)** 进入当前读，那么自然没有 T2 可以插入数据这一回事儿了。

**注意**

**next-key** 固然很好的解决了幻读问题，但是还是遵循一般的定律，隔离级别越高，并发越低。

### [关于innodb中MVCC的一些理解][innodb_MVCC] ###

即多版本并发控制技术,**它使得大部分支持行锁的事务引擎，不再单纯的使用行锁来进行数据库的并发控制，取而代之的是把数据库的行锁与行的多个版本结合起来，只需要很小的开销,就可以实现非锁定读**，从而大大提高数据库系统的并发性能

### JDBC(java的数据库连接技术)主要功能: ###

（1）建立与数据库或者其他数据源的链接

（2）向数据库发送SQL命令

（3）处理数据库的返回结果

JDBC常用类和接口 : 连接到数据库（Connection）、建立操作指令（Statement）、执行查询指令（executeQuery）、获得查询结果（ResultSet）等。

JDBC链接数据库知识包含类和接口方法详解 很棒的文章 [https://www.cnblogs.com/xiaotiaosi/p/6394554.html][https_www.cnblogs.com_xiaotiaosi_p_6394554.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU5Mzk4NQ_size_16_color_FFFFFF_t_70]: /images/20220520/961d037b3a76429faad08783c6371557.png
[https_blog.csdn.net_wukong_666_article_details_54982712]: https://blog.csdn.net/wukong_666/article/details/54982712
[https_www.jb51.net_article_50047.htm]: https://www.jb51.net/article/50047.htm
[Link 1]: https://www.nowcoder.com/discuss/105302
[20190314174309892.png]: /images/20220520/c7299865a64b43eca756e5c0948a71c0.png
[mysql]: https://www.cnblogs.com/softidea/p/5977860.html
[Link 2]: https://www.cnblogs.com/naci/p/10216131.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU5Mzk4NQ_size_16_color_FFFFFF_t_70 1]: /images/20220520/4c57e0ce52934facbab5f096fdbe48aa.png
[Link 3]: https://www.cnblogs.com/shynshyn/p/7887742.html
[https_blog.csdn.net_student_software_article_details_82078786]: https://blog.csdn.net/student__software/article/details/82078786
[mysql 1]: https://www.cnblogs.com/hansc-blog/p/9358524.html
[https_blog.csdn.net_starlh35_article_details_76445267]: https://blog.csdn.net/starlh35/article/details/76445267
[Link 4]: https://www.jianshu.com/p/0468cf485e11
[2019032217285043.png]: /images/20220520/69132d9c3b264164b4258c70e293dcb7.png
[Link 5]: https://comedsh.iteye.com/blog/698733
[https_mp.weixin.qq.com_s_U-mzFnooeQ5GboUu_0yXNw]: https://mp.weixin.qq.com/s/U-mzFnooeQ5GboUu_0yXNw
[MySQL]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw==&mid=2247489236&idx=1&sn=5067c4dffc609d0be9fb01405199cfea&chksm=eb5393e2dc241af47d98ad9a947996c1824daa789fd384b8c0dd8a2fea5cd4faa756c4e8b918&scene=21#wechat_redirect
[innodb_MVCC]: https://www.cnblogs.com/chenpingzhao/p/5065316.html
[https_www.cnblogs.com_xiaotiaosi_p_6394554.html]: https://www.cnblogs.com/xiaotiaosi/p/6394554.html