Real MySQL 7장. 쿼리 작성 및 최적화

Real MySQL 7장. 쿼리 작성 및 최적화  시스템프로그래밍  2013.03.19. 21:33  수정  삭제 http://blog.naver.com/pwk0810/40184451766 전용뷰어 보기

 

 DDL (Data Definition Language )    - data base 나 테이블의 구조를 변경하기 위한 문장

 DML(Data Maniplation Language ) - table 의 데이터를 조작하기 위한 문장

 

쿼리를 빠르게 수행되게 하려면 쿼리가 어떻게 데이터르 가져올지 예측할수 있어야 한다.

SQL 처리에서 어떻게 (HOW) 를 이해하고, 쿼리를 작성하는것이 그만큼 중요하다는 것.

 

 

7.1 쿼리와연결된 시스템 설정.

 

7.1.1 SQL MODE

 

•  STRICT_ALL-TABLES
• sql_mode 시스템 변수에 STRICT_ALL_TABLE 가 설정되면 칼럼의 정해진 길이보다 큰값을 저장할 때 경고가 아닌 오류가 발생 쿼리 실행이 중지
• STRICT_TRANS_TABLES
• 설정하면 원하지 않는 데이터 타입의 변환이 필요할때 MySQL 서버는 강제 형변환을 하지 않고 에러를 발생시킨다.
•  TRADITIONAL
• 
  
• ANTI_QUOTES
• 설정하면 홑따옴표만 문자열 값 표기로 사용할수 있고, 쌍따옴표는 칼럼명이나 테이블명과 같은 식별자 를 표기하는데만 사용
• ONLY_FULL_GROUP_BY
• SELECT 절이나 HAVING 절에 사용할 수 있다. 

 

7.1.2 영문 대소문자 구분

 

•  lower_case_table_names 
• 1 :모두 소문자로만 저장하고 서버가 대소문자를 구분하지 않게 해줌.
• 2 : 저장은 대소문자를 구분해서 하지만, 쿼리에서는 대소문자를 구별하지 않게 해줌.
• 설정 자체를 떠나서 초기 DB 나 테이블을 생성할 때 대문자 또는 소문자만으로 통일해서 사용하는 편이 좋다.

7.1.3 MYSQL 예약어

• 테이블을 생성할때는 항상 역따옴표로 테이블이나 칼럼의 이름을 둘러싸지 않은 상태로 생성하길 권장 - 그래야만 이름이 예약어인지 아닌지를 서버가 에러로 알려주기 때문이다.

 

7.2 MYSQL 문법 표기를 읽는 방법

7.3 MYSQL 연산자와 내장 함수

• DBMS 의 내장함수는 거의 같은 기능을 제공하지만 이름이 호환되는 것은 거의 없다.

7.3.1 리터럴 표기법

• SQL 표준에서 문자열은 항상 홑따옴표를 사용해서 표시한다.
• SELECT *FROM department WHERE dept_no = 'd001';
• SELECT *FROM department WHERE dept_no = "d001"

 

숫자

• 다른 DBMS 와 마찬가지로 따옴표 없이 숫자값을 입력하면 된다.
• 주어진 상수값을 숫자로 변환하는데,  이때는 상수값 하나만 변환하므로 성능과 관련된 문제가 발생하지 않는다.

 

7.3.2 MYSQL 연산자

 

