Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Index完全に理解した

k-tky
April 30, 2020

 Index完全に理解した

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

k-tky

April 30, 2020
Tweet

More Decks by k-tky

Other Decks in Programming

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設計の手助けになればいいな、と思います。