本文介绍在数据量较大的结果集场景中Lindorm SQL ORDER BY的使用解决方案以及最佳案例。

使用场景

常见Lindorm SQL的ORDER BY语法使用场景如下:
  • 在数据量较小(10万内的数据量)的结果集场景中,使用ORDER BY可以在内存中计算,没有任何使用限制。这种场景需要开启在内存中计算功能,如果需要开启,请联系Lindorm技术支持(钉钉号:s0s3eg3)。
  • 在数据量较大的结果集场景中,使用ORDER BY需要依赖Lindorm本身对数据的存储规则,所以存在一定的限制。使用ORDER BY过程中不对数据做额外的排序,如果有需要排序的列,可以将此列设置为单等值前缀列。
    警告 开启在内存中计算,如果结果集较大可能会导致客户端停止响应。

解决方案

一般情况下,创建表格后只能进行有限的排序,不支持任意列排序。如果有需要排序的列,可以将此列设置为单等值前缀列。例如以下代码。
CREATE TABLE test(p1,p2,p3,c1,c2,c3, primary key(p1,p2,p3))   // 其中p1,p2,p3为联合主键
SELECT * FROM test WHERE p1=? ORDER BY p2                     // 该SQL支持ORDER BY
SELECT * FROM test WHERE p1=? and p2=? ORDER BY p3            // 该SQL支持ORDER BY
SELECT * FROM test WHERE p1=? and c1=? ORDER BY p2            // 该SQL支持ORDER BY,p2列已设置为单等值前缀列
SELECT * FROM test ORDER BY p1                                // 该SQL支持ORDER BY,但是不建议这么用
SELECT * FROM test WHERE p1=? ORDER BY p3                     // 该SQL不支持ORDER BY,要求给定所有的前缀
SELECT * FROM test WHERE p1<? ORDER BY p2                     // 该SQL不支持ORDER BY,要求等值前缀
SELECT * FROM test WHERE p1=? and p2>? ORDER BY p3            // 该SQL不支持ORDER BY,要求等值前缀
SELECT * FROM test WHERE p1=? and p2=? and p3=? ORDER BY c1   // 该SQL不支持ORDER BY,c1为非主键列
SELECT * FROM test WHERE p1=a or p1=b ORDER BY p2            // 该SQL不支持ORDER BY,要求单值前缀,而p1给了好几个值
在数据量较大的结果集场景中,如果您必须使用ORDER BY排序,提供以下解决方案。
  • 修改表的主键。
  • 为需要排序的列创建二级索引或者搜索索引。例如上述代码中的SELECT * FROM test WHERE p1=? ORDER BY p3,通过创建二级索引语句CREATE INDEX idx ON test(p1, p3)可以支持排序。

示例

在数据量较大的结果集场景中,如果您必须使用ORDER BY排序,可以参考如下示例。

  • 由于二级索引与主表数据没有存储在同一张表中,所以在创建二级索引使用ORDER BY排序时,您需要完全指定二级索引列。代码如下:
    //需要排序c1列
    SELECT * FROM test WHERE p1=? ORDER BY c1
    
    //解决方案:创建二级索引实现c1列的排序
    CREATE INDEX idx ON test(p1, c1)
  • 二级索引列排序方式与需要的常用排序方式保持一致,性能最佳。代码如下:
    //需要排序c1列
    SELECT * FROM test WHERE p1=? ORDER BY c1 desc
    
    //解决方案:创建二级索引实现c1列的排序
    CREATE INDEX idx ON test(p1, c1 desc)
  • ORDER BY多列场景中,需要保证多列连续,并且在表中的顺序与ORDER BY的顺序保持一致,性能最佳。代码如下:
    //主表结构
    CREATE TABLE test (p1 asc,p2 asc,p3,c1,c2)
    
    //需要排序c1和c2索引列
    SELECT * FROM table WHERE p1 = ? ORDER BY c1 desc, c2 asc
    
    //创建二级索引实现排序时要保证c1、c2索引列与ORDER BY排序方式相同
    CREATE INDEX idx ON test(p1, c1 desc, c2 asc)