mysql的分区和视图

分区表

• 分区表是一个独立的逻辑表，底层由多个物理子表组成，从存储引擎的角度来看，底层表和普通表没有任何区别

• 每一个分区表都会有一个使用 # 分隔命名的表文件

• 分区表的索引也是按照分区的子表定义的，没有全局索引，分区表的索引也只是再各个底层表中加上完全相同的索引

• mysql再创建表的时候通过 partition by 字句定义每个分区存放的数据，分区表达式可以是列，也可以是包含列的表达式

• 再执行查询的时候，优化器会根据分区定义过滤不需要的分区，只查找包含需要数据的分区

• 分区的目的是把相关的数据存放再一起，如果想对某个分区进行操作就会很方便，例如删除整个分区

• 分区不需要精确定位数据的位置，也就是不需要额外的数据结构，所以代价很低

分区的原因：

• 再超大数据量的时候，索引就无法起作用了，除非是索引覆盖查询，否则会根据索引扫描的结果回表，如果数据量很大，会产生大量的随机i/o，数据库的响应时间可能会大到无法接受，而且维护索引的代价也非常高

• 分区可以理解为索引的最初形态，以非常小的代价定位到数据再哪一片区域，再在这片区域里可以做顺序扫描，也可以建立索引，还可以把数据都缓存到内存中

分区适合的场景：

• 表非常大，没办法全部放在内存中，或者只再表的最后部分有热点数据，其他都是历史数据

• 分区可以更容易进行批量删除和更新操作

• 分区可以分摊服务器压力，分区表的数据可以分布到不同的物理设备中

• 分区有利于备份，和数据的恢复

分区的限制：

• 一个表最多1024个分区
• 如果分区字段中有主键或者唯一索引的列，那么主键列和唯一索引列都必须包含进来
• 分区表不能使用外键
• 所有的分区表存储引擎必须相同
• 某些存储引擎不支持分区

分区表存在的问题：

• 如果分区表达式的值可以为null，会使分区过滤无效

• 如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤，

• 分区有很多种类型，对于范围分区的成本会很高，因为服务器需要扫描所有的分区定义列表来寻找合适的分区

• 执行sql语句的时候，需要先打开并锁住所有底层表，这个操作再分区过滤之前发生，会影响所有查询

• 维护分区的成本可能会很高，新增和删除会很快，但是重组分区会很慢，需要先创建临时分区，将数据复制到临时表没然后删除原分区

视图

视图的理解

• 视图是一种虚拟表 ，本身是不具有数据的，占用很少的内存空间，它是 SQL 中的一个重要概念。

• 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

• 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然

• 向视图提供数据内容的语句为SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句。

• 视图是向用户提供基表数据的另一种表现形式。当数据表比较复杂的情况下，视图可以帮助我 们把经常查询的结果集放到虚拟表中，提升使用效率。

不可更新的视图

• 要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一 的关系。
• 另外当视图定义出现如 下情况时，视图不支持更新操作：
  • 在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
  • 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
  • 在定义视图的SELECT语句中使用了 JOIN联合查询 ，视图将不支持INSERT和DELETE操作；
  • 在定义视图的SELECT语句后的字段列表中使用了 数学表达式 或 子查询 ，视图将不支持INSERT，也 不支持UPDATE使用了数学表达式、子查询的字段值；
  • 在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、 UNION 等，视图将不支持INSERT、UPDATE、DELETE； 在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持 INSERT、UPDATE、DELETE；
  • 视图定义基于一个 不可更新视图 ；
  • 常量视图。

视图的优缺点

• 优点：

  • 操作简单，将经常使用的查询操作定义为视图，就不需要关心表的结构、表与表之间的关联关系，以及业务逻辑和查询条件，只需要简单地操作视图，简化了对数据库的操作。
  • 视图跟实际数据表不一样，它存储的是查询语句。在使用的时候，要通过定义视图的查询语句来获取结果集，视图本身不存储数据，不占用数据存储的资源。
  • MySQL可以根据权限将用户对数据的访问限制在某些视图上，而不需要查询数据表，一定程度上保障了数据表中数据的安全性。
  • 当业务系统的需求发生变化后，如果需要改动数据表的结构，工作量相对较大，可以使用视图来减少改动的工作量。

• 视图缺点

  • 如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。
  • 维护会变得比较复杂， 可读性不好 ，容易变成系统的潜在隐患，因为创建视图的 SQL 查询可能会对字段重命名，也可能包 含复杂的逻辑，这些都会增加维护的成本
  • 如果视图过多，会导致数据库维护成本的问题

视图的算法：

• 合并算法
  • 服务器会将视图的sql和查询的sql合并
• 临时表算法
  • 服务器先执行查询sql，把数据保存在和视图结构一样的临时表中，再基于临时表查询
• 所以会尽可能的使用合并算法，但是如果视图中包含group by 、distinct 、聚合函数、union、子查询等，只要原表记录和视图无法建立一一映射场景，都会使用临时表算法来实现视图

存储程序