只专注分区性能方面，其底层实现很复杂。

7.1 分区表

分区的主要目的将数据按照较粗的粒度分在不同的表中。
在创建表时使用 PARTITION BY 字句定义每个分区存放的数据。在执行查询时，优化器根据分区定义过滤出需要的分区，再查找数据。
对用户来说，分区表时一个独立的逻辑表，但底层是由多个物理子表组成。实现分区的功能从代码上讲，实际上是对一组底层表的句柄对象的封装。从底层文件系统来看，每个分区表都有一个使用 # 分隔命名的表文件。
分区表常见使用场景：

• 表非常大以至于无法全部都放到内存中，或只在表最后部分有热点数据，其他均是历史数据
• 容易维护数据
• 高效利用多个硬件设备，分区可以将数据分布到不同的物理设备
• 用分区表来避免某些特殊瓶颈（如：InnoDB单个索引的互斥访问、ext3文件系统的 inode 锁竞争）
• 可以备份恢复独立分区

分区限制：

• 一个表最多只能有 1024 个分区
• 在 MySQL 5.5 ，某些场景中可以直接使用列来进行分区
• 如果分区字段中有主键或者唯一索引的列，那么所有这些列都必须包含进来

• 分区表中无法使用外键约束

  7.1.1 分区表的原理

  分区表由若干个底层表组成，底层表也是由句柄对象表示，所以可以直接访问各个分区，所有底层表必须使用相同的存储引擎，存储引擎管理分区的各个底层表和管理普通表一样，分区表的索引只是在各底层表上各自加上一个完全相同的索引。
  分区表上的操作逻辑：
  进行 SELECT、INSERT、DELETE、UPDATE 操作时，首先都会“先打开并锁住所有的底层表”，再进行各自操作：

• SELECT 查询

优化器尝试过滤部分分区，再调用对应存储引擎接口访问各分区数据。

• INSERT 查询

确定接收记录的分区，再将记录写入对应底层表。

• DELETE 查询

确定数据对应分区，再对相应底层表进行删除。

• UPDATE 查询

确定需要更新的记录所在分区，再取出数据并更新，判断更新后数据应该存放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表数据进行删除操作。
有些操作是支持过滤的，如果有 WHERE 条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉。
虽然每个操作都会“先打开并锁住所有的底层表”，但并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁（如 InnoDB），则会在分区层释放对应表锁。这个加锁和解锁的过程与普通 InnoDB 上的查询类似。
后面会讲访问一个分区表，对底层表锁操作的代价及带来的后果。

7.1.2 分区表的类型

MySQL 中最多的是根据范围进行分区，分区表达式可以是列，也可以是包含列的表达式。MySQL 还支持键值、哈希和列表分区，但比较少。

范围分区

例如：将每一年的销售额存放在不同分区：

CREATE TABLE sales (
  order_date DATETIME NOT NULL,
  -- Other colums omitted
) ENGINE = InnoDB PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p_2010 VALUES LESS THAN (2010),
  PARTITION p_2011 VALUES LESS THAN (2011),
  PARTITION p_2012 VALUES LESS THAN (2012),
  PARTITION p_catchall VALUES LESS THAN MAXVALUE,
);

PARTITION 字句中可以使用各种函数，要求表达式返回的值是一个确定的整数，且不能是一个常数。

哈希分区

按时间分区的 InnoDB 表，系统通过自分区可以降低索引的互斥访问的竞争。而最近一年的分区的数据会被频繁访问到，这会导致该分区大量的互斥量的竞争。使用哈希子分区可以将数据切成多个小片，大大降低互斥量的竞争问题。

其他

• 根据键值进行分区
• 使用数学模函数分区

• 假设表有一个主键id，希望根据时间将最近的热点数据集中存放。（使用：HASH(id DIV 1000000)，将为100万数据建立一个分区）

  7.1.3 如何使用分区表

  当数据量很大时，B-Tree索引除了“索引覆盖查询”之外无法发挥作用，将产生大量随机I/O。Infobright，完全放弃使用 B-Tree 索引，选择了一些更粗粒度的但消耗更小的方式检索数据，例如在大量数据上只索引对应的一小块元数据。
  这正是分区表要做的事，以小代价定位到需要的数据所在“区域”，再得到需要的数据。
  为保证大数据量的扩展性，一般有下面两个策略：

  • 全量扫描数据，不要任何索引
  • 索引数据，并分离热点

MySQL 的分区表实现有陷阱，如下节。

7.1.4 什么情况下会出问题

两个分区策略都基于两个非常重要的假设：

• 查询都能够过滤很多额外的分区
• 分区本身并不会带来很多额外的代价

事实上，某些场景会有问题。

• NULL值会使分区过滤无效

例： 按照 PARTITION BY RANGE YEAR(order_data) 分区。
当插入一个 null 值或一个非法值时，记录会被放入到第一个分区。而查询时就不得不对目标分区和第一个分区进行查询。
解决办法：

  - 创建一个“无用”分区来存 null 或 非法值数据

