MySQL OUTER LEFT JOIN 性能

Question

我正在更新现有的基于 Web 的库存系统，该系统从 MySQL 数据库中提取数据。存储数据的主要结构是一对多关系的“items”和“tags”(items可以有多个对应的tags)

现有的数据前端系统是一个 Backbone.js 应用程序，它在登录时提取整个数据存储并在内存中操作该数据，必要时通过 RESTful 接口(interface)提交回数据库。 (这不是我设计系统的方式，但它现在是 Backbone 和 Spine 应用程序中的常见模式，也是大多数教程和书籍如何教授这些框架的方式)。

为了服务于前端捕获整个数据集(此时大约有 1000 个项目和 10,000 个项目标签)执行的初始提取，后端对项目表执行 SELECT 查询，然后执行后续的 SELECT查询每个获取的项目的标签表。显然，性能很糟糕。我认为这可以通过 JOIN 进行改进，认为一个选择查询优于 1000。以下查询获取我需要的数据，但即使在我的本地开发服务器上执行也需要超过 15 秒。是什么赋予了？我们可以在不设置缓存键值存储等额外基础设施的情况下改进这个系统或查询吗？

SELECT items.*, itemtags.id as `tag_id`, itemtags.tag, itemtags.type
FROM items LEFT OUTER JOIN
     itemtags
     ON items.id = itemtags.item_id
ORDER BY items.id;

表结构如下:

CREATE TABLE `items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `num` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `length_inches` int(10) unsigned DEFAULT NULL,
  `length_feet` int(10) unsigned DEFAULT NULL,
  `width_inches` int(10) unsigned DEFAULT NULL,
  `width_feet` int(10) unsigned DEFAULT NULL,
  `height_inches` int(10) unsigned DEFAULT NULL,
  `height_feet` int(10) unsigned DEFAULT NULL,
  `depth_inches` int(10) unsigned DEFAULT NULL,
  `depth_feet` int(10) unsigned DEFAULT NULL,
  `retail_price` int(10) unsigned DEFAULT NULL,
  `discount` int(10) unsigned DEFAULT NULL,
  `decorator_price` int(10) unsigned DEFAULT NULL,
  `new_price` int(10) unsigned DEFAULT NULL,
  `sold` int(10) unsigned NOT NULL,
  `push_date` int(10) unsigned DEFAULT NULL,
  `updated` int(10) unsigned NOT NULL,
  `created` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1747 DEFAULT CHARSET=latin1;

CREATE TABLE `itemtags` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `tag` varchar(100) NOT NULL,
  `type` varchar(100) NOT NULL,
  `created` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=61474 DEFAULT CHARSET=latin1;

Answer 1

我想你可以用这个:

SELECT *, a.id as `tag_id`, a.tag, a.type
FROM items LEFT OUTER JOIN
     (SELECT id, item_id, tag, type from itemtags ORDER BY 1,2,3) a
     ON items.id = a.item_id
ORDER BY items.id;

我并没有真正改变太多，只是别名。 a 并不表示任何重要的事情。

我没有填写表格，但您的原始查询用了 4 毫秒，我用了 1 毫秒。

http://sqlfiddle.com/#!2/b9551/6

无论您的数据集中有什么，您的应用程序都可以提取整个数据存储。由于数据存储和数据集不是同义词。

您也没有任何索引。您应该在 ID, ITEM_ID 上放置一个索引，以便优化表以更快地返回结果。我使用 order by 在我的子查询中创建了一个索引。希望这会有所帮助。