《高性能Mysql》读书笔记——第七章Mysql高级特性

一、分区表

1.简单介绍

分区表对于用户来说就是一个表，但是底层实际上是物理隔离的多个子表。分区的实现对于存储引擎来说是封装好的黑盒子，对于应用来说是透明的，反正就是说，分区表其实是在Mysql服务器层实现的，和引擎应该是无关的。从底层的文件系统可知，分区表都是用#分割命名的表文件 Mysql中的分区表，每个子表有自己的索引，没有整个父表的索引。分区表有什么用呢？使用场景如下： - 表非常大，几GB以及以上级别，或者表的最后部分是热点数据，其他部分都是历史数据，很少查询，也很少改写。这样我们使用分区表时可以只查询某个子表的数据 - 分区表的数据易于维护，可以整个删除某个分区的数据，还可以针对某个独立分区进行一些优化、检查、修复操作 - 分区表的数据可以分布在不同的物理设备上 - 可以利用分区表避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等(书里写的这都是啥，求科普……) - 可以备份和恢复某个独立的分区

创建分区表的语法：

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

如果想要详细地了解分区表的创建还有修改语法，还是去官网查看CREATE TABLE,ALTER TABLE语句的文档比较好，在{% post_link mysql-basic %}文章里有给出文档链接。

分区表有一些限制： - 一个表最多只能有1024个分区 - 分区表无法使用外键约束 - 分区必须使用相同的存储引擎 - 还有一些其他的，书中介绍的不太清楚，比如提及分区字段中有主键或者唯一索引列的时候，所有的主键列和唯一索引列都必须包含进来(我实在没懂要包含进哪里来，译者的语文水平真是捉急)

2.分区表原理

以下是RUID操作的分区表底层逻辑： SELECT：查询一个分区表时，会先打开并锁住所有的分区子表，优化器判断是否可以过滤部分分区，然后再调用存储引擎来访问各个分区的数据 INSERT：写入一条记录时，同样地，先打开并锁住所有的分区子表，确定哪个分区接收这条记录，然后写入对应子表 DELETE：一样的，打开并锁住所有分区表，然后确定数据对应分区，然后在相应子表上删数据 UPDATE：一样，先打开并锁住所有分区表，然后找到要改的数据，修改，然后判断改后的数据应该放在哪个分区，就写入哪个分区子表，删除之前分区子表中的那条数据这本书啰嗦了半天，其实上述操作时类似的，而且更扯的是，书后立马说道，并不是所有情况都锁整个分区表，InnoDB就不锁，只锁行，和普通的表类似。(没区别为什么还要这么啰嗦？有细微区别却又不提……难道我在亚马逊上买到了盗版……)

3.使用分区表需要注意的一些问题

注意NULL值可能会让分区表的过滤很鸡肋，所有的NULL值或者非法值会被放到第一个分区里，那就悲剧了，尤其是上面例子中那种YEAR()函数等，一旦接收非法值就返回NULL的函数，会导致查询时总是会查第一个分区，因为这个函数会返回NULL值。书中给出的解决办法是创建一个无用的第一分区，比如一个值都没有的，LESS THAN (0)这种，我给出的建议是最好不要用这种函数去分区，最新版本的MYSQL分区的方法多了去，甚至不要用可NULL字段去分区。
查询的时候最好使用where条件尽可能地过滤分区，让被搜索到的分区尽可能地少，尤其关联查询的时候，想想吧，第一个表的每条命中数据在第二个表匹配的时候，都要把第二个表的分区全部搜一遍，这是不可接受的。

二、视图

1.简介

视图就是一个虚拟的表，其数据实际上是来源于真实的表。因此，对视图的删改查实际上是对形成视图的真实的表的删改查。 ### 2.创建视图语句

mysql>CREATE VIEW oceania AS
SELECT * FROM country WHERE continent = 'Oceania'
WITH CHECK OPTION;

其中，WITH CHECK OPTION语句是限制使用视图更新时，更新的东西必须是视图条件内的行。比如要去更新一个continent != ’Oceania’的数据是不行的，只能更新等于Oceania的数据 ### 3.视图的实现机制视图有两种实现机制： - 临时表算法：将对视图的查询更改为对临时表的查询，临时表的内容是视图中的内容 - 合并算法：将对视图的查询和视图定义的查询合并为一个查询，然后直接返回查询结果。

临时表算法浪费空间，性能较差，所以大多数时候推荐使用合并算法。在视图中包含GROUP BY、DISTINCT、聚合函数、UNION、子查询等原表记录不能与视图记录一一对应的场景，Mysql都会使用临时表算法。 ### 4.视图的限制 - 不能对视图创建触发器 - 使用临时表算法实现的视图，无法被更新 - 无法使用SHOW CREATE VIEW来查看视图定义语句（估计5.5之后的版本已经修好了这个bug吧，不是很确定）

5.视图使用小技巧

比如，利用视图可以进行基于列的权限控制：