如何提高SQL查询多个连接表的性能
我如何提高性能这个查询,同时还获得所需的所有信息..如何提高SQL查询多个连接表的性能
SELECT
tr.id, tr.request_status, tr.note, tr.created_date,
c.name AS customer_name, c.mobile_phONe,
u.full_name AS created_by_name, tt.name AS ticket_type_name
FROM
ticket_request tr
LEFT JOIN
ticket_type tt ON tt.id = tr.ticket_type_id
LEFT JOIN
users u ON u.id = tr.created_by
LEFT JOIN
customer c ON c.id = tr.customer_id
WHERE
tr.is_deleted != 1
AND tr.user_id IN (SELECT u.id FROM users u WHERE u.status = '1')
GROUP BY
tr.id
ORDER BY
tr.created_date DESC
LIMIT 0,20
目前,该查询在7-10秒内运行。
-
ticket_request
表有大约10万行 -
customers
表有300K左右行 -
users
表ticket_type
没有那么多(约1K行)
下面的加速技术是免除LIMIT
第一个,只有在那之后,再做所有的JOINs
。
SELECT tr3.id, tr3.request_status, tr3.note, tr3.created_date,
c.name AS customer_name, c.mobile_phONe,
u2.full_name AS created_by_name,
tt.name AS ticket_type_name
FROM
(
SELECT tr1.id
FROM ticket_request tr1
JOIN users u1 ON u1.id = tr1.created_by
WHERE u1.status = '1'
AND tr1.is_deleted != 1
ORDER BY tr1.created_date DESC
LIMIT 0,20
) AS tr2
JOIN ticket_request AS tr3 ON tr3.id = tr2.id
JOIN user AS u2 ON u2.id = tr3.created_by
LEFT JOIN ticket_type tt ON tt.id = tr3.ticket_type_id
LEFT JOIN customer c ON c.id = tr3.customer_id
ORDER BY tr3.created_date
的JOINs
,所述一个中的 “衍生自” 表TR2,都在触摸只有20行之后;这是加速的很大一部分。
这可能是一样好:
SELECT d.id, d.request_status, d.note, d.created_date,
c.name AS customer_name, c.mobile_phONe, d.created_by_name,
tt.name AS ticket_type_name
FROM
(
SELECT tr.id AS tr_id, tr.request_status, tr.note, tr.created_date,
tr.ticket_type_id, tr.customer_id
u.full_name AS created_by_name
FROM ticket_request tr
JOIN users u ON u.id = tr.created_by
WHERE u.status = '1'
AND tr.is_deleted != 1
ORDER BY tr.created_date DESC
LIMIT 0,20
) AS d
LEFT JOIN ticket_type tt ON tt.id = d.ticket_type_id
LEFT JOIN customer c ON c.id = d.customer_id
ORDER BY d.created_date
我还没有尝试过,但我认为如果你把LIMIT置于顶端,那么在LIMIT上限查询中的某些满意结果不能满足较低的条件,那么整个查询的结果可能小于20行对。 –
@VinhDatHa - 请说明“put on top”和“upper”是什么意思。我想到了“SELECTs”和“outer”和“inner”或“derived”。 (“派生”)是这种“子查询”的技术术语。 –
好吧,我想我现在明白了,谢谢 –
我假设你正在使用MySQL。如果不是,这个答案可以稍微修改以适应另一个数据库,但这个概念应该保持不变。您ticket_request
表之间
ALTER TABLE ticket_type ADD INDEX (id);
ALTER TABLE users ADD INDEX (id);
ALTER TABLE customer ADD INDEX (id); -- important
要解释为什么指数会有所帮助,考虑第一LEFT JOIN
:您可以添加索引,其涉及您的左边的右边与ticket_request
列加入所有的ID列ticket_type
表。如果没有索引,对于ticket_request
中的每条记录,数据库将不得不扫描整个ticket_type
表以查找与连接条件相匹配的记录。从性能角度来看,这是昂贵的。但是对于索引,数据库可以更快地完成此操作,因为它“知道”匹配记录的准确位置(或几乎完全匹配)。
虽然您提到只有customer
表非常大,但您仍可以将索引添加到其他表中。未来,他们可能会变得更大。涉及customer
的加入很可能是您查询中的瓶颈。
是的,我正在使用MySQL。我假设主要是默认索引,对吧? 而且我也索引了所有需要的外键。 –
@VinhDatHa我为你添加了MySQL标签。尽管如此,如果你首先做到这一点,情况会更有意义。 –
这里的优化最大的机会是LIMIT 0,20
是没有意义的,应予删除。
-
create index ticket_request_ix_is_deleted_created_date on ticket_request (is_deleted,created_date)
and changetr.is_deleted != 1
totr.is_deleted = 0
。或者
create index ticket_request_ix_created_date on ticket_request (created_date)
SELECT
tr.id, tr.request_status, tr.note, tr.created_date,
c.name AS customer_name, c.mobile_phONe,
u.full_name AS created_by_name, tt.name AS ticket_type_name
FROM
ticket_request tr
LEFT JOIN
ticket_type tt ON tt.id = tr.ticket_type_id and tr.is_deleted != 1
LEFT JOIN
users u ON u.id = tr.created_by
JOIN
users u1 ON u1.id = tr.user_id and u1.status = '1'
LEFT JOIN
customer c ON c.id = tr.customer_id
GROUP BY
tr.id
ORDER BY
tr.created_date DESC
LIMIT 0,20
尝试这一点,将与更好的性能工作,调整为按您的要求
这会给出错误的结果。 –
比索引其他,在应用层上可以使用的Memcached(如果你使用PHP)喜欢的东西。这也会给你很好的表现。
添加索引到所有的连接列。你已经做过了吗? –
我删除了不兼容的数据库标记。为您实际使用的数据库添加标签。 –
@TimBiegeleisen是的,我已经完成 –