LabEx
ログイン無料登録

SQLite インデックス最適化

SQLiteSQLiteBeginner
今すぐ練習

💡 このチュートリアルは英語版からAIによって翻訳されています。原文を確認するには、 ここをクリックしてください

はじめに

この実験（Lab）では、インデックスを使用して SQLite データベースのパフォーマンスを最適化する方法を学びます。クエリの速度を向上させるために、単一カラムのインデックスを作成し、実践的な応用と分析に焦点を当てます。また、クエリプランを分析し、冗長なインデックスを削除する方法も学びます。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/sort_data("Sort With ORDER BY") sqlite/SQLiteGroup -.-> sqlite/build_index("Create Single Index") sqlite/SQLiteGroup -.-> sqlite/clear_index("Drop Single Index") sqlite/SQLiteGroup -.-> sqlite/verify_table("Check Table Existence") subgraph Lab Skills sqlite/query_where -.-> lab-552552{{"SQLite インデックス最適化"}} sqlite/sort_data -.-> lab-552552{{"SQLite インデックス最適化"}} sqlite/build_index -.-> lab-552552{{"SQLite インデックス最適化"}} sqlite/clear_index -.-> lab-552552{{"SQLite インデックス最適化"}} sqlite/verify_table -.-> lab-552552{{"SQLite インデックス最適化"}} end

データベースとテーブルの作成

このステップでは、SQLite データベースと employees テーブルを作成します。その後、いくつかのサンプルデータをテーブルに挿入します。

まず、LabEx VM でターミナルを開きます。デフォルトのパスは /home/labex/project です。

my_database.db という名前の SQLite データベースを作成するには、次のコマンドを実行します。

sqlite3 my_database.db

このコマンドは、プロジェクトディレクトリに my_database.db という名前の新しい SQLite データベースファイルを作成し、SQLite シェルを開きます。

次に、次の構造で employees テーブルを作成します。

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    department TEXT
);

この SQL ステートメントは、employees という名前のテーブルを、id、first_name、last_name、email、および department の 5 つのカラムで作成します。id カラムは主キー（PRIMARY KEY）として設定されており、一意の値を含める必要があります。

次に、いくつかのサンプルデータを employees テーブルに挿入します。

INSERT INTO employees (first_name, last_name, email, department) VALUES
('John', 'Doe', '[email protected]', 'Sales'),
('Jane', 'Smith', '[email protected]', 'Marketing'),
('Robert', 'Jones', '[email protected]', 'Engineering'),
('Emily', 'Brown', '[email protected]', 'Sales'),
('Michael', 'Davis', '[email protected]', 'Marketing');

これにより、employees テーブルに 5 行のデータが挿入されます。

データが正しく挿入されたことを確認するには、次のコマンドを実行します。

SELECT * FROM employees;

次の出力が表示されるはずです。

1|John|Doe|[email protected]|Sales
2|Jane|Smith|[email protected]|Marketing
3|Robert|Jones|[email protected]|Engineering
4|Emily|Brown|[email protected]|Sales
5|Michael|Davis|[email protected]|Marketing

インデックスの作成

このステップでは、employees テーブルの last_name カラムにインデックスを作成します。

インデックスは、データベース検索エンジンがデータ検索を高速化するために使用できる特別なルックアップテーブルです。

last_name カラムに idx_lastname という名前のインデックスを作成するには、次のコマンドを実行します。

CREATE INDEX idx_lastname ON employees (last_name);

この SQL ステートメントは、employees テーブルの last_name カラムに idx_lastname という名前のインデックスを作成します。

インデックスが作成されたことを確認するには、次のコマンドを使用できます。

PRAGMA index_list(employees);

このコマンドは、作成した idx_lastname インデックスを含む、employees テーブルのインデックスのリストを表示します。次のような出力が表示されるはずです。

0|idx_lastname|0|c|0

この出力は、employees テーブルに idx_lastname インデックスが存在することを確認します。

EXPLAIN QUERY PLAN によるクエリの分析

このステップでは、EXPLAIN QUERY PLAN コマンドを使用して、SQLite がクエリをどのように実行するかを分析する方法を学びます。これは、クエリのパフォーマンスを理解し、潜在的なボトルネックを特定するための強力なツールです。

クエリを分析するには、その前に EXPLAIN QUERY PLAN を付けます。たとえば、次のクエリを分析するには：

SELECT * FROM employees WHERE last_name = 'Smith';

次のコマンドを実行します。

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE last_name = 'Smith';

出力は次のようになります。

