Chapter.1 Structure and History of MySQL

1.1 MySQL logical structure

MySQL逻辑架构如上图所示。最上层服务并非mysql独有，大部分基于网络的工具都有类似的C/S架构。第二层包含大部分mysql的核心功能，包括查询解析、分析、优化、cache、所有的内置函数（日期、时间、数学和加解密）以及所有跨存储引擎的功能（存储过程、trigger、view）。第三层包含存储引擎，负责mysql数据的存储和提取。server通过api与存储引擎通信，这些api屏蔽了不同存储引擎的差异。存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信。

每个client连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行。服务器缓存线程，不需要为每一个新建的连接创建或者销毁线程。

mysql解析查询，并创建内部数据结构（解析树），然后对其优化，包括重写查询，决定表的读写顺序以及选择合适的索引等。可以通过特殊的关键字提示优化器，影响它的决策过程，也可以请求优化器解释优化过程的各个因素，使用户知道服务器是如何进行优化决策的。优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。

对于select语句，在解析查询之前，服务器会先检查query cache，如果能找到，直接返回查询结果。

1.2 Concurrency control

mysql存在两个层面的并法控制：服务器层和存储引擎层。在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁被称为共享锁(shared lock)和排他锁(exclusive lock)，也叫读锁(read lock)和写锁(write lock)。读锁是共享的、相互不阻塞的，多个client在同一时刻可以同时读取同一个资源而互不干扰。写锁是排他的，写锁会阻塞其他的读/写锁。只有这样才能保证同一时间，只有一个client写入资源，并防止其他client读取正在写入的资源。

一种提高共享资源并发性的方式就是让锁定对象更有选择性，尽量只锁定需要修改的部分数据，而不是所有的数据。在给定的资源上，锁定的数据量越少，系统的并发程度越高。

加锁/管理锁也需要消耗系统资源，所谓的锁策略，就算在锁的开销和数据的安全性之间寻求平衡，这种平衡会影响到性能。大多数db都是在表上施加行级锁(row-level lock)，并以各种复杂的方式实现。mysql提供多种选择，每种存储引擎都可以实现自己的锁策略和锁粒度。下面介绍两种重要的锁策略。

表锁(table lock)是mysql最基本的锁策略，开销最小。一个用户对表进行写操作前(cud)需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁。写锁有着比读锁更高的优先级，写锁可以插入到锁队列中读锁的前面。

行级锁(row-level lock)可以最大程度地支持并发处理，也带来最大的锁开销。innoDB/xtraDB和其他存储引擎实现行级锁。行级锁只在存储引擎实现，mysql服务器层没有实现。

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

1.3 Transaction

事务四大特征(ACID)：原子性(atomicity)，一致性(consistency)，隔离性(isolation)，持久性(durability)。用户可以根据业务是否需要事务处理，选择合适的存储引擎。对于一些不需要事务的查询类应用，选择一个非事务性的存储引擎，可以获得更好的性能。

原子性：一个事物必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。

一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。若执行事务时系统崩溃，事务所作的修改也不会生效。

隔离性：通常来说，一个事务所作的修改在最终提交之前，对其他事务是不可见的。这里的“通常来说”，受到隔离级别的限制。

持久性：一旦事务提交，所作的修改就会永久保存到数据库中。实际上持久性也分很多不同的级别，而且不存在100%持久性保证的策略。

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。介绍一下四种隔离级别：

未提交读(read uncommitted)/脏读(dirty read)：事务中的修改，即使没有提交，对其他事务都可见。性能上不会比其他级别好太多，但会引入太多问题，很少使用。

提交读(read committed)/不可重复读(nonrepeatable read)：大多数db默认的隔离级别都锁提交读(比如sql server和oracle，但mysql不是)，提交读满足如下定义：一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所作的任何修改对其他事务都不可见。也叫不可重复读，因为执行两次同样的查询，可能会得到不一样的结果。要注意，不可重复读对应的是修改，即update操作。当两条查询语句中间夹杂着其他update事务的提交，就会导致两次同样的查询结果不一致。

