问题描述
"SELECT sum(VALUE) value ,"+" CONVERT(varchar(2),DATE_TIME,108) DAY FROM"+" ed_electricity_energy_hour WHERE item_name IN ("+" SELECT"+" ems_unit.code + '_' + ems_collector.code + '_' + ems_item.code"+" FROM"+" ems_item" +" JOIN ems_collector ON ems_collector.id = ems_item.collector"+" JOIN ems_unit ON ems_unit.id = ems_collector.unit"+" WHERE"+" ems_item.item_type = 14"+" AND ems_item.sort_area > 0"+" AND ems_unit.code = '110101A003' )"+" AND date_time LIKE '2013-02-27%' GROUP BY"+" CONVERT(varchar(2),DATE_TIME,108)"+" ORDER BY CONVERT(varchar(2),DATE_TIME,108)";说明:在日期表里查value总和,时间(取小时),而item_name是由ems_unit.code_ems_collector.code_ems_item.code组成的字符串,查询条件为时间2013-02-27%,和ems_unit.code = '110101A003',就是item_name里包含110101A003这个ems_unit.code,表ed_electricity_energy_hour的数据量大约1000条,查询时间大概20秒,大家看看 怎么优化!
解决方案
SELECT SUM(VALUE) VALUE, CONVERT(VARCHAR(2), DATE_TIME, 108) DAY FROM ED_ELECTRICITY_ENERGY_HOUR WHERE ITEM_NAME IN (SELECT EMS_UNIT.CODE || '_' || EMS_COLLECTOR.CODE || '_' || EMS_ITEM.CODE FROM EMS_ITEM JOIN EMS_COLLECTOR ON EMS_COLLECTOR.ID = EMS_ITEM.COLLECTOR JOIN EMS_UNIT ON EMS_UNIT.ID = EMS_COLLECTOR.UNIT WHERE EMS_ITEM.ITEM_TYPE = 14 AND EMS_ITEM.SORT_AREA > 0 AND EMS_UNIT.CODE = '110101A003') AND DATE_TIME LIKE '2013-02-27%' GROUP BY CONVERT(VARCHAR(2), DATE_TIME, 108) ORDER BY CONVERT(VARCHAR(2), DATE_TIME, 108); 需求分析： 1. 对ED_ELECTRICITY_ENERGY_HOUR表中Value列求和 2. 获取对ED_ELECTRICITY_ENERGY_HOUR表中Value列求和DATE_TIME的日期中的日，并根据该列分组和升序排序 3. 数据范围： a. EMS_UNIT.CODE,EMS_COLLECTOR.CODE,EMS_ITEM.CODE三列与ED_ELECTRICITY_ENERGY_HOUR.ITEM_NAME相等 b. 指定EMS_UNIT.CODE（110101A003） c. 指定EMS_ITEM.ITEM_TYPE（14） d. EMS_ITEM.SORT_AREA必须大于0 e. ED_ELECTRICITY_ENERGY_HOUR.DATE_TIME为指定日期（2013-02-27）解决方案：1. ED_ELECTRICITY_ENERGY_HOUR.ITEM_NAME采用IN还是EXISTS a. 当EMS_ITEM、EMS_COLLECTOR、EMS_UNIT三表关联结果集数据量很小（百行以内），优化提升性能空间不大 b. 当以上三表关联结果很大，上千过万，可以考虑直接与ED_ELECTRICITY_ENERGY_HOUR表四表关联，表顺序根据数据量由大到小 c. 由于ED_ELECTRICITY_ENERGY_HOUR.ITEM_NAME一列需要与不同表的多个列匹配，采用EXISTS的结果：四表关联>EXISTS>IN，个人建议还是表关联最好 d. 避免使用SubStr截取函数，这影响索引的使用2. 对ED_ELECTRICITY_ENERGY_HOUR.DATE_TIME的限制建议改为BETWEEN.AND，LIKE不会走索引，不解释3. 可以考虑将DAY一列做以下修改： CONVERT(VARCHAR(2), DATE_TIME, 108) => SUBSTR('2013-02-27',9,10) 这样做可以避免在DATE_TIME一列使用函数，提高性能，若DATE_TIME一列建有索引可以正常命中以上观点如果有误，请指点！
解决方案二：
尽量不要用IN 这种子查询，把子查询抽出来用 join的方式你做下执行计划(explain一下) 就知道是那里需要优化了
解决方案三：
把IN改为EXISTS，同时把LIKE改为inStr。然后看看。
解决方案四：
你看一下执行计划，在哪里耗时比较多，然后进行对应的调整！