데이터 베이스 구조 최적화

디자인 선택

MySQL은 열 데이터와 인덱스 데이터를 서로 별도의 파일에서 관리한다. 거의 대부분의 다른 데이터 베이스 시스템은 열 데이터와 인덱스 데이터를 동일 파일에서 같이 관리를 하고 있다. 우리는 MySQL이 선택한 방식이 현대적인 시스템에 보다 잘 맞는 것이라고 생각을 하고 있다.

열 데이터를 저장하는 다른 방법은 각 컬럼의 정보를 별도의 구역 (area)에 두는 것이다 (예를 들면 SDBM 및 Focus). 이렇게 하면 모든 쿼리가 한번 이상 컬럼을 접속해야 하기 때문에 성능 이슈가 발생한다. 이런 방식은 범용 데이터 베이스에는 그다지 좋은 방식이 아니라고 생각한다.

위의 방식보다 일반적인 방식은 인덱스와 데이터를 같이 저장하는 것이다. (오라클/ 사이베이스 등이 이 방식을 사용한다). 이렇게 하면, 열에 대한 정보를 인덱스의 리프 페이지 (leaf page)에서 찾아 볼 수가 있게 된다. 이런 레이 아웃의 장점은, 인덱스가 얼마나 잘 캐시되어 있는지에 따라서, 디스크 공간을 절약 시켜 준다는 것이다. 이 레이 아웃이 갖는 단점은 다음과 같다:

• 데이터를 가져 오기 위해서는 인덱스 전체를 읽어야 하기 때문에 테이블 스캔 속도가 느려진다.
• 인덱스 테이블만을 사용해서는 쿼리용 데이터를 추출할 수가 없게 된다.
• 노드에 인덱스를 중복 저장해야 하기 때문에 보다 많은 디스크 공간이 필요하게 된다 (열을 노드에 저장할 수가 없다).
• 삭제 동작으로 인한 테이블 오버 타임 (over time)이 발생한다 (노드에 있는 인덱스는 보통의 경우 삭제 중에는 업데이트가 되지 않기 때문임).
• 인덱스 데이터만을 캐시하는 것이 보다 어렵다.

데이터를 가능한 한 작게 만든다

가장 기본적인 최적화 방법 중의 하나는 가능한 한 디스크의 공간을 작게 가지도록 테이블을 디자인 하는 것이다. 이렇게 하면 디스크 읽기 속도를 개선 시킬 수가 있기 때문에 테이블이 작을수록 쿼리를 실행하는 동안 실제로 처리되는 컨텐츠가 메인 메모리를 적게 차지하게 된다. 인덱싱 또한 보다 작은 컬럼에서 이루어 진다면 자원을 덜 차지하게 된다.

MySQL은 여러 가지의 스토리지 엔진 (테이블 타입)과 열 포맷을 지원한다. 각각의 테이블에 대해서 어떤 스토리지 엔진과 인덱싱 방식을 사용할 지를 여러분이 결정할 수가 있다. 각 어플리케이션에 맞는 테이블 포맷을 잘 선택하는 것이 성능 향상에 커다란 도움을 줄 것이다.

아래에 열거된 기법을 활용하면 최적의 테이블 타입 및 최소한의 스토리지 공간을 만들어 낼 수 있을 것이다:

• 가능한 한 최적의 (가장 작은) 데이터 타입을 사용한다. MySQL에는 디스크 공간 및 메모리를 절약 시켜 주는 다양한 타입들이 존재 한다. 예를 들면, 보다 작은 테이블을 가지기 위해서는 가능하면 정수 타입을 사용한다. MEDIUMINT가 INT 보다는 종종 좀더 좋은 선택이 될 수 있는데, 그 이유는 MEDIUMINT 컬럼이 25% 정도 덜 공간을 차지하기 때문이다.
• 가능하다면 컬럼을 NOT NULL로 선언한다. 이렇게 하면 모든 것들이 빨라지고 컬럼 당 1 바이트의 공간을 절약 시킬 수가 있다. 어플리케이션에서 반드시 NULL을 사용해야 하는 경우라면, 그렇게 하도록 해야 하지만, 단지 모든 컬럼의 디폴트로는 사용하지 말기 바란다.
• MyISAM 테이블의 경우에는, 변수 길이 컬럼 (variable-length column) (VARCHAR, TEXT, 또는 BLOB 컬럼)을 가지고 있지 않다면, 고정 크기 열 포맷이 사용된다. 이런 형태는 속도는 빠르겠지만 불행하게도 공간은 더 차지하게 된다.
• MySQL 5.0.3 이후부터 InnoDB 테이블은 보다 간단한 (compact) 스토리지 엔진 포맷을 사용하고 있다. MySQL초기 버전에서는, InnoDB 열이 여분의 정보 (redundant information)를 가지고 있었는데, 컬럼 숫자와 각 컬럼 길이뿐만 아니라 고정 길이 컬럼에 대한 정보까지 가지고 있었다. 테이블은 디폴트로 콤팩트 포맷인 (ROW_FORMAT=COMPACT)으로 생성된다. 만약에 이전 버전 MySQL로 다운 그레이드 하고자 한다면, 이 포맷을 구형 포맷인 ROW_FORMAT=REDUNDANT로 만들어야 한다.
  콤팩트 InnoDB 포맷은 UTF-8 데이터를 가지고 있는 CHAR 컬럼 저장 방식도 변경 시켰다. ROW_FORMAT=REDUNDANT 포맷의 경우, UTF-8 CHAR(N)는 3 × N 바이트로 구성 되었는데, UTF-8 의 엔코딩 문자 최대 길이는 3 바이트로 주어졌다. 대부분의 언어는 주로 단일 바이트 UTF-8 문자로 쓰여질 수 있기 때문에, 고정 스토리지 길이는 종종 공간을 허비했다. ROW_FORMAT=COMPACT 포맷을 사용하는 경우에는, InnoDB는 변수를 N 에서 3 × N 바이트의 범위 내에서 할당한다.
• 테이블의 주 인덱스(primary index)는 가능한 한 작게 만든다. 이렇게 하면 각 열의 특성 (identification)을 쉽고 효율적으로 만들 수가 있다.
• 실제로 필요한 경우에만 인덱스를 만든다. 인덱스는 데이터를 추출할 때에는 좋지만 데이터를 빠르게 저장할 때에는 좋지가 않다. 주로 컬럼 조합 검색을 사용해서 테이블을 접속한다면, 이러한 조합의 인덱스를 별도로 만든다. 이러한 인덱스의 첫 부분은 가장 자주 사용되는 컬럼이 되어야 한다. 테이블에서 선택 (select)를 할 때 항상 많은 수의 컬럼을 사용한다면, 인덱스를 보다 효과적으로 압축한 컬럼 복사본을 사용하도록 한다.
• 스트링 컬럼이 문자의 첫 번째 숫자에 고유의 접두사 (unique prefix)를 가지고 있는 경우라면, 이 접두사만을 인덱스 하는 것이 바람직하다. 인덱스가 작을수록 보다 빠른 동작을 구현하는데, 그 이유는 디스크 공간을 덜 필요로 하기 때문일 뿐만 아니라, 인덱스 캐시 일치 비율이 더 높기 때문에 디스크 검색을 덜 하기 때문이다.