使用：PARTITION p_nulls VALUES LESS THAN(0)

  - 在 MySQL 5.5 中可以直接使用“列本身”而不是“基于列的函数”进行分区

• 分区列和索引列不匹配
• 选择分区的成本可能很高

范围分区确定分区位置需要一定的成本，当分区数量过大时较为明显。
使用键分区或哈希分区则不会有这个问题。

• 打开并锁住所有底层表的成本可能很高

当查询访问分区表的时候，MySQL 需要打开并锁住所有的底层表，这时分区表的另一个开销。
可以使用批量操作来平均出较小的单个操作的开销。

• 维护分区的成本可能很高

新增，更新，删除等分区操作。

7.1.5 查询优化

分区最大的优点是优化器可以根据分区函数来过滤一些分区。对于访问分区表来说，很重要的一点是要在 WHERE 条件中带入分区列（即使看起来多余也要带上）。
EXPLAIN 的结果 partitions 可以看到优化过滤的分区结果。
MySQL 只能在使用分区的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数。
若分区表是关联查询的第二张表，且关联条件是分区键，MySQL 就只会在对应的分区里匹配行（在 EXPLAIN 是看不到的，因为是运行时优化）。

7.1.6 合并表

即将被淘汰。

7.2 视图

MySQL 5.0 之后开始引入视图。视图本身是一个虚拟表，不存放任何数据。在使用 SQL 语句访问视图的时候，它返回的数据是 MySQL 从其他表中生成的。视图和表是在同一个命名空间，很多时候是同样对待的，但也有不同（不能对视图创建触发器，不能使用 drop table 删除视图）。
接下来主要讲视图是如何实现的，以及优化器如何处理视图。

实现视图最简单的方法是将 SELECT 语句的结果存放到临时表，访问时取出，但这样明显有性能问题，优化器也很难优化整个查询。更好的办法是，重写含有视图的查询，将视图的定义 SQL 直接包含进查询的 SQL 中。这两种算法分别称为 合并算法 和 临时表算法 。

可以通过 EXPLAIN 的 select_type 来区分使用哪种视图实现算法。值为“DERIVED”，说明采用了临时表算法。
可以为一个基于简单查询的视图指定使用临时表算法：

CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM actor;

7.2.1 可更新视图

可更新视图是指可以通过更新这个视图来更新视图涉及的相关表。

• 所有使用临时表算法的视图都无法被更新
• 视图中包含了 GROUP BY、UNION、聚合函数，以及其他一些特殊情况，无法被更新
• 不能更新视图定义列以外的列
• 更新视图的查询也可以是一个关联语句，但被更新的列必须来自同一个表

• MySQL 不支持在视图上建立触发器

  7.2.2 视图对性能的影响

  不成熟，需要大量测试，建议不用。

  7.2.3 视图的限制

  7.3 外键约束

  外键给查询和新增带来额外的性能消耗，甚至可能带来难以排查的死锁，可以使用触发器替代。

  7.4 在MySQL内部存储代码

  MySQL 允许通过触发器、存储过程、函数、事件的形式来存储代码。可以合理使用。

  7.4.1 存储过程和函数

  7.4.2 触发器

  7.4.3 事件

  事件是 MySQL 5.1 引入的一种新的存储代码的方式，它类似于 Linux 的定时任务。
  如，每周一次针对某个数据库运行一个存储过程：

  CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK
  DO
  CALL optimize_table('somedb');

  7.4.4 在存储程序中保留注释

  7.5 游标

  MySQL 在服务器端提供只读的、单向的游标，可以逐行指向查询结果，然后让程序做进一步处理。只能在存储过程或更底层的客户端API中使用，因为 MySQL 游标中指向的对象都是存储在临时表中而不是实际查询到的数据，所以 MySQL 游标是只读的。
  在一个存储过程中，可以有多个游标，也可以在循环中“嵌套”使用游标。

  7.6 绑定变量

  7.7 用户自定义函数（UDF）

  存储过程只能使用 SQL 来编写。而 UDF 没有这个限制，可以使用支持 C 语言调用约定的任何编程语言来实现。
  UDF 必须事先编译好并动态链接到服务器上。UDF 速度非常快，可以访问大量操作系统的功能，还可以使用大量库函数。
  注意点：

  • UDF 无法读写数据表
  • MySQL 版本升级的时候很可能需要重新编译这些 UDF，或者甚至修改 UDF
  • 需要确保 UDF 是线程安全的，因为它们需要在 MySQL 中执行，而 MySQL 是一个纯粹的多线程环境

UDF 最大的仓库是 http://www.mysqludf.org 。

7.8 插件

7.9 字符集和校对

字符集：一种从二进制编码到某类字符符号的映射。
校对：一组用于某个字符集的排序规则。

7.9.1 MySQL 如何使用字符集

创建对象时的默认设置

• 创建数据库时，根据服务器上的 character_set_server 设置来设定该数据库的默认字符集
• 创建表时，根据数据库的字符集设置来指定这个表的字符集设置

