mysql知识点系列-分区表、视图

本文主要基于《高性能MySQL》

1、分区表

分区表其实是一张逻辑表，底层其实是由一系列的物理表组成，mysql在这些物理表上做了一层封装，使得用户可以像操作一张表一样操作分区表。
因为底层是一系列的物理表，所以索引也没有所谓的全局索引，索引都是在每个物理表上单独定义的。
分区表可以通过关键字partition by指定每个分区存放的数据范围，这样在查询的时候，可以通过分区关键字直接过滤掉不需要的分区。这样可以加快查询速度，减少内存消耗。
不过分区表也有一些限制：

1. 表最多只能有1024个分区；
2. 如果分区字段中包含索引或者唯一索引的部分字段，那么所有的主键列或者唯一索引列都必须包含进来；
3. 分区表无法使用外键约束。

从存储引擎的角度来说，分区表的各个子表与普通表没有区别。
对于增删改查，简单来说，分区表根据分区字段直接过滤掉不需要的分区，然后对需要分区进行增删改查操作。
mysql分区表支持范围分区、Key分区、Hash分区、List分区、Column分区。
分区表的缺点：

1. 如果分区表达式计算结果为null，那么插入数据时，mysql会将该数据直接插入到第一个分区表中，如果这种数据非常多的话，第一个分区表会非常大；
2. 分区表的所有分区必须使用相同的存储引擎；
3. 范围分区、Key分区、Hash分区、List分区的分区表达式计算结果必须是整形；
4. 有些函数或者表达式无法再分区表达式中使用；
5. 选择分区的成本可能会很高，比如范围分区，每次都需要扫描所有的分区定义列表才能找到对应的分区；

根据我的经验综合以上缺点，我不建议使用分区表，因为现在分库分表的技术已经很成熟了，无论是扩展性还是灵活性都比分区表好很多，分库分表还可以减轻单数据库的压力，所以建议可以的话，尽量使用分库分表技术。

2、视图

视图是一个虚拟表，数据库本身没有这张表，视图呈现出来的数据都是从其他表生成的。
当查询视图时，mysql提供了两种处理视图查询的方法：合并算法和临时表算法。合并算法是mysql重写含有视图的查询，将视图定义的SQL直接包含进查询的SQL中，临时表算法是mysql先执行视图SQL，将结果放到一个临时表中，然后在临时表上执行查询SQL。如果可能，会尽可能使用合并算法。
如果使用的是合并算法，可以在explain extended之后使用show warnings来查看使用视图的查询重写后的结果。
只要是无法在原表记录和视图记录之间建立一一映射，mysql都将使用临时表算法来实现视图，比如group by、子查询等都会使用临时表算法。不过这在以后的版本中可能会修改，如果想要确切的知道使用的是哪种算法，可以explain一条针对视图的简单查询，如果select_type字段的值为DERIVED，则使用的是临时表算法，如果是SIMPLE，则使用的是合并算法。
视图使用哪种算法是视图本身的属性，和作用在视图上的查询语句无关，还可以使用algorithm关键字指定视图使用哪种算法。下面是一些例子：

--使用合并算法创建vw_test1
create algorithm=merge view vw_test1 as  select * from test;
--使用临时表算法创建vw_test2
create algorithm=temptable view vw_test2 as  select * from test;

mysql> explain select * from vw_test1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |   11 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

mysql> explain select * from vw_test2;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   11 | NULL  |
|  2 | DERIVED     | test       | ALL  | NULL          | NULL | NULL    | NULL |   11 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
2 rows in set
--下面是展示使用合并算法后的SQL语句
mysql> explain extended select * from vw_test1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |   11 |      100 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
1 row in set

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                     |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `crawl`.`test`.`id` AS `id`,`crawl`.`test`.`name` AS `name`,`crawl`.`test`.`address` AS `address` from `crawl`.`test` |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

视图也支持更新操作，但是更新视图记录有很多限制，尽量不要在视图上更新。视图不能创建索引。
mysql对视图的支持还不是很成熟，有些视图可能会消耗很多性能，所以使用前要做好测试。