インデックス - クエリの最適化

**条件**が真である`WHERE`句を使用して、いくつかのデータを取得する方法を学びました。たとえば、ヒーローの**名前が「Deadpond」**である場合などです。

これまでのようにテーブルとデータを作成するだけの場合、`WHERE`句を使用して`SELECT`でデータを取得すると、データベースは**一致**するデータを見つけるために**すべてのレコード**を**スキャン**する必要があります。これは、これらの例のようにヒーローが3人しかいない場合は問題ありません。

しかし、データベースに**数千**または**数百万**の**レコード**があるとします。名前が「Deadpond」のヒーローを見つけるたびに**すべて**のレコードをスキャンして、一致する可能性のあるヒーローをすべて見つける必要がある場合、それは問題になります。遅すぎるからです。

データベースの**インデックス**を使用して、これを処理する方法を紹介します。

コードの変更は**非常に小さい**ですが、何が起こっているのかを理解することは役立ちます。そこで、**すべてがどのように機能するのか**、そしてそれが何を意味するのかを説明します。

---

前の例を実行済みで、データを含むデータベースがある場合は、各例を実行する前に**データベースファイルを削除**してください。そうすれば、データが重複せず、同じ結果を得ることができます。

説明不要

あなたはすでに**SQLエキスパート**であり、私のすべての説明に時間を割くことができないでしょうか？

その場合は、ここでインデックスを作成するための最終コードを**こっそり覗き見**ることができます。

👀 ファイル全体のプレビュー

Python 3.10以降Python 3.7以降

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
    hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
    hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
    hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
    hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)
        session.add(hero_4)
        session.add(hero_5)
        session.add(hero_6)
        session.add(hero_7)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.age <= 35)
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
    hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
    hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
    hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
    hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)
        session.add(hero_4)
        session.add(hero_5)
        session.add(hero_6)
        session.add(hero_7)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.age <= 35)
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

...しかし、あなたがエキスパートでない場合は、**読み続けて**ください。これはおそらく役に立つでしょう。🤓

インデックスとは

一般的に、**インデックス**とは、**物事をより速く見つける**のに役立つものです。通常、物事を**順番**に並べることで機能します。データベースやコードについて考える前に、実際の例を考えてみましょう。

インデックスと辞書

**辞書**、つまり単語の定義が書かれた本を想像してみてください。📔 ...Pythonの`dict`ではありません。😅

たとえば、「**database**」という単語を**見つけたい**とします。辞書を取り、どこか、たとえば真ん中あたりを開きます。おそらく`m`で始まる単語の定義、たとえば`manual`などが表示されるので、辞書の`m`の文字にいると結論付けます。

アルファベットでは、`database`の`d`は`manual`の`m`の**前**に来ることがわかっています。

そのため、現在いる地点の**前**を検索する必要があることがわかります。`database`という単語が辞書のどこに genau あるかはまだわかりません。`d`が辞書のどこに genau あるかはわからないからです。しかし、**その地点の後にはない**ことがわかっているので、検索対象から辞書の**右半分を捨てる**ことができます。

次に、**辞書をもう一度開きます**が、必要な単語が含まれている可能性のある**辞書の半分**、つまり辞書の**左側**のみを考慮します。その左側の真ん中で開くと、今度は`f`の文字にたどり着くかもしれません。

`database`の`d`は`f`の前に来ることを知っています。だから、それは**その前**にあるはずです。しかし、今では`database`が**その地点の後にはない**ことがわかっているので、その地点以降の辞書を捨てることができます。

これで、検索対象の**辞書のセクションが小さく**なりました（辞書の**4分の1**だけが単語を含んでいる可能性があります）。単語が含まれている可能性のある辞書の最初の部分にある**4分の1**のページを取り、そのセクションの真ん中で開きます。おそらく`c`の文字にたどり着くでしょう。

`database`という単語は**その後**にあり、**その前**にはないことがわかっているので、そのブロックのページの左側は捨てることができます。

このプロセスを**さらに数回**繰り返すと、最終的に`d`の文字にたどり着きます。`d`のセクションで同じプロセスを続けると、最終的に`database`という**単語が見つかります**。🎉

辞書を数回、おそらく**5回か10回**開く必要がありました。それは、実際には、そうであったかもしれないことに比べて**非常に少ない作業**です。

技術的な詳細

**かっこいい言葉**が好きですか？いいですね！プログラマーはかっこいい言葉が好きになる傾向があります。😅

上記のアルゴリズムは、**バイナリサーチ**と呼ばれています。

辞書（または順序付けられたもののリスト）を**2つ**（「バイナリ」は「2つ」を意味します）の部分に分割することで何かを**検索**するため、このように呼ばれています。そして、必要なものが見つかるまで、そのプロセスを複数回行います。

