如何在mysql中优化这个IN子句

Question

我有以下表格结构：

CREATE TABLE listing_attributes (    
   id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,    
   listing_id INT,    
   attribute_id INT,    
   value LONGTEXT,   
   FOREIGN KEY ( attribute_id ) 
        REFERENCES item_type_attributes ( id ) 
        ON DELETE CASCADE ON UPDATE CASCADE,    
   FOREIGN KEY ( listing_id ) 
        REFERENCES listing( id ) 
        ON DELETE CASCADE ON UPDATE CASCADE
 );
 CREATE INDEX attribute_fk ON listing_attributes ( attribute_id );
 CREATE INDEX listing_fk ON listing_attributes ( listing_id );

这个：

CREATE TABLE vw_search(    
   listing_type_css_class VARCHAR(45),    
   override_listing_image_url VARCHAR(200),    
   override_listing_image TINYINT DEFAULT 0,    
   listing_type_priority INT DEFAULT 0,    
   main_image VARCHAR(255),    
   title VARCHAR(255),    
   new_listing INT,    
   listing_type_name VARCHAR(100),    
   location_name VARCHAR(255),    
   location_group VARCHAR(100),    
   location_id INT DEFAULT 0,    
   create_date TIMESTAMP DEFAULT '0000-00-0000:00:00' NOT NULL,    
   expiry_date DATETIME,    
   has_expired INT,    
   should_show INT,    
   price DECIMAL(19,2) NOT NULL,    
   description LONGTEXT,    
   view_count INT DEFAULT 0 NOT NULL,    
   id INT DEFAULT 0 NOT NULL,    
   category_id INT NOT NULL,    
   created_by_user_id INT NOT NULL,    
   dealer_id INT DEFAULT 0,    
   disable_price_in_listings TINYINT DEFAULT 0,    
   business_image VARCHAR(200),    
   non_expiry INT DEFAULT 0
);

运行的查询是这样的：

 SELECT COUNT(DISTINCT id) as total 
 FROM  (
      SELECT *  ,
             (  select value 
                from listing_attributes 
                where attribute_id=81 
                  and listing_id=l.id
             ) as '81',
             (  select value 
                from listing_attributes 
                where attribute_id=78 
                  and listing_id=l.id
             ) as '78' 
       from vw_search l 
       WHERE category_id IN 
          ('884','882','880','885','871','949','873','875','876','434','424','422','423','425','426','546','750','752',
           '754','756','759','763','766','774','947','770','778','783','781','786','428','547','430','431','414','415',
           '712','420','548','432','433','418','419','959','961','960','358','359','360','364','361','363','357','1003',
           '560','1002','1006','1004','1007','1005','561','377','380','553','554','555','1021','556','557','559','558',
           '435','436','437','438','439','441','443','550','442','549','444','445','446','447','448','449','450','451',
           '714','452','551','453','454','455','456','615','459','460','461','462','463','464','468','469','466','467',
           '470','811','472','816','473','813','728','476','474','799','795','803','721','806','475','477','1009','479',
           '480','481','482','828','483','484','485','385','384','386','387','388','389','1018','833','500','504','502',
           '501','503','487','488','489','490','491','1020','493','494','495','498','497','496','421','975','1000','976',
           '973','977','951','511','512','509','513','515','514','838','840','412','413','1016','1014','1019','86','399'
           ,'397','398','1008','521','522','526','523','524','525','528','1053','535','529','530','850','533','532','1052',
           '969','968','966','965','539','538','967','1012','1013','1011','1024','1022','1023','541','542','545','543','544',
           '297','296','298','300','299','301','302','303','304','305','306','307','308','318','310','311','314','319','356','312',
           '313','315','316','317','717','941','718','723','726','719','38') 
       ) as t 
 WHERE ( `81` LIKE CONCAT('%','Honda','%') ) 
   AND ( `78` BETWEEN CONVERT('1900', UNSIGNED INTEGER) 
                  AND CONVERT('2014', UNSIGNED INTEGER)  ) 
   AND (price BETWEEN CONVERT('2000', UNSIGNED INTEGER) 
                  AND CONVERT('5000', UNSIGNED INTEGER)  )  
   AND   (has_expired=0 OR non_expiry = 1) 
   AND should_show=1 

显然，这个查询效率不高，执行起来需要花费大量时间。请给我一个优化它的最佳方法。

根据以下步骤添加EXPLAIN命令的结果：

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,<derived2>,ALL,NULL,NULL,NULL,NULL,3386,"Using where; Using temporary;   Using filesort"
2,DERIVED,l,ALL,category_fk,NULL,NULL,NULL,4660,"Using where"
2,DERIVED,u,eq_ref,PRIMARY,PRIMARY,4,tradezone.l.created_by_user_id,1,
2,DERIVED,lt,eq_ref,PRIMARY,PRIMARY,4,tradezone.l.listing_type_id,1,
2,DERIVED,<derived5>,ALL,NULL,NULL,NULL,NULL,5021,
5,DERIVED,l,ALL,NULL,NULL,NULL,NULL,4660,
5,DERIVED,lt,eq_ref,PRIMARY,PRIMARY,4,tradezone.l.listing_type_id,1,
5,DERIVED,bi,eq_ref,"user_unique,user_fk_idx,user_id",user_unique,4,tradezone.l.created_by_user_id,1,
5,DERIVED,u,eq_ref,PRIMARY,PRIMARY,4,tradezone.l.created_by_user_id,1,
5,DERIVED,lo,eq_ref,PRIMARY,PRIMARY,4,tradezone.l.location_id,1,
5,DERIVED,c,eq_ref,PRIMARY,PRIMARY,4,tradezone.l.category_id,1,
5,DERIVED,hd,ref,hot_deals_listing_fk_idx,hot_deals_listing_fk_idx,4,tradezone.l.id,1,
9,"DEPENDENT SUBQUERY",lp,ref,listing_id_fk,listing_id_fk,4,tradezone.l.id,1,"Using where; Using filesort"
8,"DEPENDENT SUBQUERY",p,ref,listing_id_idx,listing_id_idx,5,tradezone.l.id,1,"Using where; Using filesort"
6,"DEPENDENT SUBQUERY",p,ref,listing_id_idx,listing_id_idx,5,tradezone.l.id,1,"Using where; Using filesort"
3,"DEPENDENT SUBQUERY",listing_attributes,ref,"listing_fk,attribute_fk",listing_fk,5,func,2,"Using where"

Answer 1

尝试以这种方式重写此查询：

SELECT distincd( id )
FROM vw_search l
JOIN listing_attributes l81 
ON l81.listing_id=l.id AND l81.attribute_id=81 
   AND l81.value LIKE CONCAT('%','Honda','%')
JOIN listing_attributes l78
ON l78.listing_id=l.id AND l78.attribute_id=78
   AND l78.value BETWEEN CONVERT('1900', UNSIGNED INTEGER) 
                     AND CONVERT('2014', UNSIGNED INTEGER)
WHERE l.category_id IN ('884','882','880' ....... 
                        .........................
                        .......................,'719','38' )
  AND l.price BETWEEN CONVERT('2000', UNSIGNED INTEGER) 
                  AND CONVERT('5000', UNSIGNED INTEGER)
  AND (l.has_expired=0 OR l.non_expiry = 1) 
  AND l.should_show=1