Создание лучшего индекса для данного SELECT

Задача

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

• Краткий алгоритм, работающий для многих более простых операторов SELECT и полезный для сложных запросов.
• Примеры применения алгоритма, а также отступления, посвященные исключениям и вариантам.
• В конце — большой список «других случаев».

Надеемся, что новичок быстро освоится, и его/её индексы перестанут быть «новичковскими».

Многие граничные случаи объяснены, так что даже эксперт может найти здесь что-то полезное.

Алгоритм

Вот способ подхода к созданию индекса для данного оператора SELECT. Следуйте приведенным ниже шагам, собирая столбцы для включения в индекс в определенном порядке. Когда шаги закончатся, у вас, как правило, будет «идеальный» индекс.

1. При наличии условия WHERE с несколькими выражениями, соединёнными оператором AND: Включите столбцы (если таковые имеются), в любом порядке, которые сравниваются с константой и не скрыты в функции.

2. У вас есть ещё одна возможность добавить в индекс; выполните первый из применимых пунктов:

• 2а. Один столбец, используемый в «диапазоне» — BETWEEN, '>', LIKE без ведущей подстановки и т. д.
• 2б. Все столбцы в порядке GROUP BY.
• 2в. Все столбцы в порядке ORDER BY, если нет смешивания ASC и DESC.

Отступление

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

Практически все индексы в MySQL структурированы как B-деревья. B-деревья позволяют очень эффективно

• Определять соответствующую(ие) строку(и) по ключу;
• Выполнять «поиск по диапазону» — то есть, начинать с одного значения ключа и повторять поиск «следующей» (или «предыдущей») строки.

Ключ PRIMARY KEY является UNIQUE KEY; UNIQUE KEY является индексом. («KEY» == «INDEX».)

InnoDB «кластеризует» PRIMARY KEY со данными. Таким образом, зная значение PK («PRIMARY KEY»), после прохождения по B-дереву для поиска записи индекса, вы получите все столбцы строки, когда дойдёте до неё. В InnoDB «вторичный ключ» (любой UNIQUE или INDEX, кроме PK) сначала проходит по B-дереву для вторичного индекса, где находит копию PK. Затем он проходит по PK для поиска строки.

Каждая таблица InnoDB имеет PRIMARY KEY. Хотя есть значение по умолчанию, если вы его не укажете, лучше явно указать PK.

Для полноты картины: MyISAM работает по-другому. Все индексы (включая PK) находятся в отдельных B-деревьях. Листовой узел таких B-деревьев содержит указатель (обычно смещение байта) в файле данных.

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

Сначала несколько примеров

Представьте список имён, отсортированных по фамилии, а затем по имени. Вы, безусловно, видели такие списки, и они часто содержат другую информацию, такую как адрес и номер телефона. Предположим, вы хотите найти меня. Если вы помните моё полное имя («Игорь» и «Петров»), найти мою запись легко. Если вы помнили только фамилию («Петров») и начальную букву имени («П»). Вы быстро сосредоточитесь на Петровых и найдёте П в них. Там вы можете вспомнить «Игорь» и пропустить «Роман». Но что, если вы вспомнили имя («Игорь») и только начальную букву фамилии («П»). Теперь у вас проблемы. Вам нужно будет просмотреть всех П — Петров, Игорь; Павлов, Игорь; Попов, Игорь и т. д. Это намного менее эффективно.

Это эквивалентно

    INDEX(last_name, first_name) -- the order of the list.
    WHERE last_name = 'James' AND first_name = 'Rick'  -- best case
    WHERE last_name = 'James' AND first_name LIKE 'R%' -- pretty good
    WHERE last_name LIKE 'J%' AND first_name = 'Rick'  -- pretty bad

Подумайте об этом примере, когда я говорю об операциях «=» и «диапазон» в Алгоритме ниже.

Алгоритм, шаг 1 (WHERE «столбец = константа»)

• WHERE aaa = 123 AND ... : индекс, начинающийся с aaa, хороший.
• WHERE aaa = 123 AND bbb = 456 AND ... : индекс, начинающийся с aaa и bbb, хороший. В этом случае не имеет значения, какой из столбцов aaa или bbb идёт первым в индексе.
• xxx IS NULL : это действует как «= константа» для этого обсуждения.
• WHERE t1.aa = 123 AND t2.bb = 456 — Вы должны учитывать только столбцы в текущей таблице.

