我这语句还能优化吗。。

Question

索引优化数据库sql

Answer 1

还有数据比较慢 不是因为最外层用了一次除法  t1.c/t2.c rate
我把这一项删了还是慢

Answer 2

+----+-------------+--------------------------+------+----------------------------------+------------------+---------+------+------+----------------------------------------------+
| id | select_type | table                    | type | possible_keys                    | key              | key_len | ref  | rows | Extra                                        |
+----+-------------+--------------------------+------+----------------------------------+------------------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>               | ALL  | NULL                             | NULL             | NULL    | NULL | 3796 | Using temporary; Using filesort              | 
|  1 | PRIMARY     | <derived3>               | ALL  | NULL                             | NULL             | NULL    | NULL | 4625 | Using where                                  | 
|  3 | DERIVED     | nt_stat_num_product_line | ref  | idx_nt_stat_name,idx_nt_stat_day | idx_nt_stat_name | 195     |      | 5746 | Using where; Using temporary; Using filesort | 
|  2 | DERIVED     | nt_stat_num_product_line | ref  | idx_nt_stat_name,idx_nt_stat_day | idx_nt_stat_name | 195     |      | 4943 | Using where; Using temporary; Using filesort | 
+----+-------------+--------------------------+------+----------------------------------+------------------+---------+------+------+----------------------------------------------+

Answer 3

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3796
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4625
        Extra: Using where
*************************** 3. row ***************************
           id: 3
  select_type: DERIVED
        table: nt_stat_num_product_line
         type: ref
possible_keys: idx_nt_stat_name,idx_nt_stat_day
          key: idx_nt_stat_name
      key_len: 195
          ref: 
         rows: 5746
        Extra: Using where; Using temporary; Using filesort
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED
        table: nt_stat_num_product_line
         type: ref
possible_keys: idx_nt_stat_name,idx_nt_stat_day
          key: idx_nt_stat_name
      key_len: 195
          ref: 
         rows: 4943
        Extra: Using where; Using temporary; Using filesort

Answer 4

SELECT sum(row_num) c, product_line pl, `day`
                FROM nt_stat_num_product_line
                WHERE `day`>='20140921' AND `day`<='20141021' AND `name`='SNOOPY_HOST_COUNT'
                GROUP BY product_line, `day`
结果几千行，随便贴几行：
+------+---------------------------------+----------+
| c    | pl                              | day      |
+------+---------------------------------+----------+
|  633 | xx部门     | 20141001 | 
|  633 |  xx部门        | 20141002 | 
|  633 |  xx部门    | 20141003 | 
|  633 |  xx部门       | 20141004 | 
|  633 |  xx部门     | 20141005 | 
+------+---------------------------------+----------+执行计划（这里没针对group by 加组合索引 但是子查询本身已经够快了）
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: nt_stat_num_product_line
         type: ref
possible_keys: idx_nt_stat_name,idx_nt_stat_day
          key: idx_nt_stat_name
      key_len: 195
          ref: const
         rows: 2783
        Extra: Using where; Using temporary; Using filesort

Answer 5

问题在于 using temporary
那里面不做group by a ，b，c 直接返回所有结果集给上一层
上一次层 distinct a，b，c即可（我这样是有前提的：
结果集不group by的时候 本身不大 ，但是你在内层group by 到别的表字段的话，会有Using temporary; Using filesort
这种情就可以像我这样，拿出来group by 这样就是在临时内存里做了？
我猜想如果连接的时候group by 他是跨表做的？
）