导入过程后出现奇怪的MYSQL查询问题

Question

我有一个我认为是一个完美的数据库副本。不幸的是，特定的查询现在变得非常慢。这些是由MySQL工作台生成的查询分析图像。你可以看到在底部(较慢的)连接的顺序是不同的，这似乎是瓶颈。我不知道是什么原因会导致连接顺序改变。有谁有什么想法吗？

​

​

​

​

查询：

SELECT 
    mutation.ENTREZ_GENE_ID AS entrezGeneId,
    gene.HUGO_GENE_SYMBOL AS hugoGeneSymbol,
    COUNT(*) AS totalCount,
    COUNT(DISTINCT (mutation.SAMPLE_ID)) AS numberOfAlteredCases
FROM
    mutation
        INNER JOIN
    mutation_event ON mutation_event.MUTATION_EVENT_ID = mutation.MUTATION_EVENT_ID
        AND mutation_event.MUTATION_TYPE != 'Fusion'
        INNER JOIN
    genetic_profile ON mutation.GENETIC_PROFILE_ID = genetic_profile.GENETIC_PROFILE_ID
        INNER JOIN
    sample ON mutation.SAMPLE_ID = sample.INTERNAL_ID
        INNER JOIN
    gene ON mutation.ENTREZ_GENE_ID = gene.ENTREZ_GENE_ID
WHERE
    mutation.SAMPLE_ID IN (SELECT 
            sample.INTERNAL_ID
        FROM
            sample
                INNER JOIN
            patient ON sample.PATIENT_ID = patient.INTERNAL_ID
                INNER JOIN
            genetic_profile ON patient.CANCER_STUDY_ID = genetic_profile.CANCER_STUDY_ID
        WHERE
            genetic_profile.STABLE_ID = 'ccle_broad_2019_mutations'
                AND sample.STABLE_ID IN (...)
                AND sample.INTERNAL_ID = mutation.SAMPLE_ID
                AND genetic_profile.GENETIC_PROFILE_ID = mutation.GENETIC_PROFILE_ID)
GROUP BY mutation.ENTREZ_GENE_ID

在慢速数据库上使用截断的样本列表进行解释：

+=====================================================================+======================================================================+=======+
|                   Query execution plan on DB2  (Slow)               |                   Query execution plan on DB1                        | Same? |
+=====================================================================+======================================================================+=======+
| id : 1,                                                             | id : 1,                                                              | Yes   |
+---------------------------------------------------------------------+----------------------------------------------------------------------+-------+
| select_type : SIMPLE,                                               | select_type : SIMPLE,                                                | Yes   |
+---------------------------------------------------------------------+----------------------------------------------------------------------+-------+
| table : mutation,                                                   | table : mutation,                                                    | Yes   |
+---------------------------------------------------------------------+----------------------------------------------------------------------+-------+
| partitions : null,                                                  | partitions : null,                                                   | Yes   |
+---------------------------------------------------------------------+----------------------------------------------------------------------+-------+
| type : ref,                                                         | type : ref,                                                          | Yes   |
+---------------------------------------------------------------------+----------------------------------------------------------------------+-------+
| possible_keys : UQ_MUTATION_EVENT_ID_GENETIC_PROFILE_ID_SAMPLE_ID,  | possible_keys : UQ_MUTATION_EVENT_ID_GENETIC_PROFILE_ID_SAMPLE_ID,   |       |
|       GENETIC_PROFILE_ID,GENETIC_PROFILE_ID_2,GENETIC_PROFILE_ID_3, |        GENETIC_PROFILE_ID,GENETIC_PROFILE_ID_2,GENETIC_PROFILE_ID_3, |       |
|       ENTREZ_GENE_ID,SAMPLE_ID,MUTATION_EVENT_ID,                   |        ENTREZ_GENE_ID,SAMPLE_ID,MUTATION_EVENT_ID,                   | Yes   |
+---------------------------------------------------------------------+----------------------------------------------------------------------+-------+
| key : GENETIC_PROFILE_ID,                                           | key : GENETIC_PROFILE_ID_2,                                          | No    |
+---------------------------------------------------------------------+----------------------------------------------------------------------+-------+
| key_len : 4,                                                        | key_len : 8,                                                         | No    |
+---------------------------------------------------------------------+----------------------------------------------------------------------+-------+
| ref : const,                                                        | ref : const,cgds_public.sample.INTERNAL_ID,                          | No    |
+---------------------------------------------------------------------+----------------------------------------------------------------------+-------+
| rows : 792,                                                         | rows : 68,                                                           | No    |
+---------------------------------------------------------------------+----------------------------------------------------------------------+-------+
| filtered : 0.01,                                                    | filtered : 100.00,                                                   | No    |
+---------------------------------------------------------------------+----------------------------------------------------------------------+-------+
| Extra : Using where; Using index                                    | Extra : null                                                         | No    |
+--------------------------------------------------------------------------------------------------------------------------------------------+-------+

Answer 1

当您插入/删除/更新表的大量数据时，应重新分析您的表：

如果您的数据不是均匀分布的，请使用直方图。

https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html