• 创建列时，根据表的设置指定列的字符集设置

  服务器和客户端通信时的设置

  当服务器和客户端通信时，它们可能使用不同的字符集。这时，服务器端将进行必要的翻译转换工作：

• 服务器端总是假设客户端按 character_set_client 设置的字符来传输数据和 SQL 语句

• 当服务器收到客户端的 SQL 语句时，先将其转换成字符集 character_set_connect 。它还使用这个设置来决定如何将数据转换成字符串
• 当服务器端返回数据或错误信息给客户端时，它会将其转换成 character_set_result

根据需要，可以使用 set name 或者 set character set 来改变设置。

MySQL 如何比较两个字符串的大小

如果比较的两个字符串的字符集不同，MySQL 会将其转成同一个字符集再进行比较。可以通过函数 CONVERT( ) 显示地将其中一个字符串的字符集转成一个兼容的字符集。
还可以使用前缀和 COLLATE 子句来指定字符串的字符集或校对字符集。

一些特殊情况

诡异的 character_set_database 设置
character_set_database 设置的默认值和默认数据库的设置相同。当改变默认数据库的时候，这个变量也会跟着变。

LOAD DATA INFINE
当使用 LOAD DATA INFINE 的时候，MySQL 总是将文件中的字符按照字符集 character_set_database 来解析。

SELECT INTO OUTFINE
MySQL 会将 SELECT INTO OUTFILE 的结果不做任何转码地写入文件。

嵌入式转义序列
MySQL 会根据 character_set_client 的设置来解析转移序列，即使是字符串中包含前缀或 COLLATE 子句。这是因为解析器在处理字符串中的转义字符时，完全不关心校对规则——对解析器来说，前缀并不是一个指令（它只是一个关键字）。

7.9.2 选择字符集和校对规则

MySQL 支持 UTF-8 的一个三字节集在内的多种字符集和校对规则。可以通过命令 SHOW CHARACTER 和 SHOW COLLATION 来查看 MySQL 支持的字符集和校对规则。
正确的设置字符集与校对规则的方法是：先为服务器选择一个合理的字符集，然后根据不同的实际情况让某些列选择合适的字符集和校对规则。
对于校对规则通常需要考虑一个问题：是否以大小写敏感的方式比较字符串，或者是以字符串编码的二进制值来比较大小。它们分别对应的校对规则的前缀分别是 _cs、_ci 和 _bin。
MySQL 如何选择字符集和校对规则，如表7-2：

例：

7.9.3 字符集和校对规则如何影响查询

不同的字符集和校对规则之间的转换可能会带来额外的系统开销。
只有排序查询要求的字符集与服务器数据的字符集相同时，才能使用索引进行排序。如果希望使用别的校对规则进行排序，那么 MySQL 需要使用文件排序（Using filesort）。
UTF-8 存储一个字符会使用变长的字节数（1-3个字节），在 MySQL 中通常使用定长的空间来存储字符串（，以保证缓存中有足够的空间来存储字符串）。（例：UTF-8 的 CHAR(10) 需要30个字节，变长的字段类型 VARCHAR 在存储时不回有这个困扰，但当它在临时表中用来处理或排序时，也总是会分配最大可能的长度)
在 MySQL 中有两个函数 LENGTH() 和 CHAR_LENGTH() 来计算字符串长度。如果使用多字节字符集则两函数返回结果不同，此时确保在统计字符集的时候使用后者。
如果要索引一个UTF-8字符集的列，MySQL 会假设每一个字符都是三个字节，所以最长索引前缀的限制一下缩短到原来的三分之一。

7.10 全文索引

用Elasticsearch。

7.11 分布式(XA)事务

分布式事务让存储引擎级别的 ACID 可以扩展到数据库层面，甚至多个数据库之间——这需要通过两阶段提交实现。MySQL 5.0 及后续版本开始支持 XA 事务了。
XA 事务的事务协调器：

• 阶段一：保证所有的事务参与者都完成了准备工作
• 阶段二：协调器收到所有参与者都准备好的消息，就会告诉所有的事务可以提交了

MySQL 在这个 XA 事务中扮演一个参与者的角色，而不是协调者。
MySQL 两种 XA 事务：

• MySQL 参与到外部的分布式事务
• 通过 XA 事务来协调存储引擎和二进制日志

事务必然带来性能的下降。

7.12 查询缓存

MySQL 支持缓存完整的 SELECT 查询结果，也就是“查询缓存”。
查询缓存：当查询命中缓存，MySQL 会立刻返回结果，跳过解析、优化和执行阶段。
查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这个表相关的所有缓存都将失效。看起来低效，但这种简单实现代价很小。但其缺点是，可能会影响一个服务器性能（它可能称为服务器的资源竞争单点），因此可以配置一个较小的查询缓存空间。

7.12.1 MySQL 如何判断缓存命中

MySQL 判断缓存命中方法：缓存放在一个引用中，通过一个哈希值引用，这个哈希值包括了如下因素：查询本身、当前要查的数据库、客户端协议版本等。