php 如何测试 MySQL 查询速度，减少不一致？

How to test MySQL query speed, with less inconsistencies?

提问by farinspace

I need a simple way to test SQL queries for speed. I am not to worried about hardware differences, I basically need a relative number.

我需要一种简单的方法来测试 SQL 查询的速度。我不担心硬件差异，我基本上需要一个相对数字。

This is what I've been doing with PHP (its fuzzy, but works):

这就是我一直在用 PHP 做的事情（模糊，但有效）：

// CONNECT TO DB HERE

$sub = new YomoSubscription(95,783);

$t = microtime(TRUE);

// contains the SQL db call i'm testing
$fp = $sub->generateFingerprint(); 

echo microtime(TRUE)-$t;

The PROBLEMI am having is that, sometimes on initial connect/run my test takes 1.25 sec for example. However on subsequent connects it takes 0.004 sec ... Why is this?

我遇到的问题是，有时在初始连接/运行时，我的测试需要 1.25 秒。但是在随后的连接上需要 0.004 秒......这是为什么？

I'm pretty sure MySQL query cache is off in my.ini:

我很确定 MySQL 查询缓存在 my.ini 中已关闭：

query_cache_size=0

采纳答案by Frank Farmer

Your first query may be slower because MySQL is actually hitting the disk on the first query, and not on the second.

您的第一个查询可能会较慢，因为 MySQL 实际上是在第一个查询中访问磁盘，而不是在第二个查询中。

Your operating system may cache files in memory as they are read; as a result, subsequent reads may not need to actually hit the disk, and will return much faster.

您的操作系统可能会在读取文件时将文件缓存在内存中；因此，后续读取可能不需要实际访问磁盘，并且返回速度会快得多。

As a rule of thumb, I generally run a query a few times, and look for consistency. More often than not, the first run will take several times longer, while the 2nd 3rd and 4th take about the same amount of time. Those subsequent runs are probably more representative of the sort of performance you'll see on an actual production system -- since your production database should keep that data in the OS cache, while your dev system is rarely accessed.

根据经验，我通常会运行几次查询，并寻找一致性。通常情况下，第一次运行会花费数倍的时间，而第二次、第三次和第四次则需要大约相同的时间。这些后续运行可能更能代表您将在实际生产系统上看到的那种性能——因为您的生产数据库应该将这些数据保存在操作系统缓存中，而您的开发系统很少被访问。

In the end, when it comes to query performance, for the most part, you should just give it a quick pass in development, and monitor the slow query log in production to see which queries reallyneed work.

最后，当谈到查询性能时，在大多数情况下，您应该在开发中快速通过，并在生产中监控慢查询日志以查看哪些查询真正需要工作。

As far as programatically running queries for performance data goes -- take several samples, and use the median. But in practice, this isn't going to be terribly representative of the actual performance issues you'll run into in production.

至于以编程方式运行的性能数据查询——取几个样本，并使用中位数。但在实践中，这并不能很好地代表您将在生产中遇到的实际性能问题。

回答by Thomas Bonini

Try using SELECT BENCHMARK(times, query)

尝试使用 SELECT BENCHMARK(times, query)

More information: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_benchmark

更多信息：http: //dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_benchmark

回答by Toto

Let's assume that:

让我们假设：

1. you do not use persistant connection
2. the database installed on the server where the stats are done (no network connection)
3. nobody else is using the database (row/table locks)
4. no other heavy process is running
5. etc....

1. 你不使用持久连接
2. 安装在完成统计的服务器上的数据库（无网络连接）
3. 没有其他人在使用数据库（行/表锁）
4. 没有其他繁重的进程正在运行
5. 等等....

If you really want to benchmark your query, you have to do the following:

如果您真的想对查询进行基准测试，则必须执行以下操作：

$database->query('SET SESSION query_cache_type = OFF');

Then you run the query 2-3 times in a loop (to "warm up" the server).

然后循环运行查询 2-3 次（以“预热”服务器）。

And only then:

只有这样：

$database->query('FLUSH STATUS'); #If you use the stats to profile your query

$t = microtime(TRUE);
$fp = $sub->generateFingerprint();
echo microtime(TRUE)-$t;

$database->query('SHOW STATUS');

Et voila!! :)))

等等！！:)))

BTW, query speed is one of the parameter to read. Learn how to read the very precious information returned by SHOW STATUSand EXPLAIN .... This will be a lot better.

顺便说一句，查询速度是读取的参数之一。了解如何阅读SHOW STATUS和返回的非常宝贵的信息EXPLAIN ...。这会好很多。

Here is a link you will love: http://www.xaprb.com/blog/2006/10/12/how-to-profile-a-query-in-mysql/

这是您会喜欢的链接：http: //www.xaprb.com/blog/2006/10/12/how-to-profile-a-query-in-mysql/

Enjoy. :)

享受。:)

回答by Byron Whitlock

You might be using persistant connections in your class. A pconnect will reuse the connection and would account for this type of lag.

您可能在类中使用持久连接。pconnect 将重用连接并解释这种类型的延迟。