基本原则就是能通过索引字段进行查询的，就通过索引字段进行查询。
索引查询基本都是精确查询，在使用过程中应避免使用Like、in、ont in、EXISTS、DisTinct等关键词，在此关于索引查询不作太多说明，大家只需把下面的非索引查询搞明白就可以了。
1.2、非索引查询：
上文中提高：要尽可能的使用索引字段进行查询，那么，使用索引字段进行的查询都能称之为索引查询吗？
答案是否定的，譬如：主键字段A是通过GUID生成的，A作为主键可能是索引字段，但是如果你针对A进行Like、in、not in 等操作就会造成非索引查询。当然：一般没有人针对GUID进行LIKE查询，因此：此处举的例子不怎么恰当，勿怪。
那么在什么情况下会执行非索引查询呢？我们应当避免使用哪些关键词呢？如下：
1、DisTinct 关键词：使用此关键字会造成全表比对，慎用！
2、Like 关键词：LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用全范围比对查找。
3、in 关键词和 not in 关键词：用IN和NOT IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：
ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。
话说另一个关键词：Exists的执行效率要比in高出很多，因此：在这儿建议大家如果能用Exists解决问题时，就尝试用Exists去解决问题吧！
4、IS NULL关键词和IS NOT NULL关键词：索引是不会索引空值的，因此会全局非索引查询。性能低下。
5、UNION关键词：UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：
select * from gc_dfys
union
select * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。
推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。
select * from gc_dfys
union all
select * from ls_jg_dfys
6、>= 及 <= 及 != 关键词：>= 和 <=作用于索引字段时会采取索引查询，但是如果作用于非索引字段，就会全表比对查询，因此，要根据情况而定。
7、Order by group by 关键词：order by group by 谈不上属于非索引关键词的范畴，但是使用order by group by 时，排序/分组的字段要使用索引字段，否则效率大大降低
以上是非索引查询涉及到的关键词，请大家慎重使用；
8、慎重 or 关键字，即使用了，也应该结合索引字段进行查询
2）SQL的书写规则：
1、同一功能同一性能不同写法SQL的影响。
如一个SQL在A程序员写的为 Select * from zl_yhjbqk
B程序员写的为 Select * from dlyx.zl_yhjbqk（带表所有者的前缀）
C程序员写的为 Select * from DLYX.ZLYHJBQK（大写表名）
D程序员写的为 Select * from DLYX.ZLYHJBQK（中间多了空格）
以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同，则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。
2、WHERE后面的条件顺序影响
SQL1、select * from A where A.Name like '%陈%'
SQL2、select * from A where A.Id>30 and A.id<=50 and A.Name like '%陈%'
以上两个SQL中，第一个会全局查找，第二个会先执行索引查询(将范围锁定为20条记录)，然后在执行非索引查询。因此：SQL1的性能会大大低于SQL2的性能。
3、SQL的执行顺序是先执行子查询，在逐步执行外层的查询，因此：在书写SQL的过程中，应尽可能的针对子查询进行精确查询且应尽可能的缩小子查询的结果集、
4、如果您仅仅只需要查询三个字段，请勿查询所有字段，譬如：select A.Id ,A.Name ,A.Sex from A 和 Select * from A 执行效率绝对是不一样滴。
5、统计查询时：譬如：select Count(1) from A 和 Select Count(*) from A 执行效率绝对是不一样滴。Count(1)的效率要远远大于Count(*)
6、能使用视图的话，就尽可能使用视图，如果你将A，B，C三张表进行连接查询，那么A，B，C三张表将会进行一个笛卡尔积连接，这个过程会大大增加系统的开销，因此：连接查询效率也不会很高。如果做成了视图，就相当于数据库事先做好了三张表的笛卡尔积，这样就省去了这一步骤，因此效率会增加！
7、针对时间字段：如果数据库中时间字段定义成varchar或nvarchar类型，请尽可能的进行数据类型转换，然后使用between and 进行查询，因为between and 执行的是索引查询。
8、批量插入数据时应尽可能的一次执行而非一次一次执行：例如： 插入一千条数据：
程序员A：
for(int i=0;i<1000;i++){
insert into A values(....)
连接数据库，执行插入操作，单独执行1000次插入
}
程序员B：
StringBuilder sb = new StringBuilder();
for(int i=0;i<1000;i++){
sb.Append("insert into A values(....);");
}
连接数据库，执行插入操作(将所有的插入语句作为一个整体)
同样是插入一千条数据，程序员B的效率要大大高于程序员A
linq分页：
int PageSize = 10;//页容量10，每页十条数据 int PageIndex = 1;//假设页码是从1开始的 //根据分析得知：我们现在要取第4页的数据，所以PageIndex=4，作如下赋值 PageIndex = 4; Lst = Lst.Skip((PageIndex-1)*PageSize).Take(PageSize).ToList();
#region Linq 分页 /// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <param name="Source"></param> /// <param name="PageSize">页容量</param> /// <param name="CurrentPageIndex">页码索引 0 代表 第一页</param> /// <returns></returns> public static IQueryable<T> Pagination<T>(IOrderedQueryable<T> Source, int PageSize, int CurrentPageIndex) { return Source.Skip(CurrentPageIndex * PageSize).Take(PageSize); } #endregion
查看sql索引，使用解释函数explain，只需添加在sql语句之前即可：
我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引：
type结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。
possible_keys：sql所用到的索引
key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
rows: 显示MySQL认为它执行查询时必须检查的行数。
用hints语句指定当前sql走哪条索引：
SELECT /*+ index(tablename IDX_NO) */ no, timeFROM tablenamewhere no = :1and time >= to_date(:2, 'yyyy-mm-dd')and time < to_date(:3, 'yyyy-mm-dd')ORDER BY time DESC
我们可以使用注释(comment)来为一个语句添加Hints，一个语句块只能有一个注释，而且注释只能放在SELECT, UPDATE, or DELETE关键字的后面
使用Oracle Hints的语法：
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...
注解：
1) DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。
2) “+”号表示该注释是一个Hints，该加号必须立即跟在”/*”的后面，中间不能有空格。
3) hint是下面介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。
4) text 是其它说明hint的注释性文本
5)使用表别名。如果在查询中指定了表别名，那么提示必须也使用表别名。例如：select /*+ index(e,dept_idx) */ * from emp e;
6)不要在提示中使用模式名称：如果在提示中指定了模式的所有者，那么提示将被忽略。
Hint的具体用法
和优化器相关的hint
1、/*+ ALL_ROWS */
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
SELECT /*+ ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
2、/*+ FIRST_ROWS(n) */
表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
SELECT /*+FIRST_ROWS(20) */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
3、/*+ RULE*/
表明对语句块选择基于规则的优化方法.
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
附录hint表格
| Hints for Optimization Approaches and Goals | |
| ALL_ROWS | The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption). |
| FIRST_ROWS | The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row). In newer Oracle version you should give a parameter with this hint: FIRST_ROWS(n) means that the optimizer will determine an executionplan to give a fast response for returning the first n rows. |
| CHOOSE | The CHOOSE hint causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement |
| RULE | The RULE hint explicitly chooses rule-based optimization for a statement block. This hint also causes the optimizer to ignore any other hints specified for the statement block. The RULE hint does not work any more in Oracle 10g. |
| Hints for Access Paths | |
| FULL | The FULL hint explicitly chooses a full table scan for the specified table. The syntax of the FULL hint is FULL(table) where table specifies the alias of the table (or table name if alias does not exist) on which the full table scan is to be performed. |
| ROWID | The ROWID hint explicitly chooses a table scan by ROWID for the specified table. The syntax of the ROWID hint is ROWID(table) where table specifies the name or alias of the table on which the table access by ROWID is to be performed. (This hint depricated in Oracle 10g) |
| CLUSTER | The CLUSTER hint explicitly chooses a cluster scan to access the specified table. The syntax of the CLUSTER hint is CLUSTER(table) where table specifies the name or alias of the table to be accessed by a cluster scan. |
| HASH | The HASH hint explicitly chooses a hash scan to access the specified table. The syntax of the HASH hint is HASH(table) where table specifies the name or alias of the table to be accessed by a hash scan. |
| HASH_AJ | The HASH_AJ hint transforms a NOT IN subquery into a hash anti-join to access the specified table. The syntax of the HASH_AJ hint is HASH_AJ(table) where table specifies the name or alias of the table to be accessed.(depricated in Oracle 10g) |
| INDEX | The INDEX hint explicitly chooses an index scan for the specified table. The syntax of the INDEX hint is INDEX(table index) where:table specifies the name or alias of the table associated with the index to be scanned and index specifies an index on which an index scan is to be performed. This hint may optionally specify one or more indexes: |
| NO_INDEX | The NO_INDEX hint explicitly disallows a set of indexes for the specified table. The syntax of the NO_INDEX hint is NO_INDEX(table index) |
| INDEX_ASC | The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values. |
| INDEX_COMBINE | If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer will use on the table whatever boolean combination of bitmap indexes has the best cost estimate. If certain indexes are given as arguments, the optimizer will try to use some boolean combination of those particular bitmap indexes. The syntax of INDEX_COMBINE is INDEX_COMBINE(table index). |
| INDEX_JOIN | Explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query. |
| INDEX_DESC | The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values. |
| INDEX_FFS | This hint causes a fast full index scan to be performed rather than a full table. |
| NO_INDEX_FFS | Do not use fast full index scan (from Oracle 10g) |
| INDEX_SS | Exclude range scan from query plan (from Oracle 10g) |
| INDEX_SS_ASC | Exclude range scan from query plan (from Oracle 10g) |
| INDEX_SS_DESC | Exclude range scan from query plan (from Oracle 10g) |
| NO_INDEX_SS | The NO_INDEX_SS hint causes the optimizer to exclude a skip scan of the specified indexes on the specified table. (from Oracle 10g) |
| Hints for Query Transformations | |
| NO_QUERY_TRANSFORMATION | Prevents the optimizer performing query transformations. (from Oracle 10g) |
| USE_CONCAT | The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Normally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them. |
| NO_EXPAND | The NO_EXPAND hint prevents the optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it. |
| REWRITE | The REWRITE hint forces the optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost. |
| NOREWRITE / NO_REWRITE | In Oracle 10g renamed to NO_REWRITE. The NOREWRITE/NO_REWRITE hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED. |
| MERGE | The MERGE hint lets you merge views in a query. |
| NO_MERGE | The NO_MERGE hint causes Oracle not to merge mergeable views. This hint is most often used to reduce the number of possible permutations for a query and make optimization faster. |
| FACT | The FACT hint indicated that the table should be considered as a fact table. This is used in the context of the star transformation. |
| NO_FACT | The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table. |
| STAR_TRANSFORMATION | The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a query optimization decision to use the best plan generated without the transformation, instead of the best plan for the transformed query. |
| NO_STAR_TRANSFORMATION | Do not use star transformation (from Oracle 10g) |
| UNNEST | The UNNEST hint specifies subquery unnesting. |
| NO_UNNEST | Use of the NO_UNNEST hint turns off unnesting for specific subquery blocks. |
| Hints for Join Orders | |
| LEADING | Give this hint to indicate the leading table in a join. This will indicate only 1 table. If you want to specify the whole order of tables, you can use the ORDERED hint. Syntax: LEADING(table) |
| ORDERED | The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause. If you omit the ORDERED hint from a SQL statement performing a join , the optimizer chooses the order in which to join the tables. You may want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information would allow you to choose an inner and outer table better than the optimizer could. |
| Hints for Join Operations | |
| USE_NL | The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table. The syntax of the USE_NL hint is USE_NL(table table) where table is the name or alias of a table to be used as the inner table of a nested loops join. |
| NO_USE_NL | Do not use nested loop (from Oracle 10g) |
| USE_NL_WITH_INDEX | Specifies a nested loops join. (from Oracle 10g) |
| USE_MERGE | The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join. The syntax of the USE_MERGE hint is USE_MERGE(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a sort-merge join. |
| NO_USE_MERGE | Do not use merge (from Oracle 10g) |
| USE_HASH | The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join. The syntax of the USE_HASH hint is USE_HASH(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join. |
| NO_USE_HASH | Do not use hash (from Oracle 10g) |
| Hints for Parallel Execution | |
| PARALLEL | The PARALLEL hint allows you to specify the desired number of concurrent query servers that can be used for the query. The syntax is PARALLEL(table number number). The PARALLEL hint must use the table alias if an alias is specified in the query. The PARALLEL hint can then take two values separated by commas after the table name. The first value specifies the degree of parallelism for the given table, the second value specifies how the table is to be split among the instances of a parallel server. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters (described in a later section) to determine the default degree of parallelism. |
| NOPARALLEL / NO_PARALLEL | The NOPARALLEL hint allows you to disable parallel scanning of a table, even if the table was created with a PARALLEL clause. In Oracle 10g this hint was renamed to NO_PARALLEL. |
| PQ_DISTRIBUTE | The PQ_DISTRIBUTE hint improves the performance of parallel join operations. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make. |
| NO_PARALLEL_INDEX | The NO_PARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation. |
| Additional Hints | |
| APPEND | When the APPEND hint is used with the INSERT statement, data is appended to the table. Existing free space in the block is not used. If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo. |
| NOAPPEND | Overrides the append mode. |
| CACHE | The CACHE hint specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. In the following example, the CACHE hint overrides the table default caching specification. |
| NOCACHE | The NOCACHE hint specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache. |
| PUSH_PRED | The PUSH_PRED hint forces pushing of a join predicate into the view. |
| NO_PUSH_PRED | The NO_PUSH_PRED hint prevents pushing of a join predicate into the view. |
| PUSH_SUBQ | The PUSH_SUBQ hint causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan. |
| NO_PUSH_SUBQ | The NO_PUSH_SUBQ hint causes non-merged subqueries to be evaluated as the last step in the execution plan. |
| QB_NAME | Specifies a name for a query block. (from Oracle 10g) |
| CURSOR_SHARING_EXACT | Oracle can replace literals in SQL statements with bind variables, if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior to be switched off. In other words, Oracle executes the SQL statement without any attempt to replace literals by bind variables. |
| DRIVING_SITE | The DRIVING_SITE hint forces query execution to be done for the table at a different site than that selected by Oracle |
| DYNAMIC_SAMPLING | The DYNAMIC_SAMPLING hint lets you control dynamic sampling to improve server performance by determining more accurate predicate selectivity and statistics for tables and indexes. You can set the value of DYNAMIC_SAMPLING to a value from 0 to 10. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify a table. |
| SPREAD_MIN_ANALYSIS | This hint omits some of the compile time optimizations of the rules, mainly detailed dependency graph analysis, on spreadsheets. Some optimizations such as creating filters to selectively populate spreadsheet access structures and limited rule pruning are still used. (from Oracle 10g) |
| Hints with unknown status | |
| MERGE_AJ | The MERGE_AJ hint transforms a NOT IN subquery into a merge anti-join to access the specified table. The syntax of the MERGE_AJ hint is MERGE_AJ(table) where table specifies the name or alias of the table to be accessed.(depricated in Oracle 10g) |
| AND_EQUAL | The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. The syntax of the AND_EQUAL hint is AND_EQUAL(table index index) where table specifies the name or alias of the table associated with the indexes to be merged. and index specifies an index on which an index scan is to be performed. You must specify at least two indexes. You cannot specify more than five. (depricated in Oracle 10g) |
| STAR | The STAR hint forces the large table to be joined last using a nested loops join on the index. The optimizer will consider different permutations of the small tables. (depricated in Oracle 10g) |
| BITMAP | Usage: BITMAP(table_name index_name) Uses a bitmap index to access the table. (depricated ?) |
| HASH_SJ | Use a Hash Anti-Join to evaluate a NOT IN sub-query. Use this hint in the sub-query, not in the main query. Use this when your high volume NOT IN sub-query is using a FILTER or NESTED LOOPS join. Try MERGE_AJ if HASH_AJ refuses to work.(depricated in Oracle 10g) |
| NL_SJ | Use a Nested Loop in a sub-query. (depricated in Oracle 10g) |
| NL_AJ | Use an anti-join in a sub-query. (depricated in Oracle 10g) |
| ORDERED_PREDICATES | (depricated in Oracle 10g) |
| EXPAND_GSET_TO_UNION | (depricated in Oracle 10g) |
扫一扫
940
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
