• 検索結果がありません。

このドキュメントに記載されている情報 (URL 等のインターネット Web サイトに関する情報を含む ) は 将来予告なしに変更することがあります このドキュメントに記載された内容は情報提供のみを目的としており 明示または黙示に関わらず これらの情報についてマイクロソフトはいかなる責任も負わないもの

N/A
N/A
Protected

Academic year: 2022

シェア "このドキュメントに記載されている情報 (URL 等のインターネット Web サイトに関する情報を含む ) は 将来予告なしに変更することがあります このドキュメントに記載された内容は情報提供のみを目的としており 明示または黙示に関わらず これらの情報についてマイクロソフトはいかなる責任も負わないもの"

Copied!
30
0
0

読み込み中.... (全文を見る)

全文

(1)

2 - SQL の最適化

(2)

1

このドキュメントに記載されている情報 (URL 等のインターネット Web サイトに関する情報を含む) は、将来予告なしに変更するこ とがあります。このドキュメントに記載された内容は情報提供のみを目的としており、明示または黙示に関わらず、これらの情報につ いてマイクロソフトはいかなる責任も負わないものとします。

お客様が本製品を運用した結果の影響については、お客様が負うものとします。お客様ご自身の責任において、適用されるすべての著 作権関連法規に従ったご使用を願います。このドキュメントのいかなる部分も、米国 Microsoft Corporation の書面による許諾を受け ることなく、その目的を問わず、どのような形態であっても、複製または譲渡することは禁じられています。ここでいう形態とは、複 写や記録など、電子的な、または物理的なすべての手段を含みます。

マイクロソフトは、このドキュメントに記載されている内容に関し、特許、特許申請、商標、著作権、またはその他の無体財産権を有 する場合があります。別途マイクロソフトのライセンス契約上に明示の規定のない限り、このドキュメントはこれらの特許、商標、著 作権、またはその他の無体財産権に関する権利をお客様に許諾するものではありません。

別途記載されていない場合、このソフトウェアおよび関連するドキュメントで使用している会社、組織、製品、ドメイン名、電子メー ル アドレス、ロゴ、人物、出来事などの名称は架空のものです。実在する会社名、組織名、商品名、個人名などとは一切関係ありませ ん。

© 2010 Microsoft Corporation. All rights reserved.

Microsoft、SQL Server は、米国 Microsoft Corporation の米国およびその他の国における登録商標または商標です。

記載されている会社名、製品名には、各社の商標のものもあります。

(3)

2

目次

1. 本書の概要 ... 3

2. 環境作成 ... 3

3. 何故 SQL は遅延するのか?... 3

4. 遅延原因 1:インデックスが作成されていない ... 4

4.1. カーディナリティ... 4

4.1.1. 実行プランの確認方法 ... 4

4.1.2. カーディナリティの低いカラムでのインデックス... 6

4.1.3. カーディナリティの低いカラムで有効なインデックス ... 8

4.2. テーブル サイズ ...10

4.2.1. 高度なインデックスの使用方法 ...10

4.2.2. カバリング インデックス ...10

4.2.3. ソート用として使用 ... 11

5. 遅延原因 2:インデックスが使用されていない ...13

5.1. SQL 文が適切に記載されていない ...13

5.1.1. 関数を使用した SQL ...14

5.1.2. 演算を使用した SQL...16

5.1.3. 暗黙の型変換がおこなわれている SQL ...17

5.1.4. 後方一致検索の SQL ...19

5.1.5. != を使用した SQL ...20

5.1.6. Is Null を使用した SQL ...21

5.2. 最適ではない実行プランによる遅延 ...23

5.2.1. 統計情報と実行プラン ...23

5.2.2. 実データと統計情報の値の乖離による遅延 ...23

6. チューニングサイクル ...28

6.1. 情報収集 ...28

6.2. 分析 ...29

6.3. チューニング ...29

6.4. テスト・評価 ...29

(4)

3

1. 本書の概要

本書は、まだあまり開発経験のないアプリケーション開発者が、SQL を最適に保つための SQL の書き方や、

インデックスを効率的に使用する方法についてまとめています。

2. 環境作成

本書で使用するデータベース及びテーブル、インデックスは、以下となります。

CREATE DATABASE DB_Test GO

USE DB_Test GO

