Skip to main content

MySQL Index, I/O, BufferPool, Lock, Count, Hint

MySQL InnoDB PK, Index Key  내부

MySQL InnoDB는 Index 테이블을 B-Tree (Balanced tree) 로 구성.
  • Delete
    • index 재정렬이 일어나지 않음
    • delete 되었다는 '상태값' 만 변경함으로써 index 테이블에 성능영향을 최소화 하기 위함 - by

순차 I/O, 랜덤 I/O 에 대해서 ... (Full Table Scan 이 더 효율적일 때도 있다.)

  • Disk I/O의 속도는 '헤더' 의 움직임과 연관이 있다.
'디스크에 데이터를 쓰고 읽는 데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다.'

'일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄이는 것이 목적이라 할 수 있다.'
  • 순차 I/O 는 탐색을 위해 헤더를 1개씩 움직이는 반면 랜덤 I/O는 더 많이 움직인다.
  • Index Scan: 랜덤 I/O , Full Table Scan:순차 I/O
'Index Range Scan은 데이터를 읽기 위해 주로 랜덤 I/O 를 사용하며, 풀 테이블 스캔은 순차 I/O를 사용한다.
그래서 큰 테이블의 레코드를 읽을 때는 풀 테이블 스캔을 사용하여 디스크헤더 의 움직임을 최소화 하는 방법이 더 효율적이다.
이런 형태는 웹서비스 보다는 데이터 웨어하우스나 통계 작업에서 자주 사용된다.'

InnoDB Buffer Pool 구조

  • 랜덤 I/O , Disk write 의 비용이 크기 때문에 InnoDB Engine 은 Buffer Pool (메모리 캐시) 을 사용하여 처리한다.
  • 최근 접근한 데이터는 다시 Access 될 가능성이 큼.
  • Insert Buffer Thread에 의해 처리.
  • DB write 는 Disk Write Thread가 처리.




INDEX Column은  'NOT NULL' 로 설정

INDEX 로 잡은 Column 에 NULL 값이 들어갔을 경우,
의도한 대로 Index Searching이 안이루어질 가능성이 큽니다. (full scan 탈 확률이 큼)
shard_user_map.login_id 가 현재 NULL 허용 Column 으로 되어 있습니다.













(login_id 는 NULL 허용 Column 이라는 전제...)
login_id 가 index 테이블로 잡혀 있지만, Query Plan 은 'Full Table Scan' 을 보여주고 있습니다.

Lock에 대해...

Lock 의 종류


  1. Record Lock (레코드 락)
    • UPDATE 시에 해당 Record에 걸리는 Lock
    • Index가 걸려 있다면, 해당 Index의 Record에 Lock
  2. Gap Lock (갭 락)
    • Record 와 Record 사이에 새로운 Record가 INSERT되는 것을 막음. (ex> Auto-Increment PK인 경우)
  3. Next Key Lock (넥스트 키 락)
  4. Auto-Increment Lock (자동 증가 락)

배타적 잠금 (Exclusive Lock)

내가 쓰기 하는 동안, 다른 사람이 변경하지 못하도록 잠금

공유 잠금 (Shared Lock)

내가 읽기 하는 동안, 다른 사람이 변경하지 못하도록 잠금

Deadlock 이 걸리면?

MySQL InnoDB 는 Dead lock이 걸린 Transaction에 대해 Rollback 처리를 한다.

인서트 인텐션 락 (Insert Intention Lock)

COUNT() 에 대해서...

COUNT(*) 에 WHERE 절을 사용하는 것을 권장...

COUNT All

MyISAM 엔진의 경우  테이블 내에 Record Count를 캐시로 저장해 놓아서 WHERE 절 없이 COUNT() 만 사용해도 괜찮다.
- https://www.percona.com/blog/2007/04/10/count-vs-countcol/
하지만 InnoDB의 경우, (이상하게도...) Record Count를 캐시하지 않는다.
때문에 (index 걸린) WHERE 절 없이 COUNT()를 하면 Full-Scan이 된다...

COUNT Benchmarking .

테스트 환경
- log_money 테이블
- Record 수: 56,000
- Field 수: 16개
SELECT COUNT(*) FROM lslot_log1.log_money;
Duration: 0.070 ~ 0.075 sec

SELECT COUNT(*) FROM lslot_log1.log_money where id > 0;
Duration: 0.019 ~ 0.020 sec

COUNT(*) vs COUNT(pk) ?

테스트 결과 눈에 띄는 차이가 없음..

DELETE 에 대해서...

Performance
When you do not need to know the number of deleted rows, theTRUNCATE TABLEstatement is a faster way to empty a table than aDELETEstatement with noWHEREclause. UnlikeDELETE,TRUNCATE TABLEcannot be used within a transaction or if you have a lock on the table. SeeSection 14.1.34, “TRUNCATE TABLE Syntax”andSection 14.3.5, “LOCK TABLES and UNLOCK TABLES Syntax”.The speed of delete operations may also be affected by factors discussed inSection 9.2.2.3, “Speed of DELETE Statements”.To ensure that a givenDELETEstatement does not take too much time, the MySQL-specificLIMIT_row_count_clause forDELETEspecifies the maximum number of rows to be deleted. If the number of rows to delete is larger than the limit, repeat theDELETEstatement until the number of affected rows is less than theLIMITvalue
  • Delete 보다는 Truncate 를 권장 (성능면에서 우수)
  • MySQL은 LIMIT row_count이 세팅되어 있음.
    • LIMIT 개수보다 record 수가 많아지게 되면, 자체적으로 DELETE 를 진행함으로써 LIMIT count를 맞춤.

Index Hint

  • IN, NOT IN 은 최대한 지양하는 방향이 좋습니다.
  • IN 의 경우, 비교인자가 많아지면 Full Table Scan을 타는 경우가 발생합니다. NOT IN의 경우는 IN 보다 더 복잡하니 성능이 더 저하.
  • 'Index Hint' 를 설정함으로서 강제할 수 있음.

HINT가 없는 쿼리

select * from lslot_log1.log_money
where
  user_id not in (101, 102)
order by log_at desc
limit 0, 30;

HINT가 있는 쿼리

select* from lslot_log1.log_money
  FORCE INDEX(idx_userid_placement_logat)
where
  user_id not in (101, 102)
order by log_at desc
limit 0, 30;

결과

 

LIMIT

쿼리플랜으로 LIMIT 이 full-scan 이라 뜰 수 있다.
하지만 읽어온 갯수가 LIMIT 에 걸리는 순간 SELECT 을 멈춘다. (Full-tabel-scan 을 하다가 중간에 멈춤)
때문에 ORDER BY + LIMIT 절을 쓸때는 조심해야 한다.
정렬이 완료되지 않은 상태에서 LIMIT 을 잘라오기 때문에, 순서가 맞지 않을 수 있다.
MySQL의 LIMIT 은 조심해서 사용해야 한다.
LIMIT 100, 30 이라고 하면 처음 100번쨰 ~ 130번쨰 사이 값만 30개 SELECT을 할 것 같지만. 그렇지 않다.
130 개를 모두 SELECT 한 후 마지막 30개만 잘라서 가져오는 형태이다.
다른 DBMS 와 차이점
  • 큐브리드는 LIMIT 개수까지만 SELECT 해옴.

Labels:

Comments

Post a Comment