2020-05-19

【InfluxDB】LIMIT と OFFSETの使い方【InfluxQL】

InfluxQLのLIMIT句とOFFSET句の使い方を紹介。

`InfluxQL`

本日はInfluxDB用のクエリ言語InfluxQLのLIMIT, OFFSET句の使い方を解説する。

`LIMIT`と`OFFSET`の効果は`SQL`と一緒

LIMIT 数字で指定した件数以上のデータがSELECTされたら、それ以上は破棄する設定。

OFFSET 数字は数字で指定した件数のデータをスキップし、それ以降を抽出する。

これらはSQLと同じなので慣れている人にはなんてことはないかもしれない。

つまりどういうこと？

例えば

SELECT * FROM メジャーメント WHERE 条件 LIMIT 1000

LIMITが指定されているので始めの1000件だけSELECTしてそれを超えた分は破棄される。

SELECT * FROM メジャーメント WHERE 条件 OFFSET 1000

今度はOFFSETが1000になっているので、始めの1000件は破棄して、1001件目以降が全て抽出される。

組み合わせたらどうなる？

SELECT * FROM メジャーメント WHERE 条件 LIMIT 1000 OFFSET 1000

こう使うと始めの1000件をスキップして、そこから1000件抽出、それ以降は破棄になる。

どういう時に使うのか

単純にLIMITだけなら上位10件だけほしい時に、OFFSETだけなら11位以降だけほしい時に使用できる。

ではLIMITとOFFSETを組み合わせるとどんな良いことがあるのだろうか。

これはかなりの量のデータを一気に抽出しようと思った時に分割して抽出するというケースにはもってこいのコードだと考えている(もちろん 10位 ~ 20位だけ抽出というような使い方も)

つまり次のようにOFFSETを一定間隔で増やして分割してInfluxQLを実行する。

SELECT * FROM メジャーメント WHERE 条件 LIMIT 1000 OFFSET 0 -- 0の場合はOFFSETを省略してもよい
SELECT * FROM メジャーメント WHERE 条件 LIMIT 1000 OFFSET 1000
SELECT * FROM メジャーメント WHERE 条件 LIMIT 1000 OFFSET 2000
SELECT * FROM メジャーメント WHERE 条件 LIMIT 1000 OFFSET 3000
...

数百万件に及ぶレコード抽出は分単位で時間がかかるため処理が固まってしまったようにも見える。

プログラムで上記のようにクエリを発行してやればプログラムが停止してないことが確認しやすくなり、現在何件目をSELECTしているのかという情報も分かりやすくなる。

デメリット

基本的にクエリは非同期で何個も同時に発行することが出来るが私の経験上あまりおすすめできない。

負荷が掛かりすぎるとクエリそのものが破棄されることがあるからだ。

そこで1クエリづつawaitして回すことになるが、クエリを分ければ分けるほど処理時間が長くなることが分かっている。

私のデータだけで申し訳ないが、200万件のレコードを一気にSELECTすれば約50～60秒。10万件づつ分けると70～80秒ほどとなった。

これはクエリ毎にOFFSETまでのレコードの検索が行われるため。つまり重複したSELECTが走っていることになり無駄が発生する。

初めから200万件一括でSELECTすればよいのではとも思うが、ユーザー側の入力で自在にデータ件数が変わってくる設計ではやはり分けたほうが安定すると考えている。

デメリットの解決策

OFFSETによる速度低下が問題になるようであれば最初のクエリで取ってきた最後のレコードの日付を使って条件を絞れば良い。

つまりOFFSETを使うのではなくWHERE句の内容を変更する。

こちらは次の実装でもう一度BLOG記事にしたい。

私の実例

1日分の約定データをSELECTするだけでも200万件ほどになる。今後の実装でより長い日数を取得する場合のためにも上記の実装を試みた。

秒足の作成や保存コードはざっくり省略しているが興味があれば参考にしてほしい。

// 予めクエリだけ設定(丸一日分のSELECT)
// ここは将来的にはユーザーの入力で変化する
const allQuery = `SELECT * FROM "bitFlyer_db"."autogen"."lightning_executions_FX_BTC_JPY" WHERE time > '2020-05-17' AND time < '2020-05-18'`
const countQuery = `SELECT COUNT(id) FROM "bitFlyer_db"."autogen"."lightning_executions_FX_BTC_JPY" WHERE time > '2020-05-17' AND time < '2020-05-18'`

remoteDB.query(countQuery) //レコード件数をカウント
    .then((res) => {
        if (res.length === 0) { console.log("No Data Counted."); process.exit(1); }
        const recordCount = res[0].count //レコード数は.countの中
        console.log(`Total Records: ${recordCount}`)
        const loopMax = Math.floor(recordCount / select_limit); //クエリ生成の最大ループ数
        (async () => { //awaitするために無名関数
            for (let loopNo = 0; loopNo <= loopMax; loopNo++) { //件数に応じたループ
                await queryMain(loopNo, loopMax) //メインのクエリを発行
            }
            //データのファイル出力
            exportData()
        })();

    })
    .catch((err) => {
        console.log(err); process.exit(1);
    })

//メインのクエリ発行
const queryMain = (loopNo, loopMax) => {
    //OFFSETを指定。limitはグローバル変数にて変数を指定している(select_limit)
    const limitedQuery = `${allQuery} LIMIT ${select_limit} OFFSET ${loopNo * select_limit}`
    //進行状況を表示することによって分かりやすくする
    console.log(`Execute Query: ${loopNo} / ${loopMax}`) 
    // promiseをreturnしないとawait出来ない
    return remoteDB.query(limitedQuery)
        .then((res) => {
            if (res.length === 0) { console.log("No Data Selected."); process.exit(1); }
            candleMain(res) //抽出されたデータから秒足生成のルーチンに飛ぶ
        }).catch((err) => {
            console.log(err)
        })
}