Computer Science
MySQL์์ Full Scan์ด ๋ฐ์ํ๋ ์ฟผ๋ฆฌ ํจํด ์์๋ณด๊ธฐ
11/5/2024

Full Scan์ ์ฟผ๋ฆฌ์ ๊ฒฐ๊ณผ๋ฅผ ๊ฐ์ ธ์ค๋๋ฐ์ ์ธ๋ฑ์ค๋ฅผ ์ฌ์ฉํ ์ ์์ด ์ ์ฒด ํ ์ด๋ธ์ ์กฐํํด์ผํ๋ ์ฟผ๋ฆฌ ์ํ ๋ฐฉ์์ ์๋ฏธํฉ๋๋ค. Full Scan์ด ์ฌ์ฉ๋๋ ๊ฒฝ์ฐ ์ฟผ๋ฆฌ ์ํ ์๊ฐ์ ๋ ์ฝ๋์ ํฌ๊ธฐ์ ๊ฐ์์ ๋น๋กํ๊ธฐ์ ๋ฐ์ดํฐ๊ฐ ๋ง์ ์ํฉ์์๋ ํ์ค์ ์ผ๋ก ์ฌ์ฉ์ด ๋ถ๊ฐํ ์ง๋ ๋ชจ๋ฆ ๋๋ค. ๋ฐ๋ผ์ ์ด๋ฅผ ํด๊ฒฐํ๊ธฐ ์ํด ํ์ํ ์ปฌ๋ผ์ ์ธ๋ฑ์ค๋ฅผ ์์ฑํ ๋ค ์ฟผ๋ฆฌ ์ํ์ด ์ธ๋ฑ์ค๋ฅผ ์ฌ์ฉํ์ฌ ์ด๋ฃจ์ด์ง๊ฒ ํด์ผํฉ๋๋ค. ์๋ ๋ด์ฉ์์๋ ์ํ๋ ์ปฌ๋ผ์ ์ธ๋ฑ์ค๋ฅผ ๊ฑธ์์์๋ ๋ถ๊ตฌํ๊ณ ํ ์ค์บ์ด ๋ฐ์ํ๋ ์ํฉ๋ค์ ๋ํด์ ๋ค๋ฃน๋๋ค.
ํ ์คํธ ๋ฐ์ดํฐ ์
ํ ์คํธ๋ฅผ ์ํด Members ํ ์ด๋ธ์ ์์ฑํ์ต๋๋ค. Member ํ ์ด๋ธ์ ์์์ 5์๋ฆฌ ์ํ๋ฒณ์ ์ด๋ฆ์ผ๋ก ๊ฐ์ง๋ฉฐ 0๋ถํฐ 100 ์ฌ์ด์ ๊ฐ์ age๋ก ๊ฐ์ต๋๋ค. ์ธ๋ฑ์ค ์ฌ์ฉ์ ๋ํ ์๊ฐ ์ฐจ๊ฐ ๋์ ์ฝ๊ฒ ๋ณด์ด๊ฒ ๋ง๋ค๊ธฐ ์ํ์ฌ 100๋ง๊ฐ์ ๋ฐ์ดํฐ๋ฅผ ์์ฑํ์ต๋๋ค.

