逻辑架构
三层架构：
- 连接与线程处理
每个连接拥有一个线程，线程的创建和销毁消耗资源，可以通过维护线程池的方式，提高性能。
注意区分线程池和连接池，连接池一般在客户端设置，而线程池是在 DB 服务器上配置。连接池可以减少连接的创建和释放，提高请求的平均响应时间，控制一个应用的 DB 连接数，但无法控制整个应用集群的连接数规模。线程池和连接池需要结合使用。
- MySQL 服务层：SQL 解析、分析、优化、缓存
MySQL 的优化器不关心使用的是什么存储引擎，但存储引擎对于优化查询有影响。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息。
Oracle 中有库基于规则优化 (RBO) 和基于代价优化 (CBO) 两种方式，但oracle 10g之后已经弃用 RBO。相比 MySQL 的优化器，Oracle 的优化更加丰富和完善，可以对照着学习。
- 存储引擎
并发控制
两种类型的锁：
- 共享锁（shared lock）/ 读锁（read lock）
- 排他锁（exclusive lock）/ 写锁（write lock）
粒度
-
表锁
存储引擎可以管理自己的锁，MySQL 本身还是会使用各种表锁来实现不同的目的。如，服务器会对ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制。 -
行级锁
行级锁只在存储引擎层实现（如 InnoDB 和 XtraDB），服务器层面没有实现。
事务
ACID
- 原子性（atomicity）
- 一致性（consistency）
- 隔离性（isolation）
- 持久性（durability）
支持事务必然会来带开销，对于一些不需要事务的查询类应用就可以选择飞事务型的存储引擎，获取更高的性能。通过LOCK TABLES语句同样可以为应用提供一定的保护。
Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transaction, but does not guarantee that a transaction is correct. Referential integrity guarantees the primary key – foreign key relationship.
四种隔离级别
- READ UNCIMMITTED（未提交读）：造成 dirty read，实际很少使用
- READ COMMITTED（提交读）：不可重复读 nonrepeatable read
- REPEATABLE READ：依然会有幻读（Phantom Read） 的问题，进一步通过多版本并发控制（Multi version Concurrency Control，MVCC）解决。是 MySQL 的默认隔离级别。
- SERIALIZABLE：在读取的每一行上都加上锁，可能导致大量的超时和锁争用，实际很少使用。
事务日志
使用事务日志，修改数据表式可以先在内存中修改，并把操作行为记录到硬盘上的事务日志，事务日志采用追加方式，写日志操作相对写数据要快。事务日志持久化后，内存中被修改的数据可以在后台慢慢刷回到硬盘。（预写式日志 Write-Adead Logging）
MySQL 中的事务
- 默认自动提交，可通过
SET AUTOCOMMIT = 0禁用 - 在同一个事务中使用多种存储引擎是不可靠的
- 除了自动加的隐式锁，还可以使用特定语句进行显式锁定,如下语句（ORACLE 里也有）
1 | SELECT ... FOR UPDATE |
多版本并发控制
MVCC 没有统一的实现标准，有多种实现机制。MVCC 可以看成是行级锁的一个变种，但它在很多情况下避免了加锁操作，开销更低。读操作非阻塞，写操作锁定必要的行。
典型的有乐观锁和悲观锁
InnoDB 的 MVCC 通过两个隐藏的列实现：行创建时间、行过期时间。存储的不是时间值而是系统版本号（system version number）。详见相关章节。
存储引擎
MySQL 最初基于ISAM构建，后来被MyISAM取代，如今默认的存储引擎为InnoDB。
不同的存储引擎各有特点，需要结合实际业务选择使用。多数情况下，使用 InnoDB 综合表现最好。
应用场景
- 日志型应用：使用 MyISAM 或 Archive，开销低，插入速度快。要做分析可以主从备份，读写分离
切换表的引擎
- ALTER TABLE：执行时间长
- 导入导出：mysqldump 导出后修改建表语句导入
- 创建与复制：创建新表，复制数据，数据量特别大可以分批处理