MySQL -- 12 -- MySQL存储引擎MyISAM和InnoDB在锁方面的区别（8.0）

最新推荐文章于 2023-01-27 14:26:41 发布

Gene Xu

最新推荐文章于 2023-01-27 14:26:41 发布

阅读量202

点赞数

分类专栏： MySQL 文章标签： MySQL MyISAM InnoDB

本文链接：https://blog.csdn.net/Goodbye_Youth/article/details/108127824

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

原文链接：MySQL – 12 – MySQL存储引擎MyISAM和InnoDB在锁方面的区别（8.0）

我们知道 MyISAM 默认使用表级锁，不支持行级锁；InnoDB 默认使用行级锁，同时也支持表级锁，这里让我们来看看 MyISAM 和 InnoDB 在锁方面的具体区别

一、创建测试数据

生成思路
- 利用 MySQL 内存表插入快的特点，可以先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中
- 这里我们来生成两张具有 100w 条数据的学生信息表，两张表除了存储引擎不一样 (一张为 InnoDB，一张为 MyISAM)，其余都一样
如果要生成大量数据，需要提前在 MySQL 配置文件中设置两个参数，然后重启 MySQL
```
tmp_table_size=1024M
max_heap_table_size=1024M
```
- tmp_table_size
  - 内存临时表的最大值，超过该限制后会记录到磁盘当中，存储在指定的 tmpdir 目录下
```
mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.00 sec)
```
- max_heap_table_size
  - 用户可以创建的内存表大小，该值用于计算内存表的最大行数值

创建普通表和内存表

普通表 (InnoDB)