• 동등 비교 = , <=>
• NULL은 IS NULL 연산자 이외에는 비교할 방법이 없다.
• 부정 비교 <> , !=
• 가독성을 위해 통일해서 사용하길 권장
• NOT 연산자 !
• AND && OR ( || )
• 나누기 ( /, DIV ) 와 나머지 (% , MOD ) 연산자
• / 연산자를 사용
• LIKE 연산자
• REGEXP 연산자보다는 훨씬 단순한 문자열 패턴 비교 연산자. 많이 사용
• 와일드 카드 문자는 % , _ 가 전부
• 'CHrist" 로 시작하는 이름을 검색하려면 인덱스 레인지 스캔을 이용해서 검색할 수 있다.
• 하지만 "rstina" 으로 끝나는 이름을 검색할 때는 와일드 카드가 검색어의 앞쪽에 있으면 인덱스의 left most 특성으로 인해 레인지 스캔을 사용하지 못하고 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔 방식으로 쿼리가 처리됨을 알수 있다
• BETWEEN 연산자
• SELECT *FROM dept_emp WHERE dept_no = 'd003' AND emp_no = 10001;
• 조건 모두 인덱스를 이용해 범위를 줄여주는 방법으로 사용할 수 있다. 
• SELECT *FROM dept_emp WHERE dept_no BETWEEN 'd003' AND 'd0005' AND emp_no = 10001;
• BETWEEN 은 > , < 연산자와 같이 , 범위를 읽어야 하는 연산자라서 모든 범위를 검색해야 한다.  결국 BETWEEN 이 사용된 두번째 쿼리에서 emp_no = 10001 조건은 비교 범위를 줄이는 역할을 하지 못한다.
• BETWEEN  은 크다와 작다 비교를 하나로 묶어둔것에 가깝고
• IN은 여러개의 동등 비교를 하나로 묶은 것과 같은 연산자라서 동등 비교 연산자에 가깝다.
• SELECT *FROM dept_emp WHERE dept_no IN ( 'd003' , 'd004', 'd005' ) AND emp_no = 10001;
• between 이 선형으로 인덱스를 검색해야 하는것과는 달리 in 은 동등 비교를 여러번 수행하는 것과 같은 효과가 있기 때문에 , dept_emp 테이블의 인덱스를 최적으로 사용할 수 있는 것.
• IN 연산자에 사용할 상수값을 한번 더 실행해서 IN으로 변경했을 때 그만큼 효율이 있는지 테스트 해 볼 필요가 있다.
• SELECT *FROM dept_mep WHERE dept_no IN(SELECT dept_no FROM departments WHERE dept_no BETWEEN 'd003' AND 'd005' ) AND emp_no = 10001;
•  IN 연산자
• 여러개의 값이 비교되지만 범위로 검색하는 것이 아니라 여러번의 동등 비교로 실행되기 때문에 일반적으로 빠르게 처리된다.
• IN 연산자에 상수값을 입력해서 전달하는 경우는 다른 DBMS만큼 최적화 해서 수행할수 있다.  하지만 IN 연산자의 입력이 상수가 아니라 서브 쿼리인 경우는 상당히 느려질수 있다.

7.3.3 MYSQL 내장 함수

• NULL 값 비교 및 대체 (IFNULL, ISNULL )
• IFNULL () 칼럼이나 표현식의 값이 NULL 인지 비교하고, NULL 이면 다른 값으로 대체하는 용도로 사용할수 있는 함수
• 현재 시각 조회 (NOW, SYSDATE)
• 문자열 처리 (RPAD, LPAD /RTRIM , LTRIM , TRIM )
• RPAD() 와 LPAD() 함수는 문자열의 좌측 또는 우측 에 문자에 덧붙여서 지정된 길이의 문자열을 만드는 함수이다.
• RTRIM() LTRIM()함수는 문자열의 우측 또는 좌측에 연속된 공백 문자를 제거하는 함수
• 문자열 결합 (CONCAT )
• 여러개의 문자열을 연결해서 하나의 문자열로 반환하는 함수
• mysql> SELECT CONCAT('george' , 'michael') as name;
• +---------------+
• | name          |
• +---------------+
• | georgemichael |
• +---------------+
• 1 row in set (0.00 sec)
• 
  
• mysql> SELECT CONCAT('george ' , 'michael') as name;
• +----------------+
• | name           |
• +----------------+
• | george michael |
• +----------------+
• 1 row in set (0.00 sec)
• 
  
• mysql> SELECT CONCAT('george ' , 'michael' , 'is ' 'singer') as name;
• +-------------------------+
• | name                    |
• +-------------------------+
• | george michaelis singer |
• +-------------------------+
• 1 row in set (0.00 sec)
• 
  
• GROUP BY 문자열 결합 ( GROUP_CONCAT)
• GROUP_CONCAT() 함수는 값들을 먼저 정렬한 후 연결하거나 각 값의 구분자 설정도 가능
• mysql> select group_concat(dept_no) from departments;
• +----------------------------------------------+
• | group_concat(dept_no)                        |
• +----------------------------------------------+
• | d001,d002,d003,d004,d005,d006,d007,d008,d009 |
• +----------------------------------------------+
• 1 row in set (0.02 sec)
• 
  
