Index完全に理解した

こちらで発表した資料です
https://easy2.connpass.com/event/173015/presentation/

Transcript

1. Index完全に理解した @k-tky
2. what am I? 所属 ・株式会社TOPGATE やってること ・現場作業員
3. とあるアプリケーションの開発． Develop... APP DB
4. とあるアプリケーションの開発．． Test... APP DB
5. とあるアプリケーションの開発．．． UAT... APP DB
6. ある日突然（突然ではない）．．． UAT... APP DB \ｵｾｰｿﾞ!!!/ \ﾍﾟｰｼﾞｶﾞﾋｮｳｼﾞｻﾚﾅｲﾝﾀﾞｹﾄﾞ ?/ /ﾄﾞｰﾅｯﾃﾝﾀﾞ!\
7. アプリの遅い原因は色々あると思いますが、 今回はDB（MySQL）が悪いことにします。
8. DB \ﾓｳﾑﾘ / SQL SQL SQL SQL SQL SQL SQL SQL
9. あー、Full Scanしてるな Index貼るか
10. よし、Indexアクセスになった な
11. ちょっと待って！そのIndex効率的？ ・indexスキャンになっているが効率的なのか？ 　indexスキャンになっているが効率的ではないことがある ・Indexとは？ ・MySQLのデータ、Indexはどのような構造なのか？ ・Indexを使った時のデータの取得方法は？
12. Indexとは？ ・Indexとは索引のこと ・検索を行う時にIndexを経由してアクセスすることで、処理の高速化が図れる ・複数の列から構成されるIndexを作成することができる ・更新処理時にはIndexもメンテナンスする必要があるため、Indexのメンテナンス分の オーバーヘッドが発生する。 ・実態のデータとは別にIndexデータを保持する分、データ容量も大きくなる
13. MySQLの基本的なデータ、Index構造 MySQLのテーブルデータ構造 ・クラスタリングIndexと呼ばれるツリー構造で データが格納される ・root、branch、leafと辿り、leafにデータが格納さ れる MySQLのIndex構造 ・通常のIndexはB-Treeと呼ばれるツリー構造で格納 される
14. Indexを使った時のデータの取得方法 ・IndexはB-Treeのrootからbranch、leafへと順次データを読み込む ・leafに格納された主キーの情報を使ってテーブルデータを取得する ・テーブルデータの読み込みも rootからbranch、leafへと順次データを読み込 み、leafに格納されたデータを取得する
15. Indexを効率的に使えるようにするには？ ・Indexを使う目的を考えてみる 　検索を早くしたい ・Indexを使っても遅い原因とは？ 　データブロックの読み込みが多いから 　データブロックの読み込みはIOが発生するため、非常に低速
16. どのようなカラムにIndexをつけるのが効率的？ ・カーディナリティが高いカラムにつける 　カーディナリティとは、全レコード数に対するデータの種類の割合 　100レコードに対してフラグなど真偽値をとる場合（2種類）は低い 　100レコードに対してデータ内容が50種類ある場合は高い ・概ね20%程度にIndexで検索結果を絞れると最大効率的だと言われています。
17. カーディナリティの高いデータのアクセスパス ・Index→テーブルデータの順でアクセスする。 　結果：読み込むデータブロックの量が少なくなる。 　Indexのデータブロックを読み込み、テーブルデータのデータブロックを読み込む
18. カーディナリティの低いデータのアクセスパス ・Index→テーブルデータの順でアクセスする。 　結果：読み込むデータブロックの量が多くなる。 　Indexのデータブロックを読み込み、テーブルデータのデータブロックを読み込む
19. MySQLの全表アクセスパス（フルスキャン） ・全表スキャンはデータ構造のroot、branch、leafのデータブロックを読み込む 　leafにデータが格納されているので、 leafは横方向に読み込んでいく
20. Indexを効率的に使えるようにするには？（再度） ・そもそも遅い原因とは？（振り返り） 　データブロックの読み込みが多い ・Indexを使ってデータブロックの読み込みを少なくする 　Indexを使うことによってデータブロックの読み込みがどのように行われるか？ 　Indexを使うことによってデータブロックの読み込みが多くなっていないか？
21. なお ・DBによってアクセスパスは変わります。 ・テーブルデータ構造も変わります。 ・DBによって特性も変わってきますので、その辺りも考慮しつつ設計してみましょう。 ・※データブロックを読み込む量を減らすことを目的としてIndexを貼ることは概ね共通だ と思います。（検索が遅い理由は基本的にデータブロックの読み込みが多いことかと）
22. まとめ ・Indexのメリット、デメリットを理解する 　よっぽどではない限り、検索が優先されると思うので貼る方向にはなると思います ・Indexは貼ればいいということではない ・Indexスキャンになっているからといって、必ずしも効率的なデータ取得がされているわ けではない ・DBごとのデータ構造、Indexの種類ごとの構造、アクセスパスを理解することで効率的 なIndex設計の手助けになればいいな、と思います。