Hexo
0%

传说中的MySQL 5.6

Posted on Edited on In

从哪说起，先从MySQL的官方文档说起。

当MySQL 5.5在全世界范围内广泛部署与使用之后，架构师们都在翘首以盼，期待着MySQL 5.6的将临。MySQL 5.6在MySQL 5.5的基础上开发完成。

今年MySQL的会议上，会听到如下的主题：

  • 优化器全面提高性能
  • InnoDB提高事务吞吐
  • 全新的NoSQL memcached APIs
  • 查询和超大表分区优化
  • replication多方面提高
  • PERFORMANCE_SCHEMA 提供了更多的性能监控数据

注1：MySQL的实现分为两层，即服务器层（后文简称为server）和存储引擎层（storage engine）
注2：MySQL的索引遍历是在存储引擎层实现。

## 优化器全面提高性能

### 索引条件下推到存储引擎
将where子句中的处理更多的转移到存储引擎处理。相比取出完整行后再使用where子句评估，ICP(Index Condition Push-down)将这些where子句发送到存储引擎，这样存储引擎可以根据索引组(index tupples)来修剪结果集。从而，降低了对主索引的IO开销，降低了server与存储引擎之间的传输开销。这项特点在innodb，MyISAM，NDCluster引擎上都有实现。

ICP是一项优化，在MySQL从一个有索引的表取数据时生效。没有ICP，存储引擎将会根据索引来定位所需要的行，然后把这些行完整返回给server，在server出对这些行验证where子句（where子句部分，如果验证不通过，将会把这些行丢弃）。一旦启用了ICP，如果where子句中有一部分条件可以只使用了索引中的列，（MySQL）server将会把这一部分（只涉及索引部分的where子句）下推到存储引擎层。存储引擎将会根据索引中的数据来判定这些下推的where子句，只有（index中的数据）满足这些（下推的）条件时才会从主表中真正读取这些行。ICP可以减少存储引擎访问主表的次数，同时还可以减少MySQL Server访问存储引擎的次数。

当需要访问整个行的完整数据是，ICP优化对于range，ref，eq_ref和ref_or_null等访问方式有效。这种策略对于InnoDB和MyISAM类型的表都有效。（注意：在MySQL 5.6中的partitioned 表不支持ICP.）对于InnoDB表来说，ICP只对二级索引（非主键索引）有效。ICP的目标是检索整行扫描读的数量，以此减少IO操作。对于InnoDB聚合索引来说，整行记录已经被读入InnoDB的缓存中。在这种情况下，使用ICP起不到减少IO的作用。

为了看出这项优化是怎么工作的，首先考虑一下在没有ICP时，索引扫描是怎样进行的。

  1. 得到下一行：首先，读取索引中的下一个元组，然后使用该元组到主表中定位和读取整行数据。
  2. 验证where子句中（应用在该表上）的条件，根据验证结果决定接受或者拒绝改行。

当启用了ICP时，索引扫描的过程会是这样：

  1. 获取索引中的下一个元组（而不是获取整行数据）。
  2. 验证where子句中（应用在该表上）的条件中的一部分（可以用该索引判定的部分），如果条件不满足，继续验证索引中的下一个元组。
  3. 如果条件满足了，使用该索引元组来定位和读取完整行记录。
  4. 验证where字句中（应用在该表上）的剩余部分，根据验证结果接受或者拒绝改行。

当ICP被启用时，Explain中的Extra列中将展示”Using index condition”. 它不会显示”Index only”，因为当完整行记录必须被读取（不管是判断where条件还是返回select需要数据）时就不是只使用Index了。

假设，我们有一张表包含了人和他们的地址，并且该表拥有一个索引index(zipcode, lastname, firstname). 如果我们知道一个人的zipcode值，但是并不确定他的姓，我们可以这样查询：

1
2
3
4
SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%'

MySQL可以根据zipcode=’95054’使用索引扫描people表。而index中剩余的部分(lastname like ‘%etrunia%’)不能被用来限制需要扫描的行数，所以没有ICP，这条查询将必须取得所有满足zipcode=’95054’完整行记录。

如果启用了ICP，MySQL将会在读取完整行记录之前检查(lastname like ‘%etrunia%’)。这样就避免了对于不满足lastname条件的行的完整行记录扫描。

ICP默认打开；可以使用optimizer_switch系统变量来设置index_condition_pushdown参数。

### 多范围读(Multi-Range Read，MRR)
直到你有足够的SSD之前，从磁盘中顺序读都会比随机读要快。对于二级索引（非主键索引），硬盘上索引条目的顺序和完整行记录所在的硬盘块的顺序是不一致的。与其是通过一些列无序的读磁盘来获取完整的行记录，MRR扫描一个查询中的一个或多个索引范围，然后所得的索引元组按照相应的完整行数据所在的磁盘块排序。然后使用更大的顺序IO的请求读取这些磁盘块。这项优化对范围索引扫描以及在索引列上的equi-join有效。（考虑InnoDB外键。）对于所有的存储引擎都有效。

