EC-CUBEのmySQL用のSQLクエリーは、どうも表示速度にとっても問題を及ぼすようだ。。。

基本、PSQLメインの仕様だから、とか言われるのだけど、実際には、DBはMSQLしかないサーバーも多いし、色々運用のことも考えると、やっぱりMSQLでもそれなりのパフォーマンスはして欲しい･･･と思っているのは私だけではないはず。（＾＾；
いいシステムなのに、ところどころオシイんですよね。でも、最近ちょっと活発になってきてよくなってくる予感。

先日来フォーラムで、MSQLのパフォーマンスについて色々見ていたところ、あるスレッドで、

/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php　の商品データ呼び出しクエリーを変更すれば、パフォーマンスに劇的に変化がある、という話を目にし、早速やってみる。結果、PSQLのDBを使用した場合とほとんど遜色ないくらい速くなった！

要は簡単で、コミュニティ版で提供されているファイルに、SC_DB_DBFactory_MYSQL.phpを置き換えて、dtb_productsなどのDBテーブルをカスタマイズして携帯用のコメントなどを追加している場合は、そのカラムについてのSQLの値を追加すればいいだけ。（これは、カスタマイズしている場合だけね。）
ソース中のクエリーの中で、データベースにカスタマイズしてカラムを追加してある場合、dtb_productsに「comment7」を追加しているのであれば、comment6の行の下に同じ記述ルールでに、comment7を取得する記述を1行追加してあげるだけでいい。例えば、comment7の追加が必要な場所は、mein_comment（商品の詳細説明）が呼び出されている箇所（現在のソースなら下記2箇所）と同じところになるので、ソース内を検索してみるべし。

以下は、コミュニティ版で提供されているSC_DB_DBFactory_MYSQL.phpのソース（2/20現在）。多分、EC-CUBEのダウンロードサイトから最新の物を持ってきたほうがいいと思うし、そのうち正式版でも同じように改善されると思うけど、一応コピペしておく。

// {{{ requires
require_once(CLASS_PATH . “db/SC_DB_DBFactory.php”);

