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
union
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 .

Technology
©2019-2020 Toolsou All rights reserved,
java Comparing attribute values between two objects utilize Python handle Excel data ——xlrd,xlwt library Bidirectional linked list Why? Python Not a future oriented programming language ?Python【 Assignment statement 】 Special lecture , You can't just a=b ah ! Suggest mastering ! utilize Python handle Excel data ——pandas library see SQL-SERVER Data volume and occupied space of database and each table PID The algorithm finally figured out the principle , It was so simple web Two front-end practical games ( Attached source code ) Beginners learn Python Be sure to know what his basic algorithms are ? What is the function ?