首页 文章

MyISAM与InnoDB [关闭]

提问于
浏览 次
825

我说_（71％插入和30％读取） . 该比率还包括我认为是一次读取和一次写入的更新 . 读取可能很脏（例如，在读取时我不需要100％准确的信息） .
有问题的任务将是每小时进行超过100万次数据库事务 .

我在网上看到了很多关于MyISAM和InnoDB之间差异的东西，对于我将用于此任务的特定数据库/表格来说，MyISAM似乎是明显的选择 . 从我似乎正在阅读的内容来看，如果需要事务处理，InnoDB很好，因为支持行级锁定 .

有没有人有这种负载（或更高）的经验？ MyISAM是走的路吗？

mysql database performance innodb myisam

25 回答

  • 0

    我试图将随机数据插入到MyISAM和InnoDB表中 . 结果令人震惊 . 插入100万行的MyISAM比InnoDB只需要几秒钟，只需要1万！

    回复于
  • 11

    对于具有更多写入和读取的负载，您将受益于InnoDB . 因为InnoDB提供了行锁定而不是表锁定，所以 SELECT 可以是并发的，不仅是彼此之间，而且也是许多 INSERT . 但是，除非您打算使用SQL事务，否则请将InnoDB提交刷新设置为2（innodb_flush_log_at_trx_commit） . 这样可以恢复将表从MyISAM移动到InnoDB时可能会丢失的大量原始性能 .

    另外，请考虑添加复制 . 这为您提供了一些读取扩展，因为您声明您的读取不必是最新的，您可以让复制落后一点 . 只要确保它可以赶上任何东西，但最重的流量，或它将永远落后，永远不会赶上 . 但是，如果你采用这种方式，我强烈建议你将从机的读取和复制滞后管理与数据库处理程序隔离开来 . 如果应用程序代码不知道这一点，那就简单多了 .

    最后，请注意不同的表加载 . 所有表都不会具有相同的读/写比率 . 一些具有接近100％读数的小表可以保留MyISAM . 同样，如果您有一些接近100％写入的表，您可能会受益于 INSERT DELAYED ，但这仅在MyISAM中受支持（InnoDB表会忽略 DELAYED 子句） .

    但基准确定 .

    回复于
  • 32

    对于读/写的比例，我猜InnoDB会表现得更好 . 由于您对脏读操作很好，您可能（如果您负担得起）复制到从属设备并让所有读取都转到从属设备 . 另外，考虑批量插入，而不是一次插入一条记录 .

    回复于
  • 62

    我已经发现即使Myisam有锁定争用，在大多数情况下它仍然比InnoDb更快，因为它使用了快速锁定采集方案 . 我曾经多次试过Innodb并且因为某种原因总是回到MyIsam . 此外，InnoDB在巨大的写入负载中可能非常占用CPU .

    回复于
  • 24

    Please note 我的正式教育和经验是在甲骨文，而我在MySQL工作完全是个人的，并且在我自己的时间，所以如果我说的是对甲骨文来说真实的东西但对MySQL不是真的，我道歉 . 虽然这两个系统共享很多，但关系理论/代数是相同的，关系数据库仍然是关系数据库，仍然存在很多差异！

    我特别喜欢（以及行级锁定）InnoDB是基于事务的，这意味着您可能会为Web应用程序的一个"operation"多次更新/插入/创建/更改/删除/ etc . 出现的问题是，如果只有一些更改/操作最终被提交，而其他更改/操作没有提交，那么大多数时候（取决于数据库的特定设计）最终会得到一个数据/结构冲突的数据库 .

    Note: 使用Oracle，create / alter / drop语句称为"DDL"（数据定义）语句，并隐式触发提交 . 插入/更新/删除语句（称为"DML"（数据操作））不会自动提交，但仅在执行DDL，提交或退出/退出时（或者如果将会话设置为"auto-commit"，或者客户端自动提交） ） . 它's imperative to be aware of that when working with Oracle, but I am not sure how MySQL handles the two types of statements. Because of this, I want to make it clear that I'我对MySQL不确定;仅限Oracle .

    基于事务的引擎擅长的一个例子：

    假设我或者您在网页上注册参加免费活动，系统的主要目的之一是只允许最多100人注册，因为这是座位的限制为了这个活动 . 一旦达到100次注册，系统将禁用进一步的注册，至少在其他人取消之前 .

    在这种情况下，可能有一个用于客人的表（姓名，电话，电子邮件等），以及一个跟踪已注册的客人数量的第二个表 . 因此，我们对一个“交易”有两个操作 . 现在假设在将客户信息添加到GUESTS表之后，存在连接丢失或具有相同影响的错误 . GUESTS表是更新（插入），但在“可用座位”可以更新之前连接已丢失 .

    现在我们将一个访客添加到访客表中，但现在可用座位数不正确（例如，当它实际为84时，值为85） .

    当然，有很多方法可以解决这个问题，例如使用"100 minus number of rows in guests table,"跟踪可用席位或者检查信息是否一致的某些代码等等 . 但是使用基于事务的数据库引擎（如InnoDB），要么所有操作都要承诺，或他们没有 . 这在许多情况下都会有所帮助，但就像我说的那样，它不是安全的唯一方式，不是（不过很好的方式，由数据库处理，而不是程序员/脚本编写者） .

    's all 291808 essentially means in this context, unless I'm遗漏了一些东西 - 要么整个交易成功应该，要么没有改变，因为只进行部分更改可能会导致数据库严重混乱，甚至可能破坏它......

    但我会再说一遍，这不是避免混乱的唯一方法 . 但它是引擎本身处理的方法之一，只需要担心“交易成功与否，以及如果没有（如重试）”，而不是手动，你只需要担心代码/脚本编写代码以从数据库外部“手动”检查它，并为此类事件做更多工作 .

    最后，关于表锁定与行锁定的说明：

    DISCLAIMER: 关于MySQL，关于MySQL的所有内容可能都是错误的，假设/示例情况是需要考虑的事情，但我可能错误地认为可能导致MySQL损坏 . 然而，这些例子在一般编程中是非常真实的，即使MySQL有更多的机制来避免这样的事情......

    无论如何，我非常有信心同意那些一直争论允许在锁定的 table 周围工作多少连接的人 . 事实上，多个连接 are the entire point of locking a table!! 因此，其他进程/用户/应用程序无法通过同时进行更改来破坏数据库 .

    如何在同一行上工作的两个或多个连接为你做一个非常糟糕的日子？假设有两个进程都想要/需要在同一行中更新相同的值，让我们说因为该行是一个公共汽车之旅的记录，并且这两个进程中的每一个同时想要更新“riders”或“available_seats”字段为“当前值加1” .

    让我们一步一步地假设这样做：

    • 进程1读取当前值，让's say it'为空，因此到目前为止'0' .

    • 进程2也读取当前值，该值仍为0 .

    • 进程1写入（当前1）为1 .

    • 进程2应该写入2，但由于它在进程1之前读取当前值并写入新值，因此它也会向表中写入1 .

    我不确定两个连接是否可以混合在一起，在第一个连接之前读取...但如果没有，那么我仍然会看到一个问题：

    • 进程1读取当前值，即0 .

    • 进程1写入（当前1），即1 .

    • 进程2现在读取当前值 . 但是当进程一DID写入（更新）时，它没有提交数据，因此只有同一个进程可以读取它更新的新值，而所有其他进程都看到旧值，直到有提交 .

    此外，至少对于Oracle数据库，存在隔离级别，我不会浪费时间试图解释 . 这是一篇关于该主题的好文章，每个隔离级别都有它的优点和缺点，这将与基于事务的引擎在数据库中的重要性有关...

    最后，MyISAM中可能存在不同的安全措施，而不是外键和基于事务的交互 . 好吧，首先，有一个事实是整个表被锁定，这使得不太可能需要事务/ FK .

    唉，如果你意识到这些并发问题，是的，你可以更安全地玩它，只需编写你的应用程序，设置你的系统，以便不可能出现这样的错误（你的代码负责，而不是数据库本身） . 但是，在我看来，我会说最好尽可能多地使用安全措施，进行防御性编程，并始终意识到人为错误是不可能完全避免的 . 这种情况发生在每个人身上，任何说他们对它免疫的人都必须撒谎，或者除了写一个“Hello World”应用程序/脚本之外别无其辞 . ;-)

    我希望其中一些对某些人有帮助，甚至更多 - 所以，我希望我现在不仅仅是假设的罪魁祸首，而是一个错误的人！如果是这样，我表示道歉，但这些例子很好思考，研究风险，等等，即使它们在这种特定背景下不具潜力 .

    随意纠正我，编辑这个“答案”，甚至投票 . 请试着改进，而不是用另一个来纠正我的错误假设 . ;-)

    这是我的第一反应，所以请原谅所有免责声明的长度等...当我不完全确定时，我只是不想听起来很傲慢！

    回复于
  • 135

    稍微偏离主题，但出于文档目的和完整性，我想添加以下内容 .

    通常使用InnoDB会导致很多LESS复杂的应用程序，可能也会更加无bug . 因为您可以将所有引用完整性（外键约束）放入数据模型中，所以您不需要任何接近MyISAM所需的应用程序代码 .

    每次插入，删除或替换记录时，您都必须检查并维护关系 . 例如 . 如果删除父项，则也应删除所有子项 . 例如，即使在简单的博客系统中，如果删除博客记录，您也必须删除评论记录，喜欢等 . 在InnoDB中，这是由数据库引擎自动完成的（如果您在模型中指定了约束） ）并且不需要应用程序代码 . 在MyISAM中，必须将其编码到应用程序中，这在Web服务器中非常困难 . Web服务器本质上是非常并发/并行的，因为这些操作应该是原子的，并且MyISAM不支持真正的事务，因此使用MyISAM进行Web服务器是有风险/容易出错的 .

    同样在大多数情况下，由于多种原因，InnoDB的性能会更好，其中一个是能够使用记录级锁定而不是表级锁定 . 不仅在写入比读取更频繁的情况下，在大型数据集上具有复杂连接的情况下也是如此 . 我们注意到，通过在MyISAM表上使用InnoDB表进行非常大的连接（需要几分钟），性能提高了3倍 .

    我会说，一般来说InnoDB（使用3NF数据模型完整的参照完整性）应该是使用MySQL时的默认选择 . MyISAM只应用于非常具体的情况 . 它很可能会执行更少，导致更大，更多的错误应用程序 .

    说完这个 . 数据模型是webdesigners / -programmers中很少发现的艺术 . 没有冒犯，但它确实解释了MyISAM被如此使用 .

    回复于
  • 15

    人们经常谈论性能，读取与写入，外键等等，但在我看来，存储引擎还有另外一个必备功能： atomic updates.

    试试这个：

    • 针对您的MyISAM表发出一个需要5秒的UPDATE .

    • 当UPDATE正在进行时，比如说2.5秒，按Ctrl-C中断它 .

    • 观察桌面上的效果 . 更新了多少行？有多少人没有更新？表格是否可读，或者当您按下Ctrl-C时它是否已损坏？

    • 针对InnoDB表尝试使用UPDATE进行相同的实验，中断正在进行的查询 .

    • 观察InnoDB表 . 零行已更新 . InnoDB已确保您拥有原子更新，如果无法提交完整更新，则会回滚整个更改 . 此外，该表没有损坏 . 即使您使用 killall -9 mysqld 来模拟崩溃，这也有效 .

    当然，性能是可取的，但不会丢失数据应该胜过它 .

    回复于
  • 3

    我认为这是一篇关于解释差异以及何时应该使用其中一个的优秀文章：http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

    回复于
  • 1

    简而言之，如果您正在处理需要可以处理大量INSERT和UPDATE指令的可靠数据库的东西，InnoDB是很好的 .

    并且，如果你需要一个主要采用大量读取（SELECT）指令而不是写入（INSERT和UPDATES）的数据库，考虑到它在表锁定方面的缺点，MyISAM是很好的 .

    你可能想看看;
    Pros and Cons of InnoDB
    Pros and Cons of MyISAM

    回复于
  • 179

    如果是70％的插入和30％的读取，则更像是在InnoDB方面 .

    回复于
  • 264

    我使用MySQL工作的大容量系统，我已经尝试了MyISAM和InnoDB .

    我发现MyISAM中的表级锁定导致了我们工作负载的严重性能问题，这听起来与您的相似 . 不幸的是，我还发现InnoDB下的表现也比我希望的要糟糕 .

    最后，我通过对数据进行分段来解决争用问题，使得插入进入“热”表并选择从不查询热表 .

    这也允许删除（数据是时间敏感的，我们只保留X天值）在“陈旧”表上发生，这些表再次未被选择查询触及 . InnoDB似乎在批量删除方面表现不佳，因此如果您计划清除数据，您可能希望以旧数据处于陈旧表中的方式对其进行构造，这可以简单地删除而不是在其上运行删除 .

    当然我不知道你的应用程序是什么，但希望这能让你深入了解MyISAM和InnoDB的一些问题 .

    回复于
  • 31

    myisam对于那种类型的工作负载是一个NOGO（高并发写入），我没有那么多使用innodb的经验（测试了3次并且在每种情况下发现性能很差，但是自上次测试以来已经有一段时间了）如果你'不是被迫运行mysql，考虑给postgres一个尝试，因为它更好地处理并发写入

    回复于
  • 5

    我在表格中简要地提到了这个问题，因此您可以得出结论是否与 InnoDB 或 MyISAM 一起使用 .

    以下是您应该在哪种情况下使用哪个数据库存储引擎的小概述：

    MyISAM   InnoDB
