Mysql InnoDB存储引擎 - 优化和实施细节

博主： silverwq
发布时间：2022 年 07 月 31 日
925 次浏览
暂无评论
7267字数
分类： mysql

概述

mysql5.5.8之后，innodb是默认存储引擎，是具备事务的，并且通过mvcc来实现良好的并发性。
innodb采用表空间保存文件。
innodb支持事务，实现了sql标准的4种隔离级别，使用一种被称为next-key-lock的策略来避免幻读（phantom)现象的产生。
插入缓冲、二次写、自适应哈希索引、预读等高性能高可用的功能。
对于表中数据的存储，InnoDB存储引擎采用了聚集的方式，因此每张表的存储都是按主键的顺序进行存放。
如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID,并以此作为主键。

存储特性

innodb表空间有两种形式

系统表空间ibdataN的形式，例如ibdata1，MySQL5.5.8以前默认的形式，文件位置在：数据目录/ibdataN，ibdataN是全局的表空间文件，所有的表的数据都放在一个文件中，把所有数据都放在一个文件中，会有io的性能等很多问题
独立表空间tablename.ibd的形式，例如erp_test.ibd，MySQL5.6以后，innodb默认的形式，文件位置在：数据目录/数据库名称的文件夹下/tablename.ibd，单独文件保存表数据；还有一个tablename.frm的文件，是用于保存表定义信息。

设置innodb_file_per_table决定表空间的模式，查看变量值

show variables like 'innodb_file_per_table';

可以设置为系统表空间，这个时候再创建表，数据就会被存在ibdata1文件中，就不会有tablename.ibd文件了

set global innodb_file_per_table=off;

如非特殊，一定要用独立表空间，为什么推荐独立表空间：

系统表空间all in one 不利于管理
系统表空间，会产生IO瓶颈
系统表空间很难回收存储空间，独立表空间可以使用optimize table命令回收存储空间

事务

innodb支持事务，默认使用的是行级锁，当更新操作的时候如果用到了索引，则索引到行，否则会锁定到表，这个要非常注意，不然并发时候就会琐死。

理想事务四大特性 ACID

原子性

事务是执行的最小单位，不可再拆分，一组 sql 要么都成功，要么都失败。

原子性是通过 undo log（回滚日志）来保证的。

一致性

是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。
比如，用户 A 和用户 B 在银行分别有 800 元和 600 元，总共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后，最后的结果是用户 A 还有 600 元，用户 B 有 800 元，总共 1400 元，而不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况（该情况，用户 A 和 B 均为 600 元，总共 1200 元）。

一致性则是通过持久性+原子性+隔离性来保证；

隔离性

多个事务同时使用相同的数据时，不能相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。

隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；

持久性

事务一旦提交，事务所做出的修改将会永久保存，此时即使数据库崩溃，修改的数据也不会丢失。

持久性是通过 redo log （重做日志）来保证的；

并发引起的问题

脏读

如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。
因为 mysql 是现在内存里进行修改，变化的可能性比较大，读到这部分数据就叫脏读。

# 会话1：设置隔离级别为读未提交
set session transaction isolation Level read uncommitted;
# 会话1：开启事务
begin;
# 会话1：修改数据，修改第一条的库存为 11
update storage set amount=11 where id = 1

# 会话2：设置隔离级别为读未提交
set session transaction isolation Level read uncommitted ;
会话2：开启事务：
begin
# 会话2：会发现id=1的库存已经被修改了
select * from storage

不可重复读

在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。
两次读取到结果不一样；A事务commit了修改（update/delete），B事务在A事务commit之前和commit之后读取到的内容不一致，所以不可以重复读取。脏读是事务没有提交前的修改被别的事务读取到，但是有一种情况是，在一个事务中同一条sql两次读取到的值不一样，也就是事务A中提交后的修改，被事务B读取到了（读提交）

