Desde hace unos meses estoy recopilando datos de partidos de fútbol de fuentes abiertas. Para hacerlo, tomo los datos de una URL de un sitio web específico, que presenta los datos de un partido de fútbol específico, con PHP a través de XPath. Luego hago algo de edición de datos para que se ajusten a la forma en que los necesito. El siguiente y último paso es transferirlos a mi base de datos MySQL en varias tablas.

Con una base de datos de rápido crecimiento, lentamente me encuentro con graves problemas de rendimiento. Debido a que hago todo localmente en mi computadora y ese no es un monstruo de máquina, ya lleva algún tiempo procesar una coincidencia. Para tener una idea de lo rápido que es eso: en los primeros días de minería de datos, una partida tardó alrededor de 24 segundos. Sin embargo, ahora el promedio superó el umbral de 60 segundos.

Hasta ahora, ocasionalmente ingresaba al código PHP e intentaba mejorarlo cuando era posible, porque pensaba que el problema principal está en fragmentos de código no tan limpios. Si bien ayudó un poco, el tiempo promedio creció aún más después de varios días y últimamente comencé a darme cuenta de que debe haber otro problema que consume mucho tiempo. Así que hice un script PHP de prueba que realiza algún tipo de registro mientras ejecuta el código principal.

Muestra que algunas consultas SQL que hago para insertar los datos en las tablas de mi base de datos toman mucho tiempo en promedio (analicé 100 coincidencias aquí):

  • plantilla inicial en DB: 6.44 segundos
  • sustitutos de banco en DB: 8.49 segundos

Al revisar las consultas nuevamente, me di cuenta de que son bastante complejas.

Esas son las tablas involucradas:

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 |
+--------+-----------------+

Y esas son las consultas involucradas:

Escuadrón de inicio de consultas SQL

$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 />");
                }

Sustitutos en banco de consulta SQL

$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 />");
                }

Ambas consultas son prácticamente idénticas. Inserta el playerID de 11 (respectivamente 12) jugadores en el tblStartingSquad (respectivamente tblSubstitutes) si no hay otra entrada con datos idénticos. El playerID debe verificarse de antemano en la base de datos ya que los datos sin procesar no tienen la identificación individual. Eso sucede seleccionándolo a través de namePlayer y short de la tabla tblPlayer.

Las tablas tblStartingSquad y tblSubstitutes contienen 110,000 filas (para 55,000 coincidencias) actualmente, tblPlayer está en 100,000 filas.

Busqué en Google algunas soluciones, pero no pude encontrar nada que mejorara la velocidad general. Lo que entiendo como un problema es que tengo que verificar cada jugador individualmente, por lo que obtengo 11 y 12 subconsultas. Eso no es realmente elegante, pero realmente no tengo idea de cómo mejorar eso. ¿Quizás alguien aquí en StackOverflow tiene una sugerencia?

0
S1dy 8 sep. 2018 a las 21:30

4 respuestas

La mejor respuesta

Reconsidere su diseño de tabla ancho para un diseño de tabla largo . Las columnas con sufijos numerados nunca son el almacenamiento de datos ideal. Las filas son baratas. Las columnas son caras. Las uniones, la agregación, la búsqueda, la indexación, etc. son mucho más fáciles en formato largo. De lo contrario, sus consultas serán complejas a medida que se muestre con 12 subconsultas o incluso autouniones.

¡Curiosamente, su tblClub y tblPlayer están en formato largo pero no tblStartingSquad y tblSubstitutes ! Simplemente, elimine todas las columnas de jugadores extraños en uno donde las filas indican diferentes jugadores:

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 (con columnas de entrenador renombradas para mayor claridad)

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

A partir de este diseño de base de datos, puede ejecutar una llamada de consulta con parámetros PHP más simple y aún más fácil con PDO, en lugar de mysqli para vincular muchos parámetros de las matrices.

// 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();
}

Haga una llamada similar para tblSubstitutes , ajustando el destino de la consulta de adición y los valores de la cláusula WHERE y los parámetros.

3
Parfait 14 sep. 2018 a las 18:21

Las declaraciones no son complejas. Simplemente contienen muchas búsquedas. Así que asegúrese de que las búsquedas sean rápidas. Necesita los siguientes índices. Agréguelos a su base de datos si aún no los tiene.

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)

No estoy seguro de qué índice de escuadrón es más probable que se use, así que cree ambos y vea cuál elige el DBMS. Luego puedes soltar el otro. Lo mismo para los índices sustitutos.

0
Thorsten Kettner 8 sep. 2018 a las 21:38

Para obtener una respuesta precisa, tenemos que ver su plan de ejecución en SQL, envíelo aquí, para que pueda ayudarlo con su problema

Antes de eso, creo que está haciendo todo mal, en lugar de escribir una selección para la fila única, simplemente puede definir la tabla definida por el usuario en su base de datos y pasar sus valores. Al hacerlo, podría tener un mejor rendimiento en su SQL y en su código del lado del servidor y le garantizo que tiene el mejor rendimiento en el primer nivel y después de eso, como dije antes, necesita un plan de ejecución

  1. declare su tipo (no olvide cambiar el tipo de datos con el suyo)

CREATE TYPE [dbo]. [Com_ListOfGuid] AS TABLE (NamePlayer NVARCHAR (256) NOT NULL, Short NVARCHAR (256) NOT NULL)

  1. en lugar de crear una declaración de selección en su código, simplemente declare una tabla de datos y complete los datos y páselos como otros parámetros (no olvide establecer un nombre de columna exactamente como su tipo de tabla definida por el usuario) 3.en su código SQL, simplemente únase al tipo de tabla definida por el usuario con su tabla e insértelos en su tabla de destino (le doy un ejemplo y cambia con su necesidad)

    PROCEDIMIENTO DE CREACIÓN Procedimiento_Su_Nombre @ObjectTable Type_Your_Name READONLY COMO INSERTAR EN TARGET_Table () SELECCIONE * * DESDE first_Table F INNER JOIN @ObjectTable T ON F.NamePlayer = t.NamePlayer y t.Short = f.Short

0
Alireza Yadegari 21 sep. 2018 a las 08:03

Como ya dijo O.Jones en los comentarios, sería útil ver más código (php) para juzgar los problemas de rendimiento. A pesar de rediseñar su base de datos, otra sugerencia rápida sería, de hecho, ejecutar un bucle y utilizar un declaración preparada para las consultas individuales para los ID de jugador. Eso podría darle un ligero aumento de rendimiento.

Me parece lógicamente hacer más en PHP en lugar de externalizar las lógicas de obtención de datos a SQL.

1
barkeldiho 8 sep. 2018 a las 19:41