조금 신기한 현상을 봐서 정리해본다.
# 테스트용 테이블 zipcode_01 생성
mysql> create table zipcode_01 as select * from zipcode where 1=0;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
# buildnum + zipcode 를 PK로 잡아준다. (결합인덱스)
mysql> alter table zipcode_01 add primary key (buildnum, zipcode);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 테스트용 데이터 입력
mysql> insert into zipcode_01 select * from zipcode order by buildnum, zipcode;
Query OK, 6348671 rows affected (4 min 21.79 sec)
Records: 6348671 Duplicates: 0 Warnings: 0
# zipcode_01, zipcode 테이블의 인덱스 정보 확인
mysql> show index from zipcode_01;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| zipcode_01 | 0 | PRIMARY | 1 | buildnum | A | 6236954 | NULL | NULL | | BTREE | | |
| zipcode_01 | 0 | PRIMARY | 2 | zipcode | A | 6236954 | NULL | NULL | | BTREE | | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.04 sec)
mysql> show index from zipcode;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| zipcode | 0 | PRIMARY | 1 | seq | A | 6097975 | NULL | NULL | | BTREE | | |
| zipcode | 1 | idx1_zipcode | 1 | sido | A | 6667 | NULL | NULL | YES | BTREE | | |
| zipcode | 1 | idx1_zipcode | 2 | buildname | A | 1945822 | NULL | NULL | YES | BTREE | | |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)
테스트용 데이터 고르기
# 아래의 데이터로 테스트를 진행한다.
# zipcode : 06018, buildnum : 1168010700106590009000001
# zipcode : 07783, buildnum : 1150010300108490001023500
mysql> select zipcode, buildnum from zipcode where sido = '서울특별시' and buildname like '혜성빌딩%';
+---------+---------------------------+
| zipcode | buildnum |
+---------+---------------------------+
| 06018 | 1168010700106590009000001 |
| 07783 | 1150010300108490001023500 |
...
19 rows in set (5.82 sec)
Select 문은 buildnum + zipcode PK 잘탄다.
mysql> explain select * from zipcode_01
-> where zipcode in ('06018', '07783')
-> and buildnum in (select buildnum
-> from zipcode
-> where sido = '서울특별시'
-> and buildname like '혜성빌딩%');
+----+-------------+------------+-------+--------------+---------+--------------------------+------+----------+-----------------------------------------------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+-------+--------------+---------+--------------------------+------+----------+-----------------------------------------------------+
| 1 | SIMPLE | zipcode | range | idx1_zipcode | 386 | NULL | 19 | 100.00 | Using index condition; Using where; Start temporary |
| 1 | SIMPLE | zipcode_01 | ref | PRIMARY | 102 | zipcode.zipcode.buildnum | 1 | 20.00 | Using where; End temporary |
+----+-------------+------------+-------+--------------+---------+--------------------------+------+----------+-----------------------------------------------------+
자 그럼 Delete는? 인덱스 안탄다.. key 도 없고 type ALL 이다.
mysql> explain delete from zipcode_01
-> where zipcode in ('06018', '07783')
-> and buildnum in (select buildnum
-> from zipcode
-> where sido = '서울특별시'
-> and buildname like '혜성빌딩%');
+----+--------------------+------------+-------+--------------+---------+------+---------+----------+------------------------------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+-------+--------------+---------+------+---------+----------+------------------------------------+
| 1 | DELETE | zipcode_01 | ALL | NULL | NULL | NULL | 6236954 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | zipcode | range | idx1_zipcode | 386 | NULL | 19 | 10.00 | Using index condition; Using where |
+----+--------------------+------------+-------+--------------+---------+------+---------+----------+------------------------------------+
물론 아래처럼 where 절에 in 절을 join 으로 바꾸거나 buildnum 을 직접 넣어주면 인덱스 잘탄다.
mysql> explain delete from zipcode_01
-> where zipcode in ('06018', '07783')
-> and buildnum in ('1168010700106590009000001','1150010300108490001023500');
+----+-------------+------------+-------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+-------+---------+---------+-------------+------+----------+-------------+
| 1 | DELETE | zipcode_01 | range | PRIMARY | 124 | const,const | 4 | 100.00 | Using where |
+----+-------------+------------+-------+---------+---------+-------------+------+----------+-------------+
mysql> explain delete a
-> from zipcode_01 a
-> inner join zipcode b on a.zipcode = b.zipcode and a.buildnum = b.buildnum
-> where b.sido = '서울특별시' and b.buildname like '혜성빌딩%'
-> and a.zipcode in ('06018', '07783');
+----+-------------+-------+--------+--------------+---------+--------------------------------------+------+----------+-------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+--------------+---------+--------------------------------------+------+----------+-------------+
| 1 | SIMPLE | b | range | idx1_zipcode | 386 | NULL | 19 | 100.00 | Using where |
| 1 | DELETE | a | eq_ref | PRIMARY | 124 | zipcode.b.buildnum,zipcode.b.zipcode | 1 | 100.00 | NULL |
+----+-------------+-------+--------+--------------+---------+--------------------------------------+------+----------+-------------+
혹시나 하는 생각에 buildnum + zipcode의 반대인 zipcode + buildnum 결합인덱스를 생성하고 다시 쿼리!
zipcode + buildnum 결합인덱스 잘탄다.
Delete 시에는 결합인덱스의 첫번째 컬럼을 조회하는 IN 절에 서브 쿼리가 들어가 있는 경우는 인덱스를 안타는구나..
mysql> explain delete from zipcode_01
-> where zipcode in ('06018', '07783')
-> and buildnum in (select buildnum
-> from zipcode
-> where sido = '서울특별시'
-> and buildname like '혜성빌딩%');
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+
| 1 | DELETE | zipcode_01 | range | idx_zipcode_01 | 22 | const | 351 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | zipcode | range | idx1_zipcode | 386 | NULL | 19 | 10.00 | Using index condition; Using where |
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+
여기서 또 하나 궁금해지는것 그렇다면 zipcode + buildnum 결합인덱스를 삭제하고 buildnum + zipcode 결합인덱스만있는 상태에서 쿼리를 고쳐서 하면? 인덱스 잘탄다.
# idx_zipcode_01 (zipcode + buildnum) 인덱스 삭제
mysql> alter table zipcode_01 drop index idx_zipcode_01 ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain delete from zipcode_01
-> where buildnum in ('1168010700106590009000001','1150010300108490001023500')
-> and zipcode in (select zipcode
-> from zipcode
-> where sido = '서울특별시'
-> and buildname like '혜성빌딩%');
+----+--------------------+------------+-------+--------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+-------+--------------+---------+-------+------+----------+------------------------------------+
| 1 | DELETE | zipcode_01 | range | PRIMARY | 102 | const | 2 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | zipcode | range | idx1_zipcode | 386 | NULL | 19 | 10.00 | Using index condition; Using where |
+----+--------------------+------------+-------+--------------+---------+-------+------+----------+------------------------------------+
그렇다면 Update는 어떨까?
동일하다!
mysql> explain update zipcode_01
-> set sido = '강남시'
-> where zipcode in ('06018', '07783')
-> and buildnum in (select buildnum
-> from zipcode
-> where sido = '서울특별시'
-> and buildname like '혜성빌딩%');
+----+--------------------+------------+-------+--------------+---------+------+---------+----------+------------------------------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+-------+--------------+---------+------+---------+----------+------------------------------------+
| 1 | UPDATE | zipcode_01 | index | PRIMARY | 124 | NULL | 6236954 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | zipcode | range | idx1_zipcode | 386 | NULL | 19 | 10.00 | Using index condition; Using where |
+----+--------------------+------------+-------+--------------+---------+------+---------+----------+------------------------------------+
mysql> alter table zipcode_01 add index idx_zipcode_01 (zipcode, buildnum);
Query OK, 0 rows affected (41.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain update zipcode_01
-> set sido = '강남시'
-> where zipcode in ('06018', '07783')
-> and buildnum in (select buildnum
-> from zipcode
-> where sido = '서울특별시'
-> and buildname like '혜성빌딩%');
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+
| 1 | UPDATE | zipcode_01 | range | idx_zipcode_01 | 22 | const | 351 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | zipcode | range | idx1_zipcode | 386 | NULL | 19 | 10.00 | Using index condition; Using where |
+----+--------------------+------------+-------+----------------+---------+-------+------+----------+------------------------------------+
'MySQL' 카테고리의 다른 글
| binlog format (0) | 2021.05.15 |
|---|---|
| 인덱스 최대 크기 (0) | 2021.05.13 |
| MySQL 포퍼먼스 튜닝 (0) | 2021.05.12 |
| MySQL 5.7 Virtual Columns (0) | 2021.04.27 |
| MySQL 5.6 -> 5.7 병렬복제 (0) | 2021.04.27 |