MyISAM与InnoDB

mysql database performance innodb myisam

284336 观看

25回复

4301 作者的声誉

我正在研究涉及大量数据库写入的项目,我会说(70%的插入和30%的读取)。该比率还包括我认为是一次读取和一次写入的更新。读取可能很脏(例如,在读取时我不需要100%准确的信息)。
有问题的任务将是每小时进行超过100万次数据库事务。

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

有没有人有这种负载(或更高)的经验?MyISAM是走的路吗?

作者: user2013 的来源 发布者: 2008 年 8 月 21 日

回应 (25)


265

6950 作者的声誉

我不是数据库专家,我不会从经验中说话。然而:

MyISAM表使用表级锁定。根据您的流量估算值,您每秒接近200次写入。使用MyISAM,其中只有一个可以随时进行。您必须确保您的硬件能够跟上这些事务以避免被溢出,即单个查询可能不会超过5毫秒。

这对我来说,你需要一个支持行级锁定的存储引擎,即InnoDB。

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

作者: rix0rrr 发布者: 22.08.2008 04:03

4

10646 作者的声誉

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

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

作者: Gary Richardson 发布者: 22.08.2008 05:07

24

53714 作者的声誉

如果您使用MyISAM,则不会每小时执行任何事务,除非您将每个DML语句视为一个事务(在任何情况下,在发生崩溃时都不会持久或原子)。

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

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

如果你可以使用小的持久性命中,你可以使用InnoDB并将innodb_flush_log_at_trx_commit设置为0或2(详见文档),你可以提高性能。

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

作者: MarkR 发布者: 16.09.2008 09:34

136

3215 作者的声誉

我使用MySQL开发了一个高容量系统,我尝试过MyISAM和InnoDB。

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

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

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

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

作者: alanc10n 发布者: 16.09.2008 09:57

62

24934 作者的声誉

对于具有更多写入和读取的负载,您将受益于InnoDB。因为InnoDB提供了行锁定而不是表锁定,所以你的SELECTs可以是并发的,不仅是彼此,而且是与许多INSERTs。但是,除非您打算使用SQL事务,否则请将InnoDB commit flush设置为2(innodb_flush_log_at_trx_commit)。这样可以恢复将表从MyISAM移动到InnoDB时可能会丢失的大量原始性能。

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

最后,请注意不同的表加载。所有表都不会具有相同的读/写比率。一些读数接近100%的小桌子可以保留MyISAM。同样,如果你有一些接近100%写入的表,你可能会受益INSERT DELAYED,但只有在MyISAM中才支持(DELAYEDInnoDB表会忽略该子句)。

但基准确定。

作者: staticsan 发布者: 05.01.2009 11:39

5

3016 作者的声誉

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

作者: yogman 发布者: 06.01.2009 12:14

3

286 作者的声誉

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

作者: pfote 发布者: 31.10.2009 10:21

1

680 作者的声誉

对于读/写的比例,我猜想InnoDB会表现得更好。由于您可以使用脏读,所以您可能(如果您负担得起)复制到从属设备并让所有读取都转到从属设备。另外,考虑批量插入,而不是一次插入一条记录。

作者: neal aise 发布者: 05.07.2010 03:51

11

121 作者的声誉

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

作者: jsherk 发布者: 20.08.2010 10:15

5

666 作者的声誉

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

作者: Ricardo 发布者: 14.09.2010 09:27

509

5099 作者的声誉

我在表格中简要讨论了这个问题,因此您可以得出结论是否与InnoDBMyISAM一起使用。

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

                                                 MyISAM InnoDB
-------------------------------------------------- --------------
必需的全文搜索是5.6.4
-------------------------------------------------- --------------
需要交易是的
-------------------------------------------------- --------------
经常选择查询是      
-------------------------------------------------- --------------
频繁插入,更新,删除是
-------------------------------------------------- --------------
行锁定(单表上的多处理)是
-------------------------------------------------- --------------
关系基础设计是的

总结一下:

经常阅读,几乎没有写作=> MyISAM
MySQL中的全文搜索<= 5.5 => MyISAM

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

