【MySQL】インデックス貼ればいい訳ではない

スポンサーリンク

検索項目にはインデックスを貼ればいいって訳ではないということを学んだので、備忘録を残します。

※インデックスやexplainについて知っていることを前提としています。

※MariaDBとありますが、MySQLと互換性をもっているので、MySQLと読み替えてください。

検証テーブルの定義と内容

テーブルの内容

※今回使わないカラムは削除しています。

MariaDB [(none)]> select * from test2.movies \G
*************************** 1. row ***************************
id: 1
title: title
*************************** 2. row ***************************
id: 2
title: NULL
*************************** 3. row ***************************
id: 3
title: testtitle

MariaDB [(none)]>

 

インデックス

インデックスは「id」と「title」にしています。

※一部消しています。

MariaDB [(none)]> show index from test2.movies \G
*************************** 1. row ***************************
Table: movies
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Null:
*************************** 2. row ***************************
Table: movies
Non_unique: 1
Key_name: index_title
Seq_in_index: 1
Column_name: title
Null: YES
2 rows in set (0.00 sec)

MariaDB [(none)]>

インデックスきかないパターン

以下のパターンはインデックスが使われないので注意してください。

また、僕が調べていないパターンもありますのでこれが全部ではありませんので、explainを活用しましょう。

  • インデックスカラムに対して演算を適用した場合
  • インデックスカラムに対して関数を適用した場合
  • 否定(<>)を利用した場合
  • あいまい検索(LIKE)の後方一致の場合

インデックスきかないパターンの検証

explain(type)見方がよくわからない方は以下のように認識しておいてください。

  • rangeはインデックス使っている
  • ALLはインデックスが使われていない

インデックスカラムに対して演算を適用した場合

MariaDB [(none)]> explain select * from test2.movies where id * 1 < 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: movies
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.00 sec)

MariaDB [(none)]>

インデックスカラムに対して関数を適用した場合

MariaDB [(none)]> explain select * from test2.movies where length(id) < 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: movies
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.00 sec)

MariaDB [(none)]>

否定(<>)を利用した場合

MariaDB [(none)]> explain select * from test2.movies where title <> 'testtitle' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: movies
type: ALL
possible_keys: index_title
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.00 sec)

MariaDB [(none)]>

 

ちなみに、「is not null」は大丈夫みたいです。

MariaDB [(none)]> explain select * from test2.movies where title is not null \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: movies
type: range
possible_keys: index_title
key: index_title
key_len: 768
ref: NULL
rows: 2
Extra: Using index condition
1 row in set (0.00 sec)

MariaDB [(none)]>

あいまい検索(LIKE)の後方一致の場合

title like ‘%testtitle’」も同じ結果です。

MariaDB [(none)]> explain select * from test2.movies where title like '%testtitle%' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: movies
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.00 sec)

MariaDB [(none)]>

 

前方一致は問題ありません。

MariaDB [(none)]> explain select * from test2.movies where title like 'testtitle%' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: movies
type: range
possible_keys: index_title
key: index_title
key_len: 768
ref: NULL
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)

MariaDB [(none)]>

以上が検証でした。

ちなみに、マルチカラムの場合だともっと複雑になるみたいです。

マルチカラムは使ったことないので調査はしていませんが、気になる方は調べてみてください。

タイトルとURLをコピーしました