WHERE 조건
비교: =, <, <=,>,>=, !=,
스코어(score) 비교: 숫자
Example
명령>
select * from zset.myzset1 where score = 30; opcode 결과>
0) key|score|value
1) myzset1|30.0|value30
명령>
select * from zset.myzset1 where score > 30; opcode 결과>
0) key|score|value
1) myzset1|40.0|value40
2) myzset1|50.0|value50
명령>
select * from zset.myzset1 where score < 30; opcode 결과>
0) key|score|value
1) myzset1|10.0|value10
2) myzset1|20.0|value20
명령>
select * from zset.myzset1 where score != 30; opcode 결과>
0) key|score|value
1) myzset1|10.0|value10
2) myzset1|20.0|value20
3) myzset1|40.0|value40
4) myzset1|50.0|value50
값(value) 비교: 문자
Example
명령>
select * from zset.myzset1 where value > 'value30'; opcode 결과>
0) key|score|value
1) myzset1|40.0|value40
2) myzset1|50.0|value50
명령>
select * from zset.myzset1 where value >= 'value30'; opcode 결과>
0) key|score|value
1) myzset1|30.0|value30
2) myzset1|40.0|value40
3) myzset1|50.0|value50
명령>
select * from zset.myzset1 where value != 'value30'; opcode 결과>
0) key|score|value
1) myzset1|10.0|value10
2) myzset1|20.0|value20
3) myzset1|40.0|value40
4) myzset1|50.0|value50
AND, OR
Example
명령>
select * from zset.myzset1 where score >= 20 and score < 40; opcode 결과>
0) key|score|value
1) myzset1|20.0|value20
2) myzset1|30.0|value30
명령>
select * from zset.myzset1 where score <= 20 or score > 40; opcode 결과>
score에 대한 or 는 조회 결과가 잘못 나올 수 있습니다. 사용하지 마십시오.
BETWEEN
Example
명령>
select * from zset.myzset1 where score between 20 and 40; opcode 결과>
0) key|score|value
1) myzset1|20.0|value20
2) myzset1|30.0|value30
3) myzset1|40.0|value40
명령>
select * from zset.myzset1 where score not between 20 and 40; opcode 결과>
0) key|score|value
1) myzset1|10.0|value10
2) myzset1|50.0|value50
GLOB
*(별표), ?(물음표)
대소문자를 구분한다.
Example
명령>
insert into zset values('myzset3', 10, 'USER-A01', 20, 'USER-A02', 30, 'USER-B01', 40, 'USER-B02',
50, 'USER-C01');
결과> 1 inserted
명령>
select * from zset.myzset3 where value glob 'USER-A*'; opcode 결과>
0) key|score|value
1) myzset3|10.0|USER-A01
2) myzset3|20.0|USER-A02
명령>
select * from zset.myzset3 where value glob '*01*'; opcode 결과>
0) key|score|value
1) myzset3|10.0|USER-A01
2) myzset3|30.0|USER-B01
3) myzset3|50.0|USER-C01
NOT GLOB
Example
명령>
select * from zset.myzset3 where value not glob 'USER-A*'; opcode 결과>
0) key|score|value
1) myzset3|30.0|USER-B01
2) myzset3|40.0|USER-B02
3) myzset3|50.0|USER-C01
명령>
select * from zset.myzset3 where value not glob '*01*'; opcode 결과>
0) key|score|value
1) myzset3|20.0|USER-A02
2) myzset3|40.0|USER-B02
LIKE
%(퍼센트), _(밑줄)
대소문자를 구분하지 않는다.
Example
명령>
select * from zset.myzset3 where value like 'user-a%'; opcode 결과>
0) key|score|value
1) myzset3|10.0|USER-A01
2) myzset3|20.0|USER-A02
명령>
select * from zset.myzset3 where value like '%01%'; opcode 결과>
0) key|score|value
1) myzset3|10.0|USER-A01
2) myzset3|30.0|USER-B01
3) myzset3|50.0|USER-C01
NOT LIKE
Example
명령>
select * from zset.myzset3 where value not like 'user-a%'; opcode 결과>
0) key|score|value
1) myzset3|30.0|USER-B01
2) myzset3|40.0|USER-B02
3) myzset3|50.0|USER-C01
명령>
select * from zset.myzset3 where value not like '%01%'; opcode 결과>
0) key|score|value
1) myzset3|20.0|USER-A02
2) myzset3|40.0|USER-B02
IN
Example
명령>
select key from zset.myzset* where key in ('myzset1','myzset2'); opcode 결과>
0) key
1) myzset1
2) myzset2
명령>
select * from zset.myzset1 where score in (10,30); opcode 결과>
0) key|score|value
1) myzset1|10.0|value10
2) myzset1|30.0|value30
명령>
select * from zset.myzset1 where value in ('value10','value30'); opcode 결과>
0) key|score|value
1) myzset1|10.0|value10
2) myzset1|30.0|value30
NOT IN
Example
명령>
select key from zset.myzset* where key not in ('myzset1','myzset2'); opcode 결과>
0) key
1) myzset3
명령>
select * from zset.myzset1 where score not in (10,30); opcode 결과>
0) key|score|value
1) myzset1|20.0|value20
2) myzset1|40.0|value40
3) myzset1|50.0|value50
명령>
select * from zset.myzset1 where value not in ('value10','value30'); opcode 결과>
0) key|score|value
1) myzset1|20.0|value20
2) myzset1|40.0|value40
3) myzset1|50.0|value50
GROUP BY
Example
명령>
select key,count(*) from zset.myzset* group by key; opcode
결과>
0) key|count(*)
1) myzset1|5
2) myzset2|10
3) myzset3|5
명령>
select key,min(score),max(score) from zset.myzset* group by key; opcode
결과>
0) key|min(score)|max(score)
1) myzset1|10.0|50.0
2) myzset2|12.3|123.4
3) myzset3|10.0|50.0
명령>
select key,min(value),max(value) from zset.myzset* group by key; opcode
결과>
0) key|min(value)|max(value)
1) myzset1|value10|value50
2) myzset2|mem10|mem90
3) myzset3|USER-A01|USER-C01
성능 Performance
ZSet 인덱스 기본 인덱스
- ZSet은 key + score로 기본 인덱스를 만듭니다.
- 아래 두 가지 경우는 조건에 맞을 때 추가로 만듭니다.
- 멤버 개수를 조회할 경우 card() 함수를 사용하세요.
select key, card(key) from zset.*; 속도가 빠릅니다. opcode
select key, count(*) from zset.* group by key; 속도가 느립니다. opcode
일반적인 경우: score 인덱스
- 일반적인 경우란 하나의 키에서 score의 값이 다른 경우입니다.
- Score 인덱스를 만드는 조건: (1) score가 다른 값일 것, (2) 하나의 키를 지정했을 것. zset.myzset1
- Score를 where 조건으로 사용하는 경우 인덱스를 사용해서 속도가 빠릅니다.
=, <=, <,>=,>, between, in
- Order by score (desc)인 경우에 이미 소트된 데이터를 조회하므로 속도가 빠릅니다.
- Min(score) 또는 max(score)를 사용하면 속도가 빠릅니다.
한 쿼리에 min(), max()를 같이 사용하면 full scan하므로 속도가 느립니다.
예를 들어 1천만개의 멤버가 있는 zset 키에서 min(score)를 사용하면 300us 걸리고 opcode,
min(score), max(score)를 같이 사용하면 802ms가 걸립니다. opcode
- 특별히 value =(equal) 조회인 경우 속도가 빠르다. 다른 조건(>,<,etc)일 때는 해당하지 않습니다.
Score가 모두 0인 경우: value 인덱스
- 하나의 키에서 Score가 모두 0인 경우 score 인덱스를 만들어도 소용이 없으므로 value 인덱스를 만듭니다.
- Value 인덱스를 만드는 조건: (1) score가 모두 0일 것, (2) 하나의 키를 지정했을 것. zset.myzset1
ZSet은 score가 모두 0이면 value로 소트됩니다.
- Value를 where 조건으로 사용하는 경우 인덱스를 사용해서 속도가 빠릅니다.
=, <=, <,>=,>, between, in
- Order by value (desc)인 경우에 이미 소트된 데이터를 조회하므로 속도가 빠릅니다.
- Min(value) 또는 max(value)를 사용하면 속도가 빠릅니다.
한 쿼리에 min(), max()를 같이 사용하면 full scan하므로 속도가 느립니다.
OPCODE
Test 데이터 입력
> :set timing on -> 쿼리 시간 표시 (us/ms/s)
> config set rsql-debug-opcode yes -> log: opcode 표시
> config set rsql-debug-sql yes -> log: sql 문장과 메모리 표시
SELECT
select * from zset.myzset*; 와일드(*) 문자 사용
select * from zset.myzset1; key 지정
select key from zset.myzset*; key만 조회
order by score/value asc/desc
select score,value from zset.myzset1 order by score;
select score,value from zset.myzset1 order by score desc;
select score,value from zset.myzset1 order by value;
select score,value from zset.myzset1 order by value desc;
select * from zset.myzset* order by score; 와일드(*) 문자 사용해서 여러 키 order by 조회
select * from zset.myzset* order by value;
group by
select key,max(score) from zset.myzset* group by key;
인덱스 사용하지 못한다. oneTable일 경우 인덱스 사용한다.
select key,max(value) from zset.myzset* group by key;
limit
select * from zset.myzset2 limit 5;
select * from zset.myzset2 order by score desc limit 5;
select * from zset.myzset2 order by value desc limit 5;
select * from zset.myzset2 limit 5 offset 3;
select * from zset.myzset2 limit 3, 5;
count
select count(*) from zset.myzset*;
값 개수를 하나씩 세지 않고 키의 멤버 수를 바로 구해서 계산한다. rsqlTabCount()
select count(*) from zset.*;
select count(key) from zset.myzset*;
select count(key) from zset.*;
select key, card(key) from zset.*;
키에 저장된 멤버 개수를 사용한다. 속도가 빠르다.
select key, count(*) from zset.* group by key;
멤버 개수를 일일이 센다. 속도가 느리다.
min(), max()
select key, min(score) from zset.myzset2; index scan, oneTable
select key, max(score) from zset.myzset2; index scan
select key, min(score), min(value) from zset.myzset2;
select key,min(score),max(score),min(value),max(value) from zset.myzset2;
select key,min(score),max(score),min(value),max(value) from zset.myzset* group by key;
sum(), avg() full scan
select sum(score) from zset.myzset2;
select avg(score) from zset.myzset2;
group_concat()
select key,group_concat(score) from zset.myzset1;
select key,group_concat(value) from zset.myzset1;
where - myzset1 scoreIndex
select * from zset.myzset1 where score = 30;
select * from zset.myzset1 where score> 30;
select * from zset.myzset1 where score>= 30;
select * from zset.myzset1 where score < 30;
select * from zset.myzset1 where score <= 30;
select * from zset.myzset1 where score != 30;
where in, not in
select * from zset.myzset1 where score in (30);
OP_OpenEphemeral 확인 필요
select * from zset.myzset1 where score in (30,50);
select * from zset.myzset1 where score not in (30);
select * from zset.myzset1 where score not in (30,50);
where between, not between
select * from zset.myzset1 where score between 20 and 40;
select * from zset.myzset1 where score not between 20 and 40;
where score and score
select * from zset.myzset1 where score = 20 and score = 40; index scan
select * from zset.myzset1 where score> 20 and score < 40;
select * from zset.myzset1 where score>= 20 and score <= 40;
select * from zset.myzset1 where score < 40 and score> 20;
select * from zset.myzset1 where score < 20 and score> 40;
where score and value
select * from zset.myzset1 where score = 20 and value = 'value20';
select * from zset.myzset1 where score = 20 and value = 'value30';
select * from zset.myzset1 where score = 30 and value = 'value25';
zadd myzset1 30 value25 30 value35 입력 후 테스트
select * from zset.myzset1 where score = 30 and value in ('value25');
select * from zset.myzset1 where score = 30 and value in ('value25','value35');
select * from zset.myzset1 where score = 30 and value between 'value25' and 'value30';
> zrem myzset1 value25 value35 입력했던 데이터를 삭제한다.
where score or score
select * from zset.myzset1 where score = 20 or score = 40;
같은 컬럼에 or는 equal만 가능 in ( ) 사용 권장.
같은 컬럼에 부등형(크다/작다) or는 첫번째 조건만 조회됨.
select * from zset.myzset1 where score>= 20 or score <= 40;
결과 오류, 사용하면 안됨.
결과 오류: 모두 나와야 함. OP_RowSetTest
select * from zset.myzset1 where score <= 40 or score>= 20;
결과 오류: OP_RowSetTest
select * from zset.myzset1 where score < 20 or score> 40;
결과 오류: OP_RowSetTest
where score or value -> table full scan
select * from zset.myzset1 where score = 20 or value = 'value10';
select * from zset.myzset1 where score <= 20 or value = 'value40';
select * from zset.myzset1 where score> 20 or value = 'value40';
select * from zset.myzset1 where score = 20 or value>= 'value40';
where 값(value) 비교: 문자
select * from zset.myzset1 where value = 'value30';
select * from zset.myzset1 where value> 'value30';
select * from zset.myzset1 where value>= 'value30';
select * from zset.myzset1 where value < 'value30';
select * from zset.myzset1 where value <= 'value30';
select * from zset.myzset1 where value != 'value30';
where value and value
select * from zset.myzset1 where value='value20' and value='value40';
select * from zset.myzset1 where value> 'value20' and value < 'value40';
select * from zset.myzset1 where value between 'value20' and 'value40';
select * from zset.myzset1 where value < 'value20' and value> 'value40';
where value or value
No valueIndex, table full scan -> 데이터 맞음.
valueIndex가 있으면 결과가 틀려진다. -> myzset3
select * from zset.myzset1 where value='value20' or value='value40';
select * from zset.myzset1 where value>= 'value20' or value <= 'value40';
select * from zset.myzset1 where value < 'value20' or value> 'value40';
where score or value
full table scan
select * from zset.myzset1 where score> 5 or value='value30';
select * from zset.myzset1 where score> 40 or value='value30';
select * from zset.myzset1 where score> 60 or value='value30';
select * from zset.myzset1 where score> 40 or value < 'value30';
where score and value
select * from zset.myzset1 where score> 5 and value='value30';
select * from zset.myzset1 where score> 40 and value='value30';
select * from zset.myzset1 where score> 60 and value='value30';
where score 비교
allScoreZero = 1, scoreIndex 없음, valueIndex 있음.
select * from zset.myzset3 where score = 0;
keyIndex 사용
select * from zset.myzset3 where score> 0;
select * from zset.myzset3 where score>= 0;
select * from zset.myzset3 where score < 0;
select * from zset.myzset3 where score <= 0;
select * from zset.myzset3 where score <= 10;
where score in ( )
select * from zset.myzset3 where score in (0);
keyIndex가 사용됨.
select * from zset.myzset3 where score in (10);
select * from zset.myzset3 where score not in (0);
select * from zset.myzset3 where score not in (10);
where score between
select * from zset.myzset3 where score between 0 and 0;
select * from zset.myzset3 where score between 10 and 20;
select * from zset.myzset3 where score between 'user02' and 'user04';
select * from zset.myzset3 where score not between 'user02' and 'user04';
여기는 OP_Lt: 에서 비교한다.
logic error가 좋을거 같은데...
where value 비교 -> index scan, value index 있음.
select * from zset.myzset3 where value = 'user03';
select * from zset.myzset3 where value = 'user025';
select * from zset.myzset3 where value> 'user03';
select * from zset.myzset3 where value>= 'user03';
select * from zset.myzset3 where value < 'user03';
select * from zset.myzset3 where value <= 'user03';
where value in ( )
select * from zset.myzset3 where value in ('user03');
select * from zset.myzset3 where value in ('user03','user05');
select * from zset.myzset3 where value not in ('user03');
select * from zset.myzset3 where value not in ('user03','user05');
where value between
select * from zset.myzset3 where value between 'user03' and 'user04';
select * from zset.myzset3 where value not between 'user03' and 'user04';
where value and value -> valueIndex 사용함.
select * from zset.myzset3 where value = 'user02' and value = 'user04';
select * from zset.myzset3 where value>= 'user02' and value <= 'user04';
select * from zset.myzset3 where value <= 'user02' and value>= 'user04'
select * from zset.myzset3 where value>= 'user02' and value = 'user04';
where value or value
select * from zset.myzset3 where value='user02' or value='user04';
select * from zset.myzset3 where value>= 'user02' or value <= 'user04'; -> 결과 오류: 모두 나와야 함. OP_RowSetTest
select * from zset.myzset3 where value < 'user02' or value> 'user04'; -> 결과 오류: OP_RowSetTest
where score and value
select * from zset.myzset3 where score = 0 and value='user03';
select * from zset.myzset3 where score = 10 and value='user03';
where score or value
select * from zset.myzset3 where score = 0 or value='user03';
select * from zset.myzset3 where score = 10 or value='user03';
select * from zset.myzset3 where value='user03' or score = 10;
select * from zset.myzset3 where value='user025' or score = 0;
select * from zset.myzset3 where value='user06' or score = 0;
성능 Performance 쿼리
select key,card(key) from zset.zset*;
zcard
select key,card(key) from zset.zsetc1000z;
select min(score) from zset.zseta10s;
select max(score) from zset.zseta10s;
select min(value) from zset.zseta10z;
select max(value) from zset.zseta10z;
zrange
select min(score) from zset.zsetb100s;
select max(score) from zset.zsetb100s;
select min(value) from zset.zsetb100z;
select max(value) from zset.zsetb100z;
select min(score) from zset.zsetc1000s;
select max(score) from zset.zsetc1000s;
select min(value) from zset.zsetc1000z;
select max(value) from zset.zsetc1000z;
성능 관련 중요 사항
하나의 키에서 min() 또는 max() 하나만 사용할 경우 인덱스를 사용해서 속도가 빠르다.
여러개 키를 지정하거나 min(), max()를 같이 사용하면 인덱스를 사용하지 못하고 full scan한다.
select min(score) from zset.zsetc1000s;
select max(score) from zset.zsetc1000s;
select min(score), max(score) from zset.zsetc1000s;
order by limit
select * from zset.zsetc1000s order by score limit 5000000,10;
select * from zset.zsetc1000s where score>= 417194852 order by score limit 10;
select * from zset.zsetc1000s where score>= 417194852 limit 10;
select * from zset.zsetc1000s where score>= 417194852 and score <= 417195248;
select * from zset.zsetc1000s where score between 417194852 and 417195248;
zrange zsetc1000s 5000000 5000009
select * from zset.zsetc1000s where score = 417194852;
zrangebyscore
select * from zset.zsetc1000s where value = 'ele_000810073187'; -> direct search
select * from zset.zsetc1000z order by value limit 5000000,10;
select * from zset.zsetc1000z where value = 'ele_000417416849';
zrangebylex zsetc1000z [ele_000417416849 [ele_000417416849
select * from zset.zsetc1000z where value>= 'ele_000417416849' order by value limit 10;
select * from zset.zsetc1000z where value>= 'ele_000417416849' limit 10;
select * from zset.zsetc1000z where value between 'ele_000417416849' and 'ele_000417417919';
select avg(score) from zset.zsetc1000s;
select * from zset.zsetc1000s where score = 417194991; -> index scan
select * from zset.zsetc1000s where value = 'ele_000675297817'; -> direct search
zrank zsetc1000s ele_000675297817
select * from zset.zsetc1000z where value = 'ele_000417417557'; -> index scan
Data
select * from zset.zsetc1000s where score in (417194991); -> index scan
select * from zset.zsetc1000s where score in (633, 417194991); -> index scan
select * from zset.zsetc1000s where score in (633, 417194991,999999351); -> index scan
select * from zset.zsetc1000s where score in (999999351,633, 417194991); -> index scan
select * from zset.zsetc1000z where value in ('ele_000417417557'); -> index scan
select * from zset.zsetc1000z where value in ('ele_000999999107','ele_000417417557'); -> index scan
select * from zset.zsetc1000z where value in ('ele_000999999107','ele_000417417557','ele_000000000601'); -> index scan
