【 TiDB 使用环境】生产环境
【 TiDB 版本】6.5.1
同样一条SQL，tidb的查询速度是mysql的20倍
1 个赞
什么 SQL 呢
sql发出来看看
| SELECT c.PERSONID,count(CALENDARDATE) AS ‘workdays’ | |
|---|---|
| FROM ATDADJUSTTIMEORDER c | |
| INNER JOIN (select * from AtdTimeOrderCalendar where CALENDARDATE BETWEEN ‘2023-07-01’ AND ‘2023-09-30’) d ON d.TIMEORDERID=c.TIMEORDERID | |
| INNER JOIN psnaccount p3 ON p3.PERSONID=c.PERSONID | |
| WHERE d.CALENDARDATE>=p3.attendondate | |
| AND BEGINDATE <= ‘2023-07-01’ AND c.ENDDATE>= ‘2023-09-30’ | |
| AND d.CALENDARDATE<=p3.DIMISSIONDATE | |
| AND d.CALENDARTYPE=1 | |
| GROUP BY personid |
大哥你是震惊部的吗
1 个赞
啥意思
夸你会起标题，可以吸引人点进来 
方便贴下表结构跟索引情况吗
哈。。。。。
你这结果查出来的东西，走的ID咋还不一样？
哪个ID啊
CREATE TABLE psnaccount (
PERSONID varchar(40) NOT NULL DEFAULT ‘’ COMMENT ‘人员Id’
PRIMARY KEY (PERSONID) /*T![clustered_index] NONCLUSTERED */,
UNIQUE KEY EMPLOYEEID (EMPLOYEEID),
KEY ix_psnaccount_TPB (TRUENAME,PERSONID,BRANCHID),
KEY ix_psnaccount_PERSONID (PERSONID),
KEY ix_psnaccount_TRUENAME (TRUENAME),
KEY ix_psnaccount_BE (BRANCHID,EMPLOYEEID),
KEY ix_psnaccount_EA (EMPLOYEEID,ATTENDONDATE),
KEY idx_ATTENDONDATE (ATTENDONDATE),
KEY idx_DIMISSIONDATE (DIMISSIONDATE),
KEY idx_EmployeeTypeId (EMPLOYEETYPEID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CREATE TABLE atdadjusttimeorder (
begindate date NOT NULL COMMENT ‘开始日期’,
enddate date NOT NULL COMMENT ‘结束日期’,
PERSONID varchar(40) NOT NULL DEFAULT ‘’ COMMENT ‘人员ID’,
TIMEORDERID varchar(40) DEFAULT ‘’ COMMENT ‘班次ID’
PRIMARY KEY (Id) /*T![clustered_index] NONCLUSTERED */,
KEY ATDADJUSTTIMEORDER_Main (PERSONID,begindate,enddate),
KEY ix_TIMEORDERID (TIMEORDERID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CREATE TABLE atdtimeordercalendar (
calendardate date NOT NULL COMMENT ‘行事日期’,
TIMEORDERID varchar(40) NOT NULL DEFAULT ‘’ COMMENT ‘班次ID’,
CALENDARTYPE int(11) DEFAULT ‘0’ COMMENT ‘行事类型’
PRIMARY KEY (Id) /*T![clustered_index] NONCLUSTERED */,
KEY ix_calendardate (calendardate),
KEY ix_TIMEORDERID (TIMEORDERID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
无用字段被我删除了
应该是没有排序
结果都不一样啊，两边数据一样吗？
数据一样的，用dm同步的