Loading [MathJax]/jax/output/CommonHTML/config.js
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >彻底缕清常见的存储引擎与锁的分类,再也不用担心面试了

彻底缕清常见的存储引擎与锁的分类,再也不用担心面试了

作者头像
码农编程进阶笔记
发布于 2024-11-23 02:49:48
发布于 2024-11-23 02:49:48
11000
代码可运行
举报
运行总次数:0
代码可运行

我们在上篇文章中提到了记录锁(行锁)、间隙锁和临键锁,后台有小伙伴催我更新一下其他的锁。拖延症又犯了,趁周末,今天我们来总结一下MyISAMInnoDB引擎下锁的种类及使用方法。

MySQL的四大常见存储引擎

谈到MyISAMInnoDB了我们先来了解一下什么是存储引擎吧。MySQL中的数据用各种不同的技术存储在文件(或者内存)中,这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能,我们把这些不同的技术以及配套的相关功能称为存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎,我们可以选择不同的存储引擎来满足我们对数据的处理(存储、检索等)需求,以改善我们应用程序的整体功能。正因为MySQL存储引擎的多样性,使得MySQL深受广大开发者的垂青。

我们的前提条件:我用的是5.7.24-log版本,可以在Navicat中通过SELECT VERSION();命令查看。那么MySQL都有哪些存储引擎呢?我们可以使用sql命令SHOW ENGINES;来查看,结果如下:

  • Engine:表示储存引擎名称;
  • Support:表示MySQL是否支持该存储引擎,DEFAULT为默认的存储引擎;
  • Comment:是对该存储引擎的功能描述,例如:InnoDB支持事务、行级锁定和外键;
  • Transactions:是否支持事务;
  • XA:存储引擎是否支持分布式事务;
  • Savepoints:存储引擎是否支持保存点。

接着让我们来说一下其中比较常见的四大存储引擎吧。

InnoDB

InnoDBMySQLMySQL5.5以后)的默认存储引擎,支持事务、行级锁和外键,被用来处理大量短期事务。如果使用到外键、需求并发程度较高、数据一致性要求较高的话,那么通常选择InnoDB引擎,这也是互联网大厂使用InnoDB存储引擎的原因。除非有非常特别的原因需要使用其他的存储引擎,否则建议优先考虑InnoDB。但是对比MyISAMInnoDB写的处理效率会差一些,并且会占用更多的磁盘空间以保留数据和索引。

MyISAM

MyISAM提供了大量的特性,包含全文索引、压缩、空间行数等,支持3种不同的存储格式,分别是:静态表、动态表、压缩表。

  • 静态表:表中的字段都是非变长字段,这样每个记录都是固定长度的。优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间通常比动态表多(因为存储时会按照列的宽度定义补足空格),在取数据的时候,默认会把字段后面的空格去掉,如果不注意会把数据本身带的空格也会忽略。
  • 动态表:表中的字段都是变长字段,记录不是固定长度的。这样存储的优点是占用的空间相对较少;缺点是频繁的更新、删除数据容易产生碎片,需要定期执行OPTIMIZE TABLE或者myisamchk -r命令来改善性能,并且出现故障的时候恢复相对比较困难。
  • 压缩表:压缩表由myisamchk工具创建,占据非常小的空间,因为每条记录都是被单独压缩的,所以只有非常小的访问开支。

MyISAM中,数据文件和索引文件可以放置在不同的目录(在创建表的时候通过DATA DIRECTORYINDEX DIRECTORY语句指定文件的绝对路径),平均分配IO,获取更快的访问速度。但是MyISAM不支持事务,不支持外键,也不支持行级锁,支持表级锁,有个缺陷就是崩溃后无法恢复。如果应用程序以检索为主,只有少量的插入、更新和删除操作,并且对事物的完整性、并发程度不是很高的话,通常建议选择MyISAM存储引擎。

Memory

Memory存储引擎使用存在内存中的内容来创建表,所以它的访问速度非常快,并且默认使用HASH索引。但是一旦服务器关闭或者mysqld守护进程崩溃时,所有的Memory数据都会丢失,但表还会继续存在,获得速度的同时也带来了一些缺陷。

它要求存储在Memory数据表里的数据使用的是长度不变的格式,这意味着不能使用BLOBTEXT这样的长度可变的数据类型,VARCHAR是一种长度可变的类型,但因为它在MySQL内部当做长度固定不变的CHAR类型,所以可以使用。

