约束/视图/分区

亦凉 2022-04-02 10:17 92阅读 0赞

**约束：**  
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** (将分区数据和表数据进行交换)