<h2 dir="auto">Multi-values insertion bug with key-generator in sharding databases/tables</h2>
<p dir="auto">We can reproduce the bug in a rule:</p>
<blockquote>
<p dir="auto">shard db by <code class="notranslate">id%2</code> : ds_0,ds_1<br>
shard table by <code class="notranslate">id%3</code> : user0,user1,user2</p>
</blockquote>
<h3 dir="auto">Case 1, literal multi-values insertion</h3>
<p dir="auto">Execute：<br>
<code class="notranslate">insert into user(</code>name<code class="notranslate">,</code>age<code class="notranslate">) values ('name-11',11),('name-12',12),('name-13',13),('name-14',14)</code></p>
<p dir="auto">Actual SQL：</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content=" ..ds_1 ::: insert into user1(`name`,`age`, id) values ('name-11', 11, 441401574401507332)
 ..ds_0 ::: insert into user2(`name`,`age`, id) values ('name-12', 12, 441401574401507331)
 ..ds_1 ::: insert into user0(`name`,`age`, id) values ('name-13', 13, 441401574401507330)
 ..ds_0 ::: insert into user1(`name`,`age`, id) values ('name-14', 14, 441401574401507329)"><pre class="notranslate"><code class="notranslate"> ..ds_1 ::: insert into user1(`name`,`age`, id) values ('name-11', 11, 441401574401507332)
 ..ds_0 ::: insert into user2(`name`,`age`, id) values ('name-12', 12, 441401574401507331)
 ..ds_1 ::: insert into user0(`name`,`age`, id) values ('name-13', 13, 441401574401507330)
 ..ds_0 ::: insert into user1(`name`,`age`, id) values ('name-14', 14, 441401574401507329)
