OneProxy :: 定义多个分表字段,以便精确定位分区位置,优化简单SQL查询!

在OneProxy中创建基于复合分区(二级分区)技术的交易表(“user_trade”)后,发现另外一个问题,会有大量的SQL语句是根据交易号(“trade_no”)来查询的,如果这样的简单SQL变慢了,将是不能接受的。按照如下所示的分区表定义,根据交易号查询将轮循64张表,这速度是绝对快不了的。

  {
        "table"         : "user_trade",
        "pkey"          : "user_id",
        "type"          : "int",
        "method"        : "hash",
        "partitions"    : 16,
        "groups"        : [ "userdb01", "userdb02", "userdb03", "userdb04" ],
        "subpkey"       : "create_time",
        "subtype"       : "timestamp",
        "submethod"     : "range",
        "subpartitions" :
           [
               { "suffix" : "_16Q1", "value" : "2016/04/01" },
               { "suffix" : "_16Q2", "value" : "2016/07/01" },
               { "suffix" : "_16Q3", "value" : "2016/10/01" },
               { "suffix" : "_16Q4", "value" : "2017/01/01" }
           ]
  }

这样的问题如果不能解决,那实在太坏了,因为这样的SQL基本上都来自于关键的业务接口。如果不能精准地定位分区,那么后端任何一台MySQL数据库出现问题,整个业务就会出现中断,这不是分库分表的设计初衷,我们必须将这个问题圆满解决。

如果我们可以从交易号中解出分区位置信息,就可以进行精确的分区定位了。那么如何在交易号中嵌入分区位置的信息就成了关键问题,将完整的用户ID(“user_id”)放入到交易号中也是一个可行的办法。即使交易号中已经有用户ID信息了,发现按照交易号查询还需要访问4张二级分区表,除非还可以从交易号中解出日期信息。这个可以通过精心设计交易号编码格式来做到,假如交易号由如下规则来生成,前8位是交易日期,中间两位是用户ID的最后两位,最后8位是一个用序列生成的顺序数,可以保证每笔交易的交易号都是唯一的。

trade_no = 日期(yyyymmdd) + 用户ID (后两位) + 顺序数 (8位)

只放置用户ID(“user_id”)的最后两位到交易号,是为了交易号尽量短一些,以节省数据库的空间,尤其是索引空间。交易发生时,日期是可以确定的,用户ID也是可以确定的,而最后8位的顺序数则可以用OneProxy序列号服务来生成。接下来让我们来重新修改一下OneProxy中交易表的分区定义,更新后的定义如下:

  {
        "table"         : "user_trade",
        "pkey"          :
		[
		    { "name":"user_id", "start":-2},
		    { "name":"trade_no", "start":9, "end":10 }
		],
        "type"          : "int",
        "method"        : "hash",
        "partitions"    : 100,
        "groups"        : [ "userdb01", "userdb02", "userdb03", "userdb04" ],
        "subpkey"       :
		[
		     { "name":"create_time"},
		     { "name":"trade_no", "start":1, "end":8 }
		],
        "subtype"       : "timestamp",
        "submethod"     : "range",
        "subpartitions" :
           [
               { "suffix" : "_16Q1", "value" : "2016/04/01" },
               { "suffix" : "_16Q2", "value" : "2016/07/01" },
               { "suffix" : "_16Q3", "value" : "2016/10/01" },
               { "suffix" : "_16Q4", "value" : "2017/01/01" }
           ]
   }

现在如何是按用户ID访问,还是按交易号访问,都可以精确地定位到分区,过滤掉不必要的分区访问。可以在OneProxy的管理端口中进行验证,如下所示:

mysql> select * from user_trade where user_id=1001;
+-----------------------------------------------------------------+
| TABLES                                                          |
+-----------------------------------------------------------------+
| select * from user_trade_1_16Q1 user_trade where user_id = 1001 |
| select * from user_trade_1_16Q2 user_trade where user_id = 1001 |
| select * from user_trade_1_16Q3 user_trade where user_id = 1001 |
| select * from user_trade_1_16Q4 user_trade where user_id = 1001 |
+-----------------------------------------------------------------+
4 rows in set (0.01 sec)

mysql> select * from user_trade where trade_no='201603180100000001';
+-------------------------------------------------------------------+
| TABLES                                                            |
+-------------------------------------------------------------------+
| select * from user_trade_1_16Q1 user_trade where trade_no = '...' |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到按用户ID去访问时,只会扫描某个分区下所有的子分区;而按交易号去访问时,则会直接命中一个子分区,从而可以保证按交易号查询的SQL的性能,从而能保证关键业务API的响应时间。