MySql 语句优化

Question

select 
    cast(`order_list`.`CreateDate` as date) AS `CreateDay`,
    count(0) as OrderStat,
    sum(`order_list`.`OrderStatus` = 2) as PaidStat,
    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` 
  from 
    `order_list` 
  where 
    CreateDate >= '2010-11-26' and CreateDate <= '2010-12-30' 
  group by 
  date(`order_list`.`CreateDate`);
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         |      280590 |     NULL | NULL   |      | BTREE      |         |
| ORDER_LIST |          1 | CreateDate |            1 | CreateDate        | A         |          18 |     NULL | NULL   |      | BTREE      |         |
| ORDER_LIST |          1 | CreateDate |            2 | OrderID           | A         |      280590 |     NULL | NULL   |      | BTREE      |         |
| ORDER_LIST |          1 | CreateDate |            3 | ContactEmail      | A         |      280590 |     NULL | NULL   |      | BTREE      |         |
| ORDER_LIST |          1 | CreateDate |            4 | UserID            | A         |      280590 |     NULL | NULL   |      | BTREE      |         |
| ORDER_LIST |          1 | CreateDate |            5 | PaymentCurrencyID | A         |      280590 |     NULL | NULL   |      | BTREE      |         |
| ORDER_LIST |          1 | CreateDate |            6 | SendMailStatus    | A         |      280590 |     NULL | NULL   | YES  | BTREE      |         |
+------------+------------+------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+--
7 rows in set (0.08 sec)
mysql> explain select
    ->     cast(`order_list`.`CreateDate` as date) AS `CreateDay`,
    ->     count(0) as OrderStat,
    ->     sum(`order_list`.`OrderStatus` = 2) as PaidStat,
    ->     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`
    ->   from
    ->     `order_list`
    ->   where
    ->     CreateDate >= '2010-11-26' and CreateDate <= '2010-12-30'
    ->   group by
    ->   date(`order_list`.`CreateDate`);
+----+-------------+------------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+------------+------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | order_list | ALL  | CreateDate    | NULL | NULL    | NULL | 280590 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+--------+----------------------------------------------+请教各位大虾这怎么优化哈.! 急.在线等.!

Answer 1

`CreateDate`、`SendMailStatus`、`OrderStatus`建立复合索引试试

Answer 2

SHOW INDEX FROM tbl_name 

Answer 3

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      |      280590 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | CreateDate |            1 | CreateDate        | A      |          18 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | CreateDate |            2 | OrderID           | A      |      280590 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | CreateDate |            3 | ContactEmail      | A      |      280590 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | CreateDate |            4 | UserID            | A      |      280590 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | CreateDate |            5 | PaymentCurrencyID | A      |      280590 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | CreateDate |            6 | SendMailStatus    | A      |      280590 |     NULL | NULL   | YES  | BTREE      |         |
| order_list |          1 | OrderStat  |            1 | CreateDate        | A      |          12 |     NULL | NULL   |      | BTREE      |         |
| order_list |          1 | OrderStat  |            2 | SendMailStatus    | A      |          12 |     NULL | NULL   | YES  | BTREE      |         |
| order_list |          1 | OrderStat  |            3 | OrderStatus       | A      |          12 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
10 rows in set (0.17 sec)

Answer 4

select 
    cast(`order_list`.`CreateDate` as date) AS `CreateDay`,
    count(0) as OrderStat,
    sum(`order_list`.`OrderStatus` = 2) as PaidStat,
    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` 
  from 
    `order_list` 
  where 
    CreateDate >= '2010-11-26' and CreateDate <= '2010-12-30' 
  group by 
  date(`order_list`.`CreateDate`) order by null;,索引都有了.差不多就这样了..

Answer 5

强制使用OrderStat试试 在SQL语句中加上FORCE INDEX (OrderStat)

Answer 6

这已经是用OrderStat了. 这性能也还不错了.谢谢两位.!

Answer 7

最后面加个order by null 就好了,把Using filesort 去掉

Answer 8

忘了.Order by 后面我是要接 CreateDate的. 加上Order By CreateDate的话还是会有Using filesort