edit-icon download-icon

より高速なデータアクセスのために最適なインデックスを選択して作成する

最終更新日: Apr 17, 2018

SQL の問題に起因する障害は、データベースで頻繁に発生します。索引付けの問題は、最も頻繁に発生する SQL の問題です。一般的な索引の問題には、索引なし、暗黙的な変換、および不適切な索引付けが含まれます。

SQL 文によって参照されるデータベース表に対して索引が作成されない場合、全表スキャンが実行されます。テーブルに大きなデータボリュームがある場合、スキャンタスクは非常に遅いです。この場合、データベース接続は占有されており、指定された最大接続数にすぐに到達します。リソースとして、新しい要求は拒否され、障害が発生します。

暗黙の変換により、SQL 照会の条件に渡された値がフィールド・データ定義と矛盾するときに索引が失敗する可能性があります。一般的な暗黙的な変換の例をいくつか示します。CHAR データはタイプ SQL クエリで参照フィールドのテーブルスキーマで定義されていますが、数値フィールドの値は、SQL クエリに渡されます。または、大文字小文字を区別する照合がフィールドに対して定義されていますが、複数テーブルの結合の場合は、結合テーブルのフィールドの大文字と小文字の区別の定義が異なる場合があります。暗黙的な変換によって索引が失敗する可能性があり、その結果、SQL 問合せがゆっくりと実行され、データベース接続が排除される前述の状況が発生します。

インデックスポリシーと最適化

インデックスを作成する

  • 照会で頻繁に参照されるフィールドに索引を作成しますが、フィールド・データの追加、削除、または変更などの操作で頻繁に更新されることはありません。
  • “order by”と”group by”の直後にフィールドを使用します。これは索引付きフィールドでなければなりません。
  • テーブルに最大 6 つのインデックスを作成します。
  • 索引付きフィールドの値が固定長で、かつ短いことを確認します。
  • 重複した値がインデックスフィールドに多すぎないことを確認します。
  • フィルタリング可能なフィールドにインデックスを作成する。

インデックスの使用上の注意

  • キーワード”like”を使用すると、接頭辞”%”が追加された場合、索引が無効になることがあります。
  • 通常、NULL 値を含む列には索引が作成されないため、NULL 値を持つ列は自動的に索引付けの候補列から除外されます。
  • キーワード”or”を使用すると、”or”のいずれかのフィールドが索引付けされていない場合、索引付けされていても、他のフィールドの索引は無効になります。
  • 演算子 “!=”を使用すると、インデックスは使用されません。この場合、インデックス効率が低く、データ範囲が不確定なためです。 代わりに、フル・テーブル・スキャンが実行されます。
  • インデックス付きフィールドで計算を実行しないでください。
  • 複合インデックスの場合は、「左端接頭辞」の原則に従う必要があります。クエリを実行するときは、最初のインデックス付きフィールドを使用する必要があります。それ以外の場合、インデックスは無効になります。 また、フィールド順序とインデックス順序を一致させてください。
  • 定義されたデータタイプ SQL クエリに渡されるデータタイプと一致させておくことで、暗黙的な変換を避ける。

インデックスのないケース