----------------------------------------------------------------
Required full-text search                        Yes      5.6.4
----------------------------------------------------------------
Require transactions                                      Yes
----------------------------------------------------------------
Frequent select queries                          Yes      
----------------------------------------------------------------
Frequent insert, update, delete                           Yes
----------------------------------------------------------------
Row locking (multi processing on single table)            Yes
----------------------------------------------------------------
Relational base design                                    Yes

    总结一下：

    Frequent reading, almost no writing   => MyISAM
Full-text search in MySQL <= 5.5      => MyISAM

    在所有其他情况下， InnoDB 通常是最好的方式 .

    回复于
  • 4

    游戏有点晚......但这里有一个非常全面的post I wrote a few months back，详细说明了MYISAM和InnoDB之间的主要区别 . 拿一杯茶（也许是一块 Cookies ），享受吧 .

    MyISAM和InnoDB之间的主要区别在于引用完整性和事务 . 还有其他差异，例如锁定，回滚和全文搜索 .

    参照完整性

    参照完整性可确保表之间的关系保持一致 . 更具体地说，这意味着当表（例如，列表）具有指向不同表（例如，产品）的外键（例如，产品ID）时，当指向的表发生更新或删除时，这些更改被级联到链接表 . 在我们的示例中，如果重命名产品，链接表的外键也将更新;如果从“产品”表中删除产品，则任何指向已删除条目的列表也将被删除 . 此外，任何新的列表必须具有指向有效的现有条目的外键 .

    InnoDB是一个关系型DBMS（RDBMS），因此具有参照完整性，而MyISAM则没有 .

    交易与原子性

    表中的数据使用数据操作语言（DML）语句进行管理，例如SELECT，INSERT，UPDATE和DELETE . 一个事务组将两个或多个DML语句一起组成一个单独的工作单元，因此要么应用整个单元，要么都不应用 .

    MyISAM不支持InnoDB的交易 .

    如果在使用MyISAM表时操作被中断，则操作立即中止，受影响的行（甚至每行中的数据）仍会受到影响，即使操作未完成也是如此 .

    如果在使用InnoDB表时操作被中断，因为它使用具有原子性的事务，任何未完成的事务都不会生效，因为没有提交 .

    表锁定与行锁定

    当查询针对MyISAM表运行时，它将查询的整个表将被锁定 . 这意味着后续查询只会在当前查询完成后执行 . 如果您正在阅读大型表，和/或频繁的读写操作，这可能意味着大量的查询积压 .

    当查询针对InnoDB表运行时，仅锁定所涉及的行，表的其余部分仍然可用于CRUD操作 . 这意味着查询可以在同一个表上同时运行，前提是它们不使用同一行 .

    InnoDB中的此功能称为并发 . 与并发性一样，存在一个主要的缺点，适用于选择范围的表，因为在内核线程之间切换存在开销，并且您应该对内核线程设置限制以防止服务器停止 .

    交易和回滚

    在MyISAM中运行操作时，将设置更改;在InnoDB中，可以回滚这些更改 . 用于控制事务的最常用命令是COMMIT，ROLLBACK和SAVEPOINT . 1. COMMIT - 您可以编写多个DML操作，但只有在进行COMMIT时才会保存更改.2 . ROLLBACK - 您可以放弃尚未提交的任何操作3. SAVEPOINT - 在列表中设置一个点ROLLBACK操作可以回滚到的操作

    可靠性

    MyISAM不提供数据完整性 - 硬件故障，不干净的关闭和取消的操作可能导致数据损坏 . 这将需要完全修复或重建索引和表 .

    另一方面，InnoDB使用事务日志，双写缓冲区以及自动校验和验证来防止损坏 . 在InnoDB进行任何更改之前，它会将事务之前的数据记录到名为ibdata1的系统表空间文件中 . 如果发生崩溃，InnoDB将通过重播这些日志进行自动恢复 .

    FULLTEXT索引

    在MySQL 5.6.4版之前，InnoDB不支持FULLTEXT索引 . 截至本文撰写时，许多共享主机提供商的MySQL版本仍然低于5.6.4，这意味着InnoDB表不支持FULLTEXT索引 .

    但是，这不是使用MyISAM的正当理由 . 最好转换为支持最新MySQL版本的托管服务提供商 . 并非使用FULLTEXT索引的MyISAM表无法转换为InnoDB表 .

    结论

    总之，InnoDB应该是您选择的默认存储引擎 . 在满足特定需求时选择MyISAM或其他数据类型 .

    回复于
  • 1

    还可以看看一些插件MySQL本身的替代品：

    MariaDB

    http://mariadb.org/

    MariaDB是一个数据库服务器，为MySQL提供直接替换功能 . MariaDB由MySQL的一些原创作者构建，并得到更广泛的免费和开源软件开发人员社区的帮助 . 除了MySQL的核心功能外，MariaDB还提供了丰富的功能增强功能，包括备用存储引擎，服务器优化和补丁 .

    Percona Server

    https://launchpad.net/percona-server

    MySQL的增强型替代产品，具有更好的性能，改进的诊断功能和附加功能 .

    回复于
  • 5

    每个应用程序都有自己的使用数据库的性能配置文件，并且它可能会随着时间的推移而发生变化 .

    你能做的最好的事情就是测试你的选择 . 在MyISAM和InnoDB之间切换是微不足道的，因此在您的站点上加载一些测试数据和消防jmeter，看看会发生什么 .

    回复于
  • 4

    InnoDB提供：

    ACID transactions
