Database operations--Creating indexes

A task was received in the previous period to query for the existence of a single piece of data from 10 million data within two seconds. It really scared me a jump. 10 million piece of data! Two seconds! Can you do that? Fortunately, the teacher pointed to the road. You can consider using table partitioning to implement. Although I don't know what the table partition is, there is at least one direction. Then began to search the online table partition data, looked at the information on the Internet, almost all of the construction of the table partition of the tutorial. But the table partition I'm going to build is different from the example, and it's hard to create a table partition dynamically. Did not do a good job for half a day. Later, I remembered that spring elder brother and tour elder brother mentioned before they in the database search data is very slow, and then indexed in the database, the search promoted several times.

OK, let's look at the index. First, 10 million false data were created based on the data format. It then starts building the table index.

The first step is to right-click the index below the table to select New Index, and then create the appropriate index for your needs.

Second, change the index name and select the fields that need to be indexed

Step three, select fields as needed

Fourth step, determine the Add index

Well, the index has been created well.

Here's a look at the comparison of indexed and non-indexed search speeds

No index to retrieve full table speed of 5 seconds

0.363 seconds to retrieve the full table after indexing

The index is known when learning the database, and the index is divided into clustered and nonclustered indexes. So, what exactly is an index?

Take the dictionary example: the directory in front of the dictionary, you can follow the pinyin and radicals to query. We want to query a word, just according to pinyin or radical query can quickly find the Chinese character to check. Then, the directory of the dictionary is the index. The phonetic query method is a clustered index, and the radical query hair is a nonclustered index.

Look at the above example, the following words are easy to understand.

Clustered search-lead storage records are physically contiguous, while nonclustered indexes are logically contiguous, and physical storage is not contiguous. Just like a field, a clustered index is contiguous, and a is definitely b behind. The non-clustered index is not necessarily the same.

The understanding of the index is so much for the time being, very shallow. If you want to really learn the essence, you will continue to deepen learning.

Database operations--Creating indexes