2013/12/27 数据路由 PDDL

大纲 why data router 我们的方案 分片数据源 全局表 业务逻辑表 全局序列号 数据聚合 事务处理 数据扩容 SQL限流 运维监控

大纲 why data router 我们的方案 分片数据源 全局表 业务逻辑表 全局序列号 数据聚合 事务处理 数据扩容 SQL限流 运维监控

业务痛点 数据量越来越大查询速度变慢 多租户需要实现数据隔离 需要实现读写分离和读数据负载均衡 需要提供数据垂直和水平拆分的方案 1 数据量越来越大查询速度变慢 5 多租户需要实现数据隔离 2 需要实现读写分离和读数据负载均衡 3 需要提供数据垂直和水平拆分的方案 4 不需要改变现有业务代码 5 需要数据路由规则配置化 6 需要提供无数据迁移的扩容方案 7 需要监控系统数据的状态 8 需要支持分布式事务 8 能够支持PostgreSQL数据库

数据切分原则 垂直切分 水平切分 垂直切分按照业务将表进行分类，分布到不同的数据库上面，把压力分担到不同数据库上面 水平切分不是将表分类，而是按照某个字段的规则分散到多个库中，每个表中只包含一部分数据，进一步访问数据分担压力 用户 User User1 用户 User2 用户 User3 用户 单数据库 Order 订单 Order1 订单 Order2 订单 Order3 订单 Application 支付 Pay 支付 Pay1 支付 Pay2 支付 Pay3

MySQL/PostgreSQL(不成熟有bug) 业内解决方案 产品 来源 支持的数据库 类型 Cobar 阿里 MySQL 中间件 TDDL MySQL/Oracle 客户端 DRDS MySQL协议 分布式数据库 MyCat 社区 MySQL/PostgreSQL(不成熟有bug) Atlas 360 TDSQL 腾讯 Heisenberg 百度 蓝海豚 京东 Sharding-Jdbc 当当 CobarClient 所有 OneProxy 平民软件 MySQL/PostgreSQL 商业版中间件

结论 大部分支持MySQL不支持PostgreSQL CobarClient只能依赖Ibatis使用，且不支持分表 2013/12/27 结论 大部分支持MySQL不支持PostgreSQL CobarClient只能依赖Ibatis使用，且不支持分表 OneProxy商业软件不开源 所以需要提供一个实施简单、路由可配置并且支持PostgreSQL的数据路由方案

大纲 why data router 我们的方案 分片数据源 全局表 业务逻辑表 全局序列号 数据聚合 事务处理 数据扩容 SQL限流 运维监控

整体架构图 2013/12/27 JDBC 规范改写 SQL 解析 SQL 改写 PDDL SQL 路由 OrderBy GroupBy slave JDBC 规范改写 SQL 解析 SQL 改写 PDDL SQL 路由 OrderBy GroupBy 结果集合并 SQL 执行 数据库连接池 管理 SQL 监控 事务管理 SQL 统计 SQL 限流 … master Application(Java) 参考方案如下： Sharding-jdbc、TDDL和MyCat

ShardingPreparedStatment JDBC 规范改写 ShardingDataSource ShardingConnection ShardingStatment ShardingPreparedStatment ShardingResultSet 获取连接 创建无参SQL语句 创建有参SQL语句 合并结果集 实现JDBC规范 获取连接都会创建一个ShardingConnection对象 创建SQLStatement对象 获取入参SQL语句 解析SQL 改写SQL 替换表名 为AVG追加查询列 路由SQL 数据库 表 执行SQL 合并结果集

2013/12/27 SQL解析 SQL解析引擎比较 3 1 2 fdbparser druid JSqlParser 使用alibaba的druid进行SQL解析 通过访问者模式（Visitor）遍历SQL，并收集相关参数 SelectVisitor InsertVisitor UpdateVisitor DeleteVisitor StatementParser(sql).accept(SQLASTVisitor); //注入访问者 ------------------------------------------------------------------- @Override //重载访问方法 public boolean visit(final XXX x) { … return super.visit(x); } 收集SQL语句的元数据 select avg(salary) , dept from compy where dept in ( ? ,? ) group by dept druid比fdbparder快16倍 druid比jsqlparser快8倍 聚合函数 列 表名 条件列 分组列 列元数据信息 聚合信息 表名 查询条件 GroupBy OrderBy Limit distinct 主要收集的内容：

