问题描述
我需要一种简单的方法来测试SQL查询的速度.我不必担心硬件差异，我基本上需要一个相对编号.
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.
这就是我一直在使用PHP所做的(它很模糊，但是可以工作):
This is what I've been doing with PHP (its fuzzy, but works):
// 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;
我遇到的问题是，有时在最初的连接/运行中，我的测试花费了1.25秒.但是，在随后的连接上需要0.004秒的时间... 这是为什么?
The PROBLEM I 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?
我非常确定my.ini中的MySQL查询缓存已关闭:
I'm pretty sure MySQL query cache is off in my.ini:
query_cache_size=0
推荐答案
您的第一个查询可能会变慢，因为MySQL实际上是在第一个查询而不是第二个查询上访问磁盘.
Your first query may be slower because MySQL is actually hitting the disk on the first query, and not on the second.
您的操作系统可能会在读取文件时将其缓存在内存中；因此，后续的读取可能不需要实际击中磁盘，并且返回速度会更快.
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.
根据经验，我通常会运行几次查询，并寻找一致性.通常，第一次跑步会花几倍的时间，而第二次第三和第四次会花费相同的时间.后续的运行可能更能代表您在实际生产系统上看到的性能-因为生产数据库应该将数据保存在OS缓存中，而很少访问开发系统.
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 really need 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.
这篇关于如何测试MySQL查询速度，并减少不一致性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！