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

chen357313771 2019-06-19 03:51:42

语句如下：

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千万，如何能满足走索引查询

...全文

3833 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

闭包客 2019-06-24

打赏
举报

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

wx18487975559 2019-06-22

打赏
举报

minsic78 2019-06-20

打赏
举报

引用 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的条件，无论是引用到的哪个字段都可以，当然是在不影响业务含义的基础上。

minsic78 2019-06-20

打赏
举报

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

chen357313771 2019-06-20

打赏
举报

引用 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

chen357313771 2019-06-20

打赏
举报

引用 10 楼 minsic78 的回复:

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

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

AHUA1001 2019-06-20

打赏
举报

先不说技术，您的逻辑就有很大的优化空间，您试试这个。 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))

weixin_43996959 2019-06-20

打赏
举报

下一手看看....

weixin_42776213 2019-06-20

打赏
举报

下载来看看，好东西

minsic78 2019-06-19

打赏
举报

引用 8 楼 chen357313771 的回复:

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

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

GZY1123 2019-06-19

打赏
举报

你可以做函数索引啊

chen357313771 2019-06-19

打赏
举报

引用 7 楼 minsic78 的回复:

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

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

minsic78 2019-06-19

打赏
举报

三连下

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

minsic78 2019-06-19

打赏
举报

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

minsic78 2019-06-19

打赏
举报

引用 4 楼 chen357313771 的回复:

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

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

chen357313771 2019-06-19