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

This article introduces the relevant knowledge of "what are the reasons for the excellence of database index". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

A short guide to working with database indexes

Database indexes are usually poor. The power of database index is realized only when it is designed and used effectively. Otherwise, the index is a pure waste of disk space and database performance. But you don't want to waste disk space, so let's quickly get through some of the content you need to design and use the index correctly.

There is only one reason to create a database index-only the current or future query load is provided on the database, that is, the index must be designed based on current or expected usage. All major databases provide a way to monitor index usage.

Use the where clause

The WHERE clause in the SQL query filters the data. The index helps to filter out the data when it stores data in a predefined order based on some key columns. If the query uses these key columns, the index will be used and the filter will be faster.

When a key column is referenced in the Where clause, the existence of the index does not guarantee its usage. Therefore, you must make sure that the index is being used. Most databases have a component called the query engine, which determines which of the many execution plans ensures the fastest results of the query.

The query engine is accompanied by interpretation commands to help us get the execution plan and list the cost of each step of the plan.

Efficient use of data types

The database supports only a small number of data types. All data types have been created to provide a specific purpose. If you do not use data types designed to serve that purpose, you will put additional load on the database for implicit casting, extra disk space, and so on.

A simple way to avoid this is to use DateTime to store dates and times and integers to store integers. Do not use VARCHAR to store everything from numbers to JSON objects. Most databases now support JSON as the native data type. This means that you can use SQL to effectively store and retrieve data from JSON objects in the database.

Don't over-index

Index creation must strike a balance between using disk space, increasing administrative overhead, and increasing query performance. You cannot create an index to save the space for database query performance. You have not been able to create indexes on all columns to provide all queries. It doesn't work like that.

You must check the query workload by identifying the following-

The column used to filter Filter data.

The column used to join the Join table.

The column used to sort Order data.

The column used to group Group data.

Used to filter columns used in a combination of Join,Oder or Group.

These five things will determine how many indexes need to be created, what combination of index columns should be in the index, and priority.

Note that the index IDX_A,Column_Q DESC on Column_P is the same as

Index IDX_A in Column_Q, column P or

Index idx_b on column_p desc, column Q

Also make sure that you use some tools or scripts to identify whether there are duplicate indexes in the database.

Know that when you don't use an index

Beware of the temptation of sample terms. This is an index killer. Know that the first column of the index must always exist in the filter, order, join, or group operation of the query to be used. If you use the LACE clause to filter data using an index, you should know that you cannot expect the index to be in something like "%".

Indexes are not used when the database engine cannot implicitly map the sorting of data types, character sets, or index columns. Use this link for more details on this content and for more restrictions on the index.

This is the end of the content of "what are the reasons for the excellent database index". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.