Shulou(Shulou.com)06/01 Report--

What is an index?

An index in a database is similar to a catalog in a book. Indexing is an internal way for SQL Server to orchestrate data, which provides SQL Server with a way to route query data.

An index page is the data page that stores the index in the data. The index page stores the keyword page that retrieves the data row and the address pointer to the data row. By using the index, the retrieval speed of the database can be greatly improved and the performance of the database can be improved.

Classification of indexes

1. Unique index

A unique index does not allow two rows to have the same index value. When a unique constraint is created, a unique index is automatically created. Although unique indexes help you find information, it is recommended that you use primary key constraints for best performance.

2. Primary key index

Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a special type of unique index.

The primary key index requires that each value in the primary key be unique. It also allows quick access to data when primary key indexes are used in queries.

3. Clustered index

In a clustered index, the physical order of the rows in the table is the same as the logical (index) order of the key values.

4. Nonclustered index

The nonclustered index is built on the index page, and the location of the record can be found in the index when querying the data. The nonclustered index makes the physical order of the rows of data in the table do not match the logical order of the key values, and the clustered index has faster data access speed than the nonclustered index. For example, an index sorted by stroke is a nonclustered index, and the page number corresponding to the word (word) drawn by "1" may be larger than the page number corresponding to the word (word) drawn by "3".

In SQL Server, only one clustered index can be created for a table, but multiple nonclustered indexes can be created. Set a column primary key, and the column defaults to the clustered index.

5. Composite index

When you create an index, you can not only create an index on one of the columns, but, like a primary key, you can combine multiple columns into an index, which is called a composite index.

It is important to note that the composite index is used only when the first column or the entire composite index column is used as a condition to complete the data query.

6. Full-text index

Full-text indexing makes a special type of tag-based functional index created and maintained by the full-text engine service in SQL Server. Full-text indexing is mainly used to search for strings in a large number of text, and the efficiency of using full-text indexing will be much higher than that of using the LIKE keyword of T-SQL.

Because of the SQL server graphical operation, it is relatively easy to create an index, so you can skip the creation step. After creating an index, you can specify the index query method of SQL server data query, just as you choose Pinyin or stroke when looking up a dictionary. The T-SQL statement is as follows:

Select * from xueyuan # specify the query table with (index=ix_name) # specify the index where student name like 'Sun%' # query condition

Although you can specify which index to query by SQL server, you don't need to specify it manually. SQL server will automatically optimize the query based on the index you create.

Using indexes can speed up data retrieval, but it is not necessary to index each column. Because the index itself needs to be maintained and takes up some resources, the indexed columns can be selected according to the following criteria.

Frequently searched columns. It is often used to query selected columns. Columns that are sorted and grouped frequently. Columns that are often used to join (primary key / foreign key).

Do not use the following columns to create an index.

Contains only a few differences worth listing. The table contains only a few rows, and it may not be cost-effective to create an index for a small table, because SQL Server takes longer to search for data in the index than it takes to search the table row by row.

The correct use of indexes in SQL statements, especially in select statements, can greatly improve the query speed and ensure the performance of the application. Provide a few experiences for your reference only:

Minimize the use of "asterisks" to return all columns when querying, and do not return unwanted columns. The index should be as small as possible and be indexed on columns with a small number of bytes. When there is more than one conditional expression in a where sentence, the expression that contains the index column should precede the other expressions. Avoid using expressions in order by sentences. Periodically regenerate or reorganize the index and defragment according to the frequency of business data. (because in SQL server, index data is automatically maintained by the system, index data may be updated after data is added, deleted, and modified. With the long-term and frequent data update, the index data will be scattered in different locations of the disk, forming fragments, resulting in slower and slower query speed. therefore, according to the frequency of data update, delete the original index regularly and recreate the same index. let the index be stored in a continuous space, so as to remove fragments, which can improve the query speed. )

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.