MySQL逻辑架构

image.png

并发控制

读写锁

在处理并发读或者写时， 可以通过实现一个由两种类型的锁组成的锁系统来解决问题，这两种类型的锁通常被称为共享锁（shared lock）和排它锁（exclusive lock）,也叫读锁（read lock）和写锁（write lock）.读锁是共享的, 即多个用户在同一个时刻可以读取同一个资源, 互不干扰; 写锁是排他的, 即一个写锁会阻塞其他读锁和写锁.

锁力度

锁策略是在锁的开销与数据安全性之间寻求平衡, 也会影响到性能.

  • 表锁, 开销最小的策略;

例如服务器会为ALTER TABLE之类的语句使用表锁, 而忽略存储引擎的锁机制

  • 行级锁, 最大的锁开销, 但可以最大程度的支持并发处理

行级锁只在存储引擎层实现, 服务器层完全不了解, 例如InnoDB和XtraDB

事务

事务就是一组原子性的sql查询， 或者说一个独立的工作单元。
事务的ACID特性，

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

隔离级别

隔离级别规定了一个事务所做的修改，哪些是在事务内和事物间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低

  • 未提交读，事务中的修改，即使没有提交，对其他事务也是可见的

事务可以读取未提交的数据， 叫做脏读

  • 提交读，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。

  • 可重复读（MySQL默认），同一个事务中多次读取同样的记录结果是一样的。

无法解决幻读问题，当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行

  • 串行化，强制事务串行执行

image.png

死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
多个事务同时锁定同一个资源时， 也会产生死锁。
补充：死锁的4个条件，互斥，占有且等待，不可抢占，循环等待。

为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制，比如检测到死锁的循环依赖，即返回一个错误；比如当查询的时间达到锁等待超时的设定后放弃锁请求；InnoDB目前处理死锁的方法是， 将持有最少行级排它锁的事务进行回滚。

锁的行为与顺序是和存储引擎相关的， 死锁有的是因为真正的数据冲突，有的则完全是由于存储引擎的实现方式导致的。死锁发生后，只有完全或者部分huigun其中一个事务，才能打破死锁。

事务日志

预写式日志，事务先修改数据在内存中的拷贝，再将修改行为记录到持久在硬盘上的事务日志中； 事务日志持久后，内存中被修改的数据再慢慢的刷回磁盘。
写日志的操作是磁盘上一小块区域内的顺序I/O，采用追加的方式，因此采用事务日志的方式相对来说会快很多。

MySQL中的事务

MySQL提供了两种事务引擎，InnoDB和 NDB cluster

  • 自动提交 ```sql SHOW VARIABLES LIKE ‘AUTOCOMMIT’;

1或者ON表示启用，0或者OFF表示禁用

SET AUTOCOMMIT = 1; 

  1. ![image.png](https://cdn.nlark.com/yuque/0/2020/png/281275/1600848329073-032c9a19-e429-4d46-8528-7001839aac68.png#align=left&display=inline&height=83&margin=%5Bobject%20Object%5D&name=image.png&originHeight=83&originWidth=209&size=3385&status=done&style=none&width=209)
  3. - 设置隔离级别
  4. ```sql
  5. SET TRANSACTION ISOLATION LEVEL=XXX
  • 在事务中混合使用存储引擎

MYSQL服务器层不管理事务， 事务是由下层的存储引擎实现的，如果在一个事务里混合使用了事务型和非事务型的表，在回滚时，非事务型的表的变更就无法撤销；在非事务型的表执行事务操作时，在回滚时MYSQL会发出警告，“某些非事务型的表上的变更不能被回滚”

  • 隐式和显式锁定

InnoDB支持显式锁定，但不建议用

多版本并发控制（MVCC）

不同数据库对MVCC的实现不同，MVCC是行级锁的变种，在很多情况下避免加锁操作，因此开销更低。
MVCC的实现是通过保存数据在某个时间点的快照来实现的， 典型的有乐观并发控制和悲观并发控制。

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。
一个保存行的创建时间，一个保存行的删除时间
存储的不是时间值，存储的是系统版本号，每开始一个新的事务，系统版本号都会递增， 事务开始时刻的系统版本号会作为事务的版本号，用来和查询到每行记录的版本号进行比较

SELECT

  • 只查版本号早于当前事务版本的数据行，确保事务读取的行，要么是事务开始前已经存在的， 要么是事务自身插入或修改过的
  • 行的删除版本要么未定义，要么大于当前事务版本号，确保事务读取的行，在事务开始之前未被删除

INSERT

  • 为新插入的一行保存当前系统版本号作为行版本号

DELETE

  • 为删除的每一行保存当前系统版本号作为行删除标识

UPDATE

  • 为插入的一行新记录保存当前系统版本号作为行版本号， 同时保存当前系统版本号到原来的行作为行删除标识

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

MySQL的存储引擎

显示表的信息

  1. SHOW TABLE STATUS LIKE 'bg_activity';

InnoDB存储引擎

默认事务型引擎，也是最重要， 使用最广泛的存储引擎。优先考虑
InnoDB的数据存储在表空间，表空间是InnoDB管理的一个黑盒子，由一系列的数据文件组成。

InnoDB采用MVCC来支持高并发，并且实现了4个标准的隔离级别，默认级别是可重复读，并且通过间隙锁防止幻读的出现，间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

InnoDB表是基于聚簇索引建立的。

MyISAM存储引擎

是mysql5.1及之前版本的默认引擎，提供大量特性，包括全文索引，压缩，空间函数等， 但不支持事务以及行级锁，且崩溃后无法安全恢复。

对于只读的数据，或者表比较小，可以忍受修复操作，依然可以使用这个。

存储

MyISAM将表存储在两个文件中，数据文件和索引文件，分别以.MYD和.MDI为拓展名

MyISAM特性

  • 加锁与并发， 对整张表加锁
  • 修复
  • 索引特性，对于BLOB,TEXT等长字段，可以基于前500个字符创建索引，也支持全文索引，这是一种基于分词的索引
  • 延迟更新索引键，修复时使用这个可以提高写入性能

    MyISAM压缩表

    如果表在创建并导入数据后，不再进行修改，可以使用myisampack对表进行压缩，压缩表可以极大减少磁盘空间占用以及I/O，从而提升查询性能，压缩表也支持索引，但索引也是只读的

    MyISAM性能

    在某些场景下性能很好， 最典型的问题还是表锁

MySQL内建的其他存储引擎，略

第三方存储引擎，略

选择合适的引擎

除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎
除非万不得已，否则建议不要混用多种存储引擎，否则可能带来一系列复杂的问题，以及一些潜在的bug和边界问题。
例如，如果要用到全文索引，建议优先考虑InnoDB+Sphinx的组合，而不是使用全文索引的MyISAM引擎

如果应用需要不同的存储引擎，需要考虑以下几个因素：

  • 事务
  • 备份
  • 崩溃恢复
  • 稀有的特性

转换表的引擎

  • ALTER TABLE，缺点是执行时间长
  • 导出与导入
  • 创建于查询， 使用INSERT…SELECT语句实现，数据量大时还可以分批处理

总结

MySQL拥有分层的架构，上层是服务器层的服务和查询执行引擎，下层则是存储引擎。