将字符集设置为utf8mb4会使查询非常慢

我已经调试了几个小时,非常感谢您提供帮助以查看是否遗漏了非常明显的内容。我有一个具有30k行的MySQL表。该架构如下:

CREATE TABLE `log` (
  `ip` varchar(15) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `date` int(11) NOT NULL,
  `requested` mediumtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `response` mediumtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `id` bigint(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

In phpmyadmin, when I query for SELECT ip, date, requested FROM log ORDER BY date DESC LIMIT 0, 1000, the query takes a few ms. However, in my PHP code, when I do the same query:

$mysqli = new mysqli(...);

$s = microtime(true);
$mysqli->set_charset('utf8mb4');
$query = $mysqli->prepare('SELECT ip, date, requested FROM log ORDER BY date DESC LIMIT 0, 1000');
$query->execute();
$query->bind_result($ip, $date, $requested);
while($query->fetch()) {
  // ... just echoing the results
}

$e = microtime(true);
echo $e - $s;

It takes 6-7 seconds. The column id is the primary key, and indexes are set to PRIMARY and date.

一些观察和调试步骤:

  • If I remove the set_charset, it takes 1.5 seconds.

  • If I keep the utf8mb4 charset but only select ip and date, the query finishes in ~0.002 seconds.

  • I searched online for solutions, and e.g. this solution did not make any difference.

  • EXPLAIN indicates that possible_keys is null, which is odd because it seems like date should be used as the index.

  • This was working fine before I changed the collation to utf8mb4_unicode_520_ci -- it was utf8_unicode before.

我想念什么?我如何才能将此查询加速到合理的水平?提前非常感谢您!! :)

评论