Обратите внимание, что выражение должно иметь вид `имя_столбца` = (константа). К этому шагу Алгоритма не относятся: DATE(dt) = '...', LOWER(s) = '...', CAST(s ...) = '...', x='...' COLLATE….

(Если в условии WHERE нет частей «=», соединённых оператором AND, перейдите к шагу 2 без столбцов в предполагаемом индексе.)

Алгоритм, шаг 2

Найдите первый из 2а/2б/2в, который применим; используйте его; затем остановитесь. Если ни один из пунктов не применим, сбор столбцов для индекса завершён.

В некоторых случаях оптимальным является выполнение шага 1 (все равенства) плюс шага 2в (ORDER BY).

Алгоритм, шаг 2а (один диапазон)

«Диапазон» появляется как

• aaa >= 123 — любой из <, <=, >=, >; но не <>, !=
• aaa BETWEEN 22 AND 44
• sss LIKE 'blah%' — но не sss LIKE '%blah'
• xxx IS NOT NULL Добавьте столбец в диапазоне в предполагаемый индекс.

Если в условии WHERE есть другие части, остановитесь.

Полные примеры (предполагается, что ничего больше не следует за фрагментом)

• WHERE aaa >= 123 AND bbb = 1 ⇒ INDEX(bbb, aaa) (порядок WHERE не имеет значения; порядок индекса имеет)
• WHERE aaa >= 123 ⇒ INDEX(aaa)
• WHERE aaa >= 123 AND ccc > 'xyz' ⇒ INDEX(aaa) or INDEX(ccc) (только один диапазон)
• WHERE aaa >= 123 ORDER BY aaa ⇒ INDEX(aaa) — Бонус: ORDER BY будет использовать индекс.
• WHERE aaa >= 123 ORDER BY aaa ⇒ INDEX(aaa) DESC — Тот же бонус.

Алгоритм, шаг 2б (GROUP BY)

Если есть GROUP BY, все столбцы GROUP BY должны быть добавлены в создаваемый индекс в указанном порядке. (Я не знаю, что происходит, если один из столбцов уже есть в индексе.)

Если вы группируете по выражению (включая вызовы функций), вы не можете использовать GROUP BY; остановитесь.

Полные примеры (предполагается, что ничего больше не следует за фрагментом)

• WHERE aaa = 123 AND bbb = 1 GROUP BY ccc ⇒ INDEX(bbb, aaa, ccc) or INDEX(aaa, bbb, ccc) (знаки «=», в любом порядке; затем GROUP BY)
• WHERE aaa >= 123 GROUP BY xxx ⇒ INDEX(aaa) (Вы должны были остановиться на шаге 2а)
• GROUP BY x,y ⇒ INDEX(x,y) (нет условия WHERE)
• WHERE aaa = 123 GROUP BY xxx, (a+b) ⇒ INDEX(aaa) — выражение в GROUP BY, поэтому нет смысла включать даже xxx.

Алгоритм, шаг 2в (ORDER BY)

Если есть ORDER BY, все столбцы ORDER BY должны быть добавлены в создаваемый индекс в указанном порядке.

Если в ORDER BY несколько столбцов и есть смесь ASC и DESC, не добавляйте столбцы ORDER BY; они не помогут; остановитесь.

Если вы сортируете по выражению (включая вызовы функций), вы не можете использовать ORDER BY; остановитесь.

Полные примеры (предполагается, что ничего больше не следует за фрагментом)

• WHERE aaa = 123 GROUP BY ccc ORDER BY ddd ⇒ INDEX(aaa, ccc) — вы должны были остановиться на шаге 2б
• WHERE aaa = 123 GROUP BY ccc ORDER BY ccc ⇒ INDEX(aaa, ccc) — ccc будет использоваться как для GROUP BY, так и для ORDER BY
• WHERE aaa = 123 ORDER BY xxx ASC, yyy DESC ⇒ INDEX(aaa) — смесь ASC и DESC.

Следующие примеры особенно хороши. Обычно LIMIT не может быть применён до тех пор, пока не будут собраны и отсортированы множество строк в соответствии с ORDER BY. Но если индекс пройдёт весь путь ORDER BY, нужно собрать только (OFFSET + LIMIT) строк. Поэтому в этих случаях вы выигрываете в лотерею с новым индексом:

