使用限制子句连接和排序的表数量进行查询优化

Question

我有一个查询，其中使用 distincct - left join - order by - limit 子句连接多个表。

查询如下所示：-

Select DISTINCT a.col1, b.col2, c.col3, d.col4, e.col5, f.col6, g.col7, h.col8, 
i.col9, j.col10
From test_a a 
left join test_b b on a.col1 = b.col2 
left join test_c c on c.col1 = d.col2 
left join test_d d on d.col1 = c.col2 
left join test_e e on e.col1 = d.col2 
left join test_f f on f.col1 = e.col2 
left join test_g g on g.col1 = f.col2 
left join test_h h on h.col1 = a.col1 
left join test_i i on i.col1 = f.col2 
left join test_j j on j.col1 = i.col2
Where a.col2 = 'Y' 
and c.col4  = 1
Order by h.col5 desc 
limit 50;

在条件中使用的所有列都有索引。这个查询的解释输出给出了结果集，我可以看到它正确使用了所有索引，并且从所有表扫描的总行数是 18000。

我在这个查询中想知道的是。如果我在没有 order by 子句的情况下运行它，它会在几秒钟内运行。例如：

Select DISTINCT a.col1, b.col2, c.col3, d.col4, e.col5, f.col6, g.col7, h.col8, 
i.col9, j.col10
From test_a a 
left join test_b b on a.col1 = b.col2 
left join test_c c on c.col1 = d.col2 
left join test_d d on d.col1 = c.col2 
left join test_e e on e.col1 = d.col2 
left join test_f f on f.col1 = e.col2 
left join test_g g on g.col1 = f.col2 
left join test_h h on h.col1 = a.col1 
left join test_i i on i.col1 = f.col2 
left join test_j j on j.col1 = i.col2
Where a.col2 = 'Y' 
and c.col4  = 1
limit 50;

如果我使用 order by 子句运行它，那么执行需要 30-40 秒。

我尝试使用 mysql:- USE INDEX FOR ORDER BY (idx_h_col5) 提供的索引提示功能，但在执行此查询时遇到语法错误。错误消息显示语法不正确，

我在 order by 子句中使用的列上有一个复合索引。我还尝试在此列上创建单个索引，但没有任何效果。

Answer 1

MySQL 可以使用键进行排序，而不是在获取数据后对结果进行排序，但前提是满足几个条件。
您可以在此处查看这些条件的列表：http:// /dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

在您的情况下，我认为多个 JOIN 会阻止快速排序。提到的 MySQL 无法使用索引进行排序的情况之一是：

您正在加入许多表，并且
ORDER BY 中的列并非全部
从第一个非常量表可以看出
用于检索行。 （这是
EXPLAIN 输出中的第一个表
没有 const 连接类型。）

我不确定是否有解决方法。这取决于表结构和实际查询。

要获得更多帮助，请尝试发布有序查询的解释输出。

Answer 2

我首先尝试添加复合索引：

Table a
    (col2, col1)

Table b
    (col4, col1)

和简单索引

Table h
    (col5)