Типы индексов MySQL и их достоинства и недостатки

ИндексЭто особый файл (индексы таблиц InnoDB являются частью пространства имен), который содержит ссылки на все записи таблицы.
Примечание:
[1] Индексы не万能! Индексы могут ускорить операции поиска данных, но замедлят операции изменения данных. При каждом изменении записи индекс необходимо обновить. Для частичного компенсирования этого недостатка многие команды SQL имеют опцию DELAY_KEY_WRITE. Эта опция временно предотвращает MySQL от немедленного обновления индекса после каждого вставления новой записи и изменения существующей, обновление индекса будет произведено только после завершения вставки/изменения всех записей. В случаях, когда необходимо вставить много новых записей в таблицу, опция DELAY_KEY_WRITE играет очень важную роль.
[2] Кроме того, индексы занимают значительное количество места на диске. Поэтому индексы должны создаваться только для тех столбцов, которые часто используются для поиска и сортировки. Обратите внимание, что если某个 столбец содержит много повторяющихся данных, создание индекса для него не имеет большого практического значения.
Теоретически,完全可以为 каждого поля в таблице создать отдельный индекс, но MySQL ограничивает общее количество индексов в одной таблице 16.

1. Индексы таблицы InnoDB

По сравнению с таблицами MyISAM, индекс имеет значительно большее значение для таблиц InnoDB. В таблицах InnoDB, индекс играет важную роль не только при поиске данных записей, но и является основой механизма блокировки данных на уровне строк. "Блокировка данных на уровне строк" означает блокировку отдельных записей, которые обрабатываются в процессе выполнения транзакции, чтобы другие пользователи не могли их посещать. Эта блокировка влияет (но не ограничивается) командами SELECT…LOCK IN SHARE MODE, SELECT…FOR UPDATE, а также INSERT, UPDATE и DELETE.
С точки зрения эффективности, уровень блокировки данных таблицы InnoDB на самом деле происходит на их индексах, а не на таблице herself. Очевидно, что механизмы уровня блокировки данных строк таблицы работают только в том случае, если у соответствующей таблицы есть подходящий индекс для блокировки.

2. Ограничения

Если в условиях запроса WHERE EXISTS подзапроса используется неравенство (WHERE column != ...), MySQL не сможет использовать индекс.
Аналогично, если в условиях WHERE запроса используется функция (WHERE DAY(столбец) = ...), MySQL не может использовать индекс.
В операциях JOIN (при необходимости извлечения данных из нескольких таблиц), MySQL может использовать индекс только в том случае, если тип данных главного ключа и внешнего ключа одинаковый.
Если в условиях WHERE запроса используется оператор сравнения LIKE и REGEXP, MySQL может использовать индекс только в том случае, если первый символ шаблона не является символом wildcard. Например, если условие запроса LIKE 'abc%', MySQL использует индекс; если условие запроса LIKE '%abc', MySQL не использует индекс.
MySQL использует индекс только в операциях сортировки ORDER BY, когда условие сортировки не является выражением запроса (хотя при выполнении запросов, включающих несколько таблиц, даже если индекс доступен, эти индексы не очень помогают в ускорении сортировки)
Если определенная столбца данных содержит много повторяющихся значений, даже если для нее создан индекс, эффект может быть не очень хорошим. Например, если определенная столбца данных содержит только такие значения, как "0/1" или "Y/N", нет необходимости создавать для нее индекс.　

Обычные индексы, уникальные индексы и основные индексы

1. Обычный индекс

Единственная задача обычного индекса (определенного ключевыми словами KEY или INDEX) - это ускорение доступа к данным. Поэтому индекс следует создавать только для тех столбцов данных, которые наиболее часто встречаются в условиях запроса (WHERE столбец = ...) или условиях сортировки (ORDER BY столбец). По возможности, следует выбирать столбец данных, который наиболее упорядочен и спрессован (например, целочисленный столбец данных) для создания индекса.

2. Уникальный индекс
Обычный индекс позволяет индексируемому столбцу данных содержать повторяющиеся значения. Например, поскольку у людей может быть одно и то же имя, одно и то же имя может появиться два или более раз в одной и той же таблице с личными данными сотрудников.
Если можно определить, что определенная столбца данных будет содержать только уникальные значения, при создании индекса для этой столбцы данных следует использовать ключевое слово UNIQUE, чтобы определить его как уникальный индекс. Преимущества этого: во-первых, это упрощает управление индексом MySQL, что делает его более эффективным; во-вторых, MySQL автоматически проверяет,是否存在 уже появлявшийся в данном столбце данных в другой записи, при вставке нового записей в таблицу. Если таковое обнаружено, MySQL отклоняет вставку этой новой записи. Таким образом, уникальный индекс гарантирует уникальность записей. На самом деле, в большинстве случаев的目的 создания уникального индекса не для повышения скорости доступа, а для предотвращения повторения данных.

3. Главный индекс

Ранее уже многократно подчеркивалось: необходимо создать индекс для поля главного ключа, который называется «главным индексом». Единственным отличием главного индекса от уникального индекса является то, что при определении используется ключевое слово PRIMARY вместо UNIQUE.

4. Индексы внешних ключей

Если для внешнего ключа определено условие внешнего ключа, MySQL определит внутренний индекс для того, чтобы наиболее эффективно управлять и использовать условие внешнего ключа.

