时间戳

首页 > 解决方案 > 提高 mysql 上的 sql 查询性能

mysql - 提高 mysql 上的 sql 查询性能

问题描述

实际上，我有一个包含十个查询联合的 sql 查询。因此，由于资源消耗，我想避免这些工会。在这个例子中，我只展示了一个联合。

  SELECT *
    FROM (  SELECT sv.SubmissionId,
                   sv1.FieldValue Etablissement,
                   sv2.FieldValue Nom,
                   sv3.FieldValue Prenom,
                   sv4.FieldValue Fonction,
                   sv5.FieldValue Identification,
                   sv6.FieldValue NomFormation,
                   ''         Signature
              FROM dfmna_rsform_submission_values sv
                   INNER JOIN dfmna_rsform_submissions sub
                       ON sv.SubmissionId = sub.SubmissionId
                   LEFT JOIN dfmna_rsform_submission_values sv1
                       ON sv.SubmissionId = sv1.SubmissionId
                      AND sv1.FieldName = 'NomEtablissement-Individuelle'
                   LEFT JOIN dfmna_rsform_submission_values sv2
                       ON sv.SubmissionId = sv2.SubmissionId
                      AND sv2.FieldName = 'Nom-Individuelle'
                   LEFT JOIN dfmna_rsform_submission_values sv3
                       ON sv.SubmissionId = sv3.SubmissionId
                      AND sv3.FieldName = 'Prenom-Individuelle'
                   LEFT JOIN dfmna_rsform_submission_values sv4
                       ON sv.SubmissionId = sv4.SubmissionId
                      AND sv4.FieldName = 'Fonction-Individuelle'
                   LEFT JOIN dfmna_rsform_submission_values sv5
                       ON sv.SubmissionId = sv5.SubmissionId
                      AND sv5.FieldName = 'NumAdelirpps-Individuelle'
                   LEFT JOIN dfmna_rsform_submission_values sv6
                       ON sv.SubmissionId = sv6.SubmissionId
                      AND sv6.FieldName = 'Nom-Formation'
             WHERE sv.FormId = 4
               AND sub.confirmed = 1
          GROUP BY sv.SubmissionId
          UNION ALL
            SELECT sv.SubmissionId,
                   sv1.FieldValue Etablissement,
                   sv2.FieldValue Nom,
                   sv3.FieldValue Prenom,
                   sv4.FieldValue Fonction,
                   sv5.FieldValue Identification,
                   sv6.FieldValue NomFormation,
                   ''         Signature
              FROM dfmna_rsform_submission_values sv
                   INNER JOIN dfmna_rsform_submissions sub
                       ON sv.SubmissionId = sub.SubmissionId
                   LEFT JOIN dfmna_rsform_submission_values sv1
                       ON sv.SubmissionId = sv1.SubmissionId
                      AND sv1.FieldName = 'NomEtablissement-Continue'
                   LEFT JOIN dfmna_rsform_submission_values sv2
                       ON sv.SubmissionId = sv2.SubmissionId
                      AND sv2.FieldName = 'Stg-Nom-Continue'
                   LEFT JOIN dfmna_rsform_submission_values sv3
                       ON sv.SubmissionId = sv3.SubmissionId
                      AND sv3.FieldName = 'Stg-Prenom-Continue'
                   LEFT JOIN dfmna_rsform_submission_values sv4
                       ON sv.SubmissionId = sv4.SubmissionId
                      AND sv4.FieldName = 'Stg-Fonction-Continue'
                   LEFT JOIN dfmna_rsform_submission_values sv5
                       ON sv.SubmissionId = sv5.SubmissionId
                      AND sv5.FieldName = 'Stg-NumAdelirpps-Continue'
                   LEFT JOIN dfmna_rsform_submission_values sv6
                       ON sv.SubmissionId = sv6.SubmissionId
                      AND sv6.FieldName = 'Nom-Formation'
             WHERE sv.FormId = 4
               AND sub.confirmed = 1
          GROUP BY sv.SubmissionId) t
   WHERE t.Nom <> ''
     AND t.NomFormation = 'my_criteria'
ORDER BY t.Nom;

所以我想要相同的输出，但我想要另一种方式来编写这个查询而不做联合。谢谢你。

标签： mysqlsqlperformanceentity-attribute-value

解决方案

欢迎来到 EAV（实体-属性-值）模式的丑陋世界。（我添加了一个标签，以便您可以找到更多关于它的问答。）

   LEFT JOIN dfmna_rsform_submission_values sv4
                   ON sv.SubmissionId = sv4.SubmissionId
                  AND sv4.FieldName = 'Fonction-Individuelle'

表dfmna_rsform_submission_values需要

PRIMARY KEY(SubmissionId, FieldName)

它可能不需要AUTO_INCREMENT.

我建议的 PK 将有助于表现一些. 但 EAV 本质上是一个问题。

另一个问题......你真的是这个意思LEFT吗？这意味着该属性是可选的。

         WHERE sv.FormId = 4
           AND sub.confirmed = 1

由于一个INDEX不能跨越两个表，这很难优化。将其中一列移到另一张表中是否合理？

      GROUP BY sv.SubmissionId

可能是“错误”查找ONLY_FULL_GROUP_BY。

WHERE t.Nom <> ''
  AND t.NomFormation = 'my_criteria'

您是否收集了很多行，只是为了稍后过滤掉其中的一些。将它们折叠到内部SELECTs。

一旦你完成了所有这些，让我们再做一次。但请提供SHOW CREATE TABLE并且EXPLAIN SELECT ... 可能需要一个综合索引来帮助解决NomFormation = 'my_criteria'。

推荐阅读