關(guān)于什么是分區(qū)和分表启上，可以先參考下面前兩篇文章氢架。
MySQL 分庫(kù)分表與分區(qū)的區(qū)別和思考
搞懂MySQL分區(qū)
MySQL分區(qū)表的真正作用
分區(qū)原理和優(yōu)缺點(diǎn)
分區(qū)是同一表中不同行的記錄分配到不同的物理文件中
我們具體是否要使用分區(qū)鹏溯，需要考慮幾點(diǎn)
- 什么場(chǎng)景使用分區(qū)
- 分區(qū)是基于mysql底層的實(shí)現(xiàn)旺罢，不影響業(yè)務(wù)系冗。分表可以支持?jǐn)?shù)據(jù)量大的表進(jìn)行分拆奕扣，但是需要我們?cè)跇I(yè)務(wù)端進(jìn)行支持
- 我們現(xiàn)在有個(gè)表，目前的數(shù)據(jù)量在2億+掌敬，隨著業(yè)務(wù)的發(fā)展惯豆，每天新增的數(shù)據(jù)至少在500W+
- 首先單機(jī)的性能和容量足夠，完全基于內(nèi)網(wǎng)的訪問(wèn)奔害，沒(méi)有太大的讀并發(fā)楷兽，但是需要保證單語(yǔ)句查詢的速度
- 每天的新增數(shù)據(jù)太大，我么先考慮按天來(lái)進(jìn)行分區(qū)华临，分區(qū)后的所有區(qū)隔離芯杀，我們場(chǎng)景也不需要經(jīng)常的跨天訪問(wèn)
- 分區(qū)后性能效果如何，能否解決問(wèn)題
- 我們首先使用部分?jǐn)?shù)據(jù)測(cè)試雅潭，測(cè)試表1900W+數(shù)據(jù)揭厚，加入分區(qū)
select *
from table
where s.start_time BETWEEN "2019-10-18 00:00:01" and "2019-10-18 23:59:59"
我們按照天來(lái)分區(qū)，一天的數(shù)據(jù)25W左右
-
分區(qū)前
image.png -
分區(qū)后
image.png
初步查看扶供，效果明顯筛圆。
問(wèn)題？
- 在插入數(shù)據(jù)的時(shí)候椿浓，缺少對(duì)應(yīng)的分區(qū)會(huì)插入失敗
- 可以設(shè)置maxValue進(jìn)行容錯(cuò)（也就是可以接收所有數(shù)據(jù)的分區(qū)）顽染，避免異常的數(shù)據(jù)操作
- 分區(qū)后如何恢復(fù)？
- 分區(qū)后的數(shù)據(jù)分到了不同的物理文件轰绵，不同分區(qū)數(shù)據(jù)和索引都是獨(dú)立的粉寞，刪除分區(qū)就會(huì)刪除對(duì)應(yīng)的數(shù)據(jù)。
- 想恢復(fù)不分區(qū)左腔，可以考慮創(chuàng)建結(jié)構(gòu)相同的表遷移數(shù)據(jù)
- 分區(qū)后的跨區(qū)查詢速度如何唧垦？
接下來(lái) - 新插入分區(qū)的值上限，需要比目前的大液样，所以得考慮后續(xù)數(shù)據(jù)量的增長(zhǎng)問(wèn)題振亮，合理的設(shè)置分區(qū)值
分區(qū)操作語(yǔ)句
查看分區(qū)情況
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='stb_traffic_info_lane';
添加分區(qū)
ALTER table stb_traffic_info_lane
add partition (partition p20200726 VALUES LESS THAN (737995) ENGINE = InnoDB);
刪除分區(qū)
alter table stb_traffic_info_lane drop partition p20200726;
創(chuàng)建表巧还，增加分區(qū)。數(shù)據(jù)從現(xiàn)有的表拷貝過(guò)來(lái)
CREATE TABLE `stb_traffic_info_lane_2` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`start_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`end_time` datetime DEFAULT NULL,
`remarks` varchar(12800) DEFAULT NULL,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
PRIMARY KEY (`id`,`start_time`),
KEY `index_1` (`index_id`,`time_type`,`start_time`,`end_time`,`cross_id`,`branch_id`,`lane_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(start_time))
PARTITIONS 2(
PARTITION p20191018 VALUES LESS THAN (TO_DAYS('20191018')) ENGINE = INNODB,
PARTITION p20191019 VALUES LESS THAN (TO_DAYS('20191019')) ENGINE = INNODB
)
從其他表遷移數(shù)據(jù)
insert into `stb_traffic_info_lane_2`
select * from `stb_traffic_info_lane_1`