服务器需要足够的内存来维持在同一时间内使用的MEMORY表,当不再使用MEMORY表时,要释放MEMORY表所占用的内存,应该执行DELETE FROMtruncate table或者删除整个表。每个MEMORY表中放置的数据量的大小,受到max_heap_table_size系统变量的约束,这个系统变量的初始值是16M,同时在创建MEMORY表时可以使用MAX_ROWS子句来指定表中的最大行数。它通常用于更新不太频繁的小表。

Merge

Merge存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同,Merge表本身没有数据,对Merge类型的表进行查询、更新、删除的操作,实际上是对内部的MyISAM表进行的。Merge表在磁盘上保留两个文件,一个是.frm文件存储表定义、一个是.MRG文件存储Merge表的组成等。MERGE表的优点在于可以突破对单个MyISAM表大小的限制,并且通过将不同的表分布在多个磁盘上,可以有效地改善MERGE表的访问效率。

我们可以通过show create table 表名 命令来查看表使用的引擎,由以下代码可以看出test表使用的是MyISAM存储引擎。

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=46 DEFAULT CHARSET=utf8

相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAMMEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎(5.1之后就不直接支持了,因为BDBoracle收购了)采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。接下来就让我们来了解一下MyISAMInnoDB锁的具体分类与使用方法。

MyISAM锁

MyISAM存储引擎支持的表级锁分为表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock),以下简称读锁和写锁。先看一下他们的特性:

  • 读锁:不会阻碍其它进程的读,但是会阻碍写,只有当读锁释放之后,才会执行其它进程的写--读锁阻塞写锁,但是不阻塞读锁;
  • 写锁:会阻碍其他进程的读和写,只有当写锁释放,才会执行其它写操作--写锁阻塞读锁和写锁;

接下来让我们用例子来演示一下上边的结论,在演示之前,先让我们来说几个命令吧。

  • LOCK TABLE 表名 WRITE/READ:给表加写锁或者读锁;
  • UNLOCK TABLES:给表解锁

演示一:表共享读锁

session1给表test加读锁时,session1只能读取当前表的数据,不可以读其他表,也不可以修改test和其他表;session2可以读取test表数据,更新test表阻塞,但是可以修改和查询其他表数据。

演示二:表独占写锁

session1给表test加写锁时,可以更新test表,读test表阻塞,但是不可以修改和查询其他表数据;session2查询和更新test表阻塞,但是可以查询和更新其他表。

另外我们还可以使用show open tables命令来查看在表缓存中当前被打开的非TEMPORARY表的锁使用情况,其中In_use表示有锁正在使用。

也可以使用show status like 'table%'命令来查看锁的争夺情况,其中Table_locks_waited为等待次数,每等待一次,值就加一,值越大,表示存在越严重的表级锁争用;Table_locks_immediate为产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁,值加一。

MyISAM默认是使用select语句加读锁,增删改操作加写锁。MyISAM是偏读锁,读写调度写优先,不适合做写为主的表的引擎。因为写锁后,其他线程不能做任何操作,大量更新会使查询很难得到锁,从而永远阻塞。 ”

InnoDB锁

上篇文章中我们讲过了记录锁(行锁)、间隙锁和临键锁,这里就不再赘述了。接下来我们按照锁的模式讲一下InnoDB里的共享锁、排他锁和意向锁,其中共享锁和排他锁属于行级锁,行级锁都是基于索引项的,如果没有索引项,则添加的是表级锁;意向锁属于表级锁。

共享锁:Shared Locks,简称S锁

若事务T对数据对象A加上S锁,则事务T只能读A;其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这就保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。可以通过select ... lock in share mode来加共享锁,通过CommitRollback来释放锁。

排他锁:Exclusive lock,简称X锁,也叫互斥锁

若事务T对数据对象A加上X锁,则只允许T读取和修改A,其它任何事务都不能再对A加任何类型的锁(排他锁不可与其他锁共存),直到T释放A上的锁。它防止任何其它事务获取资源上的锁,直到在事务的末尾将资源上的原始锁释放为止。可以通过select ... for update手动加锁,也可以通过增删改操作自动加锁,通过CommitRollback来释放锁。

意向锁:Intention Locks

说起意向锁,大家先来考虑一下这个问题:假设存在两个事务A和B对表test进行操作,首先事务A对第十行数据加了一把读锁,锁住了该行数据,让这一行只能读,不能写;然后事务B想要对该表加一把表级的写锁,那么事务B能否加锁成功呢?思考两秒钟...答案当然是否定的,即事务B无法加锁成功。如果我们假设它加锁成功的话,那么理论上它就能修改表中的任意一行,这将与事务A持有的行级锁(读锁)产生冲突。而数据库想避免这种冲突的话,就需要将事务B的加锁申请给阻塞住,直到事务A的行锁被释放。那么问题来了,数据库是怎么判断这种冲突的呢?我们可以想到两种方案:一、判断表是否已被其他事务用表锁锁表;二、判断表中的每一行是否已被行锁锁住。很显然,如果采用第二种方法的话,需要一行一行去遍历整张表,效率太慢进而造成系统消耗,所以我们选择第一种方法,这也就是意向锁是表锁的原因。

