| ex_order_total | CREATE TABLE ex_order_total (
id bigint(20) NOT NULL COMMENT ‘ID’,
order_id bigint(20) NOT NULL COMMENT ‘子订单委托单ID’,
symbol varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’ COMMENT ‘币对’,
base varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’ COMMENT ‘基准货币，symbol的前半段’,
quote varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’ COMMENT ‘计价货币，symbol的后半段’,
user_id int(11) unsigned NOT NULL COMMENT ‘用户id’,
status tinyint(3) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘订单状态：0 init，1 new，2 filled，3 part_filled，4 canceled，5 pending_cancel，6 expired’,
type tinyint(3) unsigned NOT NULL DEFAULT ‘1’ COMMENT ‘委托类型：1 limit，2 market，3 stop’,
ctime timestamp NOT NULL COMMENT ‘子订单创建时间’,
mtime timestamp NOT NULL COMMENT ‘子订单更新时间’,
source tinyint(3) unsigned NOT NULL DEFAULT ‘1’ COMMENT ‘订单来源：1web，2app，3api’,
order_type tinyint(3) NOT NULL DEFAULT ‘1’ COMMENT ‘订单类型1:常规订单，2 杠杆订单’,
created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
finger_print varchar(32) DEFAULT ‘’ COMMENT ‘防篡改指纹’,
month_code int(11) unsigned NOT NULL COMMENT ‘所处月份，用户分区’,
PRIMARY KEY (id,month_code) /*T![clustered_index] NONCLUSTERED */,
UNIQUE KEY uniq_orderid_symbol (order_id,symbol,month_code),
KEY idx_ctime (ctime),
KEY idx_status (status),
KEY idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=‘委托订单汇总表’
PARTITION BY RANGE (month_code)
(PARTITION p1 VALUES LESS THAN (2),
PARTITION p2 VALUES LESS THAN (3),
PARTITION p3 VALUES LESS THAN (4),
PARTITION p4 VALUES LESS THAN (5),
PARTITION p5 VALUES LESS THAN (6),
PARTITION p6 VALUES LESS THAN (7),
PARTITION p7 VALUES LESS THAN (8),
PARTITION p8 VALUES LESS THAN (9),
PARTITION p9 VALUES LESS THAN (10),
PARTITION p10 VALUES LESS THAN (11),
PARTITION p11 VALUES LESS THAN (12),
PARTITION p12 VALUES LESS THAN (MAXVALUE)) |
比如这个按月分区表
要求最近2个月的数据放在热存储中。你就可以把 PARTITION p7 放在ssd分区上。做冷热分离 把 PARTITION p6 放回hhd上