第七章 MySQL高级特性 高性能MySql 阅读总结

7.1分区表

        对用户来说， 分区表是一个独立的逻辑表， 但是底层由多个物理子表组成。 实现分区的代码实际上是对一组底层表的句柄对象(Handler Object)的封装。对分区表的请求， 都会通过句柄对象转化成对存储引擎的接口调用。 所以分区对于SQL层来说是一个完全封装底层实现的黑盒子， 对应用是透明的， 但是从底层的文件系统来看就很容易发现， 每一 个分区表都有一个使用＃分隔命名的表文件。

        MySQL实现分区表的方式——对底层表的封装——意味着索引也是按照分区的子表定义的， 而没有全局索引。 这和Oracle不同，在Oracle中可以更加灵活地定义索引和表是否进行分区。

          MySQL在创建表时使用PARTITION BY子句定义每个分区存放 的数据。 在执行查询的时候， 优化器会根据分区定义过滤那些没有我们需要数据的分区， 这样查询就无须扫描所有分区——只需要查找包含需要数据的分区就可以了。

        分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起， 另外， 如果想一次批量删除整个分区的数据也会变得很方便。

        在下面的场景中， 分区可以起到非常大的作用：

• 表非常大以至于无法全部都放在内存中， 或者只在表的最后部分有热点数据， 其他均是历史数据。

• 分区表的数据更容易维护。例如，想批最删除大量数据可以使用清除整个分区的方式。 另外， 还可以对一个独立分区进行优化、 检查、 修复等操作。

• 分区表的数据可以分布在不同的物理设备上， 从而高效地利用多个硬件设备。

• 可以使用分区表来避免某些特殊的瓶颈， 例如InnoDB的单个索引的互斥访问 、 ext3文件系统的inode锁竞争等。

• 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

分区表本身也有一些限制， 下面是其中比较重要的几点：

• 一个表最多只能有1024个分区。

• 在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5

中， 某些场景中可以直接使用列来进行分区。

• 如果分区字段中有主键或者唯一索引的列， 那么所有主键列和唯一索引列都必须包

含进来。

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

7.1.1分区表的原理

        如前所述， 分区表由多个相关的底层表实现， 这些底层表也是由句柄对象(Handlerobject)表示， 所以我们也可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎）， 分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看， 底层表和一个普通表没有任何不同， 存储引擎也无须知道这是一个普通表还是一个分区表的一部分。分区表上的操作按照下面的操作逻辑进行：

分区表上的操作按照下面的操作逻辑进行：

SELECT查询

当查询一个分区表的时候， 分区层先打开井锁住所有的底层表， 优化器先判断是否可以过滤部分分区， 然后再调用对应的存储引擎接口访问各个分区的数据。

INSERT操作

当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这 条记录，再将记录写入 对应底层表。

DELETE操作

当删除一条记录时，分区层先打开井锁住所有的底层表，然后确定数据 对应的分区，最后对相应底层表进行删除操作。

UPDATE操作

当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要 更新 的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区， 最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

虽然每个操作都会 “先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中 是锁住全表的。 如果存储引擎能够自己实现行级锁，例如InnoDB, 则会在分区层释放对应表锁。 这个加锁和解锁过程与普通InnoDB上的查询类似。

7.1.2分区表的类型

        MySQL支持多种分区表。 我们看到最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以是包含 列的表达式。

7.1.3如何使用分区表

        假设我们希望从一个非常大的表中查询出一段时间的记录，而这个表中包含了很多年的历史数据，数据是按照时间排序的，例如，希望查询最近几个月的数据，这大约有10亿条记录。可能过些年本书会过时，不过我们还是假设使用的是2012年的硬件设备，而原表中有10TB的数据，这个数据量远大千内存，井且使用的是传统硬盘，不是闪存（多数SSD也没有这么大的空间）。你打算如何查询这个表？如何才能更高效？

        首先很肯定：因为数据量巨大，肯定不能在每次查询的时候都扫描全表。考虑到索引在 空间和维护上的消耗，也不希望使用索引。即使真的使用索引，你会发现数据并不是按照想要的方式聚集的，而且会有大量的碎片产生，最终会导致一个查询产生成千上万的随机I/O,应用程序也随之僵死。情况好一点的时候，也许可以通过一两个索引解决一些问题。不过多数情况下，索引不会有任何作用。这时候只有两条路可选：让所有的查询都只在数据表上做顺序扫描， 或者将数据表和索引全部都缓存在内存里。

        这里需要再陈述一遍：在数据量超大的时候， B-Tree索引就无法起作用了。 除非是索 引覆盖查询， 否则数据库服务器需要根据索引扫描的结果回表， 查询所有符合条件的记录， 如果数据最巨大， 这将产生大量随机I/O    , 随之， 数据库的响应时间将大到不可接受的程度。 另外， 索引维护（磁盘空间、I/O操作）的代价也非常高。 有些系统， 如Info bright, 意识到这一点， 千是就完全放弃使用B-Tree索引， 而选择了一些更粗粒度的但消耗更少的方式检索数据， 例如在大量数据上只索引对应的一小块元数据。

        这正是分区要做的事情。 理解分区时还可以将其当作索引的最初形态， 以代价非常小的 方式定位到需要的数据在哪一片 “区域”。在这片 “区域” 中， 你可以做顺序扫描， 可以建索引， 还可以将数据都缓存到内存， 等等。 因为分区无须额外的数据结构记录每个分 区有哪些数据一分区不需要精确定位每条数据的位置， 也就无须额外的数据结构——所以其代价非常低。 只需要一个简单的表达式就可以表达每个分区存放的是什么数据。

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