</code></pre></div>
<p dir="auto">This are two issues：</p>
<ol dir="auto">
<li>ids in descending order</li>
<li>wrong sharding tables, lead to that those data can't be selected</li>
</ol>
<p dir="auto">The first row, id=441401574401507332，id%3=&gt;1，so shard to table <code class="notranslate">user1</code>，it's right。<br>
The second row, id=441401574401507331，id%3=&gt;0，so should shard to table <code class="notranslate">user0</code>，but shard to table <code class="notranslate">user2</code>, it's wrong。<br>
The second row, id%3=&gt;2，so should shard to table <code class="notranslate">user2</code>，but shard to table <code class="notranslate">user0</code>, it's wrong, too。<br>
The last row, id%3=&gt;1，so shard to table <code class="notranslate">user1</code>，it's right。</p>
<p dir="auto">We can find if make ids reverse, remainders are 1,2,0,1，just match the sharding tables number.</p>
<p dir="auto">We learn more into shardingsphere sourcecodes，it generator 4 keys and binding them to sharding tables at first, like the ascending order. And then binding keys and literal columns fields(name, age)，it reverse keys to descending order by <code class="notranslate">List.descendingIterator</code>. Finally, each key orders can't match its own row.</p>
<p dir="auto">The related codes here：</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="org.apache.shardingsphere.sharding.rewrite.token.generator.impl.keygen.GeneratedKeyInsertValuesTokenGenerator：generateSQLToken()
Iterator&lt;Comparable&lt;?&gt;&gt; generatedValues = generatedKey.getGeneratedValues().descendingIterator();"><pre class="notranslate"><code class="notranslate">org.apache.shardingsphere.sharding.rewrite.token.generator.impl.keygen.GeneratedKeyInsertValuesTokenGenerator：generateSQLToken()
Iterator&lt;Comparable&lt;?&gt;&gt; generatedValues = generatedKey.getGeneratedValues().descendingIterator();
</code></pre></div>
<h3 dir="auto">Case 2, multi-values insertion with placeholders</h3>
<p dir="auto">SQL：<br>
<code class="notranslate">insert into user(</code>name<code class="notranslate">,</code>age<code class="notranslate">) values (?,?),(?,?),(?,?),(?,?)</code></p>
<p dir="auto">Java codes：</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="try(PreparedStatement preparedStatement = con.prepareStatement(sql)){
      for (int i = 0; i &lt; 4; i++){
             preparedStatement.setString(i * 2 + 1,&quot;name-&quot; + i);
             preparedStatement.setInt(i * 2 + 2,i);
      }
      preparedStatement.execute();
}"><pre class="notranslate"><code class="notranslate">try(PreparedStatement preparedStatement = con.prepareStatement(sql)){
      for (int i = 0; i &lt; 4; i++){
             preparedStatement.setString(i * 2 + 1,"name-" + i);
             preparedStatement.setInt(i * 2 + 2,i);
      }
      preparedStatement.execute();
}
</code></pre></div>
<p dir="auto">Actual SQL：</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="..ds_0 ::: insert into user1(`name`,`age`, id) values (?, ?, ?) ::: [name-0, 0, 441306093067436035] 
..ds_1 ::: insert into user2(`name`,`age`, id) values (?, ?, ?) ::: [name-1, 1, 441306093067436034] 
..ds_0 ::: insert into user0(`name`,`age`, id) values (?, ?, ?) ::: [name-2, 2, 441306093067436033] 
..ds_1 ::: insert into user1(`name`,`age`, id) values (?, ?, ?) ::: [name-3, 3, 441306093067436032]"><pre class="notranslate"><code class="notranslate">..ds_0 ::: insert into user1(`name`,`age`, id) values (?, ?, ?) ::: [name-0, 0, 441306093067436035] 
..ds_1 ::: insert into user2(`name`,`age`, id) values (?, ?, ?) ::: [name-1, 1, 441306093067436034] 
..ds_0 ::: insert into user0(`name`,`age`, id) values (?, ?, ?) ::: [name-2, 2, 441306093067436033] 
..ds_1 ::: insert into user1(`name`,`age`, id) values (?, ?, ?) ::: [name-3, 3, 441306093067436032]
</code></pre></div>
<p dir="auto">The same problem，but codes is in different class：</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="org.apache.shardingsphere.sharding.rewrite.parameter.impl.ShardingGeneratedKeyInsertValueParameterRewriter：rewrite()
Iterator&lt;Comparable&lt;?&gt;&gt; generatedValues = shardingRouteContext.getGeneratedKey().get().getGeneratedValues().descendingIterator();"><pre class="notranslate"><code class="notranslate">org.apache.shardingsphere.sharding.rewrite.parameter.impl.ShardingGeneratedKeyInsertValueParameterRewriter：rewrite()
Iterator&lt;Comparable&lt;?&gt;&gt; generatedValues = shardingRouteContext.getGeneratedKey().get().getGeneratedValues().descendingIterator();
</code></pre></div>
<p dir="auto">This case is also reported by issue <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="565175736" data-permission-text="Title is private" data-url="https://github.com/apache/shardingsphere/issues/4306" data-hovercard-type="issue" data-hovercard-url="/apache/shardingsphere/issues/4306/hovercard" href="https://github.com/apache/shardingsphere/issues/4306">#4306</a> .</p>
<h3 dir="auto">fix</h3>
<p dir="auto">We will fix it by replace <code class="notranslate">descendingIterator</code> to <code class="notranslate">iterator</code>.</p>
<h2 dir="auto">Background</h2>
<p dir="auto">At present, when community users use ShardingSphere, they report that due to the large number of managed data sources, a large number of single tables are loaded into the memory by default, resulting in slower startup speed and taking up more memory space.</p>
<p dir="auto">Considering that in the data source registered by the user, there are a large number of single tables that do not need to be used or managed by ShardingSphere, so it is necessary to design a mechanism that allows users to actively import single tables on demand, avoiding automatic Load single table metadata.</p>
<h2 dir="auto">Metadata loading logic optimization</h2>
<h3 dir="auto">Start-up phase</h3>
<p dir="auto">According to the process of the startup phase, the automatic loading of a single table mainly depends on the automatic loading logic in <code class="notranslate">SingleTableRule</code>, so the loading logic needs to be removed. In addition, the <code class="notranslate">SingleTableRuleConfiguration</code> configuration needs to be added to support the needs of JDBC end users to import a single table, and the DistSQL <code class="notranslate">LOAD</code> statement configuration is stored through <code class="notranslate">SingleTableRuleConfiguration</code> to persist the YAML configuration to ZK. <code class="notranslate">SingleTableRuleConfiguration</code> is configured as follows:</p>
<div class="highlight highlight-source-yaml notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="# YAML configuration
databaseName: sharding_db

