如何使用PHP Profiler跟踪Mysql慢查询日志文件条目 [英] How to Trace Mysql Slow-query-log-file Entries Using PHP Profiler

问题描述

我刚刚开始使用PHP事件探查器在php脚本中查找导致mysql查询缓慢的文件.有人建议我使用 xdebug 从慢日志文件条目中的时间戳(与在同一时间戳中执行的php profiler中的文件相比)进行跟踪.

I just started using PHP profiler to find files in php script which caused slow mysql queries. Some suggested that I use xdebug to track it with the timestamp from the slow log-file entry compared to the files in php profiler that was executed at the same timestamp.

我已经阅读了xdebug文档，但找不到该问题的解释.

I have read the xdebug documentation but can't find an explanation of this problem.

谁能给我启示?

我正在使用php 7.0，Debian 9.

I'm using php 7.0, Debian 9.

我的慢速查询日志文件条目:

My slow-query-log-file entries:

# Thread_id: 222244  Schema: user  QC_hit: No
# Query_time: 51.019708  Lock_time: 0.000119  Rows_sent: 1  Rows_examined: 13295012
# Rows_affected: 0
SET timestamp=1559388099;
SELECT (COUNT(*)) AS `count` 
FROM statistics Statistics WHERE (id >= 1 AND ad_type <> 3);

这与计数SELECT语句中的行无关，而与如何跟踪导致缓慢请求发生的应用程序文件有关.

It's not about counting rows in a SELECT statement, but it's about how to track application files that cause slow requests to occur.

推荐答案

对大型表进行计数的最有效方法是，您应该将计数存储在其他表中，并在需要时增加/减少该值，那样'仅查询单个单元格，而51秒的查询不到1秒.

The most efficient way for counting large tables is that you should be storing the count in a table somewhere else and increasing/decreasing that value when required, that way you're only querying a single cell and a 51second query becomes less than 1 second.

我知道这是一件多余的事情，但这是最有效，最优化的方法

I know it feels like a redundant thing to do but it is the most efficient and optimal way

围绕着一些主题，这些主题建议查询INFORMATION_SCHEMA，但是鉴于您对WHERE的需求，这根本无济于事，其他所有事情都和您在此遇到的问题一样低效率.

There are topics around that suggest querying the INFORMATION_SCHEMA but that doesn't help at all given your need for WHERE and everything else is just as inefficient as your issue here.

您需要的只是当前计数，存储它的位置，增加/减少它的功能，您就可以了

All you need is the current count, a place to store it, functionality to increase/decrease it and you're good to go

这篇关于如何使用PHP Profiler跟踪Mysql慢查询日志文件条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！