运行mysql v5.6.22,在索引正确的数据库上运行复杂的查询,最初的响应时间非常慢(10秒)。随后的查询(针对不同的项)响应非常快(50毫秒)。所以我猜查询缓存正在做他们的工作——但是我如何最小化最初的慢速响应呢?
该数据库是一个外部维护的医疗数据库(snomed),我使用当前快照的推荐视图-我认为这些视图是对速度的限制。
奇怪的是,重新启动mysql并没有什么区别——这似乎是一个时间问题——如果数据库在一段时间内没有使用,那么启动它只需要很长时间。
所以我的问题是,是否有一个mysql设置用于保存这些缓存多长时间,或者我应该使用不同的方法而不是使用视图(snomed的数据是2年更新的,在另一个类似的药物数据库中有每月发布的版本)。
你们中的一些人希望看到这个查询,所以这里继续。警告:这确实有点棘手,基本查询的行数在注释中给出…;-)

SELECT DISTINCT concat(c.id, '::', c.effectiveTime) as id, `d1`.`term` as label, `d2`.`term`
FROM (`snomed`.`rf2_ss_refset` as refset)
JOIN `snomed`.`rf2_ss_concepts` as c ON `c`.`id` = `refset`.`referencedCOmponentId`
JOIN `snomed`.`rf2_ss_descriptions` as d1 ON `d1`.`conceptId` = `refset`.`referencedComponentId`
JOIN `snomed`.`rf2_ss_descriptions` as d2 ON `d2`.`conceptId` = `d1`.`conceptId`
JOIN `snomed`.`rf2_ss_language_refset` as lang ON `lang`.`referencedComponentId` = `d1`.`id`
WHERE `refset`.`refSetId` =  32570071000036102
AND `refset`.`active` =  1
AND `d2`.`typeId` =  900000000000013009
AND `d1`.`active` =  1
AND `d2`.`active` =  1
AND `d1`.`moduleId` =  900000000000207008
AND `d2`.`moduleId` =  900000000000207008
AND `lang`.`active` =  1
AND `lang`.`acceptabilityId` =  900000000000548007
AND `d2`.`term` like "hypertension%"
ORDER BY `d1`.`term`, `d2`.`term`

