SQL

SELECT List

SELECT로 List 데이터를 조회

SELECT로 List 데이터를 조회합니다.

테스트 데이터 입력

Example

명령>	rpush mylist1 value10 value30 value50 value20 value40
결과>	5
명령>	rpush mylist2 110 120 130 140 150
결과>	5
명령>	rpush mylist3 AAA BBB CCC DDD EEE
결과>	5

SQL Insert 문으로 입력

Example

명령>	insert into list values('mylist1', 'value40', 'value20', 'value50', 'value30', 'value10');
결과>	1 inserted
명령>	insert into list values('mylist2', '150', '140', '130', '120', '110');
결과>	1 inserted
명령>	insert into list values('mylist3', 'EEE', 'DDD', 'CCC', 'BBB', 'AAA');
결과>	1 inserted

SELECT

SELECT *
SELECT key,value
SELECT key
SELECT value

FROM

FROM list.*
FROM list.mylist*
FROM list.mylist1

SELECT * FROM LIST.MYLIST*

List의 키와 값(value)를 조회한다.

Example

명령>	select * from list.mylist*;
결과>	0) key\|value 1) mylist1\|value10 2) mylist1\|value30 3) mylist1\|value50 4) mylist1\|value20 5) mylist1\|value40 6) mylist2\|110 7) mylist2\|120 8) mylist2\|130 9) mylist2\|140 10) mylist2\|150 11) mylist3\|AAA 12) mylist3\|BBB 13) mylist3\|CCC 14) mylist3\|DDD 15) mylist3\|EEE
명령>	select * from list.mylist1;
결과>	0) key\|value 1) mylist1\|value10 2) mylist1\|value30 3) mylist1\|value50 4) mylist1\|value20 5) mylist1\|value40
명령>	select key from list.mylist*;
결과>	0) key 1) mylist1 2) mylist2 3) mylist3

ORDER BY

정렬(sort)해서 보여준다.

Example

명령>	select value from list.mylist1 order by value;
결과>	0) value 1) value10 2) value20 3) value30 4) value40 5) value50
명령>	select value from list.mylist1 order by value desc;
결과>	0) value 1) value50 2) value40 3) value30 4) value20 5) value10

정렬(sort) 순서: 숫자 < 문자
레디스에서는 숫자와 문자를 같이 소트할 수 있습니다. 숫자는 문자보다 작은 것으로 취급합니다.

Example

명령>	select * from list.mylist* order by value;
결과>	0) key\|value 1) mylist2\|110 2) mylist2\|120 3) mylist2\|130 4) mylist2\|140 5) mylist2\|150 6) mylist3\|AAA 7) mylist3\|BBB 8) mylist3\|CCC 9) mylist3\|DDD 10) mylist3\|EEE 11) mylist1\|value10 12) mylist1\|value20 13) mylist1\|value30 14) mylist1\|value40 15) mylist1\|value50

LIMIT

출력 행 수를 제한한다.

Example

명령>	select * from list.mylist* order by value limit 5;
결과>	0) key\|value 1) mylist2\|110 2) mylist2\|120 3) mylist2\|130 4) mylist2\|140 5) mylist2\|150
명령>	select * from list.mylist* order by value desc limit 5;
결과>	0) key\|value 1) mylist1\|value50 2) mylist1\|value40 3) mylist1\|value30 4) mylist1\|value20 5) mylist1\|value10

LIMIT OFFSET

offset을 사용하면 지정한 숫자만큼 건너뛰고 출력합니다. 3을 지정하면 3행을 건너뛰고 4행부터 출력합니다.
offset을 생략하고 컴마(,)를 사용할 경우 offset 숫자를 앞에 위치시킵니다.

Example

명령>	select * from list.mylist* order by value limit 5 offset 3; --> 3행 건너 5행 출력
결과>	0) key\|value 1) mylist2\|140 2) mylist2\|150 3) mylist3\|AAA 4) mylist3\|BBB 5) mylist3\|CCC
명령>	select * from list.mylist* order by value limit 3, 5; --> 3행 건너 5행 출력
결과>	0) key\|value 1) mylist2\|140 2) mylist2\|150 3) mylist3\|AAA 4) mylist3\|BBB 5) mylist3\|CCC

FUNCTIONS

COUNT(), LEN()