インデックスと小説

今度は、あなたが**小説**を読んでいると想像してみてください。そして、誰かがあなたに、ある時点で**データベース**について言及していると教えてくれ、あなたはその章を見つけたいと思っています。

そこで「*データベース*」という言葉を見つけるにはどうすればよいでしょうか？「*データベース*」という言葉が本のどこにあるかを見つけるには、**本全体**を読む必要があるかもしれません。そのため、本を5回または10回開く代わりに、**500ページ**のそれぞれを開いて、1つずつ読んで、単語を見つける必要があります。でも、その本を楽しむかもしれません。😅

しかし、**情報をすばやく見つける**ことだけに関心がある場合（SQLデータベースで作業する場合など）、5回または10か所で本を開いて探しているものを見つけることができるオプションがある場合、500ページのそれぞれを読むのは**非効率的**すぎます。

索引付きの技術書

今度は、あなたが技術書を読んでいると想像してみてください。たとえば、プログラミングに関するいくつかのトピックがあります。そして、**データベース**について説明しているセクションがいくつかあります。

この本には**索引**が付いている場合があります。本のセクションには、**取り上げられているトピックの名前**と、それらについて読むことができる本の**ページ番号**が記載されています。そして、トピック名は辞書（前の例のように、単語が書かれた本）と同様にアルファベット順に**ソート**されています。

この場合、**索引**セクションを見つけるために、その本の最後（または最初）を開くことができます。それはほんの数ページしかありません。そして、上記の**辞書**の例と同じプロセスを実行できます。

索引を開き、**5回または10回のステップ**の後、「**データベース**」というトピックと、それがカバーされているページ番号（たとえば、「第5章の253ページ」）をすばやく見つけます。これで、辞書のテクニックを使用して**トピック**を見つけ、そのトピックから**ページ番号**が得られました。

「253ページ」を探し出す必要があることは分かりました。しかし、閉じた本を見ても、そのページがどこにあるかはまだ分かりません。そのため、そのページを探し出す必要があります。見つけるには、同じプロセスをもう一度繰り返します。ただし、今回は索引でトピックを検索する代わりに、書籍全体でページ番号を検索します。そして、さらに5～10ステップ後、第5章の253ページを見つけます。

この後、この本は辞書ではなく、特定の内容が含まれていますが、「データベース」について説明しているセクションを数ステップ（500ページすべてを読むのではなく、10～20ステップ程度）で見つけることができました。

重要な点は、索引がソートされているため、辞書で使用したのと同じプロセスを使用してトピックを見つけることができるということです。そして、それによってページ番号が得られ、ページ番号もソートされているのです！ 😅

ソートされたもののリストがある場合、同じテクニックを適用できます。ここでのポイントは、索引のトピックとページ番号に同じテクニックを順番に適用して、実際の章を見つけることです。

なんて効率的なんだ！ 😎

データベースインデックスとは

データベースインデックスは、書籍の索引とよく似ています。

データベースインデックスは、いくつかの情報、いくつかのキーを、簡単かつ迅速に見つけられる方法（例えば、ソート済み）で保存し、各キーに対してデータベース内のどこか他の場所にあるデータを指します。

より明確な例を見てみましょう。データベースに次のテーブルがあるとします。

id	名前	秘密の名前	年齢
1	デッドポンド	ダイブ・ウィルソン	null
2	スパイダーボーイ	ペドロ・パルケアドール	null
3	ラスティマン	トミー・シャープ	48

そして、もっと多くの行、もっと多くのヒーローがいると想像してみましょう。おそらく数千です。

SQLデータベースに特定の名前、例えば `Spider-Boy`（SQLクエリの `WHERE` 部分で `name` を使用）でヒーローを取得するように指示すると、データベースはすべてのヒーローをスキャンし、1つずつチェックして、名前が `Spider-Boy` のヒーローをすべて見つける必要があります。

この場合、1つだけですが、データベースに同じ名前のレコードが複数存在することを制限するものはありません。そのため、データベースは検索を続け、各レコードをチェックし続けるため、非常に遅くなります。

しかし、データベースに列 `name` のインデックスがあるとしましょう。インデックスは次のようになります。インデックスは、データベースが自動的に管理する追加の特別なテーブルのようなものだと想像できます。

名前	id
デッドポンド	1
ラスティマン	3
スパイダーボーイ	2

`hero` テーブルの各 `name` フィールドが順番に並んでいます。`id` ではなく、`name` でソートされます（`name` は文字列なので、アルファベット順）。そのため、最初は `Deadpond`、次に `Rusty-Man`、最後は `Spider-Boy` になります。また、各ヒーローの `id` も含まれます。これは数千のヒーローを持つ可能性があることを覚えておいてください。

すると、データベースは上記の辞書と書籍の索引の例とほぼ同じアイデアを使用できるようになります。

