MySQL 8.0: невидимые индексы
Дата: 30.12.2016
Данная статья является переводом статьи Мартина Ханссона. Курсивом даны комментарии переводчика.
Невидимые индексы это новая возможность в MySQL 8.0, которая позволяет пометить индексы как недоступные для использования оптимизатором. Это означает, что индекс по-прежнему будет поддерживаться и обновляться по мере изменения данных, но при выполнении запроса он никак не будет использоваться (даже если в запросе есть прямое указание на его использование с помощью FORCE INDEX).
Не путайте невидимые индексы и отключенные индексы, поддерживаемые хранилищем MyISAM (отключенные индексы не обновляются при изменении данных).
Когда можно безопасно удалить индекс?
Индексы очень полезны, когда оптимизатор запросов может найти оптимальный план выполнения с их использованием. В этих случаях они могут сократить время выполнения запроса на порядки. Но важно помнить, что они также приводят к дополнительным расходам, и эта величина отнюдь не ничтожна. Многие знают, что индексы ухудшают производительность операций изменения данных (insert, update). Но, кроме того, избыточное количество индексов может отрицательно повлиять на производительность чтения, так как оптимизатор должен оценить возможность использования каждого из них. (При соединении (JOIN) нескольких таблиц количество комбинаций, которые нужно проверить оптимизатору, становится столь велико, что план запроса может составляться дольше, чем непосредственно само выполнение запроса.)
Вы можете увидеть, какие индексы не используются в настоящее время с помощью служебной базы данных SYS:
mysql> SELECT * FROM sys.schema_unused_indexes;
+---------------+-------------+------------+
| object_schema | object_name | index_name |
+---------------+-------------+------------+
| world | Country | p |
| world | Country | p_c |
+---------------+-------------+------------+
2 rows in set (0.01 sec)
Но, возможно, сервер недавно был запущен? Нет гарантии, что спустя какое-то время эти показания не изменятся. Также трудно судить о случаях, когда имеются избыточные индексы, и вы ожидаете, что удаление индекса приведет к использованию другого индекса. Может быть, у вас есть подозрение, что конкретный индекс не оправдан?
(Иными словами: заранее сложно гарантировать, что удаление индекса не ухудшит производительность. А проверять методом "тыка" не хочется, так как удаление/создание индекса затратная операция.)
Используйте невидимые индексы. Если индекс отмечен как невидимый, то оптимизатор не сможет его использовать. При этом индекс по-прежнему будет обновляться, проверять уникальность и ссылочные ограничения. И если вы поймете, что его удаление - плохая идея, то сможете вернуть индекс обратно к видимым в одно мгновение.
Есть 2 основных случая, когда полезно использовать невидимые индексы:
- 1. Удаление индексов. Перед окончательным удалением желательно иметь возможность оценить последствия. В случае, если вы ошиблись, то сделать индекс снова видимым будет очень быстро, так как реально произошло только изменение метаданных. Например:
ALTER TABLE Country ALTER INDEX c INVISIBLE;
2. Поэтапное внедрение. При добавлении индексов, важно учитывать, что они могут нежелательным образом изменить существующие планы выполнения запросов. Невидимые индексы дают возможность предварительно провести дорогостоящую операцию создания индекса, и затем в удобный момент проверить влияние индекса на производительность. Например:
ALTER TABLE Country ADD INDEX c (Continent) INVISIBLE;
# after some time
ALTER TABLE Country ALTER INDEX c VISIBLE;
Видимость индексов можно наблюдать в метаданных таблицы:
mysql> SHOW INDEXES FROM my_table;
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| my_table | 1 | just_to_be_safe_idx | 1 | a | A | NULL | NULL | NULL | YES | BTREE | | | NO |
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
Вы можете искать невидимые индексы среди всех баз данных с помощью:
SELECT * FROM information_schema.statistics WHERE is_visible='NO';
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: world
TABLE_NAME: Country
NON_UNIQUE: 1
INDEX_SCHEMA: world
INDEX_NAME: c
SEQ_IN_INDEX: 1
COLUMN_NAME: Continent
COLLATION: A
CARDINALITY: 7
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT: disabled
INDEX_COMMENT:
IS_VISIBLE: NO
Вы, также, можете объявить индекс невидимым при создании таблицы:
CREATE TABLE my_table
(
...
first_name varchar(25);
index first_name_idx(first_name) INVISIBLE;
);
Такой индекс будет поддерживаться в актуальном состоянии, но никогда не будет использоваться при выполнении запросов. Но как только в нем возникнет необходимость, он сразу будет готов к использованию.
Невидимость исчезнет, если вы загружаете таблицу, используя mysqldump/mysqlpump на старом сервере. То же самое произойдет, если вы реплицируете с нового сервера на старый.
Предостережение
Обратите внимание. что первичный ключ не может быть невидимым. Оптимизатору необходимо знать - имеет ли таблица первичный ключ. Это также справедливо для ситуации, когда нет первичного ключа, но есть уникальный ключ. В этом случае первый уникальный ключ рассматривается в качестве первичного ключа, и, следовательно, не может быть невидимым.
Спасибо, что выбрали MySQL!
Функциональность очень удобная, но пока имеет недостатки: если в запросе указано FORCE INDEX(`индекс, объявленный невидимым`), то всегда будет полный скан таблицы даже при наличии других индексов, подходящих для запроса.
Вызывает удивление, что FORCE INDEX(`индекс, объявленный невидимым`) не генерирует предупреждение. Это было бы удобно, чтобы не пропустить такие запросы при оценке производительности, когда объявляем индекс невидимым.
Дата публикации: 30.12.2016
© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.
|
