[GitHub] [incubator-shardingsphere] ygycomon commented on issue #3366: Sharding-jdbc execute sql, in(x, x, x) performance issues

[GitBox <gi...@apache.org>]

ygycomon commented on issue #3366: Sharding-jdbc execute sql, in(x,x,x) performance issues
URL: https://github.com/apache/incubator-shardingsphere/issues/3366#issuecomment-547239138
 
 
   Same issue with me.
   
   I copy the demo code from https://shardingsphere.apache.org/document/current/en/manual/sharding-jdbc/usage/sharding/.
   The <Rule Configuration Based on Java> which is the simplest way to run.
   
   SQL:select * from test_table where user_id in (1, 2, 3) 
   
   I use Wireshark to get all requests, it shows Shardingsphere sends 9 Query Requests to DB.
   select * from act_money_account_1 where user_id in (1, 2, 3)
   select * from act_money_account_1 where user_id in (1, 2, 3)
   select * from act_money_account_1 where user_id in (1, 2, 3)
   select * from act_money_account_2 where user_id in (1, 2, 3)
   select * from act_money_account_2 where user_id in (1, 2, 3)
   select * from act_money_account_2 where user_id in (1, 2, 3)
   select * from act_money_account_3 where user_id in (1, 2, 3)
   select * from act_money_account_3 where user_id in (1, 2, 3)
   select * from act_money_account_3 where user_id in (1, 2, 3)
   
   Code:
   `
   Map<String, DataSource> dataSourceMap = new HashMap<>();
   BasicDataSource dataSource1 = new BasicDataSource();
   dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
   dataSource1.setUrl("jdbc:mysql://10.0.8.2:3306/test_00");
   dataSource1.setUsername("test");
   dataSource1.setPassword("");
   dataSourceMap.put("ds0", dataSource1);
   
   BasicDataSource dataSource2 = new BasicDataSource();
   dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
   dataSource2.setUrl("jdbc:mysql://10.0.8.2:3306/test_00");
   dataSource2.setUsername("test");
   dataSource2.setPassword("");
   dataSourceMap.put("ds1", dataSource2);
   
   BasicDataSource dataSource3 = new BasicDataSource();
   dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
   dataSource2.setUrl("jdbc:mysql://10.0.8.2:3306/test_02");
   dataSource2.setUsername("test");
   dataSource2.setPassword("");
   dataSourceMap.put("ds2", dataSource2);
   
   TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("act_money_account","ds${0..2}.test${0..9}");
   
   orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 3}"));
   orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "test_${user_id % 10}"));
   
   ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
   shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
   
   Properties p = new Properties();
   p.setProperty("sql.show", "true");
   DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, p);
   `
   
   This issue can cause serious problem in PRD environment! Is there any solution to this?

----------------------------------------------------------------
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.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services