MySQL 최적화 (Optimize) > MySQL Tip

What one can and should optimize

번역: 최영봉
편집: 황미영

위의 테스트는 MySQL의 경우 8M 캐시를 사용하도록 설정한 것이고 다른 데이터베이스들은 설치 기본값을 이용하였다.

• MySQL은 풍부한 상이한 컬럼 유형(type)들의 집합을 가지고 있다. 각 컬럼에 대해 가장 효과적인 유형을 선택하여 사용하는 것이 필요하다.
• ANALYSE 프로시저는 테이블을 위한 최적의 컬럼 유형을 찾는데 도움이 될 것이다. SELECT * FROM table_name PROCEDURE ANALYSE()
• 널 값을 저장하지 않을 컬럼은 NOT NULL 로 지정한다. 이는 특별히 인덱스 컬럼의 경우 중요하다.
• ISAM 테이블들을 MyISAM 으로 변경한다.
• 가능하다면, 테이블을 고정된 테이블 형식으로 만드는 것이 좋다.
• 사용하지 않을 인덱스는 아예 만들지 않는다.
• MySQL이 인덱스의 접두부(prefix)에 대해서 검색을 수행할 수 있다는 점을 활용한다. INDEX (a, b) 로 되어 있다면, (a)에 대해 인덱싱할 필요는 없다.
• 길이가 긴 CHAR 형이나 VARCHAR 형이라면 해당 컬럼에 대해 인덱스를 생성하지 않고 그 컬럼의 접두부에 대해서만 인덱스를 생성하면 공간이 절약된다.
  
  

  CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))
  
  

• 각 테이블을 위한 가장 효과적인 테이블 유형을 사용한다.
• 서로 다른 테이블들 중 동일한 정보를 가지는 컬럼들은 같은 유형, 같은 이름을 가지도록 정의한다.

• 데이터베이스는 디렉토리로 저장된다.
• 테이블은 파일로 저장된다.
• 컬럼은 가변 길이나 고정 길이 유형으로 파일 안에 저장된다. BDB 테이블에서 데이터는 페이지에 저장된다.
• 메모리 기반의 테이블도 지원된다.
• 데이터베이스와 테이블들은 다른 디스크로부터 심볼릭 링크될 수 있다.
• Windows용 MySQL은 .sym 파일을 이용하여 데이터베이스에 대한 내부적인 심볼릭 링크를 지원한다.

• HEAP 테이블: 고정된 수의 레코드만 가지는 테이블로서 오직 메모리에만 저장되며 HASH 인덱스로 인덱스된다.
• ISAM 테이블: MySQL 3.22에서 사용된 구식 B-tree 테이블 유형이다.
• MyISAM 테이블: ISAM 테이블의 새 버전으로 많은 확장 기능들을 가지고 있다.
  • 바이너리 호환성
  • NULL 컬럼에 대한 인덱싱
  • 가변 크기 테이블의 파편화(fragmentation)가 ISAM 테이블 보다 적음
  • 거대 파일 지원
  • 인덱스 압축 향상
  • 키 통계 향상
  • 더 향상되고 빠른 auto_increment 지원
• Sleepycat의 Berkeley DB(BDB) 테이블: 안전한 트랜잭션 지원(BEGIN WORK / COMMIT | ROLLBACK)

• MySQL은 모든 컬럼들이 고정 크기 유형이라면 (VARCHAR, BLOB, TEXT 컬럼이 없다면) 테이블을 고정 크기 테이블로 생성한다. 그렇지 않다면, 가변 크기 유형의 테이블로 만든다.
• 고정 크기 유형은 동적 크기 유형에 비해 속도가 빠르며 안전하다.
• 동적 크기 레코드 유형은 대개 보다 적은 공간을 사용하지만 테이블의 갱신이 자주 발생한다면 파편화가 가중되기 마련이다.
• 어떤 경우에는 주 테이블의 속도를 향상시키기 위해 모든 VARCHAR, BLOB, TEXT 컬럼들을 다른 테이블로 옮기는 것도 유용할 때가 있다.
• myisampack(ISAM 테이블에서는 pack_isam)을 사용하면 읽기 전용, 압축된 테이블을 만들 수 있다. 느린 디스크를 사용할 때는 디스크 사용량을 최소화하는 것이 좋은 한 방법이 된다. 압축된 테이블은 더 이상 갱신되지 않는 로그 테이블 등에 사용하면 최상이다.