CREATE TABLE T_Emp(

ID int NOT NULL, Name Varchar(20), Status Varchar(1), Flag tinyint NOT NULL, UpdDate Datetime,

CONSTRAINT PK_ID PRIMARY KEY CLUSTERED(ID)) GO

CREATE INDEX IX_Name on T_Emp(Name) CREATE INDEX IX_Status on T_Emp(Status) CREATE INDEX IX_Flag on T_Emp(Flag) CREATE INDEX IX_UpdDate on T_Emp(UpdDate) GO

TRUNCATE TABLE T_Emp GO

DECLARE @i int = 1

DECLARE @name varchar(10) WHILE @i <= 10000

BEGIN

SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6) IF @i <= 9990

INSERT INTO T_Emp VALUES( @i,@name,1,0,Getdate() ) ELSE

INSERT INTO T_Emp VALUES( @i,@name,2,1,NULL ) SET @i += 1

END GO

3. 何故 SQL は遅延するのか?

SQL Server® や Oracle を始めとしたリレーショナル データベースでは、データを取得する際に SQL と呼 ばれるデータベース言語を使用してデータベースにアクセスします。データベースではその SQL を受け取 って、SQL を解析した後に値を返しますが、この時に結果がなかなか返って来ないまま数十分から数時間 が経過してしまった、という経験をお持ちの方は多いのではないでしょうか。では、SQL は何故遅延する のでしょうか? SQL が遅延する原因は大きく分けて 2 つあります。1 つはサーバーなどのリソースによ る遅延で、もう 1 つが SQL による遅延です。

サーバー リソースによる遅延とは、接続ユーザー数やデータ量が想定外に増加してしまったことにより、

CPU 使用率の高騰やメモリー不足などサーバーが高負荷状態になり、データベース サーバー自体の処理が

(5)

4

追いつかなくなってしまうようなケースで、アプリケーション開発段階で考慮することが難しい現象と言え ます。

一方、SQL による遅延とは、SQL 実行時に SQL 側に問題がある為大量データの読み込みなどが発生し、

SQL の遅延を誘発してしまうケースです。このような遅延はほとんどの場合がアプリケーション開発時に 防げるものであり、アプリケーション開発者はデータの読み込み量を抑え、遅延が発生しないように心掛け て開発する必要があります。さもないと、運用開始後に遅延が発生してアプリケーションの改修作業が発生 するだけでなく、アプリケーションのテストやリリース作業、リリース後の監視から評価まで多大な作業工 数がかかってしまうことになります。

では、大量の読み込みとはどのようなことが原因で発生してしまうのでしょうか?

4. 遅延原因 1:インデックスが作成されていない

大量の読み込みが発生する原因として最も多いのが、適切にインデックスが作成されてない為フル スキャ ンしてしまうケースです。SQL には特定の行を取得する際に WHERE 句を使用して条件を指定しますが、

1000 万件のテーブルに対して 1 件を抽出するような SQL を実行した場合、インデックスがないと 1000 万件のデータ全てを読み込んでしまうことになります。この為、遅延している SQL の条件句にイン デックスを作成することで読み込み数は大幅に改善することができます。

では、インデックスは条件句に指定された全てのカラムに作成すればよいかというとそうではありません。

インデックスはあまり作成しすぎると更新負荷が高くなってしまい、同時実行性が低下してしまう可能性が ある為、作成しすぎには注意が必要です。

そして、インデックスを効果的に作成する際のポイントとしては以下 2 つがあります。

 カーディナリティ

 データ量

4.1. カーディナリティ

インデックスを作成する場合、最も考慮すべきはその項目がカーディナリティが高いかどうかという点です。

カーディナリティとは値の種類のことで、性別のように男女 2 種類しかないようなカラムの場合、カーデ ィナリティが低いといい、逆に、顧客番号のようにユニークな値を格納するカラムの場合、カーディナリテ ィが高いといいます。

カーディナリティが低いカラムに対してインデックスを作成しても、データベースはインデックスを使用せ ずにテーブルフル スキャンを選択してしまいます。

この点を、SQL Server で実際に確認してみましょう。この点を確認するには、SQL の実行プランを見る必 要があります。以下、実行プランの確認方法になります。

4.1.1. 実行プランの確認方法