(会话1)设置隔离级别：set session transaction isolation level read COMMITTED ;
(会话1)开启事务：begin;
(会话1)修改数据：update storage set amount=12 where id = 1，修改第一条的库存为 12
(会话2)设置隔离级别：set session transaction isolation level read COMMITTED ;
(会话2)开启事务：begin
(会话2)查询库存：select * from storage，会发现id=1的库存还是10（假设原来是10）
(会话1)开启事务：commit;
(会话2)查询库存：select * from storage，会发现id=1的库存是12了，已经读取到了修改，因为两次读取的结果不一样，所以也就不可以重复读了

幻读

在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。
同样的条件，查询的结果数量，和更新受到影响的数量不一样；A事务查询结果，和A事务更新新结果不一致，比如查询时候是5条，更新的是，发现是受影响了6条；因为A事务跟新之前，B事务往表里插入了一条，对应的是insert操作；即使插入了一条，但是A事务查询的结果还是5条，这个条插入结果只有对更新有影响，就感觉出现了幻觉

(会话1)设置隔离级别：set session transaction isolation level REPEATABLE read;
(会话1)开启事务：begin;
(会话1)查询总条数：select * from storage，结果总共是5条记录（按理来说这时候修改库存数量都加1的话，受影响的数量会是5条记录，但是这个时候如果事务B中如果插入一条库存的话，修改库存数量都加1的话，受影响的数量确实是6条，明明我查询的总的的数量是5条，却受影响了6条，就感觉是幻觉一样）
(会话2)设置隔离级别：set session transaction isolation REPEATABLE read ;
(会话2)开启事务：begin
(会话2)插入数据：insert into storage values (6,'背包',10,0);
(会话1)提交事务：commit;要提交，不然事务A的所有记录的更新会被锁住
(会话1)每条记录增加1个库存：update storage set amount = amount + 1 where 1，受到影响6条，和查询的总的数量不一样

隔离级别

是为了解决上面的这些问题的，通过以下sql，可以查看隔离级别

select @@tx_isolation;

读未提交read-uncommitted

最低的隔离级别，一个事务的修改还没有提交，就被其它事务读取（脏读）
这个隔离级别没有使用锁
存在的问题：脏读、不可重复读、幻读的问题
会有脏读的问题

读已提交read-committed

保证数据提交之后才能被其它事务读取，另外一个事务不能读取未提交的数据
解决了脏读的问题
存在的问题：不可重复读、幻读

可重复读（repeatable-read）

这个是默认的隔离级别
能够保持事务的原子性，保证事务里每次读取到数据一致，不会受到其它事务的影响
解决了脏读和不可重复读的问题；
存在的问题：幻读

串行化（serializable）

事务100%被隔离
不会有脏读，不可重复，幻读三个问题
实现方式：其实是通过排队来执行事务，也就没有了并发了，也没有这些并发的问题了
缺点：所有的select语包都会被隐式的转化为select ... lock in share mode,会和update、delete互斥，会导致数据库的性能非常的低，生产环境不可用

行锁

innodb中是行锁，可以通过如下语句查看行锁的情况

show status like 'innodb_row_lock%';

共享锁

又叫做Share lock，简称S锁，又称做读锁，如果希望某条记录不让人家修改，只能读取，可以设置行锁，不会死锁。读锁是乐观锁（觉得一般会没有问题，如果有问题，等发现问题的时候再说）

(加锁会话)开启事务：begin
(加锁会话)加共享锁：select * FROM storage WHERE id = 1 LOCK in SHARE MODE;
(加锁会话)读取数据：select * FROM storage WHERE id = 1;，是可以正常的读取到数据
(加锁会话)写数据：update storage set amount = amount + 1 WHERE id = 1;，是可以正常的写数据

(其它会话)开启事务：begin
(其它会话)读取数据：select * FROM storage WHERE id = 1 LOCK in SHARE MODE;或者不加LOCK in SHARE MODE都可以正常的返回数据
(其它会话)修改数据：update storage set amount = amount + 1 WHERE id = 1;，会阻塞等待

(加锁会话)提交事务释放锁： commit，其它会话的阻塞等待更新就会执行

排它锁

