interprism's blog

インタープリズム株式会社の開発者ブログです。

SQL SERVERでインデックスを利用してみよう

この投稿は インタープリズム的「俺達私達の進捗を上げる25個前後のTips」 Advent Calendar 2015 - Qiitaの2日目 の記事です。

こんにちはNです。

最初に

ほとんどの場合アプリケーションがデータベースに対して要求する操作は選択、追加、削除、更新(SELECT、INSERT、UPDATE、DELETE)になります。インデックスはこれら全ての操作に大小の影響を及ぼしますが、適切に設定されたインデックス特に選択時のパフォーマンスに大きなメリットをもたらすものです。この記事ではインデックスを設定した場合テーブルに対しての選択がどのような影響がもたらされるかをまとめてみました。 ここではデータベースエンジンはSQL SERVER 2008R2、実行環境はSQL Management Studioを用いています。

インデックスのないテーブルを作る

SQL Management Studio(以下MSSM)で以下のDDLを実行しテーブルを新規作成します。

--Persion(個人)テーブルの内容
--[No]:個人に割り当てられた一意の番号
--[Name]:個人名
--[Age]:年齢
CREATE TABLE [dbo].[Person]
(
    [No] [int] NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    [Age] [int] NOT NULL,
)

テーブルを作成したのち次にいくつかデータを投入します。

INSERT INTO [dbo].[Person] VALUES
    (0, 'Tanaka', 20)
    ,(1, 'Satou', 3)
    ,(2, 'Nakata', 31)
    ,(3, 'Asou', 66)
    ,(4, 'Abe', 16)
    ,(5, 'Nonaka', 81)
    ,(6, 'Ozaki', 50)
    ,(7, 'Kamiyama', 22)
    ,(8, 'Mitsurugi', 31)

以下はMSSMで実行した時の画像です。

[テーブル作成] f:id:interprism:20151124083146j:plain

[データ投入] f:id:interprism:20151124084629j:plain

インデックスがないテーブルでSELECTをしてみる

次にいくつかのSELECTを実行し、どのようにデータが抽出されるか確認してみます。データがどのように取得されるのかを確認する場合は「実行計画」を取得する必要があります。実行計画とはデータベースエンジンが入力されたSQLとデータの状態を元として算出する、データアクセスの手順のことです。 ここではMSSMでツールバーに存在する「実際の実行プランを含める」をONにしてSQLを実行しています。

SELECT * FROM [dbo].[Person] -- パターン0:テーブルの全選択
SELECT * FROM [dbo].[Person] WHERE [No] = 1 -- パターン1:[No]に対するSELECT
SELECT * FROM [dbo].[Person] WHERE [Name] = 'Tanaka' -- パターン2:[Name]に対するSELECT
SELECT * FROM [dbo].[Person] WHERE [Age] > 30 -- パターン3:[AGE]に対するSELECT

以下がそのSELECTの結果と実行計画になります。

![SELECT結果] f:id:interprism:20151124090329j:plain

![実行計画の表示結果] f:id:interprism:20151124090328j:plain

SELECTの結果はそれぞれ異なりますが、実行計画がすべて同じであることに気づくと思います。

  • パターン0:テーブルの全選択

    • このSELECTはレコードの選択に対して条件が存在していないため、テーブル内全てのレコードが抽出されています。この時の実行計画をみると、[TableScan]が実行されており、これによりテーブル全てのレコードに対して走査されていることがわかります。

  • パターン1:[No]に対するSELECT

    • このSELECTは[No]カラムが1の値を持つものを選択するものです。実行計画を見るとパターン0のものと同様に[TableScan]が実施されテーブル内全てのレコードが走査され[No]カラムが1であるもののみを結果として抽出しています。

  • パターン2:[Name]に対するSELECT

    • このSELECTは[Name]カラムが'Tanaka'の値を持つものを選択するものです。実行計画はこれまでのパターンと同様に[TableScan]による全レコードの走査が行われていることがわかります。

  • パターン3:[Age]に対するSELECT

    • このSELECTは[Age]カラムが30を超える値を持つものを選択するものです。実行計画はこれまでのパターンと同様に[TableScan]による全レコードの走査が行われていることがわかります。

このようにインデックスを設定しないテーブルに対するSELECTは常にテーブルの全レコードに対する走査処理が行われることがわかりました。テーブルの全レコード走査はデータの規模によっては非常に時間のかかる処理になるため、どうしても必要な場合を除き回避すべき処理といえます。実行計画を確認した際に[TableScan]という単語を見つけた場合はそのSQLに問題がないか確認したほうがよいでしょう。

テーブルにインデックスを設定する

