FlyingZC commented on code in PR #24437: URL: https://github.com/apache/shardingsphere/pull/24437#discussion_r1124043210
########## docs/blog/content/material/2022_10_25_ShardingSphere_5.2.1_is_released — Here_are_the_highlights.en.md: ########## @@ -0,0 +1,290 @@ ++++ +title = "ShardingSphere 5.2.1 is released — Here are the highlights" +weight = 77 +chapter = true ++++ + +Our new 5.2.1 release brings new features and enhancements such as the ShardingSphere system database, SQL HINT mandatory sharding routing, asynchronous data consistency check, and support for Consul and Nacos governance center. + +# Introduction + +Following 1.5 months in development, [Apache ShardingSphere](https://shardingsphere.apache.org/) 5.2.1 is released. Our community merged 614 PRs from teams and individuals around the world. The [resulting 5.2.1 release](https://github.com/apache/shardingsphere/blob/master/RELEASE-NOTES.md) has been optimized in terms of features, performance, testing, documentation, examples, etc. + +The new release brings the following highlights: + +- ShardingSphere system database +- SQL HINT mandatory sharding routing +- Asynchronous data consistency check + +Version 5.2.1 introduces a new ShardingSphere system database to provide statistical information for distributed databases. **The statistical information can help the SQL Federation execution engine evaluate the execution costs to select an execution plan with the lowest costs.** Moreover, ShardingSphere can collect the distribution information after data sharding, providing references for automatic sharding scaling management. + +**SQL HINT is an important feature provided by ShardingSphere 5. x, with which users can flexibly control routes through SQL annotations.** The new release enhances the SQL HINT capability and supports data-sharding mandatory routing. + +Users only need to add annotations in the format: `/* SHARDINGSPHERE_HINT: t_order.SHARDING_DATABASE_VALUE=1, t_order.SHARDING_TABLE_VALUE=1 */` before transferring sharding values to the ShardingSphere routing engine. Compared with the former HintManager method, SQL HINT is more flexible without requiring users to write code. + +**The new version also enhances the data consistency check capability during data migration, enabling asynchronous execution of the check. Users can view the data migration progress through** [**DistSQL**](https://medium.com/nerd-for-tech/intro-to-distsql-an-open-source-more-powerful-sql-bada4099211)**, which improves the ease of data migration.** + +Moreover, the **governance center now supports Consul and Nacos**, providing more choices for users. SQL compatibility is greatly improved. Read/write splitting has also been optimized, as Proxy can be started forcibly by using the `-f` parameter after the secondary database is disabled. + +Next, this post will introduce the updates of ShardingSphere 5.2.1 in detail. + +# Notable Highlights + +## ShardingSphere System Database + +Like [MySQL](https://www.mysql.com/) and [PostgreSQL](https://www.postgresql.org/) system databases, ShardingSphere 5.2.1 introduces a new system database to manage the metadata of databases. + +ShardingSphere system database mainly stores dynamic and static metadata. Dynamic metadata refers to frequently updated data, such as statistical information from distributed databases, which needs to be collected and maintained regularly through built-in scheduling tasks. + +In comparison, static metadata refers to data that will remain the same without user modification. An example is the status parameter of the ShardingSphere distributed database set by users, which only needs to be stored in the metadata database for querying. + +As this function is currently in the experimental phase, you need to start collecting metadata through the following configuration. + +```java +proxy-metadata-collector-enabled: true +``` + +Connect Proxy through the command line and then execute the `SHOW DATABASES` statement. We can see the new `shardingsphere` system database which stores the metadata of distributed databases. + + + +Currently, the `sharding_table_statistics` table is added to the `shardingsphere` system database, used for collecting the distribution information of sharding tables, including `row_count` and `size`. + + + +Statistical information provided by the ShardingSphere system database can help the SQL Federation execution engine evaluate the execution cost. This allows for the selection of a suitable association order and method and achieves efficient execution. + +Moreover, by collecting the data distribution information and loading information of storage nodes, ShardingSphere can carry out automatic sharding scaling, reducing the operation & maintenance costs for users. + +## SQL HINT Mandatory Sharding Routing + +In some special business scenarios, the fields used for sharding exist in external business logic rather than SQL, database, and table structures. Currently, `Hint` is needed to introduce sharding key value to complete sharding routing. + +Before version 5.2.1, there were two ways to use `Hint`. One way is to use it through HintManager in the JDBC access port, while another way is to start the `proxy-hint-enabled` parameter in the Proxy access port. + +In the first case, users need to write codes to call the `addDatabaseShardingValue` and `addTableShardingValue` methods to set the values for the database and table sharding. However, if the HintManager method is used, users have to modify the original logic at some cost. + +```java +// Sharding database and table with using HintManagerString sql = "SELECT * FROM t_order"; +try (HintManager hintManager = HintManager.getInstance(); + Connection conn = dataSource.getConnection(); + PreparedStatement preparedStatement = conn.prepareStatement(sql)) { + hintManager.addDatabaseShardingValue("t_order", 1); + hintManager.addTableShardingValue("t_order", 2); + try (ResultSet rs = preparedStatement.executeQuery()) { + while (rs.next()) { + // ... } Review Comment: Please move the `}` to the next line. ########## docs/blog/content/material/2022_10_25_ShardingSphere_5.2.1_is_released — Here_are_the_highlights.en.md: ########## @@ -0,0 +1,290 @@ ++++ +title = "ShardingSphere 5.2.1 is released — Here are the highlights" +weight = 77 +chapter = true ++++ + +Our new 5.2.1 release brings new features and enhancements such as the ShardingSphere system database, SQL HINT mandatory sharding routing, asynchronous data consistency check, and support for Consul and Nacos governance center. + +# Introduction + +Following 1.5 months in development, [Apache ShardingSphere](https://shardingsphere.apache.org/) 5.2.1 is released. Our community merged 614 PRs from teams and individuals around the world. The [resulting 5.2.1 release](https://github.com/apache/shardingsphere/blob/master/RELEASE-NOTES.md) has been optimized in terms of features, performance, testing, documentation, examples, etc. + +The new release brings the following highlights: + +- ShardingSphere system database +- SQL HINT mandatory sharding routing +- Asynchronous data consistency check + +Version 5.2.1 introduces a new ShardingSphere system database to provide statistical information for distributed databases. **The statistical information can help the SQL Federation execution engine evaluate the execution costs to select an execution plan with the lowest costs.** Moreover, ShardingSphere can collect the distribution information after data sharding, providing references for automatic sharding scaling management. + +**SQL HINT is an important feature provided by ShardingSphere 5. x, with which users can flexibly control routes through SQL annotations.** The new release enhances the SQL HINT capability and supports data-sharding mandatory routing. + +Users only need to add annotations in the format: `/* SHARDINGSPHERE_HINT: t_order.SHARDING_DATABASE_VALUE=1, t_order.SHARDING_TABLE_VALUE=1 */` before transferring sharding values to the ShardingSphere routing engine. Compared with the former HintManager method, SQL HINT is more flexible without requiring users to write code. + +**The new version also enhances the data consistency check capability during data migration, enabling asynchronous execution of the check. Users can view the data migration progress through** [**DistSQL**](https://medium.com/nerd-for-tech/intro-to-distsql-an-open-source-more-powerful-sql-bada4099211)**, which improves the ease of data migration.** + +Moreover, the **governance center now supports Consul and Nacos**, providing more choices for users. SQL compatibility is greatly improved. Read/write splitting has also been optimized, as Proxy can be started forcibly by using the `-f` parameter after the secondary database is disabled. + +Next, this post will introduce the updates of ShardingSphere 5.2.1 in detail. + +# Notable Highlights + +## ShardingSphere System Database + +Like [MySQL](https://www.mysql.com/) and [PostgreSQL](https://www.postgresql.org/) system databases, ShardingSphere 5.2.1 introduces a new system database to manage the metadata of databases. + +ShardingSphere system database mainly stores dynamic and static metadata. Dynamic metadata refers to frequently updated data, such as statistical information from distributed databases, which needs to be collected and maintained regularly through built-in scheduling tasks. + +In comparison, static metadata refers to data that will remain the same without user modification. An example is the status parameter of the ShardingSphere distributed database set by users, which only needs to be stored in the metadata database for querying. + +As this function is currently in the experimental phase, you need to start collecting metadata through the following configuration. + +```java +proxy-metadata-collector-enabled: true +``` + +Connect Proxy through the command line and then execute the `SHOW DATABASES` statement. We can see the new `shardingsphere` system database which stores the metadata of distributed databases. + + + +Currently, the `sharding_table_statistics` table is added to the `shardingsphere` system database, used for collecting the distribution information of sharding tables, including `row_count` and `size`. + + + +Statistical information provided by the ShardingSphere system database can help the SQL Federation execution engine evaluate the execution cost. This allows for the selection of a suitable association order and method and achieves efficient execution. + +Moreover, by collecting the data distribution information and loading information of storage nodes, ShardingSphere can carry out automatic sharding scaling, reducing the operation & maintenance costs for users. + +## SQL HINT Mandatory Sharding Routing + +In some special business scenarios, the fields used for sharding exist in external business logic rather than SQL, database, and table structures. Currently, `Hint` is needed to introduce sharding key value to complete sharding routing. + +Before version 5.2.1, there were two ways to use `Hint`. One way is to use it through HintManager in the JDBC access port, while another way is to start the `proxy-hint-enabled` parameter in the Proxy access port. + +In the first case, users need to write codes to call the `addDatabaseShardingValue` and `addTableShardingValue` methods to set the values for the database and table sharding. However, if the HintManager method is used, users have to modify the original logic at some cost. + +```java +// Sharding database and table with using HintManagerString sql = "SELECT * FROM t_order"; +try (HintManager hintManager = HintManager.getInstance(); + Connection conn = dataSource.getConnection(); + PreparedStatement preparedStatement = conn.prepareStatement(sql)) { + hintManager.addDatabaseShardingValue("t_order", 1); + hintManager.addTableShardingValue("t_order", 2); + try (ResultSet rs = preparedStatement.executeQuery()) { + while (rs.next()) { + // ... } + } +} +``` + +If it’s the second case, start the `proxy-hint-enabled` parameter first, and then clear the sharding values through the following [DistSQL](https://shardingsphere.apache.org/document/5.1.0/en/concepts/distsql/) configuration. + +Nevertheless, the Hint-based method will transform Proxy's thread processing model from IO multiplexing to one independent thread for each request, which will reduce Proxy's throughput. This means that users have to make tradeoffs. + +```java +-- For the current connection, add sharding values yy to table xx. xx: logical table name; yy: database sharding value +ADD SHARDING HINT DATABASE_VALUE t_order= 100;-- For the current connection, add sharding values yy to table xx. xx: logical table name; yy: table sharding value Review Comment: Please move the comment to the next line. -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
