MySQL 在mysql中测试查询的性能

Testing performance of queries in mysql

提问by Unreason

I am trying to setup a script that would test performance of queries on a development mysql server. Here are more details:

我正在尝试设置一个脚本来测试开发 mysql 服务器上的查询性能。以下是更多详细信息：

• I have root access
• I am the only user accessing the server
• Mostly interested in InnoDB performance
• The queries I am optimizing are mostly search queries (SELECT ... LIKE '%xy%')

• 我有root权限
• 我是唯一访问服务器的用户
• 最感兴趣的是 InnoDB 性能
• 我正在优化的查询主要是搜索查询 ( SELECT ... LIKE '%xy%')

What I want to do is to create reliable testing environment for measuring the speed of a single query, free from dependencies on other variables.

我想要做的是创建可靠的测试环境来测量单个查询的速度，而不受其他变量的依赖。

Till now I have been using SQL_NO_CACHE, but sometimes the results of such tests also show caching behaviour - taking much longer to execute on the first run and taking less time on subsequent runs.

到目前为止，我一直在使用SQL_NO_CACHE，但有时此类测试的结果也会显示缓存行为 - 在第一次运行时执行时间更长，而在后续运行中花费的时间更少。

If someone can explain this behaviour in full detail I might stick to using SQL_NO_CACHE; I do believe that it might be due to file system cache and/or caching of indexes used to execute the query, as thispost explains. It is not clear to me when Buffer Pool and Key Buffer get invalidated or how they might interfere with testing.

如果有人可以详细解释这种行为，我可能会坚持使用SQL_NO_CACHE; 我确实相信这可能是由于文件系统缓存和/或用于执行查询的索引缓存，正如这篇文章所解释的。我不清楚 Buffer Pool 和 Key Buffer 何时失效或它们如何干扰测试。

So, short of restarting mysql server, how would you recommend to setup an environment that would be reliable in determining if one query performs better then the other?

因此，除了重新启动 mysql 服务器之外，您将如何建议设置一个环境，该环境可以可靠地确定一个查询是否比另一个查询执行得更好？

回答by newtover

Assuming that you can not optimize the LIKE operation itself, you should try to optimize the base query without them minimizing number of rows that should be checked.

假设您不能优化 LIKE 操作本身，您应该尝试优化基本查询，而不用它们最小化应该检查的行数。

Some things that might be useful for that:

一些可能对此有用的东西：

rowscolumn in EXPLAIN SELECT ... result. Then,

rowsEXPLAIN SELECT ... 结果中的列。然后，

mysql> set profiling=1;
mysql> select sql_no_cache * from mytable;
 ...
mysql> show profile;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000063 |
| Opening tables     | 0.000009 |
| System lock        | 0.000002 |
| Table lock         | 0.000005 |
| init               | 0.000012 |
| optimizing         | 0.000002 |
| statistics         | 0.000007 |
| preparing          | 0.000005 |
| executing          | 0.000001 |
| Sending data       | 0.001309 |
| end                | 0.000003 |
| query end          | 0.000001 |
| freeing items      | 0.000016 |
| logging slow query | 0.000001 |
| cleaning up        | 0.000001 |
+--------------------+----------+
15 rows in set (0.00 sec)

Then,

然后，

mysql> FLUSH STATUS;
mysql> select sql_no_cache * from mytable;
...
mysql> SHOW SESSION STATUS LIKE 'Select%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Select_full_join       | 0     |
| Select_full_range_join | 0     |
| Select_range           | 0     |
| Select_range_check     | 0     |
| Select_scan            | 1     |
+------------------------+-------+
5 rows in set (0.00 sec)

And another interesting value is last_query_cost, which shows how expensive the optimizer estimated the query (the value is the number of random page reads):

另一个有趣的值是last_query_cost，它显示了优化器估计查询的开销（该值是随机页面读取的次数）：

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 2635.399000 |
+-----------------+-------------+
1 row in set (0.00 sec)

MySQL documentation is your friend.

MySQL 文档是您的朋友。

回答by ablaeul

Cited from this page: SQL_NO_CACHE options affect caching of query results in the query cache. If your table is quite small, it is possible, that the table itself is already cached. Since you just avoid caching of the results and not the tables you get the described behavior sometimes. So, as told in the other postings, you should flush your tablesin between the queries.

引自本页： SQL_NO_CACHE 选项影响查询缓存中查询结果的缓存。如果您的表非常小，则表本身可能已经被缓存。由于您只是避免缓存结果而不是表，因此有时会获得所描述的行为。因此，如其他帖子所述，您应该在查询之间刷新您的表。

回答by Bram Schoenmakers

