Mysql:Using filesort处理不掉.唉.

Question

表结构:
CREATE TABLE sarticle
(
    id INTEGER(20) NOT NULL AUTO_INCREMENT,
    unitid INTEGER(20) default 0 NOT NULL,
    siteid INTEGER(20) default 0 NOT NULL,
    mtype TINYINT(4) default 0 NOT NULL,
    webarticle LONGTEXT NOT NULL,
    publishtime DATETIME,
    downloadtime DATETIME,
    createtime DATETIME,
    md5 VARCHAR(16) NOT NULL,
    organizationid INTEGER(20) default 0 NOT NULL,
    compressed CHAR(1) default '0' NOT NULL,
    fingerprint CHAR(32) NOT NULL,
    del CHAR(1) default '0',
    PRIMARY KEY(id),
    UNIQUE (md5, unitid),
    INDEX sarticle_pus (publishtime, unitid, siteid),
    INDEX sarticle_cus (createtime, unitid, siteid));查询语句:
SELECT sarticle.id, sarticle.unitid, sarticle.siteid, sarticle.mtype, sarticle.webarticle, sarticle.publishtime, sarticle.downloadtime, sarticle.createtime, sarticle.md5, sarticle.organizationid, sarticle.compressed, sarticle.fingerprint, sarticle.del FROM sarticle WHERE (sarticle.publishtime>='20100709162727' AND sarticle.publishtime<='20100712162727') AND sarticle.unitid IN (3,5,2,4) AND sarticle.siteid IN (28,960,63,41,1105,27,14,987,1256,24,68,37,33,15,11,23,12,7,18,986,38,31,140,32,21,10,35,13,26,988,9,19,8) GROUP BY sarticle.md5 ORDER BY sarticle.publishtime DESC LIMIT 21explain:*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sarticle
         type: range
possible_keys: sarticle_pus
          key: sarticle_pus
      key_len: 13
          ref: NULL
         rows: 66
        Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)当数据量增大时,mysql总出现coping to tmp......请大侠排忧解难!!查阅好久也没解决..

Answer 1

show index from sarticle;贴出来分析一下。应该是索引不合理造成的。

Answer 2

这样的检索只能使用文件排序您这种条件是范围条件(><in都属于),而且多个范围, 所以能使用前置索引检索的话,就不能用同一个索引排序; 而不能使用索引排序的话,那么就会使用文件排序 Using filesort而且您还加了一个group by 这样会生成临时表 Using temporary 而临时表是没有索引的,没有索引要排序,那么就会使用文件排序 Using filesortWHERE (sarticle.publishtime>='20100709162727' AND sarticle.publishtime<='20100712162727') AND sarticle.unitid IN (3,5,2,4) AND sarticle.siteid IN (28,960,63,41,1105,27,14,987,1256,24,68,37,33,15,11,23,12,7,18,986,38,31,140,32,21,10,35,13,26,988,9,19,8) GROUP BY sarticle.md5 ORDER BY sarticle.publishtime DESC LIMIT 21

Answer 3

Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上 
试试ORDER BY publishtime, unitid, siteid

Answer 4

select userid from a group by userid 但是我仔细查看，发现只要sql用到 group by
他的执行计划就必然是 Using temporary 

Answer 5

你这个需求就很诡异。
按照其他数据库，这条语句肯定是会出错的。
因为sarticle.id, sarticle.unitid, sarticle.siteid, sarticle.mtype等等，这些字段既不在group by中，也不在聚集函数中。建议你这样试验一下：
①把select语句后面的 ORDER BY sarticle.publishtime DESC LIMIT 21删掉，因为加上这个语句的话只能是在group by生成的临时表中再进行排序。
②添加这样一个索引 KEY `idx_test` (`publishtime`,`unitid`,`siteid`,`md5`)，group by本身就是一个排序操作。本来在我的数据库上面已经把表建好了，按理说可以试验的，但是mysql的explain的结果会收到表中数据的影响，是一个统计的结果，建议你在包含数据的表中试验，最好试验出来之后给我发个站内消息，确认一下我的猜测。