在中等sql中，mysql 5.7比mysql 5.6慢得多 [英] mysql 5.7 is much slower than mysql 5.6 in medium sql

问题描述

我们正在升级到mysql 5.7，只是发现它比5.6计数器要慢得多.虽然两者的配置几乎相同，但5.6版本以毫秒为单位执行大多数sql，而对于中等复杂的sql(例如下面的sql)，另一个版本大约需要1秒钟或更长时间.

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;

这些表分别具有约100K和1K的行.我注意到了两个我想修复"的有趣行为:

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

1. 删除ORDER BY可将执行时间从〜1.2秒缩短至0.0015秒！
2. mysql 5.7不缓存sql

注意:我们确实有缓存查询:

Note: we do have cache query :

类似于"Qcache％"的显示状态

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

我用Google搜索并发现了5.7中报告的许多问题，但不明白为什么此sql出现这种奇怪的行为(仍然有很多其他的sql在5.7上运行得慢得多).

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

这是Neville K建议的解释:

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;

推荐答案

INNER JOIN user user_table_alias ON user_table_alias.id = u.id 看起来没用.它只与自身连接，在其余的查询中未使用该技术.

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.

emailVerify上没有索引.由EXPLAIN的第一行指示. (在哪里使用"表示不使用索引)

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

此查询不能很好地适应表的大小，因为在界定最近的用户"之前，必须先查看整个表.因此，myisam使用的某些内部缓冲区现在可能已溢出. 这就是使用临时"的意思.使用filesort表示顺序很大，因此使用了tempfile，这对性能不利.

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.

这篇关于在中等sql中，mysql 5.7比mysql 5.6慢得多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！