高性能MySql学习笔记第七章：MySQL 高级特性

1. 分区表

分区表的基本介绍

• 分区表是一个独立的逻辑表，但是底层由多个物理子表组成。分区对于应用而言是完全透明的。
• MySQL 在创建表时通过PARTITION BY子句定义每个分区存放的数据。在查询数据时，优化器会根据分区定义过滤那些没有我们需要数据的分区。
• 分区的一些应用场景：
  • 表非常大无法全部加载到内存，或者只有部分数据是热点数据。
  • 分区表的数据更易维护。比如可以通过清除整个分区的方式删除大量数据。
  • 分区表的数据可以分布在不同的物理设备上，从而高效利用。
• 分区表的一些限制：
  • 一个表最多有1024个分区。
  • 如果分区字段中有主键或者唯一索引的列，那所有主键列和唯一索引列都必须成为分区字段。
  • 无法使用外键

分区表的原理

• 从存储引擎的角度，分区表的底层表和普通表没有任何不同。
• 对于SELECT、INSERT和DELETE语句，分区层先打开并锁住所有的底层表，找到数据对应的分区表后，再进行相应操作。
• 对于UPDATE语句，分区层先打开并锁住所有的底层表，先确定需要更新的记录在哪个分区，取出数据并更新，再判断更新后的数据在哪个分区。最后对底层表进行写入，并对原数据所在的底层表进行删除。

分区表的类型

• 分区表的类型一般有按范围分区、键值、哈希和列表分区。
• 分区的依据可以是列，也可以是包含列的表达式，包含列的表达式中可以使用各种函数，但是要求表达式返回的值要是一个确定的整数，且不能是常数。如下为按年份建立一个分区表：

