索引和sql优化

oracle实例：

SGA,系统全局区，整个数据库只有一个
    数据缓冲区 :  缓存数据
    共享池：  存放执行过的sql
    重做日志缓冲区： 用于支持回滚
    
PGA ，程序全局区，每个用户连接数据库之后，都会拥有一个独立的PGA，多个PGA相互之间是隔离的; 

oracle数据库：
硬盘上的地址：D:\app\Administrator\oradata\orcl
物理上划分：
    控制文件:后缀为ctl的文件，用于存放用户信息，登录信息，权限信息;
    日志文件：后缀为log的文件，用户数据库的恢复
    数据文件：后缀为dbf的文件，用于存放表空间,表，数据
逻辑上划分：
    所有的表和数据，都是存放在表空间里面的;
    新建一张表的时候，默认都是存放在USERS表空间中
    
*************************************************************************************************
索引
用于提高查询性能；
前提：有大量数据的情况下

索引的分类：
唯一索引和非唯一索引
单列索引和组合索引
B树索引：通常保存的数据是没有重复的
反向键索引：通常配合B树一起使用
位图索引 : 通常用于有大量重复数据的

关于主键，在为表设定主键的时候，系统默认会为主键创建一个唯一索引;
创建主键约束和唯一约束的时候，都会自动创建索引;

create table student(
    stuid number(8) primary key,
    stu_no number(20),
    sex   varchar2(50),
    card_no varchar2(30)
);

B树索引：
create index stunoIndex on student(stu_no);  --where stu_no-5>0
反向键索引
create index stunoIndex on student(stu_no) reverse;
位图索引：
create bitmap index sexIndex on student(sex);
组合索引
create index xxxindex on student(stu_no,sex,card_no);
当组合索引的第一个字段用于条件时，组合索引才会生效;
select 字段，字段 from student where stu_no=1 and sex='男' ;
函数索引
create index yyyindex on student(avg(sal));
如果索引字段，在查询中，非函数索引的字段不会使用索引；
索引字段如果用于了计算，索引也会失效;

当索引失效以后，如果想要强制使用索引：
hint: 
本质上就是一段注释，对于查询结果没有任何影响
参考：https://www.cnblogs.com/emilyyoucan/p/7844795.html
    /*+  */ :   +必须跟在*后面，而且中间连空格都不能有
    hint内容，必须跟在select,update,insert,delete关键字后面
    index : 表示建议使用索引,小括号里面先写表名（如果表名有别名，这里要写别名）
select /*+ index(emp 索引的名字) */ empno,ename,job,sal from emp where empno>2000;

*****************************************************************************************************
sql优化
--WHERE ENAME='SMITH'
WHERE aziduan='xxx' and b='ccc' and c='ddd'

1.    sql语句全部使用大写
2.    不要使用*来查询
3.    当需要写多个条件的时候，过滤掉数据最多的条件，应该放在sql的最右端
4.    =,<=与<,>相比较，>=,<=的效率会更高
5.    between...and...的效率比>,<的写法效率要高
6.    尽量避免使用in,any,distinct..这些关键字
7.    尽量避免使用否定性的条件,包括not等关键字
8.    尽量避免使用函数
9.    尽量减少使用联表查询，子查询等

10.当数据量比较高的时候，使用索引来提高查询的效率

11.尽量避免对索引列做运算

12.当一张表中列太多，或者数据量太大时，可以通过表的横向，纵向切割来提高效率

*******************************************************************************************
数据库设计：

三大范式：
参考：https://www.cnblogs.com/sxkio/p/15955665.html
第一范式：列不可再分
第二范式：加主键
第三范式：非主键字段，如果与当前主键无直接关联，而是与表中其他某个字段有关联，此时应该把这些字段单独分离成新的表

---------------------------------------------------------------------------------------------------

oracle的远程访问
开始菜单->net manager
实例选项和监听选项中，都有主机名和端口号
端口号默认为1521；
主机名默认为localhost(允许的值有四个：localhost,127.0.0.1,ip地址,计算机名)

也可以在路径下：D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
记事本打开listener.ora和tnsnames.ora两个文件，修改其中的主机名

