The Chinese community essay contest has been launched for a week, and we have received many excellent works from students one after another. Today, I would like to recommend one of them from [ID: RaigorJiang].

At the same time, welcome more partners to participate, we will continue to update excellent articles for you.

Hi, I’m Raigor, Apache ShardingSphere Committer and a senior user of ShardingSphere.

Since ShardingSphere 3.1.0, the business system IN my charge has been connected with ShardingSphere-JDBC and ShardingSphere-Proxy, forming the following mixed architecture:

Web applications access ShardingSphere-JDBC. Compared with Proxy access, Shardingsphere-JDBC reduces the calls at the network layer and has higher SQL execution performance.

On the other hand, deploy a ShardingSphere-Proxy service using data sources and sharding rules (YAML configuration) that are fully consistent with ShardingSphere-JDBC, In this way, developers and o&M personnel can connect to the Proxy through clients such as Navicat and DBeaver to query and manage large amounts of sharded data.

However, there are still some inconveniences in the process of use:

  • To add a Sharding table rule, you need to modify the configuration file on the JDBC side and synchronize the configuration file on the Proxy side.

  • If the modified configurations do not take effect in real time, restart the system.

  • As the number of sharding rules increases, the configuration file becomes longer and the maintenance complexity increases.

  • Multiple applications and proxies are deployed, and multiple configuration files are available. Manual maintenance may cause file contents to be inconsistent.

  • When applications and configuration files are deployed on cloud servers, it is always more difficult to change remote files than local files.

  • Developers have to switch back and forth between tools to manage data through Navicat while adjusting configuration through SSH;

  • And so on.

Of course, after accessing Apache ShardingSphere for data sharding, the performance of the entire application system has been greatly improved, and the inconvenience mentioned before is not a big problem. “Long live understanding!” .

In November 2021, Apache ShardingSphere 5.0.0 GA was released, and DistSQL was a blockbuster feature in 5.0.0. With DistSQL, users can be freed from the complexity of YAML configuration files and now:

  • To add a Sharding table rule, run the CREATE Sharding table rule statement and synchronize the JDBC end with the Proxy end.

  • The modification takes effect immediately without restart.

  • The number of SHARDING rules does not matter. The SHOW statement is filtered accurately: SHOW SHARDING TABLE RULE t_order

  • Multiple applications and proxies share the configuration center. You only need to maintain one configuration.

  • There is no need to modify the remote configuration file.

  • An SQL client (such as Navicat) handles data management and configuration management without switching clients.

Look, all the inconvenience is gone, the experience:

Shardingsphere-jdbc and ShardingSphere-Proxy are connected to the same registry:

Configuring the registry is also simple, like this:

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: localhost:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
  overwrite: false
Copy the code

👆 instructions may refer to the above configuration: shardingsphere.apache.org/document/cu… With this in mind, the development and operation experience has been greatly improved through DistSQL. Are there any limitations?

Unfortunately, DistSQL can only be executed through proxies, so if shardingSphere-JDBC users want to dynamically modify configurations, they need to access a Proxy through the registry to do so.

Of course, Apache ShardingSphere provides an open and rapidly developing community, and there will be more wonderful things in the future. Let’s move forward together and wait and see.

A more detailed demonstration of DistSQL can be found at community.sphere-ex.com/t/topic/123

Due to the version iteration, if discover individual statements cannot be used, also please use the official document: shardingsphere.apache.org/document/cu… .

The sphereex-Boot campaign is ongoing, and we are also launching the Sphereex-Boot Hidden task. Please click on the link to enter the sphereex-Boot campaign

Stay tuned to SphereEx, where excellent articles from the community continue to be posted.