MySql使用主键编码分区

需求场景：我们再设计订单表的时候，对其主键使用一定规则的编码方式，而不是自带的自增序列，方便我们业务查询。

其中一个编码规则就是把日期编入其中，我们也能够按照这个主键编码进行按照月份进行分区存储。

首先，创建订单表：

CREATE TABLE `order` (
  `order_id` BIGINT(14) UNSIGNED NOT NULL,
  `order_money` DECIMAL(6,2) UNSIGNED NOT NULL DEFAULT '0.00',
  `order_user` SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `order_time` DATETIME DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

需要注意：order_id 的编码规则是由 order_user(4位) + yymmdd + 4为顺序码组成，共计14位 。

然后，我们写入三条记录：

INSERT INTO `order`(order_id, order_money, order_user, order_time) VALUE (10011901030001, 200.00, 1001, '2019-01-03 08:12:14');
INSERT INTO `order`(order_id, order_money, order_user, order_time) VALUE (10121905090003, 350.00, 1012, '2019-05-09 12:28:20');
INSERT INTO `order`(order_id, order_money, order_user, order_time) VALUE (20071911130017, 177.00, 2007, '2019-11-13 18:30:00');

从数据上来看，我们分别在19年1月份，5月份，11月份各写入一条数据，如下图展示：

接下来我们需要根据主键进行分区，如何从主键中获取月份值，如下：

SELECT `order_id` MOD 100000000 DIV 1000000 FROM `order`;

我们的分区脚本如下：

ALTER TABLE `order` PARTITION BY LIST(`order_id` MOD 100000000 DIV 1000000) (
  PARTITION o0 VALUES IN (0),
  PARTITION o1 VALUES IN (1),
  PARTITION o2 VALUES IN (2),
  PARTITION o3 VALUES IN (3),
  PARTITION o4 VALUES IN (4),
  PARTITION o5 VALUES IN (5),
  PARTITION o6 VALUES IN (6),
  PARTITION o7 VALUES IN (7),
  PARTITION o8 VALUES IN (8),
  PARTITION o9 VALUES IN (9),
  PARTITION o10 VALUES IN (10),
  PARTITION o11 VALUES IN (11),
  PARTITION o12 VALUES IN (12),
  PARTITION onull VALUES IN (NULL)
);

分区脚本执行完毕后，之前的数据将按照分区规则进行存储，我们查看一下：

SELECT partition_name, PARTITION_DESCRIPTION, PARTITION_EXPRESSION, table_rows FROM information_schema.partitions WHERE table_name = 'order';

可以看到在分区o1，o5，o11中分别有一条数据。

我们再新插入两条数据，如下：

INSERT INTO `order`(order_id, order_money, order_user, order_time) VALUE (30881908100001, 150.00, 3088, '2019-08-10 10:30:00');
INSERT INTO `order`(order_id, order_money, order_user, order_time) VALUE (30881908200015, 180.00, 3088, '2019-08-20 20:05:00');

我们写入两条8月份的两条记录，再次查看分区情况：

我们查询一下试试：

EXPLAIN PARTITIONS SELECT * FROM `order` WHERE order_user = 3088 AND order_time BETWEEN '2019-08-01' AND '2019-09-01';

很遗憾的是，它并没有到我们的“o8”分区去查询，而是扫描了全表。

可能是我们没有使用分区主键做查询条件的原因，在试试

EXPLAIN PARTITIONS SELECT * FROM `order` WHERE order_id BETWEEN 30881908010000 AND 30881909019999;

还是全表扫描，但是用到索引了，因为是主键查询嘛，但这不是我们预期想要的。

可能是因为我们在分区的时候，使用了运算导致无法识别正确的分区。

我们换一种思路来建立分区：

ALTER TABLE `order` PARTITION BY RANGE(order_id) (
  PARTITION o0 VALUES LESS THAN (10000000000000), 
  PARTITION o1 VALUES LESS THAN (20000000000000), 
  PARTITION o2 VALUES LESS THAN (30000000000000),  
  PARTITION o3 VALUES LESS THAN (40000000000000), 
  PARTITION o4 VALUES LESS THAN (50000000000000), 
  PARTITION o5 VALUES LESS THAN (60000000000000), 
  PARTITION o100 VALUES LESS THAN MAXVALUE 
);

再来执行一遍查询：

EXPLAIN PARTITIONS SELECT * FROM `order` WHERE order_id BETWEEN 30881908010000 AND 30881909019999;

终于看到像要的结果了，看来Mysql的分区使用还是最简单的方式！

问题在于我们很多的查询基本上都不可能通过 order_id 来实现，如果不能用到 order_id 的话，就无法使用分区查询了。

备注：后来，我重新组织表结构，使用`order_id`, `order_time`做主键，然后通过MONTH(order_time)做分区，

然后后执行：

EXPLAIN PARTITIONS SELECT * FROM `order` WHERE order_time BETWEEN '2019-08-01' AND '2019-09-01';

还是没有使用分区搜索，还是全表扫描了。这样我们是去分区的意义了。

综上所述，貌似只有根据主键分区，且查询条件包含主键才能使用分区，且在主键分区的时候不能使用函数或者表达式，

只能使用简单的大于小于的判断，这样Mysql才能根据Where条件里面的主键来找到合适的分区进行查询，而不是全表扫描！