SQL优化 ~~ 出现Using temporary; Using filesort

Question

表结构：
+---------------+--------------+------+-----+---------------------+-------+
| Field         | Type         | Null | Key | Default             | Extra |
+---------------+--------------+------+-----+---------------------+-------+
| ID            | int(11)      | NO   |     | NULL                |       |
| USERID        | varchar(50)  | YES  |     | NULL                |       |
| APPID         | varchar(50)  | YES  |     | NULL                |       |
| LOGINTIME     | datetime     | NO   | MUL | 0000-00-00 00:00:00 |       |
| VERIFYRESULT  | varchar(20)  | YES  |     | NULL                |       |
+---------------+--------------+------+-----+---------------------+-------+意图：按APPID,USERID分组，并统计VERIFYRESULT等于0000和不等于0000的个数；
执行SQL语句：
explain partitions select a.APPID as appid, a.USERID as userid, 
sum(case when a.VERIFYRESULT='0000' then 1 else 0 end) as s1, 
sum(case when a_.VERIFYRESULT<>'0000' then 1 else 0 end) as s2 
from isp.SAM_AUTHLOG a
where a.LOGINTIME between '2010-01-01 00:00:00' and '2010-06-30 23:59:59'
group by a.APPID , a.USERID limit 10 \G
结果：
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: authlog0_
   partitions: p1
         type: range
possible_keys: logintime
          key: logintime
      key_len: 8
          ref: NULL
         rows: 496691
        Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)意图：和上面一样，但只是计算总的记录数；
执行SQL语句：
explain partitions select sum(c) from (
select count(distinct USERID) c from SAM_AUTHLOG a
where a.LOGINTIME between '2010-01-01 00:00:00' and '2010-03-08 23:59:59' 
group by a.APPID
) tmp \G
结果：
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
        Extra:
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: a
   partitions: p1
         type: ALL
possible_keys: logintime
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10500000
        Extra: Using filesort
2 rows in set (9.36 sec)以上两条SQL都需要进行优化处理，急需优化方案，谢谢！
问题解决，马上结贴。

Answer 1

你要是不用排序数据的话 可以在group by 后面加上order by null试试看

Answer 2

第一种情况请把你LOGINTIME   字段的索引贴出来看看，第二种不建议你使用子查询，试试改成JOIN方式

Answer 3

加了个联合索引 `APPID`, `USERID` ； 查询的问题解决了；
但计算个数 还是很慢。

Answer 4

只是对一张表操作， 用JOIN 怎么改呢？select count(1) from (
select 1
from isp.SAM_AUTHLOG authlog0_ 
where authlog0_.LOGINTIME between '2010-01-01 00:00:00' and '2010-01-30 23:59:59'
group by authlog0_.APPID , authlog0_.USERID
) tmp;select sum(c) from (
select count(distinct authlog0_.USERID) c
from isp.SAM_AUTHLOG authlog0_ 
where authlog0_.LOGINTIME between '2010-01-01 00:00:00' and '2010-06-30 23:59:59'
group by authlog0_.APPID
) tmp;以上两种方式通过索引优化好像都不行，不知道能不能改SQL进行优化？

Answer 5

select count(*) from (
select USERID from (
select authlog0_.APPID,authlog0_.USERID
from isp.SAM_AUTHLOG authlog0_ 
where authlog0_.LOGINTIME between '2010-01-01 00:00:00' and '2010-06-30 23:59:59'
group by authlog0_.APPID,authlog0_.USERID) a1 group by USERID) a2速度是否会快点

Answer 6

从你的执行计划分析，第二种情况练索引都没用上，而是进行了全表扫描，而且还有排序操作，你试试将LOGINTIME 、USERID和APPID建立组合索引试试看

Answer 7

select sum(c) from (
select count(distinct authlog0_.USERID) c
from isp.SAM_AUTHLOG authlog0_ 
where authlog0_.LOGINTIME between '2010-01-01 00:00:00' and '2010-12-30 23:59:59'
group by authlog0_.APPID
) tmp
还是上面的SQL，使用联合索引 `APPID`, `USERID`, `LOGINTIME` ，查询在10秒左右；
能在SQL写法上作优化吗？