[Real MySQL] 16장 베스트 프렉티스

16.1 임의(랜덤) 정렬

• 임의의 순서대로 나열
• 그럼 order by 만 없으면 되는거 아님?
• order by 없어도 옵티마이저가 결정한 작업 순서대로 레코드 가져옴

16.1.1 지금까지의 구현

• RAND() 를 사용해서 정렬
• 문제점 : 대용량 테이블에서where 조건이 없다거나 where 조건으로도 정렬대상 건수를 많이 줄이지 못하면 성능 폭망

16.1.2 인덱스를 이용한 임의 정렬

• 테이블 생성할 때 새로운 칼럼을 추가하고 임의의 값을 미리 각 레코드에 부여
• 쿼리에서는 임의의 값이 저장된 칼럼을 기준으로 정렬해 레코드를 가져옴

 

mysql> SET @random_base = floor((rand() * 10000000));   mysql> SELECT * FROM (              (SELECT * FROM tb_number  WHERE rand_val >= @random_base               ORDER BY rand_val ASC LIMIT 30)              UNION ALL              (SELECT * FROM tb_number  WHERE rand_val < @random_base               ORDER BY rand_val DESC LIMIT 30)            )tb_rand  ORDER BY RAND()  LIMIT 30;

 

16.2 페이징 쿼리

6.2.1 지금까지의 방법

CREATE TABLE tb_article( board_id INT NOT NULL, article_id INT NOT NULL AUTO_INCREMENT, article_title VARCHAR(100) NOT NULL, … PRIMARY KEY(article_id), INDEX ix_boardid(board_id, article_id) );

SELECT * FROM tb_article WHERE board_id=1 ORDER BY article_id DESC LIMIT n, m;

• n 값은 높은 페이지로 이동할 때마다 증가
• n + m 개의 레코드를 읽고 n개를 버리고 m개의 레코드를 반환

 

16.2.2 불필요한 접근을 제거하기 위한 페이징

SELECT * FROM tb_article WHERE board_id=1 AND article_id < 165 ORDER BY article_id DESC LIMIT 0, 20;

• 165는 이전 페이지의 마지막 article_id
• 참고
• MyISAM 테이블에서는 프라이머리 키가 보조 인덱스에 자동으로 추가 되지 않아서 board_id와 article_id 모두 포함하는 인덱스 생성
• InnoDB 테이블에서는 프라이머리 키를 자동으로 인덱스의 마지막 칼럼으로 추가

 

16.3 MySQL 에서 시퀀스 구현

• AUTO_INCREMENT기능은 테이블의 일부라서 관리하기가 용이하지만, 여러 테이블에 걸쳐 유일한 일련번호를 만들어 낼 수 없다는 단점
• Sequence? 오라클에서 제공하는 기능으로 특정 테이블에 의존적이지 않고독립적으로 일련번호 발급

 

16.3.1 시퀀스용 테이블 준비

 

MySQL에서 시퀀스 구현하기

 

CREATE TABLE mysql_sequences( seq_name VARCHAR(10) NOT NULL, seq_currval BIGINT UNSIGNED NOT NULL, PRIMARY KEY(seq_name) )ENGINE = MyISAM

• 테이블의 스토리지 엔진을 MyISAM 으로 한 이유
• InnoDB로 시퀀스 테이블을 만들면 시퀀스 테이블의 잠금 때문에 성능 저하를 유발할 가능성

 

16.3.2 시퀀스를 위한 스토어드 함수

INSERT INTO mysql_sequences SET  seq_name=’시퀀스이름’, seq_currval=(@v_current_value:=1) ON DUPLICATE KEY UPDATE seq_currval=(@v_current_value:=seq_currval+1);

SELECT @v_current_value AS nextval;

• mysql_sequences 테이블에 레코드를 INSERT해서 프라이머리 키에 중복이 발생하면 seq_currval 값만 1만큼 증가시켜서 mysql_sequencse 테이블에 업데이트
• 이 내용을 스토어드 함수로 캡슐화

위의 내용을 조금더 개선하면 …

 

DELIMITER ;;   CREATE FUNCTION nextval() RETURNS BIGINT UNSIGNED MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN INSERT INTO mysql_sequences SET  seq_name=’시퀀스이름’, seq_currval=(@v_current_value:=1) ON DUPLICATE KEY UPDATE seq_currval=(@v_current_value:=seq_currval+1);   RETURN @v_current_value; END