컬럼 인덱스

모든 MySQL 데이터 타입은 인덱스화 될 수 있다. 상호 연관된 컬럼에서 인덱스를 사용하는 것이 SELECT 연산 성능을 향상시키는 최선의 방법이다.

테이블 당 최대 인덱스 숫자 및 최대 인덱스 길이는 스토리지 엔진 별로 정의 된다. 모든 스토리지 엔진은 테이블 당 최소 16개의 인덱스와 최소 256 바이트의 인덱스 길이를 지원한다.

인덱스 정의문에서 col_name(N)를 사용하면, 스트링 컬럼에서 첫 번째 문자가 N인 것만을 인덱스할 수가 있다. 이러한 방식으로 컬럼 값의 접두사만을 인덱싱하면 인덱스 파일을 보다 작게 만들 수가 있다. BLOB 또는 TEXT 컬럼을 인덱스 할 때에는, 반드시 인덱스의 접두사 (prefix) 길이를 지정해야 한다. 예를 들면:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

접두사는 1000 바이트 길이까지 사용할 수가 있다 (InnoDB 테이블에 대해서는 767 바이트까지). 접두사 길이는 바이트 단위지만, CREATE TABLE 명령문의 접두사 길이는 문자의 숫자로 변환된다는 점을 알아 두자. 다중 바이트 문자 셋을 사용하는 컬럼에 대한 접두사 길이를 지정할 때에는 이점을 명심한다.

FULLTEXT 인덱스도 생성할 수가 있다. 이것은 전체 문장 (full-text) 검색을 위해 사용된다. MyISAM 스토리지 엔진만이 FULLTEXT 인덱스를 지원하며 CHAR, VARCHAR, 그리고 TEXT 컬럼에 대해서만 지원한다. 인덱싱은 항상 전체 컬럼에서 이루어지며 부분적인 인덱싱은 지원하지 않는다.

또한 스파샬 (spatial) 데이터 타입에서도 인덱스를 만들 수가 있다. 현재까지는 MyISAM만이 스파샬 타입에서의 R-트리 (tree) 인덱스를 지원하고 있다. MySQL 5.0.16 까지는, 다른 스토리지 엔진은 B-트리(tree)를 사용해서 스파샬 타입 인덱싱을 한다 (ARCHIVE 및 NDBCLUSTER는 예외인데, 이것들은 스파샬 타입 인덱싱을 지원하지 않는다).

MEMORY 스토리지 엔진은 HASH 인덱스를 디폴트로 사용하지만, BTREE 인덱스도 지원한다.

다중-컬럼 인덱스

MySQL은 복합 (composite) 인덱스를 만들 수가 있다 (즉, 다중 컬럼 상의 인덱스). 하나의 인덱스는 최대 15개의 컬럼으로 구성될 수 있다. 특정 데이터 타입의 경우에는 컬럼의 접두사를 인덱스할 수 있다.

다중-컬럼 인덱스는 인덱스가 된 컬럼 값의 연쇄 연결 값을 가지고 있는 정렬된 어레이 (sorted array)로 간주된다.

MySQL은 WHERE 구문에 있는 인덱스의 첫 번째 컬럼에 대해서 비록 여러분이 다른 컬럼 값은 지정하지 않았다고 하더라도, 여러분이 알려진 양을 지정할 때 쿼리가 빨리 처리할 수 있도록 다중-컬럼 인덱스를 사용한다.

테이블이 아래와 같은 형태로 되어 있다고 가정하자:

CREATE TABLE test (

    id         INT NOT NULL,

    last_name  CHAR(30) NOT NULL,

    first_name CHAR(30) NOT NULL,

    PRIMARY KEY (id),

    INDEX name (last_name,first_name)

);

name 인덱스는 last_name 과 first_name 컬럼 전반에 대한 인덱스다. 이 인덱스는 last_name, 또는 last_name 과 first_name용으로 지정된 범위 값을 지정하는 쿼리를 위해 사용된다. 따라서 name 인덱스는 아래 쿼리에서 사용될 수 있다:

SELECT * FROM test WHERE last_name='Widenius';

 

SELECT * FROM test

  WHERE last_name='Widenius' AND first_name='Michael';

 

SELECT * FROM test

  WHERE last_name='Widenius'

  AND (first_name='Michael' OR first_name='Monty');

 

SELECT * FROM test

  WHERE last_name='Widenius'

  AND first_name >='M' AND first_name < 'N';

하지만, 아래의 쿼리에서는 name 인덱스를 사용할 수가 없다:

SELECT * FROM test WHERE first_name='Michael';

 

SELECT * FROM test

  WHERE last_name='Widenius' OR first_name='Michael';

쿼리 속도를 향상 시키기 위해서 MySQL이 인덱스를 사용하는 방법에 대해서는 Section 7.4.5, “MySQL의 인덱스 사용 방법”에서 보다 자세히 다루기로 한다.

MySQL은 인덱스를 어떻게 사용하는가

인덱스는 특정 컬럼 값을 가지고 있는 열을 빨리 찾기 위해서 사용된다. 인덱스를 사용하지 않는다면, MySQL은 첫 번째 열부터 전체 테이블에 걸쳐서 연관된 열을 찾아야만 한다. 테이블이 크면 클수록 비용도 늘어나게 되는 것이다. 만일 테이블이 쿼리에 있는 질문 컬럼에 대한 인덱스를 가지고 있다면, MySQL은 모든 데이터를 조사하지 않고 데이터 파일 중간에서 검색 위치를 재빨리 알아낼 수가 있다. 만일 테이블이 1,000개의 열을 가지고 있다면, 이것은 최소 100배의 속도 향상을 가질 수가 있다. 만일 대부분의 열을 접속할 필요가 있는 경우라면, 순차적인 읽기 (sequential read)가 더 빠르게 되는데, 그 이유는 이 방법이 디스크 검색을 최소화 하기 때문이다.

대부분의 MySQL 인덱스 (PRIMARY KEY, UNIQUE, INDEX, 그리고 FULLTEXT)는 B-트리에 저장된다. 하지만, 공간 데이터 (spatial data)가 R-트리를 사용하고, MEMORY 테이블도 해시 인덱스 (hash indexes)를 지원한다는 점에 대해서는 함께 알아 두기 바란다.

스트링은 자동적으로 접두사- (prefix-)와 끝-공간 (end-space)을 압축한다.

MySQL은 다음과 같은 경우에 인덱스를 사용한다:

