The mysql optimization method uses showstatus and application features to understand the execution frequency of various SQL statements. SHOWSTATUS provides server status information and can also be obtained using the mysqladminextended-status Command. SHOWSTATUS displays session-level statistics and global statistics as needed. The following parameters apply to Myisam
The mysql optimization method uses show status and application features to understand the execution frequency of various SQL statements. The SHOW STATUS command can be used to provide server status information or the mysqladmin extended-status Command. Show status displays session-level statistics and global statistics as needed. The following parameters apply to Myisam
Mysql Optimization Method
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:
The number of times Com_select executes the select Operation. Only 1 is accumulated for one query;
The number of insert operations performed by Com_insert. insert operations for batch inserts are accumulated only once;
The number of times Com_update executes the update operation;
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:
Innodb_rows_read the number of rows returned by the select statement;
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 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
Locate SQL statements with low execution efficiency
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.
Analyze the execution plan of inefficient SQL statements through EXPLAIN
After a low-efficiency SQL statement is queried through the above steps, we can use explain or desc to obtain information about how MySQL executes the SELECT statement, including how the select statement execution table connects to and connects to the sequence.
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
Determine the problem and take corresponding optimization measures
After the above steps, you can confirm the cause of the problem and 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)
