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

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

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


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

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

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


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

全テーブル定義とテーブル自動作成VBA
・テーブル:m_customer ・テーブル:m_item ・テーブル:t_sales ・テーブル自動作成

インデックスとは

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

インデックスを作成するという事は、テーブルとは別にインデックスのデータが作成されるということです。
テーブルとは別に索引データを作成するので、データ容量はその分増えてします。
そして、インデックスはテーブルのデータが追加/変更/削除される都度、当該データに関わるインデックスも追加/変更/削除されます。
結果として、テーブルへの追加/変更/削除はそれだけ遅くなります。
むやみに作成してしまうと、かえって全体のパフォーマンスを落としてしまう事にもなりかねません。
インデックスを作成するときは、このことに注意を払ったうえでインデックスの作成を検討する必要があります。
ただし、インデックスの作成自体はかなり高速に行われるので、余程大量に作成しなければ通常はそれほど問題にはなりません。
多くのデータベースでは、インデックスに 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 ・・・新着記事一覧を見る

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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