Whether there is a global routing configuration？

[huangxinjian]

Background

Suppose there is the following scenario of horizontal sub-database sub-table

In the old single database, there are many tables like User that are only divided into databases but not tables.There may be only some tables that require further table splitting operations， Like order.

From my current understanding, the following configuration needs to be done

// divided into databases and table
result.getTables().add(getOrderTableRuleConfiguration());
result.getTables().add(getOrderItemTableRuleConfiguration());
// only divided into databases
result.getTables().add(new ShardingTableRuleConfiguration("t_user"));

Question

So when the business database is divided into horizontal sub-database and sub-table, there may be thousands of tables like User. Do we need to configure a tableRule for each table?

I coludn't find the answer on doc.

I wonder if there is a default sub-database or sub-table routing rule? If a table is not configured, it will be routed according to the default global routing rules

[RaigorJiang]

Hi @huangxinjian BroadcastTables may be what you need:

• Documentation: https://shardingsphere.apache.org/document/current/en/features/sharding/
• Example: https://github.com/apache/shardingsphere/blob/master/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/java/org/apache/shardingsphere/example/sharding/raw/jdbc/config/ShardingDatabasesConfigurationPrecise.java

[huangxinjian]

Hi @RaigorJiang ， Thanks for your answer！ But Broadcast tables cannot be used. If a broadcast table is used, then the data representing user is exactly the same on db1 db2 db3. The scenario I want is that part of the data in the t_user is routed to db1, part of it is routed to db2, and part of it is routed to db3.

Moreover, even if the broadcast table meets the scenario, I still need to configure all the tables in the existing business library through the following code.... The final configuration information will be huge :(

result.getBroadcastTables().add("t_user");
result.getBroadcastTables().add("t_userInfo");
result.getBroadcastTables().add("t_xxx");
result.getBroadcastTables().add("t_aaa");
// tooooooo much config code need to add. 

[RaigorJiang]

The scenario I want is that part of the data in the t_user is routed to db1, part of it is routed to db2, and part of it is routed to db3.

So your table t_uesr is actually a sharding table, so you need to configure sharding rule. Broadcast table is really not suitable for it.

At present, in ShardingSphere, users need to specify which tables are sharding tables, and there is no global configuration as you might imagine.

[huangxinjian]

That sounds bad. So don't we have to configure thousands of tables in actual production? Or, in actual production environment, we should use multiple data sources in a project to distinguish data sources that are separate databases from those that do not?

Because according to the current design, routing must be required as long as the sub-database is required, so the ruleConfiguration of each table must be configured.

[RaigorJiang]

Yes, as long as it is a sharding table, explicit configuration is required. Only single table does not need to be configured.

[huangxinjian]

Will you consider optimizing this in the future? Our current database has more than 1000 tables. If we want to divide the database horizontally to reduce the pressure on the database, we need to configure the information of more than 1000 tables in the configuration file.....

Moreover, the tables newly created by some colleagues in the future need to be notified to the maintainer in time. The sharding configuration of the newly added table must be maintained by the maintainer, otherwise the data will not be stored correctly.......

[RaigorJiang]

Will you consider optimizing this in the future? Our current database has more than 1000 tables. If we want to divide the database horizontally to reduce the pressure on the database, we need to configure the information of more than 1000 tables in the configuration file.....

Moreover, the tables newly created by some colleagues in the future need to be notified to the maintainer in time. The sharding configuration of the newly added table must be maintained by the maintainer, otherwise the data will not be stored correctly.......

Probably not, because everyone's application scenarios are different. ShardingSphere should consider more general cases.

If you have any suggestion (of course, to meet a variety of scenarios), you can open a discussion.

[github-actions[bot]]

Hello , this issue has not received a reply for several days. This issue is supposed to be closed.

[RaigorJiang]

Closed due to no reply for a long time.