跳跃索引
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)
oracle优化之index full scan
SELECT * FROM T_EMPLOYEE WHERE MIDDLE_NAME IS NULL;
当MIDDLE_NAME字段可以为NULL,且具有非唯一索引,ORACLE在执行此语句时,
可能会采用INDEX FULL SCAN操作。
索引全扫描规则
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量大的表上控制索引的个数。尽量避免将频繁修改的字段作为索引。