SQL优化

1、Oracle体系结构

   

1）、Oracle体系中主要模块功能：

        详细名词解释见：https://blog.csdn.net/m0_37253968/article/details/89224941

        PGA(ProcessGlobal Area)：为每个连接到Oracle数据库的用户进程保留的内存

        SGA(SystemGlobal Area)：OracleInstance的基本组成部分，在实例启动时分配；系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。

        共享池：存储执行计划
        DatabaseBufferCache：存放数据
        Redo Log：记录写的日志的，主要是用来提供数据库的性能和安全性。
   
        user1 ---- sesesion ---- Pga;
        user2 ---- sesesion ---- Pga;
        
        PMON:  负责对所有PGA工作状态的监控和善后处理工作；
        SMON：负责对所有SGA工作状态的监控和善后处理工作；
        DBWR：用来将数据写入到硬盘；
        LGWR：用来将日志数据写入到硬盘；
        CKPT：用来保障数据在写过程中可能出现的不一致情况；

    2）、模块工作过程

        a、select语句

select XX from table_name;

        user -- pga ---shared pool(有执行计划) --看database（有数据） ---直接返回给user;
   
        user -- pga ---shared pool(有执行计划) --看database（没数据） ----后台进程读数据到缓存---直接返回给user;
   
        user -- pga ---shared pool(没有执行计划，生成执行计划) --看database（有数据） ---直接返回给user;
   
        user -- pga ---shared pool(没有执行计划，生成执行计划) --看database（无数据）----后台进程读数据到缓存---直接返回给user;
   
        b、insert 语句

        user -- pga ---shared pool(有执行计划) --看database（没数据） --- 先写Redolog  --- LGWB写日志到硬盘
                                                                                                               写database   ----DBWR写数据到硬盘
                                                                                                               CKPT做日志和数据一致性的检查
                                                                                                                给客户端一个反馈
   
        user -- pga ---shared pool(没有执行计划，生成执行计划) --看database（没数据）--- 先写Redolog  --- LGWB写日志到硬盘
                                                                                                               写database   ----DBWR写数据到硬盘
                                                                                                               CKPT做日志和数据一致性的检查
                                                                                                                给客户端一个反馈
   
   
        user -- pga ---shared pool(有执行计划) --看database（有数据） -----给客户端一个反馈;
   
        user -- pga ---shared pool(没有执行计划，生成执行计划) --看database（有数据）-----给客户端一个反馈;
   

    c、update 语句

        没数据（缓存中没有数据），操作过程类似于select，先把语句读到缓存里面来；
        后半部分操作类型与insert中写部分的操作了。

    d、delete
        类似于insert语句。
        模块工作过程：https://www.cnblogs.com/augus007/articles/7999586.html 
       
    3）、重点

       影响性能的点：
  
       共享池大小：软解析（有执行计划），硬解析（没有执行计划）--->软解析指标越高越好；
       Database：逻辑读（缓存里面有数据）、物理读（缓存里面没有数据，数据从硬盘中读出来） ---->逻辑读对应的性能指标越高越好；
  
        SQL和索引优化：性能测试工程师要掌握；
        表设计：开发或者架构师（基于数据库设计的三范式和反三范式）
        配置优化：数据库配置、操作系统的配置----DBA；
        硬件层面的优化：DBA；
  
2、SQL编写
 
    1）、insert语句优化

insert into table_name()values(1,'a');
insert into table_name()values(2,'b');
insert into table_name()values(3,'c');

     优化1：指定字段名
     优化2：批量插入

 

insert into table_name(id,name) values((1,'a'),(2,'b'),(3,'c'));

    优化3：性能测试或者数据迁移的情况，如果是要插入大量数据，如果有索引，那么我们先把索引去掉，插入数据，最后再恢复索引；
    
    
    2)、select 语句优化

    a、单表优化

        记住SQL语句执行的顺序，在采用复合索引对性能进行优化的时候有用。
        select * 如何优化           ----前提：有索引情况下，某些版本可能会导致失效
        优化1：select 具体字段
        优化2：根据需要，决定要不要加索引

 

select xx from table_name where name like %xxx%;    ----前提：有索引情况下，会导致失效

     优化1：可能xxx%
     优化2：instr来优化 

 

select xx from table_name where id >= 10 and id <=20; 

    优化1：可用between and 来改写SQL语句   
  

select xx from table_name where id >100 limit 1,50;

    优化1：limit优化可用between and 来改写SQL语句,视具体需要看要不要添加索引，索引推荐书籍《品悟性能优化》--罗敏
      

select xx from table_name where name='abc';        ----前提：有索引情况下，会导致失效

    name字段有索引，且这个字段有null
    优化1：SQL语句建表的时候，给默认值；或数据入库的时候尽量避免null值入库，如果还有null值入库，把这个值换掉；
  

select xx  from table_name where id -1 = 5000; -----前提：有索引情况下，会导致失效

    优化1：

select xx  from table_name where id = 5000 +1;
select xx  from table_name where id = 5001;

      PS：索引列上尽量避免有运算，有函数操作。
      -------压测 ---awr 找到问题点 ----- 通过执行计划分析问题-----用前面说的基础内容对数据库进行优化；   

    b、多表优化

     IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况；
     基础：
     真实世界                             SQL转换                            程序角度
     一对一、一对多、多对多         内连接、外连接等                 迭代循环、归并排序、哈希连接（年薪50万会问到的内容）
      使用索引对多表进行优化（涉及到如何建立索引，包括符合索引，到索引部分再说）；
      Ps：group by,order by，limit都是有可能导致性能的问题，如何优化，需要大家先总结、思考、实践。
    
    3）、从开发角度对数据进行优化

    表的设计上：

create table table_name（
    id int，
    ip varchar(50),
    name char(100),
    message BLOB
    );

    优化：

create table table_name（
    id int(11)，
    ip int(11),
    name varchar(100),
    message varchar(512),
    );

    PS：
    能用简单类型的用简单类型，能用小字段的用小段；
    表结构的优化（基于数据的三范式和反三范式）来对表进行拆分和设计；
    从架构角度对数据库进行优化，读写分离、分库分表、集群（分布式数据库）、全局Id如何生成；和Nosql配合使用；（年薪100万会问到的内容）

    select id,name from table_name  where id=10 group by id;
    在id上加索引，是能解决性能问题的；

    在表table_name的id列上创建名为index_name的索引：

    create index index_name on table_name(id);

    select id,name from table_name  where id=10 group by ip
    在ip上加索引，不能解决性能问题的；

    a、性能不能满足要求，看在where子句后面，有没可能添加一个id字段，并添加索引；
    b、性能不能满足要求，哪就把SQL语句改成 select id,name from table_name  where id=10，分组或排序的操作都由前端来完成；  
    c、性能满足要求的，不必优化

    d、order by同group by，它还有一个可选方案，增加系统的内存大小；

 

3、索引

3.1 原理：
没有索引的时候，数据查找走得是全表扫描；
有了索引之后，先从索引找数据，有对应的索引，直接通过索引去查数据；
如果没有索引（或者索引失效），走全表扫描。

2.2 分类：
从数据结构的角度：b-tree，位图，函数，分区，反向
从常规角度：
    B+索引（普通索引）、位图索引、复合索引、唯一索引、主键索引