권장사항이다. 이것을 이해하면 당신의 어플리케이션이 더 나은 성능을 발휘할 것이다.

다만 이것이 사람의 실력을 판단하는 척도로 사용되서는 안 될 것이다.

작게 생각하기

- 조만간 규모가 커질거라면 MySQL ecosystem을 봐야된다.
- 그리고 캐싱 빡시게 안 하는 메이저 웹사이트는 없다.
- develooper.com의 Hansen PT랑 Ilia 튜토리얼 볼 것
- 처음부터 확장 가능하게 아키텍처 잘 쪼개놔야된다.
- 복제랑 파티셔닝 어떻게 할지 미리 계획 세워놔라.
- 파일 기반 세션 좀 쓰지마 -_-
- 그렇다고 너무 쓸데없이 크게 생각하지도 말 것
- 특히 성능하고 확장성 구분 못 하면 난감함

EXPLAIN 안 써보기

- SELECT 앞에 EXPLAIN 이라고 붙이기만 하면 되는 것을 (..)
- 실행 계획 확인
- 타입 컬럼에 index 써있는거랑 Extra 컬럼에 index 써있는거랑 “매우 큰” 차이 있음
* 타입에 있으면 Full 인덱스 스캔 (안 좋다.)
* Extra 컬럼에 있으면 Covering 인덱스 찾았다는 의미임 (좋다!)
- 5.0 이후부터는 index_merge 최적화도 한다.

잘못된 데이터 타입 선택

- 한 메모리 블럭 단위에 인덱스 레코드가 많이 들어갈수록 쿼리가 빨리 실행될 것이다. (중요)
- 아.. 정규화 좀 해 -_-… (이거 정말 충격과 공포인 듯)
- 가장 작은 데이터 타입을 써.. (진짜 BIGINT가 필요하냐고..)
- 인덱스 걸리는 필드는 정말 최소한으로 데이터 크기를 써야된다고.
- IP는 INT UNSIGNED로 저장해!! (아주 공감)
* 이럴 때 쓰라고 INET_ATON 함수가 아예 내장되어 있음.

PHP에서 pconnect 쓰는 짓

- 아파치에서 좀비 프로세스라도 생기면 그 커넥션은 그냥 증발하는거야..
- 어차피 MySQL 접속 속도는 Oracle이나 PostgreSQL 보다 10~100배 빠르다고.

너무 과도한 DB 추상화 계층을 두는 것
- 어디 포팅 열심히 할 거 아니면 추상화 계층 쓰지마 (ADODB, MDB2, PearDB 등)
- scale out 가능한걸 쓰라고.

스토리지 엔진 이해 못 하는 것

- 단일 엔진만으로 전체 아키텍처를 결정했다면 대부분 최적이 아님
- 엔진 별 장단점을 공부할 것
- ARCHIVE : zlib으로 압축해주고 UPDATE 안 되고 로그 Bulk Insert에 유용함.
- MEMORY : 서버 재시작하면 증발. 인덱스가 HASH나 BTREE로 가능함. 임시, 요약 데이터에 사용.
* 주간 top X 테이블 같은 것.
* 하여튼 메모리에 박아넣고 싶은 데이터 있으면..

인덱스 레이아웃 이해 못 하는 것

- 제대로 인덱스랑 스토리지 엔진 선택하려면 공부 좀 해
- 엔진은 데이터와 인덱스 레코드를 메모리나 디스크에 레이아웃하는 걸 구현한 것
- clustered 구성은 데이터를 PK 순서에 따라 저장함.
- non-clustered 구성은 인덱스만 순서대로 저장하고 데이터는 순서 가정하지 않음.
- clustered에서는 인덱스만 타면 추가적인 조회 없이 바로 데이터 가져오는 것임.
- 그래서 clustered PK는 작은 놈으로 할 필요가 있다는거
* 다른 인덱스는 각 레코드마다 PK를 앞에 더 붙이게 되니까.
* PK 지정 안 하면 아무렇게나 해버림

쿼리 캐시 이해 못 하는 것

