sql 查询连接多个表 - 太慢(8 个表) [英] sql query joins multiple tables - too slow (8 tables)

问题描述

我正在尝试将 8 个表合并为一个以创建其他应用程序使用的索引，我的查询如下:(我的 mysql 技能非常业余)

i'm trying to join 8 tables into one in order to create index used by other application, my query is like : (my mysql skill's very amateur)

SELECT t1_id, t2_name, t3_name, t4_name, t5_name, 
       t6_name, t7_name, t8_name, t9_name 
FROM t1 
  LEFT JOIN t2 ON (t1_id = t2_id) 
  LEFT JOIN t3 ON (t3_id = t1_id) 
  LEFT JOIN t4 ON (t4_id = t1_id)
  LEFT JOIN t5 ON (t5_id = t1_id)
  LEFT JOIN t6 ON (t6_id = t1_id) 
  LEFT JOIN t7 ON (t7_id = t1_id)
  LEFT JOIN t8 ON (t8_id = t1_id)
  LEFT JOIN t9 ON (t9_id = t1_id)

我在执行时甚至看不到查询结果，有什么方法可以加快速度?:) 任何类型的帮助表示赞赏，但最好只有一个查询(应用程序规则之外)

i can't even see the query results when i executing it, any ways to speed it up? :) any kinds of help is appreciated, but it's better be only one query (outside application rules)

提前致谢

推荐答案

我遇到了类似的问题，将多个查找表连接到一个所有 id 字段都已编入索引的大表.为了监控连接对查询时间执行的影响，我多次运行我的查询(限制为前 100 行)，每次都向附加表添加一个连接.加入12张表后，查询执行时间没有明显变化.当我加入第 13 个表时，执行时间跃升至 1 秒；第14桌4秒，第15桌20秒，第16桌90秒.

I had a similar problem with several lookup tables joining to a large table with all id fields indexed. To monitor the effect of the joins on query time execution, I ran my query several times (limiting to first 100 rows), adding a Join to an additional table each time. After joining 12 tables, there was no significant change in query execution time. By the time I had joined the 13th table the execution time jumped to a 1 second; 14th table 4 seconds, 15th table 20 s, 16th 90 seconds.

Keijro 建议使用相关子查询而不是连接，例如

Keijro's suggestion to use a correlated subqueries instead of joins e.g.

SELECT t1_id, 
        (select t2_name from t2 where t1_id = t2_id), 
        (select t3_name from t3 where t1_id = t3_id), 
        (select t4_name from t4 where t1_id = t4_id), 
        (select t5_name from t5 where t1_id = t5_id), 
        (select t6_name from t6 where t1_id = t6_id), 
        (select t7_name from t7 where t1_id = t7_id), 
        (select t8_name from t8 where t1_id = t8_id), 
        (select t9_name from t9 where t1_id = t9_id)  FROM t1

显着提高了查询性能.事实上，子查询似乎并没有延长执行查询的时间(查询几乎是即时的).

improved query performance dramatically. In fact the subqueries did not seem to lengthen the time to execute the query (the query was almost instanteous).

我有点惊讶，因为我认为相关子查询的性能比连接差.

I am a little suprised as I thought correlated subqueries perform worse than joins.

这篇关于sql 查询连接多个表 - 太慢(8 个表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！