Technical Analysis of ideas and solutions for improving MySQL Performance

When MySQL becomes the database of more and more web2 sites, when the site is getting bigger and bigger, the traffic is increasing, and the number of users is increasing, the database is often the first to become the bottleneck of the system, how to Improve the Performance of the database becomes the key point of success or failure of the system. This article only describes how to improve the performance of MySQL from several aspects and how to expand it. However, it will not be elaborated in detail, detailed descriptions and operations, and detailed analysis methods will be shared later.

1. select an appropriate database storage engine

MySQL supports many engines, such as MyISAM, MEMORY, InnoDB, and so on. Each type of trial use scenario is different. The proper selection of storage engine can save you a lot of time, for details, see "Chapter 15th: storage engine and table type".

Ii. Optimize SQL statements

Do not think that SQL statements have little to do with each other. Based on past experience, several malformed SQL statements can drag a database and decide not to be an alarmist. Prior to system optimization, database optimization, and database expansion, check the SQL statements used in the system carefully to solve the possible problems caused by SQL. However, the experience is that this step can solve many initial performance problems.

3. Create an index

This is related to the preceding SQL optimization. Check your SQL language carefully, optimize it as much as possible, and then create necessary indexes. Never ignore the power of indexes, we used to perform tests to generate a page with basic data records. Before indexing, it would take 81 hours to generate all the data records. After adding the index, it would only take 12 minutes, you can calculate the performance of your calculator.

4. Create partitions and tablespaces

Many people think that only Oracle has tablespaces and partitions. I don't want to think of similar things in MYSQL. Although this is introduced by mysql5. although it is not very mature, but there is no doubt that this is a good signal that we have more solutions to improve performance when using MYSQL.

Partitions are generally divided into horizontal partitions and vertical partitions. Currently, MYSQL only supports horizontal partitions, including RANGE, LIST, HASH, and KEY partitions. For details, refer to Chapter 18th: partition "," improve MySQL performance through Partition "and other articles.

For more information about table space, see Chapter 17th: MySQL cluster.

V. Adjust MYSQL and System Parameters

This solution is accompanied by the entire performance improvement process. You can adjust parameters from time to get better performance. This is complicated and there is no written rule, it is obtained through experience or practice.

6. Cluster or proxy

Up to now, MYSQL on a single machine can be optimized and adjusted. If the stress persists at this time, horizontal scaling or vertical scaling should be considered. Vertical Scaling is easier, as long as the performance of a single machine gets the bottleneck, increasing the CPU or memory or replacing a strong device can quickly achieve immediate results.

However, vertical scaling capabilities are limited, so it is impossible to achieve unlimited scaling and there is a high risk. In addition to vertical scaling, we choose horizontal scaling, that is, adding servers, unlimited performance expansion, of course, there are a lot of content in it. How to back up and synchronize databases is a problem that needs to be considered and solved, in addition, we also need to consider the problem of data duplication between so many machines. In addition, it is possible to cache the database to memcached or use mysql proxy to implement connection pool and other solutions, they all need to be considered and selected.

Each of the above categories is a big topic. Such an article only describes the optimization ideas and basic processes, and involves many things, such as diagnosis and analysis, which are very technical, next time, we will carefully analyze a topic and try to achieve the best performance of MYSQL.