MySQL查询执行过程
MySQL执行一个查询的过程：
1.客户端发送一条查询给服务器。
2.服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3.服务器端进行SQL解析、预处理。
4.由优化器生成对应的执行计划。
5.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
6.将结果返回给客户端。
MySQL 客户端/服务器通信协议
MySQL客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无须将一个消息切成小块独立来发送。
这种协议让MySQL通信简单快速，但是也从很多地方限制了MySQL。一个明显的限制是，这意味着没法进行流量控制。一旦客户端发送了请求，它能做的事情就只是等待结果了。
相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。这也是在必要的时候一定要在查询中加上LIMIT限制的原因。
查询缓存
MySQL的缓存类型：缓存完整的SELECT查询结果，也就是“查询缓存”。MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，MySQL会立刻返回结果，跳过了解析、优化和执行阶段。
在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。
如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。
对InnoDB来说，事务的一些特性会限制查询缓存的使用。当一个语句在事务中修改了某个表，MySQL会将这个表的对应的查询缓存都设置失效，而事实上，InnoDB的多版本特性会暂时将这个修改对其他事务屏蔽。在这个事务提交之前，这个表的相关查询是无法被缓存的，所以所有在这个表上面的查询——内部或外部的事务——都只能在该事务提交后才被缓存。因此，长时间运行的事务，会大大降低查询缓存的命中率。
打开查询缓存对读和写操作都会带来额外的消耗：
- 读查询在开始之前必须先检查是否命中缓存。
- 如果这个读查询可以被缓存，那么当完成执行后，MySQL若发现查询缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗。
- 这对写操作也会有影响，因为当向某个表写入数据的时候，MySQL必须将对应表的所有缓存都设置失效。如果查询缓存非常大或者碎片很多，这个操作就可能会带来很大系统消耗。
如果不想所有的查询都进入查询缓存，但是又希望某些查询走查询缓存，那么可以将query cache_type 设置成DEMAND，然后在希望缓存的查询中加上SQL_CACHE。
查询优化处理
查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。 这个过程中任何错误（例如语法错误）都可能终止查询。
语法解析器和预处理
首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。 MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正匹配。 预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里将检查数表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。 下一步预处理器会验证权限。这通常很快，除非服务器上有非常多的权限配置。
查询优化器
现在语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
查询执行引擎
在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样会生成对应的字节码。
相对于查询优化阶段，查询执行阶段不是那么复杂：MySQL只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为“handlerAPI”的接口。查询中的每一个表由一个handler的实例表示。MySQL在优化阶段就为每个表创建了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息，等等。
返回结果给客户端
查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。
如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。
MySQL将结果集返回客户端是一个增量、逐步返回的过程。结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封包进行缓存然后批量传输。
分区表
对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象（Handler Object)的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。从底层的文件系统来看就很容易发现，每一个分区表都有一个使用#分隔命名的表文件。
MySQL实现分区表的方式——对底层表的封装——意味着索引也是按照分区的子表定义的，而没有全局索引。这和Oracle不同，在Oracle中可以更加灵活地定义索引和表是否进行分区。
MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区——只需要查找包含需要数据的分区就可以了。
分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便。
分区表场景与限制
下面场景中分区可以起到非常大的作用：
- 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
- 分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。 另外，还可以对一个独立分区进行优化、检查、修复等操作。
- 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
- 可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问、ext3文件系统的InnoDe锁竞争等。
- 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。
分区表本身也有一些限制，下面是其中比较重要的几点：
- 一个表最多只能有1024个分区。
- 在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5中，某些场景中可以直接使用列来进行分区。
- 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
- 分区表中无法使用外键约束。
- NULL值会使分区过滤无效
- 分区列和索引列不匹配，无法进行分区过滤
分区表的原理
分区表由多个相关的底层表实现，这些底层表也是由句柄对象（Handlerobject)表示，所以我们也可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样，分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。
分区表上的操作按照下面的操作逻辑进行:
SELECT查询
当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。
INSERT操作
当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表。
DELETE操作
当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。
UPDATE操作
当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。
虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，例如InnoDB，则会在分区层释放对应表锁。这个加锁和解锁过程与普通 InnoDB上的查询类似。
分区表的类型
MySQL支持多种分区表。我们看到最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以是包含列的表达式。MySQL还支持键值、哈希和列表分区，这其中有些还支持子分区等。
如何使用分区表
为了保证大数据量的可扩展性，一般有下面两个策略:
全量扫描数据，不要任何索引
可以使用简单的分区方式存放表，不要任何索引，根据分区的规则大致定位需要的数据位置。只要能够使用WHERE 条件，将需要的数据限制在少数分区中，则效率是很高的。当然，也需要做一些简单的运算保证查询的响应时间能够满足需求。使用该策略假设不用将数据完全放入到内存中，同时还假设需要的数据全都在磁盘上，因为内存相对很小，数据很快会被挤出内存，所以缓存起不了任何作用。这个策略适用于以正常的方式访问大量数据的时候。
索引数据，并分离热点
如果数据有明显的“热点”，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中。这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效地使用缓存。
复制功能
MySQL内建的复制功能是构建基于MySQL的大规模、高性能应用的基础，这类应用使用所谓的“水平扩展”的架构。我们可以通过为服务器配置一个或多个备库的方式来进行数据同步。复制功能不仅有利于构建高性能的应用，同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。
复制概述
复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。主库和备库之间可以有多种不同的组合方式。
MySQL支持两种复制方式：基于行的复制和基于语句的复制。这两种方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。这意味着，在同一时间点备库上的数据可能与主库存在不一致，并且无法保证主备之间的延迟。一些大的语句可能导致备库产生几秒、几分钟甚至几个小时的延迟。
复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，但出于备份或及时从崩溃中恢复的目的，这点开销也是必要的。除此之外，每个备库也会对主库增加一些负载（例如网络I/O开销），尤其当备库请求从主库读取旧的二进制日志文件时，可能会造成更高的I/O开销。另外锁竞争也可能阻碍事务的提交。最后，如果是从一个高吞吐量的主库上复制到多个备库，唤醒多个复制线程发送事件的开销将会累加。
通过复制可以将读操作指向备库来获得更好的读扩展，但对于写操作，除非设计得当，否则并不适合通过复制来扩展写操作。在一主库多备库的架构中，写操作会被执行多次，这时候整个系统的性能取决于写入最慢的那部分。
基于语句复制
最明显的好处是实现相当简单。理论上讲，简单地记录和执行这些语句，能够让主备保持同步。另一个好处是二进制日志里的事件更加紧凑。一条更新好几兆数据的语句在二进制日志里可能只占几十个字节。
缺点同一条SQL在主库和备库上执行的时间可能稍微或很不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的SQL。另外一个问题是更新必须是串行的。这需要更多的锁。
基于行的复制
这种方式会将实际数据记录在二进制日志中。最大的好处是可以正确地复制每一行。一些语句可以被更加有效地复制。缺点比如做全表更新，使用基于行的复制开销会很大，因为每一行的数据都会被记录到二进制日志中，这使得二进制日志事件非常庞大。并且会给主库上记录日志和复制增加额外的负载，更慢的日志记录则会降低并发度。
MySQL在这两种复制模式间动态切换。 默认情况下使用的是基于语句的复制方式，但如果发现语句无法被正确地复制，就切换到基于行的复制模式。还可以根据需要来设置会话级别的变量binlog format，控制二进制日志格式。
复制比较常见的用途
数据分布
MySQL复制通常不会对带宽造成很大的压力，但基于行的复制会比传统的基于语句的复制模式的带宽压力更大。你可以随意地停止或开始复制，并在不同的地理位置来分布数据备份，例如不同的数据中心。
负载均衡
通过MySQL复制可以将读操作分布到多个服务器上，实现对读密集型应用的优化，并且实现很方便，通过简单的代码修改就能实现基本的负载均衡。
备份
对于备份来说，复制是一项很有意义的技术补充，但复制既不是备份也不能够取代备份。
高可用性和故障切换
复制能够帮助应用程序避免MySQL单点失败，一个包含复制的设计良好的故障切换系统能够显著地缩短宕机时间。
MySQL升级测试
这种做法比较普遍，使用一个更高版本的MySQL作为备库，保证在升级全部实例前，查询能够在备库按照预期执行。
复制工作流程
总的来说，复制有三个步骤：
1.在主库上把数据更改记录到二进制日志（Binary Log）中（这些记录被称为二进制日志事件）。
2.备库将主库上的日志复制到自己的中继日志（Relay Log）中。
3.备库读取中继日志中的事件，将其重放到备库数据之上。
第一步是在主库上记录二进制日志。在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。
下一步，备库将主库的二进制日志复制到其本地的中继日志中。首先，备库会启动一个工作线程，称为I/O线程，I/O线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储（binlog dump)线程，这个二进制转储线程会读取主库上二进制日志中的事件。它不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒，备库I/O线程会将接收到的事件记录到中继日志中。
备库的SQL线程执行最后一步，该线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当SQL线程追赶上I/O线程时，中继日志通常已经在系统缓存中，所以中继日志的开销很低。SQL线程执行的事件也可以通过配置选项来决定是否写入其自己的二进制日志中。
数据分片
在目前用于扩展大型MySQL应用的方案中，数据分片注是最通用且最成功的方法。它把数据分割成一小片，或者说一块，然后存储到不同的节点中。
数据分片在和某些类型的按功能划分联合使用时非常有用。大多数分片系统也有一些“全局的”数据不会被分片（例如城市列表或者登录数据）。全局数据一般存储在单个节点上，并且通常保存在类似memcached这样的缓存里。
选择分区键（partitioning key)
数据分片最大的挑战是查找和获取数据：如何查找数据取决于如何进行分片。有很多方法，其中有一些方法会比另外一些更好。
我们的目标是对那些最重要并且频繁查询的数据减少分片。这其中最重要的是如何为数据选择一个或多个分区键。分区键决定了每一行分配到哪一个分片中。
某些数据模型比其他的更容易进行分片，具体取决于实体一关系图中的关联性程度。
左边的数据模型比较容易分片，因为与之相连的子图中大多数节点只有一个连接，很容易切断子图之间的联系。右边的数据模型则很难分片，因为它没有类似的子图。
选择分区键的时候，尽可能选择那些能够避免跨分片查询的，但同时也要让分片足够小，以免过大的数据片导致问题。如果可能，应该期望分片尽可能同样小，这样在为不同数量的分片进行分组时能够很容易平衡。
数据分配
将数据分配到分片中有两种主要的方法：固定分配和动态分配。两种方法都需要一个分区函数，使用行的分区键值作为输入，返回存储该行的分片。
固定分配
固定分配使用的分区函数仅仅依赖于分区键的值。哈希函数和取模运算就是很好的例子。 这些函数按照每个分区键的值将数据分散到一定数量的“桶”中。固定分配的主要优点是简单，开销低，甚至可以在应用中直接硬编码。
但固定分配也有如下缺点：
- 如果分片很大并且数量不多，就很难平衡不同分片间的负载。
- 固定分片的方式无法自定义数据放到哪个分片上，这一点对于那些在分片间负载不均衡的应用来说尤其重要。一些数据可能比其他的更加活跃，如果这些热点数据都分配到同一个分片中，固定分配的方式就无法通过热点数据转移的方式来平衡负载。
- 修改分片策略通常比较困难，因为需要重新分配已有的数据。例如，如果通过模10的哈希函数来进行分片，就会有10个分片。如果应用增长使得分片变大，如果要拆分成20个分片，就需要对所有数据重新哈希，这会导致更新大量数据，并在分片间转移数据。
动态分配
另外一个选择是使用动态分配，将每个数据单元映射到一个分片。动态分配的最大好处是可以对数据存储位置做细粒度的控制。这使得均衡分配数据到分片更加容易，并可提供适应未知改变的灵活性。
参考
高性能MySQL
