[MySQL] LIKE 쿼리 최적화

LIKE 쿼리 최적화

일단, 여기서 언급하는 MySQL 버전은 5.7.32다

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.32    |
+-----------+
1 row in set (0.00 sec)

mysql>

테이블 및 데이터 삽입

-- test DB 생성
create database test
      default character set utf8mb4
      collate utf8mb4_bin;
use test;

-- test 테이블 생성
drop table if exists test;
create table test (
    a int primary key auto_increment,
    b varchar(64) not null,
    key(b) );

-- 행 삽입
begin;
insert into test(b) select crc32(rand()) from dual;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
insert into test(b) select crc32(rand()) from test;
commit;

-- 개수 확인
select count(*) from test;

위에 대한 결과는 아래와 같다.

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|   131072 |
+----------+
1 row in set (0.01 sec)

LIKE 에 대한 플랜

• LIKE '123%'

mysql> explain select * from test where b like '123%';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | b             | b    | 258     | NULL |  340 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

• LIKE '%123'

mysql> explain select * from test where b like '%123';
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | b    | 258     | NULL | 130986 |    11.11 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

두 쿼리 모두 컬럼 b의 인덱스를 이용한다. (explain의 결과 테이블의 key 컬럼)

특히, like '%123'의 경우 와일드 카드가 앞에 있음에도 불구하고 b 컬럼 인덱스를 이용한다. (참고: 정확한 하위 버전은 기억나진 않지만, 예전 버전은 풀스캔을 시도하였다) 모든 로우를 검사하므로 explain의 rows는 130986으로 나온다. 즉, 모든 로우를 검사한다.

헌데, b 컬럼 인덱스는 클러스터드 인덱스가 아니므로, 페이지에 (키, 레코드주소) 쌍으로 저장된다. 즉 페이지당 검색할 수 있는 키 엔트리가 많아 검색 I/O가 적어진다. 결론적으로, %123이라 하여도, 검색에 인덱스를 이용하는 것은 성능이 높아지는 것을 의미한다.

검색 성능을 더 올리고 싶다면, 어떻게 해야 할까?

LIKE %123 쿼리가 모든 로우를 검사하지 않게 하는 방법은 몇 가지가 있다.

 

• 모든 버전: %123이 필터링되는 로우에서 동일한 값을 가지는 컬럼을 검토하고, 이에 인덱스를 생성. (안 될 가능성이 아주 높다)
• 5.6 버전 이하: 컬럼을 추가 및 인덱스를 생성. b의 마지막 세자리만 컬럼을 추가하는 트리거를 생성.(추가 공간이 필요)
• 5.7 버전: 버추얼 컬럼 추가한 후, 인덱스 생성.
  • STORED 생성시, 5.6 버전 해결책과 거의 같다.
  • VIRTUAL 방식으로 생성한다면, SELECT시 해당 컬럼을 조회하지 않아야 성능에 문제 없다
• 8.0 버전: FBI로 해결
  • 참고: http://minsql.com/mysql8/B-3-C-functionBasedIndex/