MySQL の遅さをサブクエリの改善とコードの一部改良で手間暇かけずに解決する

EC-CUBE の本家に取り入れていただきました。 2.4.0 以降は下記の変更は不要になるかと思います。またチェンジセットにもありますが 4.1 では下記の変更は動きません。ご注意下さい。
http://svn.ec-cube.net/open_trac/ticket/436
EC-CUBE で MySQL が遅いというのは、以前からよくとりざたされています。最近では、 PHP 関係で有名なアシアルさんのブログ等でも述べられていました。
ECオープンソースのEC CUBEを調査してみました : アシアルブログ
http://blog.asial.co.jp/540
またこちらの工房でも確認をとっています。
「MySQL vs PostgreSQL」どっちがいいですか? | EC-CUBE工房
この問題について簡単な方法ではありますが、改善を行いましたので、報告させてもらいます。結果から記述しておきますと MySQL モードでの一覧の表示が 39.67s から 1.37s 程度まで早くなりました。
改善方法についてですが、 SC_DB_DBFactory_MYSQL.php#viewToSubQuery の変更を行い、 SQL にて改善を図ったうえで、 LC_Page_Products_List.php の内部で商品一覧部分のクエリ発行でかなり無駄になっている部分を改善しました。機能テストについては商品を 1000 件程度、一商品につき、規格を2つ。カテゴリについては3階層程度までもぐったものを1カテゴリにつき20商品程度にて振り分けています。上記にあげた工房のテストのものです。
 SQL変換部分の改善
SC_DB_DBFactory_MYSQL.php#viewToSubQuery の部分の改修を行いました。この部分は PostgreSQL モードではビューへの参照となっている部分をすべてサブクエリとして発行するコードです。
なぜそのようなことを行っているかというと EC-CUBE が当初想定していた MySQL のバージョンにて 4.x 以前はビュー機能がなかったためです(5.x 以降は搭載されました)。ただ、この部分での変換サブクエリでかなり無駄があるため、そのへんをスッキリさせました。
以下が変更部分です。 商品一覧の対象となる vw_products_allclass の value 部分を以下の SQL に変更して下さい。

(SELECT
                    pr.product_id
                    ,pl.product_code_min
                    ,pl.product_code_max
                    ,pl.price01_min
                    ,pl.price01_max
                    ,pl.price02_min
                    ,pl.price02_max
                    ,pl.stock_min
                    ,pl.stock_max
                    ,pl.stock_unlimited_min
                    ,pl.stock_unlimited_max
                    ,pr.del_flg
                    ,pr.status
                    ,pr.name
                    ,pr.comment1
                    ,pr.comment2
                    ,pr.comment3
                    ,pr.main_list_comment
                    ,pr.main_image
                    ,pr.main_list_image
                    ,pr.product_flag
                    ,pr.deliv_date_id
                    ,pr.sale_limit
                    ,pr.point_rate
                    ,pr.sale_unlimited
                    ,pr.create_date
                    ,pr.deliv_fee
                    ,pc.rank
                    ,cc.rank AS category_rank
                    ,cc.category_id
                FROM
                    (((SELECT
                        product_id as product_id_sub
                        ,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
                    FROM dtb_products_class GROUP BY product_id
                ) AS pl
                    LEFT JOIN dtb_products AS pr ON pl.product_id_sub = pr.product_id) LEFT JOIN dtb_product_categories AS pc ON pr.product_id = pc.product_id) LEFT JOIN dtb_category AS cc ON pc.category_id = cc.category_id) 


この SQL の改善によって弊社環境では、さきほどあげたテストデータでデフォルトの 39.67s から 4.09s 程度まで改善されました。また SHOW STATUS で確認したところ、問い合わせ数は 1/3 程度になりました。
 商品一覧部分の変更
上記の変更でかなり改善されてはいるのですが、もうあと一歩いってみましょう。商品一覧部分のコードを改善します。下記にソースコードを掲載しました。パッチをつくろうと思ったのですが、バージョンの関係かうまくいかなったので、コードを置いておきます。改造部分は、規格の取得をデフォルトでは商品一件ごとに全部とってきていたのですが、これを一括に取得するという方法に変更しています。
http://systemfriend.co.jp/files/public/blog/takahashi/LC_Page_Products_List.phps
.phps を .php にリネームして下さい。また公式のリビジョン 17506 のものを対象に行っていますので、バージョン等違う方はそれにあわせてください。
以上簡単になりましたが、改善についてです。ある程度のテストはしていますが、なにか問題等ありましたら、報告していただければ幸いです。ネットショップで EC-CUBE かつ MySQL を利用されている方のお役にたてばなにより:D

大量商品登録ツール

大量商品登録ツール