高性能MySQL 第一章MySQL架构历史总结

1. MySQL逻辑架构

MySQL的逻辑架构如下图所示，分为客户端、服务层和存储引擎层这三层：
（1）最上层为客户端层。完成连接处理、授权认证、安全等功能。
（2）第二层为服务层。MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
（3）第三层为存储引擎层。负责MySQL中的数据存储和提取，服务层通过API与存储引擎进行通信，这些接口屏蔽了不同存储引擎之间的差异。

注意：存储引擎是针对于表的而不是针对于库的（一个库中的不同表可以使用不同的存储引擎），但是不建议混合使用存储引擎。

MySQL最大的特点就是它的存储引擎架构，采用插件式的存储引擎，这种架构将查询处理和其他系统任务和数据存储/提取相分离。这种处理和存储分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储方式。

2. MySQL执行流程

当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？下图展示了MySQL的查询过程。

MySQL整个查询执行过程可以分为5个步骤：
（1）客户端向MySQL服务器发送一条查询请求
（2） 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
（3）服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
（4）MySQL根据执行计划，调用存储引擎的API来执行查询
（5）将结果返回给客户端，同时缓存查询结果
具体可以参考博客：MySQL逻辑架构及性能优化原理

3. 并发控制

3.1 读写锁

读锁（read lock）：也称共享锁，读锁是共享的，或者说是相互不阻塞的。多个线程在同时可以读取同一个资源而不相互干扰。
写锁（write lock）：也称排他锁，写锁是排他的，也就是说，一个写锁会阻塞其他的写锁和读锁。

锁类型	写锁	读锁
写锁	不兼容	不兼容
读锁	不兼容	兼容

3.2 锁粒度

锁的粒度：被加锁资源的最小单位，一种提高一种资源并发性的方式就是让锁定义的对象尽可能的小，最理想的方式就是对所需要的修改的数据进行精确地锁定。锁粒度也称锁的级别，分为表锁和行锁。
（1）表锁：是MySQL中最基本的锁策略，也是成本最小的锁策略，并发性低，在MySQL服务器层实现的。
（2）行级锁：最大限度的支持并发处理（同时带来最大的所开销），行级锁只在存储引擎层实现，而mysql服务器层没有实现。

3.3 死锁和阻塞

1.阻塞：阻塞是因为不同锁之间兼容性的关系在有些时候一个事务中的锁需要等待另一个事务中的锁释放所释放的资源，阻塞保证事务并发时可以正常运行，阻塞是由于资源不足引起的排队等待现象。

2.死锁：是指两个或者多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，可能会造成死锁。多个事务同时锁定同一个资源，也会产生死锁。

3.死锁的例子：

如果凑巧，两个事务都执行了第一条更新语句，并且都锁定了这一行数据，接着两个事务试着去执行第二行语句时，却发现该行数据已经被对方锁定，同时自身又持有对方的锁，出现死锁现象。

4.死锁解决方案
为了解决死锁，数据库系统实现了各种死锁检测和死锁超时机制。检测到死锁的循环依赖之后立即返回一个错误，这种方式很有效；还有一种解决方式是查询的时间达到锁等待超时的设定之后放弃锁请求，这种方式不太好。InnoDB目前处理死锁的方式是，将持有最少行级排他锁的事务进行回滚。

注意：锁的行为和顺序与存储引擎相关。死锁产生的原因有两种，一种是真正的数据冲突，一种是由于存储引擎的实现方式导致的。

4. 事务

4.1 事务介绍

事务（transaction）：是一组原子性的SQL查询，或者说是一个独立的工作单元。事务内的语句，要么就全部执行，要么就全都不执行。

4.2 事务特性（ACID)

• 原子性(atomicity)：指事务是一个不可分割的最小工作单位，事务中的操作要么全部提交成功，要么全部失败回滚。
• 一致性（consistency）：数据库总是从一个一致性状态转换到另一个一致性状态。
• 隔离性（isolation）：通常来说，一个事务所做的修改在最终提交以前，对其它事务是不可见的。
• 持久性（durability)：事务一旦提交，则其所做的修改就会永久保存到数据库中。

4.3 事务的隔离级别

4.3.1 隔离问题

脏读：指一个事务读取了另一个事务未提交的数据。
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。一个事务读取到了另一个事务提交后的数据。（update）
虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。读到的数据变多了 (insert)

4.3.2 隔离级别

根据存在的隔离问题，数据库事务的隔离级别有4种，由低到高分别为未提交读（Read uncommitted ）、提交读（Read committed ）、重复读（Repeatable read ）、序列化（Serializable）。

