MySQL从5.0和5.1开始引入了很多高级特性，分区、触发器等。除了分区之外大部分功能其实用到的场景都不是很多，从MySQL层面上使用这些高级特性说不定在后期的使用上会大大增加升级重构的成本，实际中这些高级特性的任务都在应用端层面完成了，这里这些高级特性能够做相关的了解。

7.1 分区表

分区的主要目的是将数据按照一个较粗粒度、简易的分布在不同的表中。实现方式是对底层表的封装，意味着索引也是按照分区的字表定义的，没有全局的索引。在表创建时使用PARTITION BY子句定义各个分区存放数据。

分区表的主要应用场景如下：

• 没有合适的索引。
• 表非常大无法全部放到内存，或者只在表最后有热点数据，其他全是历史数据。
• 批量删除大量数据。
• 分区表数据可以分布在不同的物理设备上。
• 可以避免某些索引互斥访问和锁竞争等。
• 独立的备份和恢复分区，在非常大的数据量场景下效果好。

7.1.1 分区表原理

分区表由多个底层表实现的，存储引擎管理分区的各个底层表和管理普通表一样，各个底层表使用的也是相同的存储引擎，各个底层表上的索引也是完全相同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

• SELECT查询
  分区别先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，然后再调用对应存储引擎的接口访问各个分区的数据。
• INSERT操作
  同上，先锁住，确定哪个分区接收这个数据，再将记录写入底层表。
• DELETE操作
  同上，先锁住，确定分区，进行删除。
• UPDATE操作
  同上先锁住所有底层表，找到需要更新的数据的分区，取出更新，判断新数据放在哪个分区，完成写入操作，然后进行原数据的删除。

大部分操作时支持过滤的，确定了操作分区后无须对其他分区进行操作。虽然操作全都是“先打开并锁住所有底层表”，但如果存储引擎能够自己实现行级锁，则会在分区层释放对应的表锁，这个加锁解锁过程和普通的InnoDB上的查询类似。

7.1.2 分区表类型

常见的分区类型：

• 根据键值分区，减少互斥量竞争。
• 使用数学模函数进行分区，数据轮询放入不同的分区，例如对日期做模7的运算。

7.1.3 如果使用分区表

当数据量超大的时候，B-Tree索引就无法起作用了。除非是索引覆盖扫描，否则数据库需要根据索引扫描结果回表，这将会产生大量随机I/O，另外这样索引的维护代价也会非常高。

这种情况下就应该完全放弃B-Tree索引，选择一些更加粗粒度的方式检索，这也是分区要做的，以小代价定位数据在那一块区域，选定区域后，区域内可以建索引，可以顺序扫描，数据可以缓存到内存，只需要一个简单的表达式可以表达每个分区存放的是什么数据。

为了保证大数据量的可扩展性，一般有以下的策略：

• 全量扫描数据，不需要索引
  使用简单的分区方式存放表，不用任何索引，根据分区的规则简单运算大致定位需要的数据位置，将数据限制在少数分区中。
• 索引数据，分离热点
  将热点数据单独放在一个分区里，让这个分区数据有机会缓存在内存中，这样很小的分区表能使用索引也能使用缓存。

7.1.4 分区表的坑

• NULL值会使分区过滤失效
  如果按照某个列值进行分区时，那么为NULL值或者是非法值的数据将会被存到一个它们不应该出现的分区中。可以创建一个“无用”分区或者直接使用列本身而不是列的函数进行分区。
• 分区列和索引列不匹配
  定义索引的列和分区的列不匹配会导致无法进行分区的过滤。通过索引条件进行扫描时会扫描每一个分区内的索引。
• 选择分区的成本可能会很高
  实现方式有多种，性能各不相同，随着分区数量的增加，成本会越来越高。（键分区和哈希分区就没有这样的问题）
• 打开并锁住所有底层表的成本很高
  当查询分区表时MySQL需要打开并锁住所有的底层表，这个操作发生在分区过滤之前，这个开销无法通过分区过滤和分区类型来改善，对一些本身操作就非常快的操作会带来明显的额外开销。
• 维护分区的成本可能很高
  新增和删除很快，但是例如重组分区或者ALTER会创建一个临时分区，复制然后删除旧分区。
• 分区必须使用相同的存储引擎。
• 某些引擎不支持分区表。
• 在MySQL 5.5分区表才开始趋于稳定。