5. Составные индексы

Индекс может охватывать несколько столбцов, например, индекс INDEX(columnA, columnB). Характеристика такого индекса в том, что MySQL может выбирать использование одного из таких индексов. Если операция поиска использует только индекс столбца columnA, можно использовать составной индекс INDEX(columnA, columnB). Однако, это использование применимо только к комбинациям данных,排列анным в начале составного индекса. Например, INDEX(A, B, C) может использоваться как индекс A или (A, B), но не как индекс B, C или (B, C).

6. Длина индекса

При определении индексов для столбцов типа CHAR и VARCHAR можно ограничить длину индекса заданным количеством символов (это число должно быть меньше максимального числа символов, разрешенных для этого поля). Это позволяет создать индекс较小 по размеру, но быстрый в поиске. В подавляющем большинстве приложений строковые данные в базе данных в основном состоят из различных имен, и установление длины индекса в 10-15 символов уже достаточно, чтобы сузить поиск до нескольких записей.
При создании индексов для столбцов типа BLOB и TEXT необходимо ограничить длину индекса; максимальная длина индекса, разрешенная MySQL, составляет 255 символов.
Полнотекстовый индекс

　　 Обычный индекс на текстовом поле может ускорить поиск строк,出现在字段内容最前面（т.е. первых символов в содержимом поля). Если в поле хранится较大段由几个、甚至是多个单词构成的文字，обычный индекс неэффективен. Такие поиски часто выполняются в виде LIKE %word%，что для MySQL является сложным процессом, и если объем данных很大, время ответа может быть очень долгим.
Этот вид场合 идеально подходит для использования полнотекстового индекса (full-text index). При создании такого типа индекса MySQL создает список всех слов,出现的文本中，查询操作将根据这份清单去检索相关的数据记录。Полнотекстовый индекс может быть создан вместе с таблицей данных или использоваться позже, когда это необходимо.

Следующая команда добавляет:
ALTER TABLE tablename ADD FULLTEXT(column1, column2)

С полнотекстовым индексом можно использовать команду SELECT для поиска записей, содержащих один или несколько данных слов. Вот базовая грамматика таких запросов:
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST('word1', 'word2', 'word3')

Эта команда найдет все записи, содержащие word1, word2 и word3 в полях column1 и column2.

Примечание: Таблицы InnoDB не поддерживают полнотекстовый индекс.

Оптимизация запросов и индексов

　　　　　 Только когда в базе данных есть достаточное количество тестовых данных, результаты тестирования производительности имеют реальную ценность. Если в тестовой базе данных только几百 записей, они часто загружаются в память после выполнения первого запроса, что делает следующие запросы очень быстрыми – независимо от использования индексов. Только когда количество записей в базе данных превышает 1000, а общее количество данных также превышает общее количество памяти сервера MySQL, результаты тестирования производительности имеют значение.

　　　　　 Когда не известно, в каких столбцах следует создать индекс, люди часто получают помощь от команды EXPLAIN SELECT. Это просто добавление ключевого слова EXPLAIN в начале обычной команды SELECT. С этим ключом MySQL не выполняет команду SELECT, а анализирует ее. MySQL выводит процесс выполнения запроса и используемые индексы (если они есть) в виде таблицы.
В выводе команды EXPLAIN, первое столбце содержит имя таблицы данных, которую читает база данных, они排列ены по порядку чтения. Столбец type определяет связь (JOIN) между текущей таблицей данных и другими таблицами. Среди различных типов связей наиболее эффективной является system, затем следуют const, eq_ref, ref, range, index и All (All означает: для каждого записи в предыдущей таблице данные в этой таблице должны быть прочитаны полностью – это часто можно избежать с помощью индекса).

Столбец possible_keys предоставляет различные индексы, которые могут быть использованы MySQL при поиске записей. Столбец key указывает на фактический выбранный индекс, длина которого в байтах указана в столбце key_len. Например, для индекса INTEGER длина будет 4. Если используется составной индекс, в столбце key_len можно увидеть, какие части его использовал MySQL. В общем, чем меньше значение в столбце key_len, тем лучше (это означает быстрее).
Столбец ref предоставляет имя столбца в другой таблице в связи с отношением. Столбец row указывает количество строк, которые MySQL планирует прочитать из этой таблицы при выполнении запроса. Продукт всех чисел в столбце row позволяет нам примерно понять, сколько комбинаций необходимо обработать для этого запроса.
В конце, столбец extra предоставляет дополнительную информацию о операции JOIN, например, если MySQL должен создать временный таблицу при выполнении этого запроса, вы увидите фразу using temporary в столбце extra.

Это все содержимое статьи, надеюсь, это поможет вам в изучении, также希望大家多多支持呐喊教程。

Заявление: данное содержимое взято из Интернета, авторские права принадлежат соответствующему автору. Содержимое предоставлено пользователями Интернета, самостоятельно загруженным, сайт не обладает правами собственности, не прошел редактирование и не несет ответственности за юридические последствия. Если вы обнаружите подозрительное содержимое, пожалуйста, отправьте письмо по адресу: notice#oldtoolbag.com (во время отправки письма замените # на @) для сообщения о нарушении авторских прав,并提供 соответствующие доказательства. В случае подтверждения, сайт немедленно удалил涉嫌侵权的内容。