MySQL分区表、HBase 融合秘术

纯手工打造每一篇开源资讯与技术干货，数十万程序员和Linuxer已经关注。

其实该不该用，我也不能给予很好的建议。还是那句话，觉得适合自己才是最好的。觉得自己可以搞定分区表那就用。

1. 好很好的使用分区表就需要做好对开发人员培训的准备，让他们知道要怎么样才能很好的使用分区表。

2. 最好需要有一个自动化的计划，定时的自动处理分区的问题。

3. DELETE 删除数据不会释放磁盘空间，DROP PARITION 会释放磁盘空间，这样节省了空间的同时，也不会因为delete标记数据过多带来性能问题。

创建 MySQL 分区数据

DROP TABLE ord_order;

-- 创建订单分区表
CREATE TABLE ord_order(
order_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
user_id INT NOT NULL COMMENT '用户ID',
goods_id INT NOT NULL COMMENT '商品ID',
order_price INT NOT NULL DEFAULT 0 COMMENT '订单实际价格(分)',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY(order_id, create_time)
)
PARTITION BY LIST (YEAR(create_time)*100   MONTH(create_time))
(
PARTITION p201601 VALUES IN (201601),
PARTITION p201602 VALUES IN (201602),
PARTITION p201603 VALUES IN (201603),
PARTITION p201604 VALUES IN (201604),
PARTITION p201605 VALUES IN (201605),
PARTITION p201606 VALUES IN (201606),
PARTITION p201607 VALUES IN (201607),
PARTITION p201608 VALUES IN (201608),
PARTITION p201609 VALUES IN (201609),
PARTITION p201610 VALUES IN (201610),
PARTITION p201611 VALUES IN (201611),
PARTITION p201612 VALUES IN (201612)
);

-- 插入相关数据
INSERT INTO ord_order VALUES
(NULL, 10000001, 11111111, 1000, '2016-01-13 01:00:10'),
(NULL, 10000001, 11111112, 2000, '2016-01-13 02:00:20'),
(NULL, 10000001, 11111113, 3000, '2016-01-13 03:00:30'),
(NULL, 10000001, 11111114, 4000, '2016-01-13 04:00:40'),
(NULL, 10000001, 11111115, 5000, '2016-01-13 05:00:50'),
(NULL, 10000001, 11111111, 1000, '2016-02-13 01:00:10'),
(NULL, 10000001, 11111112, 2000, '2016-02-13 02:00:20'),
(NULL, 10000001, 11111113, 3000, '2016-02-13 03:00:30'),
(NULL, 10000001, 11111114, 4000, '2016-02-13 04:00:40'),
(NULL, 10000001, 11111115, 5000, '2016-02-13 05:00:50'),
(NULL, 10000001, 11111111, 1000, '2016-03-13 01:00:10'),
(NULL, 10000001, 11111112, 2000, '2016-03-13 02:00:20'),
(NULL, 10000001, 11111113, 3000, '2016-03-13 03:00:30'),
(NULL, 10000001, 11111114, 4000, '2016-03-13 04:00:40'),
(NULL, 10000001, 11111115, 5000, '2016-03-13 05:00:50'),
(NULL, 10000001, 11111111, 1000, '2016-04-13 01:00:10'),
(NULL, 10000001, 11111112, 2000, '2016-04-13 02:00:20'),
(NULL, 10000001, 11111113, 3000, '2016-04-13 03:00:30'),
(NULL, 10000001, 11111114, 4000, '2016-04-13 04:00:40'),
(NULL, 10000001, 11111115, 5000, '2016-04-13 05:00:50'),
(NULL, 10000001, 11111111, 1000, '2016-05-13 01:00:10'),
(NULL, 10000001, 11111112, 2000, '2016-05-13 02:00:20'),
(NULL, 10000001, 11111113, 3000, '2016-05-13 03:00:30'),
(NULL, 10000001, 11111114, 4000, '2016-05-13 04:00:40'),
(NULL, 10000001, 11111115, 5000, '2016-05-13 05:00:50'),
(NULL, 10000001, 11111111, 1000, '2016-06-13 01:00:10'),
(NULL, 10000001, 11111112, 2000, '2016-06-13 02:00:20'),
(NULL, 10000001, 11111113, 3000, '2016-06-13 03:00:30'),
(NULL, 10000001, 11111114, 4000, '2016-06-13 04:00:40'),
(NULL, 10000001, 11111115, 5000, '2016-06-13 05:00:50'),
(NULL, 10000001, 11111111, 1000, '2016-07-13 01:00:10'),
(NULL, 10000001, 11111112, 2000, '2016-07-13 02:00:20'),
(NULL, 10000001, 11111113, 3000, '2016-07-13 03:00:30'),
(NULL, 10000001, 11111114, 4000, '2016-07-13 04:00:40'),
(NULL, 10000001, 11111115, 5000, '2016-07-13 05:00:50'),
(NULL, 10000001, 11111111, 1000, '2016-08-13 01:00:10'),
(NULL, 10000001, 11111112, 2000, '2016-08-13 02:00:20'),
(NULL, 10000001, 11111113, 3000, '2016-08-13 03:00:30'),
(NULL, 10000001, 11111114, 4000, '2016-08-13 04:00:40'),
(NULL, 10000001, 11111115, 5000, '2016-08-13 05:00:50'),
(NULL, 10000001, 11111111, 1000, '2016-09-13 01:00:10'),
(NULL, 10000001, 11111112, 2000, '2016-09-13 02:00:20'),
(NULL, 10000001, 11111113, 3000, '2016-09-13 03:00:30'),
(NULL, 10000001, 11111114, 4000, '2016-09-13 04:00:40'),
(NULL, 10000001, 11111115, 5000, '2016-09-13 05:00:50'),
(NULL, 10000001, 11111111, 1000, '2016-10-13 01:00:10'),
(NULL, 10000001, 11111112, 2000, '2016-10-13 02:00:20'),
(NULL, 10000001, 11111113, 3000, '2016-10-13 03:00:30'),
(NULL, 10000001, 11111114, 4000, '2016-10-13 04:00:40'),
(NULL, 10000001, 11111115, 5000, '2016-10-13 05:00:50'),
(NULL, 10000001, 11111111, 1000, '2016-11-13 01:00:10'),
(NULL, 10000001, 11111112, 2000, '2016-11-13 02:00:20'),
(NULL, 10000001, 11111113, 3000, '2016-11-13 03:00:30'),
(NULL, 10000001, 11111114, 4000, '2016-11-13 04:00:40'),
(NULL, 10000001, 11111115, 5000, '2016-11-13 05:00:50'),
(NULL, 10000001, 11111111, 1000, '2016-12-13 01:00:10'),
(NULL, 10000001, 11111112, 2000, '2016-12-13 02:00:20'),
(NULL, 10000001, 11111113, 3000, '2016-12-13 03:00:30'),
(NULL, 10000001, 11111114, 4000, '2016-12-13 04:00:40'),
(NULL, 10000001, 11111115, 5000, '2016-12-13 05:00:50');

-- 查看分区p201601数据
SELECT * FROM ord_order PARTITION(p201601);

-- 组合成的 row key
SELECT CONCAT(user_id, 10000000000-UNIX_TIMESTAMP(create_time), goods_id)
FROM ord_order PARTITION(p201601);

创建HBase表 ord_order

由于版本兼容的问题，这边我需要先创建好HBase对应的表。不然会报不能自动创建 column family 的错误。

使用 hbase shell 创建 ord_order 表

hbase(main):033:0