最近在生产环境中发现一条查询脚本耗时很长,排查了一天没发现问题在哪,请大神帮帮忙!
.
表结构说明:
- SPYCDMX.MXBH、SPYCD.DJBH、YSCYD.DJBH、CANGKU.CKDM 是主键;
- SPYCD.YS、SPYCD.YSRQ、CANGKU.XZDM、SPYCDMX.DJBH、SPYCDMX.dc_sync_flag、SPYCDMX.dc_sync_guid 是非聚集索引;
- SPYCDMX.dc_sync_guid 字段默认值是 NULL;
- SPYCDMX 表有400多万行数据;
.
异常查询
SELECT m.DJBH, mx.MXBH, mx.dc_sync_flag, mx.dc_sync_guid, mx.dc_sync_time FROM SPYCDMX mx INNER JOIN SPYCD m ON m.DJBH = mx.DJBH LEFT JOIN CANGKU ck ON ck.CKDM = m.DM1 LEFT JOIN YSCYD cy ON cy.DJBH = m.YDJH WHERE 1 = 1 AND m.YS = '1' AND m.YSRQ >= '2019-08-16' AND ck.XZDM = '1' AND cy.DJBH IS NOT NULL AND mx.dc_sync_flag = 'N' AND mx.dc_sync_guid IS NULL
这段代码的执行时间长达3~5分钟!
.
尝试1
SELECT m.DJBH, mx.MXBH, mx.dc_sync_flag, mx.dc_sync_guid FROM SPYCDMX mx INNER JOIN SPYCD m ON m.DJBH = mx.DJBH LEFT JOIN CANGKU ck ON ck.CKDM = m.DM1 LEFT JOIN YSCYD cy ON cy.DJBH = m.YDJH WHERE 1 = 1 AND m.YS = '1' AND m.YSRQ >= '2019-08-16' AND ck.XZDM = '1' AND cy.DJBH IS NOT NULL AND mx.dc_sync_flag = 'N' AND mx.dc_sync_guid IS NULL
在 SELECT 字段中剔除 mx.dc_sync_time 列,执行时间缩减到十几秒!
.
尝试2
SELECT m.DJBH, mx.MXBH, mx.dc_sync_flag, mx.dc_sync_guid FROM SPYCDMX mx INNER JOIN SPYCD m ON m.DJBH = mx.DJBH LEFT JOIN CANGKU ck ON ck.CKDM = m.DM1 LEFT JOIN YSCYD cy ON cy.DJBH = m.YDJH WHERE 1 = 1 AND m.YS = '1' AND m.YSRQ >= '2019-08-16' AND ck.XZDM = '1' AND cy.DJBH IS NOT NULL AND mx.dc_sync_flag = 'N' AND ISNULL(mx.dc_sync_guid, '') = ''
把 WHERE 条件中的 AND mx.dc_sync_guid IS NULL 改为 AND ISNULL(mx.dc_sync_guid, '') = '',执行时间缩减到1~2秒!
.
这是什么原因?