7.1.5 查询优化

分区优化最大的优点就是优化器可以过滤一些分区，当然这是需要在WHERE条件中带入分区列的。

EXPLAIN PARTITIONS可以观察优化器是否执行了分区过滤。

MySQL只能根据使用分区的列本身才能过滤分区，不能根据表达式的值来过滤分区（和查询中必须使用独立的列才能使用索引是一样的道理）。

7.1.6 合并表

合并表是早期的、简单的分区实现，是一种将要被淘汰的技术，分区表和优化器的紧密结合才是未来的趋势。合并表这里不做过多记录。

7.2 视图

MySQL 5.0开始引入了视图，本身是一个虚拟表，数据由MySQL其他表中生成的，大多数地方MySQL对于视图和表是同等对待的。对于好几个表的复杂查询，使用视图有时候会大大简化问题。

最简单的使用方法是将SELECT语句的结果放到临时表中，当需要访问视图时直接访问这个临时表就可以了。这种情况也有两种处理视图的方算法，一种是合并算法，就视图的定义SQL直接包含进查询的SQL中（SELECT指定的字段）；另一种是临时表算法，先查出结果定一个临时表，再从临时表中选择特定的列。主要都采用合并的算法。

视图的性能很难预测，需要详细的测试。

可更新视图是指可以通过更新视图来更新视图涉及的相关表。如果视图中包含GROUP BY、UNION、聚合函数等就不能被更新。

不支持物化视图，不支持视图中创建索引，

7.3 外键约束

外键可以看做是一个确保系统完整性的额外的特性，但是会带来额外的复杂性和额外的索引消耗，一般人都会选择在应用中实现外键功能。

InnoDB是MySQL最后那个唯一支持外键的内置存储引擎，InnoDB强制外键使用索引。

外键的好处：如果要确保两个相关表始终有一直的数据，使用外键比在应用程序中检查性能要高得多。不足：外键约束是的查询时需要额外访问一些别的表，意味着其他表也要进行锁等待。

触发器可以代替外键，如果只是进行数值约束，触发器或者应用内实现该约束会更好

外键会带来很大的额外消耗，使用前要仔细做好基准测试

7.4 在MySQL内部存储代码

MySQL允许通过触发器、存储过程、函数的形式来存储代码。存储代码帮助应用隐藏复杂性，使得应用开发更简单，不过性能可能更低，存在潜在的风险。

MySQL允许内部存储代码的形式：

• 存储过程和函数
• 触发器
• 事件

7.5 游标

MySQL在服务器端提供只读的、单向的游标，游标中指向的对象都是存储在临时表中。游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制，尽管能遍历结果中的所有行，但他一次只指向一行，主要作用就是对查询数据库所返回的记录进行遍历，以便进行相应的操作。

7.6 绑定变量

绑定变量最大的用处在于创建一个绑定变量的SQL，客户端向服务器发送了一个SQL语句原型，服务器端收到这个SQL语句框架后解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄。客户端可以通过像服务端发送各个参数取值和SQL语句句柄来执行一个具体的查询。

这样服务器只需解析一个SQL语句，执行计划会被缓存，发送的句柄和参数可以使用二进制传输，不用发送整个查询语句，效率更高。绑定变量相对更加安全，减少SQL注入。

服务器端的绑定变量是使用二进制协议将具体的参数值发给服务器端并执行，客户端模拟的绑定变量是客户端驱动程序接收一个带参数的SQL再将指定的值带入其中，最后将完整的查询发送到服务器端。

7.7 用户自定义函数

User-define function，UDF是一种对MySQL扩展的途径，其用法与内置函数相同。

减少数据在数据库和应用服务器上的传输，提高数据处理的效率，在一些简单的操作上优势大。

MySQL版本升级时要特别注意UDF，有时需要重新编译UDF或者修改。

7.8 插件

MySQL支持各种各样的插件，使得无须修改源代码就可以大大扩展相关功能。

• 存储过程插件
• 后台插件
• 全文解析插件
• 审计插件
• 认证插件

7.9 字符集和校对

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

MySQL在比较两个字符串大小时会将它们都转成同一个字符集再比较，MySQL 5.0之后支持这样的隐式转换。

7.10 全文索引

