1.Sql优化:explain
分析
- seelect * from tf_f_user;--全表扫描
- select t.user_id from tf_f_user t;--idex_fast_full_sacn
- select * from tf_f_user t where t.user_id is null;--全表扫描
- select * from tf_f_user t where t.user_id is not null;--全表扫描
- select * from tf_f_user t where t.user_id = '123';--无索引全表扫描,有索引查索引
- select count(1) from tf_f_user t;--index_fast_full_sacan
- select * from tf_f_user t , tf_f_user_member tt where t.user_id = tt.member_role_id;--两张表全表扫描
- select t.* from tf_f_user t , tf_f_user_member tt where t.user_id = tt.member_role_id;--tf_f_user_member表index_fast_full_scan
- select * from tf_f_user t left join tf_f_user_member tt on t.user_id = tt.member_role_id;--全表扫描
- select t.* from tf_f_user t left join tf_f_user_member tt on t.user_id = tt.member_role_id;--tf_f_user_member表index_fast_full_scan
总结
- Select * 或者无索引的字段,即使表有索引,也需全表查询
- Where条件无索引字段,is null ,null,’’,全表查询
- 左连接或者右连接或者内连接的使用等不会缩短查询时间,只是一种匹配方式
- 关联查询若只是查其中一张表数据,不要去查另一张表数据,否则变成两张表全表查询
- 关联查询条件需走索引,不然走全表查询
2. 几种insert数据的效率比较
原始sql,数据量为9640188,一个左连接
insert into serv
(select USER_ID SERV_ID,
0 AGREEMENT_ID,
CUST_ID CUST_ID,
PRODUCT_ID PRODUCT_ID,
0 ACCT_ID,
0 BILLING_CYCLE_TYPE_ID,
in_date CREATE_DATE,
serial_number ACC_NBR,
open_date EFF_DATE,
to_date('20501231235959','YYYYMMDDHH24MISS') EXP_DATE,
eparchy_code REGION_ID,
brand_code BAND_ID,
decode(mm.member_role_code, 9225, 1, 9226, 2, 0) ATTR_CODE,
0 CREDIT_VALUE,
0 BILL_TYPE,
SERVICE_STATE_CODE STATE,
'' STATE_DATE,
'' ENCRYPT_INFO,
'' STOP_ATTR,
PREPAY_TAG SERV_PASSWD,
'0' CYCLE_STATE,
in_date CYCLE_DATE,
user_type_code USER_TYPE,
CITY_CODE CITY_CODE,
sysdate UPDATE_DATE,
'' RLB_DISP,
'' ROLL_FLAG,
NET_TYPE_CODE NET_TYPE_CODE,
FIRST_CALL_DATE FIRST_USER_DATE,
LAST_STOP_DATE LAST_STOP_DATE
from i_user t left join
(select distinct m.member_role_id, m.member_role_code
from tf_f_user_member m
where m.member_role_code in ('9225', '9226') and m.relation_type_code='ZF'
and m.end_date >= trunc(sysdate, 'mm')) mm
on t.user_id = mm.member_role_id
)
几种运行结果
- -- insert 有索引:194s
- --inseert 无索引:68
- --insert /*+parallel(16)*/ 有索引:175
- alter table serv nologging;--加上后isnert:187s
- --create as: 53s + 31s建索引
总结
若是新表,则用create table [tablename] as 最快
- 若是老表,则加上nologging在insert
- 若是老表数据不多,先让索引失效,在重建索引
- parallel(16)并发有效果,感觉不明显
几种join的总结
若有两张表:t1,t2
T1 Right join t2:至少count(t1)条记录,t1,t2中有两条匹配数据,则多count(1)-2+2*2条记录,
右连接一样
内连接:条数为符合两边条件的乘机,即若两边都有2条数据满足,则2*2条,若无 则2*0条