MySQL-锁、事务、存储引擎、索引

RyanHe
249 阅读6分钟

MySQL-锁、事务、存储引擎、索引

一、MySQL锁类型

读写锁

MySQL锁分为共享锁(share lock)和排他锁(exclusive lock)，也称为读锁(read lock)和写锁(write lock)。
读锁是共享的，或者说是相互不阻塞的，即多个客户端在同一时刻读取同一资源，互不干扰。
写锁是排他的，写锁会阻塞其他写锁和读锁，写锁的优先级高于读锁，因此写锁可能会被插入到读锁前面。

锁粒度

提高高并发共享资源并发性的方式就是让锁更具有选择性，尽量只锁定需要修改的部分数据，锁定的数据量越少，则系统的并发越高。

  1. 表锁（table lock） 表锁是MySQL最基本的锁策略，并且是开销最小的策略。顾名思义，表锁会锁定整张表。

  2. 行级锁（row lock） 行级锁仅在存储引擎层实现，MySQL仅InnoDB引擎支持行级锁。

死锁

死锁指的多个事务对同一资源相互占用，并请求锁定对方占用的资源。例子如下：
A事务

Update table set value='xxx' where id=1;
Update table set value='xxx' where id=2;

B事务

Update table set value='xxx' where id=2;
Update table set value='xxx' where id=1;

如果凑巧，两个事务都执行了第一条语句，接下来执行第二条语句，发现资源已经被锁定了，都在等待对方释放，就陷入了死循环。当然数据库有死锁检测和死锁超时机制，比如InnoDB处理死锁的方案是:将拥有最少行的排它锁的事务进行回滚。

二、事务

事务特性

  • 原子性。一个事务必须被视为一个不可分割的最小单元。
  • 一致性。数据库总是从一个一致性状态转到另一个一致性状态。
  • 隔离性。一个事务所做的修改在提交之前，对其他事务是不可见的。
  • 持久性。一个事物一旦提交，将会永久保存在数据库中。

事务隔离级别

  • 未提交读（Read UnCommitted） 即事务中的修改，即使没有提及，对其他事务也是可见的，当然这也直接违反了事务的隔离性原则。事务读取到未提交到数据，这也成为脏读（Dirty Read）。

  • 提交读（Read Committed） 大多数数据库默认隔离级别都是提交读（但是MYSQL不是）。即一个事务提交之前，所做的修改对其他事务是不可见的。这个级别也成为不可重复读，即在当前事务内，查询某个数据两次，可能正好其他事务对其进行修改，这也可能查询出来的结果是不一样的。

  • 可重复读（Repeatable Read） 该级别保证了在同一事务内，多次读取同样的记录结果是一样的。但是可重复读无法解决幻读的问题，所谓幻读是指A事务在读取某个范围内的记录时，B事务又在当前范围内添加了数据，A事务再次读取该范围内的记录时，会产生幻行。但是Mysql的InnoDb引擎通过多版本并发控制（MVCC）解决了该问题，后续会对MVCC进行说明。

  • 串行化（Searializable） 串行化是最高的隔离级别，它强制事务串行之行。简单来讲，也就是说会在读取每一行数据上都加上锁，这样会导致大量的锁竞争，实际场景中很少用这种隔离级别，除非一定要确保数据一致性并且可以接受没有并发的情况下。

三、多版本并发控制（MVCC）

MVCC的实现，是通过保存数据在某个时间点的快照来实现，下面简述一下InnoDB的MVCC是如何工作的。
InnoDB的MVCC，是通过在每行记录后面保存两列隐藏的列来实现，一列保存创建时间，一列保存过期时间。当然存储的不是实际的时间，而是系统版本号，在InnoDB可重复读的隔离级别下，MVCC具体操作如下：

  • Select InnoDB会根据以下两个条件来检查每行记录：
  1. 只查找行的系统版本号小于等于当前事务的系统版本号
  2. 行的删除版本要么未定义，要么大于当前事务版本号，确保事务读取到的行，在事务开始之前未被删除。
  • Insert InnoDB为新插入的行记录保存当前系统版本号作为行版本号。
  • Delete InndoDB为删除的每一行保存当前系统系统版本号作为删除标识。
  • Update InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

MVCC只在可重复读和提交读两个隔离级别下工作，因为未提交读永远读取的都是最新的数据，而串行则会对所有读取的行加锁。

四、存储引擎

  • InnoDB存储引擎 InnoDB也是MySQL默认的存储引擎，采用MVCC支持高并发，并且实现了四个标准的隔离级别，InnoDB表是基于聚集索引（主键索引）建立的，并且InnoDB支持热备，最小粒度的锁是行级锁。
  • MyISAM存储引擎 不支持事务，不支持行级锁，最小粒度的锁是表锁。

五、索引

在了解索引知识之前，先一定要了解B-Tree和B+Tree。

  • 为什么InnoDB采用的B+Tree？
  • B+Tree和B-Tree有什么区别？ 引用一篇其他大佬的文章作参考B-Tree和B+Tree详解

索引匹配规则

  • 全值匹配。即匹配的是和索引中的所有列进行匹配。
  • 匹配最左前缀。即匹配索引中的第一列。
  • 匹配列前缀。即匹配某一列的开头部分，例如 like ‘王%’
  • 匹配范围值。

1.聚簇索引

也称为主键索引，是一种数据存储方式。B+Tree结构，非叶子节点包含健值和指针，叶子节点包含索引列和行数据。一张表只能有一个聚簇索引。

2.非聚簇索引

不是聚簇索引，则就是非聚簇索引。叶子结点只存索引列和主键值。所以如果sql还要返回除了索引列的其他字段信息，则需要做一次回表操作，此时我们可以考虑用覆盖索引解决该问题。

3.覆盖索引

如果一个索引包含了所要查询字段的值，就称之为覆盖索引。例子如下，此时我们建立一个（gender，name）的组合索引，则可以避免回表。

select name from table where gender = '男'
avatar
文章
阅读
粉丝