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 Toolkitpt-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 (表結合)の意味の違い

SQL 標準では JOIN 句による結合構文は次のような種類があります。

INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
CROSS JOIN

LEFT JOIN, RIGHT JOIN など、よく使われる構文は上記の省略形です。

・ただの JOIN は INNER JOIN の省略形。
・LEFT JOIN は LEFT OUTER JOIN の省略形。
・RIGHT JOIN は RIGHT OUTER JOIN の省略形。

それぞれの JOIN 構文の意味は次のとおりです。

 

INNER JOIN: 内部結合

指定したカラムについて同じ値を持つレコード同士を結びつける。
内積なので指定したカラムの値がどちらかにしかないレコードについては結果に含まれません。

SELECT * FROM table1 INNER JOIN table2 USING(id)

は、次の SQL 文と同じ結果になります。

SELECT * FROM table1, table2 WHERE table1.id = table2.id

 

LEFT OUTER JOIN: 外部結合

左のテーブルを基準にして、指定したカラムについて同じ値を持つレコード同士を結びつける。
値が右のテーブルにあり左のテーブルにない場合は INNER JOIN 同様結果に含まれませんが、
値が左のテーブルにあり右のテーブルにない場合は INNER JOIN と異なり 右のテーブルのカラムには全て NULL がパディングされ、結果に含まれます。
 
このため、

SELECT * FROM table1 LEFT OUTER JOIN table2 USING(id) WHERE table2.id IS NOT NULL

は、

SELECT * FROM table1 INNER JOIN table2 USING(id)

と同じ結果になります。
 

RIGHT OUTER JOIN: 外部結合

LEFT OUTER JOIN の左右の意味を入れ換えたもの。

SELECT * FROM table1 RIGHT OUTER JOIN table2 USING(id)

SELECT * FROM table2 LEFT OUTER JOIN table1 USING(id)

と同じ結果になります。
 

CROSS JOIN: 交差結合

ON, USING で条件を指定しない場合、左右のテーブルの直積を返します。
つまり、左右のテーブルにそれぞれ2行のレコード、3行のレコードが格納されている場合、結合結果は全てのレコードの組合せになり合計 2×3=6 行のレコードになります。
PostgreSQL や MySQL では ON, USING を指定すると INNER JOIN と等価になります。

SELECT * FROM table1 CROSS JOIN table2

は次の SQL 文と等価です。

SELECT * FROM table1, table2

また、PostgreSQL, MySQL では

SELECT * FROM table1 CROSS JOIN table2 USING(id)

は次の SQL 文と等価です。

SELECT * FROM table1 INNER JOIN table2 USING(id)

 

■ON, USING, NATURALによる結合条件指定

CROSS JOIN をのぞき、JOIN 句には ON, USING, NATURAL のいずれかを指定します。

INNER JOIN … ON 結合条件
INNER JOIN … USING(結合カラム, … )
NATURAL INNER JOIN …

 

ON:

WHERE 句と同じ書式で結合条件を絞り込む。
書式:

SELECT … FROM table1 JOIN table2 ON 条件式

table1, table2 のidが同じカラム同士を結合したい場合は次のようにします。

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

AND, OR など複雑な条件で絞り込む事も可能です。

SELECT * FROM table1 AS t1
  LEFT JOIN table2 AS t2 ON (
    (t1.id = t2.id OR t1.name = t2.name)
    AND t1.salary >= t2.age * 1000
  )

ON に指定する結合条件には、 WHERE 同様どんな条件式でも指定することが可能ですが、テーブル結合に関係のない条件式はWHERE句に書くようにしましょう。
 

USING:

カラム名で結合条件を絞り込む。
書式:

SELECT … FROM table1 JOIN table2 USING(カラム1 , カラム2, …)

両方のテーブルに同じカラム名がある場合に有効です。

SELECT * FROM table1 LEFT JOIN table2 USING(id)

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

と同等です。複数指定した場合は全てのカラムの値が一致するレコードを結合します。
 

NATURAL:

両方のテーブルを比較して名前が同じ全てのカラムを結合条件にして絞り込む。
書式:

SELECT … FROM table1 NATURAL JOIN table2

USING に両テーブルの共通カラムを全て指定したものと同じです。すなわち、

SELECT * FROM table1 NATURAL LEFT JOIN table2

は、両方のテーブルに id, name, age が存在する場合、

SELECT * FROM table1 LEFT JOIN table2 USING (id, name, age)

を指定したのと同等の結果になります。
 

■複数のJOIN句を組み合わせる

3つ以上のテーブルを複数のJOIN 句で結合する事もできます。
例として、table1, table2. table3 全てのテーブルに同じidが存在するレコードのみ抽出する場合は次のようになります。

SELECT * FROM table1
  INNER JOIN table2 USING(id)
  INNER JOIN table3 USING(id)

複数のJOIN を組み合わせる時の結合順序は左から順に評価されます。結合順序を制御したい場合、カッコが使用できます。

SELECT * FROM table1 AS t1
  LEFT JOIN ( table2 AS t2 INNER JOIN table3 USING(id)) ON t1.value = t2.tag
)

この例では table2, table3の同じidのレコードをINNER JOIN 句を使い結合し、その後 table1 と LEFT JOIN 句で結合されます。
 

参考:

SELECT(PostgreSQL 8.2.5 マニュアル)
MySQL 5.1 リファレンスマニュアル 12.2.7.1 JOIN 構文(MySQL AB)
特殊な結合演算子 SQL(TELCHSCORE by 4DD)
SQL92

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`)の更新が一切できなくなる。
複数の参照系クエリを連続で送る時に、途中でデータが書きかわると困る場合に利用する。サブクエリなどを使って1クエリにまとめてしまっている場合は不要。
 
別の接続で書き込みロックが発生している場合は、読み込みロックを獲得するまで待ちに入る。
なお、読み込みロックは性質的にパフォーマンスを向上させるものではなさそう。
 
– 書き込みロック

LOCK TABLES foo WRITE

テーブルの書き込みロッククエリを実行すると、ロック中は現在の接続以外からしか読み書きができなくなる。既にロック(読み/書きいずれか)が発生している場合はロックが獲得できるまで待ちに入る。また、デフォルトで READ よりも優先度が高い。
 
書き込みロックによってパフォーマンスが向上する可能性があるのは、処理時間の短いクエリを多く実行する場合(ロック解除時にまとめて実行するようになるため)。
 
更新を1回のクエリでしか実行しないのであれば、書き込みロックは不要。

(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 では [2006-01-14] に書いたようにクエリでは解決できない。
 
結論からいうと、

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\_%’ では同じなのに、実際は内部的には違うのがなんとも。