SQL 优化求助

Question

逻辑场景
商品销售统计分析
需求 
统计在 某一时间段内 以各商品分类下销售的商品为纬度 产生的购买记录 我的sql是这样子的
商品表 zhaipin_goods
商品分类表 zhaipin_category
订单表 zhaipin_order_info
订单商品表 zhaipin_order_goods
我写的SQLSELECT 
C.cat_name AS cat_name,
SUM(OG.goods_number) AS total_sell_num,
SUM(OI.order_amount) AS total_sell_amount 
FROM `zhaipin_category` AS C 
RIGHT JOIN `zhaipin_goods` AS G ON C.cat_id = G.cat_id 
RIGHT JOIN `zhaipin_order_goods` AS OG ON OG.goods_id=G.goods_id 
LEFT JOIN `zhaipin_order_info` AS OI ON OI.order_id=OG.order_id 
AND OI.pay_status = 2 
AND shipping_time >= ('1325318400'-8*60*60) 
AND shipping_time <= ('1338242092'-8*60*60)
GROUP BY C.cat_idEXPLAIN 的结果在线求助  数量级在100W的表

Answer 1

+----+-------------+-------+--------+-----------------------+---------+---------+--------------------+--------+---------------------------------+
| id | select_type | table | type   | possible_keys         | key     | key_len | ref                | rows   | Extra                           |
+----+-------------+-------+--------+-----------------------+---------+---------+--------------------+--------+---------------------------------+
|  1 | SIMPLE      | OG    | ALL    | NULL                  | NULL    | NULL    | NULL               | 726544 | Using temporary; Using filesort |
|  1 | SIMPLE      | G     | eq_ref | PRIMARY               | PRIMARY | 4       | dumpdb.OG.goods_id |      1 |                                 |
|  1 | SIMPLE      | C     | eq_ref | PRIMARY               | PRIMARY | 2       | dumpdb.G.cat_id    |      1 |                                 |
|  1 | SIMPLE      | OI    | eq_ref | PRIMARY,shipping_time | PRIMARY | 3       | dumpdb.OG.order_id |      1 |                                 |
+----+-------------+-------+--------+-----------------------+---------+---------+--------------------+--------+---------------------------------+
4 rows in set (0.07 sec)+----+-------------+-------+--------+-----------------------+---------+---------+--------------------+--------+---------------------------------+
| id | select_type | table | type   | possible_keys         | key     | key_len | ref                | rows   | Extra                           |
+----+-------------+-------+--------+-----------------------+---------+---------+--------------------+--------+---------------------------------+
|  1 | SIMPLE      | OG    | ALL    | NULL                  | NULL    | NULL    | NULL               | 726544 | Using temporary; Using filesort |
|  1 | SIMPLE      | G     | eq_ref | PRIMARY               | PRIMARY | 4       | dumpdb.OG.goods_id |      1 |                                 |
|  1 | SIMPLE      | C     | eq_ref | PRIMARY               | PRIMARY | 2       | dumpdb.G.cat_id    |      1 |                                 |
|  1 | SIMPLE      | OI    | eq_ref | PRIMARY,shipping_time | PRIMARY | 3       | dumpdb.OG.order_id |      1 |                                 |
+----+-------------+-------+--------+-----------------------+---------+---------+--------------------+--------+---------------------------------+
4 rows in set (0.07 sec)

Answer 2

