MySQL Database tuning

Recently new to the project, as a help, meet the performance test.

Test requirements are not high, is now a single MySQL database, no table, 40 million data, 400 milliseconds, on the pressure is more than 1100 TPS, but, the dynamic only accounted for about 20%, that is, about 200 of the TPS bar. The server is still very good, I see more than 10 CPU threads, estimated more than 100 g memory bar.

The general situation is as above.

I did not optimize the MySQL before, in fact, is not tuned to SQL, read only some of the principles of SQL execution, the structure of the database what, usually write SQL and design table when some attention, the actual combat tuning experience is zero, even if the tune, not tested, also useless, this is caught cheap.

Say a lot of nonsense, talk about the specific problem.

First, it is how to analyze the specific causes of SQL slowness.

Under MySQL, the database engine should be aware that we are using InnoDB, row locks.

In fact, my main use, is explain, this thing can help me to analyze the specific implementation of SQL.

My main point of view is the row count and see how many rows are traversed when it executes. This number directly affects the speed. Of course, the other is also very useful, not to see too understand, half guessing.

The general approach is to index a large number of columns in the query.

The main thing is to give, where conditions, join conditions, in addition, there is no need to add, the index has a price. To the main plus, in the execution of SQL, to reduce the number of traversal of the purpose of the line is good

My native test, 40 million of the data table, inserting a piece of data takes less than 200 milliseconds, which is the case when there is a normal btree index, so the price is quite large.

This order of magnitude actually needs to be divided into tables. According to my experience this time, about 10 million of the time, it should be divided, otherwise, all aspects are influential.

The other is to optimize SQL.

The main idea is that SQL executes the order of specific statements, making it possible for restrictive SQL to make the data range smaller, so that the speed can be faster in various joins and where.

Sort, group by this operation, as far as possible in the last execution, because this operation is more time-consuming, need to facilitate all the current data.

In addition, select between rows should not be used, if possible, to join, because the row of SQL will be based on the number of rows executed.

The number of results returned should always be limited. If the number of returned results, if returned too much, no matter how optimized, will be very slow, you can try to 40 million of the table select *

In addition, in the number of columns in the table to do the query, try not to use *, this or the efficiency of the impact, only list the columns you need, the efficiency will be higher.

Write more messy, most are some suggestions, ideas, specific content, on the internet, books, it is easy to find.

MySQL Database tuning