有没有办法在没有慢速日志的情况下在MySQL中获取rows_examined? [英] Is there a way to get rows_examined in MySQL without the slow log?

问题描述

我正在为自家应用创建一些个人资料信息.我希望调试页面显示发送的查询以及检查了多少行，而无需假设slow_log已打开，更不用说解析它了.

I'm building some profile information for a home grown app. I'd like the debug page to show the query sent along with how many rows were examined without assuming that slow_log is turned on, let alone parsing it.

回到2006年，我想要的是不可能.今天仍然如此吗?

Back in 2006, what I wanted was not possible. Is that still true today?

我看到Peter Zaitsev有一个技术，您在哪里:

I see Peter Zaitsev has a technique where you:

1. 运行FLUSH STATUS;
2. 运行查询.
3. 运行SHOW STATUS LIKE "Handler%";

1. Run FLUSH STATUS;
2. Run the query.
3. Run SHOW STATUS LIKE "Handler%";

，然后在输出中:

Handler_read_next = 42250表示在此扫描期间分析了42250行

Handler_read_next=42250 means 42250 rows were analyzed during this scan

这听起来像是MySQL仅在检查索引，它应该给您编号.但是，是否有一组状态变量可以轮询，累加并找出要检查的行数?还有其他想法吗?

which sounds like if MySQL is only examining indexes, it should give you the number. But are there a set of status vars you can poll, add up and find out how many rows examined? Any other ideas?

推荐答案

它比2006年略好.您可以在之前和之后发出SHOW SESSION STATUS，然后查看每个Handler_read_ *的计数，以便能够告诉检查的行数.

It's slightly better than it was in 2006. You can issue SHOW SESSION STATUS before and after and then look at each of the Handler_read_* counts in order to be able to tell the number of rows examined.

实际上没有其他方法.虽然服务器协议中有一个标志来说明是否发生表扫描，但它不会公开rows_examined.甚至像MySQL的查询分析器之类的工具也必须通过在之前/之后运行SHOW SESSION STATUS来工作(尽管我认为它只在之后显示SHOW SESSION STATUS ，因为它会记住以前的值).

There's really no other way.. While the server protocol has a flag to say if a table scan occurred, it doesn't expose rows_examined. Even tools like MySQL's Query Analyzer have to work by running SHOW SESSION STATUS before/after (although I think it only runs SHOW SESSION STATUS after, since it remembers the previous values).

我知道这与您的原始问题无关，但是除了rows_examined之外，还有其他昂贵的查询组件.如果您选择通过慢速日志来执行此操作，则应签出此补丁程序:

I know it's not related to your original question, but there are other expensive components to queries besides rows_examined. If you choose to do this via the slow log, you should check out this patch:

http://www.percona.com/docs/wiki/patches :microslow_innodb#changes_to_the_log_format

我建议您寻找"Disk_tmp_table:是"和"Disk_filesort:是".

I can recommend looking for "Disk_tmp_table: Yes" and "Disk_filesort: Yes".

这篇关于有没有办法在没有慢速日志的情况下在MySQL中获取rows_examined?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！