/**
* MySQL 固有の処理をするクラス.
*
* このクラスを直接インスタンス化しないこと.
* 必ず SC_DB_DBFactory クラスを経由してインスタンス化する.
* また, SC_DB_DBFactory クラスの関数を必ずオーバーライドしている必要がある.
*
* @package DB
* @author LOCKON CO.,LTD.
* @version $Id:SC_DB_DBFactory_MYSQL.php 15267 2007-08-09 12:31:52Z nanasess $
*/
class SC_DB_DBFactory_MYSQL extends SC_DB_DBFactory {

/**
* DBのバージョンを取得する.
*
* @param string $dsn データソース名
* @return string データベースのバージョン
*/
function sfGetDBVersion($dsn = “”) {
$objQuery = new SC_Query($this->getDSN($dsn), true, true);
list($db_type) = split(“:”, $dsn);
$val = $objQuery->getOne(“select version()”);
return “MySQL ” . $val;
}

/**
* MySQL 用の SQL 文に変更する.
*
* @access private
* @param string $sql SQL 文
* @return string MySQL 用に置換した SQL 文
*/
function sfChangeMySQL($sql){
// 改行、タブを1スペースに変換
$sql = preg_replace(“/[\r\n\t]/”,” “,$sql);
// view表をインラインビューに変換する
$sql = $this->sfChangeView($sql);
// ILIKE検索をLIKE検索に変換する
$sql = $this->sfChangeILIKE($sql);
// RANDOM()をRAND()に変換する
$sql = $this->sfChangeRANDOM($sql);
// TRUNCをTRUNCATEに変換する
$sql = $this->sfChangeTrunc($sql);
return $sql;
}

/**
* 文字コード情報を取得する
*
* @return array 文字コード情報
*/
function getCharSet() {
$objQuery = new SC_Query();
$arrRet = $objQuery->getAll(“SHOW VARIABLES LIKE ‘char%'”);
return $arrRet;
}

/**
* テーブルの存在チェックを行う SQL 文を返す.
*
* @return string テーブルの存在チェックを行う SQL 文
*/
function getTableExistsSql() {
return “SHOW TABLE STATUS LIKE ?”;
}

/**
* インデックスの検索結果を配列で返す.
*
* @param string $index_name インデックス名
* @param string $table_name テーブル名
* @return array インデックスの検索結果の配列
*/
function getTableIndex($index_name, $table_name = “”) {
$objQuery = new SC_Query(“”, true, true);
return $objQuery->getAll(“SHOW INDEX FROM ” . $table_name . ” WHERE Key_name = ?”,
array($index_name));
}

/**
* インデックスを作成する.
*
* @param string $index_name インデックス名
* @param string $table_name テーブル名
* @param string $col_name カラム名
* @param integer $length 作成するインデックスのバイト長
* @return void
*/
function createTableIndex($index_name, $table_name, $col_name, $length = 0) {
$objQuery = new SC_Query($dsn, true, true);
$objQuery->query(“CREATE INDEX ? ON ? (?(?))”, array($index_name, $table_name, $col_name, $length));
}

/**
* テーブルのカラム一覧を取得する.
*
* @param string $table_name テーブル名
* @return array テーブルのカラム一覧の配列
*/
function sfGetColumnList($table_name) {
$objQuery = new SC_Query();
$sql = “SHOW COLUMNS FROM ” . $table_name;
$arrColList = $objQuery->getAll($sql);
$arrColList = SC_Utils_Ex::sfswaparray($arrColList);
return $arrColList[“Field”];
}

/**
* テーブルを検索する.
*
* 引数に部分一致するテーブル名を配列で返す.
*
* @param string $expression 検索文字列
* @return array テーブル名の配列
*/
function findTableNames($expression = “”) {
$objQuery = new SC_Query();
$sql = “SHOW TABLES LIKE ?”;
$arrColList = $objQuery->getAll($sql, array(“%” . $expression . “%”));
$arrColList = SC_Utils_Ex::sfswaparray($arrColList, false);
return $arrColList[0];
}

/**
* View の WHERE 句を置換する.
*
* @param string $target 置換対象の文字列
* @param string $where 置換する文字列
* @param array $arrval WHERE 句の要素の配列
* @param string $option SQL 文の追加文字列
* @return string 置換後の SQL 文
*/
function sfViewWhere($target, $where = “”, $arrval = array(), $option = “”){

$arrWhere = split(“[?]”, $where);
$where_tmp = ” WHERE ” . $arrWhere[0];
for($i = 1; $i < count($arrWhere); $i++){
$where_tmp .= SC_Utils_Ex::sfQuoteSmart($arrval[$i – 1]) . $arrWhere[$i];
}
$arrWhere = $this->getWhereConverter();
$arrWhere[$target] = $where_tmp . ” ” . $option;
return $arrWhere[$target];
}

/**
* View をインラインビューに変換する.
*
* @access private
* @param string $sql SQL 文
* @return string インラインビューに変換した SQL 文
*/
function sfChangeView($sql){

$arrViewTmp = $this->viewToSubQuery();

// viewのwhereを変換
foreach($arrViewTmp as $key => $val){
$arrViewTmp[$key] = strtr($arrViewTmp[$key], $this->getWhereConverter());
}

// viewを変換
$changesql = strtr($sql, $arrViewTmp);

return $changesql;
}

/**
* ILIKE句 を LIKE句へ変換する.
*
* @access private
* @param string $sql SQL文
* @return string 変換後の SQL 文
*/
function sfChangeILIKE($sql){
$changesql = eregi_replace(“(ILIKE )”, “LIKE “, $sql);
return $changesql;
}

/**
* RANDOM() を RAND() に変換する.
*
* @access private
* @param string $sql SQL文
* @return string 変換後の SQL 文
*/
function sfChangeRANDOM($sql){
$changesql = eregi_replace(“( RANDOM)”, ” RAND”, $sql);
return $changesql;
}

/**
* TRUNC() を TRUNCATE() に変換する.
*
* @access private
* @param string $sql SQL文
* @return string 変換後の SQL 文
*/
function sfChangeTrunc($sql){
$changesql = eregi_replace(“( TRUNC)”, ” TRUNCATE”, $sql);
return $changesql;
}

/**
* WHERE 句置換用の配列を返す.
*
* @access private
* @return array WHERE 句置換用の配列
*/
function getWhereConverter() {
return array(
“&&crscls_where&&” => “”,
“&&crsprdcls_where&&” =>””,
“&&noncls_where&&” => “”,
“&&allcls_where&&” => “”,
“&&allclsdtl_where&&” => “”,
“&&prdcls_where&&” => “”,
“&&catcnt_where&&” => “”
);
}

/**
* View をサブクエリに変換するための配列を返す.
*
* @access private
* @return array View をサブクエリに変換するための配列
*/
function viewToSubQuery() {
$sql[‘vw_products_allclass_detail’] =<<< __EOS__
(
SELECT
dtb_products.product_id,
dtb_products.name,
dtb_products.deliv_fee,
dtb_products.sale_limit,
dtb_products.sale_unlimited,
dtb_products.maker_id,
dtb_products.rank,
dtb_products.status,
dtb_products.product_flag,
dtb_products.point_rate,
dtb_products.comment1,
dtb_products.comment2,
dtb_products.comment3,
dtb_products.comment4,
dtb_products.comment5,
dtb_products.comment6,　//例えば、携帯用コメントとして、dtb_productsに「comment7」を追加しているのであれば、この下の行に、「dtb_products.comment7,」と1行追加してあげる。
dtb_products.note,
dtb_products.file1,
dtb_products.file2,
dtb_products.file3,
dtb_products.file4,
dtb_products.file5,
dtb_products.file6,
dtb_products.main_list_comment,
dtb_products.main_list_image,
dtb_products.main_comment,
dtb_products.main_image,
dtb_products.main_large_image,
dtb_products.sub_title1,
dtb_products.sub_comment1,
dtb_products.sub_image1,
dtb_products.sub_large_image1,
dtb_products.sub_title2,
dtb_products.sub_comment2,
dtb_products.sub_image2,
dtb_products.sub_large_image2,
dtb_products.sub_title3,
dtb_products.sub_comment3,
dtb_products.sub_image3,
dtb_products.sub_large_image3,
dtb_products.sub_title4,
dtb_products.sub_comment4,
dtb_products.sub_image4,
dtb_products.sub_large_image4,
dtb_products.sub_title5,
dtb_products.sub_comment5,
dtb_products.sub_image5,
dtb_products.sub_large_image5,
dtb_products.sub_title6,
dtb_products.sub_comment6,
dtb_products.sub_image6,
dtb_products.sub_large_image6,
dtb_products.del_flg,
dtb_products.creator_id,
dtb_products.create_date,
dtb_products.update_date,
dtb_products.deliv_date_id,
T4.product_code_min,
T4.product_code_max,
T4.price01_min,
T4.price01_max,
T4.price02_min,
T4.price02_max,
T4.stock_min,
T4.stock_max,
T4.stock_unlimited_min,
T4.stock_unlimited_max,
T4.class_count
FROM
dtb_products
LEFT 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,
COUNT(*) as class_count
FROM dtb_products_class
GROUP BY product_id
) AS T4
ON dtb_products.product_id = T4.product_id
)
__EOS__;

return array(
“vw_cross_class” => ‘
(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 AS T1, dtb_classcategory AS T2 ) ‘,

“vw_cross_products_class” =>’
(SELECT T1.class_id1, T1.class_id2, T1.classcategory_id1, T1.classcategory_id2, T2.product_id,
T1.name1, T1.name2, T2.product_code, T2.stock, T2.price01, T2.price02, T1.rank1, T1.rank2
FROM (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 AS T1, dtb_classcategory AS T2 ) AS T1 LEFT JOIN dtb_products_class AS T2
ON T1.classcategory_id1 = T2.classcategory_id1 AND T1.classcategory_id2 = T2.classcategory_id2) ‘,

“vw_products_nonclass” => ‘
(SELECT
T1.product_id,
T1.name,
T1.deliv_fee,
T1.sale_limit,
T1.sale_unlimited,
T1.category_id,
T1.rank,
T1.status,
T1.product_flag,
T1.point_rate,
T1.comment1,
T1.comment2,
T1.comment3,
T1.comment4,
T1.comment5,
T1.comment6,　//例えば、携帯用コメントとして、dtb_productsに「comment7」を追加しているのであれば、この下の行に、「T1.comment7,」と1行追加してあげる。
T1.file1,
T1.file2,
T1.file3,
T1.file4,
T1.file5,
T1.file6,
T1.main_list_comment,
T1.main_list_image,
T1.main_comment,
T1.main_image,
T1.main_large_image,
T1.sub_title1,
T1.sub_comment1,
T1.sub_image1,
T1.sub_large_image1,
T1.sub_title2,
T1.sub_comment2,
T1.sub_image2,
T1.sub_large_image2,
T1.sub_title3,
T1.sub_comment3,
T1.sub_image3,
T1.sub_large_image3,
T1.sub_title4,
T1.sub_comment4,
T1.sub_image4,
T1.sub_large_image4,
T1.sub_title5,
T1.sub_comment5,
T1.sub_image5,
T1.sub_large_image5,
T1.sub_title6,
T1.sub_comment6,
T1.sub_image6,
T1.sub_large_image6,
T1.del_flg,
T1.creator_id,
T1.create_date,
T1.update_date,
T1.deliv_date_id,
T2.product_id_sub,
T2.product_code,
T2.price01,
T2.price02,
T2.stock,
T2.stock_unlimited,
T2.classcategory_id1,
T2.classcategory_id2
FROM (SELECT * FROM dtb_products &&noncls_where&&) AS T1 LEFT JOIN
(SELECT
product_id AS product_id_sub,
product_code,
price01,
price02,
stock,
stock_unlimited,
classcategory_id1,
classcategory_id2
FROM dtb_products_class WHERE classcategory_id1 = 0 AND classcategory_id2 = 0)
AS T2
ON T1.product_id = T2.product_id_sub) ‘,

“vw_products_allclass” => ”
(
SELECT
alldtl.*,
dtb_category.rank AS category_rank,
T2.category_id,
T2.rank AS product_rank
FROM
{$sql[‘vw_products_allclass_detail’]} AS alldtl
LEFT JOIN
dtb_product_categories AS T2
ON alldtl.product_id = T2.product_id
LEFT JOIN
dtb_category
ON T2.category_id = dtb_category.category_id
) “,

“vw_products_allclass_detail” => $sql[‘vw_products_allclass_detail’],

“vw_product_class” => ‘
(SELECT * FROM
(SELECT T3.product_class_id, T3.product_id AS product_id_sub, classcategory_id1, classcategory_id2,
T3.rank AS rank1, T4.rank AS rank2, T3.class_id AS class_id1, T4.class_id AS class_id2,
stock, price01, price02, stock_unlimited, product_code
FROM ( SELECT
T1.product_class_id,
T1.product_id,
classcategory_id1,
classcategory_id2,
T2.rank,
T2.class_id,
stock,
price01,
price02,
stock_unlimited,
product_code
FROM (dtb_products_class AS T1 LEFT JOIN dtb_classcategory AS T2
ON T1.classcategory_id1 = T2.classcategory_id))
AS T3 LEFT JOIN dtb_classcategory AS T4
ON T3.classcategory_id2 = T4.classcategory_id) AS T5 LEFT JOIN dtb_products AS T6
ON product_id_sub = T6.product_id) ‘,

“vw_category_count” => ‘
(SELECT T1.category_id, T1.category_name, T1.parent_category_id, T1.level, T1.rank, T2.product_count
FROM dtb_category AS T1 LEFT JOIN dtb_category_total_count AS T2
ON T1.category_id = T2.category_id) ‘
);
}
}
?>

