MyCat,各种分片规则,仅保证插入的时候分片.表关联,join,查询怎么命中分片条件,还是需要设计.
今天稍微测了一下.
ER 分片,此方式,插入的时候能分片,但是查询的时候不是分片,可能使用其他分片规则,而且关联字段为 主键,也许可以设计出命中规则,但是我们需求是,可横向扩展,而且可控分片
枚举分片,解决查询分片命中问题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)UPDATEDatabase changedmysql> 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)