• WHERE aaa = 123 GROUP BY ccc ORDER BY ccc LIMIT 10 ⇒ INDEX(aaa, ccc)
• WHERE aaa = 123 ORDER BY ccc LIMIT 10 ⇒ INDEX(aaa, ccc)
• ORDER BY ccc LIMIT 10 ⇒ INDEX(ccc)
• WHERE ccc > 432 ORDER BY ccc LIMIT 10 ⇒ INDEX(ccc) — Этот «диапазон» совместим с ORDER BY

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

Конец алгоритма

Вы собрали несколько столбцов; поместите их в индекс и добавьте его в таблицу. Это часто создаст «хороший» индекс для оператора SELECT, который у вас есть. Ниже приведены некоторые другие рекомендации, которые могут быть актуальными.

Пример того, как Алгоритм может быть «неправильным»:

    SELECT ... FROM t WHERE flag = true;

Согласно Алгоритму, это требует индекса INDEX(флаг). Однако индексирование столбца с двумя (или небольшим количеством) значениями почти всегда бесполезно. Это называется «низкая кардинальность». Оптимизатор предпочтёт выполнить сканирование таблицы, чем переходить между индексным B-деревом и данными.

С другой стороны, Алгоритм «правильный» в случае

    SELECT ... FROM t WHERE flag = true AND date >= '2015-01-01';

Это потребовало бы составного индекса, начинающегося с флага: INDEX(флаг, дата). Такой индекс, вероятно, будет очень полезным. И он, вероятно, будет более полезным, чем INDEX(дата).

Если создаваемый индекс включает столбец(ы), который(е) часто обновляются, обратите внимание, что обновление потребует дополнительных действий для удаления «строки» из одного места в B-дереве индекса и вставки «строки» обратно в B-дерево. Например:

INDEX(x)
UPDATE t SET x = ... WHERE ...;

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

В этом случае сокращение индекса может быть полезным:

INDEX(z, x)
UPDATE t SET x = ... WHERE ...;

Переход к INDEX(z) уменьшит объём работы при обновлении, но может навредить некоторым операторам SELECT. Это зависит от частоты каждого, а также от многих других факторов.

Ограничения

(Существуют исключения из некоторых из этих правил.)

• Вы не можете создать индекс размером более 3 КБ.
• Вы не можете включить столбец, который эквивалентен значению большему, чем некоторое значение (767 байт — VARCHAR(255) CHARACTER SET utf8).
• Вы можете обрабатывать большие поля с помощью индексирования «префикса», но см. ниже.
• Индекс не должен содержать более 5 столбцов. (Это просто правило большого пальца; ничего не мешает иметь большее количество.)
• Индексы не должны быть избыточными. (См. ниже.)

Флаги и низкая кардинальность

INDEX(flag) почти никогда не полезен, если `флаг` имеет очень мало значений. Более конкретно, когда вы говорите WHERE flag = 1, и «1» встречается более чем в 20% случаев, такой индекс будет игнорироваться. Оптимизатор предпочтёт сканировать таблицу вместо перехода между индексом и данными для более чем 20% строк.

(«20%» фактически находится где-то между 10% и 30%, в зависимости от фазы луны.)

«Покрывающие» индексы

«Покрывающий» индекс — это индекс, который содержит все столбцы в операторе SELECT. Он особенный тем, что оператор SELECT можно выполнить, просмотрев только B-дерево индекса. (Так как PRIMARY KEY InnoDB сгруппирован с данными, «покрытие» не приносит пользы при рассмотрении PRIMARY KEY.)

Мини-кулинарная книга: 1. Составьте список столбца(ов) в соответствии с «Алгоритмом» выше. 2. Добавьте в конец списка остальные столбцы, присутствующие в SELECT, в любом порядке.

Примеры:

• SELECT x FROM t WHERE y = 5; ⇒ INDEX(y,x) — Алгоритм сказал только INDEX(y)
• SELECT x,z FROM t WHERE y = 5 AND q = 7; ⇒ INDEX(y,q,x,z) — y и q в любом порядке (Алгоритм), затем x и z в любом порядке (покрытие).
• SELECT x FROM t WHERE y > 5 AND q > 7; ⇒ INDEX(y,q,x) — y или q сначала (до этого доходит Алгоритм), затем другие два поля в любом порядке.

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

