主页 / dba / 问题 / 180448
Accepted
apex
Asked: 2017-07-22 05:28:41 +0800 CST

如何添加索引提高mysql语句速度

  • 772

如何提高这个 mysql 语句的速度？花了 1.10 秒，但想知道是否可以让它更快。

SELECT 
        res_rooms.room_id AS room,
        res_rooms.type AS room_type,
        res_rooms.status AS room_status,
        IFNULL(utilizations.user_id, - 1) AS user_id,
        CONCAT_WS(0, system_users.surname, camps.s_surname) AS surname,
        CONCAT_WS(0, system_users.name, camps.s_first) AS name,
        IFNULL(utilizations.start_date, NULL) AS start_date,
        IFNULL(utilizations.end_date, NULL) AS end_date,
        utilizations.keys_issued AS keys_issued,
        utilizations.resource_type AS resource_type,
        utilizations.staff_id AS staff_id,
        CONCAT_WS(0, students.handled_by, camps.handled_by) AS handled_by,
        CONCAT_WS(0,
                students.date_of_birth,
                camps.date_of_birth) AS dob,
        CONCAT_WS(0, students.gender, camps.sex) AS gender,
        CONCAT_WS(0, students.status, camps.status) AS status,
        countries.common_name AS citizenship,
        CONCAT_WS(0, students.applying_for, camps.applying) AS applying_for,
        IFNULL(fee_schedule.ResiLife_type, 'N/A') AS residence_type,
        IFNULL(fee_schedule.Resi_Amount, 'N/A') AS reslife_amount
    FROM
        res_rooms
            LEFT JOIN
        utilizations ON utilizations.resource_id = res_rooms.room_id
            LEFT JOIN
        system_users ON system_users.user_id = utilizations.user_id
            LEFT JOIN
        camps ON camps.camp_id = utilizations.user_id
            LEFT JOIN
        students ON students.user_id = utilizations.user_id
            LEFT JOIN
        countries ON (students.citizenship = countries.code
            || camps.citizenship = countries.code)
            LEFT JOIN
        fee_schedule ON utilizations.user_id = fee_schedule.Stud_id
    WHERE
        (students.status = 'B'
            OR students.status = 'VC')
            AND utilizations.start_date <= CURDATE()
    ORDER BY room_id , start_date DESC

使用时Explain：

在此处输入图像描述

查询统计：

在此处输入图像描述

mysql time

2 个回答

  1. Best Answer

    NULL您应该为在 possible_keysof中的每个表添加一个索引EXPLAIN。用于索引的列应该与用于JOINs 的列相同。在表的情况下，您必须在相应的表countries中为字段添加索引，并且应该在每个字段上建立索引。codestudentscampscitizenship

    共同的规则是：

    当您使用ON条件连接两个表时，每个表都应该有用于ON.

    SELECT * 
  FROM a
  JOIN b ON a.foo = b.bar;

    这里的表a应该有索引 (foo) 而表b应该有索引 (bar)

    当您按WHERE子句过滤掉行时，条件中提到的列WHERE应该被索引：

    SELECT * 
  FROM a
 WHERE a.foo = 1234;

    这里的表a应该有索引（foo）。

    引用同一张表中的多个列的复杂条件需要多列索引：

    SELECT * 
  FROM a
 WHERE a.foo = 1234
   AND a.bar BETWEEN '2017-07-20' AND '2017-07-21';

    这里的表a应该有一个多列索引 (foo, bar) 或 (bar,foo)，甚至两者都有。这取决于存储在表中的某些数据，并且应该执行一些性能测量。即使对于不同的查询，不同的索引可以成为最佳索引，并且使用 (foo,bar) 可以更快地处理一天范围内的范围，而使用 (bar,foo) 可以更快地处理一个月范围内的范围。

    • 2

  2. 连接到utilizations并且students实际上是内部连接，因为 WHERE 条件将删除外部连接创建的 NULL。

    加入countries真的很糟糕，因为它使用ORed 条件并同时加入三个表（优化器对此感到窒息）。

    尽可能切换到内部连接（MySQL 的优化器过去常常无法识别外部连接实际上是内部连接）。

    并尝试将ORed 连接替换为UNION：

    SELECT 
    res_rooms.room_id AS room,
    res_rooms.type AS room_type,
    res_rooms.status AS room_status,
    IFNULL(utilizations.user_id, - 1) AS user_id,
    CONCAT_WS(0, system_users.surname, camps.s_surname) AS surname,
    CONCAT_WS(0, system_users.name, camps.s_first) AS name,
    IFNULL(utilizations.start_date, NULL) AS start_date,
    IFNULL(utilizations.end_date, NULL) AS end_date,
    utilizations.keys_issued AS keys_issued,
    utilizations.resource_type AS resource_type,
    utilizations.staff_id AS staff_id,
    CONCAT_WS(0, students.handled_by, camps.handled_by) AS handled_by,
    CONCAT_WS(0,
            students.date_of_birth,
            camps.date_of_birth) AS dob,
    CONCAT_WS(0, students.gender, camps.sex) AS gender,
    CONCAT_WS(0, students.status, camps.status) AS status,
    countries.common_name AS citizenship,
    CONCAT_WS(0, students.applying_for, camps.applying) AS applying_for,
    IFNULL(fee_schedule.ResiLife_type, 'N/A') AS residence_type,
    IFNULL(fee_schedule.Resi_Amount, 'N/A') AS reslife_amount
