我应该在我的MySQL数据库中使用MyISAM或InnoDB表吗？

I have the following two tables in my database (the indexing is not complete as it will be based on which engine I use):

我的数据库中有以下两个表（索引不完整，因为它将基于我使用的引擎）：

Table 1:

表格1：

CREATE TABLE `primary_images` (
  `imgId` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  `imgTitle` varchar(255) DEFAULT NULL,
  `view` varchar(45) DEFAULT NULL,
  `secondary` enum('true','false') NOT NULL DEFAULT 'false',
  `imgURL` varchar(255) DEFAULT NULL,
  `imgWidth` smallint(6) DEFAULT NULL,
  `imgHeight` smallint(6) DEFAULT NULL,
  `imgDate` datetime DEFAULT NULL,
  `imgClass` enum('jeans','t-shirts','shoes','dress_shirts') DEFAULT NULL,
  `imgFamily` enum('boss','lacoste','tr') DEFAULT NULL,
  `imgGender` enum('mens','womens') NOT NULL DEFAULT 'mens',
  PRIMARY KEY (`imgId`),
  UNIQUE KEY `imgDate` (`imgDate`)
)

Table 2:

表2：

CREATE TABLE `secondary_images` (
  `imgId` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  `primaryId` smallint(6) unsigned DEFAULT NULL,
  `view` varchar(45) DEFAULT NULL,
  `imgURL` varchar(255) DEFAULT NULL,
  `imgWidth` smallint(6) DEFAULT NULL,
  `imgHeight` smallint(6) DEFAULT NULL,
  `imgDate` datetime DEFAULT NULL,
  PRIMARY KEY (`imgId`),
  UNIQUE KEY `imgDate` (`imgDate`)
)

Table 1 will be used to create a thumbnail gallery with links to larger versions of the image. imgClass, imgFamily, and imgGender will refine the thumbnails that are shown.

表1将用于创建一个缩略图库，其中包含指向较大版本图像的链接。 imgClass，imgFamily和imgGender将优化显示的缩略图。

Table 2 contains images related to those in Table 1. Hence the use of primaryId to relate a single image in Table 1, with one or more images in Table 2. This is where I was thinking of using the Foreign Key ability of InnoDB, but I'm also familiar with the ability of Indexes in MyISAM to do the same.

表2包含与表1中相关的图像。因此，使用primaryId将表1中的单个图像与表2中的一个或多个图像相关联。这是我在考虑使用InnoDB的外键功能的地方，但是我也熟悉MyISAM中索引的功能。

Without delving too much into the remaining fields, imgDate is used to order the results.

在不对剩余字段进行过多研究的情况下，imgDate用于对结果进行排序。

Last, but not least, I should mention that this database is READ ONLY. All data will be entered by me. I have been told that if a database is read only, it should be MyISAM, but I'm hoping you can shed some light on what you would do in my situation.

最后，但并非最不重要的，我应该提到这个数据库是READ ONLY。所有数据都将由我输入。有人告诉我，如果一个数据库是只读的，它应该是MyISAM，但我希望你能说明你在我的情况下会做些什么。

4 个解决方案

#1

Always use InnoDB by default.

In MySQL 5.1 later, you should use InnoDB. In MySQL 5.1, you should enable the InnoDB plugin. In MySQL 5.5, the InnoDB plugin is enabled by default so just use it.

在MySQL 5.1之后，你应该使用InnoDB。在MySQL 5.1中，您应该启用InnoDB插件。在MySQL 5.5中，InnoDB插件默认启用，所以只需使用它即可。

The advice years ago was that MyISAM was faster in many scenarios. But that is no longer true if you use a current version of MySQL.

几年前的建议是MyISAM在许多情况下都更快。但如果使用当前版本的MySQL，则不再适用。

There may be some exotic corner cases where MyISAM performs marginally better for certain workloads (e.g. table-scans, or high-volume INSERT-only work), but the default choice should be InnoDB unless you can prove you have a case that MyISAM does better.

