数据统计优化!

Question

CREATE TABLE `order_list` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `OrderID` varchar(50) NOT NULL,
  `UserID` varchar(50) NOT NULL,
  `ContactEmail` varchar(70) NOT NULL DEFAULT '',
  `CreateDate` datetime NOT NULL,
  `DeliveryDate` datetime DEFAULT NULL,
  `ShortIntro` varchar(100) DEFAULT NULL,
  `PaymentTypeID` int(11) DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `ContactEmail` (`ContactEmail`),
  KEY `OrderID` (`OrderID`)
) ENGINE=InnoDB AUTO_INCREMENT=517355 DEFAULT CHARSET=utf8;
CREATE TABLE `order_product_list` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `OrderID` int(11) NOT NULL DEFAULT '0',
  `ProductID` varchar(50) NOT NULL DEFAULT '0',
  `Quantity` int(11) NOT NULL DEFAULT '0',
  `Price` decimal(11,2) NOT NULL DEFAULT '0.00',
  `BundledSN` varchar(50) DEFAULT NULL,
  `Note` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `OrderID` (`OrderID`,`ProductID`)
) ENGINE=InnoDB AUTO_INCREMENT=837811 DEFAULT CHARSET=utf8;
CREATE TABLE `product_list` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ProductID` varchar(50) NOT NULL,
  `ProductCatalog` tinyint(4) DEFAULT NULL,
  `ProcessStatus` tinyint(4) NOT NULL DEFAULT '2',
  `ManufactureName` varchar(200) DEFAULT NULL,
  `ProductName` varchar(100) DEFAULT NULL,
  `ShortDesc` text,
  `LongDesc` longtext,
  `ReleaseDate` date DEFAULT NULL,
  `Available` int(2) NOT NULL DEFAULT '1',
  `TopDisplay` int(2) NOT NULL DEFAULT '0',
  `ManualOrder` int(2) NOT NULL DEFAULT '0',
  `Rank` decimal(5,2) NOT NULL DEFAULT '1.00',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ProductID` (`ProductID`,`ProductName`)
) ENGINE=MyISAM AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;我需要根据order_list.ContactEmail,product_list.ProductID和product_list.ProductName来统计数据.
三张表关联能取到想要的结果.但是速度太慢了.求大虾赐教.!

Answer 1

select count(0) from order_list inner join 
(select order_product_list.OrderID as orderID, order_product_list.productID,product_list.ProductName 
from order_product_list inner join product_list USING(productid) where 1=1 
group by order_product_list.orderid) as ProductInfo on order_list.id = ProductInfo.OrderID  
where 1=1 and CreateDate>='0001-01-01 00:00:00' and CreateDate <='2011-01-24 14:54:32'
这是我的统计SQL语句.

Answer 2

order_product_list 和 order_list 表的OrderID字段类型不一样,不会利用索引;

Answer 3

贴出你的
explain select ....
show index from tableName

Answer 4

楼主的explain 是怎么样的,贴出来,.

Answer 5

mysql> explain select count(0) from order_list inner join
    -> (select order_product_list.OrderID as orderID, order_product_list.product
ID,product_list.ProductName
    -> from order_product_list inner join product_list USING(productid) where 1=
1
    -> group by order_product_list.orderid) as ProductInfo on order_list.id = Pr
oductInfo.OrderID
    -> where 1=1 and CreateDate>='0001-01-01 00:00:00' and CreateDate <='2011-01
-24 14:54:32';
+----+-------------+--------------------+--------+---------------+-----------+---------+--------------------------------------------+--------+-------------+
| id | select_type | table              | type   | possible_keys | key       | key_len | ref                                        | rows   | Extra       |
+----+-------------+--------------------+--------+---------------+-----------+---------+--------------------------------------------+--------+-------------+
|  1 | PRIMARY     | <derived2>         | ALL    | NULL          | NULL      | NULL    | NULL                                       | 437335 |             |
|  1 | PRIMARY     | order_list         | eq_ref | PRIMARY       | PRIMARY   | 4       | ProductInfo.orderID                        |      1 | Using where |
|  2 | DERIVED     | order_product_list | index  | NULL          | OrderID   | 156     | NULL                                       | 179538 | Using index |
|  2 | DERIVED     | product_list       | ref    | ProductID     | ProductID | 152     | bluesoleil_en.order_product_list.ProductID |      4 | Using index |
+----+-------------+--------------------+--------+---------------+-----------+---------+--------------------------------------------+--------+-------------+
4 rows in set (5.81 sec)mysql> show index from order_list;
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| order_list |          0 | PRIMARY      |            1 | ID           | A         |      436321 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | ContactEmail |            1 | ContactEmail | A         |      436321 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | OrderID      |            1 | OrderID      | A         |      436321 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.28 sec)mysql> show index from order_product_list;
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table              | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| order_product_list |          0 | PRIMARY  |            1 | ID          | A         |      718155 |     NULL | NULL   |      | BTREE      |         |
| order_product_list |          0 | ID       |            1 | ID          | A         |      718155 |     NULL | NULL   |      | BTREE      |         |
| order_product_list |          1 | OrderID  |            1 | OrderID     | A         |      718155 |     NULL | NULL   |      | BTREE      |         |
| order_product_list |          1 | OrderID  |            2 | ProductID   | A         |      718155 |     NULL | NULL   |      | BTREE      |         |
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.11 sec)mysql> show index from product_list;
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| product_list |          0 | PRIMARY   |            1 | ID          | A         |          32 |     NULL | NULL   |      | BTREE      |         |
| product_list |          0 | ProductID |            1 | ProductID   | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| product_list |          0 | ProductID |            2 | ProductName | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

