下面这条语句执行了15秒多,大家帮忙看下能不能优化下,还是我数据库建的不合理。非常感谢!!!SELECT DISTINCT b.project subproject, b.id subid, d.staffname dpm, a.tname tname, c.staffname tleader, g.location tlocation, l.project project, m.staffname tpl, a.id tid
FROM port_team a
LEFT JOIN p_project b ON a.subproject = b.id
LEFT JOIN p_staff c ON a.tleader = c.id
LEFT JOIN p_staff d ON b.pmanagera = d.id
LEFT JOIN p_team_member e ON a.id = e.teama AND e.ctype = 'staff'
LEFT JOIN p_location g ON a.tlocation = g.id
LEFT JOIN p_team_member h ON h.sid = a.tleader
LEFT JOIN p_team k ON h.teama = k.id
LEFT JOIN p_project i ON h.subprojecta = i.id
LEFT JOIN p_project j ON h.projecta = j.id
LEFT JOIN p_team ea ON ea.id = e.teama
LEFT JOIN p_project eb ON eb.id = e.subprojecta
LEFT JOIN p_project ec ON ec.id = e.projecta
LEFT JOIN p_project l ON b.parentid = l.id
LEFT JOIN p_staff m ON l.pmanagera = m.id
WHERE 1 =1
ORDER BY a.tname ASC LIMIT 0 , 30
FROM port_team a
LEFT JOIN p_project b ON a.subproject = b.id
LEFT JOIN p_staff c ON a.tleader = c.id
LEFT JOIN p_staff d ON b.pmanagera = d.id
LEFT JOIN p_team_member e ON a.id = e.teama AND e.ctype = 'staff'
LEFT JOIN p_location g ON a.tlocation = g.id
LEFT JOIN p_team_member h ON h.sid = a.tleader
LEFT JOIN p_team k ON h.teama = k.id
LEFT JOIN p_project i ON h.subprojecta = i.id
LEFT JOIN p_project j ON h.projecta = j.id
LEFT JOIN p_team ea ON ea.id = e.teama
LEFT JOIN p_project eb ON eb.id = e.subprojecta
LEFT JOIN p_project ec ON ec.id = e.projecta
LEFT JOIN p_project l ON b.parentid = l.id
LEFT JOIN p_staff m ON l.pmanagera = m.id
WHERE 1 =1
ORDER BY a.tname ASC LIMIT 0 , 30
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ALL NULL NULL NULL NULL 7 Using temporary; Using filesort
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.subproject 1
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 test.a.tleader 1
1 SIMPLE d eq_ref PRIMARY PRIMARY 4 test.b.pmanagera 1
1 SIMPLE e ALL NULL NULL NULL NULL 34
1 SIMPLE g eq_ref PRIMARY PRIMARY 2 test.a.tlocation 1
1 SIMPLE h ALL NULL NULL NULL NULL 34
1 SIMPLE k eq_ref PRIMARY PRIMARY 4 test.h.teama 1 Using index
1 SIMPLE i eq_ref PRIMARY PRIMARY 4 test.h.subprojecta 1 Using index
1 SIMPLE j eq_ref PRIMARY PRIMARY 4 test.h.projecta 1 Using index
1 SIMPLE ea eq_ref PRIMARY PRIMARY 4 test.e.teama 1 Using index
1 SIMPLE eb eq_ref PRIMARY PRIMARY 4 test.e.subprojecta 1 Using index
1 SIMPLE ec eq_ref PRIMARY PRIMARY 4 test.e.projecta 1 Using index
1 SIMPLE l eq_ref PRIMARY PRIMARY 4 test.b.parentid 1
1 SIMPLE m eq_ref PRIMARY PRIMARY 4 test.l.pmanagera 1
p_team_member 的 teama 建索引
LEFT JOIN p_project b ON a.subproject = b.id
LEFT JOIN p_project eb ON eb.id = e.subprojecta
LEFT JOIN p_project ec ON ec.id = e.projecta
同一个table,你不停的连接,速度肯定慢,你可以LEFT JOIN p_project b ON 后面加所有的匹配条件。
比如 LEFT JOIN p_project b ON a.subproject = b.id
LEFT JOIN p_staff c ON a.tleader = c.id
LEFT JOIN p_staff d ON b.pmanagera = d.id
可以优化成:
LEFT JOIN p_project b ON a.subproject = b.id
LEFT JOIN p_staff c ON a.tleader = c.id and b.pmanagera = c.id这样的sql可以优化很多的啊。