又叫做eXclusive lock，简记为X锁，又称写锁。并发时候，想修改某个数据之前，先锁定该行记录，别人就无法获取到写锁和共享锁了，delete / update / insert操作，会默认加上X锁，所以避免并发的去修改同一条记录，否则会造成阻塞。写锁是悲观锁（总是觉会出问题，所以干脆自己用这个条数据的时候，别人还是等我用完再说）。
排它锁之间，会出现死锁：要注意代码逻辑（例如：加锁会话锁定了a的同时，其它会话锁了b，这个时候加锁会话要获取b的锁的时候就要等待其它会话释放锁，可这个时候b又要获取a的排它锁，这个时候就死锁了）

begin; -- 会话1

SELECT * FROM `test` WHERE a = 2 FOR UPDATE; -- 会话1
begin; -- 会话2
SELECT * FROM `test` WHERE a = 3 FOR UPDATE; -- 会话2
SELECT * FROM `test` WHERE a = 3 FOR UPDATE; -- 会话1，会等待会话2的锁释放
SELECT * FROM `test` WHERE a = 2 FOR UPDATE; -- 会话2，会等待会话1的锁释放，互相等待，出现死锁

为了避免这种情况，可以按顺序加锁，或者利用where in (2,3)一次性锁住，在in里面的列表值mysql是会自动从小到大排序，加锁也是一条条从小到大加的锁

(加锁会话)开启事务：begin
(加锁会话)加写锁：select * FROM storage WHERE id = 1 for update;

(其它会话)开启事务：begin
(其它会话)不加锁：select * FROM storage WHERE id = 1;，正常返回
(其它会话)加共享锁：select * FROM storage WHERE id = 1 LOCK in SHARE MODE;，会阻塞等待锁释放
(其它会话)加写锁：select * FROM storage WHERE id = 1 for update;，会阻塞等待锁释放

(加锁会话)释放锁：commit

锁的算法

记录锁

唯一性索引(唯一/主键)等值查询，精准匹配，精准匹配到一条记录的时候，锁住的是索引的记录，锁定一条记录；例如：

 select * from test where a = 1 for update;

间隙锁 ，锁定一个区间，数据不存在的记录也会被锁住，最主要的目的是阻塞插入，没有匹配到记录的时候退化成间隙锁;；尽量要避免间隙锁引发死锁，间隙锁只有在可重复读隔离级别里才有

(会话1)开启事务：begin
(会话1)锁定区间(1,9)：select * from storage where id > 1 and id<9 for update;

(会话2)开启事务：beging
(会话2)在区间(1,9)内插入数据： insert into storage values (7,'数据线',10,0);，会阻塞，无法插入

临键锁

范围查询，包含记录锁和间隙锁，左开右闭的算法，这个是inndb默认的算法，与间隙锁的区别是，加了右边的边界锁定功能，也就是左开有闭。

where id > 1 and id < 4，这个时候select * from storage where id = 4 for update也会被锁住

临建锁在开发中会有死锁的问题，经常会做这类的判断需求，根据字段值查询（有索引），如果不存在，则插入，否则更新，如果不加锁的话，并发时候将导致重复。

-- 假设表里的记录是1，2，3，4，5；也就是最大值是5；6不存在
begin; -- 会话1
SELECT * FROM `test` WHERE a = 6 FOR UPDATE; -- 会话1，锁定（5，无穷大）
begin; -- 会话2
SELECT * FROM `test` WHERE a = 6 FOR UPDATE;-- 会话2，锁定（5，无穷大）
INSERT INTO `test` (`a`, `b`, `amount`, `version`) VALUES (6, 'f', 10, 0); -- 会话1，等待会话2锁释放
INSERT INTO `test` (`a`, `b`, `amount`, `version`) VALUES (6, 'f', 10, 0);-- 会话2，等待会话1锁释放，互相依赖，出现死锁

解决办法：用mysql特有的语法来解决此问题，如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执行insert语句而不是更新语句

-- 是不能写where条件的
INSERT INTO `test` (`a`, `b`, `amount`, `version`) VALUES (6, 'f', 10, 0) on duplicate key update a=6,b='f',amount=10,version=0;

适用场景

innodb存储引擎适合绝大多场景
5.7版本后，innodb也支持全文索引与空间函数，之前的版本只有myisam才支持

常见的问题