Have you considered using Maatkit? One of its capabilities I'm slightly familiar with is to capture MySQL network data with tcpdump and process the dump with mk-query-digest. This tool allows you to show some fine grained details about each query. But there's a whole bunch of other tools which should make query analysis easier.

你考虑过使用Maatkit吗？我稍微熟悉的其中一项功能是使用 tcpdump 捕获 MySQL 网络数据并使用mk-query-digest. 此工具允许您显示有关每个查询的一些细粒度详细信息。但是还有一大堆其他工具可以使查询分析更容易。

回答by David M

As the linked article suggests, use FLUSH TABLESbetween test runs to reset as much as you can (notably the query cache).

正如链接文章所建议的那样，FLUSH TABLES在测试运行之间使用尽可能多地重置（特别是查询缓存）。

Shouldn't your testing take into account that InnoDB will itself have different states during actual performance, such that you become interested in aggregate performance over multiple trials? How "real" is your performance testing going to be if you want to reset InnoDB for every trial? The query you reject because it performs poorly immediately after restart might be far and away the best query after InnoDB has warmed up a little bit.

您的测试不应该考虑到 InnoDB 本身在实际性能期间会具有不同的状态，从而使您对多次试验的总体性能感兴趣吗？如果您想为每次试验重置 InnoDB，您的性能测试有多“真实”？您拒绝的查询，因为它在重新启动后立即执行不佳可能是 InnoDB 稍微预热后的最佳查询。

If I were you, I'd focus on what the query optimizer is doing separately from InnoDB's performance. There's much written about how to tune InnoDB, but it helps to have good queries to start.

如果我是你，我会关注查询优化器在做什么，而不是 InnoDB 的性能。有很多关于如何调整 InnoDB 的文章，但它有助于开始良好的查询。

You could also try measuring performance with equivalent MyISAM tables, where FLUSH TABLESreally will reset you to a mostly-identical starting point.

您还可以尝试使用等效的 MyISAM 表来衡量性能，这FLUSH TABLES确实会让您回到一个几乎相同的起点。

Have you tried turning query caching off altogether? Even with SQL_NO_CACHE, there's about a 3% penalty just having the query cache on.

您是否尝试过完全关闭查询缓存？即使使用 SQL_NO_CACHE，仅打开查询缓存也会有大约 3% 的损失。

回答by mhughes

Full text queries on InnoDB are slow(LIKE "%query%" statements) , there is nothing that you can do to optimize them. Solutions vary from passing that particular table you are querying to MyISAM so you can create fulltext indexes (which innoDB does not support), to denormalizing the row into searchable indexes (not recommended), Doctrine ORM provides an easy example of how to archieve this : http://www.doctrine-project.org/documentation/manual/1_1/nl/behaviors:core-behaviors:searchableThe "proper" solution to your problem would be to index the information youre using full text searches on, with a solution like Sphinx Search or Apache Solr.

InnoDB 上的全文查询很慢（LIKE "%query%" statements），您无法优化它们。解决方案从将您正在查询的特定表传递给 MyISAM 以便您可以创建全文索引（innoDB 不支持），到将行非规范化为可搜索索引（不推荐），Doctrine ORM 提供了一个简单的示例来说明如何归档： http://www.doctrine-project.org/documentation/manual/1_1/nl/behaviors:core-behaviors:searchable 问题的“正确”解决方案是使用全文搜索索引您正在使用的信息，并使用Sphinx Search 或 Apache Solr 等解决方案。

Like previously said, you must consider the cache state when comparing results, a primed cache gives extremely performant queries. You should consider the cache hit percentage of a particular query, even if it is an expensive query, if it has a 99% cache hit ratio, the average performance will be very high.

如前所述，您必须在比较结果时考虑缓存状态，已准备好的缓存可提供极高性能的查询。你应该考虑一个特定查询的缓存命中率，即使它是一个昂贵的查询，如果它有 99% 的缓存命中率，平均性能会非常高。

Finegrained tuning of queries is not a silver bullet, you might be adding complexity to your application for the sake of optimizations that overall in a production enviroment, are negligible.

查询的细粒度调优不是灵丹妙药，您可能会为了优化而增加应用程序的复杂性，而在生产环境中的整体优化可以忽略不计。

Consider your workload, troubleshoot frequent , unperforming queries (use the slow_query_log in mysql, dont blindly start optimizing queries).

考虑你的工作量，对频繁的、性能不佳的查询进行故障排除（使用mysql中的slow_query_log，不要盲目开始优化查询）。

回答by Spidfire

You could try the mysql workbench, i thought it had a sql statement monitor so you can see how fast it is and why it is fast

你可以试试 mysql 工作台，我以为它有一个 sql 语句监视器，所以你可以看到它有多快以及为什么它很快