И избегайте неприятных сюрпризов производительности в продакшене

Недавно мой коллега из Nightlife Music показал мне (рецензенту кода) и другому коллеге (составителю кода) два способа оптимизации нашего SQL-запроса. Это упражнение заставило меня добавить ensure all queries are explained check в наш контрольный список проверки кода.

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

Запрос

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

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

Произошло это примерно так:

SELECT * FROM myDB.pizzas pizzas
JOIN myDB.pastas pastas USING (pizza_code)
JOIN myOtherDB.pasta_recipes pasta_recipes
ON pasta_recipes.sauce = pastas.sauce
WHERE pizzas.pizza_id = my_pizza_id
AND pasta_recipes.key = 'PIZZA NAME'

Кажется достаточно простым, правда? Где мы ошиблись? Ну, таблицы pizzas и pastas обе закодированы в latin1, а pasta_recipes закодированы в utf8. Как уже упоминалось, поскольку мы объединяем pastas и pasta_recipes, здесь нельзя использовать индекс.

ОБЪЯСНЕНИЕ на помощь

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

Как мы можем определить, будет ли наш запрос использовать индекс и сколько строк будет извлечено? Используя ключевое слово EXPLAIN.

Запустим его в БД:

EXPLAIN
SELECT * FROM myDB.pizzas pizzas      // latin1 table
JOIN myDB.pastas pastas USING (pizza_code)   // another latin1 table
JOIN myOtherDB.pasta_recipes pasta_recipes     // utf8 table
ON pasta_recipes.sauce = pastas.sauce
WHERE pizzas.pizza_id = my_pizza_id
AND pasta_recipes.key = 'PIZZA NAME'

Как видите, мы просмотрели более 5 миллионов строк, чтобы найти те, которые соответствуют нашим JOIN критериям. Индекс не использовался для соединения между pastas и pasta_recipes.

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

EXPLAIN
SELECT * FROM myDB.pizzas pizzas
JOIN myDB.pastas pastas USING (pizza_code)
JOIN myOtherDB.pasta_recipes pasta_recipes
ON pasta_recipes.sauce = CONVERT(pastas.sauce USING ASCII)
WHERE pizzas.pizza_id = my_pizza_id
AND pasta_recipes.key = 'PIZZA NAME'

Хорошо, теперь мы к чему-то приближаемся! И позвольте мне сказать вам, что запрос выполняется намного быстрее. Но постойте, разве мы не ожидали только одну строку? Сейчас мы используем индекс для соединения между pasta_recipes и pastas в столбце sauce, но похоже, что мы сканируем строки, чтобы найти те, где key совпадает с pizza_name. Итак, когда мы проверяем наши индексы в нашей pasta_recipes таблице, оказывается, что в столбцахkey, sauce and type есть composite index. Следовательно, нам нужно включить type в предложение WHERE, чтобы действительно оптимизировать наш запрос:

EXPLAIN
SELECT * FROM myDB.pizzas pizzas
JOIN myDB.pastas pastas USING (pizza_code)
JOIN myOtherDB.pasta_recipes pasta_recipes
ON pasta_recipes.sauce = CONVERT(pastas.sauce USING ASCII)
WHERE pizzas.pizza_id = my_pizza_id
AND pasta_recipes.key = 'PIZZA NAME'
AND pasta_recipes.type = 'tomato'

И результаты:

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

Если этого не сделать, это может серьезно навредить вам (и вашей базе данных!)

В Nightlife у нас будет примерно 6000 таких запросов, выполняемых около 6 утра каждое утро, наряду с десятками тысяч других запросов (как на чтение, так и на запись), поэтому очень важно, чтобы мы настроили их как можно лучше. Используя ключевое слово EXPLAIN для SQL-запросов вместе с их аналогами из других баз данных, таких как MongoDB и Neo4j (у большинства баз данных должен быть эквивалент), мы можем, по крайней мере, найти, когда что-то идет не так, даже если мы не уверены, как исправить их. Если мы не знаем, мы всегда можем попросить наших верных коллег объяснить, что не так.

Итак, если вы еще этого не сделали, убедитесь, что ваша команда использует объяснение по всем своим запросам, и посмотрите на результаты сами, прежде чем переходить к производству!

Спасибо за прочтение.

Ресурсы

  1. Ночная музыка
  2. MySQL EXPLAIN
  3. MongoDB объяснить ()
  4. Neo4j ОБЪЯСНИТЬ