実行プランとは、データベースにアクセスする時のアクセス方法や順番のことです。今回は Microsoft® SQL Server® Management Studio を使用して実行プランを確認します。

Microsoft SQL Server Management Studio を起動し、SQL Server に接続

(6)

5

「新しいクエリ」をクリック

「実際の実行プランを含める」を押す

SQL 文を記載

「実行」ボタンを押しクエリを実行

実行プラン タブをクリックして実行プランを表示

実行プランの見方は以下になります

SQL クエリ アナライザのグラフィカルな実行プランの出力は、右から左、上から下に読みます。分析 されたバッチ内の各クエリが表示されます。各クエリのコストも、バッチの総コストに占める割合とし て表示されます。

(7)

6 実行プランにマウスを置くと、詳細が表示

4.1.2. カーディナリティの低いカラムでのインデックス

それでは、カーディナリティが低いカラムだとインデックスが使用されないことを確認してみます。今回は、

「T_Emp」というテーブルの「Flag」カラムに 0 と 1 の 2 種類の値を 5000 件ずつ格納して、SELECT 文を実行します。

▼ T_Emp テーブルへのデータ挿入 TRUNCATE TABLE T_Emp GO

DECLARE @i int = 1

DECLARE @name varchar(10) WHILE @i <= 10000

BEGIN

SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6) IF @i <= 5000

INSERT INTO T_Emp VALUES( @i,@name,1,0,Getdate() ) ELSE

INSERT INTO T_Emp VALUES( @i,@name,2,1,NULL ) SET @i += 1

END GO

(8)

7

▼ Status の件数確認

SELECT Flag,COUNT(*) FROM T_Emp

GROUP BY Flag

▼ SQL/実行プラン SELECT *

FROM T_Emp WHERE Flag = 0

▼ 実行プラン

1. PK_ID を Clustered Index Scan で参照

T_Emp テーブルの Flag というカラムにはインデックス (IX_Flag) が作成されていますが、実行プラン を見るとそのインデックスではなくプライマリ キー (PK_ID) だけが参照されているのがわかります。

この PK_ID というプライマリ キーは、クラスタ インデックスというインデックスで、T_Emp テーブル そのもののデータが格納されているインデックスになります (※1)。そして、実行プランに Index Scan とあり、この Index Scan が PK_ID を全件読み込んだことがわかります。つまり、この実行プランは PK_ID というプライマリ キー (≒T_Emp テーブル) をフル スキャンした、ということになります。

(9)

8

※1.クラスタ インデックス

クラスタインデックスとは、リーフ ブロックにポインタではなく実データを格納しているインデックス のことです。通常のインデックスは、リーフ ブロックにテーブルへのポインタを格納していますが、ク ラスタ インデックスはポインタではなく実データをそのまま格納しています。この為、プライマリ キー を指定してテーブルデータを SELECT するような SQL で有効なインデックスと言えます。SQL Server では、プライマリ キーを作成すると元々存在していたテーブルデータが削除され、全てのテーブル デー タがプライマリ キー内に格納されるようになります。

4.1.3. カーディナリティの低いカラムで有効なインデックス

では、カーディナリティの低いカラムにはインデックスをつける必要はないかというとそうとは限らないケ ースがあります。

それは、インデックスを使用して数件のデータを抽出するような SQL です。例えば、先ほどの Status の 件数が 0 が 9,990 件、1 が 10 件格納されていたとします。この時に、1 の値を抽出する SELECT 文 を実行した場合、10,000 件中 10 件を取得することになる為、インデックスを使用した方がコストが低い と判断されます。

▼ データ作成

TRUNCATE TABLE T_Emp GO

DECLARE @i int = 1

DECLARE @name varchar(10) WHILE @i <= 10000

BEGIN

SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6) IF @i <= 9990

INSERT INTO T_Emp VALUES( @i,@name,1,0,Getdate() ) ELSE

INSERT INTO T_Emp VALUES( @i,@name,2,1,NULL ) SET @i += 1

END GO

▼ Status の件数

SELECT Flag,COUNT(*) FROM T_Emp

GROUP BY Flag

(10)

9

▼ SQL/実行プラン SELECT *

FROM T_Emp WHERE Flag = 1

▼ 実行プラン ステップ

1. IX_Flag インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合

上記実行プランを見ると、IX_Flag が Index Seek という実行プランで読み込まれているのが分かります。

