3. Создавайте высокопроизводительные индексы

• Индекс — это структура данных, которую механизм хранения использует для быстрого поиска записей.
• Критично для хорошей работы.
• Наиболее эффективные средства оптимизации производительности запросов. Легко повышайте производительность запросов на несколько порядков.

3.1 Основы индекса

• Механизм хранения использует индекс, сначала находит соответствующее значение в индексе, а затем находит соответствующую строку данных в соответствии с соответствующей записью индекса.
• Может содержать значения для одного или нескольких столбцов. Если включено более одного столбца, порядок столбцов также имеет значение, поскольку MySQL может эффективно использовать только самый левый префиксный столбец индекса.

3.1.1 Типы индексов

Индекс реализуется на уровне механизма хранения, а не на уровне сервера, поэтому единого стандарта индексации не существует: индексы разных механизмов хранения работают по-разному, и не все механизмы хранения поддерживают все типы индексов. Даже если несколько механизмов хранения поддерживают один и тот же тип индекса, базовая реализация может различаться.

• Индекс B-дерева:

  • Фактически, многие механизмы хранения используют дерево B +, то есть каждый узел листьев содержит указатель на следующий узел листьев, тем самым облегчая диапазон узла листьев.

  • Индексированные столбцы хранятся последовательно, что очень удобно для поиска данных диапазона.

  • Индекс сортирует несколько значений на основе порядка столбцов, когда индекс был определен в операторе CREATE TABLE.

  • MyISAM использует префиксное сжатие, чтобы уменьшить размер индекса как можно скорее, и ссылается на индексированный столбец по физическому расположению данных; InnoDB сохраняет его в соответствии с исходным форматом данных и ссылается на индексированную строку в соответствии с первичным ключом.

  • При оптимизации производительности может оказаться необходимым использовать индексы для одних и тех же столбцов, но в разном порядке, чтобы удовлетворить разные типы требований запросов.

  • Пример структуры B+Tree:

    

  • Означает, что все значения хранятся по порядку, и каждая листовая страница находится на одинаковом расстоянии от пятки. Указатель конечного узла указывает на проиндексированные данные, а не на страницы других узлов. Ниже приведен пример диаграммы узла и соответствующего ему листового узла.На самом деле между корневым узлом и листовым узлом может быть много слоев узловых страниц.Глубина дерева напрямую связана с размером таблицы.

    • Поиск начинается с корневого узла индекса (не показан), поэтому механизму хранения больше не нужно выполнять полное сканирование таблицы, что ускоряет доступ к данным.
    • Слот корневого узла хранит указатели на дочерние узлы, и механизм хранения выполняет поиск на основе этих указателей.
    • Сравнивая значение страницы узла с искомым значением, можно найти соответствующие указатели на нижние дочерние узлы, эти указатели фактически определяют верхнюю и нижнюю границы значений на странице дочерних узлов.
    • Наконец, соответствующее значение найдено, или запись не существует.

    

  • Типы запросов для индексов B-Tree (применимо к полному значению ключа, диапазону значений ключа или поиску префикса ключа, где поиск префикса ключа применим только к поискам на основе крайнего левого префикса):

    • Соответствие всем значениям: соответствие всем столбцам в индексе.
    • Совпадение с крайним левым префиксом: сопоставьте столбец с крайним левым индексом.
    • Соответствие префиксу столбца: соответствует началу значения столбца, включая крайний левый столбец.
    • Значение диапазона соответствия: соответствует определенному диапазону значения столбца, включая крайний левый столбец.
    • Точное совпадение столбца и совпадающего диапазона. Кроме того, крайний левый столбец должен содержать
    • Запросы только для индекса: запросам нужен доступ только к индексу, а не к строкам данных.

  • В дополнение к поиску по значению его также можно использовать для ORDER BY в запросе для выполнения операций сортировки (поиск по порядку). Если ORDER BY удовлетворяет предыдущему типу запроса, он также может удовлетворять соответствующим требованиям сортировки.

  • предел:

    • Индекс нельзя использовать, не начав поиск по крайнему левому столбцу индекса.
    • Индексированные столбцы нельзя пропускать
    • Если в запросе есть запрос диапазона столбца, например LIKE, все столбцы справа от него не могут быть найдены с помощью оптимизации индекса.

• Хэш-индекс:

  • В зависимости от реализации хэш-таблицы допустимы только запросы, точно соответствующие всем столбцам индекса. Для каждой строки данных механизм хранения вычисляет хеш-код для всех столбцов индекса, хэш-код — меньшее значение, а хеш-коды, вычисленные для строк с разными значениями ключа, разные. Хэш-индекс хранит все хэш-коды в индексе, сохраняя при этом указатель на каждую строку данных в хэш-таблице.

  • В MySQL только движок памяти показывает поддержку хэш-индексов, который также является типом индекса по умолчанию для таблицы движка.Он также поддерживает индексы B-Tree и неуникальные хеш-индексы (если хеш-значения нескольких столбцов являются то же самое, индекс будет начинаться с метода связанного списка, который хранит несколько указателей записей в одной и той же записи хэша).

  • Самому индексу нужно только хранить соответствующее хэш-значение, поэтому структура индекса очень компактна, а скорость поиска хеш-индекса очень высока.

  • предел:

    • Включены только хеш-значение и указатель строки, а значения полей не сохраняются, поэтому значение в индексе нельзя использовать, чтобы избежать чтения строки. Но поскольку он находится в памяти, влияние на производительность не очевидно.
    • Он не хранится в порядке значений индекса, поэтому его нельзя использовать для сортировки.
    • Поиск частичного соответствия столбца индекса не поддерживается, поскольку хэш-индексы всегда используют все содержимое индексированного столбца для вычисления хеш-значения.
    • Поддерживаются только запросы сравнения на равенство, включая =, IN() и . Также не поддерживает запросы диапазона.
    • Доступ к хеш-индексированным данным осуществляется очень быстро, если только не происходит много хеш-коллизий (разные значения индексированных столбцов с одинаковым значением хеш-функции). Когда происходит коллизия хэшей, механизм хранения должен пройти по всем указателям строк и сравнить строку за строкой, пока не будет найдена соответствующая строка.
    • Некоторые операции обслуживания индекса также очень дороги, если есть много хэш-столкновений. Например, при удалении строки необходимо пройти через каждую строку, соответствующую значение HASH.

  • Сценарий:

    • В приложениях хранилища данных существует классическая схема «звезда», которая должна управлять множеством таблиц поиска.

  • Адаптивный хэш-индекс InnoDB:

    • Когда InnoDB замечает, что некоторые значения используются очень часто, она создает хэш-индекс на основе индекса B-Tree в памяти, который имеет преимущества хэш-индекса, такие как быстрый поиск хэша. Это полностью автоматическое внутреннее поведение, которое пользователь не может контролировать или настраивать, но которое можно отключить.

  • Создайте собственный хэш-индекс:

    • Если механизм хранения не поддерживает хэш-индексы, вы можете создавать хэш-индексы, такие как InnoDB.

    • Идея: Создать псевдохеш-индекс на основе B-Tree, то есть удалить индекс индексируемого столбца, и создать для него индексированный хеш-столбец, в котором хранится хэш-значение каждой строки данных в исходный индексный столбец.

    • Дефект: необходимо поддерживать хэш-значение, которое можно поддерживать вручную или реализовать с помощью триггеров.

    • Не используйте SHA1() и MD5() в качестве хеш-функций, потому что хеш-значения, вычисляемые этими двумя функциями, очень длинные, занимают много места и медленнее для сравнения. Можно использовать CRS32().

    • Если таблица данных очень большая, CRS32() будет иметь много коллизий данных, и вы можете самостоятельно реализовать простую 64-битную хеш-функцию, которая возвращает целое число вместо строки. Также возможно использовать часть возвращаемого значения функции MD5() в качестве хеш-функции.

      SELECT CONV(RIGHT(MD5("str"), 16), 16, 10) AS HASH64

    • При запросе с использованием хэш-индекса необходимо включить соответствующее значение столбца в предложение WHERE, поскольку могут возникать коллизии хэшей и выбираться несколько разных данных.

