大家看看这个SQL该如何优化

0 0

大家看看这个SQL该如何优化0

"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秒,大家看看怎么优化!

SQL

2014年3月20日 16:47

mingnianshimanian
1
0 0 6

4个答案按时间排序按投票排序

0 0

采纳的答案

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一列建有索引可以正常命中

以上观点如果有误，请指点！

2014年3月21日 16:10

tan4836128
20
0 0 2

1条评论

0 0

尽量不要用IN 这种子查询，把子查询抽出来用 join的方式
你做下执行计划(explain一下) 就知道是那里需要优化了

2014年3月21日 11:12

jjjssh
320
0 0 6

添加评论

0 0

把IN改为EXISTS，同时把LIKE改为inStr。然后看看。

2014年3月20日 22:21

freezingsky
159
1 1 16

添加评论

0 0

你看一下执行计划，在哪里耗时比较多，然后进行对应的调整！

2014年3月20日 17:36

lingdongfeixue
0
0 0 11

添加评论

已解决问题

未解决问题

排行榜

查看全部排名>>