Optimize MySQL, or use a cache? Read an article in the thoughts

Today I would like to compare a variety of performance optimization strategies that can be used on a greenfield project . In other words, the project has not been optimized for the constraints imposed on it by previous decisions.

Specifically, the two optimization strategies I want to compare are optimized for MySQL and caching. In advance, these optimizations are orthogonal, and the only reason you choose accesses than either instead of the other accesses than either is that they all consume resources, that is, development time.

Optimize MySQL

When you optimize MySQL, you will typically look at the query statements sent to MySQL before running the explain command. A very common practice after a little 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 app. Because the index retrieves data by the speed of logarithmic complexity (and, as you search for a phone book, gradually narrows the search), it also maintains good performance as the amount of data increases. Caching the results of an unindexed query may sometimes behave worse as data grows. As the data grows, those who miss the cache may get a bad experience, and such an application is not available.

2, the optimization of MySQL does not need to worry about cache invalidation or cache data expiration issues.

3. Optimizing MySQL simplifies the technical architecture and makes it easier to replicate and work in a development environment.

Disadvantages

1, there are some queries do not light through the index to improve performance, may also need to change the mode, in some cases this may be cumbersome for some applications.

2. Some schema changes may be used for anti-normalization (data backup). Although this is a common technique for DBAs, it requires ownership to ensure that all places are updated by the application, or that triggers need to be installed to ensure this change.

3, some optimization methods may be unique to MySQL. That is, if the underlying software is ported to work on multiple databases, it is difficult to ensure that some of the more complex optimization techniques are common in addition to adding indexes.

Using the cache

This optimization requires people to analyze the actual situation of the application, and then peel off the expensive parts from MySQL and replace them with third-party caches, such as memcached or Redis.

Advantages

1, the cache for some MySQL itself is difficult to optimize the query will be workplace very good, such as large-scale aggregation or packet query.

2. The cache may be a good solution for improving the throughput rate of the system. For example, when multiple people are accessing the app at the same time, the response is slow.

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

Disadvantages

1. If the data provides multiple access paradigms (for example, in different forms on different pages), it may be difficult to expire or update the cache, and/or it may be necessary to tolerate data that has expired. A viable alternative is to design a more granular caching mechanism, and of course it has the disadvantage that fetching the cache multiple times increases latency .

2. Caching an expensive object can create potential performance differences for users who do not hit the cache (see Benefits of optimized MySQL # #). Some good performance practices indicate that you should try to minimize the differences between users, not just averaging (caching tends to do so).

3. Naïve cache implementations are unable to cope with subtle vulnerabilities such as avalanche effects . Just last week I helped a man whose database server was overwhelmed by multiple users trying to regenerate the same cached content at the same time. The correct strategy is to introduce a certain level of lock to serialize the request for cache regeneration.

Summarize

In general, I would recommend that users optimize MySQL first, as this is the most appropriate solution for the start phase. However, in the long run, most applications will have some use cases that require some degree of implementation of these scenarios.

Original link: Morgan tocker

Optimize MySQL, or use a cache? Read an article in the thoughts