场景一

代码返回修改了成功了，当时为啥数据库显示的值没有被修改？

比如一条数据的初始值是null，事务a和事务b同时更新这个条数据；
事务a先把该条数据更新为a值；
然后，事务b将这条数据更新为b值，并且提交成功；
这个时候，事务a回滚，这条数据又变回了null；

最后修改：2023 年 12 月 30 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

Mysql InnoDB存储引擎 - 优化和实施细节

silverwq • 2022 年 07 月 31 日

<h2>概述</h2>
<ol>
<li>mysql5.5.8之后，innodb是默认存储引擎，是具备事务的，并且通过mvcc来实现良好的并发性。</li>
<li>innodb采用表空间保存文件。</li>
<li>innodb支持事务，实现了sql标准的4种隔离级别，使用一种被称为next-key-lock的策略来避免幻读（phantom)现象的产生。</li>
<li>插入缓冲、二次写、自适应哈希索引、预读等高性能高可用的功能。</li>
<li>对于表中数据的存储，InnoDB存储引擎采用了聚集的方式，因此每张表的存储都是按主键的顺序进行存放。</li>
<li>如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID,并以此作为主键。</li>
</ol>
<h2>存储特性</h2>
<p>innodb表空间有两种形式</p>
<ol>
<li>系统表空间ibdataN的形式，例如ibdata1，MySQL5.5.8以前默认的形式，文件位置在：<code>数据目录/ibdataN</code>，ibdataN是全局的表空间文件，所有的表的数据都放在一个文件中，把所有数据都放在一个文件中，会有io的性能等很多问题</li>
<li>独立表空间tablename.ibd的形式，例如erp_test.ibd，MySQL5.6以后，innodb默认的形式，文件位置在：<code>数据目录/数据库名称的文件夹下/tablename.ibd</code>，单独文件保存表数据；还有一个tablename.frm的文件，是用于保存表定义信息。</li>
</ol>
<p>设置innodb_file_per_table决定表空间的模式，查看变量值</p>
<pre><code class="language-sql">show variables like 'innodb_file_per_table';</code></pre>
<p>可以设置为系统表空间，这个时候再创建表，数据就会被存在ibdata1文件中，就不会有tablename.ibd文件了</p>
<pre><code class="language-sql">set global innodb_file_per_table=off;</code></pre>
<p>如非特殊，一定要用独立表空间，为什么推荐独立表空间：</p>
<ol>
<li>系统表空间all in one 不利于管理</li>
<li>系统表空间，会产生IO瓶颈</li>
<li>系统表空间很难回收存储空间，独立表空间可以使用optimize table命令回收存储空间</li>
</ol>
<h2>事务</h2>
<p>innodb支持事务，默认使用的是行级锁，当更新操作的时候如果用到了索引，则索引到行，否则会锁定到表，这个要非常注意，不然并发时候就会琐死。</p>
<h3>理想事务四大特性 ACID</h3>
<h4>原子性</h4>
<p>事务是执行的最小单位，不可再拆分，一组 sql 要么都成功，要么都失败。</p>
<p>原子性是通过 undo log（回滚日志） 来保证的。</p>
<h4>一致性</h4>
<p>是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。<br />
比如，用户 A 和用户 B 在银行分别有 800 元和 600 元，总共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后，最后的结果是用户 A 还有 600 元，用户 B 有 800 元，总共 1400 元，而不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况（该情况，用户 A 和 B 均为 600 元，总共 1200 元）。</p>
<p>一致性则是通过持久性+原子性+隔离性来保证；</p>
<h4>隔离性</h4>
<p>多个事务同时使用相同的数据时，不能相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。</p>
<p>隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；</p>
<h4>持久性</h4>
<p>事务一旦提交，事务所做出的修改将会永久保存，此时即使数据库崩溃，修改的数据也不会丢失。</p>
<p>持久性是通过 redo log （重做日志）来保证的；</p>
<h3>并发引起的问题</h3>
<h4>脏读</h4>
<p><strong>如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。</strong><br />
因为 mysql 是现在内存里进行修改，变化的可能性比较大，读到这部分数据就叫脏读。</p>
<pre><code class="language-sql"># 会话1：设置隔离级别为读未提交
set session transaction isolation Level read uncommitted;
# 会话1：开启事务
begin;
# 会话1：修改数据，修改第一条的库存为 11
update storage set amount=11 where id = 1