インデックスとはテーブル内のレコードに対する目次のようなものです。 これによりデータを抽出する際に、ある条件に当てはまるレコードがテーブル内のどこに格納されているかが判断できるようになり、その結果テーブルのレコードの全走査を行う必要がなくなります。

インデックスを設定する

以下を実行することにより、[No],[Name],[Age]のそれぞれのカラムにインデックスを設定しました。

CREATE NONCLUSTERED INDEX [IdxNo] ON [dbo].[Person] ([No])
CREATE NONCLUSTERED INDEX [IdxName] ON [dbo].[Person] ([Name])
CREATE NONCLUSTERED INDEX [IdxAge] ON [dbo].[Person] ([Age])

以下が実行後の画面です。テーブルにインデックスが設定されているのがわかります。

![インデックス設定結果] f:id:interprism:20151124094052j:plain

インデックスの構造

SQL SERVERの基本的なインデックスとしてテーブルに対してクラスタ化インデックス、非クラスタ化インデックスの2種類が設定できます。今回は非クラスタ化インデックスのみを使っています(上記でのインデックスの設定時における構文において[NONCLUSTERED]がそれにあたります)。詳細な構造はここでは省略しますが、これらのインデックスはツリー構造で管理されており、末端にはテーブルが保持されるヒープ領域のどこにレコードが格納されているかを判別する行識別子というものが格納されています。 インデックスを参照しレコードを検索する場合は以下の処理を行い特定のレコードが選択できるようになります。

  1. ツリーのノードの値と条件となっている値を比較し、その結果から子ノードを選択します
  2. 1の処理をリーフノードにたどり着くまで繰り返します
  3. リーフノード内で条件と一致する値を検索し行識別子(RID)の指し示すテーブル内の特定のレコードを取得します

以下がインデックスの構造の簡単なイメージ図です。

![インデックス構造] f:id:interprism:20151124095248j:plain

インデックスを使ってSELECTをしてみる

インデックスを再設定した状態で以下のSQLを実行します。今回はわかりやすさを優先するためにSQL内で参照するインデックスを指定しています。

SELECT * FROM [dbo].[Person]
SELECT * FROM [dbo].[Person] WITH(INDEX ([IdxNo])) WHERE [No] = 1
SELECT * FROM [dbo].[Person] WITH(INDEX ([IdxName]))WHERE [Name] = 'Tanaka'
SELECT * FROM [dbo].[Person] WITH(INDEX ([IdxAge]))WHERE [Age] > 30

以下の画像が上記のSQLを実行した際の実行計画の結果となります。

![インデックスが参照された実行計画] f:id:interprism:20151124100842j:plain

  • パターン0:テーブルの全選択

    • インデックスは設定しましたが、条件にインデックスが存在しないためテーブルの全レコードの走査処理である[TableScan]が実施されています。

  • パターン1:[No]に対するSELECT

    • 実行計画をみるとインデックス[IdxNo]が参照され特定のレコードの行識別子が抽出され、対応するレコードがテーブルの中から選択されていることがわかります。これは実行計画と付き合わせるとインデックスを使った行識別子の抽出が[Index Seek (NonClustered) [Person].[IdxNo]]に対応し、テーブル内の特定のレコードの選択が[RID LookUp (Heap) [Person]]にあたります。[Index Seek (NonClustered) [Person].[IdxNo]]で取得された行識別子の数だけ[RID LookUp (Heap) [Person]]が行われるため、繰り返し処理が発生し[Nested Loops(Inner Join)]が行われていることも確認できます。

  • パターン2:[Name]に対するSELECT

    • 実行計画をみるとインデックス[IdxName]が使われていることがわかります。挙動についてはパターン1と同様のものであることが確認できます。

  • パターン3:[Age]に対するSELECT

    • 実行計画をみるとインデックス[IdxAge]が使われていることがわかります。しかしこの条件は「Age=30」ではなく「Age>30」です。このような場合であってもインデックスがツリー構造でありカラム[Age]についてソート済みのデータになっているため条件に当てはまる行識別子のみをまとめて取得することができます。

最後に

このようにインデックスを設定し、SELECT文で使うことで効率よくデータを取得することができるようになります。データが大規模になった場合、同じSQLであってもインデックスが使われるか使われないかによって大きく処理時間が変わることはめずらしくありません。日頃よりSQLを実行した場合にDBがどのようにデータを選択するかを意識できると、より良いSQLが実装できるようになると思います。

インタープリズム的「俺達私達の進捗を上げる25個前後のTips」 Advent Calendar 2015 - Qiitaの3日目の記事

PAGE TOP