Index Seek は条件に指定された値のみ抽出した場合の実行プランであり、今回の場合は 10 件のみ抽出し たことになります。その後で、T_Emp テーブルをその 10 件と Nested Loops で結合し、結果を返して います。

このように、カーディナリティが低いカラムであってもインデックスを使用することで数件まで絞り込める 場合、インデックスが使用され、不要な読み込みを回避することができます。

(11)

10 4.2. テーブル サイズ

インデックス作成時に考慮すべきもう 1 つのポイントとしてテーブルのサイズがあります。インデックスは テーブル サイズが大きいほど効果が高くなります。例えば、100 件の中から 1 件を抽出するのであれば全 テーブルデータを参照してもインデックスを使用しても読み込み数や処理時間はそれほどかわりません。で すが、100 万件の中から 1 件のデータを抽出する場合、全データを参照してしまうと、読み込みが大量に 発生してしまいます。このようなテーブルのデータを抽出する際はインデックスを使用することで効率的に データを取得することができます。

また、1 行のサイズが大きい場合も、テーブル サイズが大きくなる為フル スキャンのコストが高くなって しまいます。例えば、1 行のレコード長が 10bytes のテーブルと 1KB のテーブルでは同じ行数でもテー ブルのサイズが全く異なり、当然フル スキャン時のコストも高くなってしまいます。

このようなテーブルに対してはインデックスの作成を検討した方がよいと言えます。

4.2.1. 高度なインデックスの使用方法

以上のように、条件句に指定されているカラムと、カーディナリティやデータ量を考慮することで効果的な インデックスを作成することができます。ですが、さらに、以下のようなインデックスを作成することで、

インデックスをより効果的に使用することができます。

4.2.2. カバリング インデックス

カバリング インデックスとは、SELECT 文で使用する全てのカラムをインデックスに含めることでパフォ ーマンスを改善させるチューニング テクニックの 1 つです。

例えば、以下のような Flag カラムを条件句に指定した場合、実行プランは Flag のインデックスを参照し た後で Name 列の値を取得する為、テーブルを参照する必要があります。

▼ SQL/実行プラン SELECT Name FROM T_Emp WHERE Flag = 1

(12)

11

▼ 実行プラン ステップ

1. IX_Flag インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合

Flag のインデックス (IX_Flag) が使用されている為高速に処理されていますが、IX_Flag インデックス に Name 列がない為、Name を取得する為にテーブルを参照してしまっています。この為、この SQL を より効率的に処理させたい場合、SELECT 句にある項目も全てインデックスに含めることで、テーブル デ ータの読み込みを減らすことができ、処理時間も短縮させることができます。

CREATE INDEX IX_Flag_Name on T_Emp(Flag,Name) GO

▼ SQL/実行プラン SELECT Name FROM T_Emp WHERE Flag = 1

▼ 実行プラン ステップ

1. IX_Flag_Name インデックスを Index Seek で参照

4.2.3. ソート用として使用

インデックスは値が小さい順に並んでいる為、order by 句のようなソート処理が実行される SQL の場合、

そのカラムにインデックスを作成しておくことでソート時の負荷が低くなり処理を高速化させることがで きます。

以下、カバリング インデックスと同様の手順で確認してみます。(カバリング インデックスの項で作成し たインデックスは削除して下さい)

(13)

12

▼ SQL/実行プラン SELECT Name FROM T_Emp WHERE Flag = 1 ORDER BY Name

▼ 実行プラン ステップ

1. IX_Flag インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合 3. Sort 処理実行

ORDER BY を使用している為、テーブル データとインデックスを結合した後 Sort 処理が実行されている のがわかります。

この為、この Sort 処理のカラムもインデックスに追加すると、ソート処理が回避できる為高速に処理され ます。

CREATE INDEX IX_Flag_Name on T_Emp(Flag,Name) GO

▼ SQL/実行プラン SELECT Name FROM T_Emp WHERE Flag = 1 ORDER BY Name

(14)

13

▼ 実行プラン ステップ

1. IX_Flag_Name を Index Seek で参照

Flag と Name の複合インデックスを作成することで、テーブルへのアクセスと Sort 処理が回避され、作 成されたインデックスのみで処理されていることがわかります。

