MYSQL 慢 ORDER BY

Question

我在查询中添加 ORDER BY 时遇到问题。没有 ORDER BY 查询大约需要 26ms，一旦我添加 ORDER BY，它大约需要 20s。

我尝试了几种不同的方法，但似乎可以减少时间。

尝试

FORCE INDEX (PRIMARY)

尝试包装

SELECT * FROM (...)

大家还有其他想法吗？

查询:

    SELECT 
    orders.id AS order_number, 
    orders.created AS order_created, 
    CONCAT_WS(' ', clients.name, office.name) AS client_office, 
    order_item.code AS product_code, 
    order_item.name AS product_name, 
    order_item.description AS product_description, 
    order_item.sale_price, 
    jobs.rating AS job_rating, 
    job_role.start_booking AS book_start, 
    job_role.end_booking AS book_end, 
    CONCAT_WS(' ', admin_staffs.first_name, admin_staffs.last_name) AS soul, 
    admin_roles.name AS role, 
    services.name AS service, 
    COUNT(job_item.id) AS total_assets, 
    COALESCE(orders.project_name, CONCAT_WS(' ', orders.location_unit_no, orders.location_street_number, orders.location_street_number, orders.location_street_name
                )
            ) AS order_title
FROM jobs
LEFT JOIN job_item ON jobs.id = job_item.job_id
LEFT JOIN job_role ON jobs.id = job_role.job_id
LEFT JOIN admin_roles ON admin_roles.id = job_role.role_id
LEFT JOIN services ON services.id = jobs.service_id
LEFT JOIN admin_staffs ON admin_staffs.id = job_role.staff_id
LEFT JOIN order_item ON jobs.item_id = order_item.id
LEFT JOIN orders ON orders.id = order_item.order_id
LEFT JOIN clients ON orders.client_id = clients.id
LEFT JOIN office ON orders.office_id = office.id
LEFT JOIN client_users ON orders.user_id = client_users.id
GROUP BY jobs.id
ORDER BY jobs.order_id DESC
LIMIT 50

解释:

+----+-------------+--------------+--------+----------------+-----------+---------+--------------------------------------+-------+---------------------------------+| id | select_type | table        | type   | possible_keys  | key       | key_len | ref                                  | rows  | Extra                           |+----+-------------+--------------+--------+----------------+-----------+---------+--------------------------------------+-------+---------------------------------+| 1  |    SIMPLE   | jobs         | ALL    | PRIMARY        | NULL      | NULL    | NULL                                 | 49555 | Using temporary; Using filesort |        | 1  |    SIMPLE   | job_item     | ref    | job_id         | job_id    | 5       | Wx3392vf_UCO_app.jobs.id             | 8     | Using where; Using index        || 1  |    SIMPLE   | job_role     | ref    | job_id         | job_id    | 4       | Wx3392vf_UCO_app.jobs.id             | 1     | Using where                     || 1  |    SIMPLE   | admin_roles  | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.job_role.role_id    | 1     | Using where                     || 1  |    SIMPLE   | services     | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.jobs.service_id     | 1     | Using where                     || 1  |    SIMPLE   | admin_staffs | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.job_role.staff_id   | 1     | NULL                            |    | 1  |    SIMPLE   | order_item   | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.jobs.item_id        | 1     | Using where                     || 1  |    SIMPLE   | orders       | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.order_item.order_id | 1     | Using where                     || 1  |    SIMPLE   | clients      | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.orders.client_id    | 1     | Using where                     || 1  |    SIMPLE   | office       | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.orders.office_id    | 1     | Using where                     || 1  |    SIMPLE   | client_users | eq_ref | PRIMARY        | PRIMARY   | 4       | Wx3392vf_UCO_app.orders.user_id      | 1     | Using index                     |+----+-------------+--------------+--------+----------------+-----------+---------+--------------------------------------+-------+---------------------------------+

Answer 1

尝试从这个查询开始:

SELECT * FROM jobs ORDER BY jobs.order_id DESC LIMIT 50

然后通过添加更多连接来增加其复杂性:

SELECT * FROM jobs 
LEFT JOIN job_item ON jobs.id = job_item.job_id
ORDER BY jobs.order_id DESC LIMIT 50

等等

对每一步进行分析，您将能够找到瓶颈。可能是某个连接表中的某个 TEXT 字段减慢了查询速度或其他原因。