IT LAB

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다. 스토어드 프로그램은 스토어드 루틴이라 하며, 절차적인 처리를 위해 제공됨스토어드 프로그램은 스토어드 프로시저, 스토어드 함수, 트리거, 이벤트 등을 모두 아우르는 명칭임모두 똑같은 문법으로 작성할 수 있음1. 장단점장점보안 향상입력값의 유효성을 체크한 후 동적인 SQL을 생성단위별로 실행 권한 부여 가능특정 테이블의 읽기, 쓰기특정 컬럼에 대해 권한 설정 기능의 추상화개발 언어나 도구와 관계없이 생성규칙을 알지 못해도 호출하여 발급받을 수 있음각각 다른 프로그래밍 언어로 생성규칙 적용하여 생성된 값을 각각의 소스코드에서 구현하지 않아도 됨 네트워크 소요 시간 절감애플리케이션에서 데이터베이스에 여러번 질의해야 할 경우, 스토어드 프로..

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.1. 레인지 파티션파티션 키의 연속된 범위로 파티션을 정의하는 방법MAXVALUE라는 키워드를 이용해 명시되지 않은 범위의 키 값이 담긴 레코드를 저장하는 파티션 정의 가능 용도날짜 기반 (연, 월, 일 단위로 분석)범위 기반 (여러 파티션에 균등하게 나눌 수 있을 때)파티션 키 위주로 검색이 자주 실행될 때 장점큰 테이블을 작은 크기의 파티션으로 분리 가능필요한 파티션만 접근 가능 생성create table tb_article ( article_id int not null auto_increment, reg_date datetime not null, primary key(article_id, reg_date)) partitio..

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다. 파티션 기능은 큰 테이블을 논리적으로 여러 개의 작은 단위로 나누어 저장하는 기능입니다. 1. 개요파티션을 사용하는 이유단일 INSERT와 단일 또는 범위 SELECT의 빠른 처리파티션하지 않으면 인덱스도 커지고 그만큼 메모리 공간도 많이 필요해짐인덱스 크기가 메모리 사이즈보다 커지면 연산 시 성능이 심각하게 저하됨파티션은 데이터와 인덱스를 조각화해서 물리적 메모리를 효율적으로 사용할 수 있게 만들어줌 데이터의 물리적인 저장소를 분리데이터나 인덱스가 파일 시스템에서 차지하는 공간이 크다면 백업이나 관리작업이 어려워짐단, 파티션별 인덱스를 따로 가지는 것은 지원하지 않음 이력 데이터의 효율적인 관리라이프 사이클이 짧음불필요해진 데이터..

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.  1. id 칼럼테이블들이 조인될 경우, 같은 id 값을 할당받음id 값은 테이블의 접근 순서를 의미하지 않음같은 id라면 먼저 올라온 것이 먼저 접근된 순서임을 의미함 2. select_type 칼럼각 단위 select 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼 select_type설명예제SIMPLE단순한 SELECT 쿼리로, 서브쿼리를 사용하지 않음. PRIMARYUNION이나 서브쿼리를 포함하는 쿼리에서 가장 바깥쪽의 SELECT 쿼리. UNIONUNION으로 결합된 SELECT 쿼리 중 두 번째 이후의 쿼리. DEPENDENT UNION서브쿼리 내에서 UNION 또는 UNION ALL로 결합된 테이블. UNION RESULT..

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.1. 출력 포맷단순 테이블 형태JSON (FORMAT 옵션)TREE (FORMAT 옵션) 2. 쿼리 실행 시간 확인EXPLAIN ANALYZE실행 계획 및 단계별 소요 시간 정보 확인 TREE 형태들여쓰기는 호출 순서를 의미함같은 레벨에서는 상단에 위치한 라인이 먼저 실행다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행 예시explain analyze select e.hire_date, avg(s.salary) from employees e inner join salaries s on s.emp_no = e.emp_no and s.salary > 50000 and s.from_date '1990-01-..

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다. 1. 테이블 및 인덱스 통계 정보통계 정보가 비용 기반 최적화에 가장 중요함 MySQL 서버의 통계 정보InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리할 수 있음서버가 재시작되어도 재사용이 가능함 통계 테이블 mysql.innodb_table_statsmysql.innodb_index_stats 통계 테이블 컬럼select * from mysql.innodb_index_stats where table_name ='employees';stat_name='d_diff_pfx01': 인덱스가 가진 유니크한 값의 개수stat_name='n_leaf_pages': 인덱스의 리프 노드 페이지 개수stat_nam..

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.1. 인덱스 힌트STRAIGHT_JOIN조인 순서를 고정하는 역할을 담당함from 절에 명시된 테이블의 순서대로 조인을 수행하도록 유도함기본적으로 여러 개의 테이블이 조인되는 경우, 어떤 테이블이 드라이빙 테이블이 될지 모름 (통계 정보 기반으로 수립됨) 옵티마이저 힌트인 동시에 조인 키워드select straight_join e.first_name, e.last_name, d.dept_name from employees e, dept_emp de, departments d where e.emp_no=de.emp_no and d.dept_no=de.dept_no; select /*! straight_join */ e.f..

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다. 1. 옵티마이저 스위치 옵션조인 옵션optimizer_switch 시스템 변수옵티마이저 스위치 옵션 등록을 위한 키로 사용됨 MRR과 배치 키 액세스 (batch key access)Multi-Range Read네스티드 루프 조인의 단점을 보완함 (레코드를 매번 새롭게 랜덤 액세스하므로 최적화하여 읽을 수 없음)조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링함조인 버퍼에 레코드가 가득차면, MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한번에 요청함읽어야 할 레코드를 정렬된 순서로 접근해서 디스크 읽기를 최소화 할 수 있음배치 키 엑세스MRR을 응용해서 실행되는 조인 방식부가적인 정렬 작업이 필요함 (성능..

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.1. 개요쿼리 실행 절차SQL ParserSQL 문장을 잘게 쪼개서 Parse Tree 생성 (MySQL 서버가 이해할 수 있음)SQL Parser라는 모듈이 담당함문법 검사최적화 및 실행 계획 수립Parse Tree를 참조하여 결정함불필요한 조건 및 복잡한 연산의 단순화어떤 테이블을 먼저 읽을 것인지인덱스 통계 정보를 이용해 사용할 인덱스 결정가져온 레코드들을 임시 테이블에 넣고 추가로 가공해야 할지 여부 결정스토리지 엔진으로부터 데이터를 가져옴 옵티마이저 종류데이터베이스 서버에서 두뇌와 같은 역할최적의 실행 계획을 수립함 비용 기반 최적화 쿼리를 처리하기 위한 여러 가지 방법을 만듬실행 계획별 비용을 산출 (각 단위 작업의 비용 ..

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.1. 테이블 암호화일반적인 테이블 생성 구문과 동일 암호화테이블 생성 ENCRYPTION 옵션 추가하기default_table_encrtyption 시스템 변수 활성화하기 응용 프로그램 암호화버퍼 풀에 데이터가 암호화되어 적재됨인덱스를 사용할 수 없음평문의 내용을 바로 확인할 수 없음 테이블스페이스 이동테이블의 데이터 파일만 백업했다가 복구 Flush 명령어FLUSH TABLES source_table FOR EXPORT;source_table 구조를 source_table.cfg에 저장source_table.ibd와 source_table.cfg를 목적지 서버로 복사UNLOCK TABLES 명령어를 수행하여 잠금 풀기 2. 언두 로..