全量扫描数据， 不要任何索引。

        可以使用简单的分区方式存放表， 不要任何索引， 根据分区的规则大致定位需要的数据位置。 只要能够使用 WHERE条件， 将需要的数据限制在少数分区中， 则效率是很高的。 当然， 也需要做一些简单的运算保证查询的响应时间能够满足需求。 使用该策略假设不用将数据完全放入到内存中， 同时还假设需要的数据全都在磁盘上， 因为内存相对很小， 数据很快会被挤出内存， 所以缓存起不了任何作用。 这个策略适用于以正常的方式访问大量数据的时候。 警告：后面我们会详细解释， 必须将查 询需要扫描的分区个数限制在一个很小的数量。

索引数据， 并分离热点。

        如果数据有明显的 “热点” ，而且除了这部分数据， 其他数据很少被访问到， 那么可以将这部分热点数据单独放在一个分区中， 让这个分区的数据能够有机会都缓存在 内存中。 这样查询就可以只访问一个很小的分区表， 能够使用索引， 也能够有效地使用缓存。

7.1.4什么情况下会出问题

        上面我们介绍的两个分区策略都基于两个非常重要的假设：查询都能够过滤(prunning)掉很多额外的分区、 分区本身井不会带来很多额外的代价。 而事实证明， 这两个假设在某些场景下会有问题。 下面介绍一些可能会遇到的问题。

NULL值会使分区过滤无效

        关于分区表一个容易让人误解的地方就是分区的表达式的值可以是NULL:第一个 分区是一个特殊分区。假设按照PARTITIONBY RANGE YEAR(order_date)分区，那么所有order_date为NULL或者是一个非法值的时候，记录都会被存放到第一个 分区。现在假设有下面的查询：WHERE order_date BElWEEN'2012-01-01'AND '2012-01-31'。实际上，MySQL会检查两个分区，而不是之前猜想的一个：它会检查2012年这个分区，同时它还会检查这个表的第一个分区。检查第一个分区是因为YEAR()函数在接收非法值的时候可能会返回NULL值，那么这个范围的值可能会返 回NULL而被存放到第一个分区了。

        如果第一个分区非常大，特别是当使用“全量扫描数据，不要任何索引”的策略时， 代价会非常大。而且扫描两个分区来查找列也不是我们使用分区表的初衷。为了避免这种情况，可以创建一个“无用”的第一个分区，例如，上面的例子中可以使用 PARTITION p_nulls VALUES LESS THAN (0)来创建第一个分区。如果插入表中的数据都是有效的，那么第一个分区就是空的，这样即使需要检测第一个分区，代价也 会非常小。

        在MySQL5.5中就不需要这个优化技巧了，因为可以直接使用列本身而不是基于列的函数进行分区：PARTITION BY RANGE COLUMNS(order_date)。．所以这个案例最好的解决方法是能够直接使用MySQL5.5的这个语法。

分区列和索引列不匹配

        如果定义的索引列和分区列不匹配， 会导致查询无法进行分区过滤。 假设在列a上定义了索引， 而在列b上进行分区。 因为每个分区都有其独立的索引， 所以扫描列b上的索引就需要扫描每一个分区内对应的索引。 如果每个分区内对应索引的非叶子节点都在内存中， 那么扫描的速度还可以接受， 但如果能跳过某些分区索引当然会更好。要避免这个问题， 应该避免建立和分区列不匹配的索引， 除非查询中还同时包含了可以过滤分区的条件。

选择分区的成本可能很高

        如前所述分区有很多类型， 不同类型分区的实现方式也不同， 所以它们的性能也各不相同。 尤其是范围分区， 对于回答 “这一行属于哪个分区” 这些符合查询条件的行在哪些分区” 这样的问题的成本可能会非常高， 因为服务器需要扫描所有的分 区定义的列表来找到正确的答案。 类似这样的线性搜索的效率不高， 所以随着分区 数的增长， 成本会越来越高。

打开并锁住所有底层表的成本可能很高        

        当查询访问分区表的时候，MySQL需要打开并锁住所有的底层表， 这是分区表的另一个开销。 这个操作在分区过滤之前发生， 所以无法通过分区过滤降低此开销， 并 且该开销也和分区类型无关， 会影响所有的查询。 这一点对一些本身操作非常快的 查询， 比如根据主键查找单行， 会带来明显的额外开销。 可以用批量操作的方式来 降低单个操作的此类开销， 例如使用批批插入或者 LOAD DATA INFILE 一次删除多行数据， 等等。 当然同时还是需要限制分区的个数。

维护分区的成本可能很高

        某些分区维护操作的速度会非常快， 例如新增或者删除分区（当删除一个大分区可 能会很慢， 不过这是另一回事）。 而有些操作， 例如重组分区或者类似 ALTER 语句的 操作：这类操作需要复制数据。 重组分区的原理与 ALTER 类似， 先创建一个临时的 分区， 然后将数据复制到其中， 最后再删除原分区。

7.1.5查询优化

        分区最大的优点就是优化器可以根据分区函数来过滤一些分区。所以，对于访问分区表来说，很重要的一点是要在WHERE条件中带入分区列，有时候即使看似多余的也要带上，这样就可以让优化器能够过滤掉无须访问的分区。如果没有这 些条件，MySQL就需要让对应存储引擎访问这个表的所有分区，如果表非常大的话， 就可能会非常慢。