どこかから開始できます（たとえば、インデックスの途中から）。途中のヒーロー、例えば `Rusty-Man` にたどり着くことができます。そして、インデックスには `name` フィールドが順番に並んでいるため、データベースはそれ以前のすべてのインデックス行を破棄し、後続のインデックス行のみを検索できることを認識します。

名前	id
デッドポンド	1
ラスティマン	3
スパイダーボーイ	2

このように、上記の辞書の例と同様に、データベースは数千のヒーローを読み取る代わりに、数ステップ、例えば5～10ステップを実行して、テーブル（およびインデックス）に数千の行があっても、`Spider-Boy` があるインデックスの行に到達できます。

名前	id
デッドポンド	1
ラスティマン	3
✨ スパイダーボーイ ✨	2

次に、このインデックス行を見ると、`hero` テーブルの `Spider-Boy` の `id` が `2` であることが分かります。

そのため、同じテクニックを使用して、`hero` テーブルでその `id` を検索できます。

このように、最終的に、データベースは数千のレコードを読み取る代わりに、目的のヒーローを見つけるために数ステップを実行するだけで済みました。

インデックスの更新

ご想像のとおり、これがすべて機能するためには、インデックスはデータベースのデータと最新の状態である必要があります。

コードで手動で更新する必要がある場合、非常に面倒でエラーが発生しやすくなります。インデックスが最新ではなく、間違ったデータを指してしまう状態になりやすいからです。😱

朗報です。SQLデータベースにインデックスを作成すると、データベースは必要なときに自動的にインデックスを更新します。 😎🎉

`hero` テーブルに新しいレコードを追加すると、データベースは自動的にインデックスを更新します。新しいインデックスデータを配置する適切な場所を見つけるのと同じプロセス（上記で説明した5～10ステップ）を実行し、そこに新しいインデックス情報を保存します。データを更新または削除する場合も同じことが起こります。

インデックスの定義と作成は、SQLデータベースでは非常に簡単です。そして、使用するのはさらに簡単です...透過的です。データベースは使用するインデックスを自動的に判断するため、SQLクエリは変更されません。

つまり、SQLデータベースではインデックスは素晴らしいのです！そして、非常に使い勝手が良いです。では、なぜすべてにインデックスを付けないのでしょうか？.....それは、インデックスにも計算とストレージ（ディスク容量）に「コスト」がかかるからです。

インデックスのコスト

インデックスにはコストがかかります。 💰

インデックスがない状態で `hero` テーブルに新しい行を追加する場合、データベースはテーブルの最後に新しいヒーロー行を追加するために1回の操作を実行する必要があります。

しかし、ヒーロー名のインデックスがある場合、データベースは同じ1回の操作を実行してその行を追加するだけでなく、インデックスで名前の適切な場所を見つけて、そこにインデックスレコードを追加するために、追加の5～10回の操作を実行する必要があります。

`name`、`age`、`secret_name` のインデックスがある場合、データベースは同じ1回の操作を実行してその行を追加するだけでなく、各インデックスに対してインデックスで追加の5～10回の操作を3回実行する必要があります。つまり、1行の追加には約31回の操作が必要になります。

これは、データを読み取るのにかかる時間を、データを書き込むのにかかる時間とデータベースの追加の容量に交換していることも意味します。

これらの各フィールドを比較してデータベースからデータを取得するクエリがある場合（たとえば、`WHERE` を使用）、それぞれにインデックスを作成することは理にかなっています。データの作成または更新中に31回の操作（とインデックスの容量）を行う方が、すべての行を読み取って各フィールドを使用して比較できる可能性のある500回または1000回の操作よりもはるかに優れているからです。

しかし、`secret_name` でレコードを見つけるクエリがまったくない場合（`WHERE` 部分で `secret_name` を使用しない場合）、`secret_name` フィールド/列のインデックスを作成することはおそらく理にかなっていません。データベースの書き込みと更新の計算コストと容量コストが増加するためです。

SQLでインデックスを作成する

ふう、たくさんの理論と説明がありました。😅

インデックスについて最も重要なことは、インデックスを理解すること、使用方法、および使用時期です。

それでは、インデックスを作成するためのSQL構文を見てみましょう。非常に簡単です。

CREATE INDEX ix_hero_name
ON hero (name)

これは、多かれ少なかれ次のことを意味します。

SQLデータベースさん、こんにちは 👋、`CREATE` してください `INDEX` 私に。

インデックスの名前は `ix_hero_name` にしたいです。

このインデックスはテーブル `hero` の `ON` にする必要があります。そのテーブルを参照します。

使用したい列は `name` です。

SQLModelでインデックスを宣言する

それでは、SQLModelでインデックスを定義する方法を見てみましょう。

