Michi's Tech Blog

一人前のWebエンジニアを目指して

SQL総復習③ インデックス

こんにちは!
スマレジ テックファームのMichiです!

今回は『SQL総復習③ インデックス」編です。

インデックスとは?

インデックスとは日本語に直すと目次・索引という意味で、データベースにおいては「テーブルの情報を高速に検索するための仕組み」という意味で利用されます。

例えば、図書館にはたくさんの本がありますが、一冊一冊を手で探すのは時間がかかります。そこで、図書館では本のタイトルや著者名などを索引(インデックス)にします。これにより、本のタイトルや著者名を検索すると、その本がどこにあるかがすぐに分かるようになります。

データベースでも同じように、インデックスを使うことで、データを高速に検索することができます。例えば、あるテーブルの中に「名前」というカラムがある場合、この「名前」をインデックス化することで、その名前を検索する際に、テーブル内の全てのデータを調べるのではなく、インデックスを参照することで効率的に検索できます。

使ってみる

下準備

MySQL準拠で解説します。

まず、サンプルとなるテーブルを作成します。以下のクエリで、主キー、苗字、名前、年齢をカラムに持つusersというテーブルを作成しました。

-- テーブル作成
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    last_name VARCHAR(255),
    first_name VARCHAR(255),
    age INT
);

このテーブルに1万件の検証用データを挿入します。さすがに1万件分のINSERT文を書くわけにはいかないので、Pythonを使って自動化します。

まず、100件の苗字と名前が記載されているExcelファイルを用意。(今回は「日本の苗字ランキング」のようなサイトからそのままコピペしました。)


次に、このExcelファイルからデータを吸い出してフルネームを生成し、データベースに登録するPythonスクリプトを作成します。 seeder.pyファイルを実行することで、苗字100件 × 名前100件 = 10,000件のレコードが出来上がります。

# modes.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Column
from sqlalchemy.types import Integer, String

Base = declarative_base()

# usersテーブルのモデル定義
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True)
    last_name = Column(String(255))
    first_name = Column(String(255))
    age = Column(Integer)
# seeder.py

import openpyxl
from sqlalchemy import create_engine, orm
from random import randint

from model import User

# Excelから名前リストを抽出
wb = openpyxl.load_workbook("names.xlsx")
sheet = wb["Sheet1"]
last_name_cells = sheet["B2:B101"]
first_name_cells = sheet["C2:C101"]

last_name_list = [cell[0].value for cell in last_name_cells]
first_name_list = [cell[0].value for cell in first_name_cells]


# SQLAlchemyでMySQLを操作し、1万件のレコードを挿入する
engine = create_engine("mysql+pymysql://username:password@localhost/sql_index[f:id:Michi_1090:20230611152626p:plain]")
SessionClass = orm.sessionmaker(engine)
session = SessionClass()

users = []
for last_name in last_name_list:
    for first_name in first_name_list:
        user = User(last_name=last_name, first_name=first_name, age=randint(0, 100))
        users.append(user)

session.add_all(users)
session.commit()

結果

インデックスなしで検索する

まず、インデックスなしで検索してみます。

-- 年齢が20歳のユーザーのみ取り出す
SELECT * FROM users WHERE age = 20;

結果

この検索結果を取り出すのにかかった時間は0.005秒でした。

インデックスを貼った状態で検索してみる

インデックスを貼るには、CREATE INDEX インデックス名 ON 対象テーブル(対象カラム)という文法で記述します。今回の例では次の通りです。

-- 年齢のカラムにインデックスを貼る
CREATE INDEX index_age ON users (age);

この状態で、先ほどと同じくSELECT * FROM users WHERE age = 20;を実行します。

結果

この検索結果を取り出すのにかかった時間は0.001秒です。インデックスなしの時と比べて、5倍早くなっているのが分かります。今回は1万件程度のレコードなので、体感できるほどのパフォーマンスアップではありませんが、これが10万件、100万件とレコード数が増えていった場合、また、検索条件がより複雑になった場合に威力を発揮します。

どうしてインデックスを使うと早くなるの?

インデックスの仕組みはかなり複雑ですので、ここではかなり簡略化してお伝えします。

デフォルトの状態では、レコードは主キーの昇順に並んでいますので、年齢に関しては並び方に規則はありません。

id last_name first_name age
1 佐藤 太郎 47
2 田中 次郎 31
3 鈴木 三郎 32
4 中村 四郎 54
5 伊藤 五郎 67
6 高橋 六郎 8
7 山本 七郎 23
8 小林 八郎 20
9 山田 九郎 44
10 木村 十郎 11

この場合、データベースは上からすべてのレコードを検索して、目的のデータが格納されているか調べます。数百万ものデータが格納されている場合に、頭から順に調べていくのは非常に効率が悪いです。

次に、ageカラムにインデックスを貼った場合、インデックスはidageのカラムだけを保持し、ageの昇順でデータを保持します。

id age
6 8
10 11
8 20
7 23
2 31
3 32
9 44
1 47
4 54
5 67

こうすると、age = 20となるレコードが大体どのあたりにあるのかの見当がつきやすくなります。また、ageの値が20を超えた時点で、以降はage = 20となることはないので、それより下のレコードを確認する必要がなくなります。

かなりザクっと話しましたが、これがインデックスの仕組みです。

インデックスの注意点

有能なインデックスですが、注意しなければならない点もあります。

レコード数が少ない場合は、あまり意味がない

途中でも少し述べましたが、レコード数が少ないテーブルに対してインデックスを貼ってもあまり意味がありません。例では1万件のレコードで解説しましたが、実際にパフォーマンスの改善を実感できるのは、数十万件~でしょう。

更新処理が重くなる

インデックスでは、テーブルとは別にデータを独自に保持しますので、テーブルにデータを追加するとインデックスの方にもデータが追加されます。結果として、データを追加・更新するときの処理が遅くなります。

まとめ

データベースのインデックスについて解説しました。ここまでお読みいただきありがとうございました。