• READ UNCOMMITTED(未提交读)：一个事务可以读取另一个未提交事务的数据
• READ COMMITTED(提交读)：提交读也叫nonrepeatable read（不可重复读）就是一个事务要等另一个事务提交后才能读取数据。一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的。但是两次执行一样的查询，可能会得到不一样的结果（针对于update操作）。
• REPETABLE READ(可重复读)：重复读可以解决不可重复读问题，可重复读无法解决幻读的问题（insert操作）。是MySQL默认的隔离级别，InnoDB和XtraDB存储引擎通过多版本并发控制解决了幻读的问题。
• SERIALIZABLE(可串行化)：Serializable 是最高的事务隔离级别，通过强事务串行执行，避免了幻读的问题。SERIALIZABLE会在读取的每一行数据上都加锁。

隔离级别	读数据一致性	脏读	不可重复 读	幻读	加锁读
未提交读(Read uncommitted)	最低级别	是	是	是	否
已提交读(Read committed)	语句级	否	是	是	否
可重复读(Repeatable read)	事务级	否	否	是	否
可序列化(Serializable)	最高级别，事务级	否	否	否	是

5. 多版本并发控制

MVCC是行级锁的一个变种，它在很多情况下都避免了加锁操作，因此开销更低。虽然实现机制不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。MVCC是通过保存数据在某个时间点的快照来实现的。
InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间（或过期时间）。这里存储的并不是实际的时间值，而是系统版本号(可以理解为事务的ID)，每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的行记录的版本号做比较。因此InnoDB使用MVCC解决了幻读的问题。下面看一下在REPETABLE READ隔离级别下，MVCC是如何实现的：

MVCC只在REPETABLE READ READ COMMITTED两个隔离级别下工作，其他两个隔离级别都与MVCC不兼容。 READ UNCOMMITTED 总是读取最新的行，SERIALIZABLE则会对多有读取到的行都加锁。

6. MySQL常用存储引擎

6.1 MyISAM存储引擎

6.1.1 概述

MySQL5.5.8以及之前版本默认存储引擎MyISAM也是MySQL中大部分系统表和临时表使用的存储引擎，MyISAM存储引擎会将表存储在两个系统文件中， 数据文件（以MYD为扩展名），还有一个是索引文件（以MYI为扩展名）。MyISAM不支持事务和行级锁，崩溃之后无法修复。

6.1.2 MyISAM存储引擎特性

（1）加锁和并发
使用表级锁而不是行级锁，在对表中的数据进行加锁时要对整个表加锁，对表中的数据进行读取时也要对整个表加共享锁，使用MyISAM引擎的表的读取和写入操作是互斥的，在一些情况下对表中的数据进行读取时也可以在表的末尾插入数据，MyISAM对读写混合的操作的并发性不是很好。
（2）修复
对于MyISAM表，MySQL可以进行手工或者自动执行检查和修复操作（和事务恢复以及崩溃恢复不同）。执行表的修复可能导致一些数据丢失，而且修复操作非常慢，可以使用check table tablename对表进行检查，使用repair tale tablename对表进行修复。另外，mysql还提供一个命令行工具 myisamchk --help这个工具也可以对MyISAM表进行修复，但是有一点要注意就是mysql的服务必须要停止，否则会对表造成更加严重的损坏。
（3）索引特性
支持全文索引，并且是在5.7版本之前唯一原生就支持全文索引的官方的存储引擎,还支持对BLOB和TEXT字段建立前500个字符的前缀索引。
（4）数据压缩
可以使用myisampack对MyISAM表进行压缩。压缩表是不能进行修改的（除非先将表解压，修改数据，再次压缩）。压缩表可以极大的减少磁盘的空间占用，也可以减少磁盘I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。由于压缩表中的记录是独立压缩的，因此读取单行的时候不需要去解压整个表。

注：MyISAM另一个与众不同的地方是它的缓冲池只缓存索引文件，而不缓冲数据文件，数据文件的缓存交由操作系统来完成。

6.1.3 限制

MySQL 5.0之前的版本，默认表的大小为4G，如存储大表则要修改MAX_Rows和AVG_ROW_LENGTH，这连个参数相乘的大小就是表能达到的最大的大小，修改这两个参数会导致重建整个表和表的索引，需要一定的时间；MySQL 5.0版本之后，单表默认大小为256TB。

6.1.4 使用场景

• 非事务型应用
• 只读类应用
• 空间类应用（MySQL5.7之前，MyISAM是唯一一个支持空间函数的存储引擎）

6.2 InnoDB存储引擎

6.2.1 概述

MySQL5.5.8及之后的默认存储引擎，Innodb是一种事务型存储引擎，Innodb使用表空间进行数据存储，表中的数据是存储在表空间之中。由innodb_file_per_table这个参数来决定，ON表示独立表空间，文件名为tablename.ibd;OFF表示将数据存储到系统的共享表空间，文件为ibdataX（X表示数字，从1开始）。

6.2.2 InnoDB存储引擎的特点

