Shulou(Shulou.com)05/31 Report--

This article mainly explains "how database indexing actually works". Interested friends might as well take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how database indexing actually works.

The database has the function of organized data storage. The storage has a specific structure for placing data. Each database type has its own format for storing data. They are adjusted and optimized for specific use cases. In the following example, we have a database that contains car information:

> Example of a database table with cars

It looks good. It's easy to browse. But have you ever asked yourself how your data is stored?

Internal storage

Each database is stored internally in a file with specific encodings and formats applied. For simplicity, let's assume that a database is supported by CSV files. It goes like this:

ID,Brand,Model,Color,Price

1,Ford,Focus,Grey,42000

2,Toyota,Prius,White,40500

3,BMW,M5,Red,60000

4,Audi,A3,Black,38000

5,Toyota,Camry,White,51500

6,VW,Golf,Grey,32000

It's all simple. Finding with only six entries is not a problem. Can you imagine that you have 100000 entries? It becomes very slow through the file. The query time increases proportional to the size of the file. When we know that the database will grow over time, we need to find a solution.

Index to rescue here.

Database index

A database index is a data structure used to speed up the operation of retrieving data. What does it look like?

If we need to retrieve a car from our table through ID 6, it will jump faster when we jump directly to the corresponding row rather than through the rest of the loop. This is the main idea of the index. We also need to save the offset to each item.

The easiest way is to keep the offset of each entry in the hash. The key is the value of the column we want to index (in this case, it is the ID column). The hash value is the offset in the database file. For ID = 1, the offset is 0. For ID = 2, the offset is 24. It looks like this:

Hash Index {1:0, 2:24, 3:51, 4:70, 5:92, 6:118}

1 = > {0}-> 1 recorder, FordRegy, Focus, and GreyPol 42000.

2 = > {24}-> 2pr ToyotaPriusreWhitePol 40500

3 = > {51}-> 3recoveryBMW, M5, Red, 60000.

4 = > {70}-> 4rec Audirecy A3je Blackjue 38000

5 = > {92}-> 5pr ToyotaRegy CamryRegy WhitePol 51500

6 = > {118}-> 6pr VW pas Golf pr ecture GreyPol 32000

After the index is added, querying the car through ID will return the results faster. The retrieval request enters the hash index and occupies the offset of the corresponding ID. Then it starts to read the database file completely from the offset in the necessary entries.

Index is the correct way to achieve uniqueness constraints. When you want to ensure that the values in a column are unique, you need an index on the column. Otherwise, each insert operation gets stuck and checks to see if the new data already exists.

You can also have multiple indexes. If we need to query quickly through any other columns, we will also add an index. For example, we can add colors by color and query the index of express cars. But each new index brings additional costs to the database.

Index overhead

First, each index hash requires additional memory. The more indexes we add, the more memory will be used. It is important to remember to add indexes only on columns that are queried frequently. Otherwise, indexing each column consumes a lot of memory.

Second, for fast read operations, write operations are slightly slower. Each time we add an entry to the table, we must also create an item in the hash index. A similar situation applies to updating or deleting data. This is a trade-off we have to deal with.

Summary

Database indexes are a good way to enhance read queries.

Uniqueness constraints require index columns.

With each new index, more memory is consumed.

Adding an index has an impact on writing and updating operations.

This is how a simple hash index works. There are many other ways to use database indexes, such as sorting string tables or B-trees. They use more complex logic and optimized structures to achieve better performance results.

However, the idea of saving the offset remains the same. By using indexes correctly, you will be able to reach a new level when using databases.

At this point, I believe you have a deeper understanding of "how database indexing actually works". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.