• mysql> select group_concat(dept_no SEPARATOR '|') FROM departments;
• +----------------------------------------------+
• | group_concat(dept_no SEPARATOR '|')          |
• +----------------------------------------------+
• | d001|d002|d003|d004|d005|d006|d007|d008|d009 |
• +----------------------------------------------+
• 1 row in set (0.00 sec)
• 
  
• mysql> select group_concat(distinct dept_no order by dept_name desc) from depart
• ments;
• +--------------------------------------------------------+
• | group_concat(distinct dept_no order by dept_name desc) |
• +--------------------------------------------------------+
• | d007,d008,d006,d004,d001,d003,d002,d005,d009           |
• +--------------------------------------------------------+
• 1 row in set (0.02 sec)
• 값의 비교와 대체( CASE WHEN .. THEN )
• mysql> SELECT emp_no, first_name , CASE gender WHEN 'M' THEN 'Man'
•     -> WHEN 'F' THEN 'Woman' ELSE 'Unknown' END AS gender FROM employees LIMIT 10;
• +--------+------------+--------+
• | emp_no | first_name | gender |
• +--------+------------+--------+
• |  10001 | Georgi     | Man    |
• |  10002 | Bezalel    | Woman  |
• |  10003 | Parto      | Man    |
• |  10004 | Chirstian  | Man    |
• |  10005 | Kyoichi    | Man    |
• |  10006 | Anneke     | Woman  |
• |  10007 | Tzvetan    | Woman  |
• |  10008 | Saniya     | Man    |
• |  10009 | Sumant     | Woman  |
• |  10010 | Duangkaew  | Woman  |
• +--------+------------+--------+
• 10 rows in set (0.02 sec)
• 
  
• CAE WHEN 절이 일치하는 경우에만 THEN 이하의 표현식이 실행된다는 점.
• 
  
• 타입의 변환 ( CAST , CONVERT )
• SQL 은 텍스트 기반으로 작동하기 때문에 SQL 에 포함된 모든 입력값은 문자열 처럼 취급
• 암호화 및 해시 함수 ( MD5 , SHA )
• MD5와 SHA 는 모두 비대칭형 암호화 알고리즘
• mysql> select md5('abcd');
• +----------------------------------+
• | md5('abcd')                      |
• +----------------------------------+
• | e2fc714c4727ee9395f324cd2e7f331f |
• +----------------------------------+
• 1 row in set (0.06 sec)
• 
  
• mysql> select sha('adcd');
• +------------------------------------------+
• | sha('adcd')                              |
• +------------------------------------------+
• | bcc2e338ddfc6581d6f7d22d375b3a203a048c34 |
• +------------------------------------------+
• 
  
• 처리 대기 ( sleep )
• 벤치 마크 (benchmark)
• 디버깅이나 간단한 함수의 성능 테스트 용으로 아주 유용.
• mysql> select benchmark(10000, md5('abcdefghijklmn'));
• +-----------------------------------------+
• | benchmark(10000, md5('abcdefghijklmn')) |
• +-----------------------------------------+
• |                                       0 |
• +-----------------------------------------+
• 1 row in set (0.08 sec)
• 
  
• mysql> select benchmark(100, (select count(*) from employees));
• +--------------------------------------------------+
• | benchmark(100, (select count(*) from employees)) |
• +--------------------------------------------------+
• |                                                0 |
• +--------------------------------------------------+
• 1 row in set (1.11 sec)
• 
  
• 두개의 동일한 기능을 상대적으로 비교 분석하는 용도로 사용할 것을 권장.
• 
  
• VALUES() 
• COUNT() 
• 결과 레코드 건수를 반환하는 함수

7.3.4 SQL 주석

• -- 한라인만 주석
• /* */ 여러라인 주석
• # 유닉스 쉘 스크립트 처럼 # 문자를 사용해서 한라인을 주석으로 처리할 수 있음

7.4 SELECT 

