高性能MySQL第七章：MySQL高级特性

MySQL从5.0和5.1版本开始引入了很多高级特性，包括分区、触发器等，这些新特性也许不会频繁用到，但对于某些场景下，会给我们更多DB层面优化的选择，所以，了解一下总是有益的。

一、分区表

分区表，通过在创建表时，使用partition by子句来定义每个分区存放的数据，以达到将数据按照一个比较粗的粒度分在不同的表中，这样，就可以方便的对数据进行分区处理。分区表，一般在下面场景中，可以体现其价值

• 表非常大以至于无法全部放到内存中，或者只在表的最后部分有热点数据，其他的均是历史数据
• 对分区进行独立的处理，比如清除，优化，检查，修复等
• 将数据通过分区分布到不同的物理设备上
• 对分区数据进行单独的备份和恢复

1.分区表的原理

对用户来说，分区表是一个独立的逻辑表，但是其底层是由多个物理子表组成。实现分区的代码实际是对一组底层表句柄对象的封装，对分区表的请求，都会通过句柄对象转化为存储引擎的接口调用，在存储引擎层面，分区表和普通表时没有区别的。
分区表在处理DML时，是可以通过分区特性进行分区粒度上的过滤的，其执行相关语句的逻辑如下
select查询：
当查询一个分区表的时候，分区层先打开幷锁住所有的底层表，优化器先般判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问数据
insert操作
当写入一条数据时，分区层先打开幷锁住所有的底层表，然后确定接收数据的分区，再将记录写入对应底层表
delete操作
删除一条记录时，分区层先打开幷锁住所有的底层表，然后确定数据的分区，在对应的底层表上进行删除操作
update操作
当更新一条记录时，分区层先打开幷锁住所有的底层表，确定需要更新的记录所在的区，到底层表取出数据幷更新，再判断更新后的数据所属的区，然后在新区进行写入操作幷删除旧的底层表数据

可以看到，在每一个操作前，分区层都会先打开幷锁住所有的底层表，但是这幷不是说分区表在处理过程中是锁全表的，如果存储引擎有自己实现的行级锁，如innodb，则在分层区会是释放对应的表锁。

2.分区表的类型

MySQL支持多种分区表，应用场景最多的还是根据范围进行分区，分区表达式可以是列，也可以是包含列的表达式，但是有一个限制是，表达式返回的值要是一个确切的整数，且不能是常数。除了按范围，MySQL还支持键值、哈希和列表分区。

3.如何使用分区表

分区表其实可以理解为索引的最初形态，通过分区，我们可以以代价非常小的方式定位到需要的数据在哪一篇区域，在这片区域中，你可以做顺产扫描，可以建索引，可以将数据都缓存到内存中，因为分区不需要精确的定位到每条数据，也就无需额外的数据结构，这样代价是非常小的，在数据量非常巨大，使用索引也无法提升性能到可接受地步时，分区表就可以派上用场了，一般使用分区表，有如下两个策略：
全量扫描数据，不要任何索引
这种方式，需要在where条件中包含分区过滤条件，将数据扫描范围限制在少数分区中

索引数据，幷分离热点
如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将这些热点数据放置到一个区中，让这个分区的数据能够有机会都缓存在内存中，这样就能够使用索引，也能有效的使用缓存

4.使用分区表需要注意的点

1. null值会使分区过滤无效
   在进行分区时，所有分区列或分区表达式为null或者是非正常值的时候，记录就会保持到第一个特殊的分区，假设你进行正常查询，且过滤条件保证要查询的数据在一个分区当中，但是MySQL仍然会扫描两个分区，因为它会去扫描第一个特殊的分区，因为虽然在分区时，分区函数返回的是null，但实际它的值在这个范围内，出于对此的考量，MySQL仍然会扫描第一个分区，如果第一个分区特别大的话，就会带来不必要的性能损耗，这时，我们可以指定第一个分区专门专门存无用的分区，如果插入的数据都是有效的，那第一个分区就是空的
2. 分区列和索引列不匹配
   如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤，假设在a上建立了索引，在列b上进行分区，因为每个分区都有其独立的索引，所以扫描列b上的索引就需要扫描每一个分区内对应的索引。要避免这个问题，应该避免建立和分区列不匹配的索引，除非查询中还包含可以过滤分区的条件
3. 选择分区的成本可能很高
   在进行实际操作时，MySQL需要确定数据术语哪个区的问题，这需要服务器扫描所有的分区定义列表来找到正确答案，随着分区数的增加，成本会越来越高，比如在按行插入大量数据时，每一行插入到分区表，都需要扫描分区列表来确定数据所属的分区。
4. 打开幷锁住所有底层表的成本可能很高
   如前面所说的，在执行dml时，都会先锁住幷打开所有的底层表，并且这发生在分区过滤前。
5. 维护分区的成本可能很高
   分区重组或者类似alter语句，在操作时，需要先创建一个临时的分区，然后将数据复制其中，最后删除原分区

5.查询优化

分区最大的优点就是可以根据条件来过滤一些分区的数据，根据粗粒度的优势，让查询扫描更少的数据。所以，对于分区表的查询来说，最重要的一点就是在where条件中带上分区列，但有时候，分区条件也会失效，我们要优化的也就是分区条件失效的情况

select * from table where year(day) = 2010

MySQL只能使用分区函数的列本身进行比较时才能过滤分区，而不能够根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行，这个和索引类似，可以进行如下优化

select * from table where day between 2010-01-01 and 2010-12-31

6.合并表