row-level locking
foreign key constraints
automatic crash recovery
table compression (read/write)
spatial data types (no spatial indexes)

    在InnoDB中，除TEXT和BLOB之外的所有数据最多可占用8,000个字节 . InnoDB没有全文索引 . 在InnoDB中，COUNT（*）s（当不使用WHERE，GROUP BY或JOIN时）执行速度比MyISAM慢，因为行计数不在内部存储 . InnoDB将数据和索引存储在一个文件中 . InnoDB使用缓冲池来缓存数据和索引 .

    MyISAM提供：

    fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
full text indexing
smaller disk footprint
very high table compression (read only)
spatial data types and indexes (R-tree)

    MyISAM具有表级锁定，但没有行级锁定 . 没有交易 . 没有自动崩溃恢复，但它确实提供了修复表功能 . 没有外键约束 . 与InnoDB表相比，MyISAM表在磁盘上的大小通常更紧凑 . 如果需要，可以通过使用myisampack进行压缩来进一步大大减小MyISAM表的大小，但是变为只读 . MyISAM将索引存储在一个文件中，数据存储在另一个文件中MyISAM使用密钥缓冲区来缓存索引，并将数据缓存管理留给操作系统 .

    总的来说，我会推荐InnoDB用于大多数用途，而MyISAM仅用于专门用途 . InnoDB现在是新MySQL版本的默认引擎 .

    回复于
  • 53

    我知道这不会受欢迎但是这里有：

    myISAM缺乏对数据库要素的支持，例如事务和参照完整性，这通常会导致错误/错误的应用程序 . 如果您的数据库引擎甚至不支持，那么您无法学习正确的数据库设计基础知识 .

    在数据库世界中不使用引用完整性或事务就像在软件世界中不使用面向对象的编程一样 .

    InnoDB现在存在，请使用它！尽管myISAM是所有遗留系统中默认的原始引擎，但即便是MySQL开发人员也最终承认将其更改为新版本的默认引擎 .

    如果你正在阅读或写作或者你有什么性能考虑因素没关系，使用myISAM可能会导致各种各样的问题，例如我刚刚遇到的问题：我正在执行数据库同步，同时还有其他人访问了一个访问设置为myISAM的表的应用程序 . 由于缺乏事务支持和此引擎的可靠性普遍较差，这使整个数据库崩溃，我不得不手动重启mysql！

    在过去15年的开发过程中，我使用了许多数据库和引擎 . 在此期间，myISAM在我身上撞了十几次，其他数据库只有一次！这是一个微软的SQL数据库，其中一些开发人员编写了错误的CLR代码（公共语言运行库 - 基本上是在数据库内部执行的C＃代码），顺便说一下，这并不是数据库引擎的错误 .

    我同意这里的其他答案，说 quality high-availability, high-performance applications should not use myISAM as it will not work, it is not robust or stable enough to result in a frustration-free experience. 有关详细信息，请参阅Bill Karwin的答案 .

    附：当myISAM狂热地投票但不能告诉你这个答案的哪一部分是不正确的时候，一定要喜欢它 .

    回复于
  • 1

    几乎每次我开始一个新项目时，我都会谷歌同样的问题，看看我是否想出任何新的答案 .

    它最终归结为 - 我采用最新版本的MySQL并运行测试 .

    我有表格，我想要进行键/值查找......这就是全部 . 我需要获取散列键的值（0-512字节） . 此DB上没有很多事务 . 该表偶尔会获得更新（完整地），但是0个事务 .

    所以我们不是在谈论一个复杂的系统，我们正在谈论一个简单的查找，以及如何（除了使表RAM驻留）我们可以优化性能 .

    我还对其他数据库（即NoSQL）进行测试，看看是否有任何可以获得优势的地方 . 我发现的最大优势在于键映射，但就查询而言，MyISAM目前正在全力以赴 .

    虽然，我不会使用MyISAM表执行金融交易，但是对于简单的查找，您应该测试它...通常是查询/秒的2倍到5倍 .

    测试一下，我欢迎辩论 .

    回复于
  • 61

    如果您使用MyISAM，则不会每小时执行 any 次事务，除非您将每个DML语句视为事务（在任何情况下，在发生崩溃时都不会是持久的或原子的） .

    因此我认为你必须使用InnoDB .

    每秒300笔交易听起来相当多 . 如果您绝对需要这些事务在电源故障时保持持久，请确保您的I / O子系统可以轻松地每秒处理这么多次写入 . 您至少需要一个带有电池备份缓存的RAID控制器 .

    如果你可以使用小的持久性命中，你可以使用InnoDB和innodb_flush_log_at_trx_commit设置为0或2（详见文档），你可以提高性能 .

    有许多补丁可以增加谷歌和其他人的并发性 - 如果没有它们仍然无法获得足够的性能，这些可能会引起人们的兴趣 .

    回复于
  • 2

    我不是数据库专家，我不会从经验中说话 . 然而：

    MyISAM tables use table-level locking . 根据您的流量估算值，您每秒接近200次写入 . 使用MyISAM，其中只有一个可以随时进行 . 您必须确保您的硬件可以跟上这些事务以避免被超出，即单个查询可能不会超过5毫秒 .

    这告诉我你需要一个支持行级锁定的存储引擎，即InnoDB .

    另一方面，编写一些简单的脚本来模拟每个存储引擎的负载应该是相当简单的，然后比较结果 .

    回复于
  • 11

    The Question and most of the Answers are out of date .

    是的，这是一个老太太的故事，MyISAM比InnoDB更快 . 注意问题的日期：2008;现在差不多十年了 . 从那以后，InnoDB取得了显着的业绩增长 .

    戏剧性的图表是MyISAM获胜的一个案例： COUNT(*) 没有 WHERE 条款 . 但这真的是你花时间做的吗？

    如果你运行并发测试，InnoDB很可能会赢，甚至对 MEMORY .

    如果在基准测试 SELECTs 时进行任何写入，MyISAM和 MEMORY 可能会因为表级锁定而丢失 .

    实际上，Oracle非常确定InnoDB能够更好地将它们从8.0中删除 .

    该问题是在5.1的早期编写的 . 从那时起，这些主要版本被标记为"General Availability"：

    • 2010：5.5（12月的.8）

    • 2013：5 . 5（ . 10月）

    • 2015：5.7（10月.9）

    • 2018：8.0（4月.11）

    Bottom line: Don't use MyISAM

    回复于
  • 1

    根据我的经验，MyISAM是一个更好的选择，只要你不做DELETE，UPDATE，大量单个INSERT，事务和全文索引 . 顺便说一下，CHECK TABLE太可怕了 . 由于表格在行数方面变得越来越老，您不知道它何时会结束 .

    回复于
  • 12

    bottomline：如果您在离线工作时选择大块数据，MyISAM可能会为您提供更好（更好）的速度 .

    在某些情况下，MyISAM比InnoDB效率更高：在离线操作大型数据转储时（因为表锁定） .

    示例：我正在从NOAA转换csv文件（15M记录），它使用VARCHAR字段作为键 . 即使有大量的内存可用，InnoDB仍然需要永远 .

    这是csv的一个例子（第一个和第三个字段是键） .

    USC00178998,20130101,TMAX,-22,,,7,0700
