缓慢的 mysql 查询，复制到 tmp 表，使用文件排序 [英] Slow mysql query, copying to tmp table, using filesort
问题描述
我需要加快这个查询.耗时12秒，返回3917条记录.我的 mySQL 安装没有针对性能进行调整，也许我需要修改一些配置变量来帮助提高性能.在查询的大部分时间里，查询都显示正在复制到 tmp 表".
I need to speed up this query. It takes 12 seconds, returns 3917 records. My installation of mySQL is not tuned for performance, maybe I need to modify some configuration variables to help performance. The query says "Copying to tmp table" for most of the duration of the query.
此查询用于生成参数搜索的过滤器(即按品牌名称、颜色等过滤搜索结果).
This query is used to generate the filters for parametric searching (ie. filtering search results by brand name, color, etc).
查询:
SELECT attributenames.attributeid,
search_attribute_values.valueid,
attributenames.name,
search_attribute_values.value,
count(search_attribute_values.value) as count,
search_attribute_values.absolutevalue
FROM product
INNER JOIN vendorimport
ON (vendorimport.productid = product.productid
AND product.categoryid = 4871)
INNER JOIN search_attribute
ON (search_attribute.productid = product.productid
AND search_attribute.localeid = 1)
INNER JOIN search_attribute_values
ON (search_attribute.valueid = search_attribute_values.valueid)
INNER JOIN attributenames
ON (attributenames.attributeid = search_attribute.attributeid
AND attributenames.localeid = 1)
GROUP BY attributenames.attributeid, search_attribute_values.valueid
说明:
+----+-------------+-------------------------+--------+----------------------------------------------------+----------------------------+---------+---------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+--------+----------------------------------------------------+----------------------------+---------+---------------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | product | ref | PRIMARY,product_categoryID,categoryid_productid | categoryid_productid | 4 | const | 38729 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | vendorimport | ref | productimport_productid | productimport_productid | 5 | microcad.product.productid | 1 | Using where; Using index |
| 1 | SIMPLE | search_attribute | ref | PRIMARY | PRIMARY | 8 | microcad.vendorimport.productid,const | 8 | Using where; Using index |
| 1 | SIMPLE | attributenames | ref | attributenames_attributeID,attributenames_localeID | attributenames_attributeID | 8 | microcad.search_attribute.attributeid | 4 | Using where |
| 1 | SIMPLE | search_attribute_values | eq_ref | PRIMARY | PRIMARY | 4 | microcad.search_attribute.valueid | 1 | |
+----+-------------+-------------------------+--------+----------------------------------------------------+----------------------------+---------+---------------------------------------+-------+----------------------------------------------+
架构:
--
-- 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`),
KEY `categoryid_productid` (`categoryid_productid`)
) 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
推荐答案
NULL vs NOT NULL -- 使用 NOT NULL 除非你有NULL 的商业原因.
NULL vs NOT NULL -- Use NOT NULL unless you have a business reason for NULL.
在每个表上使用具有相关 PRIMARY KEY 的 InnoDB.那可能会更快.
Use InnoDB with relevant PRIMARY KEY on each table. That will probably be faster.
使用索引"，在有意义的地方，会有所帮助.
"Using index", where it makes sense, will help some.
product.categoryid = 4871 不属于 vendorimport 的 ON 子句；将其移至 WHERE 子句.(这不会加快速度.)
product.categoryid = 4871 does not belong in the ON clause for vendorimport; move it to a WHERE clause. (This won't speed things up.)
您的查询不能进一步优化——它必须完成所有 JOIN 并传送所有行.
Your query cannot be optimizer further -- it must do all the JOINs and deliver all the rows.
但是...你真的想要 3917 行输出吗?你能应付吗?也许您只想要其中的几个并且可以在 SELECT 期间过滤它们?这可以加快速度.
But... Do you really want 3917 rows of output? Can you deal with that? Maybe you only want a few of them and could filter them during the SELECT? That could speed things up.
这篇关于缓慢的 mysql 查询，复制到 tmp 表，使用文件排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
