You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/06/26 10:09:20 UTC
[GitHub] [shardingsphere] sandynz opened a new issue, #18608: Is it possible to insert new record into dynamic calculated (created) actual table for autoTables VOLUME_RANGE algorithm
sandynz opened a new issue, #18608:
URL: https://github.com/apache/shardingsphere/issues/18608
## Feature Request
### Is your feature request related to a problem?
Yes
### Describe the feature you would like.
ShardingSphere version: master branch or 5.1.2 (the running version is a little changed on local)
I'm running VOLUME_RANGE in autoTables, related Java class is VolumeBasedRangeShardingAlgorithm.
It will add 2 special ranges.
For example, table rule definition:
```
CREATE SHARDING TABLE RULE t_order(
RESOURCES(ds_0,ds_1),
SHARDING_COLUMN=order_id,
TYPE(NAME=VOLUME_RANGE,PROPERTIES("range-lower"=100,"range-upper"=300,"sharding-volume"=100)),
KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
);
```
The partition range is:
<img width="399" alt="图片" src="https://user-images.githubusercontent.com/42492540/175799125-a256b6e6-cba7-4248-99b0-265b4d2633f8.png">
The first range is(-∞..100), the last range is [300..+∞).
```
mysql> preview select * from t_order;
+------------------+-----------------------------------------------------------+
| data_source_name | actual_sql |
+------------------+-----------------------------------------------------------+
| ds_1 | select * from t_order_0 UNION ALL select * from t_order_2 |
| ds_0 | select * from t_order_1 UNION ALL select * from t_order_3 |
+------------------+-----------------------------------------------------------+
2 rows in set (0.66 sec)
```
Inserting some records:
```
insert into t_order (order_id, user_id, status) values (1,2,'ok'),(101,2,'ok'),(201,2,'ok'),(301,2,'ok'),(401,2,'ok'),(501,2,'ok'),(3001,2,'ok');
```
```
mysql> preview select * from t_order where order_id=501;
+------------------+--------------------------------------------+
| data_source_name | actual_sql |
+------------------+--------------------------------------------+
| ds_0 | select * from t_order_3 where order_id=501 |
+------------------+--------------------------------------------+
1 row in set (0.01 sec)
```
Record 501 is in `ds_0.t_order_3`.
In `ds_0.t_order_3`:
```
mysql> select * from t_order_3;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
| 301 | 2 | ok |
| 401 | 2 | ok |
| 501 | 2 | ok |
| 3001 | 2 | ok |
+----------+---------+--------+
4 rows in set (0.04 sec)
```
If we change `range-upper` to `600`.
```
mysql> ALTER SHARDING TABLE RULE t_order(
-> RESOURCES(ds_0,ds_1),
-> SHARDING_COLUMN=order_id,
-> TYPE(NAME=VOLUME_RANGE,PROPERTIES("range-lower"=100,"range-upper"=600,"sharding-volume"=100)),
-> KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
-> );
Query OK, 0 rows affected (1.35 sec)
mysql> preview select * from t_order;
+------------------+-------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql |
+------------------+-------------------------------------------------------------------------------------------------------------------------------+
| ds_1 | select * from t_order_0 UNION ALL select * from t_order_2 UNION ALL select * from t_order_4 UNION ALL select * from t_order_6 |
| ds_0 | select * from t_order_1 UNION ALL select * from t_order_3 UNION ALL select * from t_order_5 |
+------------------+-------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.05 sec)
mysql> preview select * from t_order where order_id=501;
+------------------+--------------------------------------------+
| data_source_name | actual_sql |
+------------------+--------------------------------------------+
| ds_0 | select * from t_order_5 where order_id=501 |
+------------------+--------------------------------------------+
1 row in set (0.01 sec)
```
Record 501 is in `ds_0.t_order_5`. It was `ds_0.t_order_3` before.
We need to move it from `t_order_3` to `t_order_5`.
Is it possible to insert new record into dynamic calculated actual table, even sharding column value exceeds range upper, and when range-upper increase, all records could keep in original actual tables.
Then record 501 will be inserted into `t_order_5` at the beginning, but not `t_order_3`.
Possible changes:
- `range-upper` could be removed in VOLUME_RANGE algorithm (VolumeBasedRangeShardingAlgorithm), and no `Range.atLeast(upper)`, e.g. [300..+∞).
- ShardingAutoTableAlgorithm.getAutoTablesAmount() could be calculated by current created actual tables.
- Auto TableRule.actualDataNodes could be dynamic.
Looks other 2 sharding algorithms have similar behavior:
- BOUNDARY_RANGE, BoundaryBasedRangeShardingAlgorithm
- AUTO_INTERVAL, AutoIntervalShardingAlgorithm
For backward compatibility, it might need to add new sharding algorithms.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz closed issue #18608: Is it possible to insert new record into dynamic calculated (created) actual table for autoTables VOLUME_RANGE algorithm
Posted by GitBox <gi...@apache.org>.
sandynz closed issue #18608: Is it possible to insert new record into dynamic calculated (created) actual table for autoTables VOLUME_RANGE algorithm
URL: https://github.com/apache/shardingsphere/issues/18608
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #18608: Is it possible to insert new record into dynamic calculated (created) actual table for autoTables VOLUME_RANGE algorithm
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #18608:
URL: https://github.com/apache/shardingsphere/issues/18608#issuecomment-1168324833
After discussion with @tristaZero and @strongduanmu , we'll take another way later and keep current implementation.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org