通过在二级索引上的范围扫描来读取数据，当主表很大并且没有在存储引擎的缓存中时，会造成对主表大量的随即磁盘读。通过磁盘块交换MMR优化，MySQL试图减少范围扫描造成的随机磁盘读取次数，主要通过首先只扫描索引，然后手机相关行的键（主键？）。随后这些键被排序，最后这些完整行记录按照主键的顺序被读取出来。MRR的主要目的是减少硬盘随机读，代之以更顺序的方式扫描基表。

MRR读优化提供了一下好处：

  1. MRR使得数据行可以被顺序读取而不是按照索引顺序随机读取。server获取了满足查询条件的索引元组，按照行数据ID（主键）的顺序排序，然后使用这些排序后的元组来顺序的获取数据。这使得获取数据更加的有效和低耗。
  2. MRR使得可以对需要访问索引并通过索引元组来访问完整行记录的请求进行批量处理。MRR遍历一系列的索引范围来获取满足条件的索引元组(类似于tcp拥塞控制中，把较小的包累计后一起发送)。不断的收集索引元组，当累积到一定量时，（这些元组）用来到主表中读取数据行。

在以下的场景中，应用MRR优化可能会带来利好

####Senario A: MRR可以被用于InnoDB和MyISAM表的索引范围查询和equi-join操作。

  1. 一部分的索引元组在缓存中累积。
  2. 缓存中的元组按照ID排序。
  3. 按照拍好序的顺序访问主表中的数据行。

####Senario B: MRR可以被用于NDB表，用于多范围索引扫描以及通过一个属性进行equi-join

  1. 一部分的多个范围索引的元组，或是单个索引范围的元组，当查询提交时，在中心节点的缓存中累积。
  2. 这些范围被发送到执行节点上用以访问数据行。
  3. 这些被访问的行被打成协议包，回传给中央节点。
  4. （中心节点）接收到包含数据行的协议包后，把它们放在缓存中。
  5. 数据被从缓存中读取。

当MRR被使用时，当MRR被使用时，EXPLAIN的Extra列将展示

1
Using MRR

当查询结果不需要整行数据时，InnoDB和MyISAM不会使用MRR。原因是，有可能索引中信息足够用来构造查询结果（即覆盖索引），在这样的情况下，MRR没有任何增益。

栗子

在下面的栗子中，MRR会被使用（假设有一个索引INDEX(key_part1, key_part2)）

1
2
3
SELECT * FROM t
   WHERE key_part1 >= 1000 AND key_part1 < 2000
   AND key_part2 = 10000;

索引中包含(key_part1, key_part2)元组的值，按照”首先按照key_part1排序，再按照key_part2排序”的规则排序。

如果没有MRR，一次索引扫描将覆盖所有的keypart1从1000到2000的元组，不管key_part2在这些元组中的值是不是10000^footnote1。这样的扫描出的元组中key_part2的值不一定是10000。

如果启用了MRR，扫描会被分解成多个范围，每个范围包含key_part1单个的一个值。每个这样的扫描都只需过滤处理那些key_part2的值为10000的元组。MRR使得索引扫描的元组数量大大减少。

用范围标记表示（不使用MRR的查询），不使用MRR的扫描必须要验证这样的索引区间[{1000, 10000}, {2000, INT_MIN}][^footnote2]。这样扫描结果可能会包含许多key_part2不为10000的元组。
然而MRR扫描，则验证多个单点区间[{1000, 10000}], [{1001, 10000}]…, [{2000, 10000}]，这样扫描得到的索引元组将只包含key_part2=10000的部分。

两个optimizer_switch系统变量标志为可以提供接口来使用MRR优化。mrr标志位控制MRR是否启用。如果mrr被启用了（on），mrr_cost_based标志位将控制优化其是否要根据开销来选择使用使用MRR(ON)，
还是只要能使用MRR就是用它（OFF）。mrr，mrr_cost_based都是默认打开的（on）。

对于MRR，存储引擎使用read_rnd_buffer_size系统变量的值作为能够申请多少内存(用于MRR)的指导值。引擎将根据read_rnd_buffer_size来决定将单次处理分割成多少个range。

##…未完待续

<<<<<<< HEAD

文件排序优化（File Sort Optimization, FSO）
对于组合了ORDER BY non_index_column(无索引列) 与LIMIT x的查询，本特性，在X列的内容可以被装入排序buffer中时，会加速排序。对于所有的存储引擎都生效。

Innodb优化
MySQL 5.6以innodb作为默认的存储引擎，从MySQL 5.5开始就是这样。

存储优化器状态
提供了更精确的InnoDB索引统计，并且在MySQL重启之后不会消失。InnoDB提前通过对索引的一部分进行采样计算统计，这些统计会帮助优化器决定在一个查询中要选择使用哪个索引。这些统计在MySQL重启后不会丢失。而不是在重启或发生一些运行中事件时重新计算。更加准确的统计会提高查询性能，并且存储方面会保持查询性能的稳定。这项特性被innodb_analyze_is_persistent, innodb_stat_persistent_sample_pages以及innodb_stats_transient_sample_pages等配置项控制。当这项存储状态特性被激活，这些统计信息只会在明确的对表调用ANALYZE_TABLEi。

新的INFOMATION_SCHEMA表

[^footnote2]: 原因是在SQL的比较中组合索引的比较逻辑先以第一个key为主，然后在比较第二个，依次类推。例如{1001, 0} > {1000, 10000}

05540260346b54dcac9d41b4fac10bab58d0e736