mysql 如何理解行级锁和表级锁，如何进行验证；以及innodb和myisam的区别

一、理解概念

行级锁：锁定某一行（或行区间），其他事务可以访问表中的其他行。InnoDB支持行级锁。

表级锁：锁定整个表，其他事务不能访问该表的任何数据（读锁：允许其他事务读，但不允许写；写锁：不允许其他事务读和写）。MyISAM默认使用表级锁，InnoDB在特定情况下也会使用表级锁（如扫描大部分表时可能升级为表锁）。

二、验证方法：

我们分别使用InnoDB和MyISAM引擎创建两张表，模拟两个事务，观察锁的行为。

步骤1：创建测试表
步骤2：使用两个会话（session）模拟两个事务
步骤3：在第一个事务中对某一行进行更新（不提交），然后在第二个事务中尝试更新同一行和不同行，观察阻塞情况。

对于MyISAM（表锁）：

  • 更新表中的任意一行，都会锁住整个表，导致其他事务对该表的所有更新操作被阻塞。

对于InnoDB（行锁）：

  • 更新表中的某一行，只锁定这一行，其他事务更新其他行不会受到阻塞，但更新同一行会被阻塞

三、验证步骤：

实验1：验证InnoDB的行级锁
步骤1：创建innodb_table表
步骤2：插入一些测试数据（至少两行）
步骤3：在两个会话中进行如下操作：
会话1：更新第1行（不提交）
会话2：尝试更新第2行（应该成功，因为行锁只锁第1行）和更新第1行（会被阻塞，直到会话1提交或超时）

实验2：验证MyISAM的表级锁
步骤1：创建myisam_table表（使用MyISAM引擎）
步骤2：插入同样的测试数据
步骤3：在两个会话中进行如下操作：
会话1：更新任意一行（不提交，实际上MyISAM没有事务，所以这里的更新会立即生效并释放锁？但MyISAM在执行更新时会自动加表锁，所以我们需要在不释放锁的情况下观察，因此可以在一个事务中执行多条语句？但MyISAM不支持事务，所以我们需要在同一个会话中连续操作并保持锁持有。实际上，在MyISAM中，每条更新语句执行期间都会锁定整个表，执行完立即释放。所以为了模拟长时间的锁，我们可以使用LOCK TABLES命令或者用一个长时间运行的更新（比如更新表的所有行，并且行数很多，或者人为添加延迟））

但是，由于MyISAM不支持事务，所以不能使用START TRANSACTION来保持锁。我们可以用以下方法模拟：
会话1：使用LOCK TABLES myisam_table WRITE;来显式加写锁，然后执行一条更新（比如更新某一行），此时不释放锁（不执行UNLOCK TABLES）。
会话2：尝试更新同一张表（任意行）就会被阻塞。

但是，如果我们想通过更新操作自动加锁的方式来验证，可以这样做：
会话1：执行一个大的更新，比如更新整个表（用WHERE条件限制，但条件使其执行时间很长，比如用SLEEP函数）。但是MyISAM不支持SLEEP函数，所以我们可以更新很多行（比如没有索引的全表更新）来制造一个长时间运行的更新操作。

不过为了简单，我们使用显式的表锁来演示MyISAM的表级锁行为。

另一种方法是创建一个大表，然后更新整个表（例如，更新条件是没有索引的列，进行全表扫描更新），这样更新会持续一段时间，在这期间锁会一直被持有。

但是为了实验可重复性和简单性，我们采用显式的表锁。

四、具体操作：

1.环境准备

-- 创建测试表
CREATE TABLE innodb_test (id INT PRIMARY KEY, data INT) ENGINE=InnoDB;
CREATE TABLE myisam_test (id INT PRIMARY KEY, data INT) ENGINE=MyISAM;

-- 插入测试数据
INSERT INTO innodb_test VALUES (1,100), (2,200);
INSERT INTO myisam_test VALUES (1,100), (2,200);

2.验证行级锁（InnoDB）

-- 会话1 （开启事务并锁定id=1的行）
START TRANSACTION;
UPDATE innodb_test SET data=101 WHERE id=1;  -- 获得行锁
/* !!! 不要提交事务 !!! */

-- 会话2 （尝试操作不同行 -> 成功）
UPDATE innodb_test SET data=202 WHERE id=2;  -- 可立即执行

-- 会话2 （尝试操作同一行 -> 阻塞）
UPDATE innodb_test SET data=102 WHERE id=1;  -- 被阻塞直到会话1提交

