The actual operation process of MySQL SQL optimization notes

The following articles mainly describe the actual operation process of MySQL SQL optimization application notes, as well as the solution to the problem of using index for Mysql queries, if you are interested in the actual operations, you can click to view the following articles.

Which of the following optimizations have been made for Mysql Force Index?

Query Optimization of large data volumes in MySQL 5

Recommendation circle: Database circle

For more related recommendations, you will not think about optimization when using SQL. But in the face of a database with MySQL SQL Performance problems, how should we start to analyze the system so that we can locate the problem SQL as soon as possible and solve the problem as soon as possible.

1. Use the show status command to learn the execution frequency of various MySQL SQL statements.

Reference

For example, enter

Show status like 'com _ % ';

Some are displayed: Com_xxx.

Com_xxx indicates the number of times each xx statement is executed. We usually pay attention to the following operations:

Reference

Com_select: Number of select operations

Com_insert: the number of Insert operations performed. For batch INSERT operations, only one Insert operation is accumulated.

Com_update: Number of update operations

Com_delete: Number of Delete operations

The above parameters are accumulated for all table operations of the storage engine. Some of the following parameters are only for the InnoDB Storage engine, and the accumulative algorithms are also a bit different.

Reference

Innodb_rows_read: number of rows returned by the select query

Innodb_rows_inserted: number of rows inserted by the INSERT operation

Innodb_rows_updated: number of rows updated by the Update operation

Innodb_rows_deleted: number of rows deleted by the Delete operation

Through some of the above parameters, we can understand whether the current database application is insert-based or query-based. And the approximate execution ratio of various types of MySQL SQL statements. The Count of update operations is the count of the number of executions, which is accumulated no matter whether submitted or rolled back.

For transactional applications, use Com_commit and Com_rollback for analysis. If rollback operations are frequent, consider whether there is a problem in writing.

The following parameters are used to understand the basic information of the database:

Reference

Connections: The command used to connect to the Mysql server is: show status like 'Con _ % ';)

Uptime: The Command executed during the server's working hours is: show status like 'up _ % ';)

Slow_queries: the number of Slow queries. The command is: show status like 'slow _ % ';)

2. Locate MySQL SQL statements with low execution efficiency

You can try the following two methods to define less efficient SQL statements.

Reference

1. use the slow query log to locate SQL statements with low execution efficiency. When the -- log-slow-queries [= file_name] option is enabled, mysqld writes a log file containing all SQL statements whose execution time exceeds long_query_time.

2. the slow query log is recorded only after the query is completed. Therefore, when the application reports that the execution efficiency is incorrect, the slow query log cannot be located, you can run the show processlist command to view the current MySQL thread, including the thread status and whether to lock the table. You can view the SQL Execution status in real time, optimize some lock table operations.

3. Use EXPLAIN to analyze execution plans of inefficient SQL statements.

After querying the MySQLSQL statement with low efficiency, we can use the explain or DESC command to obtain information about how Myswl executes the SELECT statement, including how tables are connected and connected in the Select statement execution sequence.

For example, if you want to count the company's sales in xxxx, You need to operate the sales and comapny table, and perform the sum operation on the money field. Let's see how to use explain:

Reference

Explain select sum (moneys) from sales a company B where a. company_id = B. id and a. year = XXXX \ G; (Note that \ G is used for better reading)

Shown as follows:

 
 

  
  
*********************** 1. row***************************   
  
  
id: 1   
  
  
select_type: SIMPLE   
  
  
table: a   
  
  
type: ALL   
  
  
possible_keys: NULL   
  
  
key:NULL   
  
  
key_len: NULL   
  
  
ref: NULL   
  
  
rows:1000   
  
  
Extra: Using where   
  
  
*********************** 2. row***************************   
  
  
id: 2   
  
  
select_type: SIMPLE   
  
  
table: b   
  
  
type: ref   
  
  
possible_keys: ind_company_id   
  
  
key:ind_comapany_id   
  
  
key_len: 5   
  
  
ref: sakila.a.company_id   
  
  
rows:1   
  
  
Extra: Using where;Using index  
 
 

The following explains the meaning of each column:

Reference

Select_type: SELECT type. common values include SIMPLE (SIMPLE table, no table connection or SUBQUERY), PRIMARY (PRIMARY query, that is, outer query), UNION, and SUBQUERY.

Table: The table of the output result set.

Type: indicates the table connection type. The type of performance from good to bad is

(System (the table has only one row, that is, the constant table ),

Const (a single table can have at most one matching row ),

Eq_ref (for each row above, only one record is queried in this table ),

Ref (using common indexes ),

Ref_or_null (similar to ref, but the condition contains NULL queries ),

Index_merge (index merge optimization ),

Unique_subquery (in is followed by a subquery that queries the primary key field ),

Index_subquery (similar to unique_subquery, which is followed by subqueries for non-unique index fields ),

Range (range query in a single table ),

Index (for each current row, data is obtained by querying the index ),

All (for each current row, data is obtained through full table scan ))

Possible_keys: indicates the index that may be used during query.

Key: indicates the index actually used

Key_len: Index Field Length

Rows: number of rows scanned

Extra: Description and description of execution

The above content is an introduction to the notes for MySQL SQL optimization. I hope you will get something better.