作者: developer99 发布者: 22.07.2011 10:01

4

876 作者的声誉

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

作者: user965748 发布者: 24.11.2011 12:34

11

119 作者的声誉

另请查看MySQL本身的一些替代品:

MariaDB的

http://mariadb.org/

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

Percona服务器

https://launchpad.net/percona-server

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

作者: Refiner 发布者: 03.04.2012 03:49

32

2153 作者的声誉

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

一般来说,使用InnoDB会导致很少的复杂应用程序,可能也会更加无错误。因为您可以将所有引用完整性(外键约束)放入数据模型中,所以您不需要使用MyISAM所需的任何应用程序代码。

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

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

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

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

作者: Patrick Savalle 发布者: 26.08.2012 12:18

12

121 作者的声誉

请注意,我的正式教育和经验是在Oracle,而我在MySQL工作完全是个人的,并且在我自己的时间,所以如果我说的是对Oracle而言是正确的,但对于MySQL不是这样,我道歉。虽然这两个系统共享很多,但关系理论/代数是相同的,关系数据库仍然是关系数据库,仍然存在很多差异!

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

注意:对于Oracle,create / alter / drop语句称为“DDL”(数据定义)语句,并隐式触发提交。插入/更新/删除语句,称为“DML”(数据操作),不会自动提交,但仅在执行DDL,提交或退出/退出时(或者如果将会话设置为“自动提交”),或者如果您的客户端自动提交)。在使用Oracle时必须注意这一点,但我不确定MySQL如何处理这两种类型的语句。因此,我想清楚地表明,当涉及到MySQL时,我不确定这一点; 仅限Oracle。

基于事务的引擎优秀的一个例子:

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

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

现在我们将一个guest添加到guest表中,但是现在可用的座位数是不正确的(例如,当它实际为84时,值为85)。

当然,有很多方法可以解决这个问题,比如跟踪“100减去来宾表中的行数”的可用座位,或者检查信息是否一致的一些代码等等。但是使用基于事务的数据库引擎,如InnoDB的,无论是全部操作都致力于,或NONE都是。这在许多情况下都会有所帮助,但就像我说的那样,它不是唯一的安全方式,不是(不过很好的方式,由数据库处理,而不是程序员/脚本编写者)。

这是所有“基于事务的”本质上意味着在这种情况下,除非我遗漏了一些东西 - 要么整个交易成功应该,要么没有任何改变,因为只做一些部分改变可能会导致一个小的严重的混乱数据库,甚至可能破坏它......

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

最后,关于表锁定与行锁定的注释:

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

无论如何,我与那些谁主张,多少个连接在同一时间被允许同意相当有信心,没有解决一个锁定的表。事实上,多个连接是锁定表的整个点! 因此,其他进程/用户/应用程序无法通过同时进行更改来破坏数据库。

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

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

  1. 进程1读取当前值,假设它为空,因此到目前为止为“0”。
  2. 进程2也读取当前值,它仍为0。
  3. 进程1写入(当前+ 1),即1。
  4. 进程2 应该写入2,但由于它进程1 之前读取当前值以写入新值,因此它也会向表中写入1。

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

  1. 进程1读取当前值,即0。
  2. 进程一写入(当前+ 1),即1。
  3. 进程2现在读取当前值。但是当进程一DID写入(更新)时,它没有提交数据,因此只有相同的进程才能读取它更新的新值,而所有其他进程都看到旧值,直到有提交。

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

最后,MyISAM中可能存在不同的安全措施,而不是外键和基于事务的交互。那么,对于一个存在的事实,整个表被锁定,这使得它不太可能被交易/ FKS 需要

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

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

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

这是我的第一个回复,所以请原谅所有免责声明的长度等等...我不想在我不完全确定的时候听起来很傲慢!

作者: Arembjorn 发布者: 21.04.2013 01:54

31

434 作者的声誉

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版本的默认引擎。

作者: Pankaj Khurana 发布者: 28.05.2013 07:03

183

382005 作者的声誉

