本文介绍在数据量较大的结果集场景中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)