SQL的ORDER BY 的使用注意

项目中我写了一条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的一种优化