What statements are used in MySQL database optimization?

The following articles mainly introduce the actual MySQL database optimization operations and related recommendations. We have also discussed some related optimization operation strategies before, let's take a look at the Group BY statements and Order By statements in MySQL database optimization today.

Optimize group by statements

BY default, MySQL sorts all group by col1, col2. .. fields. This is similar to specifying order by col1, col2.... in the query. Therefore, if you explicitly include an order by clause containing the same columns, the actual execution performance of MySQL is not affected. If the query includes group by but you want to avoid consumption of sorting results, you can specify ORDER By NULL to prohibit sorting. For example:

Reference

 
 

  
  
explain select id, sum(moneys) from sales2 group by id \G   
  
  
explain select id, sum(moneys) from sales2 group by id order by null \G  
 
 

By comparison, you can find that the first statement has Using filesort more than the second statement in Extra: And filesort is the most time-consuming.

MySQL database optimizes the order by statement

In some cases, MySQL can use an index to satisfy the order by clause without additional sorting. The WHERE condition and order by condition use the same index, and the order by order is the same as the index ORDER, and the order by field is both ascending or descending.

For example:

Reference

 
 

  
  
SELECT * FROM t1 ORDER BY key_part1,key_part2,....:   
  
  
SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC,key_part2 DESC;   
  
  
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;  
 
 

However, indexes are not used in the following cases:

Reference

 
 

  
  
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;  
 
 

ORDER by field mixing ASC and DESC

 
 

  
  
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;  
 
 

The keywords used to query rows are different from those used in order.

 
 

  
  
SELECT * FROM t1 ORDER BY key1, key2;  
 
 

Use order by for different keywords

Optimize nested queries for MySQL Databases

MySQL supports SQL subqueries. This technique can use the SELECT statement to create a single column query result, and then use the query result as a filter condition in another query, subqueries can be used to perform SQL operations that require multiple logical steps at a time. At the same time, transactions or tables can be easily locked. However, in some cases, subqueries can be replaced by more effective joins.
For example:

Reference

 
 

  
  
explain select * from sales2 where company_id not in(select id from company2) \G   
  
  
explain select * from sales2 left join comany2 on sales2.company_id = 
company2.id where sales2.company_id is null \G;   
 
 

The first sentence seems more concise than the second sentence, but the second sentence is faster than the first sentence. Because JOIN is used to complete this query, the speed is relatively fast, especially if the id in the compay2 table is indexed, the performance will be better. So why is JOIN more efficient in this case. Because MySQL does not need to create a temporary table in memory to perform the query in two steps logically.

Optimization OR conditions

If you want to use an index for a query clause that contains OR, you must use an index for each condition column between OR. If no index is available, you need to add an index.

SQL prompt

SQL prompt (SQL hint) is an important means for MySQL database to optimize the database. Simply put, some human prompts are added to the SQL statement to optimize the operation.
For example:

Reference

 
 

  
  
SELECT SQL_BUFFER_RESULTS * FROM ...  
 
 

This statement forces MySQL to generate a temporary result set. After a temporary result set is generated, all tables are locked. This can be helpful when a table lock problem occurs or it takes a long time to pass the result to the client, because the lock resources can be released as soon as possible,

The following are some SQL tips commonly used in MySQL.

Reference

1. USE INDEX
After the table name in the query statement, add the use index to provide the INDEX list to be referenced by MySQL, so that MySQL does not consider other available indexes.

Reference

 
 

  
  
explain select * from sales2 use index (ind_sales2_id) where id 3 \G;  
 
 

2. IGNORE INDEX

If you just want MySQL to IGNORE one or more indexes, you can use ignore index as the HINT.

3. FORCE INDEX

To FORCE MySQL to use a specific INDEX, you can use force index as the HINT in the query. For example, if you do not use an index forcibly, because the id value is greater than 0, MySQL performs a full table scan by default and does not use an index. For example:

Reference

 
 

  
  
expalin select * from sales2 where id > 0 \G;  
 
 

However, when the force index is used for prompting, even if the efficiency of using the INDEX is not very high, MySQL still chooses to use the INDEX, which is a right for the user to choose the execution plan. After the force index prompt is added, execute the preceding SQL

Reference

 
 

  
  
explain select * from sales2 force index(index_sales2_id) where id > 0 \G;