mysql中的InnoDB 的UNIQUE唯一索引 是行级锁还是表级锁

最新推荐文章于 2023-01-13 18:32:53 发布
原创 最新推荐文章于 2023-01-13 18:32:53 发布 · 1.5k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文详细解析了MySQL中的锁机制，包括行锁、表锁、页锁等多种类型的锁，并探讨了悲观锁与乐观锁的区别及应用场景。特别是针对InnoDB存储引擎如何在不同事务中使用多种索引来实现高效的行级锁定进行了深入分析。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL InnoDB 存储引擎的索引详解
萌萌白白的博客
02-20 968
在 MySQL 中，是最常用的存储引擎，它支持事务、行级锁和外键约束等功能，而索引则是提升数据库查询性能的关键。在 InnoDB 存储引擎中，索引不仅仅是提高查询速度的工具，还是数据库的核心组成部分之一。本文将详细介绍 InnoDB 存储引擎的索引结构、索引种类、索引优化技巧以及索引失效等方面的知识。
MySQL InnoDB 默认行级锁,行级锁都是基于索引,SQL 语句用不到索引是不会使用行级锁的，而会使用表级锁把整张表锁住
wrh_csdn的博客
03-06 3344
使用select…for update给数据加锁的时候，咱们需要注意锁的级别，MySQL InnoDB 默认行级锁。 行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，而会使用表级锁把整张表锁住，这点需要咱们格外的注意
Mysql的锁
Qiaoshurui的博客
05-22 500
目录MySQL加锁的目的是什么MySQL的锁是基于什么实现的并发事务访问相同记录读读情况写写情况读写情况 MySQL加锁的目的是什么 数据库的锁是为了解决事务的隔离性问题，为了让事务之间相互不影响，每个事务进行操作的时候都会对数据加上一把特有的锁，防止其他事务同时操作数据。 MySQL的锁是基于什么实现的 数据库里面的锁是基于索引实现的，在Innodb中我们的锁都是作用在索引上面的，当我们的SQL命中索引时，那么锁住的就是命中条件内的索引节点(行锁)，如果没有命中索引的话，那我们锁的就是整个索引树（表锁）
MySQL的锁机制 - 记录锁、间隙锁、临键锁
qq_34709713的博客
04-15 1225
记录锁(Record Locks) 记录锁是 封锁记录，记录锁也叫行锁，例如： SELECT * FROM `test` WHERE `id`=1 FOR UPDATE; 它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。 记录锁、间隙锁、临键锁都是排它锁，而记录锁的使用方法跟之前的一篇文章 共享/排它锁 里的排它锁介绍一致，这里就不详细多讲。 间隙锁(Ga...
深入理解 MySQL 主键和唯一(unique)索引
weixin_44798320的博客
03-19 6391
1、主键和唯一索引的区别   1.1 主键一定时唯一性索引，唯一性索引并不一定是主键   1.2 一个表中可以有多个唯一性索引，但只能有一个主键   1.3 主键不允许有空值，而唯一索引允许有空值   1.4 主键可以被其他字段作外键引用，而唯一性索引不能 ...
(12) 锁
杉杉coming！
04-14 999
1 概述 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。 如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。 MySQL中的锁，按照锁的粒度分，分为以下三类： 全局锁：锁定数据库中的所有表。 表级锁：每次操作锁住整张表。 行级锁：每次操作锁住对应的行数据。 2 全局锁 2.1 介绍 全局锁就是对整个数据库实例加锁
MySQL中的唯一索引的简单学习教程
12-15
在MySQL数据库中，唯一索引（UNIQUE）是一种特殊的索引类型，它的主要作用在于确保索引列中的每个值都是唯一的，从而防止数据重复。这与主键索引（PRIMARY KEY）有所不同，主键索引不仅要求唯一性，还强制列的非空性...
当 Mysql - InnoDB 行锁遇到复合主键和多列索引
qq826654664jx的专栏
09-15 890
本文由 简悦 SimpRead 转码， 原文地址 https://segmentfault.com/a/1190000020309580 背景 今天在配合其他项目组做系统压测，过程中出现了偶发的死锁问题。分析代码后发现有复合主键的 update 情况，更新复合主键表时只使用了一个字段更新，同时在事务内又有对该表的 insert 操作，结果出现了偶发的死锁问题。 比如表t_lock_test中有...
MySQL高阶---存储引擎、索引、锁
weixin_42107106的博客
07-05 1217
介绍MySQL高级内容，包括存储引擎、索引、锁三方面
MySQL 行级锁（行锁、临键锁、间隙锁）
weixin_32128491的博客
01-13 1612
锁
MySQL之InnoDB的锁类型与锁原理
积跬步，至千里。
11-26 687
在MySQL中，对于InnoDB引擎，其有共享锁、排他锁、意向锁、记录锁、间隙锁、临键锁等锁，锁的实现原理是基于索引。当用锁不当时会产生死锁。
MySQL行锁深入研究
darkng2015的博客
02-27 88
做项目时由于业务逻辑的需要，必须对数据表的一行或多行加入行锁，举个最简单的例子，图书借阅系统。假设 id=1 的这本书库存为 1 ，但是有 2 个人同时来借这本书，此处的逻辑为 view plaincopy to clipboardprint? Select restnum from book where id =1 ; -- 如果 restnum...
唯一索引引发的行锁
zhaoyangjian724的专栏
03-07 1593
SQL> create table t2(id int,name char(10)); Table created. SQL> create unique index te_idx1 on t2(id); Index created. SQL> select * from v$mystat where rownum<2; SID STATISTIC#
c mysql行锁_MySQL行锁深入研究
weixin_31115505的博客
01-19 312
转载 ：http://www.searchtb.com/2010/09/mysql%E8%A1%8C%E9%94%81%E6%B7%B1%E5%85%A5%E7%A0%94%E7%A9%B6-2.html做项目时由于业务逻辑的需要，必须对数据表的一行或多行加入行锁，举个最简单的例子，图书借阅系统。假设id=1的这本书库存为1，但是有2个人同时来借这本书，此处的逻辑为SELECT restnum ...
MySQL InnoDB中唯一索引和非唯一索引时的加锁情况
匿_名_用_户的专栏
09-18 1万+
MySQL InnoDB中唯一索引和非唯一索引时的加锁情况 MySQL的默认隔离级别为RR，这篇文章讨论一下唯一索引和非唯一索引时，使用lock read（consistent read不加锁，不讨论）时的不同的加锁策略。 前提条件是使用了RR隔离级别,并且使用了索引扫描。 假设有以下表的定义和数据： mysql> create table test1 (year int); my
MySQL数据库唯一性设置(unique index)
热门推荐
对门小刚的专栏
08-15 4万+
我们知道在数据库设计中设置成主键的字段是不会重复的、唯一的，但如果有其他字段也需要保持唯一性应该如何设置呢？比如一个保存学生信息的表里要保证每条记录的学号都不同。这时候就需要对该字段设置唯一性。 设置唯一性后的字段，在数据库层面保证了该字段不会出现相同的值。下面说一下唯一性的设置方法：1，命令行操作分为两种。一种是在建表时就想好要加上唯一性，另一种是在后期才发现需要设置唯一性。 建表时：CREA
mysql unique的用法_SQL UNIQUE 关键字使用方法及示例
weixin_28972031的博客
02-27 6240
SQL UNIQUE 关键字使用方法及示例UNIQUEUNIQUE约束确保一列中的所有值都是唯一的。在CREATE TABLE上的SQL UNIQUE约束创建表"Persons"时，以下SQL在"ID"列上创建UNIQUE约束：SQL Server / Oracle / MS Access：CREATETABLEPersons(IDintNOTNULLUNIQUE,LastNamev...
MySql unique的实现原理简析
weixin_34266504的博客
12-24 275
１、测试过程如下: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=u...
mysql中为什么有UNIQUE索引的表删除数据慢
最新发布
07-24
<think>嗯，用户现在问的是关于MySQL中带有UNIQUE索引的表删除数据慢的原因。之前用户已经讨论过事务隔离级别的问题，现在转向索引性能，特别是删除操作。 用户提供的引用材料里提到了索引类型（NORMAL, UNIQUE, FULLTEXT）的区别，以及索引创建原则。其中引用[2]特别说明唯一索引的主要作用是避免重复数据，而不是提升速度。这可能是关键线索——唯一索引在删除时可能有额外的约束检查。 用户可能在实际业务中遇到了性能瓶颈，比如批量删除数据时速度异常。需要解释清楚背后的机制，而不仅仅是表面原因。 深层需求可能是优化数据库性能。用户身份推测是中级开发或DBA，需要技术细节而非基础概念。 需要结合存储引擎分析（InnoDB为主）。唯一索引删除慢的核心原因有几点：一是唯一性约束检查需要遍历索引树确认无引用；二是MVCC版本检查；三是索引维护成本高。此外，事务隔离级别（如RR）可能加剧锁竞争。 还要注意用户引用中提到的"删除索引"操作（引用[4]），暗示用户可能尝试过删除索引来提速，但这不是根本解法。 最后要给出优化建议：降低索引数量、批处理删除、调整事务策略等。同时补充相关问题引导深入探讨，比如死锁分析或索引优化策略。</think>在 MySQL 中，具有 `UNIQUE` 索引的表删除数据较慢，通常由以下核心原因导致： --- ### 一、主要原因分析 1. **唯一性约束的额外检查** - 删除数据时，MySQL 需确保操作**不会破坏唯一性约束**（如触发重复键冲突）。 - 例如：删除 `id=100` 的记录时，若存在唯一索引 `idx_email`，系统需检查是否有其他记录的 `email` 值与被删记录相同（避免后续插入相同值时报错）。 - **代价**：额外遍历索引树，增加 I/O 开销[^2]。 2. **索引维护成本高** - 删除操作需同步更新**所有相关索引**（包括 `UNIQUE` 索引）。 - 若表有多个索引（如 5 个），删除一行需修改 5 个 B+Tree 结构，远高于无索引表的开销[^1][^3]。 3. **锁竞争与事务隔离** - **行级锁冲突**：删除操作需对数据行和索引加锁，高并发时易阻塞。 - **MVCC 机制**：在 `REPEATABLE READ` 隔离级别下，需检查数据版本可见性，增加 CPU 开销[^1]。 4. **外键关联约束（若有）** - 若被删记录被其他表的外键引用，需逐行检查引用完整性，可能触发级联删除（极耗资源）。 --- ### 二、优化策略 | **优化方向** | **具体措施** | |----------------------|-----------------------------------------------------------------------------| | **减少索引数量** | 定期审查索引，删除未使用或冗余的索引（尤其 `UNIQUE`）[^3] | | **分批删除** | 大表删除时用 `DELETE FROM table WHERE id BETWEEN 1 AND 1000` 分批提交 | | **禁用索引后重建** | ```sql ALTER TABLE table_name DISABLE KEYS; -- 执行删除操作 ALTER TABLE table_name ENABLE KEYS;``` | | **调整隔离级别** | 在事务中临时降低隔离级别（如 `READ COMMITTED`）减少 MVCC 开销 | | **避免外键级联** | 用应用层逻辑替代外键的 `ON DELETE CASCADE` | --- ### 三、性能对比示例 ```sql -- 场景：删除 100 万条数据 -- 无索引表：约 2 秒 DELETE FROM table_no_index; -- 含 3 个索引的表：约 15 秒 DELETE FROM table_with_indexes; -- 含 1 个 UNIQUE 索引的表：约 8 秒（因唯一性检查） DELETE FROM table_with_unique_index; ``` --- ### 四、根本解决思路 1. **权衡索引必要性**：仅对必须保证唯一性的字段使用 `UNIQUE` 索引[^2]。 2. **定期维护表**：对频繁删除的表执行 `OPTIMIZE TABLE` 重建索引碎片。 3. **架构优化**：历史数据归档至日志表，减小主表体积。 > **关键结论**：`UNIQUE` 索引的约束检查是删除慢的主因，需结合索引精简与批量操作优化。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值