mysql - 使用文件排序和临时优化查询

Question

谁能帮我优化这个查询。运行需要一段时间:

su_pref 表有近 900,000 行

SELECT p.mykey,
       p.merchant_name,
       m.merchant_url, 
       p.name,
       p.description,
       p.image_url, 
       p.deep_link,
       p.rrp_price, 
       p.display_price, 
       c.category,
       p.su_parent_name 
FROM su_pref p #
        INNER JOIN su_categoryrefs cr ON p.mykey = cr.mykey 
        INNER JOIN su_categories c ON cr.id = c.id 
        INNER JOIN su_merchants m ON p.merchant_id=m.id 
WHERE 
     cr.id =36 
 ORDER BY p.date_created DESC LIMIT 0,20 

这就是我使用 EXPLAIN 时得到的结果

我真的很难理解如何使用 EXPLAIN 来优化查询，因此我们将不胜感激。

以下是表定义。 su_pref 表相当大，因此我删除了一些字段

CREATE TABLE `su_pref` (
  `mykey` varchar(50) NOT NULL,
  `merchant_id` smallint(3) default NULL,
  `merchant_name` varchar(50) NOT NULL default '',
  `brand` varchar(50) default NULL,
  `merchantproductkey` varchar(50) default NULL,
  `upc` varchar(15) default NULL,
  `name` varchar(255) NOT NULL default '',
  `description` varchar(2500) NOT NULL default '',
  `short_description` varchar(500) default NULL,
  `thumb_url` varchar(500) default NULL,
  `image_url` varchar(500) default NULL,
  `deep_link` varchar(1000) default NULL,
  `merchant_link` varchar(255) default NULL,
  `rrp_price` decimal(11,2) default NULL,
  `display_price` decimal(11,2) default NULL,
  `delivery_cost` decimal(11,2) default NULL,
  `price_range` tinyint(1) default NULL,
  `keywords` varchar(500) default NULL,
  `su_parent_name` enum('Women','Men','') NOT NULL,
  `date_created` date default NULL,
  `date_modified` datetime default NULL,
  `wp_featured` varchar(1) default NULL,
  `hp_featured` varchar(1) default NULL,
  `published` varchar(1) default NULL,
  `in_todays_feed` varchar(1) default NULL,
  PRIMARY KEY  (`mykey`),
  KEY `merchant_id` (`merchant_id`),
  KEY `date_created` (`date_created`),
  FULLTEXT KEY `product_search` 
                (`name`,`description`,`short_description`,
                 `keywords`, `product_type`,`colour`,`style`,
                 `material`,`datafeed_category_name`,
                 `datafeed_subcategory_name`,
                 `brand`,`merchant_name`),
  FULLTEXT KEY `name` (`name`,`datafeed_category_name`, 
                       `datafeed_subcategory_name`,`product_type`,`keywords`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

-

CREATE TABLE `su_categories` (
  `id` mediumint(9) NOT NULL,
  `category` varchar(100) NOT NULL,
  `parent_id` tinyint(4) NOT NULL,
  `update_query` varchar(3000) default NULL,
  `url` varchar(150) default NULL,
  `last_update` datetime default NULL,
  PRIMARY KEY  (`id`,`category`,`parent_id`),
  KEY `parent_id` (`parent_id`),
  KEY `category_id` (`id`),
  FULLTEXT KEY `category_name` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

-

CREATE TABLE `su_categoryrefs` (
  `mykey` varchar(255) NOT NULL,
  `id` smallint(4) NOT NULL,
  PRIMARY KEY  (`mykey`,`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

-

CREATE TABLE `su_merchants` (
  `id` mediumint(9) NOT NULL,
  `merchant_name` varchar(40) NOT NULL,
  `merchant_url` varchar(40) default NULL,
  `merchant_website` varchar(40) default NULL,
  `merchant_description` varchar(2000) default NULL,
  `merchant_featured` varchar(1) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `merchant_name` (`merchant_name`)
) ENGINE=MyISAM AUTO_INCREMENT=128 DEFAULT CHARSET=latin1

提前谢谢

Answer 1

无法判断每个不合格列是从哪个表返回的，或者 JOIN 谓词中的“merchant_id”是从哪个表引用的，或者 ORDER BY 子句中的 date_created 是从哪个表引用的从。 (我们可以做出一些猜测，但也只是猜测。

第一步实际上是使用 table_name 或最好是表别名来限定所有这些列引用。

此外，对声明进行格式化以便普通人能够理解它也有很大帮助。

使用合格的列引用重新格式化语句会有所帮助，并且包含架构定义也会有所帮助。 (我会更新我的答案。)

SELECT p.mykey
     , p.merchant_name
     , merchant_url
     , `name`
     , description
     , image_url
     , deep_link
     , rrp_price
     , display_price
     , category
     , su_parent_name
 FROM su_pref p
 JOIN su_categoryrefs cr ON cr.mykey = p.mykey
 JOIN su_categories c    ON c.id     = cr.id
 JOIN su_merchants m     ON m.id     = p.merchant_id
WHERE cr.id = 36
ORDER BY date_created DESC LIMIT 0,20

作为一般经验法则，最好有一个索引，其前导列是连接谓词中引用的列。通常，这些是主键和所有外键。

此外，如果是“覆盖索引”(包含查询中引用的所有列的索引)，您将在 EXPLAIN 输出中看到“使用索引”；这意味着查询在不引用数据 block 的情况下从索引得到满足。

EXPLAIN 似乎显示引用了“colour_id”索引，但该索引中包含哪些列并不明显。 (解释计划输出图像对我来说太难阅读。)

<小时/>

这里有一些建议:

确保您有索引

ON su_categories (id, category)     -- you do
ON su_merchants (id, merchant_url)  -- never mind this one, it's a small table
ON su_categoryrefs (id, mykey)

我假设 su_categoryrefs 表中的 id 列是引用 su_categories.id 的外键，而不是主键表的。 (这看起来像一个关系表，解决了 su_categories 和 'su_preferences` 之间的多对多关系，但我只是猜测。