'MYSQL'에 해당되는 글 2건
[MySQL] 멀티 컬럼 select[MySQL] 멀티 컬럼 select
Posted at 2014. 3. 12. 11:03 | Posted in Server`userid` bigint(20) NOT NULL,
`groupid` bigint(20) NOT NULL,
`state` tinyint(1) NOT NULL,
`col1` bigint(20) DEFAULT NULL,
`cal2` varchar(200) NOT NULL,
`cal3` varchar(100) NOT NULL,
`create_time` datetime NOT NULL,
`update_time` datetime NOT NULL,
PRIMARY KEY (`userid`,`groupid`),
KEY `idx_userid_createtime` (`userid`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
(1) 컬럼 조합 IN 쿼리
EXPLAIN
SELECT *
FROM mytable
WHERE (userId, groupid) IN ((1526291770280964803,2679212559264916326),(1526292017760867305, 2679249801360511502),(1526421185616035771,2679342905646882845));
id|select_type|table |type|possible_keys|key |key_len|ref |rows|Extra
1 |SIMPLE |mytable|ALL |NULL |NULL|NULL |NULL|172 |Using where
index를 타지 않는다.
(2) FROM절 subquery
EXPLAIN
SELECT a.* FROM (SELECT * FROM mytable WHERE userid IN (1526291770280964803,1526292017760867305,1526421185616035771)) a
WHERE a.groupid IN (2679212559264916326, 2679249801360511502, 2679342905646882845);
id|select_type|table |type |possible_keys|key|key_len|ref|rows|Extra
1 |PRIMARY |<derived2>|ALL |NULL|NULL|NULL|NULL|15|Using where
2 |DERIVED |mytable |range|PRIMARY,idx_userid_createtime|PRIMARY|8|NULL|15|Using where
userid가 index를 탄다. groupid도 인덱스를 걸어줘야 할 듯.
(3) 각 컬럼에 대해서 IN query
EXPLAIN
SELECT *
FROM mytable WHERE userid IN (1526291770280964803,1526292017760867305,1526421185616035771) AND groupid IN (2679212559264916326, 2679249801360511502, 2679342905646882845);
id|select_type|table |type |possible_keys|key|key_len|ref|rows|Extra
1 |SIMPLE |mytable|range|PRIMARY,idx_userid_createtime|PRIMARY|16|NULL|9|Using where
결론) 3번이 가장 나아보임.
[MySQL] 실행계획(EXPLAIN)[MySQL] 실행계획(EXPLAIN)
Posted at 2014. 3. 12. 10:39 | Posted in Server- ALL: full scan (extra에 "Using ditict"/"not exists" 가 있거나 LIMIT있는 쿼리는 예외)
- index: full scan but index order. 장점:정렬할 필요 없다. 단점: 전체 테이블을 인덱스 순서로 읽어야해서 random access가 일어남(비용 큼). (extra에 "Using index"가 나오면 커버링 인덱스를 사용하는 것임.(인덱스의 데이터만을 스캔한다는 것임))
- range: 제한된 형태의 index 스캔. index보다는 나은 성능을 보인다. 범위에 따라 성능차가 있다.
- ref: 어떤 조건 하나에 매치되는 행들을 반환해주는 인덱스 접근 방식. 여러개의 행을 찾게 될 수도 있으므로 탐색과 스캔이 함계 사용된다. unique하지 않은 인덱스 검색이 걸릴때 사용된다.
- eq_ref: MySQL이 기껏해야 값 하나만을 반환한다는 것을 알때 사용됨. 기본 키 혹은 unique 인덱스에 걸릴때 사용.
- const, system: 쿼리의 일부를 상수로 대체해서 최적화 할 수 있는 경우.
- NULL: 인덱스나 테이블에 접근조차 하지 않는 경우.
key: MySQL이 최적화를 위해 어떤 키를 사용하기로 했는지를 나타냄.
key_len: MySQL이 인덱스에 얼마나 많은 바이트를 사용하는지를 보여줌.
ref: key 에 나와 있는 인덱스에서 값을 찾기 위해 선행 테이블의 어떤 컬럼(또는 상수(const))이 사용되었는지를 나타냄.
rows: 원하는 행을 찾기위해 얼마나 많은 행을 읽어야 할지에 대한 예측값. 예측값은 인덱스의 선택도와 테이블 통계 정보에 의존적이므로 정확도가 상당히 떨어질 수도 있다.
MySQL이 조사해야 할 것이라고 생각하는 행 수를 의미할 뿐 결과에 있는 행 수를 의미하지 않는다. 여기서 보여지는 행 수가 그리 중요하지 않을 수도 있다. 예측한 모든 행을 읽지는 않을 것이며, 대체로도 그렇다.
extra: 앞 선 컬럼에 적합하지 않은 나머지 정보를 표시. (중요하고 빈번한 몇가지만 나열)
- Using index: 커버링 인덱스를 사용한다는 것을 알려줌.
- Using where: MySQL서버가 스토리지 엔진에서 값을 가져온 뒤 행을 필터링한다는 것을 의미.
- Using temporary: MySQL이 쿼리 결과를 정렬하기 위해 임시 테이블을 사용한다는 것을 의미.
- Using filesort: 이는 MySQL이 결과의 순서를 맞추기 위해 인덱스 순서로 테이블을 읽는 것이 아니라 외부 정렬을 사용해야 한다는 것을 의미. 메모리나 디스크에서 수행될 수 있으며, EXPLAIN으로는 어떤 방식을 사용하지는, 디스크에서 하는지 메모리에서 하는지 등은 알 수 없다.
- Range checked for each record(index map:N): 적합한 인덱스가 없으므로 각 레코드의 조인에서 각 인덱스들을 재평가한다는 것을 의미한다. N은 possible_keys에 나타나 있는 인덱스들의 비트맵 값이다.
www.deepblue.pe.kr
