首页 > 资讯 > 数据库 >（9）MySQL进阶篇SQL优化（InnoDB锁-记录锁）

299

分享到

（9）MySQL进阶篇SQL优化（InnoDB锁-记录锁）

（9）MySQL进阶篇SQL优化（InnoDB锁-记录锁） 2019-08-22 09:08:27 299人浏览才女

摘要

1.概述 InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点Mysql与oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则I

1.概述

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点Mysql与oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁！在实际应用程序中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

2. InnoDB行锁实现方式

2.1InnoDB存储引擎的表在不使用索引时使用表锁例子

创建一个临时表：

MySQL [(none)]> CREATE TABLE Goods.tab_no_index (ID INT,Name VARCHAR(50));
Query OK, 0 rows affected (0.02 sec)

插入三条测试数据：

MySQL [(none)]> INSERT INTO goods.tab_no_index (ID,`Name`) VALUES (1,"1"),(2,"2"),(3,"3");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

session_1	session_2
（1）先设置事务T1提交类型为事务非自动提交。	（1）先设置事务T2提交类型为事务非自动提交。
`MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)`	`MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)`
（2）在当前事务T1中查询tab_no_index表的数据行ID=1数据。	（2）在当前事务T2中查询tab_no_index表的数据行ID=2数据。
`MySQL [(none)]> SELECT * FROM goods.tab_no_index WHERE ID=1; +------+------+ \| ID \| Name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)`	`MySQL [(none)]> SELECT * FROM goods.tab_no_index WHERE ID=2; +------+------+ \| ID \| Name \| +------+------+ \| 2 \| 2 \| +------+------+ 1 row in set (0.00 sec)`
（3）在当前事务T1中为tab_no_index表的数据行ID=1加上排他锁。
`MySQL [(none)]> SELECT * FROM goods.tab_no_index WHERE ID=1 FOR UPDATE; +------+------+ \| ID \| Name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)`
	（3）在当前事务T2中为tab_no_index表的数据行ID=2加上排他锁，会发生阻塞超时。
	`MySQL [(none)]> SELECT * FROM goods.tab_no_index WHERE ID=2 FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction`

从上述表格的例子来看，session_1只给ID=1数据行加了排他锁，但session_2在请求ID=2的数据行排他锁时，却出现了锁等待！

2.2InnoDB存储引擎的表在使用索引时使用行锁例子

创建一个临时表：

MySQL [(none)]> CREATE TABLE goods.tab_with_index (ID INT,Name VARCHAR(50));
Query OK, 0 rows affected (0.02 sec)

建立临时表ID列索引：

MySQL [(none)]> ALTER TABLE goods.tab_with_index ADD INDEX Index_ID(ID);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

插入三条测试数据：

MySQL [(none)]> INSERT INTO goods.tab_with_index (ID,`Name`) VALUES (1,"1"),(2,"2"),(3,"3");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

session_1	session_2
（1）先设置事务T1提交类型为事务非自动提交。	（1）先设置事务T2提交类型为事务非自动提交。
`MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)`	`MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)`
（2）在当前事务T1中查询tab_with_index表的数据行ID=1数据。	（2）在当前事务T2中查询tab_with_index表的数据行ID=2数据。
`MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1; +------+------+ \| ID \| Name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)`	`MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=2; +------+------+ \| ID \| Name \| +------+------+ \| 2 \| 2 \| +------+------+ 1 row in set (0.00 sec)`
（3）在当前事务T1中为tab_with_index表的数据行ID=1加上排他锁。
`MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1 FOR UPDATE; +------+------+ \| ID \| Name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)`
	（3）在当前事务T2中为tab_with_index表的数据行ID=2加上排他锁，却并没有发生阻塞超时。
	`MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=2 FOR UPDATE; +------+------+ \| ID \| Name \| +------+------+ \| 2 \| 2 \| +------+------+ 1 row in set (0.00 sec)`

该示例同样跟2小节示例一样，只是ID列加了索引，而session_2在请求ID=2的数据行却没有阻塞！

2.3小结

通过以上两个示例可以了解到：
●在ID列没有建立索引的情况下，InnoDB没有使用到行锁，而是使用到表锁。
●在ID列建立索引的情况下，InnoDB使用到行锁，而是没有使用到表锁。
也就是说，InnoDB存储引擎的表列如果在没有加索引情况下查询，使用到是表锁而不是行锁，会产生阻塞情况，这在并发情况下是灾难的。

4.记录锁

由于mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同记录行，但是如果是使用相同的索引键，是会出现锁冲突的。下面我们通过两个示例来了解下。

4.1InnoDB存储引擎使用相同索引键的阻塞例子

这个示例还是沿用tab_with_index表做演示，ID是非聚集索引列，Name列没有索引，有以下数据：

MySQL [(none)]> SELECT * FROM goods.tab_with_index;
+------+------+
| ID   | Name |
+------+------+
|    1 | 1    |
|    1 | 2    |
|    3 | 3    |
+------+------+
3 rows in set (0.00 sec)

