mysql分区分表⾯试题_Mysql分区和分表
⼀、概念
1.为什么要分表和分区？
⽇常开发中我们经常会遇到⼤表的情况，所谓的⼤表是指存储了百万级乃⾄千万级条记录的表。这样的表过于庞⼤，导致数据库在查询和插⼊的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的⽬的就是减少数据库的负担，提⾼数据库的效率，通常点来讲就是提⾼表的增删改查效率。
2.什么是分表？
分表是将⼀个⼤表按照⼀定的规则分解成多张具有独⽴存储空间的实体表，我们可以称为⼦表，每个表都对应三个⽂件，MYD数据⽂件，.MYI索引⽂件，.frm表结构⽂件。这些⼦表可以分布在同⼀块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的⼦表名，然后去操作它。
3.什么是分区？
分区和分表相似，都是按照规则分解表。不同在于分表将⼤表分解为若⼲个独⽴的实体表，⽽分区是将数据分段划分在多个位置存放，可以是同⼀块磁盘也可以在不同的机器。分区后，表⾯上还是⼀张表，但数据散列到多个位置了。app读写的时候操作的还是⼤表名字，db⾃动去组织分区的数据。
mysql面试题sql(1)都能提⾼mysql的性⾼，在⾼并发状态下都有⼀个良好的表现。
(2)分表和分区不⽭盾，可以相互配合的，对于那些⼤访问量，并且表数据⽐较多的表，我们可以采取分表和分区结合的⽅式(如果merge这种分表⽅式，不能和分区配合的话，可以⽤其他的分表试)，访问量不⼤，但是表数据很多的表，我们可以采取分区的⽅式等。
(3)分表技术是⽐较⿇烦的，需要⼿动去创建⼦表，app服务端读写时候需要计算⼦表名。采⽤merge好⼀些，但也要创建⼦表和配置⼦表间的union关系。
(4)表分区相对于分表，操作⽅便，不需要创建⼦表。
⼆、分区
1.分区的类型：cnc编程培训学校
(1)Range：把连续区间按范围划分
例：
create table user(
id int(11),
money int(11) unsigned not null,
date datetime
)
partition by range(YEAR(date))(
partition p2014 values less than (2015),
partition p2015 values less than (2016),
partition p2016 values less than (2017),
partition p2017 values less than maxvalue
);
(2)List：把离散值分成集合，按集合划分，适合有固定取值列的表
例：
create table user(
a int(11),
b int(11)
)
partition by list(b)(
partition p0 values in (1,3,5,7,9),
partition p1 values in (2,4,6,8,0)
);
(3)Hash：随机分配，分区数固定
例：
create table user(
a int(11),
b datetime
)
partition by hash(YEAR(b))
partitions 4;
(4)Key：类似Hash，区别是只⽀持1列或多列，且mysql提供⾃⾝的Hash函数例：
create table user(
a int(11),
b datetime
)
partition by key(b)
partitions 4;jquery ajax load操作
2.分区管理
(1)新增分区
ALTER TABLE sale_data
ADD PARTITION (PARTITION p201710 VALUES LESS THAN (201711));
(2)删除分区
--当删除了⼀个分区，也同时删除了该分区中所有的数据。
ALTER TABLE sale_data DROP PARTITION p201710;
(3)分区的合并
下⾯的SQL，将p201701 - p201709 合并为3个分区p2017Q1 - p2017Q3
ALTER TABLE sale_data
REORGANIZE PARTITION p201701,p201702,p201703,
p201704,p201705,p201706,
p201707,p201708,p201709 INTO
(
PARTITION p2017Q1 VALUES LESS THAN (201704),
PARTITION p2017Q2 VALUES LESS THAN (201707),
PARTITION p2017Q3 VALUES LESS THAN (201710)
);
3.分区应该注意的事项：
(1)做分区时，要么不定义主键，要么把分区字段加⼊到主键中。
it培训机构培训出来的人(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)对应式
对于版块数量不多，⽽且较为固定的形式，就直接对应就好。⽐如新闻版块，可以分出新闻的⽬录表，新闻的⽂章表等。
c语言printf报错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+时间戳，单纯的时间戳或⾃动编号也能⽤，但查询时要记得带上⽤户名⽤于定位表。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。