MySQL 加入一对多与 FK 很慢

Question

我有一个 People 和一个 Companies 表。每个人员记录都有一个非空的 FK Company_ID。我有大约 13 万家公司和 260 万个人记录。

我在 people.name 上有一个全文索引，效果很好。这大约需要 0.3 秒:

SELECT DISTINCT `people`.`Name`
FROM mydb.`people`
WHERE match(`people`.`Name`) against('john')

这是我的表定义和来自“显示表创建”运行的索引:

CREATE TABLE `companies` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`Name` longtext,
PRIMARY KEY (`Id`),
FULLTEXT KEY `companies_name_FT_IDX` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=129590 DEFAULT CHARSET=utf8

CREATE TABLE `people` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`Name` longtext,
`Company_Id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `IX_Company_Id` (`Company_Id`) USING HASH,
FULLTEXT KEY `people_name_FT_IDX` (`Name`),
CONSTRAINT `FK_People_Companies_Company_Id` FOREIGN KEY (`Company_Id`)       
REFERENCES `companies` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2640085 DEFAULT CHARSET=utf8

问题是通过 ID 加入个人和公司需要超过 16 秒。

SELECT DISTINCT `companies`.`Id`, `companies`.`Name`, `people`.`Name`
FROM mydb.`companies`
INNER JOIN mydb.`people` ON mydb.`companies`.`Id` = mydb.`people`.`Company_Id` 
WHERE match(`people`.`Name`) against('john')

编辑:在查看了 Workbench 中的一大堆东西之后，我看到 innodb 缓冲区在查询期间达到了最大值。我追踪到它是在 my.ini (Windows) 中设置的。它设置为 8M，我将其设置为 1G 并重新启动了 mysql。我的查询现在运行得非常快。具体来说:innodb_buffer_pool_size=1G

MySQL 的资源默认设置如此之小是有原因的吗？有什么方法可以使用 Workbench 为我的 PC 设置“推荐”值吗？似乎很可能还有更多设置，我也可以通过浏览 my.ini 文件来提高。

谢谢。

Answer 1

旧版本的 MySQL 将 innodb_buffer_pool_size 的关键设置默认为可怜的 8M。这曾经在各种论坛上引起很多问题。然后他们将默认值提高到 128M；这消除了归因于该设置的大部分问题。

如果您的 RAM 少于 4GB，那 1GB 可能就太大了。

如果您主要使用 InnoDB，在 可用 RAM 的情况下，一个好的值是 70%。 My blog关于这个话题。