mycat mysql 多租户_两种实现方式mycat多租户,枚举分片,注解拦截

枚举分片,解决查询分片命中问题

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.00sec)

两个表,都有枚举分片字段

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.00sec)

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.00sec)UPDATE

Databasechanged

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.02sec)

分片字段不能被更新

mysql> explain update detail set itemNum='100',shardingId=0 where id = 8079;

ERROR1064 (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)