1。索引
A:常用查询字段一定要创建索引
B :like 查询的字段 创建索引提高查询速度
C: in 查询的字段,创建索引提高查询速度
2。select 的列数
A: 取记录数 count(*) 尽量写成count(1), 或者第一个列的列名
B :和查询结果无关的列,尽量不要出现在select 中,是有数据量的,尤其是大数据量的时候
C :排序字段,最好用在主键,或者创建了索引的字段上,排序也是需要时间的。
3. 多表关联,关联条件的写法
A:尽量在关联的字段上面创建索引,另外如果是多个字段关联,索引字段最好放到第一个条件上面
B :主驱动表,一定把数据量最少的表,或者能够过滤了结果集合最少的表做为主驱动表。
C :一般关联,不要超过4张表,表越多,随着记录数的增多,关联查询会越来越慢
4. 多表关联查询,条件字段的写法.
举例:
A:原sql
SELECT
A.PAYMENT_CODE attr1,
E.LOOKUP_TYPE_NAME attr2,
A.PAYMENT_ID attr3,
A.PAYMENT_LEVEL_TYPE attr4,
A.PAYMENT_LEVEL_CODE attr5,
B.PAYMENT_NAME attr6,
B.PAYMENT_MEMO attr7,
C.PAYMENT_PRICE attr8,
C.CONTRACT_ID attr9,
(CASE WHEN A.SERVICE_LOOKUP_CODE IS NULL OR A.SERVICE_LOOKUP_CODE = '' THEN 'T' ELSE 'F' END) attr10,
'null' attr11,
'null' attr12,
'null' attr13,
A.EXPEND_MODULE_FLAG attr14
FROM
ECC_CUSTOMER.ECC_CUSTOMER_FIXCONTRACT_HEADINFO D
INNER JOIN ECC_CUSTOMER.ECC_CUSTOMER_FIXCONTRACT_DETAIL C ON C.CONTRACT_ID=D.CONTRACT_ID
INNER JOIN ECC_FND.ECC_FND_PAYMENT_INFO A on c.payment_id = a.payment_id
INNER JOIN ECC_FND.ECC_FND_PAYMENT_INFO_PROP B ON (A.PAYMENT_ID=B.PAYMENT_ID)
inner JOIN ECC_FND.ECC_FND_LOOKUP_CODE_PROP E ON (A.PAYMENT_LEVEL_TYPE=E.LOOKUP_TYPE AND A.PAYMENT_LEVEL_CODE=E.LOOKUP_CODE)
WHERE
A.ENABLE_FLAG = 'T'
AND B.ENABLE_FLAG = 'T'
AND C.ENABLE_FLAG = 'T'
AND A.DELETE_FLAG = 'F'
AND B.DELETE_FLAG = 'F'
AND C.DELETE_FLAG = 'F'
AND B.LANGUAGE_LOOKUP_TYPE = 'PCM_LANGUAGE_TYPE'
AND B.LANGUAGE_LOOKUP_CODE = 'CHINESE'
AND E.LANGUAGE_LOOKUP_TYPE = 'PCM_LANGUAGE_TYPE'
AND E.LANGUAGE_LOOKUP_CODE = 'CHINESE'
AND E.APP_NAME = 'CSM_WEB'
AND D.CUSTOMER_ID= 6124
B:优化后
SELECT
A.PAYMENT_CODE attr1,
E.LOOKUP_TYPE_NAME attr2,
A.PAYMENT_ID attr3,
A.PAYMENT_LEVEL_TYPE attr4,
A.PAYMENT_LEVEL_CODE attr5,
B.PAYMENT_NAME attr6,
B.PAYMENT_MEMO attr7,
C.PAYMENT_PRICE attr8,
C.CONTRACT_ID attr9,
(CASE WHEN A.SERVICE_LOOKUP_CODE IS NULL OR A.SERVICE_LOOKUP_CODE = '' THEN 'T' ELSE 'F' END) attr10,
'null' attr11,
'null' attr12,
'null' attr13,
A.EXPEND_MODULE_FLAG attr14
FROM
ECC_CUSTOMER.ECC_CUSTOMER_FIXCONTRACT_DETAIL C
INNER JOIN ECC_FND.ECC_FND_PAYMENT_INFO A on (c.payment_id = a.payment_id)
INNER JOIN ECC_FND.ECC_FND_PAYMENT_INFO_PROP B ON (A.PAYMENT_ID=B.PAYMENT_ID)
inner JOIN ECC_FND.ECC_FND_LOOKUP_CODE_PROP E ON (A.PAYMENT_LEVEL_TYPE=E.LOOKUP_TYPE AND A.PAYMENT_LEVEL_CODE=E.LOOKUP_CODE)
WHERE
A.ENABLE_FLAG = 'T'
AND B.ENABLE_FLAG = 'T'
AND C.ENABLE_FLAG = 'T'
AND A.DELETE_FLAG = 'F'
AND B.DELETE_FLAG = 'F'
AND C.DELETE_FLAG = 'F'
AND B.LANGUAGE_LOOKUP_TYPE = 'PCM_LANGUAGE_TYPE'
AND B.LANGUAGE_LOOKUP_CODE = 'CHINESE'
AND E.LANGUAGE_LOOKUP_TYPE = 'PCM_LANGUAGE_TYPE'
AND E.LANGUAGE_LOOKUP_CODE = 'CHINESE'
AND E.APP_NAME = 'CSM_WEB'
AND C.CONTRACT_ID = (select contract_id from ECC_CUSTOMER.ECC_CUSTOMER_FIXCONTRACT_HEADINFO D where D.ENABLE_FLAG='T' AND D.DELETE_FLAG='F' AND DATE(D.END_DATE) >= CURRENT DATE and d.customer_id = 6124)
说明:
D:合同头表
C:合同明细表
A :合同条目基础信息表(200条记录)
B :合同条目基础信息属性表(400条)
E :数据字典表(600条)
响应的关联字段都创建了索引。
性能说明:
1。D表 (200条)
C表 (30000条)
此时性能没有发现什么低效。
2。D表 (800条)
C表 (120000条)
此时性能比较低下,在原有的sql语句查询竟然要11秒左右,在给相应的表字段上面都创建了索引后,性能没有改进,于是苦苦思索,就只能在sql的写法上面下功夫,最后改为优化后的语句,竟然需要0。30秒左右。
一定要把记录集限制在最少,如果能在最多表的记录上面加条件的话,尽量在最多记录表上面加过滤条件。
5。索引的重整理
以及数据的重整理
(当索引失效的时候或者标记录发生变化超过60%的时候)