MySQL | てくめも@ecoop.net

MySQLでmysql.slow_logテーブルをslow queryログファイル形式でダンプする

表題のとおり、スロークエリログをテーブルに書き出しているMySQL DBサーバで
slow queryログファイルの形式でエクスポートしたくなったのでその方法をメモしておきます。

参考:Exporting mysqlslowlog table slow query log format.

# コマンドここから
mysql -u “ユーザ名” -p -h “接続先ホスト” -D mysql -s -r -e “SELECT CONCAT( ‘# Time: ‘, DATE_FORMAT(start_time, ‘%y%m%d %H%i%s’), ‘\n’, ‘# User@Host: ‘, user_host, ‘\n’, ‘# Query_time: ‘, TIME_TO_SEC(query_time), ‘ Lock_time: ‘, TIME_TO_SEC(lock_time), ‘ Rows_sent: ‘, rows_sent, ‘ Rows_examined: ‘, rows_examined, ‘\n’, sql_text, ‘;’ ) FROM mysql.slow_log” > /tmp/mysql.slow_log.log
# コマンドここまで

長くて読みづらいですが、mysqlコマンドで、slow_query_logファイル形式にあわせるようにSELECTを打って整形しています。

普段はSQLで処理できるテーブルのほうが便利ですが、Percona Toolkitのpt-query-digestのように、スロークエリログをファイルとして処理するようなツールを使うためにファイルが欲しくなることもあるのですよね。

Percona Toolkitについてはこちらの記事が参考になりました。

NULL は「値がない」のではなく「値が不明」

SQL では NULL を含む演算は IS NULL, IS NOT NULL と一部の論理演算を除き常に戻り値が NULL になります。
この結果として、NULL を値に持つレコードを IS NULL 以外で抽出できなくなります。
たとえば極端な例ですが

SELECT NULL != NULL

が true にならず NULL になったりします(つまり (NULL != NULL) IS NULL が真になる)。

この仕様は Java や PHP などのプログラミング言語から考えると不可解なものにうつりますが、 NULL は値が存在しないのではなくまだわかっていない(unknown)だけなのだ、と考えるとスッキリします。

値段のカラムに null を持つ商品について考えるとき、その商品は「無料」ではなく値段がまだ「決まっていない」だけ。携帯のメールアドレスが null のユーザがいたなら、その人は携帯電話を「持っていない」という意味ではなく、その人が携帯電話を持っているか「わからない」だけです。

指定した部署(department_id)以外にいる社員(employee)の名前を探す下記のようなクエリを実行しても、部署が null の社員の名前は含まれません。その社員が 1 の部署に配属されるかもしれないからです。

SELECT name FROM employee WHERE department_id != 1

null 値も結果に含めたい場合は下記のようにしなければなりません。

SELECT name FROM employee WHERE department_id != 1 AND department_id IS NULL

0 や に包括的なからっぽ(empty)という意味を持たせたるのも伝統的でいいですが、SQL では 0, と NULL を区別して有効に使うほうがよさそうです。

参考:
3値論理とNULL(CodeZine)

SQL の基礎 – JOIN (表結合)の意味の違い

MySQL / PostgreSQL / Oracle すべてのSQLサーバで基準になっているSQL標準のJOIN(LEFT JOINなど)について、基礎からしっかりまとめてみました。
続きを読む SQL の基礎 – JOIN (表結合)の意味の違い

MySQL の公式 GUI 管理ツール

MySQL GUI Tools Downloads(MySQL AB)にある、MySQL Administrator が便利なのでお勧めです。

Windows 版 ver. 5.0-r10 の直ダウンロード

接続方法:
– インストール後、スタートメニューから MySQL -> MySQL Administrator を実行する
– Stored Connection の … をクリック
– 左の General Options を選択(パスワードを保存したくない場合これは不要)
  – Store Passwords にチェック
  – Password storage method を Obscured にしておく
  – Apply ボタンをクリックして設定を保存
  – 左の Connections をクリックして戻る
– 下の new Connection ボタン
   Connection: 分かり易い名前(例: ユーザ名@ホスト名)
   ユーザーネーム: ログイン名(例: foo)
   パスワード: ログインパスワード(例: pass)
   Hostname: 接続先ホスト名(例1: www.example.com , 例2: 192.168.0.10)
   ポート: 3306
   Schema: 接続先DB名
   以下デフォルト。
– Apply ボタンで設定を保存、Close ボタンで閉じる
– Stored Connection のコンボボックスで、2で作った接続を選択
– OK ボタンで接続

接続後、カタログから、データベース(このツールでは Schema と呼ばれる)の操作や、テーブルの作成、編集ができます。

テーブルの中身を閲覧したい場合は、クエリブラウザを使うと便利です。
クエリブラウザを実行するには、MySQL Administrator の Tools メニューから MySQL Query Browser を選択します。

MySQL Query Browser の使い方は大体直感でわかると思いますが、上部のテキストエリアに

SELECT * FROM db1.footable;

のように SQL クエリをいれ、Execute ボタンを押せばクエリが実行され、かねやんMySQLAdmin などのように結果が一覧表示されます。
また、右の Schema からテーブルをドラッグ、上のテキストエリアにドロップすれば、そのテーブルの SELECT クエリが自動生成されます。

MySQL Administrator のその他の機能や使い方についてはこちらを参照してください。
【MySQLウォッチ】第6回管理者向け新GUIツールMySQL Administratorを検証

最後に挿入したクエリの ID を取得する

PostgreSQL 8.1 になって、
MySQL における LAST_INSERT_ID() に似た機能をもつ、LastVal() がサポートされるようになりました。

MySQL:
CREATE TABLE employee (
id int unsigned auto_increment not null primary key,
name varchar(128) not null
);

INSERT INTO employee (name) VALUES (‘John’);
SELECT LAST_INSERT_ID();

と、次のものは全く同等の意味になります。

PostgreSQL:
CREATE TABLE employee (
id serial not null primary key,
name varchar(128) not null
);

INSERT INTO employee (name) VALUES (‘John’);
SELECT LASTVAL();

LASTVAL() はシーケンスのインクリメント関数である NEXTVAL() が一番最後に返した値を返すものです。

PostgreSQL の LASTVAL() は MySQL の LAST_INSERT_ID() と同様に、
他のセッションでの NEXTVAL()の影響は受けません。

一度も自分のセッションで NEXTVAL() を呼び出していない場合は、”ERROR: lastval is not yet defined in this session”というエラーが発生します。

参考:
PostgreSQL を MySQL 互換にする(Open Tech Press)
シーケンス操作関数(PostgreSQL 8.1.0 日本語ドキュメント)

MySQLコーディング標準

MySQL は Java やその他の言語みたいにコーディングの規則が決まっていません。
決まっていないとバラバラになって困ったりするのでずっと探しているのですが、いっこうに見付からないため、それなりの理由があって実践している命名規則についてまとめがわりにメモしておきます。
– テーブル名、カラム名は全て小文字にし、単語はアンダースコア(_)で区切る。

user_detail.name

– カラム型とその修飾子は小文字にする。

int, text, unsigned, auto_increment, default

– その他の SQL の識別子(SELECT, UPDATE, INSERT, WHERE, IS, AS, NOT, NULL など)は全て大文字にする。

SELECT COUNT(*) FROM foo WHERE name = ‘John’;

テーブル名やカラムを小文字とアンダースコアの組合せに統一するのは、プラットフォームによって非互換になるのを防ぐというシンプルな理由からです。
また、その他については SHOW CREATE TABLE クエリや、mysqldump で出力されるクエリの記法に則っています。テーブル名やカラムを大文字でなく小文字にすることで、MySQL のシグネチャと、カラム,テーブル名との区別がつけられます。

参考:
– MySQL naming standards(thescripts.com フォーラム)

databases/mysql41-* から databases/mysql50-* へ移行

MySQL4.1 を使っていたのだが、MySQL5.0 がリリースされて久しいので置き換えました。
データベースの中身をダンプしてから置き換えて、ダンプから復元。

$ # あとで復元するためにデータをダンプ
$ mysqldump -AicFxp -u root –create-options > mysql.dump
$ su
# # 4.1 の停止処理
# /usr/local/etc/rc.d/mysql-server.sh stop
# mv /var/db/mysql{,_old}; mkdir /var/db/mysql # 一応退避。終われば古いものは不要になります。
# # 4.1 -> 5.0 へのバージョンアップ
# portupgrade -o databases/mysql50-client “mysql-client-4.1.16_1”
# portupgrade -o databases/mysql50-script “mysql-script-4.1.16”
# portupgrade -o databases/mysql50-server “mysql-server-4.1.16”
# portupgrade -fo databases/p5-DBD-mysql50 p5-DBD-mysql41-3.0002
# pkgdb -F # 念のため依存関係確認。

# # DB の初期化
# mysql_install_db –log-bin=hostname-bin # hostname-bin のところは任意の名前。
# # サーバを起動してここで復元。
# /usr/local/etc/rc.d/mysql-server.sh start
# mysql < mysql.dump # 復元
# /usr/local/etc/rc.d/mysql-server.sh restart # 変更の適用

参考:
– MySQL 5.0 で一挙に増加さまざまな特徴を持つストレージエンジン(日経BP ITPro)
直接関係ないけど、5.0 の新機能について書いてあります。

テーブルロックの話

テーブルロックをうまく使うと、
・MyISAM でのパフォーマンス向上
・MyISAM で(InnoDB を使わずに)簡易的にトランザクションの一部機能を実現できる
といううまみがある。(InnoDB, BDB だと逆効果になりうる。

テーブルロックの基本的なクエリは次のとおり。

– 読み込みロック

LOCK TABLES foo READ

テーブルの読み込みロッククエリを実行すると、ロック中は現在の接続(と同時に実行中の他の全ての接続)で、指定したテーブル(例では `foo`)の更新が一切できなくなる。
複数の参照系クエリを連続で送る時に、途中でデータが書きかわると困る場合に利用する。サブクエリなどを使って１クエリにまとめてしまっている場合は不要。

別の接続で書き込みロックが発生している場合は、読み込みロックを獲得するまで待ちに入る。
なお、読み込みロックは性質的にパフォーマンスを向上させるものではなさそう。

– 書き込みロック

LOCK TABLES foo WRITE

テーブルの書き込みロッククエリを実行すると、ロック中は現在の接続以外からしか読み書きができなくなる。既にロック(読み/書きいずれか)が発生している場合はロックが獲得できるまで待ちに入る。また、デフォルトで READ よりも優先度が高い。

書き込みロックによってパフォーマンスが向上する可能性があるのは、処理時間の短いクエリを多く実行する場合(ロック解除時にまとめて実行するようになるため)。

更新を１回のクエリでしか実行しないのであれば、書き込みロックは不要。

(MySQL 4.1 公式リファレンスマニュアルより)
LOCK TABLES の使用時にいくつかの面で処理が迅速になる理由は、
MySQL でキーのキャッシュが UNLOCK TABLES が呼び出されるまで
フラッシュされないためである（通常、キーのキャッシュは各 SQL
ステートメントの後にフラッシュされる）。
それによって、MyISAM テーブルに対する挿入、更新、削除処理が
迅速化される。

– ロック解除

UNLOCK TABLES

テーブルロック解除クエリが実行されるか接続が終了した時点で、現在のスレッドが保持している全てのテーブルロックを解除する。
WRITE ロックの場合はこの時点で実際にデータベース上の値を書き換える。

参考:
– MySQL4.1 リファレンスマニュアル: LOCK TABLES および UNLOCK TABLES 構文
– 釣ったよ！釣りとコンピュータ: MySQL InnoDB の利用: テーブル・ロックについて

InnoDB が本当に必要な時とは

行レベルロックが本当に必要な時のみ。
InnoDB を利用し、トランザクション処理を行う場合、MyISAM にはないエラー処理が必要になったり、対処事項が増えたり、MyISAM よりパフォーマンスに劣る面があるという点に留意すること。

InnoDB を利用しなくても済むような、以下の代替手段が存在する。

– サブクエリ

UPDATE user SET presence = 1 WHERE
job_id = (SELECT id FROM job WHERE name = ‘teacher’);

虎の子で条件分岐も使えるし、サブクエリだけで結構何でもできてしまう。

– LOCK TABLES, UNLOCK TABLES]
今日のエントリを参照。

– LAST_INSERT_ID()
最後に挿入したレコードの一意な ID はどのように取得するのか
 mysql_insert_id()
最後の挿入(INSERT),更新(UPDATE) で設定/生成されたレコードについての AUTO_INCREMENT 値が入る。AUTO_INCREMENT 値が更新/設定されなかった場合は 0 になる。

– GET_LOCK(), RELEASE_LOCK()
Java や C# のオブジェクト指定のロックのような、名前指定のロック。
ただしデッドロックを避けるため同時に複数のロックを獲得することはできないようになっている。つまり、GET_LOCK() は、前回のGET_LOCK() によるロックを開放する。

参考:
– 釣ったよ！釣りとコンピュータ: MySQL InnoDB のトランザクション

MySQL41 の SJIS 環境のクライアントからの文字化けを防ぐ

SJIS 以外は SET NAMES クエリでの設定で解決するが、SJIS ではクエリでは解決できない。

結論からいうと、

mysql –default-character-set=sjis [ -u username [ -p ]] […]

のように、クライアントでの接続時に –default-character-set=sjis と明示してやるだけで解決する(SET NAMES .. は不要)。my.cnf の [mysql] の項目に sjis と設定してやってもよい。

PHP でも my.cnf を見ているらしい。PHP ではこれ以外の妥当な設定の手段がないもよう。
現在は、一部のコードでのみ対応したい場合は、自分でラッパ関数を作ってやるのがよさそう。PEAR::DB の prepared statement 構文を使う場合は ujis (EUC_JP)として通信させてやらないとうまくいかなかった。

詳しく書くと、DB_Common::prepare で呼び出している内部関数で非 UTF8 のつもりでエスケープ処理を行うため、事前エンコードをすると文字が壊れてしまうのが原因。EUC-JP だと、ASCII コードに互換性があるためエスケープ処理で文字が壊れないのでエンコード後のエスケープでも問題ない。

//
// 単純なサンプル。実用コードではありませんよ。
//
$con->query(‘SET NAMES ujis’);
echo dec( $con->getOne(enc(‘SELECT description FROM product WHERE name = ?’), enc(array(‘苛性ソーダ’))) ) ;

/**
*
*/
function dec($val){
    return mb_convert_encoding($val, mb_internal_encoding(), ‘EUC-JP’);
}
/**
* エンコード。クエリを実行する前の前処理。
* @param string $val 変換対象の文字列
* @return string DB クエリの文字コードに変換した文字列
*/
function enc($val){
    return mb_convert_encoding($val, ‘EUC-JP’);
    // $val は mb_internal_encoding() の文字コード。それ以外も
    // 受け付ける場合, 第三引数を ‘auto,UTF-8’ 等にしておく。
}

参考:
– OSS Web – 接続キャラクタセットの変更
ここにあるとおり、show variables like ‘character\_set\_%’ では同じなのに、実際は内部的には違うのがなんとも。