The cause of

Because production data quantity is more and more big, more and more customers, project function more and more, the project itself is also more and more, lead to server memory, hard disk has been gradually before not enough, when the two solutions, add server configuration and new servers, but even the new hard disk, also need to migrate the database, Therefore, a new server purchase scheme was adopted, and since it was an efficient cloud disk before, the IO was full, so SSD disk was used for the newly purchased server, theoretically the speed can fly. In fact, I installed MySQL5.7 on the new server, because I heard that the performance of MySQL5.7 was improved by N times. Json is also supported (although not useful for us), but since MySQL8 is already out, MySQL5.7 must be stable as well. So, found a quiet night secretly database migration, and then open the slow query log, limited to 5 seconds, so began a variety of tests, and then looked at the slow query log, a pile of slow query log, so with this article, why there are so many slow query, and how to solve

Start screening

Will take out the slow query SQL, find the main slow SQL is the query list query statements, that is to say, the query itself is very complex, so the database before the SQL statement back, found that before the database is less than 1 second query out, and the slowest in the new database can reach more than 140 seconds, this was not normal, After all, the new MySQL server is better than the previous MySQL server in terms of CPU, memory, or hard disk. If the performance is almost acceptable, but suddenly so much slower, it is obvious that there is something abnormal, so we began to check one by one

Troubleshooting Step 1: Configuration problems

First of all, the configuration files of the two servers are exactly the same, because they are copied from the configuration files of the old server, but because the versions of MySQL are inconsistent, I suspect that some configurations of the new version are different, so baidu has optimized the configuration of MySQL5.7. At the same time, part of the configuration is adjusted according to the hardware conditions of the machine, such as memory, CPU, etc., restart MySQL, execute statements, the effect is not obvious, on average, it can be about 1 second faster, but this 1 second itself can be considered as query fluctuation, so it is not the problem of configuration

Troubleshooting Step 2: Hardware problems

First of all, there should be no problem with CPU and memory, and the only possibility is SSD disk, because I have seen that SSD disk leads to low performance of K and V key storage, which is completely different from mechanical disk, so I wonder whether SSD needs to be enabled with any special configuration, so Baidu, It was found that although there was an optimized configuration for SSD, the speed was not very slow due to the use of SSD, so I optimized the configuration for SSD, restarted MySQL, and executed statements, but the effect was very small

Troubleshooting Step 3: Statement problems

The statement itself is almost optimized. Small result sets drive large result sets, indexes are created based on where conditions, etc. After all, even if the MySQL upgrade will not say change of SQL syntax and so on, is the most in the optimized SQL for some special processing, so check the SQL statement so desc SQL statements to check the implementation of the index, the above differences with the old library

Old library (MySQL5.6)

New library (MySQL5.7) In 2 library data, the indexes of the same circumstances, would appear the index is not the same as the reference and accuracy, so doubt whether it is time to move the database to the index data are destroyed, so baidu to find, and also really had found an example, and migration database query is very slow, after the reconstruction after the index recovered, so ready to rebuild index, because the table is very much, So I wrote a utility class to rebuild the index (unique index and normal index, excluding primary key index), the core code is as follows:

