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

Question

首先，是这么一段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没有使用外连接，比如我某条数据是没有推送人的，那这条记录都显示不出来了。


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

Answer 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,
(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上最好做一个索引,可以试试看这样子查询用的时间