人们经常谈论性能,读取与写入,外键等等,但在我看来,存储引擎还有另外一个必备功能: 原子更新。

试试这个:

  1. 针对MyISAM表发出UPDATE需要5秒钟。
  2. 当UPDATE正在进行时,比如2.5秒,按Ctrl-C中断它。
  3. 观察桌子上的效果。更新了多少行?有多少人没有更新?表格是否可读,或者在按下Ctrl-C时是否已损坏?
  4. 针对InnoDB表尝试使用UPDATE进行相同的实验,中断正在进行的查询。
  5. 观察InnoDB表。 行已更新。InnoDB已经确保您有原子更新,如果无法提交完整更新,它会回滚整个更改。此外,该表没有损坏。即使您使用killall -9 mysqld模拟崩溃,这也有效。

当然,性能是可取的,但不要丢失数据应该胜过它。

作者: Bill Karwin 发布者: 17.07.2013 05:47

0

7962 作者的声誉

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值的外部表)。

作者: tony gil 发布者: 06.08.2013 07:02

1

29 作者的声誉

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

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

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

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

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

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

测试一下,我欢迎辩论。

作者: Cyberwip 发布者: 20.08.2013 07:54

1

12965 作者的声誉

如果是70%的插入和30%的读取,则更像是在InnoDB方面。

作者: kta 发布者: 14.12.2013 03:08

63

4884 作者的声誉

游戏有点晚了......但这是我几个月前写的一篇相当全面的文章,详细介绍了MYISAM和InnoDB之间的主要区别。拿一杯茶(也许是一块饼干),享受吧。


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或其他数据类型。

作者: d4nyll 发布者: 21.01.2015 03:32

2

460 作者的声誉

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

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

你可能想看看;
InnoDB的
利弊MyISAM的利弊

作者: Light93 发布者: 08.02.2015 04:54

56

1494 作者的声誉

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

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

我在下面做了一些基准测试 - 我使用python进行循环,使用timeit库进行时序比较。为了兴趣,我还包括内存引擎,虽然它只适用于较小的表(The table 'tbl' is full当你超过MySQL内存限制时不断遇到),但它提供了全面的最佳性能。我看的四种选择是:

  1. 香草选择
  2. 计数
  3. 条件选择
  4. 索引和非索引的子选择

首先,我使用以下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

结果:画画

不同数据库引擎对香草选择的比较

这些的速度大致相同,并且如所预期的那样,要选择的列数是线性的。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获胜

不同数据库引擎的计数比较

这个演示了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获胜

不同数据库引擎对条件选择的比较

在这里,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获胜

对于此查询,我为子选择创建了一组附加表。每个都只是两列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,而是改变子选定列的大小。

不同数据库引擎的子选择比较

在这里,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) )

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

作者: StackG 发布者: 11.06.2015 09:15

2

747 作者的声誉

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

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

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

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

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

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

我同意这里的其他答案,即质量高可用性,高性能应用程序不应该使用myISAM,因为它不起作用,它不够稳健或不够稳定,足以导致无挫折的体验。有关详细信息,请参阅Bill Karwin的答案。

PS当我的粉丝向下投票但是不能告诉你这个答案的哪一部分是不正确的时候,我喜欢它。

作者: pilavdzice 发布者: 02.05.2016 09:26

16

69816 作者的声誉

问题和大多数答案都已过时

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

戏剧性的图表是为其中的MyISAM赢得了一个案例:COUNT(*) 没有一个WHERE条款。但这真的是你花时间做的吗?

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

如果在基准测试时进行任何写操作SELECTs,MyISAM MEMORY可能会因为表级锁定而丢失。

事实上,Oracle非常确定InnoDB比他们从8.0中删除了MyISAM更好。

问题是在5.1的早期写的。从那时起,这些主要版本被标记为“一般可用性”:

  • 2010:5.5(12月的.8)
  • 2013:5.6(2月.10)
  • 2015年:5。7(10月份的.9)
  • 2018:8.0(4月.11)

底线:不要使用MyISAM

作者: Rick James 发布者: 03.05.2017 06:26
32x32