объясните | Этот несравненный меч оптимизации индексов, вы действительно знаете, как им пользоваться?

MySQL

предисловие

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

Медленные запросы вызывают много проблем, как мы можем оптимизировать медленные запросы?

Основные решения:

  • Контролируйте выполнение sql, отправляйте оповещения по электронной почте и SMS и быстро определяйте медленный запрос sql.
  • Включить функцию журнала медленных запросов к базе данных
  • Упрощение бизнес-логики
  • Рефакторинг и оптимизация кода
  • Асинхронная обработка
  • SQL-оптимизация
  • Оптимизация индекса

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

Как проверить статус выполнения индекса sql?

Да, добавить перед sqlexplainКлючевое слово, мы можем видеть его план выполнения.Через план выполнения мы можем четко видеть выполнение таблицы и индекса, выполняется ли индекс или нет, порядок выполнения индекса и тип индекса.

Шаги оптимизации индекса:

  1. Используйте объяснение для просмотра плана выполнения sql
  2. Определение того, какие индексы используются ненадлежащим образом
  3. Оптимизируйте sql, sql может потребоваться несколько оптимизаций для достижения оптимального значения использования индекса.

Поскольку первым шагом в оптимизации индекса является использование объяснения, давайте всесторонне рассмотрим его.

Введение для объяснения

Давайте посмотрим, как официальная документация mysql описывает объяснение:

  • EXPLAIN можно использовать с операторами SELECT, DELETE, INSERT, REPLACE и UPDATE.
  • Когда EXPLAIN используется с интерпретируемым оператором, MySQL отображает информацию от оптимизатора о плане выполнения оператора. То есть MySQL объясняет, как он будет обрабатывать оператор, включая информацию о том, как соединить таблицы и в каком порядке.
  • Когда EXPLAIN используется с неинтерпретируемым оператором, он отображает план выполнения оператора, выполняемого в именованном соединении.
  • Для операторов SELECT EXPLAIN может отображать предупреждающие сообщения для других планов выполнения.

объяснить подробно

Синтаксис объяснения:

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FORCONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECTstatement
  | DELETEstatement
  | INSERTstatement
  | REPLACEstatement
  | UPDATEstatement
}

Используйте простой sql, чтобы увидеть эффект от использования ключевого слова объяснения:

explain select * from test1;

Результаты:

Из приведенного выше рисунка видно, что в результате выполнения будут отображаться столбцы информации 12. Конкретная информация каждого столбца выглядит следующим образом:

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

Без дальнейших церемоний, давайте начнем.

1. идентификатор столбца

Значением этого столбца является порядковый номер в запросе на выборку, например: 1, 2, 3, 4 и т. д., который определяет порядок выполнения таблицы.

Обычно в плане выполнения sql есть три ситуации:

  • тот же идентификатор
  • другой идентификатор
  • id одинаковый и другой

Итак, каков порядок выполнения этих трех таблиц прецедентов?

1.1 тот же идентификатор

Выполните sql следующим образом:

explain select * from test1 t1 inner join test1 t2 on t1.id=t2.id

результат:На рисунке мы видим, что два идентификатора данных в результате выполнения равны 1, что является одним и тем же.

Каков порядок выполнения этой таблицы прецедентов?

Ответ: Выполнять сверху вниз, сначала выполнить таблицу t1, а затем выполнить таблицу t2.

Как посмотреть таблицу исполнения?

Ответ: Посмотрите на поле таблицы, которое будет подробно объяснено позже.

1.2 id другой

Выполните sql следующим образом:

explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);

результат:

Мы видим, что идентификаторы двух данных в результате выполнения различаются, первые данные равны 1, а вторые данные равны 2.

Каков порядок выполнения этой таблицы прецедентов?

Ответ: Первым будет выполняться больший порядковый номер, здесь он будет выполняться снизу вверх, сначала будет выполняться таблица t2, а затем будет выполняться таблица t1.

1.3 Идентификаторы одинаковые и разные

Выполните sql следующим образом:

explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid

результат:

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

Каков порядок выполнения этой таблицы дел?

Ответ: Сначала выполните тот, у которого серийный номер больше, и выполните его снизу вверх. Если серийные номера совпадают, выполните сверху вниз. Таким образом, порядок таблиц в этом столбце следующий: test1, t1,

Возможно, здесь у вас возникнут вопросы: Какого черта?

Он представляет собой производную таблицу, не волнуйтесь, мы поговорим об этом позже.

