MySQL - 基础

1MySQL架构与历史

1.1MySQL逻辑架构

1.1.1连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程。这个连接的查询只会在这个单独的线程中执行。

服务器会负责缓存线程，因此不需要为每个新建的连接创建或销毁线程。

线程池（Thread-Pooling）：可使用池中少量的线程服务大量的连接。

安全性：认证基于用户名、密码、原始主机信息。

SSL：可使用X.509证书认证。

1.1.2优化与执行

MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行优化，包括重写查询、决定表的读取顺序、选择合适的索引等。

用户可以通过特殊的关键字提示优化器，影响其决策过程。

1.2并发控制

两个层面：服务器层，存储引擎层

1.2.1读写锁

共享锁：读锁，读操作是相互不阻塞的，多个线程可以在同一时刻读取同一资源。

排他锁：写锁，写操作是互斥的，在同一时间，只有一个用户能执行写操作，并防止其他用户读正在写入的资源。

MySQL锁的内部管理是透明的。

1.2.2锁粒度

任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

因此，理想情况是，只对需要修改的数据做精确的锁定。

此外，管理锁也需要消耗资源，获得锁、检查锁是否已经解除、释放锁，都会增加开销。

锁策略：在锁的开销和数据的安全性之间寻求平衡。

表锁（table lock）：MySQL最基本的锁策略，且是开销最小的策略。

锁定整张表。用户对表进行写操作时需要先获取写锁，此时阻塞其他用户对该表的所有读写操作。

读锁不相互阻塞。

写锁比读锁有更高的优先级，一个写锁请求可能会被插入到锁队列中读锁的前面，反之不行。

行级锁（row lock）：行级锁并发程度最高，同时锁开销最大。

InnoDB实现了行级锁。

行级锁只在存储引擎中实现，在服务器层没有实现，服务器层完全不了解存储引擎中的锁实现。

事务

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

事务内的语句，要么全部执行成功，要么全部执行失败。失败了必须回滚所有步骤。

ACID

原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元。

对于一个事务来说，不可能只执行其中的一部分操作。

一致性（consistency）： 数据库总是从一个一致性的状态转换到另一个一致性的状态。

隔离性（isolation）：通常来说，一个事务所做的修改在最终提交以前，对其他事务不可见。

持久性（durability）：系统崩溃不会导致数据丢失。

1.3.1隔离级别

较低级别的隔离带来较高的并发，系统的开销也更低。

READ UNCOMMITTED（读未提交）：脏读。事务中修改，即使没有提交，对其他事务也是可见的。很少用。

READ COMMITTED（不可重复读/提交读）：大多数数据库的默认隔离级别（MySQL不是）。一个事务从开始到提交之前，其所做的修改对其他事务不可见。

执行两次相同的查询，可能会得到不一样的结果。

比如，事务B在事务A提交前读到的结果，和在事务A提交后读到的结果可能不同。不可重复读出现的原因就是由于事务并发修改记录而导致的。

REPEATABLE READ（可重复读）：MySQL默认的隔离级别。解决了脏读和不可重复读的问题，但无法解决幻读的问题。幻读只在该隔离级别出现。

SERIALIZABLE（可串行化）：最高的隔离级别，在读取的每一行数据上都加锁，避免了幻读。很少用。

1.3.2死锁

当两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环。

解决办法：死锁检测和死锁超时机制。

InnoDB处理死锁的方式：将持有最少行级排他锁的事务进行回滚。

死锁的产生原因：数据冲突（很难解决），存储引擎的实现方式。

对于事务性的系统，死锁是无法避免的。大部分情况下，只需要重新执行因死锁回滚的事务即可。

1.3.3事务日志

事务日志是一个与数据库文件分开的文件。

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修，改的数据持久到磁盘。事务日志采用的是追加的方式，因此写日志操作时磁盘上一小块区域的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志要快得多。

事务日志持久后，内存中被修改的数据在后台可以慢慢地刷回磁盘。

1.3.4MySQL中的事务

自动提交（AUTOCOMMIT）：如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。

在事务中混合使用存储引擎：事务是由下层的存储引擎实现的。所以在同一个事务中，无法使用多种存储引擎。

如果在同一个事务中同时使用了事务型和非事务型的表，正常提交没有问题，当需要回滚时，非事务型的表的修改将无法撤销，导致数据库处于不一致的状态。

1.4多版本并发控制（MVCC，Multi-Version Concurrency Control）

MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，开销更低。

MVCC的实现：保存数据在某个时间点的快照。

乐观并发控制：用户读取数据时不锁定数据。当一个用户更新数据时，系统将进行检查，查看该用户读取数据后其他用户是否又更改了该数据。如果其他用户更新了数据，将产生一个错误。一般情况下，收到错误信息的用户将回滚事务并重新开始。这种方法之所以称为乐观并发控制，是由于它主要在以下环境中使用：数据争用不大且偶尔回滚事务的成本低于读取数据时锁定数据的成本。

悲观并发控制：阻止用户以影响其他用户的方式修改数据。如果用户执行的操作导致应用了某个锁，只有这个锁的所有者释放该锁，其他用户才能执行与该锁冲突的操作。

MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。

参考资料：《高性能MySQL 第3版》