什么时候MyISAM比InnoDB好?

时间:2022-11-11 18:17:27

Sometimes I got asked on some interviews: what benefits does InnoDB have against MyISAM and when MyISAM is better than InnoDB? It's all clear about the first part of question: InnoDB is transaction compliant, row-level blocking instead of table-level blocking, foreign key support and some others, these points just came to mind immidiately.

有时我被问到一些采访:InnoDB对MyISAM有什么好处,MyISAM比InnoDB好?关于问题的第一部分,一切都很清楚:InnoDB是交易兼容的,行级阻塞而不是表级阻塞,外键支持和其他一些,这些点只是浮现在脑海中。

But when MyISAM is really better than InnoDB?

但是当MyISAM真的比InnoDB好吗?

6 个解决方案

#1


13  

MyISAM is better than InnoDB when you don't need those advanced features and storage speed is more important than other concerns. MyISAM also allows full-text searches to be performed inside the database engine itself, instead of needing to query results and then search them as an array or whatever in your application.

当您不需要这些高级功能时,MyISAM优于InnoDB,并且存储速度比其他问题更重要。 MyISAM还允许在数据库引擎本身内执行全文搜索,而不需要查询结果,然后将它们作为数组或应用程序中的任何内容进行搜索。

InnoDB is a reasonable choice if you need to store data with a high degree of fidelity with complicated interactions and relationships. MyISAM is a reasonable choice if you need to save or load a large number of records in a small amount of time.

如果您需要以高度保真度存储具有复杂交互和关系的数据,InnoDB是一个合理的选择。如果您需要在很短的时间内保存或加载大量记录,MyISAM是一个合理的选择。

I wouldn't recommend using MyISAM for data that matters. It's great for logging or comments fields or anything where you don't particularly care if a record vanishes into the twisting nether. InnoDB is good for when you care about your data, don't need fast searches and have to use MySQL.

我不建议将MyISAM用于重要的数据。它非常适合记录或评论字段或任何您不特别关心的记录,如果记录消失在扭曲的下层中。 InnoDB非常适合您关心数据,不需要快速搜索并且必须使用MySQL。

It's also worth mentioning that InnoDB supports row-level locking, while MyISAM only supports table-level locking - which is to say that for many common situations, InnoDB can be dramatically faster due to more queries executing in parallel.

值得一提的是,InnoDB支持行级锁定,而MyISAM仅支持表级锁定 - 也就是说,对于许多常见情况,由于更多查询并行执行,InnoDB可以大大加快。

The bottom line: Use InnoDB unless you absolutely have to use MyISAM. Alternatively, develop against PostgreSQL and get the best of both.

底线:使用InnoDB除非你绝对必须使用MyISAM。或者,针对PostgreSQL进行开发并充分利用它们。

#2


4  

MyISAM doesn't support transactions (and the other things mentioned) so it can work faster. MyISAM is a way to achieve higher performance in those situations when you do not need these features.

MyISAM不支持事务(以及提到的其他事情),因此它可以更快地工作。当您不需要这些功能时,MyISAM是一种在这些情况下实现更高性能的方法。

#3


3  

MyISAM supports full text, as mentioned, but also supports the MERGE table type. This is handy when you have a large table and would like to "swap" out/archive parts of it periodically. Think about a logging or report data that you want to keep the last quarter and/or year. MyISAM handles large amounts of data like this better, when you are mainly inserting and rarely updating or deleting.

如上所述,MyISAM支持全文,但也支持MERGE表类型。当你有一个大表并希望定期“交换”/存档它的部分时,这很方便。考虑要保留上一季度和/或年份的日志记录或报告数据。当您主要插入并且很少更新或删除时,MyISAM会更好地处理大量此类数据。

InnoDB performance drops pretty quickly and dramatically once you can't fit the indexes in memory. If your primary key is not going to be a number (i.e. auto increment), then you may want to rethink using InnoDB. The primary key is replicated for every index on an InnoDB table. So if you have a large primary key and a few other indexes, your InnoDB table will get very large very quick.

