Оптимизация запроса для поиска случайной выборки

У меня есть SQL-запрос для случайного выбора 1200 лучших ретвитированных твитов, по крайней мере, в 50 раз ретвитнутых, и tweetDate должен быть на 4 дня старше из 40 миллионов записей. Запрос, который я вставил ниже, работает, но он занимает 40 минут, так есть ли более быстрая версия этого запроса?

SELECT 
    originalTweetId, Count(*) as total, tweetContent, tweetDate
FROM
    twitter_gokhan2.tweetentities
WHERE
    originalTweetId IS NOT NULL
        AND originalTweetId <> - 1
        AND isRetweet = true
    AND (tweetDate < DATE_ADD(CURDATE(), INTERVAL - 4 DAY))
GROUP BY originalTweetId
HAVING total > 50
ORDER BY RAND()
limit 0 , 1200;


---------------------------------------------------------------

Table creation sql is like:

    CREATE TABLE `tweetentities` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `tweetId` bigint(20) NOT NULL,
      `tweetContent` varchar(360) DEFAULT NULL,
      `tweetDate` datetime DEFAULT NULL,
      `userId` bigint(20) DEFAULT NULL,
      `userName` varchar(100) DEFAULT NULL,
      `retweetCount` int(11) DEFAULT '0',
      `keyword` varchar(500) DEFAULT NULL,
      `isRetweet` bit(1) DEFAULT b'0',
      `isCompleted` bit(1) DEFAULT b'0',
      `applicationId` int(11) DEFAULT NULL,
      `latitudeData` double DEFAULT NULL,
      `longitude` double DEFAULT NULL,
      `originalTweetId` bigint(20) DEFAULT NULL,
     PRIMARY KEY (`id`),
  KEY `index` (`originalTweetId`),
  KEY `index3` (`applicationId`),
  KEY `index2` (`tweetId`),
  KEY `index4` (`userId`),
  KEY `index5` (`userName`),
  KEY `index6` (`isRetweet`),
  KEY `index7` (`tweetDate`),
  KEY `index8` (`originalTweetId`),
  KEY `index9` (`isCompleted`),
  KEY `index10` (`tweetContent`(191))
) ENGINE=InnoDB AUTO_INCREMENT=41501628 DEFAULT CHARSET=utf8mb4$$
Источник

4 ответа

Решение

Вы, конечно, суммируете огромное количество записей, затем рандомизируете их. Подобные вещи сложно сделать быстро. Возвращаясь к началу времени, становится еще хуже. Поиск по нулевому условию просто уничтожает его.

Если вы хотите, чтобы это работало разумно, вы должны избавиться от IS NOT NULL выбор. Иначе это будет плохо работать.

Но давайте попробуем найти разумное решение. Во-первых, давайте originalTweetId ценности нам нужны.

 SELECT MIN(id) originalId,
        MIN(tweetDate) tweetDate,
        originalTweetId, 
        Count(*) as total
   FROM twitter_gokhan2.tweetentities
  WHERE originalTweetId <> -1 
  /*AND originalTweetId IS NOT NULL We have to leave this out for perf reasons */
    AND isRetweet = true
    AND tweetDate < CURDATE() - INTERVAL 4 DAY
    AND tweetDate > CURDATE() - INTERVAL 30 DAY  /*let's add this, if we can*/
  GROUP BY originalTweetId
 HAVING total >= 50

Этот сводный запрос дает нам наименьший номер идентификатора и дату в вашей базе данных для каждого тематического твита.

Чтобы это работало быстро, нам нужен составной индекс (originalTweetId, isRetweet, tweetDate, id). Запрос выполнит сканирование диапазона этого индекса по tweetDate, что примерно так быстро, как вы можете надеяться. Отладьте этот запрос, как на корректность, так и на производительность, затем продолжайте.

Теперь сделайте рандомизацию. Давайте сделаем это с минимальным количеством данных, которое мы можем, чтобы избежать сортировки огромного количества материала.

 SELECT originalTweetId, tweetDate, total, RAND() AS randomOrder
   FROM (
    SELECT MIN(id) originalId,
           MIN(tweetDate) tweetDate
           originalTweetId, 
           Count(*) as total
      FROM twitter_gokhan2.tweetentities
     WHERE originalTweetId <> -1 
     /*AND originalTweetId IS NOT NULL We have to leave this out for perf reasons */
       AND isRetweet = true
       AND tweetDate < CURDATE() - INTERVAL 4 DAY
       AND tweetDate > CURDATE() - INTERVAL 30 DAY  /*let's add this, if we can*/
      GROUP BY originalTweetId
     HAVING total >= 50
   ) AS retweets
  ORDER BY randomOrder
  LIMIT 1200

Отлично. Теперь у нас есть список из 1200 идентификаторов и дат твитов в случайном порядке. Теперь поехали получать контент.

 SELECT a.originalTweetId, a.total, b.tweetContent, a.tweetDate
   FROM (
          /* that whole query above */
        ) AS a
   JOIN twitter_gokhan2.tweetentities AS b ON (a.id = b.id)
  ORDER BY a.randomOrder

Видишь, как это происходит? Используйте составной индекс для составления вашего резюме и сделайте это на минимальном объеме данных. Затем сделайте рандомизацию, а затем принесите дополнительные данные, которые вам нужны.

Источник

Просто чтобы уточнить... Вы действительно хотели запросить все старше 4 дней???

 AND (tweetDate < DATE_ADD(CURDATE(), INTERVAL - 4 DAY))

ИЛИ... Вы имели в виду, что хотите объединить только последние твиты за последние 4 дня. Для меня твиты, которые произошли 2 года назад, были бы бесполезны для текущих событий... Если бы это было так, вы могли бы лучше просто перейти на

 AND (tweetDate >= DATE_ADD(CURDATE(), INTERVAL - 4 DAY))
Источник

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

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

Вы можете сделать предположение, что если это ретвит, то originalTweetId не может быть нулевым /-1

Источник

Посмотрите, не быстрее ли это, чем 40 минут:

Сначала протестируйте без закомментированных строк, затем добавьте их снова, чтобы сравнить влияние на производительность. (особенно ORDER BY RAND() как известно, ужасно)

SELECT
    originalTweetId,
    total,
--    tweetContent,  -- may slow things somewhat
    tweetDate
FROM (
  SELECT
    originalTweetId,
    COUNT(*) AS total,
--    tweetContent,  -- may slow things somewhat
    MIN(tweetDate) AS tweetDate,
    MAX(isRetweet) AS isRetweet
  FROM twitter_gokhan2.tweetentities
  GROUP BY originalTweetId
) AS t
WHERE originalTweetId > 0
  AND isRetweet
  AND tweetDate < DATE_ADD(CURDATE(), INTERVAL - 4 DAY)
  AND total > 50
-- ORDER BY RAND()  -- very likely to slow performance,
                    -- test with and without...
LIMIT 0, 1200;

PS - originalTweetId должен быть проиндексирован с надеждой

Источник
Другие вопросы по тегам