Answer 6

楼主 直接运行这条的计划怎么样,select count(0) from order_list inner join 
(select order_product_list.OrderID as orderID, order_product_list.productID,product_list.ProductName 
from order_product_list inner join product_list USING(productid) where 1=1 
group by order_product_list.orderid) as ProductInfo on order_list.id = ProductInfo.OrderID 

Answer 7

/* Result : "1 rows fetched (10.016 sec)" */
很慢.得10多秒才能行.

Answer 8

你的表设计和语句有些不容易明白。1.  select order_product_list.OrderID as orderID, order_product_list.productID,product_list.ProductName 
from order_product_list inner join product_list USING(productid) 
where 1=1 
group by order_product_list.orderid
这里的最后 group by order_product_list.orderid 的意义是什么？想去重？2.product_list表中为什么 ProductID不是主键？ 而（ProductID，ProductName）为联合UNIQUE KEY？
3. order_list表中为什么不直接用OrderID为主键？
如果表中有列可以直接做为主键，则不需要再去用AUTO_INCREMENT来创建一个序列号的主键。

Answer 9

建议改你的表结构为CREATE TABLE `order_list` (
  `OrderID` varchar(50) NOT NULL,
  `UserID` varchar(50) NOT NULL,
  `ContactEmail` varchar(70) NOT NULL DEFAULT '',
  `CreateDate` datetime NOT NULL,
  `DeliveryDate` datetime DEFAULT NULL,
  `ShortIntro` varchar(100) DEFAULT NULL,
  `PaymentTypeID` int(11) DEFAULT '0',
  PRIMARY KEY (`OrderID`),
  KEY `ContactEmail` (`ContactEmail`),
  KEY `DeliveryDate` (`DeliveryDate`),
  KEY `CreateDate` (`CreateDate`)
) ENGINE=InnoDB AUTO_INCREMENT=517355 DEFAULT CHARSET=utf8;CREATE TABLE `order_product_list` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `OrderID` int(11) NOT NULL DEFAULT '0',
  `ProductID` varchar(50) NOT NULL DEFAULT '0',
  `Quantity` int(11) NOT NULL DEFAULT '0',
  `Price` decimal(11,2) NOT NULL DEFAULT '0.00',
  `BundledSN` varchar(50) DEFAULT NULL,
  `Note` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `OrderID` (`OrderID`,`ProductID`)
  KEY `ProductID` (`ProductID`)
) ENGINE=InnoDB AUTO_INCREMENT=837811 DEFAULT CHARSET=utf8;CREATE TABLE `product_list` (
  `ProductID` varchar(50) NOT NULL,
  `ProductCatalog` tinyint(4) DEFAULT NULL,
  `ProcessStatus` tinyint(4) NOT NULL DEFAULT '2',
  `ManufactureName` varchar(200) DEFAULT NULL,
  `ProductName` varchar(100) DEFAULT NULL,
  `ShortDesc` text,
  `LongDesc` longtext,
  `ReleaseDate` date DEFAULT NULL,
  `Available` int(2) NOT NULL DEFAULT '1',
  `TopDisplay` int(2) NOT NULL DEFAULT '0',
  `ManualOrder` int(2) NOT NULL DEFAULT '0',
  `Rank` decimal(5,2) NOT NULL DEFAULT '1.00',
  PRIMARY KEY (`ProductID`)
) ENGINE=MyISAM AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;

Answer 10

group by order_product_list.orderid 这我确实是用来去重的.
下面那两个问题是最开始创建表的人留下来的问题吧.!

Answer 11

那这个查询想实现的功能是什么？ 每个订单中只取一个产品？如果从常理来看，假设每个订单中都有行项目order_product_list记录，每个order_product_list记录中的产品都是product_list中的，则你的查询语句和下面语句是等效的。
select count(*) 
from order_list
where 1=1 
and CreateDate>='0001-01-01 00:00:00' 
and CreateDate <='2011-01-24 14:54:32'除非你的订单中有 空订单，order_list中有记录，但order_product_list没有记录
或者有空产品号，order_product_list中产品编号，但在product_list不存在这个产品。

Answer 12

楼主从你的sql的explain上面看来改建的索引都建的,没什么好优化了.
可能要加的话,也只在 CreateDate上面建一个索引..