mysqlをdisる会

はじめに

やあ （´・ω・｀)
ようこそ、バーボンハウスへ。
このmysqlはサービスだから、まずsystemctl start mysqld して落ち着いて欲しい。

うん、「また」なんだ。済まない。
仏の顔もって言うしね、謝って許してもらおうとも思っていない。

でも、このタイトルを見たとき、君は、きっと言葉では言い表せない
「ときめき」みたいなものを感じてくれたと思う。
殺伐とした世の中で、そういう気持ちを忘れないで欲しい
そう思って、この記事をかいたんだ

じゃあ、注文を聞こうか。

というわけでmysqlをdisります。disるだけなので内容はありません。いいね？

mysql には罠がいっぱい

そうなんですよ罠がいっぱいなんですよ奥さん。
いやこれはおそらくmysqlに限った話ではないんですけど例えばこういうの！

MySQLのチューニングなんてしたらパフォーマンス落ちるだけだし、デフォルト設定で使うべきだよね？

NO！　デフォルトで使うとか死にてえのかおめえ！
mysqlのデフォルトでの運用は大変危険です。運良く稼働しても長期運用すると痛い目を見ます。
そんな甘い考えは今すぐ捨てろ

DBのチューニングや設定なんてやばくなってからでも十分間に合うしとりあえず動かすことが大事だよね

NO!　そんな考えは捨てろ！いますぐ/etc/my.cnfをひらくんだ。悪いことはいわない今すぐ開いて設定しろ。

さてここまで読んだ皆さんはこう思っただろう「こいつ具体例出さないでなんなん？ちんちん！！！」

よろしいならば具体的な話だ。

ストレージエンジンの話

mysqlに有名なものとしてはMyISAMとInnoDBがある。
最近はもっと色々あるっぽいけど中の人が勉強不足であんまり語れないので許してね！

さてストレージエンジンですが、MyISAMとInnoDBともにメリットやデメリットがあります。
とりあえずInnoDB選んどけばええんやで

MyISAMはテーブルロック方式で書き込みが発生するとテーブルをロックするため更新には不向きなストレージエンジンです。
そのかわりリードが早いため、参照が多いテーブル構成ではいかんなくパフォーマンスを発揮してくれることでしょう。
ただトランザクションなんてものは存在しないので、データの不整合や最悪データの欠落などが起きるリスクもあります。

2018/1/13追記

パーティショニングを行うことでMyISAMにおいてもロック範囲を狭めることが可能となるようです。（私は未検証なので引用のみとなります） https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations-locking.html MySQL 5.6.5 以前では、DML または DDL ステートメントを実行するときにテーブルレベルロックを実際に実行する MyISAM などのストレージエンジンの場合、パーティション化されたテーブルに影響するそのようなステートメントがテーブルに全体としてロックを適用していました。つまり、ステートメントが完了するまですべてのパーティションがロックされました。MySQL 5.6.6 はパーティションロックプルーニングを実装し、これによって多くの場合に不必要なロックが排除されます。

でもねこれmysql 5.5 以前までは標準でMyISAMが選択されていたんですよ！奥さん！

おいおいMySQLクソじゃん。カスじゃんとおもった貴方！
そこでInnoDBなんですよ。こいつはなんと「行ロック」「トランザクション」を備えてるんですよ！！！
すごい！すごくない？？？
しかし行ロックだからといってデッドロックの恐怖はあります。
そこででてくるのが「ネクストキーロック」の話です。

ネクストキーロックの話をしだすと検証用のデータとか作らないといけないんで、
sh2さんの下記の内容をご参照ください。

2009-01-12 MySQL InnoDBのネクストキーロック おさらい
http://d.hatena.ne.jp/sh2/20090112

まあなんかその oracle には勝てなかったよって感じが伝われば幸いです。
もちろんネクストキーロックを限りなく防ぐ手立てがまったくないわけではないですが、
システムの設計により思わぬバグを生むのでシステム設計者とよくご相談の上ご検討いただければ幸いです。
この辺も合わせてご覧ください。

InnoDBのロックの範囲とネクストキーロックの話 - かみぽわーる
http://blog.kamipo.net/entry/2013/12/03/235900

#ibdataの話

mysqlといえばこいつでしょう。もはや悪。だれだこんな仕様考えたやつは！
ibdata君は日に日に増大していきます。どれくらい増大するかというと最悪HDDの空き領域をすべて食いつぶします
わーい空き容量0%だたーのしー！首つるー！
なんてことになります。

さらにこいつに容量制限をかけることができますが、容量制限をかけた場合、その容量に到達した時点で
書き込めなくなります。つまりいきなりDBに反映されなくなります。たーのしー！
なおこれがmysqlデフォルトの挙動です。ふざけんな！！！！！
なおもちろんこれを防ぐオプションは用意されています。
「innodb_file_per_table」
忘れずに入れておきましょう。

これにいれるとテーブルの情報がテーブル毎に分割されるようになるので、容量が肥大化してもそのテーブルを削除するか、レコードを削除した後にalter table などのテーブルの再構築が走る操作を行うことでテーブル情報を縮小させることが可能になります。
なおこのオプションを有効にすることでパフォーマンスが低下するというデメリットもあるらしいですが、あきらかにそれを上回るメリットのほうが大きいので有効にすべきです（もちろんわかった上でこのオプションをつけないという選択も場合によってはありだと思いますけどね）

