「インデックスを使用してSQLクエリの処理速度を向上させる方法[Python版]」

Improving SQL query processing speed using indexes [Python version]

本をめくっているとします。そして、自分が探している情報をより速く見つけたいと思います。どうやってそれを実現するのでしょうか？おそらく、用語索引を引いて、特定の用語を参照しているページにジャンプするでしょう。「SQLのインデックスは、本のインデックスと同様に機能します。

ほとんどの現実のシステムでは、大量の行（数百万行と考えてください）を持つデータベーステーブルに対してクエリを実行します。結果を取得するためにすべての行をフルテーブルスキャンするクエリは非常に遅くなります。特定の列に基づいて情報をクエリする必要があることがわかっている場合、それらの列にデータベースインデックスを作成することができます。これにより、クエリの速度が大幅に向上します。

では、今日は何を学ぶのでしょうか？PythonでSQLiteデータベースに接続してクエリを実行する方法を学びます。また、インデックスの追加方法とパフォーマンスの改善を見ていきます。

このチュートリアルに沿ってコードを記述するには、Python 3.7以上とSQLiteが作業環境にインストールされている必要があります。

注意：このチュートリアルの例およびサンプル出力は、Ubuntu LTS 22.04上のPython 3.10およびSQLite3（バージョン3.37.2）用です。

Pythonでデータベースに接続する

組み込みのsqlite3モジュールを使用します。クエリを実行する前に、次の作業を行う必要があります。

データベースに接続する
クエリを実行するためのデータベースカーソルを作成する

データベースに接続するには、sqlite3モジュールのconnect()関数を使用します。接続が確立されたら、接続オブジェクト上でcursor()を呼び出してデータベースカーソルを作成できます。以下のようになります：

import sqlite3

# データベースに接続
db_conn = sqlite3.connect('people_db.db')
db_cursor = db_conn.cursor()

ここではデータベース“people_db“に接続しようとしています。データベースが存在しない場合、上記のスニペットを実行すると、sqliteデータベースが作成されます。

テーブルの作成とレコードの挿入

さて、データベースにテーブルを作成し、レコードを追加します。

以下のフィールドを持つpeopleという名前のテーブルをpeople_dbデータベースに作成しましょう：

名前
メール
職業

# main.py
...
# テーブルを作成
db_cursor.execute('''CREATE TABLE people (
                  id INTEGER PRIMARY KEY,
                  name TEXT,
                  email TEXT,
                  job TEXT)''')

...

# トランザクションを確定し、カーソルとデータベース接続をクローズする
db_conn.commit()
db_cursor.close()
db_conn.close()

Fakerを使用した合成データの生成

これでテーブルにレコードを挿入する必要があります。これを行うために、合成データ生成用のPythonパッケージであるFakerを使用します。pipを介してインストールできます：

$ pip install faker

Fakerをインストールした後、FakerクラスをPythonスクリプトにインポートできます：

# main.py
...
from faker import Faker
...

次のステップは、peopleテーブルにレコードを生成して挿入することです。クエリを高速化するためにインデックスがどのように役立つかを知るために、大量のレコードを挿入します。ここでは、100,000件のレコードを挿入します。num_records変数を100000に設定してください。

その後、次のことを行います：

Fakerオブジェクトfakeをインスタンス化し、再現性を確保するためにシードを設定します。
fakeオブジェクト上でfirst_name()およびlast_name()を呼び出して、名前の文字列を取得します。
domain_name()を呼び出して、フェイクなドメインを生成します。
先ほど取得した名前とドメインを使用して、メールフィールドを生成します。
job()を使用して、各個別レコードの職業を取得します。

私たちはpeopleテーブルにレコードを生成して挿入します：

# レコードを生成して挿入する
fake = Faker() # 必ず以下をインポートしてください：from faker import Faker 
Faker.seed(42)

num_records = 100000

for _ in range(num_records):
    first = fake.first_name()
    last = fake.last_name()
    name = f"{first} {last}"
    domain = fake.domain_name()
    email = f"{first}.{last}@{domain}"
    job = fake.job()
    db_cursor.execute('INSERT INTO people (name, email, job) VALUES (?,?,?)', (name,email,job))