意向锁是放置在资源层次结构的一个级别上的锁,以保护较低级别资源上的共享锁或排它锁,意向锁无法手动创建。如果对任一结点加锁时,必须先对它的上层结点加意向锁也就是如果对一个结点加意向锁,则说明该结点的下层结点正在被加锁。意向锁的执行流程:如果另一个任务试图在该表级别上应用共享或排它锁,则受到由第一个任务控制的表级别意向锁的阻塞,第二个任务在锁定该表前不必检查各个页或行锁,而只需检查表上的意向锁,所以意向锁不是用来给数据加锁的,而是用来判断数据有没有存在锁的标志。下面介绍两种常用的意向锁:意向共享锁(Intent Share Lock,简称IS锁)、意向排它锁(Intent Exclusive Lock,简称IX锁)。

  • 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁的兼容关系如下:

其他锁简介(了解)

InnoDB还包含插入意向锁、自增锁和空间锁。

  • 插入意向锁(Insert Intention Locks):是间隙锁的一种,它的目的是为了提高插入性能。在多个事务对同一个索引中的同一个范围区间插入记录时,如果插入的位置不冲突,不会阻塞彼此,主要是不需要去申请排他锁。
  • 自增锁(AUTO-INC Locks):自增锁是MySQL中一种特殊的锁,如果表中存在自增字段,MySQL便会自动维护一个自增锁。和自增锁相关的一个参数为(5.1.22版本之后加入)innodb_autoinc_lock_mode,可以设定3个值,0:traditonal(每次都会产生表锁,可以控制插入顺序,效率低);1:consecutive(会产生一个轻量锁,simple insert会获得批量的锁,保证连续插入,默认);2:interleaved(不会锁表,来一个处理一个,并发最高,会存在复制问题)。

总结

MySQL这三种锁的特性可大致归纳如下:

  • 表级锁(偏读):开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
  • 行级锁(偏写):开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
  • 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2024-11-06,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 码农编程进阶笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