- !SINGLE
  tables:
    - ds_0.t_single
    # PostgreSQL, openGauss support multiple schemas
    - ds_1.public.t_config,ds_1.test.t_config
    - ds_2.*
    - ds_3.*
    - *.*
  # controls CREATE TABLE routing, required in DistSQL
  defaultDataSource: ds_0
  
# ZK structure (the original YAML will be loaded into a single table and then expanded and stored)
- !SINGLE
  tables:
    - ds_0.t_single
    # PostgreSQL, openGauss support multiple schemas
    - ds_1.public.t_config,ds_1.test.t_config
    - ds_2.t_1,ds_2.t_2
    - ds_3.t_3,ds_3.t_4
    - ds_4.t_5,ds_4.t_6
  # controls CREATE TABLE routing, required in DistSQL
  defaultDataSource: ds_0"><pre class="notranslate"><span class="pl-c"><span class="pl-c">#</span> YAML configuration</span>
<span class="pl-ent">databaseName</span>: <span class="pl-s">sharding_db</span>

- <span class="pl-k">!SINGLE</span>
  <span class="pl-ent">tables</span>:
    - <span class="pl-s">ds_0.t_single</span>
    <span class="pl-c"><span class="pl-c">#</span> PostgreSQL, openGauss support multiple schemas</span>
    - <span class="pl-s">ds_1.public.t_config,ds_1.test.t_config</span>
    - <span class="pl-s">ds_2.*</span>
    - <span class="pl-s">ds_3.*</span>
    <span class="pl-s">- *.*</span>
  <span class="pl-c"><span class="pl-c">#</span> controls CREATE TABLE routing, required in DistSQL</span>
  <span class="pl-ent">defaultDataSource</span>: <span class="pl-s">ds_0</span>
  
<span class="pl-c"><span class="pl-c">#</span> ZK structure (the original YAML will be loaded into a single table and then expanded and stored)</span>
- <span class="pl-k">!SINGLE</span>
  <span class="pl-ent">tables</span>:
    - <span class="pl-s">ds_0.t_single</span>
    <span class="pl-c"><span class="pl-c">#</span> PostgreSQL, openGauss support multiple schemas</span>
    - <span class="pl-s">ds_1.public.t_config,ds_1.test.t_config</span>
    - <span class="pl-s">ds_2.t_1,ds_2.t_2</span>
    - <span class="pl-s">ds_3.t_3,ds_3.t_4</span>
    - <span class="pl-s">ds_4.t_5,ds_4.t_6</span>
  <span class="pl-c"><span class="pl-c">#</span> controls CREATE TABLE routing, required in DistSQL</span>
  <span class="pl-ent">defaultDataSource</span>: <span class="pl-s">ds_0</span></pre></div>
