SQL第一歩第29話.索引の作成と削除

20178 ワード
SQLの最初のステップ テキストリンク
索引の作成と削除
  • DDL(Data Definition Language)を使用してインデックスを作成または削除します.

  • 実際、標準SQLでは、インデックスはデータベース製品に依存するオプションとして扱われているため、CREATE INDEXコマンドは存在しません.

  • しかし、ほとんどの有名なデータベース製品はインデックス構造を採用しており、同様の方法で管理することができます.
    • 索引の作成CREATE INDEXコマンドを使用してインデックスを作成できます.

  • データベース製品に応じてインデックスの名前を付けて管理します.

  • インデックスは、OracleおよびDB 2などのスキーマ・オブジェクトです.したがって、モードでは管理名は重複しません.

  • 逆に、SQL Server、MySQLでは、インデックスはテーブル内のオブジェクトです.したがって、テーブルの名前は重複しません.

  • MySQLで、テーブルsample 62のno列にisample 62という名前のインデックスを指定する方法:インデックスを確認するには、show INDEX FROMテーブル名と同じ文を使用します.
    • mysql > SELECT * FROM sample62;

+----+---------------------+------------------------+------+
| no | created_at          | nickname               | name |
+----+---------------------+------------------------+------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임        |      |
+----+---------------------+------------------------+------+
1 row in set (0.00 sec)

mysql > DESC sample62;

+------------+------------+------+-----+-------------------+-------------------+
| Field      | Type       | Null | Key | Default           | Extra             |
+------------+------------+------+-----+-------------------+-------------------+
| no         | int        | NO   |     | NULL              |                   |
| created_at | datetime   | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nickname   | varchar(8) | YES  |     | NULL              |                   |
| name       | varchar(4) | NO   |     | NULL              |                   |
+------------+------------+------+-----+-------------------+-------------------+
4 rows in set (0.01 sec)
    mysql > CREATE INDEX isample62 ON sample62(no);

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > SHOW INDEX FROM sample62;

+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample62 |          1 | isample62 |            1 | no          | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
  • インデックスを作成すると、ストレージデバイスにインデックス用のデータが作成されます.
  • テーブルのサイズはインデックスの作成時間に影響するため、ローが大量に存在する場合、時間とストレージ領域が大量に消費されます.
    • 索引の削除
  • DROP INDEXコマンドでインデックス名を指定することで、インデックスを削除できます.

  • SQL ServerやMySQLなどのテーブル内のオブジェクトとしてインデックスが存在する場合は、テーブル名も指定する必要があります.

  • 以前に作成したインデックスisample 65を削除するには、次の手順に従います.
    • mysql > DROP INDEX isample62 ON sample62;

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > SHOW INDEX FROM sample62;

Empty set (0.00 sec)

  • 前にインデックスの役割を果たしたように、インデックスによって検索速度を速めることができます.

  • WHERE文の条件としてインデックス内のカラムを使用できます.

  • ただし、INSERTコマンドを使用する場合は、既存のコマンドを使用するよりも長い時間で既存のテーブルおよびインデックス・オブジェクトにデータを追加する必要があります.

  • テーブルsample 62のニックネームにインデックスisample 62が指定されていると仮定する.

  • インデックスによる検索(SELECT)の方法は以下の通りです.

  • WHERE文を条件として、インデックスとしてカラムを指定します.
    • mysql > SELECT * FROM sample62 WHERE nickname = '테스트용 닉네임';

+----+---------------------+------------------------+------+------+-------------+
| no | created_at          | nickname               | name | test | test_number |
+----+---------------------+------------------------+------+------+-------------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임        |      |    0 |        NULL |
+----+---------------------+------------------------+------+------+-------------+
1 row in set (0.00 sec)
    EXPLAIN

  • インデックスを使用すると検索速度が向上し、EXPLAINコマンドを使用して、実際に入力した文がインデックスを使用しているかどうかを確認します.

  • EXPLAINコマンドを使用してインデックスされたカラムニックネームとnoカラムの違いを見てみましょう.
    • mysql > EXPLAIN SELECT * FROM sample62 WHERE nickname = '테스트용 닉네임';

+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | sample62 | NULL       | ref  | isample62     | isample62 | 27      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
    mysql > EXPLAIN SELECT * FROM sample62 WHERE no = 1;

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sample62 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
    EXPLAINコマンドは、標準SQLに存在しないデータベース製品に依存するコマンドです.
    ただし、他のデータベース製品でも同様のコマンドがサポートされています.
    たとえば、PostgreSQLは同じEXPLAINコマンドを使用し、OracleはEXPLAIN PLANコマンドを使用します.
    スプリングスタート(Spring Boot)掲示板
    android2.3以上自動着信