CREATE TABLE `zhaipin_goods` (
  `goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `cat_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `goods_sn` varchar(60) NOT NULL DEFAULT '',
  `goods_name` varchar(120) NOT NULL DEFAULT '',
  `goods_name_style` varchar(60) NOT NULL DEFAULT '+',
  `click_count` int(10) unsigned NOT NULL DEFAULT '0',
  `brand_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `provider_name` varchar(100) NOT NULL DEFAULT '',
  `goods_number` int(11) unsigned NOT NULL DEFAULT '0',
  `goods_weight` decimal(10,3) unsigned NOT NULL DEFAULT '0.000',
  `et_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `promote_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `promote_start_date` int(11) unsigned NOT NULL DEFAULT '0',
  `promote_end_date` int(11) unsigned NOT NULL DEFAULT '0',
  `warn_number` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `keywords` varchar(255) NOT NULL DEFAULT '',
  `goods_brief` varchar(255) NOT NULL DEFAULT '',
  `goods_desc` text NOT NULL,
  `goods_thumb` varchar(255) NOT NULL DEFAULT '',
  `goods_img` varchar(255) NOT NULL DEFAULT '',
  `original_img` varchar(255) NOT NULL DEFAULT '',
  `is_real` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `extension_code` varchar(30) NOT NULL DEFAULT '',
  `is_on_sale` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `is_alone_sale` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `integral` int(10) unsigned NOT NULL DEFAULT '0',
  `add_time` int(10) unsigned NOT NULL DEFAULT '0',
  `sort_order` smallint(4) unsigned NOT NULL DEFAULT '100',
  `is_delete` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_best` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_new` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_hot` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_promote` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `bonus_type_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `last_update` int(10) unsigned NOT NULL DEFAULT '0',
  `goods_type` smallint(5) unsigned NOT NULL DEFAULT '0',
  `seller_note` varchar(255) NOT NULL DEFAULT '',
  `give_integral` int(11) NOT NULL DEFAULT '-1',
  `rank_integral` int(11) NOT NULL DEFAULT '-1',
  `suppliers_id` smallint(5) unsigned DEFAULT NULL,
  `is_check` tinyint(1) unsigned DEFAULT NULL,
  `goods_ppjs` text,
  `goods_rhzf` text,
  `goods_psfs` text,
  `goods_fpbx` text,
  `is_shipping` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `good_con` int(11) NOT NULL DEFAULT '0',
  `spsp` varchar(255) DEFAULT NULL,
  `goods_proxy_type` int(1) NOT NULL DEFAULT '0' COMMENT '商品代理类型：0普通商品|1代理商品|',
  PRIMARY KEY (`goods_id`),
  KEY `goods_sn` (`goods_sn`),
  KEY `brand_id` (`brand_id`),
  KEY `sort_order` (`sort_order`),
  KEY `promote_start_date` (`promote_start_date`),
  KEY `good_con` (`good_con`),
  KEY `misc` (`is_delete`,`is_on_sale`,`is_alone_sale`,`is_promote`,`is_hot`,`is_real`,`cat_id`),
  KEY `goods_proxy_type` (`goods_proxy_type`),
  KEY `cat_id` (`cat_id`,`is_delete`,`is_on_sale`)
) ENGINE=InnoDB AUTO_INCREMENT=5396486 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMICCREATE TABLE `zhaipin_order_goods` (
  `rec_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `goods_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `goods_name` varchar(120) NOT NULL DEFAULT '',
  `goods_sn` varchar(60) NOT NULL DEFAULT '',
  `goods_number` int(8) unsigned NOT NULL DEFAULT '1',
  `et_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `goods_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `goods_attr` text NOT NULL,
  `goods_ttr` float DEFAULT NULL,
  `send_number` int(8) unsigned NOT NULL DEFAULT '0',
  `is_real` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `extension_code` varchar(30) NOT NULL DEFAULT '',
  `parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `is_gift` smallint(5) unsigned NOT NULL DEFAULT '0',
  `goods_attr_id` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`rec_id`),
  KEY `order_id` (`order_id`),
  KEY `goods_id` (`goods_id`),
  KEY `goods_sn` (`goods_sn`),
  KEY `goods_sn_2` (`goods_sn`,`order_id`),
  KEY `order_id_2` (`order_id`,`goods_sn`)
) ENGINE=InnoDB AUTO_INCREMENT=789879 DEFAULT CHARSET=utf8CREATE TABLE `zhaipin_order_info` (
  `order_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `order_sn` varchar(20) NOT NULL DEFAULT '',
  `user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `suser_id` mediumint(8) unsigned NOT NULL,
  `order_status` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `shipping_status` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `pay_status` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `consignee` varchar(60) NOT NULL DEFAULT '',
  `country` smallint(5) unsigned NOT NULL DEFAULT '0',
  `province` smallint(5) unsigned NOT NULL DEFAULT '0',
  `city` smallint(5) unsigned NOT NULL DEFAULT '0',
  `district` smallint(5) unsigned NOT NULL DEFAULT '0',
  `address` varchar(255) NOT NULL DEFAULT '',
  `zipcode` varchar(60) NOT NULL DEFAULT '',
  `tel` varchar(60) NOT NULL DEFAULT '',
  `mobile` varchar(60) NOT NULL DEFAULT '',
  `email` varchar(60) NOT NULL DEFAULT '',
  `best_time` varchar(120) NOT NULL DEFAULT '',
  `sign_building` varchar(120) NOT NULL DEFAULT '',
  `postscript` text NOT NULL,
  `shipping_id` tinyint(3) NOT NULL DEFAULT '0',
  `shipping_name` varchar(120) NOT NULL DEFAULT '',
  `pay_id` tinyint(3) NOT NULL DEFAULT '0',
  `pay_name` varchar(120) NOT NULL DEFAULT '',
  `how_oos` varchar(120) NOT NULL DEFAULT '',
  `how_surplus` varchar(120) NOT NULL DEFAULT '',
  `pack_name` varchar(120) NOT NULL DEFAULT '',
  `card_name` varchar(120) NOT NULL DEFAULT '',
  `card_message` varchar(255) NOT NULL DEFAULT '',
  `inv_payee` varchar(120) NOT NULL DEFAULT '',
  `inv_content` varchar(120) NOT NULL DEFAULT '',
  `goods_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `shipping_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
  `insure_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
  `pay_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
  `pack_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
  `card_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
  `money_paid` decimal(10,2) NOT NULL DEFAULT '0.00',
  `surplus` decimal(10,2) NOT NULL DEFAULT '0.00',
  `integral` int(10) unsigned NOT NULL DEFAULT '0',
  `integral_money` decimal(10,2) NOT NULL DEFAULT '0.00',
  `bonus` decimal(10,2) NOT NULL DEFAULT '0.00',
  `order_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `from_ad` smallint(5) NOT NULL DEFAULT '0',
  `referer` varchar(255) NOT NULL DEFAULT '',
  `add_time` int(10) unsigned NOT NULL DEFAULT '0',
  `confirm_time` int(10) unsigned NOT NULL DEFAULT '0',
  `pay_time` int(10) unsigned NOT NULL DEFAULT '0',
  `received` date DEFAULT NULL,
  `shipping_time` int(10) unsigned NOT NULL DEFAULT '0',
  `pack_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `card_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `bonus_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `invoice_no` varchar(255) NOT NULL DEFAULT '',
  `extension_code` varchar(30) NOT NULL DEFAULT '',
  `extension_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `to_buyer` varchar(255) NOT NULL DEFAULT '',
  `pay_note` varchar(255) NOT NULL DEFAULT '',
  `agency_id` smallint(5) unsigned NOT NULL,
  `inv_type` varchar(60) NOT NULL,
  `tax` decimal(10,2) NOT NULL,
  `is_separate` tinyint(1) NOT NULL DEFAULT '0',
  `parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `discount` decimal(10,2) NOT NULL,
  `order_type` int(2) DEFAULT '0' COMMENT '订单类型：6网购，7代购，8百业联盟，9诚信商家',
  PRIMARY KEY (`order_id`),
  UNIQUE KEY `order_sn` (`order_sn`),
  KEY `user_id` (`user_id`),
  KEY `pay_time` (`pay_time`),
  KEY `shipping_time` (`shipping_time`),
  KEY `add_time` (`add_time`),
  KEY `consignee` (`consignee`),
  KEY `received` (`received`),
  KEY `suser_id` (`suser_id`),
  KEY `from_ad` (`from_ad`),
  KEY `misc` (`order_status`,`shipping_status`,`pay_status`,`pay_id`),
  KEY `order_type` (`order_type`)
) ENGINE=InnoDB AUTO_INCREMENT=3466951 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMICCREATE TABLE `zhaipin_order_goods` (
  `rec_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `goods_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `goods_name` varchar(120) NOT NULL DEFAULT '',
  `goods_sn` varchar(60) NOT NULL DEFAULT '',
  `goods_number` int(8) unsigned NOT NULL DEFAULT '1',
  `et_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `goods_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `goods_attr` text NOT NULL,
  `goods_ttr` float DEFAULT NULL,
  `send_number` int(8) unsigned NOT NULL DEFAULT '0',
  `is_real` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `extension_code` varchar(30) NOT NULL DEFAULT '',
  `parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `is_gift` smallint(5) unsigned NOT NULL DEFAULT '0',
  `goods_attr_id` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`rec_id`),
  KEY `order_id` (`order_id`),
  KEY `goods_id` (`goods_id`),
  KEY `goods_sn` (`goods_sn`),
  KEY `goods_sn_2` (`goods_sn`,`order_id`),
  KEY `order_id_2` (`order_id`,`goods_sn`)
) ENGINE=InnoDB AUTO_INCREMENT=789879 DEFAULT CHARSET=utf8