개수를 조회한다.
len()는 list, set, zset, hash, stream에서 값(value)의 개수를 조회하는데 사용된다.
아래 세 번째 예처럼 len()를 사용하는 것이 네 번째 예처럼 group by key를 사용하는 것보다 훨씬 속도가 빠르다.

Example

명령>	select *count()** from list.mylist*; --> 이 경우 값(value)의 개수를 일일이 세는 것이 아니고 각 키에서 값의 개수를 가져와서 더한다. 즉, 각 키에서 llen 명령을 실행해서 개수를 가져오는 것과 같다. 그러므로 속도가 빠르다.
결과>	0) count(*) 1) 15
명령>	select count(key) from list.mylist*;
결과>	0) count(key) 1) 3
명령>	select key, len(key) from list.mylist*;
결과>	0) key\|len(key) 1) mylist1\|5 2) mylist2\|5 3) mylist3\|5
명령>	select *key, count()** from list.mylist* group by key;
결과>	0) key\|count(*) 1) mylist1\|5 2) mylist2\|5 3) mylist3\|5

MAX(), MIN()

최댓값, 최솟값을 조회한다.
레디스에서는 문자와 숫자를 같은 선상에 놓고 비교할 수 있습니다.
숫자는 문자보다 작은 것으로 취급해서 min, max를 계산합니다.

Example

명령>	select min(value),max(value) from list.mylist1;
결과>	0) min(value)\|max(value) 1) value10\|value50
명령>	select min(value),max(value) from list.mylist*;
결과>	0) min(value)\|max(value) 1) 110\|value50
명령>	select key,min(value),max(value) from list.mylist* group by key;
결과>	0) key\|min(value)\|max(value) 1) mylist1\|value10\|value50 2) mylist2\|110\|150 3) mylist3\|AAA\|EEE

MAX(value1, value2): 둘 중 큰 값 출력
MIN(value1, value2): 둘 중 작은 값 출력

Example

명령>	select max('A','B'); --> from 절 없이도 사용 가능합니다.
결과>	0) max('A','B') 1) B
명령>	select max(135,value) from list.mylist2;
결과>	0) max(135,value) 1) 135 2) 135 3) 135 4) 140 5) 150
명령>	select min('CCC',value) from list.mylist3;
결과>	0) min('CCC',value) 1) AAA 2) BBB 3) CCC 4) CCC 5) CCC

숫자 함수

SUM(), AVG()

합계, 평균을 구한다.

Example

명령>	select sum(value) from list.mylist2;
결과>	0) sum(value) 1) 650
명령>	select avg(value) from list.mylist2;
결과>	0) avg(value) 1) 130.0

문자, 숫자가 섞여 있을 경우에는 숫자만 계산합니다.

Example

명령>	select sum(value) from list.mylist*
결과>	0) sum(value) 1) 650

기타 함수

group_concat

값(value)를 묶어서 출력합니다. 구분자를 사용할 수 있습니다.

Example

명령>	select key,group_concat(value) from list.mylist1;
결과>	0) key\|group_concat(value) 1) mylist1\|value10,value30,value50,value20,value40
명령>	select key,group_concat(value,'') from list.mylist1;
결과>	0) key\|group_concat(value) 1) mylist1\|value10value30value50value20value40
명령>	select key,group_concat(value,' and ') from list.mylist1;
결과>	0) key\|group_concat(value) 1) mylist1\|value10 and value30 and value50 and value20 and value40

WHERE 조건

비교: =, <, <=, >, >=, !=, <>

값(value) 비교: 숫자

Example

명령>	select * from list.mylist2 where value = 130;
결과>	0) key\|value 1) mylist2\|130
명령>	select * from list.mylist2 where value > 130;
결과>	0) key\|value 1) mylist2\|140 2) mylist2\|150
명령>	select * from list.mylist2 where value < 130;
결과>	0) key\|value 1) mylist2\|110 2) mylist2\|120
명령>	select * from list.mylist2 where value != 130;
결과>	0) key\|value 1) mylist2\|110 2) mylist2\|120 3) mylist2\|140 4) mylist2\|150

값(value) 비교: 문자

Example

명령>	select * from list.mylist1 where value > 'value30';
결과>	0) key\|value 1) mylist1\|value50 2) mylist1\|value40
명령>	select * from list.mylist1 where value >= 'value30';
결과>	0) key\|value 1) mylist1\|value30 2) mylist1\|value50 3) mylist1\|value40
명령>	select * from list.mylist1 where value != 'value30';
결과>	0) key\|value 1) mylist1\|value10 2) mylist1\|value50 3) mylist1\|value20 4) mylist1\|value40

