Mi código en Laravel es:
Car::selectRaw('*, MIN(car_prices.price) AS min_price, MAX(car_prices.price) AS max_price, MAX(car_prices.updated_at) AS latest_update') ->leftJoin('car_prices', 'car_prices.car_id', 'cars.id') ->groupBy('car_prices.car_id') ->orderBy('latest_update', 'desc') ->paginate(10);Se tarda mucho tiempo en ejecutarse hasta que arroja un error:
Tiempo máximo de ejecución de 60 segundos excedido
El conteo de registros en la tabla de cars es 100,000 y 6,000,000 en car_prices .
La estructura de las tablas:
CREATE TABLE `cars` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=110001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci CREATE TABLE `car_prices` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `car_id` bigint(20) unsigned NOT NULL, `price` decimal(8,2) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `car_prices_car_id_foreign` (`car_id`) ) ENGINE=MyISAM AUTO_INCREMENT=5506827 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ciLa consulta:
select count(*) as aggregate from `cars` left join `car_prices` on `car_prices`.`car_id` = `cars`.`id` group by `car_prices`.`car_id`; select *, MIN(car_prices.price) AS min_price, MAX(car_prices.price) AS max_price, MAX(car_prices.updated_at) AS latest_update from `cars` left join `car_prices` on `car_prices`.`car_id` = `cars`.`id` group by `car_prices`.`car_id` order by `latest_update` desc limit 10 offset 0;¿Cómo puedo optimizarlo? ¿Debo almacenar en caché los datos? ¿O hay alguna consulta mejor que esta?
Necesita tener un mejor índice único de la tabla de automóviles last_update o eliminar ->orderBy('latest_update', 'desc') en la consulta. y ordenarlo después de recibir los resultados
Puedes verificar el rendimiento en mysql con explicación
EXPLICAR SELECCIONAR * DESDE orden de auto por last_update desc;
/// Verifique esto https://www.exoscale.com/syslog/explaining-mysql-queries/#:~:text=the%20last%20decade.-,Explain,DELETE%20%2C%20REPLACE%20%2C %20y%20ACTUALIZAR%20 .
Básicamente, necesita optimizar (mejor indexar) el "automóvil" de su tabla DB para que funcione bien
Y otra cosa que podría intentar aumentar el tiempo de ejecución. En php.ini, debe establecer max_execution_time = 600 o algo más para verificar cuánto tiempo se necesita para completar la ejecución. https://www.codewall.co.uk/aumentar-php-script-max-execution-time-limit-using-ini_set-function/
En ambas consultas,
GROUP BY cars.id Esto es en lugar de usar car_prices . car_id , que podría faltar debido a LEFT JOIN .
Una vez que haya hecho eso, la primera consulta (con solo COUNT ) puede descartar JOIN . Y luego el GROUP BY se vuelve redundante:
select count(*) as aggregate from `cars`La segunda consulta tiene problemas.
Con el diseño actual, debe pasar por todas las dos tablas. Puaj.
Además... Si no hay precios para un automóvil dado, tendrá NULL para la latest_update , por lo tanto, ordenará al final de las 100,000 filas. Dado eso, es mejor que no muestre esos autos; esto simplificaría la consulta lo suficiente como para optimizarla mejor.
Si necesita enumerar los automóviles para los que no tiene precios, haga una solicitud por separado en la interfaz de usuario. Esa consulta será LEFT JOIN .. IS NULL y no necesitará los MAX()s .
Pero, todavía estoy preocupado por las 10,000 páginas que el usuario necesita para paginar.
Cambie de MyISAM a InnoDB.
Mezcle created_at y updated_at , si no los está usando para nada.
Después de eso, cars es simplemente un mapeo entre id y name . Esto podría permitirle evitar pasar entre cars . En su lugar haz algo como
SELECT ( SELECT name FROM cars WHERE id = x.car_id ) AS name, ... FROM ... Otro pensó que cada vez que agrega una fila a car_prices , actualiza updated_at en cars . Esto le permitiría encontrar los 10 autos completamente en cars .
Decide lo que estás dispuesto a sacrificar.
Más
Nota: Con MyISAM, un SELECT lento bloquea la UPDATE . Con InnoDB, puede ejecutarse en paralelo; SELECT usa los valores antes de UPDATE . De cualquier manera, la selección es en algún "punto en el tiempo". Pero InnoDB permite más paralelismo.
Es una compensación. Una pequeña ralentización en las actualizaciones para lograr una gran aceleración en las selecciones. (No, no estoy seguro de que mi sugerencia sea "más rápida")
Algunas preguntas adicionales para analizar la compensación:
innodb_flush_log_at_trx_commit (después de cambiar a InnoDB).La consulta que ha utilizado no es apta para tablas tan grandes. en cambio, cada vez que ingrese a la tabla car_prices , establezca una operación y tome el valor mínimo y máximo y guárdelo en la tabla de autos. o puede configurar una anciana para esto.