部署图 主 从 从 主 从 从 应用系统 业务 PDDL 代码 应用系统 业务 PDDL 代码 id name 1 Anders 3 Tom 5 Eryan … id name 1 Anders 3 Tom 5 Eryan … id name 1 Anders 3 Tom 5 Eryan … 主 从 从 应用系统 业务 代码 PDDL id name 2 Azen 4 Eric 6 Own … id name 2 Azen 4 Eric 6 Own … id name 2 Azen 4 Eric 6 Own … 主 从 从

大纲 why data router 我们的方案 分片数据源 全局表 业务逻辑表 全局序列号 数据聚合 事务处理 数据扩容 SQL限流 运维监控

2013/12/27 分片数据源声明 <pddl:data-source id=“shardingDataSource” database-type=“PostgreSQL” > <pddl:data-source-partitions> <pddl:data-source-partition name="p0" read-strategy="weight"> <pddl:master-data-source ref="ds0”/> <pddl:slave-data-source ref="ds01" weight="200" /> <pddl:slave-data-source ref="ds02" weight="400" /> </pddl:data-source-partition> <pddl:data-source-partition name=“p1” read-strategy="cycle"> <pddl:master-data-source ref=“ds1” /> <pddl:slave-data-source ref=“ds11”/> <pddl:slave-data-source ref=“ds12”/> <pddl:data-source-partition name=“p2”read-strategy=”weight-x"> <pddl:master-data-source ref=“ds2” weight =“200”/> <pddl:slave-data-source ref="ds21" weight="200" /> <pddl:slave-data-source ref="ds22" weight="400" /> </pddl:data-source-partitions> </pddl:data-source> database-type数据类型：目前只支持PostgreSQL，不写默认就是PG read-strategy读数据库负载均衡配置，默认only-write。每一个数据分片都不一样 weight是数据库参与只读的权重 pool-size和timeout可以为每个数据分片指定线程池大小和等待线程超时时间

Connection. getAutoCommit() 读写分离 从库 SQL 类型解析 insert/ update/ delete select Sharding JDBC 业务代码 主库 T Connection. getAutoCommit() F 对于业务传入的SQL做类型解析 如果是DML操作则使用主库 如果是DQL操作判断AutoCommit 如果是false说明开启了事务，需要使用主库读取数据 如果是true则根据读的负载均衡策略并发读取数据

读数据负载均衡 轮询策略 权重策略 AtomicLong.getAndIncrement() % 3 轮询数范围 master slave2 策略名称 策略描述 only-write 读写操作都只使用主库 cycle 读操作只使用只读库，主库不参与 cycle-w 读操作时只读库和主库都参与 weight weight-w AtomicLong.getAndIncrement() % 3 轮询数范围 master slave2 slave1 1 2 轮询策略 主数据库可选参与读负载均衡 将每个数据库按顺序编号0~2放在数组中 自增数取模%3获取余数 根据余数返回该数据源实例 new Random() .nextInt(450) 权重策略 随机整数范围 主数据库可选参与读负载均衡 将每个数据库权重值相加 取0~450之间随机整数 判断随机数所属的区域 返回该区域数据源实例 … 99 100 … 249 250 … 449 slave1 slave2 master

大纲 why data router 我们的方案 分片数据源 全局表 业务逻辑表 全局序列号 数据聚合 事务处理 数据扩容 SQL限流 运维监控

全局表 2013/12/27 在每一个数据分片具有相同的静态数据称为全局表 在数据分片中直接关联业务表做查询提升执行效率 对全局表做DML操作时需要应用所有数据分片上 <pddl:tables> <pddl:global-table name=“stock”/> <pddl:global-table name=“city”/> ... </pddl:tables> Add(Stock) 全局/字典表解析过程 DB1 Rule t_stock=ds1,ds2,ds3 …... 业务代码 SQL解析 获取表名 Select 不需要处理，只针对DML操作 DB2 全局表路由 DB3 insert into t_stock(id,code,name) values(102, ’600XXX’, ’YYYYY’) TableName: t_stock