AND, OR

Example

명령>	select * from list.mylist2 where value >= 120 and value < 140;
결과>	0) key\|value 1) mylist2\|120 2) mylist2\|130
명령>	select * from list.mylist2 where value <= 120 or value > 140;
결과>	0) key\|value 1) mylist2\|110 2) mylist2\|120 3) mylist2\|150

BETWEEN

Example

명령>	select * from list.mylist2 where value between 120 and 140;
결과>	0) key\|value 1) mylist2\|120 2) mylist2\|130 3) mylist2\|140
명령>	select * from list.mylist2 where value not between 120 and 140;
결과>	0) key\|value 1) mylist2\|110 2) mylist2\|150

GLOB

*(별표), ?(물음표)
대소문자를 구분한다.

[AB]: A, B 문자가 들어간 문자열를 검색할 경우
[A-C]: A에서 C까지(A,B,C) 문자가 들어간 문자열을 검색할 경우
[A-Z]: 알파벳 대문자가 들어가 문자열을 검색할 경우
[a-z]: 알파벳 소문자가 들어가 문자열을 검색할 경우
[A-Za-z]: 알파벳이 들어가 문자열을 검색할 경우
[A-z]: 이렇게 지정하면 Z와 a 사이에 [,\,],^,_,' 가 있어서 이런 특수문자가 포함됩니다.

Example

명령>	insert into list values('mylist4', 'USER-C01', 'USER-B02', 'USER-B01', 'USER-A02', 'USER-A01');
결과>	1 inserted
명령>	select * from list.mylist4 where value *glob 'USER-A'**;
결과>	0) key\|value 1) mylist4\|USER-A01 2) mylist4\|USER-A02
명령>	select * from list.mylist4 where value *glob '01'*;
결과>	0) key\|value 1) mylist4\|USER-A01 2) mylist4\|USER-B01 3) mylist4\|USER-C01
명령>	select * from list.mylist4 where value *glob 'USER-[AC]'**;
결과>	0) key\|value 1) mylist4\|USER-A01 2) mylist4\|USER-A02 3) mylist4\|USER-C01

NOT GLOB

Example

명령>	select * from list.mylist4 where value *not glob 'USER-A'**;
결과>	0) key\|value 1) mylist4\|USER-B01 2) mylist4\|USER-B02 3) mylist4\|USER-C01
명령>	select * from list.mylist4 where value *not glob '01'*;
결과>	0) key\|value 1) mylist4\|USER-A02 2) mylist4\|USER-B02
명령>	select * from list.mylist4 where value *not glob 'USER-[AC]'**;
결과>	0) key\|value 1) mylist4\|USER-B01 2) mylist4\|USER-B02

LIKE

%(퍼센트), _(밑줄)
대소문자를 구분하지 않는다.
가능하면 LIKE를 사용하지 말고 GLOB를 사용하세요.
LIKE는 대소문자를 구분하지 않기 때문에 검색 범위가 넓어서 GLOB보다 속도가 느립니다.

Example

명령>	select * from list.mylist4 where value like 'user-a%';
결과>	0) key\|value 1) mylist4\|USER-A01 2) mylist4\|USER-A02
명령>	select * from list.mylist4 where value like '%01%';
결과>	0) key\|value 1) mylist4\|USER-A01 2) mylist4\|USER-B01 3) mylist4\|USER-C01

NOT LIKE

Example

명령>	select * from list.mylist4 where value not like 'user-a%';
결과>	0) key\|value 1) mylist4\|USER-B01 2) mylist4\|USER-B02 3) mylist4\|USER-C01
명령>	select * from list.mylist4 where value not like '%01%';
결과>	0) key\|value 1) mylist4\|USER-A02 2) mylist4\|USER-B02

IN

Example

명령>	select key from list.mylist* where key in ('mylist1','mylist2')
결과>	0) key 1) mylist1 2) mylist2
명령>	select * from list.mylist3 where value in ('AAA','CCC');
결과>	0) key\|value 1) mylist3\|AAA 2) mylist3\|CCC
명령>	select * from list.mylist2 where value in (120,140);
결과>	0) key\|value 1) mylist2\|120 2) mylist2\|140

NOT IN

Example

