首先，是这么一段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没有使用外连接，比如我某条数据是没有推送人的，那这条记录都显示不出来了。
那么，针对目前的这种情况，该如何优化？
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上最好做一个索引,可以试试看这样子查询用的时间
GROUP BY d.id
LIMIT 0,100 你把这两个去掉看看有多少条数据
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
这样试一下，我也不大了解
EXPLAIN your sql ,看下是哪个关联导致你的 速度慢，优化那个关系就可以了
学习了，这个方法确实有用，以前我的MYSQL有个语句用了500秒。。。
主表数据多得话就优化，EXPLAIN sql 查看具体慢在哪里
从表尽量少关联一点（根据自己代码来改造），对你的性能都会大有裨益。