数据库分区概念及简单运用

大家好，又见面了，我是你们的朋友全栈君。

概念：数据库分区是一种物理数据库设计技术

目的：主要目的是为了在特定SQL操作中减少数据读写的总量以缩短响应时间

分类：分为水平分区(Horizontal Paritioning)和垂直分区(Vertical Partitioning)

水平分区：是对表的行进行分区，通过这种方式不同分组里面的物理列分隔的数据集得以组  合，从而进行个体分隔(单分区)或集体分隔(1个或多个分区).所有表中定义的列  在米格数据集中都能找到，所以表的特性依然得以保持。

例如:一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其 中一年的记录。(一定要通过某个属性来进行分割，这里使用的就是年份)

垂直分区:通过对表的垂直划分来减少目标表的宽度，事某些特定的列被划分到特定的分区， 每个分区都包含了其中的列所对应的行。

    例如：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时  候就要把这些不经常使用的text和BLOB划分到另一个分区，在保证他们数据相  关性的同时还能提高访问速度。

分区优点:

•  相对于单个文件系统或是硬盘，分区可以存储更多的数据
• 数据管理比较方便，比如要清理或废弃某年的数据，可以直接删除该日期的分区数据即可
• 精准定位分区查询数据，不需要全表扫描查询，大大提高数据检索效率
• 可跨多个分区磁盘查询，来提高查询的吞吐量
• 在涉及聚合函数查询时，可以很容易进行数据的合并

详解：

•  分区:就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个 物               理区块组成的
•  分表:就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要               根据定义好的规则得到对应的字段名，然后操作它
•  分库：一旦分表，一个库中的表会越来越多，当数据量很大时，影响到使用时，就需要进行               分库操作。

单机数据库的问题:

• 单个表数据量越大，读写缩，插入操作重新建立索引效率越低
• 单个库数据量太大(一个数据库数据量到1T-2T就是极限)
• 单个数据库服务器压力过大
• 读写速度遇到瓶颈(并发量几百)

分区使用情景:

• 一张表的查询速度已经慢到影响使用的时候
• sql进过优化
• 数据量过大
• 表中的数据是分段的
• 对数据的操作往往只能涉及一部分数据，而不是所有的数据

分区解决的问题:主要是可以提升查询效率

分区的简单实现方式:MySQL5开始支持分区功能

使用oracle数据库创建表分区，使用的是DBeaver Enterprise工具编写sql代码，具体连接方式在此就不做阐述了，大家百度下，接下来创建表分区:

–创建分区表 此表尚未创建

--创建分区表 此表尚未创建
create table student(
    s_id number(3) primary key,
    s_name varchar2(10),
    s_sex char(2),
    s_age number(3)
)
partition by range(s_age)(
    partition p1 values less than(20),
    partition p2 values less than(40),
    partition p3 values less than(maxvalue) --分区列中的最大值
)
--向student表中添加数据
insert into student values (111,'张三','男',18);
insert into student values (222,'赵四','男',16);
insert into student values (333,'王五','男',15)
insert into student values (444,'李一','男',20)
insert into student values (555,'李七','男',32)
insert into student values (666,'徐八','男',40)
insert into student values (777,'佟九','男',49)
--查询表
select * from student;
--查询分区数据
select * from student partition(p1);
select * from student partition(p2);
select * from student partition(p3);

将已存在的表修改为分区表：

