Mysql 存储引擎和锁

Mysql存储引擎

什么是存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作（简而言之，就是如何存储数据，如何为数据创建索引和更新，查询和删除等实现方法）。

因为在关系数据库中数据的存储是以表的形式存储的，存储引擎就是指表的类型。

数据库的存储引擎决定了表在计算机中的存储方式。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能

存储引擎有哪些

使用 show engines 查看所有支持的存储引擎，目前存在九种

Support 列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎。

常用的三种存储引擎（ MyISAM,InnoDB,MEMORY）

MyISAM

介绍
1. MylSAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的。
2. 访问速度快，对事务完整性没有要求。
3. MylSAM适合查询、插入为主的应用。
4. MylSAM在磁盘.上存储成三个文件，文件名和表名都相同，但是扩展名分别为：
    .frm文件存储表结构的定义
    数据文件的扩展名为.MYD (MYData)
    索引文件的扩展名是.MYI (MYIndex)

特点
1. 表级锁定形式，数据在更新时锁定整个表。
2. 数据库在读写过程中相互阻塞：
    会在数据写入的过程阻塞用户数据的读取
    也会在数据读取的过程中阻塞用户的数据写入
3. 数据单独写入或读取，速度过程较快且占用资源相对少。


生产场景

公司业务不需要事务的支持
单方面读取或写入数据比较多的业务
MylSAM存储引擎数据读写都比较频繁场景不适合（因为读写是互相阻塞的）
使用读写并发访问相对较低的业务
数据修改相对较少的业务
对数据业务-致性要求不是非常高的业务
服务器硬件资源相对比较差（MyISAM占用资源相对少）

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务，不支持外键。

插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

InnoDB

介绍

1. 支持事务，支持4个事务隔离级别
2. MySQL从5.5.5版本开始，默认的存储引擎为InnoDB
3. 读写阻塞与事务隔离级别相关
4. 能非常高效的缓存索引和数据
5. 表与主键以簇的方式存储 BTREE
6. 支持分区、表空间，类似oracle数据库
7. 支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
8. 对硬件资源要求还是比较高的场合
9. 行级锁定，但是全表扫描仍然会是表级锁定，如
    update table set a=1 where user like ‘%zhang%’;

特点
1. InnoDB中不保存表的行数，如 select count(*) from table; 时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。需要注意的是当count(*)语句包含where条件时MyISAM也需要扫描整个表。
2. 对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立组合索引。
3. delete清空整个表时，InnoDB 是一行一 行的删除，效率非常慢。MyISAM则会重建表。

场景

1. 业务需要事务的支持。
2. 行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成。
3. 业务数据更新较为频繁的场景。
      如：论坛，微博等。
4. 业务数据一致性要求较高。
      如：银行业务。
5. 硬件设备内存较大，利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO的压力。

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），其它存储引擎都是非事务安全表，支持行锁定和外键，MySQL5.5以后默认使用InnoDB存储引擎

对于InnoDB表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引到前面几列排序后递增的。

InnoDB最大的特点就是支持事务，以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，不能很好的节省存储空间

支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM和InnoDB的区别：

MyISAM： : 不支持事务和外键约束，占用空间较小，访问速度快，表级锁定，支持全文索引，适用于不需要事务处理、单独写入或查询的应用场景。（写入和查询不一起使用的场景）

InnoDB： : 支持事务处理、外键约束、占用空间比MyISAM 大，支持行级锁定，5.5版本后支持全文索引，读写开发能力较好，适用于需要事务处理、读写频繁，一致性要求高，数据更新频繁的应用场景。

MEMORY

MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

优点：查询效率是最高的。不需要和硬盘交互。

缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表

查看和修改存储引擎

同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

1. 查询存储引擎

show engines

#Engine：存储引擎的名称。
#Support：YES表示引擎受支持且处于活动状态，NO表示不支持，DEFAULT表示默认存储引擎。DISABLED表示支持引擎但已将其禁用。
#Comment：存储引擎的简要说明。
#Transactions：存储引擎是否支持事务。
#XA：存储引擎是否支持XA事务。
#Savepoints：存储引擎是否支持回滚点（标记点）。