SELECT nextval();

 

on duplicate key로 값을 조회하는것도 좋지만 위의 예제처럼 스토어드 함수로 캡슐화하면 실수를 조금 더 줄일 수 있어

 

16.3.3 여러 시퀀스 처리하기

• 위의 스토어드 함수에서 조금만 변형하면 여러 시퀀스를 처리할 수 있다

DELIMITER ;;   CREATE FUNCTION nextval(p_seq_name CHAR(10) CHARSET latin1) RETURNS BIGINT UNSIGNED MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN INSERT INTO mysql_sequences SET  seq_name=IFNULL(p_seq_name, ‘DEFAULT’), seq_currval=(@v_current_value:=1) ON DUPLICATE KEY UPDATE seq_currval=(@v_current_value:=seq_currval+1);   RETURN @v_current_value; END

SELECT nextval(‘ARTICLE’);

 

16.3.4 시퀀스 사용 시 주의사항

• InnoDB와 같이 트랜잭션을 지원하는 스토리지 엔진 사용하면 안됨
• 시퀀스 테이블은 많은 클라이언트로부터 동시에 시퀀스 번호를 읽혀야 할 수도 있기 때문
• 시퀀스를 위해 만든 스토어드 함수는 INSERT에서 사용 X
• 트랜잭션을 사용하지 않기 때문에 잘못된 값을 가지게 될 가능성있어
• 아래와 같이 일련번호를 가져오는 작업과 sequence 값을 insert 하는 작업을 별도로 분리 해야함

SELECT NEXTVAL(); /*여기서 가져온 값이 16이라고 가정 */ INSERT INTO tb_article(article_id, …) VALUES( 16, …);

 

16.4 큰 문자열 칼럼의 인덱스(해시)

• MyISAM 이나 InnoDB 테이블의 인덱스는 하나의 레코드가 767 바이트 이상을 넘길 수 없음
• 이때 칼럼의 앞부분 767 바이트만 잘라서 p or u key 로 생성할수 있는데 이를 prefix index 라고 함
• 근데 앞 767 바이트가 중복되서 unique index(ui) 나 primary key 를 못쓸경우가 생겨     
• 이럴땐 해시값으로 p key 나 ui를 생성하는 방법을 사용 하면 됨         
• 해시값을 만들려면? SHA 나 MD5로 encrypt 하면 됨

 

16.5 테이블 파티션

• 정보/ 처리해야 할 데이터가 많을때 빠른 처리를 위한 방법중 하나로 MySQL 에서는 Partition 을 지원하고 있음
• Partition은 저장할 데이터를 분할하여 관리함으로써 보다 빠른 데이터 처리를 도와줌

• MySQL 에서는 해시 파티션의 일종으로 키 파티션 기능을 제공
• 테이블의 프라이머리 키나 유니크 키 칼럼을 이용해 파티션 하는 방법
• 파티션 적용시 PARTITION BY KEY() 와 같이 파티션 키를 명시하지 않으면 프라이머리키를 구성하는 모든 칼럼이 파티션 키가 됨

16.6 SNS 의 타임라인 구현

• facebook 이나 twitter 의 타임라인 기능은 RDBMS 에 적합하지 않은 데이터
• RDBMS 는 쓰기는 느리지만 읽기는 빠르게 처리하는게 장점. 근데 타임라인은 읽기보다 쓰기의 비중이 더 많아
• RDBMS 의 정렬은 인덱스를 이용하지 못하면 정렬대상 건수에 비례해 느려짐. 근데 타임라인의 정렬은 인덱스를 이용할 수 없어

16.6.1 예제 시나리오

•  

등록된 모든 회원은 자신의 게시물을 작성할 수 있으며, 또한 본인과 친구로 등록한 회원의 게시물을 조회할 수 있다   특정 사용자가 게시물을 조회할 때는 본인과 친구로 등록한 사용자의 게시물을 작성 시간 역순으로 타임라인을 보여주고자 한다

 

• SELECT * FROM tb_article WHERE 작성자 IN (‘김세형’, ‘임현석’) ORDER BY 작성일
• 범위조건이라 인덱스를 이용할 수없어
• 사용자가 게시물이 많아지면 MySQL 서버 폭발

 

16.6.2 인덱스 테이블 사용