- 어플리케이션 read/write 비율은 알고 있어야지
- 쿼리 캐시 설계는 CPU 사용과 읽기 성능 간의 타협
- 쿼리 캐시 크기를 늘린다고 읽기 성능이 좋아지는게 아님. heavy read라도 마찬가지.
- 과도한 CPU 사용을 막기 위해 무효화 할 때는 캐시 항목들을 뭉텅이로 날려버림
- 한마디로 SELECT가 참조하는 테이블 데이터 하나라도 변경되면 그 테이블 캐시는 다 날라간다는 얘기임
- 수직 테이블 파티셔닝으로 처방
* Product와 ProductCount를 쪼갠다든지..
* 자주 변하는 것과 변하지 않는 것을 쪼개는게 중요하다 이 말임.

Stored Procedure를 쓰는 것

- 무조건 쓰면 안 된다는게 아니고..
- 컴파일 할 때 무슨 일이 일어나는지 이해 못 하고 쓰면 재앙이 된다 이 말.
- 다른 RDBMS랑 다르게 connection thread에서 실행 계획이 세워짐.
- 이게 뭔 얘기냐 하면 데이터 한 번 가져오고 연결 끊으면 그냥 CPU 낭비 (7~8% 정도)하는 꼴이라는 것.
- 웬만하면 Prepared 구문과 Dynamic SQL을 써라.. 아래 경우를 제외하고
* ETL 타입 프로시저
* 아주아주 복잡하지만 자주 실행되지는 않는 것
* 한 번 요청할 때마다 여러번 실행되는 간단한 것 (연결한 상태로 여러번 써야 된다니까)

인덱스 컬럼에 함수 쓰는 것

- 함수에 인덱스 컬럼 넣어 호출하면 당연히 인덱스 못 탄다
- 함수를 먼저 계산해서 상수로 만든 다음에 = 로 연결해야 인덱스 탈 수 있다.
* 여기 실행 계획 보면 LIKE도 range type 인덱스 타는 것 보임

인덱스 빼먹거나 쓸모없는 인덱스 만들어 놓는 것

