SQL入門
インデックスを作成して高速化(CREATE INDEX)

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
最終更新日:2019-12-17

インデックスを作成して高速化(CREATE INDEX)


エクセルVBAでデータベースを扱うためのSQL入門です。
前回の、他のテーブルのデータで追加/更新/削除では、UPDATEで多大な時間がかかっていました。

この時間は、テーブルのインデックスを作成することで劇的に速くできます。
次回、「インデックスを作成して高速化(CREATE INDEX)」で紹介します。
インデックスを作成することで、約5分が数秒に短縮されます。

前回このように記載しました。
今回は、このインデックスとは何かの説明と、インデックスを作成するSQLを解説します。


使用するテーブル定義は以下になります。

全テーブル定義とテーブル自動作成VBA
データベースの正規化とマスタの作成で作成した全テーブル定義と、テーブル名変更と列追加(ALTERTABLE)とテーブル自動作成で作成したテーブル自動作成VBAを掲載しておきます。エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを作成したり、削除したりできるようになりました。

インデックスとは

インデックスとは言葉通りに索引の事です。
テーブルの件数が多くなると、目的のデータを検索するのに時間がかかってきます。
そこで索引を作成しておき、速やかに検索できるようにします。
インデックスはテーブルの検索を高速化する為に作成します。

インデックスを作成するという事は、テーブルとは別にインデックスのデータが作成されるということです。
テーブルとは別に索引データを作成するので、データ容量はその分増えてします。
そして、インデックスはテーブルのデータが追加/変更/削除される都度、当該データに関わるインデックスも追加/変更/削除されます。
結果として、テーブルへの追加/変更/削除はそれだけ遅くなります。
むやみに作成してしまうと、かえって全体のパフォーマンスを落としてしまう事にもなりかねません。
インデックスを作成するときは、このことに注意を払ったうえでインデックスの作成を検討する必要があります。
ただし、インデックスの作成自体はかなり高速に行われるので、余程大量に作成しなければ通常はそれほど問題にはなりません。
多くのデータベースでは、インデックスに B木構造を採用しています。
インデックスの構造について詳しく知りたい場合は別途検索してみてください。

そもそもテーブルには主キー(PRIMARY KEY)を設定します。
(主キー設定しないテーブルもあるにはありますが)
そして、主キーは自動的に主キーとしてのインデックスが作成されます。
これにより、主キーでの検索は高速に行われます。
しかし、他のカラムは特に指定しない限りインデックスは作成されません。
そこでインデックスを作製するカラムを個別に指定する必要があるということです。

インデックスを作成する場合、単独(1つ)のカラムでも作成できますし、複数のカラムを合わせて一つのインデックスとすることもできます。
検索することが多いカラムを調べ、そのカラムでインデックスを作成します。
したがって、どのカラムでインデックス作製したら効率が良くなるかは、そのテーブルの使い方次第になります。
カラムA、カラムB、それぞれで検索することが多い場合は、それぞれでインデックスを作成し、
カラムAとカラムBをあわせ検索することが多い場合は、カラムAとカラムBを合わせたインデックスを作成します。
カラムA、カラムB、さらにカラムAとカラムBをあわせ検索することが多い場合は、カラムAとカラムB、さらにでカラムAとカラムBを合わせたインデックスを作成します。

前回のUPDATEのSQLを速くするには
前回のSQLで時間がかかっているのは、
t_sales_dateの1件ずつに対して、WHERE条件で対応するt_salesを都度々集計しているからです。

SQLの処理方法自体を変更することはできません。
そこで、この検索を速くするには、codeとsales_dateでインデックスを作成することで解決します。

注目すべきはWHERE条件になります。
WHERE t_sales_date.code = T.code AND t_sales_date.sales_date = T.sales_date
Tはt_salesのエイリアス
ここで検索に使用されているt_salesのカラムは、codeとsales_dateです。
したがって、この2つのカラムでインデックスを作成します。
codeとsales_dateで別々にインデックスを作成しても相応に効果はありますが、
このUPDATEに限定した対策としては、この2つのカラムで作成することになります。

