Database Operations-create indexes, databases-Indexes

Database Operations-create indexes, databases-Indexes

When receiving a task some time ago, You need to query the existence of a data entry from 10 million data entries within two seconds. I was shocked. 10 million pieces of data! Two seconds! Is that true? Fortunately, the master gave a clear path. You can consider using Table partitions. Although I do not know what table partitions are, there is at least one direction. Then I began to search for the table partition information on the Internet and looked at the information on the Internet. Almost all of them were tutorials on table partition creation. However, the table partition I want to create is different from the example. It is difficult to create a table partition dynamically. I haven't done a good job for a long time. Later I remembered that chunge and youge mentioned that they used to search for data in the database very slowly. Then, after adding an index to the database, the search speed increased several times.

Well, let's look at the index. First, 10 million pieces of false data are created based on the data format. Create a table index.

Step 1: Right-click "Index" under the table and select "New Index". Then, create an index based on your needs.

Step 2: Change the index name and select the fields to be indexed.

Step 3: Select fields as needed

Step 4: confirm to add the index

Now, the index is created.

Next let's take a look at the comparison between the search speed with and without an index.

The full table search speed without an index is 5 seconds.

After the index is added, the full table retrieval speed is 0.363 seconds.

When learning the database, you will know that there are indexes, and the indexes are divided into clustered indexes and non-clustered indexes. So what is the index?

Take the dictionary as an example: You can query the directories in front of the dictionary according to the pinyin alphabet and radicals. To query a word, you can quickly find the Chinese character you want to query based on the pinyin or radical query. Then, the dictionary directory is the index. The pinyin query method is a clustered index, and the first query is a non-clustered index.

After reading the example above, the following is easy to understand.

Clustered search leads storage records exist physically consecutively, while non-clustered indexes are logically consecutive. Physical storage is not consecutive. Like fields, clustered indexes are continuous, and a must be followed by B. Not clustered indexes.

 

I know so much about indexes for the moment. If you want to learn more accurately, you must continue to learn more.