今回はMySQLのINDEXについての解説をしていきます。
何となくは分かってるつもりでも、完全に理解できていない方もいると思いますので、この記事を見てしっかりと理解してもらえたら嬉しく思います。
最後までお付き合いください。
INDEXとは
インデックスとは、データベースに登録されたデータを検索する際の効率を高めるための仕組みです。
インデックスに指定されたカラムはツリー格納型データに変換され、他のデータと別のスペースに保管されます。
また検索する際にもこのツリー格納型データから優先的に検索がかけられるため、データベース内のデータ検索を高速化できる仕組みとなっています。
INDEXのメリット・デメリット
インデックスのメリットは何と言っても検索速度の向上です。
インデックスが適切に使われているデータベースは、検索速度がインデックスの無いものよりも圧倒的に早くなります。
但し、近年ではサーバー自体の処理性能も格段にアップしているため、情報量が少ない場合には速度が全く変わらないという結果になることもあります。
強いて言うならビッグデータを捌く場合に役に立つ機能という程度の認識でも特に問題ありません。
デメリットとしては、データ領域を別に作成されるため、保存領域が必要以上に膨れ上がる場合があります。
また、データの新規追加や更新などをする際にはインデックス内のデータも同時に処理が行われるため、検索以外の処理にかかる時間は多少増える点が挙げられます。
INDEXにすべきカラムを見つける方法
初心者の方にとっては、どのカラムにINDEXを貼れば良いのか判断がつかない場合もあると思います。
そんな時に役に立つコマンドが”EXPLAIN”です。
このコマンドを実行したいクエリの先頭に付け足すだけで、該当クエリに対しどのカラムにインデックスを付けるべきかを自動で判断してくれます。
早速ですが、コード例を見てみましょう。
+----+---------+-----+--------+--------+--------+ | id | name | age | tall | weight | sex | +----+---------+-----+--------+--------+--------+ | 1 | Jackson | 20 | 175.80 | 68.54 | male | | 2 | Noah | 23 | 162.67 | 48.20 | female | +----+---------+-----+--------+--------+--------+
仮にこのようなデータベーステーブルがあったとします。
しかし今の段階では、このテーブルがどのように使われるべきかは今後の開発次第で変わってくるので判断が出来ません。
そういった場合に”EXPLAIN”を使用することで自動で候補を挙げてくれます。
具体的な結果は以下の通りです。
EXPLAIN SELECT * FROM sample_table; +------+-------------+--------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | sample_table | ALL | NULL | NULL | NULL | NULL | 2 | | +------+-------------+--------------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.000 sec)
このテーブルについて簡単に説明をしていきたいと思います。
まず”EXPLAIN”を使用すると、登録されているレコードがいくつあろうとカラムとタイプだけが表示されます。
そして自動でインデックスの候補を挙げてくれている場所についてですが、「possible_keys」に記載されているカラムが最も効率的なインデックスの候補となります。
但し今回は、登録されているレコードが少ないことやカラムの少ないこと、テーブルが他の参照で全く使われていないことなど総合的判断でNULLとなっています。
この場合にはインデックスは必要ないという意味になります。
INDEXを参照する
現在貼られているINDEXがどのカラムなのかを確認するには”SHOW INDEX”を使用します。
記述の定義は次の通りです。
SHOW INDEX FROM [データベース名.テーブル名];
それでは先ほどのテーブルを使って実際にインデックスを確認してみましょう。
SHOW INDEX FROM sample_db.sample_table; +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | sample_table | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.001 sec)
先ほどのテーブルではインデックスの指定をしていないので、「PRIMARY KEY」のみが表示されていることが今回の結果から確認できます。
では先ほどのテーブルにインデックスを貼った後、同じクエリを実行して比較してみましょう。
SHOW INDEX FROM sample_db.sample_table; +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | sample_table | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | sample_table | 1 | age | 1 | age | A | 2 | NULL | NULL | | BTREE | | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.001 sec)
年齢のカラムに対してINDEXを指定したので、今回の結果では”Key_name”に「age」が追加されていることが確認できます。
INDEXを追加する
コマンドでINDEXを追加する場合には”ADD INDEX”を使います。
基本記述定義は以下の通りとなります。
ALTER TABLE [テーブル名] ADD INDEX [インデックス名]([カラム名]);
こちらも実際にコードで見てみましょう。
ALTER TABLE sample_db.sample_table ADD INDEX sample01(name); Query OK, 0 rows affected (0.017 sec) Records: 0 Duplicates: 0 Warnings: 0 SHOW INDEX FROM sample_db.sample_table; +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | sample_table | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | sample_table | 1 | age | 1 | age | A | 2 | NULL | NULL | | BTREE | | | | sample_table | 1 | sample01 | 1 | name | A | 2 | NULL | NULL | | BTREE | | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.002 sec)
先ほどのテーブルの一番下に新たに「sample01」というインデックス名が追加されていることが確認できます。
また複数のインデックスを同時に追加する場合にはカラム名をカンマで区切って並べるだけで複数追加が可能です。
INDEXを削除する
不要なINDEXを削除する場合には”DROP INDEX”を使います。
基本記述定義は以下の通りとなります。
ALTER TABLE [テーブル名] DROP INDEX [インデックス名];
先ほどと同じようにこちらも実際のコードで見てみましょう。
ALTER TABLE sample_db.sample_table DROP INDEX sample01; Query OK, 0 rows affected (0.015 sec) Records: 0 Duplicates: 0 Warnings: 0 SHOW INDEX FROM sample_db.sample_table; +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | sample_table | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | sample_table | 1 | age | 1 | age | A | 2 | NULL | NULL | | BTREE | | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.001 sec)
先ほどテーブルの一番下に追加したインデックス名が再度削除されていることが確認できたと思います。
まとめ
いかがでしたか？
今回はINDEXについて解説をしてみました。
データベースのチューニングの基本ですが、適切な設計をすることによってビッグデータを効率よく捌くことが可能となります。
データベースはシステム設計でもWebサイト構築でも頻繁に使用されるものですから、確実に理解して扱えるように訓練しましょう。