ポスグレへ移行テストもしているけど、正直これだけ改善されたら、まあ、いいかな、MSQLのままでも、と思い始めている。。。

パフォーマンス改善のため、MSQLからPSQLへのデータベース移行を行う。

移行前　mySQL5.2　→　移行後　postgreSQL8.3　（※VER末番省略）

PSQLの8.3系はパフォーマンスは高いが、SQLの互換性で色々と問題が出ることもあるようなので、モジュールなどを使用する際は、注意すること。

移行の場合は、一旦同サーバ内に、サブドメインなどで別環境を作っておき、そちらでインストールしてから本番環境からDBを切り替えるのが望ましい。

【主な手順】

1. MYSQLのDBからphpMyAdminで、テーブルごとにCSVデータを書き出す。
2. （一応同じサーバ内に別環境をつくり）EC-CUBEをポスグレ使用でインストール
3. postgreSQL（以下PSQL）のDBのテーブル内のデータを全て空にする
4. テーブルごとに、先にMSQLからダウンロードしていたCSVデータをインポート（※携帯用コメントとショップ用コメントをカスタマイズ追加しているから、この際にPSQLのdtb_productsにcomment7とshop_comment1カラムを追加しておく。）
5. ビューを調整。
   ・vw_product_class
   ・vw_products_allclass
   ・vw_products_allclass_detail
   ・vw_products_nonclass
   の４つのビューに、カスタマイズで追加していたコメントカラム（comment7、shop_comment1　※ショップカラムは、表示しないので、allクラスとnonclassだけでもいいと思う)をそれぞれ追加して生成しなおす

