时间戳

首页 > 解决方案 > MySQL 查询优化，通过其中两个表的并集触及三个表

mysql - MySQL 查询优化，通过其中两个表的并集触及三个表

问题描述

我有一个查询，它根据提供的 ID 从一个表返回结果，该 ID 存在于两个表之一或两个表中的列中。下面提供了相关表的数据库架构以及初始查询，然后是同行后来向我推荐的内容。我将在下面详细介绍为什么此查询有效，但我需要进一步优化它以适应更大的数据集和分页。

CREATE TABLE `killmails` (
    `id` BIGINT(20) UNSIGNED NOT NULL,
    `hash` VARCHAR(255) NOT NULL,
    `moon_id` BIGINT(20) NULL DEFAULT NULL,
    `solar_system_id` BIGINT(20) UNSIGNED NOT NULL,
    `war_id` BIGINT(20) NULL DEFAULT NULL,
    `is_npc` TINYINT(1) NOT NULL DEFAULT '0',
    `is_awox` TINYINT(1) NOT NULL DEFAULT '0',
    `is_solo` TINYINT(1) NOT NULL DEFAULT '0',
    `dropped_value` DECIMAL(18,4) UNSIGNED NOT NULL DEFAULT '0.0000',
    `destroyed_value` DECIMAL(18,4) UNSIGNED NOT NULL DEFAULT '0.0000',
    `fitted_value` DECIMAL(18,4) UNSIGNED NOT NULL DEFAULT '0.0000',
    `total_value` DECIMAL(18,4) UNSIGNED NOT NULL DEFAULT '0.0000',
    `killmail_time` DATETIME NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`, `hash`),
    INDEX `total_value` (`total_value`),
    INDEX `killmail_time` (`killmail_time`),
    INDEX `solar_system_id` (`solar_system_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `killmail_attackers` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `killmail_id` BIGINT(20) UNSIGNED NOT NULL,
    `alliance_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `character_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `corporation_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `faction_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `damage_done` BIGINT(20) UNSIGNED NOT NULL,
    `final_blow` TINYINT(1) NOT NULL DEFAULT '0',
    `security_status` DECIMAL(17,15) NOT NULL,
    `ship_type_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `weapon_type_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `ship_type_id` (`ship_type_id`),
    INDEX `weapon_type_id` (`weapon_type_id`),
    INDEX `alliance_id` (`alliance_id`),
    INDEX `corporation_id` (`corporation_id`),
    INDEX `killmail_id_character_id` (`killmail_id`, `character_id`),
    CONSTRAINT `killmail_attackers_killmail_id_killmails_id_foreign_key` FOREIGN KEY (`killmail_id`) REFERENCES `killmails` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `killmail_victim` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `killmail_id` BIGINT(20) UNSIGNED NOT NULL,
    `alliance_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `character_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `corporation_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `faction_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `damage_taken` BIGINT(20) UNSIGNED NOT NULL,
    `ship_type_id` BIGINT(20) UNSIGNED NOT NULL,
    `ship_value` DECIMAL(18,4) NOT NULL DEFAULT '0.0000',
    `pos_x` DECIMAL(30,10) NULL DEFAULT NULL,
    `pos_y` DECIMAL(30,10) NULL DEFAULT NULL,
    `pos_z` DECIMAL(30,10) NULL DEFAULT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `corporation_id` (`corporation_id`),
    INDEX `alliance_id` (`alliance_id`),
    INDEX `ship_type_id` (`ship_type_id`),
    INDEX `killmail_id_character_id` (`killmail_id`, `character_id`),
    CONSTRAINT `killmail_victim_killmail_id_killmails_id_foreign_key` FOREIGN KEY (`killmail_id`) REFERENCES `killmails` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

第一个查询是问题开始的地方：

SELECT
    *
FROM  
    killmails k
    LEFT JOIN killmail_attackers ka ON k.id = ka.killmail_id
    LEFT JOIN killmail_victim kv ON k.id = kv.killmail_id
WHERE 
    ka.character_id = ? 
    OR kv.character_id = ?
ORDER BY killmails.killmail_time DESC
LIMIT ? OFFSET ?

这工作正常，但查询时间长。我们对此进行了优化

SELECT
    killmails.*,
FROM (
    SELECT killmail_victim.killmail_id FROM killmail_victim
        WHERE killmail_victim.corporation_id = ?
    UNION
    SELECT killmail_attackers.killmail_id FROM killmail_attackers
        WHERE killmail_attackers.corporation_id = ?
) SELECTED_KMS
LEFT JOIN killmails ON killmails.id = SELECTED_KMS.killmail_id
ORDER BY killmails.killmail_time DESC
LIMIT ? OFFSET ?

在查找角色的 killmail 时，我发现查询时间有了很大的改进，但是当我开始查询更大的数据集（例如公司和联盟的 killmail）时，查询速度变慢了。这是因为联合在一起的查询可能会返回大量数据，而将所有这些数据读入内存以便可以创建 SELECTED_KMS 表所花费的时间是我认为需要花费大量时间的。大多数时候，通过联盟，我与数据库的连接从应用程序超时。一个联盟从其中一个联盟表中返回了 900K killmailID，但不确定另一个联盟返回了什么。

我可以轻松地向内部查询添加限制语句，但是当我对数据进行分页或引入按日期搜索 KM 的功能时，这会带来很多复杂性。

我正在寻找有关如何优化此查询的建议，并且在不久的将来仍然可以轻松进行分页。

谢谢你

标签： mysqloptimizationpagination

解决方案

更改INDEX(corporation_id)两个表，INDEX(corporation_id, killmail_id)以便内部查询将“覆盖”。

一般来说，INDEX(a)当你也有INDEX(a,b). 任何只需要a, 的查询都可以使用这些索引中的任何一个。（此规则不适用于b；仅适用于“最左侧”列。）

从哪里来killmails.id？不是AUTO_INCREMENT；它在 中并不孤单PRIMARY KEY，因此没有指定的“唯一性”约束。它是其他设计所独有的吗？它是在代码的其他地方计算的吗？（我问是因为我需要感受它的独特性和其他特征。）

添加INDEX(id, killmails_time).

你用的是什么版本？

也许UNION ALL给出相同的结果？它会更快，因为它不需要重复数据删除。

你有多少内存？的价值是innodb_buffer_pool_size多少？

你真的需要 8-byteBIGINTs吗？即使您的应用程序正在使用 longlong（或任何它所称的），您也可以在不更改应用程序的情况下更改架构。

您需要这么高的精度和范围吗？ DECIMAL(30,10)-- 每个需要 14 个字节。 DOUBLE将在 8 个字节中为您提供大约 16 个有效数字，具有更广泛的值（最多约 10^308）。您使用的是什么“单位”？（光年或秒差距过大；英里或公里不够。也许是 AU？那么最低数字将是几米的精度？）

最后几个问题旨在缩小表格，看看我们是否可以避免它像现在这样明显地受到 I/O 限制。

重要的

innodb_buffer_pool_size = 128M非常小，尤其是对于 32GB 的机器，尤其是当您的数据集远大于 128MB 时。如果服务器上没有运行任何其他应用程序，则将该设置提升到20G.

推荐阅读