gpt4 book ai didi

mysql - 缓慢的*初始* mysql 查询

转载 作者:搜寻专家 更新时间:2023-10-30 23:04:31 26 4
gpt4 key购买 nike

运行 Mysql V5.6.22,在正确索引的数据库上运行复杂查询,响应时间最初非常慢(10 秒)。随后的查询(针对不同的项目)非常敏感(50 毫秒)。所以我猜查询缓存正在执行它们的工作——但我如何才能最大限度地减少初始的缓慢响应?

该数据库是一个外部维护的医学数据库 (SNOMED),我对当前快照使用推荐的 View - 我认为这些 View 是速度的限制。

奇怪的是,重新启动 mysql 并没有什么不同——这似乎是时间问题——如果数据库有一段时间没有被使用,它只需要很长时间才能启动。

所以我的问题是,是否有一个 mysql 设置用于这些缓存保留多长时间,或者我应该使用不同的方法而不使用 View (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;

最佳答案

在没有拼图的所有部分的情况下诊断别人的服务器是困难的,因此我将做出以下假设(如果我错了请纠正我):

  1. 这不是在数据库级服务器上运行
  2. 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 字节 * 4,582,286 行)。首先,尝试将配置中的值设置为 512Mb。重新启动,重新测试。如果它仍然不是很好,再添加 128Mb 并重复直到它起作用。如果此数据库位于专用计算机上,则根据您的设置将值设置得相当高(总 RAM 的 50-75%)应该是安全的。

Google 很快就给出了一个很好的指南,告诉我们应该调整哪些配置值。 http://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html

关于mysql - 缓慢的*初始* mysql 查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28364899/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com