大量の結果セットでの ORDER BY の使用 - Lindorm - Alibaba Cloud ドキュメントセンター

このトピックでは、Lindorm の SQL ステートメントで ORDER BY 句を使用する方法と、大量の結果エントリが返されるシナリオでの使用に関するベストプラクティスについて説明します。

シナリオ

Lindorm SQL ステートメントの ORDER BY 句は、一般的に以下のシナリオで使用されます。

結果エントリ数が 100,000 未満の場合、ORDER BY を使用してメモリ内のデータを制限なくクエリできます。この場合、メモリ内コンピューティング機能を有効にする必要があります。この機能を有効にするには、Lindorm のテクニカルサポート（DingTalk ID：s0s3eg3）にお問い合わせください。
結果エントリ数が 100,000 以上の場合、Lindorm は ORDER BY の使用に制限を課します。制限は、Lindorm のデータストレージルールによって異なります。ORDER BY を実行する場合、結果エントリは SQL エンジンによってソートされません。特定の列に基づいて結果エントリをソートする必要がある場合は、この列をインデックスの左端のプレフィックス列として指定できます。
警告メモリ内コンピューティングが有効になった後に大量の結果エントリが返されると、クライアントが応答を停止する可能性があります。

解決策

ほとんどの場合、テーブルの作成後、制限付きでしかデータをソートできません。特定の列に基づいてデータをソートする必要がある場合は、その列に単一の等価条件を指定する必要があります。次のステートメントは例を示しています。

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 を使用できます。ただし、この方法で ORDER BY を使用しないことをお勧めします。
SELECT * FROM test WHERE p1=? ORDER BY p3                     // この SQL ステートメントでは ORDER BY を使用できません。このステートメントにはすべての主キー列を含める必要があります。
SELECT * FROM test WHERE p1<? ORDER BY p2                     // この SQL ステートメントでは ORDER BY を使用できません。WHERE 句の主キー列 p1 に単一の等価条件を指定する必要があります。
SELECT * FROM test WHERE p1=? and p2>? ORDER BY p3            // この SQL ステートメントでは ORDER BY を使用できません。WHERE 句の主キー列 p2 に等価条件を指定する必要があります。
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) を使用して関連列にセカンダリインデックスを作成できます。このようにして、結果エントリを p3 に基づいてソートできます。

例

次のサンプルコードは、ORDER BY を使用して大量の結果エントリをソートする方法の例を示しています。

セカンダリインデックスとベーステーブルのデータは異なるテーブルに格納されます。セカンダリインデックスを作成し、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 句では、ベーステーブルに表示される順序で列を指定し、1 つ以上の列をスキップしないでください。このようにして、最適なパフォーマンスを実現できます。例：

// 指定されたスキーマに基づいてベーステーブルを作成します。
CREATE TABLE test (p1 asc,p2 asc,p3,c1,c2)

// c1 および c2 インデックスキー列に基づいて結果エントリをソートします。
SELECT * FROM table WHERE p1 = ? ORDER BY c1 desc, c2 asc

// ソート用のセカンダリインデックスを作成する場合、ORDER BY 句に表示されるのと同じ順序で c1 および c2 インデックスキー列を指定します。
CREATE INDEX idx ON test(p1, c1 desc, c2 asc)