Ch11. 쿼리 작성 및 최적화 - 3

[sanhee]

148p ~ 207p

INSERT, UPDATE와 DELETE, 스키마 조작(DDL)

• 오늘 마무리 시간에 다음 스터디 범위를 안정한 거 같아서, 임의로 정했어요.
• 혹시, 변경이 필요하면 코멘트 주세요!

[kyupid]

151p insert ... on duplicate key update

• 회원마다 방문횟수를 업데이트 하고 어드민에서는 access_log 기준으로 바로바로 가져왔는데 그게 부담이 되는 규모라면 한번에 update하는게 좋을 것같다.
• 그리고 그 방법이 insert ... on duplicate key update 이 유용하네

154p LOAD DATA

• 쓴다면 하나보다는 여러 파일을 여러 트랜잭션으로 나누어 실행하는게 좋다
  • 단일 스레드
  • 단일 트랜잭션
• dump할떄 정렬해서 할수있구나..
• Q1) 155p 정렬안한 데이터 복제테이블만들때 왜 느린지 설명하는데 b-tree 부분 잘이해가안가네
  • 비트리 공부하기

156p pk 선정

• select 비율 높은건 쿼리에 맞게 필요한 인덱스들을 추가해도 시스템 전반적 영향도가 낮음
• insert 비율 높은건 인덱스 개수최소화

[kihyuk-sung]

• INSERT
  • INSERT IGNORE
  • INSERT ON DUPLICATE KEY UPDATE
  • 중복인 경우 어떻게 동작할 지에 대한 키워드
• LOAD DATA
  • 속도는 나름 빠르지만, 싱글 쓰레드, 단일 트랜잭션으로 동작
  • 그러므로 큰 데이터의 경우 데이터를 나눈 후 병렬로 처리하는 것이 좋다.
• 대량 INSERT
  • MySQL 은 PK 에 대해서 클러스터링 구조를 가지므로, PK 에 대해서 미리 정렬하여 대량 INSERT 를 실행하는 것이 좋다.
  • 그렇지 않으면 매번 INSERT 시 마다 PK에 대해서 데이터를 정렬해서 넣어야 한다 (Tree Balancing)
• 그외 INSERT 최적화
  • Auto increment 를 사용한다. (자동적으로 PK 에 대해서 정렬된 형태로 데이터를 넣을 수 있다.)
  • 세컨더리 인덱스를 사용하지 않는다. (인덱스 하나 마다 B-Tree 정렬이 추가로 필요하다)
• UPDATE 와 DELETE
  • UPDATE ORDER BY LIMIT n
    • UPDATE row 제한 가능
  • JOIN UPDATE
    • 특정 테이블의 칼럼 값을 다른 테이블에 칼럼에 업데이트 하는 경우
  • JOIN DELETE
    • JOIN 이 성공한 경우에만 DELETE 하기 등
  • JOIN 을 하는 경우 쿼리 실행 계획을 확인하는 것이 좋다.
• ONLINE DDL
  • 8.0 까지 점차 발전함
  • INSTANCE : 메타데이터만 변경
  • INPLACE : 따로 복사 하지 않고 스키마 변경을 실행한다. 테이블 리빌드를 실행할 수도 있다. 시작과 끝 시점을 제외하곤 테이블 읽기 쓰기가 가능하다.
  • COPY : 임시 테이블에 복사 후 임시 테이블을 RENAME 한다. 테이블 읽기만 가능하고 DML은 불가능하다.
  • 여러 변경에 따라 어떤 알고리즘으로 동작할지 다르다. Document를 확인한다.
  • 테이블 구조 조회, 테이블 구조 복사 등 다양한 작업을 테이블에 대해서 실행할 수 있다.
    • ALTER / CREATE 정도만 실행해봤는데 참고할수 있을 것 같다.
  • 인덱스 조작의 경우도 8.0 부터는 온라인으로 가능하다.

[polynomeer]

11.5 INSERT

• INSERT IGNORE: 중복 무시, NOT NULL 무시 -> 의도치 않은 에러도 무시하는 위험성으로 안 쓰는게 나을듯

• INSERT ... ON DUPLICATED KEY UPDATE: 없으면 추가, 있으면 변경 -> upsert라고 부르는 쿼리, JPA에도 가능한가?

• MySQL 8.0.20 이후 VALUES() deprecated -> alias 사용

