MySQL Simple Query Optimization
У меня есть следующий запрос:
SELECT
b.item_name,
COUNT(distinct c.user_id) AS total_count,
AVG(c.item_rating) AS avg_rating
FROM item_ratings as c
INNER JOIN items AS b ON b.item_id = c.item_id
INNER JOIN users AS u ON u.user_id = c.user_id
WHERE item_active = 1 AND u.user_valid = 1
GROUP BY c.item_id
Этот запрос выполняется в течение 500 секунд для высокооптимизированной базы данных - не уверен, что происходит.
индексирование
item_ratings - item_user_id, (item_id, user_id), item_rating, item_id
users - user_id, user_valid
items - item_id (primary), item_search (item_id, item_name), item_r (parent_id, item_id, item_active)
Размер стола
Таблица item_ratings близка к 5 миллионам записей, а таблица элементов - около 200 тыс., А пользователи - около 250 тыс.
объяснять
Похоже, что запрос объяснения выполняет сортировку таблицы по элементам (возвращая все 200 тыс. Строк), даже несмотря на наличие индекса item_active. Другие таблицы (item_ratings и user) используют правильный индекс.
ОБНОВЛЕНИЕ
ПОЛНОЕ ОБЪЯСНИТЬ
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b ALL PRIMARY,item_id, item_search, item_r NULL NULL NULL 218419 Using where; Using temporary; Using filesort
1 SIMPLE c ref item_user_id ,user_id, item_id 4 myDB.b.item_id 29 Using where
1 SIMPLE u eq_ref PRIMARY,user_valid,user_id PRIMARY 4 myDB.c.user_id 1 Using where
Аппаратное обеспечение Это выделенный сервер MySQL, работающий под управлением Ubuntu 10.10 с 16 ГБ ОЗУ. Таблицы работают под управлением MyISAM.
Какие-либо предложения?
2 ответа
Ты прав. Этот запрос не должен занимать восемь минут. Одна возможность состоит в том, что индексы фактически ухудшают запрос, потому что запрос хочет выполнить полное сканирование таблицы. Прежде чем обойти их, я предлагаю следующее:
Предположительно, таблица пользователей и элементов имеет различный идентификатор. Кроме того, предположительно, у пользователя есть только один рейтинг для данного элемента. Если это так, вы можете удалить счетчик и заменить его счетчиком:
SELECT b.item_name, COUNT(c.user_id) AS total_count, AVG(c.item_rating) AS avg_rating
FROM item_ratings as c INNER JOIN
items AS b
ON b.item_id = c.item_id INNER JOIN
users AS u
ON u.user_id = c.user_id
WHERE item_active = 1 AND u.user_valid = 1
GROUP BY c.item_id
Во-вторых, нет индекса "is_active". Индекс включен (parent_id, item_id, item_active). Ваш запрос не использует parent_id, поэтому этот индекс не будет использоваться.
В-третьих, похоже, что он проходит через индекс предметов из-за агрегации. Поскольку вы, кажется, хотите item_name вместо item_id, я бы предложил изменить группу на:
group by c.item_name
Это может позволить ему создать лучший план запроса.
Даже с индексом в поле item_active, запрос все еще был ОЧЕНЬ медленным. Поскольку этот запрос выполняется только один раз в день, я нашел другое решение, которое, надеюсь, можно будет использовать для других пользователей.
Я просто вытащил список активного пива только с помощью этого запроса:
SELECT b.beer_name
FROM items as b
WHERE b.item_active = 1
Затем в каждой строке я просматривал и получал рейтинг и средний рейтинг для каждого активного элемента, например:
SELECT COUNT(DISTINCT c.user_id) AS total_count, AVG(c.item_rating) AS avg_rating
FROM item_ratings as c
INNER JOIN users AS u ON u.user_id = c.user_id
WHERE item_active = 1 AND u.user_valid = 1 and b.item_id = @item_id
Где @item_id - это item_id из цикла PHP, который я написал. После этого я беру результаты этого и помещаю это в стол для запросов. Это решение прекрасно работает для меня, потому что выполнение этих небольших запросов занимает менее секунды и может быть выполнено в пакетном формате в нерабочее время без блокировки других таблиц.
Спасибо всем за их предложения и помощь!