前言
SQL（Structured Query Language）是一种用于管理关系型数据库的标准化语言，它用于定义、操作和管理数据库中的数据。SQL是一种通用的语言，可以用于多种关系型数据库管理系统（RDBMS），如MySQL、Oracle、SQL Server等。
MySQL是一种开源的关系型数据库管理系统，它使用SQL作为其查询语言。MySQL是最流行的开源数据库之一，它具有高性能、可靠性和可扩展性。MySQL支持多用户、多线程和多表操作，可以在各种操作系统上运行。
MySQL最为最流行的开源数据库，其重要性不言而喻，也是大多数程序员接触的第一款数据库，深入认识和理解MySQL也比较重要。
本篇博客分析MySQL的两种引擎，MyISAM和InnoDB引擎的区别。
本系列文章合集如下：
【合集】MySQL的入门进阶强化——从 普通人 到 超级赛亚人 的 华丽转身
目录
引出
1.MyISAM 只有表级锁，而InnoDB 支持行级锁和表级锁，默认为行级锁；
2.MyISAM 不提供事务支持。而InnoDB提供事务支持；
3.MyISAM不支持外键，而InnoDB支持；
4.MyISAM不支持聚集索引，InnoDB支持聚集索引；
5MyISAM不支持MVCC，InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效；
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 事务安全 | 支持 | 无 |
| 存储限制 | 64TB | 有 |
| 空间使用 | 高 | 低 |
| 内存使用 | 高 | 低 |
| 插入数据的速度 | 低 | 高 |
| 对外键的支持 | 支持 | 无 |
1.是否支持行级锁
MyISAM 只有表级锁，而InnoDB 支持行级锁和表级锁，默认为行级锁。
（1）MySQL大致可以归纳为以下3种锁
- 表级锁：开销小，加锁快；不会出现死锁；锁的粒度大，发生锁冲突的概率最高，并发度最低。
- 行级锁：开销大，加锁慢；会出现死锁；锁的粒度小，发生锁冲突的概率最低，并发度最高。
- 页面锁：开销 和加锁时间界于表锁和行锁之间；会出现死锁，锁定的粒度界于表锁和行锁之间，并发一般。
（2）表锁
MyISAM会在执行select语句前，会自动给涉及的表加读锁，在执行增删改操作前会自动给涉及的表加写锁。
- MySQL的表锁有两种模式：
- 表共享读锁
- 表独占写锁
- 读锁会阻塞写，写锁会阻塞读和写。
- 对MyISAM表的读操作，不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它线程的写操作。
- 对MyISAM表的写操作，会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
读锁的演示
窗口1执行
窗口2的语句被阻塞
关闭窗口1，窗口2才执行成功
执行unlock tables释放锁
释放后才成功
create table dept(
deptno int not null auto_increment,
dname varchar(20),
loc varchar(20),
primary key(deptno)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
# 打开两个会话窗口
# 窗口1执行以下语句
lock table dept read;
select * from dept;
# 窗口2执行执行以下语句
select * from dept;
insert into dept values(null,'财务部','北京');
# 此时，窗口2的请求被阻塞，必须等待会话1释放锁后才能执行;
# 释放会话1的锁，并观察会话2的执行结果。
unlock tables;
写锁的演示
会话1加写锁，会话2读操作，会被阻塞
会话1 释放锁
# 会话1加写锁
lock table dept write;
delete from dept where deptno = 1;
# 会话2读操作，会被阻塞
select * from dept;
# 会话1 释放锁
unlock tables;
# 观察会话2 查询结果
注意：如果持有表锁的session异常终止的话（比如说执行了“ctrl+z”），那么该session是不会主动释放锁的，这时候我们可以重启mysql服务器，不推荐。可以通过show processlist 命令来查看线程ID，通过kill 【线程ID】
总结：MyISAM不适合写表的引擎，写锁后，其它线程不能做任何操作。
（3）行锁
会出现死锁，发生锁冲突几率低，并发高。
- MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引选择的行上的，如果SQL语句没有走索引，则会进行全表扫描，行锁则无法实现，取而代之的是表锁；此时其它事物无法对当前表进行更新操作。
- 如果使用的是非主键索引，则行锁转为表锁。
新建一张表
插入两条数据
会话1的事务未提交，由于不是同一行，所以会话2
# 会话1 ，执行update，事务未提交。
start transaction;
update mylock set name = 'hello' where id = 1;
# 会话2 ，执行update，由于是通过主键更新，为行级锁；
#会话1和会话2更新的不是同一行数据，会话2可以执行成功
update mylock set name = 'world' where id = 2;
# 会话2 ，执行下面update语句，则会进行阻塞，
# 必须等待会话1提交事物释放锁。
update mylock set name = 'test' where id = 1;
上述案例的代码
create table mylock(
id int not null auto_increment,
name varchar(20),
update_time datetime,
primary key(id)
)engine=innodb default charset=utf8
# 会话1 ，执行update，事务未提交。
start transaction;
update mylock set name = 'hello' where id = 1;
# 会话2 ，执行update，由于是通过主键更新，为行级锁；
# 会话1和会话2更新的不是同一行数据，会话2可以执行成功。
update mylock set name = 'world' where id = 2;
# 会话2 ，执行下面update语句，则会进行阻塞，
# 必须等待会话1提交事物释放锁。
update mylock set name = 'test' where id = 1;
会话1执行commit之后，绘画2事务才能执行
# 会话1 ，执行update,事务未提交，
# 由于通过非主键或索引选中的，升级为表锁。
start transaction;
update mylock set update_time ='2000-10-1' where name='hello'
commit
# 会话2，无法执行写操作，必须等待会话1的事务提交。
update mylock set name='abc' where id = 1;
显示加行锁的两种方式，行锁又分为共享锁和排他锁。
-
共享锁（乐观锁|S锁）：允许不同事务之间共享加锁读取，但不允许其他事务修改或者加入排他锁
select * from user where id=20 lock in share mode
-
排他锁（悲观锁|X锁）：当一个事务加入排他锁后，不允许其他事务加共享锁或排他锁读取
select * from user where id= 20 for update
-
行锁的前提有两个：1、必须是mysql的innoDb表。2、必须开启transaction事务。两者都有，锁才会生效。
-
若一个线程for update执行锁住某行数据，其他线程读取的时候，sql里没有for update，则可以正常读取。
如果是修改，则会被阻塞
只有会话1执行commit之后，会话2的update才成功
# 会话1 ，开启事物执行如下sql，事务未提交，开启读锁
start transaction;
select * from mylock where id=1 lock in share mode;
# 会话2 ，允许读操作
select * from mylock where id=1;
# 会话2，执行update写操作被阻塞，
# 必须等待会话1的事物提交释放锁。
update mylock set name = 'test' where id =1;
# 会话1，开启事务，执行如下sql，事务未提交，开启写锁
start transaction;
select * from mylock where id=1 for update;
# 会话2，执行读和写操作，会发生阻塞
select * from mylock where id=1 for update;
update mylock set name='test' where id=1;
（4）产生死锁
- 表锁不会产生死锁
- 在Innodb中，行级锁并不是直接锁记录，而是锁索引。
- 索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引。
- 如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。
创建一个发生死锁的情景，在Session A和Session B中分别执行两个事务，具体情况如下：
MySQL 中事务的开始语句为 START TRANSACTION 或 BEGIN。这两个语句的效果是相同的,都可以用来开始一个新的事务。
| 时间编号 | SessionA | SessionB |
|---|---|---|
| 1 | BEGIN; | |
| 2 | BEGIN; | |
| 3 | SELECT * FROM mylock where id = 1 FOR UPDATE | |
| 4 | SELECT * FROM mylock where id = 2 FOR UPDATE | |
| 5 | SELECT * FROM mylock where id =2 FOR UPDATE 发生阻塞 | |
| 6 | SELECT * FROM mylock WHERE id = 1 FOR UPDATE ; 死锁发生，纪录日志，回滚一个事物 |
死锁产生，会回滚事务
分析：
-
从第③步中可以看出，
Session A中的事务先对mylock表聚簇索引的id值为1的记录加了一个X型锁。 -
从第④步中可以看出，
Session B中的事务对mylock表聚簇索引的id值为2的记录加了一个X型锁。 -
从第⑤步中可以看出，
Session A中的事务接着想对mylock表聚簇索引的id值为2的记录也加了一个X型锁，但是与第④步中Session B中的事务加的锁冲突，所以Session A进入阻塞状态，等待获取锁。 -
从第⑥步中可以看出，
Session B中的事务想对mylock表聚簇索引的id值为1的记录加了一个X型锁，但是与第③步中Session A中的事务加的锁冲突，而此时Session A和Session B中的事务循环等待对方持有的锁，死锁发生，被MySQL服务器的死锁检测机制检测到了，所以选择了一个事务进行回滚，并向客户端发送一条消息：
1213 - Deadlock found when trying to get lock; try restarting transaction
以上是我们从语句加了什么锁的角度出发来进行死锁情况分析的，但是实际应用中我们可能压根儿不知道到底是哪几条语句产生了死锁，我们需要根据MySQL在死锁发生时产生的死锁日志来逆向定位一下到底是什么语句产生了死锁。
（5）查看死锁日志
SHOW ENGINE INNODB STATUS命令来查看关于InnoDB存储引擎的状态信息，其中就包括了系统最近一次发生死锁时的加锁情况
| InnoDB | |
=====================================
2023-10-25 10:48:04 0x3bd4 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 50 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 23 srv_active, 0 srv_shutdown, 18353 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 77
OS WAIT ARRAY INFO: signal count 76
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 4, rounds 29, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 7.25 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
#=======【死锁发生的时间】，后边的一串十六进制表示的操作系统为当前session分配的线程的线程id
2023-10-25 10:37:39 0x29f8
*** (1) TRANSACTION:
#=======为事务分配的id为419443，事务处于ACTIVE状态已经123秒了，
#=======事务现在正在做的操作就是：“starting index read”
TRANSACTION 419443, ACTIVE 123 sec starting index read
#=======此事务使用了1个表，为1个表上了锁
mysql tables in use 1, locked 1
#=======此事务处于LOCK WAIT状态，拥有3个锁结构（2个行锁结构，1个表级别X型意向锁结构)
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
#=======本事务所在线程的id是16
MySQL thread id 16, OS thread handle 17700, query id 592 localhost 127.0.0.1 root statistics
#=======本事务发生阻塞的语句
SELECT * FROM mylock where id =2 FOR UPDATE
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 314 page no 4 n bits 80 index PRIMARY of table `car_db`.`mylock` trx id 419443 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000006666f; asc fo;;
2: len 7; hex 01000000db03f0; asc ;;
3: len 4; hex 74657374; asc test;;
4: SQL NULL;
#=======本事务当前在等待获取的锁：
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
#=======等待获取的表空间ID为314，页号为4，也就是表mylock的PRIMAY索引中的某条记录的锁
RECORD LOCKS space id 314 page no 4 n bits 80 index PRIMARY of table `car_db`.`mylock` trx id 419443 lock_mode X locks rec but not gap waiting
#=======该记录在页面中的heap_no为6，具体的记录信息如下
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
#=======这是主键值
0: len 4; hex 80000002; asc ;;
1: len 6; hex 00000006666a; asc fj;;
2: len 7; hex 020000010a0d14; asc ;;
3: len 5; hex 68656c6c6f; asc hello;;
4: len 5; hex 9966820000; asc f ;;
#=======表示该事务获取到的锁信息
*** (2) TRANSACTION:
TRANSACTION 419444, ACTIVE 93 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 17, OS thread handle 7432, query id 596 localhost 127.0.0.1 root statistics
SELECT * FROM mylock WHERE id = 1 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 314 page no 4 n bits 80 index PRIMARY of table `car_db`.`mylock` trx id 419444 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
#======= 主键值为2
0: len 4; hex 80000002; asc ;;
1: len 6; hex 00000006666a; asc fj;;
2: len 7; hex 020000010a0d14; asc ;;
3: len 5; hex 68656c6c6f; asc hello;;
4: len 5; hex 9966820000; asc f ;;
#======= 表示该事务等待获取的锁信息
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 314 page no 4 n bits 80 index PRIMARY of table `car_db`.`mylock` trx id 419444 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
#======= 主键值为1
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000006666f; asc fo;;
2: len 7; hex 01000000db03f0; asc ;;
3: len 4; hex 74657374; asc test;;
4: SQL NULL;
#======= InnoDB存储引擎决定回滚第2个事务，也就是Session B中的那个事务
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 419445
Purge done for trx's n:o < 419441 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283036516392288, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283036516393952, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283036516393120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283036516395616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283036516391456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283036516390624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 419443, ACTIVE 748 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 16, OS thread handle 17700, query id 603 localhost 127.0.0.1 root
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1109 OS file reads, 1009 OS file writes, 430 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 169867524
Log buffer assigned up to 169867524
Log buffer completed up to 169867524
Log written up to 169867524
Log flushed up to 169867524
Added dirty pages up to 169867524
Pages flushed up to 169867524
Last checkpoint at 169867524
176 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 511619
Buffer pool size 8192
Free buffers 6941
Database pages 1242
Old database pages 476
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1086, created 156, written 609
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1242, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=7896, Main thread ID=0000000000002CEC , state=sleeping
Number of rows inserted 2, updated 7, deleted 0, read 33
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 48, updated 356, deleted 22, read 9117
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
如何避免死锁
- 程序中操作多张表，尽量以相同的顺序来访问。
- 对于非常容易产生死锁的业务部分，可以尝试使用升级锁的粒度，通过表级锁来减少死锁产生的概率。
2、是否支持事务：
MyISAM 不提供事务支持。而InnoDB提供事务支持
3、是否支持外键：
MyISAM不支持，而InnoDB支持。
4、是否支持聚集索引：
MyISAM不支持聚集索引，InnoDB支持聚集索引
5、是否支持MVCC
MyISAM不支持，InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效。
总结
1.MyISAM 只有表级锁，而InnoDB 支持行级锁和表级锁，默认为行级锁；
2.MyISAM 不提供事务支持。而InnoDB提供事务支持；
3.MyISAM不支持外键，而InnoDB支持；
4.MyISAM不支持聚集索引，InnoDB支持聚集索引；
5MyISAM不支持MVCC，InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效；
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 事务安全 | 支持 | 无 |
| 存储限制 | 64TB | 有 |
| 空间使用 | 高 | 低 |
| 内存使用 | 高 | 低 |
| 插入数据的速度 | 低 | 高 |
| 对外键的支持 | 支持 | 无 |
195
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
