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:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
![https://user-images.githubusercontent.com/42492540/175799125-a256b6e6-cba7-4248-99b0-265b4d2633f8.png"](https://user-images.githubusercontent.com/42492540/175799125-a256b6e6-cba7-4248-99b0-265b4d2633f8.png"){kind=link}