MySQL的锁与存储引擎

清疚 2022-01-14 13:29 313阅读 0赞

## 一、MySQL 引擎 ##

**1、MyISAM存储引擎**

> 不支持事务，不支持外键，只支持表锁，访问速度快  
> 对事务完整性没有要求，要求以select \\ insert 为主的 应用基本上可以用这个引擎来创建表

**2、InnoDB 存储引擎 / MySQL默认 存储引擎**

> 支持事务，支持外键，支持行锁和表锁

**3、MEMORY 存储引擎**

> 查询快，存在内存，随时会丢失  
> 支持散列索引 和B树索引  
> 散列索引 ‘相等比较’ 时非常快，但对‘范围比较’ 速度就慢了，因此散列索引值 适合使用在和操作符，不适合或操作符，也同样不适合在order by 子句中使用

**4、Archive存储引擎**

> 适合存储归档数据，如日志信息，  
> 只支持insert 和select 操作 其设计的目的是提供告诉的插入和压缩功能

## 二、InnoDB 常见的几种锁机制 ##

**1、共享锁和独占锁**

> InnoDB  通过共享锁和独占锁两种方法实现了标准的行锁  
> 共享锁： 允许事务获得锁后去读数据  
> 独占锁：允许事务获得锁后去更新或者删除数据
> 
> 一个事务 获得共享锁后，允许其他事务获取共享锁  
> 此时两个事务都持有共享锁，但是不允许其他事务获取独占锁
> 
> 如果一个事务获取独占锁   则不允许其他事务获取共享锁或者独占锁，必须等到该事务释放锁后才可以获取到

**2、意向锁**

> 上面说过的InnoDB 支持行锁和表锁，意向锁是一种表级锁，用来只是接下来一个事务将要获取什么类型的锁（共享还是独占）
> 
> 意向锁 分为 意向共享锁（IS） 和意向独占锁 （IX）  
> 以此表示接下来是获取共享锁还是独占锁  
> 意向锁不需要显示的获取，在我们获取共享锁或者独占锁的时候会自动获取，意思也就是说，如果要获取共享锁或者独占锁，则一定是先获取到了意向共享锁或者意向独占锁，
> 
> 意向锁不会锁住任何东西，除非有进行全表请求的操作，否则不会锁住任何的数据  
> 存在的意义只是用来表示有事务正在锁某一行的数据，或者将要锁某一行的数据

**3、记录锁**

> 锁住某一行 如果表存在索引，那么记录锁是锁在索引上的， 如果表没有索引，那么InnoDB 会创建一个隐藏的聚簇索引加锁， 所以我们在查询的时候尽量采用索引进行查询，这样可以降低锁的冲突

**4、间隙锁**

> 间隙锁是一种记录行与记录行之间存在空隙或者在第一行记录之前或在最后一行记录之后产生的锁，间隙锁可能占据的单行，多行或者空记录  
> 通常的情况是我们采用范围查找的时候，比如在学生成绩管理系统中，如果此时有学生成绩 60，72，80，95，一个老师要查下成绩大于 72 的所有同学的信息，采用的语句是 select \* from student where grade > 72 for update，这个时候 InnoDB 锁住的不仅是 80，95，而是所有在 72-80，80-95，以及 95 以上的所有记录。为什么会 这样呢？实际上是因为如果不锁住这些行，那么如果另一个事务在此时插入了一条分数大于 72 的记录，那会导致第一次的事务两次查询的结果不一样，出现了幻读。所以为了在满足事务隔离级别的情况下需要锁住所有满足条件的行

**5、NK （记录锁和间隙锁的组合锁）**

> Next-Key Locks，NK 是一种记录锁和间隙锁的组合锁。是 2 和 3 的组合形式，既锁住行也锁住间隙。并且采用的左开右闭的原则。InnoDB 对于查询都是采用这种锁的

**6、乐观锁**

> 大多是基于数据版本（ Version ）记录机制实现。 何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。 读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对， 如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据

![20160401140250835][]

像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

**7、悲观锁**

> 悲观锁就是当我们去获取数据的时候，不论我们有没有打算去修改，悲观锁都会认为我们一定会去修改这个数据，所以 他会把这个数据直接锁死，其他的人想操作操作，那你就阻塞，直到轮到你获取锁为止

select \* from account where name=”张三” for update  
这条sql 语句锁定了account 表中所有符合检索条件（name=”张三”）的记录。本次事务提交之前（事务提交时会释放事务过程中的锁），外界无法修改这些记录。也就是我们可以在查询数据的时候先用for update把这条数据锁住，然后更改完这条数据再提交。这样别的线程没法更新这条数据，也就保证了不会丢失更新。

悲观锁带来的性能问题。我们试想一个场景：如一个金融系统，当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户帐户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读出数据、开始修改直至提交修改结果的全过程），数据库记录始终处于加锁状态，可以想见，如果面对几百上千个并发，这样的情况将导致怎样的后果？所以我们这个时候可以使用乐观锁。

**8、乐观锁和悲观锁的区别**

> 乐观锁 总是认为不会产生并发问题，每次去取数据的时候总认为不会有其他线程对数据进行修改，因此不会上锁，但是在更新时会判断其他线程在这之前有没有对数据进行修改，一般会使用版本号机制或CAS操作实现
> 
> 悲观锁总是假设最坏的情况，每次取数据时都认为其他线程会修改，所以都会加锁（读锁、写锁、行锁等），当其他线程想要访问数据时，都需要阻塞挂起

[20160401140250835]: /images/20220114/e38b06d8ed21436b9f56ed031d7b27c7.png