• WHERE 구문과 일치하는 열을 빨리 찾기 위해.
• 열을 고려 대상에서 빨리 없애 버리기 위해. 만일 여러 개의 인덱스 사이에서 선택을 해야 한다면, MySQL은 보통 최소의 열을 찾는 인덱스를 사용한다.
• 조인 (join)을 실행할 때 다른 테이블에서 열을 추출하기 위해.
• 특정하게 인덱스된 컬럼 key_col을 위한 MIN() 또는 MAX() 값을 찾기 위해. 이것은 인덱스에서 key_col 전에 발생하는 모든 키 부분의 WHERE key_part_N = constant를 사용할 수 있는지를 검사하는 프리 프로세서 (pre-processor)에 의해 최적화 된다. 이와 같은 경우, MySQL은 각각의 MIN() 또는 MAX() 수식에 대해서 단일 키 룩업 (lookup)을 실행해서 상수로 대체한다. 모든 수식이 상수로 대체가 되면, 쿼리는 즉시 리턴 된다. 예를 들면:
  

  SELECT MIN(key_part2),MAX(key_part2)
  
    FROM tbl_name WHERE key_part1=10;
  
  
  
  

• 사용할 수 있는 키의 최 좌측 접두사 (leftmost prefix)를 가지고 정렬 (sorting) 및 그룹화 (grouping)를 하기 위해 (예를 들면, ORDER BY key_part1, key_part2). 만일 모든 키 부분이 DESC 다음에 나온다면, 그 키는 역순으로 읽혀진다.
• 데이터 열을 참조하지 않는 상태로 값을 추출하기 위해서 쿼리를 최적화 하는 경우도 있다. 만일 쿼리가 숫자로만 이루어진 테이블 컬럼만을 사용하고 몇몇 키에 대해서는 최 좌측 접두사를 가지고 있다면, 선택된 값은 인덱스 트리로부터 매우 빠른 속도로 추출이 될 것이다:
  

  SELECT key_part3 FROM tbl_name 
  
    WHERE key_part1=1
  
  
  
  

여러분이 아래와 같은 SELECT 명령문을 입력했다고 가정하자:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

만일 다중-컬럼 인덱스가 col1 과 col2에 존재한다면, 적당한 열을 직접 가져올 수가 있다. 만일 별개의 단일-컬럼 인덱스가 col1 과 가장 적은 열을 찾아내는지를 알아낸 후에 그 인덱스를 사용해서 열을 패치 (fetch)함으로써 가장 제한적인 인덱스를 찾으려고 시도를 한다.

만일 테이블이 다중-컬럼 인덱스를 가지고 있다면, 옵티마이저는 인덱스의 최 좌측 접두사를 사용해서 열을 찾는다. 예를 들면, 만일 여러분이 세개의 컬럼 인덱스를 (col1, col2, col3)에서 가지고 있다면, 여러분은 (col1), (col1, col2), 그리고 (col1, col2, col3)에서 검색 기능이 있는 인덱스를 가지게 되는 것이다.

컬럼이 인덱스의 최 좌측 접두사를 가지고 있지 않다면, MySQL은 부분 인덱스를 사용할 수 없게 된다. 아래와 같은 SELECT 명령문을 가지고 있다고 가정하자:

SELECT * FROM tbl_name WHERE col1=val1;

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

 

SELECT * FROM tbl_name WHERE col2=val2;

SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

만일 (col1, col2, col3)에 인덱스가 하나 존재한다면, 처음 두 개의 쿼리만이 인덱스를 사용하게 된다. 세 번째와 네 번째도 인덱스된 컬럼을 가지고 있기는 하지만, (col2) 와 (col2, col3) 는 (col1, col2, col3)의 최 좌측 접두사가 아니다. B-트리 인덱스는 =, >, >=, <, <=, 또는 BETWEEN 연산자를 사용하는 수식에서 컬럼 비교를 위해 사용될 수 있다. 또한, LIKE에 대한 인수가 와일드 카드 문자로 시작되지 않는 상수 스크링일 경우에는, LIKE 비교를 위해서도 이 인덱스를 사용할 수 있다. 예를 들면, 아래의 SELECT 명령문은 인덱스를 사용한다:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';

SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

첫 번째 명령문에서 보면, 'Patrick' <= key_col < 'Patricl'을 가지고 있는 열만이 고려되었다. 두 번째 명령문에서는, 'Pat' <= key_col < 'Pau'를 가지고 있는 열만이 고려된다.
아래의 SELECT 명령문은 인덱스를 사용하지 않는다:

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';

SELECT * FROM tbl_name WHERE key_col LIKE other_col;

첫 번째 명령문의 경우, LIKE 값이 와일드 카드 문자로 시작된다. 두 번째 명령문에서는, LIKE 값이 상수가 아니다.

만일 여러분이 ... LIKE '%string%'을 사용하고, string이 3개의 문자 보다 길다면, MySQL 은 스트링용 패턴을 초기화 시키기 위해Turbo Boyer-Moore algorithm 을 사용하게 되며, 이 패턴을 사용해서 검색을 보다 빠르게 실행한다.

col_name IS NULL을 사용하는 검색은 col_name 이 인덱싱 되는 경우에는 적용할 수가 있다.

WHERE 구문에 있는 모든AND 레벨에 해당하지 않는 모든 인덱스는 쿼리를 최적화하는데 사용되지 않는다. 달리 표현하면, 인덱스를 사용하기 위해서는, 인덱스의 접두사가 반드시 모든 AND 그룹에서 사용되어야 한다는 것이다.

아래의 WHERE 구문은 인덱스를 사용한다:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

    /* index = 1 OR index = 2 */

... WHERE index=1 OR A=10 AND index=2

    /* optimized like "index_part1='hello'" */

... WHERE index_part1='hello' AND index_part3=5

    /* Can use index on index1 but not on index2 or index3 */

... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

아래의 WHERE 구문은 인덱스를 사용하지 않는다:

    /* index_part1 is not used */

... WHERE index_part2=1 AND index_part3=2

 

    /*  Index is not used in both parts of the WHERE clause  */

... WHERE index=1 OR A=10

 

    /* No index spans all rows  */

... WHERE index_part1=1 OR index_part2=10

MySQL이 인덱스를 사용할 수 있을 지라도 이것을 사용하지 않는 경우도 있다. MySQL이 인덱스를 사용하기 위해서는 테이블의 거의 모든 열을 접속할 필요가 있다고 옵티마이저가 판단할 경우가 이에 해당한다. (이와 같은 경우에는, 테이블 스캔이 속도가 더 빠르다.) 하지만, 만일 이러한 쿼리가 LIMIT를 사용해서 열의 일부분만 추출하는 경우에는, MySQL은 인덱스를 사용하게 되는데, 그 이유는 결과를 리턴하기 위해서 찾아야 하는 열의 숫자가 작기 때문에 그 만큼 속도가 빠르기 때문이다.

해시 인덱스 (Hash indexes)는 위에서 설명한 것과는 다소 다른 특징을 가지고 있다:

