Principles of database creation indexing

Principles of database indexing

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

The benefits of indexing are obvious, but few people care about the cost of using the index. If the database administrator is able to have a good understanding of the cost of the index, it will not be so arbitrary to build the index everywhere. Careful counting, in fact, the cost of building an index is quite large. It takes time and effort to create indexes and maintain indexes. Especially in the database design, the database administrator for which fields in the table need to be indexed, to investigate, to coordinate. If the record in the indexed table is incremented, deleted, or modified, the database is re-adjusted to the index.

Although this working database will be completed automatically, you need to consume the resources of the server. The more data you have in a table, the more resources it consumes. If the index is an object that actually exists in the database, each index takes up a certain amount of physical space. If the index is more, it will not only occupy a lot of physical space, but also affect the performance of the whole database. It can be seen that the database administrator will still have to pay a lot of cost to use the index to improve the performance of the system. The database administrator now has to consider how to strike a balance between the two. Or, find a tipping point for return and input.

Iron Law II: For queries rarely involved in columns or duplicate values more columns, do not index at the time of the query, if we do not press a field to query, then the index is wasted on this field. If you now have an employee information form, we may inquire about employee information by employee number, employee name, or origin. However, we often do not follow the identity card number to inquire. Although this ID number is unique. At this point, it is not possible to increase the speed of the query even if the index is established on this field. Instead, it 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 "male," female, the Position field is also a limited number of content. At this point, adding indexes on these fields does not significantly increase the query speed and reduce 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 to not index columns that are rarely involved in a query or that have more duplicate values.

Iron Law Three: for the column query by scope, it is best to build an index in the information management system, many times need to query some transactions by scope. such as in the ERP system, often need to query the current month sales orders and sales shipments, which requires a date range to query transactions. If there is a time when the inventory is not correct, also need a period of inventory in and out, such as the May 1 to December 3 inventory trading situation and so on. 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 them. Because the index is sorted, the specified range is contiguous when it is saved, the query can take advantage of the sorting of the index, speed up the query time and reduce the user wait time.

However, if you may need to query by scope, but if this range of query conditions are not used in many cases, it is best not to use the index in the Employee Information table, you may need to query the employee details of March 2008 before, to increase their benefits. However, because there are not many records in the table, similar queries are rarely made. Yanet This field is indexed, although harmless, it is clear that the index gains less than its cost. For the database administrator, it is not worth the candle.

Furthermore, if you use the scope query, it is best to use the top keyword to limit the results of a query. such as the first 500 records are displayed in order, and so on. Using the top keyword with the scope can greatly improve the efficiency of the query.

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

Define the index column with the primary key, and make sure to index it. Because the primary key can be accelerated to locate a row in the table. Combined with the function of the index, the speed of the query can be doubled. As in the Employee Information table, we tend to set the employee number as the primary key. Because this not only increases the speed of the query, it also guarantees the uniqueness of the employee number because the primary key requires the record to be unique. At this point, if the Employee Number field is set to index, employee information is queried through the employee number, which is much more efficient than no index.

In addition, to make a field's value unique, it can be implemented in two ways. One is the index of the primary key mentioned above. There is also a unique index that uses the unique keyword to specify the uniqueness of the field content. Both of these methods automatically create a unique index on the specified column in the table. There is no obvious difference between the results of these two approaches.

The query optimizer does not differentiate between the unique indexes that are created in the end, 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 the field. Because the primary role of the foreign key is the connection between table and table query. If you create an index on the foreign key, you can speed up the connection query between the table and the table. As in the Employee Profile table, there is a field for the employee position. Because employee positions are constantly changing, here, the store is actually just a code for an employee position. Information about the position is documented in a separate job information sheet.

At this point, the Employee position field is the foreign key. If you create a foreign key on this field, you can significantly increase the connection speed of the two tables. Moreover, the more records, the more obvious the effect.

Therefore, it is better to index the table if it has a foreign key or a primary key. Through the index, the function of primary key and foreign key can be enhanced 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 the Text field (TXT), the Image Type field (image), and so on. If the fields in the table belong to these data types, it is best not to index them. Because these fields have some common characteristics. If the length is indeterminate, it is either very long or a few characters, or it is an empty string. Data types such as text data types are often used in database tables in the application system to make notes. Sometimes the notes are long, but sometimes they don't have data. If an index is built on this type of field, it will not work at all. Instead, it increases the burden on the system.

So, on some of the more specific types of data, it's prudent to build indexes. Under normal circumstances, it is not necessary to index it. However, there are special circumstances. For example, in the ERP system, there is a product information for this table, which has a product specification for this field. Sometimes, it can be as long as 5,000 characters long. At this point, only the data type of the text type can accommodate such a large amount of data. Moreover, in the query, the user also likes to use the specification of this parameter to query product information. At this point, if the field is not indexed, the query will be slow. When this happens, the database administrator indexes the system resources at the expense of a little.

From here can also be seen, although the above several said the time iron law, but, whether it is necessary to follow, or need the database administrator according to the actual situation of the enterprise, make a reasonable choice.

Iron Law VI: The index can be followed

The collection of where statements melts into one user sometimes uses some restriction statements when querying information. If you are inquiring about a sales order, you will often use a set of conditions for the customer and the date of the order, and if you are querying the inventory transactions for a product, you will use the conditional collection of the product number and the date of the transaction date. For these columns of data that are often used in the WHERE clause, the index is built into the collection of where clauses, and for data columns that need to be accelerated or frequently retrieved, the data columns that frequently participate in the query can be queried in the order of the index to speed up the query time.

In short, the index is like a double-edged sword, which can improve the performance of the database, it may also play a negative role in the performance of the database. As a database administrator, you have the ability to determine the right index on the right time, the right business, and the right fields. The above six iron laws are just a few basic requirements for indexing

Principles of database creation indexing