LIMIT OFFSET分页

问题

由一个表查询引起的：

SELECT t.province, COUNT(1) FROM tableA t
GROUP BY t.province
ORDER BY t.province
LIMIT 10
OFFSET 20
;

原来的构想是查询[21, 30]区间内的结果，然而在公司数据库上查返回结果为空……

分析

公司数据库是自己搞出来的，通过日常语句分析跟MySQL、PostgreSQL类似。想通过MySQL与PostgreSQL的文档来分析一下LIMIT与OFFSET的用法。

1. 查询MySQL文档：version 5.7 13.2.9 SELECT Syntax

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

样例中关于LIMIT与OFFSET解释的：

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

……

2. 查询PostgreSQL文档：PostgreSQL 9.3.19 Documentation

SELECT select_list
    FROM table_expression
    [ ORDER BY ... ]
    [ LIMIT { number | ALL } ] [ OFFSET number ]

OFFSET says to skip that many rows before beginning to return rows. OFFSET 0 is the same as omitting the OFFSET clause, and LIMIT NULL is the same as omitting the LIMIT clause. If both OFFSET and LIMIT appear, then OFFSET rows are skipped before starting to count the LIMIT rows that are returned.

看出来是先根据OFFSET进行偏移，再通过LIMIT来获取偏移后记录数
例如：

……
LIMIT 10
OFFSET 20

表示查询结果区间为[21, 30]

3. 为啥在公司数据库查询异常：
   去翻了翻有没有跟我一样迷茫的……
   找到了一个PostgreSQL 排序后 Limit OFFSET 分页 串行 问题解决办法：

百度了很久也没有最终的答案，后来在阿里巴巴的朋友告诉我，他们遇到过这类问题，阿里巴巴内部培训已经将此类问题着重强调了。下面我将解决办法贴出来：
第一种办法也是最笨的办法，就是升级 PostgreSQL 的版本，到最新的9.5版本。
第二种办法是在排序的字段上加上主键，或者组合排序的字段最终能形成一个唯一约束的记录。例如我这里在order by 后边在加上 ID的倒下即可。

4. 看起来是问题语句中GROUP BY出问题了……，这两天请教一下公司数据库工程师，分析一下为啥会有问题……

Tips

查MySQL文档的备忘知识点：

1. SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
2. SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

   In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

PostgreSQL备忘知识点：

1. The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient.

OFFSET越大，性能越差

2. When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows. You might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? The ordering is unknown, unless you specified ORDER BY.

使用LIMIT前请排序