• 이것은 = 또는 <=> 연산자를 사용하는 등식 비교에 대해서만 사용된다 (하지만 매우 빠름). 이것은 값의 범위를 찾기 위한 < 와 같은 비교 연산자와는 같이 사용되지 않는다.
• 옵티마이저는 ORDER BY 연산 속도를 증가 시키기 위해 이것을 사용할 수가 없다. (이런 타입의 인덱스는 다음 순서의 엔트리를 위한 검색용으로 사용될 수 없다.)
• MySQL은 두 개의 값 사이에 얼마나 많은 열이 있는지를 추정할 수 없다 (이것은 사용한 인덱스가 어떤 것인지를 결정하기 위한 범위 옵티마이저가 사용하는 것이다).
• 단지 전체 키만이 열에 대한 검색을 할 수가 있다. (B-트리 인덱스를 사용한다면, 키의 최 좌측 접두사가 열 검색에 사용될 수가 있다.)

MyISAM 키 캐시

공유 키 캐시 접속

다중 키 캐시

중간 삽입(Midpoint Insertion) 전략

인덱스 프리 로딩(Preloading)

키 캐시 블럭 크기

키 캐시 재 구축하기

디스크의 I/O를 최소화 하기 위해서, MyISAM 스토리지 엔진은 대부분의 데이터 베이스 시스템이 사용하고 있는 전략을 사용하고 있다. 이 엔진은 메모리에 있는 테이블 블럭 중에서 가장 자주 사용 되는 것의 정보를 보관하기 위한 캐시 매커니즘을 사용한다:

• 인덱스 블럭의 경우, key cache (또는 key buffer)라고 불리는 특정 구조를 관리한다. 이 구조는 가장 많이 사용된 인덱스 블럭이 있는 블럭 버퍼의 숫자를 가진다.
• 데이터 블럭의 경우, MySQL는 특별한 캐시를 사용하지 않는다. 대신에 이 시스템은 원 (native) OS 파일 시스템 캐시에 의존을 한다.

이 섹션에서는 우선 MyISAM 키 캐시의 기본 동작 원리에 대해 설명을 한 후에, 키 캐시 성능을 개선시킬 수 있는 기능들과 캐시 동작을 보다 효과적으로 제어할 수 있는 방법에 대해서 설명 하기로 한다:

• 여러 개의 쓰레드가 하나의 캐시에 동시에 접속을 할 수 있게 되었다.
• 여러 개의 키 캐시를 설정할 수 있고 특정 캐시에 테이블 인덱스를 할당할 수도 있다.

키 캐시 크기는 key_buffer_size 시스템 변수를 사용한다. 만일 이 변수가 0(zero)으로 설정이 된다면, 아무런 키 캐시도 사용되지 않는다. 키 캐시는 key_buffer_size 값이 너무 작아서 블럭 버퍼 (8)의 최소 숫자를 할당할 수 없다면 키 캐시는 사용되지 않는다.

키 캐시가 동작을 하지 않게 되면, 인덱스 파일은 OS가 제공하는 원 (native) 파일 시스템 버퍼만을 사용해서 접속하게 된다. (달리 표현하면, 테이블 인덱스 블럭은 테이블 데이터 블럭에 적용된 동일한 전략을 사용해서 접속하게 되는 것이다.)

인덱스 블럭은 MyISAM 인덱스 파일에 대한 연속적인 접속 단위이다. 보통의 경우, 인덱스 블럭의 크기는 인덱스 B-트리의 노드 크기와 같다. (인덱스는 B-트리 데이터 구조를 사용해서 디스크에 표시된다. 트리의 맨 밑에 있는 노드는 잎사귀 (leaf)노드이다. 잎사귀 노드 위의 것들은 잎사귀 노드가 아니다.)

키 캐시 구조안에 있는 모든 블럭 버퍼는 쿠기가 동일하다. 이 크기는 케이블 인덱스 블럭의 크기와 같거나, 크거나, 또는 작을 수가 있다. 보통의 경우, 이 두 값들 중의 하나는 다른 하나의 몇 배수가 된다.

테이블 인덱스 블럭으로부터 데이터를 접속할 때, 서버는 우선 이것이 키 캐시의 다른 블럭 버퍼에서 사용 가능한 것인지를 검사한다. 만일 가능 하다면, 서버는 디스크가 아닌 키 캐시에 있는 데이터를 접속한다. 즉, 서버는 캐시에서 읽어 오거나 캐시에 쓰게 된다. 그렇지 않을 경우에는, 서버는 서로 다른 테이블 인덱스 블록 (또는 블럭)을 가지고 있는 캐시 블럭 버퍼를 하나 선택한 후에 요청 받은 테이블 인덱스 블럭 복사본을 가지고 그것을 대체한다. 새로운 인덱스 블럭이 캐시에 생기게 되면, 인덱스 데이터는 즉시 접속될 수가 있게 된다.

만일 대체용으로 선택된 블럭이 수정되면, 그 블럭은 “오염 (dirty)”된 것으로 간주된다. 이와 같은 경우가 발생하면, 대체를 하기 전에, 그 내용물을 테이블 인덱스로 플러시 해 버린다.

일반적으로 서버는 LRU (Least Recently Used) 전략을 따른다: 대체용으로 블럭을 선택할 때, 서버는 인덱스 블럭 중에 최근에 가장 덜 사용된 것을 고른다. 이러한 선택을 보다 쉽게 하기 위해서, 키 캐시 모듈은 사용되는 모든 블럭을 특별한 큐 (LRU chain)로 관리한다. 하나의 블럭을 접속하면, 이것은 큐의 맨 마지막으로 들어간다. 블럭을 대체할 필요가 생기면, 큐의 맨 처음에 있는 블럭이 최근에 가장 덜 사용된 것이 되고 이 목적에 대한 첫 번째 후보가 되는 것이다.

공유 키 캐시 접속

쓰레드는 아래의 조건에 관련하여 키 캐시 버퍼를 동시에 접속할 수가 있다:

• 업데이트가 되지 않는 버퍼는 여러 개의 쓰레드가 접속할 수 있다.
• 업데이트가 되는 버퍼는 업데이트가 완료될 때까지 이 버퍼를 사용하고자 하는 쓰레드를 대기 상태로 만든다.
• 여러 개의 쓰레드는 서로 간에 간섭을 하지 않는 한 캐시 블럭을 대체하도록 하는 요청을 초기화 시킬 수 있다 (즉, 쓰레드들이 서로 다른 인덱스 블럭을 필요로 하는 한, 서로 다른 캐시 블럭이 대체되게끔 한다).

키 캐시에 대한 공유 접속은 서버의 성능을 획기적으로 개선 시켜 준다.

다중 키 캐시

키 캐시에 대한 공유 접속이 서버의 성능을 개선 시켜 주기는 하지만 쓰레드 간의 전반적인 경쟁을 해소시키지는 못한다. 쓰레드들은 여전히 키 캐시 버퍼 접속을 관리하는 관리 구조에 대해서 서로 경쟁을 한다. MySQL은 캐 캐시 접속 경쟁을 해소하기 위한 다중 키 캐시도 함께 제공을 한다. 이 기능을 통해 여러분은 서로 다른 캐 캐시에 서로 별도의 테이블 인덱스를 할당할 수가 있게 된다.

