こんにちは!
スマレジ テックファームの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
カラムにインデックスを貼った場合、インデックスはid
とage
のカラムだけを保持し、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万件のレコードで解説しましたが、実際にパフォーマンスの改善を実感できるのは、数十万件~でしょう。
更新処理が重くなる
インデックスでは、テーブルとは別にデータを独自に保持しますので、テーブルにデータを追加するとインデックスの方にもデータが追加されます。結果として、データを追加・更新するときの処理が遅くなります。
まとめ
データベースのインデックスについて解説しました。ここまでお読みいただきありがとうございました。