눈팅만 하다가, 제 이름 석자가 나오길래 적고 갑니다^^

 

인덱스는 WHERE의 AND 조건에서 앞에 있는 컬럼인 경우 인덱스를 탑니다.

말이 에매모호하죠?^^

 

예를 들어

 

A :  index( f1 ), index(f2), index(f3)

B :  index ( f1, f2, f3 )

 

가 있을 경우.

 

인덱스를 타느냐 안 타느냐는 사용하는 SELECT 문의 WHERE 절에 전적으로 달려 있습니다.

 

SELECT 문을 같이 올려 주셨으면 더 좋았을 텐데요.

 

예를 들어.

 

WHERE f2 = '2'

 

이라는 쿼리가 있으면, A의 경우 인덱스를 타지만, B의 경우 인덱스를 타지 않습니다.

f2가 복합 인덱스의 중간에 나와 있기 때문입니다.

 

WHERE f1 = '1' AND f2 = '2'

 

라는 쿼리의 경우,

 

A와 B 모두 인덱스를 탑니다.

하지만, A는 f1 인덱스만 타고, f2 인덱스는 타지 못합니다.

B의 경우 f1과 f2의 두 값을 모두 가지고 있으므로, 제한을 많이 해 주는 B 인덱스가 더 효율적이겠죠.

A와 B 인덱스 모두 존재하는 경우 옵티마이저는 B 인덱스를 사용할 것입니다.

 

암튼 jude 님께서 사용하시는 쿼리 문을 보면 좀 더 정확하게 설명을 드릴 수 있으며, 영어가 크게 어렵진 않으니

 

http://dev.mysql.com/doc/refman/4.1/en/mysql-indexes.html

 

여길 읽어 보시구요.

 

책을 참고하시겠다면, 그 이름도 유명한 대용량 데이터베이스 솔루션을 읽어 보시면 됩니다.

 

1. 첫번째 경우와 두번째의 인덱스 활용이 다른 이유?

>> 앞서 설명 드린 것으로 대치합니다.

 

2. 첫번째와 같이 사용한 경우에 인덱스를 활용하려면?

>> WHERE 절에서 field1 = '1' AND field2 = '2' AND field3  = '3'와 같은 쿼리 혹은 field1 = '1' AND field2 = '2' ORDER BY field3 와 같은 쿼리에서 인덱스를 잘 탈 수 있습니다.

 

3. 첫번째와 두번째 각각 디스크 사용 용량은 차이가 있는지?

>> 차이가 있겠죠?

 

4. 그 밖에 MySQL 버젼에 영향을 받는지?

>> 4.0 미만 버전과 4.0 이상 버전에서의 차이는 ORDER BY에 사용되는 컬럼이 ASC냐 DESC냐에 따라 달라집니다.

INDEX( f1, f2)가 있을 때, 4.0 미만에서는 WHERE f1 = '1' ORDER BY f2 DESC가 인덱스를 못타지만, 4.0이상에서는 인덱스를 탑니다.

 

그럼^^

 

관심가져 주셔서 감사합니다.

쿼리문은 간단한 조인문 이였는데 제대로 알지도 못하고 무조건 만들다보니 엉망진창 입니다.

 

테이블 구조를 말씀드리면

###A 테이블

no... userid... mobile_phone..

birth DATE,           ### 생년월일 YYYY-MM-DD

birth_cate INT(2)   ### 생일[음력,양력]

INDEX birthidx (birth,birth_cate)

 

### B테이블

lunar_date DATE       ### 음력 년,월,일

solar_date DATE       ### 양력 년,월,일

index lunar_date (lunar_date)

index solar_date (solar_date)

 

쿼리문의 목적은 회원정보 테이블(A)의 생일에서 음,양력 변환테이블(B)

을 이용해 음력 생일은 회원 음력 생일 날짜로 출력해 주는 것입니다.

 

물론 간단한 변환 코드들이 있지만 윤년,윤달 계산등 버그가 많고

회원 리스트에서 출력이 아니라 년,월을 기준으로 검색하여 생일인

회원들만 출력해야 하는데.. 회원들마다 일일이 쿼리 날려서 변환할순 없잖습니까?

회원 가입시에 몇 년치 변환데이터를 입력해 두기도 그렇고 -_-;

