『INDEX』の基本のキホン

SQLのパフォーマンスチューニングとなると、「実行計画や統計情報、オプティマイザ、ヒント句、パーティション、スロークエリログの解析」などの知識も必要ですが、大抵のSQLは、それ以前にINDEXを活用できていないものもあります。

しかし、ちょこっと「INDEXをキチンと当てる」SQLを組み立てることで、性能はグンと良くなるので難しい知識は抜きにして、簡単にポイントを整理したいと思います。

SQLの性能UPのポイント

SQLを組み立てる際に性能を考える上で意識するところは、「主キーやインデックスを当てる」「初期検索件数をできるだけ絞る」」「SELECTの発行件数を抑える」「ソートを意識する」そして、「できるだけ単純化を図り可読性を保つ」この辺りかと思います。
何はともあれ、RDBMS性能では欠かせない要素は「INDEX」の理解からと言えます。

最初は影が薄く、最後は重要となるINDEX

しかし、INDEXを意識していないテーブル定義やSQL文も良く見かけます。
大規模の案件でも、INDEXをまともに意識したのは、実は結合テスト後半になった頃や運用が始まってから、という大規模開発も間々あります。
それがキチンと当たり出すとパフォーマンスは、数倍～数百倍以上にもなり性能問題が一気に改善する開発は多いです。

INDEXが複雑化して思ったように当たらないというものも中にはありますが、意識しても当たっていないものや、酷いものは全く意識していないSELECT文やINDEXの定義も無い場合もあります。
だからといって無駄にINDEXを貼って逆に定義が複雑になり、INDEXが上手く当たらなかったりと副作用も付きまといます。

まずは、INDEXのポイントを正しく理解しておくことが大切となります。

主キー（プライマリキー）とINDEXの違い

主キーは、そのテーブルのレコードを物理的に一意とするために利用されINDEXとしても機能します。
対してINDEXは、主にデータの論理構造に合わせたキーとして定義しますが、一意ではなくても検索キーとして定義でき、主キー項目は、NOT NULL制約となりますが、INDEXキーは、NULLが入る項目でも含めることができます。
また、ユニークINDEXとしていたほうが多くの面で性能的に優位となります。

主キーはテーブルで一つのみという制約がありますが、INDEXは、一つのテーブルに複数定義することができます。

INDEXが必要なカラム

検索キーとして利用する場合	WHERE句で使用するカラムなので当たり前ですよね。
値に複数の要素が存在する場合	検索で利用する日付や、商品番号などはINDEX対象となりやすいと言えます。 逆に貼る意味のないカラムとして、例えば「男・女」等、そのカラムで数個のものは、貼っても体感的に早くなるものでは無いので、このような項目は除外しておいて良いでしょう。
外部結合キー（JOIN）項目	外部キーは、主キーとなっている場合が多いですが、外部結合キーにINDEXが当たっていない場合は、性能的にかなりの劣化が生じるので注意が必要です。
ソート項目	見落とされている場合がありますが、ソートキーも同様にINDEXによるパフォーマンスが影響します。
集約キー（GROUP BY）や暗黙のソートが行われる場合	GROUP BYの集計キーも同じく、INDEXが必要なカラムとなります。また、DESTINCTやUNIONなど、暗黙のソートが行われるものもあるので、これらが関係するカラムも注意が必要です。

また、INDEXを貼るカラムのサイズが大きいと、その分メモリの使用量も増大するので、カラムサイズも適切に行っておくのもポイントです。

INDEXで当てているのに早くならない？

INDEXを貼り、SQLもINDEXにピッタリハマっているのに、「全然、早くならない・・・。」ということがあります。
INDEXを貼っているカラムは、対象の値で索引を作っているので、索引検索として意味がなくなる以下のような当て方をした場合は、INDEX検索は無効とされます。

否定条件を使っている

WHERE userID <> ‘ID001’ WHERE userID NOT IN (‘ID001′,’ID002′,’ID003’)

検索カラムに関数を使用している

WHERE TRIM(userID) = ‘ID001’ WHERE TO_CHAR(birthday, ‘YYYY/MM/DD’) = ‘2001/09/30’

データ型が異なっている

WHERE productID = 1230 ※productIDが文字型とした場合

日付型として当たっていない

WHERE birthday = ‘2001/09/30’ ※birthdayが日付型とした場合

回避策は、型変換したもので検索を行う。 ⇒Oracle WHERE birthday = TO_DATE(‘2001/09/30’, ‘YYYY/MM/DD HH:MI:SS’) ⇒Access WHERE birthday = cdate(‘2001/09/30’) ⇒MySQL ※MySQLの場合は、文字列でもインデックスが当たる WHERE birthday = ‘2001/09/30’ ※Betweenで検索したほうが更に早くなる WHERE birthday between ‘2001/09/30 00:00:00’ and ‘2001/09/30 23:59:59’

like検索を前方一致以外で行っている

WHERE userID LIKE ‘%001’ WHERE userID LIKE ‘%00%’

ORDER BY にカラム連番を使用している

order by 1,2,3

NULL検索している

WHERE userID is null

INDEX以外の性能劣化原因

ORを用いている

WHERE userID = ‘ID001’ OR userID = ‘ID002’

回避策は、INを使う WHERE userID IN(‘ID001’, ‘ID002’)

select * を使っている

特にループ内部の処理で、これを使っているとDB内部的にカラム名の展開が行われオーバヘッドが掛かります。

UNION

UNIONは、SELECT対象とした論理表をマージしますが、その為各項目でのソートが発生し負荷が増大します。
よほどのことが無い限り使用は控えましょう。
ちなみに、「UNION ALL」は、論理表のマージもなくソートも発生しません。

 

INDEXをあまり意識していない方は、一度、SQLを見直すと驚くほど性能がUPすることがあります。

SQL性能は、製造段階では明確に判りにくいこともあり、いざ本番データを動かしてから「重い、遅い・・・」となることもあります。
運用が始まってからシステムが性能上、使えないという状態に陥らないよう、できるだけ直ぐに対策できるようにINDEXの効果は普段から意識しておくことは大切だと思います。