Но...

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

Избыточные/лишние индексы

ИНДЕКС(a,b) может найти всё, что может найти ИНДЕКС(a). Поэтому вам не нужен второй. Удалите более короткий.

Если у вас много SELECT-запросов, которые генерируют много ИНДЕКСОВ, это может вызвать другую проблему. Каждый индекс должен быть обновлён (рано или поздно) для каждого INSERT. Больше индексов ⇒ медленные INSERT. Ограничьте количество индексов в таблице примерно до 6 (правило большого пальца).

Обратите внимание в кулинарной книге, как в нескольких местах сказано «в любом порядке». Если, например, у вас есть оба этих (в разных SELECT-запросах):

• WHERE a=1 AND b=2 требует либо ИНДЕКС(a,b), либо ИНДЕКС(b,a)
• WHERE a>1 AND b=2 требует только ИНДЕКС(b,a) Включите только ИНДЕКС(b,a), так как он обрабатывает оба случая с помощью одного ИНДЕКСА.

Предположим, у вас много индексов, включая (a,b,c,dd) и (a,b,c,ee). Они довольно длинные. Подумайте о том, чтобы выбрать один из них или просто иметь (a,b,c). Иногда селективность (a,b,c) настолько хороша, что добавление 'dd' или 'ee' не меняет ситуацию существенно.

Оптимизатор выбирает ORDER BY

Основная кулинарная книга пропускает важную оптимизацию, которая иногда используется. Оптимизатор иногда игнорирует WHERE и вместо этого использует ИНДЕКС, который соответствует ORDER BY. Это, конечно, должно быть идеальное совпадение — все столбцы в том же порядке. И все ASC или все DESC.

Это становится особенно полезным, если есть LIMIT.

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

• Если WHERE выполняет очень мало фильтрации, извлечение строк в порядке ORDER BY избегает сортировки и имеет мало потерь (из-за «малой фильтрации»). Использование ИНДЕКСА, соответствующего ORDER BY, в этом случае лучше.
• Если WHERE выполняет много фильтрации, ORDER BY тратит много времени на извлечение строк только для того, чтобы отфильтровать их. Использование ИНДЕКСА, соответствующего условию WHERE, лучше.

Что нужно делать? Если вы считаете, что «малая фильтрация» вероятна, создайте индекс со столбцами ORDER BY в правильном порядке и надейтесь, что оптимизатор будет его использовать, когда это необходимо.

ИЛИ

Случаи…

• WHERE a=1 OR a=2 — это преобразуется в WHERE a IN (1,2) и оптимизируется таким образом.
• WHERE a=1 OR b=2 обычно оптимизировать нельзя.
• WHERE x.a=1 OR y.b=2 Это ещё хуже из-за использования двух разных таблиц.

Решение — использовать UNION. Каждая часть UNION оптимизируется отдельно. Для второго случая:

   ( SELECT ... WHERE a=1 )   -- and have INDEX(a)
   UNION DISTINCT -- "DISTINCT" is assuming you need to get rid of dups
   ( SELECT ... WHERE b=2 )   -- and have INDEX(b)
   GROUP BY ... ORDER BY ...  -- whatever you had at the end of the original query

Теперь запрос может хорошо использовать два разных индекса. Примечание: «объединение индексов» может начаться в исходном запросе, но он не обязательно быстрее, чем UNION. Статья в блоге о составных индексах, включая «объединение индексов»

Третий случай (OR через 2 таблицы) похож на второй.

Если у вас изначально был LIMIT, UNION становится сложным. Если вы начинали с ORDER BY z LIMIT 190, 10, то UNION должен быть

   ( SELECT ... LIMIT 200 )   -- Note: OFFSET 0, LIMIT 190+10
   UNION DISTINCT -- (or ALL)
   ( SELECT ... LIMIT 200 )
   LIMIT 190, 10              -- Same as originally

TEXT / BLOB

Вы не можете напрямую индексировать столбец TEXT или BLOB или большой VARCHAR или большой BINARY. Однако вы можете использовать «префиксный» индекс: INDEX(foo(20)). Это означает индексирование первых 20 символов `foo`. Но... Это редко полезно.

Пример префиксного индекса:

    INDEX(last_name(2), first_name)