MySQL锁、加锁机制(超详细)—— 锁分类、全局锁、共享锁、排他锁;表锁、元数据锁、意向锁;行锁、间隙锁、临键锁;乐观锁、悲观锁
客户端发往MySQL的一条条SQL语句,实际上都可以理解成一个个单独的事务(一条sql语句默认就是一个事务)。而事务是基于数据库连接的,每个数据库连接在MySQL中,又会用一条工作线程来维护,也意味着一个事务的执行,本质上就是一条工作线程在执行,当出现多个事务同时执行时,这种情况则被称之为并发事务,所谓的并发事务也就是指多条线程并发执行。
寻求出路的程序媛
2024/06/24
27.2K5
MySQL锁、加锁机制(超详细)—— 锁分类、全局锁、共享锁、排他锁;表锁、元数据锁、意向锁;行锁、间隙锁、临键锁;乐观锁、悲观锁
MySQL进阶突击系列(06)MySQL有几种锁?| 别背答案,现场演示一下
说到数据库锁,我们最常见的、最先想到的是行锁、表锁。MySQL 的InnoDB存储引擎,支持行级锁,而MYISAM支持的是表级锁。这个锁粒度的区别,让InnoDB在互联网海量数据高并发时代,得以脱颖而出,成为MySQL默认的存储引擎。而MYISAM,则适合在一次性大批量更新导入,后续日常查多写少的场景使用。
拉丁解牛说技术
2025/01/20
2920
MySQL中的锁(表锁、行锁,共享锁,排它锁,间隙锁)
在数据库中,除传统的 计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一 个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。本章我们着重讨论MySQL锁机制 的特点,常见的锁问题,以及解决MySQL锁问题的一些方法或建议。
兔云小新LM
2019/07/30
2.9K0
三分钟入门 InnoDB 存储引擎中的表锁和行锁
各位对 ”锁“ 这个概念应该都不是很陌生吧,Java 语言中就提供了两种锁:内置的 synchronized 锁和 Lock 接口,使用锁的目的就是管理对共享资源的并发访问,保证数据的完整性和一致性,数据库中的锁也不例外。
飞天小牛肉
2021/07/21
4K0
MySQL 锁常见知识点&面试题总结
MyISAM 仅仅支持表级锁(table-level locking),一锁就锁整张表,这在并发写的情况下性非常差。
用户6256742
2024/07/16
2150
MySQL 锁常见知识点&面试题总结
【MySQL-25】万字总结<锁>——(全局锁&行级锁&表级锁)【共享锁,排他锁】【间隙锁,临键锁】【表锁,元数据锁,意向锁】
默认情况下,InnODB在 REPEATABLE READ事务隔离级别运行,InnoDB使用 临键锁 进行搜索和索引扫描,以防止幻读。(本次演示)
YY的秘密代码小屋
2024/09/09
4050
【MySQL-25】万字总结<锁>——(全局锁&行级锁&表级锁)【共享锁,排他锁】【间隙锁,临键锁】【表锁,元数据锁,意向锁】
【MySQL】一文带你搞懂MySQL中的各种锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统的计算资( CPU 、
陶然同学
2023/10/14
2.5K0
【MySQL】一文带你搞懂MySQL中的各种锁
面试系列-mysql锁机制及死锁排查
如果有事务在表里执行增删改操作,那在行级会加独占锁,此时其实同时会在表级加一个意向独占锁;如果有事务在表里执行查询操作,那么会在表级加一个意向共享锁。其实平时操作数据库,比较常见的两种表锁,反而是更新和查询操作加的意向独占锁和意向共享锁,但是可以忽略这个意向独占锁和意向共享锁,因为两种意向锁根本不会互斥;
用户4283147
2022/10/27
8410
面试系列-mysql锁机制及死锁排查
第15章_锁
🧑个人简介:大家好,我是 shark-Gao,一个想要与大家共同进步的男人😉😉
程序员Leo
2023/08/02
2780
第15章_锁
MySQL 锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。
用户9615083
2022/12/25
1.5K0
MySQL 锁
MySQL高级9-锁
  锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除了传统的计算资源(CPU、RAM、i/O)的挣用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性,有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素,从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。
Se7eN_HOU
2023/09/06
3010
MySQL高级9-锁
史上最全MySQL锁机制
因为数据也是一种供许多用户共享的资源,如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素,所以进一步学习MySQL,就需要去了解它的锁机制。
数据和云
2019/05/29
7600
重新学习Mysql数据库7:详解MyIsam与InnoDB引擎的锁实现
本文是微信公众号【Java技术江湖】的《重新学习MySQL数据库》其中一篇,本文部分内容来源于网络,为了把本文主题讲得清晰透彻,也整合了很多我认为不错的技术博客内容,引用其中了一些比较好的博客文章,如有侵权,请联系作者。
Java技术江湖
2019/11/29
6480
mysql锁表和解锁语句_db2查看是否锁表
锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性;锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。
全栈程序员站长
2022/09/25
3.7K0
mysql锁表和解锁语句_db2查看是否锁表
MySQL锁系列
全局锁主要应用于做全库逻辑备份,这样在备份数据库期间,不会因为数据或表结构的更新,而出现备份文件的数据与预期的不一样。
AiDBA宝典
2023/04/26
3340
MySQL锁系列
锁系列-Mysql中的锁
在计算机科学中,锁是在执行多线程时用于强行限制资源访问的同步机制,即用于在并发控制中保证对互斥要求的满足。 目录: 1、行级锁、表级锁、页级锁 2、共享锁和排它锁 3、演示 在DBMS中,可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。 行级锁、表级锁、页级锁 行级锁 行级锁是Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小,但加锁的开销也最大。行级锁分为共享锁 和 排他锁。 特点
ImportSource
2018/04/03
1.3K0
锁系列-Mysql中的锁
⑩⑦【MySQL】锁:全局锁、表级锁、行级锁
①表锁 :表共享读锁(read lock) / 表独享写锁(write lock)
.29.
2023/11/21
1K0
⑩⑦【MySQL】锁:全局锁、表级锁、行级锁
MySQL 锁机制——必知必会
MyISAM表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。
高广超
2018/12/12
8670
MySQL锁详解
转载自http://www.cnblogs.com/luyucheng/p/6297752.html
allsmallpig
2021/02/25
5210
吃瓜是需要底层数据库事务锁支撑的
锁是MySQL在服务器层和存储引擎层的并发控制,锁可以保证数据并发访问的一致性、有效性;
Liusy
2021/02/01
5790
推荐阅读
相关推荐
MySQL锁、加锁机制(超详细)—— 锁分类、全局锁、共享锁、排他锁;表锁、元数据锁、意向锁;行锁、间隙锁、临键锁;乐观锁、悲观锁
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验