Large Website access volume + large data volume => seemingly simple SQL statements will also be linked to the system

A simple SQL statement: Select count (1) From tablename with (nolock) Where Columna = A and columnb = B and columnc = C, called by a key page, although memcache cache is added, due to data structure design problems, the CPU load of the database server is as high as 100%, and the critical page response times out, resulting in extremely bad impact.

The reason is,Index missingAt the beginning of the data structure design, no index was created for the table, and then ignored that the table only tested data reached 5 millions or 6 millions levels, increasing at a rate of about 0.25 million per day. During the test, performance problems were not detected because there was no access traffic. In the stress test before the launch, the test account only had a limited number, and the system added the memcache cache according to the account, therefore, the problem was ignored again. The problem was not exposed until the second day after the system was launched, and the customer service phone was quickly cracked.

It took several working days to completely solve the problem. If the data structure is designed before development, the index is created, if the data volume is concerned during development and testing, and if the SQL statement is written, the performance is analyzed by the query analyzer.Program, Consciousness should not be less, the design before development should be done in place, the development process should be well grasped, otherwise the Cup can only be buried by yourself.