• 단순한 해결책 : 사용자별 타임라인 테이블(인덱스 테이블)을 미리 만들고 칼럼으로 인덱스 생성
• 문제점1 : 인덱스 테이블이 회원 테이블의 레코드 수보다 훨씬 빠른 속도로 불어남
• 문제점2 : 새로운 게시물이 게시 또는 삭제 될때 모든 회원의 타임라인을 INSERT / DELETE 해야함 -> 서버 과부하

 

16.6.3 Try & Fail 쿼리

• 실시간으로 처리할 수 있는 만큼만 조금씩 잘라서 정렬하고, 필요한 만큼의 레코드가 조회될때까지 여러번 SELECT 쿼리를 실행하자는것이 기본 원리

• 적당한 시간 범위 자르기
•  

SELECT * FROM tb_article WHERE 작성자 IN (‘김세형’,’임현석’)   AND 작성일시 > ? /* 검색 범위 시작 일시 */   AND 작성일시 <= ? /* 검색 범위 종료 일시 */ ORDER BY 작성일시 DESC LIMIT 20;

 

• 보여줘야할 게시물 가운데 최근에 작성된 일부 게시물만 조회하고 정렬을 수행하므로 인덱스를 효율적으로 이용가능 and 소량의 레코드만 처리하면 됨
• 필요한 만큼의 레코드를 못찾았으면 다시 다음 시간 범위의 쿼리 실행
• 쿼리 여러번 실행되는건 신경 안써도 돼 -> 쿼리가 데이터를 가져오지 못했다는건 쿼리가 한일이 아무것도 없단 뜻
• 부가적인 비용을 줄이는 방법 중 하나로 Try & Fail 쿼리를 스토어드 프로시저로 구현하는걸 생각해볼 수 있음.

 

• Try & Fail 쿼리를 스토어드 프로시저로 구현
• 흐름

1. 필요할 로컬 변수 정의 및 초기화
2. 게시물의 타임라인 조회를 위해 반복 실행할 쿼리의 프리페어 스테이트먼트 준비
3. 반복 루프를 돌면서 SELECT 쿼리의 대상 시간 범위를 변수에 설정하고 프리페어 스테이트먼트를 실행
4. 필요한 레코드 20건이 채워지면 반복 루프를 종료하고 프리페어 스테이트먼트를 해제

• 주의 및 개선 사항
• 게시물의 누락
• 만약 장성 일시가 동일한 게시물이 있을 때 누락의 가능성
• 작성 일시와 게시물 번호를 함께 범위로 사용할 것 추천
• 게시물 일련번호를 부여하는 방법도 있음
• 반대로 타임라인의 최근 게시물 가져오기
• 최근 타임라인을 가져오고 싶을 때는 기준 일시에 DATE_ADD()함수를 적용
• 작성 일시를 ASC로 정렬해서 가져와야 할 것
• 회원별 게시물 작성의 불규칙성
• 실시간 통계 테이블을 별도로 생성해서 스토어드 프로시저가 참조할 수 있게 구현하는 방법
• Try & Fail 쿼리의 실행 횟수를 평균해서 저장해두는 것 -> 프로시저가 호출되면 그 회원의 평균 실행 횟수를 참조해 시간 간격을 그에 맞춰서 가변적으로 적용

 

16.7 MySQL 표준 설정

16.7.1 MySQL 표준 설정의 필요성

• MySQL 서버의 특성상 기본 확장 방식은 하드웨어의 성능을 업그레이드하는 방법(스케일 업)이 아니라 똑같은 성능의 하드웨어를 장착한 서버의 대수를 늘리는 형태(스케일 아웃)
• MySQL 서버가 필요할 때 기준이 되는 설정 파일을 준비한다면, 설정의 누락 없이 빠르게 서비스 환경 구축 가능

 

16.7.2 표준설정의 예시(p.948 참조)

• 일반적으로 고정적으로 사용하는 설정
• OS 유저나 디렉터리, 이벤트 스케줄러나 “skip-name-resolve”, “sysdate-is-now”와 같은 성능상의 이슈나 실수를 만들어 내기 쉬운 부분은 거의 고정적으로 통일해서 사용
• 복제 용도별로 사용하는 설정
• 복제 용도별로 사용할 수도 있고 사용하지 않을 수도 있는 설정은 모두 주석으로 처리
• 필요할 때만 활성화해서 사용
• 디렉터리나 파일명을 명확하게
• 하드웨어의 특성이나 서비스의 특성에 맞게 변경하는 설정
• MyISAM의 키 캐시, InnoDB의 InnoDB 버퍼 풀의 크기는 성능에 큰 영향
• 별도의 주석으로 표기해두고, 잊지 않도록 할 것

 