다중 키 캐시가 존재하는 경우, 서버는 주어진 MyISAM 테이블에 쿼리를 실행할 때 어떤 캐시를 사용해야 하는지를 알고 있어야 한다. 디폴트로는, 모든 MyISAM 테이블 인덱스가 디폴트 키 캐시에 캐시된다. 테이블 인덱스를 특정 키 캐시에 할당하기 위해서는 CACHE INDEX 명령문을 사용한다. 예를 들면, 아래의 명령문은 테이블 t1, t2, 및 t3 로 부터 hot_cache라는 이름의 키 캐시를 할당한다.

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;

+---------+--------------------+----------+----------+

| Table   | Op                 | Msg_type | Msg_text |

+---------+--------------------+----------+----------+

| test.t1 | assign_to_keycache | status   | OK       |

| test.t2 | assign_to_keycache | status   | OK       |

| test.t3 | assign_to_keycache | status   | OK       |

+---------+--------------------+----------+----------+

CACHE INDEX 명령문이 참조하는 키 캐시는 SET GLOBAL 파라미터 설정 명령문을 가지고 그 크기를 설정하거나 또는 서버 스타트업 옵션을 사용해서 생성을 할 수가 있다. 예를 들면:

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

키 캐시를 없애기 위해서는, 그 크기를 0으로 설정한다: 



mysql> SET GLOBAL keycache1.key_buffer_size=0;

디폴트 키 캐시는 없앨 수 없다는 것을 알아두기 바란다: 



mysql> SET GLOBAL key_buffer_size = 0;

 

mysql> SHOW VARIABLES LIKE 'key_buffer_size';

+-----------------+---------+

| Variable_name   | Value   |

+-----------------+---------+

| key_buffer_size | 8384512 |

+-----------------+---------+

키 캐시 변수는 이름과 컴퍼넌트 (component)를 가지고 있는 구조 시스템 변수 (structured system variable)이다. keycache1.key_buffer_size의 경우, keycache1는 캐시 변수의 이름이며 key_buffer_size는 컴퍼넌트가 된다.

테이블 인덱스는 서버가 스타트된다. 키 캐시를 제거하면, 여기에 할당된 모든 인덱스는 디폴트 키 캐시에 재 할당이 된다.

업무를 바쁘게 처리하는 서버의 경우에는 세 가지 키 캐시를 사용하는 전략을 권장한다

• “핫 (hot)” 키 캐시: 모든 키 캐시에 할당된 공간의 20% 이상을 차지하는 키 캐시. 업데이트는 없이 주로 검색용으로 사용하는 테이블용으로 이 키 캐시를 사용한다.
• “콜드 (cold)” 키 캐시: 모든 키 캐시에 할당된 공간의 20% 정도를 차지하는 키 캐시. 임시 테이블과 같이 중간 크기의 수정용 테이블로는 이 키 캐시를 사용한다.
• “웜 (warm)” 키 캐시: 모든 키 캐시에 할당된 공간 (space)의 60% 이상을 차지하는 키 캐시. 다른 모든 테이블이 디폴트로 사용하도록 이 캐시를 디폴트 키 캐시로 적용한다.

세 가지 키 캐시를 사용하는 것이 유익한 이유 중의 하나는, 하나의 키 캐시 구조에 대한 접속이 다른 키 캐시에 대한 접속을 막지 않는다는 것이다. 하나의 캐시에 할당된 테이블에 접속을 하는 명령문은 다른 캐시에 할당된 테이블에 접속을 하는 명령문과 경쟁을 하지 않게 된다. 따라서, 아래와 같은 여러 가지 이유로 인해 성능이 많이 개선될 것이다:

• 핫 캐시는 데이터 추출용 쿼리를 위해서만 사용되기 때문에, 이것이 가지고 있는 데이터는 결코 수정이 되지 않는다. 결과적으로, 하나의 인덱스 블럭을 디스크에서 가져올 필요가 있을 때마다, 대체용으로 선택된 캐시 블럭의 내용물을 우선 플러시 할 필요가 없어지게 된다.
• 핫 캐시에 할당된 인덱스의 경우, 만일 인덱스 스캔을 요구하는 쿼리가 존재하지 않는다면, 인덱스 B-트리의 잎사귀 노드가 아닌 것에 대응하는 인덱스 블럭이 캐시 안에 있을 가능성이 매우 높게 된다.
• 임시 테이블에 대해서 매우 자주 실행된 업데이트 동작은 업데이트가 된 노드가 캐시에 있을 경우에는 매우 빠르게 실행이 되며 디스크에서 먼저 읽어올 필요가 없어지게 된다. 만일 임시 테이블의 인덱스 크기가 콜드 키 캐시의 크기와 비슷하다면, 업데이트 노드가 캐시 안에 있을 가능성이 매우 높게 된다.

CACHE INDEX는 테이블과 키 캐시 간의 결합 (association)을 설정하지만, 서버가 재 시작되면 이 결합은 없어진다. 만일 서버가 시작할 때 마다 이 결합이 이루어 지도록 만들고자 한다면, 옵션 파일을 사용하는 것이 하나의 방법이다: 캐 캐시를 구성하는 변수 설정을 옵션 파일에 포함시키고, 실행될 CACHE INDEX 명령문을 가지고 있는 파일 이름을 init-file 옵션에 지정해 준다. 예를 들면:

key_buffer_size = 4G

hot_cache.key_buffer_size = 2G

cold_cache.key_buffer_size = 2G

init_file=/path/to/data-directory/mysqld_init.sql

mysqld_init.sql 안에 있는 명령문은 서버가 시작될 때마다 실행된다. 파일 안에서는 라인 당 하나의 SQL 명령문을 사용해야 한다. 다음 예제는 몇몇 테이블을 hot_cache 와 cold_cache에 할당하는 것이다:

CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache

CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache

중간 삽입(Midpoint Insertion) 전략

키 캐시 관리 시스템은 복원 (evict)해야 할 키 캐시 블럭을 선택하기 위해서 LRU 전략을 디폴트로 사용하지만, 소위 중간 삽입 (Midpoint Insertion) 전략이라고 불리는 보다 복잡한 방식도 지원을 한다

중간 삽입 전략을 사용하면 LRU 체인 (chain)은 두 부분으로 나뉘어 진다: 핫 서브 체인 (hot sub-chain) 및 웜 서브 체인 (warm sub-chain). 두 부분이 나눠지는 경계는 정해지지는 않지만, 키 캐시 관리 시스템은 웜 부분이 항상 키 캐시 블럭의 최소 key_cache_division_limit 를 가질 수 있도록 “너무 작지 않게” 만든다. key_cache_division_limit는 구조 키 캐시 변수의 컴퍼넌트이기 때문에, 이 값은 캐시 당 설정되어야 하는 파라미터가 된다.

인덱스 블럭을 테이블에서 키 캐시로 읽어 들이면 웜 서브 체인 (warm sub-chain)의 맨 마지막에 들어가게 된다. 특정 숫자 만큼 히트 (블럭에 대한 접속)가 되고 나면, 이것은 핫 서브 체인 (hot sub-chain)으로 개선된다. 현재의 버전으로 보면, 블럭(3)으로 개선 (promote)되는데 필요한 히트 수는 모든 인덱스 블럭에 대해서 동일하게 적용된다.

