Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "MySQL SQL sentence analysis and query optimization example analysis", the content is easy to understand, well-organized, hope to help you solve your doubts, the following let Xiaobian lead you to study and learn "MySQL SQL sentence analysis and query optimization example analysis" this article.

How to get SQL with performance problems

1. Obtain SQL with performance problems through user feedback

2. Obtain the SQL of performance problems through the slow query log

3. Obtain SQL with performance problems in real time

Use slow query logs to get SQL with performance problems

First of all, introduce the parameters related to slow query.

1. Slow_query_log starts custom recording slow query log.

You can set set global slow_query_log=on through the MySQL command line

Or modify the / etc/my.cnf file to add slow_query_log=on

2. Slow_query_log_file specifies the storage path and files of slow log

It is recommended that log storage and data storage be stored separately.

3. Long_query_time specifies the threshold of SQL execution time for recording slow query logs

① records all eligible SQL

② data modification statement

③ includes query statements

SQL that ④ has 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. Whether log_queries_not_using_indexes records SQL without using index

5. Log_output sets the saving format of slow log query (if you need to save it to a file, please modify it to FILE)

Slow query uses the information recorded in the log

1. The information recorded in the first line is the test done with sbtest

2. The time when the information recorded in the second line is 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 row is the number of data rows 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 a timestamp

7. The information recorded in the seventh line is the SQL statement of the query

Use slow queries to get SQL with performance problems

Commonly used slow query log analysis tool (mysqldumpslow)

Summary: summarize the identical SQL except for the query conditions, and output the analysis results in the order specified in the parameters

Slow query log instance

Configuration settings related to slow query

Command line execution parameters to view the results of the analysis

] # cd / var/lib/mysql/log

] # mysqldumpslow-s r-t 10 slow-mysql

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

You need to install the tool before using it. 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. Change 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 kit

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

Execute command to analyze slow query log

] # 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 whole process of processing query requests by the MySQL server

1. The client sends a SQL request to the server

2. The server checks whether the SQL is hit in the cache server.

3. The server carries out SQL parsing and preprocessing, and then the optimizer executes the plan accordingly.

4. According to the execution plan, call the storage engine API to query the data

5. Return the result to the client

Effect of query caching on SQL performance

1. Give priority to checking whether the entire query hits the data in the query cache

2. Implemented by a case-sensitive hash lookup

Optimization parameters of query cache

Query_cache_type sets whether the query cache is available

ON,OFF,DEMAND

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

Query_cache_size sets the memory size of the query cache

Query_cache_limit sets the maximum available storage for the query cache

Query_cache_wlock_invalidate sets whether the data in the cache is returned after the data table is locked (off by default, and it is recommended that this option be turned off)

Query_cache_min_res_unit sets the minimum value of memory blocks allocated by the query cache

Causes MySQL to generate an incorrect execution plan

1. The statistical information is not accurate

2. The cost estimate in the implementation plan is not equal to the actual cost of the implementation plan.

3. What the MySQL optimizer thinks 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 will not consider the cost beyond its control.

SQL types that can be optimized by MySQL optimizer

1. Redefine the association order of tables

The optimizer determines the association order of the tables based on the statistics.

2. Convert the outer link to the inner link

Where condition and database table structure, etc.

3. Use equivalent transformation rules

(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 consumed in each stage of query processing

Use profile

Set profiling = 1

Execute the query:

Show profiles

Show profile for query N

The time consumed by each phase of the query

Use profile to view the time consumed by the statement

Specific SQL query optimization

1. Using the principle of master-slave switch to modify the table structure of a large table, for example, now modify from the server, after the modification, master-slave switch, and then modify the large table on the old master, there is a certain risk.

2. Create a new table on the main server. The table structure is to modify the structure of the table behind the big table, then import the data of the old table back into the new table, and set up a series of triggers in the old table to synchronously update the data of the old table to the new table. When all the data in the old table is synchronized to the new table, add exclusive locks to the old table, change the new table to the name of 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, as shown in the following figure

Parameter interpretation of the above figure

-- sql statement used by alter

-- Login user of user database

-- password of password logged in user

D specify the database name of all modified tables

The name of the t table

-- charset specifies the string of the database

-- excute execution

These are all the contents of the article "sample Analysis of SQL sentence Analysis and query Optimization in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.