由于公司业务较复杂，以下是工作一年来遇到的sql优化经验，下面只是我能记住的部分
1.首先应尽量避免全表扫描，常用查询条件字段或者排序字段应为其添加索引
2.建表时应尽量给数值字段设定默认值，如 0
select id from t where num is null 这样查
select id from t where num=0这样查
第一种情况就会索引失效
那么关于索引失效的情况,总结下：
查询的时候要考虑到where子句和order by子句的索引，如果在where子句中出现了<,>,<=,>=,!=,<>,is null,is nit null,算术运算，like '%'第一个为%的，会触发全表扫描
3.如果能确定两个结果集中不会有重复数据的情况下 使用union all 而不使用 union
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20
5.关于聚集索引
create index IDerX_01_C_CODE on C_CODE(OLD_TABLE);4.关于多条件查询
create index IDerX_02_C_CODE on C_CODE(OLD_ID);
SELECT * FROM c_code WHERE old_table like 'TI%' AND old_id like '33%' ; --45.436
DROP INDEX IDerX_01_C_CODE;
DROP INDEX IDerX_02_C_CODE;
create index IDX_01_C_CODE on C_CODE (OLD_TABLE, OLD_ID)
SELECT * FROM c_code WHERE old_table like 'TI%' AND old_id like '33%' ; --15.327
上面举的是一个我曾经的例子，c_code表有几千万条数据
刚开始是分别给每个字段建立索引
后面改为建立聚集索引
然后速度由45秒多提升到15秒
使用聚集索引要注意的点：where后面的第一个查询条件必须是聚集索引的第一个字段
否则聚集索引无效
6.当查询速度很慢时，看看sql里有没有 in 与not in ，将其替换为 exists 与not exists
7.强制使用索引
当添加某字段条件进行范围性查询时会发现查询突然变得很慢，这个时候查看执行计划若该字段索引没有用到，则使用
force index('index_name'),如下就是摘录的一个例子
8.下面贴下入职以来写的最长的sql，如果后续有更长的再替换
总结就是：当查询场景较复杂时应充分了解需求，然后细化到各个模快再union all总和到一起
可加大查询效率
select zz.*,
'LINAN' LINAN,
(case
when zz.zaituu = '有' then
'有'
when (select count(0)
from c_code cv, c_codeentity cc
where cv.code_val between zz.STARTCODE and zz.ENDCODE
and cc.code_id = cv.id
and cc.status = 1) > 0 then
'有'
else
'无'
end) zaitu
from (select min(uu.code_val) STARTCODE,
max(uu.code_val) ENDCODE,
(max(uu.code_val) - min(uu.code_val) + 1) NUMBER_CAP,
uu.usingname USINGNAME,
uu.status STATUS,
uu.QLOCATIONNAME QLOCATIONNAME,
uu.enclose ENCLOSE,
uu.long_function LONG_FUNCTION,
uu.control_way CONTROL_WAY,
uu.charge CHARGE,
uu.using,
uu.username USERNAME,
uu.zaituu ZAITUU,
uu.gid GID,
uu.ysh YSH,
uu.node_name NODE_NAME
from (select ww.code_val,
(ww.code_val - row_number()
over(partition by ww.usingname,
ww.using,
ww.status,
ww.name,
ww.enclose,
ww.long_function,
ww.control_way,
ww.charge,
ww.username,
ww.zaituu,
ww.gid,
ww.ysh,
ww.node_name order by code_val)) flag,
ww.usingname USINGNAME,
ww.status STATUS,
ww.name QLOCATIONNAME,
ww.enclose ENCLOSE,
ww.long_function LONG_FUNCTION,
ww.control_way CONTROL_WAY,
ww.charge CHARGE,
ww.username USERNAME,
ww.zaituu ZAITUU,
ww.using,
ww.gid GID,
ww.ysh YSH,
ww.node_name NODE_NAME
from (select c1.code_val,
(select DESCRIPTION
from mm_dictionary
where id = c1.using) USINGNAME,
c1.using,
(case
when (select n.name || '-' ||
(select name
from c_location
where id = n.location_id)
from c_node n
where n.id IN
(SELECT entity_id
from c_codepoolentity aa
where aa.codepool_id in
(c1.codepool_id))
and n.nodetype_id = 102061) is not null then
'占用'
else
'空闲'
end) STATUS,
(select cl.name
from c_location cl
where cl.id = c1.area_id) name,
(case
when (select description
from mm_dictionary
where id = c1.lifecycle) = '投入使用' then
'装入'
when (select description
from mm_dictionary
where id = c1.lifecycle) = '工程' then
'未装入'
end) ENCLOSE,
c1.long_function,
c1.control_way,
c1.charge,
'' USERNAME,
'无' zaituu,
'' GID,
'' YSH,
(select n.name || '-' ||
(select name
from c_location
where id = n.location_id)
from c_node n
where n.id IN
(SELECT entity_id
from c_codepoolentity aa
where aa.codepool_id in
(c1.codepool_id))
and n.nodetype_id = 102061) NODE_NAME
from c_code c1
where c1.codetype_id = 114003
and c1.using = f_get_dictionaryid('用途', 2, 'AG')
and c1.code_val between 33064000 and 33064999
union all
select c1.code_val,
(select DESCRIPTION
from mm_dictionary
where id = c1.using) USINGNAME,
c1.using,
(select DESCRIPTION
from mm_dictionary
where id = c1.servicestatus) STATUS,
(select cl.name
from c_location cl
where cl.id = c1.area_id) name,
(case
when (select description
from mm_dictionary
where id = c1.lifecycle) = '投入使用' then
'装入'
when (select description
from mm_dictionary
where id = c1.lifecycle) = '工程' then
'未装入'
end) ENCLOSE,
c1.long_function,
c1.control_way,
c1.charge,
'' USERNAME,
'无' zaituu,
'' GID,
'' YSH,
'' NODE_NAME
from c_code c1
where c1.codetype_id = 114003
and (c1.using not in
(f_get_dictionaryid('用途', 2, 'AG'),
f_get_dictionaryid('用途', 3, 'PON'),
f_get_dictionaryid('用途', 1, '普通号码')) or
c1.using is null)
and c1.codetype_id not in (114017, 114019)
and c1.code_val between 33064000 and 33064999
union all
select c2.code_val,
(select DESCRIPTION
from mm_dictionary
where id = c2.using) USINGNAME,
c2.using,
(case
when (select n.name || '-' ||
(select name
from c_location
where id = n.location_id)
from c_node n
where n.id IN
(SELECT entity_id
from c_codepoolentity aa
where aa.codepool_id in
(c2.codepool_id))
and n.nodetype_id = 102042) is not null then
'占用'
else
'空闲'
end) STATUS,
(select cl.name
from c_location cl
where cl.id = c2.area_id) name,
(case
when (select description
from mm_dictionary
where id = c2.lifecycle) = '投入使用' then
'装入'
when (select description
from mm_dictionary
where id = c2.lifecycle) = '工程' then
'未装入'
end) ENCLOSE,
c2.long_function,
c2.control_way,
c2.charge,
'' USERNAME,
'无' zaituu,
'' GID,
'' YSH,
(SELECT strcat(name)
FROM c_node cn
where id in (SELECT entity_id
from c_codepoolentity aa
where aa.codepool_id in
(c2.codepool_id))
and cn.nodetype_id in (102042)) NODE_NAME
from c_code c2
where c2.codetype_id = 114003
and c2.using = f_get_dictionaryid('用途', 3, 'PON')
and c2.code_val between 33064000 and 33064999
union all
select c3.code_val,
(select DESCRIPTION
from mm_dictionary
where id = c3.using) USINGNAME,
c3.using,
(select DESCRIPTION
from mm_dictionary
where id = c3.servicestatus) STATUS,
(select cl.name
from c_location cl
where cl.id = c3.area_id) name,
(case
when (select description
from mm_dictionary
where id = c3.lifecycle) = '投入使用' then
'装入'
when (select description
from mm_dictionary
where id = c3.lifecycle) = '工程' then
'未装入'
end) ENCLOSE,
c3.long_function,
c3.control_way,
c3.charge,
'' USERNAME,
'无' zaituu,
'' GID,
'' YSH,
'' NODE_NAME
from c_code c3
where c3.codetype_id = 114003
and c3.using =
f_get_dictionaryid('用途', 1, '普通号码')
and c3.code_val between 33064000 and 33064999
union all
select c4.code_val,
(select DESCRIPTION
from mm_dictionary
where id = c4.using) USINGNAME,
c4.using,
(select DESCRIPTION
from mm_dictionary
where id = c4.servicestatus) STATUS,
(select cl.name
from c_location cl
where cl.id = c4.area_id) name,
(case
when (select description
from mm_dictionary
where id = c4.lifecycle) = '投入使用' then
'装入'
when (select description
from mm_dictionary
where id = c4.lifecycle) = '工程' then
'未装入'
end) ENCLOSE,
c4.long_function,
c4.control_way,
c4.charge,
'' USERNAME,
'无' zaituu,
'' GID,
'' YSH,
'' NODE_NAME
from c_code c4
where c4.code_val between 33064000 and 33064999
and not exists
(select *
from c_sncodeinfo zz
where zz.startnum <= c4.code_val
and zz.endnum >= c4.code_val)
and c4.codetype_id in (114017, 114019)) ww) uu
group by uu.flag,
uu.usingname,
uu.using,
uu.status,
uu.QLOCATIONNAME,
uu.enclose,
uu.long_function,
uu.control_way,
uu.charge,
uu.username,
uu.zaituu,
uu.gid,
uu.ysh,
uu.node_name
union all
select si.startnum,
si.endnum,
(endnum - startnum + 1) NUMBER_CAP,
si.usefor,
'占用',
(select cl.name
from c_location cl
where cl.id = si.qlocation_id) name,
(select (case
when cc.lifecycle = 4965 then
'装入'
else
'未装入'
end) enclose
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.long_function
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.control_way
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.charge
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.using
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
ct.name username,
'无' zaituu,
si.gid,
to_char(si.group_index_tn_logic) YSH,
cn.name NODE_NAME
from c_service ce,
c_customer ct,
c_servicecustomer sct,
c_sncodeinfo si,
c_serviceentity cs,
c_node cn
where si.startnum >= 33064000
and si.endnum <= 33064999
and ce.id = si.hid
and si.hid = sct.service_id
and si.status = 1
and ct.id(+) = sct.customer_id
and ce.id = cs.service_id
and cs.entity_id = cn.id
and not exists (select sss.*
from c_sncodeinfo zz, c_servicecurrent sss
where zz.gid = si.gid
and sss.id = zz.hid
and zz.status = 1)
and si.usefor in ('30B+D SIP', 'DID SIP')
and cs.entitytype_id = 102023
union all
select si.startnum,
si.endnum,
(endnum - startnum + 1) NUMBER_CAP,
si.usefor,
'占用',
(select cl.name
from c_location cl
where cl.id = si.qlocation_id) name,
(select (case
when cc.lifecycle = 4965 then
'装入'
else
'未装入'
end) enclose
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.long_function
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.control_way
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.charge
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.using
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
ct.name username,
'有' zaituu,
si.gid,
to_char(si.group_index_tn_logic) YSH,
cn.name NODE_NAME
from c_servicecurrent ce,
c_customer ct,
c_servicecustomer sct,
c_sncodeinfo si,
c_servicecurrententity cs,
c_node cn
where si.startnum >= 33064000
and si.endnum <= 33064999
and ce.id = si.hid
and si.hid = sct.service_id
and si.status = 1
and ct.id(+) = sct.customer_id
and ce.id = cs.service_id
and cs.entity_id = cn.id
and not exists (select sss.*
from c_sncodeinfo zz, c_service sss
where zz.gid = si.gid
and sss.id = zz.hid
and zz.status = 1)
and si.usefor in ('30B+D SIP', 'DID SIP')
and cs.entitytype_id = 102023
union all
select si.startnum,
si.endnum,
(endnum - startnum + 1) NUMBER_CAP,
si.usefor,
'占用',
(select cl.name
from c_location cl
where cl.id = si.qlocation_id) name,
(select (case
when cc.lifecycle = 4965 then
'装入'
else
'未装入'
end) enclose
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.long_function
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.control_way
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.charge
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.using
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
ct.name username,
'有' zaituu,
si.gid,
to_char(si.group_index_tn_logic) YSH,
cn.name NODE_NAME
from c_servicecurrent ce,
c_customer ct,
c_servicecustomer sct,
c_sncodeinfo si,
c_servicecurrententity cs,
c_node cn
where si.startnum >= 33064000
and si.endnum <= 33064999
and ce.id = si.hid
and si.hid = sct.service_id
and si.status = 1
and ct.id(+) = sct.customer_id
and ce.id = cs.service_id
and cs.entity_id = cn.id
and exists (select sss.*
from c_sncodeinfo zz, c_service sss
where zz.gid = si.gid
and sss.id = zz.hid
and zz.status = 1)
and si.usefor in ('30B+D SIP', 'DID SIP')
and cs.entitytype_id = 102023
union all
select si.startnum,
si.endnum,
(endnum - startnum + 1) NUMBER_CAP,
si.usefor,
'占用',
(select cl.name
from c_location cl
where cl.id = si.qlocation_id) name,
(select (case
when cc.lifecycle = 4965 then
'装入'
else
'未装入'
end) enclose
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.long_function
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.control_way
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.charge
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.using
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
ct.name username,
'无' zaituu,
si.gid,
to_char(si.group_index_tn_logic) YSH,
(case
when (select count(0)
from c_serviceservice eg2, c_service cs
where eg2.businessglobalserviceid = ce.globalserviceid
and eg2.entityglobalserviceid = cs.globalserviceid
and cs.servicetype_id in (110132, 110129)) > 0 then
(SELECT strcat(n.name)
FROM c_node n,
c_port p,
c_serviceentity se,
c_service s1,
c_serviceservice eg1,
c_serviceservice eg2
where n.id = p.node_id
and p.id = se.entity_id
and s1.id = se.service_id
and s1.globalserviceid = eg1.entityglobalserviceid
and eg1.businessglobalserviceid =
eg2.entityglobalserviceid
and eg2.businessglobalserviceid = ce.globalserviceid
and se.servicetype_id = 110125
and se.abstractentity_id = 105
and n.nodetype_id in (102010, 102062))
else
(SELECT strcat(n.name)
FROM c_node n,
c_port p,
c_serviceentity se,
c_service s1,
c_serviceservice eg1,
c_serviceservice eg2,
c_serviceservice eg3
where n.id = p.node_id
and p.id = se.entity_id
and s1.id = se.service_id
and s1.globalserviceid = eg1.entityglobalserviceid
and eg1.businessglobalserviceid =
eg2.entityglobalserviceid
and eg2.businessglobalserviceid =
eg3.entityglobalserviceid
and eg3.businessglobalserviceid = ce.globalserviceid
and se.servicetype_id in (110128, 110125)
and se.abstractentity_id = 105
and n.nodetype_id in (102010, 102062))
end) NODE_NAME
from c_service ce,
c_customer ct,
c_servicecustomer sct,
c_sncodeinfo si,
c_serviceentity cs,
c_node cn
where si.startnum >= 33064000
and si.endnum <= 33064999
and ce.id = si.hid
and si.hid = sct.service_id
and si.status = 1
and ct.id(+) = sct.customer_id
and ce.id = cs.service_id
and cs.entity_id = cn.id
and not exists (select sss.*
from c_sncodeinfo zz, c_servicecurrent sss
where zz.gid = si.gid
and sss.id = zz.hid
and zz.status = 1)
and si.usefor not in ('30B+D SIP', 'DID SIP')
and cs.entitytype_id = 102023
union all
select si.startnum,
si.endnum,
(endnum - startnum + 1) NUMBER_CAP,
si.usefor,
'占用',
(select cl.name
from c_location cl
where cl.id = si.qlocation_id) name,
(select (case
when cc.lifecycle = 4965 then
'装入'
else
'未装入'
end) enclose
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.long_function
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.control_way
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.charge
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.using
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
ct.name username,
'有' zaituu,
si.gid,
to_char(si.group_index_tn_logic) YSH,
(case
when (select count(0)
from c_serviceservice eg2, c_service cs
where eg2.businessglobalserviceid = ce.globalserviceid
and eg2.entityglobalserviceid = cs.globalserviceid
and cs.servicetype_id in (110132, 110129)) > 0 then
(SELECT strcat(n.name)
FROM c_node n,
c_port p,
c_serviceentity se,
c_service s1,
c_serviceservice eg1,
c_serviceservice eg2
where n.id = p.node_id
and p.id = se.entity_id
and s1.id = se.service_id
and s1.globalserviceid = eg1.entityglobalserviceid
and eg1.businessglobalserviceid =
eg2.entityglobalserviceid
and eg2.businessglobalserviceid = ce.globalserviceid
and se.servicetype_id = 110125
and se.abstractentity_id = 105
and n.nodetype_id in (102010, 102062))
else
(SELECT strcat(n.name)
FROM c_node n,
c_port p,
c_serviceentity se,
c_service s1,
c_serviceservice eg1,
c_serviceservice eg2,
c_serviceservice eg3
where n.id = p.node_id
and p.id = se.entity_id
and s1.id = se.service_id
and s1.globalserviceid = eg1.entityglobalserviceid
and eg1.businessglobalserviceid =
eg2.entityglobalserviceid
and eg2.businessglobalserviceid =
eg3.entityglobalserviceid
and eg3.businessglobalserviceid = ce.globalserviceid
and se.servicetype_id in (110128, 110125)
and se.abstractentity_id = 105
and n.nodetype_id in (102010, 102062))
end) NODE_NAME
from c_servicecurrent ce,
c_customer ct,
c_servicecustomer sct,
c_sncodeinfo si,
c_servicecurrententity cs,
c_node cn
where si.startnum >= 33064000
and si.endnum <= 33064999
and ce.id = si.hid
and si.hid = sct.service_id
and si.status = 1
and ct.id(+) = sct.customer_id
and ce.id = cs.service_id
and cs.entity_id = cn.id
and not exists (select sss.*
from c_sncodeinfo zz, c_service sss
where zz.gid = si.gid
and sss.id = zz.hid
and zz.status = 1)
and si.usefor not in ('30B+D SIP', 'DID SIP')
and cs.entitytype_id = 102023
union all
select si.startnum,
si.endnum,
(endnum - startnum + 1) NUMBER_CAP,
si.usefor,
'占用',
(select cl.name
from c_location cl
where cl.id = si.qlocation_id) name,
(select (case
when cc.lifecycle = 4965 then
'装入'
else
'未装入'
end) enclose
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.long_function
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.control_way
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.charge
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
(select cc.using
from c_code cc
where cc.code_val = si.startnum
and cc.codetype_id in (114017, 114019)),
ct.name username,
'有' zaituu,
si.gid,
to_char(si.group_index_tn_logic) YSH,
(case
when (select count(0)
from c_serviceservice eg2, c_service cs
where eg2.businessglobalserviceid = ce.globalserviceid
and eg2.entityglobalserviceid = cs.globalserviceid
and cs.servicetype_id in (110132, 110129)) > 0 then
(SELECT strcat(n.name)
FROM c_node n,
c_port p,
c_serviceentity se,
c_service s1,
c_serviceservice eg1,
c_serviceservice eg2
where n.id = p.node_id
and p.id = se.entity_id
and s1.id = se.service_id
and s1.globalserviceid = eg1.entityglobalserviceid
and eg1.businessglobalserviceid =
eg2.entityglobalserviceid
and eg2.businessglobalserviceid = ce.globalserviceid
and se.servicetype_id = 110125
and se.abstractentity_id = 105
and n.nodetype_id in (102010, 102062))
else
(SELECT strcat(n.name)
FROM c_node n,
c_port p,
c_serviceentity se,
c_service s1,
c_serviceservice eg1,
c_serviceservice eg2,
c_serviceservice eg3
where n.id = p.node_id
and p.id = se.entity_id
and s1.id = se.service_id
and s1.globalserviceid = eg1.entityglobalserviceid
and eg1.businessglobalserviceid =
eg2.entityglobalserviceid
and eg2.businessglobalserviceid =
eg3.entityglobalserviceid
and eg3.businessglobalserviceid = ce.globalserviceid
and se.servicetype_id in (110128, 110125)
and se.abstractentity_id = 105
and n.nodetype_id in (102010, 102062))
end) NODE_NAME
from c_servicecurrent ce,
c_customer ct,
c_servicecustomer sct,
c_sncodeinfo si,
c_servicecurrententity cs,
c_node cn
where si.startnum >= 33064000
and si.endnum <= 33064999
and ce.id = si.hid
and si.hid = sct.service_id
and si.status = 1
and ct.id(+) = sct.customer_id
and ce.id = cs.service_id
and cs.entity_id = cn.id
and exists (select sss.*
from c_sncodeinfo zz, c_service sss
where zz.gid = si.gid
and sss.id = zz.hid
and zz.status = 1)
and si.usefor not in ('30B+D SIP', 'DID SIP')
and cs.entitytype_id = 102023) zz
order by zz.STARTCODE
1068
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