では詳しい手順。

その１）

phpMyAdminを開いて、左のサイドメニューからテーブルを１つずつ選択。エクスポートタブメニューをクリックし、テーブルのエクスポートメニューで、

エクスポート：「MS Excel用CSVデータ」を選択。
Excelオプション：NULL/最初の1行目にフィールド名を追加する（チェックあり）/Excelの種類（Windows)
ファイルで保存する（チェック）/圧縮(無し）/エンコード（non)

で実行。

エンコードの変換有無は、移行する相互のDBの文字エンコードが違う場合、文字化けを防ぐために必要になるが、書き出してからテキストエディタなどで様子を見ながら変換したほうが安心な気がする。

結構な数のテーブルがあるので、PSQLにインポートの際間違えないようにファイル名に気をつけながら順番に繰り返す。

その２）

PSQLでEC-CUBEインストール用のDBを1つ新規作成し、そこへ、もとのMSQLのEC-CUBEと同じバージョンのEC-CUBEを新規インストールする。

以前のものと同じバージョンにあわせるのは、DBの安全移行のため。ただしテーブル構造などが変わっていないバージョンの変更であれば、新しいEC-CUBEでもかまわない。
別のEC-CUBEを使う場合は、DBだけでなくファイルの移行も色々気をつけないといけないから正直めんどい；問題も切り分けにくいのでまずは、普通に移行だけ先に行ったほうがいいように思う。

