水表 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 基于ucc28019的pfc电路中芯片一直不工作
  • ¥15 yolov8在3588板子端c++推理报错
  • ¥50 unitywebrequest分段下载导致报错,如何解决?
  • ¥15 错误使用 gretna_GUI_PreprocessInterface>RunBtn_Callback
  • ¥15 WPF如何用Chart绘画出Y轴的左边数据
  • ¥15 pycharm无法查看内置代码
  • ¥15 跑hls xfopencv的例程standalone_hls_axi_example出的错误,csim没问题,c synthesis出的错误
  • ¥15 sqlserver update语句逐行生效
  • ¥20 Windows10系统命令行调用
  • ¥15 php环境如何实现国密SM2相关功能