sql优化经典讲解.ppt

SQL优化基础,索引,索引的类型 B*树索引 反向码索引 降序索引 位图索引 基于函数的索引,B*树索引,基于二叉树的,由分支块和叶子块组成. 包括每个被索引列的值和行所对应的ROWID Create index indexname on tabename(columnname),反向索引,B*索引的一个分支 反转索引码中每列的字节 便于并发 Create index indexname on tablename(column) reverse,ORACLE的内部表示,SQL select dump(1,16) from dual 2 union all select dump(2,16) from dual 3 union all select dump(3,16) from dual 4 / DUMP(1,16) - Typ=2 Len=2: c1,2 Typ=2 Len=2: c1,3 Typ=2 Len=2: c1,4 已用时间: 00: 00: 00.00,反向的情况,SQL select dump(reverse(1),16) from dual 2 union all select dump(reverse(2),16) from dual 3 union all select dump(reverse(3),16) from dual; DUMP(REVERSE(1),1 - Typ=2 Len=2: 2,c1 Typ=2 Len=2: 3,c1 Typ=2 Len=2: 4,c1 已用时间: 00: 00: 00.00 SQL,降序索引,索引中的储存方式由升序变成降序 SQL select * from test t where id between 1 and 100 order by id desc,name asc; 已选择100行。 已用时间: 00: 00: 00.01 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 TABLE ACCESS (BY INDEX ROWID) OF TEST 3 2 INDEX (RANGE SCAN) OF PK_ID (UNIQUE),SQL create index i_desc on test(id desc,name asc); 索引已创建。 已用时间: 00: 00: 00.01 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF TEST 2 1 INDEX (RANGE SCAN) OF PK_ID (UNIQUE),位图索引,用于低cardinality列(即列的唯一值除以行数为一个很小的值,接近零) 如”性别” 不适用于并行 只能在CBO下 Create bitmap index indexname on tablename(column),SQL select * from testbitmap where sex=男; 已选择5000行。 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF TESTBITMAP,位图索引的特点,比B*tree索引更节省空间 建立速度快 不能走RULE 可存储NULL值,SQL create index bit_inx_sex on testbitmap(sex); 索引已创建。 SQL select * from testbitmap where sex=男; 已选择5000行。 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTBITMAP 2 1 INDEX (RANGE SCAN) OF BIT_INX_SEX (NON-UNIQUE),函数索引,必须拥有QUERY_REWRITE权限 必须使用基于成本的优化器 必须设置以下两个参数: query_rewrite_enabled=true query_rewrite_integrity=trusted Create index indexname on tabename(fun(column),SQL create index l_fun on test(upper(name); 分析表略去 SQL select * from test where upper(name)=1TEST; ID NAME - - 1 1test 已用时间: 00: 00: 00.04 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=11) 1 0 TABLE ACCESS (BY INDEX ROWID) OF TEST (Cost=2 Card=1 Bytes=11) 2 1 INDEX (RANGE SCAN) OF L_FUN (NON-UNIQUE) (Cost=1 Card=1),各种索引使用场合,B*tree 多用于oltp系统.cardinlity高的情况下 反向索引 OPS环境下 降序索引 带排序操作,位图索引 适用于低cardinality列.适合集中读取,不适合插入和修改,原因在于任何需要更新同一个位图索引条目的修改都将锁定整个位图，严重抑制了并发性,未用到索引的原因,类型不匹配 对列使用了函数，而索引只是基于列的。 使用索引实际会降低速度。 很长时间没有分析表了，表的增长较快，这样CBO会作出错误的判断。 结果集返回的比例过大 Query_rewrite_enabled未设为true而导致函数索引不可用,索引的管理,DROP过多的索引 监控索引 alter index monitoring usage; 注:9205以下有BUG. 重建索引 alter index IDX rebuild online 迁移索引 alter index rebuild tablespace ,常见的一些问题,使用NULL条件查询 方法UPDATE为N/A,然后查:如 id=N/A Like %a%: 使用firstrows提示,Like %DURE 如:where object_type like %DURE create index inxreserve on user_test(reverse(object_type) 反向索引创建后改写为: where reverse(object_type) like ERUD%,理解SQL工具,执行计划,ORACLE_HOME/rdbms/admin/utlxplan.sql Create public synonym plan_table for sys.plan_table;,查看执行计划的几种方式,1.explain plan for select * . select * from table(dbms_xplan.display); 2.Explain plan set statement_id=id into plan_table for sql statement 3.sqlplus: set autotrace on; 4.sql_trace and tkprof,Trace文件解释,Count Cpu Elapsed Disk Query Current Rows,阅读执行计划,首先读取缩进最深的语句 如果两上语句的缩进程度相同,则上面的先,Oracle优化器,Oracle优化器的类型,Cost Based Optimizer(简称CBO) 基于统计信息的优化 Rule Based Optimizer(简称RBO) 基于数据字典的优化，在以后的版本中将不支持,RBO,根据数据字典 查询有无可用的索引，如果有则使用，否则不使用 不同的访问方法有预定好的优先级，选择优先级高的执行方法,RBO中访问数据,Rowid - Cluster Join(SR) - Hash Cluster Key (SR) - UK或PK(SR) - Cluster Join - Hash Cluster Key - Index Cluster Key - Composite Index - Single Column Index - Bound Range Index Scan - Unbound Range Index Scan - Sort Merge Join - Max or Min on Indexed Column - Order on Indexed Column - Full Table Scan,RBO的特点,总是使用索引 总是使用驱动表 只有在不可避免的避免的情况才使用全表扫描 任何索引都可以(但并非很好),例子,SQL select count(*) from user_test; COUNT(*) - 30904 已用时间: 00: 00: 00.01 SQL create index id_owner on user_test(owner); 索引已创建。 已用时间: 00: 00: 00.09,SQL select * from user_test where owner=SYS; 已选择13931行。 已用时间: 00: 00: 00.07 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF USER_TEST 2 1 INDEX (RANGE SCAN) OF ID_OWNER (NON-UNIQUE) Statistics - 0 recursive calls 0 db block gets 2060 consistent gets 29 physical reads 0 redo size 995554 bytes sent via SQL*Net to client 10711 bytes received via SQL*Net from client 930 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk),SQL drop index id_owner; 索引已丢弃。 已用时间: 00: 00: 00.00 SQL select * from user_test where owner=SYS 2 / 已选择13931行。 已用时间: 00: 00: 00.07 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF USER_TEST,CBO,需要收集统计信息 表有多少行，占用多少数据块 列有多少个Null值、不同值 列的最大值和最小值，及值的分布情况 索引的层次、结点数、叶结点数，及行的分布状况(Cluster) 根据一定算法算出一个成本值，选择成本值最低的执行方法，不一定使用索引。,CBO的新功能,Partition Table 和 Partition Index Index Organized Table Reserve Key Index Function Index Bitmap Index 和 Bitmap Join Index Index Skip Scan Parallel Query 和 Parallel DML Hash Join 基于实体化视图的Query Rewrite,使用CBO,新的应用应当基于CBO开发 用Analyze dbms_stats收集信息 在语句中用hint指定 rule choose first_rows all_rows 在session中用alter session指定optimizer_mode参数 在参数文件中指定optimizer_mode参数,CBO几个概念,Selective 结果记录的比例 Histograms 列值的分布情况 Cardinality 结果记录数 Cost 执行成本,CBO : Selective,指结果记录的比例 用于决定使用索引访问还是使用全表扫描 例子： 表T的COL1字段上有10个不同值，当执行select * from T where col1=?时，这个查询的selective=1/10 * 100=10%,CBO : Histograms,用于描述列值的分布情况 在分布不平均的例上，需要用Histograms来记录列值的分布情况 例子: 在表T的COL1字段上，有100个值，它分的分布如下 10 20 5 5 40 10 10,CBO : Cardinality,CBO对查询将返回的记录数的一个估计，在最简单的情况下： Cardinality = Table rows * Selective,CBO : Cost,CBO根据所收集的统计信息或猜测信息给某一个SQL语句算出的执行成本 具体计算方法很复杂 没有统计信息或过时的统计信息，会导至CBO产生和使用错误的执行方法 CBO还不是十分完善，可以使用Hint影响CBO选择执行方法,全表扫描和索引扫描,索引扫描不一定是最快的(如小表) CBO中经常会选择全表扫描(如小表) 当Selective 15%时，选择索引扫描比较有利，否则使用全表扫描更好,CBO中的访问方法,Full Table Scans Rowid Scans Index Scans Cluster Scans Hash Scans Sample Table Scans,CBO中Index访问,Index Unique Scan Index Range Scan Descending Index Skip Scan Full Scan Fast Full Index Scan Index Join Bitmap Join,CBO中的JOIN方法,NEST LOOP MERGE JOIN HASH JOIN,几个参数对CBO的影响,OPTIMIZER_INDEX_CACHING 这个初始化参数代表一个百分比,取值范围在0到99之间. 缺省值是0，代表当CBO使用索引访问数据时，在内存中发现数据的比率是0%，这意味着通过索引访问数据将需要产生物理读取，代价昂贵。如果使用缺省设置，Oracle评估成本的时候，很多时候就会错误的选择全表扫描。,OPTIMIZER_INDEX_COST_ADJ 这个初始化参数代表一个百分比,取值范围在1到10000之间. 该参数表示索引扫描和全表扫描成本的比较。缺省值100表示索引扫描成本等于全表扫描。,收集统计信息,定时收集 在数据大量变更后收集 使用Analyze命信收集或dbms_stats包收集 在optimizer_mode=choose时，收集信息后会采用CBO进行优化，在dblink应用较多时，需要注意。,一个例子,SQL SELECT * FROM USER_TEST WHERE OBJECT_TYPE=PROCEDURE; 已选择24160行。 已用时间: 00: 00: 46.00 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=59 Card=9157 Bytes=8 51601) 1 0 TABLE ACCESS (BY INDEX ROWID) OF USER_TEST (Cost=59 Card =9157 Bytes=851601) 2 1 INDEX (SKIP SCAN) OF IDX (NON-UNIQUE) (Cost=29 Card=91 57),Statistics - 0 recursive calls 0 db block gets 42204 consistent gets 8659 physical reads 120 redo size 8034786 bytes sent via SQL*Net to client 91550 bytes received via SQL*Net from client 8279 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 124160 rows processed,对表和索引作分析,SQL exec dbms_stats.gather_table_stats(TEST,USER_TEST); PL/SQL 过程已成功完成。 已用时间: 00: 01: 18.05 SQL EXEC dbms_stats.gather_index_stats(TEST,IDX); PL/SQL 过程已成功完成。 已用时间: 00: 00: 46.07,SQL SELECT * FROM USER_TEST WHERE OBJECT_TYPE=PROCEDURE; 已选择3008行。 已用时间: 00: 00: 00.06 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=75553 Bytes= 6950876) 1 0 TABLE ACCESS (BY INDEX ROWID) OF USER_TEST (Cost=31 Card =75553 Bytes=6950876) 2 1 INDEX (RANGE SCAN) OF IDX (NON-UNIQUE) (Cost=1 Card=75 553),注意事项,建议使用DBMS_STATS 包来代替ANALYZE 语句收集统计信息。 DBMS_STATS 包可以并行的收集统计信息，可以为分区对象收集全局统计信息，以及使用 其他方式优化收集操作。 收集和基于成本优化器无关的信息必须用ANALYZE 而不是DBMS_STATS，比如. 使用VALIDATE 或LIST CHAINED ROWS 子句。 收集freelist 块的信息。,DBMS_STATS,Gather_database_stats 收集数据库下所有对象的统计信息 Gather_schema_stats 收集某一用户下所有对象的统计信息 Gather_table_stats 收集某一个表或分区的统计信息 Gather_index_stats 收集某一个索引或分区的统计信息,gather_database_stats,estimate_percent=比例 比例越大，收集越耗时 method_opt=选项 for all indexed | hidden columns size 1 - 254 degree=并行度 默认为表的并行度,gather_schema_stats,ownname=用户名 estimate_percent=比例 比例越大，收集越耗时 method_opt=选项 for all indexed | hidden columns size 1 - 254 degree=并行度 默认为表的并行度,gather_table_stats,ownname=用户名 tabname=表名 partname=分区名 estimate_percent=比例 比例越大，收集越耗时 method_opt=选项 for all indexed | hidden columns size 1 - 254 degree=并行度 默认为表的并行度,gather_index_stats,ownname=用户名 tabname=表名 partname=分区名 estimate_percent=比例 比例越大，收集越耗时 degree=并行度 默认为表的并行度,查看表的统计信息, USER | ALL | DBA _TABLES num_rows / blocks / avg_row_len / last_analyzed,查看索引的统计信息, USER | ALL | DBA _INDEXES NUM_ROWS / DISTINCT / LEAF_BLOCKS / CF / ALFBKEY CF=一个索引叶块对应的数据块的数量，越小表示一索引越有效 ALFBKEY=每一个值占据的叶块的数量,查看列的统计信息, USER | ALL | DBA _tab_col_statistics num_distinct / num_nulls / num_buckets / density num_buckets = histograms中组的数量 Density = 1 / num_distinct,查看histograms的信息,user | all | dba_histograms user | all | dba_part_histograms user | all | dba_subpart_histograms user | all | dba_tab_col_statistics 不适合在分布均匀的列或在where中用bind variable来查询的列 在收集时需要在method_opt中指定size值,CBO使用实列,使用Function Index 选择一个例值分布不平均的表 学会收集统计信息 使用dbms_job来定时收集统计信息 查看统计信息,Optimizer Hints,为什么会有Hints,因为CBO的功能还不十分强大 尽量让优化器选择，除非你很有自信 例子： 在使用dblink的环境中 在使用bind variable时,Hint : 优化器的选择,All_rows First_rows(n) n=1,10,100,1000 Choose Rule,Hint : 选择访问方法,Full(表名) Index(表名 索引) Index_asc(表名 索引) Index_desc(表名 索引) Index_combine(表名 bid1 bid2) Index_join(表名 ind1 ind2) Index_ffs(表名 索引) No_index(表名 索引),Hint