Еще один вопрос: может ли значение столбца id быть пустым?

Ответ станет известен позже.

2. столбец select_type

Этот столбец представляет тип выбора. В частности, он включает в себя следующие 11 типов:

Но наиболее часто используемые из них:

Давайте посмотрим, как выглядят эти типы SELECT:

  1. SIMPLE

Выполните sql следующим образом:

explain select * from test1;

результат:

рисунок

Он появляется только в простых запросах SELECT и не содержит подзапросов и UNION.Этот тип более интуитивно понятен, поэтому я не буду говорить больше.

  1. ОСНОВНОЙ и ПОДЗАПРОС

Выполните sql следующим образом:

explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);

результат:

Мы видим, что в sql этого вложенного запроса самая внешняя таблица t1 имеет тип PRIMARY, а самая внутренняя таблица t2 подзапроса имеет тип SUBQUERY.

  1. DERIVED

Выполните sql следующим образом:

explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid

результат:

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

  1. СОЮЗ и СОЮЗ РЕЗУЛЬТАТ

Выполните sql следующим образом:

explain
select * from test1
union
select* from test2

результат:

Таблица test2 является запросом после ключевого слова UNION, поэтому она помечена как UNION, а test1 является основной таблицей и помечена как PRIMARY. А представляет объединение таблиц с id=1 и id=2, а результат помечен как UNION RESULT.

UNION и UNION RESULT обычно являются парными.

Кроме того, чтобы ответить на вопрос выше: разрешено ли значение столбца id быть нулевым?

Если вы внимательно посмотрите на рисунок выше, вы обнаружите, что столбец id может быть пустым, и это происходит, когда тип SELECT: UNION RESULT.

3. столбец таблицы

Значение этого столбца представляет собой имя таблицы, на которую ссылается выходная строка, например, предыдущая: test1, test2 и т. д.

Но это также может быть одно из следующих значений:

: M объединение N строк со значениями и id. : значение N для идентификатора результата производной таблицы, связанного с этой строкой. Производные таблицы могут быть получены (например) из подзапросов в предложении FROM. : результат подзапроса со значением идентификатора N

4. Столбец разделов

Значение этого столбца представляет раздел, из которого запрос будет сопоставлять записи.

5. тип столбца

Значение этого столбца указывает тип соединения и является важным индикатором работы индекса. Содержит следующие виды:

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

Мы должны сосредоточиться на следующих типах:

system > const > eq_ref > ref > range > index > ALL

Перед демонстрацией поясню, что в таблице test2 есть только один кусок данных:

И общий индекс строится на поле кода:

Давайте посмотрим, как один за другим появляются несколько распространенных типов соединений:

system

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

const

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

explain select * from test2 where id=1;

результат:

eq_ref

Часто используется для сканирования первичного ключа или уникального индекса. Выполните sql следующим образом:

explain select * from test2 t1 inner join test2 t2 on t1.id=t2.id;

результат:

На данный момент некоторые люди могут быть озадачены, и const, и eq_ref являются сканированием по первичному ключу или уникальному индексу, в чем разница?

Ответ: const индексируется только один раз, и первичный ключ eq_ref соответствует первичному ключу.Поскольку в таблице есть несколько частей данных, при нормальных обстоятельствах ее необходимо проиндексировать несколько раз, чтобы сопоставить их все.

ref

Обычно используется для сканирования непервичных ключей и уникальных индексов. Выполните sql следующим образом:

explain select * from test2 where code = '001';

результат:

range

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

explain select * from test2 where id between 1 and 2;

результат:

index

Полное сканирование индекса. Выполните sql следующим образом:

explain select code from test2;

результат:

ALL

Полное сканирование таблицы. Выполните sql следующим образом:

explain select *  from test2;

результат:

6. столбец возможных_ключей

В этом столбце представлены возможные варианты индекса.

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

Если этот столбец имеет значение NULL, связанный с ним индекс отсутствует. В этом случае вы можете повысить производительность запроса, проверив предложение WHERE, чтобы увидеть, ссылается ли оно на некоторые столбцы, подходящие для индексации.

7. ключевой столбец

Этот столбец представляет фактический используемый индекс.

Может случиться так, что столбец возможных_ключей имеет значение NULL, но ключ не равен NULL.

Перед демонстрацией давайте взглянем на структуру таблицы test1:

Данные в таблице test1:

Используемый индекс:

Поля кода и имени используют общий индекс.

Выполните sql следующим образом:

explain select code  from test1;

