MySQL查询性能降低 [英] Slow MySQL Query Performance

问题描述

请提出如何在MySQL中提高此查询的性能的建议.它运行非常缓慢.

Please suggest how I can speed up performance of this query in MySQL. It runs very slowly.

查询:

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

search_attribute is 72,000,000, search_attribute_values is 350,000, product is 4,000,000

推荐答案

您现有的索引应该都不错.产品表在categoryid上有一个索引，然后应该从该表连接到search_attribute，后者在多个列上具有覆盖索引，其中第一个是productid(应使用).然后，应使用作为主键的valueid联接到search_attribute_values.

Your indexes as they stand should all be fine. The product table has an index on categoryid, and it should then join from that to search_attribute which has a covering index on multiple columns, the first of which is productid (which should be used). Then it should join on to search_attribute_values using the valueid which is the primary key.

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

However for some reason MySQL seems to have decided to do a non keyed read on search_attribute returning a massive number of rows, and then tried to join the others on to that. Possibly because of the GROUP BY (which is probably going to return strange values for all the other columns returned).

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

First thing I would try is to force MySQL to rebuild the index statistics ( using ANALYZE TABLE). Then it might use them usefully.

尝试使用STRAIGHT_JOIN失败:-

Failing that try using a 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值ID返回1个产品，其类别ID为4800.返回的产品没有定义，类似地，假设多个搜索属性可以具有相同的valueid，那么也没有定义选择哪个.

However, what values do you actually want to return? For example your query will return 1 product with a category id of 4800 for each search_attribute valueid. Which product that is returned is not defined, and similarly assuming several search attributes can have the same valueid then which one of those is chosen is also not defined.

虽然这不会出错，但确实会在MySQL中返回某些内容，但在大多数SQL版本中都会出现错误.

While this doesn't error and does return something in MySQL, it would give an error in most flavours of SQL.

这篇关于MySQL查询性能降低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！