16.8 복제를 사용하지 않는 MySQL의 설정

• 복제를 사용하려면 바이너리 로그 파일이 활성화돼야
• 고비용의 디스크 I/O 필요
• 내부적으로 갭 락과 넥스트 키 락을 사용
• MySQL 서버 전체적인 성능과 동시성까지 저하시키는 효과
• 결론(복제를 사용하지 않을 땐)
• 바이너리 로그 비활성화
• InnoDB가 갭락과 넥스트 키 락을 사용하지 않게 트랜잭션 격리 수준을 READ-COMMITTED로 사용

 

16.9 MySQL 복제 구축

16.9.1 MySQL 복제의 형태

• 하나의 슬레이브 MySQL이 둘 이상의 마스터 MySQL을 가질 수 없다
• 그 외에는 어떤 형태로든 구성 가능
• 1:M 복제
• 마스터 서버에 2개 이상의 슬레이브를 연결시키는 복제 형태
• 쿼리 요청수를 적절히 분산해서 실행 가능, 백업이나 통계, 배치 프로그램 용도로도 사용
• 1:M:M 복제
• 1:M 구조에서 슬레이브 서버가 너무 많아서 마스터 서버의 성능에 악영향이 예상될 때는 p.953 그림 처럼 1:M:M 구조의 복제 고려
• 1차 복제 그룹은 변경이 빠르게 적용될 것이므로 웹 서비스와 같은 OLTP 서비스 용도, 2차 복제 그룹은 통계나 배치 그리고 백업 용도로 사용 가능
• 업그레이드하거나 장비를 일괄 교체할 때도 사용 가능

 

16.9.2 확장(스케일 아웃)

• MySQL의 복제는 읽기를 확장하는 방법이지 쓰기를 확장하는 방법 X
• 마스터에 쓰기 작업이 집중
• 최대한 읽기 작업을 슬레이브로 옮겨야 쓰기 작업의 병목으로 인한 서비스 장애를 줄일 수 있다

 

16.9.3 가용성

• 하드웨어 문제로 MySQL 서버에 문제가 발생하면 슬레이브 서버를 마스터로 승격시켜서 서비스를 멈추지 않게 해야함
• 자동으로 슬레이브를 마스터로 승격하는 MMM(Multi-Master replication Manager) 도구

 

16.9.4 복제가 구축된 MySQL 에서의 작업

• 사용자가 동시 다발적으로 실행한 SQL이 슬레이브 서버에서는 하나의 스레드에 의해 직렬화되어 하나씩 순차적으로 실행
• 시간이 오래 걸리는 인덱스나 칼럼 추가와 같은 작업은 마스터 서버에서만 실행하는 것보다 마스터와 슬레이브 각각의 서버에서 별도로 실행할 것
• 주의점
• 마스터 서버에서 실행되는 인덱스나 칼럼 추가 명령이 슬레이브로 넘어가지 않게 해야함

SET sql_log_bin = OFF; ALTER TABLE employees ADD INDEX ix_lastname(lastname); SET sql_log_bin = ON;

 

16.10 SQL 작성 표준

16.10.1 조인 조건은 항상 ON 절에 기재

SELECT * FROM employees e LEFT JOIN dept_manager dm WHERE e.first_name=’Smith’ AND dm.emp_no = e.emp_no

• LEFT JOIN 키워드를 사용해 아우터 조인을 수행하면서 WHERE 절에 조건을 표기하는 실수
• 이런 상황에서 MySQL 옵티마이저는 INNER JOIN으로 고쳐서 실행
• LEFT JOIN 뿐 아니라 INNER JOIN에서도 가능하면 ON절에서 명시하는 습괍 추천

 

16.10.2 테이블 별칭(Alias) 사용 및 칼럼 명에 테이블 별칭 포함

SELECT first_name, last_name FROM employees e INNER JOIN dept_manager dm ON dm.emp_no = e.emp_no WHERE first_name=’Smith’;

• first_name은 employees 에만 존재하기 때문에 문제없이 실행
• 어느날 dept_manager에 first_name 칼럼이 추가된다면 에러 발생
• 가능하다면 조인 여부와 상관없이 짧은 이름으로 테이블의 별칭을 부여하고, 모든 칼럼의 이름 앞에는 테이블의 별칭을 붙이는 습관 추천

 

