首页新闻找找看学习计划

MySQL连表查询datetime字段索引失效

0
悬赏园豆:50 [待解决问题]

求助!
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]

EXPLAN查看执行计划如下:
[img=https://img-bbs.csdn.net/upload/201906/14/1560515621_14290.png][/img]

从执行计划可以看到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来万条。

时光再见的主页 时光再见 | 初学一级 | 园豆:152
提问于:2019-06-14 20:44

建议格式化一下代码,这个文本编辑器是支持markdown的

会长 7个月前
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册