EC-CUBEをチューニング - MySQL：MySQLでもviewを使って8倍速にするカスタマイズ!!
こちらは2.4系の記事になります。3系でのチューニングの記事はコチラ
さて、だいぶ間が開いてしまいましたが、前回の続きで、EC-CUBEをMySQLで動かす際のチューニングの続きです。僕がやった施策の中ではこれが劇的に効果がありました。
今回は、MySQLでもviewを使うという物です。
データベースのviewとは、簡単い言うとSELECT文の結果をテーブルの様に扱える機能という事です。
EC-CUBEは、PostgreSQLで動かす場合は、商品情報のカテゴリや規格の情報をまとめた物をviewにして、商品一覧画面や商品詳細画面等で利用しています。
ところが、EC-CUBEをMySQLで動かす場合、MySQLのバージョンが4.1でも利用出来る様に作られているため、viewの部分をサブクエリにして動かしています。
この、ネストしたサブクエリからSELECTする場合、インデックスが使えないので、こんなに遅くなっていて、インデックス張っても意味が無い訳です。
バージョン5.0からはMySQLでもviewが使える様になっています。最近のレンタルサーバであれば5.0以上を導入しているところも多いので、バージョン5.0以上なら導入可能です。
8.25倍速くなった！
結果から言うと、/products/list.php*1 が8.25倍速くなりました！
実はちょこちょこ問題もあるので、微修正は必要ですが、そんな事も苦にならないくらいの激速っぷりです！！
体感的にも劇的な改善です。
一応ちょっと負荷を上げた状態でのabテストの結果も貼っておきます。
Document Path: /eccube244/html/products/list.php
Document Length: 90587 bytesConcurrency Level: 5
Time taken for tests: 13.157 seconds
Complete requests: 10
Failed requests: 0
Write errors: 0
Total transferred: 909540 bytes
HTML transferred: 905870 bytes
Requests per second: 0.76 [#/sec] (mean)
Time per request: 6578.478 [ms] (mean)
Time per request: 1315.696 [ms] (mean, across all concurrent requests)
Transfer rate: 67.51 [Kbytes/sec] receivedConnection Times (ms)
min mean[+/-sd] median max
Connect: 0 1 0.9 2 2
Processing: 1728 5980 3188.8 5578 13155
Waiting: 1323 4816 3472.3 4640 13145
Total: 1728 5981 3189.5 5580 13157
WARNING: The median and mean for the initial connection time are not within a normal deviation
These results are probably not that reliable.Percentage of the requests served within a certain time (ms)
50% 5580
66% 7296
75% 7300
80% 7530
90% 13157
95% 13157
98% 13157
99% 13157
100% 13157 (longest request)
で、実際にどうするかですが、ここからはクロスキューブからクリスマスプレゼントです。
EC-CUBEでMySQLの場合に処理を分けている箇所を変更する
/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.phpです。
ここを継承している
/data/class_extends/db/dbfactory/SC_DB_DBFactory_MYSQL_Ex.phpを以下の物に書き換えます。
<?php // {{{ requires require_once(CLASS_PATH . "db/dbfactory/SC_DB_DBFactory_MYSQL.php"); class SC_DB_DBFactory_MYSQL_Ex extends SC_DB_DBFactory_MYSQL { function viewToSubQuery() { return array( "vw_cross_class" => ' vw_cross_class', "vw_cross_products_class" =>' vw_cross_products_class ', "vw_products_nonclass" => ' vw_products_nonclass ', "vw_products_allclass" => " vw_products_allclass ", "vw_products_allclass_detail" => " vw_products_allclass_detail ", "vw_product_class" => ' vw_product_class ', "vw_category_count" => ' vw_category_count ' ); } } ?>
やっている事は、viewをサブクエリに置き換えている所を削除して、そのままviewを使う様にしています。
EC-CUBEで利用しているMySQLのデータベースにviewを追加する
次に、データベースにviewを追加します。
MySQLではviewのfrom部分にサブクエリは使えないので、PostgreSQLを使う場合とは少し違っています。
下記のSQLをダーっと流し込んで下さい。
-- -- ビュー用の構造 `vw_T2` -- CREATE VIEW `vw_T2` AS select `dtb_products_class`.`product_id` AS `product_id_sub`, `dtb_products_class`.`product_code` AS `product_code`, `dtb_products_class`.`price01` AS `price01`, `dtb_products_class`.`price02` AS `price02`, `dtb_products_class`.`stock` AS `stock`, `dtb_products_class`.`stock_unlimited` AS `stock_unlimited`, `dtb_products_class`.`classcategory_id1` AS `classcategory_id1`, `dtb_products_class`.`classcategory_id2` AS `classcategory_id2` from `dtb_products_class` where ((`dtb_products_class`.`classcategory_id1` = 0) and (`dtb_products_class`.`classcategory_id2` = 0)); -- -------------------------------------------------------- -- -- ビュー用の構造 `vw_T4` -- CREATE VIEW `vw_T4` AS select `dtb_products_class`.`product_id` AS `product_id`, min(`dtb_products_class`.`product_code`) AS `product_code_min`, max(`dtb_products_class`.`product_code`) AS `product_code_max`, min(`dtb_products_class`.`price01`) AS `price01_min`, max(`dtb_products_class`.`price01`) AS `price01_max`, min(`dtb_products_class`.`price02`) AS `price02_min`, max(`dtb_products_class`.`price02`) AS `price02_max`, min(`dtb_products_class`.`stock`) AS `stock_min`, max(`dtb_products_class`.`stock`) AS `stock_max`, min(`dtb_products_class`.`stock_unlimited`) AS `stock_unlimited_min`, max(`dtb_products_class`.`stock_unlimited`) AS `stock_unlimited_max`, count(0) AS `class_count` from `dtb_products_class` group by `dtb_products_class`.`product_id`; -- -------------------------------------------------------- -- -- ビュー用の構造 `vw_T5` -- CREATE VIEW `vw_T5` AS select `t1`.`product_class_id` AS `product_class_id`, `t1`.`product_id` AS `product_id_sub`, `t1`.`classcategory_id1` AS `classcategory_id1`, `t1`.`classcategory_id2` AS `classcategory_id2`, `t2`.`rank` AS `rank1`, `t4`.`rank` AS `rank2`, `t2`.`class_id` AS `class_id1`, `t4`.`class_id` AS `class_id2`, `t1`.`stock` AS `stock`, `t1`.`price01` AS `price01`, `t1`.`price02` AS `price02`, `t1`.`stock_unlimited` AS `stock_unlimited`, `t1`.`product_code` AS `product_code` from ((`dtb_products_class` `t1` left join `dtb_classcategory` `t2` on((`t1`.`classcategory_id1` = `t2`.`classcategory_id`))) left join `dtb_classcategory` `t4` on((`t1`.`classcategory_id2` = `t4`.`classcategory_id`))); -- -- ビュー用の構造 `vw_category_count` -- CREATE VIEW `vw_category_count` AS select `t1`.`category_id` AS `category_id`, `t1`.`category_name` AS `category_name`, `t1`.`parent_category_id` AS `parent_category_id`, `t1`.`level` AS `level`, `t1`.`rank` AS `rank`, `t2`.`product_count` AS `product_count` from (`dtb_category` `t1` left join `dtb_category_total_count` `t2` on((`t1`.`category_id` = `t2`.`category_id`))); -- -------------------------------------------------------- -- -- ビュー用の構造 `vw_cross_class` -- CREATE VIEW `vw_cross_class` AS select `t1`.`class_id` AS `class_id1`, `t2`.`class_id` AS `class_id2`, `t1`.`classcategory_id` AS `classcategory_id1`, `t2`.`classcategory_id` AS `classcategory_id2`, `t1`.`name` AS `name1`, `t2`.`name` AS `name2`, `t1`.`rank` AS `rank1`, `t2`.`rank` AS `rank2` from (`dtb_classcategory` `t1` join `dtb_classcategory` `t2`); -- -------------------------------------------------------- -- -- ビュー用の構造 `vw_cross_products_class` -- CREATE VIEW `vw_cross_products_class` AS select `t1`.`class_id1` AS `class_id1`, `t1`.`class_id2` AS `class_id2`, `t1`.`classcategory_id1` AS `classcategory_id1`, `t1`.`classcategory_id2` AS `classcategory_id2`, `t2`.`product_id` AS `product_id`, `t1`.`name1` AS `name1`, `t1`.`name2` AS `name2`, `t2`.`product_code` AS `product_code`, `t2`.`stock` AS `stock`, `t2`.`price01` AS `price01`, `t2`.`price02` AS `price02`, `t1`.`rank1` AS `rank1`, `t1`.`rank2` AS `rank2` from (`vw_cross_class` `t1` left join `dtb_products_class` `t2` on(((`t1`.`classcategory_id1` = `t2`.`classcategory_id1`) and (`t1`.`classcategory_id2` = `t2`.`classcategory_id2`)))); -- -------------------------------------------------------- -- -- ビュー用の構造 `vw_products_allclass_detail` -- CREATE VIEW `vw_products_allclass_detail` AS select `dtb_products`.`product_id` AS `product_id`, `dtb_products`.`name` AS `name`, `dtb_products`.`deliv_fee` AS `deliv_fee`, `dtb_products`.`sale_limit` AS `sale_limit`, `dtb_products`.`rank` AS `rank`, `dtb_products`.`status` AS `status`, `dtb_products`.`product_flag` AS `product_flag`, `dtb_products`.`point_rate` AS `point_rate`, `dtb_products`.`sale_unlimited` AS `sale_unlimited`, `dtb_products`.`comment1` AS `comment1`, `dtb_products`.`comment2` AS `comment2`, `dtb_products`.`comment3` AS `comment3`, `dtb_products`.`comment4` AS `comment4`, `dtb_products`.`comment5` AS `comment5`, `dtb_products`.`comment6` AS `comment6`, `dtb_products`.`note` AS `note`, `dtb_products`.`file1` AS `file1`, `dtb_products`.`file2` AS `file2`, `dtb_products`.`file3` AS `file3`, `dtb_products`.`file4` AS `file4`, `dtb_products`.`file5` AS `file5`, `dtb_products`.`file6` AS `file6`, `dtb_products`.`main_list_comment` AS `main_list_comment`, `dtb_products`.`main_list_image` AS `main_list_image`, `dtb_products`.`main_comment` AS `main_comment`, `dtb_products`.`main_image` AS `main_image`, `dtb_products`.`main_large_image` AS `main_large_image`, `dtb_products`.`sub_title1` AS `sub_title1`, `dtb_products`.`sub_comment1` AS `sub_comment1`, `dtb_products`.`sub_image1` AS `sub_image1`, `dtb_products`.`sub_large_image1` AS `sub_large_image1`, `dtb_products`.`sub_title2` AS `sub_title2`, `dtb_products`.`sub_comment2` AS `sub_comment2`, `dtb_products`.`sub_image2` AS `sub_image2`, `dtb_products`.`sub_large_image2` AS `sub_large_image2`, `dtb_products`.`sub_title3` AS `sub_title3`, `dtb_products`.`sub_comment3` AS `sub_comment3`, `dtb_products`.`sub_image3` AS `sub_image3`, `dtb_products`.`sub_large_image3` AS `sub_large_image3`, `dtb_products`.`sub_title4` AS `sub_title4`, `dtb_products`.`sub_comment4` AS `sub_comment4`, `dtb_products`.`sub_image4` AS `sub_image4`, `dtb_products`.`sub_large_image4` AS `sub_large_image4`, `dtb_products`.`sub_title5` AS `sub_title5`, `dtb_products`.`sub_comment5` AS `sub_comment5`, `dtb_products`.`sub_image5` AS `sub_image5`, `dtb_products`.`sub_large_image5` AS `sub_large_image5`, `dtb_products`.`sub_title6` AS `sub_title6`, `dtb_products`.`sub_comment6` AS `sub_comment6`, `dtb_products`.`sub_image6` AS `sub_image6`, `dtb_products`.`sub_large_image6` AS `sub_large_image6`, `dtb_products`.`del_flg` AS `del_flg`, `dtb_products`.`creator_id` AS `creator_id`, `dtb_products`.`create_date` AS `create_date`, `dtb_products`.`update_date` AS `update_date`, `dtb_products`.`deliv_date_id` AS `deliv_date_id`, `t4`.`product_code_min` AS `product_code_min`, `t4`.`product_code_max` AS `product_code_max`, `t4`.`price01_min` AS `price01_min`, `t4`.`price01_max` AS `price01_max`, `t4`.`price02_min` AS `price02_min`, `t4`.`price02_max` AS `price02_max`, `t4`.`stock_min` AS `stock_min`, `t4`.`stock_max` AS `stock_max`, `t4`.`stock_unlimited_min` AS `stock_unlimited_min`, `t4`.`stock_unlimited_max` AS `stock_unlimited_max`, `t4`.`class_count` AS `class_count` from (`dtb_products` left join `vw_T4` `t4` on((`dtb_products`.`product_id` = `t4`.`product_id`))); -- -------------------------------------------------------- -- -- ビュー用の構造 `vw_products_allclass` -- CREATE VIEW `vw_products_allclass` AS select `alldtl`.`product_id` AS `product_id`, `alldtl`.`name` AS `name`, `alldtl`.`deliv_fee` AS `deliv_fee`, `alldtl`.`sale_limit` AS `sale_limit`, `alldtl`.`rank` AS `rank`, `alldtl`.`status` AS `status`, `alldtl`.`product_flag` AS `product_flag`, `alldtl`.`point_rate` AS `point_rate`, `alldtl`.`sale_unlimited` AS `sale_unlimited`, `alldtl`.`comment1` AS `comment1`, `alldtl`.`comment2` AS `comment2`, `alldtl`.`comment3` AS `comment3`, `alldtl`.`comment4` AS `comment4`, `alldtl`.`comment5` AS `comment5`, `alldtl`.`comment6` AS `comment6`, `alldtl`.`note` AS `note`, `alldtl`.`file1` AS `file1`, `alldtl`.`file2` AS `file2`, `alldtl`.`file3` AS `file3`, `alldtl`.`file4` AS `file4`, `alldtl`.`file5` AS `file5`, `alldtl`.`file6` AS `file6`, `alldtl`.`main_list_comment` AS `main_list_comment`, `alldtl`.`main_list_image` AS `main_list_image`, `alldtl`.`main_comment` AS `main_comment`, `alldtl`.`main_image` AS `main_image`, `alldtl`.`main_large_image` AS `main_large_image`, `alldtl`.`sub_title1` AS `sub_title1`, `alldtl`.`sub_comment1` AS `sub_comment1`, `alldtl`.`sub_image1` AS `sub_image1`, `alldtl`.`sub_large_image1` AS `sub_large_image1`, `alldtl`.`sub_title2` AS `sub_title2`, `alldtl`.`sub_comment2` AS `sub_comment2`, `alldtl`.`sub_image2` AS `sub_image2`, `alldtl`.`sub_large_image2` AS `sub_large_image2`, `alldtl`.`sub_title3` AS `sub_title3`, `alldtl`.`sub_comment3` AS `sub_comment3`, `alldtl`.`sub_image3` AS `sub_image3`, `alldtl`.`sub_large_image3` AS `sub_large_image3`, `alldtl`.`sub_title4` AS `sub_title4`, `alldtl`.`sub_comment4` AS `sub_comment4`, `alldtl`.`sub_image4` AS `sub_image4`, `alldtl`.`sub_large_image4` AS `sub_large_image4`, `alldtl`.`sub_title5` AS `sub_title5`, `alldtl`.`sub_comment5` AS `sub_comment5`, `alldtl`.`sub_image5` AS `sub_image5`, `alldtl`.`sub_large_image5` AS `sub_large_image5`, `alldtl`.`sub_title6` AS `sub_title6`, `alldtl`.`sub_comment6` AS `sub_comment6`, `alldtl`.`sub_image6` AS `sub_image6`, `alldtl`.`sub_large_image6` AS `sub_large_image6`, `alldtl`.`del_flg` AS `del_flg`, `alldtl`.`creator_id` AS `creator_id`, `alldtl`.`create_date` AS `create_date`, `alldtl`.`update_date` AS `update_date`, `alldtl`.`deliv_date_id` AS `deliv_date_id`, `alldtl`.`product_code_min` AS `product_code_min`, `alldtl`.`product_code_max` AS `product_code_max`, `alldtl`.`price01_min` AS `price01_min`, `alldtl`.`price01_max` AS `price01_max`, `alldtl`.`price02_min` AS `price02_min`, `alldtl`.`price02_max` AS `price02_max`, `alldtl`.`stock_min` AS `stock_min`, `alldtl`.`stock_max` AS `stock_max`, `alldtl`.`stock_unlimited_min` AS `stock_unlimited_min`, `alldtl`.`stock_unlimited_max` AS `stock_unlimited_max`, `alldtl`.`class_count` AS `class_count`, `dtb_category`.`rank` AS `category_rank`, `t2`.`category_id` AS `category_id`, `t2`.`rank` AS `product_rank` from ((`vw_products_allclass_detail` `alldtl` left join `dtb_product_categories` `t2` on((`alldtl`.`product_id` = `t2`.`product_id`))) left join `dtb_category` on((`t2`.`category_id` = `dtb_category`.`category_id`))); -- -------------------------------------------------------- -- -- ビュー用の構造 `vw_products_nonclass` -- CREATE VIEW `vw_products_nonclass` AS select `t1`.`product_id` AS `product_id`, `t1`.`name` AS `name`, `t1`.`deliv_fee` AS `deliv_fee`, `t1`.`sale_limit` AS `sale_limit`, `t1`.`sale_unlimited` AS `sale_unlimited`, `t1`.`category_id` AS `category_id`, `t1`.`rank` AS `rank`, `t1`.`status` AS `status`, `t1`.`product_flag` AS `product_flag`, `t1`.`point_rate` AS `point_rate`, `t1`.`comment1` AS `comment1`, `t1`.`comment2` AS `comment2`, `t1`.`comment3` AS `comment3`, `t1`.`comment4` AS `comment4`, `t1`.`comment5` AS `comment5`, `t1`.`comment6` AS `comment6`, `t1`.`note` AS `note`, `t1`.`file1` AS `file1`, `t1`.`file2` AS `file2`, `t1`.`file3` AS `file3`, `t1`.`file4` AS `file4`, `t1`.`file5` AS `file5`, `t1`.`file6` AS `file6`, `t1`.`main_list_comment` AS `main_list_comment`, `t1`.`main_list_image` AS `main_list_image`, `t1`.`main_comment` AS `main_comment`, `t1`.`main_image` AS `main_image`, `t1`.`main_large_image` AS `main_large_image`, `t1`.`sub_title1` AS `sub_title1`, `t1`.`sub_comment1` AS `sub_comment1`, `t1`.`sub_image1` AS `sub_image1`, `t1`.`sub_large_image1` AS `sub_large_image1`, `t1`.`sub_title2` AS `sub_title2`, `t1`.`sub_comment2` AS `sub_comment2`, `t1`.`sub_image2` AS `sub_image2`, `t1`.`sub_large_image2` AS `sub_large_image2`, `t1`.`sub_title3` AS `sub_title3`, `t1`.`sub_comment3` AS `sub_comment3`, `t1`.`sub_image3` AS `sub_image3`, `t1`.`sub_large_image3` AS `sub_large_image3`, `t1`.`sub_title4` AS `sub_title4`, `t1`.`sub_comment4` AS `sub_comment4`, `t1`.`sub_image4` AS `sub_image4`, `t1`.`sub_large_image4` AS `sub_large_image4`, `t1`.`sub_title5` AS `sub_title5`, `t1`.`sub_comment5` AS `sub_comment5`, `t1`.`sub_image5` AS `sub_image5`, `t1`.`sub_large_image5` AS `sub_large_image5`, `t1`.`sub_title6` AS `sub_title6`, `t1`.`sub_comment6` AS `sub_comment6`, `t1`.`sub_image6` AS `sub_image6`, `t1`.`sub_large_image6` AS `sub_large_image6`, `t1`.`del_flg` AS `del_flg`, `t1`.`creator_id` AS `creator_id`, `t1`.`create_date` AS `create_date`, `t1`.`update_date` AS `update_date`, `t1`.`deliv_date_id` AS `deliv_date_id`, `t2`.`product_id_sub` AS `product_id_sub`, `t2`.`product_code` AS `product_code`, `t2`.`price01` AS `price01`, `t2`.`price02` AS `price02`, `t2`.`stock` AS `stock`, `t2`.`stock_unlimited` AS `stock_unlimited`, `t2`.`classcategory_id1` AS `classcategory_id1`, `t2`.`classcategory_id2` AS `classcategory_id2` from (`dtb_products` `t1` left join `vw_T2` `t2` on((`t1`.`product_id` = `t2`.`product_id_sub`))); -- -------------------------------------------------------- -- -- ビュー用の構造 `vw_product_class` -- CREATE VIEW `vw_product_class` AS select `t5`.`product_class_id` AS `product_class_id`, `t5`.`product_id_sub` AS `product_id_sub`, `t5`.`classcategory_id1` AS `classcategory_id1`, `t5`.`classcategory_id2` AS `classcategory_id2`, `t5`.`rank1` AS `rank1`, `t5`.`rank2` AS `rank2`, `t5`.`class_id1` AS `class_id1`, `t5`.`class_id2` AS `class_id2`, `t5`.`stock` AS `stock`, `t5`.`price01` AS `price01`, `t5`.`price02` AS `price02`, `t5`.`stock_unlimited` AS `stock_unlimited`, `t5`.`product_code` AS `product_code`, `t6`.`product_id` AS `product_id`, `t6`.`name` AS `name`, `t6`.`deliv_fee` AS `deliv_fee`, `t6`.`sale_limit` AS `sale_limit`, `t6`.`sale_unlimited` AS `sale_unlimited`, `t6`.`category_id` AS `category_id`, `t6`.`rank` AS `rank`, `t6`.`status` AS `status`, `t6`.`product_flag` AS `product_flag`, `t6`.`point_rate` AS `point_rate`, `t6`.`comment1` AS `comment1`, `t6`.`comment2` AS `comment2`, `t6`.`comment3` AS `comment3`, `t6`.`comment4` AS `comment4`, `t6`.`comment5` AS `comment5`, `t6`.`comment6` AS `comment6`, `t6`.`note` AS `note`, `t6`.`file1` AS `file1`, `t6`.`file2` AS `file2`, `t6`.`file3` AS `file3`, `t6`.`file4` AS `file4`, `t6`.`file5` AS `file5`, `t6`.`file6` AS `file6`, `t6`.`main_list_comment` AS `main_list_comment`, `t6`.`main_list_image` AS `main_list_image`, `t6`.`main_comment` AS `main_comment`, `t6`.`main_image` AS `main_image`, `t6`.`main_large_image` AS `main_large_image`, `t6`.`sub_title1` AS `sub_title1`, `t6`.`sub_comment1` AS `sub_comment1`, `t6`.`sub_image1` AS `sub_image1`, `t6`.`sub_large_image1` AS `sub_large_image1`, `t6`.`sub_title2` AS `sub_title2`, `t6`.`sub_comment2` AS `sub_comment2`, `t6`.`sub_image2` AS `sub_image2`, `t6`.`sub_large_image2` AS `sub_large_image2`, `t6`.`sub_title3` AS `sub_title3`, `t6`.`sub_comment3` AS `sub_comment3`, `t6`.`sub_image3` AS `sub_image3`, `t6`.`sub_large_image3` AS `sub_large_image3`, `t6`.`sub_title4` AS `sub_title4`, `t6`.`sub_comment4` AS `sub_comment4`, `t6`.`sub_image4` AS `sub_image4`, `t6`.`sub_large_image4` AS `sub_large_image4`, `t6`.`sub_title5` AS `sub_title5`, `t6`.`sub_comment5` AS `sub_comment5`, `t6`.`sub_image5` AS `sub_image5`, `t6`.`sub_large_image5` AS `sub_large_image5`, `t6`.`sub_title6` AS `sub_title6`, `t6`.`sub_comment6` AS `sub_comment6`, `t6`.`sub_image6` AS `sub_image6`, `t6`.`sub_large_image6` AS `sub_large_image6`, `t6`.`del_flg` AS `del_flg`, `t6`.`creator_id` AS `creator_id`, `t6`.`create_date` AS `create_date`, `t6`.`update_date` AS `update_date`, `t6`.`deliv_date_id` AS `deliv_date_id` from (`vw_T5` `t5` left join `dtb_products` `t6` on((`t5`.`product_id_sub` = `t6`.`product_id`))); -- --------------------------------------------------------
ちょっとゲップが出るSQLですね。
さあ、これでOKです。実際にApacheベンチとかで試してみて下さい。劇的に速度が改善されているハズです。
Merry Christmas ~
*1:全件一覧表示