• 키 캐시: key_buffer_size, 기본값은 8M
• 테이블 캐시: table_cache, 기본값은 64
• 쓰레드 캐시: thread_cache_size, 기본값은 0
• 호스트명 캐시: 컴파일할 때 변경 가능, 기본값은 128
• 메모리에 맵핑된 테이블(Memory mapped tables): 현재는 압축된 테이블을 위해서만 사용된다.

MySQL은 행(raw) 캐시를 가지고 있지 않다. 그러나, 운영체제에게 이 일을 시킬 수 있다!

• sort_buffer: ORDER BY / GROUP BY 절에서
• record_buffer: 테이블을 스캔할 때
• join_buffer_size: 키 없이 join을 수행할 때
• myisam_sort_buffer_size: REPAIR TABLE에서
• net_buffer_length: SQL 문장을 읽을 때와 결과 값을 버퍼링할 때
• tmp_table_size: 임시 결과값을 위한 HEAP-table-size

• MyISAM 테이블의 열려 있는 각각의 인스턴스는 인덱스 파일과 데이터 파일을 사용한다. 만일 어떤 테이블이 두 개의 쓰레드에 의해 사용되거나 같은 쿼리에서 두 번 사용되면, MyISAM이 인덱스 파일은 공유하지만 데이터 파일은 또 하나의 인스턴스를 위해 추가로 열게 된다.
• 캐시 안의 모든 테이블이 사용 중이라면 그 캐시는 일시적으로 테이블 캐시 크기보다 커진다. 이러한 상황이 발생하면, 그 다음 방면된 테이블이 닫히게 된다.
• mysqld 변수 Opend_tables를 검사해 보면 테이블 캐시가 너무 작은지 아닌지를 알 수 있다. 이 값이 높으면 테이블 캐시를 늘려줘야 한다!

• 최적화된 테이블 유형을 사용한다.(HEAP, MyISAM, BDB 테이블)
• 데이터를 위한 최적의 컬럼을 사용한다.
• 가능한 한 고정 크기 레코드를 사용한다.
• 다른 잠금 유형(lock types)를 사용한다.(SELECT HIGH_PRIORITY, INSERT LOW_PRIORITY)
• Auto_increment
• REPLACE (REPLACE INTO table_name VALUES (...))
• INSERT DELAYED
• LOAD DATA INFILE / LOAD_FILE()
• 한번에 많은 레코드를 추가하기 위해서는 다중 레코드 INSERT를 사용한다.
• SELECT INTO OUTFILE
• LEFT JOIN, STRAIGHT JOIN
• IS NULL 과 접목된 LEFT JOIN 사용
• 일부 경우, ORDER BY 는 키를 사용할 수 있다.
• 하나의 인덱스에 있는 컬럼들만 쿼리할 경우에는 쿼리를 수행하기 위해 그 인덱스 트리만 사용하게 된다.
• 조인은 보통 subselect 보다 빠르다. (대부분의 SQL 서버들에서 그러하다.)
• LIMIT
  • SELECT * from table1 WHERE a > 10 LIMIT 10, 20
  • DELETE * from table1 WHERE a > 10 LIMIT 10
