mysql - MySQL 查询性能缓慢

Question

请建议我如何提高 MySQL 中此查询的性能。它运行得很慢。

查询:

SELECT *
FROM product, search_attribute, search_attribute_values 
WHERE 
product.categoryid = 4800 AND product.productid = search_attribute.productid  
AND search_attribute.valueid = search_attribute_values.valueid 
GROUP BY search_attribute.valueid

解释查询:

+----+-------------+-------------------------+--------+-----------------------------+---------+---------+-------------------------------------+----------+---------------------------------+
| id | select_type | table                   | type   | possible_keys               | key     | key_len | ref                                 | rows     | Extra                           |
+----+-------------+-------------------------+--------+-----------------------------+---------+---------+-------------------------------------+----------+---------------------------------+
|  1 | SIMPLE      | search_attribute        | ALL    | PRIMARY,attributeid_valueid | NULL    | NULL    | NULL                                | 79801024 | Using temporary; Using filesort |
|  1 | SIMPLE      | search_attribute_values | eq_ref | PRIMARY                     | PRIMARY | 4       | microcad.search_attribute.valueid   |        1 |                                 |
|  1 | SIMPLE      | product                 | eq_ref | PRIMARY,product_categoryID  | PRIMARY | 4       | microcad.search_attribute.productid |        1 | Using where                     |
+----+-------------+-------------------------+--------+-----------------------------+---------+---------+-------------------------------------+----------+---------------------------------+

架构:

--
-- Table structure for table `attributenames`
--

DROP TABLE IF EXISTS `attributenames`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `attributenames` (
  `attributeid` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(110) NOT NULL DEFAULT '',
  `localeid` int(11) NOT NULL DEFAULT '0',
  KEY `attributenames_attributeID` (`attributeid`),
  KEY `attributenames_localeID` (`localeid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `product`
--

DROP TABLE IF EXISTS `product`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `product` (
  `productid` int(11) NOT NULL DEFAULT '0',
  `manufacturerid` int(11) NOT NULL DEFAULT '0',
  `isactive` tinyint(1) NOT NULL DEFAULT '1',
  `mfgpartno` varchar(70) NOT NULL DEFAULT '',
  `categoryid` int(11) NOT NULL DEFAULT '0',
  `isaccessory` tinyint(1) NOT NULL DEFAULT '0',
  `equivalency` double NOT NULL DEFAULT '0',
  `creationdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modifieddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `lastupdated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`productid`),
  KEY `product_manufacturerID` (`manufacturerid`),
  KEY `product_categoryID` (`categoryid`),
  KEY `product_mfgPartNo` (`mfgpartno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `search_attribute`
--

DROP TABLE IF EXISTS `search_attribute`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `search_attribute` (
  `productid` int(11) NOT NULL DEFAULT '0',
  `attributeid` bigint(20) NOT NULL DEFAULT '0',
  `valueid` int(11) NOT NULL DEFAULT '0',
  `localeid` int(11) NOT NULL DEFAULT '0',
  `setnumber` tinyint(2) NOT NULL DEFAULT '0',
  `isactive` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`productid`,`localeid`,`attributeid`,`setnumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `search_attribute_values`
--

DROP TABLE IF EXISTS `search_attribute_values`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `search_attribute_values` (
  `valueid` int(11) NOT NULL DEFAULT '0',
  `value` varchar(255) NOT NULL DEFAULT '',
  `absolutevalue` double NOT NULL DEFAULT '0',
  `unitid` int(11) NOT NULL DEFAULT '0',
  `isabsolute` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`valueid`),
  KEY `search_attrval_value` (`value`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

每个表中的记录数:

search_attribute 为 72,000,000，search_attribute_values 为 350,000，产品为 4,000,000

Answer 1

您的索引目前应该没问题。产品表在categoryid上有一个索引，然后应该从该索引连接到search_attribute，它在多个列上有一个覆盖索引，其中第一个是productid(应该使用它)。然后它应该使用主键 valueid 加入到 search_attribute_values。

然而，出于某种原因，MySQL 似乎决定对返回大量行的 search_attribute 进行非键控读取，然后尝试将其他读取加入到该读取中。可能是因为 GROUP BY(它可能会为返回的所有其他列返回奇怪的值)。

我要尝试的第一件事是强制 MySQL 重建索引统计信息(使用 ANALYZE TABLE )。然后它可能会有用地使用它们。

尝试使用 STRAIGHT_JOIN 失败:-

SELECT *
FROM product 
STRAIGHT_JOIN search_attribute ON product.productid = search_attribute.productid
STRAIGHT_JOIN search_attribute_values ON search_attribute.valueid = search_attribute_values.valueid 
WHERE product.categoryid = 4800 
GROUP BY search_attribute.valueid

但是，您实际上想要返回什么值？例如，您的查询将为每个 search_attribute valueid 返回 1 个类别 id 为 4800 的产品。返回哪个产品没有定义，同样假设多个搜索属性可以具有相同的 valueid，那么选择其中一个也没有定义。

虽然这不会出错并且确实会在 MySQL 中返回一些内容，但它会在大多数 SQL 风格中给出错误。