핫 서브 체인으로 개선된 블럭은 체인의 맨 마지막에 위치하게 된다. 그런 후에, 그 블럭은 이 서브 체인 안에서 순환된다. 만일 이 블럭이 서브 체인의 처음 부분에 오랫동안 있게 된다면, 이것은 다시 웜 체인으로 내려가게 된다. 이렇게 되는 시간은 키 캐시의 key_cache_age_threshold 컴퍼넌트 값을 가지고 판단된다.

N 개의 블럭을 가지고 있는 키 캐시의 경우, 쓰레드홀드 (threshold)는 마지막 N × key_cache_age_threshold / 100개의 히트 내에서는 접속하지 않은 핫 서브 체인의 시작 시점 블럭은 웜 서브 체인의 시작 점으로 이동되어야 한다고 규정 한다. 그렇게 되면, 이것이 복원 (eviction)을 위한 첫 번째 후보가 되는데, 그 이유는 대체를 위한 블럭은 항상 웜 서브 체인의 처음에서 가져오기 때문이다.

중간 삽입 전략을 통해 여러분은 캐시 안에 보다 가치가 있는 블럭을 유지할 수 있게 된다. 만일 여러분이 일반적인 LRU 전략을 선호한다면, key_cache_division_limit 값을 디폴트 100으로 놔 두면 된다.

인덱스 프리 로딩(Preloading)

캐시 안에 전체 인덱스 블럭을 가질 수 있을 만큼의 충분한 블럭이 존재하거나, 또는 인덱스 잎사귀 노드가 아닌 것과 상응할 만큼의 최소 블럭을 가지고 있다면, 키 캐시를 사용해서 시작을 하기 전에 인덱스 블럭과 함께 키 캐시를 읽어 오는 것이 좋을 것이다. 이렇게 미리 읽어 오면 테이블 인덱스 블럭을 가장 효과적인 방법으로 키 캐시 버퍼 안에 넣을 수가 있게 된다: 디스크에서 순차적으로 인덱스 블럭을 읽어 옴으로써.

미리 읽어 오지 않게 되면, 블럭은 여전히 쿼리가 필요로 하는 형태로 키 캐시 안에 머물러 있게 된다. 비록 블럭이 캐시 안에 있다고 하더라도, 이것들을 보관할 만큼 충분한 공간이 있기 때문에, 순차적인 순서가 아닌 랜덤 (random) 순서로 디스크에서 블럭을 가져오게 된다.

인덱스를 캐시 안으로 미리 읽어 오기 위해서는, LOAD INDEX INTO CACHE 명령문을 사용한다. 예를 들면, 아래의 명령문은 테이블 t1 과 t2의 인덱스 노드 (인덱스 블럭)을 미리 읽어 온다:

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;

+---------+--------------+----------+----------+

| Table   | Op           | Msg_type | Msg_text |

+---------+--------------+----------+----------+

| test.t1 | preload_keys | status   | OK       |

| test.t2 | preload_keys | status   | OK       |

+---------+--------------+----------+----------+

IGNORE LEAVES 수정자 (modifier)는 인덱스 잎사귀 노드가 아닌 블럭만을 미리 읽어 오도록 한다. 따라서, 위의 명령문은 t1에서는 모든 인덱스 블럭을 미리 읽어 오지만, t2에서는 잎사귀 노드가 아닌 블럭만을 읽어 오게 된다.

만일 CACHE INDEX 명령어를 사용해서 인덱스를 키 캐시에 할당하였다면, 프리로딩 (preloading)은 인덱스 블럭을 그 캐시 안으로 집어 넣게 된다. 그렇지 않은 경우에는, 인덱스는 디폴트 키 캐시 안으로 들어가게 된다.

키 캐시 블럭 크기

key_cache_block_size 변수를 사용하면 키 캐시 블럭 버퍼 크기를 개별적으로 지정할 수가 있다. 이를 통해서 인덱스 파일에 대한 I/O 연산 성능을 튜닝할 수 있게 된다.

I/O 연산은 읽기용 버퍼의 크기가 OS의 원 (native) I/O 버퍼와 크기가 동일할 때 가장 좋은 성능을 나타낸다. 하지만 키 노드의 크기를 I/O버퍼의 크기와 똑 같게 만드는 것이 전반적으로 가장 좋은 성능을 보장해 주는 것은 아니다.

현재까지는, 테이블에 있는 인덱스 블럭의 크기를 제어할 수가 없다. 이 크기는 .MYI 인덱스 파일이 생성될 때 서버에 의해 설정되며, 테이블 정의문에 존재하는 인덱스 키 크기에 따라서 정해 진다. 대부분의 경우, 이것은 I/O 버퍼 크기와 동일하게 설정된다.

키 캐시 재 구축하기

키 캐시는 파라미터 값을 업데이트하면 언제든지 재 구성할 수가 있다. 예를 들면:

mysql> SET GLOBAL cold_cache.key_buffer_size=4*1024*1024;

여러분이 key_buffer_size 또는 key_cache_block_size 키 캐시 컴퍼넌트에 현재 값과는 다른 값을 할당한다면, 서버는 캐시의 이전 구조를 없애버리며 새로운 값을 기반으로 하는 새로운 구조를 생성하게 된다. 만일 그 캐시가 더티 (dirty) 블럭을 가지고 있다면, 서버는 이것을 없애기 전에 디스크에 보관한 후에 캐시를 재 생성한다. 만일 여러분이 다른 키 캐시 파라미터를 수정할 경우에는 이러한 재 구성은 일어나지 않는다.

키 캐시를 재 구축할 때, 서버는 우선 더티 버퍼에 있는 데이터를 디스크에 플러시를 한다. 이 과정을 마치고 나면, 캐시에 있는 컨텐츠는 더 이상 사용을 할 수 없게 된다. 하지만, 캐시 재 구축은 캐시에 할당된 인덱스를 사용하고자 하는 쿼리를 막지는 않는다. 대신에, 서버는 원 (native) 파일 시스템 캐싱을 사용해서 테이블 인덱스를 직접 접속한다. 파일 시스템 캐싱은 키 캐시를 사용하는 것만큼 효율적이지 못하기 때문에, 쿼리가 실행되기는 하지만 속도가 느려지게 된다. 캐시가 재 구축된 후에는, 여기에 할당된 인덱스를 캐싱하기 위해 다시 사용 가능한 상태가 되고, 인덱스에 대한 파일 시스템 캐싱 사용은 중단된다.

MyISAM 인덱스 통계 값 콜렉션

스토리지 엔진은 옵티마이저가 사용한 테이블에 대한 통계 정보를 수집한다. 테이블 통계치는 값 그룹 (value group)을 기반으로 하며, 여기에서 값 그룹은 동일한 키 접두사 값을 가지고 있는 열 셋이다. 옵티마이저의 경우, 중요한 통계 값은 평균 값 그룹 크기 (average value group size)가 된다.

MySQL은 아래의 방식으로 평균 값 그룹 크기를 사용한다:

