慢在b表吧
d表的过滤条件并不是truename等于吧?我看执行计划里面还有like之类的,你贴的语句里面没有。d表过滤因子不准啊。
d.username = 或 d.username like 一样慢
那你explan analyze select * from d where truename=那个值,贴下执行结果。
老兄,你a表的persionid我猜测是没有索引的,也就是没有字段在第一位。要么就是和d表的persionid数据类型不一致。
CREATE TABLE atdemployeecalendar
(
CALENDARDATE
varchar(40) NOT NULL DEFAULT ‘’ ,
PERSONID
varchar(40) NOT NULL DEFAULT ‘’ ,
PRIMARY KEY (CALENDARDATE
,PERSONID
) /*T![clustered_index] NONCLUSTERED */,
KEY syATDEMPLOYEECALENDAR_PERSONID
(PERSONID
),
KEY syATDEMPLOYEECALENDAR_CALENDARDATE
(CALENDARDATE
),
KEY atdemployeecalendarIndex2
(PERSONID
,CALENDARDATE
),
KEY idx_timeclassid
(TIMECLASSID
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;
CREATE TABLE atdpersontimesheet
(
PSNPAYID
varchar(40) NOT NULL DEFAULT ‘’ ,
PERSONID
varchar(40) DEFAULT ‘’ ,
TIMESHEETDATEFROM
varchar(10) DEFAULT ‘’ ,
TIMESHEETDATETO
varchar(10) DEFAULT ‘’ ,
PRIMARY KEY (PSNPAYID
) /*T![clustered_index] NONCLUSTERED */,
KEY syATDPERSONTIMESHEET_PERSONID
(PERSONID
),
KEY syATDPERSONTIMESHEET_DATEFROM
(TIMESHEETDATEFROM
),
KEY atdpersontimesheetIndex2
(TIMESHEETDATEFROM
,TIMESHEETDATETO
,PERSONID
),
KEY ix_atdpersontimesheet_personid_scheduledate
(PERSONID
,SCHEDULEDATE
),
KEY ix_atdpersontimesheet_scheduledate
(SCHEDULEDATE
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;
其他没用的字段删除了,
表是从mysql同步过来了,索引,字段类型都是一致的
d表的表结构贴下看看
CREATE TABLE psnaccount
(
CARDNUM
varchar(40) DEFAULT ‘’ ,
TRUENAME
varchar(240) DEFAULT ‘’ ,
PERSONID
varchar(40) NOT NULL DEFAULT ‘’ ,
BRANCHID
varchar(40) DEFAULT ‘’ COMMENT ,
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_string1
(string1
),
KEY idx_EmployeeTypeId
(EMPLOYEETYPEID
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
explain analyze select /*+ INL_JOIN(b,a) */ * from atdemployeecalendar a left join psnaccount b on a.PERSONID=b.PERSONID where b.truename=‘虞佳丽’ 可以执行下看下结果么?
你把这个hint加在你的整个语句里面select 后面那里试试呢,看快不快?我是怀疑tidb的成本评估有问题。
select /*+ INL_JOIN(b,a) */ * from atdemployeecalendar a left join psnaccount b on a.PERSONID=b.PERSONID where b.truename=‘虞佳丽’
这个SQL,加不加hint都快
你加上hint,然后看下你业务语句整体的执行计划,不需要analyze,直接explain?