第07章-MySQL高级特性

7.1 分区表

一个表最多只有1024个分区

分区表达式必须是整数，或者返回整数的形式

分区字段中有主键或唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

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

7.1.1 分区表的原理

7.1.2 分区表的类型

MySQL支持多种分区表。我们看到最多的是根据范围进行分区：

1
2
3
4
5
6
7
8

create table sales(
	order_date datetime not null,
) engine=innodb partition by range(year(order_date))(
	parition p_2010 values less than (2010),
	parition p_2010 values less than (2011),
	parition p_2010 values less than (2012),
	parition p_catchall values less than maxvalue
);

MySQL还支持键值、哈希和列表分区

7.1.3 如何使用分区表

两个策略：

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

7.1.4 什么情况下会出问题

  • NULL值会使分区过滤无效
    • 第一个分区是一个特殊分区，存放分区计算为NULL的值
  • 分区列和索引列不匹配
    • 如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤
  • 选择分区的成本可能很高
    • 可通过限制分区数量来缓解此问题
    • 键分区和哈希分区没有这样的问题
  • 打开并锁住所有底层表的成本可能很高
    • 当查询访问分区表的时候，MySQL需要打开并锁住所有底层表，这是分区表的另一个开销。这个操作在分区过滤之前发生，所以无法通过分区过滤降低此开销，并且此开销也和分区类型无关，会影响所有的查询。
  • 维护分区的成本可能很高

7.1.5 查询优化

分区的最大优点就是优化器可以根据分区函数来过滤一些分区

对于访问分区表来说，很重要的一点是要在where条件中带入分区列

7.1.6 合并表

7.2 视图

视图本身是一个虚拟表，不存放任何数据

不能对视图创建触发器，不能使用drop table命令删除视图

视图的两种实现：

  • 合并算法
  • 临时表算法

如果视图中包含group by、distinct、任何聚合函数、union、子查询等，只要无法在原表记录和视图记录之中建立一一映射的场景，MySQL都将使用临时表算法来实现视图。具体的可以使用explain，查看select_type是不是derived来确定。

7.2.1 可更新视图

所有使用临时表算法实现的视图都无法被更新

7.2.2 视图对性能的影响

在某些情况下视图可以帮助提升性能：

  • 可以在视图中实现基于列的权限控制，却不需要真正的在系统中创建权限列
  • 使用临时视图实现一些功能，之后删除

如果打算使用视图来提升性能，需要做比较详细的测试

7.2.3 视图的限制

  • 不支持物化视图
  • 不支持创建索引
  • 不会保存视图定义的原始语句

7.3 外键约束

使用外键是有成本的，比如外键通常要求每次修改数据时都要在另外一张表中多执行一次查找操作，虽然InnoDB强制外键使用索引，但还是无法消除这种约束检查的开销。

外键约束需要访问一些额外的表，这也意味着需要额外的锁，甚至会导致一些死锁

有时可以使用触发器来代替外键，对于相关数据的更新外键更合适，但是如果外键只是用作数值约束，那么触发器或者显示的限制取值会更好些。

如果只是使用外键做约束，那通常在应用程序里实现该约束会更好些。

7.4 在MySQL内部存储代码

7.4.1 存储过程和函数

7.4.2 触发器

7.4.3 事件

类似于linux的定时任务

如果一个事件很长，可能出现上一个未完成，下一个又开始

7.4.4 在存储程序中保留注释

7.5 游标

MySQL游标指向的对象都是存储在临时表中而不是实际查询到的数据，所以MySQL游标总是只读的。

如果关闭游标的时候你只是扫描了一个大结果集中的一小部分，那么存储过程可能开销很大，需要考虑使用limit进行限制

游标也可能产生一些额外的IO操作，临时内存表不支持BLOB和text，如果游标返回的结果包含这样的列，MySQL就必须创建临时磁盘表来存放

7.6 绑定变量

例如：insert into tbl(col1, col2) values(?, ?)

