An introduction to SQL statement optimization
1. SQL statement optimization explanation
1 For specific requirements, use a better SQL strategy or index strategy to achieve shorter results presentation time,
The process of improving operational efficiency is SQL optimization.
2 SQL optimization is included in database-level optimization. What we usually call SQL optimization is to optimize the SQL language
Sentence and index.
3 SQL optimization is optimized along with the business, not all the following operations must be achieved
2. Routine tuning ideas
Low-log, analyze the slow-log, and analyze the sentences that are slow to query.
According to a certain priority, check all slow sentences one by one.
Analyze top sql, perform explain debugging, and check the execution time of the statement.
Adjust the index or the sentence itself
Two MySQL log support
There are four types of logs in MySQL: error logs, binary logs, general query logs, and slow query logs.
1. Error log
The MySQL error log records the more serious warnings and error messages during the operation of MySQL, as well as MySQL
Detailed information for each startup and shutdown. The error log is usually named hostname.err. Where hostname means
Server host name.
View error log related information command: show variables like'%log_error%';
1.binlog_error_action error handling method. ABORT_SERVER has a problem to terminate the service,
IGNORE_ERROR Ignore errors.
2.log_error error log file name and path
3.log_error_verbosity logging level. A value of 1 means to record warning information. Greater than 1 means all warning letters
All information is recorded.
Modify the error log path
Modify the error log path through the MySQL configuration file.
log_error=[path]/[log file name].err
2. Binary log
Contains all updated data (addition, deletion, modification, table modification, etc.) SQL information records. MySQL master-slave configuration
Rely on this log file.
View binary log information command
show variables like'%log_bin%';
Binary log cannot be opened by modifying global parameters. This parameter is commented in the global configuration file (my.ini).
Set the value of log-bin directly to the log file name.
After setting, restart the MySQL service and you will find that the log_bin parameter value is ON
View the binary file and size used by the current server
show binary logs;
After opening, the binary file is stored in C:\ProgramData\MySQL\MySQL Server 5.7\Data. There is a
The xxx.index file (this file is called the binary file index) stores a list of all binary files. When restarting
The MySQL service may automatically add a binary file after a certain period of time. The number of the increased binary file increases. and also
You can use flush logs; command to generate a new binary file.
Since the binary log file is a binary file, you cannot directly use a text editor to view the log file
You can use the command: show binlog events in'mylogbin.000001'; or use a tool to view the binary log
The contents of the file.
Enter in the Navicat or sql command
show binlog events in'mylogbin.000001';
With the help of mysqlbinlog tool
The SQL in binlog is encrypted except for deleting and creating tables. If you want to see it, you can use the following command
mysqlbinlog --base64-output=decode-rows -v mylogbin.000003
Chinese garbled characters will appear when printing directly to the console.
3. General query log
The general query log records established client connections and executed statements.
Check whether the general query log is enabled
show variables like'%general%';
The general_log attribute value: OFF means closed (default closed), ON means open.
By viewing the log output format
show variables like'%log_output%';
Stored in C:\ProgramData\MySQL\MySQL Server 5.7\Data, hostname.log
Mysql/general_log stored in the database
Temporarily turn on/off the general log (restart invalid)
# Turn on
set global general_log=on;
# shut down
set global general_log=off;
Temporarily set the output format (restart invalid)
set global log_output='TABLE';
set global log_output='FILE';
# Both output
set global log_output='FILE,TABLE';
Permanently set, modify the global configuration file my.ini configuration file
The time in the log file is inconsistent with the system time
View system log file format
show variables like'%log_timestamps%';
Modify the log file time format to system time
set global log_timestamps = SYSTEM;
4. Slow query log
Record all queries whose execution time exceeds long_query_time seconds or queries that are not suitable for indexing.
long_query_time The default time is 10 seconds. That is, queries over 10 seconds are considered slow queries.
The default name of the slow query log: hostname-slow.log.
In addition to viewing the my.ini file, use the show variables like'%quer%'; command to view
slow_query_log indicates whether to enable slow query log. (Enabled by default)
slow_query_log_file slow query log file name
long_query_time The slow query threshold is set, and it is detected as a slow query. This value directly sets the global parameters may not
Effective, it is recommended to modify the configuration file directly during testing.
log_queries_not_using_indexes Whether to log queries that are not applicable to indexes (provided slow_query_log
View the slow query log content
The first line, the execution time of the SQL query.
The second line, execute SQL query connection information, user and connection IP.
The third line records some of our more useful information, which is parsed as follows:
-Query_time, the execution time of this SQL, the longer, the slower;
-Lock_time, waiting for table lock time in the MySQL server stage (not in the storage engine stage);
-Rows_sent, the number of rows returned by the query;
-Rows_examined, the number of rows checked by the query, of course, the longer it is, the more time it takes;
The fourth line, setting the timestamp, has no practical meaning, but corresponds to the execution time of the first line.
On the fifth line and all the following lines (before the second # Time:), the executed sql statement records information.