2018/1/13追記：

mysqlのバージョンによって、一部の操作では、一時テーブルを必要としないインプレース ALTER TABLE が可能なため、そういった場合にはテーブルの再構築が起きません。一応その旨を記載してきます。

ちなみに一度肥大化したibdata君はこいつを消す以外容量を削減する方法はなく、無論こいつを消すということはDBのデータがすべて消し飛ぶということなので心臓に悪い作業を行う羽目になります。こわーい。

インストール時の話

mysqlもバージョンによって入れ方が若干変わる。
とりあえず同じ5系ならマイナーバージョンアップだしかわんねーべって考えは通じない。
バージョンにあった適切な導入の仕方を参考にしましょう。

バージョンアップの話

MySQL 5.7の罠があなたを狙っている
https://www.slideshare.net/yoku0825/mysql-57-51945745

すごくためになるスライドです。僕が言うまでもないって感じです。
ありがてえありがてえ・・・。

チューニングの話

mysqlでinnodbを長年使い続けている方ならもちろんご存知かと思いますが
「innodb_buffer_pool_size」の話です。
とりあえず脳死で総搭載メモリ量の80%くらいとっておけばいいです。
（もちろんスワップを発生させない範囲で）
この設定をちゃんとするかしないかでもDBのパフォーマンスは大きく変わります。
ほかにもパフォーマンスに影響するオプションは結構あるので要チェックデス！

文字コードの話

何も考えずにDBにつないでinsertとかするとlatin1ではいってしまったりします。
latin1で入った場合、そのまま扱う分には大きな問題に”あまり”ならないのですが、
ダンプして復元という話になってくると結構大きな問題になったりします。
mysqlをいれたあとはDBやテーブルを作るよりも先に文字コードの設定をすることをおすすめします。
もちろん「innodb_file_per_table」もわすれずにね☆ミ

インデックスの話

複合indexを貼る場合は順番が大事です。とりあえず貼ったからといって順番を逆にしたりするとindexは効力を一切発揮しません。気をつけてね。

2017/3/20訂正:
順番を逆にしてもindexの効力は発揮されます(mysql5.7で確認)。
内容に誤りがありましたので訂正します。ただしindexを貼る順番については大事です！！！！
（昔は効かなかったようなきがするんですが、この辺ご存じの方がいましたらコメントください）

では、実際に複合indexで効力が発揮される例とされない例については下記に追加します。

作成したテーブルは以下のコマンドで作成。

CREATE TABLE index3 (
a INTEGER NOT NULL DEFAULT 0,
b INTEGER NOT NULL DEFAULT 0,
c INTEGER NOT NULL DEFAULT 0,
INDEX(a,b,c)
);

レコード数は適当に2097152程度突っ込んでおきました。
さて、indexが実際に効いているかを調べるにはどうしたらいいのでしょう？
答えは簡単「EXPLAIN」コマンドを使います。
ここで注目するのは、typeとrowsに注目します。
（本来ちゃんと調査に使うときは他の項目も重要なのですが割愛します。）

explain select count(*) from index3 where c > 9000 and b > 9000 and a > 9000;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	index3	NULL	range	a	a	4	NULL	468408	11.11	Using where; Using

typeもrange(indexを用いた範囲検索)で、rows（実際にフェッチされる見積もり）も絞りこめているようです。効いてますね。
ではこれはどうでしょうか？

explain select count(*) from index3 where c > 9500 and b > 9500;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	index3	NULL	index	NULL	a	12	NULL	3457840	11.11	Using where; Using index

rows の件数をみればわかりますが、実際に存在しているデータ量よりも多い数を示しています。
つまり全件対象になってしまっていますね。typeもindexとなっていますがこれはフルインデックススキャンを示します。
ちなみにrowsは見積もりなので実際の数と前後するので目安程度にみるものです。
なので今回のように実際のレコード数やヒットする件数と異なるというのはよくあります。

これをみれば分かる通りindex自体はa,b,cに適用されていますが、a, b, cすべてが条件に含まれてないので後者のクエリはindexが効かないということになりｍ
……はい。実はここで順番が大事という話に戻ってきます。

8.3.5 マルチカラムインデックス
https://dev.mysql.com/doc/refman/5.6/ja/multiple-column-indexes.html

一番最後に書いてある内容のとおりです。やってみましょう。

explain select count(*) from index3 where b > 9500 and a > 9500;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	index3	NULL	range	a	a	4	NULL	206700	33.33	Using where; Using index

typeがrangeになってますね。効いています。
つまりこの場合、aが主indexとなりaが含まれていればaとbの検索でindexが使用されます。
では、aとcではどうなのでしょう？

explain select count(*) from index3 where c > 9500 and a > 9500;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	index3	NULL	range	a	a	4	NULL	206700	33.33	Using where; Using index

range で効いてますね。ややこしい！

ちなみにEXPLAINにFORMAT=JSONという一文を加えることで、mysql5.6から更に詳細な情報を得ることが可能となりました。是非活用しましょう。

最後に

でもぼかぁそんなmysqlが大好きなんだなぁ