![© CROOZ,Inc. 43
４． WHERE句での注意点 その１
ちなみに私だったら面倒なのでこうします
ユーザーテーブル
Id 年齢 大学生 …
1 17 0 …
2 21 1 …
3 19 1 …
4 25 0 …
5 18 1 …
6 22 1 …
WHERE 大学生 = 1
勿論事前に[大学生]列にインデックスを
貼っておき、[年齢]更新時にUPDATE
します。
※ロジック上多少の誤差は出ます](https://image.slidesharecdn.com/mysql-index-130909040643-/85/MySQL-Index-43-320.jpg)
MySQL Index勉強会外部公開用
- 1. © CROOZ,Inc. 1 MySQL INDEX勉強会 技術統括本部 鈴木 優一
- 2. © CROOZ,Inc. 2 本日の内容 ・ INDEXとはなにか ・ 種類と構造 ・ クエリオプティマイザの流れ ・ 使いどころ ・ 効率よくインデックスをつかうために ・ EXPLAINのみかた
- 5. © CROOZ,Inc. 5 １．INDEXとはなにか 例）1組のトランプから「スペードの６」を探す 前提） ランダムに並んでいる ⇒ インデックスがない状態 カードを引く回数を最も少なくして、探すには どのように探しますか？
- 6. © CROOZ,Inc. 6 １．INDEXとはなにか 並び順がランダムだと… おおよそどの辺にあるのかが予測できない 例えば、上から順番に探すことになります 検索回数の最小は1回、最大54回 ⇒ 平均で24回の検索が必要です
- 8. © CROOZ,Inc. 8 １．INDEXとはなにか 事前に並べ変えておくと おおよそどの辺にあるのかが予測できる 例えば、真ん中のカードを一枚引き、検索 対象より前かあとかで探していく ⇒ ２分検索という
- 12. © CROOZ,Inc. 12 １．INDEXとはなにか 何枚目がなにかを事前にメモっておけば良い！ スート ランク 位置 ハート Q 1 スペード 9 2 : : : ダイヤ 1 40 : : : スペード 6 45 なぜなら、要求は「カードを引く回数を最も少なく してスペード6を探すこと」 セコいとかおもうヒトはいるか もしれませんが何もセコいこ はしていないです。 前提条件など一切ないから このメモさえあれば位置を指定して1回引けば済む
- 15. © CROOZ,Inc. 15 １．INDEXとはなにか（システム寄りに） 今回の例 select スート, ランク from トランプ where スート = ‘スペード’ AND ランク = ‘6’; このクエリを最も効率よく実行するためにはどの ようにしたらよいか？
- 20. © CROOZ,Inc. 20 ２．種類と構造 超簡単に言うと２分検索アルゴリズムの進化版 B+Treeとは ⇒２分検索については8ページを見てください ルートブロック ブランチブロック リーフブロック 1000 500 1500 1:位置 ： 499:位置 500 :位置 ： 999 :位置 1000 :位置 ： 1499 :位置 1500 :位置 ： 1999 :位置 例 999を検索したい この位置を指定して テーブルを検索する
- 22. © CROOZ,Inc. 22 ２．１ クラスタインデックスの特徴 メリット ・検索が早い 特に主キーに対する検索が早い デメリット ・インデックスサイズが大きい ・条件によってセカンダリインデックスより遅い なぜかを知るためにクラスタインデックスの 仕組みを見てみましょう
- 23. © CROOZ,Inc. 23 ２．１ クラスタインデックスの特徴 ルートブロック ブランチブロック リーフブロック 1000 500 1500 1:データ ： 499:データ 500 :データ ： 999 :データ 1000 :データ ： 1499 :データ 1500 :データ ： 1999 :データ 特徴① リーフブロックにデータが直接格納されている 特徴② データがソートされているため隣接したINDEXが 同じリーフブロックに存在する
- 24. © CROOZ,Inc. 24 ２．１ クラスタインデックスの特徴 特徴① リーフブロックにデータが直接格納されている 特徴② データがソートされているため隣接したINDEXが 同じリーフブロックに存在する ここから分かること ・主キーに対する検索が早い 何故なら、INDEX内にデータを持っていてテーブルを 見なくてよいから ・INDEXサイズが大きい 何故なら、必ずソートされているから ・少ない回数で検索できる
- 25. © CROOZ,Inc. 25 ２．１ クラスタインデックスの特徴 メリット ・検索が早い 特に主キーに対する検索が早い デメリット ・インデックスサイズが大きい ・条件によってセカンダリインデックスより遅い だから
- 26. © CROOZ,Inc. 26 ２．２ セカンダリインデックスの特徴 メリット ・場合によって検索が早い あとで説明します… デメリット ・場合によらない場合は検索が遅い なぜかを知るためにセカンダリインデックスの 仕組みを見てみましょう
- 27. © CROOZ,Inc. 27 ２．２ セカンダリインデックスの特徴 特徴① クラスタインデックスと組み合わせて利用する 特徴② リーフブロックにはデータではなく、主キーの値を 格納する リーフ ブロック ルート ブロック ブランチ ブロック キー：場所 ： キー：1700 500 :データ ： 999 :データ 1000 :データ ： 1499 :データ 1500 :データ ： 1999 :データ クラスタインデックスセカンダリインデックス
- 28. © CROOZ,Inc. 28 ２．２ セカンダリインデックスの特徴 見てわかるとおりひと手間多いです ではどういう場合にメリットは何か？ 検索条件で参照する列がすべてインデックスに 含まれている場合 なぜなら 必要な情報がすべてリーフブロックに含まれている ↓ 検索対象をクラスタインデックスを利用せずに特定可能 ↓ 検索回数が減る
- 29. © CROOZ,Inc. 29 ２．２ セカンダリインデックスの特徴 メリット ・場合によって検索が早い 検索列すべてがインデックスに含まれる場合 デメリット ・上記以外は検索が遅い だから 検索列をすべてインデックスに含め、インデックス内で検索を 完結させることをカバリングインデックスと呼びます
- 30. © CROOZ,Inc. 30 ２．３ カバリングインデックス カバリングインデックスについて以下の2つがあります 複合インデックス 1つのインデックスキーに複数のカラムを含めた もの インデックスマージ 複数のインデックスキーを組み合わせて結合 したもの
- 31. © CROOZ,Inc. 31 ２．３ カバリングインデックス イメージで見るとこんな感じ 複合インデックス キー名 種別 カラム idx_familiy INDEX First Name Middle Name Family Name インデックスマージ キー名 種別 カラム idx_first INDEX First Name idx_middle INDEX Middle Name idx_family INDEX Family Name 事前に作成されるわけではなく、 クエリ実行時に生成される 複合インデックスは存在していないが、単一インデックス が検索項目に含まれる場合、インデックスマージが行わ れています。
- 32. © CROOZ,Inc. 32 ２．３ カバリングインデックス ただし、 クエリ実行時に複数のインデックスを読み込んでから結合 するため、効率が良くない。 可能な限り複合インデックスを利用すべき
- 34. © CROOZ,Inc. 34 ３． SQLが実行される流れ 皆さんはアプリケーションから発行されたSQLがデータ ベースサーバ内でどのように処理されているか考えた ことはありますか？
- 35. © CROOZ,Inc. 35 ３． SQLが実行される流れ 大まかには以下の作業を行っています アプリケーション PARSE テーブルのチェック 構文チェック SQL書き換え 実行計画作成 実行計画をキャッシュ SQL 実行 EXECUTE データ 取り出し FETCH
- 36. © CROOZ,Inc. 36 ３． SQLが実行される流れ よくDBをただのデータ格納場所だと思っている人がいる かと思いますが、実はいろいろ複雑なことをしています 特にPARSEのなかのSQLの書き換え、実行計画作成の 部分の処理のことを「SQL最適化」といいDBMS製品の パフォーマンスを大きく左右する部分です。 そして、SQL最適化を行うプログラムを 「クエリオプティマイザ」といいます。 この「クエリオプティマイザ」こそがDBMSの肝となります
- 37. © CROOZ,Inc. 37 ３． クエリオプティマイザの役割 クエリオプティマイザは、最も早くSQLを実行できるため には内部的にどのように検索するのが良いかを推定し ます。 例えば… ・どのINDEXを使うか ・抽出と結合をどちらが先にやったほうが早いか ・フルテーブルスキャンを行うべきか など… このクエリオプティマイザにより、アプリケーションは どのインデックスを使うかやソートアルゴリズムを考えず SQLだけでデータ操作ができます
- 38. © CROOZ,Inc. 38 ３． クエリオプティマイザの役割 従って、 単にインデックスを付加してもクエリオプティマイザが インデックスで検索しないほうが実行が早いと判断した 場合、インデックスが無視されます 例えば、 ・極端にレコードが少ない場合 ⇒インデックスを読むコストより、フルテーブルスキャンの方が 早い ・複合インデックスがすべての検索条件を満たさない場合 ⇒インデックスマージのコストが極端に多いと判断された場合 単にインデックスを付加したからといって必ず効果が出るとは 思わないでください
- 40. © CROOZ,Inc. 40 ４． INDEXの使いどころ 基本は３つだけです。 ・抽出条件として検索する列に作成する 抽出する列ではなく検索する列に対して作成します ・１つのSQLでは1つのインデックスしか参照できない MySQLの仕様です。 だからインデックスマージという仕組みがあります ・クラスタインデックスは早い MySQLに設定するときのインデックスの種類は、 PRIMARY、INDEX、UNIQUE、FULLTEXTの4つです この中でPRIMARY、UNIQUEはクラスタインデックス なので検索が早い
- 41. © CROOZ,Inc. 41 ４． INDEXの使いどころ これ以外は、ケースバイケース。 経験を積むしか無いです ある程度の法則性はあるので気をつけるべき箇所を 挙げておきます。
- 42. © CROOZ,Inc. 42 ４． WHERE句での注意点 その１ 否定及び範囲指定(<など)はインデックスが効きません ユーザーテーブル Id 年齢 … 1 17 … 2 21 … 3 19 … 4 25 … 5 18 … 6 22 … WHERE 年齢 !=17 AND 年齢 !=19 WHERE 年齢>=18 AND 年齢<=22 ⇒ WHERE 年齢 IN(18,19,20,21,22) ⇒ WHERE 年齢 =18 OR 年齢 =19 OR 年齢 =20 OR 年齢 =21 OR 年齢 =22 INやWHEREで代用します
- 43. © CROOZ,Inc. 43 ４． WHERE句での注意点 その１ ちなみに私だったら面倒なのでこうします ユーザーテーブル Id 年齢 大学生 … 1 17 0 … 2 21 1 … 3 19 1 … 4 25 0 … 5 18 1 … 6 22 1 … WHERE 大学生 = 1 勿論事前に[大学生]列にインデックスを 貼っておき、[年齢]更新時にUPDATE します。 ※ロジック上多少の誤差は出ます
- 44. © CROOZ,Inc. 44 ４． WHERE句での注意点 その２ すべてのANDにかかっていないINDEXは使用できない WHERE FirstName = ‘努’ AND MiddleName = ‘-’ OR FirstName = ‘太郎’ AND FamilyName = ‘山田’ ⇒ インデックスが貼ってあっても「idx_first」のみしか使用でき ません インデックス キー名 種別 カラム idx_first INDEX FirstName idx_middle INDEX MiddleName idx_family INDEX FamilyName
- 45. © CROOZ,Inc. 45 ４． WHERE句での注意点 その３ あいまい検索時は前方一致のインデックスが使用可能 WHERE FirstName LIKE ‘努%’ 前方一致 WHERE FirstName LIKE ‘%努’ 後方一致 WHERE FirstName LIKE ‘%努%’ 部分一致 WHERE FirstName LIKE ‘努’ 定数
- 46. © CROOZ,Inc. 46 ４． WHERE句での注意点 その４ 複合インデックスの場合、前のキーが範囲検索だと 以降のキーのインデックスが効かない場合がある WHERE FirstName > ‘A’ AND MiddleName = ‘John’ AND FamilyName = ‘Sakai’ ⇒ FirstName以降インデックスが使用されない可能性が あります インデックス キー名 種別 カラム Idx_name INDEX FirstName MiddleName FamilyName
- 47. © CROOZ,Inc. 47 ４． ORDER BY句での注意点 その１ 連続しないキーにはインデックスが使用できない ORDER BY FirstName , FamilyName ⇒ MiddleNameを含める インデックス キー名 種別 カラム Idx_name INDEX FirstName MiddleName FamilyName ORDER BY FirstName , MiddleName , FamilyName
- 48. © CROOZ,Inc. 48 ４． ORDER BY句での注意点 その２ 複数のキーがある場合はインデックスが使用できない ORDER BY FirstName , FamilyName インデックス キー名 種別 カラム idx_first INDEX FirstName idx_middle INDEX MiddleName idx_family INDEX FamilyName ⇒ インデックスが効かない クエリオプティマイザの判断で インデックスマージが行われる場合がある 複合インデックスを利用するのがベター
- 49. © CROOZ,Inc. 49 ４． ORDER BY句での注意点 その３ 昇順、降順が混在している場合には インデックスが使用できない ORDER BY FirstName DESC , FamilyName ASC インデックス キー名 種別 カラム Idx_name INDEX FirstName MiddleName FamilyName また降順の場合、複合インデックスのすべてのキーを 降順にしなければならない
- 50. © CROOZ,Inc. 50 ４． ORDER BY句での注意点 その３ GROUP BY列とORDER BY列が異なる場合、インデックス は使用できない GROUP BY FirstName , FamilyName ORDER BY FirstName インデックス キー名 種別 カラム Idx_name INDEX FirstName MiddleName FamilyName この場合、内部的には一度テンポラリテーブルを作成して から再度クエリを発行するという動作をします。 EXPLAIN を実行するとUsing temporaryが表示されます
- 51. © CROOZ,Inc. 51 ４． HIVING句での注意点 HAVING句はインデックスが効きません スマートにクエリを書きますが速度が極端に遅くなるの で極力使わないようにしましょう
- 52. © CROOZ,Inc. 52 ４． ソートに注意 クエリの中にはクエリ実行時にソートが発生するものが あります。 ソートが発生するものについてはユニークインデックス 以外は効果的に機能しません 具体的には以下のとおりです ・MIN ・MAX ・UNION ・DISTINCT
- 54. © CROOZ,Inc. 54 ５． 効率よくインデックスを使うために クエリオプティマイザは最も早くクエリを実行できる用に 実行計画を立てます。 つまり絞り込んだ際にレコード数が最も少なくなるように インデックスを選択します このようなインデックスのことを「選択性が高い」 インデックスといいます 選択性の高いものの代表は以下のとおりです ・主キー ・ユニークキー 選択性の低いものの代表は以下のとおりです ・フラグ系（特に分散が同じようなものは無意味）
- 55. © CROOZ,Inc. 55 ５． 効率よくインデックスを使うために 具体的には ・性別 ・削除フラグ 単体でインデックスを付けてもあまり効果がありません
- 56. © CROOZ,Inc. 56 ５． 効率よくインデックスを使うために また複合インデックスは、選択制が高いカラムから順に 列を作成します インデックス キー名 種別 カラム Idx_name INDEX user_id item_id delete_flg 最もレコードを絞れる 次にレコードを絞れる 1か0かしかない SQLを書くときにも選択制が高い順に書きます WHERE user_id=15321 AND item_id = 45312 AND delete_flg = 0
- 57. © CROOZ,Inc. 57 ５． 効率よくインデックスを使うために もう一点注意です WHERE句内の場合、複合インデックスの先頭の列は 単体インデックスとしても使用できます インデックス キー名 種別 カラム Idx_name INDEX user_id item_id delete_flg 単体インデックスとしても使用可能 複合インデックス作成時はこの点も考慮に入れて下さい
- 58. © CROOZ,Inc. 58 ５． 効率よくインデックスを使うために 最後にもう一点注意です クエリオプティマイザは最も早くクエリを実行できる用に 実行計画を立てるだけで、実際に最も早くクエリを実行し ない場合がります。 例えばEXPLAINで見た際に論理上明らかにインデックス を参照したほうが速いはずなのに、インデックスを利用し ていないなど… この場合、FORCE INDEX(インデックス名)で使用するイン デックスを強制できます
- 60. © CROOZ,Inc. 60 ６． EXPLAINについて EXPLAINとは実行計画と結果を表示する機能 使い方 SQLの実行前に EXPLAINをつける EXPLAINからわかること ・どのインデックスが使われているか ・どのような結合が行われているか ・ソートが内部的に行われているか
- 61. © CROOZ,Inc. 61 ６． EXPLAINについて EXPLAIN実行時の注意点 絶対本番ではやらない 世間様に迷惑はかけないでください 対象テーブルデータをバックアップDBからdevに コピーするして行ってください クエリキャッシュがあることを意識してください 頻繁に実行されるクエリはDBMSにキャッシュされます RESET QUERY CACHEコマンドを実行してキャッシュを クリアしてください
- 62. © CROOZ,Inc. 62 ６． EXPLAINについて クエリキャッシュとは アプリケーション PARSE テーブルのチェック 構文チェック SQL書き換え 実行計画作成 実行計画をキャッシュ SQL 実行 EXECUTE データ 取り出し FETCH よく使うクエリのParse内容を キャッシュすることで2回目以降 のParge過程を省略する機能
- 63. © CROOZ,Inc. 63 ６． EXPLAINについて 実際に見てみましょう バックアップDBに対し 分析対象のクエリを書く 「実行する」ボタンを押す
- 64. © CROOZ,Inc. 64 ６． EXPLAINについて 実際に見てみましょう 「EXPLAINで確認」ボタンを押す
- 65. © CROOZ,Inc. 65 ６． EXPLAINについて 実際に見てみましょう 解析結果
- 66. © CROOZ,Inc. 66 ６． EXPLAINについて 各列の意味 列名 意味 id SQLの中でSELECT文をするid select_type SQLの種類 type 参照先テーブルへのアクセス手段 possible_keys オプティマイザが使用するインデックスの候補として挙げたキーの一 覧 key possible_keysの中から実際にオプティマイザが選択した物 ref 検索時にkeyの比較対象となっているカラム。定数の場合はconstと表 示される。 rows クエリによって取得が想定される行数。実際に取得された数ではない ところに注意が必要。実際やってみると結構違う。 Extra クエリの追加情報。Using filesort、Using temporaryが表示された場合 は注意が必要。 注目すべきはtypeとExtra
- 67. © CROOZ,Inc. 67 ６． EXPLAINについて typeの各項目の意味 値 意味 const PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセ ス。最速。 eq_ref JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセ スタイプ。constと似ているがJOINで用いられるところが違う。 ref ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検 索（WHERE key = value）を行った時に使われるアクセスタイプ。 range インデックスを用いた範囲検索。 index フルインデックススキャン。インデックス全体をスキャンする必要が あるのでとても遅い。 ALL フルテーブルスキャン。インデックスがまったく利用されていないこ とを示す。OLTP系の処理では改善必須 Index及びALLが出ていたらキケン
- 68. © CROOZ,Inc. 68 ６． EXPLAINについて Extraの各項目の意味 値 意味 Using where WHERE句に検索条件が指定されており、なおかつインデックスを見た だけではWHERE句の条件を全て適用することが出来ない場合に表示さ れる。 Using index クエリがインデックスだけを用いて解決できることを示す。Covering Indexを利用している場合なども表示される。 Using filesort filesort（クイックソート）でソートを行っていることを示す Using temporary JOINの結果をソートしたり、DISTINCTによる重複の排除を行う場合な ど、クエリの実行にテンポラリテーブルが必要なことを示す。 Using index for group-by MIN()/MAX()がGROUP BY句と併用されているとき、クエリがインデッ クスだけを用いて解決できることを示す。 Range checked for … JOINにおいてrangeまたはindex_mergeが利用される場合に表示される。 ほかにもある… Using where、 Using tempraryが出ていたらキケン
- 69. © CROOZ,Inc. 69 ６． EXPLAINについて 今回の例でいうと ユニークではないインデックスで等価検 索されている user_id_worker_typeを 利用しようと選択 ただし絞り込み条件でインデックスではなく WHEREで検索されている
- 70. © CROOZ,Inc. 70 ６． EXPLAINについて インデックス項目 検索列 user_id, worker_type, delete_flg user_id_worker_typeが効いていない
- 97. © CROOZ,Inc. 97 実際にやってみよう ４．EXPLAINを外して実測 シミュレーション条件 ・スレッド数：５０ ・ランプアップ間隔：0.1 ・ループ回数：10 ⇒ 簡単に言うと、50人のユーザが0.1秒ごとに 10回アクセスしていることとほぼ同じ 以下のパターンについて比較を実施 ・現状 ・複合インデックスにdelete_flg追加 ・インデックスなし（参考用）
- 109. © CROOZ,Inc. 109 まとめ