Mysql - 튜닝 (최적화)

DB 속도 개선

1. 쿼리를 잘 분석한 후 꼭 필요한 칼럼만 인덱스로 만들어야 한다.

2. 최대한 작은 칼럼 타입을 선택하라.

3. varchar 보다는 char를 사용하라

(varchar를 사용하면 디스크 사용량을 줄일 수 있는 반면 검색 시 속도가 떨어지게 된다.)

4. enum 칼럼을 사용할 수 있으면 사용하라.

5. not null을 사용하라(not null을 이용하면 속도가 빨라지며 한 칼럼 당 1비트를 줄일 수 있다.)

6. procedure analyse()를 사용하라( 3.23.x 이상의 버전에서 쓸 수 있는 기능으로 해당 칼럼을 어떤 타입으로 정의하면 좋을 지를 보여줍니다.)

-> 예 : select * from zipCode procedure analyse();

7. BLOB 이나 TEXT 칼럼은 다른 테이블로 만들어라.

 

Insert into test_table values (12, 4), (1, 4), (9, 4), (2, 4), (10, 10);

Update 문 빠르게 사용하는 방법

Optimize table

Delete 문 빠르게 사용하는 방법

MySQL의 인덱스 캐시의 양을 크게 하는 게 좋음

Where 절의 최적화

Where절에 쓰이는 컬럼을 인덱스로 만들고, 필요 없는 괄호는 없애야 한다.

 

 

 

 

 

속도 빠른 쿼리 예

 

select count(*) from tbl_name;

select MIN(key_part1), MAX(key_part1) FROM tbl_name;

select MAX(key_part2) from tbl_name where key_part_1=constant;

select … from tbl_name order by key_part1, key_part2, … LIMIT 10;

select … from tbl_name order by key_part1 desc, key_part2 desc, … LIMIT 10;

 

MySQL 서버 최적화 하기

 

방법 1 : MySQL 설치시 컴파일 방법

è     컴파일러를 gcc 대신에 pgcc를 이용하고 –06 옵션을 주어 컴파일하면 MySQL 서버는 약 11% 정도 빨라집니다.

è     -static 옵션을 주면 공유 라이브러리를 사용하지 않게 되어 메모리를 더 쓰지만, 약 13% 정도 빨라집니다.

è     유닉스 소켓을 사용하는 것이 TCP/IP를 사용하는 것보다 빠릅니다.

 

방법 2 : 디스크와 심벌릭 링크

ln –s MySQL-Version-OS mysql

 

방법 3 : 버퍼크기 조정

메모리가 256MB 이상이고 테이블의 수도 많을 때

safe_mysqld –O key_buffer=64M –O table_cache=256 –O sort_buffer=4M –O record_buffer=1M &

메모리가 128MB 정도이고 테이블의 수가 적고 정렬을 많이 사용할 때

$safe_mysqld –O key_buffer=16M –O sort_buffer=1M

메모리가 작고 클라이언트의 연결이 많을 때

safe_mysqld –O key_buffer=512K –O sort_buffer=16K –O table_cache=32 –O record_buffer=8K –O net_buffer=1k&

 

방법 4 : 기타 MySQL을 빠르게 하는 방법

PHP의 경우 mysql_pconnect()라는 함수를 이용해 서버에 연결하라.

Select 문을 쓸 때는 인덱스를 사용하는지 확인하라.

: explain문을 이용하여 현재 사용하고 있는 select가 인덱스를 사용중인지 확인할 수 있다.

칼럼에 Default Value를 사용하라.

UDF를 이용하라.

칼럼에 유일한 값을 저장할 때는 AUTO_INCREMENT를 이용하라.

Select /*! HIGH_PRIORITY */는 select에 우선순위를 주어 select 의 속도를 빠르게 할 수 있다.(다른 클라이언트에서 insert를 하려고 해도 먼저 select를 하므로 select가 빠르다.)

Insert /*! Low_priority */는 select하는 쿼리와 동시에 실행될 때 select를 먼저 하도록 한다.(insert의 속도를 늦추어 결과적으로 select의 속도를 빠르게 합니다.)

정적으로 컴파일 하는 방법

./configure –with-client-ldflags=-all-static –with-mysqld-ldflags=-all-static

 

 

(1) 설치
  설치는 성능을 약 10% 정도 끌어올려준다는 static 모드로 설치하겠습니다.

[root@localhost local]# wget http://ftp.superuser.co.kr/pub/mysql/mysql-4.0.20.tar.gz
[root@localhost local]# tar xvfz mysql-4.0.20.tar.gz
[root@localhost local]# cd mysql-4.0.20
[root@localhost mysql-2.0.20]# CFLAGS="-static -O2 -march=i686 -funroll-loops"
CXXFLAGS="-static -O2 -march=i686 -funroll-loops -felide-constructors -fno-exceptions -fno-rtti"
 ./configure
--prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data
--disable-shared --enable-assembler
--with-thread-safe-client --with-mysqld-user="mysql"
--with-client-ldflags=-all-static
--with-mysqld-ldflags=-all-static
--with-readline --without-debug
--without-docs --without-bench
--with-charset=euc_kr

MySQL 4.1.X 에서는 --with-charset=euckr 으로 해야 합니다.
  위 설정에서 static 모드로 설치하며, 언어는 많이 사용하는 euc_kr로 설치합니다.
 데이터 디렉토리는 /usr/local/mysql/data 에 저장하게 설정합니다.