-- 查看锁状态（需开启performance_schema）
SELECT * FROM performance_schema.data_locks\G

结果分析：​​

  • 会话1仅锁定id=1的行
  • 会话2可正常修改id=2的行
  • 会话2尝试修改id=1的行时被阻塞
 3.验证表级锁（MyISAM）
  
-- 会话1 （更新任意行）
UPDATE myisam_test  SET data = (SELECT SLEEP(20)) WHERE id = 1;  -- 获得表锁
/* !!! 保持操作未完成状态 !!! */

-- 会话2 （尝试操作不同行 -> 阻塞！）
UPDATE myisam_test SET data=202 WHERE id=2;  -- 被阻塞直至会话1完成

-- 查看表锁状态
SHOW OPEN TABLES WHERE In_use > 0;

　　

 

 结果分析：​​

  • MyISAM 更新任何行都会锁定整个表
  • 即使修改不同行也会被阻塞
  • SHOW OPEN TABLES显示表被锁定
 

五、关键差异总结

​​特性​​行级锁（InnoDB）表级锁（MyISAM）
​​锁定粒度​​ 单行记录 整个表
​​并发写性能​​ ⭐⭐⭐⭐ (支持并行写不同行) ⭐ (所有写操作串行化)
​​死锁概率​​ 可能发生 不可能发生
​​锁检测方式​​ INNODB_TRX + data_locks SHOW OPEN TABLES
​​更新不同行​​ 允许并行操作 强制串行操作

六、性能对比

​​场景​​InnoDB 表现MyISAM 表现
高并发写入 ✅ 行锁优势明显 ❌ 表锁导致严重阻塞
只读查询（全表扫描） ⚠️ 需经Buffer Pool ✅ 直接OS缓存加速
COUNT(*) 操作 ⚠️ 需遍历行版本 ✅ 直接存储行计数
索引查询（点查/范围查） ✅ 聚簇索引效率高 ❗️ 需回表查询

七、InnoDB和MyISAM核心区别

 