• 얼마나 많은 열이 각 ref 접속을 위해 읽혀져야 하는지를 추정하기 위해
• 파샬 조인 (partial join)이 만들어 내는 열의 숫자를 추정하기 위해: 즉, 이러한 형태의 연산이 만들어낼 수 있는 열의 숫자:
  

  (...) JOIN tbl_name ON tbl_name.key = expr
  
  

인덱스에 대한 평균 값 그룹의 크기가 증가할때마다 룩업 (lookup) 당 열의 평균 숫자가 증가하기 때문에, 인덱스는 이러한 두 가지 목적에서는 그리 유용한 것이 되지 못한다: 최적화 목적으로 인덱스를 사용하는 것이 좋을 경우에는, 각 인덱스 값이 테이블에 있는 열의 숫자 보다 작게 되는 것이 바람직하다. 주어진 인덱스 값이 많은 수의 열을 만들어 낸다면, 인덱스는 덜 유용하게 되며 MySQL은 이것을 덜 사용하게 된다.

평균 값 그룹 크기는 테이블 기수 (cardinality)와 관련이 있고, 이것은 값 그룹의 숫자가 된다. SHOW INDEX 명령문은 N/S에 기반한 기수 값을 표시하는데, 여기서 N은 테이블에 있는 열의 숫자이며 S는 평균 값 그룹 크기가 된다. 이 비율은 테이블에 있는 값 그룹의 추정적인 숫자를 만들게 된다.

<=> 비교 연산자를 기반으로 하는 조인 (join)의 경우, NULL은 다른 값들과 틀리게 취급되지 않는다: 모든 다른 N에 대해서 N <=> N 인 것 같이, NULL <=> NULL이 된다.

하지만, = 연산자에 기초한 조인 (join)의 경우에는, NULL은 NULL 값이 아닌 것과 틀리게 취급된다: expr1 또는 expr2 (또는 둘 모두)가 NULL이면, expr1 = expr2 는 트루 (true)가 아니다. 이것은 tbl_name.key = expr 형태의 비교에 대한 ref 접속에 영향을 준다: MySQL은 expr 의 현재 값이 NULL일 경우에는 비교 연산이 트루가 아니기 때문에 테이블을 접속할 수가 없게 된다.

= 비교 연산의 경우에는, 테이블 안에 얼마나 많은 NULL 값이 존재하느냐는 문제가 되지 않는다. 최적화를 위해서는 NULL이 아닌 값 그룹의 평균 크기 필요하다. 하지만, MySQL은 아직까지는 그러한 평균 크기를 수집하거나 사용하는 것을 허용하지 않고 있다.

MyISAM 테이블의 경우에는 myisam_stats_method 시스템 변수를 사용해서 테이블 통계 수집 전반을 관리할 수가 있다. 이 변수는 두 가지의 가능 값을 가지는데, 그 차이는 다음과 같다:

• myisam_stats_method가 nulls_equal이면, 모든 NULL 값은 서로 같은 것으로 취급된다. (즉, 모두가 하나의 단일 값 그룹 형태를 갖는다).
  만일 NULL 값 그룹 크기가 평균 비- NULL 값 그룹 크기보다 크다면, 이 방식은 평균 값 그룹 크기를 보다 크게 만든다. 옵티마이저는 이것이 비-NULL 값을 조사하는 조인 (join)에 비해 덜 유익한 것으로 판단한다. 결론적으로는, nulls_equal 방식은 옵티마이저가 ref 접속을 위해서는 인덱스를 사용하지 못하도록 만들어 버린다.
• myisam_stats_method가 nulls_unequal이 되면, NULL 값은 동일하지 않은 것으로 간주한다. 대신에, 각 NULL 값은 크기가 1인 서로 다른 값 그룹을 형성한다.
  만일 여러분이 많은 NULL 값을 가지고 있다면, 이 방식은 평균 값 그룹의 크기를 작게 만든다. 평균 비-NULL 값 그룹의 크기가 큰 경우에 NULL 값을 크기 1의 그룹처럼 계산하면, 옵티마이저는 비-NULL 값을 찾는 조인에 대한ulls_unequal 방식은 다른 방식이 더 효과적일 경우에도, 옵티마이저로 하여금 ref 룩업(lookup)에 대해서는 이 방식을 사용하도록 만드는 것이다.

만일 여러분이 =가 아니라 <=>를 사용하는 조인을 많이 사용할 생각이라면, NULL 값은 비교문에서 더 이상 특별한 것이 되지 않으며, 하나의 NULL은 다른 것과 동일하게 된다. 이와 같은 경우에는, nulls_equal이 적절한 통계 방법이 된다.

myisam_stats_method 시스템 변수는 글로벌 및 세션 값을 가진다. 글로벌 값을 설정하면 모든 MyISAM 테이블에 대한 MyISAM 통계치 수집에 영향을 미치게 된다. 세션 값을 설정하면 현재의 클라이언트 접속에 대한 통계 수집에만 영향을 준다. 이것은 myisam_stats_method 세션 값을 설정함으로써 다른 클라이언트에 영향을 주지 않은 채로 주어진 방식을 사용해서 테이블 통계치를 재 생성하도록 만든다는 것을 의미한다.

테이블 통계치를 재 생성하기 위해서는 아래의 방식 중에 하나를 사용할 수가 있다

• myisam_stats_method를 설정한 후에 CHECK TABLE 명령문을 입력한다
• myisamchk --stats_method=method_name -analyze를 실행한다
• 테이블을 변경해서 그 통계치가 이전 것이 되도록 한 후에 (예를 들면, 열을 삽입한 후에 그것을 삭제), myisam_stats_method를 설정하고 ANALYZE TABLE 명령문을 입력한다

myisam_stats_method를 사용할 경우에는 몇 가지 사항에 주의를 하도록 한다:

• 위에서 설명한 바와 같이, 여러분은 스스로 테이블 통계치를 명확하게 수집할 수가 있다. 하지만, MySQL역시 통계치를 자동으로도 수집할 수가 있다. 예를 들면, 테이블에 대한 명령문을 실행하는 도중에 명령문 중 하나가 테이블을 수정한다면, MySQL은 통계치를 수집하게 된다. (이것은 대량 삽입, 삭제, 또는 몇몇ALTER TABLE 명령문에서 발생할 수 있다.) 이런 일이 발생한다면, 통계치는 그 시점의 myisam_stats_method 값을 사용해서 수집된다. 따라서, 만일 여러분이 하나의 방법을 사용해서 통계치를 수집하기는 하지만, 테이블 통계치가 나중에 자동으로 수집될 때 myisam_stats_method가 다른 방법을 사용하도록 설정되어 있다면, myisam_stats_method에 설정된 방법이 사용되게 된다.
• 주어진 MyISAM 테이블을 위한 통계치를 어떤 방식을 사용해서 만들었는지는 알 수가 없다.
• myisam_stats_method는 MyISAM 테이블에만 적용된다. 다른 스토리지 엔진은 한 가지 방법만을 가지고 테이블 통계치를 수집한다. 일반적으로 nulls_equal 방식과 유사 것을 사용한다.

