MySQL 如何检查mysql查询的性能？

How to check performance of mysql query?

提问by Anand Singh

I have been learning query optimization, increase query performance and all but in general if we create a query how can we know if this is a wise query.

我一直在学习查询优化，提高查询性能，但总的来说，如果我们创建一个查询，我们如何知道这是否是一个明智的查询。

I know we can see the execution time below, But this time will not give a clear indication without a good amount of data. And usually, when we create a new query we don't have much data to check.

我知道我们可以在下面看到执行时间，但是如果没有大量数据，这个时间不会给出明确的指示。通常，当我们创建一个新查询时，我们没有太多数据要检查。

I have learned about clauses and commands performance. But is there is anything by which we can check the performance of the query? Performance here is not execution time, it means that whether a query is "ok"or not, without data dependency.

我已经了解了子句和命令的性能。但是有什么方法可以检查查询的性能吗？这里的性能不是执行时间，它意味着查询是否“ok”，没有数据依赖。

As we cannot create that much data that would be in live database.

因为我们无法在实时数据库中创建那么多数据。

采纳答案by teddyjm

General performance of a query can be checked using the EXPLAIN command in MySQL. See https://dev.mysql.com/doc/refman/5.7/en/using-explain.html

可以使用 MySQL 中的 EXPLAIN 命令检查查询的一般性能。见https://dev.mysql.com/doc/refman/5.7/en/using-explain.html

It shows you how MySQL engine plans to execute the query and allows you to do some basic sanity checks i.e. if the engine will use keys and indexes to execute the query, see how MySQL will execute the joins (i.e. if foreign keys aren't missing) and many more.

它向您展示了 MySQL 引擎如何计划执行查询并允许您进行一些基本的健全性检查，即引擎是否将使用键和索引来执行查询，查看 MySQL 将如何执行连接（即，如果外键没有丢失） ） 还有很多。

You can find some general tips about how to use EXPLAIN for optimizing queries here (along with some nice samples): http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

您可以在此处找到有关如何使用 EXPLAIN 优化查询的一般提示（以及一些不错的示例）：http: //www.sitepoint.com/using-explain-to-write-better-mysql-queries/

回答by Rick James

I like to throw my cookbookat Newbies because they often do not understand how important INDEXesare, or don't know some of the subtleties.

我喜欢把我的食谱扔给新手，因为他们经常不明白有多重要INDEXes，或者不知道一些微妙之处。

When experimenting with multiple choices of query/schema, I like to use

在尝试多种查询/模式选择时，我喜欢使用

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

That counts low level actions, such as "read next record". It essentially eliminates caching issues, disk speed, etc, and is very reproducible. Often there is a counter in that output (or multiple counters) that match the number of rows in the table (sometimes +/-1) -- that tells me there are table scan(s). This is usuallynot as good as if some INDEXwere being used. If the query has a LIMIT, that value may show up in some Handler.

这包括低级操作，例如“读取下一条记录”。它基本上消除了缓存问题、磁盘速度等问题，并且具有很高的可重现性。通常在该输出（或多个计数器）中有一个计数器与表中的行数（有时是 +/-1）相匹配——这告诉我有表扫描。这通常不如使用某些INDEX方法好。如果查询有LIMIT，则该值可能会显示在某些处理程序中。

A really bad query, such as a CROSS JOIN, would show a value of N*M, where N and M are the row counts for the two tables.

一个非常糟糕的查询，例如 a CROSS JOIN，会显示 N*M 的值，其中 N 和 M 是两个表的行数。

I used the Handler technique to 'prove' that virtually all published "get me a random row" techniques require a table scan. Then I could experiment with small tables and Handlers to come up with a list of faster random routines.

我使用 Handler 技术来“证明”几乎所有已发布的“获取随机行”技术都需要进行表扫描。然后我可以尝试使用小表和处理程序来提出更快的随机例程列表。

Another tip when timing... Turn off the Query_cache (or use SELECT SQL_NO_CACHE).

计时时的另一个提示...关闭 Query_cache（或使用SELECT SQL_NO_CACHE）。

回答by Arun Palanisamy

As mentioned above, Right query is always data-dependent. Up to some level you can use the below methods to check the performance

如上所述，Right 查询始终为data-dependent。在某种程度上，您可以使用以下方法来检查性能

1. You can use Explainto understand the Query Execution Planand that may help you to correct some stuffs. For more info : Refer Documentation Optimizing Queries with EXPLAIN

2. You can use Query Analyzer. Refer MySQL Query Analyzer

1. 您可以使用它Explain来了解查询执行计划，这可能会帮助您纠正一些问题。有关更多信息：请参阅使用 EXPLAIN 优化查询的文档

2. 您可以使用Query Analyzer. 参考MySQL 查询分析器