Улучшить производительность сложного SQL-запроса
Уже несколько месяцев я собираю данные о футбольных матчах из открытых источников. Для этого я получаю данные определенного URL-адреса веб-сайта, который представляет данные для конкретного футбольного матча, с помощью PHP через XPath. Затем я занимаюсь редактированием данных, чтобы они соответствовали моим требованиям. Следующий и последний шаг — перенести их в мою базу данных MySQL в нескольких таблицах.
С быстро растущей базой данных я медленно сталкиваюсь с серьезными проблемами с производительностью. Поскольку я все делаю локально на своем компьютере, и он не является монстром машины, обработка матча уже занимает некоторое время. Чтобы почувствовать, как быстро это происходит: в первые дни сбора данных матч занимал около 24 секунд. Однако теперь среднее значение превысило 60-секундный порог.
До сих пор я время от времени заходил в код PHP и пытался улучшить его, где это возможно, потому что я думал, что основная проблема заключается в не очень чистых фрагментах кода. Хотя это немного помогло, среднее время спустя несколько дней увеличилось, и в последнее время я начал понимать, что должна быть еще одна проблема, требующая много времени. Поэтому я создал тестовый PHP-скрипт, который выполняет какую-то регистрацию при выполнении основного кода.
Это показывает, что некоторые запросы SQL, которые я выполняю для вставки данных в таблицы базы данных, в среднем занимают много времени (я проанализировал 100 совпадений здесь):
- стартовый состав в БД: 6,44 секунды
- Заменяемые заменители в БД: 8,49 секунд
Проверяя запросы снова, я понял, что они довольно сложны.
Вот эти таблицы:
tblStartingSquad
+----+---------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+--------+
| id | matchID | player1ID | player2ID | player3ID | player4ID | player5ID | player6ID | player7ID | player8ID | player9ID | player10ID | player11ID | clubID |
+----+---------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+--------+
| 1 | 1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 1 |
| 2 | 1 | 12 | 13 | 14 | 15 | 16 | 17 | 16 | 17 | 18 | 19 | 20 | 2 |
| 3 | 2 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 1 |
| 4 | 2 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 3 |
+----+---------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+--------+
tblSubstitutes
+----+---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+--------+
| id | matchID | player12ID | player13ID | player14ID | player15ID | player16ID | player17ID | player18ID | player19ID | player20ID | player21ID | player22ID | player23ID | clubID |
+----+---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+--------+
| 1 | 1 | 32 | 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | 1 |
| 2 | 1 | 44 | 45 | 46 | 47 | 48 | 49 | 50 | 51 | 52 | 53 | 54 | 55 | 2 |
| 3 | 2 | 32 | 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | 1 |
| 4 | 2 | 56 | 57 | 58 | 59 | 60 | 61 | 61 | 62 | 63 | 64 | 65 | 66 | 3 |
+----+---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+--------+
tblMatch
+---------+---------------------+-------------+------------------+
| matchID | date | coach1 | coach2 |
+---------+---------------------+-------------+------------------+
| 1 | 2006-08-19 22:00:00 | Piotr Nowak | Fernando Clavijo |
| 2 | 2006-08-15 21:00:00 | Piotr Nowak | Mustafa Ugur |
+---------+---------------------+-------------+------------------+
tblPlayer
+----------+------------------------+------------------+
| playerID | namePlayer | short |
+----------+------------------------+------------------+
| 1 | Enis Ulusan | enis-ulusan |
| 2 | Grant Robert Murray | grant-murray |
| 3 | Evgeniy Shpedt | evgeniy-shpedt |
| 4 | Mihai Alexandru Costea | mihai-costea |
| 5 | Jan Zolna | jan-zolna |
| 6 | Adrian Gheorghiu | adrian-gheorghiu |
| 7 | Marius Marian Croitoru | marius-croitoru |
| 8 | Jacov Nachtailer | jacov-nachtailer |
| ... | ... | ... |
+----------+------------------------+------------------+
tblClub
+--------+-----------------+
| clubID | nameClub |
+--------+-----------------+
| 1 | D.C. United |
| 2 | Colorado Rapids |
| 3 | Caykur Rizespor |
+--------+-----------------+
И эти вопросы включают в себя:
SQL Query стартовый состав
$tblstarting_squad = 'INSERT INTO tblStartingSquad (matchID, player1ID, player2ID, player3ID, player4ID, player5ID, player6ID, player7ID, player8ID, player9ID, player10ID, player11ID, clubID)
SELECT
(SELECT matchID FROM tblMatch WHERE date = "' . $match_date . '" AND coach1 = "' . $match_coach_home . '" AND coach2 = "' . $match_coach_away . '"),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[0] . '" AND short = "' . $player_short[0] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[1] . '" AND short = "' . $player_short[1] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[2] . '" AND short = "' . $player_short[2] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[3] . '" AND short = "' . $player_short[3] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[4] . '" AND short = "' . $player_short[4] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[5] . '" AND short = "' . $player_short[5] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[6] . '" AND short = "' . $player_short[6] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[7] . '" AND short = "' . $player_short[7] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[8] . '" AND short = "' . $player_short[8] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[9] . '" AND short = "' . $player_short[9] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[10] . '" AND short = "' . $player_short[10] . '" LIMIT 1),
(SELECT clubID FROM tblClub WHERE nameClub = "' . $match_club[1] . '" LIMIT 1)
WHERE NOT EXISTS (
SELECT e.matchID
FROM tblStartingSquad As e
INNER JOIN tblMatch As m
ON e.matchID = m.matchID
WHERE m.date = "' . $match_date . '" AND m.coach1 = "' . $match_coach_home . '" AND m.coach2 = "' . $match_coach_away . '" AND e.clubID = (SELECT clubID FROM tblClub WHERE nameClub = "' . $match_club[1] . '")
);';
if (!mysqli_query($db_connection, $tblstarting_squad)) {
echo("Error description $tblstarting_squad: " . mysqli_error($db_connection) . "<br />");
}
SQL Query Benches заменители
$tblsubstitutes = 'INSERT INTO tblSubstitutes (matchID, player12ID, player13ID, player14ID, player15ID, player16ID, player17ID, player18ID, player19ID, player20ID, player21ID, player22ID, player23ID, clubID)
SELECT
(SELECT matchID FROM tblMatch WHERE date = "' . $match_date . '" AND coach1 = "' . $match_coach_home . '" AND coach2 = "' . $match_coach_away . '"),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[11] . '" AND short = "' . $player_short[11] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[12] . '" AND short = "' . $player_short[12] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[13] . '" AND short = "' . $player_short[13] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[14] . '" AND short = "' . $player_short[14] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[15] . '" AND short = "' . $player_short[15] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[16] . '" AND short = "' . $player_short[16] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[17] . '" AND short = "' . $player_short[17] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[18] . '" AND short = "' . $player_short[18] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[19] . '" AND short = "' . $player_short[19] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[20] . '" AND short = "' . $player_short[20] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[21] . '" AND short = "' . $player_short[21] . '" LIMIT 1),
(SELECT playerID FROM tblPlayer WHERE namePlayer = "' . $player_name[22] . '" AND short = "' . $player_short[22] . '" LIMIT 1),
(SELECT clubID FROM tblClub WHERE nameClub = "' . $match_club[1] . '" LIMIT 1)
WHERE NOT EXISTS (
SELECT e.matchID
FROM tblSubstitutes As e
INNER JOIN tblMatch As m
ON e.matchID = m.matchID
WHERE m.date = "' . $match_date . '" AND m.coach1 = "' . $match_coach_home . '" AND m.coach2 = "' . $match_coach_away . '" AND e.clubID = (SELECT clubID FROM tblClub WHERE nameClub = "' . $match_club[1] . '")
);';
if (!mysqli_query($db_connection, $tblsubstitutes)) {
echo("Error description $tblsubstitutes: " . mysqli_error($db_connection) . "<br />");
}
Оба запроса практически идентичны. Они вставляют playerID 11 (соответственно 12) игроков в tblStartingSquad (соответственно tblSubstitutes) если нет другой записи с идентичными данными. playerID необходимо предварительно проверить в базе данных, так как необработанные данные не имеют индивидуального идентификатора. Это происходит, выбрав его через namePlayer а также short из таблицы tblPlayer,
Столы tblStartingSquad а также tblSubstitutes в настоящее время содержит 110 000 строк (для 55 000 совпадений), tblPlayer на 100 000 строк.
Я немного погуглил по решениям, но не смог найти ничего, что улучшило бы общую скорость. Что я понимаю под проблемой, так это то, что я должен проверять каждого игрока индивидуально, поэтому я получаю 11 и 12 подзапросов. Это не очень элегантно, но я не знаю, как это улучшить. Может быть, у кого-то здесь на StackOverflow есть предложение?
Решение
Пересмотрите свой широкий дизайн стола для долго дизайн стола. Нумерованные столбцы с суффиксами никогда не являются идеальным хранилищем данных. Ряды дешевы. Колонны стоят дорого. Объединения, агрегация, поиск, индексация и т. Д. В длинном формате намного проще. В противном случае ваши запросы будут сложными, поскольку вы показываете с 12 подзапросами или даже самостоятельными соединениями!
Интересно, ваш tblClub а также tblPlayer в длинном формате, но не tblStartingSquad а также tblSubstitutes! Просто удалите все посторонние колонки игрока в один где строки обозначают разных игроков:
tblStartingSquad
ID MatchID PlayerID ClubID
1 1 5 1
2 1 8 1
3 1 9 1
...
tblSubstitutes
ID MatchID PlayerID ClubID
1 1 2 1
2 1 16 1
3 1 7 1
...
tblMatch (с переименованными колонками тренера для ясности)
ID Date HomeCoach AwayCoach
1 2006-08-19 22:00:00 Piotr Nowak Fernando Clavijo
2 2006-08-15 21:00:00 Piotr Nowak Mustafa Ugur
PHP
Из этого дизайна базы данных вы можете запустить более простой вызов параметризованного запроса PHP и еще проще с PDO, вместо mysqli для привязки многих параметров из массивов.
// OPEN CONNECTION
$dbconn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// SET PDO ERROR MODE TO EXCEPTION
$dbconn -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// PREPARED STATEMENT
$sql = "INSERT INTO tblStartingSquad (`Match`, `PlayerID`, `ClubID`)
SELECT m.MatchID, p1.PlayerID, c.ClubID
FROM
(SELECT p.PlayerID
FROM tblPlayer p
WHERE p.namePlayer IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
) p1
INNER JOIN
(SELECT p.PlayerID
FROM tblPlayer p
WHERE p.short IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
) p2 ON p1.PlayerID = p2.PlayerID
CROSS JOIN
(SELECT MatchID
FROM tblMatch
WHERE `date` = ? AND HomeCoach = ? AND AwayCoach = ?) m
CROSS JOIN
(SELECT ClubID
FROM tblClub
WHERE nameClub = ?) c
WHERE NOT EXISTS
(SELECT 1 FROM tblStartingSquad As e
WHERE e.MatchID = m.matchID)"
try {
// INITIALIZE STATEMENT
$stmt = $dbconn->prepare($sql);
$params = array($player_name[0], $player_name[1], $player_name[2],
$player_name[3], $player_name[4], $player_name[5],
$player_name[6], $player_name[7], $player_name[8],
$player_name[9], $player_name[10],
$player_short[0], $player_short[1], $player_short[2],
$player_short[3], $player_short[4], $player_short[5],
$player_short[6], $player_short[7], $player_short[8],
$player_short[9], $player_short[10],
$match_date, $match_coach_home, $match_coach_away, $match_club);
// ITERATIVELY BIND PARAMS
foreach($params as $key => $val) {
$stmt->bindParam($key+1, $val, PDO::PARAM_STR);
}
// EXECUTE ACTION
$stmt->execute();
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
Сделайте аналогичный звонок для tblSubstitutes, настройка назначения запроса на добавление и WHERE предложения и значения параметров.
Другие решения
Как уже было сказано О.Джонсом в комментариях, было бы полезно увидеть больше (php) кода, чтобы судить о проблемах производительности.
Несмотря на редизайн вашей базы данных, еще одним быстрым предложением будет запуск цикла и использование подготовленное заявление для отдельных запросов для идентификаторов игроков. Это может дать вам небольшой прирост производительности.
Мне кажется логичным делать больше в PHP, а не передавать логику извлечения данных в SQL.
Заявления не являются сложными. Они просто содержат много поисков. Поэтому убедитесь, что поиск выполняется быстро. Вам нужны следующие индексы. Добавьте их в свою базу данных, если у вас их еще нет.
create index idx_find_player on tblplayer (nameplayer, short, playerid);
create index idx_find_club on tblclub (nameclub, clubid)
create index idx_find_match on tblmatch (date, coach1, coach2, matchid)
create index idx_find_squad1 on tblstartingsquad (matchid, clubid)
create index idx_find_squad2 on tblSsartingsquad (clubid, matchid)
create index idx_find_subs1 on tblsubstitutes (matchid, clubid)
create index idx_find_subs2 on tblsubstitutes (clubid, matchid)
Я не уверен, какой индекс команды будет использоваться с большей вероятностью, поэтому создайте оба и посмотрите, что выберет СУБД. Вы можете бросить другой. То же самое для заменителей индексов.
для точного ответа мы должны увидеть ваш план выполнения в SQL, отправьте его сюда, чтобы я мог помочь вам с вашей проблемой
до этого я думаю, что вы все делаете неправильно, вместо того, чтобы писать один выбор для одной строки, вы можете просто определить пользовательскую таблицу в вашей базе данных и передать свои значения. сделав это, вы сможете повысить производительность своего SQL и серверного кода, и я гарантирую вам, что вы получите лучшую производительность на первом уровне, а после этого, как я уже говорил, нужен план выполнения
- объявите свой тип (не забудьте изменить тип данных своим собственным)
CREATE TYPE [dbo]. [Com_ListOfGuid] AS TABLE (NamePlayer NVARCHAR (256) NOT NULL, короткий NVARCHAR (256) NOT NULL)
-
вместо создания оператора select в вашем коде просто объявите таблицу данных, заполните ее данными и передайте ее точно так же, как и другие параметры (не забудьте указать имя столбца точно так же, как ваш тип таблицы, определенный пользователем)
3. в своем коде SQL просто присоедините пользовательский тип таблицы к вашей таблице и вставьте их в целевую таблицу (я приведу вам пример, и вы меняете его по мере необходимости)СОЗДАТЬ ПРОЦЕДУРУ
@ObjectTable Type_Your_Name READONLY
КАК
INSERT INTO TARGET_Table ()
ВЫБРАТЬ
*
ОТ
first_Table F INNER JOIN
@ObjectTable T ON F.NamePlayer = t.NamePlayer и t.Short = f.Short