特性InnoDBMyISAM
​​事务支持​​ ✅ ​​完全支持​​ (ACID 兼容) ❌ 不支持
​​锁机制​​ 🔒 ​​行级锁​​ (默认), 支持表级锁 🔒 ​​表级锁​​ (读写会锁定整个表)
​​外键​​ ✅ 支持外键约束 ❌ 不支持
​​崩溃恢复​​ ✅ ​​强健​​ (使用事务日志 redo log 恢复) ❌ ​​脆弱​​ (需要REPAIR TABLE或数据丢失)
​​数据一致性​​ ✅ ​​高​​ (MVCC、事务保证) ⚠️ ​​低​​ (表级锁导致并发问题)
​​索引结构​​ ⭐ ​​聚簇索引​​ (数据存储在索引结构中) ⛓ ​​非聚簇索引​​ (数据与索引分离存储)
​​缓存机制​​ ⚡ ​​缓冲池​​ (缓存数据页和索引页) 🔍 ​​只缓存索引文件​​
​​MVCC​​ ✅ 支持 (实现高并发读) ❌ 不支持
​​数据文件​​ innodb_file_per_table=ON: .ibd<br>innodb_file_per_table=OFF: 系统表空间文件 .frm(表结构), .MYD(数据), .MYI(索引)
​​存储限制​​ ⬆️ ​​大​​ (表空间大小通常受限于操作系统) ⬇️ ​​小​​ (受限于文件系统最大文件尺寸)
​​磁盘空间占用​​ ❗ ​​较高​​ (有额外空间用于回滚段等) ❗ ​​较低​​
​​查询性能​​ 📉 ​​写入​​和高并发​​读​​更优<br>(得益于行锁和MVCC) 📈 简单​​只读​​或全表扫描可能更快<br>(锁开销少且内存占用小)
​​全文索引​​ ✅ MySQL 5.6 及以上版本支持 ✅ 支持 (较早版本中唯一支持全文索引的引擎)
​​适用场景​​ 💼 ​​OLTP 应用​​：需要事务、数据完整性、高并发 📊 ​​OLAP、日志、只读/读多写极少表​​：需要简单快速查询
    详细说明：

  1. ​​事务 (Transactions)​​

    • ​​InnoDB:​​ 完全支持事务处理（遵循 ACID 原则：原子性、一致性、隔离性、持久性）。可以使用 BEGIN, COMMIT, ROLLBACK 等 SQL 语句。
    • ​​MyISAM:​​ ​​不支持事务​​。所有操作（INSERT, UPDATE, DELETE）都是立即提交、不可回滚的。

  2. ​​锁机制 (Locking)​​

    • ​​InnoDB:​​ 默认使用​​行级锁​​。在执行 UPDATE 或 DELETE 时通常只锁定涉及的行。这允许多个用户同时读写表中的不同行，大大提高了并发性。只有在执行 ALTER TABLE 等 DDL 操作或确定需要锁定整个表时才使用表级锁。
    • ​​MyISAM:​​ ​​只支持表级锁​​。任何写操作（INSERT, UPDATE, DELETE, ALTER TABLE）都需要获得表的​​排他锁 (X锁)​​，这会锁定整个表，阻塞其他所有的读写操作。读操作（SELECT)默认获得​​共享锁 (S锁)​​，但多个读操作通常可以同时进行（除非有排他锁在等待）。

  3. ​​外键约束 (Foreign Keys)​​

    • ​​InnoDB:​​ ​​支持外键约束​​，保证了数据引用的完整性（Referential Integrity）。
    • ​​MyISAM:​​ ​​不支持外键约束​​。数据库本身不强制维护表间关系（虽然应用层逻辑上可以有关系）。

  4. ​​崩溃恢复与数据一致性 (Crash Recovery & Data Integrity)​​

    • ​​InnoDB:​​ 具有​​强大的崩溃恢复能力​​。它使用​​事务日志 (Redo Log)​​ 📂 记录所有修改。如果服务器崩溃或意外关闭，重启时 InnoDB 可以利用这些日志自动回滚未完成的事务并重做已提交但尚未完全写入数据文件的事务，最大限度保证数据的​​一致性​​和​​持久性​​。支持 CHECK TABLE, REPAIR TABLE（但通常不需要手动修复）。
    • ​​MyISAM:​​ ​​崩溃恢复能力较弱​​。表级锁机制在崩溃时可能导致数据损坏或不一致的状态（如表数据.MYD和索引.MYI不同步）。崩溃后可能需要进行 CHECK TABLE 和手工 REPAIR TABLE 操作（或使用 myisamchk 工具）来尝试修复数据。数据损坏和丢失的风险更高。

  5. ​​索引结构 (Index Structure)​​

    • ​​InnoDB:​​ 使用​​聚簇索引 (Clustered Index)​​。这意味着表数据本身就存储在索引树的叶子节点上，通常基于主键。因此，通过主键查找非常快（只需要查一次索引树）。非主键索引（二级索引）的叶子节点存储的是主键值，而不是数据的物理地址。当使用二级索引查询时，可能需要进行“回表查询”（通过主键值去主键索引中查找实际数据行）。
    • ​​MyISAM:​​ 使用​​非聚簇索引 (Non-Clustered Index)​​。数据和索引是分开存储的（在.MYD和.MYI文件中）。索引树的叶子节点存储的是指向数据文件.MYD中行位置（例如文件偏移量）的指针。因此，无论是主键索引还是其他索引，在找到索引后都需要通过指针去访问数据文件获取实际数据行（可能会多一次磁盘 I/O）。

  6. ​​缓存 (Caching)​​

    • ​​InnoDB:​​ 实现了一个​​缓冲池 (Buffer Pool)​​，这是一个巨大的内存区域，用于缓存​​表数据​​、​​索引​​信息，以及其他辅助结构（如自适应哈希索引）。通过将最常访问的数据和索引页保存在内存中来大幅减少磁盘 I/O。
    • ​​MyISAM:​​ ​​只缓存索引文件​​。MyISAM 的键缓存 (key_buffer_size) 仅用于缓存 .MYI 文件中的索引块。​**​表数据 .MYD 文件没有内置的、像 InnoDB Buffer Pool 那样专门的数据缓存机制，依赖操作系统的文件系统缓存。

  7. ​​多版本并发控制 (MVCC - Multi-Version Concurrency Control)​​

    • ​​InnoDB:​​ ​​支持 MVCC​​。这是其实现高并发（尤其是高读取并发）的关键机制。它通过在读取操作时提供数据的快照来实现非锁定读（SELECT 操作通常不需要获取锁），避免了读取阻塞写入以及写入阻塞读取。快照基于事务隔离级别（如 READ COMMITTED, REPEATABLE READ）。
    • ​​MyISAM:​​ ​​不支持 MVCC​​。

  8. ​​物理存储 (Physical Storage)​​

    • ​​InnoDB:​​ 结构较复杂。
      • .frm 文件：存储表结构定义（在 MySQL 8.0+ 中，表定义信息移到了数据字典中）。
      • innodb_file_per_table=ON (推荐)：​​每个表存储在自己的表空间文件 .ibd​​，包含表的数据和索引。
      • innodb_file_per_table=OFF：数据存储在共享的​​系统表空间​​文件（如 ibdata1, ibdata2）中（已不推荐）。
      • 有 ​​Redo Log​​ 文件 (ib_logfile0, ib_logfile1)。
      • 有 ​​Undo Log​​ 记录回滚信息（也在系统表空间或单独的 undo 表空间中）。
    • ​​MyISAM:​​ 存储结构简单清晰：
      • .frm 文件：存储表结构定义（MySQL 8.0+ 同上）。
      • .MYD 文件：存储表​​数据​​。
      • .MYI 文件：存储表​​索引​​。
      • 备份或恢复单表容易（只需复制这3个文件）。

  9. ​​磁盘空间占用​​

    • ​​InnoDB:​​ ​​通常占用更多磁盘空间​​。原因包括：数据按页存储会有部分空间未被使用（内部碎片）、支持更复杂的行格式（如 COMPACT, DYNAMIC, COMPRESSED）、存储额外的数据结构（如用于事务的Undo Log）。
    • ​​MyISAM:​​ ​​通常占用更少的磁盘空间​​。存储效率相对较高。

  10. ​​性能特点​​

    • ​​InnoDB:​​ 由于其复杂性和支持更多功能（事务、行锁、崩溃恢复等），在​​写入负载高、并发量大、数据量大且需要保证一致性的场景（典型 OLTP）中表现更优​​。行级锁和 MVCC 使得读写冲突大大减少。对主键查询非常快。对于复杂的、涉及 JOIN 和事务的查询处理能力更强。
    • ​​MyISAM:​​ ​​在纯粹的只读查询（SELECT）或读远多于写的场景下​​，尤其是在运行需要全表扫描的查询时（如COUNT(*))，​​可能比 InnoDB 更快​​。原因是表结构简单、无事务/锁开销、数据文件紧凑并且更直接地利用操作系统缓存。但任何写操作都可能成为严重的瓶颈，因为表锁会导致阻塞。

  11. ​​全文索引 (Full-Text Indexing)​​

    • ​​InnoDB:​​ ​​在 MySQL 5.6 及以后版本原生支持全文索引​​。
    • ​​MyISAM:​​ 在很长一段时间内是 MySQL ​​唯一原生支持全文索引​​的引擎（在 5.6 版本之前）。如果使用的是较旧版本的 MySQL（<5.6），这是选择 MyISAM 的一个重要理由。

