#mysql #performance #indexing
#mysql #Производительность #индексирование
Вопрос:
Извините, я не мог быть более конкретным в названии.
Итак, я получил этот запрос:
CREATE TABLE RecordPoints AS (
SELECT competitionId, personId, personCountryId, eventId, year, date,
if(regionalAverageRecord = 'WR',
(SELECT COUNT(DISTINCT personId) FROM ResultDates rd
WHERE rd.eventId=rd2.eventId AND rd.date <= rd2.date AND rd.average > 0), 0) wrAveragePoints,
if(regionalSingleRecord = 'WR',
(SELECT COUNT(DISTINCT personId) FROM ResultDates rd
WHERE rd.eventId=rd2.eventId AND rd.date <= rd2.date), 0) wrSinglePoints,
if(NOT regionalAverageRecord in('WR', 'NR'),
(SELECT COUNT(DISTINCT personId) FROM ResultDates rd
WHERE rd.eventId=rd2.eventId AND rd.date <= rd2.date AND average > 0 AND rd.personCountryId in
(SELECT Countries.id FROM Countries JOIN Continents on Countries.continentId=Continents.id where recordName = rd2.regionalAverageRecord)), 0) crAveragePoints,
if(NOT regionalAverageRecord in('WR', 'NR'),
(SELECT COUNT(DISTINCT personId) FROM ResultDates rd
WHERE rd.eventId=rd2.eventId AND rd.date <= rd2.date AND rd.personCountryId in
(SELECT Countries.id FROM Countries JOIN Continents on Countries.continentId=Continents.id where recordName = rd2.regionalSingleRecord)), 0) crSinglePoints,
if(regionalAverageRecord = 'NR',
(SELECT COUNT(DISTINCT personId) FROM ResultDates rd
WHERE rd.eventId=rd2.eventId AND rd.date <= rd2.date AND rd.personCountryId=rd2.personCountryId AND rd.average > 0 ), 0) nrAveragePoints,
if(regionalSingleRecord = 'NR',
(SELECT COUNT(DISTINCT personId) FROM ResultDates rd
WHERE rd.eventId=rd2.eventId AND rd.date <= rd2.date AND rd.personCountryId=rd2.personCountryId), 0) nrSinglePoints
FROM ResultDates rd2 WHERE (NOT regionalAverageRecord='' OR NOT regionalSingleRecord = ''));
И для завершения потребовалось 9 часов. Чтобы разбить это, я создаю таблицу, в которой 6 столбцов представляют собой целые подзапросы, чтобы подсчитать, сколько раз PersonID появляется в той же таблице до того, как произошло первое, что я просматриваю, на основе даты и нескольких других столбцов.
Я думаю, что создание индекса по дате с использованием CREATE INDEX date ON ResultDates (date) немного ускорило его, но это все равно занимает чудовищное количество времени.
Строки в ResultDates выглядят так
------------ ----------------- --------------- --------- --------- ----- --------- ---------------------- ----------------------- ------- ----- ------ ------------
| personId | personCountryId | competitionId | eventId | roundId | pos | average | regionalSingleRecord | regionalAverageRecord | month | day | year | date |
------------ ----------------- --------------- --------- --------- ----- --------- ---------------------- ----------------------- ------- ----- ------ ------------
| 1982THAI01 | USA | WC1982 | 333 | f | 1 | 0 | WR | | 6 | 5 | 1982 | 1982-06-05 |
------------ ----------------- --------------- --------- --------- ----- --------- ---------------------- ----------------------- ------- ----- ------ ------------
Где regionalSingleRecord и regionalAverageRecord могут быть любыми из этих «имен записей»: WR, NR, большую часть времени ничего, или AfR, AsR, ER, NAR, OcR или SAR, которые я затем использую для поиска идентификатора страны на основе того, с каким континентом связаны эти имена записей.
Я создал индексы для подключения этих имен записей к континентам, а идентификаторов континентов к идентификаторам стран, хотя не уверен, насколько это улучшило скорость.
Запуск EXPLAIN на нем возвращает мне это:
---- -------------------- ------------ ------------ ------ ------------------- -------------- --------- ---------------------------------- -------- ---------- ---------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- -------------------- ------------ ------------ ------ ------------------- -------------- --------- ---------------------------------- -------- ---------- ---------------------------------------------------------------
| 1 | PRIMARY | rd2 | NULL | ref | idx_personId | idx_personId | 32 | const | 567 | 99.00 | Using where |
| 9 | DEPENDENT SUBQUERY | rd | NULL | ALL | date,idx_personId | NULL | NULL | NULL | 992294 | 0.33 | Range checked for each record (index map: 0x3) |
| 8 | DEPENDENT SUBQUERY | rd | NULL | ALL | date,idx_personId | NULL | NULL | NULL | 992294 | 0.11 | Range checked for each record (index map: 0x3) |
| 6 | DEPENDENT SUBQUERY | Continents | NULL | ref | P_id,recordIndex | recordIndex | 9 | cubing.rd2.regionalSingleRecord | 1 | 100.00 | Using index; Start temporary |
| 6 | DEPENDENT SUBQUERY | Countries | NULL | ALL | NULL | NULL | NULL | NULL | 203 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
| 6 | DEPENDENT SUBQUERY | rd | NULL | ALL | date | NULL | NULL | NULL | 992294 | 0.33 | Range checked for each record (index map: 0x1); End temporary |
| 4 | DEPENDENT SUBQUERY | Continents | NULL | ref | P_id,recordIndex | recordIndex | 9 | cubing.rd2.regionalAverageRecord | 1 | 100.00 | Using index; Start temporary |
| 4 | DEPENDENT SUBQUERY | Countries | NULL | ALL | NULL | NULL | NULL | NULL | 203 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
| 4 | DEPENDENT SUBQUERY | rd | NULL | ALL | date | NULL | NULL | NULL | 992294 | 0.11 | Range checked for each record (index map: 0x1); End temporary |
| 3 | DEPENDENT SUBQUERY | rd | NULL | ALL | date,idx_personId | NULL | NULL | NULL | 992294 | 3.33 | Range checked for each record (index map: 0x3) |
| 2 | DEPENDENT SUBQUERY | rd | NULL | ALL | date,idx_personId | NULL | NULL | NULL | 992294 | 1.11 | Range checked for each record (index map: 0x3) |
---- -------------------- ------------ ------------ ------ ------------------- -------------- --------- ---------------------------------- -------- ---------- ---------------------------------------------------------------
Я немного погуглил о том, как повысить его скорость. Основываясь на моем поиске в Google, я знаю, что это выглядит не очень хорошо. Особенно с 992294 строками, которые находятся в моей исходной таблице, на которую я смотрю.
Моя проблема в том, что я не знаю, как оптимизировать, чтобы сделать все это быстрее. Я читал, что тщательно разработанные индексы могут значительно повысить скорость, поэтому мне любопытно, какие индексы здесь можно использовать.
Ответ №1:
Подзапросы в предложениях select могут быть очень дорогостоящими. Коррелированные подзапросы в целом являются плохими показателями и, как правило, имеют лучшие альтернативы.
У меня нет времени давать подробный ответ, но мое общее впечатление от просмотра запроса заключается в том, что вы, вероятно, можете реорганизовать его, чтобы ПРИСОЕДИНИТЬ ResultDates к самому себе, один раз, в основном запросе; а затем использовать условную агрегацию в предложении SELECT . Что-то вроде этого…
SELECT rd.competitionId, rd.personId, rd.personCountryId, rd.eventId
, rd.year, rd.date
, COUNT(DISTINCT IF(rd.regionalAverageRecord = 'WR' AND rdPrev.average > 0, rdPrev.person_id, NULL) AS wrAveragePoints
, COUNT(DISTINCT IF(regionalSingleRecord = 'WR', rdPrev.person_id, NULL) AS wrSinglePoints
, [etc....]
FROM ResultDates AS rd
LEFT JOIN ResultDates AS rdPrev
ON rd.eventId=rdPrev.eventId
AND rdPrev.date <= rd.date
WHERE (NOT rd.regionalAverageRecord='' OR NOT rd.regionalSingleRecord = '')
;
Редактировать: для подзапросов / полей, включающих таблицы Countries и Continents , вы также можете обращаться только LEFT JOIN к этим таблицам и использовать объединенные значения аналогично тому, как я продемонстрировал rdPrev.average , что они используются при wrAveragePoints вычислении.
Примечание: COUNT() и большинство других функций агрегирования игнорируют значения NULL.