• INSERT 나 UPDATE 와 같은 작업은 거의 레코드 단위로 발생하므로 성능상 문제가 되는 경우는 별로 없다. 
• SELECT는 여러개의 테이블로 부터 데이터를 조합해서 빠르게 가져와야 하기 때문에 여러개의 테이블 을 어떻게 읽을 것인가에 많은 주의를 기울여야 한다.

7.4.1 SELECT 각 절의 처리 순서

• mysql> SELECT s.emp_no , COUNT(distinct e.first_name) AS cnt
•     -> FROM salaries s  INNER JOIN employees e ON e.emp_no = s.emp_no
•     -> WHERE s.emp_no IN ( 100001, 100002)
•     -> GROUP BY s.emp_no
•     -> HAVING AVG(s.salary) > 1000
•     -> ORDER BY AVG(s.salary)
•     -> LIMIT  10;
• +--------+-----+
• | emp_no | cnt |
• +--------+-----+
• | 100001 |   1 |
• | 100002 |   1 |
• +--------+-----+
• 
  
• SELECT 절 : SELECT s.emp_no, COUNT (DISTINCT e.first_name) AS cnt
• FROM 절 :  FROM salaries s  INNER JOIN employees e ON e.emp_no = s.emp_no 
• WHERE 절 :  WHERE s.emp_no IN ( 100001, 100002)
• GROUP BY 절 :  GROUP BY s.emp_no
• HAVING 절 : HAVING AVG(s.salary) > 1000
• ORDER BY 절 :ORDER BY AVG(s.salary)
• LIMIT 절 : LIMIT  10;

7.4.2 WHERE  절과 GROUP BY 절, 그리고 ORDER BY 절 인덱스 사용

• WHERE 절 뿐만 아니라 GROUP BY 나 ORDER BY 절도 인덱스를 이용해 빠르게 처리할 수 있다.

+ 인덱스를 사용하기 위한 기본 규칙

• WHERE 절이나 ORDER BY 또는 GROUP BY 가 인덱스를 사용하려면 기본적으로 인덱스된 칼럼의 값 자체를 변환하지 안혹 그대로 사용한다는 조건을 만족해야
• 인덱스는 칼러의 값을 아무런 변환 없이 B-TREE에 정렬해서 저장
• 인덱스는 salary 칼럼으로 만들어져 있는데 where 절과 같이 salary 칼럼을 가공한후 다른 상수값과 비교한다면 이 쿼리는 인덱스를 적절히 이용하지 못하게 된다.
• SELECT *FROM salaries WHERE sarlary*10 > 150000;

+ WHERE 절에서의 인덱스의 사용

• WHERE 절에서는 각 조건이 AND로 연결되면 읽어와야 할 레코드의 건수를 줄이는 역할을 하지만 각 조건이 OR 로 연결되면 읽어서 비교해야 할 레코드가 더 늘어나기 때문에 WHERE 조건에 OR 연산자가 있다면 주의 해야 한다.

+ GROUP BY 절에 인덱스의 사용

• GROUP BY 절의 각 칼럼은 비교 연산자를 가지지 않으므로 범위 제한 조건이나 체크 조건과 같이 구분해서 생각할 필요는 없다.
• GROUP BY 절 에 명시된 칼럼의 순서가 인덱스를 구성하는 칼럼의 순서와 같으면 GROUP BY 절은 일단 인덱스를 이용할 수 있다.
• GROUP BY 절에 명시도니 칼럼이 인덱스 칼럼의 순서와 위치가 같아야 한다.
• 인덱스를 구성하는 칼럼 중에서 뒷쪽에 있는 칼럼은 GROUP BY 절에 명시 되지 않아도 인덱스의 앞쪽에 있는 칼럼이 GROUP BY 절에 명시 되지 않으면 인덱스를 사용할 수 없다.
• WHERE 조건과 달리, GROUP BY 절에 명시된 칼럼이 하나라도 인덱스에 없으면 GROUP BY 절은 전혀 인덱스를 이용하지 못한다.

+ ORDER BY 절의 인덱스 사용

+WHERE 조건과 ORDER BY (또는 GROUP BY) 절의 인덱스 사용