一旦无法将索引放入内存中,InnoDB的性能就会迅速下降。如果您的主键不是一个数字(即自动增量),那么您可能想要使用InnoDB重新思考。为InnoDB表上的每个索引复制主键。因此,如果您有一个大的主键和一些其他索引,您的InnoDB表将变得非常快。

#4


2  

There are a few features that MySQL only has implemented for MyISAM (such as native fulltext indexing).

MySQL仅为MyISAM实现了一些功能(例如本机全文索引)。

That said, InnoDB is still typically better for most production apps.

也就是说,对于大多数生产应用来说,InnoDB通常更好。

#5


1  

Also: Full-text search in mySQL is only supported in myISAM tables.

另外:mySQL中的全文搜索仅在myISAM表中受支持。

#6


0  

MyISAM has a very simple structure, when compared with InnoDB. There is no row versioning, there's one file per table and rows are stored sequentially. However, while it supports concurrent inserts (SELECTs and 1 INSERT can run together), it also has table-level locks (if there are 2 INSERTs on the same table, 1 has to wait). Also, UPDATEs and DELETEs are slow because of the structure of the data files.

与InnoDB相比,MyISAM具有非常简单的结构。没有行版本控制,每个表有一个文件,行按顺序存储。但是,虽然它支持并发插入(SELECT和1 INSERT可以一起运行),但它也有表级锁(如果同一个表上有2个INSERT,1必须等待)。此外,由于数据文件的结构,UPDATE和DELETE很慢。

MyISAM doesn't support transactions or foreign keys.

MyISAM不支持事务或外键。

