During the development process, although I feel that optimizing SQL statements is very important, more emphasis is often placed on functional implementation. In order to make myself more efficient in writing MySQL statements in the future, it is necessary to make a small summary of MySQL optimization.

Step 1. Use the show status command to understand the efficiency of various SQL executions

show [session|gobal] status

The session level indicates the statistics of the current connection results.

The global level represents the statistical results since the data was last started.

If you do not write a level, the default is the session level

eg: SHOW GLOBAL STATUS;

Main parameter description

Connections: The number of times the view connects to the mysql server

Uptime: Server working time

Slow_queries: The number of slow queries

Com_xxx indicates the number of times each xxx statement is executed

Com_select Number of times to execute select

Com_insert Number of times to execute insert, when inserting in batches, only 1 time is accumulated

Com_update Number of Update operations performed

Com_delete Number of delete operations performed

Innodb_rows_xxx type parameters are only valid for the innodb storage engine

Analysis: Through the above parameters, you can easily understand the application of the current database and insert updates Is it mainly query-based or query-based, and the execution ratio. The count corresponding to the update operation is the count of execution times, which will be accumulated regardless of submission or rollback.

Step 2: Locate SQL statements with low execution efficiency

Locate which SQL statements with low execution efficiency are located through the slow query log. Started with the --log-slow-queries[=file_name] option, mysqld writes a log file containing all SQL statements executed for more than long_query_time seconds. Regarding how to locate slow queries, you can click here (//m.sbmmt.com/)

## Step 3 : Analyze the execution plan of inefficient SQL through EXPLAIN

In step 2, we can query the inefficient sql statement. Here we can obtain the mysql execution select information through explain or desc. .

#eg:

##

EXPLAIN 
SELECT 
  t0.* 
FROM
  t3 AS t0
  LEFT JOIN `t2` AS t1 
    ON t0.`id1` = t1.`id1` 
WHERE t0.id1 = 5 ;

talbe: The table that outputs the result set.

A high value of handler_read_rnd_next means that the query is running inefficiently and index remediation should be established. If a large number of table scans are being performed and the handler_read_rnd_next value is high, it usually indicates that the table index is incorrect or the query written does not utilize the index, as shown below.

##We should Regularly analyze the table and check the table

Check the table using the following command (check the t3 table)

ANALYZE TABLE t3;

The command to regularly optimize the table is as follows

optimize table table name

Common statement optimization

1. Optimize the Insert statement

(1) If data is inserted from the same client, try to use insert statements and multi-row inserts of multiple word tables to reduce Single row insertion, this method greatly reduces the consumption of direct connection and shutdown between the client and the database. eg:

INSERT INTO t3 VALUES(1,2),(8,5),(6,5),(4,3)

(2) If you insert many rows from different clients, you can get higher speed by using the inset delayed statement.

(3) If you perform batch insertion, you can increase the bulk_insert_buffer_size variable method to increase the speed.

2. Optimize the group by statement

By default, using group by col1, col2.... will sort the query accordingly , if the user wants to avoid the consumption of sorted results, he can specify order by null to disable sorting. It can be seen from the extra field in the query results:

4. Optimize statements containing or

For query clauses containing or, if you want to use an index, each condition column between or must Indexes are used; otherwise, adding an index should be considered.

5. Use sql prompts

#The above is the detailed explanation of the steps of MySQL optimization. For more related content, please pay attention to PHP Chinese Net (m.sbmmt.com)!