5. 遅延原因 2:インデックスが使用されていない

インデックスを作成することで、多くの SQL 遅延は改善されますが、遅延が解消されないケースがありま す。それは、インデックスはあるのに使用されていないようなケースです。具体的に言うと、SQL が適切 に記載されていないケースと、実行プランが最適でない場合です。

5.1. SQL 文が適切に記載されていない

一般的に以下のような SQL ではインデックスが作成されていても使用することができません。この為、こ のような SQL 文をできるだけ記述しないよう注意して SQL を記述する必要があります。

1.関数を使用した SQL 2.演算を使用した SQL

3.暗黙の型変換がおこなわれている SQL 4.後方一致検索の SQL

5.!=を使用した SQL 6.Is Null を使用した SQL

※5,6 については、SQL Server の場合インデックスが使用されます。

これらについて SQL Server で実際に確認しながら見ていくことにしましょう。

(15)

14 5.1.1. 関数を使用した SQL

条件句に関数を使用したカラムを指定して実行すると、インデックスが使用されず全件検索になってしまい ます。

以下では、SUBSTRING 関数を使用して、Name 列の頭 6 文字が「Name01」という文字列を検索してい ます。

▼ 遅延 SQL SELECT * FROM T_Emp

WHERE SUBSTRING(Name,1,6) = 'Name01'

▼ SQL/実行プラン

▼ 実行プラン ステップ

1. IX_Name インデックスを Index Scan で参照 2. その結果と PK_ID キーを Nested Loops で結合

インデックス スキャンとあるので、インデックスが正しく使われているように見えますが、Index Scan は インデックスの全データをスキャンする実行プランになります。そして、その中から条件に該当するデータ を取得し、その結果と PK_ID キー を Nested Loops で結合しています。

▼ SQL の最適化

この SQL をインデックスが使用されるよう記述する場合、カラムに対して関数を使用しないように書き換 える必要があります。今回の場合、SUBSTRING 関数で 1 文字目から 6 文字のデータを取得してその値と 文字列を比較していますので、以下のように Like 検索に変更することで同等の結果を取得することができ、

インデックスも使用することができます。

(16)

15

▼ 変更 SQL/実行プラン SELECT *

FROM T_Emp

WHERE Name Like 'Name01%'

▼ 実行プラン ステップ

1. X_Name インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合

上記を見ると、IX_Name に対する Index Scan だった実行プランが Index Seek に変わっています。

Index Seek は、条件に指定された値のみデータを抽出した場合の実行プランです (内部的には SQL が範 囲検索の SQL 文に変換されています (※2))。そして、Index Seek で抽出した結果は、PK_ID と Nested Loops で結合され、その結果が返されています。つまり、IX_Name インデックスを使用して、データを抽 出しているということができます。

※2.範囲指定に変換されたことは、Index Seek の詳細を見ることで確認することができます。

(17)

16 5.1.2. 演算を使用したSQL

インデックスが作成されたカラムに対して演算処理を行うと、インデックスが使用されません。

以下は、ID をマイナス 100 した値が 0 より小さいデータを抽出しています。

▼ SQL/実行プラン SELECT *

FROM T_Emp

WHERE ID - 100 < 0

▼ 実行プラン ステップ

1. ID キーを Clustered Index Scan で参照

先ほどの SQL と同じように Index Scan なので、インデックスの全データをスキャンした後、条件に指定 したデータを抽出しています。

▼ SQL の最適化

本 SQL のように左辺の ID を演算するのではなく、右辺の 0 の値に 100 をプラスするように書き換え ることでカラムに対する演算を回避でき、インデックスも使用されます。

▼ 変更 SQL/実行プラン SELECT *

FROM T_Emp

WHERE ID < 0 + 100

(18)

17

▼ 実行プラン ステップ

1. PK_ID キーを Index Seek で参照

上記を見ると、実行プランが Index Scan から Clustered Index Seek に変わっていて、条件に指定され た値のみ参照していることがわかります。これにより、インデックスの不要な読み込みを抑えることができ ます。

5.1.3. 暗黙の型変換がおこなわれている SQL

暗黙の型変換 (文字列型から数値型に変換) されているような場合、インデックスは使用されません。

#SQL Server の場合、数値型から文字列型に暗黙の型変換される場合は、インデックスが使用されます。