全文索引的使用场景是通过关键字的匹配来进行查询过滤，基于相似度的查询，而不是原来的精确的数值比较。是四大索引中的一种（普通索引、唯一索引、主键索引、全文索引）。

7.10.1 自然语言的全文索引

自然语言上采用的是相关度来度量，基于匹配关键词的个数，以及关键词在文档中出现的次数，在整个索引中出现次数越少的词语，匹配时的相关度越高，例如一个词语在一大半的记录中都出现了，那么自然语言将不会搜索这类词语。

SQL中由MATCH()来完成，函数MATCH()将返回关键字匹配的相关度，在MATCH()中指定的列必须在和全问索引中指定的列完全相同。

7.10.2 布尔全文索引

布尔搜索中可以指定查询中自定义某个词语的相关性，并且可以通过一些前缀修饰符定制搜索，此处不过多详细描述。

查询时会先把短语拆分成单个词语查询，然后取出这些记录进行精确的匹配短语，比LIKE操作快很多（在关键词不太常见的情况下）。

7.10.3 全文索引的限制

关键字和文本的相关性判断只考虑了词频，词在字符串中的位置也就无法用在相关性的计算上。

内存如果不能装载全部全文索引会导致搜索的速度很慢。全文索引会有很多的碎片，需要做OPTIMIZE TABLE操作。

全文索引只能做全文搜索匹配，任何其他操作，比如WHERE条件比较都只能在完成全问搜索返回记录后进行。

全文索引不存储索引列的实际值，也就是没有索引覆盖扫描。

全文索引除了相关性排序外，如果还需要其他排序操作需要进行文件排序。

在使用全文索引时通常会产生大量随机I/O，再加上GROUP BY操作等会使用到临时表和文件排序，使用全文索引有时也会使得其他列的普通索引无法使用，在日常使用中多加注意。

7.10.4 全文索引的配置和优化

保证索引缓存足够大，是的全文索引都能够缓存在内存中。停用表需要经常更行，对一些高频词语及时更新进停用表中。忽略一些太短的词语，设置索引单词的最小长度。

7.11 分布式（XA）事务

略

7.12 查询缓存

MySQL有两类缓存，一是缓存查询计划，相同类型的SQL就可以跳过解析和执行计划生成阶段，二是查询结果的缓存，完整的SELECT查询结果将得到缓存。

很多时候还是推荐默认关闭查询缓存，后续将详细展开。

7.12.1 MySQL如果判断缓存命中

缓存存放在一个引用表中，通过一个哈希值引用，所以任何的字符上的不同，空格、注释之类的，都会导致缓存不命中，因此使用缓存必须在SQL上保持统一的编码习惯。

查询语句包含不确定因素时不会被缓存，例如函数NOW()等，这类查询不会被缓存，除此之外还有用户自定义函数、存储函数、用户变量、临时表、MySQL中的系统表、任何包含列级别权限的表，都不会被缓存。

打开查询缓存后对数据库的读写操作都会带来额外的消耗，读开始之前就会检查是否命中缓存，读完若没有这个查询会将结果存入缓存，写操作必须将对应表的所有缓存都设置失效。

7.12.2 查询缓存如果使用内存

查询结果完全存储在内存中。MySQL用于查询缓存的内存被分成一个个的数据块，大小时变长的，每个数据块存储了自己的类型、大小和存储的数据，外加一个指向前一个后一个数据块的指针。

7.12.3 查询缓存发挥作用

缓存是否对系统带来了更好的性能这个主要考察缓存带来的资源节约和其本身资源消耗的关系。

可参考的数据有缓存命中率，就是查询缓存返回结果占总查询的比例；INSERT/SELECT比例，查多改少较好。还有一个参考是“命中/写入”，当这个比例大于3:1时，最好10:1时，一般推荐以这个比例为准，到达这样的比例才能使得缓存的性能提升大于资源消耗。

7.12.4 通用的查询缓存优化

• 多个小表代替一个大表
• 批量写入只需要做一次缓存失效
• 缓存空间过大会在过期操作时导致服务器僵死，可以对缓存空间大小稍作控制
• 写密集型应用应该禁用查询缓存
• 可以针对某些查询设置走查询缓存

Ref

BaronSchwartz, PeterZaitsev, VadimTkachenko, et al. 高性能MySQL[M]. 电子工业出版社, 2013.