Principles of Index Establishment

Principles of database indexing

Iron Law One: there is no free lunch, the use of indexes is a price to pay

The advantages of the index are obvious,

But

Few people have ever cared about the cost of using an index.

If the database management

Have a good understanding of the cost of the index and will not be able to index it everywhere.

Carefully count the numbers,

In fact, the cost of building an index is quite large.

It takes time and effort to create indexes and maintain indexes.

Especially in database design, the database administrator for which fields in the table need to be

To build an index, you need to investigate,

Coordinate.

If the records in the indexed tables are added, deleted,

When modifying an operation, the database will re-tune the index

Whole.

Although this working database will be completed automatically,

But

Need

To consume the resources of the server.

When there is more data in the table,

The more resources it consumes.

If the index is an object that actually exists in the database,

So

Each index consumes a

The physical space that is fixed. If the index is more,

Not only takes up a lot of physical space, but also affects the entire database

Performance of the operation.

Visible

To use indexes to improve the performance of the system, the database administrator

itself still needs to pay a lot of price.

Data

The librarian now has to consider how to strike a balance between the two.

Or say,

Find a return and input

The critical point.

Iron Law II: Columns that are seldom involved in a query, or that have more repeating values,

Do not create an index

At the time of the query,

If we do not press a field to query,

It is also a waste to build an index on this field.

As now

There is an employee information sheet,

We may, by employee number,

Employee Name,

or the place of birth

To inquire about employee information.

But

We often do not follow the identity card number to inquire.

Although this ID number is unique.

At this time

Even in this word

Index on the segment,

It is also not possible to increase the speed of queries.

Phase

Anti -

Increases system maintenance time and takes up system space.

This is foot.

In addition, as the Employee information table above, some fields have more duplicate values. such as the gender field is mainly

“

Man

”

、

“

Woman

”;

Position

There are also a limited number of items in the field.

At this time

Adding an index to these fields does not significantly increase the query speed.

Reducing

Less user response time. Instead, the overall performance of the database is reduced because of the need to occupy space.

The second iron rule in Database index management is,

For columns that are rarely involved in a query or that have more duplicate values,

No

The index to be built.

Iron Law III: It's best to index columns that are queried by scope

In the information management system,

Many times you need to query certain transactions by scope.

As in

Erp

System,

often need

To inquire about sales orders and sales shipments for the current month, this is required by date range to

Query transactions. If sometimes

When the inventory is not found, it also requires a period of inventory access, such as

5

Month

1

Day to

12

Month

3

The Daily stock trading situation

and other conditions. At this point, the query is also based on the date.

For these columns of data that need to be queried quickly or frequently within a specified range,

You need to index it.

Because the index is already

Sort

The specified range is contiguous when it is saved, and the query can take advantage of the index's ordering,

Faster query time,

Reduce

User wait time.

But

If you may need to query by scope,

But

If this range of query conditions is not used in many cases,

It's best not to use an index.

As in the Employee Information table,

may need to query

2008

Years

3

Employee details of the month before the entry,

To increase the benefits for them. However, because there are not many records in the table, similar queries are rarely made. The word yanet.

Although harmless, it is clear that

The index is less profitable than its cost. to the data

Library administrators, it is not worth the candle.

In addition, if you use the scope of the query, it is best to use

TOP

Keyword to limit the results of a single query. If the first time you press

The order shows only the previous

500

Records and so on. Put

TOP

Keyword with the scope of use, can greatly improve the query

of efficiency.

Iron Law IV: if the table has a primary key or foreign key, it must be indexed

Defines the index column with the primary key,

Be sure to index it.

Because the primary key can be accelerated to locate a row in the table.

Combine

The effect of the index doubles the speed of the query. In the employee information form, we

The employee number is often set to

Primary key.

Because this will not only improve the speed of the query,

And because the primary key requires the record to be unique,

You can also ensure that employees

The uniqueness of the number. At this point, if the Employee Number field is set

is indexed, the employee's letter is queried through the employee number

Efficiency is much higher than without indexing.

Other than that

To make a field's value unique,

Can be implemented in two ways.

One is the primary key mentioned above

Index. There is also a unique index that leverages

UNIQUE

keyword Specifies a field

The uniqueness of the content. Both of these ways

will automatically create a unique index on the specified column in the table.

There is no obvious difference between the results of these two approaches.

Query optimizer

Does not distinguish between the unique indexes that are established in which way,

And the way they do data queries is the same.

If the data column in a table has a foreign key defined,

It is best to also index this field.

Because the main role of the foreign key is

Is the connection query between the table and tables. If you create an index on the foreign key, you can speed up the table

A connection query between tables. Such as

In the Employee basic information table, there is a field for the employee position. As staff positions are constantly changing,

Here, the stored

is actually just a code for an employee position. In a separate job letter

Details of the position are recorded in the information sheet.

At this time

This Employee position field is a foreign key.

If you create a foreign key on this field,

Can significantly increase the connection between the two tables

Connection speed. Moreover, the more records, the effect

Increasingly obvious.

So

When a table has a foreign key or a primary key, it is best to index it.

By indexing, you can strengthen the primary key and the outer

The function of the key to improve the performance of the database.

Iron Law V: for some special data types, do not create an index

In the table, some fields are more special. such as text fields

(TXT)

, Image Type field

(IMAGE)

Wait a minute. If the words in the table

Segments belong to these data types, it is best not to index them. Because

There are some common features for these fields. such as Long

Degrees uncertain, or very long, a few characters

;

Or it's an empty string. such as the text data type often in the application system's data

The type of data used in the library table to make notes. Sometimes

The notes are long, but sometimes there is no data. If this type of Word

The index on the segment, it does not work at all. Instead, it increases the burden on the system.

So

On some more specific types of data, it is prudent to build indexes.

Under normal circumstances, it is not necessary to establish

Index. However, there are special circumstances. If, at times,

Erp

In the system, there are production

Information about this table, which has a

Product specifications for this field. Sometimes, the length may be as long as

5000

a character. At this point, only the data type of the text type

Can accommodate such a large amount of data. And, at the time of the query,

The user also likes to use the specification parameter to query the production

Product information. At this point, if the field is not indexed, the query will be slow. When this happens, the number of

The library administrator only sacrifices a little system

Resource to index it.

As you can see from here,

Although the above several say the time iron law, however, whether it is necessary to follow,

Or do you need database management?

According to the actual situation of the enterprise, make a reasonable choice.

Iron Law VI: The index can be followed

Where

The collection of statements is integrated

When the user queries the information,

Sometimes, some restriction statements are used.

If you are inquiring about a sales order,

Often

The set of conditions that will be used for the customer and the date of the order

;

If you are inquiring about the inventory transactions of a product, you will use the product

A collection of conditions that are numbered with the start and end dates of the transaction date.

For these often used in

Where

clause, and the index is set to the data column in the

Where

clause in the collection procedure for the

Data columns that need to be accelerated or frequently retrieved.

You can have these data columns that are frequently involved in queries sorted by index

Queries to speed up the time of the query.

Anyway

The index is like a double-edged sword,

That can improve the performance of the database,

May also have a negative effect on the performance of the database

Role.

As a database administrator, have the ability to judge at the right time,

The right business,

The appropriate fields are built

The appropriate index. The above six iron laws are just a few basic requirements for indexing

Principles of Index Establishment