▼ SQL/実行プラン SELECT *

FROM T_Emp WHERE Status = 2

(19)

18

▼ 実行プラン ステップ

1. PK_ID キーを Clustered Index Scan で参照

本 SQL は、T_Emp テーブルの Status の値が 2 のデータを抽出しています。Status のカラムは文字列 型であり、指定した値は数値で検索している為、このまま実行してしまうと暗黙的に SQL が変換され、イ ンデックスが使用されない SQL で実行されてしまいます。

上記の通り、Status 列を CONVERT_IMPLICIT 関数を使用して数値型に変換しています。これによりフル スキャンが実行されてしまいます。

▼ SQL の最適化

本 SQL は暗黙の型変換が発生しないようシングルクォートで囲むことでインデックスが使用されるように なります。

▼ SQL/実行プラン SELECT *

FROM T_Emp

WHERE Status = '2'

(20)

19

▼ 実行プラン ステップ

1. IX_Status インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合

上記を見ると、IX_Status の Index Seek で実行されており、適切にインデックスを使用していることが わかります。

5.1.4. 後方一致検索の SQL

先に確認した前方一致検索であればインデックスは使用されますが、後方一致検索ではインデックスが使用 されません。本 SQL は、Name の後ろから 3 文字が 123 のデータを抽出しています。

▼ SQL/実行プラン SELECT *

FROM T_Emp

WHERE Name Like '%123'

(21)

20

▼ 実行プラン ステップ

1. IX_Name インデックスを全データ参照 2. PK_ID キーと Nested Loos を全データ参照

本 SQL は、IX_Name インデックスをフル スキャンした後、後ろ 3 文字が 123 のデータを抽出してい ます。後方一致検索の場合、インデックスを使用するように変更することができません。この為、この SQL を高速に処理する必要がある場合、別の条件を追加できないか、全文検索インデックスで代用可能か、など を検討する必要があります。

5.1.5. != を使用した SQL

!= (Not Equal 検索) で検索した場合、Oracle ではインデックスが使用されませんが、SQL Server の場 合、インデックスが使用されるケースがあります。それは、インデックスを使用した方がフル スキャンより コストが低いと SQL Server が判断した場合です。

▼ SQL/実行プラン (10 件取得) SELECT *

FROM T_Emp WHERE Flag != 0

▼ 実行プラン ステップ

1. IX_Flag インデックスを参照

2. その結果と PK_ID キーを Nested Loops で結合

(22)

21

▼ SQL/実行プラン (9,990 件取得) SELECT *

FROM T_Emp WHERE Flag != 1

▼ 実行プラン ステップ

1. PK_ID キーを Clustered Index Scan で参照

上記のように、!= を使用した結果が 10,000 件中 10 件を取得するような結果の場合、インデックスが使 用されます。逆に、9,990 件取得する場合、テーブルを全件参照した方が速いと判断してフル スキャンが 実行されます。

5.1.6. Is Null を使用した SQL

SQL Server の場合、Is Null を使用してもインデックスが使用されるケースがあります。これも!= 検索と 同様、インデックスを使用した方がフル スキャンより高速に処理可能と SQL Server が判断した場合に使 用されます。

▼ SQL/実行プラン (NULL データ抽出:10 件) SELECT *

FROM T_Emp

WHERE UpdDate Is Null

(23)

22

▼ 実行プラン ステップ

1. IX_UpdDate インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合

▼ SQL/実行プラン (NOT NULL のデータ抽出:9,990 件) SELECT *

FROM T_Emp

WHERE UpdDate Is Not Null

▼ 実行プラン ステップ

1. PK_ID キーを Clustered Index Scan で参照

(24)

23 5.2. 最適ではない実行プランによる遅延

以上の SQL は、インデックスが使用されるよう SQL 文を書き換えればパフォーマンスが改善される可能性 が高いものでした。ですが、SQL を書き換えてインデックスが使用される SQL を記述してもインデックス が使用されないケースがあります。それは、データベースが作成した実行プランが最適ではないケースです。