（1）事务型存储引擎，完全支持事务的ACID特性，支持行级锁，支持外键。
（3）采用MVCC来支持高并发，并且实现四个标准的隔离级别，默认的级别是REPEATABLE READ（可重复读）,并且通过间隙锁（next-key locking)策略防止幻读的出现。间隙锁不仅仅使得InnoDB锁定查询涉及的行，还会对索引的间隙进行锁定，以防止幻影行的插入。InnoDB存储引擎还提供插入缓存、二次写、自适应哈希索引、预读等高性能和高可用的功能
（3）InnoDB表是基于聚簇索引建立的，每张表的存储都是按照主键的顺序进行存放。
（4）自动崩溃恢复特性等。

6.2.3 适用场景

除非需要使用到某些InnoDB不具备的特性，并且没有其他的办法可以代替，否则应该优先选择InnoDB存储引擎。需要注意的是在MySQL5.6之后，InnoDB存储引擎也支持全文索引，空间函数。
具体可以参考博客：MySQL中的全文索引（InnoDB存储引擎）

6.3 Archive存储引擎

• Archive存储引擎只支持INSERT和SELECT操纵，会缓存所有的写并且利用zlib对插入进行压缩，所以比MyISAM表的磁盘I/O更小。
• 支持行级锁和专业的缓冲区，可以实现高并发插入。模仿了事务和MVCC的一些特性，但是不是一个事务型的存储引擎
• 只允许在自增ID上加索引

使用场景：日志和数据采集类应用

6.4 CSV存储引擎

• CSV存储引擎以CSV格式进行数据存储，每一列以逗号来分割，文本类型用双引号引起来
• 所有列必须非空
• 不支持索引（索引可以优化查询的效率，如果一个表不支持索引，那么每次查询时都会进行全表扫描），因此不适合大表，不适合在线处理；
• 可以对数据文件直接编辑（其他形式以二进制进行存储）

使用场景：适合作为数据交换的中间表（可以将excl等电子表格中的数据存储为CSV文件，然后复制到MySQL目录下；同样将数据复制到CSV文件中，其他web程序就可以读取）。

6.5 Memory存储引擎

也称为HEAP存储引擎，所以数据保存在内存中，数据易丢失，但是表结构会保留。

• 支持哈希索引和BTREE索引，如果不指定默认为HASH索引，由于hash索引的特点在做等值查询的时候会非常快，但如果使用范围查询的话就无法使用hash索引了，因此要根据业务场景来选择合适的索引，等值查找推荐hash，范围查找推荐BTree索引
• 所有字段都为固定长度
• 不支持BLOG和TEXT大字段
• 使用表级锁
• 表的最大大小由max_heap_table_size参数决定（默认值16M）

容易混淆的概念：Memory存储引擎表和临时表
Memory存储引擎表和临时表，其中临时表有两种：一种是系统使用临时表（查询优化器为了优化一些查询时所使用的临时表，即内部临时表）；另一种是create temporary table 建立临时表（可以使用任何存储引擎）
临时表是只有当前线程可见，Memory存储引擎表所有线程都可使用

使用场景：

• 用于查找或者是映射表，例如邮编和地区的对应表
• 用于保存数据分析中产生的中间表
• 用于缓存周期性聚合数据的结果表

注意：使用Memory存储引擎的表易丢失，因此要求数据可再生

6.6 Federated存储引擎

提供了访问远程MySQL服务器上表的方法；本地不存储数据，数据全部放在远程服务器上；本地需要保存表结构和远程服务器的连接信息。默认是禁止的。

使用场景：偶尔的统计分析及手工查询，不适合业务场景

6.7 常见的四种存储引擎总结

特性	MyISAM	InnoDB	Memory	Archive
存储限制	256TB	64TB	RAM	None
支持事务	不支持	支持	不支持	不支持
全文索引	支持	支持	不支持	不支持
B树索引	支持	支持	支持	不支持
哈希索引	不支持	支持	支持	不支持
数据缓存	不支持	支持	支持	不支持
索引缓存	支持	支持	支持	不支持
外键	不支持	支持	不支持	不支持
锁机制	表锁	行锁	表锁	行锁

6.8 如何选择存储引擎

（1）事务
如过需要事务支持，那么选择InnoDB（或XtraDB）。如果不需要事务，并且主要是SELECT和INSERT操作，那么MyISAM可以选择。一般日志性应用比较符合这一特性。
（2）备份
如果需要在线热备份，那么选择InnoDB是基本要求。
（3）崩溃恢复
MyISAM崩溃后发生损坏的概率比InnoDB高很多，且恢复速度慢。
（4）特有的特性
有些应用可能依赖一些存储引擎所独有的特性或者优化，比如有很多应用依赖聚簇索引的优化。

参考：《高性能MySQL》第三版