文章正文
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 7502339469 出版时间:2002.pdf
家庭花卉繁殖图解 王琦主编 北京：科学技术文献出版社 PDF 7502339469 出版时间:2002.pdf ...
-
基因伦理学 人的繁殖技术化带来的问题 （德）库尔特·拜尔茨（Kurt Bayertz）著；马怀琪译 北京：华夏出版社 PDF 7508022513 2000.pdf
基因伦理学 人的繁殖技术化带来的问题 （德）库尔特·拜尔茨（Kurt Bayertz）著；马怀琪译 北京：华夏出版社 PDF 7508022513 2000.pdf ...
-
动物繁殖学实验教程 第2版 王锋著 中国农业大学出版社 PDF 9787565518461 2017.pdf
动物繁殖学实验教程 第2版 王锋著 中国农业大学出版社 PDF 9787565518461 2017.pdf ...
-
地球上的性 动物繁殖那些事 （英）朱尔斯·霍华德著 北京：商务印书馆 PDF 7100165860 2019.pdf
地球上的性 动物繁殖那些事 （英）朱尔斯·霍华德著 北京：商务印书馆 PDF 7100165860 2019.pdf ...
-
清代学术概论儒家哲学 梁启超 天津古籍出版社 PDF 679229100142.pdf
清代学术概论儒家哲学 梁启超 天津古籍出版社 PDF 679229100142.pdf ...
-
民国学术经典文库 清代学术概论 梁启超 北京：东方出版社 PDF 7506007371 1996.pdf
民国学术经典文库 清代学术概论 梁启超 北京：东方出版社 PDF 7506007371 1996.pdf ...
-
唐代政治史述论稿 陈寅恪著 北京：生活·读书·新知三联书店 PDF 7108009412 出版时间:1956.pdf
唐代政治史述论稿 陈寅恪著 北京：生活·读书·新知三联书店 PDF 7108009412 出版时间:1956.pdf ...
-
进阶式对外汉语系列教材：成功之路·进步篇3 牟世荣 北京语言大学出版社 9787561923863 牟世荣 编 PDF 出版时间:2009年07月.pdf
进阶式对外汉语系列教材：成功之路·进步篇3 牟世荣 北京语言大学出版社 9787561923863 牟世荣 编 PDF 出版时间:2009年07月.pdf ...
-
清代学术概论 梁启超著；夏晓虹点校 北京：中国人民大学出版社 PDF 7300058825 2004.pdf
清代学术概论 梁启超著；夏晓虹点校 北京：中国人民大学出版社 PDF 7300058825 2004.pdf ...
-
成功之路 进步篇 第2册 张辉编著 北京：北京语言大学出版社 PDF 9787561922095 2008.pdf
成功之路 进步篇 第2册 张辉编著 北京：北京语言大学出版社 PDF 9787561922095 2008.pdf ...
找PDF电子书，太费时间?
- 微信扫描二维码，让客服快速查找。
在线客服8:30-22:00，若离线请留言！
