我有 7 张 table 。表 1 是表 2-7 的“父表”和 has_many 关系。

我想做一个查询来获取表 1 的行，以及表 2-7 中的 ID 列表。我试过下面的查询，但对于大型数据库，我的查询需要 15 秒。我想知道如何使这个查询更快？

编辑 一些注意事项: -> 删除 Distinct 只节省了 4 秒，我仍然得到 10-11 秒的查询

-> 从查询中删除 1 个连接表(无论哪个)将时间从 15 秒减少到 2-3 秒。删除 2 个连接表(同样无关紧要)将查询减少到 1/2 秒。

SELECT  
  table1.table1_id as table1Id, 
  GROUP_CONCAT(DISTINCT table2.table2_id) AS table2Ids,
  GROUP_CONCAT(DISTINCT table3.table3_id) AS table3Ids,
  GROUP_CONCAT(DISTINCT table4.table4_id) AS table4Ids,
  GROUP_CONCAT(DISTINCT table5.table5_id) AS table5Ids,
  GROUP_CONCAT(DISTINCT table6.table6_id) AS table6Ids,
  GROUP_CONCAT(DISTINCT table7.table7_id) AS table7Ids
  FROM table1
  LEFT JOIN table2 ON table1.table1_id = table2.table1_id
  LEFT JOIN table3 ON table1.table1_id = table3.table1_id
  LEFT JOIN table4 ON table1.table1_id = table4.table1_id
  LEFT JOIN table5 ON table1.table1_id = table5.table1_id
  LEFT JOIN table6 ON table1.table1_id = table6.table1_id
  LEFT JOIN table7 ON table1.table1_id = table7.table1_id
  WHERE table1.archived = false
  GROUP BY table1.table1_id LIMIT 1000

我的解释查询:

+----+-------------+------------------+------------+-------+----------------------------------------------------------------------------------------------------+---------------------------+---------+--------------------------+------+----------+-------------+
| id | select_type | table            | partitions | type  | possible_keys                                                                                      | key                       | key_len | ref                      | rows | filtered | Extra       |
+----+-------------+------------------+------------+-------+----------------------------------------------------------------------------------------------------+---------------------------+---------+--------------------------+------+----------+-------------+
|  1 | SIMPLE      | table1           | NULL       | index | PRIMARY,unique_name_in_table8, table8_idx,table9_idx,table10_idx                                   | PRIMARY                   | 4       | NULL                     |    1 |    10.00 | Using where |
|  1 | SIMPLE      | table2           | NULL       | ref   | PRIMARY                                                                                            | PRIMARY                   | 4       | db.table1.table1_id      |   20 |   100.00 | Using index |
|  1 | SIMPLE      | table3           | NULL       | ref   | table3_to_table1_id_idx                                                                            | table3_to_table1_id_idx   | 4       | db.table1.table1_id      |  824 |   100.00 | Using index |
|  1 | SIMPLE      | table4           | NULL       | ref   | table4_word_unique,table4_to_table1_id_idx                                                         | table4_to_table1_id_idx   | 4       | db.table1.table1_id      |    4 |   100.00 | Using index |
|  1 | SIMPLE      | table5           | NULL       | ref   | table5_to_table1_id_idx                                                                            | table5_to_table1_id_idx   | 4       | db.table1.table1_id      |   26 |   100.00 | Using index |
|  1 | SIMPLE      | table6           | NULL       | ref   | table6_to_table1_id_idx                                                                            | table6_to_table1_id_idx   | 4       | db.table1.table1_id      |    3 |   100.00 | Using index |
|  1 | SIMPLE      | table7           | NULL       | ref   | table7_to_table1_id_idx                                                                            | table7_to_table1_id_idx   | 4       | db.table1.table1_id      |  483 |   100.00 | Using index |
+----+-------------+------------------+------------+-------+----------------------------------------------------------------------------------------------------+---------------------------+---------+--------------------------+------+----------+-------------+

最佳答案

我试图重写查询。试试让我知道。我还没有测试过。

SELECT  
  t1.table1Id, 
  GROUP_CONCAT(DISTINCT table2.table2_id) AS table2Ids,
  GROUP_CONCAT(DISTINCT table3.table3_id) AS table3Ids,
  GROUP_CONCAT(DISTINCT table4.table4_id) AS table4Ids,
  GROUP_CONCAT(DISTINCT table5.table5_id) AS table5Ids,
  GROUP_CONCAT(DISTINCT table6.table6_id) AS table6Ids,
  GROUP_CONCAT(DISTINCT table7.table7_id) AS table7Ids
FROM (
    SELECT table1_id as table1Id
    FROM table1
    WHERE table1.archived = false
    GROUP BY table1.table1_id LIMIT 1000
      ) AS t1
  INNER JOIN table2 ON t1.table1Id = table2.table1_id
  INNER JOIN table3 ON t1.table1Id = table3.table1_id
  INNER JOIN table4 ON t1.table1Id = table4.table1_id
  INNER JOIN table5 ON t1.table1Id = table5.table1_id
  INNER JOIN table6 ON t1.table1Id = table6.table1_id
  INNER JOIN table7 ON t1.table1Id = table7.table1_id

关于mysql 7 + 加入一个表，如何加速？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/44588997/