深入浅出MySQL - MyISAM有趣的那些“锁”事儿

陈哈哈

发布于 2021-10-13 14:31:45

5580

发布于 2021-10-13 14:31:45

小伙伴想精准查找自己想看的MySQL文章？喏 → MySQL江湖路 | 专栏目录

MyISAM是MySQL5.5版之前默认数据库引擎，也算是老一辈存储引擎代表，由早期的ISAM所改良。虽然性能极佳，但“锁”事过多，导致并发事务处理能力很差。没办法，我天生的结巴，还让我去辩论会？？你不要强人“锁”男！这也是后来InnoDB成功取代MyISAM的重要原因之一。被取代后的MyISAM也迅速淡出开发者视野。唉，毕竟，第二永远也不会被记住，除非他是岳伦。

今天我们一起来聊聊MyISAM存储引擎中的锁，MySQL中的表锁主要使用对象就是MyISAM存储引擎，大家可能会疑问，Innodb不用表锁吗？

我们知道Innodb为了提高事务并发度，采用了MVCC多版本并发控制技术，Innodb加锁主要采用的是行记录锁（Record Lock）和间隙锁（Gap Lock）相结合的策略；对了，Innodb引擎只对索引（键）加锁，并不是对某行数据加锁，这点一定要明确。

因此，当Innodb的SQL处理语句没有用到索引时（如全表扫描），InnoDB会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的大幅阳痿；

一、MySQL表级锁的几种模式

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表排他写锁（Table Write Lock）。

注：其实，这样称呼并不好理解，可以结合上篇文章《面试让HR都能听懂的MySQL锁机制，欢声笑语中搞懂MySQL锁》，我们知道是读锁和写锁就行了，这篇文章专讲的是MyISAM存储引擎中的表锁，大家注意本篇文章的锁都是表级的就行，下面我还是说人话，用读锁和写锁称呼他俩。

锁模式的兼容性：

表锁类型	读锁（表）	写锁（表）
读锁（表）	兼容（可并行）	冲突
写锁（表）	冲突	冲突

读锁：对MyISAM表的读（SELECT）操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
写锁：对MyISAM表的写操作，会阻塞其他用户对同一表的读和写操作；

对于MyISAM引擎，读读操作是可并行的；读写操作以及写写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程对该表的读、写操作都会进入等待，直到写锁被释放为止。

二、如何加表锁

显示加锁方式：

加锁：lock tables … read/write;

-- 给T1加读锁
lock tables T1 read;
-- 给T1加写锁
lock tables T1 write;
-- 给T1加读锁、T2加写锁；
lock tables T1 read, T2 write;

释放锁：unlock tables;

unlock tables;

与全局锁 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

全局锁：Flush tables with read lock (FTWRL) 这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。使用场景：全库逻辑备份。

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行DML操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁，可以根据具体业务场景修改其加锁配置。

三、MyISAM表锁并发优化

在使用MyISAM存储引擎前，我们要确认选择该引擎的原因，比如该表并发读较多，写操作较少（如用户表、日志表等），如果是由于DML（增删改）操作都较多造成并发低，建议直接改用Innodb引擎。

表锁在实现的过程中比行锁定或者页锁所带来的附加成本都要小，锁定本身所消耗的资源也是最少，毕竟是直接锁表。但由于锁定的颗粒度大，因此造成锁定资源的争用情况也会比其他的锁定级别都要多，在较大程度上会降低并发处理能力。

所以，当优化MyISAM存储引擎锁定问题时，重点还要放在提升单事务并发速度上。由于表级别锁是不可能改变的了，因此我们要着眼于尽可能让锁定的时间变短，事务间能尽快释放锁，从而提升并发。可以通过show status like 'table%'命令来排查表锁并发情况

mysql> show status like 'table%;
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Table_locks_immediate      | 1000    |
| Table_locks_waited         |   80    |
+----------------------------+---------+

参数讲解：

Table_locks_immediate：产生表锁的次数；
Table_locks_waited：出现表锁争用而发生等待的次数；

两个状态值都是从系统启动后开始记录，出现一次对应的事件则数量加1。如果排查时发现这里的Table_locks_waited状态值较高，那么说明系统中表级锁定争用现象比较严重，就要着手于如何减少表锁等待次数了。优化方式又来到我们强项了：SQl优化、分库分表、减少复杂SQL、缩印利用率等（干货太多找不到？推荐收藏《MySQL江湖路 | 专栏目录》）。

另外MyISAM还有两个有趣且有用的知识点：

1、自定义读写操作优先级！

福音！当我第一次发现这个策略配置时，心情十分激动，脑海中各种腹黑操作接踵而至~~

我有一个同事小田，经常看我文章的朋友可能会有印象。我俩关系可不一般，怎么形容呢？每天早上见到他，脑子里闪过的场景都是：“叶问一巴掌呼倒日本武士”、“钢铁侠一拳干飞金刚狼”、“腕豪大招把大虫子抱进泉水” 那种酣畅淋漓的场面。

