目录
一、数据库分区
1. 定义
数据库分区是将数据量大的表的数据均摊到不同的硬盘、系统或不同服务器存储介质中(将数据从物理上分成若干个小表存储),实际上还是一张表。
将表的数据均衡到不同的地方,为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。提升查询效率。
2. 优点
1、相对于单个文件系统或是硬盘,分区可以存储更多的数据;
2、数据管理比较方便,比如要清理或废弃某年的数据,就可以直接删除该日期的分区数据即可;
3、精准定位分区查询数据,不需要全表扫描查询,大大提高数据检索效率;
4、可跨多个分区磁盘查询,来提高查询的吞吐量;
5、在涉及聚合函数查询时,可以很容易进行数据的合并;
3. 分类
3.1水平分区
背景:数据量庞大
介绍:对表的行进行分区,不同物理不同分组里面的物理分割数据集得以组合,从而进行个体分割或集体分割。所有在表中定义的列在每个数据中都能找到。所以表的特性依然得以保持。(每行数据的字段不减少)
3.2垂直分区
背景:每行数据字段多,但有些字段包含大text且不经常被访问,这些字段就需要被切割出去。
介绍:对表的垂直划分来减少目标表的宽度,使某些特定的列被划分到特定的分区,每个列都包含了其中的列所对应的行。
4. 使用场景
一张表的查询速度已经慢到影响使用;
SQL经过优化还是很慢;
数据量大;
表中的数据是分段的;
对数据的操作往往只涉及一部分,而不是所有的数据。
如下为比较常用的按时间创建range分区脚本:
#按年创建分区
PARTITION BY RANGE (YEAR(timestamp)) (
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
#按年和月创建分区
PARTITION BY RANGE (UNIX_TIMESTAMP(timestamp)) (
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2014-08-01 00:00:00')),
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-01 00:00:00')),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2015-02-01 00:00:00'))
);
二、数据库分表
1. 定义
就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明,然后操作它。
2. 作用
减少查询时间
磁盘I/O性能提高(数据分布在不同的文件)
读写锁影响的数据量小
插入数据库需要建立索引的数据减少
3. 使用场景
一张表的查询速度慢到影响使用时;
SQL经过优化;
数据量大;
当插入数据或联合查询速度变慢时。
4. 分区和分表的区别
目的都是减少数据库的负担,提高表的增删改查效率。
分区只是一张表中的数据的存储位置发生改变,分表是将一张表分成多张表。
当访问量大且表数据比较大时,两种方式可以互相配合使用。
当访问量不大,但表数据比较多时,可以只进行分区。
三、数据库分库
1. 目的
为突破单节点数据库服务器的I/O能力限制,解决数据库扩展性问题。
2. 水平拆分
将一张表分成多张表并存放在不同的数据库中。
问题:
影响主键自增(可以使用非自增主键,如uuid、雪花算法生成id);
单表查询会变成多表(如count函数操作)
3. 垂直拆分
将系统中不存在关联关系或需要join的表可以放在不同数据库不同的服务器中。
问题:
ACID被打破;
Join操作困难;
外键约束受到影响。
4. 应用背景
单台DB的存储空间不够
随着查询量的增加,单台数据库服务器已经没法支撑
垂直分库-->水平分库-->读写分离
四、使用建议
优先考虑分区,当分区不能满足需求时,开始考虑分表,合理的分表对效率的提升会优于分区。