MySQL唯一索引范围查询的加锁情况

已于 2023-10-26 16:26:32 修改
阅读量214 收藏
点赞数
分类专栏： 数据库 文章标签： mysql 数据库
于 2023-10-26 16:20:16 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/kuun993/article/details/134058539
版权

之前看小林MySQL遇到的一个问题，简单记录一下。

唯一索引范围查询

当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：

  • 情况一：针对「大于等于」的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁。

数据库版本

MySQL 8.0.27
InnoDB 存储引擎

表数据

如下图所示，其中 id 为主键， age 为唯一索引
在这里插入图片描述

验证情况一

主键索引

唯一索引，「大于等于」范围查询，且记录存在的场景，主键索引确实会如上述在等值的位置退化为“X,REC_NOT_GAP”

普通唯一索引

唯一索引，「大于等于」范围查询，且记录存在的场景，普通的唯一索引并不会退化为记录锁，而是使用临键锁“X”，如下图所示。
在这里插入图片描述
普通的唯一索引（age字段）在等值的位置并没有退化成记录锁，仍然使用临键锁，这一点普通的唯一索引和普通索引并没有区别，age的 (10,15] 区间也会被加锁，不能插入新行。

个人认为唯一索引应该和主键索引一样，在等值的位置应该是“记录锁”，因为有唯一性，不会插入新的行，由于记录锁存在也不会删除和更新，可能这是一个可以优化的地方吧。

MySQL是怎么加锁的？

