MySQL 5.7 намного медленнее, чем MySQL 5.6 в среднем SQL

Question

MySQL 5.7 намного медленнее, чем MySQL 5.6 в среднем SQL

Мы обновляем MySQL до 5.7 и просто обнаруживаем, что он намного медленнее, чем его 5.6 контрагент. В то время как оба имеют почти идентичный конфиг, версия 5.6 выполняет большую часть sqls в миллисекундах, в то время как другая занимает около 1 секунды или более для среднего сложного sql, такого как приведенный ниже, например.

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

Обе таблицы довольно просты и хорошо проиндексированы:

-- ----------------------------
-- 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 строк соответственно. Я заметил два интересных поведения, которые я хотел бы «исправить»:

Удаление ORDER BY увеличивает время выполнения с ~ 1,2 до 0,0015 сек !!
Sql не кешируется mysql 5.7

Примечание: у нас есть запрос кеша:

ПОКАЗАТЬ СТАТУС, КАК ‘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

Я погуглил и обнаружил много проблем, о которых сообщалось в 5.7, но не понимаю, почему это странное поведение на этом sql (все еще много других sqls, которые работают намного медленнее на 5.7).

Вот ОБЪЯСНЕНИЕ, предложенное Невиллом К:

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;

6

android mysql php query-cache

Решение

Другие решения

Самостоятельное соединение выглядит излишним.

Я думаю, что вы можете переписать запрос следующим образом:

SELECT
`u`.*
FROM
`user` AS `u`
LEFT JOIN `user_suspend` user_suspend_table_alias ON user_suspend_table_alias.`userId` = `u`.`id`
WHERE    `user_suspend_table_alias`.`id` IS NULL
AND      `u`.`emailVerify` = 1
ORDER BY
`u`.`joinStamp` DESC
LIMIT 1, 18

Я предполагаю, что «emailVerify» — это столбец с несколькими значениями (0 и 1), поэтому его не следует индексировать. Я также предполагаю, что «joinStamp» является своего рода меткой времени (хотя тип данных целочисленный). Если это правда, вы можете создать индекс, чтобы ускорить это.

create index id_joinstamp on user (id, joinstamp)

1

Хорошо, спасибо NevilleK на Explain.

Я понял, как просто исправить этот SQL:

 user_table_alias.emailVerify = 1

в

u.emailVerify = 1

Я не знаю почему, но в MySQL5.6 оба выполняются в миллисекундах.

Я думаю, мне придется пересмотреть все SQL (от других разработчиков), благодаря обратному улучшению MySQL

1

В качестве обходного пути вы можете попробовать использовать ключевое слово STRAIGHT_JOIN после первого выбора. Это ключевое слово заставляет mysql присоединяться слева направо (MYSQL — Что делает STRAIGHT_JOIN в этом коде?).

0

Источник

Accepted Answer

INNER JOIN user user_table_alias ON user_table_alias.id знак равно u,id[/ NOEDIT] выглядит бесполезным. Он объединяется только против самого себя, и этот метод не используется в оставшейся части запроса.

Там нет индекса на emailVerify, Что указано в первом ряду Объяснения. («использование где» означает, что индекс не используется)

Этот запрос плохо масштабируется с размером таблицы, потому что перед тем, как разграничивать, что такое «недавние пользователи», необходимо просмотреть полную таблицу. Поэтому, возможно, некоторый внутренний буфер, используемый myisam, теперь переполнен.
Вот что означает «использование временных». Использование сортировки файлов означает, что упорядочение по настолько велико, что он использует временный файл, что ухудшает производительность.

2