MySQL은 테이블을 어떻게 열고 닫는가

mysqladmin status 명령문을 실행하면, 아래와 비슷한 것이 나올 것이다: 



Uptime: 426 Running threads: 1 Questions: 11082

Reloads: 1 Open tables: 12

여러분이 단지 6개의 테이블만을 가지고 있다면, Open tables 값이 12라는 것은 다소 수수께끼와 같을 것이다.

MySQL은 다중-쓰레드 방식이기 때문에 주어진 테이블에 대해서 쿼리를 동시에 입력하는 클라이언트가 많이 존재하게 된다. 동일한 테이블에 있는 서로 다른 상태의 클라이언트 쓰레드로 인한 문제를 최소화 하기 위해서는, 각각의 동시 쓰레드에 대해서 테이블을 서로 독립적으로 열어야 한다. 이 방식이 메모리를 추가적으로 사용하기는 하지만 일반적으로 성능을 향상 시켜준다. MyISAM 테이블을 사용한다면, 테이블을 오픈한 각 클라이언트에 대해서는 데이터 파일용 파일 디스크립터 (descriptor)가 별도로 하나씩 필요하게 된다. (반대의 경우, 인덱스 파일 디스크립터는 모든 쓰레드 간에 공유된다.)

table_cache, max_connections, 그리고 max_tmp_tables 시스템 변수는 서버가 오픈하는 최대 파일 숫자를 결정한다. 만일 여러분이 이 값들 중 하나 또는 여러 개를 증가 시킨다면, 여러분은 프로세스 당 오픈 파일 디스크립터 (open file descriptor)의 갯수를 늘릴 수 있을 것이다. 많은 OS가 여러 가지 방법을 통해 오픈 파일의 갯수를 늘릴 수 있도록 허용을 하고 있다.

table_cache는 max_connections과 관련이 있다. 예를 들면, 200개의 동시 구동 접속의 경우, 테이블 캐시의 크기는 최소 200 × N로 만들 수가 있는데, 여기에서N 은 여러분이 실행하는 쿼리 안에 있는 조인 (join) 당 테이블의 최대 숫자가 된다. 여러분은 또한 반드시 임시 테이블 및 임시 파일에 대한 여분의 파일 디스크립터를 보관하고 있어야 한다.

여러분은 지금 사용하고 있는 OS가 table_cache 설정에 포함되어 있는 오픈 파일 디스크립터의 숫자를 다룰 수 있는지를 확인해야 한다. 만일 table_cache가 너무 크다면, MySQL은 파일 디스크립터 오류를 발생 시킨 후에 쿼리를 실행하지 못하며, 또한 매우 불안정하게 된다. 또한, MyISAM 스토리지 엔진은 서로 다른 오픈 테이블별로 두 개의 파일 디스크립터를 필요로 한다는 점을 알고 있어야 한다. mysqld의 스타트 업 옵션인 --open-files-limit를 사용하면 MySQL에서 사용 가능한 파일 디스크립터의 숫자를 늘릴 수가 있다.

파일 오픈용 캐시는 table_cache 엔트리 레벨에서 관리된다. 디폴트 값은 64이다; 이 값은 --table_cache 옵션으로 변경 시킬 수가 있다. MySQL은 쿼리를 실행하기 위한 것 보다 많은 수의 테이블을 임시로 오픈 한다는 점을 알아두자.

MySQL은 아래의 환경이 되면 사용되지 않은 테이블을 닫고 캐시에서 삭제해 버린다:

• 캐시가 가득 차고 쓰레드가 캐시 안에 없는 테이블을 열고자 시도할 경우.
• 캐시가 table_cache 엔트리에 있는 것 보다 많은 것을 가지고 있고 캐시 안에 있는 테이블이 더 이상 어떠한 쓰레드에 의해서도 사용되지 않을 경우.
• 테이블 플러싱 연산이 발생될 경우. 이것은 누군가가 FLUSH TABLES 명령문을 입력하거나, mysqladmin flush-tables 또는 mysqladmin refresh 명령어를 실행할 때

테이블 캐시가 가득 차게 되면, 서버는 사용할 캐시 엔트리를 저장하기 위해서 아래의 과정을 진행한다:

• 사용 중에 있지 않는 테이블은 풀어 놓는다.
• 만일 오픈해야 할 테이블이 필요하기는 하지만 캐시가 가득 찾기 때문에 풀어 놓을 테이블이 없게 된다면, 캐시는 필요한 만큼 임시로 확장이 된다.

캐시가 임시로 확장이 되고 테이블이 사용되지 않는 상태가 되면, 테이블은 닫히고 캐시에서 풀어져 나오게 된다.

테이블은 각각의 동시 접속에 대해서 열리게 된다. 이것은 만일 두 개의 쓰레드가 동일한 테이블에 접속으로 하거나 하나의 쓰레드가 동일한 쿼리에서 한 테이블을 두 번 접속을 할 경우에는 테이블이 두 번 열려야 한다는 것 있는 하나의 엔트리를 필요로 한다. MyISAM 테이블이 처음 열릴 때에는 두 개의 파일 디스크립터를 가져 온다: 하나는 테이더 파일용이고 다른 하나는 인덱스 파일용 임. 테이블을 추가적으로 사용하는 경우에는 데이터 파일에 대해서 오직 하나의 파일 디스크립터만을 가져온다. 인덱스 파일 디스트립터는 모든 쓰레드 간에 공유가 된다.

만일 여러분이 HANDLER tbl_name OPEN 명령문을 가지고 테이블을 오픈한다면, 지정 테이블 오브젝트 (dedicated table object)는 그 쓰레드에 할당이 된다. 이 테이블 오브젝트는 다른 쓰레드가 공유할 수 없으며, 쓰레드가 HANDLER tbl_name CLOSE를 호출하거나 쓰레드를 종료하기 전까지는 닫히지 않는다. 이런 일이 발생을 하면, 테이블은 테이블 캐시 안으로 다시 들어가게 된다 (캐시가 가득 차지 않았을 경우).

mysqld의 상태 변수인 Opened_tables을 검사함으로써 여러분이 사용하는 테이블 캐시가 너무 작은지를 알아볼 수가 있다:

mysql> SHOW STATUS LIKE 'Opened_tables';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Opened_tables | 2741  |

+---------------+-------+

만일 이 값이 너무 크다면, 여러분은 테이블 캐시의 크기를 늘려주어야 한다.

동일 데이터 베이스에서 많은 테이블을 생성하기 위한 드로우백 (Drawback)

만일 여러분이 동일한 데이터 디렉토리 안에 많은 수의 MyISAM 테이블을 가지고 있다면, 테이블을 열고, 닫고, 그리고 생성하는 동작이 느려지게 된다. 만일 여러분이 많은 수의 테이블에서 SELECT 명령문을 실행한다면, 테이블 캐시가 가득 찬 후에는 오버 헤드가 발생하게 된다. 이럴 경우에는 테이블 캐시를 증가 시켜서 오버 헤드를 줄이도록 한다.