2. 查询指定库所有的存储引擎

show table status from 库名

3. 查询数据表所有的存储引擎

show table status from 库名 where name=‘表名’

4. 创建表指定存储引擎

create table 表名(字段1 数据类型,…)  engine=存储引擎名称;

5. 修改存储引擎

alter table 表名 engine=存储引擎名称;

注意：因为MyISAM不支持外键约束，如果数据表设置了外键，则无法修改为MyISAM。

事务

在开启自动提交时，每一个语句就是一个事务，在自动提交关闭的情况下，commit命令就是一次事务的结束，也是另一个事务的开始。可见对于MySQL，事务无处不在

事务特性

原子性：事务最小的执行单位，不允许分割。事务的原子性确保动作要么全部执行，要么全部不执行。
一致性：执行事务的前后，数据保持一致。例如转账的业务中，无论事务是否成功，转账者和收款人的总额应该是不变的。
隔离性：并发访问数据库时，一个用户的事务不应该被其他事务所影响，各并发事务之间数据库是独立的。
持久性：一个事务被提交后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有影响。

常见问题

脏读：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

不可重复读（Unrepeatableread）：指一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读，这种情况就被称为不可重复读。和脏读的区别在于，脏读是看到了其他事务未提交的数据，而不可重复读是看到了其他事务已经提交的数据（由于当前 SQL 也是在事务中，因此有可能并不想看到其他事务已经提交的数据）。

幻读 : 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

四种隔离级别

1. SERIALIZABLE （序列化）

如果隔离级别为序列化，则用户之间通过一个接一个顺序地执行当前的事务，这种隔离级别提供了事务之间最大限度的隔离。

2. REPEATABLE READ　（重复读，简称 RR）

在 MySQL 数据库中，默认的事务隔离级别。
在可重复读在这一隔离级别上，事务不会被看成是一个序列。不过，当前正在执行事务的变化仍然不能被外部看到，也就是说，如果用户在另外一个事务中执行同条 SELECT 语句数次，结果总是相同的。（因为正在执行的事务所产生的数据变化不能被外部看到）。

3. READ COMMITTED （读提交，简称 RC）

READ COMMITTED 隔离级别的安全性比 REPEATABLE READ 隔离级别的安全性要差。处于 READ COMMITTED 级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的多个 SELECT 语句可能返回不同的结果。
和RR区别简单点来说就是RC 总是读取记录的最新版本，而 RR 是读取该记录事务开始时的那个版本

4. READ UNCOMMITTED （读未提交，简称 RU）

READ UNCOMMITTED 提供了事务之间最小限度的隔离。除了容易产生虚幻的读操作和不能重复的读操作外，处于这个隔离级的事务可以读到其他事务还没有提交的数据，如果这个事务使用其他事务不提交的变化作为计算的基础，然后那些未提交的变化被它们的父事务撤销，这就导致了大量的数据变化。

隔离等级修改

1. 查看全局隔离等级和当前会话隔离等级

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

2. 修改当前会话隔离等级

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

结论

锁

锁的引入

如果A有100元，同时对B、C转账，若处理是同时的，则此时同时读取A的余额为100元，在对两人转账后写回，A的余额不是0元而是50元。因此，为了防止这种现象的出现，要引入锁的概念，如只有在A对B的转账完成后，才可对C转账。

锁机制用于管理对共享资源的并发访问。

锁要和事务相互搭配使用。

加锁的机制

悲观锁

正如其名，具有强烈的独占和排他特性。它指的是对数据被外界(包括本系统当前的其他事务，以及来自外部系统的事务处理)修改持保守态度。因此，在当前数据没有处理完之前，其他人或应用不能读取和操作数据！

抽象性质，不是真实存在

乐观锁

乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。乐观锁适用于读操作多的场景，这样可以提高程序的吞吐量。

抽象性质，不是真实存在

意向锁

1. 意向排他锁
事务想要获得一张表中某几行的排他锁

