Common website database Optimization Methods

In daily work, there may be performance problems in the database of an online product from time to time, such as slow access or even failure to open the database, when you use related tools to view data, you will often see too many locks, high CPU usage on the server, and high memory usage. At this time, you need to do some optimization work. The following are some methods I have summarized and I hope you can give me some suggestions.

1. Static. For pages that are too frequently accessed, we can consider static, static, including static pages and static data. The whole page is static, which is easy to understand; Data static is to form a file with the data returned by the database and then save it to a file such as XML. Each access reads XML file data, generally, static data is generated before access, and generated after access.ProgramAutomatically generated. When static is used, what static is used? Different use cases require different static generation policies.

2. Sub-tables. To generate a large amount of data in each cycle, you can consider using certain policies to store data in multiple data tables. For example, the Forum saves the data to a table based on the reply of the primary post within one month; the main post has too many fields. I save the content field to another table to avoid the problem of a too large table.

3. Sub-databases. It is to distribute the system to different data according to the module-related features to improve the overall load capacity of the system.

4. scheduled task. For example, if an album has 3000 accesses per hour, we need to record the clicks of each image (daily, weekly, monthly, and total clicks), but the browser does not have high requirements on its real-time performance. Obviously, the click volume of images updated each time will become a bottleneck of the system. At this time, you can record the access status to a temporary click table and update it back to the relevant place at every clock cycle, then, truncate the data in the temporary table.