インデックスなしケース 1

  1. テーブルスキーマを表示します。

    1. mysql> show create table customers;
    1. CREATE TABLE `customers` (
    2. `cust_id` int(11) NOT NULL AUTO_INCREMENT,
    3. `cust_name` char(50) NOT NULL,
    4. `cust_address` char(50) DEFAULT NULL,
    5. `cust_city` char(50) DEFAULT NULL,
    6. `cust_state` char(5) DEFAULT NULL,
    7. `cust_zip` char(10) DEFAULT NULL,
    8. `cust_country` char(50) DEFAULT NULL,
    9. `cust_contact` char(50) DEFAULT NULL,
    10. `cust_email` char(255) DEFAULT NULL,
    11. PRIMARY KEY (`cust_id`),
    12. ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8
  2. 次の文を実行します。

    1. mysql> select * from customers where cust_zip = '44444' limit 0,1 \G;
  3. 実行計画は以下の通りです。

    1. mysql> explain select * from customers where cust_zip = '44444' limit 0,1 \G;
    1. id: 1
    2. select_type: SIMPLE
    3. table: customers
    4. type: ALL
    5. possible_keys: NULL
    6. key: NULL
    7. key_len: NULL
    8. ref: NULL
    9. rows: 505560
    10. Extra: Using where

    実行計画では、”type”の値は”ALL”であり、フル・テーブル・スキャンが実行されることを意味します。505,560 行のデータを 1 回のスキャンでカバーする必要があり、パフォーマンスが低下します。次に、クエリを最適化する方法について説明します。

  4. インデックスを追加します。

    1. mysql> alter table customers add index idx_cus(cust_zip);
  5. 実行計画は以下の通りです。

    1. mysql> explain select * from customers where cust_zip = '44444' limit 0,1 \G;
    1. id: 1
    2. select_type: SIMPLE
    3. table: customers
    4. type: ref
    5. possible_keys: idx_cus
    6. key: idx_cus
    7. key_len: 31
    8. ref: const
    9. rows: 4555
    10. Extra: Using index condition

    実行計画では、”type”は、インデックスベース等結合クエリの意味またはテーブルの等結合の”ref”、です。

インデックスがないケース2

  1. テーブルスキーマは前述の場合と同じです。次の文を実行します。

    1. mysql> select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name;
  2. 実行計画は以下の通りです。

    1. mysql> explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name\G;
    1. id: 1
    2. select_type: SIMPLE
    3. table: customers
    4. type: ALL
    5. possible_keys: NULL
    6. key: NULL
    7. key_len: NULL
    8. ref: NULL
    9. rows: 505560
    10. Extra: Using filesort
  3. インデックスを追加します。

    1. mysql> alter table customers add index idx_cu_zip_name(cust_zip,cust_name);
  4. 実行計画は以下の通りです。

    1. mysql> explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name\G;
    1. id: 1
    2. select_type: SIMPLE
    3. table: customers
    4. type: ref
    5. possible_keys: idx_cu_zip_name
    6. key: idx_cu_zip_name
    7. key_len: 31
    8. ref: const
    9. rows: 4555
    10. Extra: Using where; Using index
    • “order by”の後にあるフィールドを使用します。これはインデックスフィールドでなければなりません。

暗黙の変換の場合

暗黙のコンバージョンケース 1

  1. mysql> explain select * from customers where cust_zip = 44444 limit 0,1 \G;
  1. id: 1
  2. select_type: SIMPLE
  3. table: customers
  4. type: ALL
  5. possible_keys: idx_cus
  6. key: NULL
  7. key_len: NULL
  8. ref: NULL
  9. rows: 505560
  10. Extra: Using where
  1. mysql> show warnings;
  2. Warning Cannot use range access on index 'idx_cus' due to type or collation conversion on field 'cust_zip'

上記の場合には、CHAR データは、タイプ 「cust_zip」フィールドのテーブルスキーマで定義されています。ただし、フィールドの SQL クエリには数値が渡されます。その結果、暗黙的な変換が行われ、索引が失敗します。

ソリューション:

  • データを変更しタイプの数値に「cust_zip」フィールドに。

  • SQL クエリに渡されるデータタイプを CHAR から数値に変更します。

暗黙のコンバージョンケース 2

  1. テーブルスキーマを表示します。

    1. mysql> show create table customers1;
    1. CREATE TABLE `customers1` (
    2. `cust_id` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    3. `cust_name` char(50) NOT NULL,
    4. KEY `idx_cu_id` (`cust_id`)
    5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    6. mysql> show create table customers2;
    7. CREATE TABLE `customers2` (
    8. `cust_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    9. `cust_name` char(50) NOT NULL,
    10. KEY `idx_cu_id` (`cust_id`)
    11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  2. 次の文を実行します。

    1. mysql> select customers1.* from customers2 left join customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x';
  3. 実行計画は以下の通りです。

    1. mysql> explain select customers1.* from customers2 left join customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x'\G;
    1. *************************** 1. row ***************************
    2. id: 1
    3. select_type: SIMPLE
    4. table: customers2
    5. type: ref
    6. possible_keys: idx_cu_id
    7. key: idx_cu_id
    8. key_len: 33
    9. ref: const
    10. rows: 1
    11. Extra: Using where; Using index
    1. *************************** 2. row ***************************
    2. id: 1
    3. select_type: SIMPLE
    4. table: customers1
    5. type: ALL
    6. possible_keys: NULL
    7. key: NULL
    8. key_len: NULL
    9. ref: NULL
    10. rows: 1
    11. Extra: Using where; Using join buffer (Block Nested Loop)
  4. “COLLATE”を修正してください。

    1. mysql> alter table customers1 modify column cust_id varchar(10) COLLATE utf8_bin ;
  5. 実行計画は以下の通りです。

    1. mysql> explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name\G;
    1. id: 1
    2. select_type: SIMPLE
    3. table: customers2
    4. type: ref
    5. possible_keys: idx_cu_id
    6. key: idx_cu_id
    7. key_len: 33
    8. ref: const
    9. rows: 1
    10. Extra: Using where; Using index
    1. id: 1
    2. select_type: SIMPLE
    3. table: customers1
    4. type: ref
    5. possible_keys: idx_cu_id
    6. key: idx_cu_id
    7. key_len: 33
    8. ref: const
    9. rows: 1
    10. Extra: Using where

    フィールドの COLLATE 属性が一貫した後、インデックスは実行計画で使用されます。 したがって、照合属性が表フィールドに対して一貫して定義されていることを確認することが重要です。

結論

索引が使用されている場合、SQL実行計画は”explain”を使用して表示できます。索引が使用されているかどうか、暗黙的な変換が行われているかどうかを判断し、適切な索引が作成されるようにします。複雑なプロセスであるため、索引作成時には注意が必要です。