QUERY PLAN
`--SEARCH employees USING INDEX idx_lastname (last_name=?)

この出力は、SQLite が idx_lastname インデックスを使用して、姓（last name）が 'Smith' の従業員を検索していることを示しています。SEARCH キーワードは、SQLite がインデックスを使用して検索を実行していることを示します。

インデックスが使用されていない場合、出力は異なります。たとえば、名（first name）が 'John' の従業員をクエリする場合（そして、first_name カラムにインデックスを作成していない場合）、出力は次のようになります。

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE first_name = 'John';

出力は次のようになります。

QUERY PLAN
`--SCAN employees

SCAN キーワードは、SQLite がフルテーブルスキャン（full table scan）を実行していることを示しています。これは、名が 'John' の従業員を見つけるために、テーブル内のすべての行を調べる必要があることを意味します。これは、インデックスを使用するよりも効率が劣ります。

データの追加とソートの分析

クエリプランの分析をより意味のあるものにするために、さらにデータを挿入しましょう。次のデータを employees テーブルに挿入します。

INSERT INTO employees (first_name, last_name, email, department) VALUES
('Alice', 'Johnson', '[email protected]', 'HR'),
('Bob', 'Williams', '[email protected]', 'Finance'),
('Charlie', 'Brown', '[email protected]', 'IT'),
('David', 'Miller', '[email protected]', 'Sales'),
('Eve', 'Wilson', '[email protected]', 'Marketing'),
('John', 'Taylor', '[email protected]', 'Engineering'),
('Jane', 'Anderson', '[email protected]', 'HR'),
('Robert', 'Thomas', '[email protected]', 'Finance'),
('Emily', 'Jackson', '[email protected]', 'IT'),
('Michael', 'White', '[email protected]', 'Sales');

次に、ソートを含む、より複雑なクエリを分析してみましょう。たとえば、「Sales」部署のすべての従業員を検索し、姓（last name）でソートするとします。次のクエリを使用できます。

SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

クエリプランを分析します。

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

出力は次のようになる可能性があります。

QUERY PLAN
`--SCAN employees USING INDEX idx_lastname

この場合、SQLite はフルテーブルスキャン（full table scan）を実行し、その後、結果をソートしています。

department カラムにインデックスを作成しましょう。

CREATE INDEX idx_department ON employees (department);

ここで、再度クエリプランを分析します。

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

出力は次のように変わる可能性があります。

QUERY PLAN
|--SEARCH employees USING INDEX idx_department (department=?)
`--USE TEMP B-TREE FOR ORDER BY

現在、SQLite は idx_department インデックスを使用して「Sales」部署の従業員を検索していますが、結果をソートする必要があります。

冗長なインデックスの削除

このステップでは、SQLite で冗長なインデックスを特定して削除する方法を学びます。冗長なインデックスは、読み取り操作に何のメリットももたらさずに、書き込み操作のオーバーヘッドを増やすことで、データベースのパフォーマンスに悪影響を与える可能性があります。

まず、department と last_name カラムの両方にインデックスを作成しましょう。

CREATE INDEX idx_department_lastname ON employees (department, last_name);

次に、employees テーブルのすべてのインデックスをリストします。

PRAGMA index_list(employees);

次のような出力が表示されるはずです。

0|idx_lastname|0|c|0
1|idx_department|0|c|0
2|idx_department_lastname|0|c|0

次に、department と last_name でフィルタリングするクエリを分析してみましょう。

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' AND last_name = 'Doe';

出力は次のようになる可能性があります。

QUERY PLAN
`--SEARCH employees USING INDEX idx_department_lastname (department=? AND last_name=?)

この出力は、SQLite がこのクエリに idx_department_lastname インデックスを使用していることを示しています。

次に、department のみでフィルタリングするクエリを分析してみましょう。

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales';

出力は次のようになる可能性があります。

QUERY PLAN
`--SEARCH employees USING INDEX idx_department (department=?)

この出力は、SQLite がこのクエリに idx_department インデックスを使用していることを示しています。

このシナリオでは、idx_department_lastname インデックスは冗長です。なぜなら、idx_department インデックスは department のみでフィルタリングするクエリに使用できるからです。idx_department_lastname インデックスは、department と last_name の両方でフィルタリングするクエリに対してのみメリットを提供します。

冗長な idx_department インデックスを削除するには、DROP INDEX コマンドを使用できます。

DROP INDEX idx_department;

ここで、employees テーブルのすべてのインデックスを再度リストします。

PRAGMA index_list(employees);

idx_department インデックスがリストに表示されなくなったことがわかります。

まとめ

この実験（Lab）では、インデックスを使用して SQLite データベースのパフォーマンスを最適化する方法を学びました。クエリの速度を向上させるために単一カラムインデックス（single-column index）を作成し、EXPLAIN QUERY PLAN を使用してクエリプランを分析し、冗長なインデックスを削除しました。これらのスキルは、より効率的で応答性の高い SQLite データベースを構築するのに役立ちます。