...해서 아래와 같은 방법을 썼습니다.

 

참 내 놓기 민망하지만 아래는 사용한 쿼리문입니다.

select a.userid, a.birth ,a.birth_cate ,b.lunar_date ,b.solar_date

from member as a , LunarToSolar as b

where
  (a.birth_cate=2
and b.lunar_date=concat(DATE_FORMAT(lunar_date,'%Y-'),DATE_FORMAT(a.birth,'%m-%d'))
and b.solar_date>='$yy-$mm-01' and b.solar_date<='$yy-$mm-31')
or (a.birth_cate=1
and b.solar_date=concat('$yy-',DATE_FORMAT(a.birth,'%m-%d'))
and b.solar_date>='$yy-$mm-01' and b.solar_date<='$yy-$mm-31')

 

$yy , $mm 은 2006 , 01 과 같이 검색한 년,월 입니다.

음력 12월 생인 사람은 또 다음해 1월 으로 넘어가고 하는것에서도 삽질 좀 했었습니다.

음력 , 양력 생일인 회원 모두 출력 해야 하기 때문에 OR 문을

쓸수 밖에 없었습니다.ㅠㅠ

 

결국 완성하긴 했지만 속도나 인덱스 같은 옵티마이져는 거의 포기했습니다.

아.. 그리고 지금은 회원 테이블A에 인덱스를 birth ,birth_cate 를 따로

잡아줬습니다.

그 전에는 쿼리를 날려보니 인덱스를 못 타더군요.

 

정말 DB는 알면 알수록 어렵군요.

아는 것도 없습니다만 ㅋㅋ

 

 

OR가 문제이군요.

 

f1이라는 컬럼이 INDEX일 때, f1 = 1 OR f1 = 2 이런 조건은 인덱스를 타지만, f1과 f2가 각각 인덱스일 때, f1 = 1 OR f2 = 2 이런 조건은 인덱스를 타지 못합니다. (다른 좋은 DB는 탈지 안 탈지는 모르겠군요)

 

암튼 설명이 길어지고 제가 자세히 설명을 드리기도 어려우니 각설하고 해결책만 말씀드리자면 OR을 없애기 위해 UNION을 쓰시면 됩니다.

 

즉,

 

SELECT

    a.userid, a.birth ,a.birth_cate ,b.lunar_date ,b.solar_date

FROM

    member as a , LunarToSolar as b

WHERE 
  (a.birth_cate=2
 and b.lunar_date=concat(DATE_FORMAT(lunar_date,'%Y-'),DATE_FORMAT(a.birth,'%m-%d'))
and b.solar_date>='$yy-$mm-01' and b.solar_date<='$yy-$mm-31')

 

UNION

 

SELECT

    a.userid, a.birth ,a.birth_cate ,b.lunar_date ,b.solar_date

FROM

    member as a , LunarToSolar as b

WHERE 

(a.birth_cate=1
and b.solar_date=concat('$yy-',DATE_FORMAT(a.birth,'%m-%d'))
and b.solar_date>='$yy-$mm-01' and b.solar_date<='$yy-$mm-31')

 

그리고 member table에 INDEX는 birth_cate만 잡아 주시면 됩니다.

(birth가 다른 쿼리에서 사용된다면, birth_cate와 birth_date를 각각의 INDEX로 잡아 주시구요)

 

UNION은 4.0이상 부터 지원되므로 3.23.x 버전이라면 위의 방법을 사용하지 못합니다.

 

이런 경우 회원 가입 시에 birth_cate를 이용하여 양/음력을 구분하시는 것처럼, 양력 생일, 음력 생일 컬럼을 만드시고, 양력으로 생일입력한 사람은 음력으로 변환해서 음력 컬럼에 저장하시고, 음력으로 생일 입력한 사람은 양력으로 변환해서 양력 컬럼에 저장하면, OR를 없앨 수 있을 것이라 생각됩니다.

 

암튼 중요한 것은 OR를 없애기 위해 로직을 좀 바꾸어야 하는데, 제가 잘못 이해하고 설명드린 것이라면 알려 주시면 감사하겠습니다~~

 

실행해 보신 결과도 올려주시며 좋구요.

 

