执行多个连接的MySQL查询花费太长时间执行

问题描述：

我有3个表。第一个称为map_life，第二个称为scripts，第三个称为npc_data。执行多个连接的MySQL查询花费太长时间执行

我运行下面的查询从map_life得到的所有特性，同时也从npc_data如果ID匹配正从scripts的script列和storage_cost列。

SELECT life.* 
    , script.script 
    , npc.storage_cost 
    FROM map_life life 
    LEFT 
    JOIN scripts script 
    ON script.objectid = life.lifeid 
    AND script.script_type = 'npc' 
    LEFT 
    JOIN npc_data npc 
    ON npc.npcid = life.lifeid

正如你所看到的，map_life id为lifeid，而scripts id为objectid和npc_data ID是npcid。

此查询需要约5秒钟才能执行，我不知道为什么。以下是所有这3个表的CREATE语句，也许我错过了一些东西？

CREATE TABLE `mcdb83`.`map_life` (
    `id` bigint(21) unsigned NOT NULL AUTO_INCREMENT, 
    `mapid` int(11) NOT NULL, 
    `life_type` enum('npc','mob','reactor') NOT NULL, 
    `lifeid` int(11) NOT NULL, 
    `life_name` varchar(50) DEFAULT NULL COMMENT 'For reactors, specifies a handle so scripts may interact with them; for NPC/mob, this field is useless', 
    `x_pos` smallint(6) NOT NULL DEFAULT '0', 
    `y_pos` smallint(6) NOT NULL DEFAULT '0', 
    `foothold` smallint(6) NOT NULL DEFAULT '0', 
    `min_click_pos` smallint(6) NOT NULL DEFAULT '0', 
    `max_click_pos` smallint(6) NOT NULL DEFAULT '0', 
    `respawn_time` int(11) NOT NULL DEFAULT '0', 
    `flags` set('faces_left') NOT NULL DEFAULT '', 
    PRIMARY KEY (`id`), 
    KEY `lifetype` (`mapid`,`life_type`) 
) ENGINE=InnoDB AUTO_INCREMENT=32122 DEFAULT CHARSET=latin1; 

CREATE TABLE `mcdb83`.`scripts` (
    `script_type` enum('npc','reactor','quest','item','map_enter','map_first_enter') NOT NULL, 
    `helper` tinyint(3) NOT NULL DEFAULT '-1' COMMENT 'Represents the quest state for quests, and the index of the script for NPCs (NPCs may have multiple scripts).', 
    `objectid` int(11) NOT NULL DEFAULT '0', 
    `script` varchar(30) NOT NULL DEFAULT '', 
    PRIMARY KEY (`script_type`,`helper`,`objectid`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Lists all the scripts that belong to NPCs/reactors/etc. '; 

CREATE TABLE `mcdb83`.`npc_data` (
    `npcid` int(11) NOT NULL, 
    `storage_cost` int(11) NOT NULL DEFAULT '0', 
    `flags` set('maple_tv','is_guild_rank') NOT NULL DEFAULT '', 
    PRIMARY KEY (`npcid`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

好。接下来为上述提供EXPLAIN。 – Strawberry

答

对于此查询：

SELECT l.*, s.script, npc.storage_cost 
FROM map_life l LEFT JOIN 
    scripts s 
    ON s.objectid = l.lifeid AND 
     s.script_type = 'npc' LEFT JOIN 
    npc_data npc 
    ON npc.npcid = l.lifeid;

你想在指标：scripts(object_id, script_type, script)和npc_data(npcid, storage_cost)。这些索引中列的顺序很重要。

答

map_life.lifeid没有任何指标定义，因此加入将导致全表扫描。定义map_life.lifeid字段的索引。
在脚本表中，主键按以下顺序定义：script_type,helper,objectid。连接在objectid上完成，并在script_type上存在恒定过滤条件。由于索引中的字段顺序错误，因此MySQL无法使用主键进行此查询。对于此查询，索引中字段的顺序应为b：objectid，script_type,helper。

以上将显着加快连接。如果您的索引实际上覆盖了查询中的所有字段，那么您可以进一步提高查询的速度，因为在这种情况下，MySQL甚至不需要触摸这些表。

考虑将以下字段添加到索引中，然后再订购scripts表：object_id, script_type, script和npcid, storage_cost索引到npc_data表。但是，这些索引可能会降低插入/更新/删除语句的速度，因此在将这些索引添加到生产环境之前，请先进行一些性能测试

执行多个连接的MySQL查询花费太长时间执行

相关推荐