実行プランとは、データベースにアクセスする時のアクセス方法や順番のことで、具体的に言うと、複数の テーブルを指定した SQL が実行された際に、どのテーブルから参照するか、どのインデックスを使用する か、どのような結合方法で実行するかを解析した結果のことです。データベースでは、幾つかの実行プラン を作成した後で、その中から最も高速に処理することができる実行プランを選択し実行します。このような 実行プランの作成や選択はオプティマイザというデータベースの頭脳とも言える箇所で行われています。こ のオプティマイザが高速に処理可能と判断した実行プランが、実際に実行した時に遅い場合がある為、遅延 が発生してしまうことになってしまいます。そして、このような遅延のほとんどが統計情報に問題があるケ ースになります。

5.2.1. 統計情報と実行プラン

統計情報とは、表や索引、カラムがどのような状態なのかを表している情報のことで、具体的には、テーブ ルにデータが何件格納されているか、1 行の平均サイズはどのぐらいなのか、カラムにはどのような値が多 く格納されているのかなどの情報が格納されています。SQL Server の統計情報では行数や平均キー長、ヒ ストグラムなどの情報が格納されています。また、インデックス作成時にインデックス列内の統計情報が作 成され、テーブル内のデータが更新されると統計情報も自動的に更新されます (デフォルト)。

実行プランは、SQL 文実行後、オプティマイザの解析処理で作成されます。オプティマイザは解析時に複数 の実行プランを作成し、その中で最適な実行プランを選択していますが、その基準となる情報が統計情報で す。この統計情報が実データと乖離しているような場合、誤った実行プランを選択してしまい処理が遅延し てしまうことになります。例えば、1,000 万件のデータが格納されているテーブルに対して 10 件取得する SELECT 文を実行したとします。この時に、統計情報が古くこのテーブルに 10 件しか格納されていないと いう情報になってしまっていた場合、フル スキャンの方がインデックスを参照するより高速に処理可能と判 断し、フル スキャンを選択してしまう可能性があります。

SQL Server ではデフォルトでは自動で統計情報を収集しているので、このように実データと統計情報の値 が乖離するようなことは多くないですが、この機能を無効にしている場合や大量データを一度に更新した場 合などにこのようなことになる可能性があります。

5.2.2. 実データと統計情報の値の乖離による遅延

統計情報による遅延について実際に SQL Server で確認してみます。今回は、現象を分かりやすくするため に 10 件データ挿入時に統計情報を取得し、その後で 20 万件挿入して実データと統計情報の値を乖離させ て遅延の現象を再現させてみます。

1. T_Emp のテーブルデータ作成

今回は誤った統計情報を取得すると遅延してしまう現象を確認する為、自動で統計情報が取得されないよ う、「統計の自動更新」を無効にします。

(25)

24 2. T_Emp のテーブルデータ作成

10 件のテーブルデータを作成します。

TRUNCATE TABLE T_Emp GO

DECLARE @i int = 1

DECLARE @name varchar(10) WHILE @i <= 10

BEGIN

SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6) INSERT INTO T_Emp VALUES( @i,@name,1,0,Getdate() )

SET @i += 1 END

GO

3. 統計情報取得

10 件格納時の統計情報を取得します。統計情報の手動取得は UPDATE STATISTICS コマンドを使用し ます。

UPDATE STATISTICS T_Emp IX_Flag

統計情報が取得されてるかどうか確認します。確認は DBCC SHOW_STATISTICS コマンドで確認する ことができます。

DBCC SHOW_STATISTICS('T_Emp','IX_Flag')

(26)

25

Rows が 10 となっていることから、この統計情報ではテーブルデータが 10 行格納されていることに なっています。実行プランを見ると、PK_ID のフル スキャンが実行されていることがわかります。

SELECT * FROM T_Emp WHERE Flag = 0

4. 20 万件のデータ作成

T_Status テーブルに 20 万件になるようデータを挿入します。

DECLARE @i int = 11

DECLARE @name varchar(10) WHILE @i <= 200000

BEGIN

SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6) INSERT INTO T_Emp VALUES( @i,@name,2,1,Getdate() )

SET @i += 1 END

GO

(27)

26

・件数確認

SELECT Flag,COUNT(*) FROM T_Emp

GROUP BY Flag

5. 統計情報の確認

統計情報が更新されてないことを確認します。

DBCC SHOW_STATISTICS('T_Emp','IX_Flag')

6. SELECT 文実行

統計情報が 10 件でテーブルデータが 20 万件格納されている状態で、以下のような Flag を条件に 10 件取得する SQL 文を実行した時の実行プランを確認します。