コードの変更はそれほど大きくありません。非常に簡単です。😆

これは、以前使用していた `Hero` モデルです。

Python 3.10以降Python 3.7以降

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: int | None = None

# Code below omitted 👇

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None

# Code below omitted 👇

👀 ファイル全体のプレビュー

Python 3.10以降Python 3.7以降

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: int | None = None


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

テーブルを作成するときに `name` フィールドのインデックスを作成するようにSQLModelに指示するように更新しましょう。

Python 3.10以降Python 3.7以降

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)

# Code below omitted 👇

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)

# Code below omitted 👇

👀 ファイル全体のプレビュー

Python 3.10以降Python 3.7以降

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

以前と同じように `Field()` を再び使用し、`index=True` を設定します。それだけです！ 🚀

`default=None` などの引数を設定しなかったことに注意してください。これは、SQLModel（Pydanticのおかげで）がそれを必須フィールドとして保持することを意味します。

情報

SQLModel（実際にはSQLAlchemy）は、インデックス名を自動的に生成します。

この場合、生成される名前は `ix_hero_name` になります。

データのクエリ

さて、フィールド `name` と新しいインデックスを使用してデータをクエリするために、コードで特別なことをしたり、異なることをしたりする必要はありません。同じコードです。

SQLデータベースはそれを自動的に判断します。 ✨

これは、インデックスが非常に使い勝手が良いことを意味するため、素晴らしいことです。しかし、インデックスが役立つことを明確にするためにコードで明示的に何もしていないため、最初は直感に反するように感じるかもしれません。すべてが舞台裏のデータベースで行われます。

Python 3.10以降Python 3.7以降

# Code above omitted 👆

def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        for hero in results:
            print(hero)

# Code below omitted 👇

# Code above omitted 👆

def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        for hero in results:
            print(hero)

# Code below omitted 👇

👀 ファイル全体のプレビュー

Python 3.10以降Python 3.7以降

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

これは以前とまったく同じコードですが、データベースは内部でインデックスを使用するようになります。

プログラムの実行

プログラムを今実行すると、以下のような出力が表示されます。

$ python app.py

// Some boilerplate output omitted 😉

// Create the table
CREATE TABLE hero (
        id INTEGER,
        name VARCHAR NOT NULL,
        secret_name VARCHAR NOT NULL,
        age INTEGER,
        PRIMARY KEY (id)
)

// Create the index 🤓🎉
CREATE INDEX ix_hero_name ON hero (name)

// The SELECT with WHERE looks the same
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age
FROM hero
WHERE hero.name = ?
INFO Engine [no key 0.00014s] ('Deadpond',)

// The resulting hero
secret_name='Dive Wilson' age=None id=1 name='Deadpond'

インデックスを追加する

hero テーブルに対して age フィールドを比較するクエリを実行するため、このフィールドにもインデックスを定義する必要があります。

Python 3.10以降Python 3.7以降

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)

# Code below omitted 👇

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)

# Code below omitted 👇

👀 ファイル全体のプレビュー

Python 3.10以降Python 3.7以降

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
    hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
    hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
    hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
    hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)
        session.add(hero_4)
        session.add(hero_5)
        session.add(hero_6)
        session.add(hero_7)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.age <= 35)
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
    hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
    hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
    hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
    hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)
        session.add(hero_4)
        session.add(hero_5)
        session.add(hero_6)
        session.add(hero_7)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.age <= 35)
        results = session.exec(statement)
        for hero in results:
            print(hero)


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

この場合、age のデフォルト値は None のままである必要があるため、Field() を使用するときに default=None を設定します。

これで、SQLModel を使用してデータベースとテーブルを作成すると、hero テーブルのこれら2つの列のインデックスも作成されます。

そのため、hero テーブルに対してデータベースをクエリし、これらの2つの列を使用して取得するデータを定義すると、データベースはこれらのインデックスを使用して読み取りパフォーマンスを向上させることができます。 🚀

プライマリキーとインデックス

id フィールドに index=True を設定しなかったことに気付いたかもしれません。

id は既にプライマリキーであるため、データベースは自動的に内部インデックスを作成します。

データベースは、データの整理、保存、および取得の主要な方法であるため、常にプライマリキーの内部インデックスを自動的に作成します。 🤓

ただし、他のフィールド（たとえば、WHERE 句で他のフィールドを使用する）でSQLデータベースを頻繁にクエリする場合は、少なくともそのフィールドにインデックスを作成することをお勧めします。

まとめ

インデックスは、データベースをクエリする際の読み取りパフォーマンスと速度を向上させるために非常に重要です。 🏎

インデックスの作成と使用は非常に簡単です。最も重要な部分は、インデックスがどのように機能するのか、いつ作成するのか、そしてどの列に作成するのかを理解することです。