• foo IN (상수 목록) 구문은 매우 최적화되어 있다.
• GET_LOCK() / RELEASE_LOCK()
• LOCK TABLES
• INSERT 와 SELECT 는 동시에 실행 될 수 있다.
• 작동하고 있는 서버로 읽어 들일 수 있는 UDF 함수들
• 압축된 읽기 전용 테이블들
• CREATE TEMPORARY TABLE
• CREATE TABLE .. SELECT
• MyISAM 테이블을 RAID와 사용하면 하나의 파일을 여러개의 파일들로 나누어 일부 파일시스템의 2G 제한을 넘어서는 것이 가능하다.
• Delayed_keys
• 리플리케이션(replication)

• >, >=, =, <, <=, 키에 대해 IF NULL 과 BETWEEN을 사용할 때
  
  SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
  SELECT * FROM table_name WHERE key_part1 IS NULL;
  
  
• 와일드카드 문자로 시작하지 않는 LIKE 절을 사용할 때
  
  SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
  
  
• 조인을 수행하면서 다른 테이블들로부터 레코드를 가져올 때
  
  SELECT * from t1, t2 where t1.col=t2.key_part;
  
  
• 특정 인덱스에 대해서 MAX() 나 MIN() 값을 구할 때
  
  SELECT MIN(key_part2), MAX(key_part2) FROM table_name where key_part1=10;
  
  
• 키의 접두부에 대해 ORDER BY 나 GROUP BY 절을 수행할 때
  
  SELECT * FROM foo ORDER BY key_part1, key_part2, key_part3;
  
  
• 쿼리에 사용되는 모든 컬럼이 한 개의 키의 부분(part)일 경우
  
  SELECT key_part3 FROM table_name WHERE key_part1=1;

• MySQL은 테이블 전체를 스캔하는 더 빠를 것이라고 판단되면 인덱스를 사용하지 않는다. 예를 들어, key_part1이 1과 100사이의 값을 고르게 가지고 있다면, 다음과 같은 쿼리에서 인덱스를 사용하는 것은 좋지 않다.
  
  SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;
  
  
• HEAP 테이블을 사용하고 있으며, 모든 키 부분들에 대해서 = 로 검색하지 않을 경우
• HEAP 테이블에 대해 ORDER BY 절로 쿼리할 경우
• 맨 처음의 키 부분을 사용하지 않을 경우
  
  SELECT * FROM table_name WHERE key_part2 = 1;
  
  
• 와일드카드 문자로 시작하는 LIKE 를 사용할 경우
  
  SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'
  
  
• 하나의 인덱스에 대해서 검색하면서 다른 인덱스에 대해서는 ORDER BY 를 적용할 때
  
  SELECT * FROM table_name WHERE key_part1 = # ORDER BY key2;

지나치게 느리다고 생각되는 모든 쿼리 문장에 대해 EXPLAIN 을 사용한다.

mysql> explain select t3.DateOfAction, t1.TransactionID
-> from t1 join t2 join t3
-> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID
-> order by t3.DateOfAction, t1.TransactionID;
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| table |  type  | possible_keys |   key   | key_len |     ref          | rows |              Extra              |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| t1    | ALL    | NULL          | NULL    | NULL    | NULL             | 11   | Using temporary; Using filesort |
| t2    | ref    | ID            | ID      | 4       | t1.TransactionID | 13   |                                 |
| t3    | eq_ref | PRIMARY       | PRIMARY | 4       | t2.GroupID       | 1    |                                 |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+

유형 ALL과 범위는 잠재적인 문제점을 알리고 있다.

현재 진행 상황을 파악하기 위해서는 SHOW processlist 를 사용한다.

+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| Id | User  | Host      | db | Command | Time | State        | Info                                |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| 6  | monty | localhost | bp | Query   | 15   | Sending data | select * from station,station as s1 |
| 8  | monty | localhost |    | Query   | 0    |              | show processlist                    |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+

mysql에서 KILL을 사용하거나 명령행에서 mysqladmin을 사용하여 불필요한(runaway) 쓰레드들을 없앨 수 있다.

다음 명령들을 실행해서 결과를 이해하도록 노력한다.

• SHOW VARIABLES;
• SHOW COLUMNS FROM ... \G
• EXPLAIN SELECT ... \G
• FLUSH STATUS;
• SELECT ...;
• SHOW STATUS;