• Индекс пространственных данных (R-дерево):

  • В отличие от B-деревьев, этот тип индекса не требует префиксного запроса и индексирует данные из всех измерений.
  • Запросы можно комбинировать, используя любое измерение при запросе.
  • Данные должны поддерживаться с помощью связанных с ГИС функций MySQL, таких как MBRCONTAINS(). (ГИС-поддержка MySQL не идеальна, и лучшим решением в реляционной базе данных с открытым исходным кодом является PostGIS или PostgreSQL)
  • Таблицы MyISAM поддерживают пространственные индексы и могут использоваться в качестве хранилища географических данных.

• Полнотекстовый индекс:

  • Он ищет ключевые слова в тексте, а не напрямую сравнивает значения в индексе.
  • Он полностью отличается от других индексов и требует внимания к таким деталям, как стоп-слова, корень и множественное число, логический поиск и т. д.
  • Больше похоже на то, что делают поисковые системы, а не на простое условное соответствие WHERE.
  • Между созданием полнотекстового индекса и индекса B-дерева на основе значений для одного и того же столбца нет конфликта, и полнотекстовый индекс подходит для операций ПОИСКПОЗ.

3.2 Преимущества индексов

• преимущество:
  • Значительно уменьшает объем данных, которые сервер должен сканировать
  • Помогает серверу избежать сортировки и временных таблиц (B-Tree будет хранить значения связанных столбцов вместе для сортировки ORDER BY и GROUP BY)
  • Случайный ввод-вывод можно превратить в порядок
• Индекс «системы Samsung», подходящей для запроса:
  • Одна звезда за объединение связанных записей
  • Порядок данных в индексе и порядок поиска совпадают, чтобы получить две звезды.
  • Столбцы в индексе содержат все столбцы, необходимые в запросе для получения Samsung.
• Индексация не лучшее решение:
  • Для очень маленьких таблиц в большинстве случаев более эффективно полное сканирование таблицы.
  • Для средних и больших таблиц индексы очень эффективны.
  • Для очень больших таблиц стоимость создания и использования индексов возрастает, и необходимо различать набор данных, требуемых запросом, например технологию секционирования.
  • Количество таблиц особенно велико, и может быть создана таблица информации метаданных для запроса определенных функций, которые необходимо использовать. Например, для выполнения запросов, которые должны агрегировать данные, распределенные несколькими приложениями, в нескольких таблицах, необходимо записать метаданные о том, «какая информация пользователя хранится в какой таблице», чтобы те запросы, которые не содержат указанного пользователя, могли быть прямо проигнорировано таблица информации. Обычный трюк для больших систем
  • Для терабайт данных поиск одной записи не имеет особого смысла, поэтому вместо индексов часто используются методы метаданных на уровне блоков.

3.3 Стратегия высокопроизводительного индексирования

3.3.1 Независимые столбцы

• Столбец индекса не может быть частью выражения или аргументом функции. Потому что MySQL не может разрешить это автоматически

  WHERE column + 1 = 5 AND TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10

3.3.2 Индекс префикса и избирательность индекса

Индексирование столбцов с длинными символами может сделать индекс большим и медленным. Одна стратегия представляет собой смоделированный хэш-индекс, а другая стратегия представляет собой префиксный индекс.

