mysql datetime列索引_MySQL连表查询datetime字段索引失效

求助！

A表：

[code=sql]

create table if not exists V_MEDICAL_INCOME

(

areacode varchar(16) null,

areaname varchar(32) null,

APPLYDEPARTMENTCODE varchar(16) null,

APPLYDEPARTMENTNAME varchar(32) null,

EXECUTIONDEPARTMENTCODE varchar(16) null,

EXECUTIONDEPARTMENTNAME varchar(32) null,

EXECUTIONDEPARTMENTTYPE varchar(4) null,

OUTPATIENT varchar(4) null,

CHARGEID varchar(16) null,

TYPE varchar(32) null,

DRUGCODE varchar(32) null,

INCOME decimal(16,2) null,

PAYMENTROUTE varchar(16) null,

CHARGEAT datetime null,

RJSJ datetime null,

RJSH int null,

SFYID varchar(16) null,

PATIENT_NO varchar(16) null

)

comment '视图-收入明细';

create index V_MEDICAL_INCOME_INDEX

on V_MEDICAL_INCOME (CHARGEAT);

create index V_MEDICAL_INCOME_INDEX_RJSH

on V_MEDICAL_INCOME (RJSH);

[/code]

B表：

[code=sql]

create table if not exists V_INCOME_RJSH

(

opercode varchar(16) null,

checkflag varchar(4) null,

checkdate datetime null,

begindate datetime null,

enddate datetime null

)

comment '视图-日结审核';

create index V_INCOME_RJSH_INDEX

on V_INCOME_RJSH (checkdate);

create index idx_opercode

on V_INCOME_RJSH (opercode);

[/code]

执行计划：

[code=sql]

EXPLAIN

SELECT max(m.areaCode) AS area_code,

m.areaName AS area_name,

max(m.departmentCode) AS department_code,

m.departmentName AS department_name,

sum(m.income) AS income,

CASE m.type

WHEN '卫材' THEN 1

WHEN '化验费' THEN 1

WHEN '检查费' THEN 1

WHEN '西药' THEN 1

WHEN '中成药' THEN 1

WHEN '中草药费' THEN 1

WHEN '外送项目' THEN 1

ELSE 2 END AS service

FROM (

SELECT t.INCOME AS income,

t.APPLYDEPARTMENTCODE AS departmentCode,

t.APPLYDEPARTMENTNAME AS departmentName,

cast(t.AREACODE AS UNSIGNED INT) AS areaCode,

t.AREANAME AS areaName,

t.TYPE AS type

FROM V_MEDICAL_INCOME t,

(SELECT DISTINCT tt.OPERCODE, tt.BEGINDATE, tt.ENDDATE, tt.CHECKDATE

FROM V_INCOME_RJSH tt

WHERE tt.CHECKFLAG = 2

AND tt.CHECKDATE BETWEEN '2019-05-01 00:00:00' AND '2019-05-01 23:59:59') ff

WHERE t.OUTPATIENT = 1

AND t.TYPE != '挂号费'

AND t.CHARGEAT >= ff.BEGINDATE

AND t.CHARGEAT >= ff.ENDDATE

AND t.sfyid = ff.opercode

) m

GROUP BY areaName, departmentName, type

[/code]

从执行计划可以看到t.CHARGEAT字段是没有走索引的，查一天的数据要花几分钟，如果把查询条件改成：

[code=sql]

AND t.CHARGEAT BETWEEN '2019-05-01 00:00:00' AND '2019-05-01 23:59:59'

[/code]

就会走V_MEDICAL_INCOME_INDEX索引，效率提高百倍，求教大神能帮忙看一下为什么V_MEDICAL_INCOME表不走索引吗？

排除条件查询的数据大于全表数据的30%，t.CHARGEAT和ff.BEGINDATE都是datetime类型。

V_MEDICAL_INCOME表有一亿条数据，V_INCOME_RJSH有300来万条。