频繁插入(insert)的业务,用什么存储引擎更合适? | 数据库系列
沈老师,MyISAM只支持表锁,但网上文章却说,在并发插入量比较大的时候,比较适合使用MyISAM,这矛盾吗?
这个问题,涉及MySQL表锁的一些细节,借着这个问题,系统性说下表锁的“所以然”。
画外音: 网上不少文章只说结论,不说为什么,容易让人蒙圈。
MySQL表锁知识系统性梳理。
哪些存储引擎使用表锁?
MySQL,除InnoDB支持行锁外,MySQL的其他存储引擎均只使用表锁,例如:MyISAM, MEMORY, MERGE等。
表锁有什么好处?
(1)表锁 占用内存少 很多,行锁的数量与行记录数相关,非常耗内存;
(2)如果业务 经常读写表中很大一部分数据时,表锁会更快 ,因为此时只涉及一个锁,而不是同时管理N多个锁;
(3)如果业务 经常使用group by,表锁会更快 ,原因同(2);
画外音: 这样的一些场景,使用MyISAM比InnoDB更优。
表锁是怎么运作的?
和其他临界资源的读写锁类似。
写时 ,要加 写锁 :
(1)如果表 没有锁 ,对表加写锁;
(2)否则,入写锁队列;
读时 ,要加 读锁 :
(1)如果表 没有写锁 ,对表加读锁;
(2)否则,入读锁队列;
表锁释放时 :
如果写锁队列和读锁队列里都有锁,写有更高的优先级,即 写锁队列先出列 。这么做的原因是,如果有“大查询”,可能会导致写锁被批量“饿死”,而写锁往往释放很快。
画外音: 潜台词是,如果有大量并发update请求,select会等所有update请求执行完才执行。
如何查看表锁情况?
如果要分析 表锁冲突情况 ,可查看:
Table_locks_immediate : 立刻获得表锁 的次数;
Table_locks_waited :需要 等待表锁 的次数;
这两个变量。
使用以下命令查看:
show status like ‘Table%’;
如果等待表锁的次数占比较大,说明表锁可能是潜在瓶颈。
说了半天,还是没有讲到点子上, 为什么在并发插入量比较大的时候,比较适合使用MyISAM呢? 不会因为表锁频繁冲突而导致吞吐量降低吗?
画外音: 知识的系统性,比问题答案更重要。
知识点一:
MyISAM的 索引与记录存储分离 ,有单独的区域存储行记录,PK是非聚集索引。
这个知识点就不展开了,《 数据库,主键为何不宜太长? 》刚讲过。
知识点二:
MyISAM表, 如果数据文件 (data file) 紧密存储 ,中间没有空闲块 (free blocks) ,数据总是插入到数据文件的尾部 (end) ,就如同追加日志一样,性能很高, 此时的并发insert与select是不加锁的 (lock free) 。
如上图所示:
(1)数据文件 连续且紧密 的存储着;
(2) 并发insert 无表锁争抢(只需插入队列互斥);
(3) insert只在数据文件的尾部进行 ;
(4) 并发select 也能够同时进行( 共享读锁 );
知识点三:
MyISAM表,如果 数据文件 (data file) 中间 有 空洞 (hole) , 上述机制会失效 ,直到空洞被新数据填满,又会启用不加锁机制。
空洞是怎么导致的?
删除或者修改数据,都可能导致空洞。
如上图所示:
(1)中间删除了一些数据,导致中间出现空闲块 (free blocks) ;
(2)此时,select和insert会有表锁冲突,无法并发;
再如上图所示:
(1)随着插入的进行,中间的空闲块又被填满了;
(2)此时,并发select和insert又恢复了;
结论
虽然MyISAM只支持表锁,但 高并发select与insert 的业务场景,上述机制使得MyISAM的 表锁 依然有 非常强劲的性能 。
画外音: 本文基于MySQL5.6。
希望解答了这位水友的疑问。
欢迎大家扫码加入星球提问,有问必答。
画外音: 这里有网上没有的知识。
作业
下面哪些场景,用表锁比行锁性能更高。
A :大部分SQL都是读请求。
B :SQL是读写混合,写请求是 单行的 delete或update。
update t set c=xxx where unique_key=yyy;
delete from t where unique_key=zzz;
C :SQL是读写混合,写请求是高并发的insert,很少有delete或update。
D :SQL会扫描大量行记录,且有很多group by。
转发+再看 + 答对 +解释原因,直接 找我领offer 。