可重复读(repeatable read)：该级别保证了在同一个事务中多次读取同样的记录，结果一致，是mysql的默认事务隔离级别。也就是说，上面谈到的“其他update事务”也被隔离了，但没有解决幻读(phantom read)问题。幻读对应的是insert操作，指的是当某个事务在执行查询后，另外一个事务又插入了新的记录，再次执行查询语句时，会查询到新的记录，即幻行(plantom row)。innodb和xtradb存储引擎通过多版本并发控制解决了幻读的问题。

序列化/可串行化(serializable)：最高的隔离级别，强制事务串行执行，避免了前面说的幻读问题。在该隔离级别下，会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用问题。只有在确保数据的一致性且可以接受没有并发的情况下，才考虑采用这一级别。

死锁(dead lock)指两个或以上事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象（多个事务同时锁定统一资源也会）。比如，事务A先修改a再修改b，事务B先修改b再修改a，就会导致最简单的死锁。越复杂的存储引擎，比如innodb，越能检测到死锁的循环依赖，并立即返回一个错误。另一种解决方式是：当查询时达到锁等待超时后放弃锁请求，但不太好。innodb目前的做法是，将持有最少行级排他锁的事务进行回滚。这种死锁处理算法相对简单。

锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句，有些存储引擎会产生死锁，有些不会。死锁的产生有两个原因：有些是因为真正的数据冲突，这种很难避免；有些完全是由于存储引擎的实现方式导致的。死锁发生之后，只有部分或者完全回滚其中一个事务，才能打破死锁，这对于事务型db来说无法避免，大多数情况下只要重新执行因死锁被回滚的事务即可。

事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到硬盘。事务日志采用的是追加的方式，因此写日志的操作是硬盘上一小块区域内的顺序IO，速度快得多。事务日志持久化后，内存中被修改的数据在后台可以慢慢刷回到硬盘，这种做法被称为预写式日志(write-ahead logging)，修改数据需要写两次磁盘。目前大多数存储引擎都是这样实现的。如果事务日志已持久化，但数据本身未写入硬盘，系统崩溃，存储引擎重启后能自动恢复这部分被修改的数据，具体实现方式视存储引擎而定。

mysql提供了两种事务型存储引擎：innodb和ndb cluster。一些第三方存储引擎也支持事务，如xtradb和pbxt。mysql默认采用自动提交模式，如果不是显式开始一个事务，则每个查询都被当做一个事务执行提交操作。还有一些命令在执行之前会强制执行commit提交当前的活动事务。例如，在数据定义语言(DDL)中，如果是会导致大量数据改变的操作，比如alter table，或者lock table也会有同样效果。

mysql服务器层不管理事务，事务是由存储引擎实现的，在同一事务中使用多种存储引擎是不可靠的。如果事务中混用了事务型和非事务型的表(如innodb表和myisam表)，正常提交下不会有什么问题。但如果事务需要回滚，非事务表的变更就无法撤销，这会导致数据库处于不一致的状态。

innodb采用的是两阶段锁定协议(two-phase locking protocol)。在事务执行过程中，随时都可以执行锁定，锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。前面描述的锁定都是隐式锁定，innodb会根据隔离级别在需要的时候自动加锁。mysql也支持lock tables和unlock tables语句，这是在服务器层实现的，但不能替代事务。

1.4 Multi-Version Concurrency Control

mysql的大多数事务型存储引擎实现的都不是简单的行级锁，不只是mysql，包括oracle、postgresql等db也都实现了多版本并发控制(MVCC)，但各自的实现机制不尽相同，因为MVCC没有一个统一的实现标准。可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始时间的不同，每个事务对同一张表、同一时刻看到的数据可能是不一样的。

不同存储引擎的MVCC实现方式不一样，典型的有乐观(optimistic)MVCC和悲观(pessimistic)MVCC，下面通过innodb简单介绍MVCC如何运行。

innodb的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间(或删除时间)，存储的也不是实际时间值，而是系统版本号。每开始一个新的事务，系统版本号会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。看一下可重复读隔离级别下，MVCC具体是如何操作的：

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

1. innodb只查找版本早于当前事务版本的数据行(行的系统版本号小于等于事务系统版本号)，这样可以确保事务读取的行，要么是事务开始前已经存在的，要么是事务自身插入或修改过的。
2. 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

