厂蚕尝アンチパターン（インデックスショットガン）
- 2. アジェンダ マスタ テキストの書式設定 アンチパターンの前の予備知識 ? インデックスについて ? オプティマイザ（統計情報、実行計画）について アンチパターン「インデックスショットガン」 ? 目的 ? アンチパターン ? アンチパターンの見つけ方 ? アンチパターンを用いてもよい場合 ? 解決策 ? 備考（検証した環境について） -1 -
- 3. インデックスについて：仕組み インデックスを使用することによりパフォーマンスが改善するのは、 検索処理のボトルネックとなる マスタ テキストの書式設定 ディスク読み込みを最小限に抑えることができるからである。 例．SELECT * FROM Bugs WHERE bug_id = 27; ①インデックスなし ②インデックスあり Bugs PK_Bugs ?1,677 万件 ? 736 MByte ? 94,208 ブロック 1-49 Bugs 50-100 ??? 1-24 25-49 bag_id 25 26 27 ??? レコードの場所が特定出来ない → テーブル全件読込 ROWID ID12345 ID12346 ID12347 ??? インデックスにてレコードの場所が特定出来る → インデックス＋テーブルの1部のみ読込 読込ブロック数 読込データサイズ 所要時間 ①インデックスなし 93,084 727 MByte 7.0 秒 ②インデックスあり 4 0.03 MByte 0.06 秒 -2 -
- 4. インデックスについて：注意点 インデックスを使用しても常に速くなるわけではない。 マスタ テキストの書式設定 ? 取得レコード数がテーブル全体の10%以下 ??? インデックスを使用した方が速い ? 取得レコード数がテーブル全体の10%を超える ??? テーブルフルスキャンが速い ※上記閾値はDBや環境にてよって変わるが、一般的に5%～15%の範囲。 ? 値の種類が少ないカラムについては、インデックスを作成しても役に立たない （例．性別、血液型） ? 値の種類が多いカラムについては、インデックスの効果が高い為、作成候補となる。（例．社員番号） この例の場合、 約14%（2,475,347件 / 16,777,216件）以下であるかが インデックス使用の閾値となっている ※インデックスを使用する／しないの判断は、オプティマイザが行う。 SELECT * FROM BUGS WHERE BUG_ID BETWEEN 1 AND 2475347; -- 実行計画（インデックス使用）-SELECT STATEMENT Cost = 25499 TABLE ACCESS BY INDEX ROWID BUGS INDEX RANGE SCAN PK_BUGS SELECT * FROM BUGS WHERE BUG_ID BETWEEN 1 AND 2475348; -- 実行計画（インデックス未使用）-SELECT STATEMENT Cost = 25499 TABLE ACCESS FULL BUGS -3 -
- 5. オプティマイザについて SQLはデータ抽出内容（What）のみを記述し、データ抽出方法（How）は記述しない言語である。デ マスタ テキストの書式設定 ータ抽出方法（How）の決定はオプティマイザが担当する。 OUTPUT INPUT 【SQL】 SELECT * FROM Bugs WHERE bug_id = 27 採用 【実行計画 A：インデックススキャン】 SELECT STATEMENT Cost = 3 TABLE ACCESS BY INDEX ROWID BUGS INDEX UNIQUE SCAN PK_BUGS オプティマイザ 【統計情報】 ?システム統計 （CPU、I/Oの処理性能） ?オブジェクト統計 （テーブル、カラム、インデックスの 件数、サイズ、分布） 【その他】 ?DBパラメータ ?オブジェクト構造 etc. 不採用 【実行計画 B：テーブルフルスキャン】 SELECT STATEMENT Cost = 25487 TABLE ACCESS FULL BUGS 統計情報と実体に乖離がある場合、 パフォーマンス障害が発生することがある。 オプティマイザが最適な実行計画を算出するよう、統計情報を適切なタイミングで最新化する必要がある。 統計情報が正しくない場合、期待されるインデックスが使用されないこともある。 -4 -
- 6. 目的 この章の目的は、「インデックスを効果的に使用し、パフォーマンスを最適化（パフォーマンスチュー マスタ テキストの書式設定 ニング）すること」である。 【パフォーマンスチューニング方法】 ① インデックスの追加／ヒント句の追加 ② SQLの修正／テーブル構造の変更（パーティション化） ③ プログラムコードの修正 ④ データベースの設定変更 ⑤ その他ミドルウェア?OSの設定変更 ⑥ ハードウェアの変更 影響範囲?対応コスト 小 影響範囲?対応コスト 大 「①インデックスの追加／ヒント句の追加」がよく使用される 理由１： 影響範囲?対応コストが最も小さい（SQL取得結果への影響はない為、テストの簡略化が可能） 理由２： ②以降の方法より改善幅が大きい（ことが多い） データベースのパフォーマンスを改善する最善の方法は、インデックスを効果的に使用することである。 -5 -
- 8. ケース１：インデックスを定義しない、もしくは少ししか定義しない マスタ テキストの書式設定 「インデックス更新によるオーバーヘッドがDBの負荷になるの定義しない方がよい？」との誤解 → 答えは当然 No！ 理由１：インデックスによる検索処理の改善によりオーバーヘッドを十分に取り戻せる。（特に、テーブ ル全体の件数が多い時に有効） Bugsテーブル （約1700万件）から 1レコード取得 L_Bugsテーブル （約1億3000万件）から 1レコード取得 インデックスなし 7.0 秒 43.3 秒 インデックスあり 0.06 秒 0.06 秒 【クエリ】 SELECT * FROM Bugs WHERE bug_id = 27 理由２：UPDATEおよびDELETEにおいてもインデックスは使用される。（更新、削除レコードを特定） Bugsテーブル （約1700万件）から 1レコードUPDATE L_Bugsテーブル （約1億3000万件）から 1レコードUPDATE インデックスなし 7.0 秒 47.4 秒 インデックスあり 0.09 秒 0.16 秒 -7 - 【クエリ】 UPDATE Bugs SET HOURS = 10 WHERE bug_id = 27
- 9. ケース２：インデックスを定義しすぎる、もしくは役に立たないインデックスを定義する 使用されないインデックスを作成するメリットはなく、デメリットのみである。 マスタ テキストの書式設定 -- 役に立たないインデックスの例 CREATE TABLE Bugs ( bug_id SERIAL PRIMARY KEY, date_reported DATE NOT NULL, summary VARCHAR(80) NOT NULL, status VARCHAR(10) NOT NULL, hours NUMERIC(9,2), -- ↓①bug_idはPKであり、インデックスが自動生成されている為、無駄となる INDEX (bug_id), -- ↓ ②インデックスは前方一致検索のみ有効の為、利用される機会が少なく、無駄となる -- （例．画面A%はインデックス有効だが、%画面A%は無効となる） INDEX (summary), -- ↓ ③時間を条件にした検索は想定されない為、無駄となる INDEX (hours), -- ↓ ④複合インデックスは左から絞り込まれる -bug_idの時点で一意となる為、data_reportedおよびstatusをキーとする意味なし、無駄 INDEX (bug_id, date_reported, status) ); -8 -
- 10. ケース３：インデックスを活用しないクエリを実行してしまう クエリによってはインデックスが活用されないことがある為、インデックスを活用されるようクエリを マスタ テキストの書式設定 記述する必要がある。インデックスを活用しないクエリの代表的なケースは以下のとおり。 インデックスを 活用しないケース 暗黙の型変換 NULLの条件 索引カラムに 関数や演算を使用 サンプルクエリ × SELECT * FROM Bugs WHERE bug_id = ‘1’; -- NUMBER型に文字列指定 ○ SELECT * FROM Bugs WHERE bug_id = 1; × SELECT * FROM Bugs WHERE bug_id IS NULL; × SELECT * FROM Bugs WHERE MONTH(date_reported) = 4; × SELECT * FROM Bugs WHERE bug_id + 1 = 10; ○ SELECT * FROM Bugs WHERE bug_id = 10 - 1; CREATE INDEX IdxDateReported ON Bugs(MONTH(date_reported)); ○ SELECT * FROM Bugs WHERE MONTH(date_reported) = 4; 中間一致、後方一致 × SELECT * FROM Bugs WHERE description LIKE '%crash%'; × SELECT * FROM Bugs WHERE description LIKE '%crash'; ○ SELECT * FROM Bugs WHERE description LIKE 'crash%'; 複合インデックスの 2番目以降を指定 CREATE INDEX TelephoneBook ON Accounts(last_name, first_name); × SELECT * FROM Accounts WHERE first_name = ‘Charles’ ○ SELECT * FROM Accounts WHERE last_name = ‘Charles’ NOTを使用 × SELECT * FROM Bugs WHERE bug_id <> 1; -9 -
- 11. アンチパターンの見つけ方 以下の発言に注意。 マスタ テキストの書式設定 ? このクエリを高速化するにはどうすればいい？ → テーブル、インデックス、データ量、パフォーマンスの測定と最適化などを調査後、相談する べき。 ? フィールド全部にインデックスを定義したのに、なぜ実行速度が速くならないのだろう？ → ケース２に該当。「インデックススナイパーライフル（的確インデックス）」を心掛ける。 ? データベースを遅くすると書いてあるのを何かで読んだことがある。それ以来、インデックスは 使わないようにしている → ケース１に該当。「改善効果＞インデックス更新のオーバーヘッド」の為、インデックスは有効 活用するべき。 -10 -
- 13. 解決策 「MENTOR」の原則に基づいて、効果的なインデックス管理を行う。 マスタ テキストの書式設定 個別的なパフォーマンス対応 Mesure（測定） ??? DB処理を測定し、パフォーマンスが悪いクエリを抽出。 ↓ Explain（解析） ??? クエリが遅くなっている原因を解析。 ↓ Nominate（指名） ??? クエリ内のボトルネック（インデックス未定義等）を特定。 ↓ Test（試験） ??? ボトルネック（インデックス追加等）を解消し、処理時間を測定。 全体的なパフォーマンス対応 Optimize（最適化） ??? キャッシュサイズ等、DBパラメータを最適化。 定期メンテナンスとして実施 Rebuild（再構築） ??? インデックスを再構築する。 -12 -
- 14. Mesure（測定） ※個別的なパフォーマンス対応① DB機能にてSQLパフォーマンスの測定をすることにより、ボトルネックSQLを抽出する。 マスタ テキストの書式設定 障害表の検索レスポンスが遅い時がある。調査して欲しい。 ユーザ 測定結果を確認したところ、「日付を条件にした検索SQL」で55秒もかかってい る。 DBA ＜Oracleの場合＞ AWRレポート機能を使用することにより、DB負荷状況および所要時間 が長いSQLを抽出できる。 SELECT * FROM L_BUGS WHERE TO_CHAR(DATE_REPORTED, 'YYYYMMDD') = '20131119' -13 -
- 15. Explain（解析） ※個別的なパフォーマンス対応② ボトルネックとなっているSQLの処理が遅い原因を解析する。 マスタ テキストの書式設定 DBA 該当SQLの実行計画をみたところ、L_BugsテーブルへのTABLE ACCESS FULLがボトルネックになっているようだ。 ＜Oracleの場合＞ AWR SQL レポート機能を使用することにより、SQLの実行統計および 実行計画を抽出できる。 -14 -
- 17. Test（試験） ※個別的なパフォーマンス対応④ インデックス作成後、再びSQLのパフォーマンス測定を行う。 マスタ テキストの書式設定 INDEX RANGE SCANにより、ディスク読込が大幅に減少した結果、 日付インデックスを追加したところ、所要時間が54秒から21秒に改善された。 DBA -16 -
- 19. Rebuild（再構築） ※定期メンテナンスとして実施 インデックスは長期にわたるレコードの更新や削除に伴い、不均衡となる為、定期的なメンテナンス マスタ テキストの書式設定 が必要。 ? レコード件数に比例して所要時時間（DB負荷）がかかる為、注意。 ? PK_L_Bugs（bug_idのみのインデックス：約1億3000万件）を再構築した場合、約6分半。 ? 最適化のタイミングはシステムに実情に合わせ、要検討。 ALTER INDEX PK_L_BUGS REBUILD インデックスが変更されました(403808 msec.) 【この章のまとめ】 ?インデックスを有効に活用するには、インデックスの仕組みを理解する。 ?その上でデータとクエリについての理解も深め、MENTORの原則に基づいてインデックスを管理する。 ?そうすれば、パフォーマンスがよいシステムを構築できる。（みんなハッピー） -18 -
- 20. 備考（検証した環境について） ■検証用DBサーバ マスタ テキストの書式設定 モデル CPU メモリ Di s k H /W 拡張スロット 1 2 3 4 1 2 3 4 Dell PowerEdge T300 クアッドコア Intel Xeon 2.5GHz クアッドコア 1GB DDR2/667MHz registerd SDRAM DIMM ECC ×２ 1GB DDR2/667MHz registerd SDRAM DIMM ECC ×２ 1GB DDR2 667 (TRANSCEND) × ２ メモリモジュール 3枚構成はT300はサポートされていない。 最大4TB （4 x SATA 1TB） 250GB 3.5インチ SATAⅡハードディスク (7200回転) (WD製) 250GB 3.5インチ SATAⅡハードディスク (7200回転) (WD製) 500GB HDD(WD製) 500GB HDD(WD製) 16倍速 SATA DVD+/-RW Drive - ドラ イブ デ ィスプ レイ キ ー ボー ド マウス ソフトウェア名 CentOS Oracle Database 11g version 5.5 (Final) 11.1.0.7.0 S/W ■検証用データ 種別 TABLE INDEX TABLE INDEX INDEX テーブル／インデックス名称 BUGS PK_BUGS L_BUGS PK_L_BUGS IDX_DATE_REPORTED レコード件数 16,777,216 － 134,217,728 － － -19 - サイズ（MByte） 736 296 5,888 2,439 2,944 用途 OS DB