Выполнение запросов без кэша

После оператора SELECT добавляется спецификатор SQL_NO_CACHE.

SELECT SQL_NO_CACHE ...

Причиной выполнять запросы “мимо” кэша имеет смысл при профилировании и отладке запросов, когда попадание в кэш может сильно “наврать” в смысле времени выполнения запроса.

Кэш автоматически очищается при любых изменениях таблицы, поэтому важным оказывается, как правило, именно время выполнения запроса без кэша.

Стоимость запроса

SHOW SESSION STATUS LIKE 'last_query_cost';

Как правило, из всех возможных вариантов запроса, оптимизатор выбирает запрос с минимальной стоимостью, и выполняет его.

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

Причинами могут быть

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

Конечно, над кодом оптимизатора постоянно ведется работа, и, как правило, в каждой следующей версии он становится “умнее”. Однако, не следует считать оптимизатор “серебрянной пулей”.

Профилирование (продолжительность запроса)

В начале сессии пишется

SET @@profiling = 1;

В конце используется SHOW PROFILES.

Для очистки истории, может использоваться конструкция

SET @@profiling = 0;
SET @@profiling_history_size = 0;
SET @@profiling_history_size = 100;
SET @@profiling = 1;

Пример:

Способы оптимизации, применяемые оптимизатором

Изменение порядка соединения
Применение правил алгебраической эквивалентности
Оптимизации COUNT, MIN, MAX
Свертка константных выражений и распространение равенства Например, sql SELECT SQL_NO_CACHE * FROM reserves r JOIN tariffs t USING (idCourt); приведет к полному сканированию таблицы и полному сканированию индексов. С другой стороны, sql SELECT SQL_NO_CACHE * FROM reserves r JOIN tariffs t USING (idCourt) WHERE idCourt=1; сводится к двум выборам по индексированным полям: sql SELECT * FROM (SELECT * FROM reserves WHERE idCourt=1) AS r, (SELECT * FROM tariffs WHERE idCourt=1) as t;
Покрывающие индексы Если индекс содержит все необходимые столбцы, то MySQL может просто использовать индексы, вообще не трогая данные таблицы
Оптимизация подзапросов Подзапросы могут быть преобразованы к эквивалентной форме и сведены к выборке по индексу
Раннее завершение Если оптимизатор обнаруживает, что какое-то условие не может выполняться, то вся конструкция по этому условию выкидывается из запроса. Например,
```
SELECT sql_no_cache
*
FROM
    reserves r join tariffs t using (idCourt)
WHERE
 	idCourt = -1;
```
завершится сразу после чтения определения таблиц, обнаружив, что idCourt имеет тип UNSIGNED INT.
Сравнение по списку IN IN в MySQL реализован с помощью сортированного двоичного поиска, и оказывается часто гораздо более эффективен, чем композиция с помощью OR.

Способы влияния на работу оптимизатора

`STRAIGHT_JOIN`

Может использоваться вместо JOIN (но USING не работает), либо в качестве спецификтора после SELECT, тогда все JOIN работают как STRAIGHT_JOIN.

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

Хинты индексов

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

Синтаксис:

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [, index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

Например:

SELECT sql_no_cache
    *
FROM
    reserves r straight_join tariffs t force index (PRIMARY) on t.idCourt = r.idCourt;
    -- reserves r join tariffs t on t.idCourt = r.idCourt;

во многих случаях выполнится быстрее, чем

SELECT sql_no_cache
    *
FROM
    reserves r join tariffs t on t.idCourt = r.idCourt;

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

Оптимизация COUNT

Выборка COUNT(*) без условий, как правило, гораздо быстрее, чем выборка по условию. Так же, чем больший объем данных выбирается по условию, тем медленнее работает запрос.

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

select sql_no_cache count(*) from world.City where id > 5;

Можно переформулировать как

select sql_no_cache (select count(*) from world.City) - count(*) from world.City where id <= 5;

Это дает прирост производительности по времени около 15% на таблицах InnoDB и еще больше на таблицах MyISAM.

Правила построения индексов

Всегда стройте индексы по столбцам, которые используются в ON, USING и WHERE
Учитывайте порядок соединения. Первая таблица в соединении не использует индексы! Учитывайте работу оптимизатора.
Старайтесь, чтобы в GROUP BY и ORDER BY встречались столбцы из одной таблицы. Составляйте индексы по ним.

Насчет ORDER BY в запросах с соединениями

Для ORDER BY важно, чтобы таблица, по которой будет производиться сортировка, в JOIN была на первом месте. Однако оптимизатор может переставлять таблицы в удобном ему порядке (и не всегда правильном). В таких случаях следует использовать STRAIGHT_JOIN.

В качестве примера, из одного популярного форумного движка:

SELECT t.*, p.*, u.username
FROM topics as t, posts as p, users as u
WHERE t.topic_replies>0 AND p.poster_id=u.user_id AND topic_first_post_id<>p.post_id AND topic_approved=1 AND p.topic_id=t.topic_id AND t.forum_id='16' AND p.post_id<'244103'
ORDER by post_id desc LIMIT 40

преобразованный к

SELECT STRAIGHT_JOIN t.*, p.*, u.username
FROM phpbb3_posts as p, phpbb3_topics as t, phpbb3_users as u
WHERE t.topic_replies>0 AND p.poster_id=u.user_id AND topic_first_post_id<>p.post_id AND topic_approved=1 AND p.topic_id=t.topic_id AND t.forum_id='13' AND p.post_id<'234103'
ORDER by post_id desc LIMIT 40

на больших таблицах дает выигрыш в несколько сотен раз.

Это не значит, что всегда нужно использовать STRAIGHT_JOIN. Но иногда, при обнаружении узких мест, он необходим.

Профилирование и оптимизация запросов.