MySQL 전문가가 전하는 MySQL 활용법!



『Real MySQL』은 기본적인 SQL 문법 소개나 매뉴얼의 번역 수준에서 나아가, MySQL을 이용하는 애플리케이션 개발이나 운영을 통해 얻은 경험과 지식, 그리고 반드시 알고 있어야 할 주의사항을 중점적으로 다룬 책이다. 많은 그림과 벤치마크 결과를 함께 수록해 MySQL을 처음 접하는 사용자도 쉽게 이해할 수 있게 구성했다. 본문은 ‘MySQL 서버의 아키텍처와 MySQL 설치’부터 ‘인덱스의 종류와 구조 및 활용’, ‘MySQL 서버와 연동하는 프로그램 개발’, ‘데이터 모델링 및 최적의 데이터 타입 선정’ 등을 다룬다.

▣ 01장_소개                                                                           

  1.1 MySQL 소개                                                                  
  1.2 왜 MySQL인가?                                                               

▣ 02장_설치와 설정

  2.1 MySQL 다운로드                                                              
  2.2 MySQL 서버 설치                                                             
____2.2.1 리눅스에 설치                                                           
____2.2.2 윈도우에 설치(MSI)                                                     
  2.3 서버 설정                                                                   
____2.3.1 설정 파일의 구성                                                        
____2.3.2 MySQL 시스템 변수의 특징                                                
____2.3.3 글로벌 변수와 세션 변수                                                 
____2.3.4 동적 변수와 정적 변수                                                   
____2.3.5 my.cnf 설정 파일                                                        
  2.4 MySQL 서버의 시작과 종료                                                    
____2.4.1 시작과 종료                                                             
____2.4.2 서버 연결 테스트                                                        
  2.5 MySQL 복제 구축                                                             
____2.5.1 설정 준비                                                               
____2.5.2 복제 계정 준비                                                          
____2.5.3 데이터 복사                                                             
____2.5.4 복제 시작                                                               
  2.6 권한 관리                                                                   
____2.6.1 사용자의 식별                                                           
____2.6.2 권한                                                                    
  2.7 예제 데이터 적재                                                            
  2.8 전문 검색을 위한 MySQL 설치                                                 
____2.8.1 MySQL의 내장 전문 검색 엔진                                             
____2.8.2 MySQL 5.0 버전의 트리톤 설치                                            
____2.8.3 MySQL 5.1 버전의 mGroonga 설치                                          
____2.8.5 mGroonga 설치 확인                                                      

▣ 03장_아키텍처

  3.1 MySQL 아키텍처                                                              
____3.1.1 MySQL의 전체 구조                                                       
____3.1.2 MySQL 쓰레딩 구조                                                       
____3.1.3 메모리 할당 및 사용 구조                                                
____3.1.4 플러그-인 스토리지 엔진 모델                                            
____3.1.5 쿼리 실행 구조                                                          
____3.1.6 복제(Replication)                                                       
____3.1.7 쿼리 캐시                                                               
  3.2 InnoDB 스토리지 엔진 아키텍처                                               
____3.2.1 InnoDB 스토리지 엔진의 특성                                             
____3.2.2 InnoDB 버퍼 풀                                                            
____3.2.3 언두(Undo) 로그                                                         
____3.2.4 인서트 버퍼(Insert Buffer)                                              
____3.2.5 리두(Redo) 로그 및 로그 버퍼                                            
____3.2.6 MVCC(Multi Version Concurrency Control)                                 
____3.2.7 잠금 없는 일관된 읽기(Non-locking consistent read)                      
____3.2.8 InnoDB와 MyISAM 스토리지 엔진 비교
____3.2.9 InnoDB와 MEMORY(HEAP)스토리지 엔진 비교                                 
  3.3 MyISAM 스토리지 엔진 아키텍처                                               
____3.3.1 키 캐시                                                                 
____3.3.2 운영체제의 캐시 및 버퍼                                                 
  3.4 MEMORY 스토리지 엔진 아키텍처                                               
____3.4.1 주의 사항                                                               
____3.4.2 MEMORY 스토리지 엔진의 용도                                             
  3.5 NDB 클러스터 스토리지 엔진                                                  
____3.5.1 NDB 클러스터의 특성                                                     
____3.5.2 NDB 클러스터의 아키텍처                                                 
____3.5.3 클러스터 간의 복제 구성                                                 
____3.5.4 NDB 클러스터의 성능                                                     
____3.5.5 NDB 클러스터의 네트워크 영향                                            
____3.5.6 NDB 클러스터의 용도                                                     
  3.6 TOKUDB 스토리지 엔진                                                        
