php - tablas - La consulta MYSQL funciona muy lento

optimizar consultas mysql explain

Question

Desarrollé un módulo de carga masiva de usuarios. Hay 2 situaciones en las que realizo una carga masiva de 20 000 registros cuando la base de datos tiene cero registros. Tarda aproximadamente 5 horas. Pero cuando la base de datos ya tiene unos 30 000 registros, la carga es muy lenta. Se tarda aproximadamente 11 horas en cargar 20 000 registros. Solo estoy leyendo un archivo CSV a través del método fgetcsv .

if (($handle = fopen($filePath, "r")) !== FALSE) {
while (($peopleData = fgetcsv($handle, 10240, ",")) !== FALSE) {
if (count($peopleData) == $fieldsCount) {
//inside i check if user already exist (firstName & lastName & DOB)
//if not, i check if email exist. if exist, update the records.
//other wise insert a new record.
}}}

A continuación se muestran las consultas que se ejecutan. (Estoy usando el framework Yii)

SELECT *
FROM `AdvanceBulkInsert` `t`
WHERE renameSource=''24851_bulk_people_2016-02-25_LE CARVALHO 1.zip.csv''
LIMIT 1
SELECT cf.*, ctyp.typeName, cfv.id as customId, cfv.customFieldId,
cfv.relatedId, cfv.fieldValue, cfv.createdAt
FROM `CustomField` `cf`
INNER JOIN CustomType ctyp on ctyp.id = cf.customTypeId
LEFT OUTER JOIN CustomValue cfv on cf.id = cfv.customFieldId
and relatedId = 0
LEFT JOIN CustomFieldSubArea cfsa on cfsa.customFieldId = cf.id
WHERE ((relatedTable = ''people'' and enabled = ''1'')
AND (onCreate = ''1''))
AND (cfsa.subarea=''peoplebulkinsert'')
ORDER BY cf.sortOrder, cf.label
SELECT *
FROM `User` `t`
WHERE `t`.`firstName`=''Franck''
AND `t`.`lastName`=''ALLEGAERT ''
AND `t`.`dateOfBirth`=''1971-07-29''
AND (userType NOT IN ("1"))
LIMIT 1

Si existe actualizar al usuario:

