MySQL逻辑架构

MySQL服务器逻辑架构图

如图所示，MySQL的架构是三层架构，具体介绍如下：

最上层的服务（层）并不是MySQL独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。这层的主要作用是连接处理、授权认证、安全等等。
第二层架构的是MySQL核心服务层，大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
第三层包括了存储引擎。存储引擎负责MySQL中数据的存储和提取。存储引擎不会去解析SQL（InnoDB例外，它会解析外键定义，因为MySQL服务器本身没有实现该功能），不同的存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求。

连接管理与安全性

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

当客户端连接到MySQL服务器时，服务器需要对其进行认证，认证基于用户名、原始主机信息和密码。

优化与执行

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

用户可以通过特殊的关键字提示优化器，影响它的决策过程；也可以请求优化器解释优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构优化。

对于SELECT语句，在解析查询之前，服务器会先检查查询缓存，如果能在其中找到对应的查询，服务器就不必再执行查询解析、优化、和执行整个过程，而是直接返回查询缓存中的结果集。

并发控制

这里讨论MySQL在两个层面的并发控制：服务器层和存储引擎层。

读写锁

在处理并发或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁（shared lock）和排他锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）。

读锁：是共享的，也就是相互不阻塞的。多个客户端在同一时刻可以同时读取同一个资源，而互不干扰。
写锁：是排他的，也就是一个写锁会阻塞其他的写锁和读锁，确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。

锁粒度

一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是：只对会修改的数据片进行精确的锁定。下面是两种最重要的锁策略：表锁和行级锁。

表锁

表锁（table lock）是MySQL中最基本的锁策略，并且是开销最小的策略：它会锁定整张表。

在特定场景表锁可能有良好的性能。另外，写锁也比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁列表的前面，反之读锁则不能插入到写锁的前面。

尽管存储引擎可以管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不同的目的。例如服务器会为ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制。

行级锁

行级锁可以最大程度的支持并发，同时开销也最大。

行级锁只在存储引擎层实现，而MySQL服务层没有实现。

事务

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功的对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。

可以同START TRANSACTION开始一个事务，然后要么用COMMIT提交事务将修改的数据持久保留，要么使用ROLLBACK撤销所有的修改。

数据库的特性有四条，简称为ACID，分别是：原子性（atomicity）、一致性（consistency）、隔离性（isolated）和持久性（durability）。

原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元，整个事务操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中一部分操作，这就是事务的原子性。
一致性（consistency）：数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性（isolated）：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。这里的“通常来说”与隔离级别有关。
持久性（durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

隔离级别

在SQL标准中，定义了四种隔离级别。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

READ UNCOMMITED（未提交读）：在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也称为脏读（Dirty Read）。这个级别会导致很多问题，而从性能上来讲却并不会比其他级别好太多，但缺少其他级别的很多好处，在实际使用中很少使用。
READ COMMITTED（提交读）：大多数数据库系统的默认隔离级别都是READ COMMITTED（但MySQL不是）。这个级别满足隔离性。这个级别有时候也叫做不可重复读，因为两次执行同样的查询，可能会得到不一样的结果。
REPEATABLE READ（可重复读）：这一级别解决了脏读的问题，该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读级别还是无法结局幻读问题：当某个事务再次读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时，会产生幻行。InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC）解决了幻读问题。可重复读是MySQL的默认事务隔离级别。
SERIALIZABLE（可串行化）：这个级别是最高的隔离级别，通过强制事务串行执行，避免了幻读问题。即会再读取的每一行数据上都加上锁，所以可能导致大量的超时和锁争用问题，只要在非常需要确保数据一致性而且可以接受没有并发的情况下，才考虑使用该级别。

死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

为了解决这种问题，数据库实现了各种死锁检测和死锁超时机制。一种解决方式是当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太友好。InnoDB目前处理死锁的方法是：将持有最少行级排他锁的事务进行回滚。

死锁产生有双重原因：有些是因为真正的数据冲突，但有些则完全由于存储引擎的实现方式导致的。死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁。

事务日志

使用事务日志在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。

事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域的顺序I/O，所以采用事务日志的方式相对来说要快很多。

事务日志持久化以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。因此通常称之为预写式日志，修改数据需要写两次磁盘。

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改等数据。

