《高性能MySQL》笔记【第一章】

1.1MySQL逻辑框架

• MySQL最与众不同的特性是它的存储引擎架构，这种架构的设计将查询处理及其他系统任务和数据的存储/提取相分离
  • 这种设计模式导致可以在使用时根据需求来选择数据存储的方式

MySQL的逻辑架构图如下：

每个虚线框为一层，总共三层。

• 第一层，服务层(为客户端服务):为请求做连接处理，授权认证，安全等。
• 第二层，核心层:查询解析，分析，优化，缓存，提供内建函数;存储过程，触发器，视图。
• 第三层，存储引擎层，不光做存储和提取数据，而且针对特殊数据引擎还要做事务处理。

1.1.1 连接管理与安全性

• 每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。
• 服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程

1.1.2 优化与执行

• MySQL会解析查询，并创建内部数据结构(解析树)，然后对其优化
• 用户可以通过特殊的关键字（hint）提示优化器，影响它的决策
• 也可以请求优化器解释优化过程的各各因素

1.2 并发控制

1.2.1 读写锁

在处理并发读写问题时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题
这两种锁分别是共享锁(读锁)和排他锁(写锁)

• 读锁是共享的，互不阻塞的
• 写锁是排他的，一个写锁会阻塞其他的写锁和读锁

1.2.2 锁粒度

锁策略：在锁的开销和数据的安全之间寻求平衡，这种平衡会影响到性能。
一般都是在表上施加行级锁

• 行级锁：每次锁定的是一行数据的锁机制就是行级别锁定。
• 行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的

两种锁策略：

1. 表锁(Table lock)

• 表锁是MySQL中最基本的锁策略，是开销最小的策略。
• 它会锁定整张表，一个用户要对表进行写操作前，需要先获得写锁，这会阻塞其他用户的操作。读锁之间互不阻塞。
• 写锁比读锁有更高的优先级，因此一个写锁可能会被插入到读锁队列的前面

2. 行级锁(row lock)

• 行级锁最大程度的支持并发处理，同时开销也最大
• 行级锁只在存储引擎区实现，而不在MySQL服务层实现

1.3 事务

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

事务要么全部执行，要么全部失败

举一个经典的例子：

A向B转账100元需要三步：
1. 检查A账户是否有大于100元的金额
2. 从账户中减去100元
3. B的账户多100元

上述的三个过程必须打包在一个事务里，任何一个步骤失败都要回滚
一般用START TRANSACTION开始一个事务，用COMMIT提交事务，ROLLBACK进行回滚

SRART TRANSACTION
SELECT money FROM A where id = A;
UPDATE checking SET money = money - 100 where id = A;
UPDATE checking SET money = money + 100 where id = B;
COMMIT

事务的特性：ACID

原子性(atomicity)

• 一个事务必须被视为一个不可分割的最小单元，事务要么全部执行，要么全部失败。
• 对于一个事务来说，不可能只执行其中的一部分操作

一致性(consistency)

• 数据库总是从一个一致性的状态转换到另一个一致性的状态
• 在上述例子中，因为保证了一致性，即使执行第三四语句之间系统崩溃，A的账户中也不会少100元，因为事务没有提交，所以修改的数据也不会保存到数据库

隔离性(isolation)

• 一个事务在提交前，所作的修改对其他事务是不可见的
• 与隔离级别有关

持久性(durability)

• 一旦事务提交，则所作的改动将永久的保存到数据库中

1.3.1 隔离级别

• 脏读： 事务可以读取未提交的数据
• 幻读： 当某个事务在读取某个范围的数据时，另一个事务在该范围插入了新的数据，当再次读取这个范围的数据时，会产生幻行

在MySQL中，定义了四种隔离级别

READ UNCOMMITTED(未提交读 read uncommitted）

• 在此级别，事务中的修改即使没有提交，对其他事务也是可见的。
• 事务可以读取未提交的数据，这被称为脏读
• 很少使用

READ COMMITTED(提交读 read committed)

• 满足隔离性的基本定义：一个事务开始时，只能看见已经提交的事务所作的修改
• 一个事务在提交前，所作的修改对其他事务是不可见的

REPEATABLE READ(重复读 repeatable read)

• 该级别保证了同一个事务中多次读取同样的记录总是一致的
• 无法解决幻读

SERIALIZABLE (可串行化 serializable)

• 最高级别的隔离级别，强制事务串行执行，避免了幻读
• 会为每一行数据都加上锁，所以会导致超时和锁争的问题
• 很少使用

1.3.2 死锁

死锁是指两个或者多个事务在统一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象，两个事务都等待对方释放锁，同时又持有对方需要的锁

InnoDB目前处理死锁的方式是，将持有少量行级的排他锁的事务进行回滚

死锁发生以后，只有部分或者完全回滚其中一个事务才能打破死锁

1.3.3 事务日志

• 使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用直接修改数据本身到磁盘。
• 事务日志采用的是追加的方式：写日志操作是在一小块区域内的顺序IO，不像随机IO需要在磁盘的多个地方移动磁头，所以使用事务日志会更快一点

1.3.4 MySQL中的事务

MySQL提供了两种事务型的存储引擎：InnoDB 和 NDB Cluster

MySQL默认采用自动提交模式(AUTOCOMMIT)：如果不是显示地开始一个事务，则每个查询都会被当作一个事务执行提交操作

InnoDB采用的是两阶段锁协议：

• 在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK时候才会释放，并且所有的锁是在同一时刻被释放
• InnoDB会根据隔离级别在需要的时候自动加锁

1.4 多版本并发控制MVCC

• MySQL的大多事务型存储引擎实现的不是简单的行级锁，一般都同时实现了多版本并发控制(MVCC)
• 可以认为MVCC是行级锁的一个变种，但是MVCC在很多情况下避免了加锁操作，因此开销更低
• MVCC的实现，是通过保存数据爱某个时间点的快照来实现的，也就是说不管需要执行多长时间，每个事务看到的数据都是一致的

典型的MVCC有：乐观并发控制和悲观并发控制

MVCC的工作方式：
用InnoDB来举例子：

• InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的，这两个列一个保存行的创建时间，一个保存行的过期时间
• 这里的时间是指系统版本号，每开始一个新事物，系统版本号就会自动递增
• 事务开始时刻的系统版本号会作为事务的版本号

MVCC具体是如何操作的：

1. select
   InnoDB会根据以下两个条件检查每行记录：

   1. InnoDB只查询版本号早于当前事务版本的数据行
   2. 行的删除版本要么未定义，要么大于当前事务版本号，这样可以保证读取到的行，在事务开始之前未被删除

2. insert
   InnoDB为新插入的行保存当前版本号为行版本号

3. delete
   InnoDB为新插入的行保存当前版本号为行删除标识

4. update
   InnoDB为新插入的行保存当前版本号为行版本号，保存当前版本号到原来的行作为行删除标识

• 保存这两个额外的系统版本号，使大多读操作都可以不用加锁。
• MVCC只在REPEATABLE READ和READ COMMITED两个隔离级别工作