Basics of SQL Data Operations (Intermediate) 6

Chapter 10 "SQL Basics" gives you a preliminary introduction to SQL. You learned how to use the SELECT statement to query, and you also learned how to create your own tables. In this chapter, you will deepen your knowledge of SQL. You'll learn how to create indexes to speed up queries. You will also learn how to use more SQL statements and functions to manipulate data in tables.

Indexing

Suppose you want to find a certain sentence in this book. You could search page by page, but that would take a lot of time. And by using this book's index, you can quickly find the topic you're searching for.

The index of a table is very similar to the index attached to the back of a book. It can greatly improve the speed of queries. For a larger table, by adding an index, a query that would normally take several hours to complete can be completed in just a few minutes. Therefore, there is no reason to add indexes to tables that require frequent queries.

Note:

When you have insufficient memory capacity or hard disk space, you may not want to add an index to a table. For databases that contain indexes, SQL Sever requires a considerable amount of additional space. For example, to create a clustered index, approximately 1.2 times the data size is required. To see how much space a table index occupies in the database, you can use the system stored procedure sp_spaceused, specifying the object name as the name of the table being indexed.

Clustered and non-clustered indexes

Suppose you have found the page number of a sentence through the index of this book. Once you know the page number, you are likely to wander through the book until you find the correct page number. By randomly searching, you can eventually reach the correct page number. However, there is a more efficient way to find the page number.

First of all, turn the book to about half way. If the page number you are looking for is smaller than the page number half way through the book, turn the book to a quarter of the way. Otherwise, turn the book to three quarters of the way. . This way you can continue to break the book into smaller parts until you find it near the correct page number. This is a very effective way to find pages.

SQL Sever's table indexes work in a similar way. A table index consists of a set of pages that form a tree structure. The root page logically divides the records of a table into two parts by pointing to the other two pages. The two pages pointed to by the root page divide the record into smaller parts. Each page breaks the record into smaller partitions until it reaches leaf-level pages.

There are two types of indexes: clustered indexes and non-clustered indexes. In a clustered index, the leaf-level pages of the index tree contain the actual data: the index order of the records is the same as the physical order. In a nonclustered index, leaf-level pages point to records in the table: the physical order of the records is not necessarily related to the logical order.

A clustered index is very much like a directory table. The order of the directory table is consistent with the actual page number order. A non-clustered index is more like a standard index table for a book. The order in the index table is usually inconsistent with the actual page number order. A book may have multiple indexes. For example, it might have both a subject index and an author index. Likewise, a table can have multiple nonclustered indexes.

Normally, you are using a clustered index, but you should have an understanding of the pros and cons of both types of indexes.

Each table can only have one clustered index, because records in a table can only be stored in one physical order. Usually you want to create a clustered index on a table based on the identification field. However, you can also create clustered indexes on other types of fields, such as character, numeric, and datetime fields.

Retrieving data from a table with a clustered index is faster than a table with a non-clustered index. When you need to retrieve data within a certain range, it is better to use a clustered index than a non-clustered index. For example, suppose you use a table to record visitor activity on your site. If you want to retrieve login information within a certain period of time, you should create a clustered index on the DATETIME type field of this table.

The main limitation on clustered indexes is that only one clustered index can be created per table. However, a table can have more than one nonclustered index. In fact, you can create up to 249 non-clustered indexes per table. You can also create clustered indexes and non-clustered indexes on a table at the same time.

Suppose you want to get data from your site activity log not only based on date, but also based on username. In this case, it is effective to create a clustered index and a non-clustered index at the same time. You can create a clustered index on the datetime field and a non-clustered index on the username field. If you find that you need more indexing methods, you can add more non-clustered indexes.

Non-clustered indexes require large amounts of hard disk space and memory. Additionally, although non-clustered indexes can improve the It also reduces the speed of inserting and updating data into the table. Whenever you change data in a table that has a nonclustered index, you must also update the index. Therefore, you should consider carefully when creating a non-clustered index on a table. If you expect that a table will need to update data frequently, don't create too many nonclustered indexes on it. In addition, if hard disk and memory space are limited, you should also limit the number of non-clustered indexes used.

Index properties

These two types of indexes have two important properties: you can use either type to index multiple fields at the same time (composite index); both types of indexes can be specified as Unique index.

You can create a composite index on multiple fields, or even a composite clustered index. Suppose there is a table that records the first and last names of visitors to your website. If you want to fetch data from the table based on the complete name, you need to create an index on both the last name field and the first name field. This is different from creating separate indexes on two fields. When you want to query more than one field at the same time, you should create an index on multiple fields. If you want to query each field separately, you should create independent indexes for each field.

Both types of indexes can be specified as unique indexes. If a field is uniquely indexed, you will not be able to enter duplicate values ​​into the field. An identification field automatically becomes a unique value field, but you can also create unique indexes on other types of fields. Suppose you use a table to store user passwords for your site. You certainly don't want two users to have the same password. You can prevent this from happening by forcing a field to be a unique value field.

The above is the content of SQL Data Operation Basics (Intermediate) 6. For more related articles, please pay attention to the PHP Chinese website (m.sbmmt.com)!