____3.6.1 프랙탈 트리(Fractal Tree) 인덱스 지원
____3.6.2 대용량 데이터와 빠른 INSERT 처리                                        
____3.6.3 트랜잭션 및 잠금 처리                                                   
____3.6.4 그 이외의 특징                                                          
____3.6.5 TokuDB의 주 용도                                                        
  3.7 전문 검색 엔진                                                              
____3.7.1 트리톤 전문 검색 엔진                                                   
____3.7.2 mGroonga 전문 검색 엔진(플러그인)                                       
____3.7.3 스핑크스 전문 검색 엔진                                                 
  3.8 MySQL 로그 파일                                                             
____3.8.1 에러 로그 파일                                                          
____3.8.2 제너럴 쿼리 로그 파일(제너럴 로그 파일, General log)                    
____3.8.3 슬로우 쿼리 로그                                                        
____3.8.4 바이너리 로그와 릴레이 로그                                             

▣ 04장_트랜잭션과 잠금

  4.1 트랜잭션                                                                    
____4.1.1 MySQL에서의 트랜잭션                                                    
____4.1.2 주의사항                                                                
  4.2 MySQL 엔진의 잠금                                                           
____4.2.1 글로벌 락                                                               
____4.2.2 테이블 락(TABLE LOCK)                                                   
____4.2.3 유저 락(USER LOCK)                                                      
____4.2.4 네임 락                                                                 
  4.3 MyISAM과 MEMORY 스토리지 엔진의 잠금
____4.3.1 잠금 획득                                                               
____4.3.2 잠금 튜닝                                                               
____4.3.3 테이블 수준의 잠금 확인 및 해제                                         
  4.4 InnoDB 스토리지 엔진의 잠금                                                 
____4.4.1 InnoDB의 잠금 방식                                                      
____4.4.2 InnoDB의 잠금 종류                                                      
____4.4.3 인덱스와 잠금                                                           
____4.4.4 트랜잭션 격리 수준과 잠금                                               
____4.4.5 레코드 수준의 잠금 확인 및 해제                                         
  4.5 MySQL의 격리 수준                                                           
____4.5.1 READ UNCOMMITTED                                                        
____4.5.2 READ COMMITTED                                                          
____4.5.3 REPEATABLE READ                                                         
____4.5.4 SERIALIZABLE                                                            
____4.5.5 REPEATABLE READ 격리 수준과 READ COMMITTED 격리 수준의 성능 비교

▣ 05장_인덱스

  5.1 디스크 읽기 방식                                                            
____5.1.1 저장 매체                                                               
____5.1.2 디스크 드라이브와 솔리드 스테이트 드라이브                              
____5.1.3 랜덤 I/O와 순차 I/O                                                     
  5.2 인덱스란?                                                                   
  5.3 B-Tree 인덱스                                                               
____5.3.1 구조 및 특성                                                            
____5.3.2 B-Tree 인덱스 키 추가 및 삭제                                           
____5.3.3 B-Tree 인덱스 사용에 영향을미치는 요소                                  
____5.3.4 B-Tree 인덱스를 통한 데이터 읽기                                        
____5.3.5 다중 칼럼(Multi-column) 인덱스                                          
____5.3.6 B-Tree 인덱스의 정렬 및 스캔 방향                                       
____5.3.7 B-Tree 인덱스의 가용성과 효율성                                         
  5.4 해시(Hash) 인덱스                                                           
____5.4.1 구조 및 특성                                                            
____5.4.2 해시 인덱스의 가용성 및 효율성                                          
  5.5 R-Tree 인덱스                                                               
____5.5.1 구조 및 특성                                                            
____5.5.2 R-Tree 인덱스의 용도                                                    
  5.6 Fractal-Tree 인덱스                                                         
____5.6.1 Fractal-Tree의 특성                                                     
____5.6.2 Fractal-Tree의 가용성과 효율성                                          
  5.7 전문 검색(Full Text search) 인덱스                                          
____5.7.1 인덱스 알고리즘                                                         
____5.7.2 구분자와 n그램의 차이                                                   
____5.7.3 전문 검색 인덱스의 가용성                                               
  5.8 비트맵 인덱스와 함수 기반 인덱스                                            
  5.9 클러스터링 인덱스                                                           