답글 감사합니다.

 

알려주신 UNION 쿼리문의 결과값은 제가 얻고자 하는 결과값과 같습니다. mysql 버젼은 4.1.8 이구요.

더불어 INDEX indexname(field1,field2...) 와

INDEX indexname1(field1) , INDEX indexname2(field2) 의 차이를 조금은 알겠네요 ^^;

 

제가 사용했던 쿼리문의 DESC 결과 입니다.

 select_type | table | type  | possible_keys     | key        | key_len | ref  | rows |
-------------+-------+-------+-------------------+------------+---------+------+------+
 SIMPLE      | a     | ALL   | member_birth_cate | NULL       |    NULL | NULL |    6 |
 SIMPLE      | b     | range | solar_date        | solar_date |       3 | NULL |   33 |
-------------+-------+-------+-------------------+------------+---------+------+------+

 

알려주신 UNION 문의 DESC 결과 입니다.

-------------+------------+-------+-------------------+-------------------+---------+-------+------+
select_type  | table      | type  | possible_keys     | key               | key_len | ref   | rows |
-------------+------------+-------+-------------------+-------------------+---------+-------+------+
PRIMARY      | a          | ref   | member_birth_cate | member_birth_cate |       5 | const |    3 |
PRIMARY      | b          | range | solar_date        | solar_date        |       3 | NULL  |   33 |
UNION        | a          | ref   | member_birth_cate | member_birth_cate |       5 | const |    2 |
UNION        | b          | ref   | solar_date        | solar_date        |       3 | func  | 1539 |
UNION RESULT | <union1,2> | ALL   | NULL              | NULL              |    NULL | NULL  | NULL |
-------------+------------+-------+-------------------+-------------------+---------+-------+------+

 

어떤게 정답에 가까운지 무식한 저로써는 알길이 없네요 ㅎㅎ

그리고 말씀해 주신 필드를 만들어서 음/양력 변환 데이터를 미리 입력해 두는 방법은 문제가 조금 있습니다.

 

양력 생일은 사람은 문제가 없으나 음력 생일인 사람은 매년 생일이 바뀌기 때문에 회원 가입시에 적어도 5년치 이상의

생일 날짜를 입력해 둬야 할것 같았습니다.

 

저도 이 방법을 생각했었는데.. 

이 기능하나 가지고 회원 테이블 필드를 몇 개씩 늘리기도 그렇고.. 따로 테이블을 만들기도 애매하더군요.

 

사실 만들고 있는 프로그램을 몇년이나 쓸지 알수 없지만.. 5년이상을 쓸 경우에는..

따로 생일 날짜 컨버터?도 만들어야 하고 골치가 =,.=;

 

이 문제 덕분에 허정수님과 말씀도 나눌수 있어서 영광이였습니다.

모르는게 많아서 앞으로 종종 여쭤볼지도 모르겠네요~

항상 건강하세요~ 파이팅!

 

 

 

PS.  이번일은 참 돈도 안되면서 별껄 다 한다는 생각이 듭니다.

디자이너 없이 대충 table 로만 작성해주는데 사용하기 까다롭게 보인다네요.

틀을 죄다 고쳐줘야 할것 같습니다 ㅎㅎ

애초에 디자이너 붙여줬으면 두번일 하지 않아도 될것을 ㅠㅠ

빨리 이 암울한 세계를 벗어나고 싶지만... 뭘 해야 할지 ~ 아휴..

넋두리 였습니다  ^.^

 

-------------+------------+-------+-------------------+-------------------+---------+-------+------+
select_type  | table      | type  | possible_keys     | key               | key_len | ref   | rows |
-------------+------------+-------+-------------------+-------------------+---------+-------+------+
PRIMARY      | a          | ref   | member_birth_cate | member_birth_cate |       5 | const |    3 |
PRIMARY      | b          | range | solar_date        | solar_date        |       3 | NULL  |   33 |
UNION        | a          | ref   | member_birth_cate | member_birth_cate |       5 | const |    2 |
UNION        | b          | ref   | solar_date        | solar_date        |       3 | func  | 1539 |
UNION RESULT | <union1,2> | ALL   | NULL              | NULL              |    NULL | NULL  | NULL |
-------------+------------+-------+-------------------+-------------------+---------+-------+------+

 

