mysql - MySQL 查询速度慢 "copying to tmp table"，帮助重写它

Question

这个mysql查询非常慢，当我查看查询时它是“复制到tmp表”。这些表非常大，尤其是 search_attribute(7900 万行)和 search_attribute_values(350,000 行)

SELECT attributenames.name, search_attribute_values.value 
FROM attributenames, categorysearchattributes, search_attribute, search_attribute_values 
WHERE 
categorysearchattributes.attributeid = attributenames.attributeid AND categorysearchattributes.categoryid = 4800 AND
categorysearchattributes.attributeid = search_attribute.attributeid AND 
search_attribute.valueid = search_attribute_values.valueid AND 
attributenames.localeid = 1 
GROUP BY search_attribute.valueid

Here is a picture of the EXPLAIN of the query

这是我的数据库架构

-- MySQL dump 10.13  Distrib 5.5.46, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version   5.5.46-0ubuntu0.14.04.2

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;



--
-- 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 `categorysearchattributes`
--

DROP TABLE IF EXISTS `categorysearchattributes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `categorysearchattributes` (
  `categoryid` int(11) NOT NULL DEFAULT '0',
  `attributeid` bigint(20) NOT NULL DEFAULT '0',
  `isactive` tinyint(1) NOT NULL DEFAULT '1',
  KEY `caterysearchattributes_aID` (`attributeid`),
  KEY `caterysearchattributes_cID` (`categoryid`)
) 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 */;

Added attributeid_valueid index

Answer 1

通过查看图中的 EXPLAIN 查询，表 search_attribute 正在进行全表扫描，即根本不使用索引。

向 search_attribute 表的 valueid 列添加索引应该会加快速度。添加索引后尝试一下并分享您的结果。

ALTER TABLE `search_attribute` ADD KEY `idx_valueid` (`valueid`);

请尝试以下建议的组合。