You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2019/01/18 10:08:05 UTC
[GitHub] jmbkeyes opened a new issue #1782: how to config the datasource if
I just need shard one table in one database?
jmbkeyes opened a new issue #1782: how to config the datasource if I just need shard one table in one database?
URL: https://github.com/apache/incubator-shardingsphere/issues/1782
My project has about 100 tables in the database, there's one big table, I want to shard the big table.
So how to config the data source?
When I use the following code to set the datasource, it seems that all sql run through shardconnection.
BasicDataSource dataSource = new BasicDataSource();
if (ExtraConfig.isDev()) {
dataSource.setMaxTotal(20);
dataSource.setInitialSize(8);
dataSource.setMinIdle(8);
dataSource.setMaxIdle(12);
} else {
dataSource.setMaxTotal(150);
dataSource.setInitialSize(40);
dataSource.setMinIdle(40);
dataSource.setMaxIdle(80);
}
dataSource.setMaxWaitMillis(10000);
dataSource.setRemoveAbandonedTimeout(10);
dataSource.setDriverClassName(connectionSetting.getDriver());
dataSource.setUrl(connectionSetting.getUrl() + "?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE");
dataSource.setUsername(connectionSetting.getUsername());
dataSource.setPassword(connectionSetting.getPassword());
ArrayList<String> initSQLs = new ArrayList<String>();
initSQLs.add("SET NAMES 'utf8mb4'");
dataSource.setConnectionInitSqls(initSQLs);
dataSource.setCacheState(false);
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("ds0", dataSource);
// Configure table rule
TableRuleConfiguration logTableRuleConfig = new TableRuleConfiguration();
logTableRuleConfig.setLogicTable("center_op_log");
logTableRuleConfig.setActualDataNodes("ds0.center_op_log${0..4}");
// Configure strategies for database + table sharding
logTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("main_center_id", "center_op_log${mainCenterId % 4}"));
// Configure sharding rule
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(logTableRuleConfig);
shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new NoneShardingStrategyConfiguration());
shardingRuleConfig.setDefaultTableShardingStrategyConfig(new NoneShardingStrategyConfiguration());
shardingRuleConfig.setDefaultDataSourceName("ds0");
Properties properties = new Properties();
properties.setProperty("sql.show", "true");
try {
return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap<String, Object>(), properties);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
below are logs.
2019-01-18 18:04:55 INFO fileLogger:76 - 访问者IP信息：10.0.0.28,10.0.0.28
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - Rule Type: sharding
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - Logic SQL: SELECT fc.id, fc.name, fc.photo,fc.allowTrainerAddTraineeCourse, fc.usedThreeParty, fc.allowPrintReceipts, fc.effectiveEndDate, fc.deviceBindEnabledForOtherEmp, fc.maxBindDeviceCountForOtherEmp, fc.deviceBindEnabledForAdmin, fcm.id as fcmId, fcm.memberType, fcm.deviceIds, cc.parentId FROM fitness_center fc INNER JOIN fitness_center_member fcm ON fc.id = fcm.fitnessCenterId INNER JOIN chain_center cc ON cc.childId = fc.id WHERE fcm.accountId = ? AND fcm.active = 1 AND fcm.inviteStatus = 5 AND (fc.active IS NULL OR fc.active = 1) GROUP BY fc.id ORDER BY fc.sortIndex
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@5b038004), containStar=false, firstSelectItemStartPosition=7, selectListLastPosition=285, groupByLastPosition=551, items=[CommonSelectItem(expression=fc.id, alias=Optional.absent()), CommonSelectItem(expression=fc.name, alias=Optional.absent()), CommonSelectItem(expression=fc.photo, alias=Optional.absent()), CommonSelectItem(expression=fc.allowTrainerAddTraineeCourse, alias=Optional.absent()), CommonSelectItem(expression=fc.usedThreeParty, alias=Optional.absent()), CommonSelectItem(expression=fc.allowPrintReceipts, alias=Optional.absent()), CommonSelectItem(expression=fc.effectiveEndDate, alias=Optional.absent()), CommonSelectItem(expression=fc.deviceBindEnabledForOtherEmp, alias=Optional.absent()), CommonSelectItem(expression=fc.maxBindDeviceCountForOtherEmp, alias=Optional.absent()), CommonSelectItem(expression=fc.deviceBindEnabledForAdmin, alias=Optional.absent()), CommonSelectItem(expression=fcm.id, alias=Optional.of(fcmId)), CommonSelectItem(expression=fcm.memberType, alias=Optional.absent()), CommonSelectItem(expression=fcm.deviceIds, alias=Optional.absent()), CommonSelectItem(expression=cc.parentId, alias=Optional.absent())], groupByItems=[OrderItem(owner=Optional.of(fc), name=Optional.of(id), orderDirection=ASC, nullOrderDirection=ASC, index=-1, expression=null, alias=Optional.absent())], orderByItems=[OrderItem(owner=Optional.of(fc), name=Optional.of(sortIndex), orderDirection=ASC, nullOrderDirection=ASC, index=-1, expression=null, alias=Optional.of(ORDER_BY_DERIVED_0))], limit=null, subQueryStatement=null, subQueryStatements=[], subQueryConditions=[])
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - Actual SQL: ds0 ::: SELECT fc.id, fc.name, fc.photo,fc.allowTrainerAddTraineeCourse, fc.usedThreeParty, fc.allowPrintReceipts, fc.effectiveEndDate, fc.deviceBindEnabledForOtherEmp, fc.maxBindDeviceCountForOtherEmp, fc.deviceBindEnabledForAdmin, fcm.id as fcmId, fcm.memberType, fcm.deviceIds, cc.parentId FROM fitness_center fc INNER JOIN fitness_center_member fcm ON fc.id = fcm.fitnessCenterId INNER JOIN chain_center cc ON cc.childId = fc.id WHERE fcm.accountId = ? AND fcm.active = 1 AND fcm.inviteStatus = 5 AND (fc.active IS NULL OR fc.active = 1) GROUP BY fc.id ORDER BY fc.sortIndex ::: [[79839]]
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - Rule Type: sharding
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - Logic SQL: select chaincente0_.id as id1_55_, chaincente0_.createTime as createTi2_55_, chaincente0_.updateTime as updateTi3_55_, chaincente0_.childId as childId4_55_, chaincente0_.parentId as parentId5_55_ from chain_center chaincente0_ where chaincente0_.childId=? limit ?
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@aaeb33a), containStar=false, firstSelectItemStartPosition=7, selectListLastPosition=196, groupByLastPosition=0, items=[CommonSelectItem(expression=chaincente0_.id, alias=Optional.of(id1_55_)), CommonSelectItem(expression=chaincente0_.createTime, alias=Optional.of(createTi2_55_)), CommonSelectItem(expression=chaincente0_.updateTime, alias=Optional.of(updateTi3_55_)), CommonSelectItem(expression=chaincente0_.childId, alias=Optional.of(childId4_55_)), CommonSelectItem(expression=chaincente0_.parentId, alias=Optional.of(parentId5_55_))], groupByItems=[], orderByItems=[], limit=Limit(offset=null, rowCount=LimitValue(value=1, index=1, boundOpened=false)), subQueryStatement=null, subQueryStatements=[], subQueryConditions=[])
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - Actual SQL: ds0 ::: select chaincente0_.id as id1_55_, chaincente0_.createTime as createTi2_55_, chaincente0_.updateTime as updateTi3_55_, chaincente0_.childId as childId4_55_, chaincente0_.parentId as parentId5_55_ from chain_center chaincente0_ where chaincente0_.childId=? limit ? ::: [[212, 1]]
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - Rule Type: sharding
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - Logic SQL: SELECT count(1) FROM trainee_sub_center_info tsci INNER JOIN account a ON tsci.traineeId = a.id INNER JOIN fitness_center fc ON fc.id = tsci.centerId INNER JOIN trainee_center_info tci ON tsci.traineeId = tci.traineeId and tci.centerId = ? WHERE tsci.centerId = ? AND tsci.groupCourseMissedAppointmentNum > 0 AND fc.maxAllowedGroupCourseMissedAppointmentNum IS NOT NULL AND tsci.groupCourseMissedAppointmentNum >= fc.maxAllowedGroupCourseMissedAppointmentNum
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@22c5f190), containStar=false, firstSelectItemStartPosition=7, selectListLastPosition=16, groupByLastPosition=0, items=[AggregationSelectItem(type=COUNT, innerExpression=(1), alias=Optional.absent(), derivedAggregationSelectItems=[], index=-1)], groupByItems=[], orderByItems=[], limit=null, subQueryStatement=null, subQueryStatements=[], subQueryConditions=[])
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - Actual SQL: ds0 ::: SELECT count(1) FROM trainee_sub_center_info tsci INNER JOIN account a ON tsci.traineeId = a.id INNER JOIN fitness_center fc ON fc.id = tsci.centerId INNER JOIN trainee_center_info tci ON tsci.traineeId = tci.traineeId and tci.centerId = ? WHERE tsci.centerId = ? AND tsci.groupCourseMissedAppointmentNum > 0 AND fc.maxAllowedGroupCourseMissedAppointmentNum IS NOT NULL AND tsci.groupCourseMissedAppointmentNum >= fc.maxAllowedGroupCourseMissedAppointmentNum ::: [[213, 212]]
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - Rule Type: sharding
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - Logic SQL: SELECT DISTINCT(tsci.id) ,tsci.traineeId,tsci.groupCourseMissedAppointmentNum,tsci.lastMissDate,fc.maxAllowedGroupCourseMissedAppointmentNum,IFNULL( a.NAME, tci.`name` ) AS NAME,a.phone FROM trainee_sub_center_info tsci INNER JOIN account a ON tsci.traineeId = a.id INNER JOIN fitness_center fc ON fc.id = tsci.centerId INNER JOIN trainee_center_info tci ON tsci.traineeId = tci.traineeId and tci.centerId = ? WHERE tsci.centerId = ? AND tsci.groupCourseMissedAppointmentNum > 0 AND fc.maxAllowedGroupCourseMissedAppointmentNum IS NOT NULL AND tsci.groupCourseMissedAppointmentNum >= fc.maxAllowedGroupCourseMissedAppointmentNum ORDER BY tsci.groupCourseMissedAppointmentNum desc,tsci.lastMissDate desc limit ?, ?
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@16b16a69), containStar=false, firstSelectItemStartPosition=15, selectListLastPosition=186, groupByLastPosition=0, items=[io.shardingsphere.core.parsing.parser.context.selectitem.DistinctSelectItem@cde1b78, CommonSelectItem(expression=tsci.traineeId, alias=Optional.absent()), CommonSelectItem(expression=tsci.groupCourseMissedAppointmentNum, alias=Optional.absent()), CommonSelectItem(expression=tsci.lastMissDate, alias=Optional.absent()), CommonSelectItem(expression=fc.maxAllowedGroupCourseMissedAppointmentNum, alias=Optional.absent()), CommonSelectItem(expression=IFNULL( a.NAME, tci.`name` ), alias=Optional.of(NAME)), CommonSelectItem(expression=a.phone, alias=Optional.absent())], groupByItems=[], orderByItems=[OrderItem(owner=Optional.of(tsci), name=Optional.of(groupCourseMissedAppointmentNum), orderDirection=DESC, nullOrderDirection=ASC, index=-1, expression=null, alias=Optional.absent()), OrderItem(owner=Optional.of(tsci), name=Optional.of(lastMissDate), orderDirection=DESC, nullOrderDirection=ASC, index=-1, expression=null, alias=Optional.absent())], limit=Limit(offset=LimitValue(value=20, index=2, boundOpened=false), rowCount=LimitValue(value=20, index=3, boundOpened=false)), subQueryStatement=null, subQueryStatements=[], subQueryConditions=[])
2019-01-18 18:04:55 INFO ShardingSphere-SQL:71 - Actual SQL: ds0 ::: SELECT DISTINCT(tsci.id) ,tsci.traineeId,tsci.groupCourseMissedAppointmentNum,tsci.lastMissDate,fc.maxAllowedGroupCourseMissedAppointmentNum,IFNULL( a.NAME, tci.`name` ) AS NAME,a.phone FROM trainee_sub_center_info tsci INNER JOIN account a ON tsci.traineeId = a.id INNER JOIN fitness_center fc ON fc.id = tsci.centerId INNER JOIN trainee_center_info tci ON tsci.traineeId = tci.traineeId and tci.centerId = ? WHERE tsci.centerId = ? AND tsci.groupCourseMissedAppointmentNum > 0 AND fc.maxAllowedGroupCourseMissedAppointmentNum IS NOT NULL AND tsci.groupCourseMissedAppointmentNum >= fc.maxAllowedGroupCourseMissedAppointmentNum ORDER BY tsci.groupCourseMissedAppointmentNum desc,tsci.lastMissDate desc limit ?, ? ::: [[213, 212, 0, 40]]
2019-01-18 18:04:59 INFO ShardingSphere-SQL:71 - Rule Type: sharding
2019-01-18 18:04:59 INFO ShardingSphere-SQL:71 - Logic SQL: select account0_.id as id1_2_0_, account0_.createTime as createTi2_2_0_, account0_.updateTime as updateTi3_2_0_, account0_.accountType as accountT4_2_0_, account0_.address as address5_2_0_, account0_.birthday as birthday6_2_0_, account0_.channel as channel7_2_0_, account0_.clientLoginTime as clientLo8_2_0_, account0_.comment as comment9_2_0_, account0_.mergeComment as mergeCo10_2_0_, account0_.mergedTs as mergedT11_2_0_, account0_.name as name12_2_0_, account0_.namePinyin as namePin13_2_0_, account0_.password as passwor14_2_0_, account0_.phone as phone15_2_0_, account0_.photo as photo16_2_0_, account0_.sex as sex17_2_0_, account0_.ssid as ssid18_2_0_, account0_.status as status19_2_0_, account0_.subscribe as subscri20_2_0_, account0_.traineeIdMergeTo as trainee21_2_0_, account0_.unionId as unionId22_2_0_, account0_.wechatId as wechatI23_2_0_, account0_.wechatName as wechatN24_2_0_ from account account0_ where account0_.id=?
2019-01-18 18:04:59 INFO ShardingSphere-SQL:71 - SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@2f140969), containStar=false, firstSelectItemStartPosition=7, selectListLastPosition=894, groupByLastPosition=0, items=[CommonSelectItem(expression=account0_.id, alias=Optional.of(id1_2_0_)), CommonSelectItem(expression=account0_.createTime, alias=Optional.of(createTi2_2_0_)), CommonSelectItem(expression=account0_.updateTime, alias=Optional.of(updateTi3_2_0_)), CommonSelectItem(expression=account0_.accountType, alias=Optional.of(accountT4_2_0_)), CommonSelectItem(expression=account0_.address, alias=Optional.of(address5_2_0_)), CommonSelectItem(expression=account0_.birthday, alias=Optional.of(birthday6_2_0_)), CommonSelectItem(expression=account0_.channel, alias=Optional.of(channel7_2_0_)), CommonSelectItem(expression=account0_.clientLoginTime, alias=Optional.of(clientLo8_2_0_)), CommonSelectItem(expression=account0_.comment, alias=Optional.of(comment9_2_0_)), CommonSelectItem(expression=account0_.mergeComment, alias=Optional.of(mergeCo10_2_0_)), CommonSelectItem(expression=account0_.mergedTs, alias=Optional.of(mergedT11_2_0_)), CommonSelectItem(expression=account0_.name, alias=Optional.of(name12_2_0_)), CommonSelectItem(expression=account0_.namePinyin, alias=Optional.of(namePin13_2_0_)), CommonSelectItem(expression=account0_.password, alias=Optional.of(passwor14_2_0_)), CommonSelectItem(expression=account0_.phone, alias=Optional.of(phone15_2_0_)), CommonSelectItem(expression=account0_.photo, alias=Optional.of(photo16_2_0_)), CommonSelectItem(expression=account0_.sex, alias=Optional.of(sex17_2_0_)), CommonSelectItem(expression=account0_.ssid, alias=Optional.of(ssid18_2_0_)), CommonSelectItem(expression=account0_.status, alias=Optional.of(status19_2_0_)), CommonSelectItem(expression=account0_.subscribe, alias=Optional.of(subscri20_2_0_)), CommonSelectItem(expression=account0_.traineeIdMergeTo, alias=Optional.of(trainee21_2_0_)), CommonSelectItem(expression=account0_.unionId, alias=Optional.of(unionId22_2_0_)), CommonSelectItem(expression=account0_.wechatId, alias=Optional.of(wechatI23_2_0_)), CommonSelectItem(expression=account0_.wechatName, alias=Optional.of(wechatN24_2_0_))], groupByItems=[], orderByItems=[], limit=null, subQueryStatement=null, subQueryStatements=[], subQueryConditions=[])
2019-01-18 18:04:59 INFO ShardingSphere-SQL:71 - Actual SQL: ds0 ::: select account0_.id as id1_2_0_, account0_.createTime as createTi2_2_0_, account0_.updateTime as updateTi3_2_0_, account0_.accountType as accountT4_2_0_, account0_.address as address5_2_0_, account0_.birthday as birthday6_2_0_, account0_.channel as channel7_2_0_, account0_.clientLoginTime as clientLo8_2_0_, account0_.comment as comment9_2_0_, account0_.mergeComment as mergeCo10_2_0_, account0_.mergedTs as mergedT11_2_0_, account0_.name as name12_2_0_, account0_.namePinyin as namePin13_2_0_, account0_.password as passwor14_2_0_, account0_.phone as phone15_2_0_, account0_.photo as photo16_2_0_, account0_.sex as sex17_2_0_, account0_.ssid as ssid18_2_0_, account0_.status as status19_2_0_, account0_.subscribe as subscri20_2_0_, account0_.traineeIdMergeTo as trainee21_2_0_, account0_.unionId as unionId22_2_0_, account0_.wechatId as wechatI23_2_0_, account0_.wechatName as wechatN24_2_0_ from account account0_ where account0_.id=? ::: [[79839]]
2019-01-18 18:04:59 INFO ShardingSphere-SQL:71 - Rule Type: sharding
2019-01-18 18:04:59 INFO ShardingSphere-SQL:71 - Logic SQL: SELECT fcm.memberType FROM Fitness_Center fc INNER JOIN Fitness_Center_Member fcm ON fc.id = fcm.fitnessCenterId WHERE fcm.accountId = ? AND fcm.active = 1 AND fcm.inviteStatus = 5 GROUP BY fcm.memberType UNION SELECT 5 as memberType FROM Center_Consultant_Info i WHERE i.consultantId = ? AND i.status > 0 AND i.inviteStatus = 5 UNION SELECT 1 as memberType FROM Center_Trainer_Info i where i.trainerId = ? AND i.status > 0 AND i.inviteStatus = 5
2019-01-18 18:04:59 INFO ShardingSphere-SQL:71 - SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@7ed0e765), containStar=false, firstSelectItemStartPosition=7, selectListLastPosition=22, groupByLastPosition=206, items=[CommonSelectItem(expression=fcm.memberType, alias=Optional.absent())], groupByItems=[OrderItem(owner=Optional.of(fcm), name=Optional.of(memberType), orderDirection=ASC, nullOrderDirection=ASC, index=-1, expression=null, alias=Optional.absent())], orderByItems=[OrderItem(owner=Optional.of(fcm), name=Optional.of(memberType), orderDirection=ASC, nullOrderDirection=ASC, index=-1, expression=null, alias=Optional.absent())], limit=null, subQueryStatement=null, subQueryStatements=[], subQueryConditions=[])
2019-01-18 18:04:59 INFO ShardingSphere-SQL:71 - Actual SQL: ds0 ::: SELECT fcm.memberType FROM Fitness_Center fc INNER JOIN Fitness_Center_Member fcm ON fc.id = fcm.fitnessCenterId WHERE fcm.accountId = ? AND fcm.active = 1 AND fcm.inviteStatus = 5 GROUP BY fcm.memberType UNION SELECT 5 as memberType FROM Center_Consultant_Info i WHERE i.consultantId = ? AND i.status > 0 AND i.inviteStatus = 5 UNION SELECT 1 as memberType FROM Center_Trainer_Info i where i.trainerId = ? AND i.status > 0 AND i.inviteStatus = 5 ::: [[79839, 79839, 79839]]
2019-01-18 18:04:59 INFO fileLogger:74 - Request URL: /api/center/course/212/courseMissedList/2/20
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services