mysql - MySQL 查询非常慢(Left-JOIN、GROUP BY 等？)

Question

我在提交给 MySQL 服务器的后续查询中遇到了麻烦。 (COUNT(*) < 20k) 表需要 25 秒 - 仅精选有 600k 行。然而，索引是在它应该创建的地方创建的(也就是说:针对 ON 子句中的相关列)。我尝试删除 GROUP BY，这稍微改善了情况。但查询仍然给出了缓慢响应的一般规则。我发表这篇文章是因为我无法在 stackoverflow 中找到各种案例的解决方案。有什么建议吗？

SELECT
    doctor.id as doctor_id,
    doctor.uuid as doctor_uuid,
    doctor.firstname as doctor_firstname,
    doctor.lastname as doctor_lastname,
    doctor.cloudRdvMask as doctor_cloudRdvMask,

    GROUP_CONCAT(recommendation.id SEPARATOR ' ') as recommendation_ids,
    GROUP_CONCAT(recommendation.uuid SEPARATOR ' ') as recommendation_uuids,
    GROUP_CONCAT(recommendation.disponibility SEPARATOR ' ') as recommendation_disponibilities,
    GROUP_CONCAT(recommendation.user_id SEPARATOR ' ') as recommendation_user_ids,
    GROUP_CONCAT(recommendation.user_uuid SEPARATOR ' ') as recommendation_user_uuids,

    location.id as location_id,
    location.uuid as location_uuid,
    location.lat as location_lat,
    location.lng as location_lng,

    profession.id as profession_id,
    profession.uuid as profession_uuid,
    profession.name as profession_name
FROM featured as doctor
LEFT JOIN location as location
    ON doctor.location_id = location.id
LEFT JOIN profession as profession
    ON doctor.profession_id = profession.id
LEFT JOIN 
    (
        SELECT 
            featured.id as id, 
            featured.uuid as uuid, 
            featured.doctor_id as doctor_id, 
            featured.disponibility as disponibility,

            user.id as user_id, 
            user.uuid as user_uuid
        FROM featured as featured
            LEFT JOIN user as user
                ON featured.user_id = user.id
            WHERE discr = 'recommendation'
        ) as recommendation
        ON recommendation.doctor_id = doctor.id
WHERE
    doctor.discr = 'doctor'
    AND
    doctor.state = 'Publié'
GROUP BY doctor.uuid

这是解释结果:

id | select_type | table      | partitions | type   | possible_keys              | key                  | key_len | ref                           | rows   | filtered | Extra       |

1 | SIMPLE      | doctor     | NULL       | ref    | discr,state                | discr                | 767     | const                         | 194653 |    50.00 | Using where |
1 | SIMPLE      | location   | NULL       | eq_ref | PRIMARY                    | PRIMARY              | 4       | doctoome.doctor.location_id   |      1 |   100.00 | NULL        |
1 | SIMPLE      | profession | NULL       | eq_ref | PRIMARY                    | PRIMARY              | 4       | doctoome.doctor.profession_id |      1 |   100.00 | NULL        |
1 | SIMPLE      | featured   | NULL       | ref    | IDX_3C1359D487F4FB17,discr | IDX_3C1359D487F4FB17 | 5       | doctoome.doctor.id            |    196 |   100.00 | Using where |
1 | SIMPLE      | user       | NULL       | eq_ref | PRIMARY                    | PRIMARY              | 4       | doctoome.featured.user_id     |      1 |   100.00 | Using index |

编辑 这个链接对我有帮助，现在是 8。 https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/ 。但我仍然发现它很慢，我只是让它以防万一有人知道还有什么可以改进。谢谢

Answer 1

我认为删除子查询以及更多索引可能会有所帮助:

SELECT . . . -- you need to fix the `GROUP_CONCAT()` column references
FROM featured doctor LEFT JOIN
     location location
     ON doctor.location_id = location.id LEFT JOIN
     profession profession
     ON doctor.profession_id = profession.id LEFT JOIN 
     featured featured
     ON featured.doctor_id = doctor.doctor_id LEFT JOIN
     user user
     ON featured.user_id = user.id
WHERE doctor.discr = 'doctor' AND
      doctor.state = 'Publié' AND
      featured.discr = 'recommendation'
GROUP BY doctor.uuid;

然后您需要在 featured(discr, state, doctor_id, location_id, professional_id) 和 featured(doctor_id, discr, user_id) 上建立索引。