Recently, I'm doing work on querying the database , Because the amount of data in a table is too large , Cause the program process to get stuck ,SQL Optimization is imminent , The index came on stage !
Oracle Query index in ：
1, There are no restrictions on the returned rows , That is, No where clause .
2, The row of the data table corresponding to any index primary column is not qualified .
for example ： stay id-name-time Column creates a three column composite index , So only for name Column qualifiers cannot use this index , because name Is not the primary column of the index .
3, There are restrictions on the primary column of the index , However, using the following expression in a conditional expression invalidates the index , Cause token scanning ：
（1）where Clause to perform a function on a field , Expression operation , This will cause the engine to abandon the index and perform a full table scan .
（2） Query field is null Index Invalidation on , Cause full table query .
terms of settlement ：SQL Used in grammar null There will be a lot of trouble , When it is best to index columns not null of ; about is
null, Composite indexes can be established , nvl( field ,0), Table and index analyse after ,is null Index lookup can be re enabled when querying , But the efficiency is not worth affirming ;is not
null The index will never be used . Generally, do not use tables with large amount of data is null query .
（3） Unequal operator is used in query criteria （<>,!=） Limits the index , Cause full table scan .
resolvent ： By changing the not equal operator to or, You can use indexes , Avoid full table scanning .
for example , hold column<>10, Change to column<10 or column>10, You can use the index .
（4） There are restrictions on the primary column of the index , But conditional use like Actions and values to ‘%’ The start or value is an assignment variable .
for example ：where city like '% Dalian %'.
select * from citys where name like '% Dalian ' ( Do not use index )
select * from citys where name like ' Dalian %' ( Use index ) .
terms of settlement ： First, try to avoid fuzzy queries , If necessary , Do not use full fuzzy query , Right fuzzy query should also be used as far as possible , Namely like ‘…%’, The index will be used ; Left blur like
‘%...’ Indexes cannot be used directly , But it can be used reverse + function index Form of , Change into like ‘…%’;
Full fuzzy queries cannot be optimized , If you must use it, it is recommended to use a search engine .
4, or Improper use of the statement will cause a full table scan
reason ：where Two conditions for comparison in Clause , An indexed , One has no index , use or Will cause a full table scan .
for example ：where A=：1 or B=：2,A Index on ,B There is no index on the , Then compare B=：2 The full table scan is restarted when
5, Composite index
When sorting, it shall be sorted according to the order of columns in the composite index , Even if only one column in the index is to be sorted , Otherwise, the sorting performance will be poor .
for example ：
create index skip1 on emp5(job,empno,date); select job,empno from emp5 where
job=’manager’ and empno=’10’ order by job,empno,date desc;
In fact, it's just a match job=’manager’ and empno=’10’ Record the conditions and press date Descending sort , But written order by date
desc Poor performance .
6, Update sentence , If only change 1,2 Fields , No Update All fields , Otherwise, frequent calls will cause significant performance consumption , It also brings a large number of logs .
7, For multiple large data tables JOIN, You have to page first and then JOIN, Otherwise, the logical reading will be very high , Poor performance .
8, select count(*) from table;
This is unconditional count Will cause a full table scan , And it doesn't make any business sense , We must put an end to it .
9, sql of where Condition to bind variables , such as where column= 1, Don't write where
column=‘aaa’, This will cause reanalysis at each execution , waste CPU And memory resources .
10, Do not use in Operator , In this way, the database will perform a full table scan .
11, not in use not in I won't go
Recommended scheme ： use not exists perhaps （ Extraneous junction + Judged to be empty ） To replace
12, > and < Operator （ Greater than or less than operator ）
The greater than or less than operator generally does not need to be adjusted , Because it has an index, it will use index search .
But in some cases, it can be optimized , If a table has 100 Wan record , A numeric field A,30 Million records A=0,30 Million records A=1,39 Million records A=2,1 Million records A=3.
Then execute A>2 And A>=3 The effect is very different , because A>2 Time ORACLE Will find out first 2 The record index of is compared again , and A>=3 Time ORACLE Then find it directly =3 Record index for .
13, UNION Operator
UNION Duplicate records are filtered out after table linking , Therefore, the resulting result set will be sorted after the table is linked , Delete duplicate records and return results .
In most practical applications, duplicate records will not be generated , The most common are process tables and history tables UNION. as ：
select * from gc_dfys
select * from ls_jg_dfys
this SQL Take out the results of the two tables at run time , Then sort in the sorting space to delete duplicate records , Finally, the result set is returned , If the table has a large amount of data, it may cause sorting with disk .
Recommended scheme ： use UNION ALL Operator substitution UNION, because UNION ALL The operation is simply to merge the two results and return .
14, WHERE The following conditions affect the order
Select * from zl_yhjbqk where dy_dj = '1K following ' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1K following '
Above two SQL in dy_dj and xh_bz Neither field is indexed , Therefore, full table scanning is performed ,
Article 1 SQL of dy_dj =
'1KV following ' The ratio of conditions in the recordset is 99%, and xh_bz=1 The ratio is only 0.5%, The first article is in progress SQL When 99% All records are recorded dy_dj and xh_bz Comparison of .
And the second is under way SQL When 0.5% All records are recorded dy_dj and xh_bz Comparison of , This leads to the second article SQL of CPU The occupancy rate is significantly lower than the first article .
15, Effect of query table order
stay FROM The list order in the following table will be correct SQL Execution performance impact , Without index and ORACLE Without statistical analysis of the table ORACLE Links are made in the order in which the table appears ,
Therefore, because the order of the tables is not correct, there will be a data crossover that consumes a lot of server resources .（ notes ： If the table is statistically analyzed ,ORACLE Links to small tables will be automatically advanced , Then link the large table ）
Table connection , The main table with large amount of data is more efficient .
16,Oracle The parser of is processed from right to left from Table name in Clause , therefore from Clause is written in the last table （ Basic class driving table） Will be processed first .
stay from Clause contains more than one table , The table with the least number of records must be selected as the base table , Put last .
Basic table for quick judgment in the following cases （ Drive table ）：
（1）where Try to use indexes in clauses .
（2） The connection operation should return fewer line drives .
（3） If where Contains selective conditions , as where id=1, The most selective part should be placed at the end of the expression .
（4） If only one table has an index , Another table has no index , An indexed table is usually used as the base table .