合并表和分区表相反，分区表是将一个大表拆分为多个小表，而合并表则是将多个表合并为一个表，这似乎并不能带来多大的用处，所以这个功能点可以忽略。

二、视图

视图本身是一个虚拟表，不存放任何数据，在使用SQL语句访问视图时，它返回的是从其他表中生成的数据，视图和表在同一个命名空间中。MySQL实现视图有两个方法，一种是合并算法，一种是临时表算法。

1.可更新视图

可更新视图是指可以通过更新视图来更新视图相关表。但是如果视图中包含了group by、union、聚合函数以及一些特殊情况，就不能被更新了，更新视图的查询也可以是一个关联语句，但是关联被更新的列必须来自同一张表。在定义视图时，如果使用了check option子句，那么更新的列必须包含在定义视图列以内

2. 视图对性能的影响

在重构时，可以使用视图，使得在修改视图底层结构的时候，应用代码还可以继续不报错的运行，可以使用视图进行基于列的权限控制，却不需要在真正的系统上建立权限，因此没有额外的开销。

3. 视图的限制

1. MySQL不支持物化视图，也不支持创建索引，不过可以通过创建缓存表或者汇总表的办法来模拟物化视图和索引
2. 视图不会保持定义的原始SQL语句，这样会限制你通过修改原始sql的方式重建视图，有一个办法是通过.frm文件的最后一行来获取信息，但是你必须对获取的信息进行一些字符处理工作

三、外键约束

外键约束主要用来保证关联表的数据一致性，但是使用外键会带来一些成本，比如对一个表进行修改操作时，都需要去检查关联表，而且外键也会约束扩展性，所以虽好能将外键约束在程序中进行实现。

四、在MySQL内部存储代码

MySQL允许通过触发器、存储过程、存储函数的形式来存储代码，使用存储代码能带来一些好处，但同时也需要一些牺牲。
优点：

• 代码在服务器内部执行，节省了带宽和网络延时
• 重用代码，保证某些行为的一致性
• 简化代码的维护和版本更新
• 提升安全，提供更细粒度的权限控制
• 存储过程的执行计划可以通过缓存避免反复解析

缺点

• 使用额外的处理语法，编写起来相对于SQL更复杂
• 执行效率较低，且逻辑实现复杂
• 带来部署的复杂性
• 给数据库服务器带来额外的压力
• 无法控制存储代码的资源消耗
• 难以调试

1.存储过程和函数

存储程序越小越简单越好，将复杂的逻辑交个上层应用去处理，这样代码更具有易读性，且更易维护，也会更灵活，但在重复执行一些小操作的情况下，存储过程可以节约大量的网络开销和解析成本。如插入一定数据数据到一张表中

2.触发器

触发器可以让你在执行insert，update，delete的时候执行一些特点的操作，可以知道在sql执行前或执行后触发，触发器本身没有返回，但是可以读取或改变触发SQL语句所影响的数据。触发器实现简单，同时功能也有限，所以在重度依赖触发器的情况下，需要注意以下几点

• 对每一个表的每一个事件，最多只能定义一个触发器，就是说不能在 after insert上定义两个触发器
• MySQL只支持基于行的触发，也就是说，触发器始终是针对一条记录的，而不是针对整个SQL语句的。
• 触发器可以掩盖服务器背后的工作，一个简单的SQL，因为触发器，可能包含许多看不见的工作
• 触发器问题难以排查
• 触发器可能导致死锁和锁等待

在触发器的使用中，对性能影响最大的就是其只基于行的触发

3.事件

事件类似于定时任务，你可以通过事件，指定MySQL在某一段时间执行一段SQL代码或每隔一个时间间隔。通常将SQL封装到一个存储过程中，然后由事件使用call函数来调用

4.在存储程序中保留注释

存储过程、存储函数、触发器、事件通常包含大量的重要代码，在代码中加上注释是有必要性的，但是MySQL客户端会自动过滤掉注释，一个可用的技巧就是使用版本相关的注释，为了使版本相关的代码不被执行，可用指定一个非常大的版本号。

五、游标

MySQL在服务器端提供只读的、单向的游标，因为MySQL游标中指向的对象都是存储在临时表中，而不是实际查询到的数据，所有MySQL游标总是只读的。

六、绑定变量

当创建一个绑定变量的SQL时，客户端向服务器发送一个sql语句的原型，服务器端收到这个SQL语句框架后解析幷存储这个语句的执行计划，返回个客户端处理句柄，之后每次执行这类查询，客户端指定使用这个句柄。使用绑定变量有如下好处

• 服务器端只需要解析一次SQL语句
• 在服务器端某些优化器的工作只需要执行一次，因为它会缓存一部分的执行计划
• 节省每次传输整个SQL的网络开销
• 减少SQL中注入和攻击的危险

七、插件

存储过程只能使用SQL来编写，而UDF没有这个限制，你可以使用支持C语言调用约定变得任何编程语言来实现

八、字符集

字符集是一种字节到字符之间的映射，而校对规则是指一个字符集的排序方法。

九、.全文索引

全文索引用于满足基于相似度查询，通过关键字来进行查询过滤的场景

十、分布式事务

存储引擎的事务特性用来保证在存储引擎实现ACID，而分布式事务则让存储引擎级别的ACID可以扩展到数据库层面，甚至是扩展到多个数据库之间。

十一、查询缓存

完全相同的查询在重复执行的时候，查询缓存可以立即返回结果，而无需执行整个查询流程，查询缓存可以减少很多重复查询的开销，但是查询保存在内存中，在使用查询缓存时，也需要考虑到查询缓存失效，内存碎片等带来的性能影响。