SQL优化记录

SQL优化记录

Scroll Down

SQL优化记录

原来SQL:

SELECT
	t.PKID,
	t.MON_NAME,
	t.CORP_CODE,
	TO_CHAR(t.CREAT_DATE, 'YYYY-MM-DD HH24:MI:SS') AS CREAT_DATE,
	t.QUA_NO,
	t.REG_ADDRESS,
	t.REGION_NAME,
	(	SELECT
			COUNT(0) 
		FROM
			T_SMS_MONPROJECT 
		WHERE
			t.PKID = MON_ID)                                             AS 
	MON_PRO_NUM,
	(	SELECT
			COUNT(DISTINCT ENTER_NAME) AS NUM 
		FROM
			T_SMS_MONPROJECT 
		WHERE
			t.PKID = MON_ID)                                             AS 
	SERVICE_NUM,
	t.LONGITUDE,
	t.LATITUDE,
	t.STATUS_CODE,
	t.STATUS_NAME,
	t.REL_STAUTS,
	TO_CHAR(t.BLACK_DATE, 'YYYY-MM-DD')            AS BLACK_DATE,
	TO_CHAR(t.UPDATE_DATE, 'YYYY-MM-DD')           AS UPDATE_DATE,
	r.ALLREGINNAME 
FROM
	T_SMS_MONMECINFO t 
		LEFT JOIN T_COD_REGION r 
		ON t.REGION_CODE = r.REGIONCODE 
WHERE
	STAGE = '1' 
ORDER BY
	t.CREAT_DATE DESC 

查询时间(以ADS反馈时间为准)
image.png
表数据量
image.png
image.png
打开sql执行计划,查询
image.png
image.png
大致看出全表扫描问题应该是出在SQL的那几个子查询列中,没有进行索引的匹配
查看索引:
image.png
image.png
只有主键的索引
建关联ID索引
image.png
现在查询时间为:
image.png