2011-11-22:OPTIMIZE TABLE の威力

mysql のお話。ストレージは innodb。
行数が多い上に、わりと挿入も削除もふだんから頻繁にしているテーブルがあった。
パフォーマンスが悪かったので OPTIMIZE TABLE してみたら、これがやたら威力あってびっくり。

使用前。

mysql> select count(id) from hogehoge;
+-----------+
| count(id) |
+-----------+
|    802776 | 
+-----------+
1 row in set (1 min 41.14 sec)

このテーブルに対して、OPTIMIZE TABLE hogehoge; したところ、

使用後。

mysql> select count(id) from hogehoge;
+-----------+
| count(id) |
+-----------+
|    802776 | 
+-----------+
1 row in set (0.27 sec)

なんとびっくり、101.14秒が0.27秒！
ただ、OPTIMIZE TABLE 自体には13分かかったけど。。。

2008-09-12:MySQL の default character set

とある、バックエンドデータベースに MySQL を使っているアプリを評価してて、はじめは test といったシングルバイト文字列でやっていたのだけど、ふと気づいて「てすと」と入れたら、見事に文字化け。
なんだろう、と思いながら、ソースを読みつつ、mysql でデータベースに接続して show create table <テーブル名>; してみたら、テーブルの DEFAULT CHARSET が latin1 だった。
「あー、これかぁ」と、alter table 文で変更をかける。

ALTER TABLE <テーブル名> default charset utf8;

が、まだだめ。文字化ける。
いくつか試行錯誤したあと、よくよく show create table の出力をよく見ると、

カラム単位に「character set latin1」が設定されてる

ことを発見。
MySQL はテーブル単位どころか、カラム単位でデフォルト文字コードが設定されるのか。すごいというか、なるほど、MySQL っぽい。見事なモジュラリティ？

というわけで、カラムごとにちまちま alter table をかけていく修行に。

ALTER TABLE <テーブル名> modify <カラム名> <データタイプ> charset utf8;

これ、システムカタログテーブルとかをどかっと update かけれたりしそうかなと予想するが、いかんせん MySQL の中身がよく分からず、今回は愛と勇気・努力と根性で乗り切る。

いいかげん、そろそろまじめに MySQL を勉強しよう。

2008-08-25:mysqldump の charset

当サイトをホスティングしているサーバをリプレイスしました（このために京都のデータセンターに出張だった）。
しばらく文字化けに苦しみましたが、現在は復旧しています。

文字化けの理由は、旧サーバが mysql 4.1 で、新サーバが 5.0 なんですが、この際のデータダンプ移行で文字コードにはまる、という

超ありがち

なもの。
mysqldump –all-databases で旧サーバでデータをとったのだけど、–default-character-set で binary が指定できず、ujis でしかダンプがとれなかったのが最初のつまづき。
その後、ダンプファイルを nkf でいろいろほげほげしてから新サーバでインポートして、mysql クライアントでつないだら、中身の日本語が見えたので、いったん安心してしまった。
文字化けが php の方かなと、php.ini の mbstring まわりのセッティングをみたり、Debian の /etc/apache2/conf.d/charset にはまってるのに気づいてなおしたり、とするが、ここの文字化けはなおらず。

結局、DB 内の文字コードがだめだったのが原因。
4.1 からダンプしたときのデータの CREATE TABLE 文に DEFAULT CHARSET がないのが問題でした。
5.0 に入ったデータを再度 mysqldump -default-character-set=binary で出力し、文字コードを utf8 に変えたあと、CREATE TABLE 文の DEFAULT CHARSET=latin1 を sed で DEFAULT CHARSET=utf8 としてからインポートしなおして解決しました。

やっぱり mysql は慣れてなくてつらい。。。

2008-06-30:PostgreSQLとMySQLのUPDATE構文

あるテーブルに UPDATE かけたくて、しかし対象行の検索には別のテーブルを結合したいビューを使いたい、という場合のオハナシ。

PostgreSQL の場合、UPDATE 文に FROM 句が使えて、ここに WHERE 句で使いたいテーブル・ビューを記述できる。
例を書くと、

UPDATE table_name as t
   SET target_column_name = value
  FROM view_name as v
 WHERE t.where_column_name = v.where_column_name
   and v.search_column_name = search_value;

のような形。

が、同じことを MySQL でやろうとしたら、怒られた。MySQL では FROM 句が使えない。
MySQL の場合は単純に UPDATE のターゲットテーブルと併記すればよい。

UPDATE table_name as t, view_name as v
   SET target_column_name = value
 WHERE t.where_column_name = v.where_column_name
   and v.search_column_name = search_value;

SQL92 標準的にどうなのかは知りません。だれか教えてください。:-)

そんなことより、今日 MySQL のマニュアル読んでてびっくりしたのは、UPDATE 文にORDER BY 句が使えること。指定した順で更新されていく。

なんの意味があるんだろう？

やっぱり MySQL は変態だなぁ、と思った（誉めてます）。