如何加快多个连接的MySQL查询

Question

这是我的问题，我正在选择并进行多个连接以获取正确的项目......它抽取了大量100,000行以上的行。当日期范围设置为1年时，此查询需要5分钟以上。

我不知道这是否可能，但恐怕用户可能会将日期范围扩展到十年并使其崩溃。

任何人都知道我可以如何加快速度？这是查询。

SELECT DISTINCT t1.first_name, t1.last_name, t1.email 
FROM table1 AS t1 
INNER JOIN table2 AS t2 ON t1.CU_id = t2.O_cid 
INNER JOIN table3 AS t3 ON t2.O_ref = t3.I_oref 
INNER JOIN table4 AS t4 ON t3.I_pid = t4.P_id 
INNER JOIN table5 AS t5 ON t4.P_cat = t5.C_id 
WHERE t1.subscribe =1 
AND t1.Cdate >= $startDate 
AND t1.Cdate <= $endDate 
AND t5.store =2 

我不是最大的MySQL所以任何帮助将不胜感激！

在此先感谢！

UPDATE

这里是说明你要的

id select_type  table type possible_keys key  key_len  ref  rows Extra 
1 SIMPLE t5 ref  PRIMARY,C_store_type,C_id,C_store_type_2 C_store_type_2 1 const 101  Using temporary 
1 SIMPLE t4 ref  PRIMARY,P_cat P_cat 5 alphacom.t5.C_id 326  Using where 
1 SIMPLE t3 ref  I_pid,I_oref I_pid 4 alphacom.t4.P_id 31 
1 SIMPLE t2 eq_ref O_ref,O_cid  O_ref 28 alphacom.t3.I_oref 1  
1 SIMPLE t1 eq_ref PRIMARY  PRIMARY  4 alphacom.t2.O_cid 1 Using where 

而且我添加了一个索引表5行和表4行，因为他们没有真正改变，但其他表得到解决每月500-1000个条目......我听说你应该在一张有很多新条目的表格中添加一个索引......这是真的吗？

Answer 1

我会尝试以下方法：

首先，确保有以下表和列的索引（每组圆括号中的列应该是一个单独的索引）：

table1 : (subscribe, CDate) 
     (CU_id) 
table2 : (O_cid) 
     (O_ref) 
table3 : (I_oref) 
     (I_pid) 
table4 : (P_id) 
     (P_cat) 
table5 : (C_id, store) 

其次，如果将上述指标没有改善的事情，就像你喜欢的话，请尝试重写查询作为

SELECT DISTINCT t1.first_name, t1.last_name, t1.email FROM 
    (SELECT CU_id, t1.first_name, t1.last_name, t1.email 
    FROM table1 
    WHERE subscribe = 1 AND 
      CDate >= $startDate AND 
      CDate <= $endDate) AS t1 
    INNER JOIN table2 AS t2 
    ON t1.CU_id = t2.O_cid 
    INNER JOIN table3 AS t3 
    ON t2.O_ref = t3.I_oref 
    INNER JOIN table4 AS t4 
    ON t3.I_pid = t4.P_id 
    INNER JOIN (SELECT C_id FROM table5 WHERE store = 2) AS t5 
    ON t4.P_cat = t5.C_id 

我希望这里的第一子-select将显着减少要考虑加入的行数，希望使后续连接的工作量减少。同样，table5上的第二个子选择背后的推理也是如此。

在任何情况下，都会搅乱它。我的意思是，最终它只是一个选择 - 你不能真正伤害任何东西。检查每个不同排列产生的计划，并试图找出每个排列的好坏。

分享和享受。

Answer 2

确保您的日期列和您加入的所有列都已编入索引。

在你的日期做一个不相等的操作符意味着它检查每一行，它本质上比等价的慢。

另外，使用DISTINCT可以为优化器在幕后运行的逻辑添加额外的比较。如果可能的话消除。

Answer 3

听起来像你应该考虑提供子集（分页）或以其他方式限制结果，除非有用户需要同时需要每行的可能性。通常100K行比普通人更容易消化。

Answer 4

嗯，首先，做一个子查询抽取表1下降到只有你真正想去参加的所有麻烦的记录...

SELECT DISTINCT t1.first_name, t1.last_name, t1.email 
FROM ( 
SELECT first_name, last_name, email, CU_id FROM table1 WHERE 
table1.subscribe = 1 
AND table1.Cdate >= $startDate 
AND table1.Cdate <= $endDate 
) AS t1 
INNER JOIN table2 AS t2 ON t1.CU_id = t2.O_cid 
INNER JOIN table3 AS t3 ON t2.O_ref = t3.I_oref 
INNER JOIN table4 AS t4 ON t3.I_pid = t4.P_id 
INNER JOIN table5 AS t5 ON t4.P_cat = t5.C_id 
WHERE t5.store = 2 

然后开始寻找在修改的连接的方向。

此外，如果t5.store只是非常罕见的2，然后翻转这个想法：构建t5子查询，然后加入它背对背。

Answer 5

尝试在加入的字段中添加索引。它可能会或可能不会提高性能。

此外，它还取决于您正在使用的引擎。如果您使用的是InnoDB，请检查您的配置参数。我遇到了类似的问题，因为innodb的默认配置不会像myisam的默认配置那么大。

Answer 6

大家都说，确保你有索引。

您还可以检查您的服务器是否设置正确，以便它可以在内存中包含更多或完整的数据集。

没有解释，没有太多的工作。另外请记住，MySQL将查看您的JOIN，并在执行查询之前遍历所有可能的解决方案，这可能需要一些时间。一旦您从EXPLAIN获得了最佳JOIN顺序，您可以尝试在查询中强制执行此顺序，从优化程序中消除此步骤。

Answer 7

目前，您的查询正在返回table2-table5上的所有匹配行，以确定t5.store是否为2.如果table2-table5中的任何一行的行数比table1高得多，则这可能会大大增加行数加工 - 因此，下面的查询可以执行显著更好：

SELECT DISTINCT t1.first_name, t1.last_name, t1.email 
FROM table1 AS t1 
WHERE t1.subscribe =1 
AND t1.Cdate >= $startDate 
AND t1.Cdate <= $endDate 
AND EXISTS 
(SELECT NULL FROM table2 AS t2 
INNER JOIN table3 AS t3 ON t2.O_ref = t3.I_oref 
INNER JOIN table4 AS t4 ON t3.I_pid = t4.P_id 
INNER JOIN table5 AS t5 ON t4.P_cat = t5.C_id AND t5.store =2 
WHERE t1.CU_id = t2.O_cid);