# 会话2：设置隔离级别为读未提交
set session transaction isolation Level read uncommitted ;
会话2：开启事务：
begin
# 会话2：会发现id=1的库存已经被修改了
select * from storage</code></pre>
<h4>不可重复读</h4>
<p><strong>在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。</strong><br />
两次读取到结果不一样；A事务commit了修改（update/delete），B事务在A事务commit之前和commit之后读取到的内容不一致，所以不可以重复读取。脏读是事务没有提交前的修改被别的事务读取到，但是有一种情况是，在一个事务中同一条sql两次读取到的值不一样，也就是事务A中提交后的修改，被事务B读取到了（读提交）</p>
<pre><code class="language-sql">(会话1)设置隔离级别：set session transaction isolation level read COMMITTED ;
(会话1)开启事务：begin;
(会话1)修改数据：update storage set amount=12 where id = 1，修改第一条的库存为 12
(会话2)设置隔离级别：set session transaction isolation level read COMMITTED ;
(会话2)开启事务：begin
(会话2)查询库存：select * from storage，会发现id=1的库存还是10（假设原来是10）
(会话1)开启事务：commit;
(会话2)查询库存：select * from storage，会发现id=1的库存是12了，已经读取到了修改，因为两次读取的结果不一样，所以也就不可以重复读了</code></pre>
<h4>幻读</h4>
<p><strong>在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。</strong><br />
同样的条件，查询的结果数量，和更新受到影响的数量不一样；A事务查询结果，和A事务更新新结果不一致，比如查询时候是5条，更新的是，发现是受影响了6条；因为A事务跟新之前，B事务往表里插入了一条，对应的是insert操作；即使插入了一条，但是A事务查询的结果还是5条，这个条插入结果只有对更新有影响，就感觉出现了幻觉</p>
<pre><code class="language-sql">(会话1)设置隔离级别：set session transaction isolation level REPEATABLE read;
(会话1)开启事务：begin;
(会话1)查询总条数：select * from storage，结果总共是5条记录（按理来说这时候修改库存数量都加1的话，受影响的数量会是5条记录，但是这个时候如果事务B中如果插入一条库存的话，修改库存数量都加1的话，受影响的数量确实是6条，明明我查询的总的的数量是5条，却受影响了6条，就感觉是幻觉一样）
(会话2)设置隔离级别：set session transaction isolation REPEATABLE read ;
(会话2)开启事务：begin
(会话2)插入数据：insert into storage values (6,'背包',10,0);
(会话1)提交事务：commit;要提交，不然事务A的所有记录的更新会被锁住
(会话1)每条记录增加1个库存：update storage set amount = amount + 1 where 1，受到影响6条，和查询的总的数量不一样</code></pre>
<h3>隔离级别</h3>
<p>是为了解决上面的这些问题的，通过以下sql，可以查看隔离级别</p>
<pre><code class="language-sql">select @@tx_isolation;</code></pre>
<h4>读未提交read-uncommitted</h4>
<ol>
<li>最低的隔离级别，一个事务的修改还没有提交，就被其它事务读取（脏读）</li>
<li>这个隔离级别没有使用锁</li>
<li>存在的问题：脏读、不可重复读、幻读的问题<br />
会有脏读的问题</li>
</ol>
<h4>读已提交read-committed</h4>
<ol>
<li>保证数据提交之后才能被其它事务读取，另外一个事务不能读取未提交的数据</li>
<li>解决了脏读的问题</li>
<li>存在的问题：不可重复读、幻读</li>
</ol>
<h4>可重复读（repeatable-read）</h4>
<ol>
<li>这个是默认的隔离级别</li>
<li>能够保持事务的原子性，保证事务里每次读取到数据一致，不会受到其它事务的影响</li>
<li>解决了脏读和不可重复读的问题；</li>
<li>存在的问题：幻读</li>
</ol>
<h4>串行化（serializable）</h4>
<ol>
<li>事务100%被隔离</li>
<li>不会有脏读，不可重复，幻读三个问题</li>
<li>实现方式：其实是通过排队来执行事务，也就没有了并发了，也没有这些并发的问题了</li>
<li>缺点：所有的select语包都会被隐式的转化为select ... lock in share mode,会和update、delete互斥，会导致数据库的性能非常的低，生产环境不可用</li>
</ol>
<h2>行锁</h2>
<p>innodb中是行锁，可以通过如下语句查看行锁的情况</p>
<pre><code class="language-sql">show status like 'innodb_row_lock%';</code></pre>
<p><img src="https://www.xiaoqiuyinboke.cn/usr/uploads/2022/09/2276757452.png" alt="l8e3geit.png" loading="lazy"  style=""></p>
<h3>共享锁</h3>
<p>又叫做Share lock，简称S锁，又称做读锁，如果希望某条记录不让人家修改，只能读取，可以设置行锁，不会死锁。读锁是乐观锁（觉得一般会没有问题，如果有问题，等发现问题的时候再说）</p>
<pre><code class="language-sql">(加锁会话)开启事务：begin
(加锁会话)加共享锁：select * FROM storage WHERE id = 1 LOCK in SHARE MODE;
(加锁会话)读取数据：select * FROM storage WHERE id = 1;，是可以正常的读取到数据
(加锁会话)写数据：update storage set amount = amount + 1 WHERE id = 1;，是可以正常的写数据

