Create a reasonable database index in the Oracle database
- 2020-06-19 11:56:14
- OfStack
In the author's opinion, there should be three appropriations when creating indexes, that is, creating the appropriate number of indexes on the appropriate table and the appropriate columns. While this can be a one-sentence summary of the basic principles of an optimized index, it takes a lot of effort on the part of the database administrator to achieve this. Specifically, to achieve the 3 appropriate has the following requirements.
1. Create indexes based on the size of the table.
Although creating an index for the table can improve the efficiency of the query. However, the database administrator should be aware that the index also requires a fixed overhead. This does not mean that creating indexes for all tables will improve the performance of the database. This is wrong. On the contrary, if all tables are indexed regardless of 372101, it will negatively impact database performance. At this point, the cost of abusing the index may be far greater than the performance benefit. In my opinion, database administrators should first index the appropriate tables, not all tables.
Generally, there is no need to create indexes for smaller tables. For example, in an ERP system database, department tables are used to store information about the enterprise sector. 1 general part of the enterprise is also more than 10, not more than 100. That's probably a lot of records for a person. But for the computer, it's not enough to put his teeth in. Therefore, it is not necessary to index similar small tables. Because even if the index is built, the performance does not improve much. Conversely, the overhead of index creation, such as maintenance costs, is higher than this. In other words, it's counterintuitive to give more than you get.
In addition, even for very large tables, indexes are not required. Some tables, although large, have a very large number of records. But at this point index the table and 1 is appropriate. If there is a table in the system, it is mainly used to save some change information in the database. Often this information is only available to database administrators. It would be inappropriate to index this table. Because this chart is rarely used, you only need to look at it if something goes wrong. Secondly, even if it is checked, there will not be many records to be queried, which may be the updated records of the last week, etc. For large tables, indexing is sometimes not as effective as expected. Moreover, the index on the table is much more expensive than the ordinary table. Do you want to index large tables at all? The author thinks, basically be the content that sees two respects. The first is the number of records that need to be queried frequently in this large table. In general, there is no need to index data if it is frequently queried for no more than 10% to 15% of the data. This is because the overhead of indexing may be much greater than the performance improvement. This ratio is just one empirical statistic. If the database administrator needs to reach a more precise conclusion, then test analysis is required. That is, the database administrator needs to test the time of the full table scan under 1 to see if it is longer or shorter than the query after indexing. If it is long, it indicates the need for an index. But if not, it means that the scan speed of the whole table is fast. There is no need to index at this point.
In summary, small tables generally do not need to be indexed when considering whether or not a table should be indexed. And for the table, you need to carry out the actual situation of the actual analysis. Simple 1 point, can be determined according to the approximate ratio. If you want to be accurate to 1 point, you can perform a full-table scan performance analysis to determine whether the index has really improved database performance as expected.
2. Create indexes based on the characteristics of the columns.
The effect of index creation varies depending on the characteristics of the column. Database administrators need to know which columns to index to get less done. You also need to know which columns to index to get less done. This helps them understand exactly what to index for.
In my experience, indexing columns with the following characteristics often works. For example, for a column with few duplicates, especially for a column with only one constraint defined. Indexing on these columns often works very well. For example, in the case of mixing 1 column with some null values with columns with non-ES11en values, if the user needs to frequently query all columns with non-ES12en value records, it is best to index them. If you frequently need multi-table join queries, setting an index on the columns that are joined can be twice as effective.
It can be seen that the appropriate index setting is not only related to the database design architecture, but also related to the economic business of the enterprise. For this reason, for 1 package, although 1 database administrator has already done the index optimization work. But as more economic data come in later, the index becomes less effective. This is mainly because the tabulation of records affects the effect of index optimization. Therefore, The author suggests that database administrators, even if they use the software suite of big software companies, also need to optimize the index of the database every once in a while, such as one year. The removal of the removal of the adjustment of the adjustment to improve database performance.
For example, there is a table in the database to save user information. There is a field id number, which is a unique field. At database design time, an index is created for this field. But when the database is put into use, users rarely enter their ID Numbers. And usually also basically do not press this number to inquire. When the record month comes many months, the index field on this ID card number not only cannot improve the query performance of the database, but becomes the chicken rib instead. For columns that have many NULL values and don't query all of the non-NULL value records very often, the database administrator is determined, even if the indexes on those columns are cleared.
Therefore, the optimization and adjustment of indexes is a dynamic process, which does not mean that there is no need to adjust after the database is designed. Database administrators often need to make appropriate changes based on how the records change. To improve the effect of indexing.
3. How many indexes is appropriate to create on a table?
While there is no limit to the number of indexes you can create on a table, more is never better. In other words, 1+1 > 2 doesn't always work when it comes to indexing. Sometimes, creating more indexes can backfire. So how many indexes should I create on a table? There is no clear criterion for this. Rather, it is up to the database administrator to make a decision based on what is actually being used and what is being recorded in the database.
In general, the more indexes a table has, the faster its query will be. However, the table updates slower. This is mainly due to the speed of table updates (such as inserting a record into the table), which increases as the index increases. This is mainly because the relevant index information needs to be updated at the same time as the record is updated. To do this, the appropriate number of indexes to create in the table requires an equilibrium point between the update speed and the query speed. For example, for some data warehouse or decision database system, it is mainly used for query. Related records are often poured at database initialization time. At this point, the index set more than 1 point, can improve the query performance of the database. At the same time, because records are not updated very often, the update speed will not be affected in the case of more indexes. Even if you need to import a lot of data initially, you can disable the index first. Wait until the data import is complete before enabling indexes. You can reduce the impact of indexes on data updates in this way. Conversely, if records in those tables need to be updated frequently, such as in some transactional applications, data update operations are routine. If there are too many indexes in one table, the update speed will be affected. Because the update operation is more frequent, the negative effect on it is much greater than the query efficiency improvement. At this point, you need to limit the number of indexes to only 1 necessary field.
In normal database optimization, the author would often index the columns based on the purpose of these tables. You can query the relevant dynamic view to see whether the operation for this table is a large proportion of update operations (including update, delete, insert, and so on) or a large proportion of query operations. When too many indexes have slowed down the update operation, the database administrator needs to disable some indexes first to improve the performance of the database.
In short, build the right indexes on the right tables, on the right columns. This sentence contains many meanings, and the above is only part of the content. As the saying goes, master takes the door, practice depends on oneself. The author here refers to can point to so far. Some specific index optimization content or need readers in the daily work to experience and summary.
1. Create indexes based on the size of the table.
Although creating an index for the table can improve the efficiency of the query. However, the database administrator should be aware that the index also requires a fixed overhead. This does not mean that creating indexes for all tables will improve the performance of the database. This is wrong. On the contrary, if all tables are indexed regardless of 372101, it will negatively impact database performance. At this point, the cost of abusing the index may be far greater than the performance benefit. In my opinion, database administrators should first index the appropriate tables, not all tables.
Generally, there is no need to create indexes for smaller tables. For example, in an ERP system database, department tables are used to store information about the enterprise sector. 1 general part of the enterprise is also more than 10, not more than 100. That's probably a lot of records for a person. But for the computer, it's not enough to put his teeth in. Therefore, it is not necessary to index similar small tables. Because even if the index is built, the performance does not improve much. Conversely, the overhead of index creation, such as maintenance costs, is higher than this. In other words, it's counterintuitive to give more than you get.
In addition, even for very large tables, indexes are not required. Some tables, although large, have a very large number of records. But at this point index the table and 1 is appropriate. If there is a table in the system, it is mainly used to save some change information in the database. Often this information is only available to database administrators. It would be inappropriate to index this table. Because this chart is rarely used, you only need to look at it if something goes wrong. Secondly, even if it is checked, there will not be many records to be queried, which may be the updated records of the last week, etc. For large tables, indexing is sometimes not as effective as expected. Moreover, the index on the table is much more expensive than the ordinary table. Do you want to index large tables at all? The author thinks, basically be the content that sees two respects. The first is the number of records that need to be queried frequently in this large table. In general, there is no need to index data if it is frequently queried for no more than 10% to 15% of the data. This is because the overhead of indexing may be much greater than the performance improvement. This ratio is just one empirical statistic. If the database administrator needs to reach a more precise conclusion, then test analysis is required. That is, the database administrator needs to test the time of the full table scan under 1 to see if it is longer or shorter than the query after indexing. If it is long, it indicates the need for an index. But if not, it means that the scan speed of the whole table is fast. There is no need to index at this point.
In summary, small tables generally do not need to be indexed when considering whether or not a table should be indexed. And for the table, you need to carry out the actual situation of the actual analysis. Simple 1 point, can be determined according to the approximate ratio. If you want to be accurate to 1 point, you can perform a full-table scan performance analysis to determine whether the index has really improved database performance as expected.
2. Create indexes based on the characteristics of the columns.
The effect of index creation varies depending on the characteristics of the column. Database administrators need to know which columns to index to get less done. You also need to know which columns to index to get less done. This helps them understand exactly what to index for.
In my experience, indexing columns with the following characteristics often works. For example, for a column with few duplicates, especially for a column with only one constraint defined. Indexing on these columns often works very well. For example, in the case of mixing 1 column with some null values with columns with non-ES11en values, if the user needs to frequently query all columns with non-ES12en value records, it is best to index them. If you frequently need multi-table join queries, setting an index on the columns that are joined can be twice as effective.
It can be seen that the appropriate index setting is not only related to the database design architecture, but also related to the economic business of the enterprise. For this reason, for 1 package, although 1 database administrator has already done the index optimization work. But as more economic data come in later, the index becomes less effective. This is mainly because the tabulation of records affects the effect of index optimization. Therefore, The author suggests that database administrators, even if they use the software suite of big software companies, also need to optimize the index of the database every once in a while, such as one year. The removal of the removal of the adjustment of the adjustment to improve database performance.
For example, there is a table in the database to save user information. There is a field id number, which is a unique field. At database design time, an index is created for this field. But when the database is put into use, users rarely enter their ID Numbers. And usually also basically do not press this number to inquire. When the record month comes many months, the index field on this ID card number not only cannot improve the query performance of the database, but becomes the chicken rib instead. For columns that have many NULL values and don't query all of the non-NULL value records very often, the database administrator is determined, even if the indexes on those columns are cleared.
Therefore, the optimization and adjustment of indexes is a dynamic process, which does not mean that there is no need to adjust after the database is designed. Database administrators often need to make appropriate changes based on how the records change. To improve the effect of indexing.
3. How many indexes is appropriate to create on a table?
While there is no limit to the number of indexes you can create on a table, more is never better. In other words, 1+1 > 2 doesn't always work when it comes to indexing. Sometimes, creating more indexes can backfire. So how many indexes should I create on a table? There is no clear criterion for this. Rather, it is up to the database administrator to make a decision based on what is actually being used and what is being recorded in the database.
In general, the more indexes a table has, the faster its query will be. However, the table updates slower. This is mainly due to the speed of table updates (such as inserting a record into the table), which increases as the index increases. This is mainly because the relevant index information needs to be updated at the same time as the record is updated. To do this, the appropriate number of indexes to create in the table requires an equilibrium point between the update speed and the query speed. For example, for some data warehouse or decision database system, it is mainly used for query. Related records are often poured at database initialization time. At this point, the index set more than 1 point, can improve the query performance of the database. At the same time, because records are not updated very often, the update speed will not be affected in the case of more indexes. Even if you need to import a lot of data initially, you can disable the index first. Wait until the data import is complete before enabling indexes. You can reduce the impact of indexes on data updates in this way. Conversely, if records in those tables need to be updated frequently, such as in some transactional applications, data update operations are routine. If there are too many indexes in one table, the update speed will be affected. Because the update operation is more frequent, the negative effect on it is much greater than the query efficiency improvement. At this point, you need to limit the number of indexes to only 1 necessary field.
In normal database optimization, the author would often index the columns based on the purpose of these tables. You can query the relevant dynamic view to see whether the operation for this table is a large proportion of update operations (including update, delete, insert, and so on) or a large proportion of query operations. When too many indexes have slowed down the update operation, the database administrator needs to disable some indexes first to improve the performance of the database.
In short, build the right indexes on the right tables, on the right columns. This sentence contains many meanings, and the above is only part of the content. As the saying goes, master takes the door, practice depends on oneself. The author here refers to can point to so far. Some specific index optimization content or need readers in the daily work to experience and summary.