インデックスが効かないケース

インデックスによってパフォーマンスが向上するが、正しく扱うには知識が必要です。

今回はインデックスの知識を深めていきましょう

B-treeインデックスの効果が発揮しないケース

インデックスの効果を発揮しないケースを理解していきましょう。

この理解を深めることで、一覧取得の方法を変えたり、最適な手法かどうか判別することができます。

col_1に関してインデックスを用いている想定で話を進めていきます。

インデックス列に演算を行っている

SELECT * 
FROM SomeTable
WHERE col_1 * 1.1 > 100;

col_1 * 1.1の値はインデックスを貼られていないため、インデックスを利用しません。

書き換えると効果を発揮します。

SELECT * 
FROM SomeTable
WHERE col_1 > 100/1.1;

1.1x > 100からx > 100/1.1に変更しています。

この場合、シンプルにcol_1 > 値が成立するので、インデックスが利用可能です。

索引列に対してSQL関数を適用している

SELECT * 
FROM SomeTable
WHERE SUBSTR(col_1, 1, 1) = ‘a’;

こちらもSUBSTR(col_1, 1, 1)で出力される値に対してインデックスを利用していないので、インデックスは適用されません。

あくまでcol_1単体で利用する必要があります。

IS NULL 述語を利用している

SELECT * 
FROM SomeTable
WHERE col_1 IS NULL;

インデックスデータにNULLは存在しないので、IS NULLを使うと強制的に全表検索になり、インデックスは使われません。

そのため、WHERE col_1 IS NULL;においてはインデックスを利用されません。

否定系を用いている

<>や!=を利用したSQLはインデックスを利用しません。

SELECT * 
FROM SomeTable
WHERE col_1 <> 100;

SELECT * 
FROM SomeTable
WHERE col_1 != 100;

SELECT * 
FROM SomeTable
WHERE col_1 NOT IN (99,100);

否定系はインデックスが適用されないため、テーブルがフルスキャンされることがあったりするので、処理速度の悪化につながる恐れがあります。

ORを用いている

SELECT * 
FROM SomeTable
WHERE col_1 = 99 OR col_1 = 100;

ORを利用するとインデックスが適用されません。

そのため書き換えます

SELECT * 
FROM SomeTable
WHERE col_1 IN(99, 100);

WHERE INを利用することで、インデックスを適用したまま検索することができます。

ただし、WHERE IN は左側からヒットしやすい値を並べる必要があるので、注意が必要です。

LIKE述語で、後方一致または中間一致を用いている

×　SELECT * FROM SomeTable WHERE col_1 LIKE ‘%a’;  // 後方一致
×　SELECT * FROM SomeTable WHERE col_1 LIKE ‘%a%’; // 中間一致
○　SELECT * FROM SomeTable WHERE col_1 LIKE ‘a%’;  // 前方一致

後方一致と中間一致はインデックスが利用されない。

前方検索はインデックスが利用される。

このことからカラムの分割が重要になる。

フルネームは、姓と名にわける。
→ 名前の検索が必要な場合

メールアドレスはユーザー名@ドメイン名の構成なため、ユーザー名とドメイン名に分ける
→ ドメイン名での検索が必要な場合などに限る

住所は都道府県・市区町村にわける。
→ 都道府県別に統計をとりたいケースがある場合

暗黙の型変換を行っている

文字列型で定義されたcol_1に対する条件を書く場合の例を示します。

×　SELECT * FROM SomeTable WHERE col_1 = 10;
○　SELECT * FROM SomeTable WHERE col_1 = ‘10’;
○　SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

数値型 → 文字型、文字型 → 数値型

文字列型→日付型、日付型→文字列型

型変換を行って、型を統一する必要がある

列とデータ型の異なる値を条件に指定した場合、DBMSは内部的に暗黙の型変換を行います。この場合、インデックスは使用されません。

これを回避するために、明示的に型を指定して、データ型と一致させて検索をする必要があります。