Spec-Zone .ru
спецификации, руководства, описания, API
|
InnoDB
автоматически расширяется, каждое вторичное устройство индексируют,
добавляя столбцы первичного ключа к этому. Рассмотрите это табличное определение:
CREATE TABLE t1 ( i1 INT NOT NULL DEFAULT 0, i2 INT NOT NULL DEFAULT 0, d DATE DEFAULT NULL, PRIMARY KEY (i1, i2), INDEX k_d (d)) ENGINE = InnoDB;
Эта таблица определяет первичный ключ на столбцах (i1, i2)
. Это также определяет
вторичное устройство, индексируют k_d
на столбце (d)
,
но внутренне InnoDB
расширяется это индексирует, и обрабатывает это как столбцы
(d, i1, i2)
.
В MySQL 5.7 оптимизатор принимает во внимание, что столбцы первичного ключа расширенного вторичного устройства индексируют, определяя, как и использовать ли, которые индексируют. Это может привести к более эффективным планам выполнения запроса и лучшей производительности.
Оптимизатор может использовать расширенное вторичное устройство, индексирует для ref
, range
, и index_merge
индексируйте доступ, поскольку свободные индексируют сканирования для соединения и оптимизации сортировки, и для
MIN()
/MAX()
оптимизация.
Следующий пример показывает, как на планы выполнения влияют тем, расширялось ли использование оптимизатора
вторичный, индексирует. Предположите это t1
заполняется с этими строками:
INSERT INTO t1 VALUES(1, 1, '1998-01-01'), (1, 2, '1999-01-01'),(1, 3, '2000-01-01'), (1, 4, '2001-01-01'),(1, 5, '2002-01-01'), (2, 1, '1998-01-01'),(2, 2, '1999-01-01'), (2, 3, '2000-01-01'),(2, 4, '2001-01-01'), (2, 5, '2002-01-01'),(3, 1, '1998-01-01'), (3, 2, '1999-01-01'),(3, 3, '2000-01-01'), (3, 4, '2001-01-01'),(3, 5, '2002-01-01'), (4, 1, '1998-01-01'),(4, 2, '1999-01-01'), (4, 3, '2000-01-01'),(4, 4, '2001-01-01'), (4, 5, '2002-01-01'),(5, 1, '1998-01-01'), (5, 2, '1999-01-01'),(5, 3, '2000-01-01'), (5, 4, '2001-01-01'),(5, 5, '2002-01-01');
Теперь рассмотрите этот запрос:
EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'
Оптимизатор не может использовать первичный ключ в этом случае, потому что это включает столбцы (i1, i2)
и запрос не обращается к i2
. Вместо этого
оптимизатор может использовать вторичное устройство, индексируют k_d
на (d)
, и план выполнения зависит от того, индексируют ли расширенные, используется.
Когда оптимизатор не рассматривает, индексируют расширения, он обрабатывает индексирование k_d
как только (d)
. EXPLAIN
поскольку запрос приводит к этому результату:
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3
AND d = '2000-01-01'\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: refpossible_keys: PRIMARY,k_d key: k_d key_len: 4 ref: const rows: 5 Extra: Using where; Using index
Когда оптимизатор берет, индексируют расширения во внимание, он обрабатывает k_d
как (d, i1, i2)
. В этом случае это может использовать крайнее левое, индексируют
префикс (d, i1)
произвести лучший план выполнения:
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3
AND d = '2000-01-01'\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: refpossible_keys: PRIMARY,k_d key: k_d key_len: 8 ref: const,const rows: 1 Extra: Using index
В обоих случаях, key
указывает, что оптимизатор будет использовать вторичный,
индексируют k_d
но EXPLAIN
вывод показывает, что эти улучшения от использования расширенного
индексируют:
key_len
идет от 4 байтов до 8 байтов, указывая, что
ключевые поиски используют столбцы d
и i1
, не
только d
.
ref
оцените изменяется от const
к const,const
потому что ключевой поиск
использует две ключевых роли, не один.
rows
рассчитайте уменьшается от 5 до 1, указывая на это
InnoDB
должен должен быть исследовать меньше строк, чтобы привести к
результату.
Extra
оцените изменяется от Using
where; Using index
к Using index
. Это означает, что строки могут
быть считаны, используя только индексирование без консультационных столбцов в строке данных.
Различия в поведении оптимизатора для использования расширенных индексируют, может также быть замечен с SHOW
STATUS
:
FLUSH TABLE t1;FLUSH STATUS;SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';SHOW STATUS LIKE 'handler_read%'
Предыдущие операторы включают FLUSH TABLE
и FLUSH
STATUS
сбросить табличный кэш и очистить счетчики состояния.
Без индексируют расширения, SHOW
STATUS
приводит к этому результату:
+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Handler_read_first | 0 || Handler_read_key | 1 || Handler_read_last | 0 || Handler_read_next | 5 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 0 |+-----------------------+-------+
С индексируют расширения, SHOW STATUS
приводит к этому результату. Handler_read_next
оцените уменьшается от 5 до 1, указывая на более
эффективное использование индексирования:
+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Handler_read_first | 0 || Handler_read_key | 1 || Handler_read_last | 0 || Handler_read_next | 1 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 0 |+-----------------------+-------+
use_index_extensions
флаг optimizer_switch
системная переменная разрешает управление, принимает ли
оптимизатор столбцы первичного ключа во внимание, определяя, как использовать InnoDB
вторичное устройство таблицы индексирует. По умолчанию, use_index_extensions
включается. Проверять, индексирует ли отключение
использования расширения, улучшит производительность, использовать этот оператор:
SET optimizer_switch = 'use_index_extensions=off';
Использование индексирует расширения оптимизатором, подвергается обычным пределам на числе ключевых ролей в индексировании (16) и максимальная длина ключа (3072 байта).