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

Я действительно пытаюсь получить время запроса, в настоящее время он должен запросить 2,5 миллиона строк, и он занимает более 20 секунд.

вот запрос

SELECT play_date AS date, COUNT(DISTINCT(email)) AS count
FROM log
WHERE play_date BETWEEN '2009-02-23' AND '2020-01-01'
AND type = 'play'
GROUP BY play_date
ORDER BY play_date desc;

 `id` int(11) NOT NULL auto_increment,
  `instance` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `type` enum('play','claim','friend','email') NOT NULL,
  `result` enum('win','win-small','lose','none') NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `play_date` date NOT NULL,
  `email_refer` varchar(255) NOT NULL,
  `remote_addr` varchar(15) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `email` (`email`),
  KEY `result` (`result`),
  KEY `timestamp` (`timestamp`),
  KEY `email_refer` (`email_refer`),
  KEY `type_2` (`type`,`timestamp`),
  KEY `type_4` (`type`,`play_date`),
  KEY `type_result` (`type`,`play_date`,`result`)

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  log ref type_2,type_4,type_result   type_4  1   const   270404  Using where

Запрос использует индекс type_4.

Кто-нибудь знает, как я могу ускорить этот запрос?

Спасибо Том

Ответ 1

Это относительно хорошо, уже. Приемник производительности состоит в том, что запрос должен сравнивать 270404 varchars для равенства для COUNT(DISTINCT(email)), а это значит, что нужно читать 270404 строк.

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

Чтобы сделать это, измените индекс следующим образом:

KEY `type_4` (`type`,`play_date`, `email`)

Я был бы удивлен, если бы это не ускорило ситуацию совсем немного.

(Спасибо MarkR за правильный термин.)

Ответ 2

Ваша индексация, вероятно, так же хороша, как вы можете ее получить. У вас есть составной индекс в 2 столбцах в вашем предложении where, а explain, который вы опубликовали, указывает, что он используется. К сожалению, есть 270 404 строки, которые соответствуют критериям в вашем предложении where, и все они должны быть рассмотрены. Кроме того, вы не возвращаете ненужные строки в список select.

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

Ответ 3

Попробуйте указатель на play_date, введите (такие же, как type_4, только обратные поля) и посмотрите, помогает ли это

Существует 4 возможных типа, и я предполагаю 100 возможных дат. Если запрос использует тип, индекс play_date, он в основном (а не 100% точная, но общая идея) говорит.

(A) Find all the Play records (about 25% of the file)
(B) Now within that subset, find all of the requested dates

Отменив индекс, подход

> (A) Find all the dates within range
> (Maybe 1-2% of file) (B) Now find all
> PLAY types within that smaller portion
> of the file

Надеюсь, что это поможет

Ответ 4

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

Ответ 5

Часть COUNT(DISTINCT(email)) - это бит, который убивает вас. Если вам действительно понадобятся первые 2000 результатов из 270 404, возможно, это поможет сделать счет электронной почты только для результатов, а не для всего набора.

SELECT date, COUNT(DISTINCT(email)) AS count
FROM log,
(
    SELECT play_date AS date
      FROM log
     WHERE play_date BETWEEN '2009-02-23' AND '2020-01-01'
       AND type = 'play'
     ORDER BY play_date desc
     LIMIT 2000
) AS shortlist
WHERE shortlist.id = log.id
GROUP BY date

Ответ 6

Попробуйте создать индекс только для play_date.

Ответ 7

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

В зависимости от того, насколько актуально вам это нужно - либо ежедневно обновляйте его (по play_date), либо живите через триггер в таблице журналов.

Ответ 8

Существует хорошая вероятность, что сканирование таблицы будет быстрее, чем случайный доступ к более чем 200 000 строк:

SELECT ... FROM log IGNORE INDEX (type_2,type_4,type_result) ...

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

SELECT SQL_BIG_RESULT ...