MySQL MySQL讲解查询理解
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7999833/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL explain Query understanding
提问by Ken
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.
我在一些博客和一些与优化相关的文章中阅读了如何优化查询。我读到我需要使用索引并确保使用良好的关系数据库模式正确设置所有主键和外键。
Now I have a query I need to optimize and I get this on the EXPLAIN:
现在我有一个需要优化的查询，我得到了这个EXPLAIN：
Using where; Using temporary; Using filesort
I am using MySQL 5.5
我正在使用 MySQL 5.5
I know I am using WHEREbut not with my temporary table nor filesort? What does this mean?
我知道我正在使用WHERE但不使用我的临时表或文件排序？这是什么意思？
采纳答案by Abhay
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.
使用文件排序是一种排序算法，其中 MySQL 无法使用索引进行排序，因此无法在内存中进行完整排序。相反，它将排序分解为更小的块，然后合并结果以获得最终的排序数据。
Please refer to http://dev.mysql.com/doc/refman/5.0/en/explain-output.html.
请参考http://dev.mysql.com/doc/refman/5.0/en/explain-output.html。
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.
我认为您可能正在使用 ORDER BY 加上一些派生表或子查询。如果您可以粘贴查询和相关表/索引信息以及 EXPLAIN 输出，那就太好了。
回答by Aditya P Bhatt
Syntax:
句法：
Explain `MySQL Query`
Example:EXPLAIN SELECT * FROM categoriesG
例子：EXPLAIN SELECT * FROM categoriesG
Example:EXPLAIN EXTENDED SELECT City.Name FROM City
JOIN Country ON (City.CountryCode = Country.Code)
WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'G
例子：EXPLAIN EXTENDED SELECT City.Name FROM City
JOIN Country ON (City.CountryCode = Country.Code)
WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'G
Explain followed with your mysql query
解释后跟您的 mysql 查询
