我的编程空间，编程开发者的网络收藏夹
学习永远不晚
位置：首页-资讯-数据库

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

2019-08-22 09:30

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号
获取短信验证
立即预约

请填写图片验证码后获取短信验证码

看不清楚，换张图片

免费获取短信验证码

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

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

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大全

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

（9）MySQL进阶篇SQL优化（InnoDB锁-记录锁）
阅读原文内容投诉

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

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

下载Word文档

相关文章

猜你喜欢

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

1.概述InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁！在
（9）MySQL进阶篇SQL优化（InnoDB锁-记录锁）
2019-08-22

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

1.概述当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-K
（10）MySQL进阶篇SQL优化（InnoDB锁-间隙锁）
2020-04-15

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

1.锁的分类锁（Locking）是数据库在并发访问时保证数据一致性和完整性的主要机制。之前MyISAM锁章节已经讲过锁分类，而InnoDB锁按照粒度分为锁定整个表的表级锁（table-level locking）和锁定数据行的行级锁（row-level loc
（8）MySQL进阶篇SQL优化（InnoDB锁-共享锁、排他锁与意向锁）
2022-02-17

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

1.概述在我们在学习InnoDB锁知识点之前，我觉得有必要让大家了解它的背景知识，因为这样才能让我们更系统地学习好它。InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外
（7）MySQL进阶篇SQL优化（InnoDB锁-事务隔离级别 ）
2020-10-31

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

1.MySQL锁概述锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源 （如 CPU、RAM、I/O 等）的抢占以外，数据也是一种供许多用户共享的资源。如何保证数 据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也
（6）MySQL进阶篇SQL优化（MyISAM表锁）
2017-01-17

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

1.概述在数据库设计过程中，用户可能会经常遇到这种问题：是否应该把所有表都按照第三范式来设计？表里面的字段到底改设置为多大长度合适？这些问题虽然很小，但是如果设计不当则可能会给将来的应用带来很多的性能问题。本章中将介绍MySQL中一些数据库对象的优化方法，其中
（5）MySQL进阶篇SQL优化（优化数据库对象）
2014-06-29

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

目录 0 存储引擎介绍1 SQL性能分析2 常见通用的JOIN查询SQL执行加载顺序七种JOIN写法 3 索引介绍3.1 索引是什么3.2 索引优劣势3.3 索引分类和建索引命令语句3.4 索引结构与检索原理3.5 哪些情况适合
2023-08-16

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

2024-04-02

热门标签

Linux(148)PHP(127)Java(102)正则表达式(101)JavaScript(69)最佳实践(67)jQuery(44)MySQL(39)Docker(37)C语言(36)性能优化(34)Python(34)XML(28)string(27)第三方库(23)回调函数(23)ZIP(22)数组(22)可扩展性(22)字符串比较(21)find(20)RPM(20)Go(20)grep(19)ASP.NETCore(19)XML解析器(19)事件(19)事件处理程序(19)StringBuilder(18)Nginx(18)

编程热搜

查看更多

编程资源站

  • 资料下载
  • 历年试题
2021年下半年软考高级信息系统项目管理师高频考点精选资料
2021下半年软考高级信息系统技术知识点记忆口诀精选资料
2021下半年软考《信息系统项目管理师》考试真题及答案精选资料
2021下半年软考高级考试备考攻略精选资料
2021年软考高级《信息系统项目管理师》巩固练习题汇总精选资料
2021下半年软考高级信息系统项目管理师30个易考知识点汇总精选资料
2021下半年软考高级知识点这样记，还担心记不住吗精选资料
2021年下半年软考高级考试重点汇总精选资料
2021下半年软考高级信息系统项目管理师计算公式汇总精选资料
2021年下半年软考高级《信息系统项目管理师》模拟试题精选资料
查看更多
信息系统项目管理师选择题每日一练（2024）历年试题
2023年下半年信息系统项目管理师综合知识真题演练历年试题
查看更多

目录

本网页已闲置超过3分钟，按键盘任意键或点击空白处，即可回到网页
最新资讯更多
    友情链接编程网

    反馈

    我要
    反馈