Заметки MySQL — оптимизация индекса

написать впереди

Эта статья представляет собой примечания к чтению и краткое изложение главы 5 «Высокопроизводительный MySQL». друзья, которым лень читать книгу ~ Друзья, которые чувствуют воду, пожалуйста, закройте ее.

Умное использование объяснения

Чтобы увидеть производительность оператора SQL, вы можете использоватьexplainКлючевые слова для просмотра эффективности выписки, вот некоторые из нихtypeчасть смысла поля,

• all, то есть полное сканирование таблицы, указывает на то, что оператор SQL не использует индекс.Возможно, сама таблица не создает индекс, или может быть так, что индекс не используется из-за оператора SQL.
• диапазон, указывающий, что используется ранжированное сканирование индекса, и производительность выше, чем индекс
• index, здесь показано, что индекс используется. В этом случае, если в дополнительном столбце указано значение «Используется индекс», это означает покрытие индексом. Покрытие индексом означает, что данные, которые мы хотим запросить, уже существуют в индексе. В этом случае , нет необходимости возвращаться к таблице для извлечения данных.
• ref, указывающий, что условный столбец использует индекс, но он не является первичным ключом и уникален, поэтому, даже если здесь используется индекс, значение индекса не уникально и есть дубликаты
• eq_ref, по сравнению с ref, использует уникальный индекс.Для каждого значения ключа индекса существует только одна совпадающая запись.
• const/system, в одной таблице есть не более одной совпадающей строки, а запрос выполняется очень быстро, поэтому значения в других столбцах в этой совпадающей строке могут рассматриваться оптимизатором в текущем запросе как константы. Например, запросы на основе первичных ключей или уникальных индексов.
• index_merge, указывающий, что используется метод оптимизации слияния индексов MySQL.При использовании объединенного индекса нам нужно проверить, является ли созданный нами индекс несколькими индексами с одним столбцом.

Как эффективно оптимизировать индексы

При использовании индекса индекс должен отображаться в виде отдельного столбца.

Быть независимым столбцом означает, что индекс не может использоваться как часть выражения, а также не может отображаться как параметр функции, в противном случае индекс завершится ошибкой, поскольку MySQL не может автоматически анализировать выражения и параметры, поэтому индекс нельзя использовать, и индекс не будет работать.Также возможны следующие ситуации:

• используется в условияхis nullилиis not nullприведет к сбою индекса, потому что индекс не будет хранитьnull
• использовать%like, потому что MySQL является левым совпадением при использовании нечеткого запроса, если вы начинаете с%В начале будет полный запрос таблицы
• При использовании многостолбцового индекса, если порядок индекса не совпадает с порядком построения индекса, или если первый индекс пропущен, а последующий индекс используется напрямую, индекс также не будет работать. Причина по-прежнему в том, что MySQL совпало слева~
• условие содержитor, индекс будет использоваться только в том случае, если все столбцы проиндексированы по отдельности.

Как создать эффективный индекс

• Если вам нужно индексировать очень длинные строки, вам нужно рассмотреть возможность индексации префикса в это время.
  • Индекс префикса предназначен для выбора части префикса требуемой строки в качестве индекса. В настоящее время необходимо ввести понятие, называемое селективностью индекса. Избирательность индекса относится к отношению уникального значения индекса к общему количеству записей в таблице данных. Видно, что выбор индекса Чем выше избирательность индекса, тем выше эффективность запроса. Когда селективность индекса равна 1, эффективность самая высокая, но в этом сценарии очевидно, что если селективность индекса равна 1, мы заплатим относительно высокую цену, и индекс будет очень большим. В это время нам нужно выбрать часть префикса строки в качестве индекса. Обычно префикс следующего столбца как индекс обладает высокой селективностью.
  • Как выбрать префикс
    • Рассчитайте селективность полного столбца для этого столбца так, чтобы селективность префикса была близка к селективности полного столбца
• Используйте многоколоночный индекс
  • Старайтесь не создавать индекс с одним столбцом для нескольких столбцов, потому что в этом случае вы можете использовать не более чем индекс с одной звездочкой.В этом случае лучше создать индекс с полным покрытием. Индекс по нескольким столбцам в большинстве случаев не улучшает производительность MySQL. Для производительности запросов в MySQL 5.0 введен объединенный индекс. В определенной степени несколько индексов по одному столбцу в таблице можно использовать для поиска указанных результатов. Однако в версиях до версии 5.0, если несколько условий в where основывались на нескольких одностолбцовых индексах, то в MySQL нельзя использовать эти индексы, в этом случае лучше использовать union
• Выберите подходящий порядок столбцов индекса
  • Опыт заключается в том, чтобы поместить наиболее избирательный столбец на передний план индекса, что может отфильтровать меньше наборов результатов при запросе.
  • Но это не всегда лучший вариант.Если вы рассматриваете группировку по или порядок по и т. д., а также рассматриваете данные, такие как гостевые учетные записи, в некоторых особых сценариях, приведенные выше эмпирические правила могут оказаться не самыми применимыми.
