概要
サーバー側でSQL文を書いた時のことです。
上司 「このSELECT文、フルスキャンになっていないか確認してください！」
私 「承知です！」
初心者エンジニアの私は「フルスキャンってなんぞや？」な感じでしたが、調べてみるとその確認方法は簡単でした。
せっかくなので備忘録として残します。
この記事でわかること
・フルスキャンとは
・フルスキャンの確認方法
・フルスキャンの回避方法
フルスキャンって？
SELECT文であるレコードを取得したいときに、対象のテーブルを全件検索してしまっている検索方法です。
レコードを総なめして、条件にヒットしたレコードを引っ張ってくるので、レコード数が少ないとさほど影響はないかもしれませんが
何千件、何万件とレコードがあるとすごく時間がかかってしまいます。
よく本を例に出されますが
フルスキャンとは、ある単語を探したい時に、1ページ目から最後まで白み潰しに探していく方法です。
これだと時間がかかり過ぎますよね。
パフォーマンス的に改善しなくてはいけない手法になります。
SQL文が効率よく検索できているかどうか、確かめる必要があります。
フルスキャンの確認方法
実行するSELECT文がフルスキャンしているかどうか確認する方法は
先頭にEXPLAINをつけて確認します
例として以下のようなテーブルがあったとします。
mysql> DESC party;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| No | int(11) | NO | | NULL | |
| name | varchar(255) | NO | | NULL | |
| type1 | varchar(10) | NO | | NULL | |
| type2 | varchar(10) | NO | | NULL | |
| move1 | varchar(30) | NO | | NULL | |
| move2 | varchar(30) | YES | | NULL | |
| move3 | varchar(30) | YES | | NULL | |
| move4 | varchar(30) | YES | | NULL | |
| lev | int(2) | NO | | NULL | |
| ability | varchar(30) | NO | | NULL | |
| gender | int(2) | YES | | NULL | |
| held_item | varchar(30) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)
mysql> SELECT * FROM party;
+----+-----+-----------+----------+--------+-----------------+--------------+--------------+--------------+-----+---------------+--------+------------+
| id | No | name | type1 | type2 | move1 | move2 | move3 | move4 | lev | ability | gender | held_item |
+----+-----+-----------+----------+--------+-----------------+--------------+--------------+--------------+-----+---------------+--------+------------+
| 1 | 25 | Pikachu | Electric | なし | High Horsepower | Volt Tackle | Quick Attack | Iron Tail | 88 | Lightning Rod | 1 | Light Ball |
| 2 | 131 | Lapras | Water | Ice | Blizzard | Psychic | Brine | Body Slam | 80 | Water Absorb | 2 | NULL |
| 4 | 143 | Snorlax | Nomrmal | なし | Blizzard | Shadow Ball | Crunch | Giga impact | 82 | Thick Fat | 1 | NULL |
| 5 | 3 | Venusaur | Gtass | Poison | Giga Drain | Frenzy Plant | Sludge Bomb | Sleep Powder | 84 | Chlorophyll | 2 | NULL |
| 6 | 6 | Charizard | Fire | Flying | Flare Blitz | Blast Burn | Air Slash | Dragon Pulse | 84 | Solar Power | 1 | NULL |
| 7 | 9 | Blastoise | Water | なし | Hydro Cannon | Flash Cannon | Blizzard | Focus Blast | 84 | Rain Dish | 1 | NULL |
+----+-----+-----------+----------+--------+-----------------+--------------+--------------+--------------+-----+---------------+--------+------------+
6 rows in set (0.00 sec)
ポケモンの手持ちをイメージしてみました。
レッドパーティですね！
幼き筆者は初見で負けてビビった記憶があります。
そんな話は置いといて。。。
このテーブルからNo（図鑑番号）が25のレコードを取得したいときに
mysql> SELECT * FROM party WHERE No = 25;
このようなSQL文を書くと思います。
この先頭にEXPLAINをつけて実行してみると
mysql> EXPLAIN SELECT * FROM party where No = 25;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | party | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
普通のSELECT文とは違う結果が返ってきました。
確認したいのはtypeの欄です。
「ALL」と書かれていますが、これはフルスキャンしていますよと言われています。
要するにSELECT * FROM party WHERE No = 25は全件取得する効率が悪い手法と分かります。
今は6件しかデータがないので、爆速でデータを取得できますが。
今後データが増えた時にとてつもなく遅いシステムができてしまいます。
未然に防ぐために、作成したSQL文がフルスキャンしていないか確認することは大事ですね。
また、既存のシステムの動作がどうも遅いなと思ったら原因はDBからデータを取得している時間が長すぎることだったりしますよね。
怪しいSQL文はEXPLAINをつけてみて確かめてみるのも大事ですね。
フルスキャンの回避方法
効率が悪いデータの取得方法は避けるべきです。
ではどうすればいか。
データの取り方を変えてみましょう。
Noで検索をかけるのではなく、idでデータを取得してみましょう。
WHERE旬をid = 1に変えて実行してみると
mysql> EXPLAIN SELECT * FROM party where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | party | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
type欄が「const」に変わりました。
以下にtypeの種類をまとめてみます。
| type | 検索方法 |
|---|---|
| ALL | フルスキャン、全件検索 |
| index | フルインデックススキャン |
| const | 主キーやユニークキーを使って検索 |
| eq_ref | ↑と類似。表結合しているものに 使われると表示される |
| ref | ユニークじゃないインデックスを使って検索 |
| range | インデックスを使って検索 |
今回は主キーで検索をかけているので「const」になってます。
なので大丈夫そうですね。
問題なのが前述の「ALL」と「index」です。改修の必要がありますね。
このように検索項目を変えてフルスキャンを回避する方法あります。
またカラムにインデックスを貼るとフルスキャンを回避できます。
先ほどフルスキャンになっていた「No」での検索がありました。
システム的にどうしても「No」で検索をかけたい時もあります。
そんな時は「No」にインデックスを貼ってあげればフルスキャンを回避してあげましょう。
mysql> show index from party;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| party | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> CREATE INDEX pokedex ON party(No);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from party;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| party | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | |
| party | 1 | pokedex | 1 | No | A | 6 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
CREATE INDEX pokedex ON party(No);
上記を実行すると、partyテーブルのNoカラムに「pokedex」という名前のインデックスを貼ることができました。
この状態で一番最初のSELECT文を確認してみましょう。
mysql> EXPLAIN SELECT * FROM party where No = 25;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | party | NULL | ref | pokedex | pokedex | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
typeが「ref」になりました。
インデックスを使って検索しているので、フルスキャンは回避できていますね。
こういった感じで
・検索項目を変えるか
・インデックスを貼るか
この2パターンでフルスキャンを回避できます。
ひとこと
インデックス最強すぎんか。。？
何でもかんでもインデックスを貼ればいいってわけじゃなさそうだけど
テーブル作成時に予想できる検索項目にはインデックスを貼る癖をつけとくべきやね。。