# トランザクションをコミットし、カーソルとデータベース接続を閉じる
db_conn.commit()
db_cursor.close()
db_conn.close()

次に、main.pyファイルに次のコードがあります：

# main.py
# インポート
import sqlite3
from faker import Faker

# データベースに接続する
db_conn = sqlite3.connect('people_db.db')
db_cursor = db_conn.cursor()

# テーブルを作成する
db_cursor.execute('''CREATE TABLE people (
                  id INTEGER PRIMARY KEY,
                  name TEXT,
                  email TEXT,
                  job TEXT)''')

# レコードを生成して挿入する
fake = Faker()
Faker.seed(42)

num_records = 100000

for _ in range(num_records):
    first = fake.first_name()
    last = fake.last_name()
    name = f"{first} {last}"
    domain = fake.domain_name()
    email = f"{first}.{last}@{domain}"
    job = fake.job()
    db_cursor.execute('INSERT INTO people (name, email, job) VALUES (?,?,?)', (name,email,job))

# トランザクションをコミットし、カーソルとデータベース接続を閉じる
db_conn.commit()
db_cursor.close()
db_conn.close()

このスクリプトを実行して、テーブルをnum_records数のレコードで作成します。

データベースのクエリ

テーブルに100,000件のレコードがあるので、peopleテーブルでサンプルクエリを実行しましょう。

次のクエリを実行してみましょう：

ジョブタイトルが「Product manager」のレコードの名前とメールアドレスを取得し、
クエリ結果を10件に制限します。

クエリの実行時間を取得するために、デフォルトのタイマーをtimeモジュールから使用します。

# sample_query.py

import sqlite3
import time

db_conn = sqlite3.connect("people_db.db")
db_cursor = db_conn.cursor()

t1 = time.perf_counter_ns()

db_cursor.execute("SELECT name, email FROM people WHERE job='Product manager' LIMIT 10;")

res = db_cursor.fetchall()
t2 = time.perf_counter_ns()

print(res)
print(f"インデックスなしのクエリ時間：{(t2-t1)/1000} us")

以下が出力です：

出力 >>
[
    ("Tina Woods", "[email protected]"),
    ("Toni Jackson", "[email protected]"),
    ("Lisa Miller", "[email protected]"),
    ("Katherine Guerrero", "[email protected]"),
    ("Michelle Lane", "[email protected]"),
    ("Jane Johnson", "[email protected]"),
    ("Matthew Odom", "[email protected]"),
    ("Isaac Daniel", "[email protected]"),
    ("Jay Byrd", "[email protected]"),
    ("Thomas Kirby", "[email protected]"),
]

インデックスなしのクエリ時間：448.275 us

また、SQLiteのコマンドラインクライアントを実行するには、コマンドラインでsqlite3 db_nameを実行します：

$ sqlite3 people_db.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.

インデックスのリストを取得するには、.indexを実行します：

sqlite> .index

現在インデックスが存在しないため、インデックスは表示されません。

以下のようにクエリプランを確認することもできます：

sqlite> EXPLAIN QUERY PLAN SELECT name, email FROM people WHERE job='Product Manager' LIMIT 10;
QUERY PLAN
`--SCAN people

ここでは、クエリプランはすべての行をスキャンすることで、効率が悪いです。

特定の列にインデックスを作成する

特定の列にデータベースインデックスを作成するには、次の構文を使用できます：

CREATE INDEX index-name on table (column(s))

例えば、特定の職種を持つ個人のレコードを頻繁に検索する必要がある場合、people_job_indexという名前のインデックスをjob列に作成すると便利です：

# create_index.py

import time
import sqlite3

db_conn = sqlite3.connect('people_db.db')

db_cursor =db_conn.cursor()

t1 = time.perf_counter_ns()

db_cursor.execute("CREATE INDEX people_job_index ON people (job)")

t2 = time.perf_counter_ns()

db_conn.commit()

print(f"インデックスの作成時間：{(t2 - t1)/1000} us")


出力 >>
インデックスの作成時間：338298.6 us

インデックスの作成には時間がかかりますが、これは一度だけの操作です。複数のクエリを実行する際にも大幅な高速化が得られます。

今度はSQLiteのコマンドラインクライアントで.indexを実行すると、次のようになります：

sqlite> .index
people_job_index

インデックスを使用してデータベースをクエリする

今、クエリプランを確認すると、peopleテーブルをjob列のインデックスpeople_job_indexを使用して検索していることがわかります：

sqlite> EXPLAIN QUERY PLAN SELECT name, email FROM people WHERE job='Product manager' LIMIT 10;
QUERY PLAN
`--SEARCH people USING INDEX people_job_index (job=?)