2. 意向共享锁（IS Lock）
事务想要获得一张表中某几行的共享锁

意向锁是什么呢？我们好像从来没有听过，也从来没有使用过，其实他们是由数据库自己维护的。

也就是说，当我们给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁。

当我们给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁。

反过来说：如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁。

如果一张表上面至少有一个意向排他锁，说明有其他的事务给其中的某些数据行加上了排他锁。

那么这两个表级别的锁存在的意义是什么呢？

第一个，我们有了表级别的锁，在 InnoDB 里面就可以支持更多粒度的锁。它的第二个作用，我们想一下，如果说没有意向锁的话，当我们准备给一张表加上表锁的时候，我们首先要做什么？是不是必须先要去判断有没其他的事务锁定了其中了某些行？如果有的话，肯定不能加上表锁。那么这个时候我们就要去扫描整张表才能确定能不能成功加上一个表锁，如果数据量特别大，比如有上千万的数据的时候，加表锁的效率是不是很低？
但是我们引入了意向锁之后就不一样了。我只要判断这张表上面有没有意向锁，如果有，就直接返回失败。如果没有，就可以加锁成功。所以 InnoDB 里面的表锁，我们可以把它理解成一个标志。就像火车上厕所有没有人使用的灯，是用来提高加锁的效率的。

共享锁和排它锁

共享锁【shared locks】

在查询sql 后添加 LOCK IN SHARE MODE

简称S锁。又称读锁，若事务T（线程A）对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改（即不能上排他锁）。

排它锁【exclusive locks】

在查询sql 后添加 for update

简称X锁。又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

需要注意的是，排他锁的真实含义不是排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，而是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。

SELECT ... 语句正常情况下为快照读，不加锁；
SELECT ... LOCK IN SHARE MODE 语句为当前读，加 S 锁；
SELECT ... FOR UPDATE 语句为当前读，加 X 锁；
常见的 DML 语句（如 INSERT、DELETE、UPDATE）为当前读，加 X 锁；
常见的 DDL 语句（如 ALTER、CREATE 等）加表级锁，且这些语句为隐式提交，不能回滚。

索引、行锁和表锁

索引

在了解行锁和表锁之前先简单了了解一下索引。具体可参照https://blog.csdn.net/weixin_43606861/article/details/116202806，我这里难得复制了就截一下图

1. 一级索引

索引和数据存储在一起，都存储在同一个B+tree中的叶子节点（自己下面不再连接有节点的节点（即末端），称为叶子节点（又称为终端结点）。）。一般主键索引都是一级索引。

2. 二级索引

二级索引树的叶子节点存储的是主键而不是数据。也就是说，在找到索引后，得到对应的主键，再回到一级索引中找主键对应的数据记录。

3. 举例介绍

4. 二级索引存储主键而不是存储数据的优缺点

优点

1、减少数据冗余
2、减少行移动或者数据页分裂时二级索引的维护工作，当数据需要更新的时候，二级索引不需要修改，只需要修改一级索引，一个表只能有一个一级索引，其他的都是二级索引，这样只需要修改一级索引就可以了，不需要重新构建二级索引
缺点：
根据二级索引查找行的完整数据需要回表。所以根据二级索引查找数据没有根据一级索引查找快

行锁（也叫Record Lock 记录锁）

行锁是锁定一个记录上的索引，而不是记录本身。在Innodb存储引擎才会有行锁

下面以两条 SQL 的执行为例，讲解一下 InnoDB 对于单行数据的加锁原理。

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';

第一条 SQL 使用主键索引来查询，则只需要在 id = 49 这个主键索引上加上写锁；
第二条 SQL 则使用二级索引来查询，则首先在 name = Tom 这个索引上加写锁，然后由于使用 InnoDB 二级索引还需再次根据主键索引查询，所以还需要在 id = 49 这个主键索引上加写锁

也就是说使用主键索引需要加一把锁，使用二级索引需要在二级索引和主键索引上各加一把锁。如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。

注意

因为覆盖索引优化，lock in share mode也就是S锁，只锁覆盖索引，不锁主键索引。
for update也就是排他锁，还会给主键索引上加锁，因为系统以为你要修改数据。

