我应该为 MySQL 数据库使用 MyISAM 还是 InnoDB 表?

2021-12-26 00:00:00 database mysql database-design innodb myisam

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

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

表 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`)
)

表 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`)
)

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

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.

表 2 包含与表 1 中的图像相关的图像.因此使用 primaryId 将表 1 中的单个图像与表 2 中的一个或多个图像相关联. 这就是我想到使用InnoDB的Foreign Key能力的地方，但我也熟悉MyISAM中Indexes的能力.

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.

无需深入研究剩余字段，imgDate 用于对结果进行排序.

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

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

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.

推荐答案

默认使用 InnoDB.

在 MySQL 5.1 以后，你应该使用 InnoDB.在 MySQL 5.1 中，您应该启用 InnoDB 插件.在 MySQL 5.5 中，默认启用 InnoDB 插件，因此只需使用它.

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.

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

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 对于某些工作负载(例如表扫描或高容量 INSERT-only 工作)的表现略好，但默认选择应该是 InnoDB，除非您可以证明em> 你有一个例子，MyISAM 做得更好.

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.

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

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

  • InnoDB 比 MyISAM 更能抵抗表损坏.
  • 行级锁定.在 MyISAM 中，读取器会阻止写入器，反之亦然.
  • 支持数据和索引的大型缓冲池.MyISAM 密钥缓冲区仅用于索引.
  • MyISAM 停滞不前；所有未来的开发都将在 InnoDB 中进行.

另见我对MyISAM 与 InnoDB 的回答

相关文章