その３）

ここで一旦新しいEC-CUBEの基本動作確認がベター。サーバー側の別のエラーが出ている場合は修正しておくこと。問題なければ、PSQLの各テーブルのデータを一旦空にする。

phpPgAdminを開いて、左メニューから、スキーマ→public→テーブルをクリックして選択。テーブルの一覧がでるので、ページ下の方の「複数行の操作」メニューから、「すべて選択する」—>「空にする」を選んで実行ボタンをクリック。

これで、テーブルの構造は残るが値は消えた。

その４）

dtb_baseinfoを選択して、上のタブメニューからインポートを選ぶ。

フォーマット（CSV）/NULL文字を許可する（\n、NULL、殻の文字列・項目いずれもチェック）

先に保存していたMSQLのdtb_baseinfoのテーブルデータを選択して、インポートボタンをクリック。

これを、最後のテーブルまで繰り返す。

途中、インポートエラーが出たらメッセージごとにもとのMSQLデータを調整するなどして対応。カスタマイズして増やしたカラムに相当するカラムがPSQLのテーブルにない場合、エラーが出るので、その際は、該当するカラムをテーブルに追加しておく。（例えば、dtb_productsにcomment7などを追加していた場合、PSQLのインポートでは、そのデータを入れるカラムがまだないので、作成しておかなくてはいけない）

