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 2021/08/26 01:33:31 UTC
[GitHub] [shardingsphere] hoorf opened a new issue #12008: AbstractScalingSQLBuilder buildSplitByPrimaryKeyRangeSQL method optimize
hoorf opened a new issue #12008:
URL: https://github.com/apache/shardingsphere/issues/12008
this find pk range method will invoke many times for
```
@Override
public String buildSplitByPrimaryKeyRangeSQL(final String tableName, final String primaryKey) {
return String.format("SELECT MAX(%s) FROM (SELECT %s FROM %s WHERE %s>=? limit ?) t", quote(primaryKey), quote(primaryKey), quote(tableName), quote(primaryKey));
}
```
use in
```
private Collection<ScalingPosition<?>> getPositionByPrimaryKeyRange(final JobContext jobContext, final DataSource dataSource, final InventoryDumperConfiguration dumperConfig) {
Collection<ScalingPosition<?>> result = new ArrayList<>();
String sql = ScalingSQLBuilderFactory.newInstance(jobContext.getJobConfig().getHandleConfig().getDatabaseType())
.buildSplitByPrimaryKeyRangeSQL(dumperConfig.getTableName(), dumperConfig.getPrimaryKey());
try (Connection connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(sql)) {
long beginId = 0;
for (int i = 0; i < Integer.MAX_VALUE; i++) {
ps.setLong(1, beginId);
ps.setLong(2, jobContext.getJobConfig().getHandleConfig().getShardingSize());
try (ResultSet rs = ps.executeQuery()) {
rs.next();
long endId = rs.getLong(1);
if (endId == 0) {
break;
}
result.add(new PrimaryKeyPosition(beginId, endId));
beginId = endId + 1;
}
}
// fix empty table missing inventory task
if (0 == result.size()) {
result.add(new PrimaryKeyPosition(0, 0));
}
} catch (final SQLException ex) {
throw new PrepareFailedException(String.format("Split task for table %s by primary key %s error", dumperConfig.getTableName(), dumperConfig.getPrimaryKey()), ex);
}
return result;
}
```
the pk start from 0,if use `SnowFlake ` pk , useless invoke many times
optimize
- find `min` and `max` pk count the range
- support the pk like `O100440001` to `O100449999` string pk not int pk
--
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] hoorf commented on issue #12008: discuss AbstractScalingSQLBuilder buildSplitByPrimaryKeyRangeSQL method optimize
Posted by GitBox <gi...@apache.org>.
hoorf commented on issue #12008:
URL: https://github.com/apache/shardingsphere/issues/12008#issuecomment-906068747
> The current data volume of each partition is balanced.
> What are the advantages and disadvantages of your solution?
for balance real a good way in current,but only int pk
--
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] hoorf edited a comment on issue #12008: discuss AbstractScalingSQLBuilder buildSplitByPrimaryKeyRangeSQL method optimize
Posted by GitBox <gi...@apache.org>.
hoorf edited a comment on issue #12008:
URL: https://github.com/apache/shardingsphere/issues/12008#issuecomment-906068747
> The current data volume of each partition is balanced.
> What are the advantages and disadvantages of your solution?
for balance real a good way in current,but only int pk
--
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] hoorf edited a comment on issue #12008: discuss AbstractScalingSQLBuilder buildSplitByPrimaryKeyRangeSQL method optimize
Posted by GitBox <gi...@apache.org>.
hoorf edited a comment on issue #12008:
URL: https://github.com/apache/shardingsphere/issues/12008#issuecomment-906005394
discuss to fix it or not ,I have resolution
--
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] hoorf commented on issue #12008: AbstractScalingSQLBuilder buildSplitByPrimaryKeyRangeSQL method optimize
Posted by GitBox <gi...@apache.org>.
hoorf commented on issue #12008:
URL: https://github.com/apache/shardingsphere/issues/12008#issuecomment-906005394
discuss to fix it ,I have resolution
--
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] avalon5666 commented on issue #12008: discuss AbstractScalingSQLBuilder buildSplitByPrimaryKeyRangeSQL method optimize
Posted by GitBox <gi...@apache.org>.
avalon5666 commented on issue #12008:
URL: https://github.com/apache/shardingsphere/issues/12008#issuecomment-906055786
The current data volume of each partition is balanced.
What are the advantages and disadvantages of your solution?
--
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] hoorf closed issue #12008: discuss AbstractScalingSQLBuilder buildSplitByPrimaryKeyRangeSQL method optimize
Posted by GitBox <gi...@apache.org>.
hoorf closed issue #12008:
URL: https://github.com/apache/shardingsphere/issues/12008
--
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