Spec-Zone .ru
спецификации, руководства, описания, API
|
Приложения, которые контролируют базу данных, могут сделать частое использование INFORMATION_SCHEMA
таблицы. Определенные типы запросов для INFORMATION_SCHEMA
таблицы могут быть
оптимизированы, чтобы выполниться более быстро. Цель состоит в том, чтобы минимизировать операции файла
(например, сканируя каталог или открывая табличный файл), чтобы собрать информацию, которая составляет эти
динамические таблицы. Эта оптимизация действительно имеет эффект на то, как сопоставления используются для
поисков в INFORMATION_SCHEMA
таблицы. Для получения дополнительной информации см.
Раздел 10.1.7.9, "Сопоставление и INFORMATION_SCHEMA
Поискы".
1) Попытайтесь использовать постоянные справочные значения для имен базы данных и имен
таблиц в WHERE
пункт
Можно использовать в своих интересах этот принцип следующим образом:
Чтобы искать базы данных или таблицы, используйте выражения, которые оценивают к константе, такой как литеральные значения, функции, которые возвращают константу, или скалярные подзапросы.
Избегите запросов, которые используют непостоянное справочное значение имени базы данных (или никакое справочное значение), потому что они требуют, чтобы сканирование каталога данных нашло соответствие имен каталогов базы данных.
В пределах базы данных избегите запросов, которые используют непостоянное справочное значение имени таблицы (или никакое справочное значение), потому что они требуют, чтобы сканирование каталога базы данных нашло соответствие табличных файлов.
Этот принцип применяется к INFORMATION_SCHEMA
таблицы, показанные в следующей
таблице, которая показывает столбцы, для которых постоянное справочное значение позволяет серверу избежать
сканирования каталога. Например, если Вы выбираете из TABLES
, использование постоянного справочного значения для TABLE_SCHEMA
в WHERE
пункт позволяет сканированию
каталога данных избежаться.
Таблица | Столбец, чтобы определить, чтобы избежать сканирования каталога данных | Столбец, чтобы определить, чтобы избежать сканирования каталога базы данных |
---|---|---|
COLUMNS |
TABLE_SCHEMA |
TABLE_NAME |
KEY_COLUMN_USAGE
|
TABLE_SCHEMA |
TABLE_NAME |
PARTITIONS
|
TABLE_SCHEMA |
TABLE_NAME |
REFERENTIAL_CONSTRAINTS |
CONSTRAINT_SCHEMA |
TABLE_NAME |
STATISTICS
|
TABLE_SCHEMA |
TABLE_NAME |
TABLES |
TABLE_SCHEMA |
TABLE_NAME |
TABLE_CONSTRAINTS
|
TABLE_SCHEMA |
TABLE_NAME |
TRIGGERS |
EVENT_OBJECT_SCHEMA |
EVENT_OBJECT_TABLE |
VIEWS
|
TABLE_SCHEMA |
TABLE_NAME |
Преимущество запроса, который ограничивается определенным постоянным именем базы данных, - то, что проверки должны быть сделанными только для именованного каталога базы данных. Пример:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'test';
Использование литерального имени базы данных test
позволяет серверу проверить
только test
каталог базы данных, независимо от того, сколькими там могли бы быть
базы данных. В отличие от этого, следующий запрос менее эффективен, потому что он требует, чтобы сканирование
каталога данных определило, какие имена базы данных соответствуют образец 'test%'
:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA LIKE 'test%';
Для запроса, который ограничивается определенным постоянным именем таблицы, потребность проверок быть сделанным только для именованной таблицы в пределах соответствующего каталога базы данных. Пример:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
Использование литерального имени таблицы t1
позволяет серверу проверить только
файлы на t1
таблица, независимо от того, сколькими таблицы там могли бы быть в
test
база данных. В отличие от этого, следующий запрос требует сканирования test
каталог базы данных, чтобы определить, какие имена таблиц соответствуют
образец 't%'
:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't%';
Следующий запрос требует, чтобы сканирование каталога базы данных определило соответствие имен базы данных для
образца 'test%'
, и для каждой базы данных соответствия, это требует, чтобы
сканирование каталога базы данных определило соответствие имен таблиц для образца 't%'
:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'test%' AND TABLE_NAME LIKE 't%';
2) Запишите запросы, которые минимизируют число табличных файлов, которые должны быть открыты
Для запросов, которые обращаются к определенному INFORMATION_SCHEMA
столбцы
таблицы, несколько оптимизации доступны, которые минимизируют число табличных файлов, которые должны быть
открыты. Пример:
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'test';
В этом случае, после того, как сервер отсканировал каталог базы данных, чтобы определить имена таблиц в базе
данных, те имена становятся доступными без дальнейших поисков файловой системы. Таким образом, TABLE_NAME
не требует, чтобы никакие файлы были открыты. ENGINE
(механизм хранения), значение может быть определено, открывая таблицу .frm
файл,
не касаясь других табличных файлов такой как .MYD
или .MYI
файл.
Некоторые значения, такой как INDEX_LENGTH
для MyISAM
таблицы, потребуйте открытия .MYD
или .MYI
файл
также.
Открывающие файл типы оптимизации обозначаются таким образом:
SKIP_OPEN_TABLE
: Табличные файлы не должны быть
открыты. Информация уже стала доступной в пределах запроса, сканируя каталог базы данных.
OPEN_FRM_ONLY
: Только таблица .frm
файл должен быть открытым.
OPEN_TRIGGER_ONLY
: Только таблица .TRG
файл должен быть открытым.
OPEN_FULL_TABLE
: Неоптимизированный информационный
поиск. .frm
, .MYD
, и .MYI
файлы должны быть открыты.
Следующий список указывает, как предыдущие типы оптимизации применяются к INFORMATION_SCHEMA
столбцы таблицы. Для таблиц и столбцов, не названных, ни одна из оптимизации не применяется.
COLUMNS
: OPEN_FRM_ONLY
применяется ко всем
столбцам
KEY_COLUMN_USAGE
: OPEN_FULL_TABLE
применяется ко всем столбцам
PARTITIONS
:
OPEN_FULL_TABLE
применяется ко всем столбцам
REFERENTIAL_CONSTRAINTS
: OPEN_FULL_TABLE
применяется ко всем столбцам
Столбец | Тип оптимизации |
---|---|
TABLE_CATALOG |
OPEN_FRM_ONLY |
TABLE_SCHEMA |
OPEN_FRM_ONLY |
TABLE_NAME |
OPEN_FRM_ONLY |
NON_UNIQUE |
OPEN_FRM_ONLY |
INDEX_SCHEMA |
OPEN_FRM_ONLY |
INDEX_NAME |
OPEN_FRM_ONLY |
SEQ_IN_INDEX |
OPEN_FRM_ONLY |
COLUMN_NAME |
OPEN_FRM_ONLY |
COLLATION |
OPEN_FRM_ONLY |
CARDINALITY |
OPEN_FULL_TABLE |
SUB_PART |
OPEN_FRM_ONLY |
PACKED |
OPEN_FRM_ONLY |
NULLABLE |
OPEN_FRM_ONLY |
INDEX_TYPE |
OPEN_FULL_TABLE |
COMMENT |
OPEN_FRM_ONLY |
Столбец | Тип оптимизации |
---|---|
TABLE_CATALOG |
SKIP_OPEN_TABLE |
TABLE_SCHEMA |
SKIP_OPEN_TABLE |
TABLE_NAME |
SKIP_OPEN_TABLE |
TABLE_TYPE |
OPEN_FRM_ONLY |
ENGINE |
OPEN_FRM_ONLY |
VERSION |
OPEN_FRM_ONLY |
ROW_FORMAT |
OPEN_FULL_TABLE |
TABLE_ROWS |
OPEN_FULL_TABLE |
AVG_ROW_LENGTH |
OPEN_FULL_TABLE |
DATA_LENGTH |
OPEN_FULL_TABLE |
MAX_DATA_LENGTH |
OPEN_FULL_TABLE |
INDEX_LENGTH |
OPEN_FULL_TABLE |
DATA_FREE |
OPEN_FULL_TABLE |
AUTO_INCREMENT |
OPEN_FULL_TABLE |
CREATE_TIME |
OPEN_FULL_TABLE |
UPDATE_TIME |
OPEN_FULL_TABLE |
CHECK_TIME |
OPEN_FULL_TABLE |
TABLE_COLLATION |
OPEN_FRM_ONLY |
CHECKSUM |
OPEN_FULL_TABLE |
CREATE_OPTIONS |
OPEN_FRM_ONLY |
TABLE_COMMENT |
OPEN_FRM_ONLY |
TABLE_CONSTRAINTS
: OPEN_FULL_TABLE
применяется ко всем столбцам
TRIGGERS
:
OPEN_TRIGGER_ONLY
применяется ко всем столбцам
Столбец | Тип оптимизации |
---|---|
TABLE_CATALOG |
OPEN_FRM_ONLY |
TABLE_SCHEMA |
OPEN_FRM_ONLY |
TABLE_NAME |
OPEN_FRM_ONLY |
VIEW_DEFINITION |
OPEN_FRM_ONLY |
CHECK_OPTION |
OPEN_FRM_ONLY |
IS_UPDATABLE |
OPEN_FULL_TABLE |
DEFINER |
OPEN_FRM_ONLY |
SECURITY_TYPE |
OPEN_FRM_ONLY |
CHARACTER_SET_CLIENT |
OPEN_FRM_ONLY |
COLLATION_CONNECTION |
OPEN_FRM_ONLY |
3) Использовать EXPLAIN
определить, может ли сервер использовать INFORMATION_SCHEMA
оптимизация для запроса
Это применяется особенно для INFORMATION_SCHEMA
запросы, которые ищут информацию
больше чем от одной базы данных, которая могла бы занять много времени и воздействовать на производительность.
Extra
значение в EXPLAIN
вывод указывает, который, если таковые вообще имеются, оптимизации,
описанной ранее, сервер может использовать, чтобы оценить INFORMATION_SCHEMA
запросы. Следующие примеры демонстрируют виды информации, которую можно ожидать видеть в 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: ALLpossible_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: ALLpossible_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: ALLpossible_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: ALLpossible_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: ALLpossible_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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra:
В этом случае никакая оптимизация не применяется потому что COLLATIONS
не один из INFORMATION_SCHEMA
таблицы,
для которых оптимизация доступна.