- 인덱스 분포도(selectivity)가 허접하면 안 쓴다.
- S = d/n
* d = 서로 다른 값의 수 (# of distinct values)
* n = 테이블의 전체 레코드 수
- 쓸모없는 인덱스는 INSERT/UPDATE/DELETE를 느리게 할 뿐..
- FK는 무조건 인덱스 걸어라. (물론 FK 제약 걸면 인덱스 자동으로 생긴다.)
- WHERE나 GROUP BY 표현식에서 쓰이는 컬럼은 인덱스 추가를 고려할 것
- covering index 사용을 고려할 것
- 인덱스 컬럼 순서에 유의할 것!

join 안 쓰는 짓

- 서브쿼리는 join으로 재작성해라
- 커서 제거해라
- 좋은 Mysql 성능을 내려면 기본
- 집합 기반으로 생각해야지 루프 돌리는거 생각하면 안 된다.

Deep Scan 고려하지 않는 것

- 검색엔진 크러울러가 쓸고 지나갈 수 있다.
- 이 경우 계속해서 전체 집합을 정렬한 다음 LIMIT로 가져와야 하니 무진장 느려진다.
- 어떻게든 집합을 작게 줄인 다음 거기서 LIMIT 걸어 가져올 것

InnoDB 테이블에서 WHERE 조건절 없이 SELECT COUNT(*) 하는 짓

- InnoDB 테이블에서는 조건절 없이 COUNT(*) 하는게 느리다.
- 각 레코드의 transaction isolation을 유지하는 MVCC 구현이 복잡해서 그렇다는..
- 트리거 걸어서 메모리 스토리지 엔진 쓰는 테이블에 통계를 별도로 유지하면 된다.

프로파일링이나 벤치마킹 안 하는 것

- 프로파일링 : 병목 찾아내기
- 벤치마킹 : 시간에 따른 성능 변화 추이 평가, 부하 견딜 수 있는지 테스트
- 프로파일링 할 때는 실제 데이터를 옮겨와서 할 것
- 어디가 병목이냐~ Memory? Disk I/O? CPU? Network I/O? OS?
- 느린 쿼리 로그로 남기기
* log_slow_queries=/path/to/log
* log_queries_not_using_indexes
- 벤치마킹 시에는 다 고정시키고 변수 하나만 바꿔가면서 해야 함. (쿼리 캐시는 끌 것.)
- 도구를 써라~~
* EXPLAIN
* SHOW PROFILE
* MyTop/innotop
* mysqlslap
* MyBench
* ApacheBench (ab)
* super-smack
* SysBench
* JMeter/Ant
* Slow Query Log

AUTO_INCREMENT 안 쓰는 것

- PK를 AUTO_INCREMENT로 쓰는건 무진장 최적화 되어 있음
* 고속 병행 INSERT 가능
* 잠금 안 걸리고 읽으면서 계속 할 수 있다는!
- 새 레코드를 근처에 놓음으로써 디스크와 페이지 단편화를 줄임
- 메모리와 디스크에 핫 스팟을 생성하고 스와핑을 줄임

ON DUPLICATE KEY UPDATE를 안 쓰는 것

- 레코드가 있으면 업데이트하고 없으면 인서트하고 이런 코드 필요없다!! 다 날려버려라!!
- 서버에 불필요하게 왔다갔다 할 필요가 없어짐
- 5-6% 정도 빠름
- 데이터 입력이 많다면 더 커질 수 있음

하지 말아야 할 것 총정리
Thinking too small
Not using EXPLAIN
Choosing the wrong data types
Using persistent connections in PHP
Using a heavy DB abstraction layer
Not understanding storage engines
Not understanding index layouts
Not understanding how the query cache works
Using stored procedures improperly
Operating on an indexed column with a function
Having missing or useless indexes
Not being a join-fu master
Not accounting for deep scans
Doing SELECT COUNT(*) without WHERE on an InnoDB table
Not profiling or benchmarking
Not using AUTO_INCREMENT
Not using ON DUPLICATE KEY UPDATE

출처: https://blog.lael.be/post/370

개요

 MySQL 쿼리 옵티마이저는 쿼리를 실행할때 최적의 계획을 세운다. 그 계획을 Database용어로 '실행계획'(Query Plan)이라고 하는데, MySQL에서는 'EXPLAIN' 키워드를 이용해 실행계획에 대한 정보를 살펴 볼 수 있다.

 이는 이슈가 발생하는 문제의 쿼리를 이해하고, 어떻게 최적화 할지에대한 insight를 제공하는 매우 강력한 도구가 될 수 있는데, 불행하게도 이를 잘 사용하는 개발자는 별로 없는것 같다. (나 포함 ㅠ)

Understanding EXPLAIN's Output

'EXPLAIN' 을 사용하면 쿼리를 실행하기전에 실행계획을 분석하여 출력한다. 아래와 같은 예를 들어보자

EXPLAIN SELECT * FROM categories

********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: categories
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: 
1 row in set (0.00 sec)

이제부터 해당 항목들에대해 자세히 살펴보도록 한다

• id : 쿼리 안에 있는 각 select 문에 대한 순차 식별자이다. 이순서대로 select문이 실행된다고 생각하면 된다.
• select_type : select 문의 유형을 말한다. 각 유형은 아래와 같다
• SIMPLE : 서브쿼리나 'union'이 없는 가장 단순한 select문을 말한다
• PRIMARY : 가장 바깥에 있는 select 문을 말한다
• DERIVED : from 문 안에있는 서브쿼리의 select 문이다.
• SUBQUERY : 가장 바깥의 select 문에 있는 서브쿼리이다.
• DEPENDENT SUBQUERY : 기본적으로 SUBQUERY와 같은 유형이며, 가장 바깥의 select문에 '의존성'을 가진 서브쿼리의 select문이다.
• UNCACHEABLE SUBQUERY
• UNION : union 문의 두번째 select 문을 말한다
• DEPENDENT UNION : 바깥 쿼리에 의존성을 가진 union문의 두번째 select문을 말한다
• table : 참조되는 테이블을 말한다
• type : MySQL이 어떤식으로 테이블들을 조인하는지를 나타내는 항목이다. 이는 매우 중요한데, 이유는 이 타입을 분석함으로써 어떤 인덱스가 사용되고 사용되지 않았는지를 알 수 있고, 이를통해 어떤식으로 쿼리가 튜닝되어야하는지에 대한 insight를 제공하기 때문이다. 각 유형은 아래와 같다
• system : 0개 또는 하나의 row를 가진 테이블이다.
• const : 테이블에 조건을 만족하는 레코드가 하나일 때, 상수 취급
• eq_ref : primary key나 unique not null column으로 생성된 인덱스를 사용해 조인을 하는 경우이다. const 방식 다음으로 빠른 방법이다.
• ref : 인덱스로 지정된 컬럼끼리의 '=' , '<=>' 와 같은 연산자를 통한 비교로 수행되는 조인이다
• index_merge 
• unique_subquery : 오직 하나의 결과만을 반환하는 'IN'이 포함된 서브쿼리의 경우이다.
• index_subquery : unique_subquery와 비슷하지만 여러개의 결과를 반환한다
• range : 특정한 범위의 rows들을 매칭시키는데 인덱스가 사용된 경우이다. BETWEEN이나 IN, '>', '>=' 등이 사용될 때이다.
• all : 조인시에 모든 테이블의 모든 row를 스캔하는경우이다. 물론 성능이 가장 좋지 않다.
• possible_keys : 테이블에서 row를 매핑시키기 위해 사용 가능한 (사용하지 않더라도) 키를 보여준다.  
• key : 실제적으로 쿼리 실행에 사용된 key의 목록이다. 이 항목에는 possible_keys 목록에 나타지 않은 인덱스도 포함 될 수 있다.
• ref : key column에 지정된 인덱스와 비교되는 column 또는 constants를 보여준다.
• rows : 결과 산출에 있어서 접근되는 record의 숫자이다. 조인문이나 서브쿼리 최적화에 있어서 중요한 항목이다.
• Extra : 실행계획에 있어서 부가적인 정보를 보여준다.

• http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain-extra-information

내컴퓨터 우클릭 - 관리 - 서비스 설정

에서 mysql stop.

> mysqld --skip-grant

새 cmd 창을 열어 mysql 서버 를 Start 하지 않은 상태에서

> mysql -uroot

or

> mysql

로 진입

mysql> update user set password = password('new-password') where user = 'root';

mysql> flush privileges;

후에

net start mysql DB 다시 가동.

새 cmd 창에서 mysql -uroot -p
비밀번호 new-password 로 접속

mysql 에서 A 서버의 mysql에서 B 서버의 mysql의 테이블을 참조하여 쓰고 싶은 경우

federated 라는 기능을 사용해야 한다.

해당 기능 사용법은 다음과 같다.

1. federated Plugin 설치

mysql 접속 후 아래 명령어 수행

>> install plugin federated soname 'ha_federated.so'

federated 설치 확인은 다음 명령어 수행

>> show engines;

2. my.cnf 파일 수정

/etc/my.cnf 파일의 [mysqld] 그룹에 다음 내용 추가

federated

mysql 데몬 재시작

>> sudo service mysql restart

3. federated 기능 Enable 확인

federated 기능 Enable 확인 명령어 수행

>> show engines;

Federated Engine의 Support 항목이 Yes로 되어 있음 정상

4. federated Table 생성

 CREATE TABLE [테이블 명]

(

     FIELD 데이터 타입

     Federate될 Table과 동일한 데이터 타입으로 세팅

)

ENGINE=FEDERATED

CONNECTION='mysql://사용자:비밀번호@호스트:포트/DB명/테이블명';

.NET Framework가 설치되어 있지 않거나 기타 다른 시스템의 환경적인 요인으로 인하여 MySQL의 Installer가 정상적으로 동작하지 않는 경우가 있습니다. 이러한 경우 다음과 같이 MySQL을 수동을 설치할 수 있습니다.

MySQL 수동 설치 파일 다운로드 받기

http://dev.mysql.com/downloads/mysql/에서 MySQL의 압축 파일 버전을 다운로드 받기 위한 링크를 클릭합니다.

페이지가 이동되면 화면 하단부의 "No Thanks, just start my download." 링크를 클릭하여 압축파일을 다운로드 받도록 합시다.

다운받은 파일의 압축 해제

다운로드 받은 파일을 C 드라이브 안에 압축 해제 합니다.

MySQL 실행 경로 환경 변수 설정하기

압축 해제한 폴더 안에 존재하는 bin 폴더가 MySQL의 실행 경로 입니다. 이 경로를 윈도우의 환경변수에 등록해야 합니다.

바탕화면의 내 PC 아이콘을 마우스 우클릭하여 "속성"을 선택합니다.

시스템 화면의 왼쪽 메뉴에서 "고급 시스템 설정" 항목을 클릭합니다.

"환경변수" 버튼을 누릅니다.

환경변수 하단부의 "시스템 변수" 항목에서 Path를 찾아 "편집"버튼을 누릅니다.

편집 창이 나타나면, 변수 값란에 입력되어 있는 기존의 값 뒤에 MySQL의 실행 폴더 경로를 추가해 주어야 합니다.

기존의 값 맨 뒤에 세미콜론(;)을 입력하고 "MySQL의 압축 해제 경로\bin"을 추가해 주어야 합니다. 여기서는 "C:\mysql-5.6.24-winx64\bin" 이라고 추가합니다.

설정이 완료되면 모든 화면의 "확인"버튼을 누르고 창을 닫습니다.

MySQL 설정파일 수정하기

MySQL의 압축을 해제한 폴더 안에는 "my-default.ini" 라는 파일이 있습니다. 이 파일이 MySQL의 환경 설정 파일의 기본 템플릿 입니다.

이 파일을 마우스 우클릭 하여 "편집"메뉴를 선택합니다.

"my-default.ini" 파일이 열리면 아래와 같이 표시된 부분이 있습니다.

이 부분에서 "#"을 삭제해서 주석을 해제하고 다음과 같이 MySQL의 경로를 작성합니다.

basedir = C:/mysql-5.6.24-winx64

datadir = C:/mysql-5.6.24-winx64/data

port = 3306

수정 결과는 다음과 같습니다.

이제 메모장의 "파일 > 다른이름으로 저장" 메뉴를 선택합니다.

파일 이름을 "my.ini"로 지정합니다.

MySQL을 윈도우 서비스로 등록하기

윈도우가 부팅될 때 마다 MySQL이 자동으로 구동되도록 하기 위해서는 윈도우에 서비스 형태로 등록해야 합니다. 이 작업을 위해서 명령프롬프트를 관리자 권한으로 실행합니다.

프롬프트 안에서 MySQL이 설치된 폴더로 접근한 후에 mysqld --install 이라고 명령어를 입력합니다. 아래의 화면 갈무리와 같이 성공했다는 메시지가 표시되면 윈도우를 재부팅 합니다.

MySQL 접속 및 Root 비밀번호 설정하기

윈도우를 재부팅한 후 명령프롬프트를 실행하고 다음의 명령어를 입력하면 MySQL에 접속할 수 있습니다. root 계정의 초기 비밀번호는 설정되어 있지 않기 때문에 비밀번호를 묻는 단계에서는 아무런 값도 입력하지 않고 엔터키를 누릅니다.

mysql -uroot -p

MySQL에 접속이 완료되면 root의 비밀번호를 설정하기 위하여 다음의 명령어들을 순차적으로 입력합니다.

use mysql;

update user set password=password('설정할 비밀번호') where user='root';

flush privileges;

제일처음 

select * from board order by idx desc limit 0 , 10

제일 마지막

select * from board order by idx asc limit 0, totalRow%10

if ( totalRow%10 == 0 ) ? 10 

만약 총갯수에서 %10 을 나눈 값이 0이라면 그 전 페이지의 10개 게시물을 보여줘야 되므로

임의의 10개의 row를 보여줘야 한다.

Unix 환경이 Linux에서 Mysql을 설치하면

Window 환경과 다르게 쿼리문 자체에서 대소문자 구별을 해버린다.

그래서

아래와 같이 코드를 작성 할 경우, 

SELECT

A.SEQ

, A.SUBJECT

, B.SEQ as IMG_SEQ

, B.CONTENTS_NAME

, B.CONTENTS_ORI_NAME

, B.CONTENTS_PATH

, B.CONTENTS_TYPE

, B.TEXT1

FROM

ADMINBOARD A ,

ADMINIMG B

WHERE

A.SEQ= #seqId#

AND A.SEQ = b.BOARD_SEQ

AND A.IMG_CATEGORY = #imgCategory#

ORDER BY

A.KEYVISUAL_ORDER

org.springframework.web.util.NestedServletException: Request processing failed;
에러를 낸다

그래서 이럴 경우에는

아래와 같이 해결 한다.

SELECT `AUTO_INCREMENT`

FROM  INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME   = 'adminboard'

40분동안 찾음;