USC00178998,20130101,TMIN,-117,,,7,0700
USC00178998,20130101,TOBS,-28,,,7,0700
USC00178998,20130101,PRCP,0,T,,7,0700
USC00178998,20130101,SNOW,0,T,,7,

    因为我需要做的是运行观察天气现象的批量离线更新，我使用MyISAM表接收数据并在键上运行JOINS，以便我可以清理传入的文件并用INT键替换VARCHAR字段（这与存储原始VARCHAR值的外部表） .

    回复于
  • 506

    为了增加这里涵盖两个发动机之间机械差异的广泛选择，我提出了一个经验速度比较研究 .

    就纯速度而言，MyISAM并不总是比InnoDB更快，但根据我的经验，PURE READ工作环境的速度往往要快2.0-2.5倍 . 显然，这并不适合所有环境 - 正如其他人所写，MyISAM缺少交易和外键之类的东西 .

    我've done a bit of benchmarking below - I'使用python进行循环，使用timeit库进行时序比较 . 为了兴趣，我还包括内存引擎，虽然它只适用于较小的表（当你超过MySQL内存限制时不断遇到 The table 'tbl' is full ），但它提供了全面的最佳性能 . 我看的四种选择是：

    • 香草选择

    • 计数

    • 条件SELECT

    • 索引和非索引子选择

    首先，我使用以下SQL创建了三个表

    CREATE TABLE
    data_interrogation.test_table_myisam
    (
        index_col BIGINT NOT NULL AUTO_INCREMENT,
        value1 DOUBLE,
        value2 DOUBLE,
        value3 DOUBLE,
        value4 DOUBLE,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8

    用'MyISAM'替换第二和第三表中的'InnoDB'和'memory' .

    1）香草选择

    查询： SELECT * FROM tbl WHERE index_col = xx

    结果： draw

    Comparison of vanilla selects by different database engines

    这些的速度大致相同，并且如所预期的那样，要选择的列数是线性的 . InnoDB似乎比MyISAM略快，但这实际上是微不足道的 .

    码：

    import timeit
import MySQLdb
import MySQLdb.cursors
import random
from random import randint

db = MySQLdb.connect(host="...", user="...", passwd="...", db="...", cursorclass=MySQLdb.cursors.DictCursor)
cur = db.cursor()

lengthOfTable = 100000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)
    cur.execute(insertString3)

