Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you an analysis on how to optimize the performance of the production database. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Requirements: the nail group name and profile, the alias of each group member, and the personal signature information of the nail group owner need to be displayed on the nail group profile page. The information required in the requirements is stored in the following four tables: user,user_profile,groups,user_group.

Choice of scheme

Option 1:

Let's take a look at the simplest implementation, a SQL statement that does everything:

SELECT name,description,user_type,nick_name,sign FROM groups,user_group,user, user_profile WHERE groups.id =? AND groups.id=user_group.group_id AND useruser_group.user_id=user.id AND useruser_profile.user_id=user.id

Option 2:

Do it in two steps through a slightly more complicated approach:

First of all, get all the relevant information about the nail group that needs to be displayed and the alias information and group member category of all nail group members:

SELECT name,description,user_type,nick_name FROM groups,user_group,user WHERE groups.id =? AND groups.id=user_group.group_id AND useruser_group.user_id=user.id

Then find the user_id of the group owner through the user_type in the above result set in the program, and then obtain the personal signature information of the nail group owner in the user_profile table:

SELECT sign FROM user_profile WHERE user_id =?

Comparison of schemes:

The biggest difference between the two solutions is the number of interactions and SQL complexity. The actual impact is that the first solution has unnecessary access to the user_profile table (non-group owner profile information), resulting in a direct increase of IO access by about 20%. In practical application, IO operation is a very expensive resource in database application system. Especially when the PV of this function is large, the IO loss caused by the first scheme is quite large.

Many people are aware of the waste of resources caused by repeated execution of the same SQL, but this phenomenon often exists in the process of application development. The main reason is that the concept of object-oriented in the thinking of development engineers is too deep, and in order to reduce the logic of their own code development and excessive dependence on program interfaces.

The above is the analysis of how to optimize the performance of the production database shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.