MySQL（ニッチな？）私的Tips集

概要

会社内の合宿でMySQLの大規模データ移管を構想する取り組みを行った際、MySQLやデータベースそのものについての勉強も行った。その時得たTipsを備忘録がてら並べておく。

知見

MySQLのクエリを気軽に叩けるサービスがある

MySQL Online
文法があっているか、データを仮に生成して狙った結果が得られるか、などを確認することができる。

Profiling on CodeIgnitor

• CodeIgnitorにはAPIを叩いた場合のプロセス一覧や実行時間などを判定してくれる機能が付いている。
• Laravelのdebug用画面のような機能に相当する。
• curlで叩けばそのまま生htmlが返ってくる。それをブラウザに貼り付けて結果を見ることになる。ゆえにWebServerとして使っている場合は（多分Laravelと同じようにブラウザで結果を確認できるが、弊社の場合のようにAPIサーバーとして使っている場合は打ち返されたテキストのHTMLを手元に保存して、ブラウザに投げないと結果が表示できない。

Consistent Hashing

• ハッシュの技術だが、（リ）シャーディングやパーティショニングにも使える技術。
• （小話）「ハッシュ」や「ハッシュ関数」というと、通常なんらかの情報を暗号化したり無意味な乱数の羅列に変換するような関数もといアルゴリズムのことだと想像してしまうが、ハッシュ化とは「要約関数」という訳語が与えられている通り情報量を減らす機能を持つ。従って変換先の文字はなんでも良く、整数に変換することもあり得る。その得られた整数を添字にもつ配列に値を格納していくというようなやり方をとることもある。

RDSのMySQLの同時接続数

• MySQLの同時接続数は、元々RDBMSとしてのMySQLが設定している上限数に依存する。
• 従って、RDSのインスタンスタイプをいくら上下させようと、MySQLが設定している上限を上回る同時接続数を設定することはできない。
• それでもRDSのインスタンスタイプによってデフォルトの同時接続数に違いがあるのは（インスタンスタイプを上げると接続数が増加する）同時接続した場合の書き込み能力や読み込み能力、これらを捌けるだけのCPU性能にも適切な規模と限界が存在するためである。貧弱なインスタンスタイプで同時接続数を最大に設定しても、それだけの接続を処理しきれない。ゆえにRDSに設定された上限はそのままに使うのがセオリー。

MySQL クエリ小ネタ

実行前に実行計画の概要を知る方法

• EXPLAIN という呪文を使う。わかりやすいQiita記事はこちら
• SELECTをはじめ、INSERTやUPDATEなどで使用することができる。
• リシャーディングやパーティショニングに関する設定でALTER TABLEやCREATE TABLEするとなると、計測はできない。なぜなら、パーティショニングでハッシュの再計算が実行されるときどれくらいかかるか実データの量によって変わってくるため、実際にやってみないことには計測できない。

インデックス指定の方法

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;

• インデックスを指定してクエリを流せる呪文。
• 一つのテーブルに対して複数のインデックスが用意されている場合に有効。
• 実行計画を立てたとき、どのインデックスが採用されるかは定かではないため、意図したインデックスがある場合は指定したほうがいい。

パーティショニングキーに設定する列項目は必ず主キーに含まれている必要がある

• MySQLのルール。
• ゆえに、新たにパーティショニングを実行する際に使いたいキーが主キーにないからと言って主キーに追加する場合、影響範囲を推定する必要がある。

キャッシュを読まないよう指定してクエリ(SELECT)実行する方法

• SQL_NO_CASHE をつける。 SELECT SQL_NO_CACHE id, name FROM customer;
• クエリパフォーマンスを計測するとき、キャッシュを読んでしまうと本来かかる時間よりも多くの時間がかかってしまう。正確に時間を計測するためにキャッシュを読まない設定をする。