DROP TABLE IF EXISTS tbl_student_innodb;
CREATE TABLE tbl_student_innodb (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid VARCHAR(40) NOT NULL COMMENT '学生唯一标识',
  name VARCHAR(20) NOT NULL COMMENT '学生姓名',
  age INT(3) UNSIGNED NOT NULL COMMENT '学生年龄',
  sex TINYINT(1) UNSIGNED NOT NULL COMMENT '学生性别: 1=男，0=女',
  PRIMARY KEY (id),
  UNIQUE KEY uk_uuid (uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表InnoDB';

普通表 (MyISAM)

DROP TABLE IF EXISTS tbl_student_myisam;
CREATE TABLE tbl_student_myisam (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid VARCHAR(40) NOT NULL COMMENT '学生唯一标识',
  name VARCHAR(20) NOT NULL COMMENT '学生姓名',
  age INT(3) UNSIGNED NOT NULL COMMENT '学生年龄',
  sex TINYINT(1) UNSIGNED NOT NULL COMMENT '学生性别: 1=男，0=女',
  PRIMARY KEY (id),
  UNIQUE KEY uk_uuid (uuid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='学生信息表MyISAM';

内存表

DROP TABLE IF EXISTS tbl_student_memory;
CREATE TABLE tbl_student_memory (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid VARCHAR(40) NOT NULL COMMENT '学生唯一标识',
    name VARCHAR(20) NOT NULL COMMENT '学生姓名',
    age INT(3) UNSIGNED NOT NULL COMMENT '学生年龄',
    sex TINYINT(1) UNSIGNED NOT NULL COMMENT '学生性别: 1=男，0=女',
    PRIMARY KEY (id),
    UNIQUE KEY uk_uuid (uuid)
) ENGINE=Memory DEFAULT CHARSET=utf8 COMMENT='学生信息表';

创建随机函数

生成随机姓名

DROP FUNCTION IF EXISTS generate_student_name;
CREATE FUNCTION generate_student_name(n INT) RETURNS VARCHAR(20)
BEGIN 
    DECLARE source_name VARCHAR(60) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
    DECLARE return_name VARCHAR(20) DEFAULT '' ;
    DECLARE i INT DEFAULT 0; 
    WHILE i < n DO 
        SET return_name = CONCAT(return_name, SUBSTRING(source_name , FLOOR(1 + RAND() * 62 ), 1)); 
        SET i = i +1; 
    END WHILE; 
    RETURN return_name; 
END;

生成随机年龄

DROP FUNCTION IF EXISTS generate_student_age;
CREATE FUNCTION generate_student_age() RETURNS INT(2)
BEGIN 
    DECLARE return_age INT(2) DEFAULT 20;
    SET return_age = return_age + RAND() * 10; 
    RETURN return_age; 
END;

生成随机性别

DROP FUNCTION IF EXISTS generate_student_sex;
CREATE FUNCTION generate_student_sex() RETURNS TINYINT(1)
BEGIN 
    DECLARE return_sex TINYINT(1) DEFAULT 1 ;
    SET return_sex = (CASE WHEN RAND() >= 0.5 THEN 1 ELSE 0 END); 
    RETURN return_sex; 
END;

创建存储过程

DROP PROCEDURE IF EXISTS add_student_record;
CREATE PROCEDURE add_student_record(IN n int)
BEGIN  
    DECLARE i INT DEFAULT 1;
    WHILE (i <= n) DO
      INSERT INTO tbl_student_memory (uuid, name, age, sex ) 
      VALUES (UUID(), generate_student_name(20), generate_student_age(), generate_student_sex());
      SET i = i + 1;
    END WHILE;
END;

调用存储过程
```
CALL add_student_record(1000000);
```

插入普通表中

INSERT INTO tbl_student_innodb SELECT * FROM tbl_student_memory;
INSERT INTO tbl_student_myisam SELECT * FROM tbl_student_memory;

统计学生表

mysql> select count(*) from tbl_student_innodb;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.17 sec)
    
mysql> select count(*) from tbl_student_myisam;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.00 sec)

二、MyISAM 测试

先查询，再更新
- 如下所示，我们先执行 SQL select * from tbl_student_myisam where id between 1 and 50000; 去查询表 tbl_student_myisam 中 id 为 1 到 50000 之间的数据
- 在执行查询 SQL 期间，我们再去执行 SQL update tbl_student_myisam set name = name where id = 50001; 去更新 id 为 50001 的那条数据，在查询 SQL 尚未执行完毕之前，更新 SQL 会被一直阻塞
- 也就是说，在执行查询语句时，MyISAM 会为表加上一个表共享读锁，用于锁住全表，并会阻塞其他进程对于表数据的写操作 (增加、更新、删除)
先查询，再查询
- 如下所示，我们先执行 SQL select * from tbl_student_myisam where id between 1 and 50000; 去查询表 tbl_student_myisam 中 id 为 1 到 50000 之间的数据
- 在执行查询 SQL 期间，我们再去执行 SQL select * from tbl_student_myisam where id = 50001; 去查询 id 为 50001 的那条数据，此时该条查询 SQL 不会被阻塞
- 也就是说，在执行查询语句时，MyISAM 会为表加上一个表共享读锁，用于锁住全表，但不会阻塞其他进程对于表数据的读操作
先更新，再更新
- 如下所示，我们先执行 SQL update tbl_student_myisam set name = name where id between 1 and 1000000; 去更新表 tbl_student_myisam 中 id 为 1 到 1000000 之间的数据
- 在执行查询 SQL 期间，我们再去执行 SQL update tbl_student_myisam set name = name where id = 50001; 去更新 id 为 50001 的那条数据，在查询 SQL 尚未执行完毕之前，更新 SQL 会被一直阻塞
- 也就是说，在执行更新语句时，MyISAM 会为表加上一个表独占写锁，用于锁住全表，并会阻塞其他进程对于表数据的写操作 (增加、更新、删除)
先更新，再查询
- 如下所示，我们先执行 SQL update tbl_student_myisam set name = name where id between 1 and 1000000; 去更新表 tbl_student_myisam 中 id 为 1 到 1000000 之间的数据
- 在执行更新 SQL 期间，我们再去执行 SQL select * from tbl_student_myisam where id = 50001; 去查询 id 为 50001 的那条数据，此时该条查询 SQL 会被阻塞
- 也就是说，在执行更新语句时，MyISAM 会为表加上一个表独占写锁，用于锁住全表，并会阻塞其他进程对于表数据的读操作
除此之外，我们还可以使用 SQL lock table tbl_student_myisam read; 或 lock table tbl_student_myisam write; 来显示地为表添加表共享读锁或表独占写锁；使用 SQL unlock tables; 来显示地为表解锁
那么我们能不能对查询语句加表独占写锁，而不是默认的表共享读锁？
- 答案是肯定的，我们可以在查询语句后加上 for update 来显示地为查询语句添加表独占写锁
```
select * from tbl_student_myisam where id between 1 and 50000 for update;
```

三、InnoDB 测试

由于 InnoDB 支持事务，我们可以将其设置为不自动提交事务 (默认为自动提交事务)，来模拟并发访问的过程
关闭事务自动提交 set autocommit = 0;，此种设置仅对当前会话有效 (想要永久有效，需要修改 MySQL 的配置文件 my.cnf)
先查询，再更新
- 如下所示，我们先执行 SQL select * from tbl_student_innodb where id = 1; 去查询表 tbl_student_innodb 中 id 为 1 的那条数据
- 执行查询语句后，尚未执行 commit; 来提交事务，此时，我们再去执行 SQL update tbl_student_innodb set name = name where id = 1; 去更新 id 为 1 的那条数据
- 结果发现，执行更新语句并未被阻塞，直接执行成功了，这和 MyISAM 不同，原因在于：InnoDB 对查询做了改进，不会对查询进行上锁
- 对两条 SQL 分别 commit; 一下，提交事务
- 那么我们如何对查询语句加共享锁？
  - 我们可以在查询语句后加上 lock in share mode 来显示地为查询语句添加共享锁
```
select * from tbl_student_innodb where id = 1 lock in share mode;
```
  - 此时我们再重复上面的验证，会发现在执行更新语句时会被阻塞，直至 commit; 提交了事务为止
  - 最后对两条 SQL 分别 commit; 一下，提交事务
- 验证行级锁
  - 如下所示，我们先执行 SQL select * from tbl_student_innodb where id = 1 lock in share mode; 去查询表 tbl_student_innodb 中 id 为 1 的那条数据，并显示加上共享锁
  - 执行查询语句后，尚未执行 commit; 来提交事务，此时，我们再去执行 SQL update tbl_student_innodb set name = name where id = 2; 去更新 id 为 2 的那条数据
  - 结果发现，执行更新语句并未被阻塞 (此时去更新 id 为 1 的那条数据会被阻塞)，也就是说，InnoDB 支持行级锁
  - 最后对两条 SQL 分别 commit; 一下，提交事务
先查询，再查询
- 如下所示，我们先执行 SQL select * from tbl_student_innodb where id = 1 lock in share mode; 去查询表 tbl_student_innodb 中 id 为 1 的那条数据，并显示加上共享锁
- 执行查询语句后，尚未执行 commit; 来提交事务，此时，我们再去执行 SQL select * from tbl_student_innodb where id = 1 lock in share mode; 再次查询 id 为 1 的那条数据，并显示加上共享锁
- 结果发现，执行查询语句并未被阻塞，也就是说，在执行查询语句时 (需要走索引)，InnoDB 会为数据行加上一个共享锁，用于锁住数据行，但不会阻塞其他事务对于数据行的读操作
其余两种情况与 MyISAM 相同，此处不再赘述

四、MyISAM 和 InnoDB 的使用场景

MyISAM
- 适用于没有事务的场景
- 适用于频繁执行全表 count 语句的场景
  - InnoDB 不保存表的具体行数，也就是说，当执行 select count(*) from table 时，需要对全表进行扫描，来计算有多少行
  - MyISAM 会保存表的具体行数，也就是说，当执行 select count(*) from table 时，不需要对全表进行扫描，只需要简单地读出保存好的行数即可
- 适用于对数据进行增删改频率不高，但查询频率非常高的场景
InnoDB
- 适用于对可靠性要求较高，且要求支持事务的场景
- 适用于对数据进行增删改查频率都非常高的场景

五、数据库锁的分类

按锁的粒度划分，可分为表级锁、行级锁、页级锁
- MyISAM 仅支持表级锁
- InnoDB 既支持行级锁，又支持表级锁
- BDB 仅支持页级锁
按锁类型划分，可分为共享锁、排他锁
- 共享锁 (Shared Lock)
  - 简称 S 锁，又称读锁，是指允许多个事务并发访问数据，但是只能读取不能修改
  - 即在有事务未释放共享锁前，其他事务只能获取共享锁，而不能获取排它锁
- 排它锁 (Exclusive Lock)
  - 简称 X 锁，又称写锁，是指同一时间内，仅允许单个事务对数据进行读取和修改
  - 即在有事务未释放排它锁前，其他事务无法获取共享锁和排它锁
按加锁方式划分，可分为自动锁、显示锁
- 自动锁
  - 意向锁、表锁以及 insert、update、delete 等操作加上的锁为自动锁，因为这是由 MySQL 自动为我们加上的
- 显示锁
  - select ... for update、lock in share mode 等操作加上的锁为显示锁，因为这是由我们自己加上的
按操作划分，可分为 DML 锁、DDL 锁
- DML 锁
  - DML，即 Data Manipulation Language
  - DML 锁，即对数据进行操作上的锁
- DDL 锁
  - DDL，即 Data Definition Language
  - DDL 锁，即对表结构进行变更上的锁
按使用方式划分，可分为乐观锁、悲观锁
- 乐观锁
  - 总是假设最坏的情况，每次获取数据都认为会被其他线程修改，所有每次获取数据时都会加上锁
- 悲观锁
  - 总是假设最好的情况，每次获取数据都认为不会被其他线程修改，所以不会上锁

六、归纳总结

MyISAM
- MyISAM 仅支持表级锁，可分为表共享读锁 (Table Read Lock) 和表独占写锁 (Table Write Lock)
  - 当某个进程对某表进行读操作时，会为该表加上表共享读锁，不会阻塞其他进程对于该表的读操作，但会阻塞其他进程对于该表的写操作
  - 当某个进程对某表进行写操作时，会为该表加上表独占写锁，会阻塞其他进程对于该表的读、写操作
InnoDB
- InnoDB 既支持行级锁，又支持表级锁，默认使用行级锁
- 在 SQL 没有走索引时，用的是表级锁；走索引时，用的是行级锁以及 Gap 锁
- 针对行级锁而言，可分为共享锁 (Shared Lock) 和排它锁 (Exclusive Lock)
  - 当某个事务对某数据行进行读操作时，不会为该数据行上共享锁，不会阻塞其他事务对于该数据行的读、写操作；可以通过 lock in share mode 来显示地为查询语句添加共享锁，此时不会阻塞其他事务对于该数据行的读操作，但会阻塞其他事务对于该数据行的写操作
  - 当某个事务对某数据行进行写操作时，会为该数据行上排它锁，会阻塞其他事务对于该数据行的读、写操作
- 针对表级锁而言，可分为意向共享锁 (IS) 和意向排他锁 (IX)，统称为意向锁
  - 在对表数据进行操作时，事务会先尝试获取意向锁，如果获取成功，则再添加行锁；如果获取失败，则说明表中某些数据行已被锁住，此时事务会被阻塞
  - 申请意向锁的动作是由 InnoDB 自己完成的，也就是说，当事务为某行数据添加行锁后，InnoDB 会自动为表添加意向锁
  - 意向锁主要用于感知表中数据行加锁的情况，不必去轮询查看每一行数据是否上了行锁，提高了加锁判断的效率

七、参考资料

Gene Xu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL -- 12 -- MySQL存储引擎MyISAM和InnoDB在锁方面的区别（8.0）

我们知道 MyISAM 默认使用表级锁，不支持行级锁；InnoDB 默认使用行级锁，同时也支持表级锁，这里让我们来看看 MyISAM 和 InnoDB 在锁方面的具体区别一、创建测试数据生成思路利用 MySQL 内存表插入快的特点，可以先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中这里我们来生成两张具有 100w 条数据的学生信息表，两张表除了存储引擎不一样 (一张为 InnoDB，一张为 MyISAM)，其余都一样如果要生成大量数据，需要提前在 MySQ
复制链接

扫一扫