项目中我写了一条sql语句
SELECT
s.student_number AS studentNumber,
s. NAME,
s.sex,
class. NAME AS studentClass,
m. NAME AS professiona,
d. NAME AS department,
c. NAME AS college
FROM
student_info AS s,
major AS m,
department AS d,
college AS c,
class
WHERE
s.student_number NOT IN (
SELECT
b.student_id
FROM
bed AS b
WHERE
b.student_id IS NOT NULL
)
AND s.class_id = class.id
AND class.major_id = m.id
AND m.department_id = d.id
AND d.college_id = c.id
ORDER BY s.student_number
LIMIT 0,13
乍眼一看,是没有问题的,在数据量少的时候也是可以跑得动,当我们拿到实际项目使用中的时候发现,sql跑不动了,按照我的想法是,因为在外部进行了多重的关联,当数据量大的时候关联的数据也需要排序。基于这一点我就把sql修改为下面的形式,然后sql就能跑了。
SELECT
s.student_number AS studentNumber,
s. NAME,
s.sex,
class. NAME AS studentClass,
m. NAME AS professiona,
d. NAME AS department,
c. NAME AS college
FROM
student_info AS s,
major AS m,
department AS d,
college AS c,
class
WHERE
s.student_number NOT IN (
SELECT
b.student_id
FROM
bed AS b
WHERE
b.student_id IS NOT NULL
ORDER BY
b.student_id
)
AND s.class_id = class.id
AND class.major_id = m.id
AND m.department_id = d.id
AND d.college_id = c.id
LIMIT 0,13
通过这个事件我忽然想到,sql的排序不一定也不能完全放在最外面,当数据量大或外连接多的时候,能早一步进行排序就早一步进行排序,这样也算是对sql的一种优化