dtb_products_class の INDEX 追加

[nanasess]

商品一覧を表示する際、 dtb_products_class にあるカラムの MAX, MIN を取得している。

                    JOIN (
                        SELECT product_id,
                            MIN(product_code) AS product_code_min,
                            MAX(product_code) AS product_code_max,
                            MIN(price01) AS price01_min,
                            MAX(price01) AS price01_max,
                            MIN(price02) AS price02_min,
                            MAX(price02) AS price02_max,
                            MIN(stock) AS stock_min,
                            MAX(stock) AS stock_max,
                            MIN(stock_unlimited) AS stock_unlimited_min,
                            MAX(stock_unlimited) AS stock_unlimited_max,
                            MAX(point_rate) AS point_rate,
                            MAX(deliv_fee) AS deliv_fee,
                            COUNT(*) as class_count
                        FROM dtb_products_class
                        WHERE del_flg = 0 
                        GROUP BY product_id
                    ) AS T4

MAX() 及び、 MIN() で参照するカラムに INDEX を付与することで高速化が期待できる。

(INDEX が無いと、全件走査して最大値、最小値を求めるが、INDEX があれば、INDEX の最大値、最小値を参照するのみなので、非常に高速)

CREATE INDEX dtb_products_class_price01_idx ON dtb_products_class (price01);
CREATE INDEX dtb_products_class_price02_idx ON dtb_products_class (price02);
CREATE INDEX dtb_products_class_stock_idx ON dtb_products_class (stock);
CREATE INDEX dtb_products_class_stock_unlimited_idx ON dtb_products_class (stock_unlimited);
CREATE INDEX dtb_products_class_point_rate_idx ON dtb_products_class (point_rate);
CREATE INDEX dtb_products_class_deliv_fee_idx ON dtb_products_class (deliv_fee);
CREATE INDEX dtb_products_class_product_id_idx ON dtb_products_class (product_id);

関連チケット

• #2644 (SC_DB_DBFactory::alldtlSQL から class_count を削除)
• #2538 (商品一覧取得のDB処理で商品規格を全件処理している)

[nanasess]

大量の INDEX を張ると、ボトルネックになる場合もあるため注意が必要

[kim]

nanasess様 今回は、2.13.4に回させていただきますが、 Indexのみのため、マイナーの範囲とさせていただき実装のご検討をお願いいたします。

[Seasoft]

説明に記載のインデックスとクエリーの組み合わせでは、噛み合っていない気がします。

パフォーマンス改善はの状況は検証済みでしょうか?

[nanasess]

Seasoft への返信

パフォーマンス改善はの状況は検証済みでしょうか?

正確な検証は未実施です。(特にレンタルサーバー)

[Seasoft]

nanasess への返信

正確な検証は未実施です。(特にレンタルサーバー)

現況までの検証は EC-CUBE 経由で行っていますか? それとも、SQL で直接行っていますか?

もしも、前者でしたら、改善前後の状態を再現するのに必要な情報 (DB、テスト用のデータをどう用意するかなど) をお知らせいただけますと幸いです。

後者でしたら、SQL をご提供いただけますと幸いです。

[nanasess]

1年以上前に、試したものなので、正確な情報が残っておらず、申し訳ございません。

PostgreSQL で、 dtb_products_class に50万レコードほど INSERT して軽く検証した程度です。 MySQL は未検証です。 データの生成は test/createEcCubeData-v25.php を使用しました。

[Seasoft]

nanasess への返信

情報ありがとうございます。

偶然手元の PostgreSQL 開発環境の dtb_products_class が 505,000 行なので、直ぐに試すことができそうです。

商品一覧画面の読み込み時間を計る方法で宜しいでしょうか?

[Seasoft]

このチケットの後、r23422 で実装が変わっているため、当時と効果が違っているかもしれませんね。

SC_DB_DBFactory_PGSQL::alldtlSQL をざっくり削除して、比較する必要もありそうですね。

[nanasess]

そうですね。明確な記録がなく恐縮ですが、商品一覧画面より、管理画面→商品マスタの全件検索の方が、速度改善が顕著でした。

また、データ構成によっては dtb_maker.maker_id, dtb_products.maker_id もボトルネックになりうると思います。

[Seasoft]

SC_DB_DBFactory_PGSQL::alldtlSQL を外した上で、「管理画面→商品マスタの全件検索」で検証しましたところ、変化がありませんでした。(975ms → 980ms)

あくまで推測ですが、このチケットが発行された当時は、最新デベロッパー版の dtb_products_class_product_id_key に相当するインデックスが存在しなかったため、説明に記載のインデックスのうち dtb_products_class_product_id_idx が有意に働いたのではないかと思います。

恐らく、残る6つのインデックスは、利用されないと思います。

(上記が正しいとすれば) 現在の説明に記載の内容は #2538 で解決済みという事になろうかと思います。

[Seasoft]

現実装に対する高速化のアプローチとしましては、r23665 を提案いたします。(PostgreSQL 用です。)

検証用データの生成 test/createEcCubeData-v25.php

-define('PRODUCTS_VOLUME', 100);
+define('PRODUCTS_VOLUME', 5000);
-    var $delete = false;
+    var $delete = true;

検証用SQL (「商品管理＞商品マスター」画面が発行したもの)

SELECT product_id, name, main_list_image, status, product_code_min, product_code_max, price02_min, price02_max,
stock_min, stock_max, stock_unlimited_min, stock_unlimited_max, update_date FROM             (
SELECT
dtb_products.*
,dtb_maker.name AS maker_name
,(SELECT MIN(product_code) FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id ) AS product_code_min
,(SELECT MAX(product_code) FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id ) AS product_code_max
,(SELECT MIN(price01) FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id ) AS price01_min
,(SELECT MAX(price01) FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id ) AS price01_max
,(SELECT MIN(price02) FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id ) AS price02_min
,(SELECT MAX(price02) FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id ) AS price02_max
,(SELECT MIN(stock) FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id ) AS stock_min
,(SELECT MAX(stock) FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id ) AS stock_max
,(SELECT MIN(stock_unlimited) FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id ) AS stock_unlimited_min
,(SELECT MAX(stock_unlimited) FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id ) AS stock_unlimited_max
,(SELECT MAX(point_rate) FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id ) AS point_rate
,(SELECT MAX(deliv_fee) FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id ) AS deliv_fee
FROM dtb_products
LEFT JOIN dtb_maker
ON dtb_products.maker_id = dtb_maker.maker_id
WHERE EXISTS(SELECT * FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id )
) AS alldtl WHERE del_flg = 0  ORDER BY update_date DESC LIMIT 10 OFFSET 0

結果

概ね4～5倍となるようです。

インデックス追加前:
  コスト値: 470,610.04 (実測: 1,550 ms)

インデックス追加後:
  コスト値: 133,553.77 (実測: 300 ms)

やはり、nanasess 様のコメント の点が私も気になります。50.5万行で全インデックスの作成に8秒弱だったので、EC-CUBE の管理画面経由の操作ならば、あまり気にならない範囲という所感ですが、検証はできておりません。