8.2.4 INFORMATION_SCHEMA 쿼리 최적화

데이터베이스를 모니터하는 응용 프로그램은 INFORMATION_SCHEMA 테이블을 자주 사용할 수 있습니다. INFORMATION_SCHEMA 테이블에 대한 특정 유형의 쿼리는 빠르게 실행하도록 최적화 할 수 있습니다. 이 목표는 파일 조작 (디렉토리 스캔과 테이블 파일을 여는 등)을 최소화하고 이러한 동적 테이블 구성 정보를 수집하는 것입니다. 이러한 최적화는 INFORMATION_SCHEMA 테이블의 검색에 어떤 데이터 정렬이 사용되는지에 영향을줍니다. 자세한 내용은 섹션 10.1.7.9 "데이터 정렬과 INFORMATION_SCHEMA 검색" 을 참조하십시오.

1) WHERE 절 데이터베이스와 테이블 이름은 상수 조회 값을 사용하려고합니다

이 원칙은 다음과 같이 활용할 수 있습니다.

이 원칙은 상수의 조회 값은 서버가 디렉토리 검색을 피할 수 컬럼을 나타내고있다 다음 표에 표시된 INFORMATION_SCHEMA 테이블에 적용됩니다. 예를 들어, TABLES 에서 선택하려면 WHERE 절에서 TABLE_SCHEMA 상수 조회 값을 사용하면 데이터 디렉토리 검색을 피할 수 있습니다.

특정 상수의 데이터베이스 이름에 제한된 쿼리의 장점은 지정된 데이터베이스 디렉토리 만 확인하면되기 것입니다. 예 :

  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'test';

리터럴의 데이터베이스 이름 test 를 사용하면 데이터베이스가 몇인지에 관계없이 서버 test 데이터베이스 디렉토리 만 확인할 수 있습니다. 대조적으로, 다음 쿼리는 패턴 'test%' 에 일치하는 데이터베이스 이름을 확인하기 위해 데이터 디렉토리 검색이 필요하기 때문에 효율성이 저하됩니다.

  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA LIKE 'test %';

특정 상수 테이블 이름에 제한된 쿼리의 경우 해당 데이터베이스 디렉토리에 지정된 테이블 만 체크하는 것만으로 끝납니다. 예 :

  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'test'AND TABLE_NAME = 't1';

리터럴 테이블 이름 t1 을 사용하면 test 데이터베이스에 테이블이 몇개 있는지에 관계없이 서버는 t1 테이블의 파일 만 검사 할 수 있습니다. 대조적으로, 다음 쿼리는 패턴 't%' 에 일치하는 테이블 명을 특정하기 위해 test 데이터베이스 디렉토리 스캔이 필요합니다.

  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'test'AND TABLE_NAME LIKE 't %';

다음 쿼리는 패턴 'test%' 에 일치하는 데이터베이스 이름을 확인하기 위해 데이터 디렉토리를 스캔해야 일치하는 데이터베이스에 대해 패턴 't%' 에 일치하는 테이블 이름을 확인하기 위해 데이터베이스 디렉토리를 스캔해야합니다.

  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'test %'AND TABLE_NAME LIKE 't %';

2) 열어야하는 테이블 파일의 수를 최소화하는 쿼리를 씁니다

특정 INFORMATION_SCHEMA 테이블 컬럼을 참조하는 쿼리는 열어야하는 테이블 파일의 수를 최소화하는 몇 가지 최적화를 사용할 수 있습니다. 예 :

  SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'test';

이 경우 서버가 데이터베이스 디렉토리를 스캔하여 데이터베이스 테이블의 이름을 확인하면 추가 파일 시스템을 조회하지 않아도 그 이름을 사용할 수있게됩니다. 따라서 TABLE_NAME 파일을 열 필요가 없습니다. ENGINE (스토리지 엔진)의 값은 테이블의 .frm 파일을 열어 알아낼 수 .MYD 와 .MYI 등의 다른 테이블 파일에 액세스 할 수 없습니다.

MyISAM 테이블의 INDEX_LENGTH 등 일부 값은 .MYD 또는 .MYI 파일을 열어야합니다.

파일 오픈 최적화의 종류는 다음과 같이 표시됩니다.

다음 목록에 위의 최적화의 종류가 어떻게 INFORMATION_SCHEMA 테이블 컬럼에 적용되는지를 보여줍니다. 지정되지 않은 테이블 및 컬럼에는 최적화가 적용되지 않습니다.

3) EXPLAIN 을 사용하여 서버가 쿼리에 INFORMATION_SCHEMA 최적화를 사용할 수 있는지 여부를 결정합니다

