DBにインデックスを貼るとどれくらい速くなるのか #SQL

インデックスはDBの高速化に有効であるという記事はいくつも見つかるが、どの程度速くなるのかという記事はあまり見かけなかったので検証してみた。

実験設定

今回は以下の単純なテーブルを用意した。

CREATE TABLE test(id integer primary key, value integer);

valueが0~1000までの乱数のレコードを10^9個insertし、value=100のレコードの個数を検索するのにかかる時間を測定する。

PCスペック

MacBook Air (Retina, 13インチ, 2020)
プロセッサ 1.1 GHz クアッドコアIntel Core i5
メモリ 16 GB 3733 MHz LPDDR4X
macOS Big Sur バージョン 11.4

手順

DBの準備

今回は準備が簡単なSQLiteを利用した。

> brew install sqlite3
> sqlite3 test.db
sqlite > create table test(id integer primary key, value integer);

データの挿入

データを手入力するのは面倒なのでJavaを使う。
DBドライバの依存を追加する必要があるのでgradleを利用した。

build.gradle

dependencies {
    implementation group:'org.xerial', name:'sqlite-jdbc', version:'3.34.0'
}

ソースコードは以下の通り。

Main.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Random;
import java.util.stream.Collectors;
import java.util.stream.Stream;

public class Main {
    public static void main(final String[] args) {
        final int numOfValues = 100_000; // SQLiteのバルクインサートは10^5程度が限界らしい
        final String url = "jdbc:sqlite:/path/to/test.db";
        final String sql = new StringBuilder("insert into test(value) values")
            .append(Stream.generate(() -> "(?)")
                .limit(numOfValues)
                .collect(Collectors.joining(",")))
            .append(";")
            .toString();
        final Random random = new Random();
        try (final Connection connection = DriverManager.getConnection(url);
             final PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (int i = 0; i < 10_000; i++) {
                for (int j = 1; j <= numOfValues; j++) {
                    preparedStatement.setInt(j, random.nextInt(1000)); //0~1000の乱数を生成する
                }
                preparedStatement.executeUpdate();
            }
        } catch (final SQLException e) {
            e.printStackTrace();
        }
    }
}

上を実行することで10^9個のレコードがDBに保存される。

sqlite > select count(*) from test;
1000000000

時間計測

SQLiteでは以下のコマンドを実行することで、それ以降のクエリの実行時間を測定できる。

sqlite > .timer on

まずは普通にクエリを投げる。

sqlite > select count(*) from test where value=100;

次にインデックスを貼ってからクエリを投げる。

sqlite > create index valueindex on test(value);
sqlite > select count(*) from test where value=100;

結果

インデックスを貼る前

sqlite> select count(*) from test where value=100;
999415
Run Time: real 153.334 user 80.279714 sys 17.292470

インデックスを貼った後

sqlite> select count(*) from test where value=100;
999415
Run Time: real 0.104 user 0.059074 sys 0.013650

153秒から0.1と大幅に実行速度が削減されたことが確認できた。
ちなみに、インデックスを貼るのに

sqlite> create index valueindex on test(value);
Run Time: real 2155.104 user 876.969727 sys 207.597416

と30分以上の時間がかかったため、クエリに絡まないカラムにインデックスを貼るのは時間の無駄であることも確認できた。

その他

ソースコードはこちら