mysql - mysql View 很慢

我的mysql视图确实在数以千计的数据中速度很慢，我们如何才能改善此功能？

在10000s的数据中获取此视图时，需要花费30秒钟以上的时间。我们如何修改此视图表？

SELECT
    i.jo_in_id,
    j.*,
    i.jo_in_week_number,
    i.jo_in_client_ref_no,
    i.cl_id AS jo_in_cl_id,
    c.cl_short_name,
    c.cl_business_name,
    m.me_first_name,
    m.me_last_name,
    m.me_mobile,
    sk.sk_name,
    sk.sk_ticketed,
    ti.ti_id,
    ta.ta_name,
    u.un_id,
    u.un_from,
    u.un_to,
    v.ve_name,
    mp.vmp_name,
    r.vr_name
FROM
    jo_2_no j2n,
    jo_in_numbers i,
    jobs j
LEFT JOIN venues_new v ON j.ve_id = v.ve_id
LEFT JOIN venues_meeting_place mp ON j.vmp_id = mp.vmp_id
LEFT JOIN venues_rooms r ON j.vr_id = r.vr_id
LEFT JOIN clients c ON j.cl_id = c.cl_id
LEFT JOIN members m ON j.me_id = m.me_id
LEFT JOIN skills sk ON j.sk_id = sk.sk_id
LEFT JOIN tasks ta ON j.ta_id = ta.ta_id
LEFT JOIN crew_tickets ti ON j.sk_id = ti.sk_id AND j.me_id = ti.me_id AND j.jo_time_off < ti.ti_expire
LEFT JOIN unavailability u ON j.me_id = u.me_id AND ((j.jo_time_on BETWEEN u.un_from AND u.un_to) OR  (j.jo_time_on BETWEEN u.un_from AND u.un_to))
WHERE
    j.jo_id = j2n.jo_id
    AND j2n.jo_in_numbers_id = i.jo_in_id

用户EXPLAIN SELECT之后是输出

最佳答案

在您的解释中，我看到您的联接表ti和u是通过表扫描读取的（类型：ALL）。这可能是您的表现最大的问题。

您应该确保已创建以下索引：

ALTER TABLE crew_tickets ADD KEY (sk_id, me_id, ti_expire);

ALTER TABLE unavailability ADD KEY (me_id, un_from, un_to);

这应该有助于到这些表的联接使用索引查找而不是表扫描。我认为它们也将作为覆盖索引进行访问。

另外，请不要使用过时的“逗号联接”。尤其不要混用两种样式。当您对逗号联接和JOIN运算符之间的优先顺序感到惊讶时，它会咬住您。请参见Can someone help explain why not using a SQL JOIN is bad practice and wrong?或Error on JOIN mysql中的示例。

这样编写联接：

FROM jo_2_no j2n
INNER JOIN jo_in_numbers i ON j2n.jo_in_numbers_id = i.jo_in_id
INNER JOIN jobs j ON j.jo_id = j2n.jo_id
LEFT JOIN venues_new v ON j.ve_id = v.ve_id
LEFT JOIN venues_meeting_place mp ON j.vmp_id = mp.vmp_id
LEFT JOIN venues_rooms r ON j.vr_id = r.vr_id
LEFT JOIN clients c ON j.cl_id = c.cl_id
LEFT JOIN members m ON j.me_id = m.me_id
LEFT JOIN skills sk ON j.sk_id = sk.sk_id
LEFT JOIN tasks ta ON j.ta_id = ta.ta_id
LEFT JOIN crew_tickets ti ON j.sk_id = ti.sk_id
  AND j.me_id = ti.me_id AND j.jo_time_off < ti.ti_expire
LEFT JOIN unavailability u ON j.me_id = u.me_id
  AND j.jo_time_on BETWEEN u.un_from AND u.un_to

我在u的加入条件中删除了冗余项。优化器可能会消除这种逻辑，但是为什么要使其如此努力呢？

关于mysql - mysql View 很慢，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41084873/