在mysql中测试查询性能 [英] Testing performance of queries in mysql

问题描述

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

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

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

• 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%')

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

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；我确实认为这可能是由于文件系统缓存和/或用于执行查询的索引缓存所致，例如

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列.然后，

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.

这篇关于在mysql中测试查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！