____5.9.1 클러스터링 인덱스                                                       
____5.9.2 보조 인덱스(Secondary index)에 미치는 영향                              
____5.9.3 클러스터 인덱스의 장점과 단점                                           
____5.9.4 클러스터 테이블 사용시 주의사항                                         
  5.10 유니크 인덱스                                                              
____5.10.1 유니크 인덱스와 일반 보조 인덱스의 비교                                
____5.10.2 유니크 인덱스 사용 시 주의사항                                         
  5.11 외래키                                                                     
____5.11.1 자식 테이블의 변경이 대기하는 경우                                     
____5.11.2 부모 테이블의 변경 작업이 대기하는 경우                                
  5.12 기타 주의사항                                                              


  9.1 사용자 정의 변수 소개                                                       
  9.2 사용자 변수의 기본 활용                                                     
  9.3 사용자 변수의 적용 예제                                                     
____9.3.1 N 번째 레코드만 가져오기                                                
____9.3.2 누적 합계 구하기                                                        
____9.3.3 그룹별 랭킹 구하기                                                      
____9.3.4 랭킹 업데이트하기                                                       
____9.3.5 GROUP BY와 ORDER BY가 인덱스를 사용하지 못하는 쿼리                     
  9.4 주의사항                                                                    
  
▣ 10장_파티션

  10.1 파티션의 개요                                                              
____10.1.1 파티션을 사용하는 이유                                                 
____10.1.2 MySQL 파티션의 내부 처리                                               
  10.2 파티션 주의사항                                                            
____10.2.1 파티션의 제한 사항                                                     
____10.2.2 파티션 사용시 주의사항                                                 
  10.3 MySQL 파티션의 종류                                                        
____10.3.1 레인지 파티션                                                          
____10.3.2 리스트 파티션                                                          
____10.3.3 해시 파티션                                                            
____10.3.4 키 파티션                                                              
____10.3.5 리니어 해시 파티션/리니어 키 파티션                                    
____10.3.6 서브 파티션                                                            
____10.3.7 파티션 테이블의 실행 계획                                              
____10.3.8 파티션 테이블 관련 벤치마킹                                            
____10.3.9 파티션 기능에 대한 결론                                                
  
▣ 11장_스토어드 프로그램

  11.1 스토어드 프로그램의 장단점                                                 
____11.1.1 스토어드 프로그램의 장점                                               
____11.1.2 스토어드 프로그램의 단점                                               
  11.2 스토어드 프로그램의 문법                                                   
____11.2.1예제 테스트 시 주의사항                                                 
____11.2.2 스토어드 프로시저                                                      
____11.2.3 스토어드 함수                                                          
____11.2.4 트리거                                                                 
____11.2.5 이벤트                                                                 
____11.2.6 스토어드 프로그램 본문(Body) 작성                                      
  11.3 스토어드 프로그램의 권한 및 옵션                                           
____11.3.1 DEFINER 와 SQL SECURITY 옵션                                           
____11.3.2 스토어드 프로그램의 권한                                               
____11.3.3 DETERMINISTIC과 NOT DETERMINISTIC 옵션                                 
  11.4 스토어드 프로그램의 참고 및 주의사항                                       
____11.4.1 한글 처리                                                              
____11.4.2 스토어드 프로그램과 세션 변수                                          
____11.4.3 스토어드 프로시저와 재귀 호출(Recursive call)                          
____11.4.4 중첩된 커서 사용                                                       
  
▣ 12장_쿼리 종류별 잠금
 
  12.1 InnoDB의 기본 잠금 방식                                                    
____12.1.1 SELECT                                                                 
____12.1.2 INSERT, UPDATE, DELETE                                                 
  12.2 SQL 문장별로 사용하는 잠금                                                 
____12.2.1 SELECT 쿼리의 잠금                                                     
____12.2.2 INSERT 쿼리의 잠금                                                     
____12.2.3 UPDATE 쿼리의 잠금                                                     
____12.2.4 DELETE 쿼리의 잠금                                                     
____12.2.5 DDL 문장의 잠금                                                        
____12.2.6 InnoDB에서 여러 쿼리 패턴 간의 잠금 대기                               
  12.3 InnoDB에서 데드락 만들기                                                   
____12.3.1 패턴 1(상호 거래 관련)                                                 
____12.3.2 패턴 2(유니크 인덱스 관련)                                             
____12.3.3 패턴 3(외래키 관련)                                                    
____12.3.4 패턴 4(서로 다른 인덱스를 통한 잠금)                                   
  
▣ 13장_프로그램 연동

  13.1 자바                                                                       
