MyCat 枚举分片设计思考,查询命中条件
MyCat,各种分片规则,仅保证插入的时候分片.表关联,join,查询怎么命中分片条件,还是需要设计.
今天稍微测了一下.
ER 分片,此方式,插入的时候能分片,但是查询的时候不是分片,可能使用其他分片规则,而且关联字段为 主键,也许可以设计出命中规则,但是我们需求是,可横向扩展,而且可控分片
<table name="orderinfo" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="sharding-by-intfile"> <childTable name="detail" primaryKey="ID" joinKey="orderNo" parentKey="orderNo"/> </table>
枚举分片,解决查询分片命中问题 mysql> explain select * from order a left join detail b on a.id = b.orderId where a.sharding_id = 0; +-----------+----------------------------------------------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+----------------------------------------------------------------------------------------------------------------------+ | dn1 | select * from order a left join detail b on a.id = b.orderId where a.sharding_id = 0 | +-----------+----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 两个表,都有枚举分片字段 mysql> explain select * from order a left detail b on a.id = b.orderId where a.sharding_id and b.sharding_id = 0; +-----------+----------------------------------------------------------------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+----------------------------------------------------------------------------------------------------------------------------------------+ | dn1 | select * from order a left detail b on a.id = b.orderId where a.sharding_id and b.sharding_id = 0 | +-----------+----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> 没有命中条件,造成全盘扫描 mysql> explain select * from order a left join detail b on a.id = b.orderId; +-----------+----------------------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+----------------------------------------------------------------------------------------------+ | dn1 | select * from rder a left join detail b on a.id = b.orderId | | dn2 | select * from rder a left join detail b on a.id = b.orderId | | dn3 | select * from rder a left join detail b on a.id = b.orderId | +-----------+----------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) UPDATE Database changed mysql> explain update detail set itemNum='100' where id = 8079 -> ; +-----------+--------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+--------------------------------------------------------------+ | dn1 | update detail set itemNum='100' where id = 8079 | | dn2 | update detail set itemNum='100' where id = 8079 | | dn3 | update detail set itemNum='100' where id = 8079 | +-----------+--------------------------------------------------------------+ 3 rows in set (0.02 sec) 分片字段不能被更新 mysql> explain update detail set itemNum='100',shardingId=0 where id = 8079; ERROR 1064 (HY000): Sharding column can't be updated DETAIL->SHARDINGID 加上分片字段 mysql> explain update order set itemNum='100' where id = 8079 and shardingId = 0; +-----------+---------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+---------------------------------------------------------------------------------+ | dn1 | update order set itemNum='100' where id = 8079 and shardingId = 0 | +-----------+---------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 删除,同样全盘扫描 mysql> explain delete detail where id = 8079; +-----------+--------------------------------------------+ | DATA_NODE | SQL | +-----------+--------------------------------------------+ | dn1 | delete detail where id = 8079 | | dn2 | delete detail where id = 8079 | | dn3 | delete detail where id = 8079 | +-----------+--------------------------------------------+ 3 rows in set (0.00 sec) 强制命中条件 mysql> explain delete detail where id = 8079 and shardingId = 0; +-----------+---------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+---------------------------------------------------------------+ | dn1 | delete detail where id = 8079 and shardingId = 0 | +-----------+---------------------------------------------------------------+ 1 row in set (0.00 sec) 全局表与分配表 inner join 以及 left jion right jion 都可以命中枚举 mysql> explain select * from user a inner join order b where a.id=b.userId and b.shardingId = 0; +-----------+----------------------------------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+----------------------------------------------------------------------------------------------------------+ | dn1 | select * from user a inner join order b where a.id=b.userId and b.shardingId = 0| +-----------+----------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> explain select * from user a right join order b on a.id = b.userId where b.shardingId = 0; +-----------+------------------------------------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+------------------------------------------------------------------------------------------------------------+ | dn1 |select * from user a right join order b on a.id = b.userId where b.shardingId = 0| +-----------+------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