可能存在一些奇异的极端情况，其中MyISAM对于某些工作负载（例如，表扫描或仅大量INSERT工作）执行得稍微好一点，但默认选择应该是InnoDB，除非您能证明您有一个MyISAM做得更好的情况。

Advantages of InnoDB besides the support for transactions and foreign keys that is usually mentioned include:

InnoDB的优点除了通常提到的对事务和外键的支持外，还包括：

• InnoDB is more resistant to table corruption than MyISAM.
• InnoDB比MyISAM更能抵抗表损坏。
• Row-level locking. In MyISAM, readers block writers and vice-versa.
• 行级锁定。在MyISAM中，读者会阻止作者，反之亦然。
• Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
• 支持数据和索引的大缓冲池。 MyISAM密钥缓冲区仅用于索引。
• MyISAM is stagnant; all future development will be in InnoDB.
• MyISAM停滞不前;所有未来的发展都将在InnoDB中进行。

See also my answer to MyISAM versus InnoDB

另请参阅我对MyISAM与InnoDB的回答

#2

MyISAM won't enable you to do mysql level check. For instance if you want to update the imgId on both tables as a single transaction:

MyISAM不会让你做mysql级别检查。例如，如果要将两个表上的imgId更新为单个事务：

START TRANSACTION;
UPDATE primary_images SET imgId=2 WHERE imgId=1;
UPDATE secondary_images SET imgId=2 WHERE imgId=1;
COMMIT;

Another drawback is integrity check, using InnoDB you can do some error check like to avoid duplicated values in the field UNIQUE KEY imgDate (imgDate). Trust me, this really come at hand and is way less error prone. In my opinion MyISAM is for playing around while some more serious work should rely on InnoDB.

另一个缺点是完整性检查，使用InnoDB你可以做一些错误检查，以避免字段UNIQUE KEY imgDate（imgDate）中的重复值。相信我，这真的来了，并且不那么容易出错。在我看来，MyISAM是为了玩，而一些更严肃的工作应该依赖于InnoDB。

Hope it helps

希望能帮助到你

#3

A few things to consider :

需要考虑的一些事项：

1. Do you need transaction support?
2. 你需要交易支持吗？
3. Will you be using foreign keys?
4. 你会使用外键吗？
5. Will there be a lot of writes on a table?
6. 桌面上会写很多吗？

If answer to any of these questions is "yes", then you should definitely use InnoDB. Otherwise, you should answer the following questions :

如果回答任何这些问题是“是”，那么你一定要使用InnoDB。否则，您应该回答以下问题：

1. How big are your tables?
2. 你的桌子有多大？
3. How many rows do they contain?
4. 它们包含多少行？
5. What is the load on your database engine?
6. 数据库引擎的负载是多少？
7. What kind of queries you expect to run?
8. 您期望运行什么样的查询？

Unless your tables are very large and you expect large load on your database, either one works just fine.

除非您的表格非常大并且您希望数据库负载很大，否则任何一个都可以正常工作。

I would prefer MyISAM because it scales pretty well for a wide range of data-sizes and loads.

我更喜欢MyISAM，因为它适用于各种数据大小和负载。

#4

I would like to add something that people may benefit from: I've just created a InnoDB table (leaving everything as the default, except changing the collation to Unicode), and populated it with about 300,000 records (rows).

我想添加一些人们可能会从中受益的东西：我刚刚创建了一个InnoDB表（将所有内容保留为默认值，除了将整理更改为Unicode），并使用大约300,000条记录（行）填充它。

Queries like SELECT COUNT(id) FROM table - would hang until giving an error message, not returning a result;

像SELECT COUNT（id）FROM table这样的查询会挂起，直到给出错误消息，而不是返回结果;

I've cloned the table with the data into a new MyISAM table - and that same query, along with other large SELECTqueries - would return fast, and everything worked ok.

我已经将带有数据的表克隆到一个新的MyISAM表中 - 同样的查询以及其他大型SELECTqueries将快速返回，一切正常。