数据库查询优化,在线等候.!

Question

这是我所创建的视图CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_order_paid` AS 
  select 
    cast(`order_list`.`CreateDate` as date) AS `CreateDate`,
    sum(((`order_list`.`SendMailStatus` = 0) and (`order_list`.`OrderStatus` = 2))) AS `PaidDirectly`,
    sum(((`order_list`.`SendMailStatus` = 1) and (`order_list`.`OrderStatus` = 2))) AS `PaidAfterRemind`,
    sum(((`order_list`.`SendMailStatus` = 3) and (`order_list`.`OrderStatus` = 2))) AS `PaidAfterInquiry`,
    sum((`order_list`.`OrderStatus` = 2)) AS `PaidStat`,
    count(0) AS `OrderStat` 
  from 
    `order_list` 
  group by 
    cast(`order_list`.`CreateDate` as date) 
  order by 
    `order_list`.`CreateDate`;这是Order_List表的索引情况.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         |      434643 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | ContactEmail |            1 | ContactEmail | A         |      434643 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | OrderID      |            1 | OrderID      | A         |      434643 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | CreateDate   |            1 | CreateDate   | A         |      434643 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.34 sec)explain 语句mysql> explain select
    ->     cast(`order_list`.`CreateDate` as date) AS `CreateDate`,
    ->     sum(((`order_list`.`SendMailStatus` = 0) and (`order_list`.`OrderStatus` = 2))) AS `PaidDirectly`,
    ->     sum(((`order_list`.`SendMailStatus` = 1) and (`order_list`.`OrderStatus` = 2))) AS `PaidAfterRemind`,
    ->     sum(((`order_list`.`SendMailStatus` = 3) and (`order_list`.`OrderStatus` = 2))) AS `PaidAfterInquiry`,
    ->     sum((`order_list`.`OrderStatus` = 2)) AS `PaidStat`,
    ->     count(0) AS `OrderStat`
    ->   from
    ->     `order_list`
    ->   group by
    ->     cast(`order_list`.`CreateDate` as date)
    ->   order by
    ->     `order_list`.`CreateDate`;
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | order_list | ALL  | NULL          | NULL | NULL    | NULL | 436880 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.00 sec)这视图的查询速度太慢了.数据量不大,几十万而已,却每次查询都得3秒以上,求高手赐教.

Answer 1

在表中添加一列 CreateDAY，
update order_list set CreateDAY=date(CreateDate)然后GROUP BY CreateDAY否则没有索引可以利用。

Answer 2

group by  cast(`order_list`.`CreateDate` as date)用函数好像用不到索引了,像版主说的这样试试.

Answer 3

说的很对，group by后面的是cast(`order_list`.`CreateDate` as date)函数，mysql目前的5.1版本不支持函数索引。mysql6.0据说会增加这个功能的。

Answer 4

是在Order_List表中创建CreateDAY吗?
我在Order_list表中创建了CreateDAY,然后在视图里面GROUP BY CreateDAY,但是结果还是没多大用.!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 '',
  `OrderStatus` tinyint(4) NOT NULL DEFAULT '1',
  `CreateDate` datetime NOT NULL,
  `SendMailStatus` int(11) DEFAULT '0',
  `CreateDAY` date DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ContactEmail` (`ContactEmail`),
  KEY `OrderID` (`OrderID`),
  KEY `CreateDate` (`CreateDate`)
) ENGINE=InnoDB AUTO_INCREMENT=517355 DEFAULT CHARSET=utf8;select 
    cast(`order_list`.`CreateDate` as date) AS `CreateDate`,
    sum(((`order_list`.`SendMailStatus` = 0) and (`order_list`.`OrderStatus` = 2))) AS `PaidDirectly`,
    sum(((`order_list`.`SendMailStatus` = 1) and (`order_list`.`OrderStatus` = 2))) AS `PaidAfterRemind`,
    sum(((`order_list`.`SendMailStatus` = 3) and (`order_list`.`OrderStatus` = 2))) AS `PaidAfterInquiry`,
    sum((`order_list`.`OrderStatus` = 2)) AS `PaidStat`,
    count(0) AS `OrderStat` 
  from 
    `order_list` 
  group by 
    `order_list`.`CreateDAY` 
  order by 
    `order_list`.`CreateDate`;