빨간 색으로 나온 것 빼고는 다 정상인데요. 원래 explain의 결과가 실제 돌아가는 거랑 좀 차이가 있기도 하더군요.

 

그리고 member와 LunarToSolar의 레코드가 각각 몇 건 씩 되는지 알면 좋겠습니다.

 

레코드가 많을 때 두 방식의 속도 차기가 확연히 난다면 빠른 방법을 택하시면 되겠습니다~

회원 정보 테이블은 이제 가입을 하고 있는터라 거의 데이터가 없습니다.

그런데 음,양력 변환 테이블은 좀 많다 싶으네요.

 

양력으로 1899년 12월 1일 부터 2200년 11월 25일 까지 들어가 있네요.

총 109938 개 입니다 ^^;

 

쿼리시간은 둘다 0.00 초 대로 잘 나오네요

 

내친김에 회원 정보 테이블을 서브 쿼리로 하나 만들고 인덱스를

똑 같이 먹인 상태에서 10만건 테스트 입력 해봤습니다.

생일은 랜덤으로 1950 ~ 1980 년 사이에 넣구요.

 

SELECT 문으로 10번 정도 테스트 한 결과

평균 제가 사용했던 쿼리문은 2.8 초 정도 UNION 문은 3.1초 정도 걸리네요. 결과 데이터는 8500건 정도구요.

 

DESC 결과 입니다.

+----+-------------+-------+-------+---------------+------------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+------+-------+-------------+
|  1 | SIMPLE      | b     | range | solar_date    | solar_date |       3 | NULL |    34 | Using where |
|  1 | SIMPLE      | a     | ALL   | births_cate   | NULL       |    NULL | NULL | 99999 | Using where |
+----+-------------+-------+-------+---------------+------------+---------+------+-------+-------------+

 

UNION 결과

+----+--------------+------------+-------+---------------+------------+---------+------+-------+-------------+
| id | select_type  | table      | type  | possible_keys | key        | key_len | ref  | rows  | Extra       |
+----+--------------+------------+-------+---------------+------------+---------+------+-------+-------------+
|  1 | PRIMARY      | b          | range | solar_date    | solar_date |       3 | NULL |    34 | Using where |
|  1 | PRIMARY      | a          | ALL   | births_cate   | NULL       |    NULL | NULL | 75000 | Using where |
|  2 | UNION        | b          | range | solar_date    | solar_date |       3 | NULL |    34 | Using where |
|  2 | UNION        | a          | ALL   | births_cate   | NULL       |    NULL | NULL | 75000 | Using where |
|NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL       |    NULL | NULL |  NULL |             |
+----+--------------+------------+-------+---------------+------------+---------+------+-------+-------------+

 

 

 

 

births_cate의 분포도가 1,2 즉, 각각 50% 밖에 안 되다 보니, 인덱스를 못타군요.

 

member 테이블의 값을 제한해야 하는데 그게 안 되고 있습니다.

인덱스를 못타다 보니, UNION 해 봤자 Full scan을 두 번하다 보니

더 늦어졌구요.

 

저녁 식사 좀 하고 와서, 저희 쪽 테이블 가지고 테스트 좀 해보고 다시 올려 드리죠.

 

우리쪽에도 비슷한 테이블이 두개다 있거든요.^^

 

테스트 할때는 몰랐는데 지금 결과를 보니 이상하네요~

 

양력 생일 회원 데이터는 사실상 DATE 부분에서 YYYY 는 검색시에 필요가 없는데.. 음력 생일 회원, 양력 생일 회원 구분해서 두번 쿼리하는게 나으려나요?

출력시 정렬은 배열로 담아 두고 년,월,일 체크해가며 정렬 시키면 문제가 안될것도 같은데요~

 

DB의 세계는 참.. ㅠㅠ

음. 답변을 이정도에서 끝내려고, 테스트 좀 하고 올릴려고 했는데, 맥주를 좀 마시는 바람에 테스트 없이 개념적인 것만 설명 드릴께요.

 

지금 상황에서는 member 테이블의 범위를 줄여주는 조건이 필요한데, member에 걸린 births_cate만으로는 좀 힘듭니다.

 