FROM
    res_rooms
        JOIN
    utilizations ON utilizations.resource_id = res_rooms.room_id
        LEFT JOIN
    system_users ON system_users.user_id = utilizations.user_id
        LEFT JOIN
    camps ON camps.camp_id = utilizations.user_id
        JOIN
    students ON students.user_id = utilizations.user_id
        LEFT JOIN
    countries ON students.citizenship = countries.code
        LEFT JOIN
    fee_schedule ON utilizations.user_id = fee_schedule.Stud_id
WHERE
    (students.status = 'B'
        OR students.status = 'VC')
        AND utilizations.start_date <= CURDATE()

UNION

SELECT 
    res_rooms.room_id AS room,
    res_rooms.type AS room_type,
    res_rooms.status AS room_status,
    IFNULL(utilizations.user_id, - 1) AS user_id,
    CONCAT_WS(0, system_users.surname, camps.s_surname) AS surname,
    CONCAT_WS(0, system_users.name, camps.s_first) AS name,
    IFNULL(utilizations.start_date, NULL) AS start_date,
    IFNULL(utilizations.end_date, NULL) AS end_date,
    utilizations.keys_issued AS keys_issued,
    utilizations.resource_type AS resource_type,
    utilizations.staff_id AS staff_id,
    CONCAT_WS(0, students.handled_by, camps.handled_by) AS handled_by,
    CONCAT_WS(0,
            students.date_of_birth,
            camps.date_of_birth) AS dob,
    CONCAT_WS(0, students.gender, camps.sex) AS gender,
    CONCAT_WS(0, students.status, camps.status) AS status,
    countries.common_name AS citizenship,
    CONCAT_WS(0, students.applying_for, camps.applying) AS applying_for,
    IFNULL(fee_schedule.ResiLife_type, 'N/A') AS residence_type,
    IFNULL(fee_schedule.Resi_Amount, 'N/A') AS reslife_amount
FROM
    res_rooms
        JOIN
    utilizations ON utilizations.resource_id = res_rooms.room_id
        LEFT JOIN
    system_users ON system_users.user_id = utilizations.user_id
        LEFT JOIN
    camps ON camps.camp_id = utilizations.user_id
        JOIN
    students ON students.user_id = utilizations.user_id
        LEFT JOIN
    countries ON camps.citizenship = countries.code
        LEFT JOIN
    fee_schedule ON utilizations.user_id = fee_schedule.Stud_id
WHERE
    (students.status = 'B'
        OR students.status = 'VC')
        AND utilizations.start_date <= CURDATE()
ORDER BY room_id , start_date DESC

    或者最好用两个连接替换它countriesplus COALESCE：

    SELECT 
    res_rooms.room_id AS room,
    res_rooms.type AS room_type,
    res_rooms.status AS room_status,
    IFNULL(utilizations.user_id, - 1) AS user_id,
    CONCAT_WS(0, system_users.surname, camps.s_surname) AS surname,
    CONCAT_WS(0, system_users.name, camps.s_first) AS name,
    IFNULL(utilizations.start_date, NULL) AS start_date,
    IFNULL(utilizations.end_date, NULL) AS end_date,
    utilizations.keys_issued AS keys_issued,
    utilizations.resource_type AS resource_type,
    utilizations.staff_id AS staff_id,
    CONCAT_WS(0, students.handled_by, camps.handled_by) AS handled_by,
    CONCAT_WS(0,
            students.date_of_birth,
            camps.date_of_birth) AS dob,
    CONCAT_WS(0, students.gender, camps.sex) AS gender,
    CONCAT_WS(0, students.status, camps.status) AS status,
    COALESCE(c1.common_name, c2.common_name) AS citizenship,
    CONCAT_WS(0, students.applying_for, camps.applying) AS applying_for,
    IFNULL(fee_schedule.ResiLife_type, 'N/A') AS residence_type,
    IFNULL(fee_schedule.Resi_Amount, 'N/A') AS reslife_amount
FROM
    res_rooms
        JOIN
    utilizations ON utilizations.resource_id = res_rooms.room_id
        LEFT JOIN
    system_users ON system_users.user_id = utilizations.user_id
        LEFT JOIN
    camps ON camps.camp_id = utilizations.user_id
        JOIN
    students ON students.user_id = utilizations.user_id
        LEFT JOIN
    countries AS c1 ON students.citizenship = c1.code
        LEFT JOIN
    countries AS c2 ON camps.citizenship = c2.code
        LEFT JOIN
    fee_schedule ON utilizations.user_id = fee_schedule.Stud_id
WHERE
    (students.status = 'B'
        OR students.status = 'VC')
        AND utilizations.start_date <= CURDATE()
ORDER BY room_id , start_date DESC
    • 1

相关问题