在mysql中测试查询的性能
我正在尝试设置一个脚本来测试开发 mysql 服务器上的查询性能.以下是更多详细信息:
I am trying to setup a script that would test performance of queries on a development mysql server. Here are more details:
- 我有根访问权限
- 我是唯一访问服务器的用户
- 最感兴趣的是 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.
直到现在我一直在使用 SQL_NO_CACHE，但有时此类测试的结果还显示了缓存行为 - 第一次运行需要更长的时间来执行，而在后续运行中花费的时间更少.
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；我相信这可能是由于文件系统缓存和/或用于执行查询的索引缓存，如 this 帖子解释了.我不清楚 Buffer Pool 和 Key Buffer 何时失效或它们如何干扰测试.
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 this post explains. It is not clear to me when Buffer Pool and Key Buffer get invalidated or how they might interfere with testing.
因此，除了重新启动 mysql 服务器之外，您建议如何设置一个环境来可靠地确定一个查询是否比另一个查询执行得更好?
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?
推荐答案
假设你不能优化 LIKE 操作本身，你应该尝试优化基本查询，而不用它们最小化应该检查的行数.
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.
一些可能对此有用的东西:
Some things that might be useful for that:
rows 列在 EXPLAIN SELECT ... 结果中.然后，
rows column in EXPLAIN SELECT ... result. Then,
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)
那么，
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)
另一个有趣的值是last_query_cost，它显示优化器估计查询的成本(该值是随机页面读取的次数):
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):
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 2635.399000 |
+-----------------+-------------+
1 row in set (0.00 sec)
MySQL 文档是您的朋友.
MySQL documentation is your friend.
相关文章