db.commit()

# Define a function to pull a certain number of records from these tables
def selectRandomRecords(testTable,numberOfRecords):

    for x in xrange(numberOfRecords):
        rand1 = randint(0,lengthOfTable)

        selectString = "SELECT * FROM " + testTable + " WHERE index_col = " + str(rand1)
        cur.execute(selectString)

setupString = "from __main__ import selectRandomRecords"

# Test time taken using timeit
myisam_times = []
innodb_times = []
memory_times = []

for theLength in [3,10,30,100,300,1000,3000,10000]:

    innodb_times.append( timeit.timeit('selectRandomRecords("test_table_innodb",' + str(theLength) + ')', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('selectRandomRecords("test_table_myisam",' + str(theLength) + ')', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('selectRandomRecords("test_table_memory",' + str(theLength) + ')', number=100, setup=setupString) )

    2）计数

    查询： SELECT count(*) FROM tbl

    结果： MyISAM wins

    Comparison of counts by different database engines

    这个演示了MyISAM和InnoDB之间的巨大差异--MyISAM（和内存）跟踪表中的记录数，因此这个事务很快而且O（1） . InnoDB计算所需的时间量与我调查范围内的表格大小呈超线性增长 . 我怀疑在实践中观察到的许多来自MyISAM查询的加速是由于类似的效果 .

    码：

    myisam_times = []
innodb_times = []
memory_times = []

# Define a function to count the records
def countRecords(testTable):

    selectString = "SELECT count(*) FROM " + testTable
    cur.execute(selectString)

setupString = "from __main__ import countRecords"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('countRecords("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('countRecords("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('countRecords("test_table_memory")', number=100, setup=setupString) )

    3）条件选择

    查询： SELECT * FROM tbl WHERE value1<0.5 AND value2<0.5 AND value3<0.5 AND value4<0.5

    结果： MyISAM wins

    Comparison of conditional selects by different database engines

    在这里，MyISAM和内存执行大致相同，并且对于较大的表格而言InnoDB大约50％ . 这种查询似乎最大化了MyISAM的好处 .

    码：

    myisam_times = []
innodb_times = []
memory_times = []

# Define a function to perform conditional selects
def conditionalSelect(testTable):
    selectString = "SELECT * FROM " + testTable + " WHERE value1 < 0.5 AND value2 < 0.5 AND value3 < 0.5 AND value4 < 0.5"
    cur.execute(selectString)

setupString = "from __main__ import conditionalSelect"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('conditionalSelect("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('conditionalSelect("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('conditionalSelect("test_table_memory")', number=100, setup=setupString) )

    4）子选择

    结果： InnoDB wins

    对于此查询，我为子选择创建了一组附加表 . 每个只有两列BIGINT，一列有主键索引，另一列没有任何索引 . 由于表大小很大，我没有测试内存引擎 . SQL表创建命令是

    CREATE TABLE
    subselect_myisam
    (
        index_col bigint NOT NULL,
        non_index_col bigint,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8;

    再次，'MyISAM'代替第二个表中的'InnoDB' .

    在此查询中，我将选择表的大小保留为1000000，而是改变子选定列的大小 .

    Comparison of sub-selects by different database engines

    这里的InnoDB轻松获胜 . 我们到达之后合理的大小表两个引擎都与子选择的大小成线性比例 . 该索引加速了MyISAM命令，但有趣的是对InnoDB速度影响不大 . subSelect.png

    码：

    myisam_times = []
innodb_times = []
myisam_times_2 = []
innodb_times_2 = []

def subSelectRecordsIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString = "from __main__ import subSelectRecordsIndexed"

def subSelectRecordsNotIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT non_index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString2 = "from __main__ import subSelectRecordsNotIndexed"

# Truncate the old tables, and re-fill with 1000000 records
truncateString = "TRUNCATE test_table_innodb"
truncateString2 = "TRUNCATE test_table_myisam"

cur.execute(truncateString)
cur.execute(truncateString2)

lengthOfTable = 1000000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)

for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE subselect_innodb"
    truncateString2 = "TRUNCATE subselect_myisam"

    cur.execute(truncateString)
    cur.execute(truncateString2)

    # For each length, empty the table and re-fill it with random data
    rand_sample = sorted(random.sample(xrange(lengthOfTable), theLength))
    rand_sample_2 = random.sample(xrange(lengthOfTable), theLength)

    for (the_value_1,the_value_2) in zip(rand_sample,rand_sample_2):
        insertString = "INSERT INTO subselect_innodb (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"
        insertString2 = "INSERT INTO subselect_myisam (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)

    db.commit()

    # Finally, time the queries
    innodb_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString) )

    innodb_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString2) )
    myisam_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString2) )

    我认为所有这一切的主要信息是，如果你真的关心速度，你需要对你正在进行的查询进行基准测试，而不是对哪个引擎更合适做出任何假设 .

    回复于

相关问题