全局表 DB1.t_stock PDDL DB2.t_stock DB3.t_stock SQL id code name 1 000001 上证指数 2 000002 万科A 3 000003 金田A … insert into t_stock(id,code,name) values(102, ’600XXX’, ’YYYYY’) insert into t_stock(id,code,name) values(102, ’600XXX’, ’YYYYY’) SQL DB1.t_stock PDDL id code name 1 000001 上证指数 2 000002 万科A 3 000003 金田A … insert into t_stock(id,code,name) values(102, ’600XXX’, ’YYYYY’) DB2.t_stock id code name 1 000001 上证指数 2 000002 万科A 3 000003 金田A … insert into t_stock(id,code,name) values(102, ’600XXX’, ’YYYYY’) DB3.t_stock

大纲 why data router 我们的方案 分片数据源 全局表 业务逻辑表 全局序列号 数据聚合 事务处理 数据扩容 SQL限流 运维监控

逻辑表定义 <pddl:tables> <pddl:logic-table name="t_order" 2013/12/27 逻辑表定义 <pddl:tables> <pddl:logic-table name="t_order" primary-key="order_id" table-postfixes="_0,_1,_2" database-strategy="orderDatabaseStrategy" table-strategy="orderTableStrategy"> <pddl:logic-child-table name="t_item" primary-key="item_id" foreign-key="order_id"> <pddl:logic-child-table name="t_item_ext" primary-key="ext_id" foreign-key="item_id"/> </pddl:logic-child-table> </pddl:logic-table> </pddl:tables> Primary-key表中的主键 Foreign-key表外键，也是父表的主键 table-postfixes表后缀名枚举集合，逻辑表和所有子表的命名规范都按照这个规则来设定 database-strategy 逻辑表的数据库路由规则 table-strategy 逻辑表的实际表路由规则 所有子表都使用主表的路由规则

路由规则 <pddl:strategy id=“orderDatabaseStrategy” 2013/12/27 路由规则 <pddl:strategy id=“orderDatabaseStrategy” sharding-columns="user_id" expression=“DB${user_id.intValue() % 3+1}”/> <pddl:strategy id="orderTableStrategy" sharding-columns="order_id" expression=“_${order_id.intValue() % 3}”/> Sharding-columns路由字段，需要和逻辑表匹配（即逻辑表中包含此字段，否则获取不到路由值） 在配置文件中配置数据库和表路由规则，使用sharding-columns来指定路由列，解析SQL后获取逻辑表和路由列关联的值，根据此值进行路由计算 SQL中支持路由的查询条件操作包括 equals 、in 和 between and 其他查询条件操作做笛卡尔操作

多租户 租户编号：p2 根据用户的租户号路由到指定的数据库中 把租户的数据分片放置在HintContext中即可 PDDL p0 p1 p2 HintContextHolder.setHintContext(new HintContext(){ @Override public String getPartitionDBName() { return ”p2”; } }); PDDL p0 p1 p2

逻辑表ER分片 2013/12/27 PDDL 1、先根据数据路由规则路由数据分片 SQL DB1.t_order_1 order_id user_id 1 3 4 7 6 … item_id order_id name 45 1 xxx 77 4 yyy 90 7 zzz … 1、先根据数据路由规则路由数据分片 DB{user_id.intValue() %3 +1}=DB1 select i.name from t_order o,t_item i where o.order_id =i.order_id and user_id =3 and (o.order_id= 1 or o.order_id=2) SQL order_id PDDL DB1.t_order_1 DB1.t_item_1 order_id user_id 2 3 5 8 6 … item_id order_id name 53 2 xxx 81 5 yyy 99 8 zzz … 2、然后再根据表规则路由到具体 表逻辑表和子表使用同一规则 表名_${order_id.intValue() % 3}=表_1,表_2 order_id 3、改写表名 select i.name from t_order_1 o,t_item_1 i where o.order_id =i.order_id and user_id =3 and (o.order_id= 1 or o.order_id=2) select i.name from t_order_2 o,t_item_2 i where o.order_id =i.order_id and user_id =3 and (o.order_id= 1 or o.order_id=2) DB1.t_order_2 DB1.t_item_2

逻辑子表路由 2013/12/27 逻辑子表使用父表的数据库和表路由规则 当子表中不包含表路由字段且单独操作子表时 查询、更新和删除操作应用在所有分表中 插入操作根据外键查询父表确定插入字表的名称 查询所有实际表 DB0 order_id user_id 3 insert t_item (i_id,o_id,city) values(87,1,23) t_order_0 i_id o_id city 4 12 7 36 … select * from t_item where city=20 order_id user_id 1 3 4 order_id=o_id i_id o_id city 1 30 4 20 7 24 … i_id o_id city 2 8 5 20 … i_id o_id city 3 12 6 36 9 24 … t_order_1 t_item_1 order_id user_id 2 3 5 插入指定表 t_item_0 t_item_1 t_item_2 t_order_2