session_1	session_2
（1）先设置事务T1提交类型为事务非自动提交。	（1）先设置事务T2提交类型为事务非自动提交。
`MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)`	`MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)`
（2）在当前事务T1中为tab_with_index表的数据行ID=1 AND `Name`="1" 加上排他锁。
MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1 AND `Name`="1" FOR UPDATE; +------+------+ \| ID \| Name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)
	（2）在当前事务T2中为tab_with_index表的数据行ID=1 AND `Name`="2" 加上排他锁，发生阻塞超时。
	MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1 AND `Name`="2" FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
	（3）在当前事务T2中为tab_with_index表插入以下一行数据，并没有发生阻塞超时。
	MySQL [(none)]> INSERT INTO goods.tab_with_index (ID,`Name`) VALUES (4,"4"); Query OK, 1 row affected (0.00 sec)

从上面示例可以看到，当session_1锁定事务中ID=1记录行时，会阻止session_2事务获取该记录行，而当插入一条ID=4数据时，却没有发生阻塞，成功插入！也就是说当索引数据加上记录锁时，会阻止其他事务对该表该行数据（例如ID=1记录行）进行插入，更新和删除操作。

4.2InnoDB存储引擎的表使用不同索引的阻塞例子

这个示例还是沿用tab_with_index表做演示，ID为主键索引列，Name为非聚集索引列，有以下数据：

MySQL [(none)]> SELECT * FROM goods.tab_with_index;
+----+------+
| ID | Name |
+----+------+
|  1 | 1    |
|  2 | 2    |
+----+------+
2 rows in set (0.00 sec)

session_1	session_2
（1）先设置事务T1提交类型为事务非自动提交。	（1）先设置事务T2提交类型为事务非自动提交。
`MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)`	`MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)`
（2）在当前事务T1中为tab_with_index表的数据行ID=1 加上排他锁。
`MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1 FOR UPDATE; +----+------+ \| ID \| Name \| +----+------+ \| 1 \| 1 \| +----+------+ 1 row in set (0.00 sec)`
	（2）由于tab_with_index表ID=1的记录行在session_1事务中被锁定，当在session_2事务查询Name=’2’记录行时，因为该记录行并不属于ID=1范围记录行之中，所以可以获得tab_with_index表的锁。
	MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE `Name`="2" FOR UPDATE; +----+------+ \| ID \| Name \| +----+------+ \| 2 \| 2 \| +----+------+ 1 row in set (0.00 sec)
	（3）同理，由于访问的Name=’1’记录已经被 session_1事务中被锁定，所以只能等待获得tab_with_index表的锁。
	MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE `Name`="1" FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

5.总结

其实在现实生产环境，即便在条件中使用了索引字段，但是决定是否使用索引来检索数据记录行是由Mysql执行计划来决定的，所以如果MySQL认为全表扫描效率更高，那么它就会优先执行全表扫描操作。比如一些很小的表，哪怕您在条件中使用了索引列，它也不会使用索引，这种情况下InnoDB将会使用表锁，而不是使用行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。还有一种情况在之前索引章节也有说过，如果检索值的数据类型与索引字段值不同，虽然MySQL能够进行数据类型转换，但是却不会使用索引，从而导致InnoDB使用表锁。例如tab_with_index表的name字段有索引，但是name字段是varchar类型的，如果where条件中值是int等值类型，那么就不是和varchar类型进行比较，而会对name进行类型转换，从而要不全表或遍历索引树扫描获取记录行，如下面语句：

-- 全表或遍历索引树扫描
EXPLAIN SELECT * FROM goods.tab_with_index WHERE `Name`=1;
-- 走索引扫描
EXPLAIN SELECT * FROM goods.tab_with_index WHERE `Name`="1";

参考文献：
深入浅出MySQL大全

您可能感兴趣的文档:

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: （9）MySQL进阶篇SQL优化（InnoDB锁-记录锁）

本文链接: https://www.lsjlt.com/news/8403.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布

（9）MySQL进阶篇SQL优化（InnoDB锁-记录锁）

1.概述

2. InnoDB行锁实现方式

2.1InnoDB存储引擎的表在不使用索引时使用表锁例子

2.2InnoDB存储引擎的表在使用索引时使用行锁例子

2.3小结

4.记录锁

4.1InnoDB存储引擎使用相同索引键的阻塞例子

4.2InnoDB存储引擎的表使用不同索引的阻塞例子

5.总结

本篇文章演示代码以及资料文档资料下载

（9）MySQL进阶篇SQL优化（InnoDB锁-记录锁）

（10）MySQL进阶篇SQL优化（InnoDB锁-间隙锁）

（8）MySQL进阶篇SQL优化（InnoDB锁-共享锁、排他锁与意向锁）

（7）MySQL进阶篇SQL优化（InnoDB锁-事务隔离级别）

（6）MySQL进阶篇SQL优化（MyISAM表锁）

（5）MySQL进阶篇SQL优化（优化数据库对象）

MySQL高级篇（SQL优化、索引优化、锁机制、主从复制）

Mysql查询最近一条记录的sql语句(优化篇)

mysql中groupby是什么意思

mysql中having是什么意思

mysql中groupby和having的关系

mysql中and的用法

mysql中having的作用

mysql中gbk什么意思

navicat怎么连接云数据库

navicat表与表之间怎么用视图连接起来

navicat数据库主键怎么设置

navicat怎么回退数据库操作