• индекс покрытия
  • Так называемый покрывающий индекс означает, что индекс содержит все поля запроса, в этом случае нет необходимости выполнять запрос обратно к таблице.
  • В MySQL в качестве индексов покрытия можно использовать только индексы B-Tree, поскольку хеш-индексы и т. д. не хранят значения индексируемых столбцов. время копирования данных.
  • Советы: уменьшитьselect *действовать
• Сортировка с использованием сканирования индекса
  • MySQL может генерировать упорядоченные результаты двумя способами: с помощью операций сортировки или сканирования в индексном порядке; использование операций сортировки требует много ресурсов ЦП и памяти, а использованиеindexПроизводительность очень хорошая, поэтому, когда мы запрашиваем упорядоченные результаты, попробуйте использовать последовательное сканирование индекса для создания упорядоченного набора результатов.
  • Как гарантировать использование последовательного сканирования индекса:
    • Порядок столбцов индекса соответствует порядку ORDER BY.
    • Все столбцы отсортированы в одном направлении
    • Если связано несколько таблиц, индекс можно использовать для сортировки только в том случае, если все поля, на которые ссылается предложение ORDER BY, относятся к первой таблице.Ограничение по-прежнему заключается в том, что должен быть удовлетворен крайний левый префикс индекса.
• сжатый индекс
  • В предыдущей статье о структуре индекса упоминалось, что в MyISAM используется технология сжатия префикса, которая уменьшает размер индекса и позволяет хранить больше индексов в памяти.Эта часть оптимизации по умолчанию только для строк, но ее можно настроить сжимать целые числа
  • Эта оптимизация в некоторых случаях дает более высокую производительность, но может привести к снижению производительности в некоторых случаях, поскольку сжатие префикса определяет, что каждое ключевое слово должно зависеть от предыдущего значения, поэтому бинарный поиск и т. д. нельзя использовать, только последовательное сканирование. поиск выполняется в обратном порядке, тогда производительность может быть плохой
• Уменьшите дублирование, избыточность и неиспользуемые индексы
  • Уникальные ограничения MySQL и ограничения первичного ключа реализуются через индексы, поэтому нет необходимости добавлять первичные ключи и уникальные ограничения для одного и того же столбца, а затем создавать индекс, который является дубликатом индекса.
  • Другой пример: если индекс (A, B) уже создан, то создание индекса (A) является дубликатом индекса, поскольку индекс MySQL является крайним левым префиксом, поэтому сам индекс (A, B) может использовать индекс (A), но создание индекса (B) не является дублирующим индексом
  • Сведите к минимуму количество новых индексов и расширьте существующие индексы, поскольку добавление индексов может привести к замедлению операций INSERT, UPDATE, DELETE и т. д.
  • Вы можете удалить неиспользуемые индексы и найти неиспользуемые индексы. Есть два способа. Откройте переменную сервера userstates в Percona Server или MariaDB, а затем подождите, пока сервер запустится в течение определенного периода времени. Вы можете запросить каждый индекс, запросив INFORMATION_SCHEMA. INDEX_STATISTICS Как часто используется индекс
• Индексы и блокировки
  • Когда я ранее говорил о структуре индекса, я сказал, что InnoDB поддерживает блокировки строк и таблиц, а блокировки строк используются по умолчанию, в то время как MyISAM использует блокировки таблиц, поэтому использование индексов может позволить запросам блокировать меньше строк, что также улучшит запросы. производительность.Если в запросе заблокировано 1000 строк, но фактически используются только 100 строк, то до 5.1 перед снятием этих блокировок необходимо совершить транзакцию.После 5.1 снять блокировки можно после фильтрации строк на сервере стороны, но это все равно вызовет конфликт замков
• Уменьшить фрагментацию индексов и данных
  • Прежде всего, нам нужно понять, почему происходит фрагментация. Например, когда InnoDB удаляет данные, это пространство остается пустым. Если за определенный период времени удаляется большой объем данных, пустое пространство будет больше, чем фактическое при выполнении новой операции вставки MySQL попытается повторно использовать эту часть пространства, но она все еще не может быть полностью занята, что вызовет фрагментацию
  • Следствием фрагментации является, конечно же, снижение производительности запросов, так как такая ситуация может привести к случайным обращениям к диску.
  • Данные можно организовать с помощью OPTIMIZE TABLE или путем повторного импорта таблицы данных.

Суммировать

В этой части индекса базы данных слишком много вещей, о которых нужно говорить. Большинство случаев необходимо сочетать с реальной ситуацией. Если мы сможем узнать больше о некоторых принципах самого индекса базы данных, это будет полезно для оптимизации. Используйте объяснение умело анализируйте оператор SQL, который вы пишете, чтобы оптимизировать его лучше.