要想允许远程访问，需要把上述的主机名修改为ip地址或者是计算机名;修改之后，需要重新启动监听服务和实例服务;

oracle服务端准备ok之后，客户端开始访问：
客户端连接：
第一种方式：直接在database下，写IP地址/orcl方式，加上账号密码，完成连接;
第二种方式：建立远程连接服务
    a.开始菜单->net manager窗口：
        左上角，绿色的加号，创建远程服务;
    b.开始菜单->oracle安装目录->配置和移植工具->net configuration assistent
        本地网络服务名配置->...->下一步到完成；

**************************************************************************************************
oracle的优化：
1.sql优化
2.索引
3.分区

oracle服务端是如何执行一条sql的？
1.语句解析
    a.查询告诉缓存:检查缓存中，是否存在执行计划
    b.语句的合法性检查：检查sql语句的语法知否合规
    c.语义检查:检查表名，字段名是否正确
    d.获取对象解析锁：给表加锁，保证数据一致性;
    e.权限检查：检查是否拥有权限
    f.确定最优执行计划
2.语句执行
3.结果收集

************************************************************************************
执行计划
如何查看执行计划：
explain plan for sql语句；

select * from table(dbms_xplan.display);

operation中，原则上来说，缩进越多，越先执行(大部分时候，都是从下往上执行)

oracle在执行sql语句，选择执行计划的时候，有两种优化方式：
1.RBO：基于规则的优化方式
2.CBO: 基于代价的优化方式

全表扫描(table access full)
举例：查询所有的员工信息
explain plan for select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;
select * from table(dbms_xplan.display);

通过ROWID的表存取(TABLE ACCESS BY USER ROWID)
explain plan for select * from emp where rowid='AAAR3sAAEAAAACXAAA';
select * from table(dbms_xplan.display);

索引扫描
索引扫描又分几种方式：
a.索引唯一扫描(INDEX UNIQUE SCAN)
explain plan for select empno,ename,job,sal from emp where empno=7369;
select * from table(dbms_xplan.display);

b.索引范围扫描(INDEX RANGE SCAN)
explain plan for select empno,ename,job,sal from emp where empno>2000;
select * from table(dbms_xplan.display);

empno本身是创建了索引的（PK_EMP），但是在查询中，把empno放到了函数里面，所以，此时查询的时候，索引会失效
explain plan for select empno,ename,job,sal from emp where substr(empno,1,4)>2000;
为了让索引生效，这里添加hint注释,再次运行的时候，这里的索引再次生效，不过这里已经是索引全扫描了
explain plan for select /*+ index(emp pk_emp)*/ empno,ename,job,sal from emp where substr(empno,1,4)>2000;

c.索引全扫描(INDEX FULL SCAN)

********************************************************************************************************************
上述举例都是单表的情况，如果是联表呢？

1.排序--合并连接(sort join--->merge join)
对于非等值连接，效率比较高
如果关联字段有索引，性能也会更好
explain plan for select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno
select * from table(dbms_xplan.display);

2.嵌套循环(NESTED LOOPS)
适用于小表关联大表

需要先指明驱动表和被驱动表，一般以小表（数据量比较少的表）作为驱动表，以大表（数据量比较大的表）作为被驱动表;
驱动表，也叫外侧表；被驱动表，也叫内侧表;
先去查询驱动表中的数据,然后通过驱动表中每一行数据，去被驱动表中查找;

这里把d(dept)表作为驱动表
explain plan for select /*+ leading(d) use_nl(e) */ e.empno,e.ename,e.job,e.sal,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno
select * from table(dbms_xplan.display);

3.哈希连接（HASH JOIN OUTER）
用于等值连接
大表关联小表，效率更好；
大表关联大表，也能提高效率;
explain plan for select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno;
select * from table(dbms_xplan.display);

4.笛卡尔积

-----------------------------------------------------------------------------------------------------------------------------

表分区
分区必须在创建表的时候就分区；
表一旦创建完成，就不能进行分区了;

分区的方式：
1.范围分区
2.列表分区
3.散列分区
4.组合分区