jude 님께서 말씀하신데로 YYYY가 검색시에 불필요하고 m-d만 값을 DATE_FORMAT()함수로 가져오시므로, 제 생각에는 member 테이블에 회원이 입력한 생일 값을 YYYY와 m-d를 두 컬럼으로 나누어 저장하시는게 좋겠습니다.

 

그리고 m-d의 값이 저장된 컬럼에 인덱스를 거시구요.

 

내일 오후까지 별 이야기가 없으시면 낼 예제하나 보여드릴께요~

 

인덱스가 걸려있고, 인덱스가 사용될 수 있는 쿼리더라도 optimizer가 컬럼의 값 분포도를 보고 인덱스를 사용할지 안 할지 결정을 합니다.

 

member 테이블의 birth_cate는 값이 1, 혹은 2인데 잘 해 봐야 분포도가 50% 밖에 안 됩니다.

 

다라서 optimizer는 member 테이블의 인덱스를 타지 않고 full scan을 하게 되므로 속도가 느려질 수 밖에 없습니다.

 

그러나

 

LunarToSolar  테이블은 WHERE에서 10만 건 중 약 30일 정도의 범위가 주어지므로 인덱스를 잘 타게 됩니다.

 

따라서 key point는 member 테이블의 범위를 줄여주는 방법을 찾아야 하고, 그 방법으로 생일의 월-일만 저장하는 컬럼을 추가합니다.

 

그 컬럼을 md라고 하고, 인덱스는 (birth_date, md)를 복합키로 해서 주세요.

 

그러면 최종 쿼리는 다음과 같습니다.

 

컬럼 명이나 테이블 명은 제가 테스트 했던 그대로 올립니다.(수정하기가 귀찮아서^^)

 

SELECT
        a.user_id, a.birth_date_user,
        sun, lunar
FROM
        u_user as a , calan as b
WHERE
        (
                a.s_or_m= 'M'
                AND a.birth_md = DATE_FORMAT( b.lunar, '%m-%d')
                AND b.sun BETWEEN '2006-01-01' AND '2006-01-31'
        )

union
SELECT
        a.user_id, a.birth_date_user,
        sun, lunar
FROM
        u_user as a , calan as b
where
        (
                a.s_or_m = 'S'
                AND a.birth_md = DATE_FORMAT( b.sun, '%m-%d' )
                AND b.sun BETWEEN '2006-01-01' AND '2006-01-31'
        )

 

EXPLAIN 결과는 다음과 같습니다.

+----+--------------+------------+-------+-----------------+----------+---------+------+------+-------------+
| id | select_type  | table      | type  | possible_keys   | key      | key_len | ref  | rows | Extra       |
+----+--------------+------------+-------+-----------------+----------+---------+------+------+-------------+
|  1 | PRIMARY      | b          | range | sun2            | sun2     |       3 | NULL |   29 | Using where |
|  1 | PRIMARY      | a          | ref   | birth_md,s_or_m | birth_md |       5 | func |    2 | Using where |
|  2 | UNION        | b          | range | sun2            | sun2     |       3 | NULL |   29 | Using where |
|  2 | UNION        | a          | ref   | birth_md,s_or_m | birth_md |       5 | func |    2 | Using where |
|NULL | UNION RESULT | <union1,2> | ALL   | NULL            | NULL     |    NULL | NULL | NULL |             |
+----+--------------+------------+-------+-----------------+----------+---------+------+------+-------------+

 

참고로 UNION을 안 쓰고 OR를 했을 때의 explain 결과는 다음과 같습니다.

 

+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                           |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|  1 | SIMPLE      | b     | range | sun2          | sun2 |       3 | NULL |   29 | Using where                                     |
|  1 | SIMPLE      | a     | ALL   | birth_md      | NULL |    NULL | NULL | 2046 | Range checked for each record (index map: 0x40) |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+

 

full scan 하는게 보이시죠?

 

 

 

도움도 주시고 테스트 결과도 올려주셔서 너무 감사합니다 ㅎㅎ

 

 

너무 귀찮게 해드린것 같네요~ 흐...

 

 

하시는 일 잘 되시고~ 건강하세요~ 빠이팅!

 

아~ 주말도 멋지게 보내세요 ^^