インデックスを作成

インデックスを作成するSQL構文
CREATE INDEX インデックス名 ON テーブル名 (カラム名 , カラム名 ・・・);

インデックス名は、このインデックスに付ける名前です。
インデックス名は他と重複出来ません。
データベース全体としてユニークでなければなりません。
()括弧内に、インデックスを作成するカラムを1つ以上指定します。

CREATE INDEX の使用例
t_salesのcodeとsales_dateでインデックスを作成します。

CREATE INDEX ix_code_date ON t_sales (code,sales_date);
このSQLを実行することでインデックスが作成されます。
インデックスが作成されることで、これを条件とした検索が高速に処理されるようになります。

このインデックスを作成すると、前回のUPDATEが劇的に速くなります。
約5分かかっていた処理が約5.5秒に短縮されました。
これほどにインデックスの効果は高いものになります。

UNIQUEインデックスを作成

作製するインデックスがユニークなキーとなる場合は、UNIQUEを指定します。
UNIQUインデックスと、主キー(PRIMARY KEY)は良く似たものになります。

UNIQUEインデックスを作成するSQL構文
CREATE UNIQU INDEX インデックス名 ON テーブル名 (カラム名 , カラム名 ・・・);

インデックス名は、このインデックスに付ける名前です。
インデックス名は他と重複出来ません。
データベース全体としてユニークでなければなりません。
()括弧内に、インデックスを作成するカラムを1つ以上指定します。

インデックスを削除

インデックスを削除するSQL構文
DROP INDEX インデックス名;

インデックス名は、このインデックス作成時に付けられた名前です。
このSQLを実行するとインデックスは完全に削除されます。

DROP INDEX の使用例
DROP INDEX ix_code_date;
ix_code_dateはインデックス作成時に付けた名前です。

インデックスを作製の最後に

他のテーブル情報を使って、INSERT/UPDATE/DELETE、そこからUPDATEを高速化するためにインデックスの作成へと進みました。

次回はトランザクション処理について解説します。
トランザクション処理の記述は極めて簡単ですが、データベースにおいてとても重要なものになります。

UPDATEとDELETEで紹介した中で説明しましたが、そこではサブクエリ(副問合せ)を使用しています。
次々回以降、サブクエリ(副問合せ)について詳しく解説していきます。
サブクエリこそ、SQLの真骨頂と言えるものです。



同じテーマ「SQL入門」の記事

データの更新(UPDATE)
データの削除(DELETE)
他のテーブルのデータで追加/更新/削除
インデックスを作成して高速化(CREATE INDEX)
トランザクション処理
VBAクラスの全コード:トランザクション処理
サブクエリ(副問合せ)
サブクエリのネストとSQLコメント&整形
WITH句(共通テーブル式)
取得行数を限定するLIMIT句
分析関数(OVER句,WINDOW句)


新着記事NEW ・・・新着記事一覧を見る

COUNTIF関数の結果は?(ツイッターお題)|エクセル雑感(5月30日)
エクセル&VBA オンライン講座|エクセル セミナー(5月29日)
アルファベットの26進(ツイッターお題)|エクセル雑感(5月19日)
VBAが消えてしまった!マクロが壊れて動かない!|エクセル雑感(5月8日)
時間に関する関数の扱い方(ツイッター投稿)|エクセル雑感(5月6日)
文字列のセルだけ結合(ツイッターのお題)|エクセル雑感(5月5日)
表示形式.言語設定|エクセル入門(5月1日)
GoogleスプレッドシートをExcelにインポートする|VBAサンプル集(4月28日)
ツイッターのお題「CSV編集」|エクセル雑感(4月25日)
プログレスバーを自作する|ユーザーフォーム入門(4月18日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