Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about Java + Mybatis how to achieve e-commerce system sub-table query, many people may not know much, in order to let you know more, Xiaobian summarized the following content for you, I hope you can harvest according to this article.

Before I begin, I'm going to talk a little bit about theory. To be honest, each of our systems does not start to divide tables and libraries as soon as they come up. Instead, when the amount of data reaches a certain level, and after all kinds of optimization methods are used, there is still a bottleneck in the system, only use the sub-table. Because sub-tables increase the complexity of the business, they are usually forced operations.

In general, MySQL needs to find ways to optimize the design and implementation when the single expression reaches tens of millions of data and continues to grow. Usually our optimization method has the following six steps:

Optimize your SQL and indexes first, and the effect is immediate. After this optimization, if there is still a performance bottleneck, you can adopt the second architectural design.

Second plus cache, memcached, redis, etc. Caching is now a necessary and reliable means of optimizing for hot data.

After the third and above are done, or slow, do master-slave copy or master-master copy, read and write separation, can be done in the application layer, high efficiency, can also use third-party tools.

Fourth, if the above are still slow, do not think about doing segmentation, mysql comes with partition table, try this first, transparent to your application, no need to change the code. However, sql statements need to be optimized for partition tables. SQL conditions should include columns with partition conditions, so that queries can be located on a small number of partitions, otherwise all partitions will be scanned. In addition, partition tables have some pits. This requirement for database knowledge is relatively high. If the company is not equipped with DBA or the technical reserve is insufficient, it should be used with caution.

Fifth, if all of the above are done, then do vertical splitting first. In fact, according to the coupling degree of your modules, divide a large system into multiple small systems, that is, distributed systems. This is now the major systems keen to adopt the program, can be given priority.

The sixth is horizontal segmentation. For tables with large data volume, this step is the most troublesome and can test the technical level most. To choose a reasonable sharding key. In order to have a good query efficiency, the table structure should also be changed, do a certain amount of redundancy, the application should also be changed, sql as far as possible with sharding key, the data will be located on the limited table to look up, rather than scanning all the tables.

MySQL databases are generally evolved according to this step, and the cost is also from low to high. Well, now that I have introduced this knowledge, I will give you a simple sub-table implementation.

Assuming we now have 10 million data, the project requires two tables. For example, the xttblog table would be split into xttblog_0 and xttblog_1. Note that I am only an example here. In actual business, horizontal tables will be divided according to specific data volume and growth trend, and the number of tables will far exceed dozens.

According to the above description, I need to divide 10 million pieces of data into two tables, so my approach is to use the auto-growth ID of the xttblog table to achieve the table division. id%2 == 0 operation table xttblog_0, the same as Id%2 == 1 operation table xttblog_1.

After you understand this, let's take a look at the code related to our MyBatis query for subtables.

Although this SQL is very simple, sub-tables will affect the complexity of some other businesses.

Now, assuming we want to query 1000001, our actual SQL execution looks like this:

After reading the above content, do you have any further understanding of how Java + Mybatis realizes the sub-table query of e-commerce system? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.