List<HashMap> list = mapper.select1(); HashMap<String,HashMap<String,Object>> temp = new HashMap<>(); for(HashMap map : list){ String tableName = map.get("TABLE_NAME").toString(); String indexName = map.get("INDEX_NAME").toString(); String nonUnique = map.get("NON_UNIQUE").toString(); String columnName = map.get("COLUMN_NAME").toString(); if(temp.containsKey(tableName+"|"+indexName)){ HashMap<String,Object> value = temp.get(tableName+"|"+indexName); List<String> columns = (List<String>) value.get("columns"); columns.add(columnName); }else{ HashMap<String,Object> value = new HashMap<>(); value.put("nonUnique",nonUnique); List<String> columns = new ArrayList<>(); columns.add(columnName); value.put("columns",columns); value.put("indexName",indexName); value.put("tableName",tableName); temp.put(tableName+"|"+indexName,value); } } List<String> creates = new ArrayList<>(); List<String> drops = new ArrayList<>(); for(Map.Entry<String,HashMap<String,Object>> entry:temp.entrySet()){ String create = null; String tableName = entry.getValue().get("tableName").toString(); String indexName = entry.getValue().get("indexName").toString(); String nonUnique = entry.getValue().get("nonUnique").toString(); List<String> columns = (List<String>) entry.getValue().get("columns"); drops.add("DROP INDEX "+indexName+" ON "+tableName+";" ); If ("0". Equals (nonUnique)){create = "create UNIQUE INDEX "+" ON "+tableName+" ("; }else{// Create a normal INDEX create = "create INDEX "+indexName+" ON "+tableName+" ("; } for(int i = 0; i < columns.size(); i++){ if(i == columns.size() - 1){ create += columns.get(i)+");" ; }else{ create += columns.get(i)+","; } } creates.add(create); } for(String str : drops){ System.out.println(str); } for(String str : creates){ System.out.println(str); }Copy the code

SQL > query all indexes as follows:

select * from information_schema.statistics WHERE INDEX_SCHEMA='xxxx' AND INDEX_NAME<>'PRIMARY'  
Copy the code

XXXX is the database instance name, the code will be printed out after the execution of THE SQL statement into SQL, of course, you can also use Java to call SQL execution, but I in order to observe the situation at any time, so THE SQL copy out to execute

When the SQL statement is executed after the index is rebuilt, the speed still does not change significantly, indicating that the index data is not abnormal.

Check out the new MySQL5.7 feature

Derived_merge is a new SQL optimization method for MySQL5.7, so try to disable derived_merge and execute SQL

set GLOBAL optimizer_switch='derived_merge=off'  
Copy the code

SQL > select * from desc; select * from desc; select * from desc

New problem after derived_Merge is disabled

When you disable derived_merge, the CPU on the server is full. The top command is used to check whether the CPU on the server is full because of MySQL (it must be MySQL, because the server is the only software).

show full processlist;  
Copy the code

Then I copied the SQL statement and found that it was only a single data query. Theoretically, it would not be so slow. In order to find out the reason, I stopped the test, restarted MySQL, and executed the view SQL statement. Check whether there is the view of SQL exceptions, found that the view of SQL is common SQL associated query (4 tables), in theory will not take so long time, take out the SQL statement to create a view with the view of execution of SQL conditions pieced together, use desc check, found that normal index hit, so try to execute an SQL, The result is very unexpected, very fast, so I thought the server is crazy, but in order to test good, execute the view SQL, the result is stuck. That is, the SQL execution of the view itself is fine, but with view queries, it is stuck. Select * from desc; select * from desc; select * from desc;

view View the SQL

The index hit number is 83141272975, with 11 digits and tens of billions of rows. The index hit number is 83141272975, with 11 digits and tens of billions of rows. That and missed the index also have no difference, but the most important thing is that we should add up all the whole library table no billions of data, after all the biggest table data quantity also is almost never, so this index is definitely has a problem, data also has a problem, but what specific problems, is not I know that, after all, not a professional database, So also hope to understand can help me answer.

Know, feeling solution is simple, baidu search MySQL5.7 view is optimized, but both baidu and Google did not find the right answer, after all, view itself is stored in a SQL statement, did not save the actual data, that is to say, even if the optimization is to optimize the SQL statement itself, However, there was no problem with the SQL statement execution itself, and I thought MySQL could not release such a big bug, so I recall that the parameter is caused by the configuration modification, because the main configuration modification is derived_merge. So I suspect derived_merge is the cause, so I turn on derived_merge

set GLOBAL optimizer_switch='derived_merge=on'  
Copy the code

Execute the view and everything is fine

Step 4: Index matching problems

Disabling derived_merge can cause problems with view queries, and many views are used in the system. Therefore, disabling derived_merge would require major changes to the system, making it impractical to disable derived_merge. That is solved the problem of the index hit, can solve the problem of low query to contrast two base hit index, found mainly list when a query ON the back with the conditions in the new library of missed the index above, ON the back of the condition is have to form a coalition with other columns in the main table index, and linked table has with other part of the table is listed into joint index, Some columns of the table without any index, so try to be linked table creation ON the back of the field of single index, created, obviously a times faster speed, but there are still some index missed, so they are ON the back of the field in the main table alone create indexes (if ON the back has a few conditions, to create a joint index), has been created, execute the statement, Second query out, problem solved

Try to optimize indexes on older libraries

Because of the new library to create the index after detailed speed is faster than the old library a lot, of course, related to configure itself, so that to go up the old library will also create the same index faster, and so on the old library created with the same index new library, executing SQL statements, slower than before to create an index doubled, view index hit, although the index hit more, But it also results in an increase in the number of rows of hit indexes

feeling

Different versions of MySQL have different SQL optimizers, and different versions may have different index hit rules. In addition, more indexes do not mean faster queries. Unreasonable index creation will not only lead to slow inserts, but also slow queries. So it is necessary to understand the MySQL index hit rules and understand the SQL optimizer used by MySQL, and do not update the version easily, god knows what kind of problems will arise…