SQL语句优化学习

跳跃索引

1)场景引入：

表t1有一个联合索引idx_u1(rank1,rank2), 但是查询的时候却没有rank1这列，只有rank2。
比如，select * from t1 where rank2 = 30.那以前遇到这样的情况，如果没有针对rank2这列单独建立普通索引，这条SQL怎么着都是走的FULL TABLE SCAN。ISS（跳跃索引）就是在这样的场景下产生的。ISS 可以在查询过滤组合索引不包括最左列的情况下，走索引扫描，而不必要单独建立额外的索引。因为毕竟额外的索引对写开销很大，能省则省。

2）跳跃索引其实就是 MySQL 8.0 推出的适合联合索引左边列唯一值较少的情况的一种优化策略

3）参考文章: https://www.cnblogs.com/lynn919/p/10875121.html.

使用绑定变量

1}认识绑定变量：高频使用的SQL语句，使用绑定变量可以编译次数，即使用绑定变量可以减少数据库解析SQL语句的次数，使其只需编译一次。

2）使用：

未绑定变量的语句:
select name,age from ccc where applyNo=#{applyNo};
使用绑定变量：

 @SelectProvider(type = DrawApplyMapperSql.class(类名），method="selcetLoanList")
 public List<DrawApplyDTO> selectLoanList(@param("applyNo")String applyNo);
 class DrawMapperSql{
 	public String selectLoanList(@param("applyNo")String applyNo){//方法名保持一致
 		return new SQL({
 		SELECT("name ");
 		SELECT("age");
 		FROM("ph_cf_draw_apply_info");
 		WHERE("apply_no = #{applyNo}");
 		WHERE("loan_status = '"+LoanStatusEnum.SUCCESS.getValue+"'");//增加查询状态
 		}).toString

避免隐式转换

链接: SQL的隐式转换文章.

update text set age=to_number('19')   //显式转换

union all的使用

union all的效率要高于union，因为union all不会将两张表格相同的数据去重，而union会将两张表格相同的数据去重。
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

with as优化子查询

1.with as的用法介绍

普通的嵌套查询

select * from person.StateProvince where CountryRegionCode in 
(select CountryRegionCode from person.CountryRegion where Name like 'C%')

使用with as优化子查询

with
cr as
(
    select CountryRegionCode from person.CountryRegion where Name like 'C%'
)


select * from person.StateProvince where CountryRegionCode in (select * from cr)

with as的使用解释

oracle优化之index full scan

  SELECT * FROM T_EMPLOYEE WHERE MIDDLE_NAME IS NULL;

当MIDDLE_NAME字段可以为NULL，且具有非唯一索引，ORACLE在执行此语句时，
可能会采用INDEX FULL SCAN操作。
索引全扫描规则

oracle优化小结

Oracle数据的扫描种类、术语

像优化器一样的思考

重点考虑如下三点：
①表上的数据怎么获取，即访问路径
②表之间的连接类型是什么（Join Method）,哪个表做驱动表
③如果是走索引，哪个索引是最优的（selectivity）

1.Selectivity(可选择性、选择率)

该列的“唯一键的数量/表中的行数”的比值，越接近1，则该列的可选择行越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据越较少，比较适合使用索引查询。

2.Cardinality(基数)

基数（Cardinality）是指数据库表中特定列中不重复的数值。基数越低，重复性高

3.索引的聚簇因子

主要反映索引块上的数据（顺序存储），与该索引基于的表块上的数据（无序存储）的顺序相似程度的差异性。即表数据的存储顺序是否与相应索引数据的存储顺序一致。
越接近表块数越好，越接近表行数越差

4.数据库中的统计信息查询SQL

---表：行数、块数、行平均度
select * from DBA_TAB_STATISTICS t where t.table_name = upper('表名')；

---列：列中distinct值个数、null值的数量、数据分布
select * from DBA_TAB_COL_STATISTICS t where t.table_name = upper('表名')

---索引：叶块数量、层级、聚簇因子
select * from DBA_IND_STATISTICS t from t.table_name = upper('表名')

5.oracle的执行顺序

1.parse

将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。

2.execute

真正的由oracle来执行语句。对于insert，update和delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。oracle首先确定所需要的数据在buffer cache中是否存在。如果数据在buffer cache中不存在，oracle在buffer cache中寻找足够的空间来加载所需要的数据。同时，oracle也会将我们对数据修改的操作以及移动前后的数据写入redo 两个buffer和undo segment.

3.Fetch

返回查询语句中所获得的记录，这步只有select语句会执行

6.HINT篇（oracle中改变其执行计划）

读取顺序：按照从左至右，从上至下的方法，了解执行计划的执行步骤。执行计划按照层次逐步缩进，从左至右看，缩进最多的那一步，最先执行，如果缩进量相同，则按照从上而下的方法判断执行顺序，可粗略认为上面的步骤优先执行。即上面的是驱动表，下面是被驱动表。每一个执行步骤优先执行。每一个执行步骤都有对应的COST，可从单步COST的高低，以及单步的估计结果集（对应ROWS/基数），来分析表的访问方式，连接顺序以及连接方式是否合理。

merge /*+ full(l) */ 
into
t_basedata_ln l using (
select /*+ parallel(4) full(trx)*/
sum (trx.trx_amt) putoutAmt ,acct_no from 
cfs_trx trx where trx.procut_mode = 'PL_WS'  and trx.trx_type = 'DB' and trx.is_effective = 'Y'
group by acct_no ) p 
on (l.account = p.acct_no and l.report_date = to_char(P_date,'yyyyMMdd') and 
l.credit_limit > p.putoutAmt and 
l.remark = 'JA_ILOAN_ADD')
when matched then 
update 
set l.shared_credit_limit = round(p.putoutAmt),l.date_updated sysdate;

使用leading，use_nl进行调优
1./*+leading (table) / 将指定作为连接次序中的首表，小表作为连接表的首表,table作为驱动表
2./+use_nl(table) */ 将指定表与嵌套的连接的行源进行连接，并把指定表作为内部表（被驱动表）
外部表又叫驱动表
3.use_hash(t1,t2) 表示对表t1和t2关联时采用hash连接
/*+leading (table) */使用介绍

7.oracle中硬解析和软解析

软解析与硬解析

8.oracle的扫描方式介绍

①全表扫描（Full Table Scan）
多块读（db file scattered read/direct path read(11g)）
ROWID(Table Access by ROWID)

②索引扫描
单块读（db file sequential read）
oracle索引扫描可以由2步组成：
1、扫描索引得到对应的ROWID值
2、通过找到的ROWID从表中读出具体的数据
每步都是单独的一次IO，但是对于索引，由于经常使用，绝大多数都已经cache到内存中，所以第1步的IO经常是逻辑IO，即数据中可以从内存中得到。第2步来说，如果表比较大，则其数据不可能全在内存中，很有可能是物理IO。

索引扫描
根据索引的类型与where限制条件的不同，主要是4种类型的Oracle索引扫描
1、索引唯一扫描（index unique scan）
通过唯一索引查找一个数值经常返回单个ROWID。如果该索引有多个列组成（即组合索引），则至少要有组合索引的引导列参与到该查询中。如果存在UNIQUE或PRIMARY KEY约束的话，Oracle经常实现唯一性扫描。
2、索引范围扫描（index range scan）
使用一个索引存取多行数据，在where限制条件中使用范围操作（例如>,<<>,>=,<=和between）。如果该索引有多个列组成（即组合索引），则至少要有组合索引的引导列参与到该查询中。

3、索引全扫描（index full scan）
全索引扫描只在CBO模式下才有效。CBO根据统计数值得知进行索引全扫描比进行全表扫描更有效时，才进行索引全扫描，而且此时查询出的数据都必须从索引中可以直接得到。什么时候索引全扫描比进行全表扫描更有效？

4、索引快速扫描（index fast full scan）
扫描索引中的所有的数据块，与index full scan 很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以顺序被返回。在这种存取方法中，可以使用对块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。索引快速扫描可用做读取多列索引的第2列。
问题，什么时候会出现索引跳跃扫描（index skip scan）

BitMap（）索引缺点
行锁范围放大，insert，update(Bitmap字段)，Delete都会导致锁争用，不适用于频繁DML的表，不适用并发场景。

9.复合索引

要把等值查询作为引导列。注意（in也是等值查询的一种）
ly_type是引导列

select count(1) from ly_test_01 where ly_type in ('A','B','D') and lcd between to_date('20200718','yyyyMMdd') and to_date('20200719','yyyyMMdd')

在DML和查询性能之间平衡，DML量大的表上控制索引的个数。尽量避免将频繁修改的字段作为索引。