mysql - 具有类似子选择的 SQL 查询的性能问题

Question

我有一个可以运行的复杂 SQL 查询，但它的性能不太理想（执行大约需要 3 秒）。我想我优化了我能想到的一切，但也许还有更多我无法理解的东西。

这里是：

SELECT DISTINCT doctors.doc_id, doctors.doc_title, doctors.doc_last, 
    doctors.doc_first, doctors.doc_email, doctors.doc_birthday,
    doctors.doc_mobile, doctors.doc_occasional, doctors.doc_fiscal_code, 
    doctors.doc_register_number, doctors.doc_register_province, 
    doctors.doc_agreement, doctors.doc_notes, doctors.doc_timestamp,
    doctors.doc_deleted, specializations.spe_id, specializations.spe_name,
    activities.act_id, activities.act_name,
    users.use_id, users.use_last, users.use_first, users.use_active,
    (SELECT COUNT(*)
    FROM congress
    INNER JOIN participants ON participants.par_congress = congress.cng_id
    WHERE par_doctor = doc_id
    AND congress.cng_from >= '2018-01-01'
    AND congress.cng_from <= '2018-07-02')
    AS cng_count,
    (SELECT COUNT(*)
    FROM visits
    INNER JOIN reports ON reports.rep_id = visits.vis_report
    INNER JOIN locations ON locations.loc_id = visits.vis_location
    WHERE visits.vis_doctor = doctors.doc_id 
    AND locations.loc_structure LIKE '%' 
    AND reports.rep_dated >= '2018-01-01' 
    AND reports.rep_dated <= '2018-07-02')
    AS vis_count_all,
    (SELECT COUNT(*)
    FROM visits
    INNER JOIN reports ON reports.rep_id = visits.vis_report
    INNER JOIN locations ON locations.loc_id = visits.vis_location 
    WHERE visits.vis_doctor = doctors.doc_id 
    AND reports.rep_user = users.use_id 
    AND locations.loc_structure LIKE '%' 
    AND reports.rep_dated >= '2018-01-01' 
    AND reports.rep_dated <= '2018-07-02')
    AS vis_count_user,
    (SELECT COUNT(*)
    FROM locations
    WHERE locations.loc_doctor = doctors.doc_id )
    AS loc_count
    FROM doctors
    LEFT JOIN locations ON locations.loc_doctor = doctors.doc_id
    INNER JOIN specializations ON specializations.spe_id = doctors.doc_specialization
    INNER JOIN activities ON activities.act_id = doctors.doc_activity
    INNER JOIN users ON users.use_id = doctors.doc_user 
    WHERE doctors.doc_last IS NOT NULL 
    AND doctors.doc_id LIKE '%' 
    AND (locations.loc_province IS NULL OR locations.loc_province LIKE '%') 
    AND (locations.loc_structure IS NULL OR locations.loc_structure LIKE '%') 
    AND DATE(doctors.doc_timestamp) <= '2018-07-02' 
    AND doctors.doc_occasional LIKE '%' 
    AND doctors.doc_deleted LIKE '0' 
    AND doctors.doc_agreement LIKE '%' 
    AND doctors.doc_active 
    AND users.use_id LIKE '%' 
    GROUP BY doctors.doc_id 
    HAVING vis_count_user <> - 1 
    ORDER BY doctors.doc_last, doctors.doc_first, doctors.doc_id

真正的瓶颈在于vis_count_all和vis_count_user子选择（它们仅在附加AND reports.rep_user = users.use_id语句上有所不同）：删除它们可以加快查询速度

由于它们仅在一个语句中有所不同，因此我不知道是否可以重用其中一个以更简单的方式获得另一个值

无论如何，我创建了我能想到的所有键，这是 EXPLAIN 结果

请问有什么改进的建议吗？谢谢

标签： mysqlperformance

Answer 1

这个“覆盖”索引可能会帮助一些人：

visits:  (vis_doctor, vis_report, vis_location)

删除无用的子句（例如包含LIKE '%'. 与此相关，

          AND  (locations.loc_province IS NULL
              OR  locations.loc_province LIKE '%')

可能优化得很差。计划A：删除它。计划 B：避免NULL用于任何用途。

两者兼而有之JOIN，并GROUP BY引发了危险信号。添加DISTINCT只是简单的“错误”。请决定您是否需要GROUP BY或DISTINCT。我认为你也不需要。当然不要两者都用。

什么？？

 ( SELECT COUNT(*) ... ) AS vis_count_user
 HAVING vis_count_user <> -1

HAVING除了减慢查询速度外，它什么也不做。

AND  DATE(doctors.doc_timestamp) <= '2018-07-02'

在函数中隐藏列可以防止使用索引。顺便说一句，索引是什么？请提供SHOW CREATE TABLE。这具有相同的语义：

AND  doctors.doc_timestamp < '2018-07-02' + INTERVAL 1 DAY

你真的打算有 6 个月，再加上 1（或 2？）天：

AND reports.rep_dated >= '2018-01-01' 
AND reports.rep_dated <= '2018-07-02'

考虑

AND reports.rep_dated >= '2018-01-01' 
AND reports.rep_dated  < '2018-01-01' + INTERVAL 6 MONTH