摆脱“使用临时"；使用文件排序" [英] Getting rid of "Using temporary; Using filesort"
问题描述
当我对查询进行解释
我看到第一行的附加"下有使用临时；使用文件排序".我知道这很糟糕，但我不知道这到底意味着什么或如何解决.
I see that it has "Using temporary; Using filesort" under "Extra" for the first row. I understand this is bad but I don't know what exactly it means or how to fix it.
If you want to see my query, here's a more general question I asked about the same query: MySQL query optimization and EXPLAIN for a noob. For reference, the query involves 24 tables and 23 joins.
我现在的问题是:
- 使用临时"和使用文件排序"是什么意思?
- 假设它们不好，我如何摆脱它们?
推荐答案
如前所述，使用文件排序"和使用临时"并不总是表示性能不佳.
As said already, "using filesort" and "using temporary" do not always imply bad performance.
此处是一些基本准则用于提高ORDER BY语句的性能.亮点:
Here are some basic guidelines for improving performance of ORDER BY statements. The highlights:
如果要提高ORDER BY速度，请检查是否可以让MySQL使用索引而不是额外的排序阶段.如果无法做到这一点，则可以尝试以下策略:
If you want to increase ORDER BY speed, check whether you can get MySQL to use indexes rather than an extra sorting phase. If this is not possible, you can try the following strategies:
增加尺寸 sort_buffer_size变量.
Increase the size of the sort_buffer_size variable.
增加尺寸 read_rnd_buffer_size变量.
Increase the size of the read_rnd_buffer_size variable.
通过仅声明与保留存储在其中的值所需的列一样大的列，每行使用较少的RAM.例如，如果值不超过16个字符，则CHAR(16)优于CHAR(200).
Use less RAM per row by declaring columns only as large as they need to be to hold the values stored in them. For example, CHAR(16) is better than CHAR(200) if values never exceed 16 characters.
首先尝试使用索引(确保要排序的字段具有索引).请注意，增加系统变量 sort_buffer_size 和 read_rnd_buffer_size 也可以对其他查询有负面影响-请考虑为您需要的会话专门设置它们，并为所有其他会话保留默认设置.
First try to use indices (make sure the fields you are sorting by have indices). Note that increasing the system variables sort_buffer_size and read_rnd_buffer_size can also have a negative effect on other queries - consider setting them specifically for the session you need them for and leave them at default for all other sessions.
这篇关于摆脱“使用临时"；使用文件排序"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
