원칙1.서브 테스크를 없애거나 줄여라
Slow Query Basics: Optimize Data Access
보통 데이터 량이 문제다
해결
1.필요한것보다 더 많은 양의 데이터 (로우, 컬럼)을 요청하지 않는가?
2.mysql 서버가 필요한 양보다 더 많은 양을 분석하고 있지 않은가?
더 많은 데이터를 요청하는 예
1.Fetching more rows than needed
많은 app개발자가 MySQL에서 착각하는 부분은
로우 100줄 호출 후 10줄후 close 해버리면 10줄만 온다고 생각하는건대
Mysql은 실제로 100줄 다보낸다
그러니 LIMIT 써라
ex) top page 10
2.Fetching all columns from a multitable join
SELECT * FROM A, B
* 쓰지 말자!
꼭 필요한 컬럼만 호출하자
3.Fetching the same data repeatedly
같은 데이터 디비에 여러번 요청 하지 말자
mysql 서버가 너무 많은 양을 분석할때
query 정확할때
측정 기준
반응시간
조사된 데이터
리턴된 데이터
Response time
service time + queue time
service time
실제로 실행 된 시간
queue time
기다린 시간
ex)I/O, lock등
quick upper-bound estimate (QUBE)
플랜과 인덱스의 데이터 량을 보고 몇번의 io 가 필요할지
측정 한다 그래서 이 시간보다 빠른가 비교..
자세한 내용 책참조 하란다..
Relational Database Index Design and the Optimizers (Wiley).
In a nutshell:
examine the query execution plan and the indexes involved, determine how many
sequential and random I/O operations might be required, and multiply these by the
time it takes your hardware to perform them. Add it all up and you have a yardstick to
judge whether a query is slower than it could or should be
Rows examined and rows returned
몇개가 조사되고 몇개가 리턴 되었느냐인대
별 쓸모 없다 테이블 조인 하게 되면 1:X
X가 엄청 크다면? 필요가 있는가..
Rows examined and access types
쿼리 코스트를 생각 할때 테이블에서 row 하나 읽는 cost 를 생각해봐라
access 방법은 EXPLAIN의 type 에 나타남
읽는 양 많음 --> 적음
full table > index scan > range scan > unique index look up > constants
MySql이 where 를 적용 하는 방법
1.검색조건을 index look up 에 사용하여 적용되지 않는 로우를 없앰
*storage engine 레벨에서 적용됨
2.using index
storage engine에서는 인덱스를 읽고
server에서 필요없는 로우 없앰
3.using where
테이블에서 로우들을 읽고
server에서 필요 없는 로우를 없앰
*result row 에 비해 examined 로우가 정말 많다면?
1.커버링 인덱스를 사용하게 하라(테이블 접근을 줄여라)
2.summary 테이블을 사용해라
3.쿼리가 최적의 플랜으로 실행 되게 재작성하라
쿼리 재작성 방법
1.동일 결과를 같고 더 빠르게 할수도 있다
2.성능의 이득이 있다면 결과값을 변경 할수도 있다
2.어플리케이션 코드를 변경 해야 할 수도 있다.
복잡한 쿼리 vs 심플 쿼리
마이 sql은 일반 서버 에서 초당 100,000건의 간단한 쿼리를 쉽게 처리한다.
gigabit network 는 초당 2000건이상을 처리한다.
즉 네트워크 비용이 예전 만큼 중요하지 않다(?.. 중요한탠대..)
물런 지금도 가능하면 네트워크 비용을 줄이는게 맞다
하지만 불가능 하다면 싱글 쿼리를 여러개의 심플한 쿼리로 변경하는걸 겁내지말라
(그정도 효능이 있다면)
데이터를 잘게 쪼개 처리하기
한번에 너무 많은 데이터를 처리하면 문제가 생길 수 있다.
DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);
이렇게 말고 아래 처럼 처리하자
rows_affected = 0
do {
rows_affected = do_query(
"DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH)
LIMIT 10000")
} while rows_affected > 0
Join Decomposition
성능을 위해 테이블에 싱글 쿼리를 날리고
조인을 어플리케이션 서버에서 하는 방법도 있다
이유
1.어플리케이션에서 캐쉬를 사용할 경우 큰 성능 효과를 기대할수있다
2.분리해서 사용할경우 lock 경쟁이 적어 질수 있다
3.DB를 확장할때 유리하다
4.MySql이 optimizer 를 사용할때 성능을 최적화 할 수 있다
5.디비에서 한번만 읽어오기 때문에 디비의 메모리 사용량과 네트워크 트래픽을 줄일수 있다
6.다른 확장 프로그램을 받는다면 nested loop join 이 아니라 hash join 으로 변경 할수 있다
MySQL이 쿼리를 처리하는 순서
1.클라이언트가 server에 sql문장을 쏜다
2.서버는 query cache 를 확인하고 존재한다면 cache에서 데이터 찾아 리턴
3.parse -> prepocess -> optimizes(sql to query execution plan)
4.query execution engine 이 플랜을 실행 (storage engine 의 API 콜)
5.결과를 클라이언트에 리턴
Mysql Clinet/Server Protocol
half-duplex 언제든 받거나 보내거나 둘중 하나를 할수있다 단 2개 동시에는 안된다.
문제
1.flow controll 할수없다
즉 클라에서 최초 몇줄이 필요하다고 해도 서버가 보내주는 모든 데이터를 받아야 한다.
why?
클라에서 서버로 쿼리를 던지는 순간 서버가 공을 가지게 된다.
그러면 클라는 서버에게 명령 할수 없다.
(통신 방법이 half-duplex니까)
2.서버가 클라로 데이터를 던질때 전체 데이터가 클라에 도착할때까지
쿼리에 필요했던 락과 리소스를 풀수 없다
(the query state is sending data)
3.클라 라이블러리를 설정하여
buffer를 사용할지 오는대로 처리할지 지정 할수 있지만(default buffer)
오는대로 처리하게 설정하면 server 와 clinet의 interact 가 끝날때가지 자원과 리솟스를
풀수 없다.
쿼리상태(query states) 값 설명
SHOW FULL PROCESSLIST .command 컬럼
Sleep
thread 가 클라에서 새로운 쿼리가 올때까지 대기
Query
쿼리를 실행 중이거나 클라한태 돌려주는중(server is sending data to client)
Locked
서버 level에서 table lock이 걸려서 thread가 대기하는 상태
(storage engine에서 직접 lock 을 거는 엔진의 상태는 표시 안됨(InnoDB 등)
Analyzeing and statistics
storage 엔진의 통계 를 확인해서 최적하는 하는중
Copying to tmp table[to disk]
group by , solt 등을 하기 위해 임시 테이블을 만들고 결과를
복사해 놓는 과정
[to disk 라고 적혀있다면 memory에서 -> disk 테이블에 쓴느중)
Sorting result
리절트를 솔팅하는 중
Sending data
서버에서 쿼리 실행 단계중에서 서로 데이터를 보내는것
또는 클라에게 보내는것
statistics 상태가 나온다면 서버 전체적으로 프로 파일링을 해봐야 한다. ㅠ_-
Query cache
1bit 라도 다르다면 매치 되지 않는다.
만약 매치 된다면 권한체크(파싱하지 않고)만하고 리턴
Query optimization process
서버 실행 계획 생성
= parsing -> preprocessing -> optimization
paring
sql을 트리로 만들면서 문법 체크
preprocessor
1.테이블, 컬럼 존재여부 애매한 컬럼등 조사
2.권한 조사
optimization
cost-based 사용
측정 기준
4KB page read
*SHOW STATUS LIKE 'Last_query_cost' 좀전에 실행된 쿼리의 코스트
964 random page 를 읽은거 같다는 뜻
1.(통계값임)
2.(캐쉬되서 리턴되는 값은 고려 안함 무저건 랜덤 I/O로 진행된다고 가정)
참조되는 값
테이블,인덱스 크기
cardinality(number of distinc values)
indexes
rows length , key length
최고의 플랜이 선택이 보장되지 않는 이유
1.통계가 틀릴경우
2.통계가 정확해도 예측값과 실제 실행 되는 자원의 값은 다르다
(mysql은 page가 메모리에 있는지 디스크에 있는지 알지 못한다.)
3.mysql은 최고로 빠른속도를 지향하지 않고 최소의 코스트를 지향한다.
4.해당 쿼리외에 동시에 실행 되는 다른 쿼리들이 영향을 줄수 있다
5.where 절에 특정 키워드가 있을 경우 최적화 안한다(FULLTEXT MATCH 등)
6.시스템 펑션이나, 유저 펑션등 mysql이 측정할수 없는애들도 있다
7.가능한 모든 플랜을 만들수 있는건 아니다. 즉 최적의 플랜을 못만들수도있다
최적화의 종류
static
parsing 할떄 where 절을 대수학의 동일 식으로 변경
dynamic
where절, 테이블 또는 인덱스의 로우 등에 따라 변경
*store procedure OR prepare statment를 실행 시킬떄
static 은 한번 하지만 dynamic늘 다시 한다.
최적화 할때 하는짓
reordering joins
테이블 조인 순서 변경
outer join to innerjoin
innerjoin으로 풀수 있다면 변경
필요없는 where 조건 정리
ex) where 5 = 5 and a>5 -> where a>5
COUNT(), MIN(), and MAX() optimizations
min일 떄 인덱스 오른쪽 끝값 읽음 등
해당 최적화가 이루어지면 아래처럼 표현됨
EXPLAIN plan : Select tables optimized away
커버링 인덱스
서브쿼리 최적화
Early termination
쿼리가 실행되는 도중에 멈출수 있다
LIMIT
DISTINCT, NOT EXISTS(), and LEFT JOIN
Equality propagation
SELECT film.film_id
-> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE film.film_id > 500;
film, film_actor에 둘다 film_id > 500을 적용한다.
*현재는 MySql만 지원하는 기능
IN
MySql에서는 In안의 벨류를 정렬시킨 후
바이너리 검색을 한다.
OR의 경우 O(n)
MySql 이 바이너리 검색을 할경우 O(log n)
옵티마이저는 똑똑하니 이길려고 싸우지 말고 옵티가 잘 못하는게 있음
그부분을 수정해라
*테이블, 인덱스의 통계정보는 storage engine이 가지고 있다!(서버가 아님!!)
MySql에서 join 이라는 단어는 범위가 넒음
모든쿼리를 뜻함(한테이블에 날리는 싱글 쿼리는 조인)
why join이라고 하는가
모든 쿼리를 아래같은(수도코드) 코드로 실행 하기 때문에
mysql> SELECT tbl1.col1, tbl2.col2
-> FROM tbl1 LEFT OUTER JOIN tbl2 USING(col3)
-> WHERE tbl1.col1 IN(5,6);
outer_iter = iterator over tbl1 where col1 IN(5,6)
outer_row = outer_iter.next
while outer_row
inner_iter = iterator over tbl2 where col3 = outer_row.col3
inner_row = inner_iter.next
if inner_row
while inner_row
output [ outer_row.col1, inner_row.col2 ]
inner_row = inner_iter.next
end
else
output [ outer_row.col1, NULL ]
end
outer_row = outer_iter.next
end
* inner it는 필러링이 됨으로 == 조건으로 무조건 밖에 숫자가 작은게 유리하다!
조인시 로우수가 적은 테이블이 outer 에 있어야한다.
즉 driving 되는 테이블이 로우 수가 적어야된다!
*서브 쿼리일경우
서브쿼리를 먼저 실행시키고 그걸 temp table로 만든후(그래서 derived table!)
다시 위 루틴을 적용 시킨다.
*right일 경우는 전부 left 쿼리로 바꿈
*full outer join 이 안되는 이유임
Mysql은 쿼리를 실행 시키기 위해서 byte-code를 만들지 않는다.
EXPLAIN EXTENDED SELECT * FROM sakila.film A inner join film_actor B using(film_id);
SHOW WARNINGS;
***** 을 하게되면 최종적으로 실행된 쿼리를 확인 할수 있따!!!
어떤 쿼리도 트리로 나타낼수 있다
Mysql은 무조건 테이블을 조인해 하나의 테이블로 만들고 그걸또 조인시킨다
ex)
select * from A, B, C, D where~
일경우
A,B JOIN->temp1 ,C JOIN-<temp2 ,D JOIN->result set
*옵티마이징에서 가장 중요한건 조인 순서를 정하는거다
STRAIGHT_JOIN 순서대로 조인 하라는 힌트절
SELECT STRAIGHT_JOIN * FROM A
n 테이블을 조인할 경우 plan은 n!이 나온다
즉.. 10개 만 조인 하면 362800이 나온다..-> 즉 느려진다.
optimizer_search_depth 로 조절 가능
옵티마이징 안되는 쿼리들
left join
subquery
결과가 나와야 조인을 시킬수 있음으로
sort optimization
메모리를쓰던 안쓰던 file sort 라고 표시된다
file sort 종류
Two passes (old)
1.row point와 order by 컬럼을 읽고
2.order by로 정렬하고
3.정렬된 순서대로 reread 한다.
느린이유
1.테이블에서 row 를 두번 읽는다
2.다시 읽을때 random I/O가 발생할 가능성이 높다
single pass (new)
1.쿼리에 필요한 모든 컬럼을 읽고
2.정렬하고
3.result 필요한 컬럼만 선택후 출력
단점
많은 데이터를 읽어 함으로
file에 쓰고 merge 하는 작업을 해야 한다.
MySql은 filesort 할때 많은 공간이 필요하다
why?
왜냐하면 정렬시 고정 된 길이의 레코드를 사용하기 때문에
1.가장큰 길이의 레코드에 의해 결정된다.
2.varchar의 경우 최대 길이로
3.utf8로 되었을 경우 무조건 3바이트를 할당함
그래서 템프 테이블은 실제 디스크에 저장된 테이블의 전체 용량보다
더큰 경우도 많다.
첫번재 테이블로 sort 가 결정될 경우 정렬후 조인 Using filesort
결과같으로 정렬할 경우 Using temporary Using filesort
*limte가 외부에 설정되었어도 만들고 하는거기때문에 별 쓸고가 없다
쿼리 실행 엔진
옵티마이징 끝나면 execution plan 이 나온다
그냥 이 plan 대로 storage engine 에 api를 호출한다.
returning results to the client
만약 캐쉬가 on 되어있으면 이때 적재
서버는 result 가 생성되면 첫줄부터 클라이언트로 보낸다(지속적으로)
why?
서버 메모리에 적재하지 않아 메모리 사용량을 줄일라고
mysql 옵티마이저의 한계
상관관계 서브쿼리
SELECT * FROM sakila.film
WHERE film_id IN(
SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);
이렇게 풀릴거라고 예상한다.
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
한대 직접 SHOW WARNINGS로 보면
SELECT * FROM sakila.film
WHERE EXISTS(
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
이렇게 풀려있다 - -...
만약 film테이블이 크다면.. 답안나온다.
서브쿼리 변환 방법
DISTICT 나 group by 를 써야 한다면 EXIST 를 고려하자
SELECT DISTINCT film.film_id FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id);
SELECT film.film_id FROM sakila.film
WHERE EXIST(SELECT * FROM sakila.film_actor
WHERE film.film_id = film_acotr.film_id)
Parallel Execution
하나의 쿼리를 여러개의 cpu 에서 병렬로 처리하는거 지원안함
Query Optimizer Hints
우선순위 변경
InnoDB등 락킹 잘관리하고 MVCC 하는애들에는 안쓰는게 좋음
MyISAM 에 조심
concurrent insert에 매우 않좋은 영향을 줄수 있음
HIGH_PRIORITY
해당 쿼리를 큐의 맨앞부분으로 이동(lock 우선순위 변경)
LOW_PRIORITY
해당 쿼리를 큐의 맨뒤부분으로 이동(lock 우선순위 변경)
기아 상태에 빠질수 있음
DELAYED
INSERT, REPLACE 문에 사용가능
클라에서 INSERT 오면 ok 응답 보내주고
BUFFER에 저장 후 테이블이 FREE 상태일때 INSERT
BULK INSERT를 위해 만들어짐
각각의 stmt에 I/O를 피할수 있음
*LAST_INSERTED_ID 사용 못함
STRAIGHT_JOIN
순서대로 조인하게 강제
*옵티가 오래걸릴(조인 테이블이 많을 경우)떄 사용하면 좋음
결과량
for SELECT, temp table, GFOUP BY, DISTINCT 할때
어떤 플랜을 짜면 좋은지 옵티에게 알림
SQL_SMALL_RESULT
작은 결과가 리턴될거임
SQL_BIG_RESULT
큰결과과 리턴될거임
그러니 디스크에 테이블을 만드삼
SQL_BUFFER_RESULT
쿼리 결과를 나오는대로 클라로 흘리는게 아니라
서버 메모리에 저장 후 결과 다 나오면 리턴
(즉 클라 메모리가 아닌 서버 메모리에 저장)
SQL_CACHE, SQL_NO_CACHE
cache에 적재 시킬래? 안시킬래
SQL_CALC_FOUND_ROWS
LIMIT 가 있어도 무저건 전체 로우를 사용
왠만하면 사용말자
FOR UPDATE and LOCK IN SHARE MODE
for SELECT
로우레벨 락킹을 지원하는 엔진에 쓸수있음
정말 잘 알지 못하고 사용하면 심각한 락 문제를 가져올수 있음
USE INDEX, IGNORE INDEX and FORCE INDEX
오더에 INDEX 사용하기 위해
FOR ORDER BY, FOR GROUP BY 힌트 사용 가능
USE INDEX
왠만하면 인덱스 쓰렴
FORCE INDEX
무저건 INDEX 쓰렴
*아래 부터는 서버 옵션임
optimizer_search_depth
옵티가 만들수 있는 플랜들 조정(낮게 설정)
크게하면 가능 플랜 만드느라 느려짐
optimizer_prune_level
옵티는 테이블의 로우수에따라 특정 쿼리를 사용하지 않게 되어 있다
default enabled
optimizer_switch
인덱스 관련 상세 옵션
ex)
index merge query plan 쓸래 등
*힌트는 왠만하면 쓰지 말자
1.옵티는 겁나 많은 사람들이 만든거다 즉 똑똑하다
2.MySql 버전 upgrade할때 힌트가문제가 될수있다
*버전 업그래이드시 pt-upgrade 툴을 쓰면
해당 쿼리들이 새 버전에서도 문제 없이 동작하는지 확인 할 수 있다
특수 타입 쿼리 튜닝
*버전마다 방법이 다르다
COUNT Queries
카운트가 하는 종류
1.벨류들에서 NULL을 제외한 벨류를 센다
2.결과에서 로우 수를 샌다
COUNT(*)
*로우를 세고 싶다면 COUNT(*)을 사용(칼럼명 쓰지 말아라!)
MyISAM의 카운트가 빠르다?
노우
단 전체 로우수를 셀대만
where 절이 있으면 안됨
꼼수
정확한 숫자의 카운트가 필요없다면
explain에서 가져오는것도 방법
JOIN Queries
1.where 컬럼에 들어가 있는 애는 인덱스가 있는지 확인
2.group by, order by만 사용해라록 해라(\
그럼 메모리에서 할수 있다.
sub Queries
최대한 조인으로 변경해라
GROUP BY and DISTINCT
둘다 비슷한 기능을 하며 내부적으로 옵티 할때는 MySQL 내부에서 자주 바꿔쓴다.
SQL_BIG_RESULT and SQL_SMALL_RESULT를 사용해라
만약 LOOK up 테이블에서 벨류를 가져와 그값으로 조인해야 할때는
group by에 look up table 컬럼 을 쓰는게 더 좋은 방법이다.
만약 정렬할필요가 없는대 정렬이 일어 난다면
자동 정렬을 피하기 위해서
ORDER BY NULL 사용
WITH ROLLUP
원하지 않는 실행 계쇡이 나올때가 많다
가능하면 어플리케이션 코드로 해결하자
LIMIT and OFFSET
인덱스를 안타면 file sort하게 된다.
LIMIT 10000,200 하게되면 10200 로우를 가져와 10000 로우를 버린다
이럴때는 좀더 최적화한다.
1.deferred join 이라고 도 부른다.
SELECT film.film_id, film.description
-> FROM sakila.film
-> INNER JOIN (
-> SELECT film_id FROM sakila.film
-> ORDER BY title LIMIT 50, 5
-> ) AS lim USING(film_id);
2.미리 페이지 별로 인덱스를 만들고 인덱스를 조건으로 타는 방법도 있다
SELECT film_id, description FROM sakila.film
-> WHERE position BETWEEN 50 AND 54 ORDER BY position;
3.삭제가 되지 않는다면 AUTO_INCREMENT PK는 증가만 한다는걸 사용가능하다.
mysql> SELECT * FROM sakila.rental
-> ORDER BY rental_id DESC LIMIT 20;
This query returns rentals 16049 through 16030. The next query can continue from
that point:
mysql> SELECT * FROM sakila.rental
-> WHERE rental_id < 16030
-> ORDER BY rental_id DESC LIMIT 20;
*가장좋은건 아무리 깊이 페이징을 해도 별 영향이 없다
4.여분의 테이블을 만든다.
ORDER BY에서 필요한 컬럼들로만 만들어진
그후 원테이블이랑 조인 때린다.
5.클라쪽에서 페이지 크기보다 큰 로우를 받고 20개 보일때 1000개를 받음
그냥 보여주다가 1000개 넘어가면 요청하는거
*토탈 페이지수를 EXPLAIN에서 가져오는거(구글도 토탈 페이지를 ++ 표시)
UNION
**Mysql은 무조건 템프테이블 만든다.(결과를 순차적으로 클라로 안보낸다!)
UNION ALL을 써라!
UNION만 쓴다면 DISTICT 를 하게 되는대
템프테이블은 인덱스가 없기 때문에 전체 테이블에 대해서 유니크를 조사해야한다.
Static Query Analysis
pt-query-adviosr 를 사용해라
대략 나쁜 패턴 이라고 생각 되는 쿼리를 뽑아 낼수있다
Using User-Defined Variables P256
유저 정의 변수 == 컨넥션이 살아 있을때까지 살아 있음
RDB는 전체를 set으로 동시처리한다 하지만 유저 변수를 쓰면 순차적 처리도 가능하다
단점
*사용하면 쿼리 캐쉬 사용못한다.
*컨넥션당이다 inter컨넥션에서 안된다.
*컨낵션 풀링이나 영속적 연결 사용하면 문제 될수 있다
*옵티가 제거해버릴수도 있다
* := 의 할당 연산자는 우선순위가 가장 낮다
예제
유저 변수에 할당되는 순서는 순차적이지 않다
그러므로 할당 후 할당되야 되는 애들은 서브쿼리로 빼야된다.
mysql> SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
-> SELECT actor_id,
-> @curr_cnt := cnt AS cnt,
-> @rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
-> @prev_cnt := @curr_cnt AS dummy
-> FROM (
-> SELECT actor_id, COUNT(*) AS cnt
-> FROM sakila.film_actor
-> GROUP BY actor_id
-> ORDER BY cnt DESC
-> LIMIT 10
-> ) as der;
조금전에 변경된 로우의 값을 리턴한때 사용가능(UPDATE RETURNING)
UPDATE t1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();
SELECT @now;
INSERT ON DUPLICATE KEY UPDATE 할때 몇개의 로우가 듑에러가 나서 업데이트 되었는가?
INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1)
ON DUPLICATE KEY UPDATE
c1 = VALUES(c1) + ( 0 * ( @x := @x +1 ) );
*벨류는 업데이트 되지만 @x에 더하고 0을 곱하므로 카운트는 올라가고 실제 값은 변하지 않는다.
유저변수는 적용되는 부분에 따라 다르게 배치된다. where 절 걸리고 select 되고 order by걸리는것처럼
*안되는거 볼래면 EXPLAIN 의 EXtra 컬럼을 확인해라
동작안함
mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt
-> FROM sakila.actor
-> WHERE @rownum <= 1
-> ORDER BY first_name;
동작됨
mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum AS rownum
-> FROM sakila.actor
-> WHERE (@rownum := @rownum + 1) <= 1;
이런방식으로 hack해서 사용하기도 한다.
mysql> SET @rownum := 0;
mysql> SELECT actor_id, first_name, @rownum AS rownum
-> FROM sakila.actor
-> WHERE @rownum <= 1
-> ORDER BY first_name, LEAST(0, @rownum := @rownum + 1);
GREATEST(), LENGTH(), ISNULL(), NULLIF(), IF(), COALESCE() 를 사용할수있다
Building a Queue Table in MySQL
queue table을 하나만 만들면 시간이 지날쓰록 쓰기 힘들다
그렇기 히스토리 테이블을 따로 만들자
워커 프로세스가 쉴때
SELECT /* waiting on unsent_emails */ SLEEP(10000);
이렇게 하고 process list 에서 커먼트가 있음 죽인다.
이메일을 보내는 큐테이블을 만들어 보자~
CREATE TABLE unsent_emails (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-- columns for the message, from, to, subject, etc.
status ENUM('unsent', 'claimed', 'sent'),
owner INT UNSIGNED NOT NULL DEFAULT 0,
ts TIMESTAMP,
KEY (owner, status, ts)
);
owner = connection process id임 (CONNECTION_ID()값이 들어감)
0이면 자기꺼라고 주장한애가 없는것
내일임 주장하기(락최소화)
SET AUTOCOMMIT = 1;
COMMIT;
UPDATE unsent_emails
SET status = 'claimed', owner = CONNECTION_ID()
WHERE owner = 0 AND status = 'unsent'
LIMIT 10;
SET AUTOCOMMIT = 0;
SELECT id FROM unsent_emails
WHERE owner = CONNECTION_ID() AND status = 'claimed';
-- result: 123, 456, 789
*SELECT FOR UPDATE 왠만하면 쓰지 말고 위에 꺼처럼 써라
문제가 생겨 워커가 주장만 하고 처리 못한거 처리하기
SHOW PROCESSLIST
현재 일 처리하는 애들의 id얻어와서
UPDATE unsent_emails
SET owner = 0, status = 'unsent'
WHERE owner NOT IN(0, 10, 20, 30) AND status = 'claimed'
AND ts < CURRENT_TIMESTAMP - INTERVAL 10 MINUTE;
문제생긴놈꺼 풀어주기
*인덱스에 주목해라 (owner, status, ts)
ts 가 마지막에 있기때문에 range 까지 쓸수 있다
Computing the Distance Between Points
P258
MySQL에서 거리 계산하기
Using User-Defined Functions
C나 C++에 아주 익숙하다면 펑션을 만들어서 써도 된다.