โ select count(*) from members;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.04 sec)
LIKE ์ฐ์ฐ๊ณผ ์์ผ๋์นด๋
LIKE ์ฐ์ฐ์๋ฅผ ์ฌ์ฉํ๋ค๋ฉด ๋ฌธ์์ด์์ ํด๋น ํจํด์ ํฌํจํ๊ณ ์๋ ๋ฌธ์์ด์ ๊ฐ์ง ๋ ์ฝ๋๋ฅผ ํํฐ๋งํ ์ ์์ต๋๋ค.
โ select * from members where name like '%abc%';
157 rows in set (0.16 sec)
ย
โ select * from members where name like '%abc';
50 rows in set (0.17 sec)
ย
โ select * from members where name like 'abc%';
48 rows in set (0.17 sec)
์ ์ฟผ๋ฆฌ๋ ์ด๋ฆ ์ค abc๋ผ๋ ํจํด์ด ์กด์ฌํ๊ฑฐ๋, abc๋ก ์์ํ๊ฑฐ๋ ๋๋๋ ์ด๋ฆ์ ๋ํ ๊ฒฐ๊ณผ๋ฅผ ๋ฐํํฉ๋๋ค. ๊ฒฐ๊ณผ๋ฅผ ์ป๋๋ฐ์๋ ๋๋ต 0.17์ด๊ฐ ๊ฑธ๋ ธ์ต๋๋ค. ์ด๋ ํด๋น ํจํด์ด ์กด์ฌํ๋ ๋ ์ฝ๋๋ฅผ ์ป๊ธฐ ์ํด ๋ชจ๋ ๋ ์ฝ๋์ name ํ๋๋ฅผ ์กฐ์ฌํด์ผํ์ฌ ๋ฐ์ํ ๊ฒฐ๊ณผ์ ๋๋ค.
์ธ๋ฑ์ค ์์ฑ
์ฟผ๋ฆฌ ์ํ ์๊ฐ ๋จ์ถ์ ์ํด์ name ์ปฌ๋ผ์ ์ธ๋ฑ์ค๋ฅผ ์์ฑํด๋ณด๋๋ก ํฉ๋๋ค. ์ ๊ณผ์ฐ ์ํ ์๊ฐ์ด ์ผ๋ง๋ ๋จ์ถ๋ ๊น์?
CREATE INDEX name_idx ON members(name);โ select * from members where name like '%abc%';
157 rows in set (0.16 sec)
ย
โ select * from members where name like '%abc';
50 rows in set (0.17 sec)
ย
โ select * from members where name like 'abc%';
48 rows in set (0.01 sec)
์์ผ๋์นด๋๊ฐ ์์์ ๋ถ๊ฑฐ๋, ์์ ๋ถ์ ๊ฒฝ์ฐ์๋ ์ธ๋ฑ์ค ์์ฑ ์ ๊ณผ ์ฐจ์ด๊ฐ ์์์ผ๋ฉฐ, ์์ผ๋ ์นด๋๊ฐ ๋ค์ ๋ถ์ ๊ฒฝ์ฐ์๋ ์ํ์๊ฐ์ด ์ ์๋ฏธํ๊ฒ ๋จ์ถ๋์์ต๋๋ค.
์ ๊ทธ๋ด๊น?
์์ ๊ฐ์ ์์์๊ฐ์ด ๋ฐ์ํ ์ด์ ๋ '%abc%'์ '%abc'๋ก ํํฐ๋ง์ ํ๋ ์ฟผ๋ฆฌ๋ ์ธ๋ฑ์ค ์ฌ์ฉ์ด ๋ถ๊ฐ๋ฅํ๊ธฐ ๋๋ฌธ์ ๋๋ค. ์ด๋ ์ฟผ๋ฆฌ์ ์คํ๊ณํ์ ํ์ธํจ์ ํตํด ์ ์ ์์ต๋๋ค.
โ explain select * from members where name like '%abc%';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | members | NULL | ALL | NULL | NULL | NULL | NULL | 997839 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-------------+
1 row in set, 1 warning (0.01 sec)
ย
โ explain select * from members where name like '%abc';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | members | NULL | ALL | NULL | NULL | NULL | NULL | 997839 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+-------------+
1 row in set, 1 warning (0.01 sec)
์์ผ๋์นด๋๊ฐ ๋ฌธ์์ด์ ์์์, ์์ ๋ถ์ ์ฟผ๋ฆฌ๋ค์ ์ฟผ๋ฆฌ๋ฅผ ์ํํ๋๋ฐ์ ์ธ๋ฑ์ค๊ฐ ์ฌ์ฉ๋์ง ์์์ต๋๋ค.
โ explain select * from members where name like 'abc%';
+----+-------------+---------+------------+-------+---------------+----------+---------+------+-------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+-------+-----------------------+
| 1 | SIMPLE | members | NULL | range | name_idx | name_idx | 1023 | NULL | 48 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
ํ ํธ, ์์ผ๋์นด๋๊ฐ ๋ฌธ์์ด์ ๋ค์ ๋ถ์ ์ฟผ๋ฆฌ๋ ์์ฒญ์ ์ฒ๋ฆฌํ๋๋ฐ์ name_idx๋ฅผ ์ฌ์ฉํ์์ ์ ์ ์์ต๋๋ค.
์ธ๋ฑ์ค๋ ์ ๋ ฌ๋ ํธ๋ฆฌ๋ก ์กด์ฌํ๋ฉฐ, ์ฟผ๋ฆฌ๋ฅผ ์ํํ๊ธฐ ์ํด ์กฐ์ฌํด์ผํ๋ ๋ฒ์๋ฅผ ์ผ๋ถ ์๋ธํธ๋ฆฌ ๊ตฌ๊ฐ์ผ๋ก ์ ํํจ์ผ๋ก์จ ์ฑ๋ฅ์ ํฅ์์ ์ด๋ค๋ ๋๋ค. ์ธ๋ฒ์งธ ์ฟผ๋ฆฌ๋ 'abc'๋ก ์์ํ๋ name์ ๊ฐ์ง ๋ ์ฝ๋๋ฅผ ์ฐพ๋ ์ฟผ๋ฆฌ์ด๊ธฐ์, abc๋ก ์์ํ๋ ๋ ์ฝ๋๋ค์ด ๋ชจ์ฌ์๋ ์๋ธํธ๋ฆฌ๋ง ์กฐ์ฌํ๋ฉด ๋๋จธ์ง๋ ์ค์บํ ํ์๊ฐ ์์ต๋๋ค. ํ ํธ abc๋ฅผ ํฌํจํ๋, ํน์ abc๋ก ๋๋๋ name์ ๊ฐ์ง ๋ ์ฝ๋๋ฅผ ์ฐพ๊ธฐ ์ํด์๋ ์ฌ์ ์์ผ๋ก ์ ๋ ฌ๋ ํธ๋ฆฌ๋ ์ฌ์ฉ๋ ์ ์์ต๋๋ค. ์ด ๋๋ ๋ชจ๋ ๋ ์ฝ๋๋ฅผ ํ์ธํ์ฌ ํด๋น ํจํด์ด ์กด์ฌํ๋์ง ๊ฒ์ฌํด์ผํฉ๋๋ค.
๋ฐ๋ผ์ varchar์ ๊ฐ์ ๋ฌธ์์ด ํ์ ์์ ์์ผ๋์นด๋๋ฅผ ํจํด์ ์์ ์ฌ์ฉํ๋ ๊ฒ์ ํ์ค์บ์ด ๋ฐ์ํ๋ ๋ํ์ ์ธ ํจํด์ด๊ณ , ์ ๋ชจ๋ฅธ์ฑ๋ก ์ฌ์ฉํ๋ค๋ฉด ์ค์ํ๊ธฐ ์ฝ์ต๋๋ค. ์ด๋ฌํ ๋ฌธ์ ๋ฅผ ๊ทน๋ณตํ๊ณ ๋ฌธ์์ด์์ ํจํด์ ๊ฒ์ฌํ๊ธฐ ์ํด์๋ ๋ฌธ์์ด ๊ฒ์ ์ฉ์ผ๋ก ๋ง๋ค์ด์ง ์ ๋ฌธ ๊ฒ์ ์ธ๋ฑ์คFull-Text Index ๋, ๋ณ๋์ ๊ฒ์์์ง์ ์ฌ์ฉํ๋ ๊ฒ์ด ์ฑ๋ฅ์ ๋์ด๋ ๋์์ฑ ์ด ๋ ์ ์์ต๋๋ค.
์ธ๋ฑ์ฑ ๋์ง ์์ ์ปฌ๋ผ์ ์กฐ๊ฑด์ ์ or ์ฐ์ฐ๊ณผ ์ฌ์ฉ
โ select * from members where name like 'abc%' or age > 99;
10015 rows in set (0.17 sec)
์์ ๊ฐ์ด ์ธ๋ฑ์ค๊ฐ ๊ฑธ๋ ค์์ง ์์ age ์ปฌ๋ผ์ ๋ํ์ฌ OR ์กฐ๊ฑด์ ๊ฐ์ง ์ฟผ๋ฆฌ๋ฅผ ์ํํ๋ ๊ฒ๋ Full Scan์ด ๋ฐ์ํ๊ฒ ๋ฉ๋๋ค. ์๋ฌด๋ฆฌ name์ ๋ํด ์ธ๋ฑ์ค๊ฐ ๊ฑธ๋ ค์๋๋ผ๋, age ์กฐ๊ฑด์ ๋ง์กฑํ๋ ๊ฒฐ๊ณผ๋ฅผ ์ป๊ธฐ ์ํด์ ์ ์ฒด ํ ์ด๋ธ์ ์กฐํํด์ผํ๊ธฐ ๋๋ฌธ์ ๋๋ค.
โ explain select * from members where name like 'abc%' or age > 99;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | members | NULL | ALL | name_idx | NULL | NULL | NULL | 997839 | 40.74 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
๋ฌผ๋ก AND ์กฐ๊ฑด์ ์ธ๋ฑ์ค๋ฅผ ํ์ฉํ ๊ฒฐ๊ณผ๋ก age๋ฅผ ํํฐ๋งํด๋ ๋ฌด๋ฐฉํ๊ธฐ ๋๋ฌธ์ ์กฐ์ฌ ๋ฒ์๋ฅผ ์ค์ผ ์ ์์ต๋๋ค.
โ select * from members where name like 'abc%' and age = 1000;
Empty set (0.01 sec)
โ explain select * from members where name like 'abc%' and age > 99;
+----+-------------+---------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------------------+
| 1 | SIMPLE | members | NULL | range | name_idx | name_idx | 1023 | NULL | 48 | 33.33 | Using index condition; Using where |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------------------+
์ปฌ๋ผ์ด ๊ฐ๊ณต๋๋ ๊ฒฝ์ฐ
์ปฌ๋ผ์ด ๊ฐ๊ณต๋์ด ์ฒ๋ฆฌ๋์ด์ผ ํ๋ ์ฟผ๋ฆฌ ์ญ์ ์ธ๋ฑ์ค๋ฅผ ์ฌ์ฉํ ์ ์์ต๋๋ค.
์ ์์ ์์ ์ฌ์ฉํ๋ Member ํ ์ด๋ธ์ age๋ฅผ ์กฐ๊ฑด์ผ๋ก ํ๋ ์ฟผ๋ฆฌ๋ฅผ ์์ฑํ๊ณ ์ํ์๊ฐ์ ํ์ธํด๋ณด๊ฒ ์ต๋๋ค.
โ select * from members where age = 99;
10042 rows in set (0.17 sec)
๋์ด๊ฐ 99์ธ ํ์์ ์กฐํํ๋ ์ฟผ๋ฆฌ๋ 0.17์ด๊ฐ ๊ฑธ๋ ธ์ต๋๋ค. ํ์ฌ์ ๊ฒฐ๊ณผ๋ ๋ณ๋์ ์ธ๋ฑ์ค๊ฐ ์์ผ๋ฏ๋ก ๋ชจ๋ ๋ ์ฝ๋๋ฅผ ์กฐํํ์ฌ ๋์ด๊ฐ 99์ธ ๊ฒฐ๊ณผ๋ง ๊ฐ์ ธ์ค๊ฒ ๋์์ต๋๋ค. ์ด๋ฌํ ๊ฒฝ์ฐ age์ ์ธ๋ฑ์ค๊ฐ ์๋ค๋ฉด, ์กฐ์ฌ์ ๋ฒ์๋ฅผ ์ค์ฌ ํจ์จ์ ์ผ๋ก ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ๋ฅผ ๊ฐ์ ธ์ฌ ์ ์์ ๊ฒ์ ๋๋ค.
โ create index age_idx on members(age);
โ select * from members where age = 99;
10042 rows in set (0.05 sec)
ํ ํธ, ์ฟผ๋ฆฌ์ ์ธ๋ฑ์ค ์ปฌ๋ผ์ ๋ํ ์ฐ์ฐ๊ณผ์ ์ด ํ์ํ๋ค๋ฉด ์ธ๋ฑ์ค๋ ์ฌ์ฉ๋ ์ ์์ต๋๋ค.
โ select * from members where age * 2 = 198;
10042 rows in set (0.15 sec)
์ ์ฟผ๋ฆฌ๋ age์ 2๋ฅผ ๊ณฑํ ๊ฐ์ด 198์ธ (์ฆ age๊ฐ 99์ธ) ๋ฐ์ดํฐ๋ค์ ๊ฐ์ ธ์ค๋ ์ฟผ๋ฆฌ์ ๋๋ค. ์ด ์ฐ์ฐ์ ์ํํ๊ธฐ ์ํด์ MySQL์ ์๋ณธ ์ปฌ๋ผ์ ๋ฐ์ดํฐ๊ฐ ์ ๋ ฌ๋์ด ์ ์ฅ๋์ด์๋ ์ธ๋ฑ์ค๋ฅผ ์ฌ์ฉํ์ง ๋ชปํฉ๋๋ค. age๋ ๋งค ๋ ์ฝ๋๋ง๋ค ์๋กญ๊ฒ ๊ณ์ฐ๋์ด 198๊ณผ ๋น๊ต๋์ด์ผ ํด๋น ๋ ์ฝ๋๋ฅผ ๊ฐ์ ธ์ฌ์ง ๋ง ์ง๋ฅผ ๊ฒฐ์ ํ ์ ์์ต๋๋ค.
โ explain select * from members where age * 2 = 198;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | members | NULL | ALL | NULL | NULL | NULL | NULL | 997839 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+