sample_query.pyを再実行することもできます。ただし、print()ステートメントのみを変更し、クエリの実行にかかる時間を確認してください：

# sample_query.py

import sqlite3
import time

db_conn = sqlite3.connect("people_db.db")
db_cursor = db_conn.cursor()

t1 = time.perf_counter_ns()

db_cursor.execute("SELECT name, email FROM people WHERE job='Product manager' LIMIT 10;")

res = db_cursor.fetchall()
t2 = time.perf_counter_ns()

print(res)
print(f"インデックスを使用したクエリの実行時間：{(t2-t1)/1000} us")

以下は出力例です：

出力 >>
[
    ("Tina Woods", "[email protected]"),
    ("Toni Jackson", "[email protected]"),
    ("Lisa Miller", "[email protected]"),
    ("Katherine Guerrero", "[email protected]"),
    ("Michelle Lane", "[email protected]"),
    ("Jane Johnson", "[email protected]"),
    ("Matthew Odom", "[email protected]"),
    ("Isaac Daniel", "[email protected]"),
    ("Jay Byrd", "[email protected]"),
    ("Thomas Kirby", "[email protected]"),
]

インデックスを使用したクエリの実行時間：167.179 us

クエリの実行時間は約167.179マイクロ秒かかります。

パフォーマンスの向上

サンプルクエリでは、インデックスを使用したクエリは約2.68倍高速です。実行時間の速度向上率は62.71%です。また、ジョブ列のみにインデックスを作成したため、他の列を含むクエリを実行しても、インデックスを使用しない場合と同じく高速化されません。

まとめと次のステップ

このガイドが、頻繁にクエリされるカラムにデータベースインデックスを作成することがどれだけクエリのスピードを向上させるかを理解するのに役立てば幸いです。これはデータベースインデックスの紹介です。また、複数列のインデックス、同じカラムの複数のインデックスなども作成できます。

このチュートリアルで使用されたすべてのコードは、このGitHubリポジトリで見つけることができます。Happy coding! Bala Priya Cは、インド出身の開発者兼技術ライターです。彼女は数学、プログラミング、データサイエンス、コンテンツ作成の交差点での作業が好きです。彼女の関心と専門分野には、DevOps、データサイエンス、自然言語処理などが含まれます。彼女は読書、執筆、コーディング、コーヒーを楽しんでいます！現在、彼女はチュートリアル、ハウツーガイド、意見記事などを執筆することにより、開発者コミュニティとの知識を学び、共有することに取り組んでいます。

We will continue to update VoAGI; if you have any questions or suggestions, please contact us!

Python

Was this article helpful?

93 out of 132 found this helpful

「インデックスを使用してSQLクエリの処理速度を向上させる方法[Python版]」

Pythonでデータベースに接続する

テーブルの作成とレコードの挿入

Fakerを使用した合成データの生成

データベースのクエリ

特定の列にインデックスを作成する

インデックスを使用してデータベースをクエリする

パフォーマンスの向上

まとめと次のステップ

Was this article helpful?

「2023年に注目すべきマーケティングトレンドのトップAI」

「2023年に必要な機械学習エンジニアの10の必須スキル」

人工知能

「アナコンダのCEO兼共同創業者、ピーターウォングによるインタビューシリーズ」

「David Smith、TheVentureCityの最高データオフィサー- インタビューシリーズ」

「トリントの創設者兼CEO、ジェフ・コフマンへのインタビューシリーズ」

アーティスの創設者兼CEO、ウィリアム・ウーによるインタビューシリーズ

もし芸術が私たちの人間性を表現する方法であるなら、人工知能はどこに適合するのでしょうか？

アステラソフトウェアのCOO、ジェイ・ミシュラ - インタビューシリーズ