mysql 5.7 慢_在中等SQL中，MySQL 5.7比MySQL 5.6慢得多

We are upgrading to mysql 5.7 and just discover that it is much slower than its 5.6 counter part. While both have almost identical config, the 5.6 version execute most of the sqls in milliseconds, while the other takes around 1 sec or more for a middle complex sql like the one below for example.

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

Both tables are pretty simple and well indexed:

-- ----------------------------

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

The tables have around 100K and 1K rows respectively. I noticed two interesting behaviors that I would like to "fix" :

Removing ORDER BY bring the exec time from ~1.2 sec to 0.0015 sec !!

The sql is not cached by mysql 5.7

Note: we do have cache query :

SHOW STATUS LIKE '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

I googled and found out many issues reported on 5.7 but don't get why this strange behaviors on this sql (still plenty of other sqls that run much slower on 5.7).

Here is the EXPLAIN, suggested by 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;

解决方案

The INNER JOIN user user_table_alias ON user_table_alias.id = u.id looks useless. It only joins against itself and that technique is not used in the rest of the query.

There is no index on emailVerify. Which is indicated by the first row of the EXPLAIN. ('using where' means no index is used)

This query does not scale well with the size of the table, because the full table must be looked at before delimiting what 'recent users' are. So probably some internal buffer used by myisam is overflowed now.

That is what 'using temporary' means. Using filesort means the order by is so big it uses a tempfile, which is bad for performance.