명령>	select key from list.mylist* where key not in ('mylist1','mylist2');
결과>	0) key\|value 1) mylist3 2) mylist4
명령>	select * from list.mylist3 where value not in ('AAA','CCC');
결과>	0) key\|value 1) mylist3\|BBB 2) mylist3\|DDD 3) mylist3\|EEE
명령>	select * from list.mylist2 where value not in (120,140);
결과>	0) key\|value 1) mylist2\|110 2) mylist2\|130 3) mylist2\|150

GROUP BY

Example

명령>	select key,*count()** from list.mylist* group by key; --> 이것 보다는 select key, len(key) from list.mylist*; 를 사용하세요. 속도가 훨씩 빠릅니다.
결과>	0) key\|count(*) 1) mylist1\|5 2) mylist2\|5 3) mylist3\|5 4) mylist4\|5
명령>	select key,min(value),max(value) from list.mylist* group by key;
결과>	0) key\|min(value)\|max(value) 1) mylist1\|value10\|value50 2) mylist2\|110\|150 3) mylist3\|AAA\|EEE 4) mylist4\|USER-A01\|USER-C01

성능 Performance

테스트 데이터 입력

redis-benchmark를 이용해서 총 30만개 키를 입력합니다.
listA 10만개, listB 10만개, listC 10만개.
src/redis-benchmark -c 5 -n 100000 -r 1000000000 lpush listA ele___rand_int__
src/redis-benchmark -c 5 -n 100000 -r 1000000000 lpush listB ele___rand_int__
src/redis-benchmark -c 5 -n 100000 -r 1000000000 lpush listC ele___rand_int__

쿼리 성능

--> List에서 쿼리는 list의 왼쪽에서 오른쪽으로 값을 조회합니다.
select * from list.mylist1; -> (253us) full value scan opcode
select value from list.mylist1 order by value; -> (289us) full value scan, sort opcode
select count(*) from list.list*; -> count: 300000 (261us) key scan, get count opcode
select key, len(key) from list.list*; -> (277us) key scan, get count opcode
select key, count(*) from list.list* group by key; -> (18ms) full value scan opcode
--> group by count는 속도가 느립니다. len(key)를 사용하세요.
select value from list.listA order by value limit 50000, 10; -> (83ms) full value scan, sort opcode
select * from list.listA where value = 'ele_000460668562'; -> (12ms) full value scan opcode
select * from list.listA where value in ('ele_000460668562'); -> (11ms) full value scan opcode
--> Value에 대한 비교는 full value scan하기 때문에 속도가 느리다.

OPCODE

select * from list.mylist1; -> full value scan

0) key|value 1) mylist1|value10 2) mylist1|value30 3) mylist1|value50 4) mylist1|value20 5) mylist1|value40 (253us) # ------------------------------------------------------- # select * from list.mylist1; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:1,p2:2,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:2,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:8,p3:0,p4type:NOTUSED # sql:"select * from list.mylist1;" -> 5rows # used_memory:1255600, total_diff:24

select value from list.mylist1 order by value; -> full value scan, sort

0) value 1) value10 2) value20 3) value30 4) value40 5) value50 (289us) # ------------------------------------------------------- # select value from list.mylist1 order by value; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 3 OP_SorterNext, p1:1,p2:10,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:1,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:0,p3:2,p4type:NOTUSED # opcode:125 OP_SorterData, p1:1,p2:4,p3:2,p4type:NOTUSED # opcode: 35 OP_SorterSort, p1:1,p2:-5,p3:0,p4type:NOTUSED # opcode:114 OP_OpenPseudo, p1:2,p2:4,p3:3,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:4,p3:0,p4type:NOTUSED # opcode:130 OP_SorterInsert, p1:1,p2:3,p3:1,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:1,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode:111 OP_SorterOpen, p1:1,p2:3,p3:0,p4type:KEYINFO # opcode: 62 OP_Init, p1:0,p2:15,p3:0,p4type:NOTUSED # sql:"select value from list.mylist1 order by value;" -> 5rows # used_memory:1255848, total_diff:24

select count() from list.list; -> key scan, get count

