索引的本质: 排好序的快速查找数据结构
索引的优势:
1》、类似大学图书馆书目索引,提高数据检索的效率,降低数据库的 IO 成本
2》、通过索引对数据进行排序,降低数据排序的成本,降低了 CPU 的消耗
索引的劣势:
1》、实际上是一张表,该表保存了主键和索引字段,并指向实体表记录,索引也会占空间
2》、索引可以提高遍历查询速度,但也降低了表的更新速度;每次更新不仅需要保存表索引数据,还需调整更新带来的键值变化后的索引数据
3》、索引只是提高效率的一个因素,还需要合理建立索引和优化 sql 语句
索引虽然会提高检索速度,但是本质上还是一张表,也会占据空间,所以不宜过多,一般一张表保持在3~5个为佳
索引的分类: -- 索引必是由条件字段触发
B 树索引 :字段唯一 锁定一行 用于OLTP系统(查询系统)
位图索引 :字段徐有重复值 锁定一组 用于OLAP系统(数仓系统)
复合索引 : 最少两个字段上,建索引
反转索引 :多用于解决热点快问题 索引后加 reverse
监控索引 :
分区索引 :
查看执行计划: set autotrace traceonly;
如一张表(id,name,性别,身份证号,省份,电话,...) 身份证号适宜用B树索引,性别和省份就宜用位图索引
-- dba_objects 系统表,建表的用户和建表的数据存在这个表中
select count(*) from dba_objects;
select count(*) from t1;
-- 备份表
create table t1 as select * from dba_objects;
set autotrace traceonly; set autotrace off;
select * from t1 where object_id = 22; (全表扫描) 第一次查询和第二次查询 physical reads 的值 有变化
** B 树索引 -- 针对某一列的索引
建索引 (idx_表名_列名)
create index idx_t1_id on t1(object_id); --B 树索引
select * from t1 where object_id = 22; (索引扫描) 索引扫描前和扫描后的 consistent gets 值会变化
-- 打印出的意思是 先索引扫描 后通过rowid 找到需要锁定的数据
-- 同一个用户表名不能重复,不同的库里表名可以重复
----------- 表变化 索引变大 表变小 索引变小 --------------------
select bytes/1024/1024 from user_segments where segment_name = 'T1';
select bytes/1024/1024 from user_segments where segment_name = 'IDX_T1_ID';
insert into t1 select * from t1; -- t1 表的数据增大一倍
commit; -- 提交之后可以在新开的窗口里面同步数据 串事务的概念
-- 查看表数据
select bytes/1024/1024 from user_segments where segment_name = 'T1';
select bytes/1024/1024 from user_segments where segment_name = 'IDX_T1_ID';
-- 清空数据
truncate table t1;
select bytes/1024/1024 from user_segments where segment_name = 'T1';
select bytes/1024/1024 from user_segments where segment_name = 'IDX_T1_ID';
------------------------------------------------------------------
-- 增加数据的时候,表的索引会自动更新
drop index idx_t1_id; -- 删除索引
truncate table t1; -- 清空数据
insert into t1 select * from dba_objects;
** 位图索引 -- 某一列数据上有重复值而展开的索引
set autotrace off;
select owner||','||object_name||','||object_type from t1 where owner = 'scott'; -- scott 用户下的表信息
select owner||','||object_name||','||object_type from t1 where owner = 'TEST'; -- TEST 用户下的表信息
select owner,count(*) from t1 group by owner;
set autotrace traceonly; -- 打开执行计划
set autotrace off;
select owner,count(owner) from t1 group by owner; (全表扫描)
create index idx_t1_owner on t1(owner); -- 增加一个索引
select owner,count(owner) from t1 group by owner; (还是 全表扫描)-- 77 consistent gets
select owner from t1 where owner = 'SCOTT'; (索引扫描)
drop index idx_t1_owner;
create bitmap index idx_t1_owner on t1(owner); -- 位图索引 多用于分析 OLAP
select owner,count(owner) from t1 group by owner; (触发索引,索引扫描)-- 80 consistent gets
select owner,count(*) from t1 group by owner; (索引扫描)or-- 有时候触发索引,有时候不触发索引
----------------------------------------------------------------------------------------------
大表建索引: -- online 同时操作会话,建索引时不影响其他人使用
CREATE OR REPLACE PROCEDURE create_index IS -- parallel 多线程并发
create_sql varchar2(200) -- nologging 不记日志
BEGIN
create_sql :='CREATE INDEX idx_table_id ON table(id) online parallel (degree 6) nologging';
execute immediate create_sql;
END;
vi /home/oracle/Desktop/test/c_idx.sh
#! /bin/sh
DATE = "`date +Y-%m-%d`"
ORA_HOME="/oracle/app/oracle/product/10.2.0/db_1/bin"
$ORA_HOME/sqlplus lckj/lckj << EOF >/home/oracle/Desktop/test/create_idx_${DATE}.log
set timing on;
set serveroutput on size 100000;
prompt "call create_index"
call create_index();
commit;
EXIT
EOF
chmod +x /home/oracle/Desktop/test/c_idx.sh
nohup sh /home/oracle/Desktop/test/c_idx.sh &
---------------------------------------------------------------------------------------------
** 复合索引
drop table t1;
create table t1 as select * from dba_objects;
-- 不同列得索引
create index idx_t1_id on t1(object_id);
create index idx_t1_name on t1(object_name);
set autotrace traceonly; set autotrace off;
select * from t1 where object_name = 'EMP' and object_id = 62940; (只会扫描一个索引,不会都执行)
-- 索引是由条件字段触发,遇到字段触发
drop index idx_t1_id;
drop index idx_t1_name;
create index idx_t1_IdName on t1(object_id,object_name); -- 复合索引:在两个字段上建立索引,而不是分别在字段上建立索引
select * from t1 where object_name = 'EMP' and object_id = 51749; (触发索引)-- 75 consistent gets
select * from t1 where object_name = 'EMP'; (不触发索引)-- 759 consistent gets
select * from t1 where object_id = 51749; (触发索引)-- 75 consistent gets
-- 因为复合索引在id,name上建立,id 在前导列上,所以条件中只存在id时,也会触发索引
** 反转索引 -- 热点块问题,高并发的问题,避免访问量比较大,速度比较慢的问题
** 监控索引-- 根据索引的使用频率进行筛选
drop table t1;
create table t1 as select * from dba_objects;
create index idx_01 on t1(created);
create index iex_02 on t1(object_name);
set autotrace traceonly;
启动监控功能
select 'alter index sys.'||index_name||' monitoring usage;' from user_indexes where table_name = 'T1';
开始监控
alter index sys.idx_01 monitoring usage;
alter index sys.iex_02 monitoring usage;
查看监控信息表
select * from v$object_usage; 显示 YES NO
MONITO USED START_MONITORING
------ ------ --------------------
YES NO 03/08/2022 17:14:52
YES NO 03/08/2022 17:14:52
YES NO 03/08/2022 17:14:52
select * from t1 where object_name = 'EMP'; -- 执行该语句
select * from v$object_usage; -- 再次查看
取消监控
select 'alter index sys.'||index_name||' nomonitoring usage;' from user_indexes where table_name = 'T1';
alter index sys.IDX_01 nomonitoring usage;
alter index sys.IEX_02 nomonitoring usage;
=======================================================================
select * from EMP
create index idx_EMP_id on EMP(ID);
create bitmap index idx_EMP_sex on EMP(sex);
explain plan for select * from EMP where id = '55555';-- 开启执行计划
select * from table (dbms_xplan.display);
explain plan for select * from EMP where sex = '男';
select * from table (dbms_xplan.display);
** 以下五行是 Oracle 创建用户的操作,若为 mysql 则可忽略
create user test identified by jumplanb; -- 创建用户
grant create session to test; -- 创建会话权限
grant create table to test; -- 创建表权限
grant unlimited tablespace to test; -- 分配表空间使用权限
select tname from test; -- 查用户中的表
create table emp
(id varchar(10)
,empno varchar(10)
,ename varchar(32)
,sex varchar(6)
,job varchar(12)
,sal number(8,2)
,comm number(8,2)
,deptno int
,tel varchar(11)
);
plsql 导入外部数据
使用 mysql 导出crv 数据,选择与虚拟机端相符合的编码方式,创建新的Oracle用户,使用plsql
登录,新建sql 命令窗口,输入符合Oracle语法规则的建表数据,新建表,工具菜单选择文本导入器,
注: 关闭打开的数据表文件,
进行导入