MySQL SQL statement analysis and query optimization details, mysqlsql

MySQL SQL statement analysis and query optimization details, mysqlsql

How to obtain SQL statements with performance problems

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

Use slow query logs to obtain SQL statements with performance problems

First, we will introduce the slow query parameters.

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

2. slow_query_log_file specifies the storage path and file for slow query logs.
Separate log storage and data storage is recommended.

3. long_query_time specifies the threshold value for recording the SQL Execution time of slow query logs.
① Record all qualified SQL statements
② Data modification statement
③ Including query statements
④ SQL statements that have been rolled back

Note:
The time can be accurate to microseconds. The unit of storage 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. Does log_queries_not_using_indexes record SQL statements with no indexes used?

5. log_output: Set the storage format of slow log query (if you want to save it as a FILE, change it to FILE)

Slow Query

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

Use slow query to obtain SQL statements with performance problems

Commonly used slow query log analysis tool (mysqldumpslow)
Description: summarizes all the SQL statements except the query conditions, and outputs the analysis results in the order specified in the parameters.

Slow query log instance

Configuration settings for slow queries

Command Line execution parameters to view analysis results

] # Cd/var/lib/mysql/log
] # Mysqldumpslow-s r-t 10 slow-mysql

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

Install the tool before using it. If you already have it, skip the following 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. Installation Toolkit
] # Rpm-ivh percona-toolkit-3.0.7-1.el7.x86_64.rpm

Analyze slow query logs by executing commands

] # Pt-query-digest -- user = root -- password = redhat -- host = 127.0.0.1 slow-mysql> slow. rep
The analysis result is as follows:

The whole process of the MySQL server processing the query request

1. The client sends an SQL request to the server
2. Check whether the cache server has hit the SQL
3. The server performs SQL parsing and preprocessing, and then the optimizer corresponds to the execution plan.
4. Call the storage engine API to query data according to the execution plan
5. Return the result to the client.

Impact of query cache on SQL Performance

1. Check whether the entire query hits the data in the query cache first.
2. implemented through a case-sensitive hash Query

Query Cache Optimization Parameters

Query_cache_type
ON, OFF, DEMAND

Note: DEMAND indicates that only SQL--CACHE and SQL _NO_CACHE are used in the query statement to control whether cache is required

Query_cache_size: Set the memory size of the query cache.

Query_cache_limit

Query_cache_wlock_invalidate sets whether the data in the cache is returned after the data table is locked (disabled by default, and this option is also recommended)

Query_cache_min_res_unit

Causes of incorrect execution plan generated by MySQL

1. Inaccurate statistics
2. The cost estimation in the execution plan is not the same as the actual execution plan cost.
3. The optimal MySQL Optimizer may be 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 will not consider its uncontrolled costs

SQL types that can be optimized by the MySQL Optimizer

1. Redefine the Association Sequence of the table
The optimizer determines the table Association Sequence Based on the statistical information.

2. Convert external links into internal connections
Where condition and database table structure

3. Use equivalent conversion rules
(5 = 5 and a> 5) will be rewritten to a> 5

4. Optimized 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 conversion rules

7. subquery Optimization

8. Optimize in () Conditions

How to determine the time spent in each phase of Query Processing

Use profile

Set profiling = 1;
Run the query:
Show profiles;

Show profile for query N;

Time consumed by 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 switchover to modify the structure of a large table. For example, modify the table structure from the server. After the modification is completed, perform master-slave switchover and then modify the large table on the old master, there are some risks.
2. Create a new table on the master server. The table structure is to modify the table structure after the large table, re-import the old table data to the new table, and create a series of triggers in the old table, synchronize the data in the old table to the new table. When all the data in the old table is synchronized to the new table, add an exclusive lock to the old table, change the new table to the old table name, and delete the renamed old table, as shown in

Use the pt-online-schema-change command to modify a large table, as shown in

Parameter description

-- SQL statement used by alter
-- Logon user of the user database
-- Password: the password of the login user
D. Specify the database names of all the modified tables.
T table name
-- Charset specifies the database string
-- Excute execution

Original Work, reprinted, please indicate the source