并行执行SQL 每一个数据库分片都有各自的线程池来执行所属的SQL 路由结果只有一条SQL，使用当前线程执行 如果是DML操作或开启事务，数据库分片内的SQL是顺序执行，数据库分片间并行执行 如果只是DQL操作，无论数据库分片内还是外都并行执行 多数据源执行互不影响，提升系统健壮性和可用性 DB1 Thread1 Thread2 Thread m … DB2 Thread1 Thread2 Thread n … DB3 Thread1 Thread2 Thread o …

大纲 why data router 我们的方案 分片数据源 全局表 业务逻辑表 全局序列号 数据聚合 事务处理 数据扩容 SQL限流 运维监控

全局序列号 用户请求 Proxy 基于数据库或Zookeeper定义全局序列号表 表里序列号表存储表名及其最大值 每个节点自动分配一段自增序列 如果越界的时候更新数据中的序列号表记录申请新的序列号区间 确保在分表情况下序列的唯一性 sequence.nextval(“user”) sequence.nextval(“user”) Application Node Application Node seqName currentVal range user 1021 1001~2000 order 4001 4001~5000 …… seqName currentVal range user 3000 2001~3000 order 3401 3001~4000 …… 1022 3001 3001~4000 达到边界 DB/ZK seqName maxVal modify_time node_name user 3000 2016-5-2 12:43:12.67 Node C order 5000 2016-5-2 12:46:56.06 Node B …. …… 4000 2016-5-2 12:49:35.92 Node C

大纲 why data router 我们的方案 分片数据源 全局表 业务逻辑表 全局序列号 数据聚合 事务处理 数据扩容 SQL限流 运维监控

聚合函数 select max(price), avg(price) from t_order; 改写SQL如下： 在select语句查询项中支持Max、Min、Count、Sum和Avg等聚合函数 对于Avg操作需要修改SQL动态增加Count列 select max(price), avg(price) from t_order; 改写SQL如下： select max(price), avg(price), count(1) as auto_key_count from t_order_0; select max(price), avg(price), count(1) as auto_key_count from t_order_1; select max(price), avg(price), count(1) as auto_key_count from t_order_2; Sum合并 sum1 +sum2 +sum3 +… +sumn Count合并 count1 +count2 +count3 +… +countn Max合并 Math.max( count1 ,count2 ,… ,countn) Min合并 Math.min( count1 ,count2 ,… ,countn) Avg合并 (avg1*count1+ avg2*count2+ … ) / (count1+ count2+ …)

排序和分组 orderby code desc 排序聚合 groupby city 分组聚合 Result 3 Result 2 id code name 7 500104 X1 9 467001 X2 Result 3 id code name 10 600789 X3 7 500104 X1 9 467001 X2 5 300001 X5 4 207862 X6 2 000002 X4 排序聚合 id code name 10 600789 X3 2 000002 X4 Result 2 id code name 5 300001 X5 4 207862 X6 Result 1 Merge Result groupby city city count beijing 302 shanghai 211 guangzhou 78 city count beijing 356 shanghai 211 guangzhou 78 shenzhen 307 Result 2 分组聚合 city count beijing 54 shenzhen 307 Result 1 Merge Result

大纲 why data router 我们的方案 分片数据源 全局表 业务逻辑表 全局序列号 数据聚合 事务处理 数据扩容 SQL限流 运维监控

Best Efforts 1PC MultipleDataSourceTransaction 业务开启事务的时候会设置代理Connection自动提交为false 把真正的Connection自动提交属性设置成false 对代理Connection做事务提交或回滚时，对真正的Connection分别做事务提交或回滚，且把自动提交属性设置成true conn1.setAutoCommit(false) ShardingConnection.setAutoCommit(false) 开启事务 conn2.setAutoCommit(false) 提交/回滚事务 ShardingConnection.setAutoCommit(false) conn1.commit()/rollback(); conn2.commit()/rollback(); conn1.setAutoCommit(true) conn2.setAutoCommit(true)

柔性事务最大努力送 业务代码 记录事务日志 PDDL 监听执行事件 执行 重试执行 清理事务日志 读取事务库 业务应用 业务库 事务同步送达 执行前事件 执行结果事件 失败 成功 执行 事务异步送达

