MySQL解释查询理解 [英] MySQL explain Query understanding

问题描述

我已经阅读了一些博客以及一些与优化有关的文章，介绍了如何优化查询.我读到我需要使用索引，并确保使用良好的关系数据库架构正确设置了所有主键和外键.

I've read on some blogs and in some articles related to optimization, how to optimize queries. I read I need to use indexes and make sure all my primary key and foreign keys are set correctly using a good relational database schema.

现在我有一个需要优化的查询，我在EXPLAIN上得到了它:

Now I have a query I need to optimize and I get this on the EXPLAIN:

Using where; Using temporary; Using filesort

我正在使用MySQL 5.5

I am using MySQL 5.5

我知道我正在使用WHERE，但是不使用我的临时表或文件排序吗?这是什么意思?

I know I am using WHERE but not with my temporary table nor filesort? What does this mean?

推荐答案

使用临时表意味着MySQL需要使用一些临时表来存储执行查询时计算出的中间数据.

Using temporary means that MySQL need to use some temporary tables for storing intermediate data calculated when executing your query.

使用文件排序是一种排序算法，其中MySQL无法使用索引进行排序，因此无法在内存中进行完整的排序.而是将排序分成较小的块，然后合并结果以获取最终的排序数据.

Using filesort is a sorting algorithm where MySQL isn't able to use an index for sorting and therefore can't do the complete sort in memory. Instead it breaks the sort into smaller chunks and then merge the results to get the final sorted data.

请参考 http://dev.mysql.com/doc/refman/5.0/en/explain-output.html .

我认为您可能正在使用ORDER BY以及一些派生表或子查询.如果您可以粘贴查询和相关的表/索引信息以及EXPLAIN输出，那就太好了.

I think you might be using an ORDER BY plus some derived table or sub-query. It would be great if you could paste your query and relevant tables/indexes information and the EXPLAIN output.

这篇关于MySQL解释查询理解的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！