|
|
Posted on 9/20/2019 9:31:08 AM
|
|
|
The concept of indexing
The purpose of establishing an index is to improve the performance of the database system, speed up the query speed of data and reduce the response time of the system.
What is an index: An index in a database is similar to a table of contents for a book, where you can quickly find the information you want without reading the entire book.
In a database, a database program uses indexes to quickly query data in a table without having to scan the entire table.
The table of contents in a book is a list of words and the page numbers in which they are located, and the index in the database is a list of values in a table and where each value is stored.
Pros and cons of indexing: Most of the overhead of query execution is I/O, and one of the main goals of using indexes to improve performance is to avoid full table scanning, because full table scanning needs to read every data page of the table from disk, and if there is an index pointing to a data value, the query only needs to read a few times to the disk.
Therefore, the reasonable use of indexes can speed up the query of data. However, indexes do not always improve the performance of the system, indexed tables need to take up more storage space in the database, and the same command to modify and delete data will take longer to run and maintain the index.
Therefore, we must use the index reasonably and update it in time to remove the suboptimal index.
Classification of indexes
The index is similar to the table of contents in front of the Chinese dictionary, and you can quickly locate the word you want to find according to the pinyin or the capital.
UNIQUE: The index value of each row is unique (a unique constraint is created, and the system will automatically create a unique index).
Primary Key Index: The primary key column specified when the table is created automatically creates a primary key index and has unique characteristics.
CLUSTERED: Clustered indexes are equivalent to using a dictionary's pinyin lookup, because the clustered index stores records that are physically continuous, that is, pinyin a must be followed by b.
NONCLUSTERED: NONCLUSTERED indexes are equivalent to using dictionary radicals to lookup, non-clustered indexes are logically continuous, and physical storage is not contiguous.
A clustered index can only have one table in a table, while a non-clustered index can have more than one table in a table.
Which fields need to be indexed
1. The primary and foreign keys of the table must have an index
2. Tables with more than 300 data should have an index
3. Tables that are frequently connected to other tables should be indexed on the connected fields
4. Fields that often appear in the Where clause, especially those in large tables, should be indexed
5. Fields that are frequently sorted or grouped (i.e., perform GROUP BY or ORDER BY operations) should be indexed
6. The index should be built on highly selective fields
7. The index should be built on small fields, for large text fields or even ultra-long fields, do not build indexes, and use integers as key values as possible, because integers have the fastest access speed
8. The establishment of composite indexes needs to be carefully analyzed; Try to consider using single-field indexes instead
9. Tables that frequently perform data manipulation should not be indexed too much
10. Delete useless indexes to avoid negative impact on the execution plan
The disadvantage of too many indexes
1. The storage space will become larger, and each index will need to be stored in space
2. If there are many non-clustered indexes, once the clustered index changes, then all non-clustered indexes will change accordingly
3. Too many indexes will lead to an increase in the number of combinations that need to be evaluated in the optimization process of the optimizer, increasing the query time
4. Each index has statistical information, and the more indexes, the more statistical information, which increases the query time
5. Update overhead, once a data changes, and there are many columns changed, it may cause several indexes to change, resulting in update, delete, and insert slowing down
|
Previous:Teenager strong accompaniment MV videoNext:C# gets the current system disk letter, system directory, desktop, etc
|
Landlord|
Posted on 12/27/2020 11:56:05 AM
|