实际场景是这样的：每天上午9点我和他都会有脚本对某张APP应用大表的不同字段数据进行UPDATE操作和部分INSERT操作。我发现如果只有我的脚本运行10分钟就跑完了，但是和他的脚本一起跑就需要半小时！

我凑！赶紧看看如何能把我的chenhh用户操作优先级提到最高，把tiantian这个垃圾用户优先级调到0！干tm的！

结果却是令人失望的。。

配置优先级只有MyISAM引擎可以，我们的表是Innodb；
MyISAM只能自定义配置读、写操作的相对优先级，无法配置不同用户间的优先级。。。

MySQL连这功能都没有吗？这TM得优化啊！唉，腹黑的复仇计划再次泡汤，新的认知层次并不能改变我的现状。。或许，还是我太过天真，像孩子一样无助？？卧槽什么歌来着？

言归正传，MyISAM当读写操作同时出现时，MySQL默认优先执行写操作。那如果一直写，那些读线程不就完蛋了？MySQL针对这类问题增加了变量max_write_lock_count控制最大写锁数量上限，同学们可以自己根据实际环境配置这个最大锁等待值，达到峰值后，MySQL会自动降低写操作优先级，等这个数量的写操作执行完后，会先把等待读（等待写锁释放）的请求队列中的事务优先处理掉，然后再继续写。

手动控制方法：

- 通过系统变量配置

系统变量配置：通过SET LOW_PRIORITY_UPDATES=ON命令，降低写操作优先级低于读

mysql> show variables like '%LOW_PRIORITY%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| low_priority_updates | OFF   |
+----------------------+-------+
1 row in set (0.01 sec)

mysql> SET LOW_PRIORITY_UPDATES=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%LOW_PRIORITY%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| low_priority_updates | ON    |
+----------------------+-------+
1 row in set (0.00 sec)

- 在SQL语句中配置

在SQL语句中临时配置：只对该SQL有效

提高优先级操作关键字： HIGH_PRIORITY，HIGH_PRIORITY可以使用在SELECT和INSERT操作中，让MYSQL知道，这个读操作优先进行。

SELECT HIGH_PRIORITY * FROM T;

降低优先级操作关键字： LOW_PRIORITY，LOW_PRIORITY可以使用在INSERT、UPDATE、REPLACE、DELETE 以及 LOAD DATA 等操作中，让mysql知道，这个操作优先级较低。

update LOW_PRIORITY T set money +=10000000 where `name` = '陈哈哈' ;

需要注意的是，如果耗时很长的慢查询（读事务）较多，也会把写进程“饿死”，因此在我们涉及到配置SQL执行优先级时，一定要控制好读（SELECT）进程的执行效率。针对一些（复杂度高或查询量大）且难以优化的SELECT语句，俗称“硬伤”，针对这些硬伤我建议对业务进行一定的拆分，降低复杂度后处理，或者如果是非必要精确的统计数据，可以加个EVENT事件，比如每10分钟更新一下结果集存到一个表中，然后使用时直接取，这个我们叫数据报表。

2、并发插入

一提到MyISAM的表锁，我们立即反应过来的就是串行化，但是我们可以思考一下，如果写操作是一堆insert语句的话，是否还必须串行化？

这里我们要提到一个MySQL特性：concurrent_insert（并发插入）

MyISAM存储引擎有一个控制是否打开concurrent_insert功能的参数选项：concurrent_insert，可以设置为0、1、2：

concurrent_insert=2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录；
concurrent_insert=1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置；
concurrent_insert=0，不允许并发插入，串行。

concurrent_insert=1（默认）：Mysql 5.5.2前显示为1；从5.5.3版本开始concurrent_insert=1参数用枚举值默认为AUTO，concurrent_insert=2 为ALWAYS。

mysql> show variables like '%Concurrent%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | AUTO  |
+-------------------+-------+
1 row in set (0.01 sec)

mysql> set GLOBAL concurrent_insert=2;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%Concurrent%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| concurrent_insert | ALWAYS |
+-------------------+--------+
1 row in set (0.00 sec)

可以利用MyISAM存储引擎的并发插入特性，将concurrent_insert设为2，属于用空间来换时间的策略，来解决应用中对同一表 SELECT 读操作和 INSERT 插入操作的冲突问题。

我知道有些同学这里有些模糊，我来解释一下。

我们知道，当delete操作时MyISAM实际上是没有删除数据的，只是标记了该行被删除，比如一张表1000w数据，我删除了500w，大小反而变大了（有兴趣同学请参考另一篇《delete、truncate、drop的区别有哪些，该如何选择》），这就是表的数据空洞，或者说数据碎片，从MySQL原理上说，当删除500w数据后，接下来插入的500w行数据，会把这些空洞填补上，也就是覆盖到之前标记删除的行上，达到空间二次利用。

但这种策略对并发插入特性并不友好，因为并发插入是追加到表尾部插入（尾插法），碎片也会越来越大，这就是一种时间换空间的做法；当然，针对这种情况定期执行OPTIMIZE TABLE语句来整理空间碎片即可，耗时较长，要在夜深人静清理，不要再高峰期！！！否则。。。