(其它会话)开启事务：begin
(其它会话)读取数据：select * FROM storage WHERE id = 1 LOCK in SHARE MODE;或者不加LOCK in SHARE MODE都可以正常的返回数据
(其它会话)修改数据：update storage set amount = amount + 1 WHERE id = 1;，会阻塞等待

(加锁会话)提交事务释放锁： commit，其它会话的阻塞等待更新就会执行</code></pre>
<h3>排它锁</h3>
<p>又叫做eXclusive lock，简记为X锁，又称写锁。并发时候，想修改某个数据之前，先锁定该行记录，别人就无法获取到写锁和共享锁了，delete / update / insert操作，会默认加上X锁，所以避免并发的去修改同一条记录，否则会造成阻塞。写锁是悲观锁（总是觉会出问题，所以干脆自己用这个条数据的时候，别人还是等我用完再说）。<br />
排它锁之间，会出现死锁：要注意代码逻辑（例如：加锁会话锁定了a的同时，其它会话锁了b，这个时候加锁会话要获取b的锁的时候就要等待其它会话释放锁，可这个时候b又要获取a的排它锁，这个时候就死锁了）</p>
<pre><code class="language-sql">begin; -- 会话1

SELECT * FROM `test` WHERE a = 2 FOR UPDATE; -- 会话1
begin; -- 会话2
SELECT * FROM `test` WHERE a = 3 FOR UPDATE; -- 会话2
SELECT * FROM `test` WHERE a = 3 FOR UPDATE; -- 会话1，会等待会话2的锁释放
SELECT * FROM `test` WHERE a = 2 FOR UPDATE; -- 会话2，会等待会话1的锁释放，互相等待，出现死锁</code></pre>
<p>为了避免这种情况，可以按顺序加锁，或者利用where in (2,3)一次性锁住，在in里面的列表值mysql是会自动从小到大排序，加锁也是一条条从小到大加的锁</p>
<pre><code class="language-sql">(加锁会话)开启事务：begin
(加锁会话)加写锁：select * FROM storage WHERE id = 1 for update;

(其它会话)开启事务：begin
(其它会话)不加锁：select * FROM storage WHERE id = 1;，正常返回
(其它会话)加共享锁：select * FROM storage WHERE id = 1 LOCK in SHARE MODE;，会阻塞等待锁释放
(其它会话)加写锁：select * FROM storage WHERE id = 1 for update;，会阻塞等待锁释放

