MySQL / 戯れ言

2008-12-08 Mon

■ MySQL の filesort プチテクニック [MySQL]

MySQL の filesort プチテクニック
http://d.hatena.ne.jp/kazuhooku/20081208/1228707040

ORDER BY で困った時にやってみよう。

2008-09-11 Thu

■ MySQL では、sysdate() ではなく、now()を使うのが無難かも [MySQL]

そもそも now() と sysdate() ではなにが違うのか？

11.5. 日付時刻関数
http://dev.mysql.com/doc/refman/5.1/ja/date-and-time-functions.html

によると

SYSDATE() は、それが実行された時間を戻します。これは NOW() の動作によって異なり、ステートメントが実行を開始する時間を示す定数時間を戻します。( ストアドルーチンまたはトリガ内で、NOW() はルーチンまたはトリガ文が実行を開始する時間を戻します。)

そのほか、 SET TIMESTAMP 文は NOW() によって戻された値に影響を及ぼしますが、SYSDATE() によって戻された値には影響しません。つまり、バイナリログのタイムスタンプ設定は、SYSDATE() の呼び出しには効果をもたらさないということになります。

なんのこっちゃ？って感じですが、そのすぐ下の例を見るとすこし分かった気になります。

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+
mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

2回目のSYSDATEは、SLEEP をいれた分だけ、遅れた時間が表示されます。

この違いは、パフォーマンスと、レプリケーションの挙動に影響を及ぼします。

例えば、where 句で SYSDATE() を使うと、レコード数の数だけ SYSDATE() が実行されるらしく、遅くなります。下記の例では、16000レコードのデータに対して SQL を実行した結果です。

select count(*) from event_master \
       where event_start_datetime < subdate (now(),1 );

select count(*) from event_master \
       where event_start_datetime  subdate (sysdate(),1 );

- NOW() 0.03～0.04 sec
- SYSDATE 0.08～0.09 sec

という結果になりました。安直に考えれば、sysdate の代わりに now を使うと、 2倍程度の高速化になることになります(大雑把すぎますが。逆に考えれば、16000回も実行してもこの程度しか差がないとも言えます)ともあれ、特別な理由がない限り、sysdate()よりも now() を使った方がよさそうです。

あとレプリケーションについてですが

そのほか、 SET TIMESTAMP 文は NOW() によって戻された値に影響を及ぼしますが、SYSDATE()によって戻された値には影響しません。

つまり sysdate() を使うと、レプリケーションでマスター側とスレーブ側とで、差異が生じるということを意味します。実際

5.4.1.6. レプリケーションとシステム機能
http://dev.mysql.com/doc/refman/5.1/ja/replication-features-functions.html

を読むと、そのあたりのことが書かれています。これを利用して、レプリケーションの遅延を計るぜ！ってことをやる方もおられるようです。

行ベースのロギングを使用している場合には、この点の心配はありません

の記載があるように、binlog_format を ROW にするとレプリケーションセーフになるようですが試してません。基本的に sysdate() じゃなきゃだめだ！ってことはないと思うので、

- パフォーマンス
- レプリケーション機能

を考慮して、自分でプログラムを書くときは、now() を、sysdate() で書いちゃったものをレプリケーション構成で動かしたい場合は、my.cnf に

[mysqld]
sysdate-is-now

を設定して、再起動。これが無難に思えます。こうすると、 sysdate() が now() として動作するので、プログラムの書き換えなしに、上記の対応が可能となります。

他にも、レプリケーションセーフでない(場合によっては、MySQLのバージョンに依存)関数が複数あります。事前に使用する MySQLのバージョンを確認し、マニュアルを参照しておいた方がよさそうです。

カテゴリ：MySQL

■ MySQL の filesort プチテクニック [MySQL]

■ MySQL では、sysdate() ではなく、now()を使うのが無難かも [MySQL]

■ MySQL管理ツール [MySQL]

■ MySQL TIPS [MySQL]

■ MySQL ノウハウ [MySQL]

■ MySQL Proxy [MySQL]

■ MySQL User Conference Japan 2007 [MySQL]

■ MySQL Conference & Expo 2007 [MySQL]

■ MySQL Multi-Master Replication Manager [MySQL]

■ phpmyedit [MySQL]

■ MySQL Performance Blog [MySQL]

■ MySQL の高速化 [MySQL]

■ 日本MySQLユーザ会 [MySQL]

■ MySQL でのトラブルシューティング [MySQL][Troubleshooting]

■ mysqldump で文字化けしないためのメモ [MySQL]

■ MySQL でのクラスタリング [MySQL]

■ MySQLバックアップ [MySQL][Backup]

■ MySQLのパフォーマンス向上 TIPS [MySQL]

■ MySQL High Availability clustering [MySQL]

■ Execute Query [MySQL][PostgreSQL][Tools]