waani
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL索引原理以及查询优化
02-24
一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。索引的目的在于提高查询效率，与我们查阅图
MySQL中的唯一索引的简单学习教程
12-15
mysql 唯一索引UNIQUE一般用于不重复数据字段了我们经常会在数据表中的id设置为唯一索引UNIQUE，下面我来介绍如何在mysql中使用唯一索引UNIQUE吧。 创建唯一索引的目的不是为了提高访问速度，而只是为了避免数据出现重复。唯一索引可以有多个但索引列的值必须唯一，索引列的值允许有空值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE。 把它定义为一个唯一索引。 创建表时直接设置: DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `stu_id` int(1
mysql下普通索引和唯一索引的效率对比
09-11
昨天有位同事说，他的网页查询过程中发现普通索引和唯一索引的效率是有差别的，普通索引比唯一索引快
MySQL索引原理及慢查询优化
12-14
MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的关系型数据库。虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到多的，也是容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。 　　本人从13年7月份起，一直在美团核心业务系统部做慢查询的优化工作，共计十余个系统，累计解决和积累了上百个慢查询案例。随着业务的复杂性提升
MySQL普通索引和唯一索引的深入讲解
12-16
场景 1、维护一个市民系统，有一个字段为身份证号 2、业务代码能保证不会写入两个重复的身份证号（如果业务无法保证，可以依赖数据库的唯一索引来进行约束） 3、常用SQL查询语句：SELECT name FROM CUser WHERE id_card = ‘XXX’ 4、建立索引 身份证号比较大，不建议设置为主键 从性能角度出发，选择普通索引还是唯一索引？ 假设字段k上的值都不重复 查询过程 1、查询语句：SELECT id FROM T WHERE k=5 2、查询过程 通过B+树从树根开始，按层搜索到叶子节点，即上图中右下角的数据页 在数据页内部通过二分法
MySQL怎么加锁的？
一起加油！！！
11-05 1282
next-key lock 会退化为记录锁 或 间隙锁记录存在，会加next-key lock 和 间隙锁记录不存在，会加间隙锁唯一索引 在满足一些条件时，next-key lock 会退化为记录锁 或 间隙锁非唯一索引，如何时候都不会退化。
【MySQL】说透锁机制（二）行锁 加锁规则 之 范围查询（你知道会锁表吗？）
天罡gg的专栏
12-15 4131
本文会按照 聚集集索->唯一索引->普通索引 的顺序 地毯式分析 范围查询中 = 的行锁情况，锁表分析在唯一索引 章节，万字长文，力求分析全面，很硬核全网独一份，别忘了收藏！ 当然如果落下什么欢迎大家评论指出！ 聚集索引 对于 聚集索引下的范围查询 =，无论是否组合，都会遵循如下规则： 所有匹配的索引记录：只有>= 的等值(=)匹配 上Record Lock，其它 上Next-key Lock; 对于 < 和
MYSQL加锁
mywaya2333的博客
11-22 759
mysql加鎖
唯一索引范围查询锁 bug修复了
呼啦呼啦嘞的博客
01-28 531
8.0.24版本 唯一索引范围查询锁 bug 修复了
Mysql修改表结构、添加索引会锁表吗？（不同版本不同）
HumorChen的博客
10-12 8436
我们将分类讨论在业务场景不同版本Mysql修改表结构添加索引是否会锁表 alter table add index 操作。
MySQL唯一索引和普通索引选哪个？
01-21
想象这样一个场景，在设计一张用户表时，每人的身份证号是唯一的，需要搜索。但由于身份证号字段较大，不好将其作为主键。在业务代码已经保证插入身份证唯一的情况下，可以选择建立唯一索引和普通索引，这时该如何选择呢？接下来，将从查询和更新的执行过程进行分析。 查询过程 假设 k 是表 t 上的索引，在搜索 select id from t where k=5 时，会先从 k 这棵 B+ 的树根开始，按层搜索叶子节点，找到 k=5 的数据页，然后在数据页内容进行二分法定位。 对于普通索引，找到 k=5 的记录后，会继续向下查找一个，直到碰到第一个不是 5 的记录结束。 对于唯一索引，由于取值唯一，找到后直
(12) 锁
杉杉coming！
04-14 892
1 概述 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。 如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。 MySQL中的锁，按照锁的粒度分，分为以下三类： 全局锁：锁定数据库中的所有表。 表级锁：每次操作锁住整张表。 行级锁：每次操作锁住对应的行数据。 2 全局锁 2.1 介绍 全局锁就是对整个数据库实例加锁
MySQL——插入加锁/唯一索引插入死锁/批量插入效率
娜娜米的博客
03-08 7448
本篇主要介绍MySQL跟加锁相关的一些概念、MySQL执行插入Insert时的加锁过程、唯一索引下批量插入可能导致的死锁情况，以及分别从业务角度和MySQL配置角度介绍提升批量插入的效率的方法；
MySQL中的锁
C/C++Linux、音视频、DPDK
02-09 343
比如一个事务想要对表加排他锁，如果没有意向锁的话，那么该事务在加锁前需要判断当前表的每一行是否已经加了锁，如果表很大，遍历每行进行判断需要耗费大量的时间。悲观锁是总以最坏的情况假设，比如操作一条数据，总认为也有其他线程要拿这条数据，那就给这条数据上排他锁，让其他事务或者线程阻塞，类似于Java中 synchronized 和 ReentrantLock 等独占锁的思想。元数据锁可以用来保证读写的正确性。行级锁是锁住行，粒度小，性能较高，但是行级锁只在存储引擎层实现，行级锁分为3种，记录锁、间隙锁和临键锁。
Mysql的唯一性索引unique
weixin_33974433的博客
11-16 379
目录 唯一性索引unique影响: unique与primary key的区别： 存在唯一键冲突时，避免策略： insert ignore: replace into: insert on duplicate key update: 结论： 参考： 唯一性索引unique影响: 唯一性索引表创建： DROP TABLE IF EXISTS `sc`; CREATE TABLE `sc` ( ...
MySQL 到底是如何加行级锁、间隙锁、临键锁的
yzx3105的博客
03-23 797
查询的记录存在：在用「唯一索引进行等值查询」时，next-key lock 会退化成「记录锁」查询的记录不存在：在用「唯一索引进行等值查询」时，next-key lock 会退化成「间隙锁」
「MySQL高级篇」你真的知道查询的时候有哪些锁吗？
weixin_43398645的博客
07-15 367
看完上面的演示可能脑袋里面很乱，但是又感觉知道了一些东西，因此我希望大家可以以这里的结论作为向导，对于结论中不理解的部分去上面找到原因，对于结论中文字不理解的地方，以实例作为辅助情况一：针对「大于等于」的范围查询，首先所有的记录都会加上临键锁 但是如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成行锁。情况二：针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中： 当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，
mysql锁问题
unique_1的专栏
11-13 133
waiting for table metadata lock waiting for table flush 若表锁住 或执行没响应 可以通过show processlist命令查看，若出现waiting for table metadata lock或说明是没有获取到metadata lock，可以通过kill pid解决； waiting for table flush也是被阻塞了，可以kill掉该进程 mysql 5.7后 通过 select * from sys.innodb_lock...
《MySQL——加锁规则（待补全，有些没看懂）》
hanhan的博客
04-04 683
catalog加锁规则等值查询间隙锁非唯一索引等值锁主键索引范围锁非唯一索引范围锁唯一索引范围锁 bug非唯一索引上存在"等值"的例子limit语句加锁关于死锁 总结 1、查询过程中访问到的对象才会加锁，而加锁的基本单位是next-key lock（前开后闭）； 2、等值查询上MySQL的优化：索引上的等值查询，如果是唯一索引，next-key lock会退化为行锁，如果不是唯一索引，需要访问到第一个不满足条件的值，此时next-key lock会退化为间隙锁； 3、范围查询：无论是否是唯一索引，范围.
mysql 唯一索引
最新发布
09-08
MySQL中的唯一索引是用于确保表中某列或某些列的值是唯一的。唯一索引可以防止插入重复的数据，并提高查询效率。 在创建表时，可以通过在列定义后添加UNIQUE关键字来创建唯一索引。以下是创建唯一索引的示例语法： CREATE TABLE table_name ( column1 data_type, column2 data_type, ... UNIQUE (column1, column2, ...) ); 可以在多个列上创建唯一索引，以确保这些列的组合值是唯一的。唯一索引可以用于非空列和允许NULL值的列。 另外，也可以使用ALTER TABLE语句来为已存在的表添加唯一索引。以下是添加唯一索引的示例语法： ALTER TABLE table_name ADD UNIQUE (column1, column2, ...); 请注意，在添加唯一索引之前，需要确保表中不存在重复的数据，否则将会导致添加唯一索引失败。 唯一索引在查询时可以提高性能，因为它们会自动创建一个索引结构，以便快速查找和比较唯一值。另外，MySQL还提供了IGNORE关键字，用于在插入数据时忽略唯一索引冲突，而不是抛出错误。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值