preface

Before I saw a friend sent me a code, let me see their former colleagues “advanced” optimization program, so-called million level optimization code!

This code, the whole person such as enlightened, as if to open the door to a new world!

Of course, this is not the level of tuning that we’re talking about today. Today we’re talking about Mysql tuning that most back ends encounter.

The purpose of SQL tuning

A typical tuning process needs to accomplish one of the following objectives:

  • Reduce user response time, which is the time between a user issuing a command and getting a response
  • Improve throughput, that is, make a SINGLE SQL use a minimum of resources

For one response time issue, consider the user of an online book app who got stuck updating his shopping cart for three minutes. In contrast, a throughput problem can be seen in a data warehouse where a single statement running in parallel for three minutes consumes the CPU resources of the entire DB server, causing other queries to fail to run.

SQL statement life cycle

  • There are two types of mysql requests
  1. One requires command parsing and distribution to be executed
  2. The other can be executed directly; Either way, if logging is enabled, the logging module records logs.

  • If the request is of the Query type, the control is given to the Query parser. The Query parser checks whether the request is of the Select type. If it is, the Query cache module is started. If there is no cache or it is not a cacheable query, the parser will process the query and pass it to the relevant processing module through the query dispenser.

  • If the parser result is DML/DDL, it is handed over to the change module; If it is a checked and repaired query, it goes to the table maintenance module, and if it is an uncached statement, it goes to the query optimizer module. In fact, the table change module is divided into several small modules,

  1. For example, insert processors, DELETE processors, UPDATE processors, CREATE processors, and ALTER processors are small modules responsible for different DML and DDL.
  2. In short, the query optimizer, table change module, table maintenance module, replication module and status module are distributed to different types of modules according to the results of the command parser, and finally interact with the storage engine to read the data that meets the conditions of the current SQL statement.
  • When a command is executed, control is returned to the connecting thread module. Each module relies on the core API module for processing tasks such as memory management, file I/O, and string processing

1. Optimize SQL statements

1. Avoid full table scans. First consider creating indexes on the columns involved in where and order BY.

2. Avoid using! In the WHERE clause. = or <> operators, otherwise the engine will abandon the index and proceed to a full table scan.

3. Avoid null values in the WHERE clause. Otherwise, the engine will give up using indexes and perform a full table scan.

select id from t where num is null
Copy the code

Select * from table where num = null; select * from table where num = null;

select id from t where num=0
Copy the code

4. Try to avoid expression operations on fields in the WHERE clause. This will cause the engine to abandon the use of indexes and perform a full table scan. Such as:

select id from t where num/2=100
Copy the code

Should be changed to:

select id from t where num=100*2
Copy the code

5. Try to avoid performing functional operations on fields in the WHERE clause. This will cause the engine to abandon the use of indexes and perform a full table scan. Such as:

Select id from t where substring(name,1,3)=' ABC 'Copy the code

— Name Id starting with ABC

select id from t where datediff(day,createdate,'2005-11-30')=0
Copy the code

Id generated by ‘2005-11-30

Should be changed to:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
Copy the code

6. Do not perform functions, arithmetic operations, or other expression operations to the left of “=” in the WHERE clause, otherwise the system will not be able to use the index correctly. 11. When using index fields as a condition, if the index is a compound index, the first field in the index must be used as a condition to ensure that the index is used by the system. Otherwise, the index will not be used.

7. Do not write queries that are meaningless, such as generating an empty table structure:

select col1,col2 into #t from t where 1=0
Copy the code

Code that does not return any result set but consumes system resources should be changed to:

create table #t(...)
Copy the code

8. In many cases it is a good idea to say exists instead of in:

select num from a where num in(select num from b)
Copy the code

Replace with the following statement:

select num from a where exists(select 1 from b where num=a.num)
Copy the code

9. The order by use

Note: Order BY is invalid in the group by subquery in Mysql5.7 and later

A limit statement is used to make the order by of a subquery valid, but the number of limits must be guaranteed, so DISTINCT implementations can also be used.