• 로그 기록시
• 많은 연결이 이루어 질 때, 연결 속도가 매우 빠르다.
• SELECT 와 INSERT 를 동시에 사용하는 곳에서.
• update를 시간이 오래 걸리는 select 문과 결합하지 않을 때
• 대부분의 select/update 문이 고유한 키들을 사용할 때
• 많은 테이블을 장시간 잠금(lock) 충돌 없이 사용할 때
• 크기가 큰 테이블을 가지고 있을 때 (MySQL 은 매우 컴팩트한 테이블 포맷을 사용한다.)

• 테이블을 UPDATE 또는 삭제된 행을 테이블에 대해 INSERT하면서 시간이 오래 걸리는 SELECT 절들과 결합시키는 일
• WHERE 절에 올 수 있는 것들에 대한 HAVING
• 키를 사용하지 않은, 또는 충분히 유니크하지 않은 키를 사용한 JOIN
• 컬럼 유형이 서로 다른 컬럼들에 대해 JOIN 수행
• 온전한 키 전체가 아닌 키의 부분에 대해서만 '=' 로 비교연산할 때 HEAP 테이블을 사용
• MySQL monitor 에서 UPDATE 나 DELETE를 사용하면서 WHERE 절을 생략하는 일. 만약 자신이 이런 경향이 있다면, mysql 클라이언트 프로그램을 실행할 때 --i-am-a-dummy 옵션을 추가하기 바란다.

• 내장된 테이블 잠금
• LOCK TABLES (모든 테이블 유형에 대해 동작함)
• GET_LOCK() / RELEASE_LOCK()
• Page locks (BDB 테이블에 대해서)
• ALTER TABLE 역시 BDB 테이블에 대해 테이블 잠금을 수행함
• LOCK TABLES 는 다중 읽기 작업 또는 한개의 쓰기 작업을 허용한다.
• 보통 WRITE 잠금은 READ 잠금 보다 우선 순위가 높다. 쓰기 작업이 무한정 대기 상태에 놓이게 되는 경우를 피하기 위해서다(to avoid starving the writers). 그리 중요하지 않은 쓰기 작업은 LOW_PRIORITY 키워드를 사용하여 lock handler가 읽기 작업에 먼저 허가를 내어 주도록 하는 것도 한 방법이다.
  
  UPDATE LOW_PRIORITY SET value=10 WHERE id=10;

MySQL 만의 기능들을 항상 주석처리함으로써 쿼리의 이식성 높일 수 있다.

SELECT /*! SQL_BUFFER_RESULTS */ ...

• SELECT SQL_BUFFER_RESULTS ...
  MySQL이 임시 결과 세트를 만들도록 강제한다. 임시 세트가 만들어지면, 그 테이블들에 대한 모든 잠금이 해제된다. 이는 테이블 잠금으로 인해 문제가 발생했을 때나 쿼리 결과를 클라이언트로 전송하는데 오랜 시간이 소요되는 경우에 도움이 된다.
  
  
• SELECT SQL_SMAIL_RESULT ... GROUP BY ...
  결과 세트가 적은 수의 레코드만 가지게끔 하도록 옵티마이저에게 지시한다.
  
  
• SELECT SQL_BIG_RESULT ... GROUP BY ...
  결과 세트가 많은 수의 레코드를 가지도록 옵티마이저에게 지시한다.
  
  
• SELECT STRAIGHT_JOIN ...
  옵티마이저가 FROM 절에 나타난 순서대로 테이블을 join 하도록 강제한다.
  
  
• SELECT ... FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2
  MySQL이 특정 인덱스들을 사용하거나 무시하도록 강제한다.
  
  

• MyISAM 테이블에 대해 트랜잭션을 수행하는 방법:
  
  mysql> LOCK TABLES trans READ, customer WRITE;
  mysql> select sum(value) from trans where customer_id=some_id;
  mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id;
  mysql> UNLOCK TABLES;
  
  