UPDATE `User` SET `id`=''51394'', `address1`=''49 GRANDE RUE'',
`mobile`='''', `name`=NULL, `firstName`=''Franck'',
`lastName`=''ALLEGAERT '', `username`=NULL,
`password`=NULL, `email`=NULL, `gender`=0,
`zip`=''60310'', `countryCode`=''DZ'',
`joinedDate`=''2016-02-23 10:44:18'',
`signUpDate`=''0000-00-00 00:00:00'',
`supporterDate`=''2016-02-25 13:26:37'', `userType`=3,
`signup`=0, `isSysUser`=0, `dateOfBirth`=''1971-07-29'',
`reqruiteCount`=0, `keywords`=''70,71,72,73,74,75'',
`delStatus`=0, `city`=''AMY'', `isUnsubEmail`=0,
`isManual`=1, `isSignupConfirmed`=0, `profImage`=NULL,
`totalDonations`=NULL, `isMcContact`=NULL,
`emailStatus`=NULL, `notes`=NULL,
`addressInvalidatedAt`=NULL,
`createdAt`=''2016-02-23 10:44:18'',
`updatedAt`=''2016-02-25 13:26:37'', `longLat`=NULL
WHERE `User`.`id`=''51394''

Si el usuario no existe, inserte un nuevo registro.

El tipo de motor de tabla es MYISAM. Solo la columna de correo electrónico tiene un índice.

¿Cómo puedo optimizar esto para reducir el tiempo de procesamiento?

Consulta 2, tomó 0,4701 segundos, lo que significa que para 30 000 registros tomará 14103 segundos, que son aproximadamente 235 minutos. aproximadamente 6 horas.

Actualizar

CREATE TABLE IF NOT EXISTS `User` (
`id` bigint(20) NOT NULL,
`address1` text COLLATE utf8_unicode_ci,
`mobile` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`firstName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`lastName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`username` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`password` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`gender` tinyint(2) NOT NULL DEFAULT ''0'' COMMENT ''1 - female, 2-male, 0 - unknown'',
`zip` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`countryCode` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`joinedDate` datetime DEFAULT NULL,
`signUpDate` datetime NOT NULL COMMENT ''User signed up date'',
`supporterDate` datetime NOT NULL COMMENT ''Date which user get supporter'',
`userType` tinyint(2) NOT NULL,
`signup` tinyint(2) NOT NULL DEFAULT ''0'' COMMENT ''whether user followed signup process 1 - signup, 0 - not signup'',
`isSysUser` tinyint(1) NOT NULL DEFAULT ''0'' COMMENT ''1 - system user, 0 - not a system user'',
`dateOfBirth` date DEFAULT NULL COMMENT ''User date of birth'',
`reqruiteCount` int(11) DEFAULT ''0'' COMMENT ''User count that he has reqruited'',
`keywords` text COLLATE utf8_unicode_ci COMMENT ''Kewords'',
`delStatus` tinyint(2) NOT NULL DEFAULT ''0'' COMMENT ''0 - active, 1 - deleted'',
`city` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`isUnsubEmail` tinyint(1) NOT NULL DEFAULT ''0'' COMMENT ''0 - ok, 1 - Unsubscribed form email'',
`isManual` tinyint(1) NOT NULL DEFAULT ''0'' COMMENT ''0 - ok, 1 - Manualy add'',
`longLat` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ''Longitude and Latitude'',
`isSignupConfirmed` tinyint(4) NOT NULL DEFAULT ''0'' COMMENT ''Whether user has confirmed signup '',
`profImage` tinytext COLLATE utf8_unicode_ci COMMENT ''Profile image name or URL'',
`totalDonations` float DEFAULT NULL COMMENT ''Total donations made by the user'',
`isMcContact` tinyint(1) DEFAULT NULL COMMENT ''1 - Mailchimp contact'',
`emailStatus` tinyint(2) DEFAULT NULL COMMENT ''1-bounced, 2-blocked'',
`notes` text COLLATE utf8_unicode_ci,
`addressInvalidatedAt` datetime DEFAULT NULL,
`createdAt` datetime NOT NULL,
`updatedAt` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `AdvanceBulkInsert` (
`id` int(11) NOT NULL,
`source` varchar(256) NOT NULL,
`renameSource` varchar(256) DEFAULT NULL,
`countryCode` varchar(3) NOT NULL,
`userType` tinyint(2) NOT NULL,
`size` varchar(128) NOT NULL,
`errors` varchar(512) NOT NULL,
`status` char(1) NOT NULL COMMENT ''1:Queued, 2:In Progress, 3:Error, 4:Finished, 5:Cancel'',
`createdAt` datetime NOT NULL,
`createdBy` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `CustomField` (
`id` int(11) NOT NULL,
`customTypeId` int(11) NOT NULL,
`fieldName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`relatedTable` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`defaultValue` text COLLATE utf8_unicode_ci,
`sortOrder` int(11) NOT NULL DEFAULT ''0'',
`enabled` char(1) COLLATE utf8_unicode_ci DEFAULT ''1'',
`listItemTag` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`required` char(1) COLLATE utf8_unicode_ci DEFAULT ''0'',
`onCreate` char(1) COLLATE utf8_unicode_ci DEFAULT ''1'',
`onEdit` char(1) COLLATE utf8_unicode_ci DEFAULT ''1'',
`onView` char(1) COLLATE utf8_unicode_ci DEFAULT ''1'',
`listValues` text COLLATE utf8_unicode_ci,
`label` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`htmlOptions` text COLLATE utf8_unicode_ci
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `CustomFieldSubArea` (
`id` int(11) NOT NULL,
`customFieldId` int(11) NOT NULL,
`subarea` varchar(256) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `CustomValue` (
`id` int(11) NOT NULL,
`customFieldId` int(11) NOT NULL,
`relatedId` int(11) NOT NULL,
`fieldValue` text COLLATE utf8_unicode_ci,
`createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM AUTO_INCREMENT=86866 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Todo el código PHP está aquí http://pastie.org/10737962

Actualización 2

Explicar el resultado de la consulta

Answer 1

Si entiendo, para todo el resultado de SELECT * FROM AdvanceBulkInsert ... ejecuta una solicitud SELECT cf.* , y para todos los SELECT cf.* , ejecuta el SELECT * FROM User

Creo que el problema es que envías demasiadas solicitudes a la base.

Creo que debería fusionar toda su solicitud de selección en solo una gran solicitud.

Para eso:

• reemplace el SELECT * FROM AdvanceBulkInsert por un EXISTS IN (SELECT * FROM AdvanceBulkInsert where ...) o un JOIN

• reemplace el SELECT * FROM User por un NOT EXISTS IN(SELECT * from User WHERE )

Luego llama a la actualización sobre todos los resultados de la selección combinada.

También debe solicitar su solicitud una vez por hora para encontrar cuál de estas solicitudes lleva más tiempo, y también debe usar ANALIZAR para encontrar qué parte de la solicitud lleva tiempo.

Editar:

Ahora he visto tu código:

Algunos llevan:

• haz indexado para cf.customTypeId, cfv.customFieldId, cfsa.customFieldId, usuario. dateOfBirth, usuario. firstName, user.lastName?

• no necesita hacer una combinación IZQUIERDA CustomFieldSubArea si tiene un WHERE que utiliza CustomFieldSubArea, un simple JOIN CustomFieldSubArea es enougth.

• Lanzará la consulta 2 mucho tiempo con relatedId = 0, ¿tal vez puede guardar el resultado en una var?

• si no necesita datos ordenados, elimine "ORDER BY cf.sortOrder, cf.label". De lo contrario, agregue índice en cf.sortOrder, cf.label

Answer 2

Los índices son tus amigos.

UPDATE User ... WHERE id = ... - Desesperadamente necesita un índice en ID, probablemente PRIMARY KEY .

Del mismo modo para renameSource .

SELECT *
FROM `User` `t`
WHERE `t`.`firstName`=''Franck''
AND `t`.`lastName`=''ALLEGAERT ''
AND `t`.`dateOfBirth`=''1971-07-29''
AND (userType NOT IN ("1"))
LIMIT 1;

Necesita INDEX(firstName, lastName, dateOfBirth) ; los campos pueden estar en cualquier orden (en este caso).

Mire cada consulta para ver lo que necesita, luego agregue ese INDEX a la tabla. Lee mi libro de cocina en la creación de índices .

Answer 3

Parece que tiene la posibilidad (¿probabilidad?) De 3 consultas para cada registro. Esas 3 consultas requerirán 3 viajes a la base de datos (y si está usando yii almacenando los registros en objetos yii, eso podría ralentizar aún más las cosas).

¿Puedes agregar una clave única en el nombre / apellido / fecha de nacimiento y otra en la dirección de correo electrónico?

Si es así, puede hacer INSERTAR .... ACTUALIZAR CLAVE DUPLICADA. Esto lo reduciría a una sola consulta para cada registro, acelerando enormemente las cosas.

Pero la gran ventaja de esta sintaxis es que puedes insertar / actualizar muchos registros a la vez (normalmente me limito a unos 250), por lo que aún menos viajes a la base de datos.

Puedes derribar una clase a la que le pasas los registros y la inserta cuando la cantidad de registros coincide con tu elección. También agregue una llamada para insertar los registros en el destructor para insertar cualquier registro final.

Otra opción es leer todo en una tabla temporal y luego usar eso como fuente para unirse a su tabla de usuarios para hacer las actualizaciones / insertar en. Esto requeriría un poco de esfuerzo con los índices, pero una carga masiva a una tabla temporal es rápida, y una actualización de eso con índices útiles sería rápida. Usarlo como fuente para las inserciones también debe ser rápido (si excluye los registros ya actualizados).

El otro problema parece ser su siguiente consulta, pero no está seguro de dónde ejecutar esto. Parece que solo necesita ejecutarse una vez, en cuyo caso podría no importar demasiado. No ha dado la estructura de la tabla CustomType, pero está unida a Customfield y el campo customTypeId no tiene índice. Por lo tanto, esa unión será lenta. Del mismo modo, en CustomValue y CustomFieldSubArea se unen las que se unen en customFieldId, y tampoco tienen un índice en este campo (con suerte, un índice único, como si esos campos no fueran únicos, obtendrán MUCHOS registros devueltos: 1 fila por cada posible combinación)

SELECT cf.*, ctyp.typeName, cfv.id as customId, cfv.customFieldId,
cfv.relatedId, cfv.fieldValue, cfv.createdAt
FROM `CustomField` `cf`
INNER JOIN CustomType ctyp on ctyp.id = cf.customTypeId
LEFT OUTER JOIN CustomValue cfv on cf.id = cfv.customFieldId
and relatedId = 0
LEFT JOIN CustomFieldSubArea cfsa on cfsa.customFieldId = cf.id
WHERE ((relatedTable = ''people'' and enabled = ''1'')
AND (onCreate = ''1''))
AND (cfsa.subarea=''peoplebulkinsert'')
ORDER BY cf.sortOrder, cf.label

Answer 4

Cuando necesite averiguar por qué una consulta toma mucho tiempo, debe inspeccionar partes individuales. Como lo muestra en la pregunta Explicación, la declaración puede ayudarlo mucho. Por lo general, las columnas más importantes son:

• select_type - esto siempre debe ser simple query / subquery. Las subconsultas relacionadas producen muchos problemas. Afortunadamente no usas ningún
• claves posibles - ¿Qué teclas va a seleccionar esta búsqueda por
• filas: cuántas filas candidatas determinan las claves / caché y otras técnicas. Un número más pequeño es mejor
• Extra - "usar" le dice exactamente cómo se encuentran las filas, esta es la información más útil

Análisis de consultas

Hubiera publicado analíticas para la primera y la tercera consulta, pero ambas son consultas bastante simples. Aquí está el desglose de la consulta que le da problemas:

EXPLAIN SELECT cf.*, ctyp.typeName, cfv.id as customId, cfv.customFieldId,
cfv.relatedId, cfv.fieldValue, cfv.createdAt
FROM `CustomField` `cf`
INNER JOIN CustomType ctyp on ctyp.id = cf.customTypeId
LEFT OUTER JOIN CustomValue cfv on cf.id = cfv.customFieldId
and relatedId = 0
LEFT JOIN CustomFieldSubArea cfsa on cfsa.customFieldId = cf.id
WHERE ((relatedTable = ''people'' and enabled = ''1'')
AND (onCreate = ''1''))
AND (cfsa.subarea=''peoplebulkinsert'')
ORDER BY cf.sortOrder, cf.label

• INNER JOIN CustomType ctyp en ctyp.id = cf.customTypeId
• LEFT OUTER JOIN CustomValue cfv en cf.id = cfv.customFieldId y relatedId = 0
• IZQUIERDA ÚNETE CustomFieldSubArea cfsa en cfsa.customFieldId = cf.id
• DÓNDE (( relatedTable = ''personas'' y enabled = ''1'') AND ( onCreate = ''1'')) Y ( cfsa.subarea = ''peoplebulkinsert'')
• ORDEN POR cf.sortOrder , cf.label

Solución

Déjame explicarte la lista anterior. Las columnas en negrita deben tener un índice. Unirse a las tablas es una operación costosa que de lo contrario debe pasar por todas las filas de ambas tablas. Si haces un índice en las columnas enlazables, el motor de DB encontrará mucho más rápido y una mejor manera de hacerlo. Esta debería ser una práctica común para cualquier base de datos

Las columnas en cursiva no son obligatorias para tener índice, pero si tiene una gran cantidad de filas (20 000 es una cantidad grande) también debe tener un índice en las columnas que usa para buscar, puede que no tenga tal impacto en la velocidad de procesamiento, pero vale la pena el tiempo extra.

Entonces, necesita agregar indicios a estas columnas

• CustomType - id
• CustomField - customTypeId, id, relatedTable, enabled, onCreate, sortOrder, label
• CustomValue - customFieldId
• CustomFieldSubArea - customFieldId, subárea

Para verificar los resultados intente ejecutar declaración de explicación nuevamente después de agregar indicios (y posiblemente otras pocas consultas de selección / inserción / actualización). La columna adicional debería decir algo como "Usar índice" y la columna de "posibles claves" debería enumerar las claves utilizadas (incluso dos o más consultas por combinación).

Nota al margen: tiene algunos errores ortográficos en su código, debe corregirlos en caso de que alguien más necesite trabajar en su código también: "reqruiteCount" como columna de tabla y "fileUplaod" como índice de matriz en su código de referencia.

Answer 5

verifique que puede tratar de reducir la consulta y verificar con el compilador en línea sql comprobar el período de tiempo y luego incluirlo en el proyecto.

Answer 6

Para mi trabajo, tengo que agregar diariamente un CSV con 524 columnas y 10k registros. Cuando intenté analizarlo y agregar el registro con php, fue horrible.

Entonces, le propongo ver la documentación sobre LOAD DATA LOCAL INFILE

Copio / pasé mi propio código, por ejemplo, pero lo adapté a sus necesidades

$dataload = ''LOAD DATA LOCAL INFILE "''.$filename.''"
REPLACE
INTO TABLE ''.$this->csvTable.'' CHARACTER SET "utf8"
FIELDS TERMINATED BY "/t"
IGNORE 1 LINES
'';
$result = (bool)$this->db->query($dataload);

Donde $ filename es una ruta local de su CSV (puede usar dirname(__FILE__) para obtenerlo)

Este comando SQL es muy rápido (solo 1 o 2 segundos para agregar / actualizar todo el CSV)

EDITAR: lea el documento, pero, por supuesto, debe tener un índice uniq en su tabla de usuario para trabajos de "reemplazo". Por lo tanto, no necesita verificar si el usuario existe o no. Y no es necesario analizar el archivo CSV con php.

Answer 7

Hacer siempre importación masiva dentro de una transacción

 $transaction = Yii::app()->db->beginTransaction();
$curRow = 0;
try
{
while (($peopleData = fgetcsv($handle, 10240, ",")) !== FALSE) {
$curRow++;
//process $peopleData
//insert row
//best to use INSERT ... ON DUPLICATE KEY UPDATE
// a = 1
// b = 2;
if ($curRow % 5000 == 0) {
$transaction->commit();
$transaction->beginTransaction();
}
}
catch (Exception $ex)
{
$transaction->rollBack();
$result = $e->getMessage();
}
//don''t forget the remainder.
$transaction->commit();

He visto cómo las rutinas de importación se aceleraron un 500% simplemente usando esta técnica. También he visto un proceso de importación que hizo 600 consultas (mezcla de seleccionar, insertar, actualizar y mostrar la estructura de la tabla) para cada fila. Esta técnica aceleró el proceso 30%.

Answer 8

Pruebe estas cosas para aumentar el rendimiento de su consulta:

• defina la indexación en la estructura de su base de datos y obtenga solo las columnas que desee.
• No use * en la consulta de selección.
• Y no coloque los identificadores entre comillas como User.id=''51394'' , sino User.id= 51394 .
• Si está proporcionando identificadores entre comillas, su indexación no funcionará. Ese enfoque mejora el rendimiento de su consulta en un 20% más rápido.
• Si está utilizando ENGINE=MyISAM y no puede definir la indexación entre su tabla de base de datos, cambie el motor de la base de datos a ENGINE=InnoDB . Y crea algunos índices como claves externas, indización de texto completo.