Answer 3

AND OI.pay_status = 2 
AND shipping_time >= ('1325318400'-8*60*60) 
AND shipping_time <= ('1338242092'-8*60*60)
这些是过滤条件不是连接条件吧，应该写成
where OI.pay_status = 2 
AND shipping_time >= ('1325318400'-8*60*60) 
AND shipping_time <= ('1338242092'-8*60*60)
我很奇怪shipping_time 是OI表的字段，但是你这么些为什么没有报错呢？
OG表的连接字段是OG.goods_id和OG.order_id这里也许要把两个字段建成组合索引（猜的），可以试试
还有都写成左连接好看些。

Answer 4

好的 
zhaipin_order_goods表
CREATE TABLE `zhaipin_order_goods` (
  `rec_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `goods_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `goods_name` varchar(120) NOT NULL DEFAULT '',
  `goods_sn` varchar(60) NOT NULL DEFAULT '',
  `goods_number` int(8) unsigned NOT NULL DEFAULT '1',
  `et_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `goods_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `goods_attr` text NOT NULL,
  `goods_ttr` float DEFAULT NULL,
  `send_number` int(8) unsigned NOT NULL DEFAULT '0',
  `is_real` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `extension_code` varchar(30) NOT NULL DEFAULT '',
  `parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `is_gift` smallint(5) unsigned NOT NULL DEFAULT '0',
  `goods_attr_id` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`rec_id`),
  KEY `order_id` (`order_id`),
  KEY `goods_id` (`goods_id`),
  KEY `goods_sn` (`goods_sn`),
  KEY `goods_sn_2` (`goods_sn`,`order_id`),
  KEY `order_id_2` (`order_id`,`goods_sn`)
) ENGINE=InnoDB AUTO_INCREMENT=789879 DEFAULT CHARSET=utf8

Answer 5

最后执行的代码是这样的
SELECT C.cat_name AS cat_name,SUM(OG.goods_number) AS total_sell_num,SUM(OI.order_amount) AS total_sell_amount FROM `zhaipin_category` AS C RIGHT JOIN `zhaipin_goods` AS G ON C.cat_id = G.cat_id RIGHT JOIN `zhaipin_order_goods` AS OG ON OG.goods_id=G.goods_id LEFT JOIN `zhaipin_order_info` AS OI ON OI.order_id=OG.order_id WHERE 1=1 AND OI.pay_status = 2 AND shipping_time >= '1325289600' AND shipping_time <= '1338220353' GROUP BY C.cat_id那个where 是我发问的时候不小心去掉的

Answer 6

刚我问一个以前的dba 同事 意识到 我这个是测试服务器上的 （公司的一个pc) 2G的内存 会不会有这个问题 我跑10W的就没问题 跑 70W的就卡死

Answer 7

zhaipin_order_goods表里把
goods_id和order_id这两个字段建成组合索引
谁在前谁在后我也不清楚，试一试。。