--创建sales表
CREATE TABLE sales(
	s_id NUMBER NOT NULL,
	product_id varchar2(5),
	sales_date DATE,
	sales_cost NUMBER(10),
	areacode varchar2(5)
);
--向表中批量添加数据  注意最后的select * from dual不要漏掉
INSERT ALL INTO sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(111,'12',TO_DATE('2011-01-01','yyyy-mm-dd'),300,'china')
into sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(112,'13',TO_DATE('2012-03-01','yyyy-mm-dd'),320,'china' )
into sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(113,'14',TO_DATE('2012-05-01','yyyy-mm-dd'),620,'china' )
into sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(114,'15',TO_DATE('2013-06-01','yyyy-mm-dd'),340,'china' )
into sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(115,'16',TO_DATE('2014-08-01','yyyy-mm-dd'),720,'china' )
into sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(116,'13',TO_DATE('2016-01-01','yyyy-mm-dd'),320,'china' )
into sales(s_id,product_id,sales_date,sales_cost,areacode) VALUES(117,'18',TO_DATE('2018-03-01','yyyy-mm-dd'),220,'china' )
SELECT * FROM dual
--复制一份该表并创建表分区
CREATE TABLE sales_range
PARTITION BY  RANGE(sales_date)(
	PARTITION part1 VALUES less than (TO_DATE('2011-01-01','yyyy-mm-dd')),
	PARTITION part2 VALUES less than (TO_DATE('2012-01-01','yyyy-mm-dd')),
	PARTITION part3 VALUES less than (TO_DATE('2013-01-01','yyyy-mm-dd')),
	PARTITION part4 VALUES less than (MAXVALUE)
)
AS SELECT * FROM sales;
--查询分区数据
SELECT * FROM sales_range PARTITION(part4);

分表场景:

• 一张表的查询速度已经影响到使用的时候
• SQL经过优化
• 数据量过大
• 当频繁插入或者联合查询时，速度变慢

分表解决的问题:

分表后，单表的并发能力提高了，磁盘I/O性能也提高了，写操作效率提高了

• 查询一次的时间短了
• 数据分布在不同的文件，磁盘I/O性能提高
• 读写锁影响的数据量变小
• 插入数据库需要重新建立索引的数据减少

分表的实现方式:（较为复杂)

需要业务系统配合迁移升级，工作量大

分区和分表的区别和联系:

• 分区和分表的目的都是减少数据库的负担，提高表的增删改查效率
• 分区只是一张表中的数据的存储位置发生改变，分表是将一张表分成多张表
• 但访问量大，且表数据较大时，两种方式可以相互配合使用
• 当访问量不大，但表数据较多时，可以只进行分区。

常见分区分表的对着策略:

• Range(范围)
• Hash（哈希)
• 按照时间拆分
• Hash之后按照分表个数取模
• 在认证库中保存数据库配置，就是建立一个DB,这个DB单独保存user_id到DB的映射关系

数据存储的进化历史：

• 单库单表
• 单库多表
• 多库多表

优先考虑分区，当分区不能满足要求时，开始考虑分表，合理的分表对效率的提升会优于分区

京东商品评价存储设计。背景：

• 商品的评论数量:数十亿条
• 每天的服务调用:数十亿次
• 每年成倍增长

整体的数据的存储：基础数据存储，文本存储

基础数据存储：

MySQL：只存储非文本的基础信息，包括：评论状态、用户、时间等基础数据。以及图片、标签、点赞等附加信息。数据组织形式(不同的数据又可选择不同的库表拆分方案):

• 评论基础数据按用户ID进行拆库并拆表
• 图片及标签处于同一数据库下，根据商品编号分别进行拆表
• 其他的扩展信息数据，因数据量不大，访问量不高，处理于同一库下且不做分表即可

文本存储:

文本存储(评论的内容)使用了mongodb 、 hbase

• 选择nosql而非MySQL
• 减轻了MySQL存储压力，释放MySQL，庞大的存储也有了可靠的保障
• nosql的高性能读写大大提升了系统的吞吐量并降低了延迟

数据分片:

在分布式存储系统中，数据需要分散存储在多台设备上，数据分片(Sharding)就是用来确定数据在多台存储设备上分布的技术。数据分片要达到三个目的:

1. 分布均匀，即每台设备上的数据量要尽可能相近。
2. 负载均衡，即每台设备上的请求量要尽可能相近。
3. 扩缩容时产生的数据迁移尽可能少.

路漫漫其修远兮，吾将上下而求索，希望此篇文章对大家有所帮助……….