TCC分布式事务 下一次专题介绍

大纲 why data router 我们的方案 分片数据源 全局表 业务逻辑表 全局序列号 数据聚合 事务处理 数据扩容 SQL限流 运维监控

insert t_order(order_id,name) 横向等积扩容 规定每个库中t_order最多存储200条记录 库中对t_order分割成两个表，每个表最多存储100条 库中的两个表按奇偶数分布数据防止热点访问问题 数据库路由规则：DB${ Math.floor(order_id / 200) } 表路由规则：t_order_${order_id % 2 } 扩容时复制一份数据schema即可，不需要修改任何规则 扩容 DB0 DB1 insert t_order(order_id,name) values(313,”hongtao”) select * from t_order where order_id=6 order_id name 2 Tom 4 Jerry 6 Jim … order_id name 1 Tomas 3 Cat 5 Green … order_id name 202 Lily 204 Lilei 206 Hanmei … order_id name 201 Obama 203 bush 205 Jamth … t_order_0 t_order_1 t_order_0 t_order_1

横向翻倍扩容 -- 两库四表 初始状态 数据库：DB0、DB1 业务表：T0、T0_1、T1、T1_1 路由规则 数据量 分库规则 分表规则 id<=1千万 DB +${ id % 2} T + ${id % 2} 1千万<=id<=2千万 T + ${id % 2} + _1 DB0 DB1 T0 T0_1 T1 T1_1

横向翻倍扩容 -- 四库八表 路由规则 数据量 分库规则 分表规则 id<=1千万 DB +${ id % 2} T + ${id % 2} 1千万<=id<=2千万 DB +${ id % 2 + 2} T + ${id % 2} + _1 2千万<=id<=4千万 DB +${id % 4} T + ${id % 4} + _2 DB0 DB1 T0 T0_1 T0_2 T1 T1_2 T1_1 DB2 DB3 T2_2 T3_2

横向翻倍扩容 -- 八库十六表 路由规则 数据量 分库规则 分表规则 id<=1千万 DB +${ id % 2} T + ${id % 2} 1千万<=id<=2千万 DB +${ id % 2 + 2} T + ${id % 2} + _1 2千万<=id<=4千万 DB +${id % 4 + 4} T + ${id % 4} + _2 4千万<=id<=8千万 DB +${id % 8} T + ${id % 8} + _3 DB0 DB1 DB2 DB3 T0 T0_2 T0_3 T1 T1_2 T1_3 T0_1 T2_2 T2_3 T1_1 T3_3 T3_2 DB04 DB5 DB6 DB7 T4_3 T5_3 T6_3 T7_3

大纲 why data router 我们的方案 分片数据源 全局表 业务逻辑表 全局序列号 数据聚合 事务处理 数据扩容 SQL限流 运维监控

SQL限流 因为采用的是按照时间取模计算，所以观察的时间片相当于一个环 游标序号=当前时间%时间片时长/时间槽时长 将考察的时间片（最少1秒，默认1分钟）分多个槽（最多20个槽，每个槽的时间最少500毫秒）。然后构造AtomicInteger环形数组。 每次执行SQL之前，首先判断在此段时间片内执行SQL的次数，如果超出了设置的阈值，则不允许执行。 在不执行时，可以重试，如果再重试2次依然不成功，则报异常，此次SQL执行失败。 1 2 3 4 5 6 7 8 9 10 11 时间片（最少1秒，默认1分钟） 游标 逻辑： 对每个时间槽进行计数 统计每个时间片中各槽计数器的总数，判断是否超出阈值 超出阈值后返回“不允许”的标志，然后再重试2次 时间槽 1 2 3 4 5 6 7 8 9 10 11 对单位时间内读写次数的限制，即限流控制

大纲 why data router 我们的方案 分片数据源 全局表 业务逻辑表 全局序列号 数据聚合 事务处理 数据扩容 SQL限流 运维监控

数据库连接监控 查看各数据源连接池的使用情况 使用Filter拦截器，在过滤链方法调用后检查Connection泄露的问题

SQL执行监控与统计 Top10 Top10 Top10 Top10 使用Metrics在框架中埋点统计SQL执行情况 统一存储在influxdb中，做实时SQL监控 耗时SQL Top10 平均耗时SQL Top10 SQL执行次数 Top10 SQL执行出错 Top10

Thank you！