результат:

Ожидается, что этот sql не будет использовать индекс, но на самом деле использует индекс полного сканирования индекса.

8. столбец key_len

В этом столбце указана длина используемого индекса. Столбец ключа выше может видеть, используется ли индекс, а столбец key_len может также видеть, достаточно ли используется индекс. Неудивительно, что это самый важный столбец.

Возникает ключевой вопрос: как рассчитывается key_len?

Три фактора определяют значение key_len:

  1. набор символов

  2. длина

  3. пусто

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

В настоящее время в моей базе данных используется формат кодировки символов: UTF8 занимает 3 байта.

Количество байтов, занимаемых общими полями MySQL:

Кроме того, добавьте 1 байт, если тип поля допускает null.

Как рассчитывается 184 на картинке выше?

184 = 30 * 3 + 2 + 30 * 3 + 2

Затем измените тип поля кода таблицы test1 на char и измените его, чтобы разрешить null:

Выполните sql следующим образом:

explain select code  from test1;

результат:

Как это рассчитывается? 183 = 30 * 3 + 1 + 30 * 3 + 2

Еще один вопрос: почему в этой колонке указано, используется индекс полностью или нет?

Выполните sql следующим образом:

explain select code  from test1 where code='001';

результат:

На приведенном выше рисунке используется объединенный индекс: idx_code_name, Если индекс соответствует всем key_len, он должен быть 183, но на самом деле это 92, что означает, что используются не все индексы, и индекс используется не полностью.

8. справочная колонка

Этот столбец представляет столбец или константу, по которой попадает индекс.

Выполните sql следующим образом:

explain select *  from test1 t1 inner join test1 t2 on t1.id=t2.id where t1.code='001';

результат:

Мы видим, что индекс, попадающий в таблицу t1, является const (постоянным), а индекс, попадающим в t2, является полем id таблицы t1 библиотеки столбца sue.

9. столбец строк

Этот столбец представляет количество строк, которые, по мнению MySQL, должны быть проверены для выполнения запроса.

Для таблиц InnoDB это число является приблизительным и не всегда может быть точным.

10.отфильтрованный столбец

В этом столбце представлен расчетный процент строк таблицы, отфильтрованных по критериям таблицы. Максимальное значение равно 100, что означает, что строки не фильтруются. Уменьшение значений от 100 указывает на усиление фильтрации.

rows показывает предполагаемое количество проверенных строк, а rows × filtered показывает количество строк, присоединенных к таблице ниже. Например, если строк 1000 и отфильтровано 50,00 (50%), количество строк, соединенных с таблицей ниже, равно 1000 × 50% = 500.

11. Дополнительный столбец

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

Impossible WHERE

Указывает, что условие после WHERE всегда ложно,

Выполните sql следующим образом:

explain select code  from test1 where 'a' = 'b';

результат:

Using filesort

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

Выполните sql следующим образом:

explain select code  from test1 order by name desc;

результат:

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

Using index

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

В приведенном выше примере он фактически используется: Использование индекса, поскольку возвращается только один столбец кода, его поле перешло в индекс.

Using temporary

Указывает, используется ли временная таблица, обычно видимая по порядку и по операторам группировки.

Выполните sql следующим образом:

explain select name  from test1 group by name;

результат:

Using where

Указывает, что используется фильтр условия where.

Using join buffer

Указывает, следует ли использовать буферизацию соединения. Таблицы из более ранних объединений частично считываются в буфер соединения, а их строки используются из буфера для выполнения соединения с текущей таблицей.

Процесс оптимизации индекса

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

  2. Используйте план выполнения объяснения для просмотра использования индекса

  3. Фокус:

    ключ (чтобы увидеть, используется ли индекс)

    key_len (чтобы убедиться, что использование индекса достаточно)

    тип (см. тип индекса)

    Дополнительно (см. дополнительную информацию: сортировка, временная таблица, где условие ложно и т. д.)

    В общем, проблему с индексом можно найти по этим 4 столбцам.

  4. Оптимизируйте sql в соответствии с проблемой индекса, обнаруженной на предыдущем шаге.

  5. вернуться к шагу 2

Последнее слово (пожалуйста, обратите внимание, не проституируйте меня по пустякам)

Если эта статья оказалась для вас полезной или познавательной, отсканируйте QR-код и обратите внимание, ваша поддержка — самая большая мотивация для меня продолжать писать.

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

Попросите в один клик три ссылки: лайк, вперед и смотреть.

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

Категории