Индекс для меня содержал бы «Ja», «Rick». Это не полезно для различения «Jamison», «Jackson», «James» и т. д., поэтому индекс практически бесполезен, и оптимизатор часто его игнорирует.

Вероятно, никогда не делайте UNIQUE(foo(20)), потому что это налагает ограничение на уникальность первых 20 символов столбца, а не всего столбца!

Подробнее о префиксной индексации

Даты

DATE, DATETIME и т. д. сложно сравнивать.

Некоторые заманчивые, но неэффективные приёмы:

date_col LIKE '2016-01%' — необходимо преобразовать date_col в строку, поэтому действует как функция LEFT(date_col, 4) = '2016-01' — скрытие столбца в функции DATE(date_col) = 2016 — скрытие столбца в функции

Все должны выполнить полный поиск. (С другой стороны, удобно использовать GROUP BY LEFT(date_col, 7) для группировки по месяцам, но это не проблема индексации.)

Это эффективно и может использовать индекс:

        date_col >= '2016-01-01'
    AND date_col  < '2016-01-01' + INTERVAL 3 MONTH

Этот случай работает, потому что оба правых значения преобразуются в константы, затем это «диапазон». Мне нравится схема с INTERVAL, так как она позволяет избежать вычисления последнего дня месяца. И она избегает добавления '23:59:59', что неправильно, если у вас есть микросекундные временные метки. (И другие случаи.)

EXPLAIN Key_len

Выполните EXPLAIN SELECT... (и EXPLAIN FORMAT=JSON SELECT... если у вас 5.6.5). Посмотрите на выбранный Key и Key_len. По этим данным можно определить, сколько столбцов индекса используется для фильтрации. (JSON делает получение ответа проще.) По этому можно определить, использует ли он столько же ИНДЕКСА, как вы думали. Оговорка: Key_len охватывает только часть WHERE; в не-JSON выводе не будет легко сказать, обрабатывался ли GROUP BY или ORDER BY индексом.

В

IN (1,99,3) иногда оптимизируется так же эффективно, как «=», но не всегда. Более старые версии MySQL не оптимизировали это так хорошо, как новые версии. (5.6, возможно, является основной вехой.)

IN ( SELECT ... )

С версии 4.1 до 5.5 IN ( SELECT ... ) оптимизировался очень плохо. SELECT фактически переоценивался каждый раз. Часто его можно преобразовать в JOIN, что работает намного быстрее. Вот шаблон, которому нужно следовать:

SELECT  ...
    FROM  a
    WHERE  test_a
      AND  x IN (
        SELECT  x
            FROM  b
            WHERE  test_b
                );
⇒
SELECT  ...
    FROM  a
    JOIN  b USING(x)
    WHERE  test_a
      AND  test_b;

Выражения SELECT потребуют префикса «a.» для имён столбцов.

К сожалению, есть случаи, когда шаблон трудно применить.

5.6 выполняет некоторую оптимизацию, но, вероятно, не так хорошо, как JOIN.

Если в подзапросе есть JOIN или GROUP BY или ORDER BY LIMIT, это усложняет JOIN в новом формате. Поэтому может быть лучше использовать этот шаблон:

SELECT  ...
    FROM  a
    WHERE  test_a
      AND  x IN ( SELECT  x  FROM ... );
⇒
SELECT  ...
    FROM  a
    JOIN        ( SELECT  x  FROM ... ) b
        USING(x)
    WHERE  test_a;

Оговорка: если вы получите два подзапроса, соединённые JOIN, обратите внимание, что ни один из них не имеет индексов, поэтому производительность может быть очень низкой. (5.6 улучшает её, динамически создавая индексы для подзапросов.)

В MariaDB и Oracle 5.7 ведутся работы, связанные с «NOT IN», «NOT EXISTS» и «LEFT JOIN..IS NULL»; вот старая дискуссия по этой теме. Поэтому то, что я говорю здесь, может не быть окончательным ответом.

Развернуть/Сжатие

Когда у вас есть JOIN и GROUP BY, у вас может возникнуть ситуация, когда JOIN развернул больше строк, чем исходный запрос (из-за многих-ко-многим), но вам нужна только одна строка из исходной таблицы, поэтому вы добавили GROUP BY, чтобы сжать обратно до нужного набора строк.

Это развертывание + сжатие само по себе дорого. Лучше их избегать, если это возможно.

