默认情况下,执行器认为Null值要大于所有值,所以,简单的如 dt_qzsj desc降序排序会把所有的null排在最前面,dt_qzsj asc会将null排在最后面。
某些情况下使用dt_qzsj desc需要将null值放到最后,如下:
explain analyze
SELECT
qzsq.c_id sqId,
...
FROM
ywst.t_qzsq qzsq
LEFT JOIN ywst.t_qzst_ws ws ON qzsq.c_id_ws = ws.c_id
LEFT JOIN ywst.t_qzst_aj aj ON qzsq.c_id_aj = aj.c_id
WHERE
qzsq.n_qzzt = 4
ORDER BY dt_qzsj DESC LIMIT 10 OFFSET 0
创建了索引create index i_t_qzsq_dt_qzsj on ywst.t_qzsq(dt_qzsj desc last);
虽然创建了索引,但是并没有走索引,需要在sql中也加入nulls last才行,正确的写法:ORDER BY dt_qzsj DESC nulls last LIMIT 10 OFFSET 0