Ch11. 쿼리 작성 및 최적화 - 3
Opened this issue · 4 comments
sanhee commented
148p ~ 207p
INSERT, UPDATE와 DELETE, 스키마 조작(DDL)
- 오늘 마무리 시간에 다음 스터디 범위를 안정한 거 같아서, 임의로 정했어요.
- 혹시, 변경이 필요하면 코멘트 주세요!
kyupid commented
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 commented
- 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 을 하는 경우 쿼리 실행 계획을 확인하는 것이 좋다.
- UPDATE ORDER BY LIMIT n
- ONLINE DDL
- 8.0 까지 점차 발전함
- INSTANCE : 메타데이터만 변경
- INPLACE : 따로 복사 하지 않고 스키마 변경을 실행한다. 테이블 리빌드를 실행할 수도 있다. 시작과 끝 시점을 제외하곤 테이블 읽기 쓰기가 가능하다.
- COPY : 임시 테이블에 복사 후 임시 테이블을 RENAME 한다. 테이블 읽기만 가능하고 DML은 불가능하다.
- 여러 변경에 따라 어떤 알고리즘으로 동작할지 다르다. Document를 확인한다.
- 테이블 구조 조회, 테이블 구조 복사 등 다양한 작업을 테이블에 대해서 실행할 수 있다.
- ALTER / CREATE 정도만 실행해봤는데 참고할수 있을 것 같다.
- 인덱스 조작의 경우도 8.0 부터는 온라인으로 가능하다.
polynomeer commented
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 commented
읽으면서 목차만 정리해보았습니다.
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 인덱스 삭제