2 vitoyi VitoYi 于 2018.01.11 15:09 提问

mysql查询性能优化(数据量非常小,但查询很慢,初步确认是因为使用了左外连接)

首先,是这么一段sql:


SQL 1:

SELECT
    d.id,
    d.project_code,
    d.push_type,
    d.push_name,
    d.push_to,
    d.push_from,
    d.push_way,
    tr.isread,
    d.push_content,
    d.push_attachment,
    d.add_time,
    d.push_status,
    d.push_reviewer,
    push_time,
    d.reviewer_time,
    d.push_class,
    d.rel_object,
    p.short_name AS project_name,
    u1. NAME AS push_to_name,
    u2. NAME AS push_from_name,
    u3. NAME AS push_reviewer_name
FROM tb_push_data d
    LEFT JOIN tb_project p ON p.short_code = d.project_code
    LEFT JOIN tb_user u1 ON u1. CODE = d.push_to
    LEFT JOIN tb_user u2 ON u2. CODE = d.push_from
    LEFT JOIN tb_user u3 ON u3. CODE = d.push_reviewer
    LEFT JOIN tb_push_log tr ON tr.pushdata_id = d.id
WHERE 1 = 1
    AND d.add_time BETWEEN '2017-01-01' and '2018-01-11'
GROUP BY d.id
LIMIT 0,100

受影响的行: 0
时间: 24.933s



可以看到,仅仅是100条数据,查询时间竟然高达24秒,于是我尝试将左外连接改为交叉连接:



SQL 2:

SELECT
    d.id,
    d.project_code,
    d.push_type,
    d.push_name,
    d.push_to,
    d.push_from,
    d.push_way,
    tr.isread,
    d.push_content,
    d.push_attachment,
    d.add_time,
    d.push_status,
    d.push_reviewer,
    push_time,
    d.reviewer_time,
    d.push_class,
    d.rel_object,
    p.short_name AS project_name,
    u1. NAME AS push_to_name,
    u2. NAME AS push_from_name,
    u3. NAME AS push_reviewer_name
FROM 
    tb_push_data d,
    tb_project p,
    tb_user u1,
    tb_user u2,
    tb_user u3,
    tb_push_log tr
WHERE 1 = 1
    and p.short_code = d.project_code
    and u1. CODE = d.push_to
    and u2. CODE = d.push_from
    and u3. CODE = d.push_reviewer
    and tr.pushdata_id = d.id
    AND d.add_time BETWEEN '2017-01-01' and '2018-01-11'
GROUP BY d.id
LIMIT 0,100

受影响的行: 0
时间: 0.189s


这一下查询时间终于算是正常了,看来很多人说的数据库查询不用外键不用join还是有道理的。
但是这又出现了一个问题,因为SQL 2没有使用外连接,比如我某条数据是没有推送人的,那这条记录都显示不出来了。


那么,针对目前的这种情况,该如何优化?

6个回答

a1491918446
a1491918446   2018.01.11 15:48
已采纳

SELECT
d.id,
d.project_code,
d.push_type,
d.push_name,
d.push_to,
d.push_from,
d.push_way,
tr.isread,
d.push_content,
d.push_attachment,
d.add_time,
d.push_status,
d.push_reviewer,
push_time,
d.reviewer_time,
d.push_class,
d.rel_object,
p.short_name AS project_name,
(select u1. NAME from tb_user u1 where u1. CODE = d.push_to) push_to_name,
(select u2. NAME from tb_user u2 where u2. CODE = d.push_from) push_from_name,
(select u3. NAME from tb_user u3 where u3. CODE = d.push_reviewer) push_reviewer_name
FROM
tb_push_data d,
tb_project p,
tb_push_log tr
WHERE 1 = 1
and p.short_code = d.project_code
and tr.pushdata_id = d.id
AND d.add_time BETWEEN '2017-01-01' and '2018-01-11'
GROUP BY d.id
LIMIT 0,100

尝试一下这样子呢?tb_user表的code上最好做一个索引,可以试试看这样子查询用的时间

airfling
airfling   2018.01.11 15:51

EXPLAIN your sql ,看下是哪个关联导致你的 速度慢,优化那个关系就可以了

tony_heipi
tony_heipi   2018.01.11 15:22

GROUP BY d.id
LIMIT 0,100 你把这两个去掉看看有多少条数据

VitoYi
VitoYi 去掉了,时间: 23.208s,1453条记录
5 个月之前 回复
boy6941003
boy6941003   2018.01.11 15:25

select x.* ,
u1. NAME AS push_to_name,
u2. NAME AS push_from_name,
u3. NAME AS push_reviewer_name
from (SELECT
d.id,
d.project_code,
d.push_type,
d.push_name,
d.push_to,
d.push_from,
d.push_way,
tr.isread,
d.push_content,
d.push_attachment,
d.add_time,
d.push_status,
d.push_reviewer,
push_time,
d.reviewer_time,
d.push_class,
d.rel_object,
p.short_name AS project_name
FROM tb_push_data d
LEFT JOIN tb_project p ON p.short_code = d.project_code
LEFT JOIN tb_push_log tr ON tr.pushdata_id = d.id
WHERE 1 = 1
AND d.add_time BETWEEN '2017-01-01' and '2018-01-11'
GROUP BY d.id
LIMIT 0,100 ) x
LEFT JOIN tb_user u1 ON u1. CODE = x.push_to
LEFT JOIN tb_user u2 ON u2. CODE = x.push_from
LEFT JOIN tb_user u3 ON u3. CODE = x.push_reviewer

这样试一下,我也不大了解

m0_38006660
m0_38006660   2018.01.11 15:59

学习了,这个方法确实有用,以前我的MYSQL有个语句用了500秒。。。

huang812561
huang812561   2018.01.11 16:16

主表数据多得话就优化,EXPLAIN sql 查看具体慢在哪里
从表尽量少关联一点(根据自己代码来改造),对你的性能都会大有裨益。

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!