[04] index and SQL statement Optimization

[04] index and SQL statement Optimization
1. Index concept

The index of a database is similar to a book directory. The index points to the location where the query content is located (such as the page number in the book), reducing the number of disk I/O times. The database program does not need to scan the entire table and can quickly find the content to be searched through the index.

Clustered index: the words we know can be searched through pinyin. Pinyin is also the index, and the index order is the physical order of data. We store the data in the order of indexes. The way to determine the physical order of the data in the table is called clustered index. Because there is only one physical order of data, one table can have only one clustered index. Clustering indexes are highly efficient, but have a significant impact on data updates. They are not suitable for columns that are frequently updated.

Non-clustered index: for words that we don't know, you need to find the page number of the word in the radicals directory, and then find the corresponding word through the page number. We store this data in one place, and the index is stored in another place. The index with a pointer pointing to the data storage location is called a non-clustered index. Clustering index efficiency is low and the impact is small.

ROWID stores the specific location of the row in the database file: 64-bit (A-Z, a-z, 0-9, +,/), ROWID consists of four parts: ooooooofffbbbbrrr

OOOOOO: Data Object number (6-Digit Display)

FFF: related data file number (3-Digit Display)

BBBBBB: data block number (6-Digit Display)

RRR: row number of the data block (3-Digit Display)

2. advantages/disadvantages of Indexes

(1) greatly improving the speed of data retrieval, grouping, and sorting

(2) It takes time to dynamically maintain indexes for adding, deleting, and modifying database tables. The larger the data volume, the more time required.

(3) Each index occupies a certain amount of physical space.

3. Notes on selecting index fields

(1) The most frequently used fields in the WHERE and order by clauses

(2) columns frequently sorted by group

(3) connection fields of two tables

(4) fields with high selectivity (less with the same value)

(5) small fields (do not index large text fields or even ultra-long fields)

(6) fields that are not updated frequently

(7) use numeric fields as much as possible for index fields. Character Types compare each character in a string one by one, while numbers only need to be compared once.

(8) try to replace char/nchar with varchar/nvarchar, because the variable length field has a small storage space and is more efficient.

(9) If a single column index contains null values, this record will not exist in the index. If each column of the composite index is empty, this record does not exist in the index. At least one column is not empty and this record exists in the index.

4. Create a composite index

(1) If several fields frequently appear in the WHERE clause in the AND mode at the same time, AND there are few single field queries, you should create a composite index.

(2) The number of fields in the composite index generally cannot exceed 3

(3) If there are both single-field indexes and composite indexes of these fields, you can delete composite indexes.

(4) put the most frequently used fields in the WHERE clause first in the composite index. If the usage frequency is the same and the data is physically sorted by a field, the field is placed first in the composite index. If the usage frequency is the same, the most selective field is placed at the top, and the least selective field is placed at the end.

(5) only when the first column of the composite index is used as a condition can the system use the index and ensure that the field order in the condition is consistent with the index order.

5. Index operation notes

(1) do not judge whether the index column is null or is not null.

(2) do not use index columns! =, <>,> Operator and NOT operation

(3) do not perform functions, arithmetic operations, or other expressions (such as +, |) on the index column.

(4) do not use like operations with wildcards % on Indexes

(5) display the converted data type. When comparing data of different data types, ORACLE automatically converts the data types. When comparing characters and values, ORACLE converts the data types to character types first. This index will not be used because of internal type conversion.