sql优化

mysql> EXPLAIN
    -> SELECT IP,ACTS,IPACTS,GRP_ID,SUM(ACCESS_NUM),CUS_ID, SUM(SORT_TIMES),SUM(FLOW_SIZE),SUM(KEYWORD_TIMES),
    -> RESERVED1_THRESHHOLD, RESERVED2_THRESHHOLD,RESERVED3_THRESHHOLD
    -> FROM BHV_U_IN_TMP_XXX GROUP BY GRP_ID,IPACTS ORDER BY NULL;
+----+-------------+------------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+------------------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | BHV_U_IN_TMP_XXX | ALL  | NULL          | NULL | NULL    | NULL | 3584 | Using temporary |
+----+-------------+------------------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.00 sec)mysql> desc BHV_U_IN_TMP_XXX;
+----------------------+---------------------+------+-----+---------+-------+
| Field                | Type                | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------+-------+
| IP                   | int(10) unsigned    | NO   |     | NULL    |       |
| ACTS                 | varchar(64)         | YES  |     | NULL    |       |
| IPACTS               | varchar(64)         | YES  |     | NULL    |       |
| GRP_ID               | int(10) unsigned    | YES  |     | NULL    |       |
| ACCESS_NUM           | int(10) unsigned    | YES  |     | NULL    |       |
| CUS_ID               | int(10) unsigned    | YES  |     | NULL    |       |
| SORT_TIMES           | bigint(20) unsigned | NO   |     | NULL    |       |
| FLOW_SIZE            | bigint(20) unsigned | NO   |     | NULL    |       |
| KEYWORD_TIMES        | int(10) unsigned    | NO   |     | NULL    |       |
| RESERVED1_THRESHHOLD | int(10) unsigned    | YES  |     | NULL    |       |
| RESERVED2_THRESHHOLD | int(10) unsigned    | YES  |     | NULL    |       |
| RESERVED3_THRESHHOLD | int(10) unsigned    | YES  |     | NULL    |       |
+----------------------+---------------------+------+-----+---------+-------+
上面的的表是myisam，没有索引。请问如何优化，我加了
CREATE INDEX idx_GRP_IPACTS ON BHV_U_IN_TMP_XXX(GRP_ID,IPACTS,ACCESS_NUM,SORT_TIMES,FLOW_SIZE,KEYWORD_TIMES)
但还是全表扫描，用force index 虽然用到索引，但是通过profiling 查看，消耗的差不多。没加索引的时候，时间消耗在Copying to tmp table上，用了索引时间消耗在 Sending data上面。、
改成innodb存储引擎，虽然不加force index 也可以用到索引，但是消耗上面差不多，没有明显的效果。表结构：CREATE TABLE `BHV_U_IN_TMP_XXX` (
  `IP` int(10) unsigned NOT NULL,
  `ACTS` varchar(64) default NULL,
  `IPACTS` varchar(64) default NULL,
  `GRP_ID` int(10) unsigned default NULL,
  `ACCESS_NUM` int(10) unsigned default NULL,
  `CUS_ID` int(10) unsigned default NULL,
  `SORT_TIMES` bigint(20) unsigned NOT NULL,
  `FLOW_SIZE` bigint(20) unsigned NOT NULL,
  `KEYWORD_TIMES` int(10) unsigned NOT NULL,
  `RESERVED1_THRESHHOLD` int(10) unsigned default NULL,
  `RESERVED2_THRESHHOLD` int(10) unsigned default NULL,
  `RESERVED3_THRESHHOLD` int(10) unsigned default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312

解决方案 »

  1.   

    坐个沙发BHV_U_IN_TMP_XXX(GRP_ID,IPACTS,ACCESS_NUM,SORT_TIMES,FLOW_SIZE,KEYWORD_TIMES)下面这几个字段加到索引里没任何用处吧ACCESS_NUM,SORT_TIMES,FLOW_SIZE,KEYWORD_TIMES另外ORDER BY NULL有什么作用？
      

  2.   


    不管有没有用到，起码这个索引的前2个字段符合order by的字段，也会用到。但是实际就是不用到这个索引。innodb是可以用到的，myisam和innodb的索引不同，所以我也就在聚合函数的字段上加了。order by null 的作用及时取消  Using filesort 的排序操作。
      

  3.   

    另外一种情况是GRP_ID,IPACTS这2个字段的选择性太差了，MYSQL觉得不划算，还是进行了全表扫描。
      

  4.   

    order by null 的作用及时取消 Using filesort 的排序操作。如果原来已经有order by a了，这时候还有filesort的话，是不是变成order by a,null就可以了？？
    我还没做测试的。
      

  5.   

    http://topic.csdn.net/u/20110511/16/8d96d410-88c0-49d3-a822-9a6cc2de3441.html
    你看下我这个帖子。myisam用不到索引，innodb能用到，这也是有可能的，因为索引是存储引擎实现的，不是MYSQL SERVER实现的。
    我也没想明白为什么myisam 不用，等高手来回答。
      

  6.   

    sql优化非常有限，除非之前用了非常差的执行计划
      

  7.   


    执行计划不是mysql本身的优化器去选择的吗，怎么看是好还是不好？
      

  8.   

    创建个 (GRP_ID,IPACTS) 索引
      

  9.   

    我上面提到创建多列索引包含了这个,但在myisam里面无效,而在innodb上有效,(估计和他们的索引机制有关系),我目前的做法是用了覆盖索引,把temporary table的问题解决了,但是通过profile查看,没有覆盖索引的时候时间消耗在create tmp table,copy tmp table,removing tmp table 上面,sending data 上面消耗时间不大;但是用了覆盖索引,虽然临时表没了,但是在sending data上面的时间消耗是前者的2倍.我对sending data 的说明不是很清楚,按理来说他们发送的数据应该是一样的,为什么时间不一样.
    请问版主这个的时间是指哪个上面的?
      

  10.   

    贴出你的 show index 以供分析。
      

  11.   

    题目中的那个sql的explain 是没有任何索引的,我试了很多个索引方法,都不行,单独的加 (GRP_ID,IPACTS) 索引也不行,也是一样的结果,(MYISAM表,innodb表则可以用到)
    我又加了覆盖索引:
    CREATE INDEX idx_GRP_IPACTS ON BHV_U_IN_TMP_XXX(GRP_ID,IPACTS,ACCESS_NUM,SORT_TIMES,FLOW_SIZE,KEYWORD_TIMES,IP,ACTS,CUS_ID,RESERVED1_THRESHHOLD, RESERVED2_THRESHHOLD,RESERVED3_THRESHHOLD)
    可以用到,但是就是遇到12楼的这个问题.
      

  12.   

    (MYISAM表不行,innodb表则可以用到)
      

  13.   

      首先  ，group  by 用到索引有几种实现方式    
    1   松散索引扫描实现  但是松散扫描的聚合函数有个条件 就是只能用到 max  和  min  两个聚合函数  ，这是  group  by  性能最高的实现方式  ，楼主的  query  不满足
    2 使用紧凑索引扫描法  
     当不满足上面的1时  ，就考虑使用第2方法 ，但是2 中也有限制条件  
    限制条件 1 group  by字段必须是索引前面的连续字段
             2 这里忘了  ，高性能mysql上有的楼主的querry可能两种都不满足   所以选择了tmp  table
      

  14.   


    你没看这个帖子啊？
    http://topic.csdn.net/u/20110511/16/8d96d410-88c0-49d3-a822-9a6cc2de3441.htmlsending data is not sending data
    每个标识项标识的时间长度是上一个标识项执行开始到本标识项执行开始的时间，而不是本标识项执行的时间。
      

  15.   


    优化group by 的3个方法我知道，上面这个sql 实现不了松散索引，虽然临时表也是一种方法，但是用临时表数据大的话，会对性能造成严重的后果。故我就用覆盖索引来取消临时表，但是sending data 过大，不明白为什么。
      

  16.   

    回20楼
    依据就是我的测试结果。只是多了个临时表去重，SENDING DATA的时间就完全不一样，证明SENDING DATA不是“发送数据”的意思。
      

  17.   

    我在机子上试验了哈，不管你怎么创建索引，只要是myisam都不会用到，除了你  force index  ，既然用不到还不如不创建，这样还能减少开销，只能建议楼主把key_buffer_size 调整哈，另外预加载key  cache  也没用，根本就不会用到