文章正文
mysql-分区表-实践
【文章】2020-04-23
简介mysql-分区表-实践
-- 注意：
-- 当更新数据时，不会更新数据所在分区
-- 取消分区，对数据没影响
-- 删除分区，同时会删除数据
-- primary key和unique key必须包含在分区key的一部分
-- 取消分区
alter table nc_order remove partitioning;
-- 合并
alter table nc_order reorganize partition pn1,p5 into (
partition px values less than (TO_DAYS('2020-03-12'))
);
-- 重定义分区（拆分）
Alter table nc_order partition by range(TO_DAYS(gmt_create))
(
partition p1 values less than (TO_DAYS('2020-03-12')),
partition p2 values less than (TO_DAYS('2020-03-13')),
partition p0 values less than (MAXVALUE)
);
-- 查询详情分区
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM information_schema.partitions WHERE
table_schema = SCHEMA()
AND table_name='nc_order';
-- 查看分区数据
SELECT count(*) FROM nc_order PARTITION (p1)
-- 查看分区文件
show variables like '%datadir%';
-- 查看数据库版本
select version();
-- 取消分区
alter table nc_order remove partitioning;
-- 测试语句
explain partitions select * from nc_order where gmt_create <= '2020-03-11 23:59:59';
select * from nc_order where gmt_create >= '2020-03-05 00:00:00' and gmt_create<='2020-03-11 23:59:59' LIMIT 1000,10;
SELECT * from test_log;
-- 取消分区
alter table test_log remove partitioning;
--
Alter table test_log partition by range COLUMNS(xtime)
(
partition p11 values less than ('20200312'),
partition p12 values less than ('20200313')
);
Alter table test_log PARTITION BY RANGE COLUMNS(xtime) (
PARTITION p1 VALUES LESS THAN ( '20151202' ),
PARTITION p2 VALUES LESS THAN ( '20151203' ),
PARTITION p3 VALUES LESS THAN ( '20151204' ),
PARTITION p4 VALUES LESS THAN ( '20151205' ),
PARTITION p5 VALUES LESS THAN ( '20151206' ),
PARTITION p6 VALUES LESS THAN ( '20151207' ),
PARTITION p7 VALUES LESS THAN ( '20151208' ),
PARTITION p8 VALUES LESS THAN ( '20151209' ),
PARTITION p9 VALUES LESS THAN ( '20151210' ),
PARTITION p10 VALUES LESS THAN ('20151211' )
);
select day(CURRENT_DATE) AS xx
-- 查看所有分区
SELECT
partition_name part,
partition_expression expr,
partition_description descr
FROM information_schema.partitions WHERE
table_schema = SCHEMA()
AND table_name='test_log';
-- 插入数据
insert into test_log values(null,'2020-03-12 10:11:13', 'hi');
insert into test_log values(null,'2020-03-13 10:12:10', 'ni');
insert into test_log values(null,'2020-03-14 10:12:10', 'hao');
-- 查看分区数据
SELECT * FROM test_log PARTITION (p11);
SELECT * FROM test_log PARTITION (p12);
SELECT * FROM test_log PARTITION (pmax);
-- 测试list分区
DROP TABLE test_list;
CREATE TABLE `test_list` (
`id` bigint(20) NOT NULL PRIMARY KEY,
sort_id BIGINT(20),
`xtime` datetime NOT NULL
)
--
DESC test_list;
ALTER TABLE test_list drop PRIMARY KEY;
DESC test_list;
-- drop INDEX xx ON tablename
-- 复合主键
alter table test_list add primary key (id,sort_id);
alter table test_list modify id int auto_increment
--
Alter table test_list partition by list(sort_id)(
PARTITION p1 values in (1),
PARTITION p2 values in (2),
PARTITION p3 values in (3),
PARTITION p4 values in (4)
)
--
insert into test_list values(null,1,'2020-03-01 10:11:13');
insert into test_list values(null,2,'2020-03-02 10:12:10');
insert into test_list values(null,0,'2020-03-03 10:12:10');
insert into test_list values(null,1,'2020-03-01 10:11:13');
insert into test_list values(null,2,'2020-03-02 10:12:10');
insert into test_list values(null,3,'2020-03-03 10:12:10');
insert into test_list values(null,3,'2020-03-03 10:12:10');
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM information_schema.partitions WHERE
table_schema = SCHEMA()
AND table_name='test_list';
SELECT * from test_list;
SELECT * FROM test_list PARTITION (p1);
SELECT * FROM test_list PARTITION (p2);
SELECT * FROM test_list PARTITION (p3);
SELECT * FROM test_list PARTITION (p4);
explain partitions select * from test_list where xtime >= '2020-03-02 00:00:00' and xtime<='2020-03-02 23:59:59' AND sort_id IN (1)
select * from test_list where xtime < '2020-03-02 00:00:00'
UPDATE test_list SET xtime = '2020-03-01 00:00:00' WHERE xtime>='2020-03-02 00:00:00'
-- 订单表
DROP TABLE nc_order_temp;
-- 备份表
ALTER TABLE nc_order RENAME TO nc_order_backup;
-- 创建临时表
CREATE TABLE `nc_order_temp` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`gmt_create` datetime NOT NULL COMMENT '创建时间' ,
`gmt_modified` datetime NULL DEFAULT NULL COMMENT '修改时间' ,
`gmt_back` datetime NULL DEFAULT NULL COMMENT '返回时间' ,
`uid` bigint(20) NOT NULL COMMENT '用户id' ,
`u_order` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户订单号' ,
`own_order` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '自有系统订单号' ,
`own_supplier_order` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上游平台的本系统订单号' ,
`supplier_order` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上游平台订单号' ,
`product_code` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品代码' ,
`chager_name` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '处理通道' ,
`product_type` smallint(6) NULL DEFAULT NULL COMMENT '产品类型（枚举 1：话费充值 2：流量充值）' ,
`customer_account` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '充值账号' ,
`use_time` int(11) NULL DEFAULT NULL COMMENT '充值时间（秒）' ,
`supplier_price` decimal(10,4) NULL DEFAULT NULL COMMENT '进价' ,
`sell_price` decimal(10,4) NULL DEFAULT NULL COMMENT '售价' ,
`profit` decimal(10,4) NULL DEFAULT NULL COMMENT '利润' ,
`order_unique_key` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`order_status` smallint(6) NULL DEFAULT NULL COMMENT '订单状态 (枚举 1：成功 2：失败 3：处理中 4....待扩展)' ,
`err_msg` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '错误信息（失败原因等）' ,
`admin_uid` bigint(20) NULL DEFAULT NULL COMMENT '管理人id' ,
`account_no` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资金流水号' ,
`is_delay` tinyint(1) NULL DEFAULT NULL COMMENT '是否为拖延订单' ,
`is_last` tinyint(1) NULL DEFAULT NULL COMMENT '是否为最新' ,
`is_recall` tinyint(1) NULL DEFAULT NULL COMMENT '是否为返销订单' ,
`check_status` tinyint(4) NULL DEFAULT 1 COMMENT '核单状态' ,
`remark` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注' ,
`area_code` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '区域' ,
`parvalue` int(11) NULL DEFAULT NULL COMMENT '面额' ,
`operator` tinyint(4) NULL DEFAULT NULL COMMENT '运营商' ,
`part_key` int(11) NOT NULL COMMENT '分区' ,
PRIMARY KEY (`id`),
UNIQUE INDEX `u_order_unique` (`u_order`, `uid`) USING BTREE ,
INDEX `idx_own_order` (`own_order`) USING BTREE ,
INDEX `idx_gmt_create` (`gmt_create`) USING BTREE ,
INDEX `idx_customer_account` (`customer_account`) USING BTREE ,
INDEX `idx_product_code` (`product_code`) USING BTREE ,
INDEX `idx_uid` (`uid`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='order_订单表';
-- 导入数据到临时表
insert into nc_order_temp select id, gmt_create, gmt_modified, gmt_back, uid, u_order, own_order, own_supplier_order,
supplier_order, product_code, chager_name, product_type, customer_account, use_time,
supplier_price, sell_price, profit, order_unique_key, order_status, err_msg, admin_uid,
account_no,is_delay,is_last, remark,is_recall,check_status
,area_code,parvalue,operator,day(gmt_create) from nc_order_backup;
-- 临时表增加id、part_key复合主键
alter table nc_order_temp modify id int NOT null;
ALTER TABLE nc_order_temp drop PRIMARY KEY;
alter table nc_order_temp add primary key (id,part_key);
alter table nc_order_temp modify id int auto_increment;
DESC nc_order_temp;
-- 变更已有的唯一索引
ALTER TABLE `nc_order_temp`
DROP INDEX `u_order_unique` ,
ADD UNIQUE INDEX `u_order_unique` (`u_order`, `uid`, `part_key`) USING BTREE ;
-- 临时表建立分区
Alter table nc_order_temp partition by list(part_key)(
PARTITION p1 values in (1),
PARTITION p2 values in (2),
PARTITION p3 values in (3),
PARTITION p4 values in (4),
PARTITION p5 values in (5),
PARTITION p6 values in (6),
PARTITION p7 values in (7),
PARTITION p8 values in (8),
PARTITION p9 values in (9),
PARTITION p10 values in (10),
PARTITION p11 values in (11),
PARTITION p12 values in (12),
PARTITION p13 values in (13),
PARTITION p14 values in (14),
PARTITION p15 values in (15),
PARTITION p16 values in (16),
PARTITION p17 values in (17),
PARTITION p18 values in (18),
PARTITION p19 values in (19),
PARTITION p20 values in (20),
PARTITION p21 values in (21),
PARTITION p22 values in (22),
PARTITION p23 values in (23),
PARTITION p24 values in (24),
PARTITION p25 values in (25),
PARTITION p26 values in (26),
PARTITION p27 values in (27),
PARTITION p28 values in (28),
PARTITION p29 values in (29),
PARTITION p30 values in (30),
PARTITION p31 values in (31)
)
-- 查看所有分区
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM information_schema.partitions WHERE
table_schema = SCHEMA()
AND table_name='nc_order_temp';
-- 测试
explain partitions select * from nc_order_temp where gmt_create >= '2020-03-09 00:00:00' and gmt_create<='2020-03-09 23:59:59' AND part_key in (9)
-- 重命名临时表
ALTER TABLE nc_order_temp RENAME TO nc_order;
-- 账户明细表
DROP TABLE uc_account_detail_temp;
-- 备份
ALTER TABLE uc_account_detail RENAME TO uc_account_detail_backup;
-- 创建临时表
CREATE TABLE `uc_account_detail_temp` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id' ,
`gmt_create` datetime NULL DEFAULT NULL COMMENT '创建时间' ,
`gmt_modified` datetime NULL DEFAULT NULL COMMENT '修改时间' ,
`uid` bigint(20) NULL DEFAULT NULL COMMENT '用户iid' ,
`account_no` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资金变动流水号' ,
`dependent_order` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '相关订单号' ,
`type` smallint(6) NULL DEFAULT NULL COMMENT '资金变动类型' ,
`change_cash` decimal(12,4) NULL DEFAULT NULL COMMENT '变动金额' ,
`new_cash` decimal(12,4) NULL DEFAULT NULL COMMENT '变动后金额' ,
`remark` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注' ,
`part_key` int(11) NOT NULL ,
PRIMARY KEY (`id`),
INDEX `idx_dependent_order` (`dependent_order`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='账户记录表'
AUTO_INCREMENT=9309639
ROW_FORMAT=DYNAMIC
;
-- 导入数据到临时表
insert into uc_account_detail_temp select id,gmt_create,gmt_modified,uid,account_no,dependent_order,type,change_cash,new_cash,remark,day(gmt_create) from uc_account_detail_backup;
-- 临时表增加id、part_key复合主键
alter table uc_account_detail_temp modify id int NOT null;
ALTER TABLE uc_account_detail_temp drop PRIMARY KEY;
alter table uc_account_detail_temp add primary key (id,part_key);
alter table uc_account_detail_temp modify id int auto_increment;
DESC uc_account_detail_temp;
-- 临时表建立分区
Alter table uc_account_detail_temp partition by list(part_key)(
PARTITION p1 values in (1),
PARTITION p2 values in (2),
PARTITION p3 values in (3),
PARTITION p4 values in (4),
PARTITION p5 values in (5),
PARTITION p6 values in (6),
PARTITION p7 values in (7),
PARTITION p8 values in (8),
PARTITION p9 values in (9),
PARTITION p10 values in (10),
PARTITION p11 values in (11),
PARTITION p12 values in (12),
PARTITION p13 values in (13),
PARTITION p14 values in (14),
PARTITION p15 values in (15),
PARTITION p16 values in (16),
PARTITION p17 values in (17),
PARTITION p18 values in (18),
PARTITION p19 values in (19),
PARTITION p20 values in (20),
PARTITION p21 values in (21),
PARTITION p22 values in (22),
PARTITION p23 values in (23),
PARTITION p24 values in (24),
PARTITION p25 values in (25),
PARTITION p26 values in (26),
PARTITION p27 values in (27),
PARTITION p28 values in (28),
PARTITION p29 values in (29),
PARTITION p30 values in (30),
PARTITION p31 values in (31)
)
-- 查看所有分区
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM information_schema.partitions WHERE
table_schema = SCHEMA()
AND table_name='uc_account_detail_temp';
-- 测试
explain partitions select * from uc_account_detail_temp where gmt_create >= '2020-03-09 00:00:00' and gmt_create<='2020-03-09 23:59:59' AND part_key in (9)
-- 重命名临时表
ALTER TABLE uc_account_detail_temp RENAME TO uc_account_detail;
打赏支持
感谢您的支持，加油!
打开微信扫码打赏，你说多少就多少
找书费时，联系客服快速获取!
在线客服8:30-22:30，若离线请留言！
获取教程，请联系在线客服!
在线客服8:30-22:30，若离线请留言！
热门阅读
-
生态学（第四版） 杨持 高等教育出版社 PDF 9787040595628 出版时间:2023年08月.pdf
生态学（第四版） 杨持 高等教育出版社 PDF 9787040595628 出版时间:2023年08月.pdf ...
-
史前的现代化 中国农业起源过程的文化生态考察 陈胜前著 北京：科学出版社 PDF 9787030397263 2013.pdf
史前的现代化 中国农业起源过程的文化生态考察 陈胜前著 北京：科学出版社 PDF 9787030397263 2013.pdf ...
-
股票大作手操盘术 融合时间和价格的利弗莫尔准则 （美）利弗莫尔著；丁圣元译 北京：人民邮电出版社 PDF 9787115292360 2012.pdf
股票大作手操盘术 融合时间和价格的利弗莫尔准则 （美）利弗莫尔著；丁圣元译 北京：人民邮电出版社 PDF 9787115292360 2012.pdf ...
-
期货市场技术分析——期（现）货市场、股票市场、外汇市场、利率 （美）约翰·墨菲 丁圣元译 地震出版社 PDF 9787548282228 出版时间：1994.pdf
期货市场技术分析——期（现）货市场、股票市场、外汇市场、利率 （美）约翰·墨菲 丁圣元译 地震出版社 PDF 9787548282228 出版时间：1994.pdf ...
-
证券业从业人员资格考试教材 证券市场基础知识 中国证券业协会编 北京：中国金融出版社 PDF 9787504963819 2012.pdf
证券业从业人员资格考试教材 证券市场基础知识 中国证券业协会编 北京：中国金融出版社 PDF 9787504963819 2012.pdf ...
-
近世代数基础 修订本 张禾瑞著 北京：高等教育出版社 PDF 7040012227 2005.pdf
近世代数基础 修订本 张禾瑞著 北京：高等教育出版社 PDF 7040012227 2005.pdf ...
-
广联达BIM算量大赛实训图集 朱溢镕、王全杰主编 化学工业出版社 PDF 9787122234353 出版时间:2015年06月.pdf
广联达BIM算量大赛实训图集 朱溢镕、王全杰主编 化学工业出版社 PDF 9787122234353 出版时间:2015年06月.pdf ...
-
中国蜻蜓大图鉴 下 张浩淼著 重庆：重庆大学出版社 PDF 9787568910378 出版时间:2019.pdf
中国蜻蜓大图鉴 下 张浩淼著 重庆：重庆大学出版社 PDF 9787568910378 出版时间:2019.pdf ...
-
懂老公的女人最幸福大全集 超值金版 静涛，袁钰编著 上海：立信会计出版社 PDF 9787542936912 2012.pdf
懂老公的女人最幸福大全集 超值金版 静涛，袁钰编著 上海：立信会计出版社 PDF 9787542936912 2012.pdf ...
-
炼金术 伟大的奥秘 （意）Andrea Aromatico原著；李晓桦译 上海：上海书店出版社 PDF 7806229884 2002.pdf
炼金术 伟大的奥秘 （意）Andrea Aromatico原著；李晓桦译 上海：上海书店出版社 PDF 7806229884 2002.pdf ...
找PDF电子书，太费时间?
- 微信扫描二维码，让客服快速查找。
在线客服8:30-22:00，若离线请留言！