Иногда следующее работает.

Использование DISTINCT или GROUP BY для противодействия развёртыванию

SELECT  DISTINCT
        a.*,
        b.y
    FROM a
    JOIN b
⇒
SELECT  a.*,
        ( SELECT GROUP_CONCAT(b.y) FROM b WHERE b.x = a.x ) AS ys
    FROM a

При использовании второй таблицы только для проверки существования:

SELECT  a.*
    FROM a
    JOIN b  ON b.x = a.x
    GROUP BY a.id
⇒
SELECT  a.*,
    FROM a
    WHERE EXISTS ( SELECT *  FROM b  WHERE b.x = a.x )

Ещё один вариант

Таблица соответствия многие-ко-многим

Сделайте так.

    CREATE TABLE XtoY (
        # No surrogate id for this table
        x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
        y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
        # Include other fields specific to the 'relation'
        PRIMARY KEY(x_id, y_id),            -- When starting with X
        INDEX      (y_id, x_id)             -- When starting with Y
    ) ENGINE=InnoDB;

Примечания:

• Отсутствие AUTO_INCREMENT id для этой таблицы — заданный PK — «естественный» PK; нет веских причин для суррогата.
• «MEDIUMINT» — напоминание о том, что все INT должны быть как можно меньше (меньше ⇒ быстрее). Конечно, объявление здесь должно соответствовать определению в связанной таблице.
• «UNSIGNED» — почти все INT можно объявить неотрицательными
• «NOT NULL» — ну, это правда, не так ли?
• «InnoDB» — эффективнее MyISAM из-за того, как PRIMARY KEY кластеризован с данными в InnoDB.
• «INDEX(y_id, x_id)» — PRIMARY KEY делает эффективным перемещение в одном направлении; этот индекс делает эффективным перемещение в другом направлении. Нет необходимости указывать UNIQUE; это будет лишняя работа при INSERT.
• В дополнительном индексе достаточно указать INDEX(y_id), так как он неявно включает x_id. Но я хотел бы сделать более очевидным, что я надеюсь на «покрывающий» индекс.

Для условного вставки новых ссылок используйте IODKU

Обратите внимание, что если у вас был AUTO_INCREMENT в этой таблице, IODKU быстро «использовал бы» id.

Подзапросы и UNION

Каждый подзапрос SELECT и каждый SELECT в UNION могут рассматриваться отдельно для поиска оптимального ИНДЕКСА.

Исключение: в «коррелированном» («зависимом») подзапросе часть WHERE, которая зависит от внешней таблицы, не легко учитывается при создании ИНДЕКСА. (Уклонение!)

JOIN

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

Оптимизатор обычно начинает с одной таблицы и извлекает необходимые данные из неё. Когда он находит подходящую (то есть, соответствующую условию WHERE, если таковое есть) строку, он обращается к «следующей» таблице. Это называется NLJ («вложенное циклическое соединение»). Процесс фильтрации и обращения к следующей таблице продолжается через остальные таблицы.

Оптимизатор обычно выбирает «первую» таблицу на основе этих подсказок:

• STRAIGHT_JOIN принудительно устанавливает порядок таблиц.
• Условие WHERE ограничивает необходимые строки (будь то индексированные или нет).
• Таблица «слева» в LEFT JOIN обычно предшествует таблице «справа». (Глядя на определения таблиц, оптимизатор может решить, что «слева» не имеет значения.)
• Текущие индексы стимулируют порядок.
• и т. д.

Выполнение EXPLAIN показывает порядок таблиц, который оптимизатор, скорее всего, использует сегодня. После добавления нового ИНДЕКСА оптимизатор может выбрать другой порядок таблиц. Вы должны ожидать изменения порядка, предположить, какой порядок наиболее разумный, и соответственно создать ИНДЕКСы. Затем запустите EXPLAIN, чтобы увидеть, был ли мозг оптимизатора на одной волне с вами.

Вы должны создать ИНДЕКС для «первой» таблицы, основываясь на любых частях условий WHERE, GROUP BY и ORDER BY, которые относятся к ней. Если GROUP/ORDER BY упоминает другую таблицу, вы должны игнорировать это условие.

