Shulou(Shulou.com)06/02 Report--

This article mainly introduces "how to get the sql with performance problems". In the daily operation, I believe that many people have doubts about how to obtain the sql with performance problems. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to get the sql with performance problems". Next, please follow the editor to study!

User feedback: get sql with performance problems

Slow log: get sql with performance problems

Obtain sql with performance in real time

Use slow query logs to get sql with performance problems

Slow_query_log starts and stops recording slow query logs

Slow_query_log_file specifies the storage path and files for slow log

Long_query_time specifies the threshold of sql execution time for recording slow query logs

Does log_queries_not_using_indexes record the sql of unused indexes

1: user information and thread id

2: query execution time

3: lock time

4. Return the number of data rows

5. Number of rows of scanning data

6. Time to execute sql

7. Sql executed

Commonly used slow query log analysis tool mysqldumpslow

Pt-query-digest, a common slow query analysis tool

Pt-query-digest\

-- explain hobby 127.0.0.1, upright rootdiary paired ssW0rd\ slow-mysql.log

Real-time query

The time spent at each stage of query processing

Use profile

1. Set profiling = 1

Execute query

Start profile

This is a session-level configuration

2 、 show profiles

View information on the total time consumed by each query

Mysql > show profiles

+- -+

| | Query_ID | Duration | Query | |

+- -+

| | 1 | 0.00012525 | sel |

Select count (*) from temp_shop_user |

| | 2 | 0.09744925 | select count (*) from temp_shop_user |

3 、 show profiles for query N

The time consumed by each phase of the query

Mysql > show profile for query 1

+-+ +

| | Status | Duration |

+-+ +

| | starting | 2.5E-5 |

| | freeing items | 9.6E-5 |

| | logging slow query | 3E-6 |

| | cleaning up | 2E-6 |

+-+ +

4 rows in set

4. Query cpu information

Mysql > show profile cpu for query 3

+-+

| | Status | Duration | CPU_user | CPU_system | |

+-+

| | starting | 3.5E-5 | 0 | 0 | |

| | checking permissions | 6E-6 | 0 | 0 | |

| | Opening tables | 1.3E-5 | 0 | 0 | |

| | System lock | 6E-6 | 0 | 0 | |

| | init | 8E-6 | 0 | 0 | |

| | optimizing | 3E-6 | 0 | 0 | |

| | statistics | 7E-6 | 0 | 0 | |

| | preparing | 4E-6 | 0 | 0 | |

| | executing | 2E-6 | 0 | 0 | |

| | Sending data | 0.0296 | 0.03125 | 0 | |

| | end | 1E-5 | 0 | 0 | |

| | query end | 3E-6 | 0 | 0 | |

| | closing tables | 8E-6 | 0 | 0 | |

| | freeing items | 0.000125 | 0 | 0 | |

| | logging slow query | 2E-6 | 0 | 0 | |

| | cleaning up | 3E-6 | 0 | 0 | |

+-+

16 rows in set

5 、 performance_schema

Version after 5.6, it is recommended to enable

1 > enter the performance_schema library

Mysql > use performance_schema

Database changed

2 > mysql > update setup_instruments set enabled = 'YES' where name like' stage%'

Query OK, 0 rows affected

Rows matched: 0 Changed: 0 Warnings: 0

3 > start the history table

Mysql > update setup_consumers set enabled = 'YES' where name like' events%'

Query OK, 0 rows affected

Rows matched: 6 Changed: 0 Warnings: 0

At this point, the study on "how to get sql with performance problems" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.