このドキュメントは最近メンテされていない項目が数多くあります。
リンクが切れていたり、現状のmysqlでは直っていたり、変更されている可能性が多々。
その点だけ注意して下さい。
InnoDBへ移行する際に把握しておかなくてはいけない事
affected_rowsは英語で「影響を受けた行数」という意味
疑似csv形式で出力する
mysql database_name -NB -e 'SELECT ... INTO OUTFILE "/tmp/hogerara.csv" FIELDS TERMINATED BY "," FROM table WHERE ...'
- ...の箇所は、普段のSELECT同様、適当に記述する事
- mysqlサーバ権限でファイルが出力されるので、一旦/tmp等に出力する必要がある(当然、外部のmysqlサーバでは使えない)
- 正しいcsv形式では、カンマ等を含むカラムは「"」で囲ったり、「"」を「""」に変換する必要があるが、このSQLでは通常のSQL結果出力と同様に、バックスラッシュによってescapeされてしまうので、それらの文字を含むデータを出力させてはならない。
- /tmpに出力した場合、通常/tmpはsticky bitが立っているので、suアカウント(またはmysqlアカウント)を持っていない限りファイルを削除できなくなるので注意する事。
- 自前でother writableなディレクトリでも用意すればいいのだろうが、わざわざそういう事をするのは面倒だったりセキュリティ的にアレだったりするので、そこまでやるなら普通にtab区切りで出力してから適当に変換した方がいいと思う。
ソースからの構築時のトラブル
- 最近、mysql-4.1.8をソースから構築しようとしたものの、何故かlibmysqlclient.soが作られない。色々と-sharedにしても駄目。
- make testで引っかかるポイント。
- mysql-4.1.8で、--without-innodbで構築すると、multi_updateのテスト時に「engine = innodb」を指定してテーブルを作っている部分で、警告が出て、テストに通らない。
- mysql-4.1.8で、SMPマシンでSMPカーネルなせい……かどうかは不明だが、insert delayedのテストに通らない時がある(通る時もある)。
- insert delayedは別スレッドで実行され、そのスレッドと、その直後に実行するselectのスレッドと、どちらが先に実行されるかは不定(らしい。また聞き)。なので、この時にinsert delayedした内容をselectで取得できるかは不定。なのに、test caseの答えの方は、「selectできる」方に固定になっている。
- ただ、本当に、test caseの答えの方が正しく、結果が不定な方が間違っている、とすると、自分の構築したバイナリの方が間違っている、という事になるが……。
- クライアント側も、statically linkedに作らないと、make testできない。
- 正しくsoを見付けられるように設定されていないらしく、エラーになってしまう。
- 「--with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static」をつける。
- 間違って、変な状態のバイナリをインストールしてしまった時は、ソースディレクトリに入り、make uninstallで、インストールしたバイナリ等を削除できる。但し、上書きインストールの時は、元の状態にまでは戻らない、当然。
- そういうのを求める人は、素直に、どっかで配布しているようなパッケージをインストールしましょう。
なんかよく分からないけどmysqlが重い時
- mysqladmin processlistで見てみて、「Waiting for tables」になっているプロセスが多い場合、開ける最大テーブル数がボトルネックになっているので、mysqlの設定を変更したり、ulimitの最大ファイルハンドラオープン数の上限を増やす事。具体的には、
mysql_use_result()とmysql_store_result()の違い
- mysql_use_result()は、SELECT等の結果はサーバ側に一旦保存され、fetchする度にクライアントへと転送される為、クライアント側のメモリ使用量が少ない。
- しかし、その代償として、以下の制限がかけられる。
- SELECTした全ての結果をfetchしなくてはならない（途中で残りを捨てる事は出来ない）。
- 全ての結果をfetchするまでは、SELECT中のテーブルはロックされた状態になる(但し、下記のSQL_BUFFER_RESULTを使えば、ロックは回避できるらしい)
- 全ての結果をfetchするまでは、そのdbhを使って他の（次の）SQLを実行する事は出来ない
- 全ての結果をfetchするまでは、全部で何行あるか分からない
- 結果レコードに対するランダムアクセスは出来ない（当然だが）。
- mysql_store_result()は、SELECT等の結果は全部まとめてクライアント側に一挙に送られる為、クライアント側のメモリの使用量が大きい。
- しかし、その見返りとして、以下のメリットがある。
- 上記のmysql_use_result()の制限は当然、無い
- mysqlにはカーソルの概念が無いが、クライアント側で全結果レコードを保持している性質上、擬似的にカーソル的な使い方が出来るようになる（しかし、こういう使い方が、本当に正式にサポートされた使い方なのかは、ちょっと怪しげ…‥）
- 要するに、dbhからSELECTのsthを作って、その全部の結果をfetchしないまま、他のSQLを実行したりしても、古い方のsthからまだfetchを続ける事が出来る、という事。
- 両方とも、C?のAPIなので、通常はいじる必要は無い（大抵はデフォルトでmysql_store_result()を使うようになっている）が、コレが問題になる場合もあるので、その場合は、自分が使用しているモジュール／ライブラリのやり方に沿って、コレを変更する事。
- この問題の詳細については、下のページの末尾の方の、「必要があります」と強調で書かれている辺りを。
- 尚、mysql_use_result()を使用する際に、SELECT文にSQL_BUFFER_RESULTを指定する事で、サーバ側のロックを防ぐ事が可能だそうだ。mysqlはバッドノウハウの温床だ。
SHOW TABLE STATUS;
- 上記のSQL文を実行する事で、現在USEしているデータベース内の全テーブルについて、
- テーブル型（MyISAMとか）
- 行フォーマット（固定長か、可変長か。VARCHARやBLOB等の可変長カラムを含まない場合、行全体が固定長になり、フラグメンテーションが起きなくなる）
- 総行数
- 平均行サイズ
- AUTO_INCREMENTで次にINSERTした時に得られる番号
- その他色々な情報
- が、見られる。
- 他では見る事の出来ない情報が多く含まれているので、このSQL文は憶えておいて損は無い気がする。
my.cnf
- max_connectionsは、サーバマシン自身を屈服させない程度に、なるべく多くしておく
- max_connect_errorsは、通常は10のままで問題無いが、NIC/回線周りに問題があったりでエラーが起こり得る環境なら、増やしておいてもいい
- コレは、接続エラーのあった接続元ホストのipを憶えておき、ソレが一定回数以上になると、「怪しいホスト」として、接続を拒否する為の機能。
- 上記のカウントは、「mysqladmin flush-hosts」でクリアできる。
- wait_timeout, interactive_timeoutは、デフォルトでは八時間だが、FastCGI等でプロセスが常駐する場合、普通はコネクションも張りっぱなしにすると思うので、八時間アクセスが無いとmysqld側から切られてエラーになってしまう（設置時には問題が無いので、余計トラブルになりやすい）。
- 必要なら、大幅に増やしておく事。
- wait_timeout, interactive_timeoutが、どっちが何だったかは、忘れた…‥。
- 例えば、BLOBに5Mのファイルを保存したい場合、max_allowed_packetも5M以上にしておかなくてはならない。max_allowed_packetサイズを超えるとエラーになる。
- max_allowed_packetは、SQL文またはSQLの実行結果を取得する際の、最大サイズと大体同じ。
- log-slow-queriesで、「充分遅いクエリ」をslowlogとして書き出すようになる。
- 「充分遅い」かどうかは、long_query_timeの秒数以内かどうかで判定される（デフォルト五秒）。
- log-binで、SQL実行時のバイナリログを残すようになる。レプリケーション時には必須。
DBのエンコーディングとVARCHAR(n)の関係
- mysql.gr.jpのMLの09214より（メンテ中でMLアーカイヴにリンクはれない…‥）。
- 要するに、my.cnfとかconfigureオプションで、デフォルトエンコーディングをujisやsjisやutf8等のマルチバイト系にした場合、
- LENGTH(col)で得られる長さは、バイト長
- VARCHAR(n)で生成されたカラムの最大バイト長は、「n * 該当エンコーディングで一番バイト長の長い文字のバイト数」
- となるっぽい（eucならn * 3, sjisならn * 2, utf8ならn * 6？）。
- VARCHAR(n)のnはエンコーディングに関わらず0〜255なので、テーブル的には、VARCHARがどんな長さでも問題ないのだろうが…‥
- 要するに、指定した長さよりも上限が大きくなるので、行を格納する際に、行サイズの上限で後ろ部分が切り詰められるような事を期待していると、上手く動かない可能性がある、という事なのか？？？
- それ以外には、特に問題は無い？
リファレンス
レプリケーション
- レプリケーション設定は、my.cnfで行うが、その後、my.cnfの該当行をコメントアウトして再起動しても、レプリケーションは続行するっぽい。
- 何故なら、my.cnfよりも、master.infoを優先して見るから！
- レプリケーションを完全に停止させたい場合は、master.infoをmvするなりrmする必要があるっぽい。
一行だけmysqldumpする
mysqldump --no-create-info --where='unique_id=1' dbname tablename >> dump.sql
テーブルが破損した時に
REPAIR TABLE
- というSQLを実行する事で、テーブルを修復できるが、このSQLはレプリケーション側には反映されない。
- つまり、REPAIR TABLEの結果、テーブルの行が何行か削除された場合、コレはそのまま、レプリケーション間での不整合となってしまう。要注意！！
mysqlをとめずにバックアップを取る場合のパターン
- いきなりmysqldump --opt
- いきなりmysqldump
- --opt、或いは--lock-tablesをつけなければ、ロックは、テーブル単位でかかる
- テーブル単位では安全だが、バックアップを取るタイミングで、テーブル間に不整合が出る可能性がある（実際に動作しているスクリプトの方でちゃんとLOCK TABLESを行っているなら、不整合は出ないと思うが‥…）
- リレーションのあるテーブル群単位でmysqldump --opt
- いきなりtar
- あまりよくない（バックアップした先がテーブル破損する可能性あり）
- しかし、コレならDBに影響を与える事なく、バックアップが可能（ディスクアクセスやcpu消費による影響はある）
- 先にFLUSH TABLESを行ってからtar
- いきなりtarよりはマシだが、
上記同様、テーブル破損の可能性はある
- 但し、テーブルを全くロックせずにバックアップを取るなら、破損を覚悟してこうするしかない（或いは、後述のレプリケーションをするか）
- FLUSH TABLES WITH READ LOCKしてからtarで固めてUNLOCK TABLES
- 安全にtarで固められるが、mysqldumpと同様に、ロックがかかる
- しかし、mysqldumpよりは早い気はする…‥
- mysqlhotcopyコマンドを使う
- 上記の、「FLUSH, LOCK, tar, UNLOCK」を行ってくれるコマンド、らしい。
- 長所短所も上記と同じ
- レプリケーションを行い、slave側でバックアップ
- 一番安全かつ影響を与えないが、レプリケーションが完全に正常に行われている、という保証が必要。
なんかよく分からないけどエラーが出た時
DBD::mysql::db do failed: Got error 127 from table handler at (eval 54) line 349.
$ perror 127
Error code 127: Unknown error 127
127 = Record-file is crashed
テーブルの構造を、CREATE TABLEフォーマットで見る
SHOW CREATE TABLE table_name
現在のテーブルに溜まったデータについての統計情報等を取得する
select * from ant_user PROCEDURE ANALYSE();
一つのテーブルが4Gを超える可能性がある場合
CREATE TABLE hoge (‥‥) AVG_ROW_LENGTH = 255 MAX_ROWS = 268435455;
手dump
mysql -h hostname -u username -p******** dbname -NB -e 'SELECT * FROM hoge' > hoge.txt
- -Nで一番上のカラム名除去
- -Bでバッチモード（出力結果に罫線を表示しない／tabか何か区切りになる）
- -pの後に空白を入れずにパスワードをつける事が出来る（危険）
- -vをつければつけるほど、出力情報が冗長になる（注意：ファイルdump時にはつけない方がいい）
手流し込み
mysql -h hostname -u username -p******** dbname < hoge.sql
- -fで、途中でエラーがあっても続行するようになる（hoge.sqlにINSERT INTO……をたくさん書いている場合とかに有効）
inner join
SELECT t1.*, t2.hoge
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.id = t2.id
WHERE t1.val = ?
SELECT t1.*, t2.hoge
FROM table1 AS t1
INNER JOIN table2 AS t2 USING (id)
WHERE t1.val = ?
outer join (left joinまたはright join)
- 片方のテーブルを基準に、もう一方のテーブルを外部結合する。
- 基準側テーブルは全て含まれる。追加テーブル側に該当する行が無い場合は、NULLが追加される
SELECT t1.*, t2.hoge
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.id = t2.id
WHERE t1.val = ?
SELECT t1.*, t2.hoge
FROM table1 AS t1
LEFT JOIN table2 AS t2 USING (id)
WHERE t1.val = ?
myisamchkをSQLで実行
- CHECK TABLE
- REPAIR TABLE
- OPTIMIZE TABLE
- ANALYZE TABLE
CHECK TABLE hoge_table EXTENDED;
- で、非常に詳細なテーブルの完全チェックを行う。巨大なテーブルに行う際には注意。他のオプションは↑のCHECK TABLEのマニュアルを。
その他
- INSERT DELAYEDは、実行すると、別スレッドが生成され、実際のINSERTの処理はその別スレッドが実行するという形となる。なので、接続内でのみ有効なtemporary tableに対してINSERT DELAYEDを実行する事は出来ない(テーブルが見付からないエラーになる)。
- ALTER TABLEにかかる時間は、テーブルサイズのO(N)では済まない！O(N^1.5)程度かかる覚悟はしておく事(要するに、巨大なテーブルほど余計に時間がかかる事を覚悟する事)。
- mysqldumpしたSQLを流し込む場合にかかる時間も同様。
- mysqlに接続しにいくようなアプリ(特にCGI等のネットサービス系)は、mysqlへの接続に失敗した場合に「只今メンテ中です」のような画面を出すようにしておく事(コレをするだけで信用度が大きくアップ)。
- 大量のテーブルを作るような使い方をする際には、データベース置きディレクトリのファイルシステム種別に注意する必要がある。
- 一つのテーブルにつき、三つのファイルを作る(*.FRM, *.MYI, *.MYD)。
- linuxでは、ext2/3は、一つのディレクトリに大量のファイルを置くと、異様に重くなってしまうので避ける事。reiserfsやxfsが吉。
- bsd系osについては未調査。
- mysql内アカウントのユーザ名は16文字まで(CHAR(16) BINARY NOT NULL)。
- パスワードは特に長さ制限は無いっぽい(digestされて格納されるからっぽい)。
- mysqldは別サーバで動かす事が多いが、稀に、mysqlに接続する側のプロセスの居るサーバと、mysqldの動いているサーバの時計がずれている事がある。
- ちゃんと定期的にntpで同期を取るようにする
- 或いは、パラノイアな人であれば、接続元プロセスが時間を取得する場合はシステムのtimeではなく、いちいちSQLから「SELECT NOW()」とでもするようにすればいい。
- 「末尾がspaceになり得るデータ」(テキスト及び純粋なバイナリデータ)には、CHARやVARCHARは使ってはならない。BLOB系に入れるようにする。
- CHARやVARCHARは、末尾がspaceだった場合、どの段階でかは忘れたが、自動的に除去されるので、保存したデータを比較する際に不一致と判定されたり、バイナリデータの場合、破損する可能性がある。
- この挙動は5.0.3にて、VARCHARは末尾のspaceを除去しない仕様に改められた。やっぱりみんなバッドノウハウだと思っていたんだろう。
- BLOB系カラムにインデクスを張る場合は、たとえTINYBLOBであろうとも、インデクスの長さ指定が必須になる。
- 尚、インデクスの長さは最長で255まで。
- インデクスがカラムより短い場合、後半部分は切り捨てられてインデクスされる(結果、前半部分が同じで後半部分の違うカラムがUNIQUE判定で同一視される可能性があるっぽい)。
- mysqldを停止／再起動させる前には必ず、mysqladmin processlistコマンドで、接続しているサービスが無い事を確認する。
- もし接続しているサービスがあるなら、接続元を確認する事。
- mysqldumpコマンドの--optオプション（より正確には、--lock-tablesオプション）は、全テーブルまとめてロックする為、テーブルの数があまりにも多い場合にエラーになる可能性があるっぽい。
- テーブルの数が徐々に増加する場合、途中からエラーで定時バックアップが失敗していて、いざという時にバックアップが無い、という事の無いように、注意しなくてはならない
- GRANT構文を使って（別に直接mysqlデータベースを操作してからFLUSH TABLEしてもいいが）、DBやテーブル毎に、細かくアクセス制限を設定できる。
- mysqldumpslowコマンドで、遅いクエリの統計情報が見れる（mysql起動時に--log-slow-queriesオプションをつけておく必要がある？）
- コマンドラインからSELECT文を入れる場合、横に長くなりすぎる場合は、「\G」を、末尾のセミコロンの代わりに入れると、見やすくなる
select * from hoge where id = 123;
select * from hoge where id = '123';
- としてしまうと、間に文字列→数値化関数が入ってしまう為、SQLの組み立て方によっては、インデクスが使われなくなってしまう事があるらしい？
- よく分からないけど遅いような気がする時は、explainで確認してみる。
- 遅いかどうかも分からない時は、本番環境で走らせて、slowlogをチェック。
- 七百万件程度の行を持つテーブルに新しくインデクスを追加するのに、五十分程度かかった。その五十分間はテーブルはロックされるので、インデクス追加時は要注意。
- テーブルの大きさとかマシンスペックとか詳細は暇な時に調べます‥‥
- vmstatで様子を見ていたところ、cpuはそれほど消費せずに、ブロックデバイスのreadがものすごく高かったので、ディスクの読み込みがボトルネックになっていた様子。実行時に他のディスクアクセス系プロセス（一番可能性が高いのは他のmysqlプロセス？）へも大きく影響を与えると思うので注意
- mysqladmin processlistで現在のコネクション一覧と、それらが実行しているSQLが見れるが、SQLは後半が省略されている。全部見たい時は-vをつける。
- 非UNIQUEなカラムをuniq化した一覧を取りたい時は
SELECT DISTINCT non_unique_col FROM ……
- 非UNIQUEなカラムをuniq化した一覧の総数を取りたい時は
SELECT COUNT(DISTINCT non_unique_col) FROM ……
- COUNT()とDISTINCTの順序に注意。分かりにくい…‥
- テーブルにVARCHAR型やBLOB型等の可変サイズなカラムを含んでいる場合、前述の型を含まない固定長のテーブルよりも低速化につながる可能性がある（テーブルのフラグメント化が起きた際に）。
- 不定文字長でも、最大値が充分に短い場合は、VARCHAR型よりもCHAR型を使った方が省サイズかつ高速。尚、mysqlのCHAR型は、取り出す際に、何故か勝手に末尾の空白が除去される仕様になっているので、何も気にせずにVARCHAR型と同様に使える。
- しかし、この仕様が仇になる場合もある（VARCHAR型で、末尾に空白のついた文字列を扱いたい場合）。
- テーブルの中に既にBLOB型やVARCHAR型のカラムがある場合、テーブル全体が不定長になるので、CHAR型を指定しても、勝手にVARCHAR型に変換される。
- CHAR, VARCHAR型は、binary指定をつけないと、大文字小文字を区別しない。uniqueなkeyとして使う場合に引っかかりやすいので注意する。
- ENUM, SET型も、大文字小文字を区別しない！！！しかも、こっちには、BINARY指定をつける事すらできない…‥。つまり、
CREATE TABLE hoge(hoge ENUM('aaa', 'AAA'));
- として作ったテーブルは、descで見てみると、確かに、'aaa'と'AAA'の両方の値を入れられそうな定義になっているが、
INSERT INTO hoge VALUES('aaa');
INSERT INTO hoge VALUES('AAA');
- のどっちを実行しても、入るのは常に'aaa'。
- BLOB型の代用としてENUM型を使う際には、この点に注意を払う必要がある。
- 他にも、「insert時に数値を入れると、ENUM型内部の、その番号の内部インデクスを持つパラメータとして反映される」とか、ENUM型には罠多数。
- mysqlのLIKEには罠が多い気がするので、LIKEを使わずに、慣れたREGEXPを使った方が無難そう。
経験的に得られた結論
- HDDは何時か壊れる。メモリも何時か壊れる。*.MYIファイルもよく壊れる。*.MYDファイルも(*.MYIファイルほどではないが)壊れる。
- 大事なデータを失いたくないなら、レプリケーションすべし。
- レプリケーションのスレーヴに送られるデータは、「マスターサーバで実行した結果」ではなく、「マスターサーバで実行したSQL」が送られるので、マスターサーバのdb等が破損しても、スレーヴサーバのデータは単体としては正常な事が期待できる(但し、「実行したSQL」に差し込んだデータが、マスターサーバ内の破損したデータを元にしていると、結局アウトだが)。
- マスターサーバ一台につき、最低二台のスレーヴサーバを用意する。
- 一台は、長時間ロックを行うような集計などに使う為の、readonlyなDBとして使う用。
- もう一台はバックアップ用(他の二台のどっちかが壊れた時に、素早く代役をさせる用途)。
- たまにはmyisamchkか、それ相当のSQLを自動実行するようにする。
総合的な結論
- mysqlを間違い無く使いこなすには、C?の知識を持って、mysqlのCのAPIを眺めておく事が必要。
- コレが無いと、「mysqlにはカーソルのサポートが無いのに、複数のsthから平行にfetch出来たりする。この使い方は本当に安全なのか、実は危険なのか」といった問題が判別できない。そして困る。
- ちなみに、この答えは「安全」。
- スクリプティング言語のmysqlバインディングでは、SQLのfetchを行う時、内部では大抵、mysql_store_result()を使うようになっている(筈)。その為、SQLが実行されると同時に、その全ての結果がクライアント側に送られ、メモリ上に保持される為、一見、カーソルをサポートしているように見えるだけ。
- 「なんとなく使いこなす」レベルで良いなら、別にC?の知識は不要。
- しかし、業務で使うのだったら、「なんとなく」レベルではちょっとアレではないのか？きっと将来いつかトラブるような気がしてならない。
- RDBMS?はバッドノウハウの宝庫。
- 「比較的シンプル」と言われるmysqlですら、この複雑怪奇さなのに、他のRDBMS?は、少なくとも、自分にはちょっと手に負えなさ気味。*dbm系で充分。
最終更新 : 2010/01/05 13:23:15 JST