分表和分区比较
分区比较简单,由数据库自身维护数据关系。
分表比分区更复杂,但是性能稍微好一点点。
但分表会造成业务代码更复杂,要通过代码指定数据存储到特定的表。
错误的分表操作,容易引发其他问题
数据库分库(物理层面进行拆分)
跨库Join的问题,虽然可以通过基础表多数据库同步复制来解决,但也增加了复杂度。
不同业务的拆分:编程复杂(根据业务选择对应的数据库),做关联业务联级操作的时候,有分布式事务的问题。
同一类业务的拆分:编程复杂,尤其在涉及到复杂业务查询时,有可能需要多库多次查询,然后进行结果集合并。
MySQL 分区、分表、分库
1. 分区、分表、分库都可以大幅提升数据库读的性能。
2. 数据库分库可以提升并发写的速度:通过简单的hash取余、日期时间、或业务区域等等,来拆分同一类业务数据(如订单数据)。或者通过不同业务领域,来拆分不同业务数据(商品、客户、订单、采购等等)。
3. 分区最简单,由数据库自身维护数据关系;
分表复杂,需要开发人员指定数据读写在哪张子表;
分库最复杂,除了为对应的数据选择对应的数据库以外,还需要解决跨库的分布式事务问题。
4. 分区、分表、分库并不冲突,比如在读写分离的业务场景,对读的数据库的某些表进行分区或者分表。或者对于一些容易编程的业务表采用分表,编程复杂的业务用分区,都是可行的。对于那些大访问量,并且表数据比较多的表,我们可以采取分表和分区结合的方式(如果merge这种分表方式,不能和分区配合的话,可以用其他的分表试),访问量不大,但是表数据很多的表,我们可以采取分区的方式等。
分区
1.分区的类型:
(1)Range:把连续区间按范围划分
例:
create table user(
idint(11),money int(11) unsigned not null,
datedatetime)
partitionby range(YEAR(date))(
partition p2014values less than (2015),
partition p2015values less than (2016),
partition p2016values less than (2017),
partition p2017valuesless than maxvalue
);
(2)List:把离散值分成集合,按集合划分,适合有固定取值列的表
例:
create table user(
aint(11),
bint(11)
)
partitionbylist(b)(
partition p0values in (1,3,5,7,9),
partition p1values in (2,4,6,8,0)
);
(3)Hash:随机分配,分区数固定
例:
create table user(
aint(11),
bdatetime)
partitionby hash(YEAR(b))
partitions4;
(4)Key:类似Hash,区别是只支持1列或多列,且mysql提供自身的Hash函数
例:
create table user(
aint(11),
bdatetime)
partitionby key(b)
partitions4;
2.分区管理
(1)新增分区
ALTER TABLEsale_dataADD PARTITION (PARTITION p201710 VALUES LESS THAN (201711));
(2)删除分区
--当删除了一个分区,也同时删除了该分区中所有的数据。
ALTER TABLE sale_data DROP PARTITION p201710;
(3)分区的合并
下面的SQL,将p201701 - p201709 合并为3个分区p2017Q1 - p2017Q3
ALTER TABLEsale_data
REORGANIZE PARTITION p201701,p201702,p201703,
p201704,p201705,p201706,
p201707,p201708,p201709INTO(
PARTITION p2017Q1VALUES LESS THAN (201704),
PARTITION p2017Q2VALUES LESS THAN (201707),
PARTITION p2017Q3VALUES LESS THAN (201710)
);
3.分区应该注意的事项:
(1)做分区时,要么不定义主键,要么把分区字段加入到主键中。
(2)分区字段不能为NULL,要不然怎么确定分区范围呢,所以尽量NOT NULL
分表
1.垂直分表
把原来有很多列的表拆分成多个表,原则是:
(1)把常用、不常用的字段分开放
(2)把大字段独立存放在一个表中
2.水平分表
为了解决单表数据量过大的问题,每个水平拆分表的结构完全一致。
例:
(1)按时间结构
如果业务系统对时效性较高,比如新闻发布系统的文章表,可以把数据库设计成时间结构,按时间分有几种结构:
(a)平板式
表类似:
article_201701
article_201702
article_201703
用年来分还是用月可自定,但用日期的话表就太多了,也没这必要。一般建议是按月分就可以。
这种分法,其难处在于,假设我要列20条数据,结果这三张表里都有2条,那么业务上很有可能要求读三次表。如果时间长了,有几十张表,而每张表是0条,那不就是要读完整个系统的表才行么?另外这个结构,要作分页是比较难实现的。
主键:在这个系统中,主键是13位带毫秒的时间戳,不要用自动编号,否则难以通过主键定位到表,也可以在查询时带上时间,但比较烦琐。
(b)归档式
表类似:
article_old
article_new
为了解决平板式的缺点,可以采用时间归档式设计,可以看到这个系统只有两张表。一张是旧文章表,一张是新文章表,新文章表放2个月的信息,每天定期把2
个月中的最早一天的文章归入旧表中。这样一方面可以解决性能问题,因为一般新闻发布系统读取的都是新的内容,旧的内容读取少;第二可以委婉地解决功能问
题,比如平板式所说的问题,在归档式中最多也只需要读2张表就完成了。
归档式的缺点在于旧表容量还是相对比较大,如果业务允许,可对旧表中的超旧内容进行再归档或直接清理掉。
(2)按版块结构
如果按照文章的所属版块进行拆表,比如新闻、体育版块拆表,一方面可以使每个表数据量分离,另一方面是各版块之间相互影响可降到最低。假如新闻版块的数据表损坏或需要维护,并不会影响到体育版块的正常工作,从而降低了风险。版块结构同时常用于bbs这样的系统。
板块结构也有几种分法:
(a)对应式
对于版块数量不多,而且较为固定的形式,就直接对应就好。比如新闻版块,可以分出新闻的目录表,新闻的文章表等。
news_category
news_article
sports_category
sports_article
可看到每一个版块都对应着一组相同的表结构,好处就是一目了然。在功能上,因为版块之间还是有一些隔阂,所以需要联合查询的需求不多,开发上比时间结构的方式要轻松。
主键:依旧要考虑的,在这个系统中,主键是版块+时间戳,单纯的时间戳或自动编号也能用,查询时要记得带上版块用于定位表。
(b)冷热式
对应式的缺点是,如果版块数量很大而且不确定,那要分出的表数量就太多了。举个例子:百度贴吧,如果按一个词条一个表设计,那得有多少张表呢?
用这样的方式吧。
tieba_汽车
tieba_飞机
tieba_火箭
tieba_unite
这个表汽车、火箭表是属于热门表,定义为新建的版块放在unite表里面,待到其超过一万张主贴的时候才开对应表结构。因为在贴吧这种系统中,冷门版块
肯定比热门版块多得多,这些冷门版块通常只有几张帖子,为它们开表也太浪费了;同时热门版块数量和访问量等,又比冷门版块多得多,非常有特点。
unite表还可以扩展成哈希表,利用词条的md5编码,可以分成n张表,我算了一下,md5前一位可分36张表,两位即是1296张表,足够了。
tieba_unite_ab
tieba_unite_ac
(3)按哈希结构
哈希结构通常用于博客之类的基于用户的场合,在博客这样的系统里有几个特点,1是用户数量非常多,2是每个用户发的文章数量都较少,3是用户发文章不定
期,4是每个用户发得不多,但总量仍非常之大。基于这些特点,用以上所说的任何一种分表方式都不合适,一没有固定的时效不宜用时间拆,二用户很多,而且还
偏偏都是冷门,所以也不宜用版块(用户)拆。
哈希结构在上面有所提及,既然按每个用户不好直接拆,那就把一群用户归进一个表好了。
blog_aa
blog_ab
blog_ac
如上所说,md5取前两位哈希可以达到1296张表,如果觉得不够,那就再加一位,总数可达46656张表,还不够?
表的数量太多,要创建这些表也是挺麻烦的,可以考虑在程序里往数据库insert之前,多执行一句判断表存在与否并创建表的语句,很实用,消耗也并不很大。
主键:依旧要考虑的,在这个系统中,主键是用户ID+时间戳,单纯的时间戳或自动编号也能用,但查询时要记得带上用户名用于定位表。
参考资料: