MySQL의 문자열 필드의 경우 in을 사용할 때 따옴표가 없으면 성능 문제가 발생합니다.

시나리오 및 환경

redhat6.5 + 64비트 + 12코어 + 16G

테이블 수 600w

MySQL 5.0

문제 설명

in을 사용하는 과정에서 동료가 조건부 쿼리에 간단한 내용을 작성했습니다(필드는 공통 인덱스인 varchar입니다). 느린 쿼리 다수 발생

문제 SQL

select count(*) total from member_phone where phone in(1521xxx541,15845xxx412)

문제 SQL과 수정 쓰기 비교

실행 시간

mysql> select count(*) total from member_phone where phone in(1521xxx541,15845xxx412);
+-------+
| total |
+-------+
|     1 | 
+-------+
1 row in set (2.76 sec)
mysql> select count(*) total from member_phone where phone in('1521xxx541','15845xxx412');
+-------+
| total |
+-------+
|     1 | 
+-------+
1 row in set (0.01 sec)
mysql> select count(*) total from member_phone where (phone='1521xxx541' or phone='15845xxx412');
+-------+
| total |
+-------+
|     1 | 
+-------+
1 row in set (0.00 sec)

EXPLAIN

mysql> explain select count(*) total from member_phone where phone in(1521xxx541,15845xxx412) \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: member_phone
         type: index
possible_keys: phone
          key: phone
      key_len: 18
          ref: NULL
         rows: 6307075
        Extra: Using where; Using index
1 row in set (0.00 sec)
mysql> explain select count(*) total from member_phone where phone in('1521xxx541','15845xxx412') \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: member_phone
         type: range
possible_keys: phone
          key: phone
      key_len: 18
          ref: NULL
         rows: 2
        Extra: Using where; Using index
1 row in set (0.00 sec)
mysql> explain select count(*) total from member_phone where (phone='1521xxx541' or phone='15845xxx412') \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: member_phone
         type: range
possible_keys: phone
          key: phone
      key_len: 18
          ref: NULL
         rows: 2
        Extra: Using where; Using index
1 row in set (0.01 sec)

요약

SQL의 세 가지 유형 중 효율성이 높은 것부터 낮은 것까지는 or, 따옴표 있음, 따옴표 없음입니다. 설명에 따옴표가 없으면 인덱스 폰을 사용한다는 뜻이며, 타입이 인덱스가 된다는 뜻이다. MySQL이 행이 아닌 인덱스 순서로 스캔한다는 점만 빼면 전체 테이블 스캔과 거의 같다.

알림

where에 or가 여러 개 있거나 in에 조건이 많거나 in이 여러 개 있는 경우 실제 성능은 상대적으로 낮습니다. 저는 개인적으로 위 테스트를 MySQL 5.0에서만 테스트했습니다. 상위 버전에서 공식적으로 최적화되었는지는 모르겠습니다.