• Berkeley DB 테이블에 대해 트랜잭션을 수행하는 방법:
  
  mysql> BEGIN WORK;
  mysql> select sum(value) from trans where customer_id=some_id;
  mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id;
  mysql> COMMIT;
  
  
• 다음과 같이 함으로써 여러 트랜잭션들 간의 간섭을 방지할 수 있다는 점에 주목할 필요가 있다:
  
  UPDATE customer SET value=value+new_value WHERE customer_id=some_id;

• REPLACE는 테이블의 이전 레코드가 새 레코드와 같은 고유 인덱스 값을 가지고 있다면 예전 레코드가 먼저 삭제되고 새 레코드가 추가된다는 점만 제외하면 INSERT와 똑같이 작동한다. 
  
  다음과 같이 하는 대신,
  
  SELECT 1 FROM t1 WHERE key=#
  IF found-row
  LOCK TABLES t1
  DELETE FROM t1 WHERE key1=#
  INSERT INTO t1 VALUES (...)
  UNLOCK TABLES t1;
  ENDIF
  
  다음과 같이 한다.
  
  REPLACE INTO t1 VALUES (...)

• 프라이머리 키는 짧은 것을 사용한다. 테이블 조인할 때는 문자열형 보다는 숫치형을 사용한다.
• 여러 부분으로 구성된 키를 사용할 때는 첫 번째 부분이 가장 많이 사용되는 키이어야 한다.
• 의심스러울 때는, 앞부분이 더 많이 중복된 컬럼을 사용해서 보다 나은 키 압축 효과를 얻는다.
• 클라이언트를 실행 중이고 MySQL 서버가 같은 머신에 있다면, TCP/IP 대신에 유닉스 소켓을 사용하여 서버에 연결하는 것이 좋다.(이렇게 하면 7.5% 정도까지 효율이 증진된다.) MySQL 서버에 접속할 때 호스트이름이나 localhost를 지정하지 않으면 유닉스 소켓으로 접속하게 된다.
• 가능하다면 --skip-locking(일부 운영체제에서는 이 것이 기본값이다)을 사용한다. 이는 외부적인 잠금을 사용하지 않게 되고 퍼포먼스가 향상된다.
• 긴 키를 사용하기 보다는 응용프로그램 수준에서 해시된 값을 사용한다.
  
  SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND col_1='constant' AND col_2='constant';
  
  
• Store BLOB's that you need to access as files in files. 데이터베이스에는 파일이름만 저장한다.
• 레코드들의 큰 부분을 지우는 것 보다 레코드 전체를 지우는 것이 더 빠르다.
• SQL이 충분히 빠르지 않다면, 데이터에 접근하는 더 낮은 수준의 인터페이스를 점검해 본다.

• MyISAM; 이식이 수월한 거대 테이블 유형
• HEAP; 메모리 상의 테이블
• Berkeley DB; Sleepycat에서 제공하는 트랙잰션이 가능한 테이블
• 대폭 확대된(풀린) 제한들
• 동적 문자셋
• 더 많이 제공되는 STATUS 변수들
• CHECK table, REPAIR table
• 더 빠른 GROUP BY 절과 DISTINCT 절
• 최적화된 LEFT JOIN ... IF NULL
• CREATE TABLE ... SELECT
• CREATE TEMPORARY table_name (...)
• 임시적인 HEAP에서 MyISAM 테이블로의 자동 변환
• 리플리케이션
• mysqlhotcopy 스크립트

• 진보된 트랜잭션
• 오류로부터 안전한 리플리케이션
• 텍스트 검색
• 많은 테이블의 삭제 (이 작업 후에 많은 테이블의 갱신이 이루어진다.)
• 너 나은 키 캐시
• 원자화된 RENAME (RENAME TABLE foo as foo_old, foo_new as foo)
• 쿼리 캐시
• MERGE TABLES
• 향상된 GUI 클라이언트