Database Optimization-index and database index

Database Optimization-index and database index

Database index

1. What is an index?

In databases, the meaning of indexes is no different from the word "Index" in the daily sense (think about dictionary scanning when I was a child). It is a database object used to increase the speed of accessing database table data.
A) indexes can avoid full table scans. Most queries can only scan a small number of index pages and data pages, instead of traversing all data pages.
B) For non-clustered indexes, some queries may not even access data pages.
C) clustered indexes can prevent data insertion operations from concentrated on the last data page of the table.
D) In some cases, indexes can also be used to avoid sorting operations.

 

Of course, as we all know, although indexes can increase the query speed, they will also lead to a decline in the performance of database system update data, because most data updates need to update indexes at the same time.

For example, select * from table1 where id = 44. If no index exists, you must traverse the entire table until the row with ID equal to 44 is found. If an index exists, it must be an index created on the column with ID ), find 44 in the index (that is, find the ID column) to find the location of this row, that is, find this row. It can be seen that the index is used for locating.

2.Index Storage

The basic information contained in an index record includes: key value (that is, the value of all fields specified when you define the index) + logical pointer (pointing to the data page or another index page ).

When you create an index for an empty table, the database system will allocate an index page for you, which is always empty before you insert data. This page is both a root node and a leaf node. Every time you insert a row of data into the table, the database system inserts a row of index records into the root node. When the root node is full, the database system splits according to the following steps:
A) create two son nodes
B) split the data in the original root node into two halves and write the data to the new two sons respectively.
C) Add a pointer to two son nodes in the Root Node

 

Generally, because index records only contain index Field Values (and 4-9 bytes pointer), index entities are much smaller than real data rows, index pages are much more intensive than data pages. An index page can store more index records, which means that I/O takes a significant advantage in index search, understanding this helps you understand the advantages of using indexes in essence.

3. Index type

A) unique index: the unique index does not allow two rows to have the same index value.

B) primary key index: If a primary key is defined, the primary key index will be automatically created. The primary key index is a special type of unique index. Each value must be unique and cannot be blank.

C) clustered indexes. Table data is stored in the order of indexes. For clustered indexes, leaf nodes store real data rows, and there are no separate data pages. Each table can have only one

D) Non-clustered indexes. The table data storage sequence is irrelevant to the index sequence. For non-clustered indexes, the data is stored in one location, and the index is stored in another location. The index contains a pointer to the data storage location.

 

4. index creation syntax

Crate [unique] (unique) [clustered] (clustering) [nonclustered] index name on table name (column name)

Note: select a value in the preceding [] as needed.

 

5. Index deletion syntax

Drop index table name. index name

Sysindexes: system index table

 

6. Advantages and Disadvantages of Indexes

Advantages:Creating indexes can greatly improve the system performance.
1): by creating a unique index, You can ensure the uniqueness of each row of data in the database table.
2): it can greatly speed up data retrieval, which is also the main reason for creating an index.
3): it can accelerate the connection between tables, especially in terms of data reference integrity.
4): when data is retrieved using grouping and sorting clauses, the time for grouping and sorting can also be significantly reduced.
5): By using indexes, you can use the optimizer during the query process to improve system performance.

 Disadvantages:1): it takes time to create and maintain indexes. This time increases with the increase of data volume.
2): indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space is larger.
3): when adding, deleting, and modifying data in a table, the index must also be dynamically maintained, reducing the Data Maintenance speed.

 

7. Where to create an index

Indexes are created on certain columns in the database table. When creating an index, you should consider which columns can create an index and which Columns cannot create an index.

In general, you should create an index on these columns:
1): in columns that frequently need to be searched, the search speed can be accelerated;
2): forces the uniqueness of the column as the primary key and the data arrangement structure in the organization table;
3): these columns are usually used in connection columns. These columns are mainly foreign keys, which can speed up the connection. You can create indexes on columns that often need to be searched by range, because the index has been sorted, its specified range is continuous;
4): Create an index on the columns that frequently require sorting. Because the index has been sorted, you can use the index sorting to speed up the sorting query time;
5): create indexes on columns in the WHERE clause frequently to speed up condition judgment.

　　Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following features:
1): indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, there is an index or no index, and the query speed cannot be improved.

On the contrary, the addition of indexes reduces the system maintenance speed and space requirements.
2): indexes should not be added to columns with only few data values. This is because these columns have very few values, such as gender columns in the personnel table. In the query results, the data rows in the result set account for a large proportion of the data rows in the table,

That is, the proportion of data rows to be searched in the table is large. Adding indexes does not significantly accelerate the search speed.
3): indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is either large or small, which is not conducive to the use of indexes.
4): When the modification performance is much higher than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced.

When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when modification operations are far more than search operations, you should not create an index.