General steps for optimizing SQL statements in Mysql

Through showstatus and application features, you can learn the execution frequency of various SQL statements. Through SHOWSTATUS, You can provide server status information or use the mysqladminextended-status Command. SHOWSTATUS displays session-level statistics and global statistics as needed. The following parameters reference Myisam and Innodb Storage

Use show status and application features to learn the execution frequency of various SQL statements. Use SHOW STATUS to provide server status information, or use the mysqladmin extended-status Command. Show status displays session-level statistics and global statistics as needed. The following parameters reference Myisam and Innodb Storage

Learn the execution frequency of various SQL statements through show status and application features

The show status command can be used to provide the server status information or the mysqladmin extended-STATUS Command. Show status displays session-level statistics and global statistics as needed.

The following parameters count both Myisam and Innodb Storage engines:

1. The number of times Com_select executes the select Operation. Only 1 is accumulated for one query;
2. The number of insert operations performed by Com_insert. insert operations for batch inserts are accumulated only once;
3. The number of times Com_update executes the update operation;
4. The number of times Com_delete executes the delete operation.

The following parameters are used to count the Innodb Storage engine, and the accumulative algorithms are slightly different:

1. Innodb_rows_read the number of rows returned by the select statement;
2. Innodb_rows_inserted: number of rows inserted by the Insert operation;
3. Innodb_rows_updated: number of rows updated by the update operation;
4. Innodb_rows_deleted: number of rows deleted by the delete operation.

Through the above parameters, you can easily understand whether the current database application is dominated by insert and update, query operations, and the approximate execution ratio of various types of SQL statements. The Count of update operations is the count of the number of executions. Both the commit and rollback operations are accumulated.

For transactional applications, you can use Com_commit and Com_rollback to learn about transaction commit and rollback. For databases with frequent rollback operations, it may mean that there is a problem in application writing.

In addition, the following parameters help us understand the basic information of the database:

• Number of times that Connections attempted to connect to the Mysql server
• Uptime server working time
• Slow_queries slow Query Count

You can locate SQL statements with low execution efficiency in the following two ways:

• You can use the slow query log to locate SQL statements with low execution efficiency. When you start the SQL statements with the -- log-slow-queries [= file_name] option, mysqld writes a log file containing all SQL statements whose execution time exceeds long_query_time. You can link to relevant chapters in management and maintenance.
• The slow query log is recorded only after the query is completed. Therefore, when the application reports an execution efficiency error, the slow query log cannot be queried, you can use 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 in real time and optimize some lock table operations.

After analyzing the execution plan of inefficient SQL statements by using the EXPLAIN statement, we can use explain or desc to obtain information about how MySQL executes the SELECT statement, including how to connect and connect the select statement execution table.

Explain can know when to add an index to the table to obtain a faster SELECT statement that uses indexes to search for records.

Mysql> explain select sum (moneys) from sales a, companys B where a. company_id = B. id and a. year = 2006;
+ ---------------- + ---------- + ----------- + ---------------- + ------------ + ----------- + ---------------- +
| Select_type | table | type | possible_keys | key | key_len | rows | Extra |
+ ---------------- + ---------- + ----------- + ---------------- + ------------ + ----------- + ---------------- +
| SIMPLE | B | index | PRIMARY | 4 | 1 | Using index |
| SIMPLE | a | ALL | NULL | 12 | Using where |
+ ---------------- + ---------- + ----------- + ---------------- + ------------ + ----------- + ---------------- +
2 rows in set (0.02 sec)

Note:

• Select_type: select Type
• Table: The table of the output result set.
• Type: indicates the table connection type.
  • ① When only one row in the table is of the type value, system is the best connection type;
  • ② When the select Operation uses indexes for table join, the value of type is ref;
  • ③ When the select Table connection does not use an index, you will often see that the value of type is ALL, indicating that a full table scan is performed on the table, in this case, you need to consider creating indexes to improve the efficiency of table connection.
• Possible_keys: indicates the index columns that can be used during query.
• Key: indicates the index used.
• Key_len: Index Length
• Rows: scan range
• Extra: Description and description of execution

Identify the problem and take corresponding optimization measures. After the above steps, you can basically confirm the cause of the problem. You can take appropriate measures based on the situation to optimize and improve the execution efficiency.

For example, in the preceding example, we confirm that the full table scan of Table a results in unsatisfactory efficiency. We created an index for the year field of Table, the number of rows to be scanned is obviously small.

Mysql> explain select sum (moneys) from sales a, companys B where a. company_id = B. id and a. year = 2006;
+ ---------------- + ---------- + ----------- + ---------------- + ------------ + ----------- + ---------------- +
| Select_type | table | type | possible_keys | key | key_len | rows | Extra |
+ ---------------- + ---------- + ----------- + ---------------- + ------------ + ----------- + ---------------- +
| SIMPLE | B | index | PRIMARY | 4 | 1 | Using index |
| SIMPLE | a | ref | year | 4 | 3 | Using where |
+ ---------------- + ---------- + ----------- + ---------------- + ------------ + ----------- + ---------------- +

2 rows in set (0.02 sec)

From: http://see.xidian.edu.cn/cpp/html/1472.html