Skip to main content

MySQL InnoDB 특징 및 기본 개념

InnoDB index

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


순차 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 될 가능성이 큼.


 

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 

-> where 절 (index) 을 사용한 쿼리가 빠른 결과를  얻음.

'INDEX HINT' 를 사용해도 같은 결과를 얻을거라 예상됨.

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;

결과

1. FORCE INDEX Hint가 없는 쿼리             2. FORCE INDEX  Hint가 있는 쿼리.  

LIMIT

MySQL의 LIMIT 은 조심해서 사용해야 한다.
LIMIT 100, 30 이라고 하면 100개를 건너뛰고 100~ 130개 만 SELECT 후 나머지 값들은 조회안하고 종료할 것 같지만,
(안타깝게도) 130 까지 모두 조회 후, 30개만 띄워주는 형식이다. by <Real MySQL> 
만약 LIMIT 10000, 30 이라면, 10030 개를 SELECT 하게 되어 성능에 큰 무리를 준다.
  • 다른 DBMS 와 차이점
    • 큐브리드는 LIMIT 개수까지만 SELECT 해옴.
Labels:

Comments

Post a Comment