水表 2016-12-16 04:02 采纳率: 0%
浏览 958
已采纳

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条回答 默认 最新

  • 水表 2016-12-16 07:03
    关注

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 数学的三元一次方程求解
  • ¥20 iqoo11 如何下载安装工程模式
  • ¥15 本题的答案是不是有问题
  • ¥15 关于#r语言#的问题:(svydesign)为什么在一个大的数据集中抽取了一个小数据集
  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 蓝桥杯单片机第十三届第一场,整点继电器吸合,5s后断开出现了问题