约束/视图/分区

约束：
primary key (索引实现)
unique key (索引实现)
foreign key
FOREIGN KEY （p_id） references table_name(id) [on delete option ][on update option]，若未显式加索引，innodb 自动给fk列加索引
子表插入或更新时，会查询父表是否存在记录 （select… lock in share mode）,查不到错误，也可能锁等待
option: 控制当父表行更新或删除时对子表的操作
cascade 父删除或更新，子也删除或更新
set null 父删除或更新，子数据改为null
no action 错误，不允许
restrict(默认) 错误，不允许

default
not null （sql_mode 是否严格决定报错还是默认转换（如null转为0））
enum(‘a’,‘b’)值只能为其中一个 ,set(‘a’,‘b’)值只能为其中多个 这实现了类似check的约束
触发器实现约束（每行 insert,update,delete 前或后 调用sql命令或存储过程） 完成一些不好实现的约束
create trigger trigger_name before update on table_name for each row
begin
end

视图： 不用关心基表，安全层
mysql不支持物化视图，所有视图都是虚表（不占存储空间）
视图查询都是换算到基表查询
有些视图可更新（插入或更新，本质换算到基表进行插入或更新，updatable view）
create view view_name
as …
[with check option] ,若有表明需检查插入数据是否满足视图定义，如视图定义为id<10的数据，通过视图插入id=20的记录
物化视图：占存储空间，一般用于多表join或group by等复杂查询的结果保存
mysql 只能通过自己创建表或trigger实现类似物化视图功能

分区：
mysql支持水平，不支持垂直，有些引擎支持（innodb,myisam）,有些引擎不支持（csv…）,
mysql 支持局部分区索引（分区含数据和索引），不支持全局分区索引（数据放分区，索引放一起）
分区列：存在显式主键或唯一索引， 分区列必须为其中一部分，否则任意列
1 range 整数（列或列函数处理），

 create table t(
 id int
 )engine=innodb
  partition by range(id) (
 partition p0 values less than (10),
  partition p1 values less than (20),
  partition p2 values less than maxvalue);

分析：t#P#p0.ibd… 大于等于10小于20放p1，null 是最小值，放p0，不加maxvalue，像30这样的找不到分区报错，
partition pruning(分区修剪) ，将查询锁定在某个分区 ,explain partitions 去看一下，这个必须依靠实践，版本不同可能就不同
2 list 整数（列或列函数处理），

 create table listP(
 id int
 )engine=innodb
  partition by list(id) (
 partition p0 values in (10),
  partition p1 values in (20,30));

分析：null值必须指定放在某个分区
3 hash 均匀分布数据，用户给定表达式（返回整数）作为hash函数并给定分区数量，mysql取模确定分区

 create table hashP(
 id int
 )engine=innodb
  partition by hash(id*2) 
  partitions 4;

分析：若id=3，6对4取模为2，放第三个分区p2中，null当0处理
4 key 使用mysql提供的hash函数，null当0处理

 create table keyP(
 id int
 )engine=innodb
  partition by key(id) 
  partitions 4;

5 columns 解决以前分区值（列值或列转换值）只能为整数的问题。
range columns 可使用多个列分区

   create table rangeC(
 name varchar(10),
 date datetime
 )engine=innodb
  partition by range columns(name,date) ( 
 partition p0 values less than ('d','2010-10-01'),
  partition p1 values less than ('g','2015-10-01'));

list columns

   create table listC(
 name varchar(10)
 )engine=innodb
  partition by list columns(name) ( 
 partition p0 values in ('d'),
  partition p1 values in ('g','f'));

子分区：range，list分区后再使用hash,key分区

   create table sub1(
 a int,
 b int
 )engine=innodb
  partition by list (a)
  subpartition by  hash(b)
  subpartitions 2
  ( 
 partition p0 values in (1),
  partition p1 values in (2));

上面不显式指定名称

 create table sub2(
 a int,
 b int
 )engine=innodb
  partition by list (a)
  subpartition by  hash(b)
  ( 
 partition p0 values in (1)(subpartition s0,subpartition s1),
  partition p1 values in (2)(subpartition s2,subpartition s3));

显式指定名称

分区与性能：
如果能将查询确定在具体分区（partition pruning），性能一般提升
olap: 查大量数据，性能提升
oltp: 谨慎，用分区列去查还好，用其他列索引性能反而下降（扫多个分区）

alter table e1 exchange partition p0 with table e2 (将分区数据和表数据进行交换)