主页 / dba / 问题 / 267540
Accepted
Peter VARGA
Asked: 2020-05-21 07:38:49 +0800 CST

MyISAM 比 InnoDB 慢约 5 倍

  • 772

我认为，MyISAM由于其架构比InnoDB. 因此，我将此表的引擎更改InnoDB为MyISAM：

CREATE TABLE `table1` (
  `DateTime` datetime NOT NULL,
  `BidHigh` decimal(11,5) NOT NULL,
  `BidLow` decimal(11,5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='1 minute rates';

ALTER TABLE `table1` ADD PRIMARY KEY (`DateTime`);

这些条件适用：

  1. 我正在一台 SLES 15.1 VM 上测试它，该 VM 在我的主机上具有 5 GB RAM 和 8 个 CPU 内核，并且它不必为其他 VM 提供服务[只是一个信息，表明没有其他 VM 可能会影响结果]。
  2. 一个 PHP 脚本正在运行数以千计的以下 SELECT 语句。
  3. 数据库中有 24 个上述表可以在 PHP 脚本中访问。
  4. 每个表有约 800k 行。
  5. 在每次测试之间，我都会重新启动服务器以确保始终存在相同的条件。由于差异如此之大，我没有运行多次测试来获得平均值......

执行时间：

  • 使用InnoDB引擎，脚本运行 199 秒。
  • 使用MyISAM引擎，脚本运行 1'026 秒。长5倍以上。

我正在运行这些 SELECT 语句：

SELECT `DateTime` FROM table1
     WHERE `DateTime` BETWEEN '2018-12-27 07:50:00' AND '2199-12-31 23:59:00'
        AND BidHigh > 0.96604
     ORDER BY `DateTime` LIMIT 1;

- 和 - 

SELECT MIN( BidLow ) FROM table1
    WHERE `DateTime` BETWEEN '2018-12-27 07:45:00' AND '2199-12-31 23:59:00';

好的，我发现这是一个索引问题。添加这两个索引

ALTER TABLE `table1` ADD UNIQUE `BidHigh` (`DateTime`, `BidHigh`);

- 和 - 

ALTER TABLE `table1` ADD UNIQUE `BidLow` (`DateTime`, `BidLow`);

修复了性能问题，现在脚本需要 245 秒，仍然慢于InnoDB- 这不是我所期望的......

将这些索引添加到InnoDB版本不会提高性能。

我的问题：

  1. 为什么InnoDB不需要这些索引并且仍然更快？
  2. 有更好的解决方案吗？
  3. 而且，我显然完全误解了MyISAM导致如此可怕表现的变化。
mysql performance

3 个回答

  1. Best Answer

    1) InnoDB 在索引方面也会更快。

    2）具有适当索引的 InnoDB 是最好的解决方案。

    3) 十多年来，对于大多数工作负载，MyISAM 都比 InnoDB 慢。两者之间的内存和缓存工作方式存在根本差异。

    在这种情况下，InnoDB 通过主键选择第一行。由于 InnoDB 表是按主键聚集的，因此速度非常快，并且 PK 在您创建表时可能已经在内存中。

    在 BidHigh 中创建一个索引，它会更快。

    除非你有充分的理由使用 MyISAM - 你不应该。如果你认为你有一个非常好的理由，你应该重新审视它，因为它们在 2020 年将消失得无影无踪。

    • 2

  2. “MyISAM 更好......”是一个非常过时的古老“妻子的故事”。使用 InnoDB。

    这两个引擎使用索引的方式完全不同。

    PRIMARY KEY ( DateTime) -- 我希望您不要尝试在同一秒内存储两条记录。PK 是独一无二的。

    查询 1

    SELECT `DateTime` FROM table1
     WHERE `DateTime` BETWEEN '2018-12-27 07:50:00' AND '2199-12-31 23:59:00'
        AND BidHigh > 0.96604
     ORDER BY `DateTime` LIMIT 1;

    由于这涉及两个范围，因此基本上不可能为 MyISAM 或 InnoDB 构建一个好的索引。优化器将使用以 开头的索引DateTime，然后测试另一列的所有行。让我们研究一下可能的索引：

    PRIMARY KEY(DateTime)

    对于 MyISAM，有一个基于 DateTime 的 BTree，外加一个指向数据行的指针。它将查看数据行以获取BidHigh以检查其值。

    对于 InnoDB，数据按日期时间排序。因此没有额外的获得 BidHigh。获胜者：InnoDB。

    在任一引擎中，优化器都可能足够聪明，可以避免排序并获得LIMIT. 但这是有风险的，因为它取决于需要测试多少行。由于数据的这种变化，您可以很容易地看到由于选择的查询计划而导致5 倍（甚至 500 倍）的减速。索引（日期时间，BidHigh）

    这通过使其成为“覆盖”索引来解决 MyISAM 的低效率问题。对于 InnoDB 来说，这主要是一种浪费；PK 本质上是一个INDEX(DateTime, BidHigh, BidLow)，它只比那个 2 列索引稍差一点。

    INDEX(BidHigh, DateTime)

    如果与BidHign​​上的范围测试匹配的行数比 DateTime 上的范围测试少得多，这可能会更快。但是在到达之前会有一个排序LIMIT。

    用来EXPLAIN SELECT ...看看它做了什么。

    也许空间

    第一个查询需要一个二维索引，这不是INDEX给你的。我讨论了5个选项，用“纬度/经度”术语表达：http: //mysql.rjweb.org/doc.php/find_nearest_in_mysql

    对于第一个查询，使用SPATIAL可能是可行的，但对于第二个查询可能不可行。

    查询 2

    SELECT MIN( BidLow ) FROM table1
    WHERE `DateTime` BETWEEN '2018-12-27 07:45:00' AND '2199-12-31 23:59:00';

    对于 InnoDB： PRIMARY KEY(DateTime)导致扫描大约一年的数据。

    对于 MyISAM，我怀疑它会使用索引，除非它完全INDEX(DateTime, BidLow)是“覆盖”。

    23:59:00

    你假设在一天的最后一分钟没有出价？

    考虑使用

    WHERE DateTime >= '...
  AND DateTime  < '...-01-01'
    • 2

  3. 我正在添加我的评论作为答案，因为评论字段的长度不够长。

    我接受了Gordan Bobic的回答，因为他是新成员，我认为他因接受他的回答而赢得的声誉正在推动他。

    由于他的一些陈述在Rick James的回答中得到证实，这表明Gordan Bobic了解正在发生的事情。

    戈登的声明：

    除非你有充分的理由使用 MyISAM - 你不应该。如果你认为你有一个非常好的理由，你应该重新审视它，因为它们在 2020 年将消失得无影无踪。

    对我来说是一个非常重要的触发因素，因为我意识到我的理解是错误的，我不得不重新考虑使用过的引擎。

    我没有提到整个问题，因为它不适合一个问题。在阅读了两个答案后，我意识到我必须重建数据库。因此，例如，我在某些表中有一个JSON字段 [最大长度约为 6k 字节，平均长度约为 2k 字节] 。InnoDB

    由于行数很高，这些InnoDB表的大小也很高，并且每天都在增长。这就是我开始将这些InnoDB表转换为MyISAM. 但是，如前所述，这大大降低了性能，因此我问了这个问题。

    我将这些字段提取JSON到一个非常简单的MyISAM表中，该表只有两个字段[主键和JSON字段]。这减小了大小 [~30%] 并且不影响性能。

    也许我现在听起来有点混乱，但在整个上下文中它帮助了我很多并解决了问题！

    • 1

相关问题