0) count(*) 1) 300000 (261us) # ------------------------------------------------------- # select count(*) from list.list*; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:1,p3:0,p4type:NOTUSED # opcode: 77 OP_Copy, p1:1,p2:2,p3:0,p4type:NOTUSED # opcode:116 OP_Close, p1:1,p2:0,p3:0,p4type:NOTUSED # opcode: 92 OP_Count, p1:1,p2:1,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:1,p2:1,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:7,p3:0,p4type:NOTUSED # sql:"select count(*) from list.list*;" -> 1rows # used_memory:7294944, total_diff:24

select key, len(key) from list.list*; -> key scan, get count

0) key|len(key) 1) listA|100000 2) listB|100000 3) listC|100000 (277us) # ------------------------------------------------------- # select key, len(key) from list.list*; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:1,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:1,p2:2,p3:0,p4type:NOTUSED # opcode: 64 OP_Function, p1:0,p2:3,p3:2,p4type:FUNCCTX # opcode: 89 OP_Column, p1:1,p2:0,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:1,p2:0,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:1,p2:-3,p3:1,p4type:INT32 # opcode: 96 OP_OpenRead, p1:1,p2:1,p3:0,p4type:INDEX # opcode: 62 OP_Init, p1:0,p2:9,p3:0,p4type:NOTUSED # sql:"select key, len(key) from list.list*;" -> 3rows # used_memory:7294920, total_diff:24

select key, count() from list.list group by key; -> full value scan

0) key|count(*) 1) listA|100000 2) listB|100000 3) listC|100000 (18ms) # ------------------------------------------------------- # select key, count(*) from list.list* group by key; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 65 OP_Return, p1:6,p2:0,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 72 OP_Null, p1:0,p2:1,p3:2,p4type:NOTUSED # opcode: 65 OP_Return, p1:5,p2:0,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:9,p2:2,p3:0,p4type:NOTUSED # opcode: 77 OP_Copy, p1:1,p2:9,p3:1,p4type:NOTUSED # opcode:157 OP_AggFinal, p1:2,p2:0,p3:0,p4type:FUNCDEF # opcode: 65 OP_Return, p1:5,p2:0,p3:0,p4type:NOTUSED # opcode: 48 OP_IfPos, p1:3,p2:25,p3:0,p4type:NOTUSED # opcode: 65 OP_Return, p1:5,p2:0,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:1,p2:4,p3:0,p4type:NOTUSED # opcode: 11 OP_Goto, p1:0,p2:-2,p3:0,p4type:NOTUSED # opcode: 12 OP_Gosub, p1:5,p2:-3,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:2,p2:7,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:1,p2:3,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:0,p3:1,p4type:NOTUSED # opcode: 18 OP_If, p1:3,p2:17,p3:0,p4type:NOTUSED # opcode:154 OP_AggStep, p1:0,p2:0,p3:2,p4type:FUNCDEF # opcode: 12 OP_Gosub, p1:6,p2:-4,p3:0,p4type:NOTUSED # opcode: 48 OP_IfPos, p1:4,p2:-2,p3:0,p4type:NOTUSED # opcode: 12 OP_Gosub, p1:5,p2:-3,p3:0,p4type:NOTUSED # opcode: 76 OP_Move, p1:8,p2:7,p3:1,p4type:NOTUSED # opcode: 16 OP_Jump, p1:10,p2:14,p3:10,p4type:NOTUSED # opcode: 86 OP_Compare, p1:7,p2:8,p3:1,p4type:KEYINFO # opcode: 89 OP_Column, p1:2,p2:0,p3:8,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:2,p2:-6,p3:9,p4type:INT32 # opcode: 96 OP_OpenRead, p1:2,p2:1,p3:0,p4type:INDEX # opcode: 12 OP_Gosub, p1:6,p2:-4,p3:0,p4type:NOTUSED # opcode: 72 OP_Null, p1:0,p2:7,p3:7,p4type:NOTUSED # opcode: 69 OP_Integer, p1:0,p2:4,p3:0,p4type:NOTUSED # opcode:173 OP_Noop, p1:1,p2:1,p3:0,p4type:NOTUSED # opcode: 62 OP_Init, p1:0,p2:33,p3:0,p4type:NOTUSED # sql:"select key, count(*) from list.list* group by key;" -> 3rows # used_memory:7294904, total_diff:24

select value from list.listA order by value limit 50000, 10; -> full value scan, sort

