【笔记】高性能MySQL（一）
MySQL架构与历史。
MySQL逻辑架构
MySQL 的逻辑架构分为三层。
最上层是客户端层，非MySQL独有。
第二层架构是 MySQL 服务层，大多数 MySQL 的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现，包括存储过程、触发器、视图等。
第三层是存储引擎。存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。
并发控制
MySQL 在两个层面进行并发控制：服务器层 & 存储引擎层。
读写锁
因为读数据和写数据需要不同的并发控制。所以使用两种锁来处理，这两种锁通常被称为共享锁（shared lock）和排他锁(exclusive lock)，也叫读锁和写锁。
如果一个资源加上了读锁时，其他需要共享锁的操作也能同时进行，即多个线程可以在同一时刻读取同一资源。
如果一个资源加上写锁，则其他操作都不能进行，即同一时刻只能存在一个线程进行写入同一资源。
写锁比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面。
锁粒度
锁粒度即是要在哪个层面上进行加锁操作，可以对整张表进行加锁操作（表级锁），也可以对表中的某行数据进行加锁操作（行级锁）。
对数据进行更精确的锁定可以提高系统的并发量，但是需要消耗更多的资源进行加锁操作。锁的各种操作，包括获得锁、检测锁是否已经解除、释放锁等，都会增加系统的开销。
大多数商业数据库一般都是在表上施加行级锁(row-level lock)。而 MySQL 不同的存储引擎实现了不同的锁策略和锁粒度。最重要的两种锁粒度：
- 表级锁(table lock)
当一个线程对表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会对整个表进行锁定，因此会阻塞其他线程的读写操作。 - 行级锁(row lock)
行级锁可以最大程度地支持并发处理。
事务
事务即是将一组原子性的 SQL 查询。这一组 SQL 语句要么全部执行成功，要么全部执行失败。
事务的四种特性(ACID)
- 原子性（atomicity）
一个事务必须被视为一个不可分割的最小单元，一个事务中的所有操作要么全部提交成功，要么全部失败回滚。 - 一致性（consistency）
数据库总是从一个一致性的状态转换到另一个一致性的状态。 - 隔离性（isolation）
一个事务所做的修改在最终提交之前，对其他事务是不可见的。 - 持久性（durability）
一旦事务提交，则其所做的修改就会永久保存到数据库中。
隔离级别
三个概念
- 脏读(dirty read)
一个事务T1更新(UPDATE)了一行数据(data1 -> data2)，这时表数据已发生改变，但是事务T1还没提交，这时另一个事务T2去读这个数据data2，这种情况就是脏读。
如果T1后续操作失败进行回滚的话，数据重新变回data1，T2读取的数据data2就是无效数据。 - 不可重复读
事务T1读取某一数据data1，事务T2读取并修改了该数据data -> data2，T1为了对读取值进行检验而再次读取该数据data2，便得到了不同的结果，这种情况就是不可重复读。
因为每次重新查询的数据无法保证一致，所以事务T1的提交就无法保证正确。 - 幻读
事务T1将表中满足字段A>100的所有数据进行修改操作，同时事务T2插入了一条满足A>100的数据，T1再次查询满足A>100的数据进行校验，发现多出一条数据未修改，这种情况就是幻读。
四种隔离级别
- READ UNCOMMITTED（未提交读）
事务中的修改，即使没有提交，对其他事务也都是可见的。 - READ COMMITTED（提交读）
一个事务开始时，只能“看见”已经提交的事务所做的修改。大多数数据库系统的默认隔离级别都是READ COMMITTED（MySQL不是）。 - REPEATABLE READ（可重复读）
保证了在同一个事务中多次读取同样记录的结果是一致的。可重复读是MySQL的默认隔离级别。 - SERIALIZABLE（可串行化）
最高隔离级别。强制事务串行执行，除非需要确保数据的一致性并且可以接受没有并发，不然不使用。
| 隔离级别 | 脏读可能性 | 不可重复读可能性 | 幻读可能性 | 加锁 |
|---|---|---|---|---|
| READ UNCOMMITTED | Yes | Yes | Yes | No |
| READ COMMITTED | No | Yes | Yes | No |
| REPEATABLE READ | No | No | Yes | No |
| SERIALIZABLE | No | No | No | Yes |
可以使用下面的命令来修改事务的隔离级别。1
2// 设置全局/当前会话的事务隔离级别
SET GLOBAL/SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
死锁
当两个事务相互持有对方在等待的锁时，就会产生死锁。
1 | |
当如上两个事务同时执行时就会引发死锁问题。
解决死锁一般有两种方法。
- 检测死锁的循环依赖。
- 查询的时间达到锁等待时间后放弃锁。
锁机制是由不同存储引擎实现的。以同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会。
MySQL中的事务
自动提交（AutoCommit）
自动提交是指如果不是显示地开始一个事务，则每个查询都被当作一个事务执行提交。MySQL 默认采用自动提交（AUTOCOMMIT）模式。1
2
3
4// 查询是否开启自动提交
mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
// 开启自动提交
mysql> SET AUTOCOMMIT = 1;在事务中混合使用存储引擎
因为事务是由下层的存储引擎实现的，如果在同一个事务内使用不同存储引擎，有的存储引擎支持事务（如 InnoDB），有的存储引擎不支持事务（如 MyISAM），那事务发生回滚时非事务型表的变更会无法撤销。因此不要在同一个事务中使用多种存储引擎。