现有三张表联合查询，速度非常慢，有懂sql语句优化的大神吗？求大神赐教！
表dsc_attendance，这个表有按时间范围做分区
CREATE TABLE dsc_attendance (id varchar(60) NOT NULL COMMENT '主键',rfid varchar(20) DEFAULT NULL COMMENT 'rfid',gate_id varchar(15) DEFAULT NULL COMMENT '门id',state varchar(10) DEFAULT NULL COMMENT '进出类型',occurtime datetime NOT NULL COMMENT 'occurtime',
PRIMARY KEY (id,occurtime),
KEY gate_id (gate_id),
KEY state (state),
KEY rfid (rfid) USING BTREE,
KEY occurtime (occurtime) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (YEARWEEK(occurtime))
(
PARTITION p201601 VALUES LESS THAN (201601) ENGINE = InnoDB,
PARTITION p201602 VALUES LESS THAN (201602) ENGINE = InnoDB,
PARTITION p201603 VALUES LESS THAN (201603) ENGINE = InnoDB,
PARTITION p201604 VALUES LESS THAN (201604) ENGINE = InnoDB,
PARTITION p201605 VALUES LESS THAN (201605) ENGINE = InnoDB,
PARTITION p201606 VALUES LESS THAN (201606) ENGINE = InnoDB,
PARTITION p201607 VALUES LESS THAN (201607) ENGINE = InnoDB,
PARTITION p201608 VALUES LESS THAN (201608) ENGINE = InnoDB,
PARTITION p201609 VALUES LESS THAN (201609) ENGINE = InnoDB,
PARTITION p201610 VALUES LESS THAN (201610) ENGINE = InnoDB,
PARTITION p201611 VALUES LESS THAN (201611) ENGINE = InnoDB,
PARTITION p201612 VALUES LESS THAN (201612) ENGINE = InnoDB,
PARTITION p201613 VALUES LESS THAN (201613) ENGINE = InnoDB,
PARTITION p201614 VALUES LESS THAN (201614) ENGINE = InnoDB,
PARTITION p201615 VALUES LESS THAN (201615) ENGINE = InnoDB,
PARTITION p201616 VALUES LESS THAN (201616) ENGINE = InnoDB,
PARTITION p201617 VALUES LESS THAN (201617) ENGINE = InnoDB,
PARTITION p201618 VALUES LESS THAN (201618) ENGINE = InnoDB,
PARTITION p201619 VALUES LESS THAN (201619) ENGINE = InnoDB,
PARTITION p201620 VALUES LESS THAN (201620) ENGINE = InnoDB,
PARTITION p201621 VALUES LESS THAN (201621) ENGINE = InnoDB,
PARTITION p201622 VALUES LESS THAN (201622) ENGINE = InnoDB,
PARTITION p201623 VALUES LESS THAN (201623) ENGINE = InnoDB,
PARTITION p201624 VALUES LESS THAN (201624) ENGINE = InnoDB,
PARTITION p201625 VALUES LESS THAN (201625) ENGINE = InnoDB,
PARTITION p201626 VALUES LESS THAN (201626) ENGINE = InnoDB,
PARTITION p201627 VALUES LESS THAN (201627) ENGINE = InnoDB,
PARTITION p201628 VALUES LESS THAN (201628) ENGINE = InnoDB,
PARTITION p201629 VALUES LESS THAN (201629) ENGINE = InnoDB,
PARTITION p201630 VALUES LESS THAN (201630) ENGINE = InnoDB,
PARTITION p201631 VALUES LESS THAN (201631) ENGINE = InnoDB,
PARTITION p201632 VALUES LESS THAN (201632) ENGINE = InnoDB,
PARTITION p201633 VALUES LESS THAN (201633) ENGINE = InnoDB,
PARTITION p201634 VALUES LESS THAN (201634) ENGINE = InnoDB,
PARTITION p201635 VALUES LESS THAN (201635) ENGINE = InnoDB,
PARTITION p201636 VALUES LESS THAN (201636) ENGINE = InnoDB,
PARTITION p201637 VALUES LESS THAN (201637) ENGINE = InnoDB,
PARTITION p201638 VALUES LESS THAN (201638) ENGINE = InnoDB,
PARTITION p201639 VALUES LESS THAN (201639) ENGINE = InnoDB,
PARTITION p201640 VALUES LESS THAN (201640) ENGINE = InnoDB,
PARTITION p201641 VALUES LESS THAN (201641) ENGINE = InnoDB,
PARTITION p201642 VALUES LESS THAN (201642) ENGINE = InnoDB,
PARTITION p201643 VALUES LESS THAN (201643) ENGINE = InnoDB,
PARTITION p201644 VALUES LESS THAN (201644) ENGINE = InnoDB,
PARTITION p201645 VALUES LESS THAN (201645) ENGINE = InnoDB,
PARTITION p201646 VALUES LESS THAN (201646) ENGINE = InnoDB,
PARTITION p201647 VALUES LESS THAN (201647) ENGINE = InnoDB,
PARTITION p201648 VALUES LESS THAN (201648) ENGINE = InnoDB,
PARTITION p201649 VALUES LESS THAN (201649) ENGINE = InnoDB,
PARTITION p201650 VALUES LESS THAN (201650) ENGINE = InnoDB,
PARTITION p201651 VALUES LESS THAN (201651) ENGINE = InnoDB,
PARTITION p201652 VALUES LESS THAN (201652) ENGINE = InnoDB
)*/;
表t_s_base_user
CREATE TABLE t_s_base_user (ID varchar(32) NOT NULL,password varchar(100) DEFAULT NULL,realname varchar(50) DEFAULT NULL,username varchar(50) DEFAULT NULL,departid varchar(32) DEFAULT NULL,role_id varchar(36) DEFAULT NULL,userstatus varchar(50) DEFAULT NULL,
PRIMARY KEY (ID),
KEY FK_15jh1g4iem1857546ggor42et (departid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表t_s_depart
CREATE TABLE t_s_depart (ID varchar(32) NOT NULL,departname varchar(100) NOT NULL,parentdepartid varchar(32) DEFAULT NULL,seq int(5) DEFAULT NULL,
PRIMARY KEY (ID),
KEY FK_knnm3wb0bembwvm0il7tf6686 (parentdepartid),
CONSTRAINT fk_t_s_depart_pid FOREIGN KEY (parentdepartid) REFERENCES t_s_depart (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
需要优化的sql语句：
SELECT d.departname,b.realname,count(da.rfid) 'total',
SUM(da.state in ('3','4')) as 'danbian',
SUM(da.state in ('5','6')) as 'paihuai'
FROM dsc_attendance da
RIGHT JOIN t_s_base_user b ON b.rfid=da.rfid
LEFT JOIN t_s_depart d ON d.ID=b.departid
WHERE b.userstatus IS NULL AND b.role_id='402881b847945d60014794688f07000b'
AND da.occurtime BETWEEN '2016-12-16 00:00:00' AND '2016-12-16 23:59:59'
GROUP BY b.ID
ORDER BY d.seq,total
LIMIT 1,100;
查询的结果，查100条记录足足用了134秒，太慢了，求赐教：
explain的结果：