• 쿼리에 WHERE 절만 또는 GROUP BY  나 ORDER BY 절만 포함돼 있다면 사용된 절 하나에만 초점을 맞춰서 인덱스를 사용할 수 있게 튜닝하면 된다.
• WHERE 절과 ORDER BY 절이 같이 사용된 하나의 쿼리 문장은 다음 3가지 중 한가지 바법으로만 인덱스를 이용한다.
• WHERE 절과 ORDER BY 절이 동시에 같은 인덱스를 사용
• WHERE 절의 비교 조건에서 사용하는 칼럼과 ORDER BY 절의 정렬 대상 칼럼이 모두 하나의 인덱스에 포함되 있을때.
• 나머지 2가지 방식보다 훨씬 빠른 성능을 보이기 때문에 가능하다면 이방식으로 처리할 수 있게 쿼리를 튜닝하거나 인덱스를 생성
• WHERE 절만 인덱스를 이용
• ORDER BY 절은 인덱스를 이용한 정렬이 불가능하며,  인덱스를 통해 검색된 겨로가 레코드를 별도의 정렬 처리 과정 (File sort )을 거쳐서 정렬을 수행
• WEHRE 절의 조건에 일치하는 레코드의 건수가 많지 않을때 효율적인 방식
• ORDER BY 절만 인덱스를 이용
• 이 방식은 ORDER BY절의 순서대로 인덱스를 읽으면서, 레코드 한건씩을 WHERE 저의 조건에 일치하는지 비교해 일치하지 않ㅇ르때는 버리는 형태로 처리
• 아주 많은 레코드를 조회해서 정렬해야 할때는 이런 형태로 튜닝

+ GROUP BY 절과 ORDER BY 절의 인덱스 사용

• GROUP BY 절과 ORDER BY 절이 동시에 사용된 쿼리에서 두 절이 모두 하나의 인덱스를 사용해서 처리 되려면 GROUP BY 절에 명시된 칼럼과 ORDER BY 절에 명시도니 칼럼이 순서와 내용이 모두 같아야 한다.

+ WHERE 조건과 ORDER BY 절 , 그리고 GROUP BY 절의 인덱스 사용

 

1. WHERE 절이 인덱스를 사용할 수 있는가?
2. GROUP BY 절이 인덱스를 사용할 수 있는가?
3. GROUP BY 절과 ORDER BY 절이 동시에 인덱스를 사용할 수 있는가?

7.4.3 WHERE 절의 비교 조건 사용시 주의 사항.

+ NULL 비교

• 
  

 

7.4.6 JOIN

+ 조인 순서와 인덱스 

인덱스 레인지 스캔으로 레코드를 읽는 작업을 다시 한번 간단 정리하면

 

• 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다.  이 과정을 인덱스 탐색이라고 한다.
• 1번에서 탐색된 위치 부터 필ㅇ한 만큼 인덱스를 죽 읽는다.  이 과정을 인덱스 스캔이라고 한다.
• 2번에서 읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 , 최종 레코드를 읽는다.
• 
  

 SELECT * FROM employees e, dept_emp de WHERE e.emp_no = de.emp_no;

 

두칼럼 모두 각각 인덱스가 있는 경우

 

• 두 칼럼에 인덱스가 모두 준비되어 있을때는 어느 테이블을 드라이빙으로 선택하든 인덱스를 이용해 드리븐 테이블의 검색 작업을 빠르게 수행 할 수 있다. 
• 옵티마이저가 통계 정보를 이용해 적절히 드라이빙 테이블을 선택

employyes.emp_no 에만 인덱스가 있는 경우

• dept_emp 테이블이 드리븐 테이블로 선택된다면 employees 테이블의 레코드 건수만큼 dept_emp 테이블을 풀스캔해야만 "e.emp_no = de.emp_no " 조건에 일치하는 레코드를 찾을 수 있다.
• 옵티마이저는 dept_emp 테이블을 드라이빙 테이블로 선택하고 , employees 테이블을 드라븐 테이블로 선택

dept_emp.emp_no 에만 인덱스가 있는 경우

두 칼럼 모두 인덱스가 없는 경우

• 드리븐 테이블을 풀스캔하는 경우

+ JOIN 칼럼의 데이터 타입

+ OUTER JOIN 주의 사항