0) value 1) ele_000460668562 2) ele_000460693454 3) ele_000460703242 4) ele_000460729231 5) ele_000460737472 6) ele_000460743031 7) ele_000460747505 8) ele_000460758744 9) ele_000460823365 10) ele_000460826146 (83ms) # ------------------------------------------------------- # select value from list.listA order by value limit 50000, 10; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:1,p2:18,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:6,p2:1,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:1,p2:0,p3:6,p4type:NOTUSED # opcode: 48 OP_IfPos, p1:2,p2:-6,p3:1,p4type:NOTUSED # opcode: 36 OP_Sort, p1:1,p2:-5,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:8,p3:0,p4type:NOTUSED # opcode:131 OP_IdxInsert, p1:1,p2:7,p3:4,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:4,p2:2,p3:7,p4type:NOTUSED # opcode:122 OP_Delete, p1:1,p2:0,p3:0,p4type:NOTUSED # opcode: 38 OP_IdxLE, p1:1,p2:-4,p3:4,p4type:INT32 # opcode: 33 OP_Last, p1:1,p2:0,p3:0,p4type:NOTUSED # opcode: 49 OP_IfNotZero, p1:3,p2:14,p3:0,p4type:NOTUSED # opcode:119 OP_Sequence, p1:1,p2:5,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:4,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode:152 OP_OffsetLimit, p1:1,p2:3,p3:2,p4type:NOTUSED # opcode: 15 OP_MustBeInt, p1:2,p2:0,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:50000,p2:2,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:10,p2:1,p3:0,p4type:NOTUSED # opcode:100 OP_OpenEphemeral, p1:1,p2:3,p3:0,p4type:KEYINFO # opcode: 62 OP_Init, p1:0,p2:23,p3:0,p4type:NOTUSED # sql:"select value from list.listA order by value limit 50000, 10;" -> 10rows # used_memory:7296464, total_diff:24

select * from list.listA where value = 'ele_000460668562'; -> full value scan

0) key|value 1) listA|ele_000460668562 (12ms) # ------------------------------------------------------- # select * from list.listA where value = 'ele_000460668562'; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode:115 OP_String8, p1:0,p2:2,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:3,p2:2,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:3,p4type:NOTUSED # opcode: 52 OP_Ne, p1:2,p2:-4,p3:1,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:1,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:10,p3:0,p4type:NOTUSED # sql:"select * from list.listA where value = 'ele_000460668562';" -> 1rows # used_memory:7357888, total_diff:24

select * from list.listA where value in ('ele_000460668562'); -> full value scan

0) key|value 1) listA|ele_000460668562 (11ms) # ------------------------------------------------------- # select * from list.listA where value in ('ele_000460668562'); # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode:115 OP_String8, p1:0,p2:2,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:3,p2:2,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:3,p4type:NOTUSED # opcode: 52 OP_Ne, p1:1,p2:-4,p3:2,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:1,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:10,p3:0,p4type:NOTUSED # sql:"select * from list.listA where value in ('ele_000460668562');" -> 1rows # used_memory:7359928, total_diff:24

<< Select String	Select List	Select Set >>

Email 답글이 올라오면 이메일로 알려드리겠습니다.

SELECT List

SELECT로 List 데이터를 조회

테스트 데이터 입력

Example

SQL Insert 문으로 입력

Example

SELECT

FROM

SELECT * FROM LIST.MYLIST*

Example

ORDER BY

Example

Example

LIMIT

Example

LIMIT OFFSET

Example

FUNCTIONS

COUNT(), LEN()

Example

MAX(), MIN()

Example

Example

숫자 함수

SUM(), AVG()

Example

Example

기타 함수

group_concat

Example

WHERE 조건

비교: =, <, <=, >, >=, !=, <>

Example

Example

AND, OR

Example

BETWEEN

Example

GLOB

Example

Example

LIKE

Example

Example

IN

Example

NOT IN

Example

GROUP BY

Example

성능 Performance

테스트 데이터 입력

쿼리 성능

OPCODE

select * from list.mylist1; -> full value scan

select value from list.mylist1 order by value; -> full value scan, sort

select count(*) from list.list*; -> key scan, get count

select key, len(key) from list.list*; -> key scan, get count

select key, count(*) from list.list* group by key; -> full value scan

select value from list.listA order by value limit 50000, 10; -> full value scan, sort

select * from list.listA where value = 'ele_000460668562'; -> full value scan

select * from list.listA where value in ('ele_000460668562'); -> full value scan

select count() from list.list; -> key scan, get count

select key, count() from list.list group by key; -> full value scan