• Селективность индекса:
  • Относится к отношению уникального значения индекса (также мощности) к общему количеству записей в таблице данных (#T).
  • Чем выше селективность, тем выше эффективность запроса.Селективность уникального индекса равна 1, а производительность наилучшая.

• Индекс префикса:

  • Часть строки в начале индекса может сэкономить пространство индекса, тем самым повысив эффективность индексации, но снизив избирательность индекса.

  • В целом, селективность префикса столбца достаточно высока, чтобы удовлетворить производительность запросов.

  • Для столбцов BLOB, TEXT или очень длинных столбцов VARCHAR необходимо использовать префиксные индексы, поскольку MySQL не позволяет индексировать полную длину этих столбцов.

  • Хитрость выбора длины префикса: сохраняйте высокую селективность (ближе к индексации всего столбца), но не слишком длинную. Другими словами, мощность префикса должна быть близка к мощности всего столбца.

  • метод:

    Сначала рассчитайте селективность всей колонки:SELECT COUNT(DISTINCT col)/COUNT(*) FROM mytable, а затем сравнивают со средней селективностью и наихудшей селективностью.

    • Средняя селективность: рассчитывается для разных длин префиксов в одном запросе.

      SELECT COUNT(DISTINCT LEFT(col, 3))/COUNT(*) AS sel3,
          COUNT(DISTINCT LEFT(col, 4))/COUNT(*) AS sel4,
          ...
      FROM mytable;

    • Наихудшая селективность: для нескольких длин префиксов, выбранных для средней селективности, учитывайте селективность, когда распределение данных очень неравномерно.

  • недостаток:

    • MySQL не может использовать индекс префикса, чтобы сделать Order By и Group By
    • Не закрывайте скан

  • Сценарии применения:

    • Для очень длинных шестнадцатеричных уникальных идентификаторов, таких как сеанс (SESSION), который сохраняет веб-сайт, можно использовать префиксный индекс длиной 8, и он полностью прозрачен для приложений верхнего уровня.

  • Бывают также случаи, когда полезно использовать индекс суффиксов, например, для поиска всех адресов электронной почты для доменного имени. Однако MySQL изначально не поддерживает обратное индексирование, поэтому вы можете использовать триггеры, чтобы перевернуть строку и сохранить ее, а также построить индекс префикса на основе этого.

3.3.3 Многоколоночные индексы

• Создание независимых одностолбцовых индексов для нескольких столбцов в большинстве случаев не улучшает производительность запросов MySQL.

  • Политика «Слияние индексов» введена в mysql5.0, а позже, в некоторой степени, вы можете найти указанные строки, используя несколько одиночных взаимодействий в таблице. Одновременно используйте два индекса single-coli для сканирования например запросов и объединения результатов, вы можете увидеть процесс через EXTRA из объяснения. Этот алгоритм имеет три варианта:
    • объединение условий ИЛИ
    • Пересечение условий И (пересечение)
    • Объединить объединение и пересечение первых двух случаев
  • Более ранние версии MySQL могли использовать только один из этих индексов с одним столбцом, и в этом случае ни один индекс с одним столбцом не был бы очень эффективным. Используйте полное сканирование таблицы для примера запроса, если только вы не измените его на UNION.

  -- 两个单列索引的查询
  mysql> SELECT film_id, actor_id FROM sakila.film_actor
      -> WHERE actor_id = 1 OR film_id = 1;

• Стратегия слияния индексов иногда является результатом оптимизации, но на самом деле чаще всего индекс в таблице построен плохо:

  • Когда сервер пересекает несколько индексов (обычно с множественными условиями), он обычно означает, что необходим индекс нескольких столбцов со всеми соответствующими столбцами, а не несколько независимых однополых индексов
  • Когда сервер выполняет совместную работу по нескольким индексам (обычно с несколькими или условиями), он обычно потребляет много процессора и памяти в кэше, сортировке и слиянии операций алгоритма. Особенно, когда некоторые из этих индексов не очень избирательны и должны объединить большое количество данных, возвращаемых сканированием.
  • Оптимизатор не учитывает их в «стоимости запроса», и оптимизатор заботится только о случайном чтении страниц. Это сделает стоимость запроса заниженной, в результате чего выполнение плана будет не таким хорошим, как полное сканирование таблицы. Это не только потребует больше ресурсов ЦП и памяти, но также может повлиять на параллелизм запроса, но если такой запрос выполняется отдельно, влияние на параллелизм часто игнорируется. Вообще говоря, часто лучше изменить запрос на UNION.

• Если вы видите слияние индексов в EXPLAIN, вам следует проверить структуру запроса и таблицы для получения оптимальных результатов. Вы также можете отключить слияние индексов с помощью параметра optimizer_switch или использовать подсказку INGORE INDEX, чтобы позволить оптимизатору игнорировать определенные индексы.

3.3.4 Выберите соответствующий порядок столбцов индекса (сценарий B-Tree)

Правильный порядок зависит от запроса, использующего индекс, а также необходимо учитывать, как лучше удовлетворить потребности сортировки и группировки.

• Экспериментальные правила выбора порядка столбцов:
  • Если сортировку и группировку не нужно учитывать, поместите наиболее избирательный столбец вверху индекса.
  • Производительность зависит не только от селективности (общей кардинальности) всех индексируемых столбцов, но и от конкретных значений условий запроса, то есть от распределения значений.
    • Если селективность некоторых значений индекса очень мала, то есть диапазон соответствия очень велик, значит, индекс в принципе бесполезен. Этот особый случай может снизить производительность всего приложения.

3.3.5 кластеризованный индекс (ориентированный на InnoDB)

• Не отдельный тип индекса, а способ хранения данных. Конкретные детали зависят от его реализации, но индекс кластера InnoDB фактически хранит индекс B-деревьев и линию данных в той же структуре.

• Когда таблица имеет кластеризованный индекс, ее строки данных фактически хранятся на листовых страницах в индексе, но узлы также содержат только столбцы индекса. Термин «кластеризованный» означает, что строки данных и значения смежных ключей компактно хранятся вместе (индексно-организованные таблицы в Oracle). Как показано на рисунке, индексированный столбец является столбцом первичного ключа.

  

• Таблица может иметь только один кластеризованный индекс, потому что невозможно хранить строки в двух разных местах одновременно.

• Встроенный механизм хранения MySQL не поддерживает выбор индекса в качестве кластеризованного индекса InnoDB будет кластеризовать данные по первичному ключу, который по умолчанию использует кластеризованный индекс:

  • Если первичный ключ не определен, InnoDB выбирает уникальный ненулевой индекс.
  • Если нет уникального ненулевого индекса, InnoDB неявно определяет прогрессивный индекс.

• InnoDB агрегирует записи только на одной странице, страницы, содержащие соседние ключевые значения, могут быть далеко друг от друга.

• Преимущества и недостатки:

  • Преимущества (полное использование при проектировании таблиц и запросов может существенно повлиять на производительность):
    • Связанные данные могут храниться вместе. Например, при реализации почтового ящика данные агрегируются в соответствии с идентификатором пользователя, так что для получения всех писем определенного пользователя с диска необходимо прочитать всего несколько страниц данных. Без кластеризованного индекса каждое электронное письмо может привести к дисковому вводу-выводу.
    • Доступ к данным быстрее. Поскольку и индекс, и данные хранятся в одном B-дереве.
    • Запросы, использующие сканирование покрывающего индекса, могут напрямую использовать значение первичного ключа в узле страницы.
  • недостаток:
    • Максимизируйте производительность интенсивного применения ввода / вывода, но если данные хранятся в памяти, порядок доступа - это не то, что не так важно, и кластерный индекс не имеет преимуществ.
    • Скорость вставки сильно зависит от порядка вставки. Вставка в порядке первичного ключа — самый быстрый способ загрузить данные в таблицу InnoDB. Если данные загружаются не в соответствии с порядком первичного ключа, лучше всего использовать команду OPTIMIZE TABLE для реорганизации таблицы после завершения загрузки.
    • Обновление столбца кластеризованного индекса обходится дорого, поскольку InnoDB вынуждена перемещать каждую обновленную строку в новое место.
    • Таблица, основанная на кластеризованном индексе, может столкнуться с проблемой «разделения страниц» при вставке новой строки или при обновлении первичного ключа и необходимости перемещения строки. Когда значение первичного ключа строки требует, чтобы строка была вставлена ​​в полную страницу, механизм хранения разделяет страницу на две страницы, чтобы разместить строку, что приводит к тому, что таблица занимает больше места на диске.
    • Полное сканирование таблицы может быть медленным, особенно если строки разрежены или хранение данных прерывисто из-за разделения страниц.
    • Вторичные индексы (некластеризованные индексы) могут быть больше, чем ожидалось, поскольку конечные узлы вторичного индекса содержат столбцы первичного ключа строки, на которую ссылаются.
    • Для доступа к вторичному индексу требуется два поиска по индексу вместо одного. Поскольку конечный узел вторичного индекса хранит не указатель на физическое расположение строки, а значение первичного ключа строки. (адаптивный хеш-индекс InnoDB может уменьшить такое дублирование работы)

• Распределение данных и сравнение Myiasam InnoDB

  

  • ИнноБД:
    • За счет использования кластерного индекса сохраняет всю таблицу
    • Каждый конечный узел кластеризованного индекса содержит значение первичного ключа, идентификатор транзакции, указатели отката для транзакций и MVVC, а также все остальные столбцы.
    • Листовой узел вторичного индекса хранит не «указатель строки», а значение первичного ключа, которое используется в качестве «указателя» на строку. То есть конечные узлы содержат индексированные столбцы и столбцы первичного ключа. Эта стратегия заставит вторичный индекс занимать больше места, но уменьшит обслуживание вторичного индекса при перемещении строк или разделении страниц данных, поскольку нет необходимости обновлять указатель во вторичном индексе.
  • MyISAM
    • Используется независимое хранилище строк, которое хранится на диске в соответствии с порядком вставки данных.
    • Структура индекса первичного ключа такая же, как и любого другого индекса. Индекс первичного ключа представляет собой уникальный ненулевой индекс с именем PRIMARY.

• Вставьте строки в порядке первичного ключа в таблицу InnoDB

  • Если агрегирование данных не требуется, рекомендуется определить суррогатный ключ в качестве первичного ключа, а данные первичного ключа не должны иметь отношения к приложению. Самый простой способ — использовать самоувеличивающийся столбец AUTO_INCREMENT, который может гарантировать, что строки данных записываются по порядку, что лучше для производительности операции ассоциации на основе первичного ключа.

  • Лучше избегать случайных (прерывистых и с очень большим распределением значений) кластеризованных индексов, особенно для приложений с интенсивным вводом-выводом, например, использование UUID в качестве кластеризованного индекса может снизить производительность, это делает кластеризацию Вставка индекса полностью случайна , из-за чего вставка строки занимает больше времени, а индекс занимает больше места. Поскольку поля первичного ключа длиннее, это также вызвано разделением страниц и фрагментацией.

  • Вставьте данные в соответствии с идентификатором заказа:

    Каждая запись сохраняется после предыдущей записи, и когда достигается максимальный коэффициент заполнения страницы (InnoDB по умолчанию равен 15/16 от размера страницы, оставляя место для последующего изменения), следующая запись будет вставлена ​​в новую страницу. . Как только данные загружаются таким последовательным образом, страницы первичного ключа заполняются записями, расположенными приблизительно в определенной последовательности (страницы вторичного индекса могут быть другими).

    Еще худшие сценарии исхода:

    • При высоких одновременных рабочих нагрузках может возникнуть значительный конфликт. Поскольку все вставки происходят здесь, может возникнуть конкуренция за блокировку промежутка.
    • Механизм блокировки AUTO_INCREMENT также может быть оспариваемым. Рассмотрите возможность изменения дизайна таблицы или приложения или изменения конфигурации innodb_autoinc_lock_mode.

  • Вставьте данные со случайным идентификатором:

    Значение первичного ключа новой строки не обязательно больше значения предыдущей вставки, поэтому необходимо найти подходящее место для новой строки — обычно в середине существующих данных — и выделить место. Это добавляет много дополнительной работы и приводит к менее оптимальному распределению данных.

    недостаток:

    • Записанная целевая страница могла быть сброшена на диск и удалена из кеша или не была загружена в кеш. InnoDB необходимо прочитать целевую страницу с диска в память перед вставкой, что вызовет много случайных операций ввода-вывода.
    • Поскольку записи выполняются не по порядку, для выделения места для новых строк требуются частые разбиения страниц. Поскольку разделение страниц может привести к перемещению больших объемов данных, для вставки требуется изменить как минимум три страницы вместо одной.
    • Из-за частого разделения страниц страницы становятся разреженными и заполняются неравномерно, поэтому в конечном итоге данные будут фрагментированы.

3.3.6 Индексы покрытия

Хорошо спроектированный индекс должен учитывать весь запрос, а не только часть условия WHERE.

• Индекс покрытия: индекс содержит (покрывает) значения всех полей, которые необходимо запросить.

• Запрос необходимо только отсканировать индекс обратно в таблицу без необходимости прочитать преимущества линий данных:

  • Записи индекса обычно меньше, чем размер строки данных, что значительно сокращает доступ к данным, если нужно только прочитать индекс. Это очень важно при загрузке кэша, когда большая часть времени отклика тратится на копирование данных. Покрывающие индексы также полезны для приложений с интенсивным вводом-выводом, поскольку индексы меньше и их легче разместить в памяти (особенно MyISAM может сжимать индексы).
  • Индексы хранятся в порядке значений столбцов (по крайней мере, в пределах одной страницы), поэтому запросы диапазона с интенсивным вводом-выводом будут намного меньше операций ввода-вывода, чем случайное чтение каждой строки с диска.
  • Некоторые механизмы хранения, такие как MyISAM, кэшируют только индексы в памяти, а данные кэшируются операционной системой, поэтому для доступа к данным требуется системный вызов. Это может привести к серьезным проблемам с производительностью, особенно к тем системным вызовам, на которые приходится наибольшая нагрузка при доступе к данным.
  • Покрывающие индексы полезны для таблиц InnoDB, которые используют кластеризованные индексы. Вторичный индекс InnoDB хранит значение первичного ключа строки в конечном узле, поэтому, если вторичный узел может покрыть запрос, можно избежать вторичного запроса индекса первичного ключа.

• Покрывающий индекс должен хранить значение индексированного столбца, в то время как хеш-индекс, пространственный индекс и полнотекстовый индекс не сохраняются. MySQL может использовать только индекс B-Tree в качестве индекса покрытия.

• Причины, по которым нельзя использовать покрывающий индекс:

  • Ни один индекс не может охватывать этот запрос.
  • Операция LIKE больше не может выполняться для индекса.

• Покрывающие индексы можно использовать с ленивыми ассоциациями, поскольку доступ к столбцам задерживается. Сначала используйте покрывающий индекс на первом этапе запроса, а затем запросите значение столбца, которое нужно получить на внешнем уровне.

• Листовые узлы вторичного индекса InnoDB содержат значение первичного ключа, что означает, что вторичный индекс может эффективно использовать эти столбцы первичного ключа для покрытия запросов.

  -- last_name字段有二级索引，虽然该索引的列不包括逐渐actor_id，但也能用于对actor_id做覆盖查询
  mysql>EXPLAIN SELECT actor_id, last_name
       -> FROM sakila.actor WHERE last_name = "HOPPER"\G

• Использование таблицы InnoDB для запроса всех столбцов через первичный ключ не является покрывающим запросом.Хотя конечные узлы кластеризованного индекса содержат данные всех столбцов, это всего лишь метод хранения данных, а не индекс.

3.3.7 Сортировка с использованием сканирования индекса

• Как MySQL производит упорядоченные результаты:
  • по операции сортировки
  • Сканировать в индексном порядке. Значение столбца type из EXPLAIN равно "index"
• Если индекс не может покрыть все столбцы, требуемые запросом, то необходимо запросить каждую запись обратно в таблицу. Это в основном случайный ввод-вывод, который медленнее, чем последовательное полное сканирование таблицы, особенно при рабочих нагрузках с интенсивным вводом-выводом.
• Спроектируйте индексы так, чтобы они максимально удовлетворяли сортировку и поиск строк.
• Требования сортировки сканирования индекса (если они не выполняются, необходимо выполнить операцию сортировки):
  • MySQL может использовать индекс для сортировки результатов, только если порядок столбцов индекса точно такой же, как порядок предложения ORDER BY, и направление сортировки (обратное или положительное) всех столбцов одинаково.
  • Если запросу необходимо связать несколько таблиц, только если поля, на которые ссылается предложение ORDER BY, относятся к первой таблице.
  • Предложение ORDER BY имеет те же ограничения, что и поисковый запрос: должен удовлетворяться крайний левый префикс индекса.
• Предложение ORDER BY может игнорировать это ограничение, когда лидер указан как константа\константа, если эти столбцы определены в предложении WHERE или JOIN.... WHERE col1="xxx" ORDER BY col2 DESC;, где col1 и col2 — совместные индексы.
• Запросы, которые нельзя отсортировать с помощью индекса:
  • ORDER BY использует два разных направления сортировки
  • ORDER BY ссылается на столбец, которого нет в индексе.
  • Столбцы в WHERE и ORDER BY не могут сочетать крайний левый префикс индекса
  • ГДЕ является запрос диапазона в первом столбце, MySQL не может индексировать остальные столбцы
  • В столбце есть несколько условий равенства, что также является запросом диапазона для сортировки.

3.3.8 Сжатые (сжатые префиксом) индексы

• MyISAM использует префиксное сжатие для уменьшения размера индекса, что позволяет помещать в память больше индексов, что в некоторых случаях может значительно повысить производительность.
• По умолчанию сжимаются только строки, целые числа также могут быть сжаты с помощью настройки.
• Метод сжатия каждого индексного блока: сначала полностью сохранить первое значение индексного блока, затем сравнить остальные значения с первым значением, чтобы получить одинаковое количество префиксных байтов и оставшихся разных частей суффикса, а затем сохранить эту часть . MyISAM использует аналогичный метод сжатия для указателей.
• Сжатые блоки занимают меньше места за счет того, что некоторые операции могут выполняться медленнее. Поскольку каждое значение зависит от предыдущего значения, вы не можете использовать двоичный поиск для сканирования с начала, а производительность сканирования в обратном порядке снижается.
• Для приложений с интенсивным использованием ЦП, поскольку сканирование часто требует случайного поиска, этот индекс не рекомендуется.
• Укажите параметр PACK_KEYS в операторе CREATE TABLE, чтобы управлять сжатием индексов.

3.3.9 Избыточные и повторяющиеся индексы

• Избыточные индексы: создайте несколько индексов для одного и того же столбца. MySQL должен поддерживать дубликаты индексов отдельно, а оптимизатор также должен учитывать один за другим при выполнении запросов, что может повлиять на производительность.

  • (A) является избыточным индексом (A, B), (B, A) и (B) не являются, только для индексов B-дерева
  • (A, ID) также является избыточным индексом, поскольку столбец первичного ключа для InnoDB уже включен во вторичный индекс.
  • Другие типы, такие как хэш-индексы, не будут избыточными индексами B-Tree.
  • Добавление нового индекса приведет к замедлению операций INSERT, UPDATE и т. д., особенно когда новый индекс достигает узкого места в памяти.

• Двойной индекс: индекс того же типа, созданный в том же порядке в том же столбце. Этой операции следует избегать, распространенной ошибкой является добавление уникального лимита и индекса запроса к первичному ключу, который представляет собой три повторяющихся индекса. (Если тип индекса отличается, он не считается повторяющимся индексом)

• В большинстве случаев избыточные индексы не нужны, и вам следует попытаться расширить существующие индексы, а не создавать новые. Если только расширение существующего индекса не приведет к тому, что он станет слишком большим, что повлияет на производительность других запросов, использующих этот индекс.

  • Если у вас есть запрос по целочисленному столбцу, теперь вам нужно добавить очень длинный столбец VARCHAR для расширения индекса, что может привести к резкому падению производительности. В частности, есть запросы, которые используют этот индекс в качестве покрывающего запроса или таблицы MyISAM и имеют множество запросов диапазона.

    -- Q1查询：
    SELECT count(*) FROM userinfo WHERE state_id=5;
    -- Q2查询：
    SELECT state_id, city, address FROM userinfo WHERE state_id=5;
    -- Q2的查询速度会比Q1慢，最简单的办法是扩展索引变成覆盖查询：
    ALTER TABLE userinfo DROP key state_id, ADD KEY state_id_2 (state_id, city, address);
    -- 索引扩展后，Q2运行更快，但Q1变慢了。如果想要两个查询都变得更快，就需要两个索引，尽管这是冗余的。

• Решение избыточных и повторяющихся индексов состоит в том, чтобы просто удалить их. Как найти эти индексы:

  • Напишите несколько сложных запросов, которые обращаются к таблице INFORMATION_SCHEMA (сервер может вызвать проблемы с производительностью, если данных или таблиц много).
  • сторонние инструменты.

• Поскольку вторичный индекс содержит значение первичного ключа, (A) эквивалентно (A, ID), что полезно для таких запросов, как WHERE A=5 ORDER BY ID. Но если (A) расширяется до (A,B), что эквивалентно (A,B,ID), предыдущий запрос не может использовать этот индекс для сортировки, а только для сортировки файлов.

3.3.10 Неиспользуемые индексы

Найдите их и удалите! Однако некоторые индексы функционируют как уникальные ограничения.Хотя они не используются запросами, они используются для предотвращения дублирования данных.

3.3.11 Индексы и блокировки

• Индексы могут блокировать меньше строк. Если запрос никогда не обращается к этим ненужным строкам, блокируется меньше строк:
  • Хотя блокировки строк в InnoDB эффективны, а использование памяти невелико, при блокировке строк все же возникают дополнительные накладные расходы.
  • Блокировка большего количества строк, чем необходимо, увеличивает конкуренцию за блокировку и уменьшает параллелизм.
• InnoDB блокирует строку только при доступе к ней, а индексы могут уменьшить количество обращений к InnoDB, тем самым уменьшая количество блокировок. Но это работает только в том случае, если InnoDB может отфильтровать все нежелательные строки на уровне механизма хранения.
  • Если индекс не может отфильтровать недопустимые строки, сервер MySQL не может применить предложение WHERE до тех пор, пока данные не будут извлечены InnoDB и возвращены на уровень сервера. В настоящее время InnoDB заблокировала эти строки (включая данные строк, которые не индексируются, они должны быть отфильтрованы на уровне сервера, поскольку индекс работает только на уровне механизма хранения) и будут освобождены только при необходимости.
  • В MySQL 5.0 и более новых версиях InnoDB может снять блокировку после фильтрации строк на стороне сервера, но в более ранних версиях блокировку можно снять только после фиксации транзакции.
• Если вы не используете индекс для поиска и блокировки строк, MySQL может выполнить полное сканирование таблицы и заблокировать все строки, независимо от того, нужны они или нет.
• InnoDB использует разделяемые (чтение) блокировки для вторичных индексов, но доступ к первичным ключевым индексам требует монопольных (запись) блокировок, что исключает возможность использования покрывающих индексов (не понимаю?????) и делает SELECT FOR UPDATE более эффективнее, чем LOCK IN SHARE MODE, или неблокирующие запросы выполняются намного медленнее.

3.4 Тематическое исследование индекса

Создайте сайт онлайн-знакомств с таблицами информации о пользователях, включая страну, регион, город, пол, цвет глаз и многое другое. Сайты должны поддерживать различные комбинации этих функций для поиска пользователей, а также должны позволять сортировать пользователей и ограничивать результаты на основе их последнего времени в сети, рейтингов других участников и т. д.

• Сортировать по индексу или сначала получить данные, а затем отсортировать? Использование индексной сортировки сильно ограничивает структуру индексов и запросов.

3.4.1 Поддержка нескольких условий фильтрации

• Сначала посмотрите, какие столбцы имеют разные значения и какие столбцы чаще всего появляются в предложении WHERE.

  • Избирательность по стране и полу обычно низкая. Учитывая частоту использования, в качестве префикса запроса рекомендуется использовать (пол, страна).

    • Даже если запрос не использует столбец пола, вы можете добавить AND SEX IN('m', 'f') к условию запроса, которое нужно обойти. Но этот трюк не рекомендуется, если в столбце слишком много значений и список IN() слишком длинный, или если количество IN() слишком много и комбинаций слишком много.
    • Один из основных принципов: рассмотреть все варианты на столе. При разработке индексов думайте не только о том, какие индексы необходимы для существующих запросов, но и об оптимизации запроса.Если вы обнаружите, что для некоторых запросов необходимо создавать новые индексы, но этот запрос снизит эффективность других запросов, вам следует подумать об оптимизации исходного запроса и найти наилучший баланс между оптимизацией запроса и индекса, а не слепо гнаться за наиболее совершенным показатель.
• Рассмотрим список других распространенных комбинаций WHERE и нужно понять, какие комбинации будут медленными без подходящего индекса.
  • (пол, страна, возраст), (пол, страна, область, возраст) (пол, страна, область, город, возраст) общие
    • Это потребует много индексации. Если вы хотите как можно больше повторно использовать индекс, вы можете использовать трюк IN(), упомянутый ранее.
    • Если вы не укажете это поле для поиска, вам необходимо определить список всех стран или список всех регионов страны, чтобы убедиться, что префикс индекса имеет те же ограничения (объединение всех стран, регионов, полов будет очень большое состояние)
• Создавать индексы для некоторых редких поисковых запросов (таких как has_pictures, eye_color, eduaction)
  • Эти столбцы очень избирательны и редко используются, поэтому вы можете игнорировать их и позволить MySQL просканировать некоторые дополнительные строки.
  • Или добавьте эти столбцы перед столбцом возраста и используйте трюк IN() для обработки поиска без этих столбцов.
• Почему вы собираетесь указывать ВОЗРАСТ? Какое особое место в AGE?
  • Заставьте MySQL использовать как можно больше индексированных столбцов, поскольку запросы могут использовать только крайний левый префикс индекса, пока не встретится первое условие диапазона. Все предыдущие столбцы — это равные условия, а столбец возраста — в основном условия диапазона.
  • Хотя IN() можно использовать вместо запросов диапазона, например age IN(18, 19, 20), не все запросы диапазона можно преобразовать.
  • Второй принцип заключается в размещении столбцов, которые необходимо использовать для запросов диапазона, за индексом как можно дальше, чтобы оптимизатор мог использовать как можно больше столбцов индекса.

3.4.2 Избегайте условий с несколькими диапазонами

Предположим, у вас есть столбец last_online, и вы хотите отобразить пользователей, которые были в сети за последние несколько недель, с помощью следующего запроса:

WHERE eye_color IN('brown', 'blue', 'hazel')
    AND hair_color IN('black', 'red', 'blonde', 'brown')
    AND sex IN("M", "F")
    AND last_online > DATE_SUB(NOW(), INTERVAL 7 DAY)
    AND age BETWEEN 18 AND 25;

-- MySQL会将age>18和age IN(18,19)都认为是范围查询(通过EXPLAIN查看)，但两种访问效率是不同的，因为第二个查询是多个等值条件查询。对MySQL来说，无法在使用范围查询后面的其他索引列，但对多个等值范围查询没有这个限制。

• Этот запрос имеет два условия диапазона, и MySQL не может использовать их оба.
• Если нет возможности преобразовать поле age в список IN(), а диапазонный запрос по этим двум измерениям должен быть очень быстрым, к сожалению, прямого решения этой проблемы нет, но один из диапазонных запросов может быть преобразовано в простое сравнение эквивалентности:
  • Активный столбец рассчитывается заранее, и это поле поддерживается запланированной задачей. Когда пользователь входит в систему каждый раз, установите соответствующее значение на 1 и установите значение пользователей, которые не входили в систему в течение последних 7 дней подряд, на 0
  • Этот метод может использовать индекс (активный, пол, страна, возраст). active не совсем точен, потому что требования к точности для этого типа запроса не высоки. Если вам нужен точный подсчет, вы можете поместить столбец last_online в предложение WHERE, но не добавлять его в индекс. Таким образом, это условие запроса не может использовать какой-либо индекс, но поскольку фильтруемость этого условия невысока, даже добавление этого столбца в индекс не сильно поможет, или отсутствие подходящего индекса не окажет явного влияния на запрос.
• Если пользовательская система видит как активных, так и неактивных пользователей, в запросе можно использовать список IN(). Другой вариант — создать отдельные индексы для разных комбинаций, как минимум (активный, пол, страна, возраст), (активный, страна, возраст), (пол, страна, возраст) и (страна, возраст). Эти индексы могут быть более оптимизирован для конкретного запроса, но, учитывая дополнительные затраты пространства на обслуживание индекса, это не очень хорошая стратегия.

3.4.3 Оптимизация сортировки

• Столбцы с очень низкой селективностью, мы можем добавить специальный индекс для сортировки. Например, можно создать (пол, рейтинг), в этом запросе используется ORDER BY и LIMIT, если индекса нет, будет очень медленно

  SELECT <cols> FROM profiles WHERE sex="M" ORDER BY rating LIMIT 10

• Даже с индексом запросы могут быть очень медленными, если пользовательский интерфейс должен быть постраничным, а страница перевернута на конец страницы:

  SELECT <cols> FROM profiles WHERE sex="M" ORDER BY rating LIMIT 10000, 10;

  Этот тип запроса представляет собой серьезную проблему независимо от того, как создается индекс. Потому что по мере увеличения смещения MySQL требуется много времени для сканирования данных, которые необходимо отбросить. Денормализация, предварительное вычисление и кэширование могут быть единственными стратегиями решения таких запросов. Лучшим подходом является ограничение количества страниц, которые может перелистнуть пользователь, что на практике не оказывает большого влияния на взаимодействие с пользователем, поскольку пользователей редко волнует 10 000-я страница результатов поиска.

• Еще один лучший способ оптимизировать этот тип индекса — использовать отложенную ассоциацию, используя запрос покрывающего индекса для возврата необходимых первичных ключей, а затем связать исходную таблицу с этими первичными ключами для получения требуемых строк. Это может уменьшить количество сканируемых MySQL строк, которые необходимо отбросить.

  SELECT <cols> FROM profiles INNER JOIN (
    SELECT <primary key cols> FROM profiles 
    WHERE sex="M" ORDER BY rating LIMIT 10000, 10
  ) AS x USING(<primary key cols>);

3.5 Ведение и индексирование таблиц

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

3.5.1 Поиск и исправление плохих таблиц

Поврежденные индексы могут привести к тому, что запросы будут возвращать неверные результаты или возникнут необоснованные конфликты первичных ключей, а в серьезных случаях могут привести к сбоям в работе базы данных.

• Попробуйте запустить CHECK TABLE, чтобы проверить наличие повреждений таблицы (обратите внимание, что некоторые движки не поддерживают эту команду), которая обычно находит большинство ошибок таблиц и индексов.
• Способы исправления ошибок таблицы:
  • REPAIR TABLE можно использовать для восстановления поврежденных таблиц (обратите внимание, что некоторые движки не поддерживают эту команду).
  • Если механизм хранения не поддерживает REPAIR TABLE, вы также можете перестроить таблицу с помощью инструкции ALTER, которая ничего не делает, например меняет механизм хранения таблицы на текущий механизм:ALTER TABLE innodb_dbl ENGINE=INNODB;
  • Экспортируйте копию данных, а затем импортируйте ее снова.
  • Используйте сторонние инструменты
  • Если повреждение представляет собой системную область или область «данные строки» таблицы, а не индекс, предыдущий подход бесполезен. Восстанавливайте таблицы только из резервных копий или пытайтесь восстановить как можно больше данных из поврежденных файлов данных.
• Если таблица движка InnoDB повреждена, то необходимо найти серьезную ошибку и немедленно выяснить ее причину. Потому что InnoDB, как правило, не повреждается. Если происходит повреждение, это может быть аппаратная проблема с базой данных, или файлы данных, которыми манипулируют вне MySQL, или ошибка в InnoDB (маловероятно). Нет запроса, который портит InnoDB.
  • Если есть повреждение данных, самое главное выяснить причину, а не просто исправить ее, в противном случае очень вероятно, что повреждение будет продолжаться. Данные можно восстановить, войдя в режим принудительного восстановления данных InnoDB, установив параметр innodb_force_recovery.

3.5.2 Обновление статистики индекса

• Оптимизатор запросов MySQL использует два API для понимания информации о распределении значений индекса механизма хранения:
  • records_in_range(), получить примерное количество записей в этом диапазоне, передав два граничных значения. Возвращает точное значение для некоторых механизмов хранения, таких как MyISAM, и расчетное значение для InnoDB.
  • info(), который возвращает различные типы данных, в том числе кардинальность индекса (сколько записей на значение ключа)
• Если число просканированных строк, предоставленное оптимизатору механизмом хранения, является неточным или если сам план выполнения слишком сложен для точного получения количества строк, совпадающих на каждом этапе, оптимизатор использует статистику индекса для оценки количества просканированных строк. ряды.
• Оптимизатор MySQL использует модель, основанную на стоимости, и основной мерой стоимости является количество строк, которые необходимо просмотреть запросу. Если в таблице нет статистики или статистика неточна, оптимизатор, скорее всего, примет неправильное решение. Исправьте это, запустив ANALYZE TABLE для повторного создания статистики. То, как каждый механизм хранения реализует статистику, отличается, и частота ANALYZE TABLE и стоимость каждого запуска также различны:
  • Механизм памяти вообще не хранит статистику индексов.
  • MyISAM хранит статистику индекса на диске, а ANALYZE TABLE необходимо выполнить полное сканирование индекса, чтобы вычислить количество элементов индекса, а таблица должна быть заблокирована в течение всего процесса.
  • До MySQL 5.5 InnoDB не хранила статистику индексов на диске, а вместо этого оценивала их посредством случайного доступа к индексам и сохраняла их в памяти.
• использоватьSHOW INDEX FROM table;команда для просмотра мощности индекса. Кардинальность показывает, сколько различных значений, по оценке механизма хранения, имеет индексированный столбец. В MySQL 5.0 и более поздних версиях эту информацию можно легко запросить через таблицу INFORMATION_SCHEMA.STATISTICS, но если на сервере большое количество таблиц БД, то скорость получения метаданных отсюда будет очень медленной, и это принесет дополнительные затраты к MySQL.давление.
• Статистика для InnoDB:
  • Механизм вычисляет статистику путем выборки, сначала случайным образом считывает небольшое количество страниц индекса, а затем использует его в качестве выборки для расчета статистики индекса. Количество образцовых страниц в старой версии равно 8, в новой версии можно установить innodb_stats_sample_pages, чтобы установить количество образцовых страниц. Теоретически большие значения могут помочь генерировать более точную информацию индекса, особенно для некоторых очень больших таблиц данных.
  • Статистика индекса вычисляется при первом открытии таблицы, при выполнении команды ANALYZE TABLE или при значительном изменении размера таблицы (вставляется более одной шестнадцатой или 2 миллиарда новых строк).
  • Обновление статистики индекса будет запущено, когда будут открыты определенные таблицы INFORMATION_SCHEMA, или когда используются SHOW TABLE STATUS и SHOW INDEX, или когда на клиенте MySQL включено автозаполнение.
    • Если на сервере находится большой объем данных, это может вызвать серьезные проблемы, особенно при относительно медленном вводе-выводе.Когда клиент или программа мониторинга инициирует выборочное обновление индексной информации, это может вызвать большое количество блокировок. и оказывать дополнительное давление на сервер. Параметр innodb_stats_on_metadata можно отключить, чтобы избежать упомянутых выше проблем.

3.5.3 Уменьшить фрагментацию индекса и данных

• Фрагментация индекса:
  • Индексы B-Tree могут стать фрагментированными, что снижает эффективность запросов. Фрагментированные индексы могут храниться на диске некачественно или не по порядку.
  • По своей структуре B-Tree требует произвольного доступа к диску для поиска листовых страниц, поэтому случайный доступ неизбежен. Однако производительность запросов выше, если листовые страницы физически последовательные и компактные. В противном случае для запросов диапазона, сканирования покрытия индекса и т. д. скорость может быть во много раз медленнее, это более очевидно для сканирования покрытия индекса.
• Фрагментация хранения данных для таблиц (сложнее, чем фрагментация индекса):
  • тип:
    • Фрагментация строк: строки данных хранятся в виде нескольких фрагментов в нескольких местах. Своевременный запрос, который обращается только к одной строке индекса, также может привести к снижению производительности.
    • Внутристрочная фрагментация: Относится к логически последовательным страницам или строкам, хранящимся на диске непоследовательно. Фрагментация между строками оказывает большое влияние на такие операции, как полное сканирование таблиц и сканирование кластерных индексов, которые в противном случае выиграли бы от последовательного хранения данных на диске.
    • Фрагментация свободного места: на страницах данных много свободного места. Это приводит к тому, что сервер считывает много ненужных данных, что приводит к потерям.
  • Для таблиц MyISAM могут иметь место все три типа фрагментации, но InnoDB не имеет фрагментации коротких строк, она перемещает короткие строки и перезаписывает их во фрагмент.
• Перегруппируйте данные:
  • OPTIMIZE TABLE
  • экспорт и импорт
  • Индекс перестроения алгоритма сортировки (для MyISAM)
  • Возможность добавлять и удалять индексы «в режиме онлайн», чтобы устранить фрагментацию индекса, сначала удаляя, а затем воссоздавая индекс (для последних версий InnoDB).
  • ничего не делатьALTER TABLE <table> ENGINE = <engine>;перестроить таблицу (для движков, не поддерживающих OPTIMIZE TABLE)
• Дефрагментация индексов и таблиц должна определяться некоторыми фактическими измерениями, а не произвольными предположениями, а также тем, достигли ли данные стабильного состояния (если дефрагментация сжимает данные вместе, это может привести к тому, что последующие операции обновления вызовут ряд разделений страниц и реорганизации, отрицательно влияющие на производительность, пока данные не достигнут нового устойчивого состояния)

3.6 Резюме

• То, как MySQL и механизмы хранения получают доступ к данным, в сочетании с природой индексов, делает индексы полезной и гибкой работой, которая влияет на доступ к данным (независимо от того, находятся ли данные на диске или в памяти).
• Индексы B-Tree используются в большинстве случаев, другие типы индексов в основном подходят только для специальных целей.
• Когда вы выбираете индекс и пишете запросы для использования этих индексов, всегда нужно помнить следующие три принципа:
  • Однострочный доступ медленный. Много работы тратится впустую, если сервер считывает блок данных из хранилища только для того, чтобы получить одну его строку. Лучшие блоки для чтения содержат столько строк, сколько необходимо. Использование индекса может создавать ссылки на местоположение для повышения эффективности.
  • Последовательный доступ к данным диапазона выполняется быстро. На это есть две причины: последовательный ввод-вывод не требует многократных обращений к диску и намного быстрее, чем случайный ввод-вывод; если сервер может считывать данные в нужном порядке, не требуются дополнительные операции сортировки, а запросы GROUP BY не нуждаются в сортировке. больше. и агрегировать строки по группам.
  • Запросы покрытия индекса выполняются быстро. Если индекс содержит все столбцы, требуемые запросом, подсистеме хранения не нужно возвращаться к таблице для поиска строк, избегая большого количества обращений к одной строке.
• При написании оператора запроса вы должны выбрать максимально подходящий индекс, чтобы избежать однострочного поиска, максимально использовать собственный порядок данных, чтобы избежать дополнительных операций сортировки данных, и использовать индекс для покрытия запроса как насколько это возможно.
• Для некоторых запросов невозможно создать индекс «Samsung», вы должны пойти на компромисс или искать альтернативные стратегии (например, антипарадигмальные слова или предварительные вычисления сводных таблиц).
• Понять, как работают индексы, чтобы создать наиболее подходящие индексы
• Обоснование созданного системой индекса: запросы анализируются по времени ответа.
  • Найдите те запросы, которые занимают больше всего времени или больше всего нагружают сервер
  • Проверьте схему, SQL и структуру индекса этих запросов.
  • Определите, сканирует ли запрос слишком много строк, выполняет много дополнительных сортировок или использует временные таблицы, использует случайный ввод-вывод для доступа к данным или имеет слишком много операций возврата к таблице для столбцов, не входящих в индекс.
• Если запрос не может использовать все возможные индексы, вы должны посмотреть, можно ли создать более подходящий индекс для повышения производительности. Если нет, также попробуйте посмотреть, можно ли переписать запрос в запрос, который эффективно использует существующий индекс или вновь созданный индекс.