mysql> explain select
    ->     cast(`order_list`.`CreateDate` as date) AS `CreateDate`,
    ->     sum(((`order_list`.`SendMailStatus` = 0) and (`order_list`.`OrderStatus` = 2))) AS `PaidDirectly`,
    ->     sum(((`order_list`.`SendMailStatus` = 1) and (`order_list`.`OrderStatus` = 2))) AS `PaidAfterRemind`,
    ->     sum(((`order_list`.`SendMailStatus` = 3) and (`order_list`.`OrderStatus` = 2))) AS `PaidAfterInquiry`,
    ->     sum((`order_list`.`OrderStatus` = 2)) AS `PaidStat`,
    ->     count(0) AS `OrderStat`
    ->   from
    ->     `order_list`
    ->   group by
    ->     `order_list`.`CreateDAY`
    ->   order by
    ->     `order_list`.`CreateDAY`;
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | order_list | ALL  | NULL          | NULL | NULL    | NULL | 437440 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+

Answer 5

楼主你没有加索引啊。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 '',
  `OrderStatus` tinyint(4) NOT NULL DEFAULT '1',
  `CreateDate` datetime NOT NULL,
  `SendMailStatus` int(11) DEFAULT '0',
  `CreateDAY` date DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ContactEmail` (`ContactEmail`),
  KEY `OrderID` (`OrderID`),
  KEY `CreateDate` (`CreateDate`)
  KEY `CreateDAY` (`CreateDAY`)
)

Answer 6

建立索引index(CreateDAY,SendMailStatus,OrderStatus)试试;

Answer 7

1· 建议你把原来的那个字段改成datealter table order_list CreateDate CreateDate Date;2. 改sql，在里面增加一个虚拟列吧：CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_order_paid` AS 
  select 
    cast(`ol`.`CreateDate` as date) AS `CreateDate`,
    sum(((`ol`.`SendMailStatus` = 0) and (`ol`.`OrderStatus` = 2))) AS `PaidDirectly`,
    sum(((`ol`.`SendMailStatus` = 1) and (`ol`.`OrderStatus` = 2))) AS `PaidAfterRemind`,
    sum(((`ol`.`SendMailStatus` = 3) and (`ol`.`OrderStatus` = 2))) AS `PaidAfterInquiry`,
    sum((`ol`.`OrderStatus` = 2)) AS `PaidStat`,
    count(0) AS `OrderStat` 
  from 
    (select order_list.*, cast(`order_list`.`CreateDate` as date) as date1 from `order_list`) ol
  group by 
    ol.date1 
  order by 
    ol.date1;

Answer 8

创建了CreateDAY之后,还是无法使用索引.mysql> EXPLAIN select
    ->     cast(`order_list`.`CreateDate` as date) AS `CreateDate`,
    ->     sum(((`order_list`.`SendMailStatus` = 0) and (`order_list`.`OrderStatus` = 2))) AS `PaidDirectly`,
    ->     sum(((`order_list`.`SendMailStatus` = 1) and (`order_list`.`OrderStatus` = 2))) AS `PaidAfterRemind`,
    ->     sum(((`order_list`.`SendMailStatus` = 3) and (`order_list`.`OrderStatus` = 2))) AS `PaidAfterInquiry`,
    ->     sum((`order_list`.`OrderStatus` = 2)) AS `PaidStat`,
    ->     count(0) AS `OrderStat`
    ->   from
    ->     `order_list`
    ->   group by
    ->     `order_list`.`CreateDAY`
    ->   order by
    ->     `order_list`.`CreateDAY`;
+----+-------------+------------+-------+---------------+-----------+---------+------+--------+-------+
| id | select_type | table      | type  | possible_keys | key       | key_len |ref  | rows   | Extra |
+----+-------------+------------+-------+---------------+-----------+---------+------+--------+-------+
|  1 | SIMPLE      | order_list | index | NULL          | CreateDAY | 4       |NULL | 437089 |       |
+----+-------------+------------+-------+---------------+-----------+---------+------+--------+-------+
1 row in set (0.00 sec)而且查询速度更慢了.

Answer 9

新增加的CreateDAY字段上，你建立了索引没有啊？

Answer 10

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         |      437089 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | ContactEmail |            1 | ContactEmail | A         |      437089 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | OrderID      |            1 | OrderID      | A         |      437089 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | CreateDate   |            1 | CreateDate   | A         |      437089 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | CreateDAY    |            1 | CreateDAY    | A         |      437089 |     NULL | NULL   | YES  | BTREE      |         |
+------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.77 sec)

Answer 11

速度不可能慢吧,你上面的explain 是索引扫描了,不是全表数据扫描. 

Answer 12

没建立之前 你是extra 是:Using temporary; Using filesort
现在没了,
你说时间更久了..楼主是怎么看他执行的时间的?

Answer 13

我是用EMS SQL Manager.执行SQL语句以后会自动返回所用的时间.!

Answer 14

建议用 mysql workbench吧，这个比较标准的。