その５）

PSQLでは、VIEWを使用してテーブルから値をあらかじめ集めておくことで表示のパフォーマンスをあげている。上記のようなカスタマイズでMSQLのテーブルにカラムを追加していた場合、そのデータをEC-CUBEで表示させるならVIEWにも修正が必要になる。(例えば、上記携帯用コメントcomment7は、携帯ページで表示させるので、それを読み込んでいるVIEWにもデータがなくては表示エラーになってしまう）

今回は、

• comment7→携帯ページ用商品説明文を入力＆表示させている。
• shop_comment1→商品登録時に店舗のメモを格納しているが管理画面で確認するだけでフロント表示はしない。

となっているので、

• vw_product_class
• vw_products_allclass
• vw_products_allclass_detail
• vw_products_nonclass

の４つのビューに、それぞれ、値を足すことにする。

1. vw_product_classは、マスター的な役割をするので、comment7もshop_comment1も追加する
2. vw_products_allclassには、comment7のみ追加
3. vw_products_allclass_detailには、comment7のみ追加
4. vw_products_nonclassには、もshop_comment1も追加する

vw_product_class以外には、shop_comment1のほうが必要かどうかちょいとあいまい。なくてもいいかもしれない。（エラー出たときに足せば･･･）

やり方。まず、左メニューからビューのvw_product_classを選択。ページの右上のほうのタブメニューからエクスポートを選択。
まずは、ダウンロードを選んで、一旦ローカルにビューのデータをバックアップ。その後、表示でファイルを開く。L18あたりの、 t6.comment6,の後ろに、 t6.comment7, t6.shop_comment1,を追加。ソースをドラッグ&コピー。

phpPgAdminへ戻り、左メニューのビューをクリックして、表示されるビューの一覧から、vw_product_classのテーブル内の「破棄」ボタンをクリックして、vw_product_classビューを破棄する。

画面の上の方のデータベース名をクリック。
SQLタブをクリックして、フォームに修正後のソースを貼り付けて、実行。

これで、新しく修正されたビューが再生成される。「レコードが見つかりません」と表示されればok。残りのビューについても同様に繰り返す。

vw_products_allclassは、t5.comment3, の後ろにt5.comment7,を、t1.comment3, の後ろにt1.comment7,を追加。

vw_products_allclass_detailは、t3.comment3, の後ろにt3.comment7, を追加。

vw_products_nonclassは、 t1.comment6, の後ろに、t1.comment7, t1.shop_comment1, を追加。

これでOK。

最終テストを行うので、新EC-CUBEから、/data/install.php以外のデータを一旦削除し、本番のec-cubeのソースをこちらにコピーしてくる。これで、本番の環境と同じプログラムでDBのみPSQLの環境ができたことになるので、データが正しく移行されて、表示されているか確認。

動作に問題がなければ、本番のec-cubeの/data/install.phpの接続データベースをPSQLの情報に変更して、接続するDBを入れ替えて完了。テストの段階で、PHPソースなどを変更しなければならなかった場合は、本番にもそれらを反映する。

以上。書いてると長いけど、慎重にやれば、作業自体はそれほど難しくはない。