MySQL中的事务

MySQL提供了两种事务型的存储引擎：InnDB和NDB Cluster。另外还有一些第三方的存储引擎也支持事务，例如XtraDB和PBXT。

自动提交

MySQL默认采用自动提交（AUTOCOMMIT）模式，也就是说，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。可以通过SET AUTOCOMMIT = ON/OFF来开启/关闭自动提交模式。

MySQL可以通过执行
SET [GLOBAL | SESSION] TRANSATION ISOLATION LEVEL READ UNCOMMITTED| READ COMMITTED| REPEATABLE READ| SERIALIZABLE来设置全局/当前会话的隔离级别，新的隔离级别会在下一个事务开始的时候生效。

隐式和显式锁定

InnoDB采用的是两阶段锁定协议。在执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK的时候才会释放，并且所有的锁都是在同一时刻被释放。这就是隐式锁定。

InnoDB也支持通过特定的语句进行显式锁定：SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE,MySQL也支持LOCK TABLES和UNLOCK TABLES语句，这是在服务层实现的，和存储引擎无关。

显示的使用这些语句不但没有必要，还会严重影响性能，实际上InnoDB的行级锁工作的更好，所以应当尽量避免使用LOCK TABLES。

多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁，基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制（MVCC）。可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有可能不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC的实现是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

SELECT：InnoDB会根据以下两个条件检查每行记录：
- InnoDB只查找早于当前事务版本的数据行（即行的系统版本号小于或等于事务的系统版本号），这样可以确保读取的行要么在事务开始前已经存在，要么是事务自身插入或者修改过的。
- 行的删除要么未定义，要么大于当前事务版本号。这样可以确保读取的行在事务开始之前未被删除。
INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
DELETE：InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE：InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

保存这两个额外系统版本号，优点就是使大多数读操作都可以不用加锁，读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。缺点就是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

MVCC只在 REPEATABLE READ（可重复读）和READ COMMITTED（提交读）两个隔离级别下工作，因为READ UNCOMMITTED总是读取最新的数据行，而SERIALIZABLE则会对所有读取的行加锁。

MySQL的存储引擎

可以使用SHOW TABLE STATUS 命令显示表的相关信息。例如：

mysql> show table status like 'user' \G
*************************** 1. row ***************************
           Name: user	//表名
         Engine: InnoDB	//表引擎
        Version: 10	//版本
     Row_format: Dynamic//行的格式。Dynamic的行长度是可变的，一般包含可变长度的字段，如VARCHAR
			//；Fixed的行长度则是固定的，只包含固定长度的列，如CHAR。Compressed的行只在压缩
			//表中存在。
           Rows: 5	//表中的行数，MyISAM和其他一些存储引擎是精确值，但InnoDB是估计值。
 Avg_row_length: 59	//平均每行包含的字节数
    Data_length: 16384	//表数据的大小（字节）
Max_data_length: 0	//表的最大数据容量，该值与存储引擎关
   Index_length: 0	//索引的大小
      Data_free: 0	//对于MyISAM表，表示已分配但目前没有使用的空间
 Auto_increment: 6	//下一个AUTO_INCREMENT的值
    Create_time: 2018-11-19 20:58:21	//表的创建时间
    Update_time: NULL	//表数据的最后修改时间
     Check_time: NULL	//使用CHECK TABLE命令或者myisamchk工具最后一次检查表的时间
      Collation: utf8_general_ci	//表的默认字符集和字符列排序规则
       Checksum: NULL	//如果启用，保存的是整个表的实时校验和
 Create_options:	//创建表时指定的其他选项
        Comment:	//建表时的备注
1 row in set (0.00 sec)

InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期事务，短期事务大部分情况是正常提交的，很少会回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。

InnoDB的数据存储在表空间长，表空间是由InnoDB管理的一个黑盒子，有一系列的数据文件组成，InnoDB可以将每个表的数据和索引存放在单独的文件中。

InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别，默认为REPATABLE READ，并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

InnoDB表是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能，不过它的二级索引中必须包含主键列。因此若表上的索引较多的话，主键应当尽可能的小。InnoDB是平台独立的，可以将数据和索引文件在平台之间拷贝。

InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以及加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。

MyISAM存储引擎

M有IASM是MySQL5.1之前的默认存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数等，但MyIASM不支持事务和行级锁，而且有一个最大的缺陷就是崩溃后无法安全恢复。

存储

MyIASM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。MyIASM表可以包含动态或者静态行。MySQL会根据表的定义来决定采用何种行格式。MyIASM表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。

在MySQL5.0中，MyIASM表如果是可变行，则默认配置只能处理256TB的数据，可以通过修改表单MAX_ROWS和AVG_ROW_lENGTH选项的值来实现，两者相乘就是表可能达到的最大大小。修改这两个参数会导致重建整个表和表的所有索引，这可能需要很长的时间才能完成。

MyIASM特性

加锁与并发：MyIASM对整张表加锁，而不是针对行。读取时会对需要读到的表加共享锁，写入时则对表加排他锁。但是在表有读取查询的同时，也可以往表中插入新的记录（也被称为并发插入）。
修复：对于MyIASM表，MySQL可以手工或者自动检查和修复操作。执行表的修复可能导致一些数据的丢失，而且修复操作是非常慢的。可以通过CHECK TABLE mytable检查表的错误，如果有错误可以通过执行REPAIR TABLE mytable来修复，或者使用myiasmchk命令行工具也可以。
索引特性：对于MyIASM表，即使是BLOB和TEXT等长字段，也可以基于其前500个字符创建索引。MyIASM也支持全文索引，这是一种基于分词创建的索引。
延迟更新索引键（Delayed Key Write）：如果在创建MyIASM表时指定了DELAY_KEY_WRITE选项，在每次修改执行完时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大的提升写入性能。

MyIASM压缩表

如果表在创建并导入以后，不会再进行修改操作，那么这样得表更适合采用MyIASM压缩表。

可以使用myiaspack对MyIASM表进行压缩。压缩表是不能进行修改的（除非先将表解压、修改数据、然后再次压缩）。压缩表可以极大的减少磁盘空间占用，减少磁盘I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。

MyIASM性能

MyIASM引擎数据以紧密格式存储，所以在默写场景下的性能很好。

MyIASM有一些服务器级别的性能扩展限制，比如对索引键缓冲区的Mutex锁，MariaDB基于段的索引键缓冲区机制来避免该问题，性能上的表锁问题。

选择合适的引擎

对于如何选择存储引擎，可以简单的归纳为一句话：除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以代替，否则都应该优先选择InnoDB引擎。

除非万不得已，否则建议不要混合使用多种存储引擎，佛祖额可能带来一系列复杂的问题，以及一些潜在的bug和边界问题。

如果需要不同的存储引擎，应先考虑以下几个因素：

事务：如果应用到事务，那么InnoDB是目前最稳定并且经过沿着轨道选择。如果不需要事务，并且主要是SELECT和INSERT操作，那么MyISAM是不错的选择。
备份：如果需要在线热备份，那么选择InnoDB就是基本的要求。
崩溃恢复：建议选择InnoDB，因为拥有自动恢复功能。
特有的特性：有些应用可能依赖一些存储引擎所独有的特性或者优化，比如聚簇索引的优化，应该综合各种情况考虑，选择满足特殊情况下最优的引擎。

转换表的引擎

转换表的引擎有三种方法：

ALTER TABLE：将表从一个引擎修改为另一个引擎最简单的办法是使用ALTER TABLE语句。例如：ALTER TABLE mytable ENGINE = InnoDB。这种语法是用于任何存储引擎，但是有一个缺点：需要执行很长时间。MySQL会按照行将数据从原表复制到一张新的表，在复制期间可能会消耗系统所有的I/O能力，同时原表会加上锁。同时如果转换表的存储引擎，将会失去和引擎相关的所有特性.
导出与导入：可以使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项。

创建与查询：这种方法不需要导出整个表，而是先创建一个新的存储引擎的表，然后利用INSERT–SELECT语法来导数据。

1
2
3

CREATE TABLE innodb_table LIKE myisam_table;
ALTER TABLE innodb_table ENGINE = InnoDB;
INSERT INTO innodb_table (SELECT * FROM myisam_table);

当然如果数据量很大的情况下，可以采用分批导入的方法。