MySQL
Настройка производительности

Синтаксис

• Не используйте DISTINCT и GROUP BY в том же SELECT.

• Не разбивайте страницы с помощью OFFSET, «помните, где вы остановились».

• ГДЕ (a, b) = (22,33) не оптимизируется вообще.

• Явно говорю ALL или DISTINCT после UNION - это напоминает, что вы выбираете между быстрым ALL или медленным DISTINCT.

• Не используйте SELECT *, особенно если у вас есть столбцы TEXT или BLOB, которые вам не нужны. В tmp-таблицах и передаче есть накладные расходы.

• Это быстрее, когда GROUP BY и ORDER BY могут иметь точно такой же список.

• Не используйте FORCE INDEX; это может помочь сегодня, но, вероятно, завтра будет больно.

замечания

Добавьте правильный индекс

Это огромная тема, но это также самая важная проблема «производительности».

Основным уроком для новичков является изучение «составных» индексов. Вот краткий пример:

INDEX(last_name, first_name)

отлично подходит для них:

WHERE last_name = '...'
WHERE first_name = '...' AND last_name = '...'   -- (order in WHERE does not matter)

но не для

WHERE first_name = '...'   -- order in INDEX _does_ matter
WHERE last_name = '...' OR first_name = '...'   -- "OR" is a killer

Правильно установите кеш

innodb_buffer_pool_size должно быть около 70% доступной ОЗУ.

Избегайте неэффективных конструкций

x IN ( SELECT ... )

превратиться в JOIN

Когда это возможно, избегайте OR .

Не скрывайте индексированный столбец в функции, такой как WHERE DATE(x) = ... ; переформулировать как WHERE x = ...

Вы можете вообще избегать WHERE LCASE(name1) = LCASE(name2) , имея подходящую сортировку.

Не используйте OFFSET для «разбивки на страницы», вместо этого «помните, где вы остановились».

Избегайте SELECT * ... (кроме отладки).

Примечание для Марии Делевой, Барранка, Батсу: Это владелец места; пожалуйста, удалите эти элементы по мере создания полномасштабных примеров. После того, как вы сделаете то, что сможете, я перейду к описанию остальных и / или брошу их.

Отрицательных

Вот некоторые вещи, которые вряд ли помогут производительности. Они основаны на устаревшей информации и / или наивности.

• InnoDB улучшилась до такой степени, что MyISAM вряд ли будет лучше.
• PARTITIONing редко обеспечивает преимущества в производительности; это может даже повредить работе.
• Установка query_cache_size больше , чем 100M, как правило , больно производительности.
• Увеличение количества значений в my.cnf может привести к «замене», что является серьезной проблемой производительности.
• «Префиксные индексы» (например, INDEX(foo(20)) ), как правило, бесполезны.
• OPTIMIZE TABLE почти всегда бесполезен. (И это связано с блокировкой таблицы.)

Имейте ИНДЕКС

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

WHERE a = 12  --> INDEX(a)
WHERE a > 12  --> INDEX(a)

WHERE a = 12 AND b > 78  --> INDEX(a,b) is more useful than INDEX(b,a)
WHERE a > 12 AND b > 78  --> INDEX(a) or INDEX(b); no way to handle both ranges

ORDER BY x  --> INDEX(x)
ORDER BY x, y  --> INDEX(x,y) in that order
ORDER BY x DESC, y ASC  --> No index helps - because of mixing ASC and DESC

Не скрывать функцию

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

WHERE DATE(dt) = '2000-01-01'

Вместо этого, учитывая INDEX(dt) они могут использовать индекс:

WHERE dt = '2000-01-01'  -- if `dt` is datatype `DATE`

Это работает для DATE , DATETIME , TIMESTAMP и даже DATETIME(6) (микросекунды):

WHERE dt >= '2000-01-01'
  AND dt  < '2000-01-01' + INTERVAL 1 DAY

ИЛИ ЖЕ

В общем, OR убивает оптимизацию.

WHERE a = 12 OR b = 78

не может использовать INDEX(a,b) и может или не может использовать INDEX(a), INDEX(b) посредством «слияния индекса». Слияние индекса лучше, чем ничего, но только едва.

WHERE x = 3 OR x = 5

превращается в

WHERE x IN (3, 5)

который может использовать индекс с x в нем.

подзапросов

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

Этот коррелированный подзапрос часто очень хорош. Примечание: оно должно возвращать не более 1 значения. Это часто полезно в качестве альтернативы, хотя и не обязательно быстрее, чем LEFT JOIN .

SELECT a, b, ( SELECT ... FROM t WHERE t.x = u.x ) AS c
    FROM u ...
SELECT a, b, ( SELECT MAX(x) ... ) AS c
    FROM u ...
SELECT a, b, ( SELECT x FROM t ORDER BY ... LIMIT 1 ) AS c
    FROM u ...

Обычно это некоррелировано:

SELECT ...
    FROM ( SELECT ... ) AS a
    JOIN b ON ...

Примечания к FROM-SELECT :

• Если он возвращает 1 строку, отлично.
• Хорошая парадигма (опять же «1 строка») для подзапроса ( SELECT @n := 0 ) , тем самым инициализируя `@variable для использования в остальном или запросе.
• Если он возвращает много строк, а JOIN также ( SELECT ... ) со многими строками, то эффективность может быть ужасной. Pre-5.6, не было индекса, поэтому он стал CROSS JOIN ; 5.6+ включает в себя вывод лучшего индекса в временные таблицы, а затем его создание, только для его выброса, когда он заканчивается с помощью SELECT .

ПРИСОЕДИНЯЙТЕСЬ + ГРУППА

Общая проблема, которая приводит к неэффективному запросу, выглядит примерно так:

SELECT ...
    FROM a
    JOIN b  ON ...
    WHERE ...
    GROUP BY a.id

Во-первых, JOIN расширяет количество строк; то GROUP BY уменьшает количество строк в a .

Возможно, не будет никаких хороших вариантов для решения этой проблемы с взрывом-взломом. Один из возможных вариантов - превратить JOIN в коррелированный подзапрос в SELECT . Это также исключает GROUP BY .