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

Due to the rapid development of micro-service technology, in our architecture, each micro-service will correspondingly connect to a database, and the tables associated with each database (such as user tables, business tables, etc.) will synchronize data with each other. Other data operations are independent (such as log tables, operation tables, etc.). This design is based on performance considerations to reduce database capacity and try our best to avoid performance bottlenecks. This design is indeed very friendly for container, in daily operation and maintenance, such as monthly / quarterly data summary is uncomfortable, as DBA, dealing with cross-table query should be a small case, but in hibernate cross-table query, although troublesome but still can be solved. However, the demand received recently is to cross! Ku! Lian! Check!

I was thinking that in the context of microservices, cross-database queries should be the new normal.

When there is a single database, the data needed for many lists and detail pages in the system can be simply queried through the SQL join associated table; however, in the case of multiple databases, the data may be distributed on different nodes / instances, and join cannot be used across libraries, so the problem brought by join is very thorny.

In the process of development, we can only connect to one database per connection, which is a routine operation, such as

Db1 = pymysql.connect ("11.22.33.44", "yerik", "mimajiubiekanla", "shujukuming1", port=3306)

What if we want to query another database? No, just set up another connection.

Db2 = pymysql.connect ("55.66.77.88", "yerik", "mimajiubiekanla", "shujukuming2", port=3306)

How can this be done with join? some readers may want to say that it can be developed at the code level through xx operations, but how readable is the code? In addition, as far as code review is concerned, you will not be allowed to write this way, in case you quit one day, who can stand the hype of the code?

However, the solution is always more difficult than the view. Using views, we can easily implement such cross-library query requirements. We know that the so-called view is actually a stored query statement that, when called, produces a result set, and the view acts as a virtual table. Therefore:

If you want to query one or more tables, you can write complex SQL statements to achieve that if you want these SQL statements to be stored as views, then when you query, you can query the view directly.

At first, I was surprised by this idea. I always thought it was too scary to build views across libraries. After all, practice is the only criterion for testing truth. Just create a view on navicat and then run it.

It's amazing that the problem is solved in this way, or a SQL statement can solve the problem.

Through this idea, we can continue to promote: cross-table joint check, build a view, cross-database joint check, build a view. Build it and it's over. In addition, this operation also needs to consider the problem of data synchronization, because it is a joint check of multiple databases, and if the data is inconsistent, it will be a disaster. This specific problem needs to be analyzed in detail. Adding locks or configuring synchronization policies are all routine solutions. Since I don't have this requirement, I won't discuss it.

This case is very touching to me.

The so-called operation and maintenance development, is to provide development ideas in the process of operation and maintenance, the original blindly code development, encountered such a multi-database query almost want to explode with the same place, with the help of this idea, multi-database query becomes a single-table query our architecture is based on jhipster development, to operate the database needs to call hibernate through jpa. This is a conventional way of thinking, but for agile development, a web service, I use django to develop, and a direct sql is done. The query results are sorted out in a csv file and sent to users by email. Agile development is no more than that. The development of this small plug-in takes only four hours from requirement confirmation to service launch, but because of simplicity, it has been in service all the time, and there is no failure. It also saves the workload for operation and maintenance.

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.