Как ускорить запрос при использовании счетчика и группировать по
У меня есть две таблицы с именем продавец и товар. Они связаны через третью таблицу (seller_item), используя отношение внешнего ключа от "n" к "m".
Теперь я попытаюсь ответить на требование: "Мне, как продавцу, нужен список моих конкурентов с указанием количества товаров, которые я продаю, и они продают также". Итак, список всех продавцов с количеством перекрывающихся товаров по отношению к одному конкретному продавцу. Также я хочу, чтобы это было отсортировано по количеству и ограничено. Но запрос использует временную таблицу и сортировку файлов, что очень медленно. Объясните, говорит:
Используя где; Использование индекса; Используя временные; Использование сортировки файлов
Как я могу ускорить это?
Вот запрос:
SELECT
COUNT(*) AS itemCount,
s.sellerName
FROM
seller s,
seller_item si
WHERE
si.itemId IN
(SELECT itemId FROM seller_item WHERE sellerId = 4711)
AND
si.sellerId=s.id
GROUP BY
sellerName
ORDER BY
itemCount DESC
LIMIT 50;
таблица определений:
CREATE TABLE `seller` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sellerName` varchar(50) NOT NULL
PRIMARY KEY (`id`),
UNIQUE KEY `unique_index` (`sellerName`),
) ENGINE=InnoDB
contains about 200.000 rows
-
CREATE TABLE `item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`itemName` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_index` (`itemName`),
) ENGINE=InnoDB
contains about 100.000.000 rows
-
CREATE TABLE `seller_item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sellerId` bigint(20) unsigned NOT NULL,
`itemId` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sellerId` (`sellerId`,`itemId`),
KEY `item_id` (`itemId`),
CONSTRAINT `fk_1` FOREIGN KEY (`sellerId`) REFERENCES `seller` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `fk_2` FOREIGN KEY (`itemId`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB
contains about 170.000.000 rows
База данных Mysql Percona 5.6
Вывод EXPLAIN:
+----+-------------+-------------+--------+----------------------+----- ---------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+----------------------+--------------+---------+---------------------+------+----------------------------------------------+
| 1 | SIMPLE | s | index | PRIMARY,unique_index | unique_index | 152 | NULL | 1 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | si | ref | sellerId,item_id | sellerId | 8 | tmp.s.id | 1 | Using index |
| 1 | SIMPLE | seller_item | eq_ref | sellerId,item_id | sellerId | 16 | const,tmp.si.itemId | 1 | Using where; Using index |
+----+-------------+-------------+--------+----------------------+--------------+---------+---------------------+------+----------------------------------------------+
5 ответов
Я сомневаюсь, что такой запрос может быть выполнен быстро в режиме реального времени в базе данных вашего размера, особенно для продавцов с большим количеством популярных товаров на складе.
Вы должны это материализовать. Создать такую таблицу
CREATE TABLE
matches
(
seller INT NOT NULL,
competitor INT NOT NULL,
matches INT NOT NULL,
PRIMARY KEY
(seller, competitor)
)
и обновите его партиями в cron-скрипте:
DELETE
FROM matches
WHERE seller = :seller
INSERT
INTO matches (seller, competitor, matches)
SELECT si.seller, sc.seller, COUNT(*) cnt
FROM seller_item si
JOIN seller_item sc
ON sc.item = si.item
AND sc.seller <> si.seller
WHERE si.seller = :seller
GROUP BY
si.seller, sc.seller
ORDER BY
cnt DESC
LIMIT 50
Вы также должны сделать (seller, item) PRIMARY KEY на seller_item, В настоящее время для поиска продавца по элементу требуется два поиска вместо одного: первый идентификатор по элементу с использованием KEY (item)затем продавец по id используя PRIMARY KEY (id)
Я полагаю, что у вас сложилось неправильное представление о вашей способности устранить временное использование; Использование шагов сортировки файлов для удовлетворения вашего запроса. Запросы формы
SELECT COUNT(*), grouping_value
FROM table
GROUP BY grouping_value
ORDER BY COUNT(*)
LIMIT n
всегда используйте временный набор результатов в памяти и всегда сортируйте этот набор результатов. Это связано с тем, что результирующий набор нигде не существует до тех пор, пока не выполнится запрос, и он должен быть отсортирован до того, как будет выполнено предложение LIMIT.
"Файловая сортировка" несколько неправильно названа. Это не обязательно означает, что происходит сортировка файла в файловой системе, просто сортируется временный набор результатов. Если этот набор результатов массивный, сортировка может вылиться из ОЗУ в файловую систему, но это не обязательно. Пожалуйста прочтите это. https://www.percona.com/blog/2009/03/05/what-does-using-filesort-mean-in-mysql/ Не отвлекайтесь на Using filesort предмет в вашем EXPLAIN Результаты.
Одним из приемов получения большей производительности от такого рода запросов является минимизация размера отсортированных результатов. Вы уже отфильтровали их до того, что хотите; это хорошо.
Но вы все равно можете организовать сортировку меньшего количества материала, сортируя только seller.id и количество, а затем присоединение к (более длинному) sellerName после того, как вы знаете точные пятьдесят строк, которые вам нужны. Это также имеет то преимущество, что вы можете агрегировать только с таблицей seller_item, а не с набором результатов, который получается при объединении этих двух.
Вот что я имею в виду. Этот подзапрос генерирует список из пятидесяти sellerId ценности, которые вам нужны. Все, что нужно отсортировать, это количество и sellerId, Это быстрее, чем сортировка и подсчет sellerName потому что там меньше данных и данных фиксированной длины, чтобы перемешать в операции сортировки.
SELECT COUNT(*) AS itemCount,
sellerId
FROM seller_item
WHERE itemId IN
(SELECT itemId FROM seller_item WHERE sellerId = 4711)
GROUP BY SellerId
ORDER BY COUNT(*) DESC
LIMIT 50
Обратите внимание, что это сортирует большой набор результатов, а затем отбрасывает большую его часть. Это дает вам точные пятьдесят идентификаторов продавца, которые вам нужны.
Вы можете сделать это еще быстрее, отфильтровывая больше строк, добавляя HAVING COUNT(*) > 1 сразу после вашего GROUP BY пункт, но это меняет смысл вашего запроса и может не соответствовать вашим бизнес-требованиям.
Когда у вас есть эти пятьдесят предметов, вы можете получить имена продавцов. Весь запрос выглядит так:
SELECT s.sellerName, c.itemCount
FROM seller s
JOIN (
SELECT COUNT(*) AS itemCount, sellerId
FROM seller_item
WHERE itemId IN
(SELECT itemId FROM seller_item WHERE sellerId = 4711)
GROUP BY SellerId
ORDER BY COUNT(*) DESC
LIMIT 50
) c ON c.sellerId = s.id
ORDER BY c.itemCount DESC
Ваши усилия по индексированию должны быть потрачены на быстрые внутренние запросы. Внешний запрос будет быстрым, несмотря ни на что; он обрабатывает только пятьдесят строк и использует индексированный id значение для поиска других значений.
Внутренний запрос SELECT itemId FROM seller_item WHERE sellerId = 4711, Это значительно выиграет от вашего существующего (sellerId, itemId) составной индекс: он может выполнять произвольный доступ, а затем сканировать этот индекс, что очень быстро.
SELECT COUNT(*)... запрос выиграет от (itemId, sellerId) составной индекс. Эта часть вашего запроса является сложной и медленной, но все же этот индекс поможет.
Посмотрите, другие упоминали об этом, и я тоже. Имея оба уникальных составных ключа (sellerId, itemId) и первичный ключ id на что seller_item Стол, с уважением, невероятно расточительный.
- Это делает ваши обновления и вставляет медленнее.
- Это означает, что ваша таблица организована в виде дерева на основе бессмысленного
idа не значащая пара значений.
Если вы сделаете один из двух индексов, которые я упомянул первичным ключом, и создадите другой, не делая его уникальным, у вас будет гораздо более эффективная таблица. Эти таблицы объединения многие-ко-многим не нуждаются и не должны иметь суррогатных ключей.
переформулирование
Я думаю, что это то, что вы действительно хотели:
SELECT si2.sellerId, COUNT(DISTINCT si2.itemId) AS itemCount
FROM seller_item si1
JOIN seller_item si2 ON si2.itemId = si1.itemId
WHERE si1.sellerId = 4711
GROUP BY si2.sellerId
ORDER BY itemCount DESC
LIMIT 50;
(Заметка: DISTINCT вероятно не нужно.)
В словах: для продавца № 4711 найдите товары, которые он продает, а затем найдите, какие продавцы продают почти такой же набор товаров. (Я не пытался отфильтровать #4711 из набора результатов.)
Более эффективный N:M
Но все еще есть неэффективность. Давайте разберем вашу таблицу сопоставления "многие ко многим" (seller_item).
- Имеет
idкоторый, вероятно, не используется ни для чего. Избавиться от этого. - Тогда продвигайте
UNIQUE(sellerId, itemId)вPRIMARY KEY(sellerId, itemId), - Теперь поменяй
INDEX(itemId)вINDEX(itemId, sellerId)так что последним этапом запроса может быть "использование индекса".
У вас очень большой набор данных; Вы отладили свое приложение. Рассмотрите возможность удаления FOREIGN KEYs; они несколько дорогостоящие.
Получение sellerName
Это может быть возможно JOIN в sellers получить sellerName, Но попробуйте это просто sellerId первый. Затем добавьте имя. Убедитесь, что число не увеличивается (что часто происходит) и что запрос не замедляется.
Если что-то пойдет не так, то сделайте
SELECT s.sellerName, x.itemCount
FROM ( .. the above query .. ) AS x
JOIN sellers AS s USING(sellerId);
(При желании вы можете добавить ORDER BY sellerName.)
Поскольку мы ограничиваем (потенциально большой) набор результатов максимум 50 строками, я бы откладывал получение имени продавца до тех пор, пока у нас не будет счетчиков, поэтому нам нужно получить только 50 имен продавцов.
Во-первых, мы получаем itemcount по seller_id
SELECT so.seller_id
, COUNT(*) AS itemcount
FROM seller_item si
JOIN seller_item so
ON so.item_id = si.item_id
WHERE si.seller_id = 4711
GROUP BY so.seller_id
ORDER BY COUNT(*) DESC, so.seller_id DESC
LIMIT 50
Для повышения производительности я бы сделал подходящий индекс покрытия доступным для объединения с so, например
CREATE UNIQUE INDEX seller_item_UX2 ON seller_item(item_id,seller_id)
Используя "покрывающий индекс", MySQL может полностью удовлетворить запрос со страниц индекса, без необходимости посещать страницы в базовой таблице.
После создания нового индекса я бы отбросил индекс в столбце singleton item_id, так как этот индекс теперь избыточен. (Любой запрос, который мог бы эффективно использовать этот индекс, сможет эффективно использовать составной индекс, который имеет item_id в качестве ведущей колонки.)
Там нет обойти операцию "Использование файловой сортировки". MySQL должен оценить агрегат COUNT() в каждой строке, прежде чем он сможет выполнить сортировку. Нет никакого способа (учитывая текущую схему) для MySQL, чтобы возвратить строки в порядке, используя индекс, чтобы избежать операции сортировки.
Как только мы получим этот набор (не более) пятидесяти строк, мы сможем получить имя продавца.
Чтобы получить имя продавца, мы могли бы использовать коррелированный подзапрос в списке SELECT или операцию соединения.
1) Использование коррелированного подзапроса в списке SELECT, например
SELECT so.seller_id
, ( SELECT s.sellername
FROM seller s
WHERE s.seller_id = so.seller_id
ORDER BY s.seller_id, s.sellername
LIMIT 1
) AS sellername
, COUNT(*) AS itemcount
FROM seller_item si
JOIN seller_item so
ON so.item_id = si.item_id
WHERE si.seller_id = 4711
GROUP BY so.seller_id
ORDER BY COUNT(*) DESC, so.seller_id DESC
LIMIT 50
(Мы знаем, что подзапрос будет выполнен (максимум) пятьдесят раз, по одному разу для каждой строки, возвращаемой внешним запросом. Пятьдесят выполнений (с подходящим доступным индексом) не так уж плохи, по крайней мере, по сравнению с 50000 выполнениями.)
Или 2) используя операцию соединения, например
SELECT c.seller_id
, s.sellername
, c.itemcount
FROM (
SELECT so.seller_id
, COUNT(*) AS itemcount
FROM seller_item si
JOIN seller_item so
ON so.item_id = si.item_id
WHERE si.seller_id = 4711
GROUP BY so.seller_id
ORDER BY COUNT(*) DESC, so.seller_id DESC
LIMIT 50
) c
JOIN seller s
ON s.seller_id = c.seller_id
ORDER BY c.itemcount DESC, c.seller_id DESC
(Опять же, мы знаем встроенный вид c вернет (максимум) пятьдесят строк, и получение пятидесяти названий продавцов (с использованием подходящего индекса) должно быть быстрым.
ТАБЛИЦА РЕЗУЛЬТАТОВ
Если мы денормализуем реализацию и добавим сводную таблицу, содержащую item_id (в качестве первичного ключа) и "количество" количества продавцов этого item_id, наш запрос может воспользоваться этим.
В качестве иллюстрации того, как это может выглядеть:
CREATE TABLE item_seller_count
( item_id BIGINT NOT NULL PRIMARY KEY
, seller_count BIGINT NOT NULL
) Engine=InnoDB
;
INSERT INTO item_seller_count (item_id, seller_count)
SELECT d.item_id
, COUNT(*)
FROM seller_item d
GROUP BY d.item_id
ORDER BY d.item_id
;
CREATE UNIQUE INDEX item_seller_count_IX1
ON item_seller_count (seller_count, item_id)
;
Новая сводная таблица станет "не синхронизированной", когда строки будут добавлены / обновлены / удалены из таблицы seller_item.
И заполнение этой таблицы потребует ресурсов. Но доступность этого ускорит запросы того типа, над которым мы работаем.
Я не уверен, насколько быстро это будет в вашей базе данных, но я бы написал запрос следующим образом.
select * from (
select seller.sellerName,
count(otherSellersItems.itemId) itemCount from (
select sellerId, itemId from seller_item where sellerId != 4711
) otherSellersItems
inner join (
select itemId from seller_item where sellerId = 4711
) thisSellersItems
on otherSellersItems.itemId = thisSellersItems.itemId
inner join seller
on otherSellersItems.sellerId = seller.id
group by seller.sellerName
) itemsSoldByOtherSellers
order by itemCount desc
limit 50 ;