浅谈MySQL(五)-视图及表

在MySQL数据库中,视图(view)是一个命名的虚表,与持久表不同的是,视图中的数据没有实际的物理存储。

    1、视图作用:

创建的语法:create view_name [(column_list)] as select_statement [with [cascaded | local] check option ].

虽然视图是基于基表的一个虚拟表,但是用户可以对某些视图进行更新操作,其本质就是通过视图的定义来更新基表。视图中定义with check option就是针对可更新的视图的。

例:
 mysql> create table a(id int);
 Query OK, 0 rows affected (0.02 sec)

mysql> create view v_a as select * from a where id<10;
 Query OK, 0 rows affected (0.00 sec)

mysql> insert into v_a select 20;
 Query OK, 1 row affected (0.01 sec)
 Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from v_a;
 Empty set (0.00 sec)

如上面的操作,视图中插入的数据提示成功,但视图中并没有数据,如果我们查看基表,会发现基表中已经插入了该数据,我们加上with check option再试试。

mysql> create view v_a as select * from a where id<10 with check option;
 Query OK, 0 rows affected (0.00 sec)

mysql> insert into v_a select 20;
 ERROR 1369 (HY000): CHECK OPTION failed 'test.v_a'

现在我们会看到报错的提示,并且基表中也没有插入数据。这次MySQL就会检查视图中插入的数据是否满足视图定义的条件,不允许视图随意更新或插入数据。

2、分区表

分区功能不是在存储引擎层完成的,所以有很多存储引擎支持分区。分区的过程就是将一个表或索引分解为多个更小、更可管理的部分。从逻辑上而言,只有一个表或一个索引,但是在物理上这个表或索引可能由数十个物理分区组成,每个分区都是独立的对象,可以独自处理。MySQL支持的分区类型为水平分区(按行分配),并且MySQL的分区是局部分区索引,就是一个分区中既存放了数据又存放了索引。

mysql> show variables like “%partition%”\G;
*************************** 1. row ***************************
Variable_name: have_partitioning
Value: YES
1 row in set (0.00 sec)

通过命令我们可以查看当前数据库是否支持分区功能,当然,并不是启用了分区,性能就更会更快更好。目前MySQL支持一下几种类型的分区,无论哪种分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分:

    a)RANGE分区:行数据基于属于一个给定连续区间的列值被放入分区。(必须是整型)

最常用的一种分区类型,我们创建一个id列的区间分区表,当id小于10时,数据插入p0分区,10到20时插入p1分区。

mysql> 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);
 Query OK, 0 rows affected (0.01 sec)

range分区主要用于日期列的分区,例如对销售类的表,可以根据年来分区存放销售记录。如果我们要删除某一年的数据,也不需要像常规的sql语句一样where year>=2017 and year<2018,只需要alter table tableName drop partition p1就可以了。同样查询速度也会提高,只需要在常规的select语句前添加explain partitions即可。sql优化器只会去搜索那个符合的分区,不会整表搜索,速度大幅度提高。

    b)LIST分区:和RANGE类似,LIST分区是面向离散的值。(必须为整型)

LIST分区和RANGE分区非常相似,只是分区的值是离散的,而非连续的,如:

mysql> create table a(
    -> a int,
    -> b int)ENGINE=INNODB
    -> partition by list(b)(
    -> partition p0 values in(1,3,5),
    -> partition p1 values in(2,4,6));
Query OK, 0 rows affected (0.01 sec);

如果插入的数值不在分区的定义中,mysql会抛出异常。另外在insert多行数据的过程中如果遇到未定义的值,MyIsam和InnoDb存储引擎的处理完全不同,mysiam会将之前的行数据插入,之后的数据不会插入,而innodb存储引擎将其视为一个事物,因此没有任何数据插入。

    c)HASH分区:根据用户自定义的表达式的返回值来分区,返回值不能为负数(必须是整型号)

HASH分区的目的是将数据均匀的分布到预先定义的各个分区中,保证各分区的数据数量大致都是一样的。在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在HASH分区中,MySQL自动完成这些工作,用户所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式,以及指定被分区的表将要被分割的分区数量。如下:

mysql> create table b(
    -> id int,
    -> t datetime
    -> )ENGINE=INNODB
    -> partition by hash(year(t))
    -> partitions 4;
Query OK, 0 rows affected (0.01 sec)

    d)KEY分区:根据MySQL提供的哈希函数来进行分区(必须为整型)

KEY分区和HASH分区相似,不同的是HASH分区使用的是用户自定义的函数,而KEY分区使用的是MySQL数据库提供的函数进行分区。对于NDB Cluster引擎,MySQL数据库使用MD5函数来分区;对于其他存储引擎,MySQL数据库使用的是其内部的哈希函数,基于PASSWORD()一样的运算法则。如:

mysql> create table c(
    -> id int,
    -> t datetime
    -> )ENGINE=INNODB
    -> partition by key(t)
    -> partitions 4;
Query OK, 0 rows affected (0.01 sec)

当然还有其它的分区和子分区,鉴于上述的分区基本可以满足我们在项目中的使用,我就不再深入介绍更多的,以防多了嚼不烂。

3、分区和性能

分区并不是一定会提升查询的速度,甚至会发现查询的速度急速下降。

数据库的应用分为两类:OLTP(在线事物处理),如Blog、电子商务、网游等;另一个是OLAP(在线分析处理),如数据仓库、数据集市。

对于OLAP的应用,分区确实是可以很好地提高查询的性能,因为OLAP应用大多数查询需要频繁的扫描一张很大的表,假设一张1亿的表,其中有一个时间戳属性列,用户的查询需要从这张表中获取一年的数据。如果按时间戳进行分区,则只需要扫描相应的分区即可。

然后对于OLTP的应用,分区应该非常小心了,在这种应用下,通常不可能获取一张大表中10%的数据,大部分是通过索引返回几条记录而已。而根据B+树索引的原理可知,对于一张大表,一般的B+树需要2~3次的磁盘IO。因此B+树可以很好地完成操作,不需要分区的帮助,并且设计不好的分区会带来严重的性能问题。

举个简单的例子:有一张1000W数据的表,团队会选择对其做分区,比如对主键做10个HASH分区,这样每个分区都只有100W数据了,因此查询起来会变快了。但是有一种情况,1000W数据和100W数据本身构成的B+树的层次都是2层,那么上述分区就不能带来性能提升。好的,如果1000W的B+层是3,,100W的层是2,这样就会避免一次IO,提升查询效率。但是如果查询的条件不是主键,而是其它的字段,这是查询就会扫描所有10个区,需要20次的IO,这样就会大大减低了查询效率。

                                               你们的支持是我前进的东西,感谢您的阅读!

 

 

发表评论