16.10.4 FULL GROUP BY 사용

• FULL GROUP BY : GROUP BY절에 명시된 칼럼 이외의 모든 칼럼은 집합 함수를 통해서만 조회 가능 제약
• FULL GROUP BY를 사용하지 않는 쿼리는 가독성을 떨어뜨리고 사용자의 실수 유발 가능성

 

16.10.5 DELETE, UPDATE 쿼리에서 ORDER BY.. LIMIT.. 사용 자제

• MySQL 5.0 이하는 DELETE나 UPDATE쿼리에 ORDER BY … LIMIT … 형태의 쿼리를 실행해도 아무 문제 X
• 복제가 구축된 5.1 버전부터는 이러한 쿼리가 마스터와 슬레이브의 데이터를 달라지게 만들 가능성
• 그래서 이러한 쿼리는 경고 메시지를 출력하고, 때로는 에러를 발생
• 경고 메시지때문에 쌓인 에러 로그 때문에 디스크의 여유 공간 문제 발생 가능성

 

16.10.6 문자열 리터럴 표기는 홑따옴표만 사용

• MySQL 에서는 홑따옴표와 쌍따옴표 모두 사용 가능
• 하지만 두 따옴표의 이스케이프 방식이 달라 혼란을 초래할 수 있고, 잘못된 데이터가 저장될 가능성
• 가능하다면 SQL 표준인 홑따옴표를 사용할 것을 권장

 

16.10.7 서브쿼리는 조인으로 변경

• MySQL은 서브 쿼리 최적화 능력이 상당히 부족
• FROM 절에 사용된 괄호의 개수만큼 임시 테이블을 만들어 처리한다고 가정하면 될 정도로 취약
• 불필요한 FROM절의 서브 쿼리는 제거를 추천

 

16.10.8 UNION [ALL] 은 사용 자제

• UNION은 항상 내부적으로 임시 테이블을 만들어 버퍼링한 다음에 사용자에게 결과를 반환
• 여러 집합의 중복된 레코드를 제거해야 할 때는 우회 방법이 없지만, 중복 제거가 없는 경우는 두 개의 쿼리문으로 분리해서 실행하는 편이 더 효율적으로 처리

 

16.10.9 스토어드 함수는 가능하면 DETERMINISTIC 으로 정의

• DETERMINISTIC : 스토어드 함수나 프로시저에서 입력 값이 똑같으면 출력 값도 같다는 것을 옵티마이저에게 알려주는 명령

 

16.10.10 스토어드 프로그램에서는 예외 처리 코드를 작성

• 스토어드 프로시저나 함수는 디버깅이 어려움
• 항상 예외 핸들러 코드를 포함시켜야

 

16.10.11 UPDATE, DELETE 쿼리와 적용 건수(Affected row counts) 체크

• INSERT 쿼리는 성공하면 1, 실패하면 0으로 처리된 레코드 건수가 에러 여부에 따라 명확
• UPDATE나 DELETE 문장은 쿼리의 성공적인 실행 여부를 업무적인 정상 처리 여부로 판단하기에는 부족
• 반드시 적용된 건수를 체크해보고 COMMIT 할 것 추천

 

16.10.12 숫자 값은 반드시 숫자 타입의 칼럼으로 정의

• 문자열 컬럼과 숫자(상수)를 비교하게 되면, 옵티마이저는 문자열 칼럼을 모두 숫자로 변환해서 비교 수행
• 풀 테이블 수행 OR 인덱스 풀 스캔 수행

 

16.13 스키마 검토

• 서비스의 기능을 업그레이드하거나 수정하다 보면 조인을 하는 칼럼끼리 타입 불일치가 발생하거나, 문자열 타입의 문자집합이나 콜레이션 등의 문제가 있어 쿼리의 성능이나 데이터의 일관성이 손상될 수 있다
• ERD를 직접 하나씩 점검하는 것이 가장 좋겠지만, 시간적인 문제로 어렵다면 MySQL 딕셔너리 정보를 확인해 보는 방법도 있다

SELECT table_schema, table_name, column_name, column_type, character_set_name, collation_name FROM information_schema.columns WHERE table_schema = 'employees' AND column_name LIKE '%dept_no%';