Optimize MySQL or use cache? _ MySQL

Today, I want to compare the various performance optimization policies that can be used in a Greenfield project. In other words, the project has no constraints imposed on it by previous decisions and has not been optimized. Specifically, the two optimization strategies I want to compare are to optimize MySQL and cache. MySQL optimization

Today, I want to compare the various performance optimization policies that can be used in a Greenfield project. In other words, the project has no constraints imposed on it by previous decisions and has not been optimized.

Specifically, the two optimization strategies I want to compare are to optimize MySQL and cache. It is pointed out in advance that these optimizations are orthogonal. The only reason why you choose one of them, not the other, is that they all consume resources, that is, the development time.

Optimize MySQL

When optimizing MySQL, the query statements sent to mysql are checked first, and then the explain command is run. A common practice after a slight review is to increase the index or make some adjustments to the pattern.

Advantages

1. an optimized query is fast for all users who use the application. Because the index uses the speed of log complexity to retrieve data (also named, as you search for a phone book, gradually narrow the search scope), and it can maintain good performance as the data volume increases. Caching the results of an unindexed query may result in worse performance as data increases. As data grows, users who do not hit the cache may have a bad experience, and such applications are unavailable.

2. optimizing MySQL does not need to worry about cache invalidation or cache data expiration.

3. optimizing MySQL simplifies the technical architecture and makes it easier to copy and work in the development environment.

Disadvantages

1. some queries may not improve performance by indexing, but may need to change the mode. in some cases, this may be difficult for some applications.

2. some pattern changes may be used for de-normalization (data backup ). Although this is a common technique for DBA, it requires ownership to ensure that everything is updated by the application, or the trigger needs to be installed to ensure this change.

3. some optimization methods may be unique to MySQL. That is to say, if the underlying software is transplanted to multiple databases to work, it is difficult to ensure that some more complex optimization technologies, except adding indexes, can be used universally.

Use cache

This optimization requires someone to analyze the actual situation of the application, and then remove the expensive part from MySQL and use a third-party cache instead, such as memcached or Redis.

Advantages

1. cache works well for queries that are difficult to optimize by MySql itself, such as large-scale aggregation or grouping queries.

2. caching may be a good solution for improving the system throughput. For example, when multiple users access the application at the same time, the response speed is very slow.

3. caching may be easier to build on another application. For example, your application may be the front end of another software package that uses MySQL to store data, but it is very difficult to make any database changes to this software package.

Disadvantages

1. if data provides multiple access paradigms (for example, displaying data in different forms on different pages), it may be difficult to make the cache expire or update, at the same time,/or you may need to tolerate expired data. A feasible alternative solution is to design a more fine-grained Cache mechanism. of course, it also has the disadvantage that multiple accesses to the cache will increase latency.

2. caching an object with high cost may lead to potential performance differences for users who do not hit the cache (see advantages of optimizing MySQL #1. Some good performance practices show that you should try to narrow down the differences between users, not just mean (cache tends to do this ).

3. naive caching cannot cope with some subtle vulnerabilities, such as the avalanche effect. I helped a person last week. his database server was overwhelmed by multiple user requests trying to regenerate the same cached content at the same time. The correct policy is to introduce a certain number of locks to serialize cache regeneration requests.

Summary

In general, I recommend that you optimize MySQL first, because this is the most suitable solution in the initial stage. However, in the long run, most applications have some use cases that need to be implemented to some extent at the same time.

Original article: Morgan Tocker translation: Bole online-Gao Lei