____13.1.1 JDBC 버전                                                              
____13.1.2 MySQL Connector/J를 이용한 개발                                        
  13.2 C/C++                                                                      
____13.2.1 주요 헤더 파일과 MySQL 예제                                            
____13.2.2 에러 처리                                                              
____13.2.3 프로그램의 컴파일                                                      
____13.2.4 MySQL 서버 접속                                                        
____13.2.5 설정 파일 읽기                                                         
____13.2.6 SELECT 실행                                                            
____13.2.7 INSERT / UPDATE / DELETE 실행                                          
____13.2.8 다중 문장 실행과 다중 결과 셋 가져오기
____13.2.9 커넥션 옵션                                                            
____13.2.10 프리페어 스테이트먼트 사용                                            
  
▣ 14장_데이터 모델링

  14.1 논리 모델링                                                                
____14.1.1 모델링 용어                                                            
____14.1.2 용어집                                                                 
____14.1.3 엔터티                                                                 
____14.1.4 식별자(프라이머리 키)                                                  
____14.1.5 관계(릴레이션)                                                         
____14.1.6 엔터티의 통합                                                          
____14.1.7 관계의 통합                                                            
____14.1.8 모델 정규화                                                            
  14.2 물리 모델링                                                                
____14.2.1 프라이머리 키 선택                                                     
____14.2.2 데이터 타입 선정                                                       
____14.2.3 반정규화                                                               
  
▣ 15장_데이터 타입

  15.1 문자열(CHAR와 VARCHAR)                                                     
____15.1.1 저장 공간                                                              
____15.1.2 비교 방식                                                              
____15.1.3 문자집합(캐릭터 셋)                                                    
____15.1.4 콜레이션(Collation)                                                    
____15.1.5 문자열 이스케이프 처리                                                 
  15.2 숫자                                                                       
____15.2.1 정수                                                                   
____15.2.2 부동 소수점                                                            
____15.2.3 DECIMAL                                                                
____15.2.4 정수 타입의 칼럼을 생성할 때의 주의사항                                
____15.2.5 자동 증가(AUTO_INCREMENT) 옵션 사용                                    
  15.3 날짜와 시간                                                                
____15.3.1 TIMESTAMP 타입의 옵션                                                  
____15.3.2 타임 존 등록 및 사용                                                   
  15.4 ENUM과 SET                                                                 
____15.4.1 ENUM                                                                   
____15.4.2 SET                                                                    
  15.5 TEXT, BLOB                                                                 
  15.6 공간(Spatial) 데이터 타입                                                  
____15.6.1 POINT 타입                                                             
____15.6.2 LINESTRING 타입                                                        
____15.6.3 POLYGON 타입                                                           
____15.6.4 GEOMETRY 타입                                                          
  
▣ 16장_베스트 프랙티스

  16.1 임의(랜덤) 정렬                                                            
____16.1.1 지금까지의 구현                                                        
____16.1.2 인덱스를 이용한 임의 정렬 방법                                         
  16.2 페이징 쿼리                                                                
____16.2.1 지금까지의 방법                                                        
____16.2.2 불필요한 접근을 제거하기 위한 페이징 쿼리                              
  16.3 MySQL에서 시퀀스 구현                                                      
____16.3.1 시퀀스용 테이블 준비                                                   
____16.3.2 시퀀스를 위한 스토어드 함수                                            
____16.3.3 여러 시퀀스 처리하기                                                   
____16.3.4 시퀀스 사용 시 주의사항                                                
  16.4 큰 문자열 칼럼의 인덱스(해시)                                              
  16.5 테이블 파티션                                                              
  16.6 SNS의 타임라인 구현                                                        
____16.6.1 예제 시나리오                                                          
____16.6.2 인덱스 테이블 사용                                                     
____16.6.3 Try & Fail 쿼리                                                        
  16.7 MySQL 표준 설정                                                            
____16.7.1 MySQL 표준 설정의 필요성                                               
____16.7.2 표준 설정의 예시                                                       
  16.8 복제를 사용하지 않는 MySQL의 설정                                          
  16.9 MySQL 복제 구축                                                            
____16.9.1 MySQL 복제의 형태                                                      
____16.9.2 확장(스케일 아웃)                                                      
____16.9.3 가용성                                                                 
____16.9.4 복제가 구축된 MySQL에서의 작업                                         
  16.10 SQL 작성 표준                                                             
____16.10.1 조인 조건은 항상 ON 절에 기재                                         
____16.10.2 테이블 별칭(Alias)