1.范围分区
按照工资的范围，进行分区：
0-2000，第一分区
2001-4000，第二分区
4000以上，第三分区
create table emp(
    empno  number(8) primary key,
    ename  varchar2(50),
    job    varchar2(50),
    sal    number(8)
)
partition by range(sal)(
    partition firstpart values less than (2000),
    partition secondpart values less than (4000),
    partition threepart values less than (maxvalue)
);

查看表是否存在
select * from emp;

往表中添加数据
insert into emp values(1001,'zhangsan','助理',1500);
commit;
有了分区之后，增，删，改，查操作，与未分区之前，完全一致；

查询指定分区下的数据
select * from emp partition(firstpart);
此时可以查看到数据，确实存入了第一个分区

insert into emp values(1002,'lisi','程序员',3500);
commit;

数据存入了第二分区
select * from emp partition(firstpart);--没有1002的数据
select * from emp partition(secondpart);--有了1002的数据

insert into emp(empno,ename,job) values(1003,'wangwu','销售员');
commit;

select * from emp partition(firstpart);
select * from emp partition(secondpart);
当sal为空时，数据也分到了第三个分区中
select * from emp partition(threepart);

2.列表分区
创建用户表,表中有一个字段：岗位；
岗位的可能值有：程序员，销售员，助理，经理
存入数据的时候，按照岗位来进行分区

create table t_users(
    userid    number(8) primary key,
    username  varchar2(50),
    job       varchar2(50)
)
partition by list(job)
(
    partition pro_part values ('程序员'),
    partition sale_part values ('销售员'),
    partition help_part values ('助理'),
    partition manager_part values ('经理'),
    partition def_part values (default)
);

先检查表是否存在
select * from t_users;

添加数据
insert into t_users values(1001,'zhangsan','销售员');
commit;

查询指定分区下的数据:
select * from t_users partition(sale_part);

如果job为空呢？
insert into t_users(userid,username) values(1002,'lisi');
commit;

没有为空单独分区的时候，job为空的数据，会存入默认分区中
select * from t_users partition(def_part);

3.散列分区
create table dept(
    dno   number(8) primary key,
    dname varchar2(50)
)
partition by hash(dno)
(
    partition fp,
    partition sp,
    partition tp
);

insert into dept values(10,'销售部');
insert into dept values(20,'开发部');
insert into dept values(30,'人事部');

select * from dept partition(tp);

4.组合分区
创建订单表，有订单创建时间，订单状态
先按照订单创建时间，做父分区，范围分区;
再按照订单状态，做子分区
订单状态：0-未支付，1-已支付，2-已到货

create table t_order(
    orderid   number(8) primary key,
    userid    number(8),
    shopid    number(8),
    total_money  number(8,2),
    create_time date,
    order_status  varchar2(10)
)
partition by range(create_time)
subpartition by list(order_status)
(
    partition p1 values less than (to_date('2022-03-01','yyyy-mm-dd'))
    (
        subpartition p1_sub1 values ('0'),
        subpartition p1_sub2 values ('1'),
        subpartition p1_sub3 values ('2'),
        subpartition p1_sub4 values (default)
    ),
    partition p2 values less than (to_date('2022-06-01','yyyy-mm-dd'))
    (
        subpartition p2_sub1 values ('0'),
        subpartition p2_sub2 values ('1'),
        subpartition p2_sub3 values ('2'),
        subpartition p2_sub4 values (default)
    ),
    partition p3 values less than (maxvalue)
    (
        subpartition p3_sub1 values ('0'),
        subpartition p3_sub2 values ('1'),
        subpartition p3_sub3 values ('2'),
        subpartition p3_sub4 values (default)
    )
);

对于分区：
在现有分区的基础上，可以新增一个分区，也可以删除一个现有的分区；
如果现在只有一个分区了，那么这个分区不允许被删除;

可以针对表的全局（所有分区）创建索引，此时的索引针对所有分区都有效；
也可以只针对某一个分区来创建索引，此时索引只针对这一个分区有效，其他分区里面，该所以无效；这种情况下，如果删除该分区下的索引，其他分区不受影响；