MySQL：INDEXの基礎
CyberXの川村といいます。よろしくお願いします！
CyberXではソーシャルアプリ開発を主に行っていますが、そのソーシャルアプリの運用においてサーバートラブルを起こさず、いつでもユーザーの皆さんになるべくストレスを与えずに遊んでもらう事が重要であるのは言うまでもありません。
インフラ強化以前に、プログラマとしては自分が書いたプログラムやDB設計でインフラをいじめないようにしたいものです。。。
そこで今回はMySQLのINDEXに関して、基礎的な内容ではありますが書きたいと思います。
1つのクエリーで1つのテーブルに対し、1つのINDEXしか使われない
MySQLの特徴です。INDEXマージも行われません。
そこで、複合INDEXの貼り方が重要になります。
カーディナリティが高いカラムに貼ると効果が高い
「カーディナリティが高い＝カラムの値の種類がレコード数に比べて多いこと」
要は、よりユニークな値がたくさん含まれるカラムに対して貼ったら効果が高いよ、と言う事です。
0と1しか設定されないようなカラムに貼ってもあまり効果は期待出来ません。
ユーザーID等、ユーザー毎にユニークな値が入るカラムはカーディナリティが高い、ということになります。
複合INDEXちゃんと貼れてる
貼り方が不適切だとINDEXが使われません。
ここで注意しなければならないのが、複合INDEXに含めるカラムの「順番」です。
カラム順にヒットするかどうか見て行くため、ヒットしないカラムが前の方にあると効果激減です。
【例】
カラム
column1, column2, column3, column4, column5
複合INDEX
column1→column2→column3
SQL①
select * from table where column1 = 'aaa' and column2 = 'bbb' and column3 = 'ccc'
⇒INDEXが使用される
SQL②
select * from table where column1 = 'aaa' and column3 = 'ccc'
⇒INDEXが使用されない
SQL③
select * from table where column1 = 'aaa'
⇒INDEXが使用される
SQL④
select * from table where column2 = 'bbb'
⇒INDEXが使用されない
下記に注意して貼ればよいかと。
1.WHERE句でよく一緒に使われるカラム
2.WHERE句に単独で使われることもあるカラムは、インデックスの最初に指定する
3.カーディナリティの高いカラムから順に指定する
貼りまくればいいっていうわけじゃない
INDEXを貼れば貼った分、INSERTとUPDATE速度に悪影響が出ます(INDEXが更新されるため)。
また、INDEXを貼ったカラムの実データ分の容量が使用されるので、貼り過ぎるとディスクを圧迫する原因になりえます。
スマートに、必要な分だけ貼れたらカッコイイ！
Explainの活用
SQLをexplainでチェックして、INDEXが使われているか調べる事が出来ます。
INDEXが使用されるパターン
・フィールド値を定数と比較するとき(WHERE name = "hogehoge" )
・フィールド値全体でJOINするとき( WHERE a.name = b.name )
・フィールド値の範囲を求める時 (>、>=、 <、 <=、 BETWEEN)
・LIKEで文字列の先頭が固定な時(WHERE name like 'hoge%')
・MIN(), MAX()
・WHEREのすべてのフィールドがindexの一部の場合 (DBまったく参照されず)
INDEXが使用されないパターン
・LIKEがワイルドカードで始まる時
・DB全体を読んだ方が早いとMySQLが判断した時
・WHERE と ORDER BYのフィールドが違う時にはどちらかしか使われない
・NULL
・!=、<>(Not Equals)の使用
・列に対する関数や演算子の使用(SELECT * FROM column WHERE val * 2 > 2000)
基礎的な内容ですがミスするとDBに大きな負荷をかけてしまいかねません。
以上のポイントをおさえて、インフラに優しくなりたいですね！
CyberXではソーシャルアプリ開発を主に行っていますが、そのソーシャルアプリの運用においてサーバートラブルを起こさず、いつでもユーザーの皆さんになるべくストレスを与えずに遊んでもらう事が重要であるのは言うまでもありません。
インフラ強化以前に、プログラマとしては自分が書いたプログラムやDB設計でインフラをいじめないようにしたいものです。。。
そこで今回はMySQLのINDEXに関して、基礎的な内容ではありますが書きたいと思います。
1つのクエリーで1つのテーブルに対し、1つのINDEXしか使われないMySQLの特徴です。INDEXマージも行われません。
そこで、複合INDEXの貼り方が重要になります。
カーディナリティが高いカラムに貼ると効果が高い「カーディナリティが高い＝カラムの値の種類がレコード数に比べて多いこと」
要は、よりユニークな値がたくさん含まれるカラムに対して貼ったら効果が高いよ、と言う事です。
0と1しか設定されないようなカラムに貼ってもあまり効果は期待出来ません。
ユーザーID等、ユーザー毎にユニークな値が入るカラムはカーディナリティが高い、ということになります。
複合INDEXちゃんと貼れてる貼り方が不適切だとINDEXが使われません。
ここで注意しなければならないのが、複合INDEXに含めるカラムの「順番」です。
カラム順にヒットするかどうか見て行くため、ヒットしないカラムが前の方にあると効果激減です。
【例】
カラム
column1, column2, column3, column4, column5
複合INDEX
column1→column2→column3
SQL①
select * from table where column1 = 'aaa' and column2 = 'bbb' and column3 = 'ccc'
⇒INDEXが使用される
SQL②
select * from table where column1 = 'aaa' and column3 = 'ccc'
⇒INDEXが使用されない
SQL③
select * from table where column1 = 'aaa'
⇒INDEXが使用される
SQL④
select * from table where column2 = 'bbb'
⇒INDEXが使用されない
下記に注意して貼ればよいかと。
1.WHERE句でよく一緒に使われるカラム
2.WHERE句に単独で使われることもあるカラムは、インデックスの最初に指定する
3.カーディナリティの高いカラムから順に指定する
貼りまくればいいっていうわけじゃないINDEXを貼れば貼った分、INSERTとUPDATE速度に悪影響が出ます(INDEXが更新されるため)。
また、INDEXを貼ったカラムの実データ分の容量が使用されるので、貼り過ぎるとディスクを圧迫する原因になりえます。
スマートに、必要な分だけ貼れたらカッコイイ！
Explainの活用SQLをexplainでチェックして、INDEXが使われているか調べる事が出来ます。
INDEXが使用されるパターン・フィールド値を定数と比較するとき(WHERE name = "hogehoge" )
・フィールド値全体でJOINするとき( WHERE a.name = b.name )
・フィールド値の範囲を求める時 (>、>=、 <、 <=、 BETWEEN)
・LIKEで文字列の先頭が固定な時(WHERE name like 'hoge%')
・MIN(), MAX()
・WHEREのすべてのフィールドがindexの一部の場合 (DBまったく参照されず)
INDEXが使用されないパターン・LIKEがワイルドカードで始まる時
・DB全体を読んだ方が早いとMySQLが判断した時
・WHERE と ORDER BYのフィールドが違う時にはどちらかしか使われない
・NULL
・!=、<>(Not Equals)の使用
・列に対する関数や演算子の使用(SELECT * FROM column WHERE val * 2 > 2000)
基礎的な内容ですがミスするとDBに大きな負荷をかけてしまいかねません。
以上のポイントをおさえて、インフラに優しくなりたいですね！
ソースNAT の設定で・・・・
Cyberx ネットワークエンジニアの野澤です！！
今回はネットワークでのソースNATの話です。
CyberxでもWebサーバへの負荷対策のためロードバランサーを使用しています。
簡単に書くと下記の構成になっています。
インターネットA回線 ⇔ ロードバランサー ⇔ Webサーバ
｜
インターネットB回線 ←
ルーティングの設定は以下で対応
----------------------------------
A回線：ロードバランサーでソースNATで対応
B回線：Webサーバ側でデフォルトゲートをB回線へ設定
※A回線経由できたパケットはA回線へ返すという設定です。
トラフックの少ないときには問題なく動いていました。
しかし・・・・・問題が・・・・・
サービス中に一定のトラフック量を超えたらタイムアウトが発生してしまう（苦
何が原因なのかなぁと頭を悩ませました。
その時直感でソースNATが怪しいと思いルーティングの設定を以下に変えました。
ルーティングの設定は以下に変更
----------------------------------
A回線：Webサーバ側でデフォルトゲートをA回線へ設定
B回線：B回線へ返すパケットは１対１のスタテックルーティングで設定
※ソースNATを外しました。
ソースNATを外した結果サービス中に一定のトラフック量を超えてもタイムアウ
トが起きなくなりました。
今回の件で学んだこと
・机上での考え通り動作しないことがある
・NAT設定は思っていた以上に負荷がかかる
１つの障害（課題）をクリアすると自信がついてきますね！！
今回はネットワークでのソースNATの話です。
CyberxでもWebサーバへの負荷対策のためロードバランサーを使用しています。
簡単に書くと下記の構成になっています。
インターネットA回線 ⇔ ロードバランサー ⇔ Webサーバ
｜
インターネットB回線 ←
ルーティングの設定は以下で対応
----------------------------------
A回線：ロードバランサーでソースNATで対応
B回線：Webサーバ側でデフォルトゲートをB回線へ設定
※A回線経由できたパケットはA回線へ返すという設定です。
トラフックの少ないときには問題なく動いていました。
しかし・・・・・問題が・・・・・
サービス中に一定のトラフック量を超えたらタイムアウトが発生してしまう（苦
何が原因なのかなぁと頭を悩ませました。
その時直感でソースNATが怪しいと思いルーティングの設定を以下に変えました。
ルーティングの設定は以下に変更
----------------------------------
A回線：Webサーバ側でデフォルトゲートをA回線へ設定
B回線：B回線へ返すパケットは１対１のスタテックルーティングで設定
※ソースNATを外しました。
ソースNATを外した結果サービス中に一定のトラフック量を超えてもタイムアウ
トが起きなくなりました。
今回の件で学んだこと
・机上での考え通り動作しないことがある
・NAT設定は思っていた以上に負荷がかかる
１つの障害（課題）をクリアすると自信がついてきますね！！
CyberX技術勉強会 #１
皆様はじめまして！！ CyberXの永田と申します。
久しく更新が止まっており、申し訳なく思っております・・・
6月から心機一転、定期的に更新いたしますので、ご期待ください。
今回、先日開催致しました、技術系イベントのお話をさせていただきます。
5月25日（水）弊社主催にて『CyberX 技術勉強会 #1』を開催致しました！！
イベント概要 ：http://p.tl/h3Zi
当日詳細：http://www.kirishikistudios.com/?p=182
（@satullyさんのブログにてまとめられております）
今回は『スマートフォン版のソーシャルアプリができるまで HTML5 + JavaScript』をテーマに
弊社の成功事例、失敗事例などをお話させていただきました。
大勢の方々にご参加頂き、大変嬉しく思っております
初めての開催、運営でしたので、不慣れな事も多く、ご不便おかけした事も多かったのでは・・・
と心配しておりましたが、参加者の方々に有意義な内容だった♪ とのコメントを多く頂き
嬉しく思っております。
開催の目的としましては、業界内でに知見の共有もさる事ながら、外部の方々とのコミュニケーションの
機会の創出も目的としております。
今後も、サイバーエージェントグループや、社外の方々と積極的に開催をしていきます。
また、今回使用しましたセミナールームですが、エンジニア/デザイナー向けの勉強会主催者様には
積極的にお貸できればと思いますので、お気軽にお問合せください。
宜しくお願い致します。
久しく更新が止まっており、申し訳なく思っております・・・
6月から心機一転、定期的に更新いたしますので、ご期待ください。
今回、先日開催致しました、技術系イベントのお話をさせていただきます。
5月25日（水）弊社主催にて『CyberX 技術勉強会 #1』を開催致しました！！
イベント概要 ：http://p.tl/h3Zi
当日詳細：http://www.kirishikistudios.com/?p=182
（@satullyさんのブログにてまとめられております）
今回は『スマートフォン版のソーシャルアプリができるまで HTML5 + JavaScript』をテーマに
弊社の成功事例、失敗事例などをお話させていただきました。
大勢の方々にご参加頂き、大変嬉しく思っております
初めての開催、運営でしたので、不慣れな事も多く、ご不便おかけした事も多かったのでは・・・
と心配しておりましたが、参加者の方々に有意義な内容だった♪ とのコメントを多く頂き
嬉しく思っております。
開催の目的としましては、業界内でに知見の共有もさる事ながら、外部の方々とのコミュニケーションの
機会の創出も目的としております。
今後も、サイバーエージェントグループや、社外の方々と積極的に開催をしていきます。
また、今回使用しましたセミナールームですが、エンジニア/デザイナー向けの勉強会主催者様には
積極的にお貸できればと思いますので、お気軽にお問合せください。
宜しくお願い致します。