解决海量数据问题:分区表

分区表

分区指的是将一个表或者索引分解为多个更小、更加容易管理的部分。从访问数据库的应用角度上看,它们只有一个逻辑表示,但在物理上表的记录可能划分为多个模块,也就是所谓的分区。

Mysql支持水平分区,即将同一个表的不同行的记录划分到不同的物理文件中,暂时不支持垂直分区(将同一张表的记录按照列划分到不同的物理文件中)。而每一个分区中既存放着索引也存放着数据。

Mysql支持的分区类型:

  • Range分区:行数据基于一个给定连续区间的列的值,划分到不同分区。

  • List分区:List分区与Range的区别在于其按照离散的列值,划分分区。

  • Hash分区:依据特定的值,进行hash计算,放入不同的分区。

分区列的选定原则:

若表中存在主键或者唯一索引,分区列必须是唯一索引的一个组成部分。

分区类型

①Range分区

其依据某一列的值,按照不同的区间划分为不同的分区=》存放在不同的物理文件中。

根据score的值,按照划分为 小于60 ,60到100 两个分区。即及格和未及格两个分区。

create table t1
(
   score INT 
)ENGINE=INNODB
PARTITION BY RANGE(id)(    //按照socre的值进行分区
PARTITION p0 VALUES LESS THAN (60),      //未及格
PARTITION p1 VALUES LESS THAN (100));    //及格

使用范围分区,在编写查询语句时,应当遵循以下原则:
①尽可能使得查询条件对应到一个分区内,使得查询发生在一个文件中。

②对于诸如删除,修改等操作,采取分区,可以一定程度上提高性能。

②List分区

LIst分区和Range分区类似,但分区依据的列的值是离散的。例如依据不同的年份,划分数据到不同的分区。

create table t1
(
   year_num INT 
)ENGINE=INNODB
PARTITION BY List(year_num)(               //按照年份(离散)进行分区
PARTITION p0 VALUES IN (2015),
PARTITION p1 VALUES IN (2016),
PARTITION p2 VALUES IN (2017),
PARTITION p3 VALUES IN (2018),
PARTITION p4 VALUES IN (2019),
PARTITION p5 VALUES IN (2020));

③Hash分区

采取Range和List分区,存在几个问题,必须事先确认分区的标准,拓展性不足,另外数据可能不能均匀的分布在不同的分区中,而Hash分区可以解决数据均匀分布在不同位置。

通常选取自增的主键等列作为Hash分区的列,能够很好的使得数据均匀的分布:

create table t1
(
   col1 INT NULL,
   col2 DATE NULL,
   col3 INT NULL,
   col4 INT NULL,
   UNIQUE KEY(col1,col2,col3,col4)
)
PARTITION BY HASH(col3)    //按照col3所在列 进行hash分区 共4个分区
PARTITIONS 4;              

分区和性能

对表做了分区,查询速度一定会提升吗?答案是否定的。通常而言,数据库应用分为两大类:

①OLTP:在线事务处理,是最主流的数据库应用,其通常的特点是并发量大,事务通常简短,对实时性要求较高。如:在线商城、微博等。
②OLAP:在线分析处理,通常应用于数据仓库、数据分析等场景。

以网络游戏为例,玩家操作的游戏数据库是OLTP的,但游戏运营商需要批量地对玩家进行分析,则是通过OLAP来达到预测玩家行为和特点。

对于OLAP应用,分区可以很好的提高查询的性能,因为OLAP应用最常见的场景是扫描一张大表,例如,需要获取《2019年所有玩家的购买记录》 那么只需要查询2019年所在的分区即可。

而对于OLTP应用,其特点是事务简短***,通常查询和访问的数据很少*。例如对于一条记录的查询,走B+树索引,需要2-3次的磁盘IO。

而不当的分区,可能导致对每个分区进行扫描查询,每个分区的B+树索引需要进行2-3次磁盘IO,多个分区则可能造成非常大的性能开销。

因此分区以及相应查询语句的原则应当为:尽可能避免访问多个分区。

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页