sql优化：年月分别两个字段，做区间查询不走索引问题

[chen357313771]

语句如下：

SELECT substr(orgCode,1,instr(orgCode,  '.',1,3)) orgCode,SUM(s.MONTH_ADD)
		from SPECIL_TASK_REPORT s
		WHERE ((s.year = 2018 and s.month >= 7 or (s.year > 2018)) 
			and  ((s.year = 2018 and s.month <= 12) or (s.year < 2018)))
		GROUP BY substr(orgCode,1,instr(orgCode,  '.',1,3))

执行计划：
索引：


数据量大概1千万，如何能满足走索引查询

[ZJHZ_叶]

确定括号没有问题？你这语句就不是你想要的……

[minsic78]

这条件拼出来的吧，绕来绕去就是2018年7月到12月的数据，半年的数据应该是不会少的，CBO现在估算的300多万，数据太多了，全表扫描极有可能是正确的路径。

[minsic78]

看上去这张表还是个一级list分区，用的year字段采用的年度分区？扫描的应该是2018年的这个分区数据，按照你取了半年的数据来看，这样的计划可以了，走索引反而不行。

[chen357313771]

引用 3 楼 minsic78 的回复:

看上去这张表还是个一级list分区，用的year字段采用的年度分区？扫描的应该是2018年的这个分区数据，按照你取了半年的数据来看，这样的计划可以了，走索引反而不行。

你好，确实是分区表，半年的数据300多万，是不是数据量大了，优化器选择了表扫描，而且刚才查了下，年月索引重复性特别高 现在查询差不多平均10秒，在不做结构变化的情况下，有没有优化的余地了呢？

[minsic78]

引用 4 楼 chen357313771 的回复:

[quote=引用 3 楼 minsic78 的回复:] 看上去这张表还是个一级list分区，用的year字段采用的年度分区？扫描的应该是2018年的这个分区数据，按照你取了半年的数据来看，这样的计划可以了，走索引反而不行。

你好，确实是分区表，半年的数据300多万，是不是数据量大了，优化器选择了表扫描，而且刚才查了下，年月索引重复性特别高 现在查询差不多平均10秒，在不做结构变化的情况下，有没有优化的余地了呢？[/quote] 返回数据量太大，或者占整表数据的比例太高，用索引是不合适的，分区就是用来对付这种不可避免的全表扫描操作的，可以扫描一个或者几个分区，来避免扫描整张表所有的数据。 300多万的数据，应该是没法继续常规的优化了，如果要优化，而且这张表的字段很多的话，可以考虑在这个sql里涉及到的所有字段上（包括select列表、where条件以及group子句中引用到的字段）创建组合索引，相当于将该表垂直分割，有点类似使用列存数据库了，这样减少整体扫描的数据，节省IO。

[minsic78]

忘记说了，在大表上多几个索引字段的代价会比较高的，会影响DML语句的性能，尤其是批量处理数据的时候，你会发现效率会与没有索引的时候差的不是一星半点。

[minsic78]

三连下 如果以SQL的执行时间为标准来判断SQL性能的好坏，最好多执行几次，取稳定时间为准，因为有buffer cache缓存的影响。

[chen357313771]

引用 7 楼 minsic78 的回复:

三连下 如果以SQL的执行时间为标准来判断SQL性能的好坏，最好多执行几次，取稳定时间为准，因为有buffer cache缓存的影响。

好的，明白了，谢谢，我考虑拆表弄了

[GZY1123]

你可以做函数索引啊

[minsic78]

引用 8 楼 chen357313771 的回复:

[quote=引用 7 楼 minsic78 的回复:] 三连下 如果以SQL的执行时间为标准来判断SQL性能的好坏，最好多执行几次，取稳定时间为准，因为有buffer cache缓存的影响。

好的，明白了，谢谢，我考虑拆表弄了[/quote] 可以先试下建组合索引，拆表的代价毕竟更高

[weixin_42776213]

下载来看看，好东西

[weixin_43996959]

下一手看看....

[AHUA1001]

先不说技术，您的逻辑就有很大的优化空间，您试试这个。 SELECT substr(orgCode,1,instr(orgCode, '.',1,3)) orgCode,SUM(s.MONTH_ADD) from SPECIL_TASK_REPORT s WHERE s.year = 2018 and s.month between 7 and 12 GROUP BY substr(orgCode,1,instr(orgCode, '.',1,3))

[chen357313771]

引用 10 楼 minsic78 的回复:

[quote=引用 8 楼 chen357313771 的回复:] [quote=引用 7 楼 minsic78 的回复:] 三连下 如果以SQL的执行时间为标准来判断SQL性能的好坏，最好多执行几次，取稳定时间为准，因为有buffer cache缓存的影响。

好的，明白了，谢谢，我考虑拆表弄了[/quote] 可以先试下建组合索引，拆表的代价毕竟更高[/quote] 试过了组合索引，优化器还是不用

[chen357313771]

引用 11 楼 AHUA1001 的回复:

先不说技术，您的逻辑就有很大的优化空间，您试试这个。 SELECT substr(orgCode,1,instr(orgCode, '.',1,3)) orgCode,SUM(s.MONTH_ADD) from SPECIL_TASK_REPORT s WHERE s.year = 2018 and s.month between 7 and 12 GROUP BY substr(orgCode,1,instr(orgCode, '.',1,3))

早试过了，不走索引的都是 full table access

[minsic78]

再说一遍吧，不走现在的索引范围扫描是对的。。。 如果说后来的覆盖组合索引没走，你可以放出索引的DDL脚本看下，如果索引建好了，应该是走index fast full scan，而不是你原先期待的index range scan～

[minsic78]

引用 14 楼 minsic78 的回复:

再说一遍吧，不走现在的索引范围扫描是对的。。。 如果说后来的覆盖组合索引没走，你可以放出索引的DDL脚本看下，如果索引建好了，应该是走index fast full scan，而不是你原先期待的index range scan～

对了，想到一种可能，这个SQL里引用到的字段上，是不是没有not null约束或者主键约束，如果是的话，那么可以尝试在原来的索引字段组合中加个常量0，比如这样的索引：(a,b,c,0)，其中a、b、c为SQL引用的字段组合，或者，你可以在SQL中加上一个not nul的条件，无论是引用到的哪个字段都可以，当然是在不影响业务含义的基础上。

[wx18487975559]

[闭包客]

or 查询对于使用索引是有影响的，你也可以使用指定使用索引的语法。