Почему этот запрос вызывает блокировку ожидания ожидания ожидания?

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

mysql> explain 

SELECT categories.name AS cat_name, 
COUNT(distinct items.id) AS category_count 
FROM `items` 
INNER JOIN `categories` ON `categories`.`id` = `items`.`category_id` 
WHERE `items`.`state` IN ('listed', 'reserved') 
   AND (items.category_id IS NOT NULL) 
GROUP BY categories.name 
ORDER BY category_count DESC 
LIMIT 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: items
         type: range
possible_keys: index_items_on_category_id,index_items_on_state
          key: index_items_on_category_id
      key_len: 5
          ref: NULL
         rows: 119371
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: categories
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: production_db.items.category_id
         rows: 1
        Extra: 
2 rows in set (0.00 sec)

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

Почему этот запрос заставляет время ответа базы данных увеличиваться в десять раз и некоторые запросы, которые обычно занимают 40-50 мс (обновления в таблице элементов), взорваться до 50 000 мс и выше в разы?

Ответы

Ответ 1

Сложно сказать без дополнительной информации, например

  • Выполняется ли внутри транзакции?
  • Если да, то какой уровень изоляции?
  • Сколько существует категорий?
  • Сколько элементов?

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

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

1). Элементы items.state, вероятно, будут лучше, чем каталог, вместо того, чтобы иметь строку в каждой строке в элементах, это для экономии пространства, а сравнение идентификаторов - быстрее, чем сравнение строк (независимо от того, какая оптимизация может привести к делать).

2) Я предполагаю, что items.state - это столбец с низкой мощностью (несколько уникальных значений), поэтому индекс в этом столбце, вероятно, причиняет вам боль больше, чем помогает вам. Каждый индекс добавляет над головой при вставке/удалении/обновлении строк, так как индексы должны поддерживаться, этот конкретный индекс, вероятно, не используется, что очень важно. Конечно, я просто догадываюсь, это зависит от остальных запросов.

SELECT
    ; Grouping by name, means comparing strings. 
    categories.name AS cat_name, 
    ; No need for distinct, the same item.id cannot belong to different categories
    COUNT(distinct items.id) AS category_count  
FROM `items` 
INNER JOIN `categories` ON `categories`.`id` = `items`.`category_id` 
WHERE `items`.`state` IN ('listed', 'reserved') 
   ; Not needed, the inner join gets rid of items with no category_id
   AND (items.category_id IS NOT NULL) 
GROUP BY categories.name 
ORDER BY category_count DESC 
LIMIT 10\G

Способ структурирования этого запроса в основном состоит в том, чтобы сканировать всю таблицу элементов, поскольку она использует индекс category_id, затем фильтрует по предложению where, а затем соединяется с таблицей категорий, что означает, что индекс ищет первичный ключ ( category.id) индекс в строке позиции в наборе результатов. Затем группировка по имени (используя сравнение строк) для подсчета, а затем избавление от всего, кроме 10 результатов.

Я бы написал запрос как:

SELECT categories.name, counts.n
FROM (SELECT category_id, COUNT(id) n
      FROM items 
      WHERE state IN ('listed', 'reserved') AND category_id is not null
      GROUP BY category_id ORDER BY COUNT(id) DESC LIMIT 10) counts 
JOIN categories on counts.category_id = categories.id
ORDER BY counts.n desc

(Прошу прощения, если синтаксис не идеален, я не запускаю MySQL)

С помощью этого запроса, вероятно, будет работать движок:

Используйте индекс items.state, чтобы получить "перечисленные", "зарезервированные" элементы и группу по категориям_ид, сравнивая числа, а не строки, затем получая только 10 самых верхних счетчиков, затем присоединяйся к категориям, чтобы получить имя (но используя только 10 index стремится).