<ul dir="auto">
<li>The <code class="notranslate">tables</code> attribute allows users to configure single tables to be loaded, supports MySQL-style <code class="notranslate">database.table</code> configuration, and PostgreSQL/openGauss-style <code class="notranslate">database.schema.table</code> configuration. If users need to load all configurations, they can import all single tables under a certain data source through the <code class="notranslate">*</code> wildcard. If the user-configured data source is in a logical data source, the data source will be converted to a logical data source.</li>
<li><code class="notranslate">defaultDataSource</code> is used to support the current DistSQL statement SET DEFAULT SINGLE TABLE STORAGE UNIT = ds_0, defaultDataSource will be used for CREATE TABLE statement routing processing;</li>
</ul>
<p dir="auto">The optimized new processing flow is as follows:</p>
<ol dir="auto">
<li>In the startup phase, the DatabaseRuleConfiguration will be initialized first. When the SingleTableRuleConfiguration is initialized, the single table will be loaded according to the YAML configuration, and the expanded configuration will be built, then stored in the SingleTableRuleConfiguration, and then the SingleTableRuleConfiguration will be persisted in the ZK;</li>
<li>Initialize DatabaseRule according to RuleConfiguration, and SingleTableRule initialization will load a single table according to the tables attribute configured in SingleTableRuleConfiguration;</li>
<li>Get all the tables according to the TableContainedRule interface method, and load the real metadata;</li>
<li>Decorate real metadata as logical metadata;</li>
<li>Metadata is persisted to ZK and synchronized to each instance;</li>
</ol>
<h3 dir="auto">Register storage unit</h3>
<p dir="auto">Execute REGISTER STORAGE UNIT DistSQL, the optimized execution flow is as follows:</p>
<ol dir="auto">
<li>Call RegisterStorageUnitBackendHandler and verify the connectivity of the storage data source;</li>
<li>Then call the ContextManager#addResources method to add storage resources through the management center;</li>
<li>Call the ShardingSphereDatabasesFactory.create method to recreate the logic library object for adding storage resources (this step is consistent with the logic of loading metadata in the startup phase, first initialize SingleTableRuleConfiguration, and then initialize SingleTableRule, SingleTableRule will load a single table according to SingleTableRuleConfiguration, metadata loading engine Load according to Table in Rule);</li>
<li>Replace the corresponding ShardingSphereDatabase object in ShardingSphereMetaData.</li>
</ol>
<h3 dir="auto">Execute DDL statements</h3>
<p dir="auto">Execute the DDL statement because the logic itself is to load metadata according to a certain table, so the logic of loading metadata does not need to be adjusted, but after executing the DDL statement, refresh the SingleTableRuleConfiguration, persist the newly created single table into ZK, and synchronize to individual instances. The new processing flow is as follows:</p>
<ol dir="auto">
<li>Process DDL statements through KernelProcessor, execute and merge;</li>
<li>MetaDataRefreshEngine calls CreateTableStatementSchemaRefresher to execute metadata refresh logic;</li>
<li>CreateTableStatementSchemaRefresher internally calls the GenericSchemaBuilder#build method to load and decorate the metadata of the new table;</li>
<li>Synchronize metadata information through other Proxy nodes through the SchemaAlteredEvent event;</li>
<li>Through the RuleConfigurationsChangedEvent event, the single table added or deleted by the DDL statement is synchronized to zk and each node;</li>
</ol>
<h3 dir="auto">New DistSQL</h3>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="LOAD SINGLE TABLE ds_0.t_single, ds_1.public.t_config, ds_1.test.t_config [INTO databaseName];
LOAD SINGLE TABLE ds_2.*, ds_3.* [INTO databaseName];

-- DistSQL queries the underlying used and unused single tables, not loaded into memory
SHOW SINGLE TABLES
SHOW UNLOADED SINGLE TABLES"><pre class="notranslate">LOAD SINGLE TABLE <span class="pl-c1">ds_0</span>.<span class="pl-c1">t_single</span>, <span class="pl-c1">ds_1</span>.<span class="pl-c1">public</span>.t_config, <span class="pl-c1">ds_1</span>.<span class="pl-c1">test</span>.t_config [INTO databaseName];
LOAD SINGLE TABLE ds_2.<span class="pl-k">*</span>, ds_3.<span class="pl-k">*</span> [INTO databaseName];

<span class="pl-c"><span class="pl-c">--</span> DistSQL queries the underlying used and unused single tables, not loaded into memory</span>
SHOW SINGLE TABLES
SHOW UNLOADED SINGLE TABLES</pre></div>
<h2 dir="auto">Development task</h2>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked="">  Rename SingleTableXXX to SingleXXX, such as SingleTableRuleConfiguration to SingleRuleConfiguration</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked="">  Add SingleTableRuleConfiguration tables configuration, and implement configuration persistence &amp; initialization loading single table logic;</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked="">  SingleTableRule load single table logic adjustment, load according to SingleTableRuleConfiguration configuration, no configuration will not be loaded;</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked="">  MetaDataRefresher adds SingleTableRuleConfiguration refresh after DDL statement is executed;</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked="">  Add LOAD, UNLOAD, SHOW LOADED TABLE, SHOW UNLOADED TABLE DistSQL</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox">  Add test case</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox">  Add document description</li>
</ul>