Generally, MyISAM should be better if you work on general trends (so you don't care about the correctness of individual rows) and data is updated by night or never. Also, it allows to move individual tables from one server to another, via the filesystem.

通常,如果您处理一般趋势(因此您不关心单个行的正确性),并且数据在夜间或从不更新,MyISAM应该更好。此外,它允许通过文件系统将各个表从一个服务器移动到另一个服务器。

InnoDB supports very well concurrency and transactions. Has a decent support for fulltext and an almost-decent support for foreign keys.

InnoDB支持非常好的并发和事务。对全文提供了不错的支持,并且对外键提供了相当不错的支持。

#1


13  

MyISAM is better than InnoDB when you don't need those advanced features and storage speed is more important than other concerns. MyISAM also allows full-text searches to be performed inside the database engine itself, instead of needing to query results and then search them as an array or whatever in your application.

当您不需要这些高级功能时,MyISAM优于InnoDB,并且存储速度比其他问题更重要。 MyISAM还允许在数据库引擎本身内执行全文搜索,而不需要查询结果,然后将它们作为数组或应用程序中的任何内容进行搜索。

InnoDB is a reasonable choice if you need to store data with a high degree of fidelity with complicated interactions and relationships. MyISAM is a reasonable choice if you need to save or load a large number of records in a small amount of time.

如果您需要以高度保真度存储具有复杂交互和关系的数据,InnoDB是一个合理的选择。如果您需要在很短的时间内保存或加载大量记录,MyISAM是一个合理的选择。

I wouldn't recommend using MyISAM for data that matters. It's great for logging or comments fields or anything where you don't particularly care if a record vanishes into the twisting nether. InnoDB is good for when you care about your data, don't need fast searches and have to use MySQL.

我不建议将MyISAM用于重要的数据。它非常适合记录或评论字段或任何您不特别关心的记录,如果记录消失在扭曲的下层中。 InnoDB非常适合您关心数据,不需要快速搜索并且必须使用MySQL。

It's also worth mentioning that InnoDB supports row-level locking, while MyISAM only supports table-level locking - which is to say that for many common situations, InnoDB can be dramatically faster due to more queries executing in parallel.

值得一提的是,InnoDB支持行级锁定,而MyISAM仅支持表级锁定 - 也就是说,对于许多常见情况,由于更多查询并行执行,InnoDB可以大大加快。

The bottom line: Use InnoDB unless you absolutely have to use MyISAM. Alternatively, develop against PostgreSQL and get the best of both.

底线:使用InnoDB除非你绝对必须使用MyISAM。或者,针对PostgreSQL进行开发并充分利用它们。

#2


4  

MyISAM doesn't support transactions (and the other things mentioned) so it can work faster. MyISAM is a way to achieve higher performance in those situations when you do not need these features.

MyISAM不支持事务(以及提到的其他事情),因此它可以更快地工作。当您不需要这些功能时,MyISAM是一种在这些情况下实现更高性能的方法。

#3


3  

MyISAM supports full text, as mentioned, but also supports the MERGE table type. This is handy when you have a large table and would like to "swap" out/archive parts of it periodically. Think about a logging or report data that you want to keep the last quarter and/or year. MyISAM handles large amounts of data like this better, when you are mainly inserting and rarely updating or deleting.

如上所述,MyISAM支持全文,但也支持MERGE表类型。当你有一个大表并希望定期“交换”/存档它的部分时,这很方便。考虑要保留上一季度和/或年份的日志记录或报告数据。当您主要插入并且很少更新或删除时,MyISAM会更好地处理大量此类数据。

InnoDB performance drops pretty quickly and dramatically once you can't fit the indexes in memory. If your primary key is not going to be a number (i.e. auto increment), then you may want to rethink using InnoDB. The primary key is replicated for every index on an InnoDB table. So if you have a large primary key and a few other indexes, your InnoDB table will get very large very quick.

一旦无法将索引放入内存中,InnoDB的性能就会迅速下降。如果您的主键不是一个数字(即自动增量),那么您可能想要使用InnoDB重新思考。为InnoDB表上的每个索引复制主键。因此,如果您有一个大的主键和一些其他索引,您的InnoDB表将变得非常快。

#4


2  

There are a few features that MySQL only has implemented for MyISAM (such as native fulltext indexing).

MySQL仅为MyISAM实现了一些功能(例如本机全文索引)。

That said, InnoDB is still typically better for most production apps.

也就是说,对于大多数生产应用来说,InnoDB通常更好。

#5


1  

Also: Full-text search in mySQL is only supported in myISAM tables.

另外:mySQL中的全文搜索仅在myISAM表中受支持。

#6


0  

MyISAM has a very simple structure, when compared with InnoDB. There is no row versioning, there's one file per table and rows are stored sequentially. However, while it supports concurrent inserts (SELECTs and 1 INSERT can run together), it also has table-level locks (if there are 2 INSERTs on the same table, 1 has to wait). Also, UPDATEs and DELETEs are slow because of the structure of the data files.

与InnoDB相比,MyISAM具有非常简单的结构。没有行版本控制,每个表有一个文件,行按顺序存储。但是,虽然它支持并发插入(SELECT和1 INSERT可以一起运行),但它也有表级锁(如果同一个表上有2个INSERT,1必须等待)。此外,由于数据文件的结构,UPDATE和DELETE很慢。

MyISAM doesn't support transactions or foreign keys.

MyISAM不支持事务或外键。

Generally, MyISAM should be better if you work on general trends (so you don't care about the correctness of individual rows) and data is updated by night or never. Also, it allows to move individual tables from one server to another, via the filesystem.

通常,如果您处理一般趋势(因此您不关心单个行的正确性),并且数据在夜间或从不更新,MyISAM应该更好。此外,它允许通过文件系统将各个表从一个服务器移动到另一个服务器。

InnoDB supports very well concurrency and transactions. Has a decent support for fulltext and an almost-decent support for foreign keys.

InnoDB支持非常好的并发和事务。对全文提供了不错的支持,并且对外键提供了相当不错的支持。