이것은 특히 여러 데이터베이스의 정보를 검색하여 장시간 소요 성능에 영향을 미칠 수있는 INFORMATION_SCHEMA 쿼리에 적용됩니다. 앞서 최적화 중 서버가 INFORMATION_SCHEMA 쿼리의 평가에 사용할 수있는 것이 있으면, EXPLAIN 의 출력의 Extra 값으로 표시됩니다. 다음의 예는 Extra 값에 표시되는 것으로 예상되는 정보의 종류를 보여줍니다.

  mysql> EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE
     -> TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'\G
 *************************** 1. row ******************** *******
            id : 1
   select_type : SIMPLE
         table : VIEWS
          type : ALL
 possible_keys : NULL
           key : TABLE_SCHEMA, TABLE_NAME
       key_len : NULL
           ref : NULL
          rows : NULL
         Extra : Using where; Open_frm_only; Scanned 0 databases

상수의 데이터베이스 조회 값 및 테이블 조회 값을 사용하면 서버는 디렉토리 검색을 피할 수 있습니다. VIEWS.TABLE_NAME 의 참조에서는 .frm 파일 만 열어야합니다.

  mysql> EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES\G
 *************************** 1. row ******************** *******
            id : 1
   select_type : SIMPLE
         table : TABLES
          type : ALL
 possible_keys : NULL
           key : NULL
       key_len : NULL
           ref : NULL
          rows : NULL
         Extra : Open_full_table; Scanned all databases

조회 값이 지정되지 않은 ( WHERE 절이없는) 때문에, 서버는 데이터 디렉토리와 각 데이터베이스 디렉토리를 스캔해야합니다. 이와 같이 특정 된 각 테이블에 대해 테이블​​ 이름과 행 형식이 선택됩니다. TABLE_NAME 는 또한 테이블 파일을 열 필요가 없습니다 ( SKIP_OPEN_TABLE 최적화가 적용됩니다). ROW_FORMAT 는 모든 테이블 파일을 열어야합니다 ( OPEN_FULL_TABLE 이 적용됩니다). EXPLAIN 은 OPEN_FULL_TABLE ( SKIP_OPEN_TABLE 보다 부하가 크기 때문에)를보고합니다.

  mysql> EXPLAIN SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
     -> WHERE TABLE_SCHEMA = 'test'\G
 *************************** 1. row ******************** *******
            id : 1
   select_type : SIMPLE
         table : TABLES
          type : ALL
 possible_keys : NULL
           key : TABLE_SCHEMA
       key_len : NULL
           ref : NULL
          rows : NULL
         Extra : Using where; Open_frm_only; Scanned 1 database

테이블 이름 조회 값이 지정되어 있지 않기 때문에, 서버는 test 데이터베이스 디렉토리를 스캔해야합니다. TABLE_NAME 컬럼과 TABLE_TYPE 열은 각각 SKIP_OPEN_TABLE 최적화 및 OPEN_FRM_ONLY 최적화가 적용됩니다. EXPLAIN 은 OPEN_FRM_ONLY (이것은 많은 경우 부담이 크기 때문에)를보고합니다.

  mysql> EXPLAIN SELECT B.TABLE_NAME
     -> FROM INFORMATION_SCHEMA.TABLES AS A, INFORMATION_SCHEMA.COLUMNS AS B
     -> WHERE A.TABLE_SCHEMA = 'test'
     -> AND A.TABLE_NAME = 't1'
     -> AND B.TABLE_NAME = A.TABLE_NAME\G
 *************************** 1. row ******************** *******
            id : 1
   select_type : SIMPLE
         table : A
          type : ALL
 possible_keys : NULL
           key : TABLE_SCHEMA, TABLE_NAME
       key_len : NULL
           ref : NULL
          rows : NULL
         Extra : Using where; Skip_open_table; Scanned 0 databases
 *************************** 2. row ******************** *******
            id : 1
   select_type : SIMPLE
         table : B
          type : ALL
 possible_keys : NULL
           key : NULL
       key_len : NULL
           ref : NULL
          rows : NULL
         Extra : Using where; Open_frm_only; Scanned all databases;
                Using join buffer

첫 번째 EXPLAIN 출력 행의 경우 : 상수 데이터베이스 조회 값 및 테이블 조회 값에 따라 서버는 TABLES 값 디렉토리 검색을 피할 수 있습니다. TABLES.TABLE_NAME 의 참조는 또한 테이블 파일이 필요하지 않습니다.

두 번째 EXPLAIN 출력 행의 경우 : COLUMNS 테이블의 모든 값이 OPEN_FRM_ONLY 조회이기 때문에 COLUMNS.TABLE_NAME 에서 .frm 파일을 열어야합니다.

  mysql> EXPLAIN SELECT * FROM INFORMATION_SCHEMA.COLLATIONS\G
 *************************** 1. row ******************** *******
            id : 1
   select_type : SIMPLE
         table : COLLATIONS
          type : ALL
 possible_keys : NULL
           key : NULL
       key_len : NULL
           ref : NULL
          rows : NULL
         Extra :

이 경우 COLLATIONS 는 최적화를 사용할 수 INFORMATION_SCHEMA 테이블 중도 아니기 때문에 최적화가 적용되지 않습니다.