CREATE TABLE tbl (
    date DATETIME NOT NULL,
    -- Other columns
)  ENGINE=InnoDB PARTITION BY RANGE (YEAR(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,
);

如何使用分区表

一般有两种策略使用分区表：

• 全量扫描数据，不需要任何索引。使用简单的分区方式存放表，不需要任何索引，根据分区的规则大致定位需要的数据位置。
• 分离热点，索引数据。将热点数据单独放置到一个分区，并在这个分区内通过索引访问。

分区表的问题

• NULL 值使分区过滤无效
  • 第一个分区是特殊分区。假设按照PARTITION BY RANGE (YEAR(date)) 分区，当 data 为 NULL 或是一个非法值时，记录都会被记录到第一个分区。
  • 如果第一个分区特别大，当使用"全量扫描数据，不需要任何索引"策略时，代价会非常大。
  • 一种优化方式是使用列本身进行分区而不是基于列的函数进行分区：PARTITION BY RANGE COLUMNS(date) 。然后保证列值不为 NULL 即可。
• 分区列和索引列不匹配
  • 假设列 a 上定义了索引，而在列 b 上进行分区。因为每个分区都有其独立的索引，所以扫描a列的索引就需要扫描每个分区内对应的索引。应该尽量避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。
• 选择分区的成本可能很高
  • 对于范围查询而言，服务器需要扫描所有分区定义的列表来确定某一个查询属于哪一个分区。随着分区数的增长，这个成本会越来越高。根据实践经验，100个左右的分区是没有问题的。
• 打开并锁住所有底层表的成本可能很高
  • 当查询访问分区表时，MySQL 需要打开并锁住所有的底层表，这是分区表的另一个开销。这一点对一些本身操作很快的查询，比如根据主键查找单行，会带来明显的额外开销。

查询优化

• 访问分区表一定要在WHERE条件中带入分区列。可以使用EXPLAIN PARTITION观察优化器是否执行了分区过滤。
• MySQL 优化器能够将范围条件转换为离散的值列表，并根据列表中的每个值过滤分区。
• MySQL 只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即便这个表达式就是分区函数也不行。比如上文中的表SELECT * FROM tbl WHERE YEAR(date) = 2011;将无法过滤分区，而使用SELECT * FROM tbl WHERE date BETWEEN '2011-01-01' AND '2011-12-31';则可以。

合并表

• 合并表相当于一个容器，里面包含了多个真实表，是一种将被淘汰的技术。

2. 视图

• 视图本身是一个虚拟表，不存放任何数据。使用 SQL 语句访问视图时，其返回的数据是 MySQL 从其他表中生成的。
• MySQL 会通过两种算法处理视图，分布是合并算法(MERGE)和临时表算法(TEMPTABLE)。如果是临时表算法实现的视图，EXPLAIN 中会显示为派生表(DERIVED)。
• 视图的实现算法是视图本身的属性，和作用在视图上的查询语句无关。
• 可更新视图是值可以通过更新这个视图来更新涉及的相关表。使用临时表算法的视图都无法被更新。
• MySQL 不支持物化视图，也不支持在视图上创建索引。

3. 外键约束

• InnoDB 是目前 MySQL 中唯一支持外键的内置存储引擎。
• 如果只是使用外键做约束，通常在应用程序中实现该约束会更好。使用外键会带来很大的额外消耗。

4. 在 MySQL 内部存储代码

• 存储代码是一种帮助应用程序隐藏复杂性，使得应用开发更简单的方法。不过它的性能可能更低。
• 存储代码包括存储过程和函数、触发器、事件。
• MySQL 中，对于每一个表的每一个事件，最多定义一个触发器。MySQL 只支持"基于行的触发"。也就是触发器始终是针对一条记录，而非整个 SQL 语句。所以如果变更的数据集很大，效率会比较低。这使得经常无法使用触发器维护汇总表和缓存表。InnoDB 中触发器是在同一个事务中完成的。
• 事件可以指定 MySQL 在某个时间，或者每间隔一个时间执行一段 SQL 代码。如果一个定时时间需要执行很长时间，还未完成时，下一个时间点的事件又开始了。MySQL 本身是不会防止这种并发的。

5. 游标

• MySQL 在服务器端提供只读的、单向的游标，只能在存储过程中或者更底层的客户端 API 中使用。
• 当打开游标时，是需要执行整个查询的。如果游标所使用的数据量不大，应当考虑添加LIMIT限制返回结果集。

6. 绑定变量

• 绑定变量的基本含义

  • 当创建一个绑定变量SQL时，客户端向服务器发送了一个SQL语句的原型。服务器端收到这个SQL语句的框架后，解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄。以后每次执行这类查询，客户端都指定使用这个句柄。
  • 绑定变量的SQL，使用问号标记可以接收参数的位置，当真正需要执行具体查询的时候，啧使用具体值代替这些问号。例如：INSERT INTO tbl(col1, col2, col3) VALUES(?, ?, ?)；。可以通过向服务器端发送各个问号的取值和这个SQL的句柄来反复执行一个具体的查询，这是绑定变量的具体优势所在。

• MySQL绑定变量可以高效地执行大量的重复语句，主要因为以下几个原因：

  • 服务端只需要解析一次SQL。
  • 服务器端的某些优化器工作只需要执行一次，因为可以缓存部分执行计划。
  • 仅将参数，而不是整个SQL语句发送到服务端，网络开销会更小。
  • MySQL存储参数时，直接将其存放到缓存中，而不需要在内存中多次复制。

• 绑定变量相对也更加安全，因为无需在应用程序中处理转义，大大减小了SQL注入的风险。

• MySQL支持SQL接口的绑定变量，使用PREPARE关键字可以创建一个绑定变量的SQL语句，这个过程也被称作预编译。

• 绑定变量的限制：

  • 绑定变量是会话级别的，所以连接之间不能共用绑定变量句柄
  • 如果只是执行一次SQL，那绑定变量比直接执行多了一次额外的准备阶段消耗和一次网络开销。
  • 如果总是忘记释放绑定变量资源，则服务器端可能发生资源泄露。
  • 有些操作，比如BEGIN，无法在绑定变量中完成。

• 绑定变量的三种类型：

  • 客户端模拟的绑定变量。客户端的SDK接受一个带参数的SQL，再将指定的值代入其中，最终将完整SQL发送到服务端。这种方式不涉及到MySQL服务端的绑定变量。
  • 服务器端的绑定变量。客户端使用特殊的二进制协议将带参数的字符串发送到服务器端，然后使用二进制协议将具体参数发送给服务器端并执行。
  • SQL接口的绑定变量。客户端先发送使用PREPARE的SQL语句设置参数，最后使用EXECUTE携带参数执行。所有这些都使用普通的文本传输协议。

7. 用户自定义函数

• 用户自定义函数（UDF）必须事先编译好并动态链接到服务器上。

8. 字符集和校对

• MySQL的设置可以分为两类：创建对象时的默认值、在服务器和客户端通信时的设置。
• 创建对象时的默认值
  • MySQL服务器有默认的字符集和校验规则，数据库和每个数据表也有自己的默认值。
  • 真正存放数据的是列，只有当创建列而没有为列指定字符集的时候，表的默认字符集才有作用。
• 如果比较的两个字符串的字符集不同，MySQL会先将其转成同一个字符集再进行比较。如果两个字符集不兼容的话，则会抛出错误。
• 只有排序查询要求的字符集和服务器数据的字符集相同的时候，才能使用索引进行排序。
• UTF-8是一种变长的字符编码，会使用1-3个字节存储一个字符。再MySQL内部，通常使用一个定长的空间来存储字符串。这样做的目的是希望保证缓存中有足够的空间来存储字符串。所以一个UTF-8编码的VARCHAR(10)需要30个字节。

9. 全文索引

• 全文索引作用对象是一个“全文集合”。具体的，对应某一条记录，MySQL会将需要索引的列全部拼接成一个字符串，然后进行索引。所以全文索引并不会存储关键字具体存储在哪一列。
• 在使用全文索引进行查询的时候，会自动按相似度进行排序。在使用全文索引进行排序的时候，MySQL无法再使用普通索引排序。
• 在MATCH()函数中指定的列必须和在全文索引中指定的列完全相同，否则无法使用全文索引。
• 布尔全文索引通过停用词列表过滤掉“噪音”词。布尔搜索查询还可以通过一些前缀修饰符定制搜索。
• 全文索引的限制：
  • MySQL全文索引只会使用词频判断相关性，不会考虑索引词的位置。
  • MySQL全文索引只有全部在内存时，性能才非常好。
  • 如果查询中使用了MATCH AGAINST子句，而对应列上又有全文索引，那MySQL就一定会使用这个全文索引，而不会使用其他任何索引。

10. 分布式(XA)事务

• 分布式事务让存储引擎级别的ACID 可以拓展到数据库层面，甚至拓展到多个数据库之间 —— 这需要通过两阶段提交实现。
• MySQL的存储引擎是完全独立的，所以一个跨存储引擎的事务，需要使用XA协议。这属于MySQL内部的XA事务。　　
• 在存储引擎提交的同时，需要将“提交”的信息写入BinLog，也是一个分布式事务，不过参与者是MySQL本身。

11. 查询缓存

如何判断缓存命中

• MySQL通过一个哈希值指向缓存。这个哈希值包括了查询本身、当前要查询的数据库、客户端的协议版本等因素。MySQL直接使用客户端发送的原始信息判断缓存命中，故而任何字符上的不同，比如空格、注释，都会导致缓存的不命中。
• 当查询语句中有一些不确定的数据时，则不会被缓存。比如包含函数NOW()或者CURRENT_DATE()的查询不会被缓存。
• 当向某个表写入数据时，MySQL会将该表所有的缓存都设置失效。

查询缓存如何使用内存

• MySQL会预先申请一大块内存，用于查询缓存，并进行管理。管理过程中有可能出现碎片。

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

• 如果服务器上有大量缓存未命中，但是实际上大多数缓存都被缓存了，可能是因为如下原因：
  • 查询缓存还没有完成预热。
  • 查询语句总是没有执行过。
  • 缓存失效太多了。缓存碎片、内存不足、数据修改都会造成缓存失效。

如何配置和维护查询缓存

• 查询缓存的参数：

  • query_cache_type: 是否打开缓存。可设置为ON、OFF或DEMAND。DEMAND表适只有在语句中明确写明SQL_CACHE才会放入查询缓存。
  • query_cache_size：查询缓存使用的总内存空间，单位字节，必须是1024的整数倍。
  • query_cache_min_res_unit: 查询缓存分配内存块时的最小单位。
  • query_cache_limit: MySQL能够缓存的最大查询结果。
  • query_cache_work_invalidate: 如果数据表被其他链接锁住，是否仍要从查询缓存中返回结果，默认为OFF。

• 减少碎片

  • 选择合适的query_cache_min_res_unit可以帮忙减少由碎片导致的内存空间浪费。设置合适的值可以平衡每个数据块的大小和每次存储结果时内存块申请的次数。
  • 命令FLUSH QUERY CACHE可以完成碎片整理，但是该命令不会情况缓存。命令RESET QUERY CACHE可以清空缓存。

• 提高查询缓存命中率
  

InnoDB 和查询缓存

• 如果当前事务的事务ID小于数据的事务ID（联系MVCC），那这个事务无法访问查询缓存，也无法写入缓存。
• 如果表上有任何锁，那对这个表的任何查询语句都是无法被缓存的。

通用查询缓存优化

• 用多个小表替代一个大表对查询缓存有好处。这个设计使得失效策略能够在一个更合适的粒度进行。
• 批量写入时只需要做一次缓存失效，所以相比单条写入会更好。
• 如果缓存空间太大，则过期操作时有可能导致服务器僵死。可以减小缓存空间的大小或者直接禁用缓存。
• 无法在数据库级别或者表级别控制查询缓存，可以通过SQL_CACHE和SQL_NO_CACHE语句控制某个查询是否需要进行缓存。也可以通过修改会话级别的变量query_cache_type控制查询缓存。
• 对于写密集型应用而言，直接禁用查询缓存可能性能更好。
• 如果想要大多数查询不走查询缓存，但是少数查询走查询缓存。可以将query_cache_type设置为DEMAND，然后在希望缓存的查询中添加SQL_CACHE命令。反之，可以使用关键字SQL_NO_CACHE