永田順伸のブログ

注文管理関連のテーブルは、何年もサイトを運営していると何十万件というデータが保存されることになる。1つのテーブルに大量のデータが保管されると、新規データの登録時に時間がかかる。そこで、テーブルを分割するというアイデアが浮かんでくるが、テーブルの分割をプログラム側で対応すると、手間がかかりすぎるし、切り替え時にサーバを止めるタイミングも難しい。

実は、MySQLにはこのような状況に対応するためのMERGEテーブルというものがある。データが多くなったらMERGEテーブルとUNIONで既存のテーブルと新規のテーブルを1つのテーブルとして扱うようにすればいい。検索を実行するときは新旧テーブル全体を対象にして、データを登録するときは新しいテーブルを対象にできるなど、非常に都合がいい。テーブルの作り方を工夫すると連番も前のテーブルを自動的に引き継ぐのでさらに安心だ。

お客様のサーバで複数のテーブルを分割したが、osCommerceを稼働させたまま瞬時に作業を終わることができた。
ただし、準備に時間はかかった。

http://dev.mysql.com/doc/refman/4.1/ja/merge.html

osCommerceやZen Cartのバージョンによっては注文関連のテーブルにインデックスを設定するとパフォーマンスが改善される。
ただし、丁寧にすべてのカラムごとに１つのインデックスを設定すると、インデックスが効果的に機能しない。
「実践ハイパフォーマンスＭｙＳＱＬ」の６６ページに、「ＭｙＳＱＬでは、１つのクエリを実行するとき、１つのテーブルにつき１つのインデックスしか使用できないのである」と書かれている。とすると、WHERE句で指定される検索条件に使用されるカラムが何個もある場合は、せっかく設定したインデックスが無駄になるということになる。
こんなときは、複合インデックスにすると効果的。ＭｙＳＱＬでは、１つのインデックスに対して１５カラムまで１つのインデックスに設定できる。
注文関連テーブルの場合も、クエリを確認して、検索条件中のカラムを同一のインデックスに設定するといい。
phpMyAdminなどを使って、EXPLAINで分析しながら調整するとわかりやすい。
あるとき、トップページだけ時間がかかるので調べてみたら、ベストセラー（ランキング）を表示しているモジュールの中のクエリに時間がかかっていた。「WHERE」以降にはカラムがひとつあり、「ORDER BY」以降にもカラムがひとつあって、同じテーブルに属していたのでこの二つのカラムで複合インデックスを作ったら、2秒近くかかっていた処理が0.024秒に短縮された。

詳細は以下のＵＲＬで確認してください。

http://dev.mysql.com/doc/refman/4.1/ja/mysql-indexes.html

http://dev.mysql.com/doc/refman/4.1/ja/multiple-column-indexes.html

もう少しで2008年も終わろうという頃、
長年お世話になっているお客様から電話があった。
osCommerceがここ何週間か非常に重くて1日に何度も画面が表示できないことがあるという。
topコマンドで確認してみるとMySQLが常に一番上に居座っている状態。
アクセス数などを確認してみたがそちらは問題ない状況だった。
MySQLにslowlogを設定して確認してみたところ、ある処理に12秒もかかっていた。
問題のテーブルを確認するとインデックスがない。
インデックスを追加すると、負荷は半分ほど低下した。さらに処理速度を確認しながら別のテーブルにもインデックスを追加した。その後に、テーブルを最適化したり、MySQLの設定をチューニングしたら、見違えるほど高速になった。レンタルサーバには使用していないアプリが動作していることがある。このサーバでもTomcatが動作していて、メモリを無駄に使っていたのでそちらを停止して、MySQLに余分に割り当てた。
ハードを上位機種にするとそれだけでも高速になるので、このチューニングは無駄だと考えられるお客様が多いが、チューニングは必要だと実感した。
今回、問題になったのは注文関係のテーブルだ。
長年、osCommerceで営業を続け、ある程度の売上がコンスタントにあるサイトだったため、注文関連テーブルに保存されたデータ量が膨大になりとうとう限界がきたということだと思う。
考えてみれば当たり前のことだが、予想外な原因で驚いた。
(also_purchased_products.phpで商品を表示して、ページキャッシュを使用していないとさらに遅くなります。　2009/01/26追加)

MySQLのパフォーマンスチューニングは面白いですね。
作業のご依頼があれば喜んでお引き受けします。
詳細を教えていただければお見積もりいたします。
(何件かお問い合わせをいただいています。ありがとうございます。　2009/01/26追加)

というわけで、
今年もよろしお願いします。

Zencartの商品データをWordPressで利用するため、一旦ファイルに落として、文字コードを変換して使用することにした。
実は、MySQLには「create table 新しいテーブル名 like 元のテーブル名;」という構文があり、それを使ってコピーすることもできるらしいけど、MySQL上で文字コードを変換する方法を知らないのでこのままで行くことにした。

あとは、データをデータベースに読み込んで、ALTER TABLE 構文を使ってWordPressの投稿用テーブルになるよう修正する。負荷があまり大きくなければ定時更新処理を設定して連携のできあがりのはずだ。

CRONで定時に処理をすることで商品情報をコピーして、そのデータを使いWordPress側でコンテンツを作成することにした。しかし、文字化けが起こる。phpMyAdminでみるとすべて????のようになる。
nkfで文字コードもUTF8に変換しているし、mysqlには–default-character-set=utf8も設定している。DBもUTF8だし、テーブルもすべてUTF8。ほかに何があるかと調べること数時間。
mysqldumpで出力したときに、

/*!40101 SET NAMES ujis */;

こういうSQLも出力されている。一見コメント風なので、見逃していたが、これが有効なら、これ以降はEUCになってしまい、????のような文字化けになる。試しにsedを使い、

sed ’s/SET NAMES ujis/SET NAMES utf8/g’ data.sql.euc > data.sql.utf8

のように修正して再実行。結果は文字化けなしでDBにデータを流し込めた。