insert：innodb为新插入的每一行保存当前系统版本号作为行版本号。

delete：innodb为删除的每一行保存当前系统版本号作为行删除标识。

update：innodb为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

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

MVCC只在提交读和可重复读两个隔离级别下工作，脏读和序列化这两个隔离级别都不兼容MVCC，因为脏读总是读取最新的数据行，而不是符合当前事务的数据行，而序列化会对所有读取的行加锁。

1.5 MySQL Storage Engine

在文件系统中，mysql将每个db（也可以称为schema）保存为数据目录下的一个子目录。创建表时，mysql会在db子目录下创建一个和表同名的.frm文件保存表的定义。因为mysql使用文件系统的目录和文件来保存db和表定义，大小写敏感性和具体的平台密切相关。不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在mysql服务层统一处理的。可以使用show table status like 'table_name'或查询information_schema中对应的表，显示表的相关信息。值得注意的是，表相关信息中rows列的值不一定精确，如果是myisam表则是精确的，如果是innodb表，则为估计值。

innodb是mysql的默认事务型引擎，它被设计用来处理大量的短期事务。短期事务大部分都是正常提交的，很少会被回滚。innodb的性能和自动崩溃回复特性，使得它在非事务型存储的需求中也很流行。

innodb的数据存储在表空间中，表空间是由innodb管理的一个黑盒，由一系列的数据文件组成。innodb可以将每个表的数据和索引存放在单独的文件中。innodb采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是可重复读，通过间隙锁(next-key locking)策略防止幻读的出现。间隙锁使得innodb不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻行插入。

innodb表是基于聚簇索引建立的，它的索引结构和mysql其他存储引擎有很大不同，聚簇索引对主键查询有很高的性能。不过它的二级索引(非主键索引)必须包含主键列，所以如果主键列很大的话，其他所有索引都会很大。所以如果表上的索引很多的话，主键应当尽可能小。

作为事务型引擎，innodb通过一些机制和工具支持真正的热备份（如oracle的mysql enterpise backup），mysql其他存储引擎不支持热备份，要保持一致性，需要停止对所有表的写入。

myisam提供大量特性，包括全文索引、压缩、空间函数(GIS)等，但myisam不支持事务和行级锁，而且崩溃后无法安全恢复。对于只读的数据，或者表比较小，可以忍受修复操作，则可以使用myisam。

myisam将表存储在两个文件中：数据文件和索引文件，分别以.myd和.myi为扩展名。myisam表可以包含动态或静态行。mysql会根据表的定义来决定采用何种行格式。如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许适合采用myisam压缩表。压缩表不能修改，除非先将表解压，修改数据后再次压缩。压缩表可以极大地减少磁盘占用和IO，支持只读索引。

myisam有一些服务器级别的性能扩展限制，比如对索引键缓冲区的mutex锁。但最典型的性能问题还是表锁的问题，如果所有的查询长期处于locked状态，表锁就是罪魁祸首。

如何选择合适的存储引擎？大部分情况下，innodb是最好的选择。除非需要用到某些innodb不具备的特性，否则都应该优先选择innodb。如果要选择不同存储引擎，需要考虑是否使用事务、如何执行备份、崩溃后如何恢复、以及存储引擎的特性。

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

1. 使用alter table table_name ENGINE = InnoDB，适用于所有存储引擎，问题是会执行很长时间。mysql会按行将数据从原表复制到一张新表中，复制期间可能会消耗系统所有的IO能力，同时原表会加上读锁。需要注意的是，如果转换表的存储引擎，会失去原引擎相关的所有特性。

2. 使用mysqldump将数据导出到文件，然后修改文件中’create table’语句的存储引擎选项，并修改表名(db中不允许同名表出现)，再重新导入文件。

3. 利用’insert…select语法导数据

create table innodb_table like myisam_table;
alter table innodb_table ENGINE=InnoDB;
insert into innodb_table select * from myisam_table;

若数据量很大，则可以做分批处理，针对每一段数据执行事务提交操作。

原文地址：http://www.cnblogs.com/JHSeng/p/16865349.html