Oracle 索引在海量数据的应用
-- 速度（提升速度）
-- 程序员必备技能
-- 1.绝对不能有not in
-- 2.对常见的查询条件的字段，需要添加索引
-- 3.对于海量的数据，设置分区
-- 系统维护
-- 保证数据库服务器内存足够大，让SGA足够大，保证缓存足够，减少硬盘的读写
-- 尽可能把最小而且查询频率最高的数据，要规划好表空间，放在SSD
--原理：
--索引，减少遍历（由头到尾的查询一次）
-- 2张表
create table t_user5 (
id number primary key,
user_name varchar2(100),
sex number
);
select key_value,display from dicts where key_type='sex' and lang='en'
insert into t_user5 values(1,'小平',1);
insert into t_user5 values(2,'小盛',1);
insert into t_user5 values(3,'小龙',0);
insert into t_user5 values(4,'小君',0);
insert into t_user5 values(5,'小芳',0);
commit
select * from t_user5
select key_value,display from dicts where key_type='sex' and lang='en'
-- 违反主键这个唯一性约束
insert into t_user5(select id,user_name,sex from t_user5)
select myseq.nextval from dual
insert into t_user5(
select
myseq.nextval,
user_name||id,
sex
from t_user5
)
commit
select count(1) from t_user5
-- 0.013 (利用主键唯一索引)
select * from t_user5 where id=8000000
--------------------
-- 0.014
select t_user5.id,t_user5.user_name,dicts.display from t_user5
left join dicts on
t_user5.sex=dicts.key_value
and key_type='sex' and lang='en'
where t_user5.id=8000000
------------------------
--- 2.193秒 （相差了156倍）
select count(1) from t_user5
left join dicts on
t_user5.sex=dicts.key_value
and key_type='sex' and lang='en'
-- 耗时2.104
select count(1) from t_user5
left join dicts on
t_user5.sex=dicts.key_value
and key_type='sex' and lang='en'
where t_user5.user_name = '小平1'
------------------------------
-- 2.085
select * from t_user5
left join dicts on
t_user5.sex=dicts.key_value
and key_type='sex' and lang='en'
where t_user5.user_name = '小平1'
--- 数据从1000万提升（失败了）
-- 以空间换时间（写好书后，新建目录）
create index user5_name_index
on t_user5(user_name)
alter tablespace oa_data
add datafile 'c:\data\oa_data_003.dbf'
size 100m
autoextend on next 5m maxsize 2048m;
-----------------
--user_name 有了索引后，速度提升会0.045秒
select * from t_user5
left join dicts on
t_user5.sex=dicts.key_value
and key_type='sex' and lang='en'
where t_user5.user_name = '小平1'
select * from dicts
select * from t_user5
-------------------
insert into dicts (
select
myseq.nextval,
display,
myseq.nextval key_value,
key_type,
lang from dicts
)
commit
select count(1) from dicts
-- 没有索引：耗时1秒
select * from dicts where key_type='sex' and lang='en' and key_value=1
---------添加索引
create index dicts_kkl on dicts(key_value,key_type,lang)
-- 有索引：0.02秒
select * from dicts where key_type='sex' and lang='en' and key_value=1
