데이터베이스 질문과 답변
고수님들의 조언좀 부탁드립니다.
아직까지 갈길이 참 먼것같습니다.
다름이 아니라
아래와 같은 쿼리문을 explain했을때 인덱스를 타는지 체크를 하고있습니다.
건수는 250만건 정도인데요
인덱스는 아래처럼 잡았습니다.
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| tbl | 0 | PRIMARY | 1 | idx | A | 2275687 | (NULL) | (NULL) | BTREE | ||
| tbl | 1 | name | 1 | name | A | 379281 | (NULL) | (NULL) | YES | BTREE | |
| tbl | 1 | name | 2 | A | 2275687 | (NULL) | (NULL) | YES | BTREE | ||
| tbl | 1 | code | 1 | code | A | 2 | (NULL) | (NULL) | YES | BTREE | |
| tbl | 1 | code | 2 | num | A | 2275687 | (NULL) | (NULL) | YES | BTREE |
EXPLAIN SELECT
idx,
CODE,
num,
NAME,
email,
domain
FROM
tbl
WHERE
CODE = 'sign'
ORDER BY num DESC
-- 결과 --
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | tbl | ALL | code | (NULL) | (NULL) | (NULL) | 1706907 | Using where; Using filesort |
많은 정보를 찾아보고 시도를 해도 도대체 뭐가 문제인지 모르겟습니다.
고수님들의 조언좀 부탁드립니다.
인덱스가 3개 있네요.
1. pk (idx)
2. name (name, email)
3. code (code, prino)
조건절은 code 뿐이네요.
그런데 code 는 값의 종류가 2개 뿐이네요.
code 는 변별력이 없어 인덱스 타기에 적합하지 않아요.
그래서 실행계획도 테이블 풀스캔 탄 듯 하네요.
답변감사합니다.
답변중에 code는 값의종류가 2개라는게 무슨뜻인지요.?
추가조건절은 검색할때 이루어지는거라 예시에는 입력하지 않았습니다. ㅡㅡ
Cardinality 항목의 값이 2 네요.
예를 들어 주민 번호 같은 항목은 유니크하고 Cardinality = 전체 행수 가 되구요.
성별 같은 경우 값은 남/녀 2개 뿐이죠 이경우 Cardinality = 2 가 되는 거죠.
네.감사합니다.
그렇다면 인덱스를 잘못잡은것 같은데요.
어떤형태로 잡아야될지 조언좀 구합니다.
결과rows수가 너무 많아, 인덱스스캔보다 풀스캔이 낫다고
옵티마이져가 판단 한 겁니다.
실행결과만봐도 그렇게보이네요.
code인덱스를 사용했을때, 랜덤엑세스가 매우 많이 발생해야되니까요..
인덱스를 무조건 타야 하는 것은 아닙니다.
위 경우엔 250만건 중에 170만건을 조회하는 구문이니
인덱스틀 탈 필요가 없는 구문입니다.
만약 위 구문을 페이징 처리에 이용한다면?
LIMIT 구문으로 검색 범위를 줄여준다면?
code 인덱스를 이용할 수 있겠네요.
자꾸 질문드려 죄송합니다.
order by 절이 들어가야하기때문에 그렇습니다.
테스트로 아래처럼 했을때에도 결과는 같네요
EXPLAIN SELECT
idx,
CODE,
num,
NAME,
email,
conn_domain
FROM
tbl
WHERE
CODE='sign'
ORDER BY num DESC
LIMIT 0,15
-- 결과 --
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | tbl | range | code | code | 93 | (NULL) | 2282662 | Using where; Using filesort |
답변감사합니다.^^
CODE + num DESC 로 인덱스를 만들면 좋을거 같네요..
음 MySQL 쪽 잘 안써봐서...
아마도 MySQL 버전이 낮은 경우 DESC 정렬시 인덱스 사용 못하는 듯 하네요.
최신 버전에서는 잘 될 것으로 기대합니다.
1. 일단 인덱스 힌트 한번 사용해 보시구요.
- FROM tbl USE INDEX (code)
2. 조건절도 추가해 보시구요.
- AND num > 0
3. 이도 저도 안된다면?
- 인덱스를 DESC 로 다시 만들어 보세요.
- (code, num DESC)
모든분들 답변감사합니다.^^
마농님의 말씀을 빌리자면
현재 서버는 클라우드서버입니다. MySQL버전은 5버전입니다.
마지막으로 한가지만 더여쭐께요
위에 글올린내용중에요 name이라는 인덱스가 있습니다.
250만건의 리스트가 출력이될때 아무상관이 없겠으나
검색을 name, email이라는 컬럼으로 할때 code인덱스에 추가해서 사용할수있는지요.
검색 패턴마다 필요 인덱스는 다릅니다.
그렇다고 모든 검색 패턴별로 인덱스를 만들수는 없겠지요.
자주 쓰는 패턴 몇가지를 가지고 필요 인덱스를 검토하고
중요 검색 조건을 선정하고
인덱스들를 통합할 수 있는지까지를 검토해서
최종 인덱스를 선정해야 하겠지요.
http://wiki.gurubee.net/pages/viewpage.action?pageId=12189750
) 버튼을 클릭하여 작성 하시면 됩니다.