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='' where id = 8079
-> ;
+-----------+--------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------+
| dn1 | update detail set itemNum='' where id = 8079 |
| dn2 | update detail set itemNum='' where id = 8079 |
| dn3 | update detail set itemNum='' where id = 8079 |
+-----------+--------------------------------------------------------------+
3 rows in set (0.02 sec) 分片字段不能被更新
mysql> explain update detail set itemNum='',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)