(加锁会话)释放锁：commit</code></pre>
<h3>锁的算法</h3>
<h4>记录锁</h4>
<p>唯一性索引(唯一/主键)等值查询，精准匹配，精准匹配到一条记录的时候，锁住的是索引的记录，锁定一条记录；例如：</p>
<pre><code class="language-sql"> select * from test where a = 1 for update;</code></pre>
<p><strong>间隙锁</strong> ，锁定一个区间，数据不存在的记录也会被锁住，最主要的目的是阻塞插入，没有匹配到记录的时候退化成间隙锁;；尽量要避免间隙锁引发死锁，间隙锁只有在可重复读隔离级别里才有</p>
<pre><code class="language-sql">(会话1)开启事务：begin
(会话1)锁定区间(1,9)：select * from storage where id &gt; 1 and id&lt;9 for update;

(会话2)开启事务：beging
(会话2)在区间(1,9)内插入数据： insert into storage values (7,'数据线',10,0);，会阻塞，无法插入</code></pre>
<p><img src="https://www.xiaoqiuyinboke.cn/usr/uploads/2022/09/882899594.png" alt="l8e2s19a.png" loading="lazy"  style=""></p>
<h4>临键锁</h4>
<p>范围查询，包含记录锁和间隙锁，左开右闭的算法，这个是inndb默认的算法，与间隙锁的区别是，加了右边的边界锁定功能，也就是左开有闭。</p>
<pre><code class="language-sql">where id &gt; 1 and id &lt; 4，这个时候select * from storage where id = 4 for update也会被锁住</code></pre>
<p><img src="https://www.xiaoqiuyinboke.cn/usr/uploads/2022/09/2997265556.png" alt="l8e2t2gj.png" loading="lazy"  style=""><br />
临建锁在开发中会有死锁的问题，经常会做这类的判断需求，根据字段值查询（有索引），如果不存在，则插入，否则更新，如果不加锁的话，并发时候将导致重复。</p>
<pre><code class="language-sql">-- 假设表里的记录是1，2，3，4，5；也就是最大值是5；6不存在
begin; -- 会话1
SELECT * FROM `test` WHERE a = 6 FOR UPDATE; -- 会话1，锁定（5，无穷大）
begin; -- 会话2
SELECT * FROM `test` WHERE a = 6 FOR UPDATE;-- 会话2，锁定（5，无穷大）
INSERT INTO `test` (`a`, `b`, `amount`, `version`) VALUES (6, 'f', 10, 0); -- 会话1，等待会话2锁释放
INSERT INTO `test` (`a`, `b`, `amount`, `version`) VALUES (6, 'f', 10, 0);-- 会话2，等待会话1锁释放，互相依赖，出现死锁</code></pre>
<p>解决办法：用mysql特有的语法来解决此问题，如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执行insert语句而不是更新语句</p>
<pre><code class="language-sql">-- 是不能写where条件的
INSERT INTO `test` (`a`, `b`, `amount`, `version`) VALUES (6, 'f', 10, 0) on duplicate key update a=6,b='f',amount=10,version=0;</code></pre>
<h2>适用场景</h2>
<ol>
<li>innodb存储引擎适合绝大多场景</li>
<li>5.7版本后，innodb也支持全文索引与空间函数，之前的版本只有myisam才支持</li>
</ol>
<h2>常见的问题</h2>
<h3>场景一</h3>
<p>代码返回修改了成功了，当时为啥数据库显示的值没有被修改？</p>
<ol>
<li>比如一条数据的初始值是null，事务a和事务b同时更新这个条数据；</li>
<li>事务a先把该条数据更新为a值；</li>
<li>然后，事务b将这条数据更新为b值，并且提交成功；</li>
<li>这个时候，事务a回滚，这条数据又变回了null；</li>
</ol>

概述

存储特性

事务

理想事务四大特性 ACID

原子性

一致性

隔离性

持久性

并发引起的问题

脏读

不可重复读

幻读

隔离级别

读未提交read-uncommitted

读已提交read-committed

可重复读（repeatable-read）

串行化（serializable）

行锁

共享锁

排它锁

锁的算法

记录锁

临键锁

适用场景

常见的问题

场景一

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Mysql InnoDB存储引擎 - 优化和实施细节

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款