根据索引对单行数据进行更新的加锁原理了解了，那如果更新操作涉及多个行呢，比如下面 SQL 的执行场景。

update user set age = 10 where id > 49;#注意是update语句

上述 SQL 的执行过程如下图所示。MySQL Server 会根据 WHERE 条件读取第一条满足条件的记录，然后 InnoDB 引擎会将第一条记录返回并加锁，接着 MySQL Server 发起更新改行记录的 UPDATE 请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有匹配的记录为止。

表锁

表锁由 MySQL Server 实现，一般在执行 DDL 语句时会对整个表进行加锁，比如说 ALTER TABLE 等操作。在执行 SQL 语句时，也可以明确指定对某个表进行加锁。

如果行锁后where条件不是索引，则会触发表锁

两者区别

1. 表锁由 MySQL Server 实现，行锁则是存储引擎实现，不同的引擎实现的不同。在 MySQL 的常用引擎中 InnoDB 支持行锁，而 MyISAM 则只能使用 MySQL Server 提供的表锁。

2. 默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁（隐式），而select语句默认不会加任何锁类型！！！加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过SELECT FROM查询数据，因为普通查询没有任何锁机制！！！！！！(这下可以理解之前介绍排他锁的时候说的话了把)

3. InnoDB默认是行级别的锁，当SELECT使用了FOR UPDATE语句 且有明确指定的主键时候，是行级锁。否则是表级别。

4. 注意，显式的加锁，即for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

5. 表锁一般是 DDL 处理时使用

Gap Lock和Next-Key Lock

Gap Lock （间隙锁）

间隙锁的存在主要是解决幻读的问题，之所以出现幻读的问题，是因为记录之间存在缝隙，用户可以往这些缝隙中插入数据，这就导致了幻读问题，现在 Gap Lock 间隙锁，就是要把这些记录之间的间隙也给锁住，间隙锁住了，就不能往间隙里面新增数据，也就不用担心幻读问题了，这也是 Gap Lock 存在的意义。

间隙锁是一个在索引记录之间的间隙上的锁，遵循左开右闭的原则。Gap Lock 只在 REPEATABLE READ 隔离级别下有效，默认已经打开了间隙锁，所以在RR模式下默认解决了幻读问题。

查看间隙锁是否打开，OFF表示打开

show variables like "innodb_locks_unsafe_for_binlog"

Next-Key Lock（后码锁、临键锁）

如果我们既想锁定一行，又想锁定行之间的记录，那么就是 Next-Key Lock 了，换言之，Next-Key Lock 是 Record Lock 和 Gap Lock 的结合体。

Next-Key Lock加锁规则：

1. 锁的范围是左开右闭。

2. 如果是唯一非空索引的等值查询，Next-Key Lock 会退化成 Record Lock。

3. 普通索引上的等值查询，向后遍历时，最后一个不满足等值条件的时候，Next-Key Lock 会退化成 Gap Lock。

MySQL InnoDB存储引擎中默认使用的是Next-Key Lock，先使用Gap Lock，当通过对应唯一索引查询时，会将Gap Lock降级为Record Lock。

死锁

在并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，出现互相或者循环等待，就会导致这几个线程都进入无限等待的状态，称为死锁

如何避免死锁：

1. 使用更合理的业务逻辑，以固定的顺序访问表和行。

2. 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4. 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5. 为表添加合理的索引。如果不使用索引将会为表的每一行记录添加上锁，死锁的概率大大增加。

参考文章：

https://blog.csdn.net/Huangjiazhen711/article/details/127567020

https://blog.csdn.net/qq_29168493/article/details/79066399

https://blog.csdn.net/xinyuan_java/article/details/128493205

https://blog.csdn.net/S_ZaiJiangHu/article/details/116720884

索引https://blog.csdn.net/weixin_43606861/article/details/116202806

posted @ 2023-04-20 15:05 Joni是只狗阅读(63) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Joni是只狗

Mysql 存储引擎和锁