MySQL ORDER BY多连接优化

Question

我需要一些帮助优化我的数据库的一些查询。我明白使用索引来帮助连接和通过语句进行排序来帮助加快速度，但是我想知道是否有一些技术可用来避免使用filesort，并在使用EXPLAIN命令时使用临时文件。这是我正在使用的一个例子。

SELECT a.id, DATE_FORMAT(a.submitted_at, '%d-%b-%Y') as submitted_at, a.user_id, 
      data1.*, 
      data2.name, data2.type, 
      u.first_name, u.last_name    
      FROM applications AS a 
      LEFT JOIN users AS u ON u.id = a.user_id 
      LEFT JOIN score_table AS data1 ON data1.applications_id = a.id 
      LEFT JOIN sections AS data2 ON data2.id = data1.section_id 
      WHERE category_id = [value] && submitted_at IS NOT NULL 
      ORDER BY data2.type 

同样，索引正在我的查询中正确使用，就像上面的查询一样。如果我取出ORDER BY子句，则查询将使用适当的索引快速执行。我知道连接的顺序会影响查询的性能。当我在用户表上使用ORDER BY进行测试时，因为它是“const”之后的下一个表，所以它只会在EXPLAIN上使用“Using where，Using Filesort”。如果我放到任何其他表中，我们会进入“使用临时”问题。

我的问题是：什么是最佳方式来优化这样的查询运行得更快，在最好的情况下，避免在EXPLAIN中使用filesort/temporary？我对任何可能性都开放:)对于如何使这样的查询执行更好的理论，我对或多或少感兴趣，而不是这个确切的查询，因为我必须在这些深层次的ORDER BY查询中执行越来越多的查询我正在处理的数据库。

- 编辑 -

这里是上面的查询的解释.....

id select_type  table type possible_keys    key   key_len  ref     rows Extra 
1 SIMPLE   a  ref  category_id,submitted_at category_id  4   const    49  Using where; Using temporary; Using filesort 
1 SIMPLE   u  eq_ref PRIMARY      PRIMARY  4   a.user_id   1 
1 SIMPLE   data1 ref  app id      app id  4   a.id    7 
1 SIMPLE   data2 eq_ref PRIMARY      PRIMARY  4   data1.section_id 1 

Answer 1

夫妇的事情。

1. 您确定需要使用'LEFT JOIN'吗？看看这个查询，看起来你可以用'INNER JOIN'逃脱，这会减少潜在的行数。

2. 您没有发布您的架构，但我认为users.id，applications.user_id，score_table.applications_id，applications.id，sections.id和score_table.section_id都是整数？如果他们是非整数，我会强烈要求你转换它们。如果不是主键，请确保它们已被编入索引。

3. 我不会运行任何mysql级别的数据格式（即DATE_FORMAT），因为它会在查询过程中产生一些开销，而我会在应用层格式化数据。

4. ORDER BY强制MySQL创建临时表以便正确排序，因此请确保您绝对需要此功能。如果是这样，请确保sections.type已编入索引。

5. 我会考虑使用不同的别名命名约定。 data1和data2是如此抽象，很难辨别它们实际上指的是什么。例如，我建议你使用你正在别名的表格的缩写构造;应用程序变成应用程序（而不是a），score_table变成分数（而不是数据1）等。