SELECT * FROM T_Emp WHERE Flag = 0

(28)

27

▼ 実行プラン ステップ

1. PK_ID キーを Clustered Index Scan で参照

実行プランを見ると、Clustered Index Scan とあり、PK_ID (≒T_Emp テーブル) のフル スキャンに なっていることがわかります。

7. 統計情報を取得後再実行

統計情報を取得して、統計情報の値を最新の状態にしてから同様の SELECT 文を実行します。

UPDATE STATISTICS T_Emp IX_Flag

DBCC SHOW_STATISTICS('T_Emp','IX_Flag')

SELECT * FROM T_Emp WHERE Flag = 0

(29)

28

▼ 実行プラン ステップ

1. IX_Flag インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合

このように、テーブルデータと統計情報で同期が取れていないと、オプティマイザが誤った実行プランを 最適と判断してしまいます。SQL Server では統計情報は自動更新の為、あまり意識する必要は有りませ んが、大量データの更新処理等が実行された時は統計情報を取得して、このような遅延処理が起きないよ う気をつける必要があります。

6. チューニングサイクル

以上の点を考慮してアプリケーションを開発することで、運用が開始した後に処理遅延が発生する可能性 も少なくなります。但し、このように考慮して開発しても実際にはデータ量の増加により予想外に処理が遅 延してしまうことがあります。このようなことにならない為に、運用開始後に遅延している SQL を発見し 対処する、そしてまた監視、というようなチューニングサイクルを実施する必要があります。チューニング サイクルのフェーズは以下の通りとなります。

チューニングサイクル 1. 情報収集 2. 分析

3. チューニング 4. テスト・評価

6.1. 情報収集

まず最初に、データベースの稼働データや処理時間が長い SQL を取得します。データベースの稼働データは稼働

状況の傾向を把握する為に常に取得しておく必要があります。

(30)

29

具体的な情報の収集方法については、自習書の「監視ツールの基本操作」を参考にして下さい。

参考: http://www.microsoft.com/japan/sqlserver/2008/self-learning/default.mspx

6.2. 分析

次に、取得した情報を分析します。分析は、データ量や処理量の増加に伴いアプリケーションや SQL が遅延してい ないか、データベースの全体で読み込み量やサーバーの CPU 使用率が増加していないかなど、ボトルネックの原 因を調査します。例えば、日々処理が遅延する SQL が確認された場合、テーブルのフル スキャンが実行されてい ないか、データ量の増加に伴って読み込みが多くなる処理がないかなどをポイントにボトルネックを特定します。

6.3. チューニング

遅延原因が把握できたら、遅延を解消する為のチューニングを行います。チューニングは本書で見てきたような観点、

つまり、インデックスが貼ることで改善できるか、インデックスが使用されない SQL の書き方になってないか、デー タと統計情報の値が乖離していないかなどを確認し、チューニングを実施します。

6.4. テスト・評価

チューニングした結果をテストし効果を確認します。特に、インデックスを作成するチューニングの場合、そのインデッ クスと同じカラムを参照している SQL にも影響してしまう可能性がある為、そのような

SQL

の実行プランも確認す ることが好ましいです。

また、この処理がチューニングされたことにより、別の所で新たにボトルネックが発生する可能性がある為、引き続き データを取得し、情報収集する必要があります。

以上のチューニング サイクルを実施することで、データベースのパフォーマンスを向上させ、システムを安定的に運

用させることができます。

参照

関連したドキュメント

これらの先行研究はアイデアスケッチを実施 する際の思考について着目しており,アイデア

  BCI は脳から得られる情報を利用して,思考によりコ

関係委員会のお力で次第に盛り上がりを見せ ているが,その時だけのお祭りで終わらせて

テキストマイニング は,大量の構 造化されていないテキスト情報を様々な観点から

名刺の裏面に、個人用携帯電話番号、会社ロゴなどの重要な情

の知的財産権について、本書により、明示、黙示、禁反言、またはその他によるかを問わず、いかな るライセンスも付与されないものとします。Samsung は、当該製品に関する

  

つの表が報告されているが︑その表題を示すと次のとおりである︒ 森秀雄 ︵北海道大学 ・当時︶によって発表されている ︒そこでは ︑五