使用绑定变量可以更高效地执行大量的重复语句：

  • 在服务端只需要解析一次SQL语句
  • 服务端某些优化器的工作只需要执行一次
  • 以二进制的方式发送参数和句柄比发送原始文本效率更高
  • 仅传输参数，网络开销更小
  • 更安全

7.6.1 绑定变量的优化

7.6.2 SQL接口的绑定变量

7.6.3 绑定变量的限制

7.7 用户自定义函数

必须事先编译好并动态链接到服务器上

7.8 插件

7.9 字符集和校对

7.9.1 MySQL如何使用字符集

7.9.2 选择字符集和校对规则

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

7.10 全文索引

7.11 分布式（XA）事务

7.11.1 内部XA事务

MySQL本身的插件式架构导致其在内部需要XA事务。

MySQL中各个存储引擎是完全独立的，彼此不知道对方的存在，所以一个跨存储引擎的事务需要一个外部的协调者。

如果将MySQL记录的二进制日志操作看做一个独立的“存储引擎”，就不难理解为什么即使是一个存储引擎参与的事务仍然需要XA事务。在存储引擎提交的同时，需要将“提交”的信息写入二进制日志，这就是一个分布式事务，只不过二进制日志的参与者是MySQL本身。

XA事务为MySQL带来了巨大的性能下降

一个事务如果开启了二进制日志，则不仅需要对二进制日志进行持久化操作，InnoDB事务日志还需要两次日志持久化操作。

MySQL复制需要二进制日志和XA事务的支持

7.11.2 外部XA事务

7.12 查询缓存

MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，MySQL会立即返回结果，跳过了解析、优化和执行阶段。

查询缓存会跟踪查询涉及的每个表，如果这些表发生变化，则和这个表相关的查询缓存全部失效。这种机制看起来比较低效，因为数据变化时可能对应的查询结果并没有变更，但这种简单实现代价很小。

查询缓存是一个影响服务器扩展性的因素，它可能成为整个服务器的资源竞争点，在多核服务器上还可能导致服务器僵死。很多时候我们还是应该默认关闭查询缓存，如果查询缓存的作用很大的话，就配置一个很小的查询缓存空间（几十兆）

7.12.1 MySQL如何判断缓存命中

通过一个哈希值引用，这个哈希值包括如下因素：查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能会影响返回结果的信息。

即便存在一个空格的区别，也会被认为是两次不一样的查询。

当查询语句中存在一些不确定的数据时，不会被缓存，如函数now()。可以转换为一个确定的日期进行查询

对InnoDB来说，当一个事务修改了某个表，那么在事务提交前，这个表的查询缓存都设置失效。因此长时间运行的事务，会大大降低查询缓存的命中率。

7.12.2 查询缓存如何使用内存

7.12.3 什么情况下查询缓存能发挥作用

命中和写入的比率，即Qcache_hits和Qcache_inserts的比值大于3:1时查询缓存通常是有效的，不过这个比率最好达到10:1。如果没能达到这个比率，可以考虑禁用查询缓存。

7.12.4 如何配置和维护查询缓存

参数：

  • query_cache_type
  • query_cache_size
  • query_cache_min_res_unit
  • query_cache_limit
  • query_cache_wlock_invalidate

减少碎片

提高查询缓存的使用率

7.12.5 InnoDB和查询缓存

事务是否可以访问查询缓存取决于当前事务ID，以及对应的数据表上是否有锁。每个InnoDB表的内存数据字典都保存了一个事务ID号，如果当前事务ID小于该事务ID，则无法访问查询缓存。

如果表上有任何锁，那么对这个表的任何查询语句都无法被缓存

7.12.6 通用查询缓存优化

  • 用多个小标代替一个大表对查询缓存有好处，使得失效策略能够在一个更合适的粒度上进行
  • 批量写入只需要做一次缓存失效，所有性能更优秀
  • 控制缓存大小，或者直接禁用
  • 写密集的应用，禁用查询缓存可能性能更优

7.12.7 查询缓存的替代方案

客户端缓存