mysql - SQL查询确实很慢，如何改进？

Question

编辑:问题似乎出在 help_file 表中，该表包含每次选择该表时加载的 .pdf 文件的实际二进制数据，有什么方法可以解决这个问题吗？我不想每次都加载所有数据。

我不太精通 SQL 查询的运行时效率，但这个查询非常慢，大约需要两秒钟才能执行。

SELECT
  id,
  display_name,
  NAME,
  document_id,
  chapter,
  aenderung_datum,
  DATA,
  file_attached,
  right_group,
  role,
  partner,
  sequence_nr
FROM
  (SELECT
    hf.id,
    hf.display_name,
    hf.name,
    hf.document_id,
    hf.aenderung_datum,
    DATA,
    LENGTH(DATA) > 0 AS file_attached,
    GROUP_CONCAT(
      DISTINCT (
        IF(
          b.name IS NULL,
          a.right_group_id,
          b.name
        )
      )
      ORDER BY a.right_group_id
    ) AS right_group,
    GROUP_CONCAT(
      DISTINCT (
        IF(
          role.name IS NULL,
          role_id,
          role.name
        )
      )
      ORDER BY role_id
    ) AS role,
    GROUP_CONCAT(
      DISTINCT (IF(ch.name IS NULL, '-', ch.name))
      ORDER BY a.chapter_id
    ) AS chapter,
    GROUP_CONCAT(
      DISTINCT (
        IF(c.name IS NULL, partner_id, c.name)
      )
      ORDER BY partner_id
    ) AS partner,
    GROUP_CONCAT(
      DISTINCT a.sequence_nr
      ORDER BY a.sequence_nr
    ) AS sequence_nr
  FROM
    db_release_osp.help_file hf
    LEFT JOIN db_release_osp.help_file_assign AS a
      ON help_file_id = hf.id
    LEFT JOIN cdb_admin_osp.right_group AS b
      ON a.right_group_id = b.id
    LEFT JOIN cdb_admin_osp.role
      ON a.role_id = role.id
    LEFT JOIN db_release_osp.help_chapter AS ch
      ON a.chapter_id = ch.id
    LEFT JOIN cdb_osp_admin.partner AS c
      ON partner_id = c.id
  GROUP BY hf.id
  ORDER BY hf.id) AS overview_table

有什么我忽略的大缺陷吗？我怎样才能缩短所需的时间？

我想使用临时表可能是一个解决方案。

Answer 1

首先，您的外部查询返回内部查询的确切结果，但字段的顺序除外(章节)。如果您明确需要这种方式，我会将 Chapter 字段移动到预期位置，然后删除多余的外部查询。

SELECT
      hf.id,
      hf.display_name,
      hf.name,
      hf.document_id,
      GROUP_CONCAT( DISTINCT (IF(ch.name IS NULL, 
         '-', ch.name))
         ORDER BY a.chapter_id ) AS chapter,
      hf.aenderung_datum,
      DATA,
      LENGTH(DATA) > 0 AS file_attached,
      GROUP_CONCAT( DISTINCT ( IF( b.name IS NULL,
         a.right_group_id, b.name )) 
         ORDER BY a.right_group_id ) AS right_group,
      GROUP_CONCAT( DISTINCT ( IF( role.name IS NULL,
         a.role_id, role.name ))
         ORDER BY a.role_id ) AS role,
      GROUP_CONCAT( DISTINCT ( IF(c.name IS NULL, 
         a.partner_id, c.name))
         ORDER BY a.partner_id ) AS partner,
      GROUP_CONCAT( DISTINCT a.sequence_nr 
         ORDER BY a.sequence_nr ) AS sequence_nr
   FROM
      db_release_osp.help_file hf
         LEFT JOIN db_release_osp.help_file_assign AS a
            ON hf.id = a.help_file_id
            LEFT JOIN cdb_admin_osp.right_group AS b
               ON a.right_group_id = b.id
            LEFT JOIN cdb_admin_osp.role
               ON a.role_id = role.id
            LEFT JOIN db_release_osp.help_chapter AS ch
               ON a.chapter_id = ch.id
            LEFT JOIN cdb_osp_admin.partner AS c
               ON partner_id = c.id
   GROUP BY 
      hf.id
   ORDER BY 
      hf.id

接下来我将在表上提供以下索引，以帮助它们“覆盖”索引。这样，它可以直接从索引中获取 id 和名称(或其他部分)，而无需转到原始数据页查找查找表值。

table             index
help_file         (id)
right_group       (id, name )
role              (id, name )
help_chapter      (id, name )
partner           (id, name )
help_file_assign  (help_file_id, right_group_id, role_id, chapter_id, partner_id )
-- Note: Due to lack of alias on your left-join to the partner table
-- alias "c", it APPEARS the "Partner_ID" column is from your 
-- help_file_assign table.  If other, please adjust, but also fix the
-- alias reference for clarification.

如果性能仍然困扰着您，您可能需要再添加一个关键字，因为这会告诉 MySQL 完全按照所显示的方式运行查询。

SELECT STRAIGHT_JOIN  (rest of query)