• LOAD DATA

  • 장점: 스토리지 엔진이 데이터 직접 적재 -> INSERT보다 빠름
  • 단점: 단일 스레드, 단일 트랜잭션, Undo Log 계속 유지 -> 갈수록 느려짐, 오버헤드
  • 결론: LOAD DATA 대상 파일은 여러 개로, 트랜잭션도 여러 개로, 테이블 간 복사는 INSERT ... SELECT
    -> 그러면 LOAD DATA는 어떤식으로 사용하는게 좋은지 예시?

• 대량 INSERT 성능

  • PK로 정렬되어 있어야 빠르다. 세컨더리 인덱스가 많은 수록, 테이블이 클수록 SELECT ⬆, INSERT ⬇
  • PK 선정은 SELECT와 INSERT의 trade-off 고려하여 선정, INSERT는 인덱스가 적을수록 유리
  • INSERT 최적화를 위해 단조 증가/감소되는 PK, 세컨더리 인덱스 최소화
    -> Auto-Increment는 클러스터링 되지 않게 해줌
    -> 주의! MAX() x, LAST_INSERT_ID() o

11.6 UPDATE와 DELETE

• UPDATE ... ORDER BY ... LIMIT n: ORDER BY가 부정확할 수 있으니 조심 -> 그냥 안쓰는게..
• JOIN UPDATE
  • LOCK 걸리니깐 배치에서 사용 권장
  • 조인 순서에 따라 성능 달라질 수 있으니 실행계획 확인
  • GROUP BY 사용 안되므로 서브 쿼리 사용할 것 -> INNER JOIN LATERAL도 가능
  • STRAIGHT JOIN으로 조인 순서 명시

11.7 스키마 조작(DDL)

• 온라인 DDL
  • innodb_lock_wait_timeout은 온라인 스키마 변경과는 무관

[ghojeong]

읽으면서 목차만 정리해보았습니다.

11.5 INSERT

• 11.5.1 고급옵션
  • 11.5.1.1 INSERT IGNORE
  • 11.5.1.2 INSERT ... ON DUPLICATE KEY UPDATE
• 11.5.2 LOAD DATA 명령 주의 사항
• 11.5.3 성능을 위한 테이블 구조
  • 11.5.3.1 대량 INSET 성능
  • 11.5.3.2 프라이머리 키 선정
  • 11.5.3.3 AUTO-Increment 칼럼

11.6 UPDATE 와 DELETE

• 11.6.1 UPDATE ... ORDER BY ...LIMIT n
• 11.6.2 JOIN UPDATE
• 11.6.3 여러 레코드 UPDATE
• 11.6.4 JOIN DELETE

11.7 스키마 조작(DDL)

11.7.1 온라인 DDL

• 11.7.1.1 온라인 DDL 알고리즘
• 11.7.1.2 온라인 처리 가능한 스키마 변경
• 11.7.1.3 INPLACE 알고리즘
• 11.7.1.4 온라인 DDL 의 실패 케이스
• 11.7.1.5 온라인 DDL 진행 상황 모니터링

11.7.2 데이터베이스 변경

• 11.7.2.1 데이터베이스 생성
• 11.7.2.2 데이터베이스 목록
• 11.7.2.3 데이터베이스 선택
• 11.7.2.4 데이터베이스 속성 변경
• 11.7.2.5 데이터베이스 삭제

11.7.3 테이블 스페이스 변경

11.7.4 테이블 변경

• 11.7.4.1 테이블 생성
• 11.7.4.2 테이블 구조 조회
• 11.7.4.3 테이블 구조 변경
• 11.7.4.4 테이블 명 변경
• 11.7.4.5 테이블 상태 조회
• 11.7.4.6 테이블 구조 복사
• 11.7.4.7 테이블 삭제

11.7.5 칼럼 변경

• 11.7.5.1 칼럼 추가
• 11.7.5.2 칼럼 삭제
• 11.7.5.3 칼럼 이름 및 칼럼 타입 변경

11.7.6 인덱스 변경

• 11.7.6.1 인덱스 추가
• 11.7.6.2 인덱스 조회
• 11.7.6.3 인덱스 이름 변경
• 11.7.6.4 인덱스 가시성 변경
• 11.7.6.5 인덱스 삭제

11.7.7 테이블 변경 묶음 실행

11.7.8 프로세스 조회 및 강제 종료

11.7.9 활성 트랜잭션 조회