What exactly is affecting our performance

Preface

Note: The scenario discussed here is the Mis,oa class of systems

Most students see this problem, the first reaction is a bad SQL statement, not indexed, which has become even a kind of inertial thinking.
When found that the performance problems, generally think of adding an index, or the transformation of the connection, remove the "not exists" and so on, but the effect is often not ideal, or after a period of time, the effect is not ideal, at this time, the general people will feel that the problem is that they are not the great God.

Body

What we are sharing today is: how many ways can we take to improve performance, not the great God?
Performance has three major killers: 1) bad SQL use; 2) bad data structure; 3) bad business model
As a practical example, in the requirements tracking function in the EPO material procurement system, you can find all the historical submission request forms. The enquiry includes the status of all links to the completion of the issuance of the receipt of the notification from the order-----Request and acceptance. The initial approach is to connect the requirements table, demand table, order form, acceptance form, receiving notification form, and issuance table through join to form a view. When the amount of data is small, it seems that everything is so perfect, but then it is not so good. So, later we took a series of optimizations.

Phase One: materialized views

When the join, the problem occurs in the join calculation process, the data volume is large, the computation cost will be large, the performance will be degraded. However, when the problem came the day, for this problem, we are very calm face, do a materialized view can be, every night to calculate, the next day to query materialized view. The use of native SQL functions, materialized views, for everyone, extremely easy, low investment, high yield, the effect is immediate.
materialized views: sacrificing real-time, sacrificing storage space for performance gains .

Phase two: Changing the data structure

as the amount of data increases, we find that materialized views are calculated more slowly, albeit at night, but as everyone realizes that materialized views can solve the problem, large quantities begin to use materialized views, and all are put into the night, the degree of abuse even alarmed the DBA, At night the DB load actually exceeds the daytime peak operating period.
one problem with materialized views hidden in this scenario is that data that is never changed will be counted repeatedly. For example, there is a 2010 demand list, the application is a computer, the company stipulated that the computer 4 years of scrap, if we run materialized view in 2015 years, the data corresponding to the demand sheet will never change, but in each materialization process this demand sheet will be join into the view, And the process of join generates computational cost.
all we have to do now is to get rid of those invalid calculations by changing the data structure. There are a number of options for separating historical data, manifested tables, and so on. We take the form of the manifested table, the manifested table will be differentiated, such as the scrap request just mentioned or the completion of the demand list, such demand is no longer change, we give the 不会变化 mark. And for those that have not yet been used to complete the demand list, we mark as 待计算 , for those 待计算 demand list We recalculate every night, delete the original data, fill up the latest data. In the current data situation, the demand list to be calculated accounted for less than 5% of the total order, compared to the previous way we omitted at least 95% invalid calculation process.
change data structure: Sacrifice storage space, kill invalid computation process, thus effectively reduce database load .

Phase Three: Optimizing the business model

Demand tracking in a query condition is the name of the item, for such text-based query conditions, 99% of the students choose to be "like%xyz%", but only half of the students know that this style is not used to index, half of the students and 99% of the students will think that there is no way. It is true that technically 99% of people can't think of a way, technically this problem is not solved, because we are not the great God. But what we can share with you is that this is not a bad SQL notation, but a poor business model.
For example, we can analyze through the backstage, we like to use "computer", "host" such keywords, and input "computer" in our material library is definitely "tablet computer", "laptop", "host" corresponding to the "computer host", we in the background by the "computer" into "tablet", " Laptop "," Computer host "," host "to convert" computer host ", you can use" like computer host% "," like Tablet pc% "," like notebook computer% ". Through optimization of the business model, select the appropriate SQL statement. In the example above, 80% of the query criteria can be converted to "xyz%" in the background, which can be used to the familiar index.
Optimize your business model: Streamline query conditions and better use our known SQL techniques with operational data analysis .

When we are not a big God, when we are discovering performance problems, we can analyze the way it is: Have you ever made a common-sense SQL error? Have you chosen the right data structure? Have you ever thought about a business model that sucks?

What exactly is affecting our performance