From the index technology to talk about database query index establishment and query condition writing

The index advantage is of course to improve the retrieval speed, but it does not mean that the index created by the database will actually increase the retrieval speed. Why? We know that the index itself is ordered, and the index search usually involves multiple searches. (Of course, random searches can be performed for indexes implemented using arrays in the memory, however, databases rarely organize data in this way. Generally, database B is used ),

The index advantage is of course to improve the retrieval speed, but it does not mean that the index created by the database will actually increase the retrieval speed. Why? We know that the index itself is ordered, and the index search usually involves multiple searches. (Of course, random searches can be performed for indexes implemented using arrays in the memory, however, databases rarely organize data in this way. Generally, database B is used ),

The index advantage is of course to improve the retrieval speed, but it does not mean that the index created by the database will actually increase the retrieval speed. Why?

We know that the index itself is ordered, and the index search usually involves multiple searches. (Of course, random searches can be performed for indexes implemented using arrays in the memory, however, databases rarely organize data in this way, and generally use B + trees). Therefore, index search is generally not at a constant level. Due to the data size of the index, not once can all index data be loaded into the memory, so it may also cause multiple disk reads, after locating the target index, you also need to read and write specific data block disks at the constant level. Therefore, the disk read and write required for one index positioning can be controlled at the constant level. therefore, the index search speed is at the logarithm level. however, this is not equivalent to the specific query speed during database query. The following is an analysis:

1) index query is enabled only when an indexed field is used as a condition, improving the speed;
2) if the condition of the index field and other non-index fields are in the or relationship, the index is also enabled, but it does not increase the speed, because the retrieval speed depends on the slow condition;
3) if the index field has the highest validity rate (equal condition) in equivalent query, and is faster than or less than the same time range query condition, it depends on the Implementation Technology of the database index, therefore, the database generally does not use the B-tree, which is similar to the B + tree. Because the satellite data of the B + tree is on the leaf node, range reading can be realized, improves the efficiency of range query;
4) for Fuzzy queries, the index is generally not enabled depending on the specific database (Oracle will make some optimization and use the index. For details, refer to the subsequent test data );
5) is null, is not nul and other conditions are the same.

Therefore, the actual application of database indexes should be performed according to actual needs:
1) if a field or several fields are frequently used as separate condition queries, you can create an index;
2) If fuzzy queries are used for common fields, do not create an index;
3) index field conditions and non-index fields are meaningful only when the logical relationship is the same. Otherwise, full table scan is performed;

The following are some conditions for Oracle index validity:
1) indexes are generally enabled, and In and between are also enabled.
2) Like is special. In fact, Oracle will index and locate the first part of the 1st fuzzy match symbol. For details, refer to the test data below;
3) is null, is not null does not enable the index;
4) when the index field is in Or relationship with the constant expression, the constant expression does not affect the result, but the variable and parameterization will scan the entire table;

The following is my test result: the data volume is 0.2 billion, the server is a common PC, the Cards field is an index field, and the batchno field is a non-index field. The result is as follows:

Select count (*) from cards; --> 52 s
Select count (1) from cards; --> 52 s
Select * from cards where cards = '1'; -- does not exist, in milliseconds
Select * from cards where cards = '000000'; -- exists, in milliseconds
Select * from cards where cards> = '000000'; -- millisecond level, but also related to the returned data size. The index is valid.
Select * from cards where cards> = '000000' and cards <= '000000'; -- millisecond level, but also related to the returned data size. The index is valid.
Select * from cards where cards in ('20160301', '20160301'); -- millisecond seconds, the index is valid.
Select * from cards where cards in (select '000000' from dual); -- the index is valid in milliseconds.
Select * from cards where cards in ('20140901'); -- 999906236 seconds, the index is valid.

Select * from cards where cards> '123'; -- millisecond-level, index valid.
Select * from cards where cards like '200'; --> millisecond level, index valid, but also related to the returned data size
Select * from cards where cards like '_ 999999 _'; -- Minute-level, invalid Index
Select * from cards where cards like '100 _ '; -- millisecond-level, index valid, but also related to the returned data size
Select * from cards where cards like '_ 999999'; -- the minute-level index is invalid.
Select * from cards where cards like '000000_000000'; -- the minute-level index is invalid.

Select * from cards where cards is null; -- the minute-level index is invalid.
Select * from cards where cards = '000000' or 1> 1; -- millisecond level, index valid
Update cards set batchno = batchno where cards = '000000'; -- in milliseconds, the index is valid.
Update cards set batchno = batchno where cards = '000000' or I> 1; -- the minute-level index is invalid. I is a variable.
Update cards set batchno = batchno where cards = '000000' or: V> 1; -- the minute-level index is invalid. v is the placeholder symbol, which is actually a common parameterized query.

Select * from cards where cards between '000000' and '000000'; -- the millisecond-level index is valid.
Select * from cards where cards = '20180101' or batchno = '20180101'; -- the index is valid, but it does not make sense. It is still a full table scan;
Select * from cards where cards = '000000' and batchno = '000000'; -- the index is valid in milliseconds.
Select * from cards where batchno = '000000' and cards = '000000'; -- the index is valid in milliseconds. This indicates that Oracle has optimized the index Field Processing first.

Select * from cards where (batchno, cards) in (select '000000', '000000' from dual); -- in milliseconds, the index is valid.

PS: from the test above, Oracle optimization, especially Like optimization, is in place, because I still thought that the database would not use indexes for Fuzzy queries. of course, from the above tests, we can also introduce some Oracle index storage technologies.