1편에 이어서..
특정 Like 조건을 인덱스 할 수 없는 이유
- B-Tree 인덱스는 기본적으로 오름차순 정렬 되기 때문에 왼쪽 값을 기준으로 오른쪽 값이 정렬된다. 이러한 특징으로 인해 특정 LIKE 조건을 사용할 때 인덱스를 적용할 수 없다.
1. LIKE '%mat'
2. LIKE '_mat'
3. LIKE '%mat%'
email 칼럼에 인덱스 설정을 해준다.
해당 인덱스를 기반으로 조회를 진행할 때는 저장된 값의 왼쪽 부터 한 글자씩 비교해가며 일치하는 레코드를 찾아간다.
만약 아래와 같은 쿼리를 처리한다고 가정할때
위 쿼리는 인덱스 레인지 스캔 방식을 사용할 수 없다. 이유는 email 칼럼에 저장된 값의 왼쪽부터 한 글자씩 비교해가며 일치하는 레코드를 찾아야 하는데, 조건절에 주어진 값은 왼쪽 부분이 고정되어 있지 않기 때문이다.
실제 실행 계획을 살펴보아도 풀 테이블 스캔을 진행하는 것을 확인할 수 있다.
mysql> EXPLAIN SELECT * FROM members m WHERE m.email LIKE '%mat%';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | m | NULL | ALL | NULL | NULL | NULL | NULL | 9421768 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
Full Text search
- 그렇다면 특정 문자열을 포함하는 레코드를 조회하기 위해서는 무조건 풀 테이블 스캔을 진행해야 하는가? MySQL의 InnoDB나 MyISAM은 문서 전체에 대한 분석과 검색을 위한 인덱싱 알고리즘인 전문 검색(full text search)을 제공한다. 전문 검색을 활용하면 앞서 언급한 특정 LIKE조건에 인덱스를 활용할 수 있는 대안이 될 수 있다.
전문 검색을 생성하기 위해서는 아래와 같이 작성한다.
생성된 인덱스를 확인한다.
mysql> SHOW INDEX FROM members;
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| members | 0 | PRIMARY | 1 | id | A | 9421768 | NULL | NULL | | BTREE | | | YES | NULL |
| members | 1 | email | 1 | email | NULL | 9915796 | NULL | NULL | | FULLTEXT | | | YES | NULL |
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
생성한 인덱스는 WHERE절에 특수한 메서드( MATCH(), AGAINST() )를 활용해야 한다.
mysql> EXPLAIN SELECT FROM members m WHERE MATCH(m.email) AGAINST('ab' IN BOOLEAN MODE);
+----+-------------+-------+------------+----------+---------------+-------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+----------+---------------+-------+---------+-------+------+----------+-----------------------------------+
| 1 | SIMPLE | m | NULL | fulltext | email | email | 0 | const | 1 | 100.00 | Using where; Ft_hints: no_ranking |
+----+-------------+-------+------------+----------+---------------+-------+---------+-------+------+----------+-----------------------------------+
실행 계획의 type을 살펴보면 fulltext를 활용하는 것을 확인할 수 있다. 자세한 사용 방법은 추후 학습을 통해 추가할 예정이다.
출처 : https://hyeonic.github.io/database/basic/like-index.html
