In Oracle databases, creating indexes is relatively simple. However, it is difficult to create an index reasonably.
In Oracle databases, creating indexes is relatively simple. However, it is difficult to create an index reasonably.
I believe that three appropriate indexes should be created, that is, the appropriate number of indexes should be created on the appropriate tables and columns. Although this can be summarized in one sentence, the database administrator must make great efforts to optimize the index. Specifically, the following requirements must be met to meet these three requirements.
1. Create an index based on the table size.
Although creating an index for a table can improve the query efficiency. However, the database administrator must note that indexing also requires a certain amount of overhead. This does not mean that indexes are created for all tables, so the database performance can be improved. This is wrong. On the contrary, if indexes are created for all tables no matter which one or two of them are used, the indexes will negatively affect the database performance. Because the overhead of index abuse may be far greater than the resulting performance gains. Therefore, the author believes that the database administrator must first create an index for the appropriate table, rather than creating an index for all the tables.
Generally, you do not need to create an index for a small table. For example, in an ERP system database, the department table is used to store information about an Enterprise department. Generally, there are dozens of enterprises, with a maximum of one hundred. The 100 records may be too many for humans. But it is not enough for a computer to plug his teeth. Therefore, there is no need to create an index for similar small tables. Because even if an index is created, its performance will not be greatly improved. On the contrary, the indexing overhead, such as the maintenance cost, is greater than this. That is to say, paying more than getting more is obviously against common sense.
In addition, for ultra-large tables, you do not have to create an index. Although some tables are relatively large, there are many records. However, it is appropriate to create an index for this table. If there is a table in the system, it is mainly used to save some change information in the database. This information is often used only by the database administrator. It is not appropriate to create an index for this table. Because this table is rarely used, you only need to view it when there is a problem. Second, even if it is viewed, there are not many records to be queried, which may be the last week's update records. For some ultra-large tables, index creation often fails to achieve the expected results. In addition, when you create an index on a table, the indexing overhead is much larger than that of a common table. Are you sure you want to create an index for a large table? The author believes that it mainly focuses on two aspects. First, you need to pay attention to the number of records that need to be queried frequently in this large table. In general, if the data to be queried does not exceed 10% to 15%, there is no need to create an index for it. At this time, the indexing overhead may be much higher than the performance improvement. This ratio is just an empirical data. If the database administrator needs to draw a more accurate conclusion, test and analysis are required. That is, the database administrator needs to test the full table scan time to see if it is longer or shorter than the query time after the index is created. If it is long, it indicates that it is necessary to create an index. If not, it means that the full table scan speed is faster. In this case, there is no need to create an index.
In short, when considering whether to create an index for a table, it is generally not necessary to create an index for a small table. For tables, actual analysis is required. A little simpler, which can be determined based on the approximate ratio. If you want to be more accurate, you can perform full table scan performance analysis to determine whether the database performance has been improved as expected after the index is created.
2. Create indexes based on column features.
The index creation effect varies depending on the characteristics of the column. The database administrator needs to know which columns can be indexed. At the same time, you also need to know which columns to create an index will result in more than half the effort. This helps them understand how to create an index for a field.
Based on the author's experience, creating indexes for columns with the following features can be quite effective. For example, for columns with few duplicates, especially those columns with unique constraints. Creating an index on these columns often results in very good results. For example, if you want to query all columns with non-null values in a mix of Null values and non-Null values, it is best to set an index for these columns. If you frequently need multi-table join queries, you can set indexes on the connected columns to get twice the result with half the effort.
It can be seen that the proper index setting is related not only to the database design architecture, but also to the economic business of the enterprise. For this reason, some software packages, although the database administrator has done index optimization at the beginning. However, with the increase of economic data, the index effect will be compromised. This is mainly because the table of records affects the index optimization effect. Therefore, the author suggests that database administrators optimize database indexes over a period of time, for example, one year, even if they are using big-name software suits. Remove the removed items and adjust them to improve the database performance.
For example, a table in the database is used to save user information. There is a field ID number, which is a unique field. An index is created for this field during database design. However, when the database is in use, the user does not enter the user's ID card number. In addition, this number is not usually used for queries. When there are too many records in months and months, the index field on this ID card number will not improve the database query performance, but will become a weakness. For these columns with many NULL values that do not frequently query all columns with non-NULL values, the database administrator must make up his mind to clear the indexes of these columns.
Therefore, the optimization and adjustment of indexes are a dynamic process, which does not mean that after the database is designed, it does not need to be adjusted. Database Administrators often need to make appropriate changes based on the changes recorded. To improve the index effect.
3. How many indexes can be created on a table?
Although there is no limit on the number of indexes created on a table, the more indexes you create, the better. That is to say, in terms of index creation, 1 + 1> 2 is often not true. Sometimes, the more indexes you create, the opposite effect may be achieved. How many indexes can be created on a table? There is no clear standard. Instead, the database administrator needs to determine based on the actual usage and the situations recorded in the database.
Generally, the more indexes a table has, the faster it queries. However, the table update speed is reduced. This is mainly because the table update speed (such as inserting a record into the table) increases with the increase of indexes. This is mainly because related index information needs to be updated while updating records. Therefore, if it is appropriate to create an index in a table, a balance between the update speed and the query speed needs to be obtained. For example, some data warehouses or decision-making database systems are mainly used for query. Related records are often imported during database initialization. In this case, you can set more indexes to improve the query performance of the database. At the same time, because the record is not updated much, the update speed will not be affected when there are many indexes. Even if you need to import a large amount of data at the beginning, you can disable the index first. After the data is imported, enable the index. This method can be used to reduce the impact of indexes on data updates. On the contrary, if the tables often need to update records, such as some transaction application systems, data update operations are common. If you create too many indexes in a table, the update speed will be affected. Because update operations are frequent, the negative impact on these operations is much higher than query efficiency. In this case, you need to limit the number of indexes and create indexes only on some necessary fields.
During database optimization, I usually set indexes for the columns Based on the purposes of these tables. You can query the related dynamic views to see if the operations on this table are large in the proportion of update operations (including update, delete, and insert operations) or large in the proportion of query operations. When too many indexes affect the update speed, the database administrator needs to disable some indexes to improve the database performance.
In short, create an appropriate index on the appropriate tables and columns. This sentence contains many meanings. The above content is only part of the content. As the saying goes, the Master leads the door and practices depend on himself. Here I can point to the end. Some specific index optimization content still requires readers to understand and summarize in their daily work.
