mysql 5.7 es mucho más lento que mysql 5.6 en sql mediano
Estamos actualizando a mysql 5.7 y descubrimos que es mucho más lento que su contraparte 5.6. Si bien ambos tienen una configuración casi idéntica, la versión 5.6 ejecuta la mayoría de los sqls en milisegundos, mientras que el otro toma alrededor de 1 segundo o más para un sql complejo medio como el siguiente, por ejemplo.
-- Getting most recent users that are email-verified and not banned
SELECT
`u`.*
FROM
`user` AS `u`
INNER JOIN `user` user_table_alias ON user_table_alias.`id` = `u`.`id`
LEFT JOIN `user_suspend` user_suspend_table_alias ON user_suspend_table_alias.`userId` = `user_table_alias`.`id`
WHERE
(
`user_suspend_table_alias`.`id` IS NULL
)
AND
`user_table_alias`.`emailVerify` = 1
ORDER BY
`u`.`joinStamp` DESC
LIMIT 1, 18
Ambas tablas son bastante simples y están bien indexadas:
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(128) NOT NULL DEFAULT '',
`username` varchar(32) NOT NULL DEFAULT '',
`password` varchar(64) NOT NULL DEFAULT '',
`joinStamp` int(11) NOT NULL DEFAULT '0',
`activityStamp` int(11) NOT NULL DEFAULT '0',
`accountType` varchar(32) NOT NULL DEFAULT '',
`emailVerify` tinyint(2) NOT NULL DEFAULT '0',
`joinIp` int(11) unsigned NOT NULL,
`locationId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `username` (`username`),
KEY `accountType` (`accountType`),
KEY `joinStamp` (`joinStamp`),
KEY `activityStamp` (`activityStamp`)
) ENGINE=MyISAM AUTO_INCREMENT=89747 DEFAULT CHARSET=utf8 COMMENT='utf8_general_ci';
-- ----------------------------
-- Table structure for user_suspend
-- ----------------------------
DROP TABLE IF EXISTS `user_suspend`;
CREATE TABLE `user_suspend` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) DEFAULT NULL,
`timestamp` int(11) DEFAULT NULL,
`message` text NOT NULL,
`expire` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `userId` (`userId`)
) ENGINE=MyISAM AUTO_INCREMENT=513 DEFAULT CHARSET=utf8;
Las tablas tienen alrededor de 100K y 1K filas respectivamente. Noté dos comportamientos interesantes que me gustaría "arreglar":
¡Eliminar ORDER BY hace que el tiempo de ejecución sea de ~ 1.2 segundos a 0.0015 segundos!Mysql 5.7 no almacena en caché el sqlNota: tenemos consulta de caché:
MOSTRAR ESTADO COMO 'Qcache%'
Qcache_free_blocks 19408
Qcache_free_memory 61782816
Qcache_hits 31437169
Qcache_inserts 2406719
Qcache_lowmem_prunes 133483
Qcache_not_cached 43555
Qcache_queries_in_cache 41691
Qcache_total_blocks 103951
Busqué en Google y descubrí muchos problemas reportados en 5.7, pero no entiendo por qué este comportamiento extraño en este sql (todavía hay muchos otros sqls que se ejecutan mucho más lentamente en 5.7).
Aquí está el EXPLICAR, sugerido por Neville K:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user_table_alias NULL ALL PRIMARY NULL NULL NULL 104801 10.00 Using where; Usingtemporary; Usingfilesort
1 SIMPLE u NULL eq_ref PRIMARY PRIMARY 4 knn.base_user_table_alias.id 1 100.00 NULL
1 SIMPLE user_suspend_table_alias NULL ref userId userId 5 knn.base_user_table_alias.id 1 10.00 Using where;