八、使用建议

  • ​​绝大多数情况选择 InnoDB:​​ 由于其强大的事务支持、行级锁、崩溃恢复能力和良好的并发性能，​​InnoDB 是现代的、生产级应用的默认选择，尤其适用于任何存在写入操作或需要数据完整性和高并发的场景（如电商、金融系统、用户管理平台等）​​。
  • ​​可能考虑 MyISAM 的有限场景:​​
    • 纯只读表或分析型工作负载（OLAP），且并发度不高，并且需要极高的​​全表扫描性能​​。
    • 数据仓库中的某些特定维度表，几乎没有更新。
    • 只存储日志数据（但需承担数据丢失的风险）。
    • 使用非常旧的 MySQL 版本（<5.5），且确实需要非常快的只读查询并且可以完全放弃事务和安全性。
    • 需要极低的磁盘空间占用（嵌入式等资源受限环境）。

九、 ​​选型指南​​

​​👉 强制使用 InnoDB：​​

  • OLTP 场景（用户中心/订单系统）
  • 需要事务保证（如转账操作）
  • 高并发写入场景
  • 要求崩溃自愈能力
  • 需要外键约束

​​△ 谨慎考虑 MyISAM：​​

  • 只读数据仓库（需配合 CONCURRENT INSERT）
  • 临时日志表（可容忍数据丢失）
  • 遗留系统兼容（MySQL 5.5 及更早版本）
  • 磁盘空间极度敏感（MyISAM 空间占用约少 20%）
📌 ​​重要警示​​：MyISAM 在 2024 年的新项目中已无存在必要，云数据库（如 RDS/Aurora）已全面弃用 MyISAM。MySQL 8.0 系统表也已全部转为 InnoDB。
posted @ 2025-06-05 18:09  joshua317  阅读(163)  评论(0)    收藏  举报