6表联合查询，有一个表数据有1万多，访问速度10多秒

Question

我们要做一个查询是由6表数据联合显示的，现在有一个表1万多数据，每次刷新都要10多秒才显示，各位帮忙给点建议，怎么做优化好呢？
sql :
SELECT com_cn_user.real_name,com_cn_client.clShortName,com_cn_product.skuNum1,com_cn_product.Arrival_cycle,com_cn_product.Buy,com_cn_chuku_detail.id,com_cn_chuku_detail.sku,com_cn_product.ItemNum,com_cn_product.Color,com_cn_product.model,com_cn_product.Title_CN,com_cn_product.Brand,sum(com_cn_chuku_detail.quantity) as quantity,com_cn_chuku_detail.re 
 FROM com_cn_chuku
 LEFT JOIN com_cn_chuku_detail ON com_cn_chuku.chukuNum = com_cn_chuku_detail.chukuNum 
 LEFT JOIN com_cn_product ON com_cn_chuku_detail.itemNum = com_cn_product.ItemNum 
 LEFT JOIN com_cn_supply ON com_cn_supply.syNum = com_cn_product.syNum 
 LEFT JOIN com_cn_user ON com_cn_user.usernum = com_cn_supply.usernum
 LEFT JOIN com_cn_client ON com_cn_client.clNum = com_cn_chuku.clNum
 where com_cn_chuku.state=2 and com_cn_chuku.submit_date between '2012-1-1' and '2012-06-15' group by com_cn_product.ItemNum ORDER BY Nullexplain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE com_cn_chuku ALL state NULL NULL NULL 177 Using where; Using temporary
1 SIMPLE com_cn_chuku_detail ref chukuNum chukuNum 4 com_backup.com_cn_chuku.chukuNum 47  
1 SIMPLE com_cn_product ref ItemNum ItemNum 47 com_backup.com_cn_chuku_detail.itemNum 1  
1 SIMPLE com_cn_supply ref syNum syNum 32 com_backup.com_cn_product.syNum 1  
1 SIMPLE com_cn_user ref usernum usernum 38 com_backup.com_cn_supply.usernum 1  
1 SIMPLE com_cn_client ref clNum clNum 32 com_backup.com_cn_chuku.clNum 2 

Answer 1

我有个疑问是，看到这个explain的结果，好像不会很慢，但是实际刷新还是很慢，是不是哪里要清除缓存。

Answer 2

com_cn_chuku 表结构贴出来……或者说这个表的索引，需要重新考虑

Answer 3

--
-- 表的结构 `com_cn_chuku`
--CREATE TABLE `com_cn_chuku` (
  `chukuNum` int(11) NOT NULL auto_increment,
  `usernum` varchar(12) NOT NULL,
  `clNum` varchar(12) NOT NULL,
  `Lead_single` varchar(15) NOT NULL,
  `Lead_Name` varchar(15) NOT NULL,
  `state` tinyint(1) NOT NULL,
  `shengorder` tinyint(1) NOT NULL,
  `submit_date` datetime NOT NULL,
  `re` varchar(200) default NULL,
  PRIMARY KEY  (`chukuNum`),
  KEY `usernum` (`usernum`),
  KEY `clNum` (`clNum`),
  KEY `state` (`state`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=236 ;

Answer 4

submit_date 就没索引么，扫全表……

Answer 5

有索引也不一定不扫全表……一下子说不清，hoho~~~~如果仅针对目前的搜索方式，state，submit_date联合索引更佳

Answer 6

我刚刚发现如果是按照6个表那样查询，还可以。查询慢的原因是，得到6个表的结果后，还要foreach循环，把itemNum的值传入到一个函数，可能是由于这个函数里面的SQL语句才慢的
EXPLAIN SELECT sum( com_cn_chuku_detail.quantity ) AS quantity
FROM com_cn_chuku_detail
JOIN com_cn_chuku ON com_cn_chuku.chukuNum = com_cn_chuku_detail.chukuNum
WHERE com_cn_chuku.state =2
AND com_cn_chuku.submit_date
BETWEEN '2012-1-1'
AND '2012-06-15'
AND com_cn_chuku_detail.itemNum =1012441
GROUP BY Date( com_cn_chuku.submit_date )
ORDER BY NULL 

Answer 7

刚刚建立联合索引。
id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  com_cn_chuku  ref  PRIMARY,state  state  1  const  118  Using where; Using temporary
1  SIMPLE  com_cn_chuku_detail  ref  chukuNum,itemNum  chukuNum  4  com_backup.com_cn_chuku.chukuNum  47  Using where

Answer 8

如果那个函数在mysql中创立存储过程，这样会不会快一些呢

Answer 9

问题解决了，原来是
EXPLAIN SELECT sum( com_cn_chuku_detail.quantity ) AS quantity
FROM com_cn_chuku_detail
JOIN com_cn_chuku ON com_cn_chuku.chukuNum = com_cn_chuku_detail.chukuNum
WHERE com_cn_chuku.state =2
AND com_cn_chuku.submit_date
BETWEEN '2012-1-1'
AND '2012-06-15'
AND com_cn_chuku_detail.itemNum =1012441
GROUP BY Date( com_cn_chuku.submit_date )
ORDER BY NULL 
 com_cn_chuku_detail.itemNum =1012441没有加单引号，加了单引号，就OK了。