mysql语句优化求教

现有三张表联合查询,速度非常慢,有懂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的结果:
图片说明

2个回答

原来可以写个视图来解决问题

u011503625
水表 回复情人节也放假:把要查的字段建成一个视图,再select查视图只需1秒几
接近 3 年之前 回复
baidu_34049233
情人节也放假 视图给你提速了吗?跟直接查不一样吗
接近 3 年之前 回复

MySQL如何优化LEFT JOIN和RIGHT JOIN http://www.data.5helpyou.com/article211.html

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!