MySQL SQL statement analysis and query optimization detailed explanation

How to obtain SQL statements with performance issues

1. Obtain SQL statements with performance issues through user feedback
2. Obtain SQL statements with performance issues through slow query logs
3. Obtain SQL with performance issues in real time

Use slow query logs to obtain SQL statements with performance issues

First, let's introduce the parameters related to slow queries.

1. slow_query_log starts the custom slow query log setting. You can set it through the MySQL command line: set global slow_query_log=on
Or modify the /etc/my.cnf file and add slow_query_log=on

2. slow_query_log_file specifies the storage path and file of the slow query log. It is recommended to store the log storage and data storage separately.

3. long_query_time specifies the threshold for recording the SQL execution time of the slow query log ① Record all SQL that meet the conditions
② Data modification statements ③ Including query statements ④ SQL that has been rolled back

Notice:
The time can be accurate to microseconds, and the storage unit is seconds. The default value is 10 seconds. For example, if we want to query the value of 1 microsecond, we need to set it to 0.001 seconds.

4. log_queries_not_using_indexes Whether to log SQL that does not use indexes

5. log_output sets the save format of slow log query (if you need to save it as a file, please change it to FILE)

Information recorded in the slow query usage log

1. The first line records the test information using sbtest
2. The second line records the time of the slow query log
3. The information recorded in the third line is the time of the lock used
4. The information recorded in the fourth line is the number of rows of data returned
5. The information recorded in the fifth line is the number of rows of scanned data
6. The information recorded in the sixth line is the timestamp
7. The information recorded in the seventh line is the query SQL statement

Use slow query to obtain SQL with performance issues

Commonly used slow query log analysis tool (mysqldumpslow)
Introduction: Summarizes SQL statements that are identical except for the query conditions, and outputs the analysis results in the order specified in the parameters.

Slow query log example

Slow query related configuration settings

Command line execution parameters to view the analysis results

]# cd /var/lib/mysql/log
]# mysqldumpslow -sr -t 10 slow-mysql

Commonly used slow query log analysis tool (pt-query-digest)

Before using the tool, you need to install it first. If you already have it, you can skip the following installation steps.
1. Perl module
]# yum install -y perl-CPAN perl-Time-HiRes perl-IO-Socket-SSL perl-DBD-mysql perl-Digest-MD5
2. Switch to the src directory to download the rpm package
]# cd /usr/local/src
]# wget https://www.percona.com/downloads/percona-toolkit/3.0.7/binary/redhat/7/x86_64/percona-toolkit-3.0.7-1.el7.x86_64.rpm

3. Install the toolkit
]# rpm -ivh percona-toolkit-3.0.7-1.el7.x86_64.rpm

Execute commands to analyze slow query logs

]# pt-query-digest --user=root --password=redhat --host=127.0.0.1 slow-mysql > slow.rep
The results of the analysis are as follows

The entire process of MySQL server processing query requests

1. The client sends a SQL request to the server
2. The server checks whether there is a hit on the SQL in the cache server
3. The server parses and preprocesses SQL, and then the optimizer executes the corresponding execution plan
4. According to the execution plan, call the storage engine API to query data
5. Return the result to the client

The impact of query cache on SQL performance

1. Prioritize checking whether the entire query hits the data in the query cache
2. Implemented via a case-sensitive hash search

Optimizing parameters for query cache

query_cache_type sets whether query cache is available
ON, OFF, DEMAND

Note: DEMAND means that only SQL-CACHE and SQL_NO_CACHE are used in the query statement to control whether caching is required

query_cache_size sets the memory size of the query cache

query_cache_limit sets the maximum value of the query cache available storage

query_cache_wlock_invalidate sets whether to return cached data after the table is locked (this option is disabled by default and is recommended)

query_cache_min_res_unit sets the minimum value of the memory block allocated by the query cache

Reasons that cause MySQL to generate incorrect execution plans

1. Inaccurate statistical information
2. The cost estimate in the execution plan is not equal to the actual cost of the execution plan
3. The MySQL optimizer may think that the best is different from what you think is the best
4. MySQL never considers other concurrent queries, which may affect the current query data
5. MySQL sometimes generates execution plans based on some fixed rules
6. MySQL doesn’t consider costs that are beyond its control

SQL types that the MySQL optimizer can optimize

1. Redefine the association order of the table. The optimizer will determine the association order of the table based on statistical information.

2. Convert external links to internal links
where conditions and database table structure, etc.

3. Use equivalent transformation rules
(5=5 and a > 5) will be rewritten as a > 5

4. Optimize count(), min() and max()
select tables optimized away
The optimizer has removed the table from the execution plan and replaced it with a constant.

5. Convert an expression to a constant expression

6. Use equivalent transformation rules

7. Subquery optimization

8. Optimize the in() condition

How to determine the time spent in various stages of query processing

Using profiles

set profiling = 1;
Execute the query:
show profiles;

show profile for query N;

The time taken for each stage of the query

Use profile to view the time consumed by the statement

Specific SQL query optimization

1. Use the principle of master-slave switching to modify the table structure of a large table. For example, modify it on the slave server now, and after the modification is completed, perform master-slave switching, and then modify the large table on the original master. There is a certain risk.
2. Create a new table on the primary server. The table structure is the same as the table structure after the large table is modified. Then import the data of the old table into the new table again, and create a series of triggers in the old table to synchronize the data of the old table to the new table. After all the data in the old table is synchronized to the new table, add an exclusive lock to the old table, change the name of the new table to the old table, and delete the renamed old table, as shown in the following figure

Use the pt-online-schema-change command to modify the large table. The specific operation is shown in the following figure

Explanation of the parameters in the above figure

--alter SQL statement used
--user database login user
--password Password of the logged in user
D specifies the database name for all modified tables
t The name of the table
--charset specifies the database string
--execute Execute

Original work, please indicate the source for reprinting