«

SQLインデックスで速度を検証する

2024 年 11 月 18 日 by marukor

シナリオ

ユーザーからの注文情報を取得すること想定

構成

SQLはMySQL使用しています。

テーブルは、usersテーブル、ordersテーブルの二つです。
テーブルの構成は以下です。

usersテーブル

ordersテーブルDESCRIBE orders;

検証用データ作成

まず検証用のデータを１００万件を追加します。

ユーザーのテストデータ（100万件の内、1000人分）

1000人分のユーザーを作成します。このデータは後で orders テーブルに関連付けます。


DELIMITER $$

CREATE PROCEDURE InsertUsers()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 1000 DO
        INSERT INTO users (name) VALUES (CONCAT('User_', i));
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

CALL InsertUsers();

注文のテストデータ（100万件）

users テーブルの各ユーザーにランダムで複数の注文を関連付けます。

DELIMITER $$

CREATE PROCEDURE InsertOrders()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE random_user INT;
    DECLARE random_quantity INT;
    WHILE i <= 1000000 DO
        SET random_user = FLOOR(1 + (RAND() * 1000)); -- ランダムなユーザーID (1～1000)
        SET random_quantity = FLOOR(1 + (RAND() * 100)); -- ランダムな数量 (1～100)
        INSERT INTO orders (product_name, quantity, user_id)
        VALUES (CONCAT('Product_', i), random_quantity, random_user);
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

CALL InsertOrders();

無事挿入できました。

速度検証

実行するSQLは以下です。

SELECT u.id AS user_id, u.name, SUM(o.quantity) AS total_quantity
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id
HAVING total_quantity > 1000;

quantity （注文数）の合計を求め、特定の条件を満たすレコードを絞り込みます。

※検証しやすいようにあえて、負荷の多い集計クエリを実行しています。

インデックス設定前

取得するのに4秒近くかかっています。これは遅すぎて実際に運用できないレベルです。

インデックス追加

結合に使用しているordersテーブルのuser_idにインデックスを追加していきます。

-- orders テーブルの user_id にインデックスを追加
CREATE INDEX idx_user_id ON orders(user_id);

orderテーブルのuser_idにインデックスを追加しましたが結果はほとんど変化ありませんでした。

調べてみると、SUM(o.quantity) を使った集計を行うため、quantity カラムにもインデックスを追加することで、集計処理が高速化する可能性があるらしいです。

なのでordersテーブルのquantityにインデックスを追加してみました。

少し早くなった。本当に少しだけだけど変化があったのでOKということにします。

まとめ

今回はインデックスの検証だったので、クエリを変えることはしませんでしたが、インデックスを追加することでの変化を見ることができたので、これでヨシとします。
SQLの速度を上げるには、インデックスだけでは限界がありそうですね。

タグ: ,

TrackBack