频繁插入(insert)的业务,用什么存储引擎更合适? | 数据库系列

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 虽然MyISAM只支持表锁,但高并发select与insert的业务场景,3个知识点告诉你,MyISAM的表锁依然有非常强劲的性能。

继续回答星球水友提问:

沈老师,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 :需要 等待表锁 的次数; 这两个变量。   使用以下命令查看:
image.png

如果等待表锁的次数占比较大,说明表锁可能是潜在瓶颈。   说了半天,还是没有讲到点子上,为什么在并发插入量比较大的时候,比较适合使用MyISAM呢? 不会因为表锁频繁冲突而导致吞吐量降低吗? 画外音:知识的系统性,比问题答案更重要。   知识点一: MyISAM的 索引与记录存储分离 ,有单独的区域存储行记录,PK是非聚集索引。
image.png

知识点二:

MyISAM表, 如果数据文件(data file)紧密存储 ,中间没有空闲块 (free blocks) ,数据总是插入到数据文件的尾部 (end) ,就如同追加日志一样,性能很高, 此时的并发insert与select是不加锁的(lock free)

image.png

如上图所示: (1)数据文件 连续且紧密 的存储着; (2) 并发insert 无表锁争抢(只需插入队列互斥); (3) insert只在数据文件的尾部进行 (4) 并发select 也能够同时进行(共享读锁);   知识点三: MyISAM表,如果 数据文件 (data file) 中间 空洞 (hole) 上述机制会失效 ,直到空洞被新数据填满,又会启用不加锁机制。   空洞是怎么导致的? 删除或者修改数据,都可能导致空洞。
image.png

如上图所示: (1)中间删除了一些数据,导致中间出现空闲块 (free blocks) (2)此时,select和insert会有表锁冲突,无法并发;
image.png

再如上图所示: (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。  

本文转自“架构师之路”公众号,58沈剑提供。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
6月前
|
SQL 分布式计算 数据库
【YashanDB 知识库】Hive 命令工具 insert 崖山数据库报错
【YashanDB 知识库】Hive 命令工具 insert 崖山数据库报错
|
6月前
|
SQL 分布式计算 数据库
【YashanDB知识库】Hive 命令工具insert崖山数据库报错
【YashanDB知识库】Hive 命令工具insert崖山数据库报错
|
10月前
|
存储 关系型数据库 MySQL
数据库引擎之InnoDB存储引擎
【10月更文挑战第29天】InnoDB存储引擎以其强大的事务处理能力、高效的索引结构、灵活的锁机制和良好的性能优化特性,成为了MySQL中最受欢迎的存储引擎之一。在实际应用中,根据具体的业务需求和性能要求,合理地使用和优化InnoDB存储引擎,可以有效地提高数据库系统的性能和可靠性。
155 5
|
12月前
|
SQL 关系型数据库 MySQL
ThinkPHP6 连接使用数据库,增删改查,find,select,save,insert,insertAll,insertGetId,delete,update方法的用法
本文介绍了在ThinkPHP6框架中如何连接和使用数据库进行增删改查操作。内容包括配置数据库连接信息、使用Db类进行原生MySQL查询、find方法查询单个数据、select方法查询数据集、save方法添加数据、insertAll方法批量添加数据、insertGetId方法添加数据并返回自增主键、delete方法删除数据和update方法更新数据。此外,还说明了如何通过数据库配置文件进行数据库连接信息的配置,并强调了在使用Db类时需要先将其引入。
ThinkPHP6 连接使用数据库,增删改查,find,select,save,insert,insertAll,insertGetId,delete,update方法的用法
|
SQL 关系型数据库 BI
关系型数据库SQL server INSERT 语句
【8月更文挑战第3天】
292 9
|
存储 关系型数据库 MySQL
|
存储 关系型数据库 MySQL
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
|
存储 安全 关系型数据库
|
存储 关系型数据库 MySQL
MySQL数据库——存储引擎(2)-存储引擎特点(InnoDB、MyISAM、Memory)、存储引擎选择
MySQL数据库——存储引擎(2)-存储引擎特点(InnoDB、MyISAM、Memory)、存储引擎选择
216 1
|
关系型数据库 MySQL 数据库
MySQL数据库——触发器-案例(Insert类型、Update类型和Delete类型)
MySQL数据库——触发器-案例(Insert类型、Update类型和Delete类型)
383 0