在哪里?
CREATE TABLE `rf2_ss_refset` (
  `id` char(36) COLLATE utf8_unicode_ci NOT NULL,
  `effectiveTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` smallint(1) NOT NULL,
  `moduleId` bigint(20) unsigned NOT NULL,
  `refSetId` bigint(20) unsigned NOT NULL,
  `referencedComponentId` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`effectiveTime`),
  KEY `moduleId_idx` (`moduleId`),
  KEY `refSetId_idx` (`refSetId`),
  KEY `referencedComponentId_idx` (`referencedComponentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE ALGORITHM=UNDEFINED DEFINER=`snomed`@`localhost` SQL SECURITY DEFINER VIEW `rf2_ss_concepts`
AS SELECT
   `t1`.`id` AS `id`,
   `t1`.`effectiveTime` AS `effectiveTime`,
   `t1`.`active` AS `active`,
   `t1`.`moduleId` AS `moduleId`,
   `t1`.`definitionStatusId` AS `definitionStatusId`
FROM `rf2_full_concepts` `t1` where (`t1`.`effectiveTime` = (select max(`t2`.`effectiveTime`) from `rf2_full_concepts` `t2` where (`t1`.`id` = `t2`.`id`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`snomed`@`localhost` SQL SECURITY DEFINER VIEW `rf2_ss_descriptions`
AS SELECT
   `t1`.`id` AS `id`,
   `t1`.`effectiveTime` AS `effectiveTime`,
   `t1`.`active` AS `active`,
   `t1`.`moduleID` AS `moduleID`,
   `t1`.`conceptId` AS `conceptId`,
   `t1`.`languageCode` AS `languageCode`,
   `t1`.`typeID` AS `typeID`,
   `t1`.`term` AS `term`,
   `t1`.`caseSignificanceId` AS `caseSignificanceId`
FROM `rf2_full_descriptions` `t1` where (`t1`.`effectiveTime` = (select max(`t2`.`effectiveTime`) from `rf2_full_descriptions` `t2` where (`t1`.`id` = `t2`.`id`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`snomed`@`localhost` SQL SECURITY DEFINER VIEW `rf2_ss_language_refset`
AS SELECT
   `t1`.`id` AS `id`,
   `t1`.`effectiveTime` AS `effectiveTime`,
   `t1`.`active` AS `active`,
   `t1`.`moduleId` AS `moduleId`,
   `t1`.`refSetId` AS `refSetId`,
   `t1`.`referencedComponentId` AS `referencedComponentId`,
   `t1`.`acceptabilityId` AS `acceptabilityId`
FROM `rf2_full_language_refset` `t1` where (`t1`.`effectiveTime` = (select max(`t2`.`effectiveTime`) from `rf2_full_language_refset` `t2` where (`t1`.`id` = `t2`.`id`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`snomed`@`localhost` SQL SECURITY DEFINER VIEW `rf2_ss_relationships`
AS SELECT
   `t1`.`id` AS `id`,
   `t1`.`effectiveTime` AS `effectiveTime`,
   `t1`.`active` AS `active`,
   `t1`.`moduleId` AS `moduleId`,
   `t1`.`sourceId` AS `sourceId`,
   `t1`.`destinationId` AS `destinationId`,
   `t1`.`relationshipGroup` AS `relationshipGroup`,
   `t1`.`typeId` AS `typeId`,
   `t1`.`characteristicTypeId` AS `characteristicTypeId`,
   `t1`.`modifierId` AS `modifierId`
FROM `rf2_full_relationships` `t1` where (`t1`.`effectiveTime` = (select max(`t2`.`effectiveTime`) from `rf2_full_relationships` `t2` where (`t1`.`id` = `t2`.`id`)));

#select count(*) from rf2_full_concepts # 507046
CREATE TABLE `rf2_full_concepts` (
  `id` bigint(20) unsigned NOT NULL,
  `effectiveTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` tinyint(4) DEFAULT NULL,
  `moduleId` bigint(20) unsigned NOT NULL,
  `definitionStatusId` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`effectiveTime`),
  KEY `moduleId_idx` (`moduleId`),
  KEY `definitionStatusId_idx` (`definitionStatusId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

#select count(*) from rf2_full_descriptions # 1486373
CREATE TABLE `rf2_full_descriptions` (
  `id` bigint(20) unsigned NOT NULL,
  `effectiveTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` tinyint(4) DEFAULT NULL,
  `moduleID` bigint(20) unsigned NOT NULL,
  `conceptId` bigint(20) unsigned NOT NULL,
  `languageCode` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
  `typeID` bigint(20) unsigned NOT NULL,
  `term` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `caseSignificanceId` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`effectiveTime`),
  KEY `moduleID_idx` (`moduleID`),
  KEY `conceptId_idx` (`conceptId`),
  KEY `typeID_idx` (`typeID`),
  KEY `caseSignificanceId_idx` (`caseSignificanceId`),
  KEY `term_idx` (`term`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

#select count(*) from rf2_full_relationships = 4582286
CREATE TABLE `rf2_full_relationships` (
  `id` bigint(20) unsigned NOT NULL,
  `effectiveTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` tinyint(4) DEFAULT '1',
  `moduleId` bigint(20) unsigned NOT NULL,
  `sourceId` bigint(20) unsigned NOT NULL,
  `destinationId` bigint(20) unsigned NOT NULL,
  `relationshipGroup` bigint(20) unsigned NOT NULL,
  `typeId` bigint(20) unsigned NOT NULL,
  `characteristicTypeId` bigint(20) unsigned NOT NULL,
  `modifierId` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`effectiveTime`),
  KEY `moduleId_idx` (`moduleId`),
  KEY `sourceId_idx` (`sourceId`),
  KEY `destinationId_idx` (`destinationId`),
  KEY `relationshipGroup_idx` (`relationshipGroup`),
  KEY `typeId_idx` (`typeId`),
  KEY `characteristicTypeId_idx` (`characteristicTypeId`),
  KEY `modifierId_idx` (`modifierId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

#select count(*) from rf2_full_language_refset # 624467
CREATE TABLE `rf2_full_language_refset` (
  `id` char(36) COLLATE utf8_unicode_ci NOT NULL,
  `effectiveTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` smallint(1) NOT NULL,
  `moduleId` bigint(20) unsigned NOT NULL,
  `refSetId` bigint(20) unsigned NOT NULL,
  `referencedComponentId` bigint(20) unsigned NOT NULL,
  `acceptabilityId` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`,`effectiveTime`),
  KEY `moduleId` (`moduleId`),
  KEY `refSetId` (`refSetId`),
  KEY `referencedComponentId` (`referencedComponentId`),
  KEY `acceptabilityId` (`acceptabilityId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

最佳答案

在没有全部问题的情况下诊断其他人的服务器是很困难的,因此我将做出以下假设(如果我错了,请纠正我):
这不是在数据库级服务器上运行的
mysql配置尚未特别配置到此任务
对于您所展示的示例,您的表会进行过度索引,而且由于数据集很大,我可以想象最大的索引文件太大,无法放入mysql的索引缓存中。
如果没有更多关于环境的信息,您描述的行为似乎取决于配置的缓冲区。此时,操作系统进入并缓冲内存中的数据库。
重新启动mysql时,它查询得又快又好,因为操作系统仍然将文件保存在其缓冲区中。当您停止访问数据库时,操作系统将最终解除缓冲文件,您将返回到慢速初始查询。
我在超过配置缓冲区大小的大型索引上看到了相同的行为,但这是一组大得多的数据。将数据库配置为具有更大的索引和表数据缓冲区,解决了我的特殊问题。在我的例子中,查询速度从10-15秒降到了毫秒。
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
如果您有一些空闲RAM,请尝试稍微增大缓冲池的大小。
默认大小为128MB,最大的索引文件大约为279MB(64字节*4582286行)。
首先,尝试将配置中的值设置为512MB。重新启动,重新测试。如果仍然不是很好,再添加一个128MB并重复直到它工作。
如果这个数据库是在一台专用机器上,根据您的设置,将该值设置得很高(总内存的50-75%)应该是安全的。
谷歌很快就想出了一个很好的指南,告诉你该如何处理配置值。
http://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html

10-05 20:52
查看更多