Ко второй (и последующим) таблице будет обращение на основе условия ON. (Вместо использования commajoin, пожалуйста, используйте JOIN с ключевым словом JOIN и условием ON!) Кроме того, могут быть части условия WHERE, которые актуальны. GROUP/ORDER BY не должны учитываться при написании оптимального ИНДЕКСА для последующих таблиц.

Разбиение

РАЗДЕЛЕНИЕ (PARTITIONing) редко является заменой хорошего ИНДЕКСА.

РАЗДЕЛЕНИЕ ПО ДИАПАЗОНУ (PARTITION BY RANGE) — это техника, которая иногда полезна, когда индексирование недостаточно эффективно. В двумерной ситуации, такой как близость в географическом смысле, одно измерение частично может обрабатываться с помощью обрезки разделов; затем другое измерение может обрабатываться с помощью обычного индекса (предпочтительно первичного ключа). Подробнее об этом: Найти 10 ближайших пиццерий.

ПОЛНЫЙ ТЕКСТ (FULLTEXT)

FULLTEXT теперь реализован как в InnoDB, так и в MyISAM. Он предоставляет способ поиска «слов» в столбцах TEXT. Это намного быстрее (когда это применимо), чем col LIKE '%word%'.

    WHERE x = 1
      AND MATCH (...) AGAINST (...)

всегда(?) использует индекс FULLTEXT в первую очередь. То есть, весь алгоритм становится недействительным, когда один из операторов AND является MATCH.

Признаки новичка

• Отсутствие «составных» (также «составных») индексов
• Отсутствие первичного ключа
• Избыточные индексы (особенно очевидно — первичный ключ PRIMARY KEY(id), ключ KEY(id))
• Индексирование большинства или всех столбцов по отдельности («Но я проиндексировал всё»)
• «Запятая-соединение» — это FROM a, b WHERE a.x=b.x вместо FROM a JOIN b ON a.x=b.x

Ускорение wp_postmeta

Опубликованная таблица (см. Википедию) выглядит так

    CREATE TABLE wp_postmeta (
      meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      post_id bigint(20) unsigned NOT NULL DEFAULT '0',
      meta_key varchar(255) DEFAULT NULL,
      meta_value longtext,
      PRIMARY KEY (meta_id),
      KEY post_id (post_id),
      KEY meta_key (meta_key)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Проблемы:

• AUTO_INCREMENT не приносит пользы; на самом деле он замедляет большинство запросов и засоряет диск.
• Гораздо лучше первичный ключ PRIMARY KEY(post_id, meta_key) — кластеризованный, обрабатывает обе части обычного JOIN.
• BIGINT избыточен, но это нельзя исправить без изменения других таблиц.
• VARCHAR(255) может быть проблемой в 5.6 с utf8mb4; см. обходные решения ниже.
• Когда столбцы `meta_key` или `meta_value` могут быть NULL?

Решения:

    CREATE TABLE wp_postmeta (
        post_id BIGINT UNSIGNED NOT NULL,
        meta_key VARCHAR(255) NOT NULL,
        meta_value LONGTEXT NOT NULL,
        PRIMARY KEY(post_id, meta_key),
        INDEX(meta_key)
        ) ENGINE=InnoDB;

Журнал публикаций (Postlog)

Первоначальная публикация: март 2015 г.; Обновление: февраль 2016 г.; Добавлено DATE: июнь 2016 г.; Добавлено пример WP: май 2017 г.

Советы в этом документе применимы к MySQL, MariaDB и Percona.

См. также

• Слайды туториала Percona 2015
• Некоторая информация в руководстве MySQL: Оптимизация ORDER BY
• Короткий, но сложный пример
• Страница руководства MySQL об обработке диапазонов в составных индексах
• Некоторые обсуждения JOIN
• Основы индексирования: оптимизация запросов MySQL к одной таблице (Стефан Комбодон - Percona)
• Сложный запрос, хорошо объяснённый.

Этот блог — сводка туториала Percona, который я дал в 2013 году, плюс многолетний опыт исправления тысяч медленных запросов на сотнях систем. Извините, что это не объясняет, как создавать INDEXы для всех SELECT-запросов. Некоторые из них слишком сложны.

Рик Джеймс любезно позволил нам использовать эту статью в базе знаний.

Сайт Рика Джеймса содержит другие полезные советы, руководства, оптимизации и советы по отладке.

Исходный источник: http://mysql.rjweb.org/doc.php/random