Sharding-JDBC分庫分表綁定表規則優化使用
springboot配置
# t_order 根據order_id分表
sharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds.t_order_$->{0..1}
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2}
sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=order_id
# t_order_item 根據order_id字段分表
sharding.jdbc.config.sharding.tables.t_order_item.actual-data-nodes=ds.t_order_item_$->{0..1}
sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 2}
sharding.jdbc.config.sharding.tables.t_order_item.key-generator-column-name=order_item_id
#綁定表規則清單
sharding.jdbc.config.sharding.binding-tables[0]=t_order,t_order_item
說明:t_order為主表,t_order_item為子表,分片建都是order_id
執行邏輯sql查詢語句:
SELECT
i.*
FROM
t_order o,
t_order_item i
WHERE
o.order_id = i.order_id
AND o.order_id = 100
[INFO ] 2018-08-29 13:31:02,724 --main-- [Sharding-Sphere-SQL] Actual SQL: ds :::
SELECT
i.*
FROM
t_order_0 o,
t_order_item_0 i
WHERE
o.order_id = i.order_id
AND o.order_id = 100
[INFO ] 2018-08-29 13:31:02,724 --main-- [Sharding-Sphere-SQL] Actual SQL: ds :::
SELECT
i.*
FROM
t_order_1 o,
t_order_item_1 i
WHERE
o.order_id = i.order_id
AND o.order_id = 100
問題:查詢2次,分片字段order_id=100未使用到,如果使用到應該值查詢1次
解決方法:
執行邏輯sql查詢語句:
SELECT
i.*
FROM
t_order_item i,t_order o
WHERE
o.order_id = i.order_id
AND o.order_id = 100
[INFO ] 2018-08-29 13:31:02,724 --main-- [Sharding-Sphere-SQL] Actual SQL: ds :::
SELECT
i.*
FROM
t_order_item_0 i
t_order_0 o
WHERE
o.order_id = i.order_id
AND o.order_id = 100
原因:源碼ParsingSQLRouter類的方法tableNames.iterator().next() 擷取集合第一個表元素為t_order,可以使用分片鍵,優化查詢為一次,否則不使用分片鍵生成2個sql查詢2次