【mysql性能优化】分区表、合并表

【最近在学习Mysql性能优化，以下是知识总结】

一、合并表

1.1合并表的概念
合并表相当于一个容器，里面包含了多个真实表。
合并表（merge table）是一种早期的、简单的分区实现，和分区表相比有一些不同的限制，并且缺乏优化。分区表严格来说是一个逻辑上的概念，用户无法访问底层的各个分区，对用户来说分区是透明的。但是合并表允许用户单独访问各个子表。分区表和优化器的结合更紧密，这也是未来发展的趋势，而合并表则是一种将被淘汰的技术，在未来的版本中可能被删除。

*特点（也是不足）：会出现重复的行。

1.2创建合并表（语法）

CREATE TABLE t1(a INT NOT NULL PRIMARY KEY)ENGINE=MyISAM;
CREATE TABLE t2(a INT NOT NULL PRIMARY KEY)ENGINE=MyISAM;
INSERT INTO t1(a) VALUE(1),(2);
INSERT INTO t2(a) VALUE(1),(2);
CREATE TABLE mrg(a INT NOT NULL PRIMARY KEY)ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
SELECT a FROM mrg;

结果：

INSERT_METHOD=LAST;//这里的意思是所有的INSERT语句都会发给最后一个表，值为FIRST的话就是第一个，当然还可以在SQL中明确的操作任何一个子表。

1.3合并表的限制和特性

1. 删除合并表，它的子表不会受影响；
2. 删除子表，则视操作系统而定。例如在GNU/Linux上，如果子表的文件描述还是被打开的状态，那么这个表还存在，但是只能通过合并表才能访问到；（经测试：在win10这么做会报错DROP TABLE t1,t2;SELECT A FROM mrg;[Err] 1168 - Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3. 在使用CREATE语句创建一个合并表的时候，并不会检查各个子表的兼容性，如果自表的定义稍有不同，那么mysql就可能创建出一个后面无法使用的合并表。另外如果成功创建了合并表后再修改某个子表的定义，那么之后再使用合并表可能会看到这样的报错：[Err] 1168 - Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
4. 根据合并表的特性，不难发现，在合并表上无法使用REPLACE语法，无法使用自增字段。更多细节请参阅mysql官方手册。
5. 如果一个查询访问合并表，那么它需要访问所有子表。这会让根据键查找单行的查询速度变慢，如果能够只访问一个对应表，速度肯定更快。所以，限制合并表中的子表数量很重要，特别是当合并表示某个关联查询的一部分的时候，因为这时访问一个表的记录数可能会将比较操作传递到关联的其他表中，这时减少记录的访问就是减少整个关联操作。当你打算使用合并表的时候，还需要记住以下几点：1）执行范围查询时，需要在每一个子表上各执行一次，这比直接访问单个表的性能要查很多，而且子表越多，性能越差。2）全表扫描和普通表的全表扫描速度相同。3）在合并表上做唯一键和主键查询时，一旦找到一行数据就会停止。所以一旦查询在合并表的某一个子表中找到一行数据，就会立刻返回，不会访问任何其他表。4）子表的读取顺序和CREATE TABLE语句中的顺序相同。如果需要频繁地按照某个特定顺序访问表，那么可以通过这个特性来让合并排序操作更高效。
6. 一个MyISAM表可以是多个合并表的子表。
7. 可以通过直接复制.frm、.MYI、.MYD文件，来实现在不同的服务器之间复制各个子表。
8. 在合并表中可以很容易地添加新的子表：直接修改合并表的定义就可以了。
9. 可以创建一个合并表，让它只包含需要的数据，例如只包含某个时间段的数据，而这个是分区表做不到的一点。
10. 如果想对某个子表做备份、恢复、修改、修复或者别的操作时，可以先将其从合并表中删除，操作结束后再将其加回去。
11. 可以使用myisampack来压缩所有的子表。

二、分区表

2.1概念（定义）：对用户来讲，分区表示一个独立的逻辑表，但是底层由多个物理子表组成。在底层系统来看，每个分区表都有一个使用#分割命名的表文件。
2.2分区表的目的：
1）让某些特定查询减少相应时间；
在下列的场景中，分区可以起到非常大的作用：
1. 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
2. 分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。
3. 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
4. 可以使用分区表来避免某些特殊的瓶颈。例如：InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。
5. 如需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

2.3分区表的原理

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

在对分区表的数据进行增删改查前，分区层都会先打开并锁住所有的底层表，如果是1）查询的话，优化器先判断是否过滤部分分区，然后在调用对应的存储引擎接口访问各个分区的数据。如果是2）插入数据的话，会确定哪个分区接受这条记录，再将记录写入对应底层表。如果是3）删除，确认分区后会对底层表进行删除操作。如果是4）更新操作，确认分区后，取出符合条件的数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。
2.4分区表的类型

2.5使用分区表
假设我们要在一个非常大的表中查出一段时间的记录，这个表含有很多年的历史数据，而我们要查询的仅仅是最近几个月的数据，这大约有10亿条记录。使用全表扫描，那么速度肯定会很慢，使用索引会在空间和维护上有很大消耗，最终一个查询会有产生成千上万的随机I/O，应用程序就会因此僵死。
所以这里我们使用分区表来解决这个问题。为了保证大数据的可拓展性，一般有下面两个策略：

1. 全量扫描数据，不要任何索引。使用简单的分区方式存放表，不要任何索引。
2. 索引数据，并分离热点。如果数据有明显的‘热点’，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在这个分区中，让这个分区的数据能够有机会都缓存在内存中。这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效地使用缓存。

2.5分区表的查询优化
引入分区给查询优化带来了一些新的思路（同时也带来了新的bug）。
对于访问分区表来讲，很重要的一点是要在WHERE条件中带入分区列，有时候即使看似多余也要带上，这样就可以让优化器能够过滤掉无须访问的分区。如果没有这些条件，MySQL就需要让对应存储引擎访问这个表的所有分区，如果表非常大的话，就可能会非常慢。
可以使用EXPLAIN PARTITION语句观察优化器是否执行了分区的过滤。
示例：

正如你所看到的，这个查询将访问所有的分区。下面我们在WHERE条件中再加入一个时间限制条件：

注意：mysql只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行。所以，下面的是不行的：

改写成下面的即可：

附：
*什么情况会出问题？

1. NULL值会使分区过滤无效
2. 分区列和索引列不匹配
3. 选择分区的成本可能很高
4. 打开并锁住所有底层表的成本可能很高
5. 维护分区的成本可能很高
   详细请参阅《高性能mysql第三版》有关分区的部分。

*分区的限制

1. 所有的分区都必须使用相同的存储引擎。
2. 分区函数中可以使用的函数和表达式也有一些限制。
3. 某些存储引擎不支持分区。
4. 对于MyISAM的分区表，不能再使用LOAD INDEX INTO CACHE操作。
5. 对于MyISAM表，使用分区表时需要打开更多的文件描述符。虽然看起来是一个表，其实背后有很多独立的分区，每个分区对于存储引擎来说都是一个独立的表。这样即使分区表只占用一个表缓存条目，文件描述符还是需要多个。因此即使已经配置了合适的表缓存，以确保不会超过操作系统的单个进程可以打开的文件描述符的个数，但对于分区表而言，还是会出现超过文件描述符限制的问题。